You can translate the question and the replies:

Error loading cache timeout after 900seconds

Dear all when executing the cache preload VQL command -------------------------------------------------------------- SELECT * FROM "DV_ITSR.T_COSTING_RESULT_J_BV_EAGLE_MAINDB_PROD_SRZMGE_1" CONTEXT( 'cache_preload' = 'true' , 'cache_invalidate' = 'all_rows' , 'cache_wait_for_load' = 'true' , 'cache_return_query_results' = 'false') -------------------------------------------------------------- I receive an error as below. Which I do not understand how to overcome. I did not find any settings that I can influence the timeout value. the cache is made available in JDBC connection to an DB2 z/OS instance. Ran it several times and always ended with the same error with timeout of 900 seconds. Smaller caches on smaller views work fine. I found the ODBC timeout connection info: https://community.denodo.com/kb/view/document/Configuration%20of%20ODBC%20timeout%20in%20a%20DSN?category=Northbound+Connections but I do not have ODBC connections. DB2 Based DV_ITSR.T_COSTING_RESULT_J_BV_EAGLE_MAINDB_PROD_SRZMGE_1 One is an Excel File HTTP (sharepoint based) not listed in the error message. In both I did not find any options similar to the described note for ODBC to set timeout. I would appreciate any support. Finished with error: Error executing query. Total time 900.0 seconds. DV_ITSR.T_COSTING_RESULT_J_BV_EAGLE_MAINDB_PROD_SRZMGE_1 [PROJECTION] [QUERY_TIMEOUT] DV_ITSR.T_COSTING_RESULT_J_BV_EAGLE_MAINDB_PROD_SRZMGE_1 [SELECTION] [PROCESSING] DV_ITSR.T_COSTING_RESULT_J_BV_EAGLE_MAINDB_PROD_SRZMGE_1 [JOIN] [QUERY_TIMEOUT] DV_ITSR.T_COSTING_RESULT_J_BV_EAGLE_MAINDB_PROD_SRZMGE_1 [JOIN] [QUERY_TIMEOUT] DV_ITSR.T_COSTING_RESULT_ADJ [VIRTUAL] [PROCESSING] DV_ITSR.T_COSTING_RESULT_ADJ [JDBC WRAPPER] [PROCESSING] DV_ITSR.T_COSTING_RESULT_ADJ#0 [JDBC ROUTE] [PROCESSING] Also after adjusting the JDBC Connection based on what I understood form JDBC connection properties, I am running in the same error after 900'000msec tehese have been updated: TESTWHILEIDLE = true TIMEBETWEENEVICTION = 9000000 NUMTESTPEREVICTION = 20 MINEVICTABLETIME = 1800000 see full connection properties below: CREATE OR REPLACE DATASOURCE JDBC ds_db2o_prod_xxx FOLDER = '/data sources/db2' DRIVERCLASSNAME = 'com.ibm.db2.jcc.DB2Driver' DATABASEURI = 'jdbc:db2://xxxx' USERNAME = 'xxx' USERPASSWORD = 'xxxxx' ENCRYPTED CLASSPATH = '' DATABASENAME = 'db2' DATABASEVERSION = '10zOS' FETCHSIZE = 1000 BATCHINSERTSIZE = 200 VALIDATIONQUERY = 'select 1 from SYSIBM.SYSDUMMY1' INITIALSIZE = 4 MAXIDLE = -1 MINIDLE = 0 MAXACTIVE = 20 EXHAUSTEDACTION = 1 TESTONBORROW = true TESTONRETURN = false TESTWHILEIDLE = true TIMEBETWEENEVICTION = 9000000 NUMTESTPEREVICTION = 20 MINEVICTABLETIME = 1800000 POOLPREPAREDSTATEMENTS = false MAXSLEEPINGPS = 4 INITIALCAPACITYPS = 8; Your help is highly appreciated. regards, Erik
user
30-12-2017 16:14:05 -0500

2 Answers

Hi, Assuming you are running this cache load command from the VDP Administration Tool, you are likely running in to the timeout within the client tool. You can adjust the Query Timeout in the [Admin Tool Preferences](https://community.denodo.com/docs/html/browse/6.0/vdp/administration/installation_and_execution/launching_the_virtual_dataport_administration_tool/tool_preferences#connection-parameters) or use the *QUERYTIMEOUT* parameter in the [CONTEXT clause](https://community.denodo.com/docs/html/browse/6.0/vdp/vql/queries_select_statement/context_clause/context_clause) of your cache load query. See this prior question [How to solve QUERY_TIMEOUT](https://community.denodo.com/answers/question/details?questionId=90670000000Cd90AAC&title=How+to+solve+QUERY_TIMEOUT) for more information. If you are not expecting the query itself to take so long, you can look in to the [Execution Trace](https://community.denodo.com/docs/html/browse/6.0/vdp/administration/creating_views/querying_views/execution_trace_of_a_statement) to determine where the bottleneck may be, and look to that system for options to improve the performance on the base query. I hope this helps!
Denodo Team
02-01-2018 12:38:07 -0500
Thanks a lot this helped.
user
02-01-2018 12:47:17 -0500
You must sign in to add an answer. If you do not have an account, you can register here