Docs
...
Cannot see updated information in the cache dashboard

Cannot see updated information in the cache dashboard of Data Virtualization

Last updated: Jun 18, 2025
Cannot see updated information in the cache dashboard of Data Virtualization
You cannot see newer queries in the Data Virtualization cache dashboard or the cache metadata is not being updated for existing caches.

Symptoms

You notice that the cache hit count or the last used timestamp are not getting updated for all the caches. You might also notice that newer queries are not appearing in the Responsiveness histogram or the Queries tab of the cache dashboard.

Causes

It is likely that event monitoring is turned off from the monitoring console or has stopped working. It is also likely that Db2 usage lists are no longer active. Event monitors are used to find recently run queries and related metadata, and db2 usage lists are used to find cache hit count.

Resolving the problem

  1. Go to Monitor > Settings > Event Monitoring and ensure that event monitor collection is turned on, and then take a note of the collection period (the expected default is 5 minutes).
  2. Run a valid query. Then after the collection period, run the following SQL to verify that the previous valid query was recorded and collected
    SELECT UNIQUE CAST(SQL_TEXT AS VARCHAR(1000)),SESSION_AUTH_ID, TIME_COMPLETED FROM IBMCONSOLE.EVENT_ACTIVITY A, IBMCONSOLE.SQL_DIM D WHERE A.SQL_HASH_ID = D.SQL_HASH_ID AND STMT_TYPE = 2 AND ROUTINE_ID = 0 AND SQLCODE >= 0 AND PARTIAL_RECORD = 0 AND ACTIVITY_TYPE = 'READ_DML' AND TIME_COMPLETED > (EXTRACT(EPOCH FROM (CURRENT TIMESTAMP - CURRENT TIMEZONE - 1 HOUR))) AND upper(SQL_TEXT) LIKE '%BLUADMIN%' ORDER BY TIME_COMPLETED DESC FETCH FIRST 1 ROWS ONLY
    This query returns the valid SQL that ran in the last 1 hour, and it returns all valid queries containing the text BLUADMIN. You can customize and replace the following values:
    • BLUADMIN with another keyword.
    • 1 HOUR with another time.
    • 1 ROWS ONLY with a different row count.
    Note: If the query returns zero results after the event monitoring collection period has passed, then the event collection is not working properly. Contact IBM Db2 Data Management Console Support for a resolution. Do not proceed to the next step.
  3. Run the following queries to verify that the event extraction is set up correctly:
    SELECT 1 FROM SYSIBM.SYSROUTINES WHERE ROUTINESCHEMA='CACHESYS' AND ROUTINENAME='EXTRACT_EVENT_METASTORE';
    SELECT 1 FROM SYSTOOLS.ADMIN_TASK_LIST WHERE NAME = 'EXTRACT_EVENT_METASTORE';
  4. Based on the results from the previous step, complete one of the following options. Ensure you replace <CACHING_POD_NAME> with the name of the caching pod.
    • If the queries from the previous step return zero or no results, then restart the caching process on the head pod:
      1. Access the head pod (c-db2u-dv-db2u-0) in the Data Virtualization instance.
      2. Switch to the db2inst1 user:
        su - db2inst1
      3. /opt/dv/current/run_time/caching/run-cache.sh stop
    • If the queries from the previous step return 1 in the result set, then it is likely that the Db2 usage lists are no longer active. Run this query:
      SELECT  USAGELISTNAME, STATE, LAST_STATE_CHANGE, USED_ENTRIES FROM TABLE (MON_GET_USAGE_LIST_STATUS ('CACHESYS', NULL, -1))

      If this query returns zero results even when there are several active caches, then the inactive usage lists is why the cache metadata (i.e. hit count) is not updating. The result should return the same number of usage lists as the number of active caches, and with the A (active) or P (pending) state.

      Restart the caching process on the head pod:
      1. Access the head pod (c-db2u-dv-db2u-0) in the Data Virtualization instance.
      2. Switch to the db2inst1 user:
        su - db2inst1
      3. /opt/dv/current/run_time/caching/run-cache.sh stop
      Note: Alternatively, to resolve this issue without restarting the caching pod, complete these steps:
      1. Obtain the usage list name that you will use to replace <USAGE_LIST_NAME>:
        SELECT USAGELISTNAME FROM SYSCAT.USAGELISTS WHERE USAGELISTSCHEMA LIKE 'CACHESYS' AND STATUS LIKE 'V'
      2. Run this query for each usage list:
        set usage list CACHESYS.<USAGE_LIST_NAME> state active