topic Re: Handling LOB in Qlik Replicate

Handling LOB

Vaibhav_19 — Thu, 17 Oct 2024 05:54:12 GMT

Here’s a rephrased version of your message with improved grammar:

Hello community,

We have a table with a LOB (21914KB) column and approximately 750,000 rows of data. When I try to load the table, the task runs for 8-9 hours, then encounters a fatal error, recovers on its own, and restarts the loading process from the beginning. Is there a way to handle this?

A few points related to the task:

The task configuration is set to Full Load + CDC.
We've modified the task settings to allow unlimited LOB.
Endpoints:
- Source: Microsoft SQL
- Target: PostgreSQL
We've also set the ExecutionTimeout internal parameter on the target endpoint to 9 hours.

Re: Handling LOB

SachinB — Thu, 17 Oct 2024 06:15:50 GMT

Hello @Vaibhav_19 ,

Thanks for reaching out to Qlik community forum.

Could you please elaborate or provide logs for fatal error. Usually for big tables (CLOB/BLOB) take time in order to execute/fetch the query from source and target endpoints, normally these tasks will fail with "Query Time Out" error, and we would request them to increase timeout parameter from default setting to 10x.

Below community link for the same.

Query-timeout-expired

Regards,

Sachin B

Re: Handling LOB

Vaibhav_19 — Thu, 17 Oct 2024 09:52:21 GMT

Hello @SachinB ,

Thanks for Response

We already the set ExecutionTimeout parameter to 8hrs.

Attaching the Logs for error details.

