ABAP DDIC

DDIC:- Central source of information for the data in a database management is DDIC.

Objects:- Tables, view, data element, structure, table type, domain, type group, search help, lock object.

Tables:-  Tables are the entities whose information is stored in database. Information is stored in tables as a row and columns; name must be started with Y or Z & can be 16 char long. Tables are client dependent or client independent based on the field MANDT.

Client Dependent:- tables which can be access by some specific user are client dependent. If the first field of the table is MANDT and data type is CLNT, the length is 3 then it is called client dependent table. Client dependent means if we make change in one client it will be reflected in the entire cross clients. If we have 3 clients: 300,310,320. If we change in 320 it will change in 300 and 310 as well.

Client Independent:- Tables which can be access by all user are client independent (no MANDT field in table) if we change in 1 client it will not be reflected in the other clients.

Pool Table & Cluster Table:- Table pools (pools) and table clusters (clusters) are special table types in the ABAP Dictionary. The data from several different tables can be stored together in a table pool or table cluster. Tables assigned to a table pool or table cluster are indicated as pooled tables or cluster tables.

You must use a table pool or table cluster exclusively for storing internal control information (screen sequences, program parameters, temporary data, and continuous texts such as documentation). Data of commercial relevance is stored in transparent tables.

Difference between transparent tables and pooled tables:-

Transparent tables: Transparent tables in the dictionary have a one-to-one relation with the table in database. Its structure corresponds to single database field. Table in the database has the same name as in the dictionary. Transparent table holds application data.

Pooled tables: Pooled tables in the dictionary have a many-to-one relation with the table in database. Table in the database has the different name as in the dictionary. Pooled table are stored in table pool at the database level.

Transparent	Pool	Cluster
Contain a single table. Used to store master data	They are used to hold a large number of very small tables(stores customizing data or system data)	They are used to hold data from a few number of large tables.(stores system data)
It has a one-to-one relationship with a table in the database	It has a many-to-one relationship with a table in the database	It has a many-to-one relationship with table in the database
For each transparent table there is one associated table in the database	It is stored with other pooled tables in a single table called table pool in the database	Many cluster tables are stored in a single table in the database called a table cluster
The database table has the same name, same number of fields and the fields have the same names	The database table has different name, different number of fields and fields have different names	The database table has different name, different number of fields and fields have different names
There is only a single table	Table pools contain more tables than table clusters	Contains less tables than table pools
Single table can have one or more primary key	Primary key of each table does not begin with same fields or fields	Primary key of each table begins with same fields or fields
Secondary indexes can be created	Secondary indexes cannot be created	Secondary indexes cannot be created
They can be accessed using open and native SQL	They can be accessed using open SQL only	They can be accessed using open  SQL only
USE: They are used to hold master data e.g. Table vendors or table of customers. Example of transaction data is orders placed by customers	USE: They reduce the amount of database resources needed when many small tables have to be opened at the same time	USE: They would be used when the tables have primary key in common and data in these tables are all accesses simultaneously

Difference between table & structure:- Structure can be define like a table but it never has entries, difference between table and structure is that the structure does not exist in the database system, but tables and views do exist in database. Only at runtime structure holds the data whereas tables hold the data permanently in the database.

Views:- The data of a view is derived from one or more tables, but not stored physically. Fields that is not required can be hidden. Views are helpful for reducing the redundant tables because there is no need to create tables for each application.

Database View:- Database view is generated by using one or more transparent tables which are related to each other. Joins, selection and projection operators are used for creating database view. Database views implement an inner join. The other view types implement an outer join.

Projection View:- projection view is used to hide some fields. This can minimize interfaces; for example when you access the database, you only read and write the field contents actually needed. It is applied only on single table. You cannot define selection conditions for projection views. It supports insert, update, delete and select operators.

Help View:- is used for creating online help system for additional information. It can be accessed only through the SAP help. Can be used as selection method in search helps.

 Maintenance View:- A maintenance view permits you to maintain the data of an application object together.

Difference between Projection View & Data Base View:-

Projection View	Database View
It must be created using a single table	It can be created using one or more table
Name of the fields must be same as the name used in the corresponding table	Name of the field can  be different from the name used in corresponding  tables
Modification of data can be made	Modification is possible If single table view is created

Data Element:- It is used to describe the semantic attributes of the table fields, like description the field. Data element describes how a field can be displayed to end-user.

Domains:- Domain is the technical attributes of the field it describes the technical characteristics like its data type, decimal places, data length and value range.

Delivery Class:- The delivery class helps to control the transport of table data for an installation or upgrdation in a client copy and transports between customer systems. It is also used in the extended table maintenance.

Types of Delivery Class:

