Tables for Viewing, Monitoring, and Controlling the Cache

SQL Cache Tables › Tables for Viewing, Monitoring, and Controlling the Cache

Tables for Viewing, Monitoring, and Controlling the Cache

SQL is the Application Programming Interface (API) used to view, monitor, and change the cache and the cache configuration. Therefore, cache administration, configuration, and dynamic SQL cache monitoring is available in any environment that supports CA IDMS SQL, such as IDMSBCF, OCF, CA IDMS Visual DBA, and the CA IDMS SQL programs, among others.

This section describes three table procedures and one view of the SYSCA tables defined for dynamic SQL cache management.

DSCCACHEOPT

The DSCCACHEOPT table manages the SQL cache options.

Column	Data Type	Description
CACHEMAXCNT	INTEGER	The maximum number of entries that the cache can contain.
DEFAULT	CHAR(4)	Default for caching: ON/OFF. This specifies if caching is enabled or disabled for any connect name that does not appear in the EXCEPTCON column.
EXCEPTCNT	INTEGER	Count of rows in the DSCCACHEOPT relation with non-NULL value for the EXCEPTON column, in other words, the number of connect names in the list of exceptions.
EXCEPTCON	CHAR(8)	Connect name that forms an exception to the default caching.

Note the following:

After startup of an IDMS Central Version, DSCCACHEOPT reflects the parameters of the sysgen SQL CACHE statement. In absence of an SQL CACHE statement there will be no rows in DSCCACHEOPT and SQL caching will be disabled, but can be activated by inserting a DSCACHEOPT row. Updates to the DSCCACHEOPT table will have no impact on the sysgen of the CV.
In local mode when no DSCCACHEOPT row exists, a DSCCACHEOPT row will be automatically inserted with values derived from the SYSIDMS parameter SQL_CACHE_ENTRIES.
There can be 0 to n rows in this table. If there are 0 rows, this means that SQL statement caching is not active and not defined to the system. If there are rows, then the first row will have non-NULL values for CACHEMAXCNT, DEFAULT and EXCEPTCNT and a NULL value for EXCEPTCON. The first row contains the main SQL cache parameters. Other rows in the DSCCACHEOPT relation will have only non-NULL values for the EXCEPTCON column. These rows form the list of exception connect names.
You can issue select, insert, update and delete commands against DSCCACHEOPT.
Deleting the first row automatically deletes all other rows and removes all SQL cache structures from the system, effectively disabling caching until a new DSCCACHEOPT row is inserted. Deleting other rows removes exception connect names from the exception list.
Inserting a row is always possible. When one or more rows already exists, an insert can only specify a value for EXCEPTCON, this is the way to add connect names to the list. When no rows exist, the first insert must specify values for CACHEMAXCNT and DEFAULT. Other values are not allowed. A successful insertion of the first row enables SQL caching.
Updating of CACHEMAXCNT and DEFAULT columns automatically applies to the first row only, so that no WHERE clause is needed to filter the first row. When CACHEMAXCNT is decreased, the entries in the SQL cache with the highest AGE (see the section, "DSCCACHE") are removed. Increase CACHEMAXCNT to allow the size of the cache to be increased. You cannot update EXCEPTCON for the first row. You cannot update EXCEPTCNT as this is automatically calculated.
The size of the cache is specified in terms of number of entries. Each entry represents a single cached statement. The cache is allocated from the storage pool within a central version and from operating system storage in local mode. You can determine the amount of storage being consumed by the cache by selecting from the DSCCACHECTRL table.

DSCCACHECTRL

The DSCCACHECTRL table controls SQL caching.

Column	Data Type	Description
REQUEST	CHAR	Future use
STATUS	CHAR	Future use
CACHEMAXCNT	INTEGER	Maximum count of entries
CACHECURCNT	INTEGER	Current count of entries used
CURRENT	INTEGER	Current entry
OLDEST	INTEGER	Oldest entry
STORAGEUSEKB	INTEGER	Total storage used by the cache

Note the following:

There can be 0 rows or 1 row in this table. If no rows are present, no SQL statements have been cached.
Only select and delete statements against this table are possible.
Deleting the 1 row in DSCCACHECTRL clears the SQL cache structures. It does not disable caching, which is controlled through the DSCCACHEOPT table.

DSCCACHE

The DSCCACHE table represents the SQL cache. Each row is a cache entry.