00006124: 2024-10-17T08:36:14 [SOURCE_CAPTURE ]I: Sampled server version: 2016 (sqlserver_endpoint_imp.c:2393)
00006124: 2024-10-17T08:36:14 [SOURCE_CAPTURE ]I: NLS configuration sampled: Associated code page=1252 (sqlserver_endpoint_imp.c:2483)
00006588: 2024-10-17T08:36:14 [SORTER ]I: Reload for table Id 1 is requested (sorter_transaction.c:2468)
00006588: 2024-10-17T08:36:14 [SORTER ]I: Start collecting changes for table id = 1 (sorter_transaction.c:2343)
00012580: 2024-10-17T08:36:14 [TASK_MANAGER ]I: Start loading table 'dbo'.'tablename' (Id = 1) by subtask 1. Start load timestamp 000624A378125340 (replicationtask_util.c:755)
00013444: 2024-10-17T08:36:16 [SOURCE_UNLOAD ]I: Calculated batch used for UNLOAD size is 1 rows per fetch. (sqlserver_endpoint_unload.c:222)
00006124: 2024-10-17T08:36:20 [TARGET_LOAD ]I: Table 'dbo'.'Main_tablename' contains LOB columns, change working mode to default mode (odbc_endpoint_imp.c:8309)
00006124: 2024-10-17T08:36:20 [TARGET_LOAD ]I: Table 'dbo'.'Main_tablename' has Non-Optimized Full LOB Support (odbc_endpoint_imp.c:8322)
00009924: 2024-10-17T08:37:02 [SOURCE_CAPTURE ]E: SqlStat: 42000 NativeError:9005 [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Invalid parameter passed to OpenRowset(DBLog, ...). (PcbMsg: 105) [1020417] (sqlserver_log_processor.c:4338)
00009924: 2024-10-17T08:37:02 [SOURCE_CAPTURE ]E: Encountered an unexpeceted error. [1020417] (sqlserver_endpoint_capture.c:1043)
00012580: 2024-10-17T08:37:02 [TASK_MANAGER ]I: Task error notification received from subtask 0, thread 0, status 1020417 (replicationtask.c:3520)
00009924: 2024-10-17T08:37:02 [SOURCE_CAPTURE ]E: Error executing source loop [1020417] (streamcomponent.c:1942)
00009924: 2024-10-17T08:37:02 [TASK_MANAGER ]E: Stream component failed at subtask 0, component st_0_UAT [1020417] (subtask.c:1433)
00009924: 2024-10-17T08:37:02 [SOURCE_CAPTURE ]E: Stream component 'st_0_UAT' terminated [1020417] (subtask.c:1602)
00012580: 2024-10-17T08:37:02 [TASK_MANAGER ]W: Task 'SP_CDC' encountered a recoverable error (repository.c:6049)
00006588: 2024-10-17T08:37:02 [SORTER ]I: Final saved task state. Stream position 00ccda62:00000040:0001, Source id 177163, next Target id 155049, confirmed Target id 155045, last source timestamp 1729134421600000 (sorter.c:781)
00012580: 2024-10-17T08:37:02 [TASK_MANAGER ]I: Subtask #0 ended (replicationtask_util.c:591)
00012580: 2024-10-17T08:37:02 [TASK_MANAGER ]I: Subtask #1 ended (replicationtask_util.c:591)
00012580: 2024-10-17T08:37:11 [SERVER ]I: Stop server request received internally (server.c:2410)
00012580: 2024-10-17T08:37:11 [TASK_MANAGER ]I: Task management thread terminated (replicationtask.c:4446)
00005380: 2024-10-17T08:37:11 [SERVER ]I: Client session (ID 407799875) closed (dispatcher.c:195)
00005380: 2024-10-17T08:37:11 [UTILITIES ]I: The last state is saved to file 'E:\Qlik Replicate\data\tasks\SP_CDC/StateManager/ars_saved_state_000002.sts' at Thu, 17 Oct 2024 03:07:02 GMT (1729134422255912) (statemanager.c:642)
00010504: 2024-10-17T08:37:11 [SERVER ]I: The process stopped (server.c:2537)
00010504: 2024-10-17T08:37:11 [AT_GLOBAL ]I: Closing log file at Thu Oct 17 08:37:11 2024 (at_logger.c:2553)

Regards,

Vaibhav B.

Re: Handling LOB

Dana_Baldwin — Thu, 17 Oct 2024 18:53:01 GMT

Hi @Vaibhav_19

00009924: 2024-10-17T08:37:02 [SOURCE_CAPTURE ]E: SqlStat: 42000 NativeError:9005 [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Invalid parameter passed to OpenRowset(DBLog, ...). (PcbMsg: 105) [1020417] (sqlserver_log_processor.c:4338)

This error means that the LSN the task needs to continue without losing data is no longer available in the online TLOG or the backup TLOGs. Your options are to reload the task or restore the missing backup TLOGs so the task can continue. You should consider increasing the retention period of the backup TLOGs to help prevent this in the future.

You might also consider working with our Professional Services team (fee based) to ensure the task is set up for the best possible performance for the amount of volume at the source. Reach out to your Account Manager if this is of interest.

Thanks,

Dana

Re: Handling LOB

DesmondWOO — Fri, 18 Oct 2024 02:57:35 GMT

Hi @Vaibhav_19 ,

In addition to Dana's comment, please check if the task was stopped for a long period. To prevent truncation from the TLOG, Replicate starts transactions in the source database by default. If the task was stopped, truncation may occur, causing Replicate to be unable to find the corresponding LSN. Please find the details from the following link:

Microsoft SQL Server - Setting advanced connection properties

Regards,
Desmond

Re: Handling LOB

SachinB — Sat, 19 Oct 2024 01:54:38 GMT

Hello @Vaibhav ,

When working with an MS-SQL source endpoint, the COMMIT event usually shows up with LCX_NULL context. Here it comes with a context which is not recognized yet and therefore Replicate produces an error. An easy and immediate option to solve the error is to ignore the validity of the CONTEXT value in the case of COMMIT, assuming that the MS-SQL storage engine knows its job better than Replicate.

When Replicate reads from the online log it will use the fn_dblog function, the first parameter passed to the query is the last LSN that Replicate processed. If there is no access to the backups and the log has been truncated you can get this error.

If the cause of the problem is reason 1 mentioned below, to eliminate the error, you should edit the MS-SQL source endpoint setting under Replicate GUI:
1. manage endpoints connections --> [your MS-SQL source endpoint definition] --> advanced tab --> internal parameters
2. Add the internal parameter: ignoreTxnCtxValidityCheck and set it to True
If the cause of the problem is reason 2 mentioned below, you should make sure that the TLOG backups are available to Replicate

Regards,

Sachin B

Re: Handling LOB

Vaibhav_19 — Tue, 22 Oct 2024 09:38:54 GMT

Hello Community,

We have resolved the issue by.

1. We increase the ExecutionTimeout parameter value.
2. From logs we find the LOB size and change the task setting set it to Limted LOB Size to 30000KB.

By setting the parameters Task Full-Load completes successfully and CDC are Capturing.

Re: Handling LOB

SushilKumar — Tue, 22 Oct 2024 10:25:12 GMT

hello @Vaibhav_19

30000KB of lob size is quite large which is not required to be that much long. you can keep it to 32,16kb or 8kb as per business requirement. ExecutionTimeout Should be referred when you found timeout either on Source and Target endpoint.

Regards,

Sushil Kumar