Qlik Replicate and SAP Hana General error;4 cannot allocate enough memory: OOM

[Steve_Nguyen]

Qlik Replicate task fails during run time:

RetCode: SQL_ERROR SqlState: S1000 NativeError: 4 Message: [SAP AG][LIBODBCHDB DLL][HDBODBC] General error;4 cannot allocate enough memory: OOM. please check traces for further information [1022502] (ar_odbc_stmt.c:2789)

Resolution

To address the memory error:

1. If not already stopped, manually stop the current task
2. Edit the source SAP HANA endpoint / from Advanced tab
3. Add the following three Internal Parameters:
   
   
   • fetchQueryHints 
     value:  NO_CS_EXPR_JOIN
   • hextobinLocation
   • value: excludingJoin
   • maxFetchIntervalSize (note that this internal parameter is depreciated in 2023.5 and 2023.11 as the parameter now appears as an endpoint setting)
     value: 10000
     
     
     

For more information on how to set Internal Parameters, see: Qlik Replicate: How to set Internal Parameters and what are they for? 

Environment

Qlik Replicate  
SAP Hana Endpoint

[iti-attunity-sup]

Could you explain the rationale behind choosing NO_CS_EXPR_JOIN and excludingJoin?

[Steve_Nguyen]

@iti-attunity-sup 

The following three internal tuning parameters can be helpful for the SAP HANA or SAP Application source endpoint (Trigger-based CDC) to improve memory usage and performance. Read on to learn more about these parameters and how to use them best. 

1. fetchQueryHints
   
   This internal parameter is used to specify SAP hints in the query. Please note that different hints may affect the HANA memory allocation.
   
   These hints guide the query engine on executing the data retrieval query more efficiently or in a particular manner, based on the context of the data being fetched.
   
   For the details on SAP hints, please refer to SAP: Hint Syntax in SQL Statements
   
   
2. hextobinLocation
   
   This parameter will not impact the memory allocation however it may improve performance. Its default value is joinOnly.
   
   The alternative value depends on the table and system optimization, so it's advisable to try different options to determine which one is the most efficient.
   
   These options provide users with the flexibility to control where to apply the HEXTOBIN function in the SELECT statement, allowing them to choose the most effective setting for their tables.
   
   The possible values are:
   
   
   • logRangeOnly
     
     This option will add HEXTOBIN to the VARBINARY primary key column only on the LOG_RANGE part of the SELECT statement
     
     
   • excludingJoin
     
     This option will add HEXTOBIN to the VARBINARY primary key column in the LOG_RANGE and CHANGES parts only in the SELECT statement
     
     
   • joinOnly
     
     This option will add HEXTOBIN to the VARBINARY primary key column in the JOIN part only in the SELECT statement (default, current implementation)
     
     
   • changesOnly
     
     This option will add HEXTOBIN to the VARBINARY primary key column in the CHANGES part only in the SELECT statement
     
     
   • allPlaces
     
     This option will add HEXTOBIN to the VARBINARY primary key column in all parts of the SELECT statement
     
     Example Scenario (using hextobinLocation with excludingJoin)
     
     Imagine you are replicating data in a CDC process where a join between two tables is required to track changes. Some records might be excluded based on the join conditions.
     
     The hextobinLocation = excludingJoin setting ensures that only the rows that pass the join filter (i.e., rows that are not excluded) are converted from hexadecimal to binary. This reduces the overhead of converting unneeded data.
     
     
3. maxFetchIntervalSize
   
   The parameter refers to the maximum size of the data interval that can be fetched during the CDC process.
   
   It specifies the maximum number of records that can be retrieved in each CDC operation.

   This internal parameter is depreciated in 2023.5 and 2023.11 as the parameter now appears as an endpoint setting.

   By configuring the maxFetchIntervalSize, you can optimize the performance of the CDC process.
   
   Larger values might fetch more data in each cycle but could also increase memory usage or processing time, whereas smaller values may reduce the impact on resources but increase the number of fetch cycles.
   
   This parameter is important for balancing performance and resource utilization in high-throughput environments where large volumes of changes need to be captured.