Learn to adapt SAP ERP HCM programs so that they function without any problems after migration to the SAP HANA platform.
Key Concept
SAP HANA is an extremely fast, in-memory, column-based relational database management system that allows applications to store and retrieve data as well as to perform complex analytical calculations.
Many companies are planning to switch to SAP HANA if they have not done so already. It is therefore essential that the SAP ERP HCM programs be fully functional and written in a way to make the best use of SAP HANA.
In this first article of my two-part series, I cover the considerations to keep in mind for custom coding when working on an SAP HANA implementation or migrating to an SAP HANA system—what areas you should look into and what coding to avoid for correct program function and good performance on SAP HANA.
I start with a brief SAP HANA overview. Then I show you how custom code must be changed so that the program function is not hampered. In addition, I explain the code constructs or parts of an SAP ERP HCM program that can be optimized to get the best performance on SAP HANA.
Examples of SAP ERP HCM coding with SAP ERP HCM tables clearly demonstrate both good code and constructs that should not be used.
SAP HANA: An Overview
SAP HANA is an in-memory database that allows fast calculations and data retrieval. Before diving into the details of what needs to be changed in SAP ERP HCM programs, I briefly describe the storage of the tables. Prior to SAP HANA, data was stored in row-based database tables. In SAP HANA, however, you now store data in column-based architecture as well. SAP HANA supports both row- and column-based storage, but it is most suited for column-based architecture.
SAP HANA is an in-memory database that allows fast calculations and data retrieval. Before diving into the details of what needs to be changed in SAP ERP HCM programs, I briefly describe the storage of the tables. Prior to SAP HANA, data was stored in row-based database tables. In SAP HANA, however, you now store data in column-based architecture as well. SAP HANA supports both row- and column-based storage, but it is most suited for column-based architecture.
Consider the data stored in table T512T. Execute transaction code SE11 and in the Name field of the screen that the system displays, type the name of the table (T512T). Click the Technical Settings button. Then, from the screen that appears, select the DB-Specific Properties tab, as shown in Figure 1.
Figure 1
Database-specific properties of table T512T
Note that the Column Store radio button is selected for the storage type for the table in question. Almost all the tables use the column-storage architecture.
In SAP HANA, there are no pool or cluster tables. They have been converted to transparent tables. In addition, because the database is in-memory and because it is so fast, it is not necessary to create secondary indexes on database tables for optimal performance.
When an SAP HANA migration is done, the code in the standard SAP system (i.e., the original SAP code) continues to run without problems. SAP has adjusted the standard SAP code to function properly in the SAP HANA environment. However, for custom code, including that of the SAP ERP HCM module, you need to make certain adaptations to keep the code fully functional and optimal for SAP HANA. You need to complete these three steps:
- Migration to SAP HANA may leave some of your custom programs with syntax errors. Statements or constructs that were allowed in the pre-SAP HANA version might not function after migration.
- Some programs might not give any syntax errors, but also do not yield the correct results, such as programs that assume data is to be returned in a sorted order from a SELECT statement.
- Some programs might need slight changes to the traditional ABAP Open SQL (based on the new database access golden rules) to capitalize on SAP HANA’s performance. In this step, no modern and advanced features such as advanced Open SQL, Core Data Services (CDS) views, or ABAP-Managed Database Procedures (AMDPs) are involved. This is a step recommended by SAP and SAP experts. In some cases, it may not be necessary. For example, the original Open SQL may have good performance, so this step, which requires considerable work, might not make a substantial difference.
The first job is to make sure that all custom programs are functional and without syntax errors. You need to perform a complete check on the existing ABAP code. Most of the custom code functions as anticipated.
Once steps 1 through 3 are done, you may need to use more advanced ABAP features, such as CDS views, AMDPs, and advanced Open SQL, to push more application server logic to the database to fully use SAP HANA. These advanced features are not covered in this article.
Native SQL
Make sure to remove (and substitute) any code within an SAP ERP HCM program that is specific to the underlying database, such as Oracle. Typical examples include Native SQL and the use of database hints in Open SQL statements. These were allowed in the pre-SAP HANA environment, but upon migration to SAP HANA, they must be removed. Consider the example shown in Figure 2.
DATA ename TYPE pa0001-ename.
EXEC SQL PERFORMING read_row.
SELECT ename FROM pa0001 INTO :ename WHERE mandt = :sy-mandt
ENDEXEC.
cl_demo_output=>display( ).
FORM evaluate.
cl_demo_output=>write( |{ ename } | ).
ENDFORM.
Figure 2
Native SQL example
As you can see, this code uses Native SQL. The example is very simple, but it shows you the details of a Native SQL construct. This construct depends on the database to accept the given piece of code.
To remove any chance of errors, before migration to the database remove this Native SQL code from the program and replace it with an equivalent database-independent Open SQL statement. This step ensures that the SAP ERP HCM program functions on SAP HANA. Once this is done, test the code and check the program to make sure it yields the correct result on the SAP HANA platform.
In addition, your custom programs may have accessed the ABAP Database Connectivity (ADBC) that provides an object-oriented application programming interface (API) to the Native SQL interface. You need to remove these calls, along with all the relevant object uses.
Database Hints
Prior to SAP HANA, few developers used database hints. For optimal performance, they used to give a "hint" to the database optimizer showing which index was to be used. Figure 3 is an example of this bad code.
Select * from ZHCM_APPRAISAL_RATINGS
into it_ratings ””” WRONG CODE for hana
for all entries in it_pernr
where pernr = it_pernr-pernr
%_HINTS ORACLE 'index(Z01)'.
Figure 3
Database hints example
Such statements are strictly not allowed in SAP HANA, and the hints must be removed. The above code must be rewritten as shown in Figure 4.
Select * from ZHCM_APPRAISAL_RATINGS
into it_ratings
for all entries in it_pernr
where pernr = it_pernr-pernr.
Figure 4
Database hints removed
Function Modules for Determining an Index
In addition to Native SQL and hints, there may be statements in SAP ERP HCM programs that check the existence of an index for a database table. These include situations in which information about the database index is read via function modules DB_EXISTS_INDEX and DD_INDEX_NAME. All such coding must be removed from the program.
Pooled and Cluster Tables
As mentioned earlier, all cluster and pool tables in the SAP HANA system are converted into simple transparent tables. As a result, you need to check each custom SAP ERP HCM program that addresses cluster and pool tables. The SORT rule applies for both transparent tables and any tables that were cluster or pooled pre-SAP HANA and have now been converted to transparent tables. For any code that addressed pool and cluster tables earlier in ABAP code, make sure you do not trust the implicit sorting. It is better to use an ORDER BY clause or a subsequent SORT statement. Also, keep in mind that using the DELETE statement for a pool table deletes a line from a single table in SAP HANA, rather than deleting entries from all the tables forming the pool as it did in the pre-SAP HANA system.
Don’t Rely on Implicit Sorting at the Database Level
Another important change to make in ABAP programs is that you must examine any code that assumes default sorting in the result set returned by a SELECT statement. As mentioned earlier, SAP HANA has a column-based storage architecture.
In pre-SAP HANA systems, you query the row-based database used to send back the resultant data in sorted order based on a primary key, assuming the Open SQL SELECT statement did not explicitly specify the sort order. In a column-based storage system, however, the sort order is not guaranteed. In the SAP HANA system, the SAP ERP HCM code is syntactically correct. However, because of this transition from row- to column-based architecture, it does not produce the expected output.
When a particular program was developed and tested in the non-SAP HANA platform, the SAP ERP HCM developer may have noticed either in the output or within the debugger that the result set appeared in sorted order. The developer therefore may have not used the SORT statement after the SELECT statement (or the ORDER BY clause in the SELECT statement), as he or she might have thought it was not necessary.
One of the ways to do this sorting is to use the SORT BY variant of the SORT statement. This should come right after the SELECT statement in question. Consider the example in Figure 5.
SELECT * FROM PA0001 INTO ITAB.
The code reads:
SELECT * FROM PA0001 INTO ITAB.
SORT ITAB BY PERNR.
Figure 5
Using a SORT statement
A better form for SAP HANA is the use of the ORDER BY clause in the SELECT statement. It can be used along with ORDER BY PRIMARY KEY or by any other field(s) of choice.
Sorting Problem Example
There may be a situation in which a program is syntactically correct, but does not yield the desired results in the SAP HANA environment. As mentioned earlier, you need to make sure that default field sorting is not assumed for the result set of a SELECT statement. Suppose you have a SELECT statement that reads the entire set of rows from Wage Type text table T512T as shown in Figure 6.
Note
It is particularly important to check the sorting of data fetched from
the database before a READ statement with a BINARY SEARCH variant.
data it_512t type STANDARD TABLE OF t512t.
select * from t512t into table it_512t.
read table it_512t into data(wa) with key
sprsl = '1'
molga = '01'
lgart = '$36J' binary search.
Figure 6
Using SELECT with READ..BINARY SEARCH
Suppose you want to read all the Wage Type table texts in an internal table and then use a READ statement with BINARY SEARCH. For the READ statement to succeed, the table must be in sorted form. In an SAP HANA system, there is no guarantee that the result table from the prior SELECT statement arrives in a sorted form. When you execute the code in
Figure 6 into an SAP HANA system, the first few entries appear as shown in
Figure 7.
Figure 7
Contents of table T512T
As you see, the column SPRSL starts with 6, even though there are entries pertaining to SPRSL = 1 in the table as well. This means that the table is not in sorted form. Hence, the READ statement with the BINARY SEARCH fails and produces a value SY-SUBRC equal to 4, despite the fact that the internal table contains values corresponding to the specified keys.
There are two ways to correct this:
Add the ORDER BY PRIMARY KEY clause to the SELECT statement. After this, the internal table is in sorted order and the READ statement succeeds (i.e., the value of the return code is 0). The correct code for this is shown in
Figure 8.
select * from t512t into table it_512t order by primary key.
read table it_512t into data(wa) with key
sprsl = '1'
molga = '01'
lgart = '$36J' binary search.
Figure 8
Using ORDER BY in a SELECT statement
Another way of handling this is to add a SORT statement between the SELECT and the READ statements and to specify the criteria of the sort fields (i.e., they should correspond to the primary key of the table). This is shown in Figure 9.
select * from t512t into table it_512t.
sort it_512t by sprsl molga lgart.
read table it_512t into data(wa) with key
sprsl = '1'
molga = '01'
lgart = '$36J' binary search.
Figure 9
Using a SORT statement after SELECT
Golden Rules Revisited
Now it is time to revisit the golden rules of ABAP database access prior to SAP HANA.
One such rule followed for SAP ERP HCM programs was to keep the load of the database as low as possible. There is a slight change in this rule for SAP HANA. It is important that no unnecessary work is done by the database. However, you should try to push down all intensive calculations to the fast in-memory database rather than leaving the application server to perform them.
Suppose you need to sort data fetched from the database. In addition, you must make sure that the output is correct as shown in Figure 10.
select * from t512t into table it_512t order by primary key.
This is better on SAP HANA than the following:
select * from t512t into table it_512t.
sort it_512t by sprsl molga lgart.
Figure 10
A SORT statement
The main point is that the programs running in SAP HANA should be made to push the computation and processing to the database server level (the faster layer). This process is different from the pre-SAP HANA style, in which most calculations were done at the level of the application server.
In SAP HANA, there is no need for secondary indexes. Prior to SAP HANA, SELECT queries could be made to run faster when you created secondary indexes. This is no longer required after the move to the SAP HANA environment.
The following rules must still be followed in SAP HANA:
- Don’t fetch unnecessary data (i.e., more than what is actually required). For example, you should not use * when fetching columns from the database table. Instead, specify the actual columns required.
- Make sure the number of database invocations is minimized (i.e., use a SELECT in a loop rather than SELECT.. FOR ALL ENTRIES). In addition, you should use JOINS instead of nested SELECT.. ENDSELECTs.
In the second part of this series, I explain how to use a few standard tools to find the programs that may be problematic within an SAP HANA environment.
Rehan Zaidi
Rehan Zaidi is a consultant for several international SAP clients (both on-site and remotely) on a wide range of SAP technical and functional requirements, and also provides writing and documentation services for their SAP- and ABAP-related products. He started working with SAP in 1999 and writing about his experiences in 2001. Rehan has written several articles for both SAP Professional Journal and HR Expert, and also has a number of popular SAP- and ABAP-related books to his credit.
You may contact the author at
erpdomain@gmail.com.
If you have comments about this article or publication, or would like to submit an article idea, please contact the
editor.
