Pre-Creating Caching Objects for Database Caching
The instructions in this topic are optional and assume that you can create database objects as needed. You will also need to be familiar with the server, schema, and database naming conventions for the database where you are creating these tables. It is suggested that you obtain the documentation for your database of choice. Depending on your corporate policies and procedures, you might have to create the caching objects directly in the database before using Studio to set up caching.
At a minimum, non-file caching requires three tables. One table to hold the cache data, one to hold the cache status, and one to hold the cache tracking information. You can name them anything that is valid according to the constraints of the database where you are creating them. It is suggested that you use names that will be obvious to someone else.
To pre-create caching objects in a database
1. Determine the optimal database for your cache target.
2. Add that database as a data source within Studio, if it is not there already.
3. Determine whether single or multiple cache tables will be needed.
4. Determine how many caches will be needed, or define a process for their request.
5. Locate and open the tool you want to use to create your database tables.
6. For single table caches create the following table using DDL statements similar to the following for each unique TDV resource for which you want to define a cache. The DDL might need to be altered depending on version of TDV your are using, database type where you are creating the table, TDV resource that you are caching, and whether you want to index the data or not. In the following table, UID stands for user identification. You can name the tables anything you want <table_data> is used to indicate that you will need to specify the names, data types, and other table creation required metadata for the columns you want to create in that table.
Table | DDL Example |
<UID_cache> | DROP TABLE IF EXISTS `INV_cache_data`; CREATE TABLE `INV_cache_data` ( `cachekey` integer NOT NULL, <table data> ) |
7. For multiple table caches, create tables with the following structures for each TDV resource for which you want to define a cache. The DDL might need to be altered depending on version of TDV your are using, database type where you are creating the table, TDV resource that you are caching, and whether you want to index the data or not.
Table | DDL Example |
<UID_cache_status> | DROP TABLE "QAN"."mm_cache_status"; CREATE TABLE "QAN"."mm_cache_status" ( "clusterid" varchar(40), "serverid" varchar(255) NOT NULL, "resourceid" varchar(255) NOT NULL, "parameters" varchar(255), "status" char(1) NOT NULL, "cachekey" number(10, 0) NOT NULL, "starttime" timestamp(9) NOT NULL, "finishtime" timestamp(9), "cleartime" timestamp(9), "bytes" number(19, 0), "message" varchar(255), "bucket" varchar(255), "incrementalstatus" char(1), "incrementalmaintenancelevel" varchar(255) ) |
<UID_cache_tracking> | DROP TABLE "QAN"."mm_cache_tracking"; CREATE TABLE "QAN"."mm_cache_tracking" ( "clusterid" varchar(40), "serverid" varchar(255) NOT NULL, "resourceid" varchar(255) NOT NULL, "catalog" varchar(255), "schema" varchar(255), "table" varchar(255) NOT NULL, "createtime" timestamp(9) ) |
<UID_cache_data> | DROP TABLE "QAN"."mm0"; CREATE TABLE "QAN"."mm0" ( <table_data> ); CREATE UNIQUE INDEX "mm0_PRIMARY" ON "QAN"."mm0"("TransactionID" asc) DROP TABLE "QAN"."mm1"; CREATE TABLE "QAN"."mm1" ( <table_data>); CREATE UNIQUE INDEX "mm1_PRIMARY" ON "QAN"."mm1"("TransactionID" asc) DROP TABLE "QAN"."mm2"; CREATE TABLE "QAN"."mm2" ( <table_data>); CREATE UNIQUE INDEX "mm2_PRIMARY" ON "QAN"."mm2"("TransactionID" asc) DROP TABLE "QAN"."mm3"; CREATE TABLE "QAN"."mm3" ( <table_data> ); ... |
8. Re-introspect the data source to make sure that the additional table you have created are available through TDV.
9. Set up your cache and select these tables as the status, tracking and data tables.