topic Re: Error Trying reconnect to target data when insert data to source. (SQL server source to AS400 Target) in Qlik Replicate

Error Trying reconnect to target data when insert data to source. (SQL server source to AS400 Target)

DuongNguyen — Fri, 13 Oct 2023 11:20:27 GMT

I insert 4 rows to source table. but it not insert to target table and they in Comming Changes. The target icon show "Trying reconnect to target data"

hmm. how to fix it?

Re: Error Trying reconnect to target data when insert data to source. (SQL server source to AS400 Target)

Dana_Baldwin — Fri, 13 Oct 2023 16:01:50 GMT

Hi @DuongNguyen

Please check the task log for any helpful information. If nothing found, increase logging for Target_Apply to verbose long enough to see that message on the screen, then lower logging back to info and check the log file again.

FYI, ODBC endpoints should be implemented with assistance from our Professional Services team.

Using ODBC to connect to a target #Using ODBC to connect to a target | Qlik Replicate Help

Thanks,

Dana

Re: Error Trying reconnect to target data when insert data to source. (SQL server source to AS400 Target)

DuongNguyen — Mon, 16 Oct 2023 13:44:23 GMT

@Dana_Baldwin This is the content in the log file, can you help me?

00013224: 2023-10-14T11:17:46 [TARGET_APPLY ]I: Going to connect to ODBC connection string: DRIVER=IBM i Access ODBC Driver;SYSTEM=xxx; (odbc_endpoint_imp.c:3434)

00013224: 2023-10-14T11:17:46 [TARGET_APPLY ]I: ODBC driver version: '07.01.027' (ar_odbc_conn.c:633)

00013224: 2023-10-14T11:17:46 [TARGET_APPLY ]I: Target endpoint 'ODBC' is using provider syntax 'GenericWithoutSchema' (provider_syntax_manager.c:957)

00013224: 2023-10-14T11:17:46 [TARGET_APPLY ]I: Transaction size is 10000. Array size is 1000. (odbc_endpoint_imp.c:1117)

00013224: 2023-10-14T11:17:46 [TARGET_APPLY ]I: Restore bulk state. Last bulk last record id - '0', last applied record id - '0', target confirmed record id - '0', sorter confirmed record id - '0' (endpointshell.c:2020)

00013224: 2023-10-14T11:17:46 [TARGET_APPLY ]I: Set Bulk Timeout = 30000 milliseconds (bulk_apply.c:564)

00013224: 2023-10-14T11:17:46 [TARGET_APPLY ]I: Set Bulk Timeout Min = 1000 milliseconds (bulk_apply.c:565)

00013224: 2023-10-14T11:17:46 [TARGET_APPLY ]I: Working in bulk apply mode (endpointshell.c:2028)

00013224: 2023-10-14T11:17:46 [SOURCE_CAPTURE ]I: Source endpoint 'SQL Server' is using provider syntax 'SQLServer' (provider_syntax_manager.c:951)

00013224: 2023-10-14T11:17:46 [SOURCE_CAPTURE ]I: ODBC driver version: '18.02.0002' (ar_odbc_conn.c:633)

00013224: 2023-10-14T11:17:46 [SOURCE_CAPTURE ]I: SQL server version: Microsoft SQL Server 2019 (RTM-CU20) (KB5024276) - 15.0.4312.2 (X64)

00013224: 2023-10-14T11:17:46 [SOURCE_CAPTURE ]I: Sampled server version: 2019 (sqlserver_endpoint_imp.c:2393)

00013224: 2023-10-14T11:17:46 [SOURCE_CAPTURE ]I: NLS configuration sampled: Associated code page=950 (sqlserver_endpoint_imp.c:2483)

00013224: 2023-10-14T11:17:46 [TARGET_APPLY ]I: Target components st_0_AS400 - Target was reattached after 5 seconds. (subtask.c:1417)

00013224: 2023-10-14T11:17:48 [TARGET_APPLY ]I: Error in bulk, bulk state: bulk confirmed record id - '0', bulk last record id - '0', confirmed record id - '0', sorter confirmed record id - '0' (bulk_apply.c:2476)

00013224: 2023-10-14T11:17:48 [TARGET_APPLY ]I: Failed (retcode -1) to execute statement: 'INSERT INTO "attrep_changesF18074BDE511EF37"("seq","col1","col2","col3","col4","col5","col6","col7","col8","col9","col10","col11","col12","col13","col14","col15","col16","col17","col18","col19","col20","col21","col22","col23","col24","col25","col26","col27","col28","col29","col30","col31","col32","col33","col34","col35","col36","col37","col38","col39","col40","col41","col42","col43","col44","col45","col46") values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)' [1022502] (ar_odbc_stmt.c:2822)

00013224: 2023-10-14T11:17:48 [TARGET_APPLY ]I: RetCode: SQL_ERROR SqlState: HY000 NativeError: -7008 Message: [IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL7008 - "attr0001" in DBMOTO not valid for operation. [1022502] (ar_odbc_stmt.c:2828)

00013224: 2023-10-14T11:17:48 [TARGET_APPLY ]I: execute statement failed [1022502] (odbc_bulk.c:1019)

00013224: 2023-10-14T11:17:48 [TARGET_APPLY ]I: Failed to copy data to net changes table [1022502] (bulk_apply.c:6370)

00013224: 2023-10-14T11:17:48 [TARGET_APPLY ]I: Error executing command [1022502] (streamcomponent.c:1983)

00013224: 2023-10-14T11:17:48 [TASK_MANAGER ]I: Stream component failed at subtask 0, component st_0_AS400 - Target [1022502] (subtask.c:1433)

00013224: 2023-10-14T11:17:48 [TARGET_APPLY ]I: Target component st_0_AS400 - Target was detached because of recoverable error. Will try to reattach (subtask.c:1548)

Re: Error Trying reconnect to target data when insert data to source. (SQL server source to AS400 Target)

SushilKumar — Mon, 16 Oct 2023 08:37:25 GMT

Hello team,

Seems the error you encountered may require through analysis of TASK settings and Associated logs. hence request you to raise Support case and upload diagnostics package for further assistance.

Please refer to Replicate https://community.qlik.com/t5/Knowledge/How-to-collect-Diagnostics-Package-from-Qlik-Replicate/ta-p/1815056

Regards,

Sushil Kumar

Re: Error Trying reconnect to target data when insert data to source. (SQL server source to AS400 Target)

DesmondWOO — Mon, 16 Oct 2023 11:44:16 GMT

Hi @DuongNguyen ,

After reviewing several articles from the Internet, it seems that this error SQL7008 is associated with the journal.
The table attrep_changesF18074BDE511EF37 is a temporary table. I think it is unnecessary to enable journaling for this table.

I would recommend reaching out to your DBA or IBM engineer to investigate the SQL7008 error further. If it turns out that it is our coding issue, please create a support as suggested by Sushil.

Regards,
Desmond

Re: Error Trying reconnect to target data when insert data to source. (SQL server source to AS400 Target)

DuongNguyen — Mon, 16 Oct 2023 13:48:50 GMT

hi @DesmondWOO. how to disable journaling for this table in task?