Column	Data Type	Description
KEY	INTEGER	Non-unique key
LOCK	BINARY(4)	Lock word for access to entry
DBNAME	CHAR (8)	DBName of SQL session
DEFAULTSCHEMA	CHAR (18)	Default schema of session if statement contains at least one unqualified table reference
USECNT	INTEGER	Usage count
AGE	INTEGER	A value used to determine which entry to purge from a full cache when a new entry is inserted. The longer an entry remains in the cache without being used, the higher its age.
COMPILECOST	INTEGER	Compilation cost
ACCPLANSCANCOST	FLOAT	Cost of scan in access plan
ACCPLANCPUCOST	FLOAT	Cost of CPU in access plan
ACCPLANROWCNT	FLOAT	Count of rows in access plan
EXECCOST	INTEGER	Cost of last execution of statement
COMPILECNT	INTEGER	Count of (re)compilations
COMPILESTAMP	TIMESTAMP	Timestamp of compilation
STMTSIZE	INTEGER	Size of statement
STATEMENT	VARCHAR (8192)	Statement
SQLDIBSIZE	INTEGER	Size of SQLDIB
SQLCMD	INTEGER	Type of SQL command
SQLITCL	INTEGER	Combined Itree/TELL table length
SQLARG	INTEGER	Bit flags for argument usage
SQLOPT	INTEGER	Session options flags
SQLTBL	INTEGER	Length of tuple buffer row
SQLPBL	INTEGER	Length of parameter buffer
SQLCID	INTEGER	Cursor identifier
SQLSID	INTEGER	Section identifier
SQLNM1	CHAR(32)	Literal value 1
SQLNM2	CHAR(32)	Literal value 2
SQLITL	INTEGER	Size of Itree
SQLITBADDR	BINARY(4)	Address of Itree
RTREESIZE	INTEGER	Size of Rtree
RTREEOFFSET	INTEGER	Offset of Rtree for relocation purposes
RTREEDOFAOFF	INTEGER	Offset of DOFA in Rtree
RTREEADDR	BINARY(4)	Address of Rtree
FIBSIZE	INTEGER	Size of FIB
FIBADDR	BINARY(4)	Address of FIB
FOPSIZE	INTEGER	Size of FOP
GSTSIZE	INTEGER	Size of GST
FOPADDR	BINARY(4)	Address of FOP
LASTUSER	CHAR(8)	Reserved
GLOBALCURSORNAME	CHAR(18)	Reserved
FCRC	BINARY(4)	FCRC flags
SQLDAADDR	BINARY(4)	Address of cached input SQLDA
STORAGEUSED	INTEGER	Size in bytes of used storage

Note the following:

One row of this table represents one cached statement.
Rows cannot be inserted or updated.
Because of the size of the STATEMENT column in DSCCACHE and also because many of these columns are for internal use only, it is advisable to use a view on this table procedure. The supplied DSCCACHEV view (shown next) is an example of such a view.

The following acronyms are used in the previous table.

Itree: A data structure that contains the internal input representation of an SQL statement
Rtree: A data structure that contains the internal runtime instruction of an SQL statement. The Rtree is used by the SQL runtime engine IDMSHLDB.
FIB: A data structure that contains runtime metadata.
FOB/FOP: FIB objects list data structure
GST: Global Security Table
FCRC: Fixed part of Compiled Relational Command data structure
SQLDA: the SQL Descriptor Area (SQLDA) is a data structure used to describe variable data passed as part of a dynamic SQL statement.

DSCCACHEV

SYSCA.DSCCACHEV is created during installation. It defines a view on the SYSCA.DSCCACHE table procedure as follows:

create view SYSCA.DSCCACHEV as
 select KEY, DBNAME, DEFAULTSCHEMA, USECNT, AGE
      , COMPILECNT as "#C", compilestamp
      , ACCPLANSCANCOST, ACCPLANCPUCOST
      , ACCPLANROWCNT, FIBSIZE, FIBADDR
      , SUBSTR(STATEMENT, 1, 72) as STMT1
 from SYSCA.DSCCACHE;

You have the option to define your own views.

Allowable Operations on DSCCACHE Tables

	DSCCACHOPT	DSCCACHECTRL	DSCCACHE	DSCCACHEV
Type	Table Procedure	Table Procedure	Table Procedure	View
SELECT	X	X	X	X
INSERT	X
UPDATE	X
DELETE	X	X	X	X