2 Built-In Procedures

ttAgingLRUConfig

Description

This procedure sets the Least Recently Used (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 database under a specified threshold by removing the least recently used data.

Data is removed if the database 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 cannot be specified on a cache group with the AUTOREFRESH attribute, unless the cache group is dynamic. For explicitly loaded cache groups with the AUTOREFRESH attribute, 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:

Result set

ttAgingLRUConfig returns these results:

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

ttAgingScheduleNow

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 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 must 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.

Required privilege

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:

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, 2011-03-25 13:06:29.000000 >
< 2, 2011-03-25 13:06:42.000000 >
2 rows found.

Command> CALL ttAgingScheduleNow ('agingex');

Command> SELECT * FROM agingex;
0 rows found.

See also

ttApplicationContext

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.

Required privilege

This procedure requires no privilege.

Syntax

ttApplicationContext (cmd)

Parameters

ttApplicationContext has the parameter:

Result set

ttApplicationContext returns no results.

Example

CALL ttApplicationContext (0x123);

See also

"XLA Reference" in Oracle TimesTen In-Memory Database C Developer's Guide

ttBackupStatus

Description

This procedure returns a single row with information about the current or last backup of the database. 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.

Required privilege

This procedure requires the ADMIN privilege.

Syntax

ttBackupStatus ()

Parameters

ttBackupStatus has no parameters.

Result set

ttBackupStatus returns the results:

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 database startup or shutdown.

ttBlockInfo

Description

This procedure provides information about perm blocks and the amount of block-level fragmentation in a database.

Required privilege

This procedure requires no privilege.

Syntax

ttBlockInfo()

Parameters

ttBlockInfo has no parameters.

Result set

ttBlockInfo returns the result set:

Examples

CALL ttBlockInfo();
< 288, 3, 128711700, 128698596 >
1 row found.

ttBookmark

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

Required privilege

This procedure requires no privilege.

Syntax

ttBookmark()

Parameters

ttBookmark has no parameters.

Result set

ttBookmark returns the result set:

Example

CALL ttBookmark ();

ttCacheAutorefresh

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.

Required privilege

This procedure requires the CACHE_MANAGER or ADMIN privilege.

Syntax

ttCacheAutorefresh ('cacheGroupOwner', 'cacheGroupName', synchronous)

Parameters

ttCacheAutorefresh has the parameters:

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 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.

ttCacheAutorefreshStatsGet

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.

Required privilege

This procedure requires no privilege.

Syntax

ttCacheAutorefreshStatsGet ('cacheGroupOwner', 'cacheGroupName')

Parameters

ttCacheAutorefreshStatsGet has the parameters:

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:

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, 2011-07-23 15:43:52.000000, 850280, 44, 0, 75464, 528255, 75464, 310,
 110, 6800, 1890912, 12439795, 1890912, 160020, InProgress, 2, 74 >
< 1164260, 2011-07-23 15:43:33.000000, 831700, 43, 13550, 108544, 759808, 108544,
 1030, 230, 12290, 1815448, 11911540, 1815448, 160020, Complete, 2, 72 >
< 1164260, 2011-07-23 15:43:12.000000, 810230, 42, 17040, 115712, 809984, 115712,
 610, 330, 16090, 1706904, 11151732, 1706904, 146470, Complete, 2, 70>
< 1164260, 2011-07-23 15:42:52.000000, 790190, 41, 14300, 94208, 659456,
 94208,560, 320, 13410, 1591192, 10341748, 1591192, 129430, Complete, 2, 68 >
< 1164260, 2011-07-23 15:42:32.000000, 770180, 40, 12080, 99328, 695296,
 99328,450, 290, 11340, 1496984, 9682292, 1496984, 115130, Complete, 2, 66 >
< 1164260, 2011-07-23 15:42:12.000000, 750130, 39, 10380, 86016, 598368,
 86016,430, 230, 9720, 1397656, 8986996, 1397656, 103050, Complete, 2, 64 >
< 1164260, 2011-07-23 15:41:52.000000, 730130, 38, 13530, 112640, 700768, 112640,
 530, 220, 12780, 1311640, 8388628, 1311640, 92670, Complete, 2,62 >
< 1164260, 2011-07-23 15:41:32.000000, 710120, 37, 9370, 56320, 326810, 56320,
310, 160, 8900, 1199000, 7687860, 1199000, 79140, Complete, 2, 60 >
< 1164260, 2011-07-23 15:41:22.000000, 700120, 36, 2120, 10240, 50330, 10240, 50,
 200, 1870, 1142680, 7361050, 1142680, 69770, Complete, 2, 58 >
< 1164260, 2011-07-23 15:41:12.000000, 690110, 35, 0, 0, 0, 0, 0, 0, 0, 1132440,
 7310720, 1132440, 67650, Complete, 2, 56 >
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.

ttCacheAwtMonitorConfig

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.

Run this procedure on the replication node that is replicating AWT changes to Oracle. If the active standby pair is functioning normally, the node replicating AWT changes is the standby. If the active is operating stand alone, the node replicating AWT changes is the active.

If a failure occurs on the node where the active database resides, the standby node becomes the new active node. In that case you would run this procedure on the new active node.

Required privilege

This procedure requires the CACHE_MANAGER privilege.

Syntax

ttCacheAWTMonitorConfig ('state', samplingRate)

Parameters

ttCacheAWTMonitorConfig has the parameters:

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.

Examples

Command> CALL ttCacheAwtMonitorConfig;
< OFF, 0 >
1 row found.

Command> CALL ttCacheAwtMonitorConfig ('ON', 16);
< ON, 16 >
1 row found.

Command> CALL ttCacheAwtMonitorConfig; ('OFF')
< OFF, 0 >
1 row found.

See also

"ttRepAdmin"

ttCacheAWTThresholdGet

Description

This procedure returns the current transaction log file threshold for databases 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:

Example

CALL ttCacheAWTThresholdGet();

Note

This procedure is available only for IMDB Cache.

See also

ttCacheAWTThresholdSet

ttCacheAWTThresholdSet

Description

This procedure sets the threshold for the number of transaction log files that can accumulate before AWT is considered either terminated or too far behind to catch up. This setting applies to all subscribers to the database. 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 database.

Required privilege

This procedure requires the CACHE_MANAGER privilege.

Syntax

ttCacheAWTThresholdSet(threshold)

Parameters

ttCacheAWTThresholdSet has the parameter:

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

ttCacheAWTThresholdGet

ttCacheConfig

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 database is considered terminated.

The automatic refresh state of the database and cache groups can be determined from the procedure ttCacheDbCgStatus.

Required privilege

This procedure requires the CACHE_MANAGER privilege.

Syntax

ttCacheConfig(Param, tblOwner, tblName, Value)

Parameters

ttCacheConfig has these parameters:

Result set

ttCacheConfig returns no results when it is used to set parameter values. When it is used to return parameter settings, it returns the following results.

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.

You must call the ttCacheGrid built-in procedure from every node in a cache grid or a active standby pair. For more information, see "Reporting Oracle execution errors for AWT cache groups" in the Oracle In-Memory Database Cache User's Guide.

See also

ttCacheDbCgStatus

Description

This procedure returns the automatic refresh status of the database and the specified cache group. If you do not specify any values for the parameters, the procedure returns the automatic refresh status for the database.

Required privilege

This procedure requires no privilege.

Syntax

ttCacheDbCgStatus([cgowner, cgName])

Parameters

ttCacheDbCgStatus has these optional parameters:

Result set

ttCacheDbCgStatus returns the result:

Examples

This example shows that the automatic refresh status of the database 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 database, call ttCacheDbCgStatus with no parameters:

CALL ttCacheDbCgStatus;
< dead, <NULL> >
1 row found.

Note

This procedure is available only for IMDB Cache.

See also

ttCacheDDLTrackingConfig

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:

Result set

ttCacheDDLTrackingConfig returns no results.

Examples

Command> CALL ttCacheDDLTrackingConfig('enable');

ttCachePolicyGet

Description

This procedure returns the current policy used to determine when the TimesTen cache agent for the connected database should run. The policy can be either always or manual.

Required privilege

This procedure requires no privilege.

Syntax

ttCachePolicyGet()

Parameters

ttCachePolicyGet has no parameters.

Result set

ttCachePolicyGet returns the result:

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

ttCachePolicySet

Description

The procedure defines the policy used to determine when the TimesTen cache agent for the connected database should run. The policy can be either always or manual.

Required privilege

This procedure requires the CACHE_MANAGER privilege.

Syntax

ttCachePolicySet('cachePolicy')

Parameters

ttCachePolicySet has these parameters:

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 database with a relative path, TimesTen looks for the database relative to where TimesTen Data Manager is running, and fails. For example, on Windows, if you specify the path for the database 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 database 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

ttCachePropagateFlagSet

Description

This procedure allows you to temporarily stop any updates from propagating to Oracle.

Required privilege

This procedure requires the CACHE_MANAGER privilege.

Syntax

ttCachePropagateFlagSet(CommitsOn)

Parameters

ttCachePropagateFlagSet has the parameter:

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.

ttCacheSqlGet

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.

Required privilege

This procedure requires the CACHE_MANAGER privilege.

Syntax

ttCacheSqlGet('feature_name', 'cache_group_name', install_flag)

Parameters

ttCacheSqlGet has these parameters:

Result set

ttCacheSqlGet returns the result set:

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 database has been destroyed by the ttDestroy utility.

ttCacheStart

Description

This procedure starts the TimesTen cache agent for the connected database.

Required privilege

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 database.

If you attempt to start the TimesTen cache agent (by changing the policy from manual to always) for a database with a relative path, TimesTen looks for the database relative to where the TimesTen Data Manager is running, and fails. For example, on Windows, if you specify the path for the database 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 database 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 database-level locking (LockLevel=1).

See also

ttCacheStop

Description

This procedure stops the TimesTen cache agent for the connected database.

Required privilege

This procedure requires the CACHE_MANAGER privilege.

Syntax

ttCachdeStop(timeout)

Parameters

ttCacheStop has the parameter:

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 database-level locking (LockLevel=1).

See also

ttCacheUidGet

Description

This procedure returns the cache administration user ID for the database. If the cache administration user ID and password have not been set for the database, ttCacheUidGet returns NULL.

Required privilege

This procedure requires CACHE_MANAGER privilege.

Syntax

ttCacheUidGet()

Parameters

ttCacheUidGet has no parameters.

Result set

ttCacheUidGet returns the results:

Example

CALL ttCacheUidGet();

Note

This procedure is available only for IMDB Cache.

See also

ttCacheUidPwdSet

Description

This procedure sets the cache administration user ID and password. You only need to specify the cache administration user ID and password once for each new database. The cache administration password can be changed at any time.

Required privilege

This procedure requires the CACHE_MANAGER privilege.

Syntax

ttCacheUidPwdSet('UID', 'PWD')

Parameters

ttCacheUidPwdSet has these parameters:

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 database.

See also

ttCkpt

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 database on disk and to purge transaction log files. A non-blocking checkpoint does not require any locks on the database.

Applications should checkpoint databases periodically either by setting the background checkpointing attributes (CkptFrequency and CkptLogVolume) or by explicitly calling this procedure. Applications can call this procedure asynchronously to any other application running on the database.

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.

When a database crashes, and the checkpoints on disk are non-blocking checkpoints, TimesTen uses the log to recover.

Required privilege

This procedure requires the ADMIN privilege.

Syntax

ttCkpt()

Parameters

ttCkpt has these optional parameters:

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

ttCkptBlocking

Description

This procedure performs a blocking checkpoint operation. A checkpoint operation is used to make a record of the current state of the database on disk, and to purge transaction log files. This checkpoint requires exclusive access to the database, and so may cause other applications to be blocked from the database 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 database up to date after recovery.

Required privilege

This procedure requires the ADMIN privilege.

Syntax

ttCkptBlocking(timeout, retries)

Parameters

ttCkptBlocking has these optional parameters:

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

ttCkptConfig

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 database 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:

Result set

ttCkptConfig returns the following results.

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

ttCkptHistory

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:

Examples

This example shows a checkpoint in progress:

Call ttckpthistory;
< 2011-04-14 16:56:34.169520, <NULL>, Fuzzy           , In Progress     , User
, <NULL>, 0, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>
<NULL>, 13, 6 >

< 2011-04-14 16:55:47.703199, 2011-04-14 16:55:48.188764, Fuzzy           , 
Completed       , Checkpointer    , <NULL>, 1, 0, 8964304, 294, 33554432, 291, 
5677288, 27, 1019512, 1065408, <NULL>, 5 >

< 2011-04-14 16:54:47.106110, 2011-04-14 16:54:47.723379, Static          , 
Completed       , Subdaemon       , <NULL>, 0, 0, 8960328, 294, 33554432, 291, 
5677288, 256, 33157172, 5321548, <NULL>, 4 >

< 2011-04-14 16:54:41.633792, 2011-04-14 16:54:42.568469, Blocking        , 
Completed       , User            , <NULL>, 1, 0, 8958160, 294, 33554432, 291, 
5677288, 31, 1162112, 6604976, <NULL>, 3 >

< 2011-04-14 16:54:37.438827, 2011-04-14 16:54:37.977301, Static          , 
Completed       , User            , <NULL>, 0, 0, 1611984, 93, 33554432, 92, 
1853848, 93, 33554432, 1854052, <NULL>, 2 >

< 2011-04-14 16:54:36.861728, 2011-04-14 16:54:37.438376, Static          , 
Completed       , User            , <NULL>, 1, 0, 1609936, 93, 33554432, 92, 
1853848, 93, 33554432, 1854052, <NULL>, 1 >

6 rows found.

This example shows that an error occurred during the most recent checkpoint attempt:

call ttckpthistory;
< 2011-04-14 16:57:14.476860, 2011-04-14 16:57:14.477957, Fuzzy           , Failed 
, User            , 847, 1, <NULL>, <NULL>, 0, 0, 0, 0, 0, 0, 0, <NULL>, 7 >

< 2011-04-14 16:56:34.169520, 2011-04-14 16:56:59.715451, Fuzzy           , 
Completed       , User            , <NULL>, 0, 0, 8966472, 294, 33554432, 291, 
5677288, 5, 522000, 532928, <NULL>, 6 >

< 2011-04-14 16:55:47.703199, 2011-04-14 16:55:48.188764, Fuzzy           , 
Completed       , Checkpointer    , <NULL>, 1, 0, 8964304, 294, 33554432, 291, 
5677288, 27, 1019512, 1065408, <NULL>, 5 >

< 2011-04-14 16:54:47.106110, 2011-04-14 16:54:47.723379, Static          , 
Completed       , Subdaemon       , <NULL>, 0, 0, 8960328, 294, 33554432, 291, 
5677288, 256, 33157172, 5321548, <NULL>, 4 >

< 2011-04-14 16:54:41.633792, 2011-04-14 16:54:42.568469, Blocking        , 
Completed       , User            , <NULL>, 1, 0, 8958160, 294, 33554432, 291, 
5677288, 31, 1162112, 6604976, <NULL>, 3 >

< 2011-04-14 16:54:37.438827, 2011-04-14 16:54:37.977301, Static          , 
Completed       , User            , <NULL>, 0, 0, 1611984, 93, 33554432, 92, 
1853848, 93, 33554432, 1854052, <NULL>, 2 >

< 2011-04-14 16:54:36.861728, 2011-04-14 16:54:37.438376, Static          , 
Completed       , User            , <NULL>, 1, 0, 1609936, 93, 33554432, 92, 
1853848, 93, 33554432, 1854052, <NULL>, 1 >

7 rows found.

Notes

Results are ordered by start time, with the most recent first.

A failed row is overwritten by the next checkpoint attempt.

See also

ttCompact

Description

This procedure compacts both the permanent and temporary data partitions of the database.

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 is supported for backward compatibility. New applications should not call it.

Required privilege

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

ttCompactTS

ttCompactTS

Description

This procedure is similar to ttCompact, except that ttCompactTS may be used to compact a small fraction of the database, while ttCompact compacts the entire database. ttCompactTS is a time-sliced version of ttCompact. ttCompactTS iterates through all the blocks in the database compacting the quantum specified each time. When a sweep is completed, the value of the DS_COMPACTS field in the MONITOR table is incremented.

This procedure is supported for backward compatibility. New applications should not call it.

Required privilege

This procedure requires the ADMIN privilege.

Syntax

ttCompactTS(quantum)

Parameters

ttCompactTS has the parameter:

Result set

ttCompactTS returns no results.

Example

CALL ttCompactTS (5);

Note

Compacting data does not modify result addresses.

See also

ttCompact

ttComputeTabSizes

Description

The ttComputeTabSizes built-in procedure refreshes table size statistics stored in TimesTen system tables. After calling this built-in procedure, you can review the statistics updates by querying the DBA_TAB_SIZES, USER_TAB_SIZES or ALL_TAB_SIZES view.

This procedure computes the different types of storage allocated for the specified table, such as the amount of storage allocated for in-line row storage, out-of-line buffers and system usage. If no table is specified, the procedure computes the sizes for all tables on which the user has SELECT privileges.The execution of this built-in behaves like a DDL statement: the transaction commits just before the procedure begins and commits again upon its successful termination.

Required privilege

This procedure requires the SELECT privilege on the specified table.

Syntax

ttComputeTabSizes (['tblName'], [includeOutOfLine])

Parameters

ttComputeTabSizes has the parameters:

Result set

ttComputeTabSizes returns no results.

Example

To compute the size of my_table without including out-of-line columns, use:

CALL ttComputeTabSizes ('my_table', 0);

Note

The built-in procedure allows concurrent insertions while ttComputeTabSizes is executing. For this reason, the size computed by ttComputeTabSizes for each table is any value between the minimum size of the table during the computation and the maximum size of the table during the computation. For example, if a table is 250 MB in size when ttComputeTabSizes is executed, and a transaction running concurrently raises its size to 300 MB, ttComputeTabSizes estimates a value between 250 and 300 MB.

See also

ttSize

ttConfiguration

Description

The ttConfiguration procedure returns the values for most connection attributes for the current database connection.

Required privilege

This procedure requires no privilege.

Syntax

ttConfiguration('paramName')

Parameters

ttConfiguration has the optional parameter:

Result set

ttConfiguration returns the result set:

Example

To see the value of the QueryThreshold connection attribute, use

CALL ttConfiguration('querythreshold');
<QueryThreshold, 0>
1 row found

To see the values of all attributes, use:

CALL ttConfiguration();
< CacheGridEnable, 1 >
< CacheGridMsgWait, 60 >
< CkptFrequency, 600 >
< CkptLogVolume, 0 >
. . .

Note

Client driver attributes are not returned by this procedure.

See also

Connection Attributes

ttContext

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 database from the list of connections presented by the ttStatus utility and the ttDataStoreStatus built-in procedure.

Required privilege

This procedure requires no privilege.

Syntax

ttContext()

Parameters

ttContext has no parameters.

Result set

ttContext returns the result set:

Example

CALL ttContext;

Note

The context value numbers are unique only within a process. The context value number is not unique within the entire database. Therefore you may see the same context value number for different processes.

See also

ttDataStoreStatus

Description

This procedure returns the list of processes connected to a database. If the dataStore parameter is specified as NULL, then the status of all active databases is returned.

The result set is similar to the printed output of the ttStatus utility.

Required privilege

This procedure requires no privilege.

Syntax

ttDataStoreStatus('dataStore')

Parameters

ttDataStoreStatus has the parameter:

Result set

ttDataStoreStatus returns the result set:

Example

CALL ttDataStoreStatus('/data/Purchasing');

See also

ttDurableCommit

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 database with DurableCommits disabled.

Calling ttDurableCommit also makes durable the current transaction and any previously committed delayed durability transactions. 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.

Required privilege

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 (UPS).

ttGridAttach

Description

This procedure attaches a grid member to an existing local 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 cannot be used remotely.

Required privilege

This procedure requires the CACHE_MANAGER privilege.

Syntax

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:

Result set

ttGridAttach returns no results.

Examples

To attach to a standalone TimesTen database to a grid:

CALL ttGridAttach (1, 'alone2','sys2',5002);

To attach an active master database to a grid:

CALL ttGridAttach(1,'cacheact','sys1',5003,'cachestand','sys2',5004);

To attach a standby master database 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

ttGridCheckOwner

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.

Required privilege

This procedure requires the CACHE_MANAGER privilege.

Syntax

ttGridCheckOwner(['cvName', 'cvOwner'])

Parameters

ttGridCheckOwner has the optional parameter:

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

ttGridCreate

Description

This procedure creates a cache grid. This built-in procedure must be run only once. You can run it from any standalone database or from the active or standby master database in an active standby pair.

You must commit after calling this procedure if AUTOCOMMIT=0.

Required privilege

This procedure requires the CACHE_MANAGER privilege.

Syntax

ttGridCreate('gridName')

Parameters

ttGridCreate has the parameter:

Result set

ttGridCreate returns no results.

Example

To create a grid named mygrid:

CALL ttGridCreate ('mygrid');

See also

ttGridDestroy

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 cache groups. Before destroying a cache grid, detach all the TimesTen databases from the cache grid. To force the grid to be destroyed, supply a value of '1' as an argument to the force parameter.

Required privilege

This procedure requires the CACHE_MANAGER privilege.

Syntax

ttGridDestroy('gridName', [force])

Parameters

ttGridDestroy has the parameters:

Result set

ttGridDestroy returns no results.

Example

To destroy the mygrid cache grid with force, use:

CALL ttGridDestroy ('mygrid', 1);

See also

ttGridDetach

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.

Required privilege

This procedure requires the CACHE_MANAGER privilege.

Syntax

ttGridDetach([['nodeMemberName',] [force] [oraclePropWaitSec])

Parameters

ttGridDetach has the optional parameters:

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

ttGridDetachAll

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.

Required privilege

This procedure requires the CACHE_MANAGER privilege.

Syntax

ttGridDetachAll([oraclePropWaitSec])

Parameters

ttGridDetachAll has the optional parameter:

Result set

ttGridDetachAll returns no results.

Examples

To detach all grid members, use:

CALL ttGridDetachAll();

See also

ttGridDetachList

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:

Result set

ttGridDetachList returns no results.

Example

CALL ttGridDetachList('TTGRID_cacheact_3A TTGRID_cachestand_3B',1, );

See also

ttGridGlobalCGResume

Description

This procedure resumes operations that were blocked after a call to ttGridGlobalCGSuspend.

Required privilege

This procedure requires the CACHE_MANAGER privilege.

Syntax

ttGridGlobalCGResume()

Parameters

ttGridGlobalCGResume has no parameters.

Result set

ttGridGlobalCGResume returns no results.

Examples

To detach all grid members, use:

CALL ttGridGlobalCGResume();

See also

ttGridGlobalCGSuspend

Description

This procedure temporarily blocks dynamic loading and deleting cache instances for global cache groups. Use the ttGridGlobalCGResume procedure to re-enable these actions.

Required privilege

This procedure requires the CACHE_MANAGER privilege.

Syntax

ttGridGlobalCGSuspend(wait)

Parameters

ttGridGlobalCGSuspend has the parameter:

Result set

ttGridGlobalCGSuspend returns no results.

Examples

To set a wait interval of 10 seconds:

CALL ttGridGlobalCGSuspend(10);

See also

ttGridInfo

Description

This procedure returns information about the specified cache grid or all cache grids.

Required privilege

This procedure requires the CACHE_MANAGER privilege.

Syntax

ttGridInfo(['gridName'])

Parameters

ttGridInfo has the optional parameter:

Result set

ttGridInfo returns information about the cache grid.

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

ttGridNameSet

Description

This procedure associates a TimesTen database with a grid.

Required privilege

This procedure requires the CACHE_MANAGER privilege.

Syntax

ttGridNameSet('gridName')

Parameters

ttGridNameSet has the parameter:

Result set

ttGridNameSet returns no results.

Example

To associate the database with the grid mygrid, use:

CALL ttGridNameSet('mygrid');

See also

ttGridNodeStatus

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.

Required privilege

This procedure requires the CACHE_MANAGER privilege.

Syntax

ttGridNodeStatus(['gridName'])

Parameters

ttGridNodeStatus has the optional parameter:

Result Set

ttGridNodeStatus returns the results:

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 database, 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

ttGridUserAssignedName

Description

This procedure returns the name of the current local host for the database. The value returned is only for the current session. It is not a systemwide 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.

Required privilege

This procedure requires no privilege.

Syntax

ttGridUserAssignedName()

Parameters

ttGridUserAssignedName has no parameters.

Result set

ttGridUserAssignedname returns the result:

Example

CALL ttGriduserAssignedName ();

See also

ttHostNameGet

Description

This procedure returns the name of the current local host for the database. The value returned is only for the current session. It is not a systemwide 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.

Required privilege

This procedure requires no privilege.

Syntax

ttHostnameGet()

Parameters

ttHostNameGet has no parameters.

Result set

ttHostNameGet returns the result:

Example

CALL ttHostNameGet ();

See also

ttHostNameSet

Description

This procedure specifies the name of the default local host for the current database. The value is only used in the current session, it is not a systemwide 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 computer 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.

Required privilege

This procedure requires the ADMIN privilege.

Syntax

ttHostnameSet('hostName')

Parameters

ttHostNameSet has the parameter:

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

ttIndexAdviceCaptureDrop

Description

This procedure drops existing capture data for either the current connection or for the database. Subsequent calls to ttIndexAdviceCaptureOutput at that level return no rows.

This procedure and the procedures related to it are referred to as the Index Advisor. For details on using these procedures, see "Using the Index Advisor to recommend indexes" in the Oracle TimesTen In-Memory Database Operations Guide.

Required privilege

This procedure requires no privileges to drop a connection level capture.

This procedure requires ADMIN privileges to drop a database level capture.

Syntax

ttIndexAdviceCaptureDrop(captureLevel)

Parameters

ttIndexAdviceCaptureDrop has this optional parameter:

Result set

ttIndexAdviceCaptureDrop returns no results.

Example

CALL ttIndexAdviceCaptureDrop;

Note

To drop both connection level and database level captures, invoke the command twice, once for each capture level.

It is an error to call this command while a capture is in progress at the level you are attempting to drop.

See also

ttIndexAdviceCaptureEnd

Description

This procedure ends either an active connection level capture from the current connection or an active database level capture.

This procedure and the procedures related to it are referred to as the Index Advisor. For details on using these procedures, see "Using the Index Advisor to recommend indexes" in the Oracle TimesTen In-Memory Database Operations Guide.

Required privilege

This procedure requires no privilege to end a connection level capture.

This procedure requires ADMIN privileges to end a database level capture.

Syntax

ttIndexAdviceCaptureEnd(captureLevel)

Parameters

ttIndexAdviceCaptureEnd has this optional parameter:

Result set

ttIndexAdviceCaptureEnd returns no results.

Example

The following example ends the collection for the connection level capture:

Call ttIndexAdviceCaptureEnd(0)

Note

To end both connection level and database level captures, invoke the command twice, once for each capture level.

It is an error to call this procedure without first starting a capture at the specified level by calling the ttIndexAdviceCaptureStart procedure.

See also

ttIndexAdviceCaptureInfoGet

Description

This procedure returns a row for each active capture. A capture is active if it has started capturing index advice or if it has stopped capturing index advice, but the capture data is still available.

One row relates to a connection level capture, if one exists. Another row relates to a database level capture, if one exists. At most there will be one connection level and one database capture.

If no capture is in progress or no data exists, this procedure does not return any rows.

This procedure and the procedures related to it are referred to as the Index Advisor. For details on using these procedures, see "Using the Index Advisor to recommend indexes" in the Oracle TimesTen In-Memory Database Operations Guide.

Required privilege

This procedure requires no privilege to get information on a connection level capture.

This procedure requires ADMIN privileges to get information on a database level capture.

Syntax

ttIndexAdviceCaptureInfoGet()

Parameters

ttIndexAdviceCaptureInfoGet has no parameters.

Result set

ttIndexAdviceCaptureInfoGet returns the result set:

Example

This example shows capture information for a completed connection level capture for 363 prepared statements and 369 executed statements:

Command> CALL ttIndexAdviceCaptureInfoGet();
< 0, 1, 0, 0, 363, 369, 2012-07-27 11:44:08.136833, 2012-07-27 12:07:35.410993 >
1 row found.

Note

If there is an active database level capture and you call this procedure on a connection that does not have ADMIN privilege, TimesTen returns an error.

See also

ttIndexAdviceCaptureOutput

Description

This built-in returns the list of create index recommendations from the last recorded capture at the specified level.

This procedure and the procedures related to it are referred to as the Index Advisor. For details on using these procedures, see "Using the Index Advisor to recommend indexes" in the Oracle TimesTen In-Memory Database Operations Guide.

For a connection level capture, run this procedure in the same connection that initiated the capture. For a database level capture, run this procedure in a connection with ADMIN privileges.

Required privilege

This procedure requires no privilege to get output on a connection level capture.

This procedure requires ADMIN privileges to get output on a database level capture.

Syntax

ttIndexAdviceCaptureOutput(captureLevel)

Parameters

ttIndexAdviceCaptureOutput has this optional parameter:

Result set

ttIndexAdviceCaptureOutput returns the result set:

Example

The following example provides the CREATE statement for an index called PURCHASE_i1 on the HR.PURCHASE table. There are four distinct statements that would benefit from the index in this SQL workload.

CALL ttIndexAdviceCaptureOutput();
< 4, create index PURCHASE_i1 on HR.PURCHASE(AMOUNT); >
1 row found. 

Note

All names returned will be fully schema qualified.

See also

ttIndexAdviceCaptureStart

Description

This procedure enables index advice capture. It is recommended that statistics be updated before you call this procedure, using ttOptEstimateStats and setting the 'invalidate' parameter set to 'yes'. Updating the statistics in this way ensures statistics are up to date and forces statements to be re-prepared during the capture. To set statistics to known values instead, call ttOptSetTblStats with the 'invalidate' parameter set to 'yes'.

This procedure and the procedures related to it are referred to as the Index Advisor. For details on using these procedures, see "Using the Index Advisor to recommend indexes" in the Oracle TimesTen In-Memory Database Operations Guide.

Required privilege

This procedure requires no privilege to start a connection level capture.

This procedure requires ADMIN privileges to start a database level capture.

Syntax

ttIndexAdviceCaptureStart()

Parameters

ttIndexAdviceCaptureStart has this optional parameter:

Result set

ttIndexAdviceCaptureStart returns no results

Example

The following example starts a collection for the Index Advisor at the connection-level.

Call ttIndexAdviceCaptureStart(0,0);

Note

It is an error to call this procedure if index advice is already being captured at the database level. Connection level captures can be issued concurrently on independent connections without conflict. Outstanding connection level captures that are in progress when a database level capture begins complete as intended.

See also

ttLoadFromOracle

Description

This procedure takes a TimesTen table name, an Oracle SELECT statement and the number of threads for parallel load. It executes the query on Oracle and loads the result set into the specified TimesTen table.

The procedure requires the connection attribute UID, the connection attribute OraclePWD and the connection attribute OracleServiceName to be specified. You must commit after calling this procedure.

For more details and usage information, see "Loading Oracle data into a TimesTen table" in the Oracle TimesTen In-Memory Database Operations Guide.

Required privilege

This procedure requires INSERT privileges to the table to be loaded.The session must have all the required privileges to execute the query on the Oracle database.

Syntax

ttLoadFromOracle(tblOwner, tblName, Query, numThreads)

Parameters

ttLoadFromOracle has these parameters:

Result set

ttLoadFromOracle returns the result set:

Example

The following example selects information about employees from the Oracle HR.EMPLOYEES table and loads it into the TimesTen HR.EMPLOYEES table. If the table does not exist, this procedure creates the table. In this example information was found for 107 employees.

Command> CALL ttLoadFromOracle ('HR','EMPLOYEES','SELECT * FROM HR.EMPLOYEES');
< 107 >
1 row found.

Notes

TimesTen does not empty the table before the load. The target table does not require a primary key.TimesTen returns an error if the query output cannot be converted to rows in the target table due to a mismatch of column types or number of columns.Loading data into TimesTen LOB columns is not supported. If the Oracle query has LOB output, it is mapped to a variable type.

If the data from the Oracle query exceeds the TimesTen column size the data is truncated. LOB columns are truncated to 4MB.

See also

ttLockLevel

Description

Changes the lock level between row-level and database-level locking on the next transaction and for all subsequent transactions for this connection. Applications can change the lock level again by calling ttLockLevel again. The initial value depends on the LockLevel connection attribute. See "LockLevel" for full details of the different locking levels.

Required privilege

This procedure requires the ADMIN privilege.

Syntax

ttLockLevel('lockLevel')

Parameters

ttLockLevel has the parameter:

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 database-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

ttLockWait

ttLockWait

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 and 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.

Cache grid uses message wait time with lock wait time. When using cache grid, lock wait times are approximately half the value you have specified. If your applications require the full lock wait time, specify twice the desired seconds.

Required privilege

This procedure requires no privilege.

Syntax

ttLockWait(seconds)

Parameters

ttLockWait has the required parameters:

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 to get the lock. After that it times out the lock request and returns error TT6003 to the application. By default, TimesTen uses a value of 10 seconds for lock timeouts. If a value of 0 is specified, transactions do not wait for any unavailable locks.

See also

ttLogHolds

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.

Applications should monitor log holds and the accumulation of log files. For more information, see "Monitoring accumulation of transaction log files" in the Oracle TimesTen In-Memory Database Operations Guide.

Required privilege

This procedure requires no privilege.

Syntax

ttLogHolds()

Parameters

ttLogHolds has no parameters.

Result set

ttLogHolds returns the result set:

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 >

ttMonitorHighWaterReset

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 connection 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 connection attribute. These columns are useful for sizing databases during application development and deployment.

Required privilege

This procedure requires the ADMIN privilege.

Syntax

ttMonitorHighWaterReset()

Parameters

ttMonitorHighWaterReset has no parameters.

Result set

ttMonitorHighWaterReset returns no results.

Example

CALL ttMonitorHighWaterReset();

ttOptClearStats

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 System Tables and Views Reference.

Required privilege

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:

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 the current user's tables and reprepares all commands that affect these tables.

CALL ttOptClearStats('', 0);

Clears the statistics for all the current user's tables without repreparing commands that reference these tables.

See also

ttOptCmdCacheInvalidate

Description

You can use this procedure to recompile or invalidate dependent commands. Scenarios in which you may want to call this procedure include:

• After all needed statistics have been collected.

• When table cardinalities have been changed significantly.

The procedure either marks the command as needing recompilation or as invalidated.

Neither option stops execution of the command.

Required privilege

This procedure requires the DDL privilege.

Syntax

ttOptCmdCacheInvalidate('tblName', invalidate)

Parameters

ttOptCmdCacheInvalidate has these parameters:

Result set

ttOptCmdCacheInvalidate returns no results.

Example

To recompile dependent commands on the table tab1, use:

CALL ttOptCmdCacheInvalidate ('tab1', 1);

To invalidate the dependent commands on table tab1, use:.

CALL ttOptCmdCacheInvalidate ('tab1', 2);

See also

ttOptEstimateStats

Description

The ttOptEstimateStats 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').

Required privilege

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:

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, ttOptEstimateStats 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.

If you estimate or update statistics with an empty table list, statistics on system tables are updated also, if you have privileges to update the system tables.

See also

ttOptGetColStats

Description

This procedure returns statistics information in text format.

Required privilege

This procedure requires the SELECT privilege on the specified tables.

Syntax

ttOptGetColStats('tblName', 'colName')

Parameters

ttOptGetColStats has these parameters:

Result set

ttOptGetColStats returns the result set:

Examples

CALL ttOptGetColStats ();
< T1 , X1, (2, 10, 10, 100 (,4, 40, 10 ,1, 10, 5) ,(4, 20, 20 ,11, 20, 15) )>

See also

ttOptGetFlag

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.

Required privilege

This procedure requires no privilege.

Syntax

ttOptGetFlag('flagName')

Parameters

ttOptGetFlag has the parameter:

Result set

ttOptGetFlag returns the result set:

Examples

CALL ttOptGetFlag('TmpHash');

See also

ttOptSetFlag

ttOptGetMaxCmdFreeListCnt

Description

This procedure returns the size of the free list of SQL compiled command cache. To reset the size of the cache, use ttOptSetMaxPriCmdFreeListCnt for materialized views and ttOptSetMaxCmdFreeListCnt for regular tables.

Required privilege

This procedure requires no privilege.

Parameters

ttOptGetMaxCmdFreeListCnt has no parameters.

Syntax

ttOptGetMaxCmdFreeListCnt()

Result set

ttOptGetMaxCmdFreeListCnt returns the results.

Example

CALL ttOptGetMaxCmdFreeListCnt( );

See also

ttOptGetOrder

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.

Required privilege

This procedure requires no privilege.

Syntax

ttOptGetOrder( )

Parameters

ttOptGetOrder has no parameters.

Result set

ttOptGetOrder returns the result set:

Examples

CALL ttOptGetOrder;

See also

ttOptSetOrder

ttOptSetColIntvlStats

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.

Required privilege

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:

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:

CALL ttOptSetColIntvlStats('t1', 'x1', 1, (2, 10, 10, 100, (4, 40, 10, 1, 10, 5),
 (4, 20, 20, 11, 20, 15)));

Notes

You must specify the minimum and maximum values in the interval as VARBINARY. NULL values are not permitted as minimum or maximum values. The value is stored in the platform-specific endian format.

See also

ttOptSetColStats

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 the table is populated with data, the values specified do not need to bear any relation to the actual values, although some basic validity checking is performed.

Required privilege

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:

Result set

ttOptSetColStats returns no results.

Example

CALL ttOptSetColStats ('SALLY.ACCTS, 'BALANCE, 400, 0x00001388, 0x000186A0, 1, 0);

Notes

You must specify the minimum and maximum values 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

ttOptSetFlag

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.

Required privilege

This procedure requires no privilege.

Syntax

ttOptSetFlag('optFlag', optVal)

Parameters

ttOptSetFlag has these parameters:

Optimizer flags

When setting the optimizer flags, use the following character strings, which are not case sensitive:

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 except GenPlan 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:

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

ttOptSetMaxCmdFreeListCnt

Description

This procedure sets the maximum count of the free list of SQL compiled commands for regular tables. To get the current setting use the ttOptGetMaxCmdFreeListCnt procedure.

Required privilege

This procedure requires the ADMIN privilege.

Syntax

ttOptSetMaxCmdFreeListCnt(maxCnt)

Parameters

ttOptSetMaxCmdFreeListCnt has the required parameter:

Result set

ttOptSetMaxCmdFreeListCnt returns no results.

Example

CALL ttOptSetMaxCmdFreeListCnt(40);

See also

ttOptSetMaxPriCmdFreeListCnt

Description

This procedure sets the maximum count of the free list of SQL compiled commands that perform materialized view maintenance.

When this command is set, freeable materialized view compiled commands are counted separately from those of regular tables. If this command is not set, materialized view compiled commands are counted as regular commands.

Required privilege

This procedure requires the ADMIN privilege.

Syntax

ttOptSetMaxCmdPriFreeListCnt(maxCnt)

Parameters

ttOptSetMaxPriCmdFreeListCnt has the required parameter:

Result set

ttOptSetMaxPriCmdFreeListCnt returns no results.

Example

CALL ttOptSetMaxPriCmdFreeListCnt(40);

See also

ttOptSetOrder

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.

Required privilege

This procedure requires no privilege.

Syntax

ttOptSetOrder('joinOrder')

Parameters

ttOptSetOrder has the required parameter:

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');

Consider an application that makes the following 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

A table alias name for a derived table is not supported in the join order. If you specify a table alias name, TimesTen returns the warning message 965 that indicates the order cannot be honored.

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.

See also

ttOptSetTblStats

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.

Required privilege

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:

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 System Tables and Views Reference.

See also

ttOptShowJoinOrder

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.

Required privilege

This procedure requires no privilege.

Syntax

ttOptShowJoinOrder()

Parameters

ttOptShowJoinOrder has no parameters.

Result set

ttOptShowJoinOrder returns the result:

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

ttOptStatsExport

Description

This procedure returns the set of statements required to restore the table statistics to the current state. If no table is specified, it returns the set of statements required to restore the table statistics for all user tables that the calling user has permission to access.

Required privilege

This procedure requires ADMIN privilege.

Syntax

ttOptStatExport('tblName')

Parameters

ttOptStatsExport has the parameter:

Result set

ttOptStatsExport returns the result set:

Examples

CALL ttOptStatsExport('MyTable');

See also

ttOptUpdateStats

Description

This procedure updates the statistics for the specified table. TimesTen looks at the databases 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.

Required privilege

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:

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 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 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.

If you estimate or update statistics with an empty table list, statistics on system tables are updated also, if you have privileges to update the system tables.

See also

ttOptUseIndex

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.

Required privilege

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:

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

ttPLSQLMemoryStats

Description

This procedure returns result statistics about PL/SQL library cache performance and activity.

Required privilege

This procedure requires no privilege.

Syntax

ttPLSQLMemoryStats(paramName, paramValue )

Parameters

ttPLSQLMemoryStats takes no parameters.

Parameters

ttPLSQLMemoryStats returns the results in the following columns:

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 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.

ttRamPolicyAutoReloadGet

Description

This procedure returns the RAM autoreload policy used to determine if a database is reloaded into RAM after an invalidation. The policy can be either autoreload or noautoreload.

Required privilege

This procedure requires no privilege.

Syntax

ttRamPolicyAutoReloadGet()

Result set

ttRamPolicyAutoReloadGet returns the results:

Parameters

ttRamPolicyAutoReloadGet has no parameters.

Examples

To view the RAM autoreload policy, use:

CALL ttRamPolicyAutoReloadGet();

See also

ttRamPolicyAutoReloadSet

Description

This procedure determines the RAM autoreload policy if a database is invalidated. The policy can be either autoreload or noautoreload.

Required privilege

This procedure requires the ADMIN privilege.

Syntax

ttRamPolicyAutoReloadSet(ramPolicyAutoReload)

Parameters

ttRamPolicyAutoReloadSet has the parameters: