topic Re: operation timeout in Qlik Replicate

operation timeout

suvbin — Mon, 11 Dec 2023 04:05:15 GMT

Hi Team,

Facing the below error, but the task is running fine, Does the below error states that the insert statement taking time for the execution?

Source: IBM DB2

target : Bigquery

00011020: 2023-12-10T22:25:25 [TARGET_APPLY ]T: Failed to apply INSERT (1) for table 2000018 [1022502] (bulk_apply.c:2996)
00011020: 2023-12-10T22:25:25 [TARGET_APPLY ]I: Error in bulk, bulk state: bulk confirmed record id - '771', bulk last record id - '249722313', confirmed record id - '249713821', sorter confirmed record id - '249713821' (bulk_apply.c:2475)
00011020: 2023-12-10T22:25:25 [TARGET_APPLY ]T: Error executing command [1022502] (streamcomponent.c:1981)
00011020: 2023-12-10T22:25:25 [TARGET_APPLY ]I: Failed (retcode -1) to execute statement: 'INSERT INTO `thjek`.`FCN__ct` (`header__change_seq`,`header__change_oper`,`header__change_mask`,`header__stream_position`,`header__operation`,`header__transaction_id`,`CNMRKT`,`CNCUST`,`CNCTNO`,
00011020: 2023-12-10T22:25:25 [TARGET_APPLY ]I: RetCode: SQL_ERROR SqlState: HY000 NativeError: 115 Message: [Simba][BigQuery] (115) Operation timeout. PollJob [1022502] (ar_odbc_stmt.c:2816)
00011020: 2023-12-10T22:25:25 [TARGET_APPLY ]I: Failed to execute bulk insert statement.

Re: operation timeout

kng — Mon, 11 Dec 2023 04:01:00 GMT

Hello Team,
Good day

Thanks for reaching Qlik community!!

00011020: 2023-12-10T22:25:25 [TARGET_APPLY ]I: RetCode: SQL_ERROR SqlState: HY000 NativeError: 115 Message: [Simba][BigQuery] (115) Operation timeout. PollJob [1022502] (ar_odbc_stmt.c:2816) 00011020: 2023-12-10T22:25:25 [TARGET_APPLY ]I: Failed to execute bulk insert statement.

Indicates some type of performance problem on the network or the target. Please work with the appropriate teams to determine the root cause.

you can try with On your target endpoint to increase the timeout values for the connection., go to Advanced -> Internal Parameters

Add "additionalConnectionProperties" (you will need to type this in for the parameter to appear, it is case sensitive)

In the value field, enter: Timeout= 3600

The following two parameters you can enter an exclamation point in the Search parameter field and scroll down to add them:

excecuteTimeout
set value to:
3600

loadTimeout
set value to:
3600

These values are estimates and may need to be increased further. The values are in seconds.

You will need to stop and resume any tasks using this endpoint before they go into effect.

Also we recommend to open a ticket to help you better if the above didn't help and posting logs on public forum is not recommended for security reasons.

Hope this helps!!

Regards,
shivananda

Re: operation timeout

john_wang — Mon, 11 Dec 2023 04:04:50 GMT

Hello @suvbin ,

Thanks for reaching out to Qlik Community!

It's hard to tell from error message only however you may have a try with:

1. Add 2 internal parameters in target endpoint and set them to 7200 (or lower values)

executeTimeout = 7200 (default is 60 seconds)

loadTimeout = 7200 (default is 1200 seconds)

2. Stop the task and then RESUME

Good luck with resolving the issue,

Regards,

John.

Re: operation timeout

suvbin — Mon, 11 Dec 2023 04:09:49 GMT

Thank you @john_wang , @kng for the quick response. Will check on these parameters.

@kng removed the logs, as suggested. thank you for the heads up.

Re: operation timeout

suvbin — Mon, 11 Dec 2023 04:11:54 GMT

Already timeouts are applied at the target.

executeTimeout = 2400

loadTimeout = 2400

Shall i increase them?

Re: operation timeout

john_wang — Mon, 11 Dec 2023 04:19:51 GMT

Hi @suvbin ,

Personally I do not see setting then higher value will help (your timeout setting are 40 minutes already). Please set target_apply to Verbose before your resume the task. and open a support ticket , upload the task Diag Packages after the problem recreate, support team will help you further.

BTW, please check with Google BigQuery side to see if any helpful clue there.

Regards,

John.

Re: operation timeout

Heinvandenheuvel — Mon, 11 Dec 2023 15:25:46 GMT

@suvbin wrote: "Failed (retcode -1) to execute statement: 'INSERT INTO `thjek`.`FCN__ct` (`header__change_seq`,... Operation timeout. PollJob ...

Did it ever work (in prod)? Does it never work now, or sometimes? If it worked before, what changed? Change Volume ? This is a 'change table' which should be entirely Replicate created, has only transformation as doen for base table, has no foreign keys or constraints other then a unique PK.

Did the PK end up not being unique? Is there a second task writing to the same table?

@john_wang wrote "Personally I do not see setting then higher value will help (your timeout setting are 40 minutes already)."

I agree. That's no longer a performance issue, but a structural issue like the table being locked due to a maintenance job or whatever.

@suvbin --- What is this 'PollJob' supposed to do? I suspect it has issues and Replicate is not the cause but just a messenger bringing bad tidings from the target.

Google is your friend - for example (no solution, but there are more) - https://community.fabric.microsoft.com/t5/Desktop/DetailsODBC-ERROR-HY000-Simba-BigQuery-115-Operation-timeout/td-p/1782726

Re: operation timeout

john_wang — Tue, 12 Dec 2023 04:15:22 GMT

Hello @suvbin ,

I totally agree with @Heinvandenheuvel . Besides that,

@suvbin --- What is this 'PollJob' supposed to do? I suspect it has issues and Replicate is not the cause but just a messenger bringing bad tidings from the target.

It's better if you can check and Analysing BigQuery Logs.

Hope this helps.

Regards,

John.

Re: operation timeout

kishetty03 — Sun, 24 Nov 2024 05:49:42 GMT

@suvbin
Were you able to get a solution to this??

I am getting this error and its exactly the same.
could you please revert if there was a solution for this