ttSQLCmdCacheInfo

Description

Displays all compiled SQL statements in the TimesTen SQL command cache.

Required privilege

This procedure requires the ADMIN privilege.

Syntax

ttSQLCmdCacheInfo()

Parameters

ttSQLCmdCacheInfo has the optional parameter:

Parameter	Type	Description
`sqlCommandID`	TT_INTEGER for 32-bit systems TT_BIGINT for 64-bit systems	The unique identifier of a SQL command in the TimesTen command cache. If no value is supplied displays information about all current commands in the TimesTen cache.

Result set

ttSQLCmdCacheInfo returns the result set:

Parameter	Type	Description
`sqlCommandID`	TT_INTEGER NOT NULL for 32-bit systems TT_BIGINT NOT NULL for 64-bit systems	The unique identifier of a command in the TimesTen command cache.
`privateCommandConnectionID`	TT_INTEGER	The unique ID of a private connection. If not a private connection, the value is NULL.
`executions`	TT_BIGINT NOT NULL	A counter for the number of executions that took place on this command since it was brought into the command cache.
`prepares`	TT_BIGINT NOT NULL	A counter for the number of user prepares that result in a hit on the command cache.
`reprepares`	TT_BIGINT NOT NULL	A counter for the number of reprepares or invalidations of this command.
`freeable`	TT_TINYINT NOT NULL	Indicates whether this command can be garbage collected by the subdaemon. 1 - Indicates freeable. 0 - Indicates non-freeable.
`size`	TT_INTEGER NOT NULL	The total space (bytes) allocated for this command in the command cache.
`owner`	TT_CHAR(31) NOT NULL	The identifier of the user who created this command.
`queryText`	TT_VARCHAR(1024) NOT NULL	The first 1024 characters of the SQL text for the current command.

Examples

To display command info for all of the current valid commands, use

Command> CALL ttSQLCmdCacheInfo;
< 528079360, 2048, 0, 1, 0, 1, 2168, TTUSER , select * from t7 where x7 is not null or exists (select 1 from t2,t3 where not 'tuf' like 'abc') >
< 527609108, 2048, 0, 1, 0, 1, 2960, TTUSER , select * from t1 where x1 = (select x2 from t2 where z2 in (1,3) and y1=y2) order by 1, 2, 3 >
< 528054656, 2048, 0, 1, 0, 1, 1216, TTUSER , create table t2(x2 int,y2 int, z2 int) >
< 528066648, 2048, 0, 1, 0, 1, 1176, TTUSER , insert into t2 select * from t1 >
< 528013192, 2048, 0, 1, 0, 1, 1848, TTUSER , select * from t1 where exists (select * from t2 where x1=x2) or y1=1 >
< 527582620, 2048, 0, 1, 0, 1, 1240, TTUSER , insert into t2 select * from t1 >
< 527614292, 2048, 0, 1, 0, 1, 2248, TTUSER , select * from t1 where exists (select x2 from t2 where x1=x2) order by 1, 2, 3 >
< 528061248, 2048, 0, 1, 0, 1, 696, TTUSER , create index i1 on t3(y3) >
< 528070368, 2048, 0, 1, 0, 1, 824, TTUSER , call ttOptSetOrder('t3 t4 t2 t1') >
< 528018856, 2048, 0, 1, 0, 1, 984, TTUSER , insert into t2 select * from t1 >
< 527606460, 2048, 0, 1, 0, 1, 2624, TTUSER , select * from t1 where x1 = (select x2 from t2 where y1=y2) order by 1, 2, 3 >
< 528123000, 2048, 0, 1, 0, 1, 3616, TTUSER , select * from t1 where x1 = 1 or x1 = (select x2 from t2,t3 where z2=t3.x3) >
< 528074624, 2048, 0, 1, 0, 1, 856, TTUSER , call ttOptSetOrder('t4 t2 t3 t1') >
< 527973892, 2048, 0, 1, 0, 1, 2872, TTUSER , select * from t1 where x1 in (select x2 from t2) or x1 in (select x3 from t3) order by 1, 2, 3 >
< 527953876, 2048, 0, 1, 0, 1, 3000, TTUSER , select * from t1 where x1 = (select x2 from t2) order by 1, 2, 3 >
< 527603900, 2048, 0, 1, 0, 1, 2440, TTUSER , select * from t1 where x1 in (select x2 from t2 where y1=y2) order by 1, 2, 3 >
< 528093308, 2048, 0, 1, 0, 1, 3608, TTUSER , select * from t1 where x1 = 1 or x1 = (select x2 from t2,t3 where z2=t3.x3 and t3.z3=1) >
< 528060608, 2048, 0, 1, 0, 1, 696, TTUSER , create index i1 on t2 (y2) > 
…..

To display command info for the SqlCmdId 527973892, use

Command> CALL ttSQLCmdCacheInfo(527973892);
< 527973892, 2048, 0, 1, 0, 1, 2872, TTUSER, 
select * from t1 where x1 in (select x2 from t2) or 
x1 in (select x3 from t3) order by 1, 2, 3 >
1 row found.