Oracle® TimesTen In-Memory Database Reference Release 11.2.1 Part Number E13069-04 |
|
|
View PDF |
TimesTen built-in procedures extend standard ODBC and JDBC functionality. You can invoke these procedures using the ODBC or JDBC procedure call interface. The procedure takes the position of the SQL statement, as illustrated in the following examples.
The following ODBC call tells the optimizer that it should not generate temporary hash indexes when preparing commands:
SQLExecDirect (hstmt, (SQLCHAR*) "{CALL ttOptSetFlag ('TmpHash', 0)}", SQL_NTS);
This is the equivalent JDBC call:
CallableStatement cstmt = con.prepareCall ("{CALL ttOptSetFlag ('TmpHash', 0)}"); cstmt.execute();
TimesTen built-in procedures can also be called from PL/SQL using the EXECUTE IMMEDIATE statement with CALL, as illustrated in the following example.
For example, to call the built-in procedure ttConfiguration
, create a PL/SQL record type and then SELECT INTO that record type. Because ttConfiguration
returns more than one row, use BULK COLLECT.
Command> DECLARE > TYPE ttConfig_record IS RECORD > (name varchar2(255), value varchar2 (255)); > TYPE ttConfig_table IS TABLE OF ttConfig_record; > v_ttConfigs ttConfig_table; > BEGIN > EXECUTE IMMEDIATE 'CALL ttConfiguration' > BULK COLLECT into v_ttConfigs; > DBMS_OUTPUT.PUT_LINE ('Name: ' || v_ttConfigs(1).name > || ' Value: ' || v_ttConfigs(1).value); > end; > / Name: CacheGridEnable Value: 0 PL/SQL procedure successfully completed.
Note:
String parameter values for built-in procedures must be single-quoted as indicated in these examples, unless the value is NULL.Description
This procedure sets the LRU aging attributes on all regular tables that have been defined with an LRU aging policy. For cache tables, the aging policy is defined on the root table but applies to all tables in the cache group. The aging policy is defined on tables when they are created or altered, using the CREATE TABLE or ALTER TABLE SQL statements.
The LRU aging feature helps applications maintain the usage size of the data store under a specified threshold by removing the least recently used data.
Data is removed if the data store space in-use exceeds the specified threshold values. For cache groups, aging is defined at the root table for the entire cache instance. LRU aging is not allowed for cache groups with AUTOREFRESH. For those cache groups, use time-based aging.
Required privilege
This procedure requires no privilege to query the current values. It requires the ADMIN privilege to change the current values.
Syntax
ttAgingLRUConfig(LowUsageThreshHold, HighUsageThreshHold, AgingCycle)
Parameters
ttAgingLRUConfig has these optional parameters:
Parameter | Type | Description |
---|---|---|
lowUsageThreshold |
BINARY_FLOAT | Sets, displays or resets the low end of percentage of data store PermSize, specified in decimals. The bottom of the threshold range in which LRU aging should be deactivated. Default is 80%. |
highUsageThreshold |
BINARY_FLOAT | Sets, displays or resets the high end of percentage of data store PermSize, specified in decimals. The top of the threshold range in which LRU aging should be activated. Default is 90%. |
agingCycle |
TT_INTEGER | Sets, displays or resets the number of minutes between aging cycles, specified in minutes. Default is 1 minute. If you use this procedure to change the aging cycle, the cycle is reset based on the time that this procedure is called. For example, if you call this procedure at 12:00 p.m. and specify a cycle of 15 minutes, aging occurs at 12:15, 12:30, 12:45, etc. |
Result set
ttAgingLRUConfig returns these results:
Column | Type | Description |
---|---|---|
lowUsageThreshold |
BINARY_FLOAT NOT NULL | The current setting for the low end of percentage of data store PermSize, specified in decimals. |
highUsageThreshold |
BINARY_FLOAT NOT NULL | The current setting for the high end of percentage of data store PermSize, specified in decimals. |
agingCycle |
TT_INTEGER NOT NULL | The current setting for the number of minutes between aging cycles, specified in minutes. |
Examples
To set the aging threshold to a low of 75 percent and a high of 95 percent and the aging cycle to 5 minutes, use:
CALL ttAgingLRUConfig (.75, .90, 5); <.7500000, .9000000, 5>
To display the current LRU aging policy for all tables that defined with an LRU aging policy, call ttAgingLRUConfig without any parameters:
Call ttAgingLRUConfig();
If the tables are defined with the default thresholds and aging cycle, the procedure returns:
<.8000000, .9000000, 1> 1 row found.
To change the low usage threshold to 60%, the aging cycle to 5 minutes and to retain the previous high usage threshold, use:
Call ttAgingLRUConfig (60,,5); < .6000000, .9000000, 5 > 1 row found.
Notes
The values of this procedure are persistent, even across system failures.
If no parameters are supplied, this procedure only returns the current LRU aging attribute settings.
See also
Description
This procedure starts the aging process, regardless of the value of the aging cycle. The aging process begins right after the procedure is called unless there is already an aging process in progress. In that case, the new aging process begins when the aging process that was in process at the time the built-in was called has completed.
Aging occurs only once when you call this procedure. This procedure does not change any aging attributes. The previous aging state is unchanged. For example, if aging state is OFF when you call ttAgingScheduleNow, the aging process starts. When aging is complete, if your aging state is OFF, aging does not continue. To continue aging, you must call ttAgingScheduleNow again or change the aging state to ON, in which case aging occurs next based on the value of the aging cycle.
For tables with aging ON, the aging cycle is reset to the time when ttAgingScheduleNow was called. For example, if you call this procedure at 12:00 p.m. and the aging cycle is 15 minutes, aging occurs immediately and again at 12:15, 12:30, 12:45, etc.
If used in an external scheduler, such as a cron
job, or executed manually, this procedure starts the aging process at the time the procedure is executed, if there is no aging process in progress, or as soon as the current aging process has completed. In the case that you want aging to occur only when the external scheduler executes the ttAgingScheduleNow procedure or you call it manually, set the aging state to OFF.
Aging is performed by a background thread that wakes up every second to check if any work needs to be done. Calling ttAgingScheduleNow only guarantees that the aging thread works on the specified tables within the next second, at best. If the aging thread is working on a different table at the time the built-in procedure is called, it may take some time to reach the specified table. The rows are visible until the aging thread commits the delete.
This procedure requires the DELETE privilege on the table being aged, or the DELETE ANY TABLE privilege when you do not specify a table.
Syntax
ttAgingScheduleNow ('tblname')
Parameters
ttAgingScheduleNow has the parameter:
Parameter | Type | Description |
---|---|---|
tblname |
TT_CHAR (61) | The name of the table on which to start the aging process.
If Using a synonym to specify a table name is not supported. |
Result set
ttAgingScheduleNow returns no results.
Examples
To schedule aging on all tables, including tables defined with both LRU aging and time-based aging, call ttAgingScheduleNow without any parameter values:
CALL ttAgingScheduleNow ();
This examples creates the table agingex
with time-based aging policy and the aging state set to OFF. ttAgingScheduleNow is called, using the ttIsql utility, to start the aging process once. Rows are deleted from the table. After ttAgingScheduleNow is called, the aging state remains OFF. To continue aging, alter the table and set the aging state to OFF.
Command> CREATE TABLE agingex (col1 TT_INTEGER PRIMARY KEY NOT NULL, ts TIMESTAMP NOT NULL) AGING USE ts LIFETIME 1 MINUTES CYCLE 30 MINUTES OFF; Command> DESCRIBE agingex; Table TTUSER.AGINGEX: Columns: *COL1 TT_INTEGER NOT NULL TS TIMESTAMP (6) NOT NULL Aging use TS lifetime 1 minute cycle 30 minutes off 1 table found. (primary key columns are indicated with *) Command> INSERT INTO agingex VALUES (1, SYSDATE); 1 row inserted. Command> INSERT INTO agingex VALUES (2, SYSDATE); 1 row inserted. Command> SELECT * FROM agingex;
< 1, 2007-03-25 13:06:29.000000 > < 2, 2007-03-25 13:06:42.000000 > 2 rows found. Command> CALL ttAgingScheduleNow ('agingex'); Command> SELECT * FROM agingex; 0 rows found.
See also
Description
This procedure sets application-defined context for the next update record (either an UPDATE or commit) in order to pass application specific data to XLA readers.
This procedure requires no privilege.
Syntax
ttApplicationContext (cmd)
Parameters
ttApplicationContext has the parameter:
Parameter | Type | Description |
---|---|---|
cmd |
VARBINARY(16384) NOT NULL | Context information to be passed. |
Result set
ttApplicationContext returns no results.
Example
CALL ttApplicationContext (0x123);
See also
"XLA Reference" in Oracle TimesTen In-Memory Database C Developer's Guide
Description
This procedure returns a single row with information about the current or last backup of the data store. If a backup is in progress, this information represents the current backup. If no backup is in progress, this information represents the last backup taken.
If no backup has been taken on the database since the last first-connect, the status field is 0 and the rest of the columns are NULL.
This procedure requires the ADMIN privilege.
Syntax
ttBackupStatus ()
Parameters
ttBackupStatus has no parameters.
Result set
ttBackupStatus returns the results:
Column | Type | Description |
---|---|---|
status |
TT_INTEGERNOT NULL | An INTEGER code representing the current progress of a backup or the completion status of the last backup. Values are:
0 - No backup has been taken on the data store since the last first-connect. 1 - A backup is currently in progress. 2 - The last backup completed successfully. 3 - The last backup failed. In this case the error column contains the error code for the failure. |
destination |
TT_INTEGER | The type of backup taken. The value is NULL when no backup has been taken on the data store. Value is one of:
0 - Backup is/was being written to a file. 1 - Backup is/was being written to a stream. 2 - Backup is/was taken on behalf of replication duplicate. |
backupType |
TT_INTEGER | Backup type, either full or incremental. The value is NULL when no backup has been taken on the data store. Value is one of:
0 - Incremental backup 1 - Full backup |
startTime |
TT_TIMESTAMP | Time when the backup was started. The value is NULL when no backup has been taken on the data store. |
endTime |
TT_TIMESTAMP | Time when the backup completed. If NULL and startTime is non-NULL, a backup is currently in progress. |
backupLFN |
TT_INTEGER | The transaction log file number of the backup point. The value is NULL when no backup has been taken on the data store. |
backupLFO |
TT_INTEGER | The transaction log file offset of the backup point. The value is NULL when no backup has been taken on the data store. |
error |
TT_INTEGER | In the case of a failed backup, this column indicates the reason for the failure. The value is one of the TimesTen error numbers. The value is NULL when no backup has been taken on the data store. |
processId |
TT_INTEGER | The ID of the process or daemon performing the backup (if known). |
Example
CALL ttBackupStatus (); < 2, 2, 1, 2005-08-12 13:10:32.587557, 2005-08-12 13:10:33.193269, 1, 1531840, 0, 6968 > 1 row found.
Notes
Does not return information about previous backups, other than the current or last one.
Information returned is not persistent across data store startup or shutdown.
Description
This procedure provides information about perm blocks and the amount of block-level fragmentation in a data store.
Required privilege
This procedure requires no privilege.
Syntax
ttBlockInfo()
Parameters
ttBlockInfo has no parameters.
Result set
ttBlockInfo returns the result set:
Column | Type | Description |
---|---|---|
TotalBlocks |
TT_BIGINT NOT NULL | Total number of blocks in the data store. |
FreeBlocks |
TT_BIGINT NOT NULL | Total number of free blocks in the data store. |
FreeBytes |
TT_BIGINT NOT NULL | Total size of the free blocks. |
LargestFree |
TT_BIGINT NOT NULL | Size of the largest free block. |
Examples
CALL ttBlockInfo(); < 288, 3, 128711700, 128698596 > 1 row found.
Description
This procedure returns information about the TimesTen transaction log. Records in the transaction log are identified by pairs of integers:
A transaction log file number
An offset in that transaction log file
Transaction log file numbers correspond to the file system names given to transaction log files. For example, the transaction log file SalesData.log29
has the transaction log file number 29
.
Three log records are identified in the result row of ttBookmark:
The identity of the most recently written log record
The identity of the log record most recently forced to the disk
The replication bookmark. The replication bookmark is the oldest log record that represents an update not yet replicated to another system
This procedure requires no privilege.
Syntax
ttBookmark()
Parameters
ttBookmark has no parameters.
Result set
ttBookmark returns the result set:
Column | Type | Description |
---|---|---|
writeLFN |
TT_INTEGER | Last written transaction log file |
writeLFO |
TT_INTEGER | Last written offset in transaction log file |
forceLFN |
TT_INTEGER | Last transaction log file forced to disk |
forceLFO |
TT_INTEGER | Offset of last transaction log file forced to disk |
holdLFN |
TT_INTEGER | Replication bookmark transaction log file |
holdLFO |
TT_INTEGER | Replication bookmark log offset |
Example
CALL ttBookmark ();
Description
This procedure starts an immediate autorefresh on the set of cache groups that are associated by sharing the same autorefresh interval with the specified cache group. This set of associated cache groups would normally be refreshed together automatically. The effect on the autorefresh process is the same as that of adding a new cache group with the same refresh interval as that of the specified cache group. This procedure is useful if updates have occurred on the Oracle database and you would like to refresh them on the cache group before the next scheduled autorefresh.
If there is an existing transaction with locks on table objects that belong to the set of cache groups to be autorefreshed, this procedure returns an error without taking any action. This procedure establishes a condition that requires that you commit or rollback before you can perform other work in the session.
This procedure requires the CACHE_MANAGER or ADMIN privilege.
Syntax
ttCacheAutorefresh ('cacheGroupOwner', 'cacheGroupName', synchronous)
Parameters
ttCacheAutorefresh has the parameters:
Parameter | Type | Description |
---|---|---|
cacheGroupOwner |
VARCHAR2 (30) | Name of the cache group owner. |
cacheGroupName |
VARCHAR2 (30) NOT NULL | Name of the cache group. |
synchronous |
TT_INTEGER | Species whether data is updated on synchronously or asynchronously.
0 or NULL - Asynchronous mode. The procedure returns immediately. 1 - Synchronous mode. The procedure returns after the refresh operation has completed on all associated cache groups. |
Result set
ttCacheAutorefresh returns no results:
Example
This example autorefreshes the testcache cache group and all cache groups with the same autorefresh interval. The procedure returns synchronously.
Command> call ttcacheautorefresh('user1','testcache', 1);
Notes
The specified cache group autorefresh state must be ON. While, other associated cache groups can be in any state, they are not refreshed if they are not in the autorefresh ON state.An autorefresh of the specified associated cache groups cannot already be in progress.You cannot call this procedure on the standby node of an active standby pair.
This procedure is available only for IMDB Cache.
Description
This procedure returns information about the last ten autorefresh transactions on the specified cache group. This information is only available when the AUTOREFRESH state is ON or PAUSED, and the cache agent is running.
The information returned by this built-in procedure is reset whenever:
The cache agent is restarted
The state is set to OFF and then back to ON or PAUSED
The cache group is dropped and recreated
This procedure requires no privilege.
Syntax
ttCacheAutorefreshStatsGet ('cacheGroupOwner', 'cacheGroupName')
Parameters
ttCacheAutorefreshStatsGet has the parameters:
Parameter | Type | Description |
---|---|---|
cacheGroupOwner |
VARCHAR2 (30) | Name of the cache group owner. |
cacheGroupName |
VARCHAR2 (30) NOT NULL | Name of the cache group for which autorefresh information should be returned. |
Result set
The ttCacheAutorefreshStatsGet built-in procedure returns only a subset of column information for a cache group with autorefresh mode FULL. A column value of 0 returns for information that is not available.
ttCacheAutorefreshStatsGet returns the results:
Column name | Column type | Description | Returned for full autorefresh |
---|---|---|---|
cgId |
TT_BIGINT | The cache group ID. | Y |
startTimestamp |
TT_TIMESTAMP | Timestamp when autorefresh started for this interval. See Notes section. | Y |
cacheAgentUpTime |
TT_BIGINT | Number of cache agent clock ticks in milliseconds at the time the autorefresh transaction started for this interval. This value is cumulative and is reset when the cache agent process starts. See Notes section. | Y |
autorefNumber |
TT_BIGINT | Autorefresh number | Y |
autorefDuration |
TT_BIGINT | The number of milliseconds spent in this autorefresh transaction. | Y |
autorefNumRows |
TT_BIGINT | The number of rows autorefreshed in this autorefresh. This includes all rows, including those in the root table and the child tables.
In the case of cache groups with more than one table, child table rows get updated multiple times. Therefore, the number of rows autorefreshed may be more than number of rows updated on Oracle. |
N |
numOracleBytes |
TT_BIGINT | The number of bytes transferred from Oracle in this autorefresh transaction. | N |
autorefNumRootTblRows |
TT_BIGINT | The number of root table rows autorefreshed in this autorefresh transaction. | Y |
autorefQueryExecDuration |
TT_BIGINT | The duration in milliseconds that it takes for the autorefresh query to execute on Oracle. | N |
autorefQueryFetchDuration |
TT_BIGINT | The duration in milliseconds that it takes for the autorefresh query to fetch rows from Oracle. | N |
autorefTtApplyDuration |
TT_BIGINT | The duration in milliseconds that it takes for TimesTen to apply the autorefresh. | N |
totalNumRows |
TT_BIGINT | The total number of rows autorefreshed since the cache agent started.
The total number of rows autorefreshed may not be the same as number of rows updated on Oracle. This is because of a delay in marking the log; some updates may get autorefreshed and counted more than once. |
N |
totalNumOracleBytes |
TT_BIGINT | The total number of bytes transferred from Oracle since the cache agent started. | N |
totalNumRootTblRows |
TT_BIGINT | The total number of root table rows autorefreshed since the cache agent started. | Y |
totalDuration |
TT_BIGINT | The total autorefresh duration in milliseconds since the cache agent started. | Y |
status |
VARCHAR2 (128) | A string description of the status of the current autorefresh. See Note section. Supported values for this field are:
|
Y |
Example
In this example, testcache is a READONLY cache group with one table and an incremental autorefresh interval of 10 seconds.
Command> call ttcacheautorefreshstatsget('user1','testcache'); < 1164260, 2007-07-23 15:43:52.000000, 850280, 44, 0, 75464, 528255, 75464, 310, 110, 6800, 1890912, 12439795, 1890912, 160020, InProgress > < 1164260, 2007-07-23 15:43:33.000000, 831700, 43, 13550, 108544, 759808, 108544, 1030, 230, 12290, 1815448, 11911540, 1815448, 160020, Complete > < 1164260, 2007-07-23 15:43:12.000000, 810230, 42, 17040, 115712, 809984, 115712, 610, 330, 16090, 1706904, 11151732, 1706904, 146470, Complete > < 1164260, 2007-07-23 15:42:52.000000, 790190, 41, 14300, 94208, 659456, 94208,560, 320, 13410, 1591192, 10341748, 1591192, 129430, Complete > < 1164260, 2007-07-23 15:42:32.000000, 770180, 40, 12080, 99328, 695296, 99328,450, 290, 11340, 1496984, 9682292, 1496984, 115130, Complete > < 1164260, 2007-07-23 15:42:12.000000, 750130, 39, 10380, 86016, 598368, 86016,430, 230, 9720, 1397656, 8986996, 1397656, 103050, Complete > < 1164260, 2007-07-23 15:41:52.000000, 730130, 38, 13530, 112640, 700768, 112640, 530, 220, 12780, 1311640, 8388628, 1311640, 92670, Complete > < 1164260, 2007-07-23 15:41:32.000000, 710120, 37, 9370, 56320, 326810, 56320, 310, 160, 8900, 1199000, 7687860, 1199000, 79140, Complete > < 1164260, 2007-07-23 15:41:22.000000, 700120, 36, 2120, 10240, 50330, 10240, 50, 200, 1870, 1142680, 7361050, 1142680, 69770, Complete > < 1164260, 2007-07-23 15:41:12.000000, 690110, 35, 0, 0, 0, 0, 0, 0, 0, 1132440, 7310720, 1132440, 67650, Complete > 10 rows found.
Notes
Most of the column values reported above are collected at the cache group level. For example, autorefDuration
and autorefNumRows
only include information for the specified cache group. Exceptions to this rule are column values cacheAgentUpTime, startTimestamp
and autorefreshStatus
. These values are reported at the autorefresh interval level.
StartTimestamp
is taken at the beginning of the autorefresh for the autorefresh interval. A cache group enters the "in progress" state as soon as the autorefresh for the interval starts. It is not marked "complete" until the autorefresh for all cache groups in the interval are complete.
This procedure is available only for IMDB Cache.
Description
This procedure enables monitoring to determine the amount of time spent in each component of the workflow of an AWT cache group. To display the monitoring results, use the ttRepAdmin utility with the -awtmoninfo and -showstatus commands.
If the replication agent is restarted, monitoring is turned off.
Setting the monitoring state to OFF resets the internal counters of the monitoring tool.
Required privilege
This procedure requires the CACHE_MANAGER privilege.
Syntax
ttCacheAWTMonitorConfig ('state', samplingFrequency)
Parameters
ttCacheAWTMonitorConfig has the parameters:
Parameter | Type | Description |
---|---|---|
state |
TT_CHAR(10) | Enables and disables AWT monitoring. Its value can be ON or OFF . Default is OFF |
samplingFactor |
TT_INTEGER | Positive integer that specifies the frequency with which the AWT workflow is sampled. If samplingFactor is set to 1 , every AWT operation is monitored. Greater values indicate less frequent sampling. The value recommended for accuracy and performance is 16 . If state is set to ON , the default for samplingFactor is 16. If state is set to OFF , the default for samplingFactor is 0 . |
Result set
ttCacheAWTMonitorConfig returns the following result if you do not specify any parameters. It returns an error if the replication agent is not running or if an AWT cache group has not been created.
Column | Type | Description |
---|---|---|
State |
TTVARCHAR (10) NOT NULL | Current state of AWT monitoring. The value can be ON or OFF . |
AWTSamplingFactor | TT_INTEGER NOT NULL | Positive integer that specifies the frequency with which the AWT workflow is sampled. |
Examples
Example 2-1
Retrieve the current state and sampling factor when monitoring is disabled.
Command> CALL ttCacheAwtMonitorConfig;< OFF, 0 >1 row found.
Example 2-2
Enable monitoring and set the sampling frequency to 16.
Command> CALL ttCacheAwtMonitorConfig ('ON', 16);< ON, 16 >1 row found.
Example 2-3
Disable monitoring.
Command> CALL ttCacheAwtMonitorConfig; ('OFF')< OFF, 0 >1 row found.
See also
Description
This procedure returns the current transaction log file threshold for data stores that include AWT cache groups.
Required privilege
This procedure requires no privilege.
Syntax
ttCacheAWTThresholdGet()
Parameters
ttCacheAWTThresholdGet has no parameters.
Result set
ttCacheAWTThresholdGet returns the result:
Column | Type | Description |
---|---|---|
threshold |
TT_INTEGER NOT NULL | The number of transaction log files for all AWT cache groups associated with the data store. If 0, there is no set limit. |
Example
CALL ttCacheAWTThresholdGet();
Note
This procedure is available only for IMDB Cache.
See also
Description
This procedure sets the threshold for the number of transaction log files that can accumulate before AWT is considered either dead or too far behind to catch up. This setting applies to all subscribers to the data store. When the threshold is exceeded, updates are no longer sent to Oracle. If no threshold is set then the default is zero.
Using this built-in procedure, the threshold can be set after an AWT cache group has been created.
This setting can be overwritten by a CREATE REPLICATION statement that resets the Log Failure Threshold for the data store.
This procedure requires the CACHE_MANAGER privilege.
Syntax
ttCacheAWTThresholdSet(threshold)
Parameters
ttCacheAWTThresholdSet has the parameter:
Parameter | Type | Description |
---|---|---|
threshold |
TT_INTEGER | Specifies the number of transaction log files for all AWT cache groups associated with the data store. If the threshold is NULL, the log failure threshold is set to zero. |
Result set
ttCacheAWTThresholdSet returns no results.
Example
To set the threshold to allow 12 transaction log files to accumulate, use:
CALL ttCacheAWTThresholdSet(12);
Notes
This procedure is available for Oracle In-Memory Database Cache.
The user is responsible to recover when the threshold is exceeded.
See also
Description
For all cache groups that cache data from the same Oracle instance, this procedure specifies a timeout value and recovery policies in the case that the Oracle Server is unreachable and the cache agent or data store is considered dead.
The automatic refresh state of the data store and cache groups can be determined from the procedure ttCacheDbCgStatus.
This procedure requires the CACHE_MANAGER privilege.
Syntax
ttCacheConfig(Param, tblOwner, tblName, Value)
Parameters
ttCacheConfig has these parameters:
Parameter | Type | Description |
---|---|---|
Param |
VARCHAR2(50) NOT NULL | Specifies the parameter to be set by Value :
|
tblOwner |
VARCHAR2(30) | Specifies the owner of the cached Oracle table.
This parameter is required if A synonym cannot be used to specify a table name. |
tblName |
VARCHAR2(30) | Specifies the name of the cached Oracle table.
This parameter is required if Using a synonym to specify a table name is not supported. |
Value |
VARCHAR2(200) | Specifies the value to be set for Param .
|
Result set
ttCacheConfig returns no results when it is used to set parameter values. When it is used to return parameter settings, it returns these results:
Column | Type | Value |
---|---|---|
Param |
VARCHAR2(50) | Parameter name:
|
tblOwner |
VARCHAR2(30) | Owner of the cached Oracle table. |
tblName |
VARCHAR2(30) | Name of the cached oracle table.
Using a synonym to specify a table name is not supported. |
Value |
VARCHAR2(200) | Specifies the value set for Param .
|
Examples
To set the cache agent timeout to 600 seconds (10 minutes), enter:
CALL ttCacheConfig('AgentTimeout',,,'600');
To determine the current cache agent timeout setting, enter:
CALL ttCacheConfig('AgentTimeout'); < AgentTimeout, <NULL>, <NULL>, 600 > 1 row found.
To set the recovery method to Manual
for cache groups whose automatic refresh status is dead
, enter:
CALL ttCacheconfig('DeadDbRecovery',,,'Manual');
Configure the IMDB cache to prevent an automatic full refresh and receive an Oracle error when there is an update on a cached Oracle table while the cache administration user's tablespace is full. The Oracle table is terry.customer
.
CALL ttCacheConfig('TblSpaceFullRecovery','terry','customer','None');
To determine the current setting for TblSpaceFullRecovery
on the terry.customer
cached Oracle table, enter:
CALL ttCacheConfig('TblSpaceFullRecovery','terry','customer'); < TblSpaceFullRecovery, TERRY, CUSTOMER, none > 1 row found.
To configure a warning to be returned when the cache administration user's tablespace is 85 percent full and an update operation occurs on the cached Oracle table, enter:
CALL ttCacheConfig('TblSpaceThreshold',,,'85');
Note
This procedure is available only for IMDB Cache.
See also
Description
This procedure returns the automatic refresh status of the data store and the specified cache group. If you do not specify any values for the parameters, the procedure returns the automatic refresh status for the data store.
This procedure requires no privilege.
Syntax
ttCacheDbCgStatus([cgowner, cgName])
Parameters
ttCacheDbCgStatus has these optional parameters:
Parameter | Type | Description |
---|---|---|
cgOwner |
VARCHAR2(30) | Specifies the user name of the cache group owner. |
cgName |
VARCHAR2(30) | Specifies the cache group name. |
Result set
ttCacheDbCgStatus returns the result:
Column | Type | Value |
---|---|---|
dbStatus |
VARCHAR2(20) | Specifies the status of all the cache groups in the data store with respect to autorefresh. The status is one of:
|
cgStatus |
VARCHAR2(20) | Specifies the autorefresh status of the specified cache group. The status is one of:
|
Examples
This example shows that the automatic refresh status of the data store is alive
. The automatic refresh status of the cache group is ok
.
CALL ttCacheDbCgStatus ('terry', 'cgemployees'); < alive, ok > 1 row found.
To determine the automatic refresh status of the data store, call ttCacheDbCgStatus
with no parameters:
CALL ttCacheDbCgStatus; < dead, <NULL> > 1 row found.
Note
This procedure is available only for IMDB Cache.
See also
This procedure enables or disables tracking of DDL statements issued on cached Oracle tables. By default, DDL statements are not tracked.
DDL tracking saves the change history for all the cached Oracle tables. The SQL statement and when it was executed are written to a table in the cache administration user schema on Oracle. One DDL tracking table is created to store DDL statements issued on any cached Oracle table. This information can be used to diagnose autorefresh problems.
See "Tracking DDL statements issued on cached Oracle tables" in Oracle In-Memory Database Cache User's Guide.
Required privilege
This procedure requires the CACHE_MANAGER privilege.
Syntax
ttCacheDDLTrackingConfig('trackingStatus')
Parameters
ttCacheDDLTrackingConfig has the parameter:
Parameter | Type | Description |
---|---|---|
trackingStatus |
TT_VARCHAR(10) | Specifies whether DDL statements issued on cached Oracle tables are tracked. Valid values are:
|
Result set
ttCacheDDLTrackingConfig returns no results.
Examples
Command> CALL ttCacheDDLTrackingConfig('enable');
Description
This procedure returns the current policy used to determine when the TimesTen cache agent for the connected data store should run. The policy can be either always
or manual
.
This procedure requires no privilege.
Syntax
ttCachePolicyGet()
Parameters
ttCachePolicyGet has no parameters.
Result set
ttCachePolicyGet returns the result:
Column | Type | Value |
---|---|---|
cachePolicy |
TT_VARCHAR(10) | Specifies the policy used to determine when the TimesTen cache agent for the data store should run. Valid values are:
|
Examples
To get the current policy for the TimesTen cache agent, use:
CALL ttCachePolicyGet ();
Note
This procedure is available only for IMDB Cache.
See also
Description
The procedure defines the policy used to determine when the TimesTen cache agent for the connected data store should run. The policy can be either always
or manual
.
This procedure requires the CACHE_MANAGER privilege.
Syntax
ttCachePolicySet('cachePolicy')
Parameters
ttCachePolicySet has these parameters:
Parameter | Type | Description |
---|---|---|
cachePolicy |
TT_VARCHAR(10) NOT NULL | Specifies the policy used to determine when the TimesTen cache agent for the data store should run. Valid values are:
|
Result set
ttCachePolicySet returns no results.
Examples
To set the policy for TimesTen cache agent to always
, use:
CALL ttCachePolicySet ('always');
Notes
This procedure is available only for IMDB Cache.
If you attempt to start the TimesTen cache agent (by changing the policy from manual
to always
) for a data store with a relative path, TimesTen looks for the data store relative to where TimesTen Data Manager is running, and fails. For example, on Windows, if you specify the path for the data store as DataStore=./payroll
and attempt to start the TimesTen cache agent with this built-in procedure, the agent is not started because TimesTen Data Manager looks for the data store in the install_dir
\srv
directory. On UNIX, TimesTen Data Manager looks in the /var/TimesTen/
instance
directory.
Successfully setting the policy to always
automatically starts the cache agent if it was stopped.
See also
Description
This procedure allows you to temporarily stop any updates from propagating to Oracle.
This procedure requires the CACHE_MANAGER privilege.
Syntax
ttCachePropagateFlagSet(CommitsOn)
Parameters
ttCachePropagateFlagSet has the parameter:
Parameter | Type | Description |
---|---|---|
CommitsOn |
TT_INTEGER NOT NULL | If 0, sets a flag to stop updates from being sent to Oracle. The flag remains set until the end of the transaction or until the procedure is set to 1.
If 1, updates are sent to Oracle. |
Result set
ttCachePropagateFlagSet returns no results.
Notes
This procedure is available only for IMDB Cache.
When using this procedure, it is important to turn off AutoCommit, otherwise after the procedure is called the transaction ends and propagation to Oracle is turned back on.
The propagate flag is reset after a commit or rollback.
If the value of ttCachePropagateFlagSet is re-enabled several times during a single transaction, the transaction is only partially propagated to Oracle.
ttCachePropagateFlagSet is the only built-in procedure that can be used in the same transaction as any of the other cache group operations, such as FLUSH, LOAD, REFRESH and UNLOAD.
Description
This procedure generates the Oracle SQL statements to install or uninstall Oracle objects for:
Read-only cache groups
User managed cache groups with incremental autorefresh
Asynchronous writethrough (AWT) cache groups
This is useful when the user creating the cache group does not have adequate privilege to write on the Oracle database. The Oracle DBA can then use the script generated by this built-in procedure to create the Oracle objects.
This procedure requires the CACHE_MANAGER privilege.
Syntax
ttCacheSqlGet('feature_name', 'cache_group_name', install_flag)
Parameters
ttCacheSqlGet has these parameters:
Parameter | Type | Description |
---|---|---|
feature_name |
TT_VARCHAR (100) | Can be specified as INCREMENTAL_AUTOREFRESH or ASYNCHRONOUS_WRITETHROUGH. |
cache_group_name |
TT_VARCHAR (100) | The name of the cache group. Specify NULL when installing objects for ASYNCHRONOUS WRITETHROUGH cache groups or to uninstall all Oracle objects in the AUTOREFRESH user's account. |
install_flag |
TT_INTEGER NOT NULL | If install_flag is 1, ttCacheSqlGet returns Oracle SQL to install the autorefresh or asynchronous writethrough Oracle objects.
If |
Result set
ttCacheSqlGet returns the result set:
Column | Type | Description |
---|---|---|
retval |
TT_VARCHAR (4096) NOT NULL | The Oracle SQL statement to uninstall or install autorefresh or asynchronous writethrough Oracle objects. |
continueFlag |
TT_SMALLINT NOT NULL | Non zero only if the Oracle SQL statement in the retval result column exceeds 4096 bytes and must be continued into the next result row. |
Example
CALL ttCacheSqlGet('INCREMENTAL_AUTOREFRESH', 'westernCustomers', 1);
To remove all Oracle objects in the AUTOREFRESH user's account, use:
CALL ttCacheSqlGet('INCREMENTAL_AUTOREFRESH', NULL, 0);
Notes
This procedure is available only for IMDB Cache.
Each returned retval
field contains a separate Oracle SQL statement that may be directly executed on Oracle. A row may end in the middle of a statement, as indicated by the continueFlag
field. In this case, the statement must be concatenated with the previous row to produce a usable SQL statement.
The script output of this procedure is not compatible with Oracle's SQL*Plus utility. However, you can use the ttIsql cachesqlget
command to generate a script that is compatible with the SQL*Plus utility.
You can specify NULL for the cache_group_name
option to generate Oracle SQL to clean up Oracle objects after a data store has been destroyed by the ttDestroy utility.
Description
This procedure starts the TimesTen cache agent for the connected data store.
This procedure requires the CACHE_MANAGER privilege.
Syntax
ttCacheStart()
Parameters
ttCacheStart has no parameters.
Result set
ttCacheStart returns no results.
Examples
To start the TimesTen cache agent, use:
CALL ttCacheStart ();
Notes
This procedure is available only for IMDB Cache.
The cache administration user ID and password must be set before starting the cache agent when there are or might be AUTOREFRESH or ASYNCHRONOUS WRITETHROUGH cache groups in the data store.
If you attempt to start the TimesTen cache agent (by changing the policy from manual to always) for a data store with a relative path, TimesTen looks for the data store relative to where the TimesTen Data Manager is running, and fails. For example, on Windows, if you specify the path for the data store as DataStore=./payrol
l and attempt to start the TimesTen cache agent with this built-in procedure, the agent is not started because TimesTen Data Manager looks for the data store in the \srv
directory. On UNIX, the TimesTen Data Manager looks in the /var/TimesTen/
instance
directory.
When using this procedure, no application, including the application making the call, can be holding a connection that specifies data store-level locking (LockLevel=1).
See also
Description
This procedure stops the TimesTen cache agent for the connected data store.
This procedure requires the CACHE_MANAGER privilege.
Syntax
ttCacheStop(timeout)
Parameters
ttCacheStop has the parameter:
Parameter | Type | Description |
---|---|---|
timeout |
TT_INTEGER | Specifies that the TimesTen daemon should kill the cache agent if it doesn't stop within timeout seconds. If set to 0, the daemon waits forever for the cache agent. The default value is 100 seconds. |
Result set
ttCacheStop returns no results.
Examples
To stop the TimesTen cache agent, use:
CALL ttCacheStop();
Notes
This procedure is available only for IMDB Cache.
Do not shut down the cache agent immediately after dropping or altering a cache group. Instead, wait for at least two minutes. Otherwise, the cache agent may not get a chance to clean up the Oracle objects that were used by the AUTOREFRESH feature.
When using this procedure, no application, including the application making the call, can be holding a connection that specifies data store-level locking (LockLevel=1).
See also
Description
This procedure returns the cache administration user ID for the data store. If the cache administration user ID and password have not been set for the data store, ttCacheUidGet returns NULL.
This procedure requires CACHE_MANAGER privilege.
Syntax
ttCacheUidGet()
Parameters
ttCacheUidGet has no parameters.
Result set
ttCacheUidGet returns the results:
Column | Type | Description |
---|---|---|
UID |
TT_VARCHAR (30) | The current cache administration user ID, used for AUTOREFRESH and ASYNCHRONOUS WRITETHROUGH cache groups. |
Example
CALL ttCacheUidGet();
Note
This procedure is available only for IMDB Cache.
See also
Description
This procedure sets the cache administration user ID and password. The cache administration user ID and password only need to be specified once for each new data store. The cache administration password can be changed at any time.
This procedure requires the CACHE_MANAGER privilege.
Syntax
ttCacheUidPwdSet('UID', 'PWD')
Parameters
ttCacheUidPwdSet has these parameters:
Parameter | Type | Description |
---|---|---|
UID |
TT_VARCHAR (30) | The cache administration user ID, used for AUTOREFRESH and ASYNCHRONOUS WRITETHROUGH cache groups. |
PWD |
TT_VARCHAR (30) | The password for the cache administration user. |
Result set
ttCacheUidPwdSet returns no results.
Example
CALL ttCacheUidPwdSet('myid', 'mypwd');
Notes
This procedure cannot be called from a Client/Server connection.
This procedure is available only for IMDB Cache.
The cache administration user ID and password cannot be set while either the cache agent or the replication agent are running.
The cache administration user ID cannot be reset while there are ASYNCHRONOUS WRITETHROUGH cache groups or AUTOREFRESH cache groups (with a state that is not equal to OFF) on the data store.
See also
Description
This procedure performs a non-blocking checkpoint operation. The blocking checkpoints are described in "ttCkptBlocking". A checkpoint operation is used to make a record of the current state of the data store on disk and to purge transaction log files. A non-blocking checkpoint does not require any locks on the data store.
Applications should checkpoint data stores periodically either by setting the background checkpointing attributes (CkptFrequency and CkptLogVolume) or by explicitly calling this procedure.
By default, TimesTen performs background checkpoints at regular intervals.
In the case that your application attempts to perform a checkpoint operation while a backup is in process, the backup waits until the checkpoint finishes. Regardless of whether the checkpoint is a background checkpoint or an application-requested checkpoint, the behavior is:
If a backup or checkpoint is running and you try to do a backup, it waits for the running backup or checkpoint to finish.
If a backup or checkpoint is running and you try to do a checkpoint, it does not wait. It returns an error immediately.
To turn off background checkpointing, set CkptFrequency=0 and CkptLogVolume=0.
This procedure can be called asynchronously to any other application running on the data store.
When a data store crashes, and the checkpoints on disk are non-blocking checkpoints, TimesTen uses the log to recover.
This procedure requires the ADMIN privilege.
Syntax
ttCkpt()
Parameters
ttCkpt has these optional parameters:
Parameter | Type | Description |
---|---|---|
timeout |
TT_INTEGER | The time (in seconds) that ttCkpt should wait to get a data store lock before timing out. The value of timeout can be between 0 and one million, inclusively. If not specified, it defaults to infinity (the checkpoint never times out). |
retries |
TT_INTEGER | The number of times that ttCkpt should attempt to get a data store lock, if timeouts occur. The value of retries can be between 0 and 10, inclusive. If not specified, defaults to zero. |
Result set
ttCkpt returns no results.
Example
CALL ttCkpt();
Note
For a description of checkpoints, see "Transaction Management and Recovery" in Oracle TimesTen In-Memory Database Operations Guide.
See also
Description
This procedure performs a blocking checkpoint operation. A checkpoint operation is used to make a record of the current state of the data store on disk, and to purge transaction log files. This checkpoint requires exclusive access to the data store, and so may cause other applications to be blocked from the data store while the checkpoint is in progress.
When this procedure is called, TimesTen performs a blocking checkpoint when the current transaction is committed or rolled back. If, at that time, other transactions are in progress, the checkpointing connection waits until the other transactions have committed or rolled back. While the checkpoint connection is waiting, any other new transactions that want to start form a queue behind the checkpointing transaction. As a result, if any transaction is long-running, it may cause many other transactions to be held up. So this blocking checkpoint should be used with caution. To perform a non-blocking checkpoint, use the ttCkpt procedure.
No log is needed to recover when blocking checkpoints are used. TimesTen uses the log, if present, to bring the data store up to date after recovery.
This procedure requires the ADMIN privilege.
Syntax
ttCkptBlocking(timeout, retries)
Parameters
ttCkptBlocking has these optional parameters:
Parameter | Type | Description |
---|---|---|
timeout |
TT_INTEGER | The time (in seconds) that ttCkptBlocking should wait to get a data store lock before timing out. The value of timeout can be between 0 and one million, inclusively. If not specified, it defaults to infinity (the checkpoint never times out). |
retries |
TT_INTEGER | The number of times that ttCkptBlocking should attempt to get a data store lock, if timeouts occur. The value of retries can be between 0 and 10, inclusive. If not specified, defaults to zero. |
Result set
ttCkptBlocking returns no results.
Example
CALL ttCkptBlocking(); CALL ttCkptBlocking(1,10);
Notes
Because the checkpoint takes place at commit or rollback, the call to ttCkptBlocking always succeed. At commit or rollback, any problems with the checkpoint operation, such as a lack of disk space or a timeout, result in a warning being returned to the application. Checkpoint problems are not reflected as errors, since the commit or rollback of which they are a part can succeed even if the checkpoint fails. Warnings are reflected in ODBC with the return code SQL_SUCCESS_WITH_INFO.
For more information on checkpoints, see "Transaction Management and Recovery" in Oracle TimesTen In-Memory Database Operations Guide.
See also
Description
This procedure reconfigures the background checkpointer dynamically or returns the currently active settings of the configuration parameters. Changes made using ttCkptConfig become effective immediately. Thus, changes to ckptRate
can take effect on a checkpoint that is currently in progress.
Changes made to the background checkpointer using ttCkptConfig are persistent. Subsequent loads of the data store retain the new settings, unless the CkptFrequency and CkptLogVolume connection attributes are specified in the DSN or connection string, in which case the attribute values are used instead.
Required privilege
This procedure requires no privilege to query the current values. It requires the ADMIN privilege to change the current values.
Syntax
ttCkptConfig(ckptFrequency, ckptLogVolume, ckptRate)
Parameters
ttCkptConfig has these parameters:
Parameter | Type | Description |
---|---|---|
ckptFrequency |
TT_INTEGER | Checkpoint frequency in seconds. Values from 0 to MAXINT are allowed. A value of 0 means that checkpoint frequency is not considered when scheduling checkpoints. |
ckptLogVolume |
TT_INTEGER | Log volume between checkpoints in megabytes. Values from 0 to MAXINT are allowed. A value of 0 means that checkpoint log volume is not considered when scheduling checkpoints. |
ckptRate |
TT_INTEGER | Specifies the rate in MB per second at which a checkpoint should be written to disk. A value of 0 indicates that the rate should not be limited, a value of NULL means that the rate should be left unchanged. Changes to this parameter take effect even on a checkpoint that is currently in-progress. |
Result set
ttCkptConfig returns these results:
Column | Type | Description |
---|---|---|
ckptFrequency |
TT_INTEGER NOT NULL | Currently active setting for checkpoint frequency in seconds. |
ckptLogVolume |
TT_INTEGER NOT NULL | Currently active setting for log volume between checkpoints in Megabytes. |
ckptRate |
TT_INTEGER NOT NULL | Current rate at which checkpoints are written to disk. |
Examples
To view the current settings of the background checkpointer configuration parameters, use:
CALL ttCkptConfig; < 600, 32, 0 > 1 row found.
To stop the background checkpointer from initiating checkpoints unless the log reaches its limit, use:
CALL ttCkptConfig(0); < 0, 32, 0 > 1 row found.
To stop the background checkpointer from initiating checkpoints, use:
CALL ttCkptConfig(NULL, 0); < 0, 0, 0 > 1 row found.
To set the background checkpointer configuration to initiate a checkpoint every 600 seconds or to checkpoint when the log reaches 32 megabytes (whichever comes first), use:
CALL ttCkptConfig(600, 32); < 600, 32, 0 > 1 row found.
Notes
By default, TimesTen performs background checkpoints at regular intervals.
In the case that your application attempts to perform a checkpoint operation while a backup is in process, the backup waits until the checkpoint finishes. Regardless of whether the checkpoint is a background checkpoint or an application-requested checkpoint, the behavior is:
If a backup or checkpoint is running and you try to do a backup, it waits for the running backup or checkpoint to finish.
If a backup or checkpoint is running and you try to do a checkpoint, it does not wait. It returns an error immediately.
To turn off background checkpointing, set CkptFrequency=0 and CkptLogVolume=0.
See also
Description
This procedure returns information about the last eight checkpoints of any type taken by any agent.
Required privilege
This procedure requires no privilege.
Syntax
ttCkptHistory( )
Parameters
ttCkptHistory has no parameters.
Result set
ttCkptHistory returns the result set:
Column | Type | Description |
---|---|---|
startTime |
TT_TIMESTAMP NOT NULL | Time when the checkpoint was begun. |
endTime |
TT_TIMESTAMP | Time when the checkpoint completed. |
type |
TT_CHAR (16) NOT NULL | The type of checkpoint taken. Value is one of:
Static - Automatically taken at data store creation and at last disconnect. Blocking - Transaction-consistent checkpoint. Fuzzy - Non-blocking checkpoint. The background checkpointer performs this type if possible. None - For Temporary data stores, which have no checkpoint files. |
status |
TT_CHAR (16) NOT NULL | Result status of the checkpoint operation. Value is one of:
In Progress - The checkpoint is currently in progress. Only the most recent result row can have this status. Completed - The checkpoint completed successfully. Failed - The checkpoint failed. Only the most recent result row can have this status. In this case the error column indicates the reason for the failure. |
initiator |
TT_CHAR (16) NOT NULL | The source of the checkpoint request. Value is one of:
User - A user-level application. This includes TimesTen utilities such as ttIsql. Checkpointer - The background checkpointer. Subdaemon - The managing subdaemon of the data store. For a shared data store, the final disconnect checkpoint is taken by the subdaemon. |
error |
TT_INTEGER | In the case of a Failed checkpoint, this column indicates the reason for the failure. The value is one of the TimesTen error numbers. |
ckptFileNum |
TT_INTEGER NOT NULL | The data store file number used by the checkpoint. This corresponds to the number in the checkpoint file extension datastore .ds0 or datastore .ds1. |
ckptLFN |
TT_INTEGER | The transaction log file number of the checkpoint log record. |
ckptLFO |
TT_INTEGER | The transaction log file offset of the checkpoint log record. |
blksTotal |
TT_BIGINT | The number of permanent blocks currently allocated in the data store. These blocks are subject to consideration for checkpointing. |
bytesTotal |
TT_BIGINT | The number of bytes occupied by blksTotal . |
blksInUse |
TT_BIGINT | Of blksTotal , the number of blocks currently in use. |
bytesInUse |
TT_BIGINT | The number of bytes occupied by blksInUse . |
blksDirty |
TT_BIGINT | The number of dirty blocks written by this checkpoint. |
bytesDirty |
TT_BIGINT | The number of bytes occupied by blksDirty . |
bytesWritten |
TT_BIGINT | The total number of bytes written by this checkpoint. |
Percent_Complete |
TT_INTEGER | If there is an in-progress checkpoint, indicates the percentage of the checkpoint that has been completed. If no checkpoint is in-progress, the value is NULL. The returned value is calculated by comparing the block ID of the last-written block against the data store's PermSize. The value does not necessarily indicate the precise time remaining to complete the checkpoint, although it does give some indication of the remaining time needed to complete the disk write. The field shows only the progress of the writing of dirty blocks and does not include additional bookkeeping at the end of the checkpoint. |
Examples
CALL ttCkptHistory; < 2005-03-15 16:15:36.000000, 1753-01-01 00:00:00.000000, Fuzzy, In Progress, Checkpointer, 0, 0, -1, -1, 0, 0, 0, 0, 0, 0, 0 > < 2005-03-15 16:14:36.000000, 2005-03-15 16:14:37.000000, Fuzzy, Completed, Checkpointer, 0, 1, 0, 357768, 22, 2097152, 21, 980720, 11, 748960, 822496 > < 2005-03-15 16:13:36.000000, 2005-03-15 16:13:38.000000, Blocking, Completed, User, 0, 0, 0, 357712, 22, 2097152, 21, 980720, 10, 683800, 1506296 > < 2005-03-15 16:13:00.000000, 2005-03-15 16:13:01.000000, Static, Completed, Subdaemon, 0, 1, 0, 357616, 22, 2097152, 21, 980720, 10, 683800, 683800 > < 2005-03-15 16:12:56.000000, 2005-03-15 16:12:57.000000, Fuzzy, Completed, User, 0, 0, 0, 357520, 22, 2097152, 21, 980720, 2, 92736, 134368 > < 2005-03-15 16:12:36.000000, 2005-03-15 16:12:38.000000, Fuzzy, Completed, Checkpointer, 0, 1, 0, 357424, 22, 2097152, 21, 980720, 10, 683800, 789728 > < 2005-03-15 16:11:36.000000, 2005-03-15 16:11:39.000000, Static, Completed, User, 0, 0, 0, 357368, 22, 2097152, 21, 980720, 22, 2097152, 980864 > < 2005-03-15 16:11:31.000000, 2005-03-15 16:11:36.000000, Static, Completed, User, 0, 1, 0, 357312, 22, 2097152, 21, 980720, 22, 2097152, 980864 > CALL ttCkptHistory; < 2005-03-15 17:03:43.000000, 2005-03-15 17:03:43.000000, Fuzzy, Failed, Checkpointer, 847, 1, -1, -1, 0, 0, 0, 0, 0, 0, 0 > < 2005-03-15 17:02:43.000000, 2005-03-15 17:02:44.000000, Static, Completed, Subdaemon, 0, 0, 0, 362704, 22, 2097152, 21, 980720, 10, 683800, 683800 >
Notes
Results are ordered by start time, with the most recent first.
A failed row is overwritten by the next checkpoint attempt.
See also
Description
This procedure compacts both the permanent and temporary data partitions of the data store.
Call ttCompact periodically to reorganize the internal structure of a data store. It may also be useful to call ttCompact when the application receives out of memory errors because the cause of the problem may be data store fragmentation.
ttCompact merges adjacent blocks of free space, but does not move any items that are allocated. Therefore, fragmentation that is caused by small unallocated blocks of memory surrounded by allocated blocks of memory is not eliminated by using ttCompact.
This procedure requires the ADMIN privilege.
Syntax
ttCompact()
Parameters
ttCompact has no parameters.
Result set
ttCompact returns no results.
Example
CALL ttCompact;
Note
Compacting data does not modify result addresses.
See also
Description
This procedure is similar to ttCompact, except that ttCompactTS may be used to compact a small fraction of the data store, while ttCompact compacts the entire data store. ttCompactTS is a time-sliced version of ttCompact. ttCompactTS iterates through all the blocks in the data store compacting the quantum specified each time. ttCompactTS called repeatedly ultimately has the same effect as a call to ttCompact. When a sweep is completed, the value of the DS_COMPACTS field in the MONITOR table is incremented.
This procedure requires the ADMIN privilege.
Syntax
ttCompactTS(quantum)
Parameters
ttCompactTS has the parameter:
Parameter | Type | Description |
---|---|---|
quantum |
TT_INTEGER NOT NULL | A non-zero positive integer that specifies the number of data blocks a ttCompactTS should compact. Each quantum corresponds to one data block. |
Result set
ttCompactTS returns no results.
Example
CALL ttCompactTS (5);
Note
Compacting data does not modify result addresses.
See also
Description
This procedure the attribute values for the current database connection.
This procedure requires no privilege.
Syntax
ttConfiguration()
Parameters
ttConfiguration has the parameter:
Parameter | Type | Description |
---|---|---|
paramname |
TT_VARCHAR (30) | The name of a connection attribute for which you want this procedure to return the value. |
Result set
ttConfiguration returns the result set:
Column | Type | Description |
---|---|---|
ParameterName |
TT_VARCHAR (30) NOT NULL | The names of the connection attributes specified in the connection string, returned in alphabetical order. |
ParameterValue |
TT_VARCHAR (1024) | The values of the connection attributes specified in the connection string. |
Example
CALL ttConfiguration();
Note
Client driver attributes are not returned by this procedure.
See also
Description
This procedure returns the context value of the current connection as a BINARY(8) value. The context can be used to correlate a unique connection to a data store from the list of connections presented by the ttStatus utility and the ttDataStoreStatus built-in procedure.
This procedure requires no privilege.
Syntax
ttContext()
Parameters
ttContext has no parameters.
Result set
ttContext returns the result set:
Column | Type | Description |
---|---|---|
CONTEXT |
Binary(8) | Current connection's context value. |
Example
CALL ttContext;
Note
The context value numbers are unique only within a process. The context value number is not unique within the entire data store. Therefore you may see the same context value number for different processes.
See also
Description
This procedure returns the list of processes connected to a data store. If the dataStore
parameter is specified as NULL, then the status of all active data stores is returned.
The result set is similar to the printed output of the ttStatus utility.
This procedure requires no privilege.
Syntax
ttDataStoreStatus('dataStore')
Parameters
ttDataStoreStatus has the parameter:
Parameter | Type | Description |
---|---|---|
dataStore |
TT_VARCHAR (256) | Full path name of desired data store or NULL for all data stores. |
Result set
ttDataStoreStatus returns the result set:
Column | Type | Description |
---|---|---|
dataStore |
TT_VARCHAR (256) NOT NULL | Full path name of data store. |
PID |
TT_INTEGER NOT NULL | Process ID. |
Context |
BINARY(8) NOT NULL | Context value of connection. |
conType |
TT_CHAR (16) NOT NULL | Type of process connected. The result can be one of the following:
application - an ordinary application is connected. replication - a replication agent is connected. subdaemon - a subdaemon is connected. oracleagent - an cache agent is connected. |
ShmID |
TT_VARCHAR (260) NOT NULL | A printable version of the shared memory ID that the data store occupies. |
connection_Name |
TT_CHAR (30) NOT NULL | The symbolic name of the data store connection. |
connID |
TT_INTEGER NOT NULL | The numeric ID of the data store connection |
Example
CALL ttDataStoreStatus('/data/Purchasing');
See also
Description
This procedure specifies that the current transaction should be made durable when it is committed. It only has an effect if the application is connected to the data store with DurableCommits disabled and logging to disk enabled. (See "Logging".)
Calling ttDurableCommit also makes the current transaction and any previously committed non-durable transactions durable. There is no effect on other transactions that are committed subsequent to calling ttDurableCommit. ttDurableCommit does not commit transactions. The application must do the commit, for example with a call to SQLTransact
.
This procedure requires no privilege.
Syntax
ttDurableCommit()
Parameters
ttDurableCommit has no parameters.
Result set
ttDurableCommit returns no results.
Example
CALL ttDurableCommit;
Note
Some controllers or drivers may only write data into cache memory in the controller or may write to disk some time after the operating system is told that the write is done. In these cases, a power failure may mean that some information you thought was durably committed does not survive the power failure. To avoid this loss of data, configure your disk to write all the way to the recording media before reporting completion or you can use an Uninterruptable Power Supply.
Description
This procedure attaches a grid member to an existing cache grid. A grid member can be a standalone TimesTen database or a TimesTen active standby pair.
If a member is an active standby pair, both nodes of the pair must attach to the grid. When calling the ttGridAttach
built-in procedure from each node of the active standby pair, specify the IP address or host name of both nodes.
This procedure starts the cache agent if it is not already running.
This procedure requires the CACHE_MANAGER privilege.
For a standalone TimesTen database:
ttGridAttach(currentNode, 'name1', IPAddr1, port1)
For a node of an active standby pair:
ttGridAttach(currentNode, 'name1', IPAddr1, port1 'name2', IPAddr2, port2)
Parameters
ttGridAttach has the parameters:
Parameter | Type | Description |
---|---|---|
currentNode |
TT_INTEGER NOT NULL | The node number for the master data store. Valid values for this parameter are:
1 - Standalone or active master data store 2 - Standby master data store. |
name1 |
TT_VARCAHR (30) | Fully qualified name that uniquely identifies the grid member for the active master data store. |
IPAddr1 |
TT_VARCHAR (128) NOT NULL | IP address of the node where the active master data store resides. |
port1 |
TT_INTEGER NOT NOLL | Port number for the cache agent process of the active master data store. |
name2 |
TT_VARCAHR (30) | Fully qualified name that uniquely identifies the grid member for the standby master data store. |
IPAddr2 |
TT_VARCHAR (128) NOT NULL | IP address of the node where the standby master data store resides. |
port2 |
TT_INTEGER NOT NOLL | Port number for the cache agent process of the standby master data store. |
Result set
ttGridAttach returns no results.
Examples
To attach to a standalone TimesTen data store to a grid:
CALL ttGridAttach (1, 'alone2','sys2',5002);
To attach an active master data store to a grid:
CALL ttGridAttach(1,'cacheact','sys1',5003,'cachestand','sys2',5004);
To attach a standby master data store to a grid:
CALL ttGridAttach(2,'cacheact','sys1',5003,'cachestand','sys2',5004);
Note that the only difference between the calls for attaching the active and the standby master stores is the node number.
See also
Description
This procedure checks if the number of rows in global cache groups match number of rows in the ownership tables. Call this procedure only when the cache grid is quiet.
This procedure requires the CACHE_MANAGER privilege.
Syntax
ttGridCheckOwner(['cvName', 'cvOwner'])
Parameters
ttGridCheckOwner has the optional parameter:
Parameter | Type | Description |
---|---|---|
cvName |
TT_VARCHAR (30) | The name of the cache group to be checked...name and owner of cache group that need to be checked. If null, all cache groups are checked. |
cvOwner |
TT_VARCHAR (30) | .The owner of the cache group to be checked. If null, all cache groups are checked. |
Result set
ttGridCheckOwner displays no results.
Example
To get information on the mygroup
cache group, owned by user terry, use:
CALL ttGridCheckOwner ('mygroup', 'terry');
To get information on all cache groups, use:
CALL ttGridCheckOwner();
See also
Description
This procedure creates a cache grid. This built-in procedure needs to be run only once. You can run it from any standalone data store or from the active or standby master data store in an active standby pair.
This procedure requires the CACHE_MANAGER privilege.
Syntax
ttGridCreate('gridName')
Parameters
ttGridCreate has the parameter:
Parameter | Type | Description |
---|---|---|
gridName |
TT_VARCHAR (30) NOT NULL | Specifies the name of the grid |
Result set
ttGridCreate returns no results.
Example
To create a grid named mygrid
:
CALL ttGridCreate ('mygrid');
See also
Description
This procedure destroys a cache grid by removing all cache grid objects stored on the Oracle database.
By default, this built-in procedure does not destroy the grid if there are still attached members or existing global cache groups. Before destroying a cache grid, detach all of the TimesTen data stores from the cache grid. To force the grid to be destroyed, supply a value of '1' as an argument to the force
parameter.
This procedure requires the CACHE_MANAGER privilege.
Syntax
ttGridDestroy('gridName', [force])
Parameters
ttGridDestroy has the parameters:
Parameter | Type | Description |
---|---|---|
gridName |
TT_VARCHAR (30) NOT NULL | The fully qualified name of the grid to be destroyed. |
force |
TT_INTEGER | This optional parameter forces the cache grid to be destroyed even if there are still grid members attached to the cache grid or if it still contains global cache groups. Valid value is 1. |
Result set
ttGridDestroy returns no results.
Example
To destroy the mygrid
cache grid with force, use:
CALL ttGridDestroy ('mygrid', 1);
See also
Description
This procedure detaches a node from a cache grid.
Use this procedure before destroying a cache grid. You cannot destroy a cache grid if there are any nodes attached to the cache grid.
This procedure requires the CACHE_MANAGER privilege.
Syntax
ttGridDetach(['nodeMemberName',] [force])
Parameters
ttGridDetach has the optional parameters:
Parameter | Type | Description |
---|---|---|
nodeMemberName |
TT_VARCHAR (200) | Specifies the node to detached from the grid.
Each node of an active standby pair must be detached separately. |
force |
TT_INTEGER | This optional parameter forces a node to be detached without checking whether it is dead. Valid value is 1. |
Result set
ttGridDetach returns no results.
Example
To detach the current node from the grid, use
CALL ttGridDetach();
To detach the remote node TTGRID_alone2_2
from the grid, use
CALL ttGridDetach('TTGRID_alone2_2',1);
See also
Description
This procedure detaches all attached members from the grid. A grid member can be a standalone TimesTen database or a TimesTen active standby pair.
This procedure starts the cache agent if it is not already running.
This procedure requires the CACHE_MANAGER privilege.
ttGridDetachAll()
Parameters
ttGridDetachAll has no parameters:
Result set
ttGridDetachAll returns no results.
Examples
To detach all grid members, use:
CALL ttGridDetachAll();
See also
Description
This procedure detaches the nodes in the list. It is useful for remote nodes, because they are unavailable.
Required privilege
This procedure requires the CACHE_MANAGER privilege.
Syntax
ttGridDetachList('nodeMemberName1 [nodeMemberName2 ...]' [,force])
Parameters
ttGridDetachList has the parameters:
Parameter | Type | Description |
---|---|---|
nodeMemberName |
TT_VARCHAR (8192) NOT NULL | The fully qualified name of the node to be removed |
force |
TT_INTEGER | This optional parameter forces nodes to be detached without checking whether they are dead. Valid value is 1. |
Result set
ttGridDetachList returns no results.
Example
CALL ttGridDetachList('TTGRID_cacheact_3A TTGRID_cachestand_3B',1);
See also
Description
This procedure resumes operations that were blocked after a call to ttGridGlobalCGSuspend. This procedure starts the cache agent if it is not already running.
This procedure requires the CACHE_MANAGER privilege.
ttGridGlobalCGResume()
Parameters
ttGridGlobalCGResume has no parameters.
Result set
ttGridGlobalCGResume returns no results.
Examples
To detach all grid members, use:
CALL ttGridGlobalCGResume();
See also
Description
This procedure temporarily blocks dynamic loading and deleting cache instances for global cache groups. This procedure starts the cache agent if it is not already running. Use the ttGridGlobalCGResume procedure to re-enable these actions.
This procedure requires the CACHE_MANAGER privilege.
ttGridGlobalCGSuspend(wait)
Parameters
ttGridGlobalCGSuspend has the parameter:
Parameter | Type | Description |
---|---|---|
wait |
TT_INTEGER NOT NULL | The number of seconds that the command waits for a pending delete to be propagated to Oracle or a pending transparent load operation to complete before returning.TimesTen returns an error if either the pending delete or the pending transparent load operation cannot complete in the specified time.
If no value is specified, there is no wait interval. |
Result set
ttGridGlobalCGSuspend returns no results.
Examples
To set a wait interval of 10 seconds:
CALL ttGridGlobalCGSuspend(10);
See also
Description
This procedure returns information about the specified cache grid or all cache grids.
This procedure requires the CACHE_MANAGER privilege.
Syntax
ttGridInfo(['gridName'])
Parameters
ttGridInfo has the optional parameter:
Parameter | Type | Description |
---|---|---|
gridName |
TT_VARCHAR (30) | If gridName is specified, displays information about the specified grid. Otherwise, displays information about all grids. |
Result set
ttGridInfo returns information about the cache grid.
Column | Type | Description |
---|---|---|
gridName |
TT_VARCHAR (30) | The name of the grid specified |
cacheAdminID |
TT_VARCHAR (30)
NOT NULL |
The cache administration user ID associated with the grid. |
platform |
TT_VARCHAR (100) | The operating system platform on which the grid is operating. |
major1, major2, major3 |
TT_VARCHAR (10) for each field | The major TimesTen release associated with the grid. For example, release 11.2.1 is represented as 11, 2, 1. |
Example
To get information on the mygrid
cache grid, use:
CALL ttGridInfo ('mygrid'); < MYGRID, CACHEUSER, Linux Intel x86, 32-bit, 11, 2, 1 >
To get information on all grids, use:
CALL ttGridInfo();
See also
Description
This procedure associates a TimesTen data store with a grid.
This procedure requires the CACHE_MANAGER privilege.
Syntax
ttGridNameSet('gridName')
Parameters
ttGridNameSet has the parameter:
Parameter | Type | Description |
---|---|---|
gridName |
TT_VARCHAR (30) | Associates the TimesTen data store that calls the procedure with the grid specified by gridName . |
Result set
ttGridNameSet returns no results.
Example
To associate the data store with the grid mygrid
, use.
CALL ttGridNameSet('mygrid');
See also
Description
This procedure returns information about all members of the specified cache grid. If no grid name is specified, then it displays information about all members of all cache grids associated with the Oracle database.
This procedure requires the CACHE_MANAGER privilege.
Syntax
ttGridNodeStatus(['gridName'])
Parameters
ttGridNodeStatus has the optional parameter:
Parameter | Type | Description |
---|---|---|
gridName |
TT_VARCHAR (30) | If gridName is specified, displays information about all members of the named grid. Otherwise, displays information about all grids. |
Result Set
ttGridNodeStatus returns the results:
Column | Type | Description |
---|---|---|
gridName |
TT_VARCHAR (30) | The name of the grid. |
nodeID |
TT_INTEGER NOT NULL | The ID of the node. |
activeNode |
TT_INTEGER NOT NULL | The number of the node on which the active master data store currently resides. |
node1Attached |
CHAR (1) NOT NULL | Indicates if the active node is attached to the grid:
T - the active is attached. F - the active is detached. |
Host1 |
TT_VARCHAR (200) NOT NULL | The host name where the active data store is located. |
memberName1 |
TT_VARCHAR (200) NOT NULL | Unique fully qualified grid member name for the active master data store. |
IPaddr1 |
TT_VARCHAR (128) NOT NULL | The IP address where the active master data store is located. |
port1 |
TT_INTEGER NOT NULL | The port number for the cache agent process of the active master data store. |
node2Attached |
CHAR (1) | Indicates if the standby node is attached to the grid:
T - the standby is attached. F - the standby is detached. |
host2 |
TT_VARCHAR (200) | The host name where the standby master data store is located. |
memberName2 |
TT_VARCHAR (200) | Unique fully qualified grid member name for the standby master data store. |
IPaddr2 |
TT_VARCHAR (128) | The IP address where the standby master data store is located. |
port2 |
TT_INTEGER | The port number for the cache agent process of the standby master data store. |
For a grid member that is a standalone database, the number of columns in the result set is fewer than for a member that is an active standby pair.
Example
If ttgrid
is the only cache grid in the data store, display information about its members:
Command> call ttGridNodeStatus; < TTGRID, 1, 1, T, sys1, TTGRID_alone1_1, 140.87.0.201, 5001, <NULL>, <NULL>,<NULL>, <NULL>, <NULL> > < TTGRID, 2, 1, T, sys2, TTGRID_alone2_2, 140.87.0.202, 5002, <NULL>, <NULL>,<NULL>, <NULL>, <NULL> > < TTGRID, 3, 1, T, sys3, TTGRID_cacheact_3A, 140.87.0.203, 5003, T, sys4, TTGRID_cachestand_3B, 140.87.0.204, 5004 >
See also
Description
This procedure returns the name of the current local host for the data store. The value returned is only for the current session. It is not a system-wide setting and does not persist after the current session has been disconnected.
This procedure can be used to check whether a particular store name in a scheme refers to the current host. This can be helpful when configuring replication schemes.
This procedure requires no privilege.
Syntax
ttHostnameGet()
Parameters
ttHostNameGet has no parameters.
Result set
ttHostNameGet returns the result:
Column | Type | Description |
---|---|---|
hostName |
TT_VARCHAR (200) | The current default local host setting for the data store. If a default has not been supplied then the current hostname is returned. |
Example
CALL ttHostNameGet ();
See also
Description
This procedure specifies the name of the default local host for the current data store. The value is only used in the current session, it is not a system-wide setting and does not persist after the current session has been disconnected.
To configure master/subscriber relationships and replication object permissions correctly, Replication DDL processing relies on being able to determine whether a host name used in a replication scheme refers to the machine on which the script is currently being run. This procedure allows an application to set a default host name for the current session that can be used by Replication DDL processing whenever there is a need to establish the name of the current host.
This procedure requires the ADMIN privilege.
Syntax
ttHostnameSet('hostName')
Parameters
ttHostNameSet has the parameter:
Parameter | Type | Description |
---|---|---|
hostName |
TT_VARCHAR (200) | The required default name for the local machine. If NULL is supplied the default value is cleared. |
Result set
ttHostNameSet returns no results.
Example
CALL ttHostNameSet ('alias1');
Note
The legal value of hostName can be any host name or IP address string except "localhost
", "127.0.0.1
" or "::1
". You cannot set the default host name to a value that is different from a local host name used in an existing replication scheme.
See also
Description
Changes the lock level between row-level and data store-level locking on the next transaction and for all subsequent transactions for this connection. Applications can change the lock level again by calling ttLockLevel once more. The initial value depends on the LockLevel attribute. See LockLevel for full details of the different locking levels.
This procedure requires the ADMIN privilege.
Syntax
ttLockLevel('lockLevel')
Parameters
ttLockLevel has the parameter:
Parameter | Type | Description |
---|---|---|
lockLevel |
TT_CHAR (20) NOT NULL | Locking level for the connection. |
The value of lockLevel
may be one of two case-insensitive strings:
Row
: Locking should be set to row-level locking.
DS
: Locking should be set to data store-level locking.
Result set
ttLockLevel returns no results.
Example
CALL ttLockLevel ('Row');
Notes
This procedure does not affect the current transaction.
Row-level locking is required when caching Oracle tables.
This procedure must be called from within a transaction. It has the effect of setting the locking level for subsequent transactions for the connection that invoked it. The new lock level does not affect the current transaction. It takes effect at the beginning of the next transaction.
See also
Description
This procedure allows an application to change the lock timeout interval of the current connection. The change takes effect immediately and applies to all subsequent statements in the current transaction, as well as all subsequent transactions on the connection.
The lock wait interval is the number of seconds to wait for a lock when there is contention on it. You can also indicate a fraction of a second.
Lock wait intervals are imprecise, and may be exceeded, generally by no more than 100 milliseconds, due to the scheduling of the agent that detects timeouts. This imprecision does not apply to zero second timeouts, which are always reported immediately.
If AutoCommit is off at the time that ttLockWait is called, the application must commit the transaction in which ttLockWait is executed.
This procedure requires no privilege.
Syntax
ttLockWait(seconds)
Parameters
ttLockWait has the required parameters:
Parameter | Type | Description |
---|---|---|
seconds |
NUMBER (8,1) NOT NULL | Number of seconds to wait for a lock when there is contention on it. You can also specify fractions of a second. Values between 0.0 and 1000000.0 inclusive are accepted. |
Result set
ttLockWait returns no results.
Examples
To indicate a six second lock wait, use:
CALL ttLockWait (6);
To indicate a tenth of a second lock wait, use:
CALL ttLockWait (0.1);
Notes
When a lock is not immediately available to a TimesTen transaction, it waits a predetermined amount of time to try and get the lock. After that it times out the lock request and returns TimesTen error 6003 to the application. By default, TimesTen uses a value of 10 seconds for lock timeouts.
Of special interest is the lock time-out interval value of 0. If that value is specified, transactions do not wait for any unavailable locks. If the lock is not available, the request returns with TimesTen error 6003.
See also
Description
This procedure returns information about transaction log holds, including those created on behalf of incremental backups, replication peers, persistent XLA subscribers, XA, long-running transactions and checkpoints. This procedure can help diagnose situations where it appears that checkpoint operations are not purging all unneeded transaction log files.
This procedure requires no privilege.
Syntax
ttLogHolds()
Parameters
ttLogHolds has no parameters.
Result set
ttLogHolds returns the result set:
Column | Type | Description |
---|---|---|
HoldLFN |
TT_INTEGER NOT NULL | Returns the transaction log file number of the hold. |
HoldLFO |
TT_INTEGER NOT NULL | Returns the transaction log file offset of the hold |
type |
TT_CHAR (30) NOT NULL | Returns the type of hold, one of:
|
description |
TT_VARCHAR (1024) NOT NULL | Describes the type-specific object for which the hold was created. Each description corresponds with the Type returned. Descriptions are one of:
|
Example
CALL ttLogHolds(); < 0, 1148544, Long-Running XA Transaction , 0x1-476c6f62616c-5861637431 > < 0, 1149752, Long-Running Transaction, 4.2 > < 0, 1149992, Checkpoint , sample.ds1 > < 0, 1150168, Checkpoint , sample.ds0 >
Description
This procedures set the value of PERM_IN_USE_HIGH_WATER column in the MONITOR table to the current value of PERM_IN_USE_SIZE attribute and sets the value of the TEMP_IN_USE_HIGH_WATER column in the MONITOR table to the current value of TEMP_IN_USE_SIZE attribute. These columns are useful for sizing databases during application development and deployment.
This procedure requires the ADMIN privilege.
Syntax
ttMonitorHighWaterReset()
Parameters
ttMonitorHighWaterReset has no parameters.
Result set
ttMonitorHighWaterReset returns no results.
Example
CALL ttMonitorHighWaterReset();
Description
This procedure clears the statistics for the specified table, causing the TimesTen query optimizer to use estimates or default values for subsequent queries involving the table. The procedure is useful if statistics are assumed to be out of date and an application wants to use built-in default values. This procedure removes all rows from the TBL_STATS and COL_STATS system tables that pertain to the specified tables. See "SYS.TBL_STATS" and "SYS.COL_STATS" in Oracle TimesTen In-Memory Database SQL Reference.
This procedure requires no privilege for the table owner. This procedure requires no privilege if tblName
is not specified, because the procedure operates on the current user's tables if tblName
is not specified.
This procedure requires the ALTER ANY TABLE privilege if user is not the table owner.
Syntax
ttOptClearStats('tblName', invalidate)
Parameters
ttOptClearStats has these parameters:
Parameter | Type | Description |
---|---|---|
tblName |
TT_CHAR (61) | Name of an application table. Can include table owner. If tblName is the empty string or is not specified, statistics are cleared for all of the current user's tables in the data store.
Using a synonym to specify a table name is not supported. |
invalidate |
TT_INTEGER | 0 (no) or 1 (yes). Default is 0.
If If |
Result set
ttOptClearStats returns no results.
Example
CALL ttOptClearStats ( 'SALLY.ACCTS', 1 );
Clears the statistics for the SALLY.ACCTS
table and reprepares all commands that affect the ACCTS
table.
CALL ttOptClearStats();
Clears the statistics for all of the current user's tables and reprepares all commands that affect these tables.
CALL ttOptClearStats('', 0);
Clears the statistics for all of the current user's tables without repreparing commands that reference these tables.
See also
Description
This procedure updates the statistics for the specified table. This procedure estimates statistics by looking at a random sample of the rows in the specified table(s). The sample size is the number of rows specified (if sampleStr
has the form '
n
ROWS'
) or a percentage of the total number of rows (if sampleStr
has the form '
p
PERCENT
'
).
This procedure requires no privilege for the table owner. This procedure requires no privilege if tblName
is not specified, because the procedure operates on the current user's tables if tblName
is not specified.This procedure requires the ALTER ANY TABLE privilege if user is not the table owner.
Syntax
ttOptEstimateStats('tblName', invalidate, 'sampleStr')
Parameters
ttOptEstimateStats has these parameters:
Parameter | Type | Description |
---|---|---|
tblName |
TT_CHAR(61) | Name of an application table. Can include table owner. If tblName is the empty string, statistics are estimated for all of the current user's tables in the data store.
Using a synonym to specify a table name is not supported. |
invalidate |
TT_INTEGER | 0 (no) or 1 (yes). If invalidate is 1, all commands that reference the affected tables are automatically prepared again when re-executed, including commands prepared by other users. If invalidate is 0, the statistics are not considered to have been modified and existing commands are not reprepared. The invalidate parameter is optional and defaults to 0. |
sampleStr |
TT_VARCHAR (255) NOT NULL | String of the form 'n ROWS', where n is an INTEGER greater than zero; or 'p PERCENT', where p is a floating point number between 0.0 and 100.0 inclusive. |
Result set
ttOptEstimateStats returns no results.
Examples
CALL ttOptEstimateStats ( 'ACCTS', 1, '5 PERCENT' ); CALL ttOptEstimateStats ( 'ACCTS', 1, '75 ROWS' );
Notes
The TimesTen statistics include the number of rows in each table, the number of unique values in each column, and the minimum and maximum values in each column. TimesTen assumes a uniform distribution of column values.
This procedure only runs faster than ttOptUpdateStats when you sample less than 50 percent of the rows in the table.
Estimates are not computed on columns that are longer than 2,048 bytes, and statistics for these columns are not updated. To update statistics on columns longer than 2,048 bytes, use the ttOptUpdateStats built-in procedure. (For varying length columns, this procedure updates statistics only if the column has a maximum length of 2,048 bytes or less.)
If a very small value is chosen for the sampleStr
parameter, this procedure runs quickly but may result in suboptimal execution plans. For "good" distributions of data, a 10 percent selection is a good choice for computing statistics quickly without sacrificing plan accuracy. If the number of rows specified is sufficiently large or the table in question is sufficiently small, to improve performance TimesTen computes exact statistics anyway on all columns that have a length of 2,048 bytes or less. For example, the only difference between
ttOptEstimateStats ('ACCTS', 1, '100 PERCENT' )
and
ttOptUpdateStats( 'ACCTS', 1 )
is that the former does not compute statistics for long columns.
The statistics are stored in the TBL_STATS and COL_STATS system tables.
For performance reasons, TimesTen does not hold a lock on tables or rows when computing statistics. However, computing statistics can still slow performance. Estimating statistics generally provides better performance than computing exact statistics.
See also
Description
This procedure returns statistics information in text format.
This procedure requires the SELECT privilege on the specified tables.
Syntax
ttOptGetColStats('tblName', 'colName')
Parameters
ttOptGetColStats has these parameters:
Parameter | Type | Description |
---|---|---|
tblName |
TT_CHAR (61) | Name of the table whose statistics are to be returned. If NULL is passed, then values for all tables are returned.
Using a synonym to specify a table name is not supported. |
colName |
TT_CHAR (30) | Name of the column for which statistics should be returned. If NULL is passed, statistics for all columns in the specified table are returned. |
Result set
ttOptGetColStats returns the result set:
Column | Type | Description |
---|---|---|
tblName |
TT_CHAR (30) | Name of the table.
Using a synonym to specify a table name is not supported. |
colName |
TT_CHAR (30) | Name of the column. |
stats |
TT_VARCHAR (409600) NOT NULL | Statistics in text form. |
Examples
CALL ttOptGetColStats (); < T1 , X1, (2, 10, 10, 100 (,4, 40, 10 ,1, 10, 5) ,(4, 20, 20 ,11, 20, 15) )>
See also
Description
This procedure returns the optimizer flag settings for the current transaction. The results are returned as a result set that can be retrieved using the ODBC SQLFetch
function or the JDBC ResultSet.getXXX
method, just like the result of a SQL SELECT statement. Applications can request the value of a specific optimizer flag by passing the flag name to ttOptGetFlag. Alternatively, applications can request the values of all the optimizer flags by passing NULL. The optimizer flags and their meanings are described under the ttOptSetFlag built-in procedure.
This procedure requires no privilege.
Syntax
ttOptGetFlag('flagName')
Parameters
ttOptGetFlag has the parameter:
Parameter | Type | Description |
---|---|---|
flagName |
TT_CHAR (32) | Name of the flag whose value is to be returned. If NULL is passed, then the values of all flags are returned. |
Result set
ttOptGetFlag returns the result set:
Column | Type | Description |
---|---|---|
flagName |
TT_VARCHAR (32) NOT NULL | Name of the flag. See "ttOptSetFlag" for a description of possible flag values. |
value |
TT_INTEGER NOT NULL | Current flag value, either 0 or 1. |
Examples
CALL ttOptGetFlag('TmpHash');
See also
Description
This procedure returns the size of the SQL compiled command cache. To reset the size of the cache, use ttOptSetMaxPriCmdFreeListCnt for regular tables and ttOptSetMaxCmdFreeListCnt for materialized views.
This procedure requires no privilege.
Parameters
ttOptGetMaxCmdFreeListCnt has no parameters.
Syntax
ttOptGetMaxCmdFreeListCnt()
Result set
ttOptGetMaxCmdFreeListCnt returns the results.
Column | Type | Description |
---|---|---|
retVal |
TT_VARCHAR (200) NOT NULL | The size of the SQL compiled command cache. |
Example
CALL ttOptGetMaxCmdFreeListCnt( );
See also
Description
This procedure returns a single-row result set containing the join order for the current transaction. This result set can be retrieved using the ODBC SQLFetch
function or the JDBC ResultSet.getXXX
method, just like the result of a SQL SELECT statement. Join orders are described under the ttOptSetOrder built-in procedure.
This procedure requires no privilege.
Syntax
ttOptGetOrder( )
Parameters
ttOptGetOrder has no parameters.
Result set
ttOptGetOrder returns the result set:
Column | Type | Description |
---|---|---|
joinOrder |
TT_VARCHAR(1024) NOT NULL | Optimizer join order for the current transaction. |
Examples
CALL ttOptGetOrder;
See also
Description
This procedure modifies the statistics for the specified columns with interval information. This procedure allows an application to set statistics manually rather than have TimesTen automatically compute them. This feature is useful for preparing commands before the data has been inserted or for seeing how table characteristics can affect the choice of execution plan. This procedure modifies the relevant row(s) in the COL_STATS system table. Modifying interval statistics for a column that is not currently indexed has no effect.
Because this procedure can be used before any data are in the table, the values specified do not need to bear any relation to the actual values, although some basic validity checking is performed.
This procedure requires no privilege (if owner) or ALTER ANY TABLE privilege (if not owner).
Syntax
ttOptSetColIntvlStats('tblName', 'colName', invalidate, (stats))
Parameters
ttOptSetColIntvlStats has these parameters:
Parameter | Type | Description |
---|---|---|
tblName |
TT_CHAR(61) NOT NULL | Name of an application table. Can include table owner.
Using a synonym to specify a table name is not supported. |
colName |
TT_CHAR(30) NOT NULL | Name of a column in that table. |
invalidate |
TT_INTEGER | 0 (no) or 1 (yes). If invalidate is 1, all commands that reference the affected tables are automatically prepared again when re-executed. This includes commands prepared by other users. If invalidate is 0, the statistics are not considered to have been modified and existing commands are not reprepared. |
stats | VARBINARY (409600) NOT NULL | Sets stats for the column, using the format:
The modal value ( |
Result set
ttOptSetColIntvlStats returns no results.
Example
To set the following statistics for column t1.x1
:
Two intervals
Integer type
10 rows with null value
10 unique value
100 rows
Interval 1 (4 unique values besides the most frequently occurring value, 40 rows with values other than most frequently occurring value, 10 rows with most frequently occurring value, min = 1, max = 10, mod = 5)
Interval 2 (4 unique values besides the most frequently occurring value, 20 rows with values other than most frequently occurring, 20 rows with most frequently occurring value, min = 11, max = 20, mod = 15)
Use the statement:
CALLttOptSetColIntvlStats('t1', 'x1', 1, (2, 10, 10, 100, (4, 40, 10, 1, 10, 5), (4, 20, 20, 11, 20, 15)));
Notes
The minimum and maximum values in the interval need to be given as VARBINARY. NULL values are not permitted as minimum or maximum values. The value is stored in the platform-specific endian format.
See also
Description
This procedure modifies the statistics for the specified columns. This procedure allows an application to set statistics manually rather than have TimesTen automatically compute them. This feature is useful for preparing commands before the data has been inserted or for seeing how table characteristics can affect the choice of execution plan. This procedure modifies the relevant row(s) in the COL_STATS system table.
Because this procedure can be used before any data are in the table, the values specified do not need to bear any relation to the actual values, although some basic validity checking is performed.
This procedure requires no privilege (if owner) or ALTER ANY TABLE privilege (if not owner).
Syntax
ttOptSetColStats('tblName', 'colName', numUniq, minVal,maxVal, invalidate, numNull)
Parameters
ttOptSetColStats has these parameters:
Parameter | Type | Description |
---|---|---|
tblName |
TT_CHAR(61) NOT NULL | Name of an application table. Can include table owner.
Using a synonym to specify a table name is not supported. |
colName |
TT_CHAR(30) NOT NULL | Name of a column in that table. |
numUniq |
TT_INTEGER NOT NULL | Number of unique values in the column. |
minVal |
VARBINARY(1024) NOT NULL | Minimum value in the column (possibly truncated). |
maxVal |
VARBINARY(1024) NOT NULL | Maximum value in the column (possibly truncated). |
invalidate |
TT_INTEGER | 0 (no) or 1 (yes). If invalidate is 1, all commands that reference the affected tables are automatically prepared again when re-executed. This includes commands prepared by other users. If invalidate is 0, the statistics are not considered to have been modified and existing commands are not reprepared. |
numNull |
TT_INTEGER | Indicates the total number of NULLs in the column. |
Result set
ttOptSetColStats returns no results.
Example
CALL ttOptSetColStats ('SALLY.ACCTS, 'BALANCE, 400, 0x00001388, 0x000186A0, 1, 0);
Notes
The minimum and maximum values need to be given as VARBINARY. NULL values are not permitted as minimum or maximum values. The value is stored in the platform-specific endian format.
The statistics are treated as a single interval of column values that are uniformly distributed between the minimum value and the maximum value.
See also
Description
This procedure allows applications to alter the generation of execution plans by the TimesTen query optimizer. It sets flags to enable or disable the use of various access methods. The changes made by this call take effect during preparation of statements and affect all subsequent calls to the ODBC functions SQLPrepare
and SQLExecDirect
or the JDBC methods Connection.prepareCall
and Statement.execute
in the current transaction. All optimizer flags are reset to their default values when the transaction has been committed or rolled back. If optimizer flags are set while AutoCommit is on, they are ignored because each statement is executed within its own transaction.
This procedure requires no privilege.
Syntax
ttOptSetFlag('optFlag', optVal)
Parameters
ttOptSetFlag has these parameters:
Parameter | Type | Description |
---|---|---|
optFlag |
TT_CHAR(32) NOT NULL | Name of optimizer flag. |
optVal |
TT_INTEGER NOT NULL | 0 (disable) or 1 (enable). |
Optimizer flags
When setting the optimizer flags, use the following character strings, which are not case sensitive:
Flag | Description |
---|---|
DynamicLoadEnable |
Enables or disables dynamic load of Oracle data to a TimesTen dynamic cache group. By default, dynamic load of Oracle data is enabled. |
DynamicLoadErrorMode |
Enables or disables dynamic load error mode. It controls output of error messages upon failure of a transparent load operation on a TimesTen dynamic cache group. Disabled by default. |
FirstRow |
Enable or disable first row optimization in a SELECT, UPDATE or DELETE statement. If the SQL keyword FIRST is used in the SQL statement, it takes precedence over this optimizer hint. The FIRST keyword enables first row optimization. |
GenPlan |
Enable or disable the creation of entries in the PLAN table for the rest of the transaction.
See "Generating a query plan" in Oracle TimesTen In-Memory Database Operations Guide. |
Hash |
Allow or disallow the use of existing hash indexes in indexed table scans. |
HashGb |
Allow or disallow the use of hash groups. |
NestedLoop |
Refers to a common way of joining two tables. |
PassThrough |
Temporarily changes the pass through level for IMDB Cache applications. The pass through level can be set at any time and takes effect immediately. Legal values for this flag are:
0 - SQL statements are executed only against TimesTen. 1 - Statements other than INSERT, DELETE or UPDATE and DDL are passed through if they generate a syntax error in TimesTen or if one or more tables referenced within the statement are not in TimesTen. All INSERT, DELETE and UPDATE statements are passed through if the target table cannot be found in TimesTen. DDL statements are not passed through. 2 - Same as 1, plus any INSERT, UPDATE and DELETE statement performed on READONLY cache group tables is passed through. 3 - All SQL statements, except COMMIT and ROLLBACK, and TimesTen built-in procedures that set or get optimizer flags are passed through. COMMIT and ROLLBACK are executed on both TimesTen and Oracle. 4 - All SELECT statements on global cache groups tables that cannot use transparent load are executed on Oracle. 5 - All SELECT statements on global cache groups tables that cannot use transparent load are executed on Oracle. The SELECT statement is not executed until after all committed changes to the global cache group are propagated to Oracle. |
RowLock |
Allow or disallow the optimizer to consider using row locks. |
Scan |
Refers to full table scans. |
Rowid |
Allow or disallow the use of Row IDs. |
ShowJoinOrder |
Shows the join order of the tables in an optimizer scan. |
TmpHash |
Allow or disallow the use of a temporary hash scan. This is an index that is created during execution for use in evaluating the statement. Though index creation is time-consuming, it can save time when evaluating join predicates. |
TblLock |
Allow or disallow the optimizer to consider using table locks. |
TmpTable |
Stores intermediate results into a temporary table. This operation is sometimes chosen to avoid repeated evaluation of predicates in join queries or sometimes just to allow faster scans of intermediate results in joins. |
TmpTtree |
Performs a temporary range scan. Can also be used so that values are sorted for a merge join. Though index creation is time-consuming, it can save time when evaluating join predicates. |
Ttree |
Allow or disallow the use of existing range indexes in indexed table scans. |
In addition, the string AllFlags
can be used to refer to all optimizer flags, and the string Default
can be used to refer to the default flags. Default
excludes the GenPlan
flag but includes all other optimizer flags.
Flag description
The value of each flag can be 1 or 0:
If 1, the operation is enabled.
If 0, the operation is disabled unless absolutely necessary.
Initially, all the flag values exceptGenPlan
are 1 (all operations are permitted).
For example, an application can prevent the optimizer from choosing a plan that stores intermediate results:
ttOptSetFlag ( 'TmpTable', 0 )
Similarly, an application can specify a preference for MergeJoin
:
ttOptSetFlag ( 'NestedLoop', 0 )
In the second example, the optimizer may still choose a nested loop join if a merge join is impossible (for example, if there is no merge-join predicate). Similarly, the optimizer may occasionally not be able to satisfy an application request to avoid table scans (when the Scan
flag is set to 0).
You cannot specify that a particular operation is prohibited only at a certain step of a plan or that a particular join method always be done between two specific tables. Similarly, there is no way to specify that certain indexes be used or that a hash index be used to evaluate a specific predicate. Each operation is either fully permitted or fully restricted.
When a command is prepared, the current optimizer flags, index hints and join order are maintained in the structure of the compiled form of the command and are used if the command is ever reprepared by the system. See "The TimesTen Query Optimizer" in Oracle TimesTen In-Memory Database Operations Guide for an example of reprepared statements.
If both RowLock
and TblLock
are disabled, TimesTen uses row-locking. If both RowLock
and TblLock
are enabled, TimesTen uses the locking scheme that is most likely to have better performance:
TblLock status | RowLock status | Effect on the optimizer |
---|---|---|
Disabled | Disabled | Use row-level locking. |
Enabled | Disabled | Use table-level locking. |
Disabled | Enabled | Use row-level locking. |
Enabled | Enabled | Optimizer chooses row-level or table-level locking. |
In general, table-level locking is useful when a query accesses a significant portion of the rows of a table and/or when there are very few concurrent transactions accessing the table.
Result set
ttOptSetFlag returns no results.
Example
CALL ttOptSetFlag ('TmpHash', 1);
See also
Description
This procedure sets the size of the regular SQL compiled command cache. To get the current setting use the ttOptGetMaxCmdFreeListCnt procedure.
This procedure requires the ADMIN privilege.
Syntax
ttOptSetMaxCmdFreeListCnt(maxCnt)
Parameters
ttOptSetMaxCmdFreeListCnt has the required parameter:
Parameter | Type | Description |
---|---|---|
maxCnt |
TT_INTEGER NOT NULL | The size of the SQL compiled command cache. |
Result set
ttOptSetMaxCmdFreeListCnt returns no results.
Example
CALL ttOptSetMaxCmdFreeListCnt(40);
See also
Description
This procedure sets the size of the compiled command cache for commands that perform materialized view maintenance.
This procedure requires the ADMIN privilege.
Syntax
ttOptSetMaxCmdFreeListCnt(maxCnt)
Parameters
ttOptSetMaxPriCmdFreeListCnt has the required parameter:
Parameter | Type | Description |
---|---|---|
maxCnt |
TT_INTEGER NOT NULL | The size of the SQL compiled command cache. |
Result set
ttOptSetMaxPriCmdFreeListCnt returns no results.
Example
CALL ttOptSetMaxPriCmdFreeListCnt(40);
See also
Description
This procedure specifies the order in which tables should be joined by the optimizer. The character string is a list of correlation names referenced in the query or a subquery, separated by spaces (not commas). The table listed first is scanned first by the plan. (It is outermost in a nested loop join, for example.) A correlation name is a shortcut or alias for a qualified table name.
This procedure requires no privilege.
Syntax
ttOptSetOrder('joinOrder')
Parameters
ttOptSetOrder has the required parameter:
Parameter | Type | Description |
---|---|---|
joinOrder |
TT_VARCHAR(1024) | List of space-separated table correlation names. If an owner is required to distinguish the table name, use a table correlation name. If the joinOrder is not specified the query optimizer reverts to its default behavior. |
Result set
ttOptSetOrder returns no results.
Examples
CALL ttOptSetOrder ('EMPS DEPTS ACCTS');
Use the correlation name instead of the actual table name when specifying the join order.
If an application makes the call:
call ttOptSetOrder('ORDERS CUSTOMERS');
the optimizer scans the ORDERS table before scanning the CUSTOMERS when evaluating the following query that lists all the customers who have at least one unshipped order:
SELECT CUSTOMERS.NAME FROM CUSTOMERS WHERE EXISTS (SELECT 1 FROM ORDERS WHERE CUSTOMERS.ID = ORDERS.CUSTID AND ORDER.STATUS ='UN-SHIPPED');
If an application makes the call:
ttOptSetOrder('DEPTS EMPS ACCTS');
the optimizer is prevented from executing a join between DEPTS and ACCTS when evaluating the number of employees working on a specific account:
SELECT COUNT(DISTINCT EMPS.ID) FROM ACCTS, DEPTS, EMPS WHERE ACCTS.DEPTS = DEPTS.ID AND EMPS.DEPTS = DEPTS.ID AND ACCTS.NUM = :AcctNum
If the application does not reset the join order and tries to prepare a command that does not reference each of the three tables (and no others), the optimizer issues warning number 965. The specified join order is not applicable. TimesTen considers valid join orders and ignores the specified join order when preparing the command.
Notes
The string length is limited to 1,024 bytes. If a string exceeds this length, it is truncated and a warning is issued.
When correlation names referenced in subqueries are in included in the order, TimesTen may internally change the isolation mode.
When a command is prepared, the current optimizer flags, index hints, and join order are maintained in the structure of the compiled form of the command and are used if the command is ever reprepared by the system. See "The TimesTen Query Optimizer" in Oracle TimesTen In-Memory Database Operations Guide for an example of reprepared statements.
The changes made by this call take effect immediately and affect all subsequent calls to the ODBC function SQLPrepare
or the JDBC method Connection.prepareCall
in the current transaction. The query optimizer reverts to its default behavior for subsequent transactions.
The tables referenced by a query must exactly match the names given if the join order is to be used (the comparisons are not case sensitive). A complete ordering must be specified; there is no mechanism for specifying partial orders. If the query has a subquery then the join order should also reference the correlation names in the subquery. In essence, the join order should reference all the correlation names referenced in the query. The TimesTen optimizer internally implements a subquery as a special kind of join query with a GROUP BY. For the join order to be applicable it should reference all the correlation names. If there is a discrepancy, a warning is issued and the specified join order is ignored completely. Here are some examples:
See also
Description
This procedure modifies the statistics for the specified table. This procedure allows an application to set statistics explicitly rather than have TimesTen automatically compute them.
This procedure requires no privilege (if owner) or ALTER ANY TABLE privilege (if not owner).
Syntax
ttOptSetTblStats('tblName', numRows, invalidate)
Parameters
ttOptSetTblStats has these parameters:
Parameter | Type | Description |
---|---|---|
tblName |
TT_CHAR(61) NOT NULL | Name of an application table. Can include table owner.
Using a synonym to specify a table name is not supported. |
numRows |
TT_INTEGER NOT NULL | Number of rows in the table. |
invalidate |
TT_INTEGER | 0 (no) or 1 (yes). If invalidate is 1, all commands that reference the affected tables are automatically prepared again when re-executed, including commands prepared by other users. If invalidate is 0, the statistics are not considered to have been modified and existing commands are not reprepared. |
Result set
ttOptSetTblStats returns no results.
Example
CALL ttOptSetTblStats ( 'ACCTS', 10000, 0 );
Note
This feature is useful for preparing commands before the data has been inserted or for seeing how table size can affect the choice of an execution plan. Because the command can be used before any data are in the table, the values specified do not need to bear any relation to the actual values. This procedure modifies the relevant row(s) in the TBL_STATS system table. See "SYS.TBL_STATS" in Oracle TimesTen In-Memory Database SQL Reference.
See also
Description
This procedure returns the join order of the last prepared or executed SQL statement (SELECT, UPDATE, DELETE, and INSERT SELECT) in the current transaction. For a join order to be collected, use ttOptSetFlag('ShowJoinOrder', 1)
or set the ttIsql "ShowJoinOrder" command to ON (1) first in the same transaction. AUTOCOMMIT must be off when using either of these commands. The join order is represented by table names.
This procedure requires no privilege.
Syntax
ttOptShowJoinOrder()
Parameters
ttOptShowJoinOrder has no parameters.
Result set
ttOptShowJoinOrder returns the result:
Column | Type | Description |
---|---|---|
tblName |
TT VARCHAR (4096) NOT NULL | Table names, including owner name quantifiers and correlation name for each table if specified. Table names are returned in parentheses.
Using a synonym to specify a table name is not supported. |
Example
>AUTOCOMMIT 0; > CALL ttOptSetFlag ('ShowJoinOrder', 1); >PREPARE SELECT * FROM t1; >CALL ttOptShowJoinOrder(); >( T1 )
Notes
You must call ttOptSetFlag('ShowJoinOrder', 1)
or set the ttIsql "ShowJoinOrder" command to ON (1) before using this procedure.
This procedure works within one transaction and is not persistent across transactions.
See also
Description
This procedure updates the statistics for the specified table. TimesTen looks at the data stored in the table and updates the TBL_STATS and COL_STATS system tables. If the table is large, this process can take some time. Statistics are not computed automatically as rows are updated; an application must compute them explicitly by calling this procedure.
This procedure requires no privilege for the table owner. This procedure requires no privilege if tblName
is not specified, because the procedure operates on the current user's tables if tblName
is not specified.This procedure requires the ALTER ANY TABLE privilege if user is not the table owner.
Syntax
ttOptUpdateStats('tblName', invalidate, option)
Parameters
ttOptUpdateStats has these parameters:
Parameter | Type | Description |
---|---|---|
tblName |
TT_CHAR(61) | Name of an application table. Can include table owner. If a value of NULL or an empty string is provided, the statistics for all of the current user's tables are updated.
Using a synonym to specify a table name is not supported. |
invalidate |
TT_INTEGER | 0 (no) or 1 (yes). If invalidate is 1, all commands that reference the affected tables are automatically prepared again when re-executed, including commands prepared by other users. If invalidate is 0, the statistics are not considered to have been modified and existing commands are not reprepared.
The |
option |
TT_INTEGER | Specifies whether to collect complete interval statistics information. Valid values for this option are:
Null or 0 - Collect complete interval statistics only if a t-range index exists on the column. If a range index does not exist, only single interval statistics are collected. 1 - Do not collect complete interval statistics. Only single interval statistics are collected. See "Notes" for more information. |
Result set
ttOptUpdateStats returns no results.
Example
CALL ttOptUpdateStats ( 'ACCTS', 1 );
Updates the ACCTS table and causes all commands that reference the ACCTS table to be re-prepared when they are next executed.
CALL ttOptUpdateStats('', 1);
Updates all of the current user's tables and causes commands on those tables to be reprepared when they are next executed.
CALL ttOptUpdateStats('ACCTS', 0, 1);
Forces single interval statistics to be collected.
Notes
If the table name specified is an empty string, statistics are updated for all of the current user's tables.
When complete interval statistics are collected, the total number of rows in the table is divided into 20 or less intervals and the distribution of each interval is recorded in the statistics. The new statistics contain the information:
Number of intervals
Total number of NULLs in the column
Total number of NON NULL UNIQUE values in the column
Total number of rows in the table
Interval information, each interval contains:
The minimum value
The maximum value
The most frequently occurring value
The number of times the most frequent value occurred
The number of rows that have different values than the most frequent value
The number of unique values besides the most frequent value
Collection of complete interval statistics requires the data to be sorted.
If complete interval statistics are not selected, then statistics are collected by treating the entire distribution as a single interval.
For performance reasons, TimesTen does not hold a lock on tables or rows when computing statistics. However, computing statistics can still slow performance. Estimating statistics generally provides better performance than computing exact statistics. See "ttOptEstimateStats" for information on estimating statistics.
See also
Description
This procedure allows applications to alter the generation of execution plans by the TimesTen query optimizer. It allows applications to disable the use of a set of indexes or enable the consideration of only a set of indexes for each correlation used in a query. Enabling the consideration of an index does not guarantee that the plan generated uses the index. Depending on the estimated cost, the optimizer might choose to use a serialization scan or a materialization scan to access the associated correlation if these scans resulted in a better plan than the ones that use the specified index.
The changes made by this call take effect immediately and affect all subsequent calls to the ODBC functions SQLPrepare
and SQLExecDirect
or the JDBC methods Connection.prepareCall
and Statement.execute
in the current transaction until the applications explicitly issue a call to clear it. The setting is cleared whenever a new transaction is started.
This procedure requires no privilege.
Syntax
ttOptUseIndex('IndexName, CorrelationName, 0 | 1 [;...]')
Parameters
ttOptUseIndex has a single string parameter, indOption
, of type TT_VARCHAR(1024) with these components:
Component | Description |
---|---|
IndexName |
The name of the user-defined index or '_TMPTTREE' for temporary range index or '_TMPHASH' for temporary hash index. If index name is omitted, the setting applies to all indexes of the specified correlation. |
CorrelationName |
The correlation name of the table. If a table is defined with a correlation name in the FROM clause, use this correlation name instead of the table name when specifying the index hint for this table. If correlation name is omitted for an entry, the setting affects all tables with the specified index name. |
0 | 1 |
Disables(0) or enables (1) the use of the index specified by IndexName . |
Result set
ttOptUseIndex returns no results.
Examples
CALL ttOptUseIndex('"3456"."1234", t1, 0'); CALL ttOptUseIndex('data1.i1, data1.t1, 0'); CALL ttOptUseIndex('i1, t1, 0');
Note
If ttOptUseIndex is called without a parameter or with a NULL value, TimesTen clears the previous index hint.
See also
Description
This procedure returns result statistics about PL/SQL library cache performance and activity.
This procedure requires no privilege.
Syntax
ttPLSQLMemoryStats(paramName, paramValue )
Parameters
ttPLSQLMemoryStats takes no parameters.
Parameters
ttPLSQLMemoryStats returns the results in the following columns:
Columns | Type | Description |
---|---|---|
paramName |
TT_VARCHAR(30) NOT NULL | The name of the result statistic returned in this row. |
paramValue |
BINARY_FLOAT NOT NULL | The value of the result statistic returned in this row. |
The following statistics are returned:
Gets
: Number of times a lock was requested for a PL/SQL object.
GetHits
: Number of times a PL/SQL object's handle was found in memory.
GetHitRatio
: Ratio of GetHits to Gets.
Pins
: Number of times a PIN was requested for PL/SQL objects.
PinHits
: Number of times all of the metadata pieces of the library object were found in memory.
PinHitRatio
: Ratio of PinHits to Pins.
Reloads
: Any PIN of an object that is not the first PIN performed since the object handle was created, and which requires loading the object from the database.
Invalidations
: Total number of times objects in this namespace were marked invalid because a dependent object was modified.
CurrentConnectionMemory
: The total amount of heap memory, in Megabytes, allocated to PL/SQL on this database connection.
DeferredCleanups
: Total number of times a deferred cleanup occurred.
Examples
connect "DSN=sample"; Connection successful: DSN=sample;UID=timesten;DataStore=/scratch/timesten/sample;DatabaseCharacterSet=AL32UTF8;ConnectionCharacterSet=AL32UTF8;PermSize=128;TypeMode=0;PLSQL_MEMORY_SIZE=32;PLSQL_MEMORY_ADDRESS=20000000;PLSQL=1; (Default setting AutoCommit=1) Command> create procedure hello is begin dbms_output.put_line('Hello, World!'); end; > / Procedure created. Command> call ttPlsqlMemoryStats; < Gets, 485.00000 > < GetHits, 444.000000 > < GetHitRatio, .9154639 > < Pins, 260.00000 > < PinHits, 178.000000 > < PinHitRatio, .6846154 > < Reloads, 4.000000 > < Invalidations, 0.000000e+00 > < CurrentConnectionMemory, 56.00000 > 9 rows found.
Description
This procedure returns the RAM policy used to determine when a data store is loaded into memory. The policy can be either always
, manual
, or inUse
.
This procedure requires no privilege.
Syntax
ttRamPolicyGet()
Result set
ttRamPolicyGet returns the results:
Column | Type | Description |
---|---|---|
ramPolicy |
TT_VARCHAR (10) | The policy used to determine when the data store is loaded into system RAM. Valid values are:
|
ramGrace |
TT_INTEGER | If the ramPolicy is inUse , this field reports the number of seconds the data store is kept in RAM after the last application has disconnected. Otherwise, this field is NULL. |
Parameters
ttRamPolicyGet has no parameters.
Examples
To view the RAM policy, use:
CALL ttRamPolicyGet();
See also
Description
This procedure defines the policy used to determine when a data store is loaded into memory. The policy can be either always
, manual
, or inUse
.
This procedure requires the ADMIN privilege.
Syntax
ttRamPolicySet('ramPolicy', ramGrace)
Parameters
ttRamPolicySet has the parameters:
Parameter | Type | Description |
---|---|---|
ramPolicy |
TT_VARCHAR (10) NOT NULL | The policy used to determine when the data store is loaded into system RAM. Valid values are:
|
ramGrace |
TT_INTEGER | Sets the number of seconds the data store is kept in RAM after the last application has disconnected. This number is only effective if ramPoliy is inUse . This parameter is optional, and when omitted or set to NULL, the existing ramGrace period is left unchanged. |
Result set
ttRamPolicySet returns no results.
Examples
To set the policy for loading a data store into RAM to be inUse and for the data store to kept in RAM for 10 seconds after the last application has disconnected, use:
CALL ttRamPolicySet('inUse', 10);
See also
Description
This procedure scans the indicated table (or all of the current user's tables) to find redundant indexes. It returns the names of the redundant indexes and a suggestion for which to drop.
This procedure requires no privilege.
Syntax
ttRedundantIndexCheck('tblname')
Parameters
ttRedundantIndexCheck has the parameter:
Parameter | Type | Description |
---|---|---|
tblName |
TT_CHAR(61) | Name of an application table. Can include table owner. If a value of NULL or an empty string is provided, the redundant indexes for all of the current user's tables.
Using a synonym to specify a table name is not supported. |
Result Set
ttRedundantIndexCheck returns the result:
Column | Type | Description |
---|---|---|
redundancy |
TT_VARCHAR (1024) NOT NULL | The names of redundant indexes and a suggestion for which index to drop. |
Example
Create table y
with a primary key. Then create index i
. TimesTen returns a warning that a redundant index is being created. Create another index, i1
. The command fails and TimesTen returns an error. Call this procedure to show the warnings.
CREATE TABLE y (ID tt_integer primary key); CREATE INDEX i ON y (id); Warning 2240: New non-unique index I has the same key columns as existing unique index Y; consider dropping index I CREATE INDEX i1 ON y (id); 2231: New index I1 would be identical to existing index I The command failed. CALL ttredundantindexcheck ('y'); < Non-unique index SCOTT.Y.I has the same key columns as unique index SCOTT.Y.Y; consider dropping index SCOTT.Y.I > 1 row found.
Description
This procedure changes the state of the active data store in an active standby pair from ACTIVE to IDLE. Use this procedure when reversing the roles of the master data stores in an active standby pair.
This procedure requires the ADMIN privilege.
Syntax
ttRepDeactivate()
Parameters
ttRepDeactivate has no parameters.
Result set
ttRepDeactivate returns no results.
Example
To deactivate the active data store in an active standby pair, use:
CALL ttRepDeactivate();
See also
Description
This procedure returns the status of one or more replication peer data stores.
This procedure requires no privilege.
Syntax
ttReplicationStatus('receiver', 'hostname')
Parameters
ttReplicationStatus has the optional parameters:
Parameter | Type | Description |
---|---|---|
receiver |
TT_VARCHAR(200) | Subscriber of interest or NULL for all subscribers. If the parameter is provided, then it names a replication subscriber about which information is sought. If the parameter is not provided, then information on replication subscribers defined for the current data store is returned. |
hostname |
TT_VARCHAR(200) | The host name of one or more stores that are configured to receive updates from the executing store; if NULL, then receiving stores are identified by subscriber alone. If both receiver and hostname are NULL, then all receiving stores are selected. |
Result set
ttReplicationStatus returns the result set:
Column | Type | Description |
---|---|---|
subscriber |
TT_VARCHAR(200) NOT NULL | Subscriber name. |
hostName |
TT_VARCHAR(200) NOT NULL | Host name. |
port |
TT_INTEGER NOT NULL | Defined port number. |
pState |
TT_CHAR(10) NOT NULL | Peer state. The values of the result column are:
|
logs |
TT_INTEGER NOT NULL | Number of transaction log files held for this peer. |
lastMsg |
TT_INTEGER | Seconds since last interaction or NULL. |
replicationName |
TT_CHAR(30) NOT NULL | Name of replication scheme. |
replicationOwner |
TT_CHAR(30) NOT NULL | Owner of replication scheme. |
Example
CALL ttReplicationStatus('System8');
Notes
If the receiver
parameter is not NULL, only the status of the given receiver is returned. If the receiver
parameter is NULL, the status of all subscribers is returned.
This procedure is supported only for TimesTen Data Manager ODBC applications. It is not supported for TimesTen Client or JDBC applications.
See also
Description
This procedure returns the replication restart policy used to determine when the TimesTen Replication Agent for the connected data store should run. The policy can be always
, manual
, or norestart
.
This procedure requires no privilege.
Syntax
ttRepPolicyGet()
Parameters
ttRepPolicyGet has no parameters.
Result set
ttRepPolicyGet returns the results:
Parameter | Type | Description |
---|---|---|
repPolicy |
TT_VARCHAR (10) | The policy used to determine when the TimesTen Replication Agent for the data store should run. Valid values are:
|
Examples
To set the policy for TimesTen Replication Agent to always, use:
CALL ttRepPolicyGet();
See also
Description
This procedure defines the replication restart policy used to determine when the TimesTen Replication Agent for the connected data store should run. The policy can be either always
, manual
, or norestart
.
This procedure requires the ADMIN privilege.
Syntax
ttRepPolicySet('repPolicy')
Parameters
ttRepPolicySet has this parameter:
Parameter | Type | Description |
---|---|---|
repPolicy |
TT_VARCHAR (10) NOT NULL | Specifies the policy used to determine when the TimesTen Replication Agent for the data store should run. Valid values are:
|
Result set
ttRepPolicySet returns no results.
Examples
To set the policy for TimesTen Replication Agent to always, use:
CALL ttRepPolicySet('always');
See also
Description
This procedure returns the number of seconds that was most recently specified as the query threshold for the replication agent. The number of seconds returned may not be the same as the query threshold in effect. Setting a new value for the query threshold takes effect the next time the replication agent is started.
This procedure requires the ADMIN privilege.
Syntax
ttRepQueryThresholdGet()
Parameters
ttRepQueryThresholdGet has no parameters.
Result set
ttRepQueryThresholdGet returns the result:
Column | Type | Description |
---|---|---|
repQueryThreshold |
TT_INTEGER | The number of seconds that a replication query executes before returning an error. |
Examples
To get the replication query threshold value, use:
CALL ttRepQueryThresholdGet; < 4 > 1 row found.
See also
Description
This procedure specifies the number of seconds that a query can be executed by the replication agent before TimesTen writes a warning to the support log and throws an SNMP trap. The specified value takes effect the next time the replication agent is started. The query threshold for the replication agent applies to SQL execution on detail tables of materialized views, ON DELETE CASCADE operations and some internal operations that execute SQL statements.
This procedure requires the ADMIN privilege.
Syntax
ttRepQueryThresholdSet(seconds);
Parameters
ttRepQueryThresholdSet has the parameter:
Parameter | Type | Description |
---|---|---|
seconds |
TT_INTEGER NOT NULL | Number of seconds a SQL statement can be executed by the replication agent before TimesTen writes a warning to the support log and throws an SNMP trap. The value must be greater than or equal to 0. Default is 0 and indicates that no warnings will be written. |
Result set
ttRepQueryThresholdSet returns no results.
Examples
To set the replication query threshold value to four seconds, use:
CALL ttRepQueryThresholdSet(4);
See also
Description
This procedure starts the TimesTen Replication Agent for the connected data store.
This procedure requires the CACHE_MANAGER privilege.
Syntax
ttRepStart()
Parameters
ttRepStart has no parameters.
Result set
ttRepStart returns no results.
Examples
To start the replication agent, use:
CALL ttRepStart();
Notes
The replication agent does not start if the data store does not participate in any replication scheme.
When using this procedure, no application, including the application making the call, can be holding a connection that specifies data store-level locking (LockLevel=1).
See also
Description
This procedure returns the current replication state of a data store in an active standby pair.
This procedure requires no privilege.
Syntax
ttRepStateGet()
Parameters
ttRepStateGet has no parameters.
Result set
ttRepStateGet returns the result:
Column | Type | Description |
---|---|---|
state |
TT_VARCHAR (20) NOT NULL | The current replication state of the data store. One of:
|
Examples
To determine whether the standby data store in an active standby pair has moved from the IDLE to the STANDBY state, use:
CALL ttRepStateGet(); < STANDBY >
See also
Description
This procedure saves the state of a remote peer data store in an active standby pair to the currently connected data store. Currently, may only be used to indicate to the active data store that the standby data store, storeName
on hostName,
has failed, and that all updates on the active data store should be replicated directly to the read-only subscribers.
This procedure requires the ADMIN privilege.
Syntax
ttRepStateSave('state', 'storeName', 'hostName')
Parameters
ttRepStateSave has these parameters:
Parameter | Type | Description |
---|---|---|
state |
TT_VARCHAR (20) NOT NULL | The replication state of the indicated data store. May only be specified as FAILED in this release. Recording that a standby data store has failed indicates that all replicated updates are to be sent directly from the active data store to the read-only subscribers. |
storeName |
TT_VARCHAR (200) NOT NULL | Name of the data store for which the state is indicated. |
hostName |
TT_VARCHAR (200) | Name of the host where the data store resides. |
Result set
ttRepStateSave returns no results.
Examples
To indicate to the active data store that the standby data store standby
on host backup1
has failed, use:
ttRepStateSave('FAILED', 'standby', 'backup1');
See also
Description
This procedure sets the replication state of a data store in an active standby pair replication scheme. Currently, ttRepStateSet may only be used to set the state of a data store to ACTIVE, indicating that it is to take the active role in an active standby pair. ttRepStateSet may only be executed in the following situations:
A data store has had a CREATE ACTIVE STANDBY PAIR command executed and no failures have occurred since.
A data store is currently in the STANDBY state, and the other data store in the active standby pair has had its state changed from ACTIVE to IDLE using the ttRepDeactivate procedure.
A data store has just recovered from the local transaction log and was in the ACTIVE state before it went down.
This procedure requires the ADMIN privilege.
Syntax
ttRepStateSet('state')
Parameters
ttRepStateSet has the parameter:
Parameter | Type | Description |
---|---|---|
state |
TT_VARCHAR (20) NOT NULL | The replication state of the data store. Must be ACTIVE, in this release. Setting a store to ACTIVE designates it as the active data store in an active standby pair. |
Result set
ttRepStateSet returns no results.
Examples
To set the replication state of the data store to ACTIVE, use:
CALL ttRepStateSet('ACTIVE');
See also
Description
This procedure stops the TimesTen replication agent for the connected data store.
This procedure requires the CACHE_MANAGER privilege.
Syntax
ttRepStop()
Parameters
ttRepStop has no parameters.
Result set
ttRepStop returns no results.
Examples
To stop the replication agent, use:
CALL ttRepStop();
Note
When using this procedure, no application, including the application making the call, can be holding a connection that specifies data store-level locking (LockLevel=1).
See also
Description
This procedure changes a replicating subscriber's state with respect to the executing master store.
This procedure requires the ADMIN privilege.
Syntax
ttRepSubscriberStateSet('replicationName', 'replicationOwner', 'subscriberStoreName', 'subscriberHostName', newStateCode)
Parameters
ttRepSubscriberStateSet has these parameters:
Parameter | Type | Description |
---|---|---|
replicationName |
TT_CHAR (30) | The name of the replication scheme on which to operate. May be NULL to indicate all replication schemes. |
replicationOwner |
TT_CHAR (30) | The owner of the replication scheme. May be NULL to indicate all replication scheme owners. |
subscriberStoreName |
TT_VARCHAR (200) | The name of the subscribing data store whose state is to be set. May be NULL to indicate all stores on host subscriberHostName . |
subscriberHostName |
TT_VARCHAR (200) | The subscriber's host. May be NULL to indicate all hosts of subscribing peers. |
newStateCode |
TT_INTEGER | An integer code representing the specified subscriber's new state:
0/NULL - started 1 - paused 2 - stopped All other state codes are disallowed. (This procedure cannot set a subscriber state to "failed.") |
Result set
ttRepSubscriberStateSet returns no results.
Examples
For the replication scheme named REPL.REPSCHEME
, the following directs the master data store to set the state of the subscriber data store (SUBSCRIBERDS ON SYSTEM1
) to Stop
(2
):
CALL ttRepSubscriberStateSet('REPSCHEME', 'REPL', 'SUBSCRIBERDS','SYSTEM1', 2);
To direct the master data store to set the state of all its subscribers to Pause
(1
), use:
CALL ttRepSubscriberStateSet( , , , , 1 );
Leaving a parameter empty is equivalent to using NULL.
See also
Description
This procedure checks a subscriber database to determine if all updates have been received from a specified master database.
This built-procedure can only be invoked from a subscriber and only one connection at a time can invoke the procedure.
When the procedure returns, either it has timed out or all transactions that committed before it was invoked have been shipped and applied on the subscriber. You must supply a value for all the arguments in this procedure.
This procedure requires the ADMIN privilege.
Syntax
ttRepSubscriberSync('replicationName', 'replicationOwner', 'MASTERStoreName', 'masterHostName', waitTime)
Parameters
ttRepSubscriberSync has these parameters:
Parameter | Type | Description |
---|---|---|
replicationName |
TT_CHAR (30) NOT NULL | The name of the replication scheme on which to operate. May be NULL to indicate all replication schemes. |
replicationOwner |
TT_CHAR (30) NOT NULL | The owner of the replication scheme. May be NULL to indicate all replication scheme owners. |
masterStoreName |
TT_VARCHAR (200) NOT NULL | The name of the master data store with which the subscriber needs to sync. |
masterHostName |
TT_VARCHAR(200) NOT NULL | The master host. May be NULL to indicate all hosts of master peers. |
waitTime |
TT_INTEGER NOT NULL | Number of seconds to wait for the specified master(s). A value of -1 indicates to wait forever. This parameter is required and may not be NULL. |
Result Set
ttRepSubscriberSync returns the result set:
Column | Type | Description |
---|---|---|
timeOut |
BINARY(1) | 0x00 - The wait succeeded within the allotted waitTime ; the specified subscribers are up to date at the time this procedure was called. TimesTen returns 0x01 if not enough time has been granted. |
Example
If there is one defined replication scheme REPOWNER.REPSCHEME
, to direct the subscriber data store to wait ten minutes to catch up with master REP1
on SERVER1
, use:
CALL ttRepSubscriberSync('REPSCHEME, 'REPOWNER, 'REP1', 'SERVER1', 600);
See also
Description
This procedure causes the caller to wait until all transactions that committed prior to the call have been transmitted to the subscriber subscriberStoreName
and the subscriber has acknowledged that the updates have been durably committed at the subscriber data store.
If you set the waitTime
parameter to -1 and the subscriberStoreName
parameter to NULL, the ttRepSubscriberWait procedure does not return until all updates committed up until the time of the procedure call have been transmitted to all subscribers, and all subscribers have acknowledged that the updates have been durably committed.
The ttRepSubscriberWait function should not be used when an urgent response is required. Instead, you should use the return receipt service.
Note:
If this procedure is called after all write transaction activity is quiesced at a store (there are no active transactions and no transactions have started), it may take a 60 seconds or longer before the subscriber sends the acknowledgement that all updates have been durably committed at the subscriber.This procedure requires no privilege.
Syntax
ttRepSubscriberWait('replicationName', 'replicationOwner', 'subscriberStoreName', 'subscriberHostName', waitTime)
Parameters
ttRepSubscriberWait has these parameters:
Parameter | Type | Description |
---|---|---|
replicationName |
TT_CHAR (30) | The name of the replication scheme on which to operate. May be NULL to indicate all replication schemes. |
replicationOwner |
TT_CHAR (30) | The owner of the replication scheme. May be NULL to indicate all replication scheme owners. |
subscriberStoreName |
TT_VARCHAR (200) | The name of the subscribing data store whose state is to be set. May be NULL to indicate all stores on host subscriberHostName . |
subscriberHostName |
TT_VARCHAR(200) | The subscriber's host. May be NULL to indicate all hosts of subscribing peers. |
waitTime |
TT_INTEGER NOT NULL | Number of seconds to wait for the specified subscriber(s). A value of -1 indicates to wait forever. This parameter is required and may not be NULL. |
Result Set
ttRepSubscriberWait returns the result set:
Column | Type | Description |
---|---|---|
timeOut |
BINARY(1) | 0x00 - The wait succeeded within the allotted waitTime ; the specified subscribers are up to date at the time this procedure was called. TimesTen returns 0x01 if not enough time has been granted. |
Example
If there is one defined replication scheme REPOWNER.REPSCHEME
, to direct the transmitting data store to wait ten minutes for subscriber REP
2 on SERVER2
to catch up, use:
CALL ttRepSubscriberWait('REPSCHEME','REPOWNER', 'REP2', 'SERVER2', 600);
See also
Description
This procedure returns static attributes associated with the caller's use of the replication- based return service. This procedure operates with either the RETURN RECEIPT or RETURN TWOSAFE service.
This procedure requires no privilege.
Syntax
ttRepSyncGet()
Parameters
ttRepSyncGet has no parameters.
Result set
ttRepSyncGet returns the result set:
Column | Type | Description |
---|---|---|
requestReturn |
BINARY(1) | 0 - Don't wait for return notification configured with the RETURN RECEIPT BY REQUEST or RETURN TWOSAFE BY REQUEST option; this value is the default.
1 - Wait for the return notification. Commit resets this attribute to its default value of 0 ("off"). |
returnWait |
TT_INTEGER | Specifies the number of seconds to wait for return service acknowledgement. The default value is 10 seconds. A value of `0' means that there is no wait time.This attribute persists across transaction boundaries and applies to all RETURN services independent of the BY REQUEST option. |
localAction |
TT_INTEGER | The current LOCAL ACTION configuration for RETURN services.
1 -- NO ACTION -- When a COMMIT times out, it returns the application unblocked, leaving the transaction in the same state it was when the COMMIT began,. The application may only reissue the COMMIT. This is the default. 2 -- COMMIT. When the COMMIT times out, the transaction is committed locally. No more operations are possible on this transaction, and the replicated data stores diverge.This attribute persists across transactions and for the life of the connection. |
Example
To retrieve the caller's requestReturn value, use:
SQLCHAR requestReturn[1]; SQLINTEGER len; rc = SQLExecDirect ( hstmt , (SQLCHAR *) "{CALL ttRepSyncGet( NULL )}" , SQL_NTS ) rc = SQLBindCol ( hstmt , /* ColumnNumber */ 1 , /* TargetType */ SQL_C_BINARY ) , /* TargetValuePtr */ requestReturn ,./* BufferLength */ sizeof requestReturn , /* StrLen_ */ &len ); rc = SQLFetch( hstmt ); if ( requestReturn[0] ) { ... }
Notes
When called within a stand-alone transaction, ttRepSyncGet always returns the default value for requestReturn.
ttRepSyncGet may be called at any point within a transaction in which it is used to request the BY REQUEST return service for that transaction.
If you call ttRepSyncGet in a transaction that does not, in fact, update any RETURN RECEIPT BY REQUEST or RETURN TWOSAFE BY REQUEST replication elements, the call has no external effect.
See also
Description
This procedure sets static attributes associated with the caller's use of the replication-based return service. This procedure operates with either the RETURN RECEIPT or RETURN TWOSAFE service.
This procedure requires no privilege.
Syntax
ttRepSyncSet(requestReturn, returnWait, localAction)
Parameters
ttRepSyncSet has these optional parameters:
Parameter | Type | Description |
---|---|---|
requestReturn |
BINARY(1) | 0x00 - Turn off the return service for the current transaction.
0x01 - Turn on return services for the current transaction. Committing the transaction resets this attribute to its default value of 0 ("off"). You can use this parameter to turn on or turn off return services only when the replication subscribers have been configured with RETURN RECEIPT BY REQUEST or RETURN TWOSAFE BY REQUEST. |
returnWait |
TT_INTEGER | Specifies the number of seconds to wait for return service acknowledgement. The default value is 10 seconds. A value of '0' means there is no wait time.
This timeout value overrides the value set by the RETURN WAIT TIME attribute in the CREATE REPLICATION or ALTER REPLICATION statement. The timeout set by this parameter persists across transaction boundaries and applies to all return services independent of the BY REQUEST option. |
localAction |
TT_INTEGER | Action to be performed in the event the subscriber is unable to acknowledge commit of the transaction within the timeout period specified by returnWait . This parameter can only be used for return twosafe transactions. Set to NULL when using the RETURN service.
1 -- NO ACTION -- When a COMMIT times out, it returns the application unblocked, leaving the transaction in the same state it was when the COMMIT began,. The application may only reissue the COMMIT. This is the default. 2 -- COMMIT. When the COMMIT times out, the transaction is committed locally. No more operations are possible on this transaction, and the replicated data stores diverge.This attribute persists across transactions and for the life of the connection. |
Result set
ttRepSyncSet has no result set.
Examples
To enable the return receipt service in the current transaction for all the replication elements configured with RETURN RECEIPT BY REQUEST or RETURN TWOSAFE BY REQUEST, use:
rc = SQLExecDirect ( hstmt , (SQLCHAR *)"{CALL ttRepSyncSet( 0x01 )}" , SQL_NTS )
Note
The call to enable the return receipt service must be part of the transaction (AutoCommit must be off).
See also
Description
This procedure queries a subscriber data store in a replication scheme configured with a return service and a RETURN DISABLE failure policy to determine whether return service blocking for the subscriber has been disabled by the failure policy.
The ttRepSyncSubscriberStatus procedure returns the failure status of the subscriber data store with the specified name on the specified host. You can specify only the storeName
. However, an error is generated if the replication scheme contains more than one subscriber with the same name on different hosts.
This procedure requires no privilege.
Syntax
ttRepSyncSubscriberStatus('storeName', 'hostName')
Parameters
ttRepSyncSubscriberStatus has these parameters:
Parameter | Type | Description |
---|---|---|
subscriber |
TT_VARCHAR (200) NOT NULL | The name of the subscribing data store to be queried. |
hostName |
TT_VARCHAR (200) | The host name of one or more stores that are configured to receive updates from the executing store; if NULL, then receiving stores are identified by receiver alone. If both receiver and hostname are NULL, then all receiving stores are selected. |
Result set
ttRepSyncSubscriberStatus returns:
Column | Type | Description |
---|---|---|
disabled |
TT_INTEGER | Value is either:
'1', if the return service has been disabled on the subscriber data store. '0' if the return service is still enabled on the subscriber data store. |
Note
If the replication scheme specifies DISABLE RETURN ALL, then you must use ttRepSyncSubscriberStatus to query the status of each individual subscriber in the replication scheme.
Description
This procedure returns the status of transmission of updates to subscribers for the current transaction. The corresponding ttRepSyncSet built-in procedure allows you to stop transmission of updates to subscribers for the length of a transaction.
This procedure requires the ADMIN privilege.
Syntax
ttRepTransmitGet()
Parameters
ttRepTransmitGet has no parameters.
Result set
ttRepTransmitGet returns the result:
Column | Type | Description |
---|---|---|
transmit |
TT_INTEGER | 0 - Updates are not being transmitted to any subscribers for the remainder of the transaction on the connection.
1- Updates are being transmitted to subscribers on the connection. (default) |
Example
To return the transmit status on the active data store in an active standby pair, use:
CALL ttRepTransmitGet();
See also
Description
This procedure stops subsequent updates on the connection it is executed in from being replicated to any subscriber.
This procedure should be used with care since it could easily lead to transactional inconsistency of remote stores if partial transactions are replicated. If updates are disallowed from getting replicated, the subscriber stores diverge from the master store.
Required privilege
This procedure requires the ADMIN privilege.
Syntax
ttRepTransmitSet(transmit)
Parameters
ttRepTransmitSet has the parameter:
Parameter | Type | Description |
---|---|---|
transmit |
TT_INTEGER NOT NULL | When set to 1 updates are transmitted to subscribers on the connection after the built-in is executed to replicate. (default)
When set to 0 updates are not transmitted to any subscribers for the remainder of the transaction in which this call was issued, as well as on the connection that issued it. |
Result set
ttRepTransmitSet returns no results.
Example
To activate the active data store in an active standby pair, use:
CALL ttRepTransmitSet(1);
To deactivate the active data store in an active standby pair, use:
CALL ttRepTransmitSet(0);
See also
Description
This procedure checks on the status of a RETURN RECEIPT or RETURN TWOSAFE replication transaction. Using the built-in procedure ttRepXactTokenGet, you can get the token of a RETURN RECEIPT or RETURN TWOSAFE transaction. This is then passed as an input parameter to this built-in procedure. Only a token received from ttRepXactTokenGet may be used. The procedure returns a list of rows each of which have three parameters, a subscriber name, the replication status with respect to the subscriber and an error string that is only returned in the case of a RETURN TWOSAFE replication transaction that has begun, but not completed, commit processing.
Note:
The error parameter is only returned for RETURN TWOSAFE transactions.This procedure requires no privilege.
Syntax
ttRepXactStatus(token)
Parameters
ttRepXactStatus has the parameter:
Parameter | Type | Description |
---|---|---|
xactID |
VARBINARY (10000) | If no parameter is specified, status is returned for one of the following:- If called in a transaction that has begun, but not completed, commit processing, it returns the status of the transaction.- If called at any other time, it returns status for the most recently committed transaction on the connection that was in RETURN RECEIPT or RETURN TWOSAFE mode. |
Result set
ttRepXactStatus returns the result set:
Column | Type | Description |
---|---|---|
subscriberName |
TT_CHAR (61) | The name of the data store that subscribes to tables updated in the transaction. The name returns as: store_name @ host_name . |
state |
TT_CHAR (2) | The state of the transaction with respect to the subscribing data store. The return values are one of the following:
|
errorString |
TT_VARCHAR (2000) | Error string retuned by the subscriber agent describing the error it encountered when applying the twosafe transaction. If no error is encountered, this parameter is NULL. Non-Null values are only returned when this procedure is called inside a twosafe replication transaction that has begun, but has not yet completed, processing a commit. |
See also
Description
This procedure returns a token for RETURN RECEIPT or RETURN TWOSAFE replication transactions. Depending on the input parameter, type, it returns either:
A token to the most recently committed RETURN RECEIPT transaction on the connection handle in which it is invoked.
A token to the most recent transaction on the connection handle in which it is invoked that has begun commit processing on a transaction in RETURN TWOSAFE mode.
This procedure can be executed in any subsequent transaction or in the same transaction after commit processing has begun for a transaction in RETURN TWOSAFE replication.
This procedure requires no privilege.
Syntax
ttRepXactTokenGet('type')
Parameters
ttRepXactTokenGet has these parameters:
Parameter | Type | Description |
---|---|---|
type |
TT_CHAR (2) NOT NULL | The type of transaction desired:
|
Result set
ttRepXactTokenGet returns the result set:
Column | Type | Description |
---|---|---|
token |
VARBINARY (10000) | A varbinary token used to represent the transaction desired. |
See also
Description
This procedure explicitly sets the value for the user-specified column ID. Updates presented to the application by the Transaction Log API may contain information about the columns of a table. This column information contains a system-specified column number and a user-specified column identifier. The user-specified column ID has the value 0 until set explicitly by this call.
This procedure requires the XLA privilege.
Syntax
ttSetUserColumnID('tblName', 'colName', repID)
Parameters
ttSetUserColumnID has these parameters:
Parameter | Type | Description |
---|---|---|
tblName |
TT_CHAR(61) NOT NULL | Table name.
Using a synonym to specify a table name is not supported. |
colName |
TT_CHAR(30) NOT NULL | Column name. |
repID |
TT_INTEGER NOT NULL | Integer identifier. |
Result set
ttSetUserColumnID returns no results.
Example
CALL ttSetUserColumnID('APP.SESSION', 'SESSIONID', 15);
See also
Description
This procedure explicitly sets the value of the user table ID. The table that each row is associated with is expressed with two codes: an application-supplied code called the user table ID and a system-provided code called the system table ID. Updates are presented to the application by the Transaction Log API in the form of complete rows. The user table ID has the value zero until explicitly set with the ttSetUserTableID procedure.
This procedure requires the XLA privilege.
Syntax
ttSetUserTableID('tblName', repID)
Parameters
ttSetUserTableID has these parameters:
Parameter | Type | Description |
---|---|---|
tblName |
TT_CHAR (61) NOT NULL | Table name.
Using a synonym to specify a table name is not supported. |
repID |
BINARY(8) NOT NULL | Integer identifier. |
Result set
ttSetUserTableID returns no results.
Example
CALL ttSetUserTableID('APP.SESSION', 0x123456);
See also
Description
This procedure estimates the size of a table or view. It returns a single row with a single DOUBLE column with the estimated number of bytes for the table. The table can be specified as either a table name or a fully qualified table name. A non-NULL nrows
parameter causes the table size to be estimated assuming the statistics of the current table scaled up to the specified number of rows. If the nrows
parameter is NULL, the size of the table is estimated with the current number of rows.
The current contents of the table are scanned to determine the average size of each VARBINARY and VARCHAR column. If the table is empty, the average size of each VARBINARY and VARCHAR column is estimated to be one-half its declared maximum size. The estimates computed by ttSize include storage for the table itself, VARBINARY and VARCHAR columns and all declared indexes on the table.
The table is scanned when this built-in procedure is called. The scan of the table can be avoided by specifying a non-NULL frac
value, which should be between 0 and 1. This value is used to estimate the average size of varying-length columns. The maximum size of each varying-length column is multiplied by the frac
value to compute the estimated average size of VARBINARY or VARCHAR columns. If the frac
parameter is not given, the existing rows in the table are scanned and the average length of the varying-length columns in the existing rows is used. If frac
is omitted and the table has no rows in it, then frac
is assumed to have the value 0.5.
This procedure requires the SELECT privilege on the specified table.
Syntax
ttSize('tblName', nRows, frac)
Parameters
ttSize has these parameters:
Parameter | Type | Description |
---|---|---|
tblName |
TT_CHAR(61) NOT NULL | Name of an application table. Can include table owner. This parameter is required.
Using a synonym to specify a table name is not supported. |
nRows |
TT_INTEGER | Number of rows to estimate in a table. This parameter is optional. |
frac |
BINARY_DOUBLE | Estimated average fraction of VARBINARY or VARCHAR column sizes. This parameter is optional. |
Result set
ttSize returns the result set:
Column | Type | Description |
---|---|---|
size |
BINARY_DOUBLE NOT NULL | Estimated size of the table, in bytes. |
Examples
CALL ttSize('ACCTS', 1000000, NULL); CALL ttSize('ACCTS', 30000, 0.8); CALL ttSize('SALES.FORECAST', NULL, NULL);
When using ttSize, you must first execute the command and then fetch the results. For example:
ODBC
double size; SQLLEN len; rc = SQLExecDirect(hstmt, "call ttSize('SalesData', 250000, 0.75)", SQL_NTS); rc = SQLBindColumn(hstmt, 1, SQL_C_DOUBLE, &size, sizeof double, &len); rc = SQLFetch(hstmt); rc = SQLFreeStmt(hstmt, SQL_CLOSE);
JDBC
. . . . . . String URL="jdbc:timesten:MyDataStore"; Connection con; double tblSize=0; . . . . . . con = DriverManager.getConnection(URL); CallableStatement cStmt = con.prepareCall(" {CALL ttSize('SalesData', 250000, 0.75) }"); if( cStmt.execute() ) { rs=cStmt.getResultSet(); if (rs.next()) { tblSize=rs.getDouble(1); } rs.close(); } cStmt.close(); con.close(); . . . . . .
Note
The ttSize procedure allows you to estimate how large a table will be with its full population of rows based on a small sample. For the best results, we recommend populating the table with at least 1,000 typical rows.
Description
This procedure returns all compiled SQL statements in the TimesTen SQL command cache.
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.
Description
This procedure returns command statistics update information. This procedure can be useful for certain operations, such as CREATE INDEX or DROP INDEX and other DDL operations that alter objects.
This procedure requires no privilege.
Syntax
ttSQLCmdCacheInfoGet()
Parameters
ttSQLCmdCacheInfoGet has no parameters.
Result set
ttSQLCmdCacheInfoGet returns the result set:
Parameter | Type | Description |
---|---|---|
cmdCount |
TT_INTEGER NOT NULL | Number of commands in the cache |
freeableCount |
TT_INTEGER NOT NULL | Count of number of freeable commands that can be garbage collected by the subdaemon at that moment. This number is obtained by examining the command information. |
size |
TT_BIGINT NOT NULL | The current total space allocated to store all the cached commands. |
Examples
To generate a list of all TimesTen instance users, use:
Command> CALL ttSQLCmdCacheInfoGet; < 5,4,12316 > 1 row found
Description
This procedure returns all detailed run-time query plans for SQL statements in the TimesTen SQL command cache. If no argument is supplied, this procedure displays the query plan for all valid commands in the TimesTen cache. For invalid commands, an error is returned that displays the text of the query and the syntax problems.
This procedure requires the ADMIN privilege.
Syntax
ttSQLCmdQueryPlan(commandID)
Parameters
ttSQLCmdQueryPlan 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 the query plan for all valid commands in the TimesTen cache. |
Result set
ttSQLQueryPlan 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. |
queryText |
TT_VARCHAR(1024) | The first 1024 characters of the SQL text for the current command. |
step |
TT_INTEGER | The step number of current operation in this run-time query plan. |
level |
TT_INTEGER | The level number of current operation in this run-time query plan. |
operation |
TT_CHAR(31) | The operation name of the current step in this run-time query plan. |
tblName |
TT_CHAR(31) | Name of the table used in this step, if any.
Using a synonym to specify a table name is not supported. |
tblOwnerName |
TT_CHAR(31) | Name of the owner of the table used in this step, if any. |
ixName |
TT_CHAR(31) | Name of the index used in this step, if any. |
indexedPred |
TTVARCHAR(1024) | In this step, if an index is used, the indexed predicate is printed if available. Not all expressions can be printed out and the output may be fragmented and truncated. "..." represents the unfinished portion of the expression. |
nonIndexedPred |
TT_VARCHAR(1024) | In this step, if a non-indexed predicate is used, the non-indexed predicate is printed if available. Not all expressions can be printed out and the output may be fragmented and truncated. "..." represents the unfinished portion of the expression. |
Examples
To display the query plan for SQLCmdID 528078576:
Command> call ttSqlCmdQueryPlan(528078576); < 528078576, select * from t1 where 1=2 or (x1 in (select x2 from t2, t5 where y2 in (select y3 from t3)) and y1 in (select x4 from t4)), <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL> > < 528078576, <NULL>, 0, 4, RowLkSerialScan , T1 , TTUSER , , , > < 528078576, <NULL>, 1, 7, RowLkTtreeScan , T2 , TTUSER , I2 , , > < 528078576, <NULL>, 2, 7, RowLkTtreeScan , T5 , TTUSER , I2 , , > < 528078576, <NULL>, 3, 6, NestedLoop , , , , , > < 528078576, <NULL>, 4, 6, RowLkTtreeScan , T3 , TTUSER , I1 , ( (Y3=Y2; ) ) , > < 528078576, <NULL>, 5, 5, NestedLoop , , , , , > < 528078576, <NULL>, 6, 4, Filter , , , , , X1 = X2; > < 528078576, <NULL>, 7, 3, NestedLoop(Left OuterJoin) , , , , , > < 528078576, <NULL>, 8, 2, Filter , , , , , > < 528078576, <NULL>, 9, 2, RowLkTtreeScan , T4 , TTUSER , I2 , , Y1 = X4; > < 528078576, <NULL>, 10, 1, NestedLoop(Left OuterJoin) , , , , , > < 528078576, <NULL>, 11, 0, Filter , , , , , > 13 rows found.
To display query plans for all valid queries, omit the argument for ttSqlCmdQueryPlan:
< 528079360, select * from t7 where x7 is not null or exists (select 1 from t2,t3 where not 'tuf' like 'abc'), <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL> > < 528079360, <NULL>, 0, 2, RowLkSerialScan , T7 , TTUSER , , , > < 528079360, <NULL>, 1, 3, RowLkTtreeScan , T2 , TTUSER , I2 , , NOT(LIKE( tuf ,abc ,NULL )) > < 528079360, <NULL>, 2, 3, RowLkTtreeScan , T3 , TTUSER , I2 , , > < 528079360, <NULL>, 3, 2, NestedLoop , , , , , > < 528079360, <NULL>, 4, 1, NestedLoop(Left OuterJoin) , , , , , > < 528079360, <NULL>, 5, 0, Filter , , , , , X7 > < 527576540, call ttSqlCmdQueryPlan(527973892), <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL> > < 527576540, <NULL>, 0, 0, Procedure Call , , , , , > < 528054656, create table t2(x2 int,y2 int, z2 int), <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL> > < 528066648, insert into t2 select * from t1, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL> > < 528066648, <NULL>, 0, 0, Insert , T2 , TTUSER , , , > < 528013192, select * from t1 where exists (select * from t2 where x1=x2) or y1=1, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL> > < 528061248, create index i1 on t3(y3), <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL> > < 528070368, call ttOptSetOrder('t3 t4 t2 t1'), <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL> > < 528070368, <NULL>, 0, 0, Procedure Call , , , , , > < 528018856, insert into t2 select * from t1, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL> > < 527573452, call ttsqlCmdCacheInfo(527973892), <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL> > < 527573452, <NULL>, 0, 0, Procedure Call , , , , , > ….. /* more rows here */
Description
This procedure returns the 5 digits that make up the release number of the current TimesTen instance.
Required privilege
This procedure requires no privilege.
Syntax
ttVersion()
Parameters
ttVersion has no parameters:
Result set
ttVersion returns the result set:
Column | Type | Description |
---|---|---|
major |
TT_INTEGER NOT NULL | The major release number. Indicates releases with major infrastructure and functionality changes. |
minor | TT_INTEGER NOT NULL | The minor release number. Indicates a version with new functionality changes, but no infrastructure changes. |
patch |
TT_INTEGER NOT NULL | The patch release number. Indicates a release that contains all bug fixes since the previous maintenance release. |
relDot4 | TT_INTEGER NOT NULL | Indicates a release with minor bug fixes. |
relDot5 | TT_INTEGER NOT NULL | This digit in the release number is reserved for future use. |
Example
CALL ttVersion( ); <11, 2, 1 , 1, 0> 1 row found.
In this case, the TimesTen release number is: 11.2.1.1.0.
Description
This procedure allows applications to specify that operations executed on the current connection should return a warning if they allocate memory and find that memory is low. If the value is set, a warning is returned for any operation that does an allocation and finds total memory in use to be above the connection's threshold value as specified by the PermWarnThreshold and TempWarnThreshold data store attributes. See "Data Store Attributes" for more information.
This procedure requires no privilege.
Syntax
ttWarnOnLowMemory(permanent, temporary)
Parameters
ttWarnOnLowMemory has these parameters:
Parameter | Type | Description |
---|---|---|
permanent |
TT_INTEGER NOT NULL | 1(enable) or 0 (disable) warnings for the permanent data partition. |
temporary |
TT_INTEGER NOT NULL | 1 (enable) or 0 (disable) warnings for the temporary data partition. |
Result set
ttWarnOnLowMemory returns no results.
Example
CALL ttWarnOnLowMemory(1, 0);
Enables low memory warnings for the permanent data partition only.
Note
By default, low memory warnings are not issued for either partition. Applications that want to receive these warnings must call this procedure. This procedure is connection specific, and so needs to be issued for each connection upon which warnings are desired. Also, the current setting does not persist to subsequent connections.
Description
This procedure returns transaction ID information for interpreting lock messages. The two result columns of ttXactIdGet are used in combination to uniquely identify a transaction in a data store. Taken individually, the columns are not interesting. The result should only be used to correlate with other sources of transaction information. The numbers may not follow a strict pattern.
This procedure requires no privilege.
Syntax
ttXactIdGet()
Parameters
ttXactIdGet has no parameters.
Result set
ttXactIdGet returns the result set:
Column | Type | Description |
---|---|---|
xactID |
TT_INTEGER | Connection ID. |
counter |
TT_BIGINT | An increasing number that is used to discriminate successive transactions of the same Transaction Id. |
Example
Command > automcommit 0; Command > call ttXactIdGet; <2,11> 1 row found Command > commit; Command > call ttXactIdGet <3, 12> 1 row found
Note
The output correlates to the values printed in lock error messages and ttXactAdmin lock information output.
See also
Description
This procedure creates the specified bookmark.
This procedure requires the XLA privilege.
Syntax
ttXlaBookmarkCreate('bookmark', 'replicated')
Parameters
ttXlaBookmarkCreate has the parameter:
Parameter | Type | Description |
---|---|---|
Parameter | Type | Description |
bookmark |
TT_CHAR (31) NOT NULL | The name of the bookmark to be created. |
replicated |
BINARY(1) | 0x00 or null (equivalent) for non-replicated bookmarks (default setting);
0x01 for replicated bookmarks. If null, non-replicated bookmarks are used. |
Result set
ttXlaBookmarkCreate returns no results.
Example
For non-replicated bookmark, execute the following:
Command > call ttXlaBookmarkCreate(mybookmark);
or
Command> call ttxlabookmarkcreate('mybkmk2',0x00);
For a replicated bookmark, execute the following:
Command > call ttXlaBookmarkCreate(mybookmark, 0x01);
For more details on XLA bookmarks, including replicated XLA bookmarks, see "About XLA bookmarks" in the Oracle TimesTen In-Memory Database C Developer's Guide.
See also
Description
This procedure deletes the specified bookmark. The bookmark cannot be deleted while it is in use.
This procedure requires the XLA privilege.
Syntax
ttXlaBookmarkDelete('bookmark')
Parameters
ttXlaBookmarkDelete has the parameter:
Parameter | Type | Description |
---|---|---|
bookmark |
TT_CHAR (31) NOT NULL | The name of the bookmark to be deleted. |
Result set
ttXlaBookmarkDelete returns no results.
Example
Command > call ttXlaBookmarkDelete('mybookmark');
Note
Before dropping a table that is subscribed to by an XLA bookmark, you must first drop all XLA bookmarks or unsubscribe from XLA tracking.
See also
Description
This procedure configures persistent XLA tracking of a table. This procedure cannot be executed when the specified bookmark is in use.
This procedure requires the XLA privilege.
Syntax
ttXlaSubscribe('tblName', 'bookmark')
Parameters
ttXlaSubscribe has the parameters:
Parameter | Type | Description |
---|---|---|
tblName |
TT_CHAR (61) NOT NULL | The name of the table to be tracked.
Using a synonym to specify a table name is not supported. |
bookmark |
TT_CHAR (31) NOT NULL | The name of the bookmark that the application uses to track this table. |
Result set
ttXlaSubscribe returns no results.
Example
Command > call ttXlaSubscribe ('SALLY.ACCTS', mybookmark);
See also
Description
This procedure stops persistent XLA tracking of a table. This procedure cannot be executed when the specified bookmark is in use.
This procedure requires the XLA privilege.
Syntax
ttXlaUnsubscribe('tblName', 'bookmark')
Parameters
ttXlaUnsubscribe has the parameters:
Parameter | Type | Description |
---|---|---|
tblName |
TT_CHAR (61) NOT NULL | The name of the table on which XLA tracking should be stopped.
Using a synonym to specify a table name is not supported. |
bookmark |
TT_CHAR (31) NOT NULL | The name of the bookmark that the application uses to track this table. |
Result set
ttXlaSubscribe returns no results.
Example
Command > call ttXlaSubscribe ('SALLY.ACCTS', mybookmark);
Note
Before dropping a table that is subscribed to by an XLA bookmark, you must first drop all XLA bookmarks or unsubscribe from XLA tracking.
See also
As an Amazon Associate I earn from qualifying purchases.