A	Application Table	It is used for storing master and transaction data.
C	Customer Table	Data is only maintained by the customer.
L		It is used for storing temporary data.
G	Customer Table	SAP can insert new data records but cannot overwrite or delete existing ones.
E	System Table	With its own namespace for customer entries. The customer namespace must be defined in table TRESC.
S	System Table	Data changes have the status of program changes.
W	System Table	It is a table of the development environment. Data of this table is transported with its own transport objects.

Technical Settings:- Technical settings are used at the time of table creation. It helps making control during table creation.  It provides the following benefits:

(i)                 Access the table behavior

(ii)               Specify the buffering requirement

(iii)             Specify the storage space requirement

(iv)              Upgrade the logged entries

Parameters of technical settings:

(i)                 Data class

(ii)               Size category

(iii)             Buffering

(iv)              Log data changes

Data Class:- The data class determines the physical area of the database (table space) in which the table is created. There are 5 data classes: 

APPL0: Master Data - Master data is data which is frequently read, but rarely updated. 

APPL1: Transaction Data – Data which Transaction data is data which is frequently updated.

APPL2: Organizational & Customizing Data – Specify when the system is configured and then not changed frequently. Like country codes, city codes.

USR1 & USR2: these classes are used for user developments.

Size Category:- The size category defines the expected space required for the table in the database. You can choose a size category from 0 to 4 for your table. Each category is assigned a certain fixed memory size in the database, which depends on the database system used, and then some space is reserved in the database. If more space is required at a later time due to more data entries, additional memory will be added depending on the selected size category.

Log Data Changes:- This parameter defines whether the system logs changes to the table entries. I If logging is switched on, the system records each change to a table record in a log table.  (with UPDATE, DELETE)  to an existing data record by a user or an application program is recorded in a log table (DBTABLOG) in the database.

SAP Database change logs - simple solution for recording database change history

There is a very simple way to activate change history for database tables in SAP, including your own custom z tables. All you have to do is check the 'Log data changes' checkbox within technical settings. 

Once you have done this and activated it you simply use transaction SCU3 to view any changes made to data within this table. There is obviously a very slight performance hit involved with doing this as logs need to be created each time a change is made so you probably only want to turn this on for tables you are interested in recording history for. 

These logs are stored in table 'DBTABLOG' and can be access via function module 'DBLOG_READ'. 

View change log entries 
Simply execute transaction SCU3 and press the 'Evaluate Logs' button. Now enter your table name and display option you require and press execute. The change logs should then be displayed.

How to Convert Pooled Tables to Transparent Table:

You can easily convert pooled tables to transparent table using the transparent flag in the technical settings. You can use this option to access a pooled table from outside the SAP system.

Procedure

  1.  Open the Repository Browser (transaction code SE80) and navigate to the pooled table that you want to convert.

  2.  Double-click on the pooled table. The maintenance screen of the table appears.

  3.  On the maintenance screen of the pooled table, choose Technical Settings.

The maintenance screen for the technical settings of the pooled table appears.

  4.  On the technical settings maintenance screen, select the Convert to transparent table indicator.

This corrects only the technical settings and not the table definition.

  5.  Maintain the remaining attributes of the technical settings.

  6.  Choose   with the quick Info text Activate. The technical settings are activated.

If you have set the transparent flag of a table and you want to convert it back into pooled table, you must first cancel the transparent flag. Then activate the technical settings. Only after these actions you can change the table category.

Buffering:  Buffering is used to reduce database load and improves the performance when accessing the data records contained in the database table.

Buffering permission: The buffering permission defines whether the table may be buffered.

Here possibilities are:

i) Buffering not allowed

ii) Buffering allowed but not activated

iii) Buffering activated 

How to Buffer: The data buffer is checked first to see whether it is there. If not, the data is read from the database and stored in buffered. The record is saved in RAM on the application server in data buffer. Later the record is read from the buffer instead of the data base. The performance using the buffered data runs faster because they don’t have to wait for it to come from the data base.

Buffering Types:  Full buffering, Generic Buffering, Single Record Buffering.

Full Buffering: All Records of the table are loaded into the buffer when a record in the table is accessed. This type of buffering is mainly used for small tables.

Generic Buffering: All Records with the same key fields are loaded into the buffer when a record in the table is accessed. This statement causes the buffering of all records of table that correspond to client 100 and company code "Poland". Only the data that is relevant to the company code used on a particular application is loaded in the buffers of that server.

Single Record Buffering: The specific record that was accessed is loaded into the buffer. It requires less storage space in the buffer as compared to other buffering techniques.

Buffering Notes:

·         Transparent and pooled tables are buffered but cluster tables can not buffered.

·         Big tables should not be buffered, use index instead.

