4. SAP HANA database hints
In the preceding parts of this blog series, I wrote about the usage of a secondary SAP HANA database to execute analytical queries and that the client decides which HANA database (primary or secondary) to use in such an scenario.
Now I will go into more details: When selecting from SAP HANA, you can add some database hints. In this section, I will explain how the client’s intent to accept slightly delayed data is converted into SAP HANA database hints. This helps you if you want to build own apps using the Active/Active (read enabled) configuration.
4.1 General information about SAP HANA database hints
When selecting data from SAP HANA, the caller can add SAP HANA database hints to the SQL select statement. Among others, there are hints that control that the caller accepts data that is somewhat outdated (but not older than a given time duration).
You find an overview on the SAP HANA database hints here.
4.2 Hint classes
Some of the SAP HANA database hints are configurable. We call them hint classes.
You find the configuration in the indexserver.ini file of SAP HANA.
The hint classes allow you to set preferences where SAP HANA should read data from if the caller indicates that it accepts outdated data. The preferences are expressed by the options listed under “enabled_features” and by their order in the list:
|sr||System Replication||This is the relevant replication in the Active/Active (read enabled) configuration|
|atr||Asynchronous Table Replication||
Another option to replicate data to another HANA database.
You can find more information on ATR in the Asynchronous Table Replication section of the SAP HANA Administration Guide.
SAP HANA allows to cache the results of selects against views data for a given retention time.
You can find more information on result caches in the SAP HANA Troubleshooting and Performance Analysis Guide.
You find more information on hint classes here.
The default configuration of the hint classes shipped by SAP is as follows:
I recommend not to change the default setting.
4.3 Converting the client’s intent to accept slightly delayed data into SAP HANA database hints in SAP S/4 HANA
As pointed out in the preceding blog post, different clients have different ways to express the acceptance of slightly outdated data. Mainly, we have
- UI5 clients that precisely express via http header fields what data age they accept,
- and other clients which may not use http/ODATA but express by their nature that they are analytical.
- In SAP S/4 HANA the general translation rules are:
|Client||http header Cache-Control||<Seconds>||Resulting HANA database hint||Effect|
|SAP Fiori app via http/ODATA||Max-age = <seconds>||1-59||RESULT_LAG (‘hana_short’)||Data read from a secondary HANA database|
|SAP Fiori app via http/ODATA||Max-age = <seconds>||>59||RESULT_LAG (‘hana_long’, <value in seconds>)||Data read from a secondary HANA database if last synchronization with primary HANA database is not older than <seconds>. If there is in addition a result cache, this will be used if its last refresh is not older than <seconds>|
|SAP Fiori app via http/ODATA||Max-age = <seconds>||0||RESULT_LAG (‘hana_long’, <value in seconds>)||Only fresh data read. If there is in addition a result cache, this will get refreshed|
|Analytical client using e.g. INA protocol||n/a||n/a||RESULT_LAG (‘hana_long’)||Data read from a secondary DB. If there is in addition a result cache, this will be used.|
However, if you as a consultant, partner or customer implement your own service, you may
- use the CDS view header annotation ‘@OData.publish: true’ to generate your service: you will benefit from the translation to SAP HANA DB hints automatically at runtime
- Find more information on service generation via CDS annotation ‘@OData.publish: true’ here.
- implement your service on your own, or redefine parts of a generated service: You can add the SAP HANA DB hint that fits to your need to your code redefinition.
- Find more information on usage of SAP HANA database hints in ABAP open SQL here.
4.4 SAP HANA database hints in SAP S/4HANA at runtime
With what I explained above, I now can add details to the sequence diagrams which were shown in the preceding blog posts.
For a client that is analytical by its nature, the sequence diagram now looks as follows:
And for a SAP Fiori UI client for which an accepted max-age of 59 seconds is configured the sequence diagram now looks as follows:
Finally, there is no reason to be afraid of using a SAP HANA database hint even if you did not enable the Active/Active (read enabled) configuration. SAP HANA will simply ignore the hint at runtime.