general question about timeout values in additionalconnectionproperties, loadtimeout and executetimeout

[suhailmemon]

We use qlikreplicate and in there we have a bigquery endpoint as target where we have the following properties setup as internal parameters:

additionalConnectionProperties ->  Timeout=12000

excecuteTimeout -> 6000

loadTimeout -> 12000

are all the above timeout values in seconds? can someone please explain what each of these mean? how do these different timeout values work with each other? which timeout value do we hit into first in which scenario?

thanks in advance. 

[SushilKumar · Accepted solution]

Hello @suhailmemon 

Thanks for reaching out to Qlik via Qlik Community Support page. Timeout mainly put as internal parameters to overcome certain situations. Qlik Replicate is Software it captures and Generate Changes in terms of SQL statements. 

In your case The error occurs when Qlik Replicate is working on a query to the source or target and the query takes longer than anticipated, leading to the timeout. 

Below configured in Seconds. 

additionalConnectionProperties -> Timeout=12000 -- Related to network timeout. 
excecuteTimeout -> 6000                   -- Wait for Querry timeout Submit by QR to database or Target endpoint
loadTimeout -> 12000                         --- Wait for Querry timeout Submit by QR to database or Target endpoint for Full load of tables

If our response has been helpful, please consider clicking "Accept as Solution". This will assist other users in easily finding the answer. if you require further assistance, please raise a Support case. 

Regards,

Sushil Kumar

[suhailmemon]

thank you for your response @SushilKumar . just another follow up question. 

before i ask the question here is the scenario:

in our qlikreplicate tasks that is replicating data from oracle and applying changes on target: bigquery (fyi...it connects to bigquery using simba bigquery odbc driver) :

1) at let's say 8:00 AM, the log file shows that qlikreplicate tries to issue a truncate table sql statement on top of bigquery. it's trying to truncate an intermediate table with name: attrep_changesXXXXXX. 

2) now this task gets "stuck". the log file shows that it's accepting all these incoming changes but nothing is getting applied on bigquery (i.e. no [TARGET_APPLY] statements in the log file). the lag on this task is steadily increasing by the minute

3) the lag on this task (from the QEM UI) is now around "3:19:03" and exactly 3 hours 20 mins later at 11:20 AM (3 hours 20 mins = 12000 seconds), the log file shows this error:

[TARGET_APPLY ]T: Failed (retcode -1) to execute statement: 'TRUNCATE TABLE `rt_metadata`.`attrep_changesXXXXXXXX`' [1022502] (ar_odbc_stmt.c:4996)

[TARGET_APPLY ]T: RetCode: SQL_ERROR SqlState: HY000 NativeError: 100 Message: [Simba][BigQuery] (100) Error interacting with REST API: [1022502] (ar_odbc_stmt.c:5003)

[TARGET_APPLY ]T: Connection failure error codes are: 100,8711,115,120 (provider_syntax_manager.c:2927)

[TARGET_APPLY ]T: Network error encountered (ar_odbc_util.c:1271)

[TARGET_APPLY ]T: Failed to truncate net changes table [1022506] (odbc_bulk.c:871)

4) immediately after the above, the log file shows that it tries to restablish the ODBC connection to bigquery and it succeeds in making the connection and it is able to apply the accumulated changes on bigquery successfully and now the lag is down to 0

------------------------------------------------------------------------------------------------------------------------

question:

which of these values(in the bigquery target endpoint) should i reduce to make sure that qlikreplicate doesn't wait 12000 seconds before trying to establish a connection to bigquery again?

additionalConnectionProperties -> Timeout=12000 -- Related to network timeout. 
excecuteTimeout -> 6000                   -- Wait for Querry timeout Submit by QR to database or Target endpoint
loadTimeout -> 12000                         --- Wait for Querry timeout Submit by QR to database or Target endpoint for Full load of tables

[suhailmemon]

can anyone please answer the above question?

[DesmondWOO · Accepted solution]

Hi @suhailmemon ,

The 'additionalConnectionProperties' parameter allows you to add configuration properties available in the Simba ODBC connector.

According to the Simba document, 

Timeout
The length of time, in seconds, for which the connector retries a failed API call before
timing out. The specified value must be an integer. A value of 0 indicates no timeout.

Default value is 300. Is there a specific reason why you set the value to 12,000? Please lower the value and let us know the results.

Regards,
Desmond

[suhailmemon]

i'm not sure why the value was set so high. sounds good we will lower the value and let you know how the test goes. thanks.