·         Table which has frequent modify like (insert/update/delete) should not be buffered. Mean mostly read and rarely changed table cannot buffer.

·         The table containing currency exchanges rates is updated only ones a day, but it is read frequently, buffering is recommended in this case.

Index:  Index helps to speedup selection from database. An index is sorted copy of selected database table fields. Sorting provides faster access to the data records of the table, for example using a binary search.

Two types of index: Primary and Secondary Index.  

Primary Index: Automatically created using the primary keys. The primary index contains the key fields of the table and a pointer to the non-key fields of the table.

Secondary Index: User creates manually. Max no of secondary index can have 9. It can be either unique or non-unique.

Use of sec index in report:

SELECT <fields> INTO <work area> FROM <database table>

        WHERE <logical expression>

          AND <logical expression>

%_HINTS <database name> 'INDEX("<database table>" "Index name")'.

 How to Check if an Index is Used:

Procedure

1. Open a second session and choose System ® Utilities ® Performance trace. The Trace Requests screen appears.
2. Select Trace on. The SQL trace is activated for your user that is all the database operations under your user are recorded.
3. In the first window, perform the action in which the index should be used. If your database system uses a cost-based optimizer, you should perform this action with as representative data as possible. A cost-based optimizer tries to determine the best index based on the statistics.
4. In the second session, choose Trace off and then Trace list.

Unique Index: An entry in an index can refer to several records that have the same values for the index fields. A unique index does not permit these multiple entries. The index fields of a unique index thus have key function that is they already uniquely identify each record of the table.

The primary index of a table is always a unique index since the index fields form the key of the table, uniquely identifying each data record.

You can define a secondary index as a unique index when you create it. This ensures that there are no double records in the table fields contained in the index. An attempt to maintain an entry violating this condition in the table results in termination due to a database error.

Append Structure: Sometimes we need to add new fields into SAP standard tables to meet user requirements. Fortunately, SAP has provided tool to add fields without change the tables called Append Structure.

Points:

·         An append structure is a structure that is assigned to exactly one table or structure.

·         There can be more than one append structure for a table or structure.

·         The name of append structure should also reside in the customer namespace, which is the field names should begin with ZZ or YY. This prevents name conflicts with fields inserted in the table by SAP.

·         You use append structures for enhancements that are not included in the standard. This includes special developments, country versions and adding customer fields to any tables or structures.

·         No append structures can be added to tables with long fields (data types VARC, LCHR or LRAW). This is because a long field must always be the last field in the table. Structures with long fields can be enhanced with append structures.

·         The table to be enhanced should not be a pool or cluster table.

·         Append structures appear in original table / structure with .APPEND key word.

·         When an append structure is changed and activated, the original table / structure also gets activated automatically.

Possible Enhancement Categories:

• Cannot be enhanced: No customer enhancements would be possible.
• Can be enhanced (character-type): Only character type fields (data type C, N, D, or T) can be added.
• Can be enhanced (character-type or numeric): Fields of all data types except deep data types (tables, references, strings) are allowed to be added.
• Can be enhanced (deep): Fields of all data types are allowed to be added.

Include Structure:  Used for add some fields in Tables. We can include structure A in structure B using keyword include. The structure to be included can be local or a dictionary object. To include all fields from structure used including. This will create direct fields in other structure. Fields can be access directly.

Use of Local Include structure in report:

data : begin of dept,

           deptno type i,

           dname(20) type c,

       end of dept.

data : begin of emp,

         empno type i,

         ename(20) type c.

         include structure dept.

data : addr(30) type c,

       end of emp.

Append Structure	Include Structure
You can append structure only at the last of table/structure.	You can use include anywhere in your  Table/structure.
We can't use that structure in another table	We can use include structure in more than   one table
Append Structure is to add fields to the SAP standard Table/structure.	Include structure is used to add field in ztable/zstructure.
If the structure or table contains the last field as long  data type then you can't use append structure.	In this scenario you can use include structure.
You can append exactly one append structure to a table/structure if u need it to add more than in a table you can do it with different name and append it.	Add up to 9 Structure

Foreign Key:  Foreign key is used to describe the relationship between different tables. The use of foreign key is to validate the data being entered into one table by checking against a valid set of values in another table.

The table that is referenced by the foreign key (in our example, First Table) is called the check table.  The check table is also known as the "referenced" or "parent" table.

The table that contains the foreign key fields (in our example, 2^nd Table) is called the foreign key table. The foreign key table is also known as the "dependent" or "child" table.

The foreign key field and the primary key of the check table must share the same domain.

Foreign keys are used for:

1.       Maintaining data integrity

2.       Providing additional texts in the online help system

3.       Creating other dictionary objects that are defined over multiple tables (such as views)

