[SOURCE_UNLOAD ]E: RetCode: SQL_ERROR SqlState: S1000 NativeError: 129 Message: [SAP AG][LIBODBCHDB DLL][HDBODBC]General error;129 transaction rolled back by an internal error: Search result size limit exceeded: 2254282483 [1022502] (ar_odbc_stmt.c:2789)
Cause
There is a limitation in SAP HANA , on the number of the records. In very specific scenarios limits for (intermediate) result sets exist, e.g. 2147483648 (2 billion).
If the source table total rows number exceeds the 2 billion limitation , Replicate reports above error (in this sample, the user table total row number is 2254282483).
Resolution
Because the table is too big to do Full Load by a single retrieve query from source SAP Hana, we need to split the single source table read return set to multiple return sets. There are several options:
1. In a single task, Parallel Load helps if both source and target endpoints are in supported list; however if target is not in parallel load support list, eg for Kafka target, we need to break down the table into multiple tasks by using filter, and "passthru filter" should be used (passthru filter take actions on Full Load only, CDC will not be affected). see below options 2 and 3.
2. If part of the data (aka history data rows) will not being updated during the load Let's say the table will be divided into 2 tasks. Each task loads part of records.
(1). Create following tasks TASK1: Full Load Only with passthru filter on PK which are history data rows, or a history rows partition TASK2: Full Load + CDC with passthru filter on PK which maybe being updated
(2). Start TASK1 first, after it's done, and then start TASK2
3. Sometimes it's impossible to know if a row will be updated or not, or any row maybe being updated, then option 2 does not work. We may use this method. Let's say the table will be divided into 3 tasks. Each task loads 1M records.
(1). Create following tasks TASK1: Full Load Only with passthru filter on PK (1...1,000,000) TASK2: Full Load Only with passthru filter on PK (1,000,001 ... 2,000,000) TASK3: Full Load + CDC with passthru filter on PK (2,000,001 ... 3,000,000)
(2). In the TASK3, change the Full Load settings as below: Enable the "Before cached changes have been applied" option box
(3). Start TASK3 first, and then start other tasks.
(4). Once Full Load processes are completed for all tasks, resume TASK3 for CDC.
