Caching to a Single-Table Database Target
For the resource being cached, storage depends on the result set output and on retrieval of the entire result set or retrieval of a filtered subset. Decide on a storage type for caching the execution result. For information on how TDV cached data types are mapped to data source data types, see “Cache Data Type Mapping” in the TDV Reference Guide.
If a resource uses a data source that was originally added to TDV Server using pass-through mode without saving the password, row-based security can affect cache refresh functionality. For details on Save Password and Pass-through Login, see
Adding a Data Source.
To set up caching to a single table
1. In Studio, open a view or procedure.
2. Select the Caching tab.
3. Click Create Cache.
4. Under Status, select the Enable check box. If you decide to leave it cleared, you can continue to define the cache, but it will not be active until you select the Enable check box
When a cache is disabled, all existing cache settings are ignored. The view or procedure is used as if caching did not exist. Toggling between the enabled and disabled state does not cause refreshing of the data or resetting of the expiration date for the data.
5. Under Storage, select Single Table. The cached data is stored in a specified data source. Type or browse to such a storage data source for the cache target.
6. Use Browse to locate and specify the data source where you want to create the cache table. After you select the data source, its full path is displayed in the Data Source field. For data integrity, each resource that is cached should have its own cache data tables.
7. Click Open Data Source. In the Caching section of the Configuration tab, create (execute the DDL), rename or browse to two tables, one for storing cache status data (Status Table) and the other for storing cache tracking data (Tracking Table).
8. Save the data source panels.
9. Navigate back to the view or procedure with the open Caching tab.
10. Click Browse next to the result field in the Table for Caching section. In the Select cache table for result dialog box, specify a location within the data source to create a table for caching. This location can be the root level of the data source, or a schema. Follow the instructions on the screen to get through the screens that might appear.
For example, when you select a data source, the right side of the screen might say Create Table. When you type a name and click Create, a DDL for result window appears showing the DDL generated by Studio. Click Execute in that window to create the table in the data source. Even though a dialog box might say DDL execution was successful, a Status of CONFIG ERROR plus an Error message may appear on the Caching panel. Click Details for a full description.
11. 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.
14. If you have data type incompatibilities between your view and your data storage type, see “Cache Data Type Mapping” in the TDV Reference Guide.