Pull-based incremental caching can be set up using the TDV caching mechanism with two scripts. With pull-based incremental caching the user or client application must make a request for their copy of the cache to be synchronized with the centralized cache copy. Because you define the scripts used to create and refresh the cache, you can make the scripts as complicated or as simple as your situation requires. Your scripts can be defined using SQL or using custom Java procedures. The procedure must have logic to identify what rows have changed.
This topic covers the generalized steps for creating the scripts necessary for the pull-bases method of defining incremental caching and provides the following sample scripts:
To set up pull-based incremental caching
1. Locate or create a reliable and unique identifier within your data source that you can use to determine how data has changed since the last cache refresh. This unique identifier could be a system change number, a log sequence number (LSN), or a TIMESTAMP. Typically the cachekey column can be used to determine what data has been updated.
2. Create a new SQL script or Java procedure using the instructions in
Java Procedures or
Adding a Custom Java Procedure.
To use an existing SQL script or procedure locate the script or procedure within the Studio resource tree.
3. Make sure that the scripts or procedures for the cache have one VARCHAR output parameter.
4. Evaluate the following TDV API calls for use are in Studio under <localhost>/lib/util:
— GetEnvironment—Can be used to return one or more of the system properties to your Pull-Based incremental caching SQL script
— GetProperty—Can be used to return one or more of the system properties to your Pull-Based incremental caching custom Java procedure. Specifically, you can use it to acquire the server ID.
The system caching properties that can be interacted with include:
— System.CACHED_RESOURCE_PATH
— System.CACHED_RESOURCE_TYPE
— System.CACHED_RESOURCE_PARAM_KEY
— System.CACHE_DATASOURCE_PATH.
— System.CACHED_RESOURCE_CACHE_KEY
— System.CACHED_RESOURCE_BUCKET_PATH.
— System.CACHED_RESOURCE_REFRESH_OUTCOME
— System.CACHED_RESOURCE_ERROR_MESSAGE
— System.CACHED_RESOURCE_INCREMENTAL_MAINTENANCE_LEVEL
5. Review other functions under the lib directory to determine if they can help you achieve the action that you want for your pull-based incremental caching. Other functions of possible interest might include:
— CreateResourceCacheKey() is called to generate a new cachekey value.
— LoadResourceCacheStatus() is called to inform the server of the in-progress refresh.
— LoadResourceCacheStatus() is called to inform the server of the newly active data.
— TestDataSourceConnection is used to test to see if a data source's connection is operational.
— ClearResourceCache(path, type) is used to clear the cache on a resource.
— UpdateResourceCacheKeyStatus(path, type, cacheKey, status, startTime, message) is used to update the cache key for the specified resource.
— GetResourceCacheStatusProcedure is used with CreateResourceCacheKey and UpdateResourceCacheKeyStatus to support external cache loading. Returns Cache status information for a given cache key.
6. Consider using some of the following logic to control caching logic:
— The status table is queried to find the currently active cachekey. The cachekey is in the row with the server ID, resource path, and status column with value 'A'. The INSERT, UPDATE, and DELETE operations can be performed on the data table using the cachekey as a filter to avoid updating any other keys.
— Perform INSERTs and other operations to create new rows in the storage table as appropriate, making sure all such rows have the new cachekey value. This should be performed on an independent transaction so it can be committed when done.
— Perform INSERTs to the status table with the server ID, resource path, cachekey, the status set to 'I', and the starttime set to the current time to indicate an in-progress refresh. This should be performed on an independent transaction so it can be committed immediately.
— UPDATE all rows in the status table with the server ID and resource path that have status 'A' to have status 'C'. This marks the previous active cache data for clearing. Then UPDATE the row into the status table with the serverID, resource path, and cachekey to set the status set to 'A' and finishtime to the current time. This will indicate that this cache key is the new active one. This should be performed on an independent transaction so it can be committed immediately.
7. Save the script or procedure.
8. In Studio, open a view or procedure that has had caching enabled for which you want to define Pull-Based incremental caching.
Note: If your cache objects were created prior to the current version of TDV, you might need to recreate them.
9. Select the Caching tab.
10. Under Advanced, choose Incremental Refresh Mode to enable incremental caching that can be refreshed on demand.
Incremental refresh caching only updates the client version of the caches when the client requests the update.
11. Specify values for the following fields:
— Initialize the cache using—Specify a procedure or script that exists in the Studio resource tree and has one output parameter. This script will be used to create the initial cache.
— Refresh the cache using—Specify a procedure or script that exists in the Studio resource tree and has one output parameter. The output parameter should be a of VARCHAR data type.
12. Save the cache settings. After cache settings are saved, each cached resource appears with a lightning-bolt icon in the Studio resource tree to show that the resource is cached.
13. If you have data type incompatibilities between your view and your data storage type, see “Cache Data Type Mapping” in the TDV Reference Guide.
Pull-Based Incremental Cache Initialization Sample Script
The following sample script is used to create the cache table. It has one IncrementalMaintenanceLevel output parameter of type VARCHAR.
PROCEDURE InitalCache(OUT IncrementalMaintenanceLevel VARCHAR)
BEGIN
DECLARE cacheKey BIGINT;
DECLARE maxI BIGINT;
/* 1. Retrieve cache key from request environment */
CALL /lib/util/GetEnvironment('System.CACHED_RESOURCE_CACHE_KEY', cacheKey);
CALL /lib/debug/Log('cachedResourceCacheKey = ' || cacheKey);
/* 2. Determine initial snapshot level */
SELECT {option no_data_cache} MAX(i) INTO maxI FROM /shared/INCREMENTAL_CACHING/INCR_CACHE_TEST;
SET maxI = COALESCE(maxI, 0);
/* 3. Load cache target table */
INSERT INTO
/shared/INCREMENTAL_CACHING/incr_cache_test_target
SELECT {option disable_data_cache}
cacheKey, S.*
FROM
/shared/INCREMENTAL_CACHING/INCR_CACHE_TEST S
WHERE
i <= maxI;
/* 4. Return incremental maintenance level */
SET IncrementalMaintenanceLevel = CAST(maxI AS VARCHAR);
END
Pull-Based Incremental Cache Refreshing Sample Script
The following sample script is used to request that a local copy of the cache be refreshed with the latest data from the cache table. It has one IncrementalMaintenanceLevel output parameter of type VARCHAR.
PROCEDURE DeltaLoader(OUT IncrementalMaintenanceLevel VARCHAR)
BEGIN
DECLARE cacheKey BIGINT;
DECLARE maxI BIGINT;
/* 1. Retrieve cache key from request environment */
CALL /lib/util/GetEnvironment('System.CACHED_RESOURCE_CACHE_KEY', cacheKey);
CALL /lib/debug/Log('cachedResourceCacheKey = ' || cacheKey);
/* 2. Retrieve incremental maintenance level from request environment */
CALL /lib/util/GetEnvironment('System.CACHED_RESOURCE_INCREMENTAL_MAINTENANCE_LEVEL', IncrementalMaintenanceLevel);
CALL /lib/debug/Log('cachedResourceIncrementalCacheMaintenanceLevel = ' || IncrementalMaintenanceLevel);
/* 3. Determine next level */
SELECT {option no_data_cache} MAX(i) INTO maxI FROM /shared/INCREMENTAL_CACHING/QA/"db-lab-9"/QAN/INCR_CACHE_TEST;
SET maxI = COALESCE(maxI, 0);
/* 4. Refresh cache target table */
INSERT INTO
/shared/INCREMENTAL_CACHING/QA/"db-lab-9"/QAN/incr_cache_test_target
SELECT {option disable_data_cache}
cacheKey, S.*
FROM
/shared/INCREMENTAL_CACHING/QA/"db-lab-9"/QAN/INCR_CACHE_TEST S
WHERE
i > CAST(IncrementalMaintenanceLevel AS BIGINT) AND i <= maxI;
/* 5. Update incremental maintenance level */
SET IncrementalMaintenanceLevel = CAST(maxI AS VARCHAR);
CALL /lib/debug/Log('IncrementalMaintenanceLevel for successful run of DeltaLoader script= ' || IncrementalMaintenanceLevel);
EXCEPTION
ELSE
--Log the exception
CALL /lib/debug/log('Exception raised in the delta loader script');
CALL /lib/debug/log('Exception is : ' || CURRENT_EXCEPTION.NAME || ': ' ||
CURRENT_EXCEPTION.MESSAGE);
--Don't advance the incremental maintenance level on a failure
CALL /lib/util/GetEnvironment('System.CACHED_RESOURCE_INCREMENTAL_MAINTENANCE_LEVEL', IncrementalMaintenanceLevel);
CALL /lib/debug/Log('IncrementalMaintenanceLevel after exception in DeltaLoader script= ' || IncrementalMaintenanceLevel);
END