- SAP Managed Tags:
Core Data Services (CDS) is often mentioned in the context of SAP HANA only. But did you know that SAP provides ABAP Core Data Services that you can run on any database used in an SAP environment? Let’s first recap what CDS is before we dive right in what optimizations there are for Db2 for Linux, UNIX, and Windows (LUW).
What Is SAP Core Data Services (CDS)?
It’s an infrastructure that lets you push down ABAP application workload into the database and expose database content to modern UI technologies like SAP Fiori. In this blog, I focus on various aspects of SAP ABAP Core Data Services that you can use with Db2 for Linux, UNIX, and Windows.
Optimizations in Db2 LUW
Over time, Db2 LUW has been optimized to process CDS workload very efficiently.
When I started my very first tests with CDS and Db2 10.5, it turned out that CDS uses the database in a way we hadn’t seen before with ABAP. Very often CDS uses a lot of stacked views, and CDS introduced a new [0..1] association. This association is represented in the database as join called LEFT OUTER TO ONE JOIN if the database supports the TO ONE syntax.
Here is an example of a simple looking CDS SQL SELECT query. But the underlying object tree that consists of various joins, views, and database tables is very large. As you can probably imagine, this query will generate a lot of workload in the database.
LEFT OUTER TO ONE JOIN (the "TO ONE" is new to the SQL world) means that for one record of the left table only 1 or 0 matching records exist in the right table.
As of Db2 LUW 11.1 Mod Pack 1 Fix Pack 1, IBM introduced the LEFT OUTER TO ONE JOIN for SAP customers. The Db2 optimizer is now capable to remove the right table and the join if no data is accessed from the right table in the SQL result. This leads to a much quicker execution of the SQL query.
Due to the intensive usage of views in CDS, another change in Db2 called “Break CSETEMP” has been introduced. CSETEMP objects may lead to a serialization of the execution plan and therefore may prevent parallel execution. Parallel execution is very important for CDS, and “Break CSETEMP” allows the Db2 optimizer to optimize for more parallel execution.
We tested 13 queries, which are exemplary for analytical SAP Fiori queries. The red bars show the performance with the new optimizations:
The SQL view text of an SAP CDS view is generated and afterwards created in the database while the application developer activates the SAP CDS view in the SAP dictionary. This SQL view text generation sometimes leads to funny constructs such as “…..RTRIM( RTRIM ( RTRIM( RTRIM ( RTRIM( …..” or this “….RPAD( '', 1, ' ' ) || '/' ) ) || RPAD( '', 1, ' ' )….”. RTRIM and RPAD are SQL functions that are processed during SQL statement execution. This means that if statements are executed several times, RTIM and RPAD are also executed with every SQL statement execution. Therefore, it’s much better to optimize such statements during SQL prepare because an SQL prepare is only done once. The enhancements that came with Db2 11.1. Mod Pack 1 Fix Pack 2 enable the Db2 optimizer to simplify such constructs. Multiple RTRIMs are collapsed into one RTRIM, and RPADs with constant input parameters are replaced with just a constant. So, “….RPAD( '', 1, ' ' ) || '/' ) ) || RPAD( '', 1, ' ' )….” folds into “ / “ and does not cause workload during SQL statement execution.
For a complete list of all optimizations in Db2 for SAP CDS, see Db2 LUW Optimized for SAP Core Data Services and SAP Note 2586972.
To make sure that you benefit from all optimizations, we highly recommend that you use at least Db2 11.1 Mod Pack 4 Fix Pack 5 or Db2 11.5
Tips for Optimizing SAP CDS Queries
Despite all optimizations in Db2, an SQL query might perform slower than expected. To optimize such an SQL query, you can do the following:
- Check if INTRA_PARALLEL is turned on in the database manager configuration of your Db2 instance. See SAP Note 2047006 for more information.
- Check the location of the table queue (TQ) operator in the access plan. All operations below the TQ can be executed in parallel. Therefore, it is key that the TQ is as high as possible in the access plan.
- Check for CSETEMP objects in the access plan because these objects may serialize the SQL statement execution (as mentioned above). Running with an appropriate Db2 level with this featured turned on helps.
- Also check for jump scans and table scans in the access plan. Both can be avoided by creating additional indexes on the database table.
In case of a large access plan, it is helpful to identify the area that seems to be the bottleneck, that is the area where a very large number of rows is returned. In many cases, this area is represented by an SAP CDS view. You can then run further tests and improvements only against this view.
The number of rows returned in the access plan is an estimation of the Db2 optimizer. Sometimes it is helpful to compare the number of estimated rows with the real number of retuned rows after the SQL statement execution. To do so, you can use the db2caem tool. If you detect a big difference between the estimated and real number of rows, the SQL optimizer has generated the access plan based on incorrect assumptions and this might lead to an inefficient access plan. In this situation, you can check if the statistics are up to date. If they aren’t, you can run RUNSTATS.
For more information, see Db2 LUW Optimized for SAP Core Data Services and Core Data Services SAP Business Suite – Best Practice Guide..
Creating an SAP Fiori App with Db2 LUW
One use case for SAP CDS are SAP Fiori apps. SAP Fiori apps are not limited to SAP HANA only, so we decided to create our own SAP Fiori app in combination with an SAP system running on Db2 LUW.
This is how you do it:
First create an SAP CDS view in ABAP Development Tools (ADT). You need to add the annotation “OData.publish=true” to the SAP CDS view in order to be able to call the SAP CDS view from an SAP Fiori app. Here’s an example of such a CDS view definition with the annotation:
Next, you install the SAP WEB IDE Personal Edition, which is one of several ways to create an SAP Fiori app. After the installation finished, create connection files that enable the SAP WEB IDE Personal Edition to connect to the SAP system that hosts the SAP CDS view. The SAP WEB IDE Personal Edition can be accessed via a browser. It offers the possibility to create SAP Fiori apps based on templates. Various templates are available such as “List Report Application” or “SAP Fiori Master-Detail Application”.
The List Report Application template allows you to easily display the content of an OData object, in this case the result of the SAP CDS view. The template assistant guides you through the template. After completion of all steps, the SAP Fiori app will be created and stored in the SAP WEB IDE Personal Edition.
For testing purposes, you can launch the SAP Fiori app directly out of the SAP WEB IDE Personal Edition without deploying the SAP Fiori app into your SAP system. This is a very convenient way of testing. After launching the SAP Fiori app, a new browser window opens, and you will be prompted to enter your logon credentials to connect to your SAP system. Afterwards, the List Report app shows the contents of the SAP CDS view.
After successful testing of the SAP Fiori app, you can deploy the app into you SAP system. The SAP Fiori app can then be started out of the SAP system without the need of SAP WEB IDE Personal Edition. For more detailed information, see the step-by-step documentation in Db2 LUW Optimized for SAP Core Data Services.
Developing an SAP Fiori app is very interesting. Give it a try and create an SAP Fiori master-detail application with Db2 LUW. You can find the necessary SAP CDS views for the SAP Flight Model at the end of the above mentioned documentation.
References:
2 Comments
| User | Count |
|---|---|
| 16 | |
| 13 | |
| 12 | |
| 11 | |
| 11 | |
| 10 | |
| 9 | |
| 9 | |
| 9 | |
| 6 |
