Database Tables in SAP
Database Tables in SAP
To understand database tables in SAP better, we have to first understand SAP’s architecture. Here is a small glimpse into SAP’s 3-tier architecture.
Tier – I : This is the UI layer. When you log into SAP using the SAP GUI, this GUI can be called the UI layer. You can also log in using the browser based Web UI.
Tier – II : This is the Application Server Layer. This is the core of SAP. This comprises of the runtime environment of SAP. At a high level there is a dispatcher that looks at the incoming request from the front end and allocates a work process ( WP ) to take care of the request. There are a lot of other processes involved in SAP’s runtime environment and they are beyond the scope of this ABAP tutorial. For now it suffices to know that an ABAP program’s runtime environment is in the Application Server’s memory.
Tier- III : This is where the database resides. SAP has been built in a database agnostic way. There are a list of databases supported by SAP ( For example, Oracle, Informix, DB2, Max DB, SQL Server etc ) and as long as you use one of those databases in tier-III SAP will work. Now, we all know that each of these databases have their own versions/flavors of SQL. So when SAP’s Application server interacts with the database layer, it talks via the “Database Interface” or simply DBIF. The task of DBIF is to ensure that it converts SAP’s Open SQL into native SQL understandable by the corresponding database.
Without going much further into SAP’s architecture, let’s start by creating a database table in SAP. Let’s try to create a database table “Customer” as follows.
|Database Table Name||ZCUST|
|Database Table Description||Customer|
|Database Table Fields||CUSTNO|
That’s a very simple database table. You might have immediately noticed a peculiarity. Why did I define the database table name as “ZCUST” as opposed to saying “CUST” ? To understand this, we have to first understand the concept of SAP Namespace.
SAP Namespace :
- SAP reserves the right to create new programs starting with the letter ‘A’ through ‘X’
- SAP gives its customers the right to create new programs starting with either the letter ‘Y’ OR ‘Z’
- SAP gives its partners special names spaces.
This is a very important concept. Imagine a scenario when SAP is implemented in a company say ‘Johnson & Johnson’ with lots of customization in Version 4.7. Now, J&J wants to upgrade SAP to ECC 6.0. As part of the upgrade SAP overwrites a lot of ABAP Objects. Since J&J has written all of its custom programs starting with either a ‘Y’ or ‘Z’, SAP’s upgrade ensures that those programs are not touched. It is however the responsibility of the customer to ensure that all those custom programs work with the new version of SAP – This is essentially what an upgrade project involves anyway.
Before creating the database table itself, we have to ensure that the fields and the corresponding data types and domains are created.
A domain describes the technical characteristics of the field. It includes the following
- Field Length
- Data Type
The Data element contains the “Field Labels” and Online documentation for the field. ( This is the documentation that shows up when a user hits F1 on that field )
Let’s go ahead and start to create the database table. We will follow a Top-Down Approach to creating the database table. This is a much more intuitive way. The Bottom-up approach is equally easy conceptually but can become a bit confusing for bigger tables.
Step 1 :
Goto ABAP Dictionary and enter the name “ZCUST” and click on CREATE button
Step 2 :
Enter the Database table Description.
Step 3 :
Under the Delivery and Maintenance Tab, Choose a Delivery Class of “A”. This means that this table can contain master and transactional data. Customer data falls under the category of Master Data and hence we have chosen the delivery class of “A”
Choose “Display/Maintenance Allowed” in the data browser field.
Step 4 :
Now go to the Fields tab and start adding fields. Since this is an Application data table, add the field MANDT to the beginning of the table. Do not add any data elements just yet ( Except for MANDT which is already defined by SAP ). We have not created them. Save the Database Table. We will add Data Elements after we create them. While saving, make sure you select a Z package – say Z_IDES. If you are asked to create a transport, either create a new one or hit enter to record it under an existing transport.
Make sure that MANDT and CUSTNO are checked-on for Primary Key.
Step 5 :
Create Domains : Enter the domain name – ZCUSTNO and click on Create.
Enter a Short Description. We want the customer number to be alpha numeric. So let it be a Character ( CHAR ) of length 10.
Now, in order to use it , we have to ACTIVATE it. Click the Active Button ( ) to activate it.
Similarly, create the ZCUSTNAME domain as CHAR (100 ). We don’t want a customer whose name exceeds 100 characters 🙂
Step 6 :
Data Elements : Data Elements link the database table field to the domains. Let’s start by creating data elements for CUSTNO and CUSTNAME fields.
Goto [SE11] and enter the data element name ZCUSTNO in the Data Type field and click on Create.
Choose “Data Element” in the pop-up.
Give a short Description and select the ZCUSTNO domain name that we have created in Step 5. The data type and length are automatically picked up. If not, make sure the domain is activated.
Goto the Field Label tab and enter a description.
Activate the Data Element. Similarly create data element called ZCUSTNAME.
Step 7 :
Add the data elements to the table that we have created in Step 4. Goto [SE11], enter the database table ZCUST in the field database table and click on Change. Add the new Z data elements that we have created.
Step 8 : Technical Settings
Click on the Technical settings button on the top application tool bar.
Since we are creating master data, select APPL0. Select a size category. Buffering is not allowed by default. If the database table is huge and lots of “READ” operations happen on the table, enable buffering.
Add Field Labels
Step 9 :
Finally Activate the table. ( )