The
transaction of the database dictionary is SE11. It is a central repository
to create objects and to store the data. An advantage of a data dictionary is
reusability - once an object is developed in a data dictionary it can be used
in any program throughout the SAP.
Creating
Database Tables
Radio
Button 1: Database Table Overview
Database
table is a combination of structure and table data (table structure consists of
field name and table data will store the business data like master and
transaction data) in which we can display, Create, and Update the changes in
the table.
Step-by-Step
Table Creation Process
Let's
create a table zcustomer_tabl table.
Step 1: Table Name Creation
Write the
name of the table that you want to create in the box. If that name is already
taken by another table write another name, and click on create button.
Step 2: Short Description
We need to
write a short description first (any meaningful description regarding tables
like customer information).
Step 3: Delivery Class Configuration
Then we
need to provide a delivery class in the delivery and maintenance tab. It is a
tick mark field which means it is a mandatory field we need to provide value
here otherwise it will throw an error (compulsory otherwise we cannot proceed
further).
Select the
search help near the box or press F4. It will display the search help bar. We
have 7 types of delivery classes:
- A - Application table
(master and transaction table - 99% we will work with master and
transaction data)
- C - Customizing table (we
can make direct changes only in customizing server only not in the
production server)
- L - Used by application
programmers
- G, E, S, W - Used by SAP only
Understanding
Data Types
Master
Data - It is
only created once in the database (e.g., Bank Master).
Transaction
Data - Which
is created frequently or changes frequently (e.g., deposit, withdraw).
Delivery
Class - It is
used to control the Transport of the table data. It is used to specify the type
of the table.
- In Real time we mostly use the
delivery class 'A'
- In the case of delivery class
'A' the table can maintained in any system (development, quality,
production systems) using SM30
Data
Browser/Table View Maintenance
This
property is used to specify whether the table can be maintained or not. In real
time we mostly use "Display/maintenance allowed" option.
It is of 3
types:
- Display/Maintenance allowed
with Restrictions -
We can only Display the data. Create, or change is not possible
- Display/Maintenance allowed - We can Display,
Create, Change directly
- Display/Maintenance not
allowed -
We cannot create, display, or change the data directly
Step 4: MANDT Field Configuration
In the
fields tab while creating a table in SAP, the first field always be MANDT and
it is always the primary key, it is a non-transportation object.
MANDT - Using mandt we can restrict
the data across the client (dev and unit test system). Whatever data we have
entered in the development server should not be there in another system like a
unit test server, that is why we have to use the mandt field.
Step 5: Primary Key Definition
Then write
your all fields in a single table. Min. 1 primary key is mandatory and max. 16
primary keys allowed, tick key button, and initial checkbox.
- The key checkbox will tell us
that the field is a primary key
- An initial checkbox will tell
us that the column cannot be NULL
Step 6: Field Definition
Then write
the field name that you want to insert into the table. We can give the data
type and field description directly but we will lose the features like search
help and value range which are given through data element and domain level
only.
Step 7: Technical Settings
After
defining the data type with their corresponding fields we need to give a
technical setting that consists of data class and size category.
Data
Class - Is
used to specify the type of the data to be maintained in the table. Data class
provides what type of table it is:
- APPL0 (master data class) -
The data which is not changing frequently (e.g., DATE OF BIRTH)
- APPL1 (transaction data class)
- The data which is changing frequently (e.g., SALARY)
- APPL2 (organizational data
class) - The data which is changing rarely (e.g., COMPANY CODE)
In Real
time we mostly use APPL0 or APPL1.
Size
Category -
Size category ranges from 0 to 8. It determines the space required for the
table.
- It is used to specify the
initial space required for the table in the database
- In real time we mostly use '0'
category (0 – 8) -> [0 – 2700]
- 0 means e.g., 8kb it will
reserve 8kb of space in the main memory
Step 8: Currency and Weight Fields
If you
want to add any currency or weight field you need to define the reference table
and reference field. Because every Country has different measuring units that
is why SAP has made it mandatory.
Activate
it.
Enhancement
Category
EXTRAS
-> ENHANCEMENT CATEGORY
Enhancement
category is used to enhance the particular fields of the table. In Real time we
mostly use Can be enhanced (DEEP).
- If we want to add any fields
in the future we have to choose can be enhanced
- If we don't want to add any
fields then cannot be enhanced
- If not sure then select not
classified
Foreign
Key Relationships
FOREIGN
KEY - Select
the row in which you want to add a foreign key with the parent table click on
the key icon. It will ask for a check table which will be the parent table that
gives the name of a parent table.
Click on
the proposal it will give you the field name in which the foreign key can be
maintained. Select on that field click on ok. That field will be added as the
foreign key. Parent table is called check table and we can maintain foreign key
with its primary key and child table column but data element must be same to
apply foreign key in the child table.
It gives
data element level field validation.
Cardinality
Ratio =
Single record of a header table corresponds to multiple records of a Secondary
table.
Text
Tables
Text
Table – These
are Tables That hold the Description of the Parent Table.
Creating
Text Tables
Step 1:
Create A Parent Table
Step 2:
Create a text table
2nd key
always be langu in the text table.
Step 3:
Create a foreign key relationship
- Mark 1st row and select
foreign key button
- Mark 2nd row which is langu
and select foreign key button
- Mark the 3rd row select the
foreign key button and give the parent table name in the check table field
- Mark the radio button Key
fields of a text table
- Cardinality 1:N
- Create a proposal and click on
copy
Text table
is now created.
Step 4:
Check the text table in the parent table
It will
show text table was created or not.
Index
Tables
INDEX
TABLE - To
improve the performance of select queries. Suppose, your query is taking too
much time to execute then to resolve that issue we need to create an index
table. E.g., bseg, mseg, lips etc.
We create
index tables in very rare cases, especially in the ECC system. Not necessarily
in the Hana system because the Hana system is very fast.
Advantages
- It gives high performance
- It stores the data always in
sorted order
Disadvantages
- It occupies space from the
table in which the index table is being created which will reduce the
performance of the database table, which is why it is recommended to
create an index table very rarely
Types
of Index
Primary
Index
Primary
Key will act as Primary Index. We can create Primary Key for custom Table only.
SAP automatically creates a primary key index implicitly, when we activate our
database tables.
When we
search the record in any tables based on the primary key it always gives high
performance.
Secondary
Index
These are
created and maintained by the Technical Consultants/ABAPer. Based upon our
requirement, we will create a secondary Index. Maximum 9 Secondary Index we can
create in a table. Secondary Index is possible for both Standard and Custom
Tables.
Creating
Index Tables
- STEP 1: CLICK IN INDEX TAB
- Step 2: Click on create button then
create index
- Step 3: Give 3 digit Index Name
Starting with Z and click Continue
- Step 4: Give a Short Description and
field Names that you want to use in your program
- Step 5: Give Field Names and
Activate your index table
Buffering
Overview
Buffering
is a temporary storage on the application layer. There are 3 layers in SAP:
- Presentation Layer – In which we interact
with SAP system
- Application layer – In which we write our
programs
- Database layer – In which tables data
are residing there (it is a permanent storage)
It
improves the performance when accessing the data records contained in the
table. Sometimes, we need to access a particular set of records more frequently
than others. Accessing Records Directly from a database table can be very
time-consuming.
Buffering
Options
- Buffering not allowed – Table buffering Is not
performed for the table
- Buffering Switched on – Table Buffering is
Performed for the table
- Buffering allowed, but
switched off -
Buffer is allowed but It is switched off and can be switched on anytime
according to the requirement of the customer
Types
of Buffering
1.
Single Record Buffering
In the
case of single record buffering single record load in the buffer at a time.
When to
use: Single record
Buffering should be used for tables where single records are accessed by
specifying the complete Key.
Advantages:
- This type of Buffering
requires less memory on the application layer
Disadvantages:
- There is no Significant
reduction in the number of database access
2. Full
Record Buffering
In the
case of Full Record Buffering, The Full Records of a table load in the buffer.
When to
use: Tables that
are best suited for full record buffering are frequently accessed and rarely
changed. Tables that contain transaction data should not go for full record
buffering.
Advantages:
- There is a Significant
reduction in no. of database access
Disadvantages:
- This type of buffering
requires more space on the application layer
3.
Generic Area Buffering
The
records that match the generic key are loaded in the buffer. The best example
of generic area buffering is language-dependent tables.
In the
case of Generic Area Buffering we need to pass the number of key fields. The
number of key fields for a generic area buffering is less than the primary key
of a table.
Important
Points in Buffering
- If we don't need buffering
select checkbox buffering allowed but switched off
- If we want to reset the buffer
of a particular table never use t-code /$TAB. This t-code will reset the
buffer of all tables not a single table
- We can pass the table in
t-code AL12 to reset the buffer of a particular table
Table
Maintenance Generator (TMG)
Table
Maintenance Generator -
It is used to maintain multiple entries & validate the table data using the
T-code SM30.
In
real-time users cannot access SE11 code. Using a tool called TMG users can
perform data entry (DML operation - insert, update, delete). In TMG, Users can
perform operations to a particular table plus they will be restricted to a
particular table only.
Steps
for Working with TMG
- Execute SE11
- Select the Radio button DB
Table
- Provide the Table name &
click on change
- Click on utilities
- Click on TMG
- Select the auth. group as
&NC& and Provide the function group as your table name
- Select the one-step radio
button
- Enter the overview screen
number (could be any). [4 digit only]
- Click on the create icon(F6)
and click on save
- Press the enter button &
Click on Local Object or TR and click on Back
- Test TMG through SM30
- Create t-code in SE93 (same
name as table name)
Maintenance
Types
- One-step - Data entry (multiple
records) if our table has fewer fields then select one step
- Two-step - Data entry (record by
record) if our table has more fields then select two steps
Recording
Routine
- Standard recording routine - It will ask for
transport requests
- No, or user, recording routine - It will not ask for
transport requests
Events
in TMG
Validation
can be done by Table events using SM30. Totally 38 events are there in TMG.
Mostly we
use these 5 events in real life:
- Before saving the data in the
database
- After saving the data in the
database
- Before deleting the data
display
- After deleting the data
display
- Creating a new entry
Important
Points in TMG
- To generate the TMG, data
browser/table view maintenance options need to be - display/maintenance
allowed
- Whenever we add new
columns/fields to the table after the generation of TMG, the newly added
fields do not appear automatically while maintaining data with the help of
SM30. We need to delete the TMG and generate it again
- To delete the TMG, open the
table in change mode, otherwise delete button does not appear
Database
Utility
Database
Utility acts
as an Interface between the ABAP dictionary and the database.
We can
call the database utility from the initial screen of the ABAP dictionary with
utilities -> Database utility (T-Code: SE14).
Purpose
of Database Utility
- To check the default value of
table fields
- To adjust the table data
Important
point: Do not
perform any database operation on the table while performing the database
utility. If you do that it will delete the data from the table.
Data
Browser
The t-code
for data browser is SE16. Through data browser we can view the data
of a table. From SE11 we can navigate SE16 data browser. The t-code for new
data browser is SE16N.
Types
of Database Tables
There are
3 types of database tables:
1.
Transparent Table
In case of
transparent table, there is a relationship between the ABAP dictionary and the
database.
2.
Pooled Table
In the
case of a pooled table, there is an N:1(many to one) relationship between the
ABAP dictionary and the database. For pooled tables, a primary-foreign key
relationship is not required.
Table
pool structure:
Tabname Varkey Dataln Vardata
3.
Cluster Table
In case of
cluster table, there is N:1(many to one) relationship between the ABAP
dictionary and the database. For cluster tables, primary-foreign key
relationship is mandatory.
Table
cluster structure:
Key Pageno Vardata
Views
Views - Views is a virtual table.
View does not have any data. It is bringing the data from a table. View is a
logical database because it has no data while table is a physical database it
has its own data.
The View
is a virtual table that gets the field and data from other tables.
Table
vs View Comparison
Table:
- Having fields and we can store
data
- A table has a technical
setting
- In a table, we can store 1
kind of data either master or transaction
- Additional resources are
required to maintain tables like technical setting, data class, size
category that is why they have poor performance compared to views
View:
- In view, we get the field and
data from other table
- Views don't have technical
settings
- In views we can view both
master and transaction data simultaneously
- No additional Resources are
required (Good Performance)
Types
of Views
1.
Database View
It is
created based on 1 or more than 1 table using join condition. Tables Should
have relationship between to create Database View.
In the
database view, the maintenance status is read-only which means we can not
change the data we can see only in the database we can put selection
conditions.
2.
Projection View
Projection
view only creates with a single table called a basis table. Suppose if a table
has 200 columns and we want to show only 5 columns then we will use projection
view.
Maintenance
status is always in change and display mode.
3.
Maintenance View
It is also
known as a Table maintenance generator. Its purpose is to maintain multiple
table. The tables must have relationship between them. Take mandt field in the
maintenance view.
4. Help
View
It is also
called search help. The tables must have relationship between them.
Important
Points: Not more
than 1 record should exist in the dependent/Secondary table.
