How to Troubleshoot Cache Refresh

Caching is often needed in TDV implementations, often with scheduled cache refreshes. These refresh activities can fail or hang for a number of reasons. In tightly controlled environments such as user acceptance testing or production, failures can be difficult to diagnose.

This topic assumes a typical production logging level, and also assumes that it is impractical to change the debug setting if it requires a TDV restart to take effect.

When executing a cache refresh, TDV performs several steps:

• Computes the Result Set—What calculations does TDV perform to generate the result set? Is a federated join algorithm used?

• Writes to the Cache Target—How does TDV write the result set to the cache database? Does it use SQL INSERT or some advanced mechanism?

• Completes the Refresh Process—Once the cache table has been fully updated, TDV cuts over to the new data. Has transactional integrity been maintained?

If you suspect a cache refresh is failing, try to locate the problem among these steps. The two main log files that contain cache-related errors are cs_server.log and cs_server_task.log.

To see information about the most recent cache refreshes, you can do the following:

• Click the Studio’s Manager tab on the left, and select Cached Resources from the list. This window lists the names of all recent refreshes and when they were launched.

• On the same page, select a cache refresh from the Details list to see more information for that refresh.

• Click the Studio’s Modeler tab on the left, navigate to Composite Data Services > Databases > System in the resource tree, and locate the SYS_CACHES system table.

For a list of the fields in the SYS_CACHES table, refer to the TDV System Tables chapter of the TDV Reference Manual.

• In the SYS_CACHES table, INITIAL_TIME indicates when the refresh was kicked off, so you can tell whether it was triggered as expected. The table also tracks NUM_SUCCESS and NUM_FAIL.

• If your TDV implementation has enhanced logging capability, such as the open source KPI Module (https://github.com/TIBCOSoftware/ ASAssets_KPI), you can log the cache refresh history. This provides a record of each cache’s refresh processing time.

TDV issues one or more SQL statements to fetch data from data sources. Under normal logging settings, these fetches are not logged. To see them, open the view’s execution plan and look for FETCH nodes. If one of the data sources does not seem to be responding normally, you can On copy a FETCH node’s SQL and execute it in a client native to the database (such as Oracle SQL Developer).

Note: This technique is not reliable for clustered data sources, because system changes may have been made since the problem occurred (such as a bad node being taken out of service), meaning that you cannot reliably reconstruct what TDV encountered.

On the rare occasions when a data source connection issue involves the cache_status table, the cache refresh can hang. For a remedy, read the Managing Cache Status Table Probe Row Conflicts section of the TDV Caching chapter of the TDV User Guide.

After the source data has been retrieved, TDV computes the requested result set using SQL statements, procedural logic (even for cached views), and so on. A prolonged CPU spike can indicate computation problems, such as a runaway thread. Unfortunately, you cannot tie a CPU spike to a particular request or query. However, you can see a given request’s memory consumption. If a request’s memory consumption is high and stays high throughout the request’s life cycle, something is probably wrong.

• Click the Studio’s Manager tab on the left, and select Requests from the list. This window lists the names of all recent requests and when they were launched. You can use the start time and TRUE in the Cache column to help find the request associated with the cache refresh.

• You can double-click a request and examine its Full SQL to verify that you have found the correct one.

You will not be given the fully qualified path to the cached resources (the best identifier), but you can usually make a confident guess. After you have identified the request, you can see its Memory and Max Memory consumption.

When you troubleshoot a cache refresh, always check the writing step.

With the result set computed, TDV begins writing to the cache database. By design, TDV does not always wait for request completion. Instead, TDV may start streaming partial results to the cache database as soon as they are available.

You can determine whether the first batch of records has been inserted into the cache table. Use the cache_tracking and cache_status tables to determine the correct target table. (For multitable caching, pick the appropriate bucket.) When querying this table, set transaction isolation to READ UNCOMMITTED, either from TDV Studio or from a native database client.

• If the target table appears to be fully populated, but the cache refresh hangs, TDV may have been unable to cut over from the old cache data to the new cache data. See Completes the Refresh Process.

• Always check for errors related to table spaces that have reached their maximum—a common occurrence. When this happens, TDV hangs, because it is unable to commit a transaction.

• If multitable caching is configured, TDV drops indexes before inserts and recreates them afterward.

After the cache table is fully populated, TDV performs a cutover. It updates the cache_tracking and cache_status tables to start using the new cache data. If the old cache data is currently being used to service a request, it continues to do so. Only requests issued after the cutover use the new cache data. When TDV determines that the old cache data is no longer needed, it purges the old cache.