Creating a Multiple Table Cache on a Database Target
With the multiple table cache option, TDV stores the cache data, from views or tables, in a user specified number of tables. Because the cached data can be stored in different tables, it is possible to:
• Create more effective indexes on the cached data
• Clear old data from the cache faster than with other modes of caching
The multi-table cache option is primarily meant for caches that contain a substantial amount of data which is retained for a long time.
The multi-table feature is designed for multiple cache refreshes. Each refresh populates rows in one table, and because multiple tables are open the performance of caching is improved. Records are not distributed across multiple tables.Multiple tables available for cache refreshes creates a situation where customers accessing data in one of the cached tables does not slow down a refresh action because one of the other cache tables can be used.
If a resource uses a data source that was originally added to TDV Server using the 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.
Follow the instructions in these sections to use the multi-table cache feature:
Configuring Teradata for Use as a Multi-Table Cache Target
For Teradata, the TDV multi-table option can make use of the Teradata bulk loading FastLoad FastExport functions. You must follow the Teradata documentation for how to set up the FastLoad FastExport features on the Teradata data source and configuring session count. You can then use Studio to complete the configuration of FastLoad FastExport functions.
Note: If you are not using Teradata as a cache target, you can skip to
Enabling Caching to Multiple Tables.
To configure Teradata as the multi-table cache target
1. Open Studio.
2. Select Administration > Configuration.
3. Search for Enable Native Loading and make sure it is set to True.
4. Right-click the data source name in the Studio resource tree and select Open.
5. Select the Configuration tab and under the Connection Information select the Advanced tab.
6. Use the Advanced tab to set values for the following fields.
Field | Description |
Enable FastLoad/FastExport for large tables | True means to use Teradata’s FastLoad or FastExport utility to speed up query times. Query cardinality is used to decide whether to use Fastpath or JDBC default loading. |
FastExport Session Count | The number of FastExport sessions to use for Teradata. |
FastLoad Session Count | The number of FastLoad sessions to use for Teradata. |
7. Save your settings.
Enabling Caching to Multiple Tables
When clearing a multi-table cache, TDV first attempts to clear the cache using a TRUNCATE command. If the TRUNCATE command is not supported, a DELETE command is attempted.
To enable caching that saves data to multiple tables
1. In Studio, open a view or a table.
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 is not active until you select the Enable check box.
When a cache is disabled, all existing cache settings are ignored. The view 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, specify the storage type as Multi-table. Multi-table is a data caching option that creates one table for each cache refresh up to the cache table threshold.
This option requires that you specify a database data source to hold the cache tables and that you have sufficient privileges on that data source to CREATE and DELETE cache objects.
Note: Resources cannot share the same data cache table.
6. Identify a database data source where you have permissions to create and delete objects from the database.
Note: File data sources are not valid targets for multi-table caching.
7. 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.
8. 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).
9. Save the data source panels.
10. Navigate back to the view or procedure with the open Caching tab.
11. In the Multi-table section, choose between the alternatives shown in the table.
Field | Description |
Create Tables Automatically | Provides selections for indicating a table prefix, number of buckets, and index creation for your required cache objects. |
Choose Tables for Caching | Provides a mechanism for you to select predefined cache tables. Your predefined cache tables must adhere to the metadata requirements of the cache tables. For data integrity, each resource that is cached should have its own cache data tables. |
12. If you selected Create Tables Automatically, you must make choices in the fields listed in the table.
Fields | Description |
Table Catalog (optional) | Depending on how your data source was defined and introspected, you might have one or two levels of folders under the data source in the Studio resource tree. The table catalog refers to the first level of folder under the data source. If your data source contains subfolders, type the name of the first level here. The name is case-sensitive. |
Table Schema (optional) | The table schema refers to the second level of folder under the data source. If your data source contains subfolders, type the name of the secondary level here. The name is case-sensitive. |
Table Prefix | Prefix to add to the cache tables and any indexes that you want to create. A unique prefix is required. |
Number of Caching Tables | Use to indicate the number of snapshots of the cache to keep. Start with a value of 3 and then determine if that number needs to be increased to accommodate your specific caching needs. The number of cache tables needed depends on how often data is cached, how much data is cached, how often refresh occurs, and the longest running transaction that accesses this cache. For example, if the longest running transaction is 4 hours and refreshes happen every hour, you should define at least 4 cache tables. If a cache is to refresh every 2 hours but some transactions run for 6 hours, configure at least 4 cache tables. Each row in cache status table has the name of the cache table containing the snapshot. A cache table is not eligible for garbage collection as long as there is an active transaction that uses it. TDV attempts to detect cache table sharing, but it is your responsibility to ensure that each cache table is used by only one resource. |
Create Cache Tables | Runs the DDL to create the tables needed for caching. |
Drop indexes before load and create indexes after load | When selected, all indexes listed in Indexes tab of the view are part of the generated DDL and are created. Enabling this check box makes TDV drop indexes before loading data and recreates them after, to improve load time. |
13. If you want to have the multi-table caching option to drop and create indexes after the cache loads, select the Indexes tab and review or define an index for the view or table. For information on how to create the index using the tab, see
Defining Primary Key for a View or Table in the Indexes Panel.
14. If you selected Choose Tables For Caching, you must make choices in the following fields:
Fields | Description |
Add Table | Use to add additional tables for cache storage. |
Table Name | Browse to select or create the table for a multi-table cache. You can also type the resource tree path. This table must not be used to store data for other caches. |
15. 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.
18. If you have data type incompatibilities between your view and your data storage type, see “Cache Data Type Mapping” in the TDV Reference Guide.