You can translate the question and the replies:

Cache Load Question

Source Database: DB2 I have a view say top_level_view, which takes a long time to query and aggregate from the DB2 database. We decided to cache this view with a where clause for 15K keyids. To begin with we tried to run the view without caching to see whether it completes. ``` SELECT * FROM top_level_view where key_id in ( select key_id from distinct_view -- This returns around 15K unique key_id ) ``` The above query runs beyond the allowed 15 minutes limit in Denodo and fails. Also, there are some restrictions in DB2 which terminates the process when it reaches specific resource limitation. So, as the next step we extracted those 15K key_id into spreasheet and manually tried and we found around 4k key_ids can be passed to run successfully without breaking both Denodo and DB2 restrictions. Cache DB: Oracle Cache Type: Full Cache, Never Expires ``` SELECT * FROM top_level_view where key_id in ( -- 4K key_ids passed manually ) CONTEXT ('i18n'='us_est', 'cache_preload'='true', 'cache_wait_for_load'='true') ``` Based on the baseline and before we create a scheduler job, we wanted to test it with VQL and it started failing again as it went beyond both Denodo and DB2 limits. So, we again did some trial and error and found 500 - 700 key_ids would work successfully for the cache process. So, here are my questions 1. Why the view runs successfuly without cache for 4k records and fails when we try to cache it? We understood some invalidation updates in cache is causing this, but not sure whether that is the reason. 2. If we need to load 500-700 keys in a batch, then what are the options we have available. This job needs to run periodiocally to keep the cache upto date. There is no option for us to consider incremental caching. Please suggest how to approach this and the best practices. Update: Used 'cache_return_query_results' = 'false' and don't see any noticable difference.
07-02-2020 10:25:25 -0500

1 Answer

Hi, The cache process could have failed due to many reasons. I would perform the following checks: * I would check the query’s execution trace to find query start time and the response time and this will give an idea to know which is taking more time. * Increase the server querytimeout value and try to execute the query to see if it retrieves all the records without failing. Besides that, if you are using Denodo Express, then there is a limitation for number of rows returned by a query. Refer to [Limitations of Virtual DataPort with Denodo Express]( Regarding your second question, I would use Batch Insert Size in the write setting of the cache configuration to load data in batches. To update the cache periodically, I would create a scheduler job. For more information refer the article [Batch inserts in the cache and Scheduler]( and [Configuring the Cache]( The parameter cache_return_query_results' = 'false' speeds up the process of loading the cache.You can have a look at the document [Recommended Parameters for Queries that Load the Cache]( For more information, you can refer to the knowledge base articles [Availability of full cache]( and also at [Best Practices to Maximize Performance III: Caching]( Hope this helps!
Denodo Team
10-02-2020 07:49:05 -0500
You must sign in to add an answer. If you do not have an account, you can register here