SQL Cache Tables › Examples of Displaying and Controlling the Cache

Examples of Displaying and Controlling the Cache

CACHE Options

To display the cache options:

Select * from SYSCA.DSCCACHEOPT;
*+
*+  CACHEMAXCNT  DEFAULT    EXCEPTCNT  EXCEPTCON
*+  -----------  -------    ---------  ---------
*+         1000  OFF                2  <null>
*+       <null>  <null>        <null>  SYSTEM
*+       <null>  <null>        <null>  APPLDICT

To change the default for caching:

Update SYSCA.DSCCACHEOPT set DEFAULT = 'ON';

To add the connect name 'TSTDICT' to the exception list:

Insert into SYSCA.DSCCACHEOPT (EXCEPTCON) values ('TSTDICT');

To remove the connect name 'SYSTEM' from the exception list:

Delete from SYSCA.DSCCACHEOPT where EXCEPTCON = 'SYSTEM';

To remove all the connect names from the exception list:

Delete from SYSCA.DSCCACHEOPT where EXCEPTCON is not null;

To decrease the number of entries in the cache from 1000 to 5:

Update SYSCA.DSCCACHEOPT set CACHEMAXCNT = 5;

Only the last 5 used entries will be kept in the cache.

To increase the number of entries in the cache from 5 to 9999:

Update SYSCA.DSCCACHEOPT set CACHEMAXCNT = 9999;

The cache will be extended with 9994 new slots.

To clear the SQL cache and remove all the SQL cache structures from the system, effectively disallowing any SQL caching:

Delete from SYSCA.DSCCACHEOPT;

To rebuild the SQL cache environment or to build the SQL cache environment in a system that has no SQL CACHE statement in its SYSGEN:

Insert into SYSCA.DSCCACHEOPT (CACHEMAXCNT, DEFAULT) values (1000, 'ON');
Insert into SYSCA.DSCCACHEOPT (EXCEPTCON) values ('APPLDICT');
Insert into SYSCA.DSCCACHEOPT (EXCEPTCON) values ('SYSTEM');

CACHE Control Parameters

To display cache control parameters:

Select * from SYSCA.DSCCACHECTRL;
*+
*+REQUEST  STATUS  CACHEMAXCNT  CACHECURCNT  CURRENT OLDEST
*+-------  ------  -----------  -----------  ------- ------
*+  A              1000                   7        6      0
*+
*+ STORAGEUSEKB
*+ ------------
*+          138

To clear the cache, but allow caching to continue as defined by the option in DSCCACHEOPT:

Delete from SYSCA.DSCCACHECTRL;

CACHE Entries

To display key columns of all cache entries:

Select * from SYSCA.DSCCACHEV;
*+
*+  KEY  DBNAME    DEFAULTSCHEMA     USECNT          AGE
*+  ---  ------    -------------     ------          ---
*+   29  SYSDICT   <null>                 4            0
*+   32  SYSDICT   <null>                 1            1
*+   28  SYSDICT   <null>                 2            1
*+   32  SYSDICT   <null>                 7            7
*+   29  SYSDICT   <null>                 6            6
*+
*+     #C  COMPILESTAMP                  FIBSIZE  FIBADDR
*+     --  ------------                  -------  --------
*+     1  2002-09-04-10.05.20.740186         736  12AC6208
*+     1  2002-09-04-10.07.20.009275        2528  12ACD088
*+     1  2002-09-04-10.06.19.785231        2580  12ACB888
*+     1  2002-09-04-10.02.39.729463         552  12AC0A08
*+     1  2002-09-04-10.03.00.735305         736  12ABFD88
*+
*+ STMT1
*+ -----
*+ Select * from SYSCA.DSCCACHEV
*+ select * from empnsql.department
*+ select * from empnsql.office
*+ select * from SYSCA.DSCCACHECTRL
*+ select * from sysca.dsccachev

To display cache entries with AGE > 1:

Select * from SYSCA.DSCCACHEV where AGE > 1;

To display cache entries for DBNAME SYSDICT:

Select * from SYSCA.DSCCACHEV where DBNAME = 'SYSDICT';

To display cache entries for statements that use schema EMPNSQL:

Select * from SYSCA.DSCCACHEV where STMT1 like '%EMPNSQL.%';

To remove cache entries that use schema EMPNSQL:

Delete from SYSCA.DSCCACHE where STATEMENT like '%empnsql.%';