You can translate the question and the replies:

Denodo Increamental Cache Load

I am using Denodo V7 and I have enabled full Cache on a view in Denodo. Now I am trying to set up an incremental Cache load for this view through the Denodo Scheduler. But I keep getting an error which goes like this 'Error executing query. Total time 80.302 seconds. QUERY [PROJECTION] [ERROR] INCREMENTAL_CACHE_LOAD [STORED_PROCEDURE] [ERROR] COM.DENODO.CONNECT.INCREMENTALCACHELOAD.STOREDPROCEDURE.INCREMENTALCACHELOADSTOREPROCEDURE [STORED PROCEDURE] [ERROR] Received exception with message 'com.denodo.vdb.engine.storedprocedure.StoredProcedureException: ERROR getting rows to update in cache.'' I have rereun this several times and it still fails. If i change the load process to Non incremental then the job runs fine. I need to do an initial load once and then set up this incremental load for running every day. Any ideas how this can be fixed ?
Incremental Cache Denodo
user
23-06-2020 05:03:27 -0400
code

3 Answers

Hi, I have found when using the Full cache option with Incremental mode that I have to first do an explicit load of the cache. When running the incremental to update based on a date field (typically a time stamp of last update), I will set the condition ```0 'c_last_modified < @LASTCACHEREFRESH' ``` More information on using the incremental cache stored procedure can be found in the [Denodo Incremental Cache Load Stored Procedure User Manual](https://community.denodo.com/docs/html/document/denodoconnects/7.0/Denodo%20Incremental%20Cache%20Load%20Stored%20Procedure%20-%20User%20Manual). Also, since your job uses a stored procedure, the job is likely failing due to reaching the default timeout for stored procedures in the VDP server. To increase this value, navigate to Administration > Server Configuration > Stored procedures. Increase the value for "Query timeout" and retry the job. Hope this helps!
Denodo Team
25-06-2020 16:04:24 -0400
code
HI there, I am still getting the same error message Error executing query. Total time 80.104 seconds. QUERY [PROJECTION] [ERROR] INCREMENTAL_CACHE_LOAD [STORED_PROCEDURE] [ERROR] COM.DENODO.CONNECT.INCREMENTALCACHELOAD.STOREDPROCEDURE.INCREMENTALCACHELOADSTOREPROCEDURE [STORED PROCEDURE] [ERROR] Received exception with message 'com.denodo.vdb.engine.storedprocedure.StoredProcedureException: ERROR getting rows to update in cache.' These are the steps I have taken * Installed the Denodo Incremental cache Load 7 JAR file. * Create the Incremental Cache Load Stored Procedure * Set up a job in Denodo Scheduler with Increemental load properties, I used Custom conditions (snapshot_date between '2020-05-01' and '2020-05-02' ) What else should I do to get it to work ? Thanks Gaurav
user
26-10-2020 12:28:33 -0400
Hi, I have found when running stored procedures via the Scheduler, and having them time out after 80 seconds, I will double check the query timeout setting for stored procedures using the VDP Administration Tool. The default timeout is 8000 milliseconds (80 seconds), which matches up with your reported observations. This setting is explained further in the [Configuring Runtime Parameters for Stored Procedures](https://community.denodo.com/docs/html/browse/7.0/vdp/administration/server_administration_-_configuring_the_server/configuring_runtime_parameters_for_stored_procedures/configuring_runtime_parameters_for_stored_procedures#configuring-runtime-parameters-for-stored-procedures) section of the Virtual DataPort Administration Guide. I would increase this setting. You can also verify the value for the property **storedProcedures.defaultQueryTimeout** stored in the <DENODO_HOME>/conf/vpd/VDBConfiguration.properties file. I've also found reviewing the section [Optimizing Cache Load Processes](https://community.denodo.com/kb/view/document/Best%20Practices%20to%20Maximize%20Performance%20III:%20Caching?category=Best+Practices#h.530wvvaovz3n) in the Knowledge Base Article **Best Practices to Maximize Performance III: Caching** to be helpful when configuring an incremental cache. Hope this helps!
Denodo Team
06-11-2020 09:07:54 -0500
code
You must sign in to add an answer. If you do not have an account, you can register here