topic A table unable to full load due to SQL Error SQLState 42601 in Qlik Replicate

A table unable to full load due to SQL Error SQLState 42601

desmondchew — Mon, 15 May 2023 16:42:15 GMT

We are running full load, CDC and store capture. Source endpoint is a SQL server while target endpoint is Snowflake.

We are hitting errorRetCode: SQL_ERROR SqlState: 42601 NativeError: 1810 Message: SQL compilation error: error line 1 at position 175

Invalid character length: 104,857,600.RetCode: SQL_ERROR SqlState: 42601 NativeError: 1810 Message: SQL compilation error: error line 1 at position 463
Invalid character length: 104,857,600. Must be between 1 and 16,777,216.; Execute create table statement failed, statement CREATE TABLE

How can we resolve this?

Thanks
Desmond

Re: A table unable to full load due to SQL Error SQLState 42601

Dana_Baldwin — Mon, 15 May 2023 17:24:54 GMT

Hi @desmondchew

This is a snowflake limitation as stated here:
https://docs.snowflake.com/en/sql-reference/data-types-text.html

I suspect there are LOB columns involved; please change the task setting 'Limit LOB size to (KB)' to 16384 (or lower based on needs) and run the task again.

Thanks,

Dana

Re: A table unable to full load due to SQL Error SQLState 42601

desmondchew — Thu, 18 May 2023 02:02:52 GMT

Hi Dana,

I have limit LOB size to (KB) 16300, however the task refuse to load. According to the error log it seems to be finding a scn in redo log. I have setup this task fresh creating a new task and did a full load. Why is it looking for a scn?

Stream component 'st_0_odsprod_array' terminated
Stream component failed at subtask 0, component st_0_odsprod_array
Error executing command
Failed to set stream position on context '00000000.8b0043b4.00000001.005e.01.0000:820097.2924261.16'
The provided scn 000000008B0043B4 is not found in Redo log, thread 1

Thank you.

Desmond

Re: A table unable to full load due to SQL Error SQLState 42601

SachinB — Thu, 18 May 2023 04:44:52 GMT

Hello @desmondchew ,

Could you please create support case for the same and provide appropriate logs, We will investigate the issue and will provide analysis for the same?

Regards,

Sachin B

Re: A table unable to full load due to SQL Error SQLState 42601

Heinvandenheuvel — Thu, 18 May 2023 13:55:30 GMT

@desmondchew Missing SCN would be something completely different from exceeding LOB size. Is the max lob size issue fixed? Be sure to carefully read the Snowflake limitation link above paying attention to the BYTES vs CHARS issue. The limit is 16MB BYTES, which may be 4MB CHARS for unicode-4-bytes-per-char.

For the missing SCN... how far back are you trying to resume from? There is a limit within Oracle of about 5 days for SCN usage history https://www.red-gate.com/simple-talk/databases/oracle-databases/oracle-system-change-number-an-introduction which may play a rolde. Can you resume by timestamp?

Hein