Example: Table T1 is called the foreign key table (dependent table) and table T2 the check table (referenced table). The pair of fields for the two tables must have the same data type and length. One field of the foreign key table therefore corresponds to each key field of the check table. This field is called the foreign key field.

Cardinality: When creating foreign key relationships, you have to specify the relationship in between the 2 tables. Means for 1 entry in the main table, how many can be possible in the foreign key table. This is called cardinality. Let's see how many possibilities can be happen in this case.

For the left side:

 N = 1     Each record in the foreign key table refers to exactly one record in the check table.

 N = C     Each record in the foreign key table refers to zero or one records in the check table.

For the right side:

 M = 1   Each record in the check table has exactly one dependent record.

 M = C   Each record in the check table has a zero or one dependent records.

 M = N   Each record in the check table has at least one dependent record.

 M = CN Each record in the check table has zero, one, or many dependent entities.

Primary Key: A primary key is a field or group of fields that uniquely identify a record in a table. Primary Key fields cannot be NULL and cannot contain duplicate values. If you want to link two tables, the primary key of one table will be added to another table where primary key of first table will be become the foreign key of second table.

Value table:  The table contains a set of values at domain level. Which means that field can have values among them only.

Check table:  This table is the table to which your field is mapped. This means your field will always check this table for possible values. If there no entry for your entry in that check table, you cannot add that entry to your table.

Search Help:  It permits the user to display a list of all possible values for a screen field.

Seach Help FM: F4IF_INT_TABLE_VALUE_REQUEST

 3 Types of Search Help:

Elementary Search Help: defines a search path where we will define the table from which the data has to be read and the selection criteria. Through import and export parameters. Used when we gets the data rom a single table.

Collective search help: of elementary search helps. When we need to fetch data based on multiple selection criteria s. More than one table are selection from multiple tables.

Elementary search helps are those which use only 1 table, whereas using collective we can use multiple tables.  It permits the user to display a list of possible values for a screen field. A value can be directly copied to an input field by list selection.

Append search help: An append search help is used to enhance a collective search help (that is not the original in the current system) by further search paths (elementary search helps) without modifications. This technique can be used for example by special developments, country versions, SAP partners and SAP customers to add further search paths to a collective search help in the SAP standard version.

Parameter of a search help:

• Import parameters: Parameters with which context information from the processed input template (screen) may be copied to the help process.
• Export parameters: Parameters with which values from the hit list may be returned to the input template.
• Internal Parameters: Internal Parameters are used for internal input help process.

A parameter can simultaneously be an input and an export parameter. A search help can also contain parameters that are neither import nor export parameters

When you define a parameter of a search help, you must also define whether it should be used to copy data to the input help (IMPORT parameter) or whether to return data from the input help (EXPORT parameter).

Diff between Elementary search helps & Collective search helps: 

1)      Elementary search helps describe a search path. The elementary search help must define where the data of the hit list should be read from (selection method), how the exchange of values between the screen template and selection method is implemented (interface of the search help) and how the online input help should be defined (online behavior of the search help).

2)      Collective search helps combine several elementary search helps. Collective search help thus can offer several alternative search paths.

3)      An elementary search help defines the standard flow of an input help.

4)      A collective search help combines several elementary search helps. The user can thus choose one of several alternative search paths with collective search help.

5)      A collective search help comprises several elementary search helps. It combines all the search paths that are meaningful for a field.

6)      Both elementary search helps and other search helps can be included in a collective search help. If other collective search helps are contained in collective search help, they are expanded to the level of the elementary search helps when the input help is called.

Lock Object: Lock objects are use in SAP to avoid the inconsistency at the time of data is being insert/change into database. , it should start with a E and then followed by Y or Z.

When you create a lock object System automatically create two function modules.

1. ENQUEUE_<Lock object name>. to insert the object in a queue.

2. DEQUEUE_<Lock object name>. To remove the object is being queued through above FM.

Normally if a person opens table maintenance generator and tries to maintain the table, no one else can maintain the same table at the same time. This is because of table level lock by default in SAP. Only one user can maintain any table at a time through SM30 or any transaction that calls table maintenance generator.

SAP Provide three types of Lock objects. 

- Read Lock (Shared Locked)

   Protects read access to an object. The read lock allows other transactions read access but not write access to the locked area of the table.

- Write Lock (exclusive lock)

   Protects write access to an object. The write lock allows other transactions neither read nor write access to the locked area of the table.

- Enhanced write lock (exclusive lock without cumulating)

  Works like a write lock except that the enhanced write lock also protects from further accesses from the same transaction.

Lock Types

There are four types of locks in the SAP System:

1.     Shared lock

2.     Exclusive lock

3.     Exclusive but not cumulative lock

4.     Optimistic lock