How to use RESULT_LAG( ) HINT to redirect the data selection to Data snapshots / Secondary node HANA DB (active/active)HSR scenario

CONCEPT of using HINT RESULT_LAG ( )

We can use configurable hint classes as a standard way of controlling at run time how the data is selected, either from the snapshot or from the database. Several features in SAP HANA use data snapshots or replicated data to improve performance; this includes:

• Result Cache
• Asynchronous Table Replication
• System Replication (Active/Active Read Enabled).

NOTE: Snapshots carry the risk of holding stale data and administrators need to be able to specify a maximum time value up to which the replicated data may lag behind the live data. The features listed above all use the RESULT_LAG() hint with a set of standard configurable hint classes as a common way of controlling how the data is selected at run time (also referred to as hint-based routing). Hint classes give the administrator a tool to balance a system in terms of query response time, query load, resource utilization and freshness of data. Moreover, they de-couple SAP HANA features from application development and administrator choices.

Using the Hint RESULT_LAG(), The RESULT_LAG() hint takes two parameter values to determine:

• Which is the preferred data source (snapshot or live data)
• How much time the retrieved data may lag behind the live data.

SYNTAX:

WITH HINT (RESULT_LAG(“<short_class_name>”, [seconds]))

A.Class name: The following pre-defined hint classes are available. Only the last two elements of the name are used in SQL statements, for example ‘hana_cache’, ‘hana_short’ and so on:

• hint_result_lag_hana_cache – for Result Cache, the default lag time value is the cache retention time
• hint_result_lag_hana_atr – for Asynchronous Table Replication, the default lag time value can be defined as a configuration parameter
• hint_result_lag_hana_sr – for System Replication (Active/Active), the default lag time value can be defined  as a configuration parameter
• hint_result_lag_hana_short
• hint_result_lag_hana_long

Note that customers and developers may also add classes of their own (and corresponding configuration  values) but these must not use the hana_* or sap_* naming convention.

1. Seconds: This parameter is an optional time value in seconds; this is the maximum time lag up to which the data is judged to be acceptable; if the snapshot or replica is older then it will not be used and the query is routed to the data source. If no seconds value is given the default value will be used. In some cases this feature can be disabled by setting a configuration option – see details below.

EXAMPLES:

ALTER VIEW v2 ADD CACHE RETENTION 100;

SELECT * FROM V2 WITH HINT( RESULT_LAG ( ‘hana_cache’, 30) );  => The output of this query is read from the result cache of the target view / table function if the cache duration is less than 30 seconds. Here as we see , though the cache retention of the view is 100seconds, this will not be considered by the hint clause if the cache is older than 30 seconds

SELECT * FROM T2 WITH HINT( RESULT_LAG (‘hana_atr’) , 10 ); => The output of this query is read from the replicated tables . If the current lag time for the data on the replica is less than the stated value for the [seconds] parameter (10 seconds in this example) then the query is executed on the replicated table, otherwise it would be executed on the source.

SELECT * FROM T2 WITH HINT( RESULT_LAG (‘hana_sr’, 60) ); => The output of this query is read from the secondary node despite the query getting executed on the primary node (active/active scenario with log mode logrepay_readonly )

Reference: help.sap.com