This section summarizes the new features of Oracle TimesTen In-Memory Database 11g Release 2 (11.2.2) that are documented in this guide and provides links to more information.
In a ttRepAdmin -duplicate operation, now you can specify a local or remote IP address for the destination of the duplicate, using the options -localIP and -remoteIP. See "Duplicate a database".
A new tool, the Index Advisor, can be used to recommend a set of indexes that could improve the performance of a specific SQL workload. The Index Advisor consists of several built-in procedures. The procedures that make up the Index Advisor are:
New tools have been added that enable you to load the results of a SQL query from a back-end Oracle database into a single table on TimesTen without creating a cache grid, cache group, and cache table to contain the results. TimesTen provides the tools that will execute a user provided SELECT statement on Oracle and load the result set into a table on TimesTen. The ttIsql utility is enhanced to include the LoadFromOracle command. In addition, two new built-in procedures are in this release as part of this feature:
The ttIsql utility now supports autovariables, showcurrenttime as a set/show attribute, the IF command, and WHENEVER error handling. See more details in the description of the ttIsql utility.
The new Automatic parallel replication allows for the parallel replication and application of transactions changes to the receiving nodes in a replication scheme. See Chapter 1, "Connection Attributes" for more details on parallel replication.
The ability to determine the current space usage of a table using the ttComputeTabSizes built-in procedure.
The new Automatic parallel replication allows for the parallel replication and application of transactions changes to the receiving nodes in a replication scheme. See Chapter 1, "Connection Attributes" for more details on parallel replication.
You can configure parallel propagation of changes in AWT cache tables to the corresponding Oracle tables. See Chapter 1, "Connection Attributes" for more details on parallel propagation.
The maximum value of the LogBufMB and LogFileSize connection attributes has increased to 64GB on 64-bit systems.
Reference
11g Release 2 (11.2.2)
E21643-10
September 2012
Oracle TimesTen In-Memory Database Reference, 11g Release 2 (11.2.2)
E21643-10
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
This software and related documentation are provided under a license agreement containing restrictions on use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is prohibited.
The information contained herein is subject to change without notice and is not warranted to be error-free. If you find any errors, please report them to us in writing.
If this is software or related documentation that is delivered to the U.S. Government or anyone licensing it on behalf of the U.S. Government, the following notice is applicable:
U.S. GOVERNMENT END USERS: Oracle programs, including any operating system, integrated software, any programs installed on the hardware, and/or documentation, delivered to U.S. Government end users are "commercial computer software" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, use, duplication, disclosure, modification, and adaptation of the programs, including any operating system, integrated software, any programs installed on the hardware, and/or documentation, shall be subject to license terms and license restrictions applicable to the programs. No other rights are granted to the U.S. Government.
This software or hardware is developed for general use in a variety of information management applications. It is not developed or intended for use in any inherently dangerous applications, including applications that may create a risk of personal injury. If you use this software or hardware in dangerous applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure its safe use. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this software or hardware in dangerous applications.
Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
Intel and Intel Xeon are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks are used under license and are trademarks or registered trademarks of SPARC International, Inc. AMD, Opteron, the AMD logo, and the AMD Opteron logo are trademarks or registered trademarks of Advanced Micro Devices. UNIX is a registered trademark of The Open Group.
This software or hardware and documentation may provide access to or information on content, products, and services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to third-party content, products, and services. Oracle Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use of third-party content, products, or services.
Oracle TimesTen In-Memory Database is a memory-optimized relational database. Deployed in the application tier, Oracle TimesTen In-Memory Database operates on databases that fit entirely in physical memory using standard SQL interfaces. High availability for the in-memory database is provided through real-time transactional replication.
This document provides a reference for TimesTen attributes, built-in procedures, and utilities.This document is intended for readers with a basic understanding of database systems.
TimesTen documentation is available on the product distribution media and on the Oracle Technology Network:
http://www.oracle.com/technetwork/products/timesten/documentation/
TimesTen supports multiple platforms. Unless otherwise indicated, the information in this guide applies to all supported platforms. The term Windows applies to all supported Windows platforms. The term UNIX applies to all supported UNIX platforms and also to Linux. Refer to the "Platforms" section in Oracle TimesTen In-Memory Database Release Notes for specific platform versions supported by TimesTen.
|
Note: In TimesTen documentation, the terms "data store" and "database" are equivalent. Both terms refer to the TimesTen database unless otherwise noted. |
This document uses the following text conventions:
| Convention | Meaning |
|---|---|
| italic | Italic type indicates terms defined in text, book titles, or emphasis. |
monospace | Monospace type indicates code, commands, URLs, function names, attribute names, directory names, file names, text that appears on the screen, or text that you enter. |
italic monospace | Italic monospace type indicates a placeholder or a variable in a code example for which you specify or use a particular value. For example:
Replace |
| [ ] | Square brackets indicate that an item in a command line is optional. |
| { } | Curly braces indicated that you must choose one of the items separated by a vertical bar ( | ) in a command line. |
| | | A vertical bar (or pipe) separates alternative arguments. |
| . . . | An ellipsis (. . .) after an argument indicates that you may use more than one argument on a single command line. An ellipsis in a code example indicates that what is shown is only a partial example. |
| % | The percent sign indicates the UNIX shell prompt. |
In addition, TimesTen documentation uses the following special conventions:
| Convention | Meaning |
|---|---|
install_dir | The path that represents the directory where TimesTen is installed. |
TTinstance | The instance name for your specific installation of TimesTen. Each installation of TimesTen must be identified at installation time with a unique instance name. This name appears in the installation path. |
bits or bb | Two digits, either 32 or 64, that represent either a 32-bit or 64-bit operating system. |
release or rr | The first three parts in a release number with or without dots. The first three parts of a release number represent a major TimesTen release. For example, 1122 or 11.2.2 represents TimesTen 11g Release 2 (11.2.2). |
DSN | TimesTen data source name (for the TimesTen database). |
For information about Oracle's commitment to accessibility, visit the Oracle Accessibility Program website at http://www.oracle.com/pls/topic/lookup?ctx=acc&id=docacc.
Access to Oracle Support
Oracle customers have access to electronic support through My Oracle Support. For information, visit http://www.oracle.com/pls/topic/lookup?ctx=acc&id=info or visit http://www.oracle.com/pls/topic/lookup?ctx=acc&id=trs if you are hearing impaired.
The following sections list all TimesTen system limits and defaults.
Specific operating system limits may take precedence over these values. For more information, see "Installation prerequisites" in Oracle TimesTen In-Memory Database Installation Guide.
Each process connected to a TimesTen database keeps at least one operating-system file descriptor open from the time of the first connection until the process terminates. Additional file descriptors may be opened for each database connection:
Connections to databases that have logging to disk enabled require an additional two file descriptors for the duration of the connection.
An additional file descriptor is needed for the duration of database checkpoints issued by the process.
Additional file descriptors may be opened during transaction commit or rollback operations.
For multithreaded applications that maintain many concurrent TimesTen database connections, the default number of open files permitted to each process by the operating system may be too low.
On Solaris, the default limit is 256 open files and may be raised for a session with the ulimit command (limit for csh users). You can also set the per-process limit programmatically with setrlimit.
On AIX, the limit is 2,048 open files, so you are not likely to encounter problems.
On Linux, the default limit is 1,024 open files, so you are not likely to encounter problems.
On Windows, the default limit is at least 2,000 open files, so you are not likely to encounter problems.
Most of the open file descriptors are used for reading and writing database recovery log files. If a process fails to open a log file, the database is marked as requiring recovery and all current connections to the database are terminated.
TimesTen built-in procedures extend standard ODBC and JDBC functionality. You can invoke these procedures using the ODBC or JDBC procedure call interface. The procedure takes the position of the SQL statement, as illustrated in the following examples.
The following ODBC call tells the optimizer that it should not generate temporary hash indexes when preparing commands:
SQLExecDirect (hstmt, (SQLCHAR*)
"{CALL ttOptSetFlag ('TmpHash', 0)}", SQL_NTS);
This is the equivalent JDBC call:
CallableStatement cstmt = con.prepareCall
("{CALL ttOptSetFlag ('TmpHash', 0)}");
cstmt.execute();
TimesTen built-in procedures can also be called from PL/SQL using the EXECUTE IMMEDIATE statement with CALL, as illustrated in the following example.
For example, to call the built-in procedure ttConfiguration, create a PL/SQL record type and then SELECT INTO that record type. Because ttConfiguration returns multiple rows, use BULK COLLECT.
Command> DECLARE
> TYPE ttConfig_record IS RECORD
> (name varchar2(255), value varchar2 (255));
> TYPE ttConfig_table IS TABLE OF ttConfig_record;
> v_ttConfigs ttConfig_table;
> BEGIN
> EXECUTE IMMEDIATE 'CALL ttConfiguration'
> BULK COLLECT into v_ttConfigs;
> DBMS_OUTPUT.PUT_LINE ('Name: ' || v_ttConfigs(1).name
> || ' Value: ' || v_ttConfigs(1).value);
> end;
> /
Name: CacheGridEnable Value: 0
PL/SQL procedure successfully completed.
|
Note: String parameter values for built-in procedures must be single-quoted as indicated in these examples, unless the value isNULL. |
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:
| Parameter | Type | Description |
|---|---|---|
lowUsageThreshold | BINARY_FLOAT | Sets, displays or resets the low end of percentage of database PermSize, specified in decimals. The bottom of the threshold range in which LRU aging should be deactivated. Default is 80%. |
highUsageThreshold | BINARY_FLOAT | Sets, displays or resets the high end of percentage of database PermSize, specified in decimals. The top of the threshold range in which LRU aging should be activated. Default is 90%. |
agingCycle | TT_INTEGER | Sets, displays or resets the number of minutes between aging cycles, specified in minutes. Default is 1 minute. If you use this procedure to change the aging cycle, the cycle is reset based on the time that this procedure is called. For example, if you call this procedure at 12:00 p.m. and specify a cycle of 15 minutes, aging occurs at 12:15, 12:30, 12:45, etc. |
Result set
ttAgingLRUConfig returns these results:
| Column | Type | Description |
|---|---|---|
lowUsageThreshold | BINARY_FLOAT NOT NULL | The current setting for the low end of percentage of database PermSize, specified in decimals. |
highUsageThreshold | BINARY_FLOAT NOT NULL | The current setting for the high end of percentage of database PermSize, specified in decimals. |
agingCycle | TT_INTEGER NOT NULL | The current setting for the number of minutes between aging cycles, specified in minutes. |
Examples
To set the aging threshold to a low of 75 percent and a high of 95 percent and the aging cycle to 5 minutes, use:
CALL ttAgingLRUConfig (.75, .90, 5); <.7500000, .9000000, 5>
To display the current LRU aging policy for all tables that defined with an LRU aging policy, call ttAgingLRUConfig without any parameters:
Call ttAgingLRUConfig();
If the tables are defined with the default thresholds and aging cycle, the procedure returns:
<.8000000, .9000000, 1> 1 row found.
To change the low usage threshold to 60%, the aging cycle to 5 minutes and to retain the previous high usage threshold, use:
Call ttAgingLRUConfig (60,,5); < .6000000, .9000000, 5 > 1 row found.
Notes
The values of this procedure are persistent, even across system failures.
If no parameters are supplied, this procedure only returns the current LRU aging attribute settings.
See also
Description
This procedure starts the aging process, regardless of the value of the aging cycle. The aging process begins right after the procedure is called unless there is 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.
This procedure requires the DELETE privilege on the table being aged, or the DELETE ANY TABLE privilege when you do not specify a table.
Syntax
ttAgingScheduleNow ('tblname')
Parameters
ttAgingScheduleNow has the parameter:
| Parameter | Type | Description |
|---|---|---|
tblname | TT_CHAR (61) | The name of the table on which to start the aging process.
If Using a synonym to specify a table name is not supported. |
Result set
ttAgingScheduleNow returns no results.
Examples
To schedule aging on all tables, including tables defined with both LRU aging and time-based aging, call ttAgingScheduleNow without any parameter values:
CALL ttAgingScheduleNow ();
This examples creates the table agingex with time-based aging policy and the aging state set to OFF. ttAgingScheduleNow is called, using the ttIsql utility, to start the aging process once. Rows are deleted from the table. After ttAgingScheduleNow is called, the aging state remains OFF. To continue aging, alter the table and set the aging state to OFF.
Command> CREATE TABLE agingex (col1 TT_INTEGER PRIMARY KEY NOT NULL,
ts TIMESTAMP NOT NULL)
AGING USE ts LIFETIME 1 MINUTES CYCLE 30 MINUTES OFF;
Command> DESCRIBE agingex;
Table TTUSER.AGINGEX:
Columns:
*COL1 TT_INTEGER NOT NULL
TS TIMESTAMP (6) NOT NULL
Aging use TS lifetime 1 minute cycle 30 minutes off
1 table found.
(primary key columns are indicated with *)
Command> INSERT INTO agingex VALUES (1, SYSDATE);
1 row inserted.
Command> INSERT INTO agingex VALUES (2, SYSDATE);
1 row inserted.
Command> SELECT * FROM agingex;
< 1, 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
Description
This procedure sets application-defined context for the next update record (either an UPDATE or commit) in order to pass application specific data to XLA readers.
This procedure requires no privilege.
Syntax
ttApplicationContext (cmd)
Parameters
ttApplicationContext has the parameter:
| Parameter | Type | Description |
|---|---|---|
cmd | VARBINARY(16384) NOT NULL | Context information to be passed. |
Result set
ttApplicationContext returns no results.
Example
CALL ttApplicationContext (0x123);
See also
"XLA Reference" in Oracle TimesTen In-Memory Database C Developer's Guide
Description
This procedure returns a single row with information about the current or last backup of the 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.
This procedure requires the ADMIN privilege.
Syntax
ttBackupStatus ()
Parameters
ttBackupStatus has no parameters.
Result set
ttBackupStatus returns the results:
| Column | Type | Description |
|---|---|---|
status | TT_INTEGER NOT NULL | An INTEGER code representing the current progress of a backup or the completion status of the last backup. Values are:
|
destination | TT_INTEGER | The type of backup taken. The value is NULL when no backup has been taken on the database. Value is one of:
|
backupType | TT_INTEGER | Backup type, either full or incremental. The value is NULL when no backup has been taken on the database. Value is one of:
|
startTime | TT_TIMESTAMP | Time when the backup was started. The value is NULL when no backup has been taken on the database. |
endTime | TT_TIMESTAMP | Time when the backup completed. If NULL and startTime is non-NULL, a backup is currently in progress. |
backupLFN | TT_INTEGER | The transaction log file number of the backup point. The value is NULL when no backup has been taken on the database. |
backupLFO | TT_BIGINT | The transaction log file offset of the backup point. The value is NULL when no backup has been taken on the database. |
error | TT_INTEGER | If a backup fails, this column indicates the reason for the failure. The value is one of the TimesTen error numbers. The value is NULL when no backup has been taken on the database. |
processId | TT_INTEGER | The ID of the process or daemon performing the backup (if known). |
Example
CALL ttBackupStatus (); < 2, 2, 1, 2005-08-12 13:10:32.587557, 2005-08-12 13:10:33.193269, 1, 1531840, 0, 6968 > 1 row found.
Notes
Does not return information about previous backups, other than the current or last one.
Information returned is not persistent across database startup or shutdown.
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:
| Column | Type | Description |
|---|---|---|
TotalBlocks | TT_BIGINT NOT NULL | Total number of blocks in the database. |
FreeBlocks | TT_BIGINT NOT NULL | Total number of free blocks in the database. |
FreeBytes | TT_BIGINT NOT NULL | Total size of the free blocks. |
LargestFree | TT_BIGINT NOT NULL | Size of the largest free block. |
Examples
CALL ttBlockInfo(); < 288, 3, 128711700, 128698596 > 1 row found.
Description
This procedure returns information about the TimesTen transaction log. Records in the transaction log are identified by pairs of integers:
A transaction log file number
An offset in that transaction log file
Transaction log file numbers correspond to the file system names given to transaction log files. For example, the transaction log file SalesData.log29 has the transaction log file number 29.
Three log records are identified in the result row of ttBookmark:
The identity of the most recently written log record
The identity of the log record most recently forced to the disk
The replication bookmark. The replication bookmark is the oldest log record that represents an update not yet replicated to another system
This procedure requires no privilege.
Syntax
ttBookmark()
Parameters
ttBookmark has no parameters.
Result set
ttBookmark returns the result set:
| Column | Type | Description |
|---|---|---|
writeLFN | TT_INTEGER | Last written transaction log file. |
writeLFO | TT_BIGINT | Last written offset in transaction log file. |
forceLFN | TT_INTEGER | Last transaction log file forced to disk. |
forceLFO | TT_BIGINT | Offset of last transaction log file forced to disk. |
holdLFN | TT_INTEGER | Replication bookmark transaction log file. |
holdLFO | TT_BIGINT | Replication bookmark log offset. |
Example
CALL ttBookmark ();
Description
This procedure starts an immediate autorefresh on the set of cache groups that are associated by sharing the same autorefresh interval with the specified cache group. This set of associated cache groups would normally be refreshed together automatically. The effect on the autorefresh process is the same as that of adding a new cache group with the same refresh interval as that of the specified cache group. This procedure is useful if updates have occurred on the Oracle database and you would like to refresh them on the cache group before the next scheduled autorefresh.
If there is an existing transaction with locks on table objects that belong to the set of cache groups to be autorefreshed, this procedure returns an error without taking any action. This procedure establishes a condition that requires that you commit or rollback before you can perform other work in the session.
This procedure requires the CACHE_MANAGER or ADMIN privilege.
Syntax
ttCacheAutorefresh ('cacheGroupOwner', 'cacheGroupName', synchronous)
Parameters
ttCacheAutorefresh has the parameters:
| Parameter | Type | Description |
|---|---|---|
cacheGroupOwner | VARCHAR2 (30) | Name of the cache group owner. |
cacheGroupName | VARCHAR2 (30) NOT NULL | Name of the cache group. |
synchronous | TT_INTEGER | Species whether data is updated on synchronously or asynchronously.
|
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.
Description
This procedure returns information about the last ten autorefresh transactions on the specified cache group. This information is only available when the AUTOREFRESH state is ON or PAUSED, and the cache agent is running.
The information returned by this built-in procedure is reset whenever:
The cache agent is restarted.
The state is set to OFF and then back to ON or PAUSED.
The cache group is dropped and recreated.
This procedure requires no privilege.
Syntax
ttCacheAutorefreshStatsGet ('cacheGroupOwner', 'cacheGroupName')
Parameters
ttCacheAutorefreshStatsGet has the parameters:
| Parameter | Type | Description |
|---|---|---|
cacheGroupOwner | VARCHAR2 (30) | Name of the cache group owner. |
cacheGroupName | VARCHAR2 (30) NOT NULL | Name of the cache group for which autorefresh information should be returned. |
Result set
The ttCacheAutorefreshStatsGet built-in procedure returns only a subset of column information for a cache group with autorefresh mode FULL. A column value of 0 returns for information that is not available.
ttCacheAutorefreshStatsGet returns the results:
| Column name | Column type | Description | Returned for full autorefresh |
|---|---|---|---|
cgId | TT_BIGINT | The cache group ID. | Y |
startTimestamp | TT_TIMESTAMP | Timestamp when autorefresh started for this interval. See "Notes" below. | Y |
cacheAgentUpTime | TT_BIGINT | Number of cache agent clock ticks in milliseconds at the time the autorefresh transaction started for this interval. This value is cumulative and is reset when the cache agent process starts. See "Notes" below. | Y |
autorefNumber | TT_BIGINT | Autorefresh number for a cache group indicates the number of times this cache group has been incrementally refreshed since the cache agent started. This number will be initialized to 0 when the cache agent is started. | Y |
autorefDuration | TT_BIGINT | The number of milliseconds spent in this autorefresh transaction. | Y |
autorefNumRows | TT_BIGINT | The number of rows autorefreshed in this autorefresh. This includes all rows, including those in the root table and the child tables.
If there are cache groups with multiple tables, child table rows get updated multiple times. Therefore, the number of rows autorefreshed may be more than number of rows updated on Oracle. | N |
numOracleBytes | TT_BIGINT | The number of bytes transferred from Oracle in this autorefresh transaction. | N |
autorefNumRootTblRows | TT_BIGINT | The number of root table rows autorefreshed in this autorefresh transaction. | Y |
autorefQueryExecDuration | TT_BIGINT | The duration in milliseconds that it takes for the autorefresh query to execute on Oracle. | N |
autorefQueryFetchDuration | TT_BIGINT | The duration in milliseconds that it takes for the autorefresh query to fetch rows from Oracle. | N |
autorefTtApplyDuration | TT_BIGINT | The duration in milliseconds that it takes for TimesTen to apply the autorefresh. | N |
totalNumRows | TT_BIGINT | The total number of rows autorefreshed since the cache agent started.
The total number of rows autorefreshed may not be the same as number of rows updated on Oracle. This is because of a delay in marking the log; some updates may get autorefreshed and counted multiple times. | N |
totalNumOracleBytes | TT_BIGINT | The total number of bytes transferred from Oracle since the cache agent started. | N |
totalNumRootTblRows | TT_BIGINT | The total number of root table rows autorefreshed since the cache agent started. | Y |
totalDuration | TT_BIGINT | The total autorefresh duration in milliseconds since the cache agent started. | Y |
status | VARCHAR2 (128) | A string description of the status of the current autorefresh. See "Notes" below. Supported values for this field are:
| Y |
numlogrows | TT_BIGINT | Number of rows fetched from the Oracle database in this autorefresh. | Y |
totalnumlogrows | TT_BIGINT | The cumulative number of rows fetched from the Oracle database in this autorefresh. | Y |
Example
In this example, testcache is a READONLY cache group with one table and an incremental autorefresh interval of 10 seconds.
Command> call ttcacheautorefreshstatsget('user1','testcache');
< 1164260, 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.
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:
| Parameter | Type | Description |
|---|---|---|
state | TT_CHAR(10) | Enables and disables AWT monitoring. Its value can be ON or OFF. Default is OFF |
samplingRate | TT_INTEGER | Positive integer that specifies the frequency with which the AWT workflow is sampled. If samplingRate is set to 1, every AWT operation is monitored. Greater values indicate less frequent sampling. The value recommended for accuracy and performance is 16. If state is set to ON, the default for samplingRate is 16. If state is set to OFF, the default for samplingRate is 0. |
Result set
ttCacheAWTMonitorConfig returns the following result if you do not specify any parameters. It returns an error if the replication agent is not running or if an AWT cache group has not been created.
| Column | Type | Description |
|---|---|---|
State | TTVARCHAR (10) NOT NULL | Current state of AWT monitoring. The value can be ON or OFF. |
AWTSamplingFactor | TT_INTEGER NOT NULL | Positive integer that specifies the frequency with which the AWT workflow is sampled. |
Examples
Example 2-1
Retrieve the current state and sampling factor when monitoring is disabled.
Command> CALL ttCacheAwtMonitorConfig; < OFF, 0 > 1 row found.
Example 2-2
Enable monitoring and set the sampling frequency to 16.
Command> CALL ttCacheAwtMonitorConfig ('ON', 16);
< ON, 16 >
1 row found.
Example 2-3
Disable monitoring.
Command> CALL ttCacheAwtMonitorConfig; ('OFF')
< OFF, 0 >
1 row found.
See also
Description
This procedure returns the current transaction log file threshold for 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:
| Column | Type | Description |
|---|---|---|
threshold | TT_INTEGER NOT NULL | The number of transaction log files for all AWT cache groups associated with the database. If the result is 0, there is no set limit. |
Example
CALL ttCacheAWTThresholdGet();
Note
This procedure is available only for IMDB Cache.
See also
Description
This procedure sets the threshold for the number of transaction log files that can accumulate before AWT is considered either 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.
This procedure requires the CACHE_MANAGER privilege.
Syntax
ttCacheAWTThresholdSet(threshold)
Parameters
ttCacheAWTThresholdSet has the parameter:
| Parameter | Type | Description |
|---|---|---|
threshold | TT_INTEGER | Specifies the number of transaction log files for all AWT cache groups associated with the database. If the threshold is NULL, the log failure threshold is set to zero. |
Result set
ttCacheAWTThresholdSet returns no results.
Example
To set the threshold to allow 12 transaction log files to accumulate, use:
CALL ttCacheAWTThresholdSet(12);
Notes
This procedure is available for Oracle In-Memory Database Cache.
The user is responsible to recover when the threshold is exceeded.
See also
Description
For all cache groups that cache data from the same Oracle instance, this procedure specifies a timeout value and recovery policies in the case that the Oracle Server is unreachable and the cache agent or database is considered terminated.
The automatic refresh state of the database and cache groups can be determined from the procedure ttCacheDbCgStatus.
This procedure requires the CACHE_MANAGER privilege.
Syntax
ttCacheConfig(Param, tblOwner, tblName, Value)
Parameters
ttCacheConfig has these parameters:
| Parameter | Type | Description |
|---|---|---|
Param | VARCHAR2(50) NOT NULL | Specifies the parameter to be set by Value:
|
tblOwner | VARCHAR2(30) | Specifies the owner of the cached Oracle table.
This parameter is required if A synonym cannot be used to specify a table name. |
tblName | VARCHAR2(30) | Specifies the name of the cached Oracle table.
This parameter is required if Using a synonym to specify a table name is not supported. |
Value | VARCHAR2(200) | Specifies the value to be set for Param.
|
Result set
ttCacheConfig returns no results when it is used to set parameter values. When it is used to return parameter settings, it returns the following results.
| Column | Type | Value |
|---|---|---|
Param | VARCHAR2(50) | Parameter name:
|
tblOwner | VARCHAR2(30) | Owner of the cached Oracle table. |
tblName | VARCHAR2(30) | Name of the cached Oracle table.
Using a synonym to specify a table name is not supported. |
Value | VARCHAR2(200) | Specifies the value set for Param.
|
Examples
To set the cache agent timeout to 600 seconds (10 minutes), enter:
CALL ttCacheConfig('AgentTimeout',,,'600');
To determine the current cache agent timeout setting, enter:
CALL ttCacheConfig('AgentTimeout');
< AgentTimeout, <NULL>, <NULL>, 600 >
1 row found.
To set the recovery method to Manual for cache groups whose automatic refresh status is dead, enter:
CALL ttCacheconfig('DeadDbRecovery',,,'Manual');
Configure the IMDB cache to prevent an automatic full refresh and receive an Oracle error when there is an update on a cached Oracle table while the cache administration user's tablespace is full. The Oracle table is terry.customer.
CALL ttCacheConfig('TblSpaceFullRecovery','terry','customer','None');
To determine the current setting for TblSpaceFullRecovery on the terry.customer cached Oracle table, enter:
CALL ttCacheConfig('TblSpaceFullRecovery','terry','customer');
< TblSpaceFullRecovery, TERRY, CUSTOMER, none >
1 row found.
To configure a warning to be returned when the cache administration user's tablespace is 85 percent full and an update operation occurs on the cached Oracle table, enter:
CALL ttCacheConfig('TblSpaceThreshold',,,'85');
Note
This procedure is available only for IMDB Cache.
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
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.
This procedure requires no privilege.
Syntax
ttCacheDbCgStatus([cgowner, cgName])
Parameters
ttCacheDbCgStatus has these optional parameters:
| Parameter | Type | Description |
|---|---|---|
cgOwner | VARCHAR2(30) | Specifies the user name of the cache group owner. |
cgName | VARCHAR2(30) | Specifies the cache group name. |
Result set
ttCacheDbCgStatus returns the result:
| Column | Type | Value |
|---|---|---|
dbStatus | VARCHAR2(20) | Specifies the status of all the cache groups in the database with respect to autorefresh. The status is one of:
|
cgStatus | VARCHAR2(20) | Specifies the autorefresh status of the specified cache group. The status is one of:
|
Examples
This example shows that the automatic refresh status of the 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
This procedure enables or disables tracking of DDL statements issued on cached Oracle tables. By default, DDL statements are not tracked.
DDL tracking saves the change history for all the cached Oracle tables. The SQL statement and when it was executed are written to a table in the cache administration user schema on Oracle. One DDL tracking table is created to store DDL statements issued on any cached Oracle table. This information can be used to diagnose autorefresh problems.
See "Tracking DDL statements issued on cached Oracle tables" in Oracle In-Memory Database Cache User's Guide.
Required privilege
This procedure requires the CACHE_MANAGER privilege.
Syntax
ttCacheDDLTrackingConfig('trackingStatus')
Parameters
ttCacheDDLTrackingConfig has the parameter:
| Parameter | Type | Description |
|---|---|---|
trackingStatus | TT_VARCHAR(10) | Specifies whether DDL statements issued on cached Oracle tables are tracked. Valid values are:
|
Result set
ttCacheDDLTrackingConfig returns no results.
Examples
Command> CALL ttCacheDDLTrackingConfig('enable');
Description
This procedure returns the current policy used to determine when the TimesTen cache agent for the connected database should run. The policy can be either always or manual.
This procedure requires no privilege.
Syntax
ttCachePolicyGet()
Parameters
ttCachePolicyGet has no parameters.
Result set
ttCachePolicyGet returns the result:
| Column | Type | Value |
|---|---|---|
cachePolicy | TT_VARCHAR(10) | Specifies the policy used to determine when the TimesTen cache agent for the database should run. Valid values are:
|
Examples
To get the current policy for the TimesTen cache agent, use:
CALL ttCachePolicyGet ();
Note
This procedure is available only for IMDB Cache.
See also
Description
The procedure defines the policy used to determine when the TimesTen cache agent for the connected database should run. The policy can be either always or manual.
This procedure requires the CACHE_MANAGER privilege.
Syntax
ttCachePolicySet('cachePolicy')
Parameters
ttCachePolicySet has these parameters:
| Parameter | Type | Description |
|---|---|---|
cachePolicy | TT_VARCHAR(10) NOT NULL | Specifies the policy used to determine when the TimesTen cache agent for the database should run. Valid values are:
|
Result set
ttCachePolicySet returns no results.
Examples
To set the policy for TimesTen cache agent to always, use:
CALL ttCachePolicySet ('always');
Notes
This procedure is available only for IMDB Cache.
If you attempt to start the TimesTen cache agent (by changing the policy from manual to always) for a 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
Description
This procedure allows you to temporarily stop any updates from propagating to Oracle.
This procedure requires the CACHE_MANAGER privilege.
Syntax
ttCachePropagateFlagSet(CommitsOn)
Parameters
ttCachePropagateFlagSet has the parameter:
| Parameter | Type | Description |
|---|---|---|
CommitsOn | TT_INTEGER NOT NULL | If 0, sets a flag to stop updates from being sent to Oracle. The flag remains set until the end of the transaction or until the procedure is set to 1.
If 1, updates are sent to Oracle. |
Result set
ttCachePropagateFlagSet returns no results.
Notes
This procedure is available only for IMDB Cache.
When using this procedure, it is important to turn off AutoCommit, otherwise after the procedure is called the transaction ends and propagation to Oracle is turned back on.
The propagate flag is reset after a commit or rollback.
If the value of ttCachePropagateFlagSet is re-enabled several times during a single transaction, the transaction is only partially propagated to Oracle.
ttCachePropagateFlagSet is the only built-in procedure that can be used in the same transaction as any of the other cache group operations, such as FLUSH, LOAD, REFRESH and UNLOAD.
Description
This procedure generates the Oracle SQL statements to install or uninstall Oracle objects for:
Read-only cache groups
User managed cache groups with incremental autorefresh
Asynchronous writethrough (AWT) cache groups
This is useful when the user creating the cache group does not have adequate privilege to write on the Oracle database. The Oracle DBA can then use the script generated by this built-in procedure to create the Oracle objects.
This procedure requires the CACHE_MANAGER privilege.
Syntax
ttCacheSqlGet('feature_name', 'cache_group_name', install_flag)
Parameters
ttCacheSqlGet has these parameters:
| Parameter | Type | Description |
|---|---|---|
feature_name | TT_VARCHAR (100) | Can be specified as INCREMENTAL_AUTOREFRESH or ASYNCHRONOUS_WRITETHROUGH. |
cache_group_name | TT_VARCHAR (100) | The name of the cache group. Specify NULL when installing objects for asynchronous writethrough cache groups or to uninstall all Oracle objects in the autorefresh user's account. |
install_flag | TT_INTEGER NOT NULL | If install_flag is 1, ttCacheSqlGet returns Oracle SQL to install the autorefresh or asynchronous writethrough Oracle objects.
If |
Result set
ttCacheSqlGet returns the result set:
| Column | Type | Description |
|---|---|---|
retval | TT_VARCHAR (4096) NOT NULL | The Oracle SQL statement to uninstall or install autorefresh or asynchronous writethrough Oracle objects. |
continueFlag | TT_SMALLINT NOT NULL | Non-zero only if the Oracle SQL statement in the retval result column exceeds 4096 bytes and must be continued into the next result row. |
Example
CALL ttCacheSqlGet('INCREMENTAL_AUTOREFRESH', 'westernCustomers', 1);
To remove all Oracle objects in the autorefresh user's account, use:
CALL ttCacheSqlGet('INCREMENTAL_AUTOREFRESH', NULL, 0);
Notes
This procedure is available only for IMDB Cache.
Each returned retval field contains a separate Oracle SQL statement that may be directly executed on Oracle. A row may end in the middle of a statement, as indicated by the continueFlag field. In this case, the statement must be concatenated with the previous row to produce a usable SQL statement.
The script output of this procedure is not compatible with Oracle's SQL*Plus utility. However, you can use the ttIsql cachesqlget command to generate a script that is compatible with the SQL*Plus utility.
You can specify NULL for the cache_group_name option to generate Oracle SQL to clean up Oracle objects after a database has been destroyed by the ttDestroy utility.
Description
This procedure starts the TimesTen cache agent for the connected database.
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
Description
This procedure stops the TimesTen cache agent for the connected database.
This procedure requires the CACHE_MANAGER privilege.
Syntax
ttCachdeStop(timeout)
Parameters
ttCacheStop has the parameter:
| Parameter | Type | Description |
|---|---|---|
timeout | TT_INTEGER | Specifies that the TimesTen daemon should kill the cache agent if it doesn't stop within timeout seconds. If set to 0, the daemon waits forever for the cache agent. The default value is 100 seconds. |
Result set
ttCacheStop returns no results.
Examples
To stop the TimesTen cache agent, use:
CALL ttCacheStop();
Notes
This procedure is available only for IMDB Cache.
Do not shut down the cache agent immediately after dropping or altering a cache group. Instead, wait for at least two minutes. Otherwise, the cache agent may not get a chance to clean up the Oracle objects that were used by the AUTOREFRESH feature.
When using this procedure, no application, including the application making the call, can be holding a connection that specifies database-level locking (LockLevel=1).
See also
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.
This procedure requires CACHE_MANAGER privilege.
Syntax
ttCacheUidGet()
Parameters
ttCacheUidGet has no parameters.
Result set
ttCacheUidGet returns the results:
| Column | Type | Description |
|---|---|---|
UID | TT_VARCHAR (30) | The current cache administration user ID, used for autorefresh and asynchronous writethrough cache groups. |
Example
CALL ttCacheUidGet();
Note
This procedure is available only for IMDB Cache.
See also
Description
This procedure sets the cache administration user ID and password. 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.
This procedure requires the CACHE_MANAGER privilege.
Syntax
ttCacheUidPwdSet('UID', 'PWD')
Parameters
ttCacheUidPwdSet has these parameters:
| Parameter | Type | Description |
|---|---|---|
UID | TT_VARCHAR (30) | The cache administration user ID, used for autorefresh and asynchronous writethrough cache groups. |
PWD | TT_VARCHAR (30) | The password for the cache administration user. |
Result set
ttCacheUidPwdSet returns no results.
Example
CALL ttCacheUidPwdSet('myid', 'mypwd');
Notes
This procedure cannot be called from a Client/Server connection.
This procedure is available only for IMDB Cache.
The cache administration user ID and password cannot be set while either the cache agent or the replication agent are running.
The cache administration user ID cannot be reset while there are asynchronous writethrough cache groups or autorefresh cache groups (with a state that is not equal to OFF) on the database.
See also
Description
This procedure performs a non-blocking checkpoint operation. The blocking checkpoints are described in "ttCkptBlocking". A checkpoint operation is used to make a record of the current state of the 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.
This procedure requires the ADMIN privilege.
Syntax
ttCkpt()
Parameters
ttCkpt has these optional parameters:
| Parameter | Type | Description |
|---|---|---|
timeout | TT_INTEGER | The time (in seconds) that ttCkpt should wait to get a database lock before timing out. The value of timeout can be between 0 and one million, inclusively. If not specified, it defaults to infinity (the checkpoint never times out). |
retries | TT_INTEGER | The number of times that ttCkpt should attempt to get a database lock, if timeouts occur. The value of retries can be between 0 and 10, inclusive. If not specified, defaults to zero. |
Result set
ttCkpt returns no results.
Example
CALL ttCkpt();
Note
For a description of checkpoints, see "Transaction Management and Recovery" in Oracle TimesTen In-Memory Database Operations Guide.
See also
Description
This procedure performs a blocking checkpoint operation. A checkpoint operation is used to make a record of the current state of the 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.
This procedure requires the ADMIN privilege.
Syntax
ttCkptBlocking(timeout, retries)
Parameters
ttCkptBlocking has these optional parameters:
| Parameter | Type | Description |
|---|---|---|
timeout | TT_INTEGER | The time (in seconds) that ttCkptBlocking should wait to get a database lock before timing out. The value of timeout can be between 0 and one million, inclusively. If not specified, it defaults to infinity (the checkpoint never times out). |
retries | TT_INTEGER | The number of times that ttCkptBlocking should attempt to get a database lock, if timeouts occur. The value of retries can be between 0 and 10, inclusive. If not specified, defaults to zero. |
Result set
ttCkptBlocking returns no results.
Example
CALL ttCkptBlocking(); CALL ttCkptBlocking(1,10);
Notes
Because the checkpoint takes place at commit or rollback, the call to ttCkptBlocking always succeed. At commit or rollback, any problems with the checkpoint operation, such as a lack of disk space or a timeout, result in a warning being returned to the application. Checkpoint problems are not reflected as errors, since the commit or rollback of which they are a part can succeed even if the checkpoint fails. Warnings are reflected in ODBC with the return code SQL_SUCCESS_WITH_INFO.
For more information on checkpoints, see "Transaction Management and Recovery" in Oracle TimesTen In-Memory Database Operations Guide.
See also
Description
This procedure reconfigures the background checkpointer dynamically or returns the currently active settings of the configuration parameters. Changes made using ttCkptConfig become effective immediately. Thus, changes to ckptRate can take effect on a checkpoint that is currently in progress.
Changes made to the background checkpointer using ttCkptConfig are persistent. Subsequent loads of the 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:
| Parameter | Type | Description |
|---|---|---|
ckptFrequency | TT_INTEGER | Checkpoint frequency in seconds. Values from 0 to MAXINT are allowed. A value of 0 means that checkpoint frequency is not considered when scheduling checkpoints. |
ckptLogVolume | TT_INTEGER | Log volume between checkpoints in megabytes. Values from 0 to MAXINT are allowed. A value of 0 means that checkpoint log volume is not considered when scheduling checkpoints. |
ckptRate | TT_INTEGER | Specifies the rate in MB per second at which a checkpoint should be written to disk. A value of 0 indicates that the rate should not be limited, a value of NULL means that the rate should be left unchanged. Changes to this parameter take effect even on a checkpoint that is currently in-progress. |
Result set
ttCkptConfig returns the following results.
| Column | Type | Description |
|---|---|---|
ckptFrequency | TT_INTEGER NOT NULL | Currently active setting for checkpoint frequency in seconds. |
ckptLogVolume | TT_INTEGER NOT NULL | Currently active setting for log volume between checkpoints in Megabytes. |
ckptRate | TT_INTEGER NOT NULL | Current rate at which checkpoints are written to disk. |
Examples
To view the current settings of the background checkpointer configuration parameters, use:
CALL ttCkptConfig; < 600, 32, 0 > 1 row found.
To stop the background checkpointer from initiating checkpoints unless the log reaches its limit, use:
CALL ttCkptConfig(0); < 0, 32, 0 > 1 row found.
To stop the background checkpointer from initiating checkpoints, use:
CALL ttCkptConfig(NULL, 0); < 0, 0, 0 > 1 row found.
To set the background checkpointer configuration to initiate a checkpoint every 600 seconds or to checkpoint when the log reaches 32 megabytes (whichever comes first), use:
CALL ttCkptConfig(600, 32); < 600, 32, 0 > 1 row found.
Notes
By default, TimesTen performs background checkpoints at regular intervals.
In the case that your application attempts to perform a checkpoint operation while a backup is in process, the backup waits until the checkpoint finishes. Regardless of whether the checkpoint is a background checkpoint or an application-requested checkpoint, the behavior is:
If a backup or checkpoint is running and you try to do a backup, it waits for the running backup or checkpoint to finish.
If a backup or checkpoint is running and you try to do a checkpoint, it does not wait. It returns an error immediately.
To turn off background checkpointing, set CkptFrequency=0 and CkptLogVolume=0.
See also
Description
This procedure returns information about the last eight checkpoints of any type taken by any agent.
Required privilege
This procedure requires no privilege.
Syntax
ttCkptHistory( )
Parameters
ttCkptHistory has no parameters.
Result set
ttCkptHistory returns the result set:
| Column | Type | Description |
|---|---|---|
startTime | TT_TIMESTAMP NOT NULL | Time when the checkpoint was begun. |
endTime | TT_TIMESTAMP | Time when the checkpoint completed. |
type | TT_CHAR (16) NOT NULL | The type of checkpoint taken. Value is one of:
|
status | TT_CHAR (16) NOT NULL | Result status of the checkpoint operation. Value is one of:
In
|
initiator | TT_CHAR (16) NOT NULL | The source of the checkpoint request. Value is one of:
|
error | TT_INTEGER | If a checkpoint fails, this column indicates the reason for the failure. The value is one of the TimesTen error numbers. |
ckptFileNum | TT_INTEGER NOT NULL | The database file number used by the checkpoint. This corresponds to the number in the checkpoint file extension datastore.ds0 or datastore.ds1. |
ckptLFN | TT_INTEGER | The transaction log file number of the checkpoint log record. |
ckptLFO | TT_BIGINT | The transaction log file offset of the checkpoint log record. |
blksTotal | TT_BIGINT | The number of permanent blocks currently allocated in the database. These blocks are subject to consideration for checkpointing. |
bytesTotal | TT_BIGINT | The number of bytes occupied by blksTotal. |
blksInUse | TT_BIGINT | Of blksTotal, the number of blocks currently in use. |
bytesInUse | TT_BIGINT | The number of bytes occupied by blksInUse. |
blksDirty | TT_BIGINT | The number of dirty blocks written by this checkpoint. |
bytesDirty | TT_BIGINT | The number of bytes occupied by blksDirty. |
bytesWritten | TT_BIGINT | The total number of bytes written by this checkpoint. |
Percent_Complete | TT_INTEGER | If there is an in-progress checkpoint, indicates the percentage of the checkpoint that has been completed. If no checkpoint is in-progress, the value is NULL. The returned value is calculated by comparing the block ID of the last-written block against the database's PermSize. The value does not necessarily indicate the precise time remaining to complete the checkpoint, although it does give some indication of the remaining time needed to complete the disk write. The field shows only the progress of the writing of dirty blocks and does not include additional bookkeeping at the end of the checkpoint. The value is non-NULL if you call this procedure while a checkpoint is in progress. |
ckptVNo | TT_INTEGER NOT NULL | The checkpoint sequence number that is incremented for each checkpoint. |
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
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.
This procedure requires the ADMIN privilege.
Syntax
ttCompact()
Parameters
ttCompact has no parameters.
Result set
ttCompact returns no results.
Example
CALL ttCompact;
Note
Compacting data does not modify result addresses.
See also
Description
This procedure is similar to ttCompact, except that ttCompactTS may be used to compact a small fraction of the 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.
This procedure requires the ADMIN privilege.
Syntax
ttCompactTS(quantum)
Parameters
ttCompactTS has the parameter:
| Parameter | Type | Description |
|---|---|---|
quantum | TT_INTEGER NOT NULL | A nonzero positive integer that specifies the number of data blocks a ttCompactTS should compact. Each quantum corresponds to one data block. |
Result set
ttCompactTS returns no results.
Example
CALL ttCompactTS (5);
Note
Compacting data does not modify result addresses.
See also
Description
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.
This procedure requires the SELECT privilege on the specified table.
Syntax
ttComputeTabSizes (['tblName'], [includeOutOfLine])
Parameters
ttComputeTabSizes has the parameters:
| Parameter | Type | Description |
|---|---|---|
tblName | TT_CHAR(61) | Name of an application table. Can include the table owner. If a value of NULL or an empty string is provided, updates the statistics for all the current tables.
The type of tables that can be estimated are:
|
includeOutOfLine | TT_INTEGER | 0 (no) or 1 (yes). Defa ult is 1 (yes).
If value is Avoiding the computation of out-of-line values significantly decreases the latency of this procedure. |
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
Description
The ttConfiguration procedure returns the values for most connection attributes for the current database connection.
This procedure requires no privilege.
Syntax
ttConfiguration('paramName')
Parameters
ttConfiguration has the optional parameter:
| Parameter | Type | Description |
|---|---|---|
paramName | TT_VARCHAR (30) | The name of a connection attribute for which you want this procedure to return the value. |
Result set
ttConfiguration returns the result set:
| Column | Type | Description |
|---|---|---|
paramName | TT_VARCHAR (30) NOT NULL | The names of the connection attributes specified in the connection string, returned in alphabetical order. |
paramValue | TT_VARCHAR (1024) | The values of the connection attributes specified in the connection string. |
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
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.
This procedure requires no privilege.
Syntax
ttContext()
Parameters
ttContext has no parameters.
Result set
ttContext returns the result set:
| Column | Type | Description |
|---|---|---|
CONTEXT | BINARY(8) | Current connection's context value. |
Example
CALL ttContext;
Note
The context value numbers are unique only within a process. The context value number is not unique within the entire database. Therefore you may see the same context value number for different processes.
See also
Description
This procedure returns the list of processes connected to a 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.
This procedure requires no privilege.
Syntax
ttDataStoreStatus('dataStore')
Parameters
ttDataStoreStatus has the parameter:
| Parameter | Type | Description |
|---|---|---|
dataStore | TT_VARCHAR (256) | Full path name of desired database or NULL for all databases. |
Result set
ttDataStoreStatus returns the result set:
| Column | Type | Description |
|---|---|---|
dataStore | TT_VARCHAR (256) NOT NULL | Full path name of database. |
PID | TT_INTEGER NOT NULL | Process ID. |
Context | BINARY(8) NOT NULL | Context value of connection. |
conType | TT_CHAR (16) NOT NULL | Type of process connected. The result can be one of the following:
|
ShmID | TT_VARCHAR (260) NOT NULL | A printable version of the shared memory ID that the database occupies. |
connection_Name | TT_CHAR (30) NOT NULL | The symbolic name of the database connection. |
connID | TT_INTEGER NOT NULL | The numeric ID of the database connection |
Example
CALL ttDataStoreStatus('/data/Purchasing');
See also
Description
This procedure specifies that the current transaction should be made durable when it is committed. It only has an effect if the application is connected to the 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.
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).
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.
This procedure requires the CACHE_MANAGER privilege.
For a standalone TimesTen database:
ttGridAttach(currentNode, 'name1', IPAddr1, port1)
For a node of an active standby pair:
ttGridAttach(currentNode, 'name1', IPAddr1, port1 'name2', IPAddr2, port2)
Parameters
ttGridAttach has the parameters:
| Parameter | Type | Description |
|---|---|---|
currentNode | TT_INTEGER NOT NULL | The node number for the master database. Valid values for this parameter are:
|
name1 | TT_VARCAR (30) | Fully qualified name that uniquely identifies the grid member for the active master database. |
IPAddr1 | TT_VARCHAR (128) NOT NULL | IP address of the node where the active master database resides. |
port1 | TT_INTEGER NOT NULL | Port number for the cache agent process of the active master database or a standalone database. |
name2 | TT_VARCAR (30) | Fully qualified name that uniquely identifies the grid member for the standby master database. |
IPAddr2 | TT_VARCHAR (128) NOT NULL | IP address of the node where the standby master database resides. |
port2 | TT_INTEGER NOT NOLL | Port number for the cache agent process of the standby master database. |
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
Description
This procedure checks if the number of rows in global cache groups match number of rows in the ownership tables. Call this procedure only when the cache grid is quiet.
This procedure requires the CACHE_MANAGER privilege.
Syntax
ttGridCheckOwner(['cvName', 'cvOwner'])
Parameters
ttGridCheckOwner has the optional parameter:
| Parameter | Type | Description |
|---|---|---|
cvName | TT_VARCHAR (30) | The name of the cache group to be checked. If null, all cache groups are checked. |
cvOwner | TT_VARCHAR (30) | The owner of the cache group to be checked. If null, all cache groups are checked. |
Result set
ttGridCheckOwner displays no results.
Example
To get information on the mygroup cache group, owned by user terry, use:
CALL ttGridCheckOwner ('mygroup', 'terry');
To get information on all cache groups, use:
CALL ttGridCheckOwner();
See also
Description
This procedure creates a cache grid. This built-in procedure 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.
This procedure requires the CACHE_MANAGER privilege.
Syntax
ttGridCreate('gridName')
Parameters
ttGridCreate has the parameter:
| Parameter | Type | Description |
|---|---|---|
gridName | TT_VARCHAR (30) NOT NULL | Specifies the name of the grid. |
Result set
ttGridCreate returns no results.
Example
To create a grid named mygrid:
CALL ttGridCreate ('mygrid');
See also
Description
This procedure destroys a cache grid by removing all cache grid objects stored on the Oracle database.
By default, this built-in procedure does not destroy the grid if there are still attached members or existing 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.
This procedure requires the CACHE_MANAGER privilege.
Syntax
ttGridDestroy('gridName', [force])
Parameters
ttGridDestroy has the parameters:
| Parameter | Type | Description |
|---|---|---|
gridName | TT_VARCHAR (30) NOT NULL | The fully qualified name of the grid to be destroyed. |
force | TT_INTEGER | This optional parameter forces the cache grid to be destroyed even if there are still grid members attached to the cache grid or if it still contains cache groups. Valid value is 1. |
Result set
ttGridDestroy returns no results.
Example
To destroy the mygrid cache grid with force, use:
CALL ttGridDestroy ('mygrid', 1);
See also
Description
This procedure detaches a node from a cache grid.
Use this procedure before destroying a cache grid. You cannot destroy a cache grid if there are any nodes attached to the cache grid.
This procedure requires the CACHE_MANAGER privilege.
Syntax
ttGridDetach([['nodeMemberName',] [force] [oraclePropWaitSec])
Parameters
ttGridDetach has the optional parameters:
| Parameter | Type | Description |
|---|---|---|
nodeMemberName | TT_VARCHAR (200) | Specifies the node to detach from the grid.
Each node of an active standby pair must be detached separately. |
force | TT_INTEGER | This optional parameter forces a node to be detached without checking whether it is terminated. Valid value is 1. |
oraclePropWaitSec | TT_INTEGER | The optional parameter specifies the number of seconds to wait for all transactions to propagate to Oracle before detaching the node. A value of -1 indicates to wait forever. If no value is specified, ttGridDetach waits 1 second. |
Result set
ttGridDetach returns no results.
Example
To detach the current node from the grid, use
CALL ttGridDetach();
To detach the remote node TTGRID_alone2_2 from the grid, use
CALL ttGridDetach('TTGRID_alone2_2',1);
See also
Description
This procedure detaches all attached members from the grid. A grid member can be a standalone TimesTen database or a TimesTen active standby pair.
This procedure starts the cache agent if it is not already running.
This procedure requires the CACHE_MANAGER privilege.
ttGridDetachAll([oraclePropWaitSec])
Parameters
ttGridDetachAll has the optional parameter:
| Parameter | Type | Description |
|---|---|---|
oraclePropWaitSec | TT_INTEGER | The optional parameter specifies the number of seconds to wait for all transactions to propagate to Oracle before detaching all nodes. A value of -1 indicates to wait forever. If no value is specified, ttGridDetachAll waits 1 second. |
Result set
ttGridDetachAll returns no results.
Examples
To detach all grid members, use:
CALL ttGridDetachAll();
See also
Description
This procedure detaches the nodes in the list. It is useful for remote nodes, because they are unavailable.
Required privilege
This procedure requires the CACHE_MANAGER privilege.
Syntax
ttGridDetachList('nodeMemberName1 [nodeMemberName2 ...]' [,force])
Parameters
ttGridDetachList has the parameters:
| Parameter | Type | Description |
|---|---|---|
nodeMemberName | TT_VARCHAR (8192) NOT NULL | The fully qualified name of the node to be removed. |
force | TT_INTEGER | This optional parameter forces nodes to be detached without checking whether they are terminated. Valid value is 1. |
oraclePropWaitSec | TT_INTEGER | The optional parameter specifies the number of seconds to wait for all transactions to propagate to Oracle before detaching the listed nodes. A value of -1 indicates to wait forever. If no value is specified, ttGridDetachList waits 1 second. |
Result set
ttGridDetachList returns no results.
Example
CALL ttGridDetachList('TTGRID_cacheact_3A TTGRID_cachestand_3B',1, );
See also
Description
This procedure resumes operations that were blocked after a call to ttGridGlobalCGSuspend.
This procedure requires the CACHE_MANAGER privilege.
ttGridGlobalCGResume()
Parameters
ttGridGlobalCGResume has no parameters.
Result set
ttGridGlobalCGResume returns no results.
Examples
To detach all grid members, use:
CALL ttGridGlobalCGResume();
See also
Description
This procedure temporarily blocks dynamic loading and deleting cache instances for global cache groups. Use the ttGridGlobalCGResume procedure to re-enable these actions.
This procedure requires the CACHE_MANAGER privilege.
ttGridGlobalCGSuspend(wait)
Parameters
ttGridGlobalCGSuspend has the parameter:
| Parameter | Type | Description |
|---|---|---|
wait | TT_INTEGER NOT NULL | The number of seconds that the command waits for a pending delete to be propagated to Oracle or a pending transparent load operation to complete before returning.TimesTen returns an error if either the pending delete or the pending transparent load operation cannot complete in the specified time.
If no value is specified, there is no wait interval. |
Result set
ttGridGlobalCGSuspend returns no results.
Examples
To set a wait interval of 10 seconds:
CALL ttGridGlobalCGSuspend(10);
See also
Description
This procedure returns information about the specified cache grid or all cache grids.
This procedure requires the CACHE_MANAGER privilege.
Syntax
ttGridInfo(['gridName'])
Parameters
ttGridInfo has the optional parameter:
| Parameter | Type | Description |
|---|---|---|
gridName | TT_VARCHAR (30) | If gridName is specified, displays information about the specified grid. Otherwise, displays information about all grids. |
Result set
ttGridInfo returns information about the cache grid.
| Column | Type | Description |
|---|---|---|
gridName | TT_VARCHAR (30) | The name of the grid specified |
cacheAdminID | TT_VARCHAR (30)
| The cache administration user ID associated with the grid. |
platform | TT_VARCHAR (100) | The operating system platform on which the grid is operating.
The platform value is displayed as:
For example:
The comma does not represent a new column. |
major1, major2, major3 | TT_VARCHAR (10) for each field | The major TimesTen release associated with the grid. For example, release 11.2.1 is represented as 11, 2, 1. |
Example
To get information on the mygrid cache grid, use:
CALL ttGridInfo ('mygrid');
< MYGRID, CACHEUSER, Linux Intel x86, 32-bit, 11, 2, 1 >
To get information on all grids, use:
CALL ttGridInfo();
See also
Description
This procedure associates a TimesTen database with a grid.
This procedure requires the CACHE_MANAGER privilege.
Syntax
ttGridNameSet('gridName')
Parameters
ttGridNameSet has the parameter:
| Parameter | Type | Description |
|---|---|---|
gridName | TT_VARCHAR (30) | Associates the TimesTen database that calls the procedure with the grid specified by gridName. |
Result set
ttGridNameSet returns no results.
Example
To associate the database with the grid mygrid, use:
CALL ttGridNameSet('mygrid');
See also
Description
This procedure returns information about all members of the specified cache grid. If no grid name is specified, then it displays information about all members of all cache grids associated with the Oracle database.
This procedure requires the CACHE_MANAGER privilege.
Syntax
ttGridNodeStatus(['gridName'])
Parameters
ttGridNodeStatus has the optional parameter:
| Parameter | Type | Description |
|---|---|---|
gridName | TT_VARCHAR (30) | If gridName is specified, displays information about all members of the named grid. Otherwise, displays information about all grids. |
Result Set
ttGridNodeStatus returns the results:
| Column | Type | Description |
|---|---|---|
gridName | TT_VARCHAR (30) | The name of the grid. |
nodeID | TT_INTEGER NOT NULL | The unique ID of the grid node. |
activeNode | TT_INTEGER NOT NULL | The number of the node on which the active master database or a standalone database currently resides. |
node1Attached | CHAR (1) NOT NULL | Indicates if the active node is attached to the grid:
|
Host1 | TT_VARCHAR (200) NOT NULL | The host name where the active database is located. |
memberName1 | TT_VARCHAR (200) NOT NULL | The unique member name for the stand alone database or active standby database. |
IPaddr1 | TT_VARCHAR (128) NOT NULL | The IP address where the active master or stand alone database is located. |
port1 | TT_INTEGER NOT NULL | The port number for the cache agent process of the active master or stand alone database. |
node2Attached | CHAR (1) | Indicates if the standby node is attached to the grid:
|
host2 | TT_VARCHAR (200) | The host name where the standby master database is located. |
memberName2 | TT_VARCHAR (200) | The unique member name for the stand alone database or active standby database. |
IPaddr2 | TT_VARCHAR (128) | The IP address where the standby master database is located. |
port2 | TT_INTEGER | The port number for the cache agent process of the standby master database. |
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
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.
This procedure requires no privilege.
Syntax
ttGridUserAssignedName()
Parameters
ttGridUserAssignedName has no parameters.
Result set
ttGridUserAssignedname returns the result:
| Column | Type | Description |
|---|---|---|
hostName | TT_VARCHAR (200) | The current default local host setting for the database. If a default has not been supplied then the current hostname is returned. |
Example
CALL ttGriduserAssignedName ();
See also
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.
This procedure requires no privilege.
Syntax
ttHostnameGet()
Parameters
ttHostNameGet has no parameters.
Result set
ttHostNameGet returns the result:
| Column | Type | Description |
|---|---|---|
hostName | TT_VARCHAR (200) | The current default local host setting for the database. If a default has not been supplied then the current hostname is returned. |
Example
CALL ttHostNameGet ();
See also
Description
This procedure specifies the name of the default local host for the current 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.
This procedure requires the ADMIN privilege.
Syntax
ttHostnameSet('hostName')
Parameters
ttHostNameSet has the parameter:
| Parameter | Type | Description |
|---|---|---|
hostName | TT_VARCHAR (200) | The required default name for the local computer. If NULL is supplied the default value is cleared. |
Result set
ttHostNameSet returns no results.
Example
CALL ttHostNameSet ('alias1');
Note
The legal value of hostName can be any host name or IP address string except "localhost", "127.0.0.1" or "::1". You cannot set the default host name to a value that is different from a local host name used in an existing replication scheme.
See also
Description
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:
| Parameter | Type | Description |
|---|---|---|
captureLevel | TT_INTEGER | Legal values for the capture level are:
|
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
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:
| Parameter | Type | Description |
|---|---|---|
captureLevel | TT_INTEGER | Legal values for the capture level are:
|
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
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:
| Columns | Type | Description |
|---|---|---|
captureState | TT_INTEGER NOT NULL | The state of the capture:
|
connID | TT_INTEGER | The connection ID of the connection that initiated the last capture, or the current capture if one is in progress.
This value is |
captureLevel | TT_INTEGER | The level of the most recent capture.
This value is |
captureMode | TT_INTEGER | The mode of the most recent capture.
This value is |
numPrepared | TT_INTEGER | The number of prepared statements during the capture period.
This value is |
numExecuted | TT_INTEGER | The number of executed statements during the capture period.
This value is |
captureStartTime | TT_TIMESTAMP | The time stamp taken at the start of the capture period.
This value is |
captureEndTime | TT_TIMESTAMP | The time stamp taken at the end of the capture period.
This value is |
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
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:
| Parameter | Type | Description |
|---|---|---|
captureLevel | TT_INTEGER | Legal values for the capture level are:
|
Result set
ttIndexAdviceCaptureOutput returns the result set:
| Column | Type | Description |
|---|---|---|
stmtCount | TT_INTEGER | The number of statements in the captured workload that would have benefited from this index if it were present. |
createStmt | TT_VARCHAR (8300) NOT NULL | The executable statement that can be used to create an index. |
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
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:
| Parameter | Type | Description |
|---|---|---|
captureLevel | TT_INTEGER | Legal values for the capture level are:
|
captureMode | TT_INTEGER | Legal values for the capture mode are: 0 - Provides complete capture of index advice including execution of the SQL statements. This is the default.
|
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
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:
| Parameter | Type | Description |
|---|---|---|
tblOwner | TT_CHAR (30) | TimesTen table owner. |
tblName | TT_CHAR (30) NOT NULL | Name of the table to be created and loaded with Oracle data.
The specified TimesTen table cannot be a system table, a synonym, a view, a materialized view or a detail table of a materialized view, a global temporary table, a cache group table or a table that contains compressed columns. |
Query | TT_VARCHAR (409600) NOT NULL | An Oracle SELECT query to derive the table column definition.
The Oracle query cannot have any parameter bindings. Any expressions in the |
numThreads | TT_INTEGER | Number of threads for parallel load. If NULL, defaults to four. |
Result set
ttLoadFromOracle returns the result set:
| Column | Type | Description |
|---|---|---|
numRows | BIGINT NOT NULL | A single number indicating the number of rows loaded. |
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
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.
This procedure requires the ADMIN privilege.
Syntax
ttLockLevel('lockLevel')
Parameters
ttLockLevel has the parameter:
| Parameter | Type | Description |
|---|---|---|
lockLevel | TT_CHAR (20) NOT NULL | Locking level for the connection. |
The value of lockLevel may be one of two case-insensitive strings:
Row: Locking should be set to row-level locking.
DS: Locking should be set to 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
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.
This procedure requires no privilege.
Syntax
ttLockWait(seconds)
Parameters
ttLockWait has the required parameters:
| Parameter | Type | Description |
|---|---|---|
seconds | NUMBER (8,1) NOT NULL | Number of seconds to wait for a lock when there is contention on it. You can also specify fractions of a second. Values between 0.0 and 1000000.0 inclusive are accepted. |
Result set
ttLockWait returns no results.
Examples
To indicate a six second lock wait, use:
CALL ttLockWait (6);
To indicate a tenth of a second lock wait, use:
CALL ttLockWait (0.1);
Notes
When a lock is not immediately available to a TimesTen transaction, it waits a predetermined amount of time to try 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
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.
This procedure requires no privilege.
Syntax
ttLogHolds()
Parameters
ttLogHolds has no parameters.
Result set
ttLogHolds returns the result set:
| Column | Type | Description |
|---|---|---|
HoldLFN | TT_INTEGER NOT NULL | Returns the transaction log file number of the hold. |
HoldLFO | TT_BIGINT NOT NULL | Returns the transaction log file offset of the hold |
type | TT_CHAR (30) NOT NULL | Returns the type of hold, one of:
|
description | TT_VARCHAR (1024) NOT NULL | Describes the type-specific object for which the hold was created. Each description corresponds with the Type returned. Descriptions are one of:
|
Example
CALL ttLogHolds(); < 0, 1148544, Long-Running XA Transaction , 0x1-476c6f62616c-5861637431 > < 0, 1149752, Long-Running Transaction, 4.2 > < 0, 1149992, Checkpoint , sample.ds1 > < 0, 1150168, Checkpoint , sample.ds0 >
Description
This procedures set the value of PERM_IN_USE_HIGH_WATER column in the MONITOR table to the current value of PERM_IN_USE_SIZE 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.
This procedure requires the ADMIN privilege.
Syntax
ttMonitorHighWaterReset()
Parameters
ttMonitorHighWaterReset has no parameters.
Result set
ttMonitorHighWaterReset returns no results.
Example
CALL ttMonitorHighWaterReset();
Description
This procedure clears the statistics for the specified table, causing the TimesTen query optimizer to use estimates or default values for subsequent queries involving the table. The procedure is useful if statistics are assumed to be out of date and an application wants to use built-in default values. This procedure removes all rows from the TBL_STATS and COL_STATS system tables that pertain to the specified tables. See "SYS.TBL_STATS" and "SYS.COL_STATS" in Oracle TimesTen In-Memory Database System Tables and Views Reference.
This procedure requires no privilege for the table owner. This procedure requires no privilege if tblName is not specified, because the procedure operates on the current user's tables if tblName is not specified.
This procedure requires the ALTER ANY TABLE privilege if user is not the table owner.
Syntax
ttOptClearStats('tblName', invalidate)
Parameters
ttOptClearStats has these parameters:
| Parameter | Type | Description |
|---|---|---|
tblName | TT_CHAR (61) | Name of an application table. Can include table owner. If tblName is the empty string or is not specified, statistics are cleared for all the current user's tables in the database.
Using a synonym to specify a table name is not supported. |
invalidate | TT_INTEGER | 0 (no) or 1 (yes). Default is 0.
If If |
Result set
ttOptClearStats returns no results.
Example
CALL ttOptClearStats ( 'SALLY.ACCTS', 1 );
Clears the statistics for the SALLY.ACCTS table and reprepares all commands that affect the ACCTS table.
CALL ttOptClearStats();
Clears the statistics for all 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
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.
This procedure requires the DDL privilege.
Syntax
ttOptCmdCacheInvalidate('tblName', invalidate)
Parameters
ttOptCmdCacheInvalidate has these parameters:
| Parameter | Type | Description |
|---|---|---|
tblname | TT_CHAR(61) | The name of the table for which the dependent commands should be invalidated or recompiled |
action | TT_INTEGER | Recompiles or invalidates the dependent commands.
|
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
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').
This procedure requires no privilege for the table owner. This procedure requires no privilege if tblName is not specified, because the procedure operates on the current user's tables if tblName is not specified.This procedure requires the ALTER ANY TABLE privilege if user is not the table owner.
Syntax
ttOptEstimateStats('tblName', invalidate, 'sampleStr')
Parameters
ttOptEstimateStats has these parameters:
| Parameter | Type | Description |
|---|---|---|
tblName | TT_CHAR(61) | Name of an application table. Can include table owner. If tblName is an empty string, statistics are estimated for all the current user's tables in the database.
Using a synonym to specify a table name is not supported. |
invalidate | TT_INTEGER | 0 (no) or 1 (yes). If invalidate is 1, all commands that reference the affected tables are automatically prepared again when re-executed, including commands prepared by other users. If invalidate is 0, the statistics are not considered to have been modified and existing commands are not reprepared. The invalidate parameter is optional and defaults to 0. |
sampleStr | TT_VARCHAR (255) NOT NULL | String of the form 'n ROWS', where n is an INTEGER greater than zero; or 'p PERCENT', where p is a floating point number between 0.0 and 100.0 inclusive. |
Result set
ttOptEstimateStats returns no results.
Examples
CALL ttOptEstimateStats ( 'ACCTS', 1, '5 PERCENT' ); CALL ttOptEstimateStats ( 'ACCTS', 1, '75 ROWS' );
Notes
The TimesTen statistics include the number of rows in each table, the number of unique values in each column, and the minimum and maximum values in each column. TimesTen assumes a uniform distribution of column values.
This procedure only runs faster than ttOptUpdateStats when you sample less than 50 percent of the rows in the table.
Estimates are not computed on columns that are longer than 2,048 bytes, and statistics for these columns are not updated. To update statistics on columns longer than 2,048 bytes, use the ttOptUpdateStats built-in procedure. (For varying length columns, this procedure updates statistics only if the column has a maximum length of 2,048 bytes or less.)
If a very small value is chosen for the sampleStr parameter, this procedure runs quickly but may result in suboptimal execution plans. For "good" distributions of data, a 10 percent selection is a good choice for computing statistics quickly without sacrificing plan accuracy. If the number of rows specified is sufficiently large or the table in question is sufficiently small, to improve performance TimesTen computes exact statistics anyway on all columns that have a length of 2,048 bytes or less. For example, the only difference between
ttOptEstimateStats ('ACCTS', 1, '100 PERCENT' )
and
ttOptUpdateStats( 'ACCTS', 1 )
is that the former does not compute statistics for long columns.
The statistics are stored in the TBL_STATS and COL_STATS system tables.
For performance reasons, 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
Description
This procedure returns statistics information in text format.
This procedure requires the SELECT privilege on the specified tables.
Syntax
ttOptGetColStats('tblName', 'colName')
Parameters
ttOptGetColStats has these parameters:
| Parameter | Type | Description |
|---|---|---|
tblName | TT_CHAR (61) | Name of the table whose statistics are to be returned. If NULL is passed, then values for all tables are returned.
Using a synonym to specify a table name is not supported. |
colName | TT_CHAR (30) | Name of the column for which statistics should be returned. If NULL is passed, statistics for all columns in the specified table are returned. |
Result set
ttOptGetColStats returns the result set:
| Column | Type | Description |
|---|---|---|
tblName | TT_CHAR (30) | Name of the table.
Using a synonym to specify a table name is not supported. |
colName | TT_CHAR (30) | Name of the column. |
stats | TT_VARCHAR (409600) NOT NULL | Statistics in text form. |
Examples
CALL ttOptGetColStats (); < T1 , X1, (2, 10, 10, 100 (,4, 40, 10 ,1, 10, 5) ,(4, 20, 20 ,11, 20, 15) )>
See also
Description
This procedure returns the optimizer flag settings for the current transaction. The results are returned as a result set that can be retrieved using the ODBC SQLFetch function or the JDBC ResultSet.getXXX() method, just like the result of a SQL SELECT statement. Applications can request the value of a specific optimizer flag by passing the flag name to ttOptGetFlag. Alternatively, applications can request the values of all the optimizer flags by passing NULL. The optimizer flags and their meanings are described under the ttOptSetFlag built-in procedure.
This procedure requires no privilege.
Syntax
ttOptGetFlag('flagName')
Parameters
ttOptGetFlag has the parameter:
| Parameter | Type | Description |
|---|---|---|
flagName | TT_CHAR (32) | Name of the flag whose value is to be returned. If NULL is passed, then the values of all flags are returned. |
Result set
ttOptGetFlag returns the result set:
| Column | Type | Description |
|---|---|---|
flagName | TT_VARCHAR (32) NOT NULL | Name of the flag. See "ttOptSetFlag" for a description of possible flag values. |
value | TT_INTEGER NOT NULL | Current flag value, either 0 or 1. |
Examples
CALL ttOptGetFlag('TmpHash');
See also
Description
This procedure returns the size of the free list of SQL compiled command cache. To reset the size of the cache, use ttOptSetMaxPriCmdFreeListCnt for materialized views and ttOptSetMaxCmdFreeListCnt for regular tables.
This procedure requires no privilege.
Parameters
ttOptGetMaxCmdFreeListCnt has no parameters.
Syntax
ttOptGetMaxCmdFreeListCnt()
Result set
ttOptGetMaxCmdFreeListCnt returns the results.
| Column | Type | Description |
|---|---|---|
retVal | TT_VARCHAR (200) NOT NULL | The size of the SQL compiled command cache. |
Example
CALL ttOptGetMaxCmdFreeListCnt( );
See also
Description
This procedure returns a single-row result set containing the join order for the current transaction. This result set can be retrieved using the ODBC SQLFetch function or the JDBC ResultSet.getXXX() method, just like the result of a SQL SELECT statement. Join orders are described under the ttOptSetOrder built-in procedure.
This procedure requires no privilege.
Syntax
ttOptGetOrder( )
Parameters
ttOptGetOrder has no parameters.
Result set
ttOptGetOrder returns the result set:
| Column | Type | Description |
|---|---|---|
joinOrder | TT_VARCHAR(1024) NOT NULL | Optimizer join order for the current transaction. |
Examples
CALL ttOptGetOrder;
See also
Description
This procedure modifies the statistics for the specified columns with interval information. This procedure allows an application to set statistics manually rather than have TimesTen automatically compute them. This feature is useful for preparing commands before the data has been inserted or for seeing how table characteristics can affect the choice of execution plan. This procedure modifies the relevant row(s) in the COL_STATS system table. Modifying interval statistics for a column that is not currently indexed has no effect.
Because this procedure can be used before any data are in the table, the values specified do not need to bear any relation to the actual values, although some basic validity checking is performed.
This procedure requires no privilege (if owner) or ALTER ANY TABLE privilege (if not owner).
Syntax
ttOptSetColIntvlStats('tblName', 'colName', invalidate, (stats))
Parameters
ttOptSetColIntvlStats has these parameters:
| Parameter | Type | Description |
|---|---|---|
tblName | TT_CHAR(61) NOT NULL | Name of an application table. Can include table owner. Using a synonym to specify a table name is not supported. |
colName | TT_CHAR(30) NOT NULL | Name of a column in that table. |
invalidate | TT_INTEGER | 0 (no) or 1 (yes). If invalidate is 1, all commands that reference the affected tables are automatically prepared again when re-executed. This includes commands prepared by other users. If invalidate is 0, the statistics are not considered to have been modified and existing commands are not reprepared. |
stats | VARBINARY (409600) NOT NULL | Sets stats for the column, using the format:
The modal value ( Because this parameter is a compound structure it cannot be parameterized using ODBC functions or described using the |
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
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.
This procedure requires no privilege (if owner) or ALTER ANY TABLE privilege (if not owner).
Syntax
ttOptSetColStats('tblName', 'colName', numUniq, minVal,maxVal,
invalidate, numNull)
Parameters
ttOptSetColStats has these parameters:
| Parameter | Type | Description |
|---|---|---|
tblName | TT_CHAR(61) NOT NULL | Name of an application table. Can include table owner.
Using a synonym to specify a table name is not supported. |
colName | TT_CHAR(30) NOT NULL | Name of a column in that table. |
num_Uniq | TT_INTEGER NOT NULL | Number of unique values in the column. |
minVal | VARBINARY(1024) NOT NULL | Minimum value in the column (possibly truncated). |
maxVal | VARBINARY(1024) NOT NULL | Maximum value in the column (possibly truncated). |
invalidate | TT_INTEGER | 0 (no) or 1 (yes). If invalidate is 1, all commands that reference the affected tables are automatically prepared again when re-executed. This includes commands prepared by other users. If invalidate is 0, the statistics are not considered to have been modified and existing commands are not reprepared. |
num_Null | TT_INTEGER | Indicates the total number of NULLs in the column. |
Result set
ttOptSetColStats returns no results.
Example
CALL ttOptSetColStats ('SALLY.ACCTS, 'BALANCE, 400, 0x00001388, 0x000186A0, 1, 0);
Notes
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
Description
This procedure allows applications to alter the generation of execution plans by the TimesTen query optimizer. It sets flags to enable or disable the use of various access methods. The changes made by this call take effect during preparation of statements and affect all subsequent calls to the ODBC functions SQLPrepare and SQLExecDirect or the JDBC methods Connection.prepareCall and Statement.execute in the current transaction. All optimizer flags are reset to their default values when the transaction has been committed or rolled back. If optimizer flags are set while AutoCommit is on, they are ignored because each statement is executed within its own transaction.
This procedure requires no privilege.
Syntax
ttOptSetFlag('optFlag', optVal)
Parameters
ttOptSetFlag has these parameters:
| Parameter | Type | Description |
|---|---|---|
optFlag | TT_CHAR(32) NOT NULL | Name of optimizer flag. |
optVal | TT_INTEGER NOT NULL | The value of the optimizer flag. The value is generally 0 (disable/disallow) or 1 (enable/allow), except as described under "Optimizer flags" below. |
Optimizer flags
When setting the optimizer flags, use the following character strings, which are not case sensitive:
| Flag | Description |
|---|---|
BranchAndBound | Enables or disables branch and bound optimization. If enabled, TimesTen calculates the maximum cost of the query plan during a "zero phase," at the very beginning of the optimization process. If disabled, TimesTen does not perform this cost analysis. |
DynamicLoadEnable | Enables or disables dynamic load of Oracle data to a TimesTen dynamic cache group. By default, dynamic load of Oracle data is enabled. |
DynamicLoadErrorMode | Enables or disables dynamic load error mode. It controls output of error messages upon failure of a transparent load operation on a TimesTen dynamic cache group. Disabled by default. |
FirstRow | Enables or disables first row optimization in a SELECT, UPDATE or DELETE statement. If the SQL keyword FIRST is used in the SQL statement, it takes precedence over this optimizer hint. The FIRST keyword enables first row optimization. |
ForceCompile | Enables or disables forced compilation. If enabled, TimesTen recompiles the query and regenerates the query plan each time. If disabled, TimesTen does not compile the query plan even if it is available. |
GenPlan | Enables or disables the creation of entries in the PLAN table for the rest of the transaction.
For an example, see "Generating a query plan" in Oracle TimesTen In-Memory Database Operations Guide. |
GlobalProcessing | Enables or disables global execution of SELECT and UNLOAD CACHE GROUP statements in a grid. By default, these statements are executed locally.
0 - 1 - See Oracle In-Memory Database Cache User's Guide for more details. |
Hash | Enables or disables the use of existing hash indexes in indexed table scans. |
HashGb | Enables or disables the use of hash groups. |
IndexedOR | Enables or disables serialized table scans. If disabled, TimesTen uses serialized table scans for IN...list conditions, else TimesTen uses multiple index scans for an OR condition. |
MergeJoin | Enables or disables the use of merge joins. |
NestedLoop | Refers to a common way of joining two tables. |
NoRemRowIdOpt | Enables or disables internal generation of RowIDs. If enabled, RowIDs are not internally generated for optimization purposes. If disabled, RowIDs may be internally generated, even if the row is not in the SELECT list. |
PassThrough | Temporarily changes the pass through level for IMDB Cache applications. The pass through level can be set at any time and takes effect immediately. Legal values for this flag are:
0 (default) - SQL statements are executed only on TimesTen. 1 - 2 - 3 - All statements are passed through to the Oracle database for execution, except that 4 - 5 - |
RowLock | Allows or disallows the optimizer to consider using row locks. |
Scan | Refers to full table scans. |
Rowid | Enables or disables the use of Row IDs. |
ShowJoinOrder | Shows the join order of the tables in an optimizer scan. |
TmpHash | Enables or disables the use of a temporary hash scan. This is an index that is created during execution for use in evaluating the statement. Though index creation is time-consuming, it can save time when evaluating join predicates. |
TblLock | Enables or disables the optimizer to consider using table locks. |
TmpTable | Stores intermediate results into a temporary table. This operation is sometimes chosen to avoid repeated evaluation of predicates in join queries or sometimes just to allow faster scans of intermediate results in joins. |
TmpTtree | Performs a temporary range scan. Can also be used so that values are sorted for a merge join. Though index creation is time-consuming, it can save time when evaluating join predicates. |
Ttree | Enables or disables the use of existing range indexes in indexed table scans. |
UseBoyerMooreStringSearch | Enables or disables the Boyer-Moore string search algorithm. If enabled, Boyer-Moore string search algorithm is enabled. This can improve performance of LIKE operations. |
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:
| TblLock status | RowLock status | Effect on the optimizer |
|---|---|---|
| Disabled | Disabled | Use row-level locking. |
| Enabled | Disabled | Use table-level locking. |
| Disabled | Enabled | Use row-level locking. |
| Enabled | Enabled | Optimizer chooses row-level or table-level locking. |
In general, table-level locking is useful when a query accesses a significant portion of the rows of a table and/or when there are very few concurrent transactions accessing the table.
Result set
ttOptSetFlag returns no results.
Example
CALL ttOptSetFlag ('TmpHash', 1);
See also
Description
This procedure sets the maximum count of the free list of SQL compiled commands for regular tables. To get the current setting use the ttOptGetMaxCmdFreeListCnt procedure.
This procedure requires the ADMIN privilege.
Syntax
ttOptSetMaxCmdFreeListCnt(maxCnt)
Parameters
ttOptSetMaxCmdFreeListCnt has the required parameter:
| Parameter | Type | Description |
|---|---|---|
maxCnt | TT_INTEGER NOT NULL | The max number of free SQL compiled commands for regular tables. |
Result set
ttOptSetMaxCmdFreeListCnt returns no results.
Example
CALL ttOptSetMaxCmdFreeListCnt(40);
See also
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.
This procedure requires the ADMIN privilege.
Syntax
ttOptSetMaxCmdPriFreeListCnt(maxCnt)
Parameters
ttOptSetMaxPriCmdFreeListCnt has the required parameter:
| Parameter | Type | Description |
|---|---|---|
maxCnt | TT_INTEGER NOT NULL | The size of the SQL compiled command cache. |
Result set
ttOptSetMaxPriCmdFreeListCnt returns no results.
Example
CALL ttOptSetMaxPriCmdFreeListCnt(40);
See also
Description
This procedure specifies the order in which tables should be joined by the optimizer. The character string is a list of correlation names referenced in the query or a subquery, separated by spaces (not commas). The table listed first is scanned first by the plan. (It is outermost in a nested loop join, for example.) A correlation name is a shortcut or alias for a qualified table name.
This procedure requires no privilege.
Syntax
ttOptSetOrder('joinOrder')
Parameters
ttOptSetOrder has the required parameter:
| Parameter | Type | Description |
|---|---|---|
join_Order | TT_VARCHAR(1024) | List of space-separated table correlation names. If an owner is required to distinguish the table name, use a table correlation name. If the joinOrder is not specified the query optimizer reverts to its default behavior. |
Result set
ttOptSetOrder returns no results.
Examples
CALL ttOptSetOrder ('EMPS DEPTS ACCTS');
Use the correlation name instead of the actual table name when specifying the join order.
If an application makes the call:
call ttOptSetOrder('ORDERS CUSTOMERS');
The optimizer scans the ORDERS table before scanning the CUSTOMERS when evaluating the following query that lists all the customers who have at least one unshipped order:
SELECT CUSTOMERS.NAME
FROM CUSTOMERS
WHERE EXISTS (SELECT 1
FROM ORDERS
WHERE CUSTOMERS.ID = ORDERS.CUSTID
AND ORDER.STATUS ='UN-SHIPPED');
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
Description
This procedure modifies the statistics for the specified table. This procedure allows an application to set statistics explicitly rather than have TimesTen automatically compute them.
This procedure requires no privilege (if owner) or ALTER ANY TABLE privilege (if not owner).
Syntax
ttOptSetTblStats('tblName', numRows, invalidate)
Parameters
ttOptSetTblStats has these parameters:
| Parameter | Type | Description |
|---|---|---|
tblName | TT_CHAR(61) NOT NULL | Name of an application table. Can include table owner.
Using a synonym to specify a table name is not supported. |
num_Rows | TT_INTEGER NOT NULL | Number of rows in the table. |
invalidate | TT_INTEGER | 0 (no) or 1 (yes). If invalidate is 1, all commands that reference the affected tables are automatically prepared again when re-executed, including commands prepared by other users. If invalidate is 0, the statistics are not considered to have been modified and existing commands are not reprepared. |
Result set
ttOptSetTblStats returns no results.
Example
CALL ttOptSetTblStats ( 'ACCTS', 10000, 0 );
Note
This feature is useful for preparing commands before the data has been inserted or for seeing how table size can affect the choice of an execution plan. Because the command can be used before any data are in the table, the values specified do not need to bear any relation to the actual values. This procedure modifies the relevant row(s) in the TBL_STATS system table. See "SYS.TBL_STATS" in Oracle TimesTen In-Memory Database System Tables and Views Reference.
See also
Description
This procedure returns the join order of the last prepared or executed SQL statement (SELECT, UPDATE, DELETE, and INSERT SELECT) in the current transaction. For a join order to be collected, use ttOptSetFlag('ShowJoinOrder', 1) or set the ttIsql "ShowJoinOrder" command to ON (1) first in the same transaction. AUTOCOMMIT must be off when using either of these commands. The join order is represented by table names.
This procedure requires no privilege.
Syntax
ttOptShowJoinOrder()
Parameters
ttOptShowJoinOrder has no parameters.
Result set
ttOptShowJoinOrder returns the result:
| Column | Type | Description |
|---|---|---|
tblName | TT VARCHAR (4096) NOT NULL | Table names, including owner name quantifiers and correlation name for each table if specified. Table names are returned in parentheses.
Using a synonym to specify a table name is not supported. |
Example
>AUTOCOMMIT 0;
> CALL ttOptSetFlag ('ShowJoinOrder', 1);
>PREPARE SELECT * FROM t1;
>CALL ttOptShowJoinOrder();
>( T1 )
Notes
You must call ttOptSetFlag('ShowJoinOrder', 1) or set the ttIsql "ShowJoinOrder" command to ON (1) before using this procedure.
This procedure works within one transaction and is not persistent across transactions.
See also
Description
This procedure 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:
| Parameter | Type | Description |
|---|---|---|
tblName | TT_CHAR (61) NOT NULL | Name of the table whose statistics are to be returned. If NULL is passed, then values for all tables are returned.
Using a synonym to specify a table name is not supported. |
Result set
ttOptStatsExport returns the result set:
| Column | Type | Description |
|---|---|---|
stmt | TT_VARCHAR (8300) NOT NULL | The set of statements required to restore the table(s) statistics to the current state. |
Examples
CALL ttOptStatsExport('MyTable');
See also
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.
This procedure requires no privilege for the table owner. This procedure requires no privilege if tblName is not specified, because the procedure operates on the current user's tables if tblName is not specified.This procedure requires the ALTER ANY TABLE privilege if user is not the table owner.
Syntax
ttOptUpdateStats('tblName', invalidate, option)
Parameters
ttOptUpdateStats has these parameters:
| Parameter | Type | Description |
|---|---|---|
tblName | TT_CHAR(61) | Name of an application table. Can include table owner. If a value of NULL or an empty string is provided, the statistics for all the current user's tables are updated.
Using a synonym to specify a table name is not supported. |
invalidate | TT_INTEGER | 0 (no) or 1 (yes). If invalidate is 1, marks all commands for reprepare on next execution except ALTER TABLE DROP TABLE, and the ALTER TABLE ADD COLUMN FOR SELECT * FROM TABLE statements. These exceptions require manual reprepare. If invalidate is 0, the statistics are not considered to have been modified and existing commands are not reprepared.
The |
option | TT_INTEGER | Specifies whether to collect complete interval statistics information. Valid values for this option are:
See "Notes" below for more information. |
Result set
ttOptUpdateStats returns no results.
Example
CALL ttOptUpdateStats ( 'ACCTS', 1 );
Updates the ACCTS table and causes all commands that reference the ACCTS table to be re-prepared when they are next executed.
CALL ttOptUpdateStats('', 1);
Updates all 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
Description
This procedure allows applications to alter the generation of execution plans by the TimesTen query optimizer. It allows applications to disable the use of a set of indexes or enable the consideration of only a set of indexes for each correlation used in a query. Enabling the consideration of an index does not guarantee that the plan generated uses the index. Depending on the estimated cost, the optimizer might choose to use a serialization scan or a materialization scan to access the associated correlation if these scans resulted in a better plan than the ones that use the specified index.
The changes made by this call take effect immediately and affect all subsequent calls to the ODBC functions SQLPrepare and SQLExecDirect or the JDBC methods Connection.prepareCall and Statement.execute in the current transaction until the applications explicitly issue a call to clear it. The setting is cleared whenever a new transaction is started.
This procedure requires no privilege.
Syntax
ttOptUseIndex('IndexName, CorrelationName, 0 | 1 [;...]')
Parameters
ttOptUseIndex has a single string parameter, indOption, of type TT_VARCHAR(1024) with these components:
| Component | Description |
|---|---|
IndexName | The name of the user-defined index or '_TMPRANGE' for temporary range index or '_TMPHASH' for temporary hash index. If index name is omitted, the setting applies to all indexes of the specified correlation. |
CorrelationName | The correlation name of the table. If a table is defined with a correlation name in the FROM clause, use this correlation name instead of the table name when specifying the index hint for this table. If correlation name is omitted for an entry, the setting affects all tables with the specified index name. |
0 | 1 | Disables(0) or enables (1) the use of the index specified by IndexName. |
Result set
ttOptUseIndex returns no results.
Examples
CALL ttOptUseIndex('"3456"."1234", t1, 0');
CALL ttOptUseIndex('data1.i1, data1.t1, 0');
CALL ttOptUseIndex('i1, t1, 0');
Note
If ttOptUseIndex is called without a parameter or with a NULL value, TimesTen clears the previous index hint.
See also
Description
This procedure returns result statistics about PL/SQL library cache performance and activity.
This procedure requires no privilege.
Syntax
ttPLSQLMemoryStats(paramName, paramValue )
Parameters
ttPLSQLMemoryStats takes no parameters.
Parameters
ttPLSQLMemoryStats returns the results in the following columns:
| Columns | Type | Description |
|---|---|---|
paramName | TT_VARCHAR(30) NOT NULL | The name of the result statistic returned in this row. |
paramValue | BINARY_FLOAT NOT NULL | The value of the result statistic returned in this row. |
The following statistics are returned:
Gets: Number of times a lock was requested for a PL/SQL object.
GetHits: Number of times a PL/SQL object's handle was found in memory.
GetHitRatio: Ratio of GetHits to Gets.
Pins: Number of times a PIN was requested for PL/SQL objects.
PinHits: Number of times all the metadata pieces of the library object were found in memory.
PinHitRatio: Ratio of PinHits to Pins.
Reloads: Any PIN of an object that is not the first PIN performed since the object handle was created, and which requires loading the object from the database.
Invalidations: Total number of times objects in this namespace were marked invalid because a dependent object was modified.
CurrentConnectionMemory: The total amount of heap memory, in Megabytes, allocated to PL/SQL on this database connection.
DeferredCleanups: Total number of times a deferred cleanup occurred.
Examples
connect "DSN=sample";
Connection successful:
DSN=sample;UID=timesten;DataStore=/scratch/timesten/sample;
DatabaseCharacterSet=AL32UTF8;ConnectionCharacterSet=AL32UTF8;PermSize=128;
TypeMode=0;PLSQL_MEMORY_SIZE=32;PLSQL_MEMORY_ADDRESS=20000000;PLSQL=1;
(Default setting AutoCommit=1)
Command> create procedure hello is begin dbms_output.put_line('Hello, World!');
end;
> /
Procedure created.
Command> call ttPlsqlMemoryStats;
< Gets, 485.00000 >
< GetHits, 444.000000 >
< GetHitRatio, .9154639 >
< Pins, 260.00000 >
< PinHits, 178.000000 >
< PinHitRatio, .6846154 >
< Reloads, 4.000000 >
< Invalidations, 0.000000e+00 >
< CurrentConnectionMemory, 56.00000 >
9 rows found.
Description
This procedure returns the RAM autoreload policy used to determine if a database is reloaded into RAM after an invalidation. The policy can be either autoreload or noautoreload.
This procedure requires no privilege.
Syntax
ttRamPolicyAutoReloadGet()
Result set
ttRamPolicyAutoReloadGet returns the results:
| Column | Type | Description |
|---|---|---|
ramPolicyAutoreload | TT_INTEGER | The policy used to determine if the database is reloaded into RAM after an invalidation. Valid values are:
|
Parameters
ttRamPolicyAutoReloadGet has no parameters.
Examples
To view the RAM autoreload policy, use:
CALL ttRamPolicyAutoReloadGet();
See also
Description
This procedure determines the RAM autoreload policy if a database is invalidated. The policy can be either autoreload or noautoreload.
This procedure requires the ADMIN privilege.
Syntax
ttRamPolicyAutoReloadSet(ramPolicyAutoReload)
Parameters
ttRamPolicyAutoReloadSet has the parameters:
| Parameter | Type | Description |
|---|---|---|
ramPolicyAutoreload | TT_INTEGER NOT NULL | The policy used to determine if the database is reloaded into RAM after an invalidation. Valid values are:
|
Result set
ttRamPolicyAutoReloadSet returns no results.
Examples
To automatically reload a database into RAM after an invalidation, use:
CALL ttRamPolicyAutoReloadSet(1);
See also
Description
This procedure returns the RAM policy used to determine when a database is loaded into memory. The policy can be either always, manual, or inUse.
This procedure requires no privilege.
Syntax
ttRamPolicyGet()
Result set
ttRamPolicyGet returns the results:
| Column | Type | Description |
|---|---|---|
ramPolicy | TT_VARCHAR (10) | The policy used to determine when the database is loaded into system RAM. Valid values are:
|
ramGrace | TT_INTEGER | If the ramPolicy is inUse, this field reports the number of seconds the database is kept in RAM after the last application has disconnected. Otherwise, this field is NULL. |
Parameters
ttRamPolicyGet has no parameters.
Examples
To view the RAM policy, use:
CALL ttRamPolicyGet();
See also
Description
This procedure defines the policy used to determine when a database is loaded into memory. The policy can be either always, manual, or inUse.
This procedure requires the ADMIN privilege.
Syntax
ttRamPolicySet('ramPolicy', ramGrace)
Parameters
ttRamPolicySet has the parameters:
| Parameter | Type | Description |
|---|---|---|
ramPolicy | TT_VARCHAR (10) NOT NULL | The policy used to determine when the database is loaded into system RAM. Valid values are:
|
ramGrace | TT_INTEGER | Sets the number of seconds the database is kept in RAM after the last application has disconnected. This number is only effective if ramPoliy is inUse. This parameter is optional, and when omitted or set to NULL, the existing ramGrace period is left unchanged. |
Result set
ttRamPolicySet returns no results.
Examples
To set the policy for loading a database into RAM to be inUse and for the database to kept in RAM for 10 seconds after the last application has disconnected, use:
CALL ttRamPolicySet('inUse', 10);
See also
Description
This procedure scans the indicated table (or all the current user's tables) to find redundant indexes. It returns the names of the redundant indexes and a suggestion for which to drop.
This procedure requires no privilege.
Syntax
ttRedundantIndexCheck('tblname')
Parameters
ttRedundantIndexCheck has the parameter:
| Parameter | Type | Description |
|---|---|---|
tblName | TT_CHAR(61) | Name of an application table. Can include table owner. If a value of NULL or an empty string is provided, the redundant indexes for all the current user's tables.
Using a synonym to specify a table name is not supported. |
Result Set
ttRedundantIndexCheck returns the result:
| Column | Type | Description |
|---|---|---|
redundancy | TT_VARCHAR (1024) NOT NULL | The names of redundant indexes and a suggestion for which index to drop. |
Example
Create table y with a primary key. Then create index i. TimesTen returns a warning that a redundant index is being created. Create another index, i1. The command fails and TimesTen returns an error. Call this procedure to show the warnings.
CREATE TABLE y (ID tt_integer primary key);
CREATE INDEX i ON y (id);
Warning 2240: New non-unique index I has the same key columns as existing
unique index Y; consider dropping index I
CREATE INDEX i1 ON y (id);
2231: New index I1 would be identical to existing index I The command failed.
CALL ttredundantindexcheck ('y');
< Non-unique index SCOTT.Y.I has the same key columns as unique index SCOTT.Y.Y;
consider dropping index SCOTT.Y.I >
1 row found.
Description
This procedure changes the state of the active database in an active standby pair from ACTIVE to IDLE. Use this procedure when reversing the roles of the master databases in an active standby pair.
This procedure requires the ADMIN privilege.
Syntax
ttRepDeactivate()
Parameters
ttRepDeactivate has no parameters.
Result set
ttRepDeactivate returns no results.
Example
To deactivate the active database in an active standby pair, use:
CALL ttRepDeactivate();
See also
Description
This procedure returns the status of one or more replication peer databases.
This procedure requires no privilege.
Syntax
ttReplicationStatus('receiver', 'hostname')
Parameters
ttReplicationStatus has the optional parameters:
| Parameter | Type | Description |
|---|---|---|
receiver | TT_VARCHAR(200) | Subscriber of interest or NULL for all subscribers. If the parameter is provided, then it names a replication subscriber about which information is sought. If the parameter is not provided, then information on replication subscribers defined for the current database is returned. |
hostname | TT_VARCHAR(200) | The host name of one or more stores that are configured to receive updates from the executing store; if NULL, then receiving stores are identified by subscriber alone. If both receiver and hostname are NULL, then all receiving stores are selected. |
Result set
ttReplicationStatus returns the result set:
| Column | Type | Description |
|---|---|---|
subscriber | TT_VARCHAR(200) NOT NULL | Subscriber name. |
hostName | TT_VARCHAR(200) NOT NULL | Host name. |
port | TT_INTEGER NOT NULL | Defined port number. |
pState | TT_CHAR(10) NOT NULL | Peer state. The values of the result column are:
|
logs | TT_INTEGER NOT NULL | Number of transaction log files held for this peer. |
lastMsg | TT_INTEGER | Seconds since last interaction or NULL. |
replicationName | TT_CHAR(30) NOT NULL | Name of replication scheme. |
replicationOwner | TT_CHAR(30) NOT NULL | Owner of replication scheme. |
Example
CALL ttReplicationStatus('System8');
Notes
If the receiver parameter is not NULL, only the status of the given receiver is returned. If the receiver parameter is NULL, the status of all subscribers is returned.
This procedure is supported only for TimesTen Data Manager ODBC applications. It is not supported for TimesTen Client or JDBC applications.
See also
Description
This procedure returns the replication restart policy used to determine when the TimesTen Replication Agent for the connected database should run. The policy can be always, manual, or norestart.
This procedure requires no privilege.
Syntax
ttRepPolicyGet()
Parameters
ttRepPolicyGet has no parameters.
Result set
ttRepPolicyGet returns the results:
| Parameter | Type | Description |
|---|---|---|
repPolicy | TT_VARCHAR (10) | The policy used to determine when the TimesTen Replication Agent for the database should run. Valid values are:
|
Examples
To set the policy for TimesTen Replication Agent to always, use:
CALL ttRepPolicyGet();
See also
Description
This procedure defines the replication restart policy used to determine when the TimesTen Replication Agent for the connected database should run. The policy can be either always, manual, or norestart.
This procedure requires the ADMIN privilege.
Syntax
ttRepPolicySet('repPolicy')
Parameters
ttRepPolicySet has this parameter:
| Parameter | Type | Description |
|---|---|---|
repPolicy | TT_VARCHAR (10) NOT NULL | Specifies the policy used to determine when the TimesTen replication agent for the database should run. Valid values are:
|
Result set
ttRepPolicySet returns no results.
Examples
To set the policy for TimesTen replication agent to always, use the following.
CALL ttRepPolicySet('always');
See also
Description
This procedure returns the number of seconds that was most recently specified as the query threshold for the replication agent. The number of seconds returned may not be the same as the query threshold in effect. Setting a new value for the query threshold takes effect the next time the replication agent is started.
This procedure requires the ADMIN privilege.
Syntax
ttRepQueryThresholdGet()
Parameters
ttRepQueryThresholdGet has no parameters.
Result set
ttRepQueryThresholdGet returns the result:
| Column | Type | Description |
|---|---|---|
repQueryThreshold | TT_INTEGER | The number of seconds that a replication query executes before returning an error. |
Examples
To get the replication query threshold value, use:
CALL ttRepQueryThresholdGet; < 4 > 1 row found.
See also
Description
This procedure specifies the number of seconds that a query can be executed by the replication agent before TimesTen writes a warning to the support log and throws an SNMP trap. The specified value takes effect the next time the replication agent is started. The query threshold for the replication agent applies to SQL execution on detail tables of materialized views, ON DELETE CASCADE operations and some internal operations that execute SQL statements.
This procedure requires the ADMIN privilege.
Syntax
ttRepQueryThresholdSet(seconds);
Parameters
ttRepQueryThresholdSet has the parameter:
| Parameter | Type | Description |
|---|---|---|
seconds | TT_INTEGER NOT NULL | Number of seconds a SQL statement can be executed by the replication agent before TimesTen writes a warning to the support log and throws an SNMP trap. The value must be greater than or equal to 0. Default is 0 and indicates that Timesten does not write any warnings. |
Result set
ttRepQueryThresholdSet returns no results.
Examples
To set the replication query threshold value to four seconds, use:
CALL ttRepQueryThresholdSet(4);
See also
Description
This procedure starts the TimesTen replication agent for the connected database.
This procedure requires the CACHE_MANAGER privilege.
Syntax
ttRepStart()
Parameters
ttRepStart has no parameters.
Result set
ttRepStart returns no results.
Examples
To start the replication agent, use:
CALL ttRepStart();
Notes
The replication agent does not start if the database does not participate in any replication scheme.
When using this procedure, no application, including the application making the call, can be holding a connection that specifies database-level locking (LockLevel=1).
See also
Description
This procedure returns the current replication state of a database in an active standby pair.
This procedure requires no privilege.
Syntax
ttRepStateGet()
Parameters
ttRepStateGet has no parameters.
Result set
ttRepStateGet returns the result:
| Column | Type | Description |
|---|---|---|
state | TT_VARCHAR (20) NOT NULL | The current replication state of the database. One of:
|
gridState | TT_VARCHAR (20) NOT NULL | The current grid state of the database. One of:
|
Examples
To determine the replication and the grid state of the active standby pair, use:
Call ttRepStateGet(); <STANDBY, NO GRID> Call ttRepStateGet(); <ACTIVE, NO GRID> Call ttRepStateGet(); <ACTIVE, AVAILABLE> Call ttRepStateGet(); <ACTIVE, UNAVAILABLE>
See also
Description
This procedure saves the state of a remote peer database in an active standby pair to the currently connected database. Currently, may only be used to indicate to the active database that the standby database, storeName on hostName, has failed, and that all updates on the active database should be replicated directly to the read-only subscribers.
This procedure requires the ADMIN privilege.
Syntax
ttRepStateSave('state', 'storeName', 'hostName')
Parameters
ttRepStateSave has these parameters:
| Parameter | Type | Description |
|---|---|---|
state | TT_VARCHAR (20) NOT NULL | The replication state of the indicated database. May only be specified as FAILED in this release. Recording that a standby database has failed indicates that all replicated updates are to be sent directly from the active database to the read-only subscribers. |
storeName | TT_VARCHAR (200) NOT NULL | Name of the database for which the state is indicated. |
hostName | TT_VARCHAR (200) | Name of the host where the database resides. |
Result set
ttRepStateSave returns no results.
Examples
To indicate to the active database that the standby database standby on host backup1 has failed, use:
ttRepStateSave('FAILED', 'standby', 'backup1');
See also
Description
This procedure sets the replication state of a database in an active standby pair replication scheme. Currently, ttRepStateSet may only be used to set the state of a database to ACTIVE, indicating that it is to take the active role in an active standby pair. ttRepStateSet may only be executed in the following situations:
A database has had a CREATE ACTIVE STANDBY PAIR command executed and no failures have occurred since.
A database is currently in the STANDBY state, and the other database in the active standby pair has had its state changed from ACTIVE to IDLE using the ttRepDeactivate procedure.
A database has just recovered from the local transaction log and was in the ACTIVE state before it went down.
This procedure requires the ADMIN privilege.
Syntax
ttRepStateSet('state')
Parameters
ttRepStateSet has the parameter:
| Parameter | Type | Description |
|---|---|---|
state | TT_VARCHAR (20) NOT NULL | The replication state of the database. Must be ACTIVE, in this release. Setting a store to ACTIVE designates it as the active database in an active standby pair. |
Result set
ttRepStateSet returns no results.
Examples
To set the replication state of the database to ACTIVE, use:
CALL ttRepStateSet('ACTIVE');
See also
Description
This procedure stops the TimesTen replication agent for the connected database.
This procedure requires the CACHE_MANAGER privilege.
Syntax
ttRepStop()
Parameters
ttRepStop has no parameters.
Result set
ttRepStop returns no results.
Examples
To stop the replication agent, use:
CALL ttRepStop();
Note
When using this procedure, no application, including the application making the call, can be holding a connection that specifies database-level locking (LockLevel=1).
See also
Description
This procedure changes a replicating subscriber's state with respect to the executing master store.
This procedure requires the ADMIN privilege.
Syntax
ttRepSubscriberStateSet('replicationName', 'replicationOwner', 'subscriberStoreName', 'subscriberHostName', newStateCode)
Parameters
ttRepSubscriberStateSet has these parameters:
| Parameter | Type | Description |
|---|---|---|
replicationName | TT_CHAR (30) | The name of the replication scheme on which to operate. May be NULL to indicate all replication schemes. |
replicationOwner | TT_CHAR (30) | The owner of the replication scheme. May be NULL to indicate all replication scheme owners. |
subscriberStoreName | TT_VARCHAR (200) | The name of the subscribing database whose state is to be set. May be NULL to indicate all stores on host subscriberHostName. |
subscriberHostName | TT_VARCHAR (200) | The subscriber's host. May be NULL to indicate all hosts of subscribing peers. |
newStateCode | TT_INTEGER | An integer code representing the specified subscriber's new state:
0/ 1 - Paused. 2 - Stopped. All other state codes are disallowed. (This procedure cannot set a subscriber state to "failed.") |
Result set
ttRepSubscriberStateSet returns no results.
Examples
For the replication scheme named REPL.REPSCHEME, the following directs the master database to set the state of the subscriber database (SUBSCRIBERDS ON SYSTEM1) to Stop (2):
CALL ttRepSubscriberStateSet('REPSCHEME', 'REPL', 'SUBSCRIBERDS','SYSTEM1', 2);
To direct the master database to set the state of all its subscribers to Pause (1), use:
CALL ttRepSubscriberStateSet( , , , , 1 );
Leaving a parameter empty is equivalent to using NULL.
See also
Description
This procedure causes the caller to wait until all transactions that committed before the call have been transmitted to the subscriber subscriberStoreName. It also waits until the subscriber has acknowledged that the updates have been durably committed at the subscriber database.
Call this procedure in a separate transaction, when no other transaction is pending on the active database. This call returns an error if any transactions on the active database are open.
If you set the waitTime parameter to -1 and the subscriberStoreName parameter to NULL, the ttRepSubscriberWait procedure does not return until all updates committed up until the time of the procedure call have been transmitted to all subscribers, and all subscribers have acknowledged that the updates have been durably committed.
The ttRepSubscriberWait procedure should not be used when an urgent response is required. Instead, you should use the return receipt service.
|
Note: If this procedure is called after all write transaction activity is quiesced at a store (there are no active transactions and no transactions have started), it may take 60 seconds or longer before the subscriber sends the acknowledgment that all updates have been durably committed at the subscriber. |
This procedure requires no privilege.
Syntax
ttRepSubscriberWait('replicationName', 'replicationOwner', 'subscriberStoreName', 'subscriberHostName', waitTime)
Parameters
ttRepSubscriberWait has these parameters:
| Parameter | Type | Description |
|---|---|---|
replicationName | TT_CHAR (30) | The name of the replication scheme on which to operate. May be NULL to indicate all replication schemes. |
replicationOwner | TT_CHAR (30) | The owner of the replication scheme. May be NULL to indicate all replication scheme owners. |
subscriberStoreName | TT_VARCHAR (200) | The name of the subscribing database whose state is to be set. May be NULL to indicate all stores on host subscriberHostName. |
subscriberHostName | TT_VARCHAR(200) | The subscriber's host. May be NULL to indicate all hosts of subscribing peers. |
waitTime | TT_INTEGER NOT NULL | Number of seconds to wait for the specified subscriber(s). A value of -1 indicates to wait forever. This parameter is required and may not be NULL. |
Result Set
ttRepSubscriberWait returns the result set:
| Column | Type | Description |
|---|---|---|
timeOut | BINARY(1) | 0x00 - The wait succeeded within the allotted waitTime; the specified subscribers are up to date at the time this procedure was called. TimesTen returns 0x01 if not enough time has been granted. |
Example
If there is one defined replication scheme REPOWNER.REPSCHEME, to direct the transmitting database to wait ten minutes for subscriber REP2 on SERVER2 to catch up, use:
CALL ttRepSubscriberWait('REPSCHEME','REPOWNER', 'REP2', 'SERVER2', 600);
See also
Description
This procedure returns static attributes associated with the caller's use of the replication- based return service. This procedure operates with either the RETURN RECEIPT or RETURN TWOSAFE service.
This procedure requires no privilege.
Syntax
ttRepSyncGet()
Parameters
ttRepSyncGet has no parameters.
Result set
ttRepSyncGet returns the result set:
| Column | Type | Description |
|---|---|---|
requestReturn | BINARY(1) | 0 (default) - Don't wait for return notification configured with the RETURN RECEIPT BY REQUEST or RETURN TWOSAFE BY REQUEST option.
|
returnWait | TT_INTEGER | Specifies the number of seconds to wait for return service acknowledgment. The default value is 10 seconds. A value of `0' means that there is no wait time.This attribute persists across transaction boundaries and applies to all RETURN services independent of the BY REQUEST option. |
localAction | TT_INTEGER | The current LOCAL ACTION configuration for RETURN services.
|
Example
To retrieve the caller's requestReturn value, use:
SQLCHAR requestReturn[1];
SQLINTEGER len;
rc = SQLExecDirect ( hstmt
, (SQLCHAR *) "{CALL ttRepSyncGet( NULL )}"
, SQL_NTS )
rc = SQLBindCol ( hstmt
, /* ColumnNumber */ 1
, /* TargetType */ SQL_C_BINARY )
, /* TargetValuePtr */ requestReturn
,./* BufferLength */ sizeof requestReturn
, /* StrLen_ */ &len );
rc = SQLFetch( hstmt );
if ( requestReturn[0] ) {
...
}
Notes
When called within a standalone transaction, ttRepSyncGet always returns the default value for requestReturn.
ttRepSyncGet may be called at any point within a transaction in which it is used to request the BY REQUEST return service for that transaction.
If you call ttRepSyncGet in a transaction that does not, in fact, update any RETURN RECEIPT BY REQUEST or RETURN TWOSAFE BY REQUEST replication elements, the call has no external effect.
See also
Description
This procedure sets static attributes associated with the caller's use of the replication-based return service. This procedure operates with either the RETURN RECEIPT or RETURN TWOSAFE service.
This procedure requires no privilege.
Syntax
ttRepSyncSet(requestReturn, returnWait, localAction)
Parameters
ttRepSyncSet has these optional parameters:
| Parameter | Type | Description |
|---|---|---|
requestReturn | BINARY(1) | 0x00 - Turn off the return service for the current transaction.
You can use this parameter to turn on or turn off return services only when the replication subscribers have been configured with |
returnWait | TT_INTEGER | Specifies the number of seconds to wait for return service acknowledgment. The default value is 10 seconds. A value of '0' means there is no wait time.
This timeout value overrides the value set by the The timeout set by this parameter persists across transaction boundaries and applies to all return services independent of the |
localAction | TT_INTEGER | Action to be performed in the event the subscriber is unable to acknowledge commit of the transaction within the timeout period specified by returnWait. This parameter can only be used for return twosafe transactions. Set to NULL when using the RETURN service.
1 (default) - 2 - |
Result set
ttRepSyncSet has no result set.
Examples
To enable the return receipt service in the current transaction for all the replication elements configured with RETURN RECEIPT BY REQUEST or RETURN TWOSAFE BY REQUEST, use:
rc = SQLExecDirect ( hstmt,
(SQLCHAR *)"{CALL ttRepSyncSet( 0x01 )}",
SQL_NTS )
Note
The call to enable the return receipt service must be part of the transaction (AutoCommit must be off).
See also
Description
This procedure queries a subscriber database in a replication scheme configured with a return service and a RETURN DISABLE failure policy to determine whether return service blocking for the subscriber has been disabled by the failure policy.
The ttRepSyncSubscriberStatus procedure returns the failure status of the subscriber database with the specified name on the specified host. You can specify only the storeName. However, an error is generated if the replication scheme contains multiple subscribers with the same name on different hosts.
This procedure requires no privilege.
Syntax
ttRepSyncSubscriberStatus('storeName', 'hostName')
Parameters
ttRepSyncSubscriberStatus has these parameters:
| Parameter | Type | Description |
|---|---|---|
subscriber | TT_VARCHAR (200) NOT NULL | The name of the subscribing database to be queried. |
hostName | TT_VARCHAR (200) | The host name of one or more stores that are configured to receive updates from the executing store; if NULL, then receiving stores are identified by receiver alone. If both receiver and hostname are NULL, then all receiving stores are selected. |
Result set
ttRepSyncSubscriberStatus returns:
| Column | Type | Description |
|---|---|---|
disabled | TT_INTEGER | Value is either:
'1' if the return service has been disabled on the subscriber database. '0' if the return service is still enabled on the subscriber database. |
Note
If the replication scheme specifies DISABLE RETURN ALL, then you must use ttRepSyncSubscriberStatus to query the status of each individual subscriber in the replication scheme.
Description
This procedure returns the status of transmission of updates to subscribers for the current transaction. The corresponding ttRepSyncSet built-in procedure allows you to stop transmission of updates to subscribers for the length of a transaction.
This procedure requires the ADMIN privilege.
Syntax
ttRepTransmitGet()
Parameters
ttRepTransmitGet has no parameters.
Result set
ttRepTransmitGet returns the result:
| Column | Type | Description |
|---|---|---|
transmit | TT_INTEGER | 0 - Updates are not being transmitted to any subscribers for the remainder of the transaction on the connection.
1 (default) - Updates are being transmitted to subscribers on the connection. |
Example
To return the transmit status on the active database in an active standby pair, use:
CALL ttRepTransmitGet();
See also
Description
This procedure stops subsequent updates on the connection it is executed in from being replicated to any subscriber.
This procedure should be used with care since it could easily lead to transactional inconsistency of remote stores if partial transactions are replicated. If updates are disallowed from getting replicated, the subscriber stores diverge from the master store.
Required privilege
This procedure requires the ADMIN privilege.
Syntax
ttRepTransmitSet(transmit)
Parameters
ttRepTransmitSet has the parameter:
| Parameter | Type | Description |
|---|---|---|
transmit | TT_INTEGER NOT NULL | When set to 1 updates are transmitted to subscribers on the connection after the built-in is executed to replicate (default).
When set to 0 updates are not transmitted to any subscribers for the remainder of the transaction in which this call was issued and on the connection that issued it. |
Result set
ttRepTransmitSet returns no results.
Example
To activate the active database in an active standby pair, use:
CALL ttRepTransmitSet(1);
To deactivate the active database in an active standby pair, use:
CALL ttRepTransmitSet(0);
See also
Description
This procedure checks on the status of a RETURN RECEIPT or RETURN TWOSAFE replication transaction. Using the built-in procedure ttRepXactTokenGet, you can get the token of a RETURN RECEIPT or RETURN TWOSAFE transaction. This is then passed as an input parameter to this built-in procedure. Only a token received from ttRepXactTokenGet may be used. The procedure returns a list of rows each of which have three parameters, a subscriber name, the replication status with respect to the subscriber and an error string that is only returned if a RETURN TWOSAFE replication transaction began but did not complete commit processing.
|
Note: The error parameter is only returned forRETURN TWOSAFE transactions. |
This procedure requires no privilege.
Syntax
ttRepXactStatus(token)
Parameters
ttRepXactStatus has the parameter:
| Parameter | Type | Description |
|---|---|---|
xactID | VARBINARY (10000) | If no parameter is specified, status is returned for one of the following:- If called in a transaction that has begun, but not completed, commit processing, it returns the status of the transaction.- If called at any other time, it returns status for the most recently committed transaction on the connection that was in RETURN RECEIPT or RETURN TWOSAFE mode. |
Result set
ttRepXactStatus returns the result set:
| Column | Type | Description |
|---|---|---|
subscriberName | TT_CHAR (61) | The name of the database that subscribes to tables updated in the transaction. The name returns as: store_name@host_name. |
state | TT_CHAR (2) | The state of the transaction with respect to the subscribing database. The return values are one of the following:
|
errorString | TT_VARCHAR (2000) | Error string retuned by the subscriber agent describing the error it encountered when applying the twosafe transaction. If no error is encountered, this parameter is NULL. Non-null values are only returned when this procedure is called inside a twosafe replication transaction that has begun, but has not yet completed, processing a commit. |
See also
Description
This procedure returns a token for RETURN RECEIPT or RETURN TWOSAFE replication transactions. Depending on the input parameter, type, it returns either:
A token to the most recently committed RETURN RECEIPT transaction on the connection handle in which it is invoked.
A token to the most recent transaction on the connection handle in which it is invoked that has begun commit processing on a transaction in RETURN TWOSAFE mode.
This procedure can be executed in any subsequent transaction or in the same transaction after commit processing has begun for a transaction in RETURN TWOSAFE replication.
This procedure requires no privilege.
Syntax
ttRepXactTokenGet('type')
Parameters
ttRepXactTokenGet has these parameters:
| Parameter | Type | Description |
|---|---|---|
type | TT_CHAR (2) NOT NULL | The type of transaction desired:
|
Result set
ttRepXactTokenGet returns the result set:
| Column | Type | Description |
|---|---|---|
token | VARBINARY (10000) | A varbinary token used to represent the transaction desired. |
See also
Description
This procedure explicitly sets the value for the user-specified column ID. Updates presented to the application by the Transaction Log API may contain information about the columns of a table. This column information contains a system-specified column number and a user-specified column identifier. The user-specified column ID has the value 0 until set explicitly by this call.
The system assigns an ID to each column during a CREATE or ALTER TABLE operation. Setting a user-assigned value for the column ID allows you to have a unique set of column numbers across the entire database or a specific column numbering system for a given table.
This procedure requires the XLA privilege.
Syntax
ttSetUserColumnID('tblName', 'colName', repID)
Parameters
ttSetUserColumnID has these parameters:
| Parameter | Type | Description |
|---|---|---|
tblName | TT_CHAR(61) NOT NULL | Table name.
Using a synonym to specify a table name is not supported. |
colName | TT_CHAR(30) NOT NULL | Column name. |
repID | TT_INTEGER NOT NULL | Integer identifier. |
Result set
ttSetUserColumnID returns no results.
Example
CALL ttSetUserColumnID('APP.SESSION', 'SESSIONID', 15);
See also
Description
This procedure explicitly sets the value of the user table ID. The table that each row is associated with is expressed with two codes: an application-supplied code called the user table ID and a system-provided code called the system table ID. Updates are presented to the application by the Transaction Log API in the form of complete rows. The user table ID has the value zero until explicitly set with the ttSetUserTableID procedure.
This procedure requires the XLA privilege.
Syntax
ttSetUserTableID('tblName', repID)
Parameters
ttSetUserTableID has these parameters:
| Parameter | Type | Description |
|---|---|---|
tblName | TT_CHAR (61) NOT NULL | Table name.
Using a synonym to specify a table name is not supported. |
repID | BINARY(8) NOT NULL | Integer identifier. |
Result set
ttSetUserTableID returns no results.
Example
CALL ttSetUserTableID('APP.SESSION', 0x123456);
See also
Description
This procedure estimates the size of a table or view. It returns a single row with a single DOUBLE column with the estimated number of bytes for the table. The table can be specified as either a table name or a fully qualified table name. A non-NULL nrows parameter causes the table size to be estimated assuming the statistics of the current table scaled up to the specified number of rows. If the nrows parameter is NULL, the size of the table is estimated with the current number of rows.
The current contents of the table are scanned to determine the average size of each VARBINARY and VARCHAR column. If the table is empty, the average size of each VARBINARY and VARCHAR column is estimated to be one-half its declared maximum size. The estimates computed by ttSize include storage for the table itself, VARBINARY and VARCHAR columns and all declared indexes on the table.
The table is scanned when this built-in procedure is called. The scan of the table can be avoided by specifying a non-NULL frac value, which should be between 0 and 1. This value is used to estimate the average size of varying-length columns. The maximum size of each varying-length column is multiplied by the frac value to compute the estimated average size of VARBINARY or VARCHAR columns. If the frac parameter is not given, the existing rows in the table are scanned and the average length of the varying-length columns in the existing rows is used. If frac is omitted and the table has no rows in it, then frac is assumed to have the value 0.5.
This procedure requires the SELECT privilege on the specified table.
Syntax
ttSize('tblName', nRows, frac)
Parameters
ttSize has these parameters:
| Parameter | Type | Description |
|---|---|---|
tblName | TT_CHAR(61) NOT NULL | Name of an application table. Can include table owner. This parameter is required.
Using a synonym to specify a table name is not supported. |
nRows | TT_INTEGER | Number of rows to estimate in a table. This parameter is optional. |
frac | BINARY_DOUBLE | Estimated average fraction of VARBINARY or VARCHAR column sizes. This parameter is optional. |
Result set
ttSize returns the following result set.
| Column | Type | Description |
|---|---|---|
size | BINARY_DOUBLE NOT NULL | Estimated size of the table, in bytes. |
Examples
CALL ttSize('ACCTS', 1000000, NULL);
CALL ttSize('ACCTS', 30000, 0.8);
CALL ttSize('SALES.FORECAST', NULL, NULL);
When using ttSize, you must first execute the command and then fetch the results. For example:
ODBC
double size;
SQLLEN len;
rc = SQLExecDirect(hstmt, "call ttSize('SalesData', 250000,
0.75)", SQL_NTS);
rc = SQLBindColumn(hstmt, 1, SQL_C_DOUBLE, &size, sizeof double,
&len);
rc = SQLFetch(hstmt);
rc = SQLFreeStmt(hstmt, SQL_CLOSE);
JDBC
. . . . . .
String URL="jdbc:timesten:MyDataStore";
Connection con;
double tblSize=0;
. . . . . .
con = DriverManager.getConnection(URL);
CallableStatement cStmt = con.prepareCall("
{CALL ttSize('SalesData', 250000, 0.75) }");
if( cStmt.execute() )
{
rs=cStmt.getResultSet();
if (rs.next()) {
tblSize=rs.getDouble(1);
}
rs.close();
}
cStmt.close();
con.close();
. . . . . .
Note
The ttSize procedure allows you to estimate how large a table will be with its full population of rows based on a small sample. For the best results, we recommend populating the table with at least 1,000 typical rows.
See also
Description
This procedure returns all compiled SQL statements in the TimesTen SQL command cache.
This procedure requires the ADMIN privilege.
Syntax
ttSQLCmdCacheInfo()
Parameters
ttSQLCmdCacheInfo has the optional parameter:
| Parameter | Type | Description |
|---|---|---|
sqlCommandID | TT_INTEGERfor 32-bit systems
| The unique identifier of a SQL command in the TimesTen command cache. If no value is supplied displays information about all current commands in the TimesTen cache. |
Result set
ttSQLCmdCacheInfo returns the result set:
| Parameter | Type | Description |
|---|---|---|
sqlCommandID | TT_INTEGER NOT NULL for 32-bit systems
| The unique identifier of a command in the TimesTen command cache. |
privateCommandConnectionID | TT_INTEGER | The unique ID of a private connection. If not a private connection, the value is 2048. |
executions | TT_BIGINT
| A counter for the number of executions that took place on this command since it was brought into the command cache. |
prepares | TT_BIGINT NOT NULL | A counter for the number of user prepares that result in a hit on the command cache. |
reprepares | TT_BIGINT NOT NULL | A counter for the number of reprepares or invalidations of this command. |
freeable | TT_TINYINT NOT NULL | Indicates whether this command can be garbage collected by the subdaemon.
|
size | TT_INTEGER NOT NULL | The total space (bytes) allocated for this command in the command cache. |
owner | TT_CHAR(31) NOT NULL | The identifier of the user who created this command. |
queryText | TT_VARCHAR (409600) NOT NULL | The full SQL text for the current command. |
Examples
To display command info for all the current valid commands, use
Command> CALL ttSQLCmdCacheInfo;
< 528079360, 2048, 0, 1, 0, 1, 2168, TTUSER , select * from t7 where x7 is
not null or exists (select 1 from t2,t3 where not 'tuf' like 'abc') >
< 527609108, 2048, 0, 1, 0, 1, 2960, TTUSER , select * from t1 where x1 =
(select x2 from t2 where z2 in (1,3) and y1=y2) order by 1, 2, 3 >
< 528054656, 2048, 0, 1, 0, 1, 1216, TTUSER , create table t2(x2 int,y2 int,
z2 int) >
< 528066648, 2048, 0, 1, 0, 1, 1176, TTUSER , insert into t2 select * from t1 >
< 528013192, 2048, 0, 1, 0, 1, 1848, TTUSER , select * from t1 where exists
(select * from t2 where x1=x2) or y1=1 >
< 527582620, 2048, 0, 1, 0, 1, 1240, TTUSER , insert into t2 select * from t1 >
< 527614292, 2048, 0, 1, 0, 1, 2248, TTUSER , select * from t1 where exists
(select x2 from t2 where x1=x2) order by 1, 2, 3 >
< 528061248, 2048, 0, 1, 0, 1, 696, TTUSER , create index i1 on t3(y3) >
< 528070368, 2048, 0, 1, 0, 1, 824, TTUSER , call ttOptSetOrder('t3 t4 t2 t1') >
< 528018856, 2048, 0, 1, 0, 1, 984, TTUSER , insert into t2 select * from t1 >
< 527606460, 2048, 0, 1, 0, 1, 2624, TTUSER , select * from t1 where x1 =
(select x2 from t2 where y1=y2) order by 1, 2, 3 >
< 528123000, 2048, 0, 1, 0, 1, 3616, TTUSER , select * from t1 where x1 = 1 or
x1 = (select x2 from t2,t3 where z2=t3.x3) >
< 528074624, 2048, 0, 1, 0, 1, 856, TTUSER , call ttOptSetOrder('t4 t2 t3 t1') >
< 527973892, 2048, 0, 1, 0, 1, 2872, TTUSER , select * from t1 where x1 in
(select x2 from t2) or x1 in (select x3 from t3) order by 1, 2, 3 >
< 527953876, 2048, 0, 1, 0, 1, 3000, TTUSER , select * from t1 where x1 =
(select x2 from t2) order by 1, 2, 3 >
< 527603900, 2048, 0, 1, 0, 1, 2440, TTUSER , select * from t1 where x1 in
(select x2 from t2 where y1=y2) order by 1, 2, 3 >
< 528093308, 2048, 0, 1, 0, 1, 3608, TTUSER , select * from t1 where x1 = 1 or
x1 = (select x2 from t2,t3 where z2=t3.x3 and t3.z3=1) >
< 528060608, 2048, 0, 1, 0, 1, 696, TTUSER , create index i1 on t2 (y2) >
…..
To display command info for the SqlCmdId 527973892, use
Command> CALL ttSQLCmdCacheInfo(527973892); < 527973892, 2048, 0, 1, 0, 1, 2872, TTUSER, select * from t1 where x1 in (select x2 from t2) or x1 in (select x3 from t3) order by 1, 2, 3 > 1 row found.
Description
This procedure returns command statistics update information. This procedure can be useful for certain operations, such as CREATE INDEX or DROP INDEX and other DDL operations that alter objects.
This procedure requires no privilege.
Syntax
ttSQLCmdCacheInfoGet()
Parameters
ttSQLCmdCacheInfoGet has no parameters.
Result set
ttSQLCmdCacheInfoGet returns the result set:
| Parameter | Type | Description |
|---|---|---|
cmdCount | TT_INTEGER NOT NULL | Number of commands in the cache. |
freeableCount | TT_INTEGER NOT NULL | Count of number of freeable commands that can be garbage collected by the subdaemon at that moment. This number is obtained by examining the command information. |
size | TT_BIGINT NOT NULL | The current total space allocated to store all the cached commands. |
Examples
To generate a list of all TimesTen instance users, use:
Command> CALL ttSQLCmdCacheInfoGet; < 5,4,12316 > 1 row found
Description
This procedure returns all detailed run-time query plans for SQL statements in the TimesTen SQL command cache. If no argument is supplied, this procedure displays the query plan for all valid commands in the TimesTen cache. For invalid commands, an error is returned that displays the text of the query and the syntax problems.
This procedure requires the ADMIN privilege.
Syntax
ttSQLCmdQueryPlan(commandID)
Parameters
ttSQLCmdQueryPlan has the optional parameter:
| Parameter | Type | Description |
|---|---|---|
sqlCmdID | TT_INTEGER for 32-bit systems
| The unique identifier of a SQL command in the TimesTen command cache. If no value is supplied displays the query plan for all valid commands in the TimesTen cache. |
Result set
ttSQLCmdQueryPlan returns the result set:
| Parameter | Type | Description |
|---|---|---|
sqlCmdID | TT_INTEGER NOT NULL for 32-bit systems
| The unique identifier of a command in the TimesTen command cache. |
queryText | TT_VARCHAR(1024) | The first 1024 characters of the SQL text for the current command. |
step | TT_INTEGER | The step number of current operation in this run-time query plan. |
level | TT_INTEGER | The level number of current operation in this run-time query plan. |
operation | TT_CHAR(31) | The operation name of the current step in this run-time query plan. |
tblName | TT_CHAR(31) | Name of the table used in this step, if any.
Using a synonym to specify a table name is not supported. |
tblOwnerName | TT_CHAR(31) | Name of the owner of the table used in this step, if any. |
ixName | TT_CHAR(31) | Name of the index used in this step, if any. |
indexedPred | TTVARCHAR(1024) | In this step, if an index is used, the indexed predicate is printed if available. Not all expressions can be printed out and the output may be fragmented and truncated. "..." represents the unfinished portion of the expression. |
nonIndexedPred | TT_VARCHAR(1024) | In this step, if a non-indexed predicate is used, the non-indexed predicate is printed if available. Not all expressions can be printed out and the output may be fragmented and truncated. "..." represents the unfinished portion of the expression. |
Examples
To display the query plan for SQLCmdID 528078576:
Command> call ttSqlCmdQueryPlan(528078576); < 528078576, select * from t1 where 1=2 or (x1 in (select x2 from t2, t5 where y2 in (select y3 from t3)) and y1 in (select x4 from t4)), <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL> > < 528078576, <NULL>, 0, 4, RowLkSerialScan , T1 , TTUSER , , , > < 528078576, <NULL>, 1, 7, RowLkRangeScan , T2 , TTUSER , I2 , , > < 528078576, <NULL>, 2, 7, RowLkRangeScan , T5 , TTUSER , I2 , , > < 528078576, <NULL>, 3, 6, NestedLoop , , , , , > < 528078576, <NULL>, 4, 6, RowLkRangeScan , T3 , TTUSER , I1 , ( (Y3=Y2; ) ) , > < 528078576, <NULL>, 5, 5, NestedLoop , , , , , > < 528078576, <NULL>, 6, 4, Filter , , , , , X1 = X2; > < 528078576, <NULL>, 7, 3, NestedLoop(Left OuterJoin) , , , , , > < 528078576, <NULL>, 8, 2, Filter , , , , , > < 528078576, <NULL>, 9, 2, RowLkRangeScan , T4 , TTUSER , I2 , , Y1 = X4; > < 528078576, <NULL>, 10, 1, NestedLoop(Left OuterJoin) , , , , , > < 528078576, <NULL>, 11, 0, Filter , , , , , > 13 rows found.
To display query plans for all valid queries, omit the argument for ttSqlCmdQueryPlan:
< 528079360, select * from t7 where x7 is not null or exists (select 1 from t2,t3
where not 'tuf' like 'abc'), <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>,
<NULL>, <NULL> >
< 528079360, <NULL>, 1, 3, RowLkRangeScan , T2
, TTUSER , I2 , , NOT(LIKE( tuf ,abc ,NULL )) >
< 528079360, <NULL>, 2, 3, RowLkRangeScan , T3 , TTUSER , I2 , , >
< 528079360, <NULL>, 3, 2, NestedLoop , , , , , >
< 528079360, <NULL>, 4, 1, NestedLoop(Left OuterJoin) , , , , , >
< 528079360, <NULL>, 5, 0, Filter , , , , , X7 >
< 527576540, call ttSqlCmdQueryPlan(527973892), <NULL>, <NULL>, <NULL>, <NULL>,
<NULL>, <NULL>, <NULL>, <NULL> >
< 527576540, <NULL>, 0, 0, Procedure Call , , , , , >
< 528054656, create table t2(x2 int,y2 int, z2 int), <NULL>, <NULL>, <NULL>,
<NULL>, <NULL>, <NULL>, <NULL>, <NULL> >
< 528066648, insert into t2 select * from t1, <NULL>, <NULL>, <NULL>, <NULL>,
<NULL>, <NULL>, <NULL>, <NULL> >
< 528066648, <NULL>, 0, 0, Insert , T2 , TTUSER , , , >
< 528013192, select * from t1 where exists (select * from t2 where x1=x2) or y1=1,
<NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL> >
< 528061248, create index i1 on t3(y3), <NULL>, <NULL>, <NULL>, <NULL>, <NULL>,
<NULL>, <NULL>, <NULL> >
< 528070368, call ttOptSetOrder('t3 t4 t2 t1'), <NULL>, <NULL>, <NULL>, <NULL>,
<NULL>, <NULL>, <NULL>, <NULL> >
< 528070368, <NULL>, 0, 0, Procedure Call , , , , , >
< 528018856, insert into t2 select * from t1, <NULL>, <NULL>, <NULL>, <NULL>,
<NULL>, <NULL>, <NULL>, <NULL> >
< 527573452, call ttsqlCmdCacheInfo(527973892), <NULL>, <NULL>, <NULL>, <NULL>,
<NULL>, <NULL>, <NULL>, <NULL> >
< 527573452, <NULL>, 0, 0, Procedure Call , , , , , >
….. /* more rows here */
Description
This built-in procedure evaluates a SELECT query on an Oracle table and generates a CREATE TABLE SQL statement that you can choose to execute. The TimesTen CREATE TABLE statement matches the result set column names and types.
This procedure does not create the TimesTen table, it only returns a statement that identifies the table schema.
For more details and usage information, see "Loading Oracle data into a TimesTen table" in the Oracle TimesTen In-Memory Database Operations Guide.
This procedure requires no privileges. The session user must have all required privileges to execute the query on the Oracle database.
Syntax
ttTableSchemaFromOraQueryGet(TblOwner, tblname, Query)
Parameters
ttTableSchemaFromOraQueryGet has the optional parameter:
| Parameter | Type | Description |
|---|---|---|
tblOwner | TT_CHAR (30) | TimesTen table owner. |
tblName | TT_CHAR (30) NOT NULL | Table name for the CREATE TABLE statement.
The specified TimesTen table cannot be a system table, a synonym, a view, a materialized view or a detail table of a materialized view, a global temporary table, a cache group table or a table that contains compressed columns. |
Query | TT_VARCHAR (409600) NOT NULL | An Oracle SELECT query to derive the table column definition.
Any expressions in the |
Result set
ttTableSchemaFromOraQueryGet returns the result set:
| Parameter | Type | Description |
|---|---|---|
tableSchema | TT_VARCHAR (409600) NOT NULL | A CREATE TABLE statement that matches the result set of the Oracle SELECT query. |
Examples
This example, returns the CREATE TABLE statement to create the TimesTen HR.EMPLOYEES table with all columns found in the Oracle HR.EMPLOYEES table.
Command> call ttTableSchemaFromOraQueryGet('hr','employees',
'SELECT * FROM hr.employees');
< CREATE TABLE "HR"."EMPLOYEES" (
"EMPLOYEE_ID" number(6,0) NOT NULL,
"FIRST_NAME" varchar2(20 byte),
"LAST_NAME" varchar2(25 byte) NOT NULL,
"EMAIL" varchar2(25 byte) NOT NULL,
"PHONE_NUMBER" varchar2(20 byte),
"HIRE_DATE" date NOT NULL,
"JOB_ID" varchar2(10 byte) NOT NULL,
"SALARY" number(8,2),
"COMMISSION_PCT" number(2,2),
"MANAGER_ID" number(6,0),
"DEPARTMENT_ID" number(4,0)
) >
1 row found.
Notes
The Oracle query cannot have any parameter bindings.
TimesTen returns an error if the query cannot be described on Oracle, for example, if there is a syntax error.
If an output column type does not have a matching type in TimesTen, TimesTen outputs a warning and the following line for the column definition: >>>>column_name column_type /* reason */
If the Oracle query outputs types not supported by TimesTen, you can add a CAST clause in the SELECT list to explicitly change the output to a TimesTen supported type. Column aliases can be specified for expressions in the SELECT list.
If the Oracle query has LOB output, it is mapped to a variable type.
Description
This procedure returns the five digits that make up the release number of the current TimesTen instance.
Required privilege
This procedure requires no privilege.
Syntax
ttVersion()
Parameters
ttVersion has no parameters.
Result set
ttVersion returns the result set:
| Column | Type | Description |
|---|---|---|
major | TT_INTEGER NOT NULL | The major release number. Indicates releases with major infrastructure and functionality changes. |
| minor | TT_INTEGER NOT NULL | The minor release number. Indicates a version with new functionality changes, but no infrastructure changes. |
patch | TT_INTEGER NOT NULL | The patch release number. Indicates a release that contains all bug fixes since the previous maintenance release. |
relDot4 | TT_INTEGER NOT NULL | Indicates a release with minor bug fixes. |
relDot5 | TT_INTEGER NOT NULL | This digit in the release number is reserved for future use. |
Example
CALL ttVersion( ); <11, 2, 2 , 1, 0> 1 row found.
In this case, the TimesTen release number is: 11.2.2.1.0.
Description
This procedure allows applications to specify that operations executed on the current connection should return a warning if they allocate memory and find that memory is low. If the value is set, a warning is returned for any operation that does an allocation and finds total memory in use to be above the connection's threshold value as specified by the PermWarnThreshold and TempWarnThreshold connection attributes. See "Connection Attributes" for more information.
This procedure requires no privilege.
Syntax
ttWarnOnLowMemory(permanent, temporary)
Parameters
ttWarnOnLowMemory has these parameters:
| Parameter | Type | Description |
|---|---|---|
permanent | TT_INTEGER NOT NULL | 1(enable) or 0 (disable) warnings for the permanent data partition. |
temporary | TT_INTEGER NOT NULL | 1 (enable) or 0 (disable) warnings for the temporary data partition. |
Result set
ttWarnOnLowMemory returns no results.
Example
CALL ttWarnOnLowMemory(1, 0);
Enables low memory warnings for the permanent data partition only.
Note
By default, low memory warnings are not issued for either partition. Applications that want to receive these warnings must call this procedure. This procedure is connection specific, and so you must issue it for each connection upon which warnings are desired. Also, the current setting does not persist to subsequent connections.
Description
This procedure returns transaction ID information for interpreting lock messages. The two result columns of ttXactIdGet are used in combination to uniquely identify a transaction in a database. Taken individually, the columns are not interesting. The result should only be used to correlate with other sources of transaction information. The numbers may not follow a strict pattern.
This procedure requires no privilege.
Syntax
ttXactIdGet()
Parameters
ttXactIdGet has no parameters.
Result set
ttXactIdGet returns the result set:
| Column | Type | Description |
|---|---|---|
xactID | TT_INTEGER | Connection ID. |
counter | TT_BIGINT | An increasing number that is used to discriminate successive transactions of the same transaction ID. |
Example
Command > automcommit 0; Command > call ttXactIdGet; <2,11> 1 row found Command > commit; Command > call ttXactIdGet <3, 12> 1 row found
Note
The output correlates to the values printed in lock error messages and ttXactAdmin lock information output.
See also
Description
This procedure creates the specified bookmark.
This procedure requires the XLA privilege.
Syntax
ttXlaBookmarkCreate('bookmark', 'replicated')
Parameters
ttXlaBookmarkCreate has the parameter:
| Parameter | Type | Description |
|---|---|---|
bookmark | TT_CHAR (31) NOT NULL | The name of the bookmark to be created. |
replicated | BINARY(1) | 0x00 or NULL (equivalent) for non-replicated bookmarks (default setting).
If |
Result set
ttXlaBookmarkCreate returns no results.
Example
For non-replicated bookmark, execute the following:
Command > call ttXlaBookmarkCreate(mybookmark);
or
Command> call ttxlabookmarkcreate('mybkmk2',0x00);
For a replicated bookmark, execute the following:
Command > call ttXlaBookmarkCreate(mybookmark, 0x01);
For more details on XLA bookmarks, including replicated XLA bookmarks, see "About XLA bookmarks" in the Oracle TimesTen In-Memory Database C Developer's Guide.
See also
Description
This procedure deletes the specified bookmark. The bookmark cannot be deleted while it is in use.
This procedure requires the XLA privilege.
Syntax
ttXlaBookmarkDelete('bookmark')
Parameters
ttXlaBookmarkDelete has the parameter:
| Parameter | Type | Description |
|---|---|---|
bookmark | TT_CHAR (31) NOT NULL | The name of the bookmark to be deleted. |
Result set
ttXlaBookmarkDelete returns no results.
Example
Command > call ttXlaBookmarkDelete('mybookmark');
Note
Before dropping a table that is subscribed to by an XLA bookmark, you must first drop all XLA bookmarks or unsubscribe from XLA tracking.
See also
Description
This procedure configures persistent XLA tracking of a table. This procedure cannot be executed when the specified bookmark is in use.
This procedure requires the XLA privilege.
Syntax
ttXlaSubscribe('tblName', 'bookmark')
Parameters
ttXlaSubscribe has the parameters:
| Parameter | Type | Description |
|---|---|---|
tblName | TT_CHAR (61) NOT NULL | The name of the table to be tracked.
Using a synonym to specify a table name is not supported. |
bookmark | TT_CHAR (31) NOT NULL | The name of the bookmark that the application uses to track this table. |
Result set
ttXlaSubscribe returns no results.
Example
Command > call ttXlaSubscribe ('SALLY.ACCTS', mybookmark);
See also
Description
This procedure stops persistent XLA tracking of a table. This procedure cannot be executed when the specified bookmark is in use.
This procedure requires the XLA privilege.
Syntax
ttXlaUnsubscribe('tblName', 'bookmark')
Parameters
ttXlaUnsubscribe has the parameters:
| Parameter | Type | Description |
|---|---|---|
tblName | TT_CHAR (61) NOT NULL | The name of the table on which XLA tracking should be stopped.
Using a synonym to specify a table name is not supported. |
bookmark | TT_CHAR (31) NOT NULL | The name of the bookmark that the application uses to track this table. |
Result set
ttXlaSubscribe returns no results.
Example
Command > call ttXlaSubscribe ('SALLY.ACCTS', mybookmark);
Note
Before dropping a table that is subscribed to by an XLA bookmark, you must first drop all XLA bookmarks or unsubscribe from XLA tracking.
See also
The ODBC standard defines four connection attributes:
DSN
Driver
UID
PWD
For a description of the ODBC definition of these attributes, see the appropriate ODBC manual for your platform:
Microsoft ODBC 3.0 Programmer's Reference and SDK Guide
Microsoft ODBC 2.0 Programmer's Reference and SDK Guide
This chapter describes all the attributes defined by TimesTen. To view the names and values of most attributes specified in the connection string, an application can use the ttConfiguration built-in procedure.
|
Note: According to the ODBC standard, when an attribute occurs multiple times in a connection string, the first value specified is used, not the last value. |
On UNIX, False means the attribute value is set to 0 and True means the attribute value is set to 1.
On Windows, False means the check box is unchecked and True means the check box is checked.
The following sections provide details on all TimesTen attributes, which are first listed in tables in "List of Attributes". Following the tables, each attribute is described in detail.
Only the instance administrator can change a first connection attribute to a value other than the one currently in effect. (No privileges are required to change AutoCreate and ForceConnect.)
This section includes the tables:
Table 1-1 Data store attributes
| Name | Description | Default |
|---|---|---|
|
|
A name that identifies the specific attributes of a connection to the database. |
None |
|
|
Identifies the physical database. |
None |
|
|
Identifies the character set used by the database. This attribute is required at database creation time. |
None |
|
|
A statement that identifies the use of the data source name. |
None |
|
|
The directory where transaction log files are stored. |
Database directory |
|
|
Specifies that disk space for the database should be preallocated when creating the database. |
0 (False) |
|
|
Starts user-specified parallel replication. |
0 (Starts automatic parallel replication) |
|
|
Specifies the number of tracks available for user-defined parallel replication. |
1 |
|
|
Specifies that the database is not saved to disk. |
0 (False) |
|
|
The type mode for the database. |
0 (Oracle Type Mode) |
Table 1-2 First connection attributes
| Name | Description | Default |
|---|---|---|
|
|
Specifies that the first connection creates the database if it does not exist. |
1 (True) |
|
|
Controls the frequency in seconds that TimesTen performs a background checkpoint. |
600 |
|
|
Controls the amount of data in megabytes that collects in the log between background checkpoints. |
0 (Off) |
|
|
Controls the maximum rate at which data should be written to disk during a checkpoint operation. |
0 (Unlimited rate) |
|
|
Indicates the upper bound on the number of user-specified concurrent connections to the database. |
The lesser of |
|
|
Specifies whether a connection is allowed to a failed database if it is not properly restored from the corresponding subscriber database. |
0 (Connection disallowed) |
|
|
Determines whether the first connection to a database should proceed if TimesTen recovery encounters a defective log record. |
1 (Continues after log is truncated) |
|
|
The size of the internal log buffer in MB. |
64 |
|
|
The number of log buffer strands. |
4 |
|
|
The transaction log file size in MB. |
64 |
|
|
Controls the method used by TimesTen to write and sync log data to transaction log files. |
1 (Write data to transaction log files using buffered writes. Use explicit sync operations as needed to sync log data to disk) |
|
|
Specifies that unneeded transaction log files are deleted during a checkpoint operation. |
1 (True) |
|
|
Allows applications that connect to a shared database to specify whether the real memory should be locked during database loading. |
0 (Do not acquire a memory lock) |
|
|
Specifies that the existing database should be overwritten with a new one when a connection is attempted. |
0 (False) |
|
|
The size in MB for the permanent partition of the database. |
32 |
|
|
Controls the number of threads used to apply changes on the active master database to the standby master database in an active standby pair replication scheme. |
1 |
|
|
The number of threads used to rebuild indexes during recovery. |
'1 |
|
|
The size in MB for the temporary partition of the database. |
The default size is determined from the |
Table 1-3 General connection attributes
| Name | Description | Default |
|---|---|---|
|
|
Specifies whether there is a symbolic name for the data source. |
The process name |
|
|
Controls transactional commit behavior in relation to DDL. |
0 (Oracle behavior) |
|
|
Determines whether a table is included in an active standby pair replication scheme at table creation time when the |
|
|
|
Enables replication of DDL (Data Definition Language) statements in an active standby replication scheme. |
2 (Replication of objects enabled) |
|
|
Specifies whether diagnostic messages are generated. |
1 (Messages are generated) |
|
|
Determines whether applications use TimesTen or Oracle parameter binding for duplicate occurrences of a parameter in a SQL statement. |
0 (Oracle-style binding) |
|
|
Specifies that commit operations should write log records to disk. |
0 (Records not written to disk) |
|
|
Specifies whether the isolation level is read committed or serializable. |
1 (Read committed) |
|
|
Specifies whether the connection should use row-level locking (value = 0) or database-level locking (value = 1). |
0 (Row-level locking) |
|
|
Allows an application to configure the lock wait interval for the connection. |
10 seconds |
|
|
Specifies that value used for the LogPurge attribute should match those of current connections. |
0 (False) |
|
|
The threshold at which TimesTen returns a warning and throws an SNMP trap when the permanent partition of the database is low in memory. |
90% |
|
|
Determines if commands are shared between connections. |
0 (On) |
|
See "UID and PWD". |
Specify the password that corresponds with the specified UID. When caching Oracle data, PWD specifies the TimesTen password. You can specify the Oracle PWD in the connection string, if necessary. |
None |
|
The value of the encrypted user password. |
None | |
|
|
Determines whether TimesTen returns a warning and throws an SNMP trap if a query times out before executing. |
0 (No warning is returned) |
|
|
Assigns a connection to a replication track. |
None |
|
|
Specifies the time limit in seconds within which the database should execute SQL statements. |
0 (No timeout) |
|
|
The threshold at which TimesTen returns a warning and throws an SNMP trap when the temporary partition of the database is low in memory. |
90% |
|
See "UID and PWD". |
Specify a user name that is defined on the server. When caching Oracle data, the UID must match the UID on the Oracle database that is being cached in TimesTen. |
None |
|
|
Specifies that the connection attempt should wait if an immediate connection is not possible. |
1 |
Table 1-4 NLS general connection attributes
| Name | Description | Default |
|---|---|---|
|
|
The character encoding for the connection, which may be different from the database character set. |
|
|
|
The default length semantics configuration. |
|
|
|
Determines whether an error is reported when there is data loss during an implicit or explicit character type conversion between |
0 (False) |
|
|
The collating sequence to use for linguistic comparisons. |
|
Table 1-5 PL/SQL first connection attributes
| Name | Description | Default |
|---|---|---|
|
|
Determines whether PL/SQL is enabled. |
1 (PL/SQL is enabled) |
|
|
The virtual address at which the shared memory segment is loaded into each process that uses the TimesTen direct drivers. |
Platform specific |
|
|
The size in megabytes of the shared memory segment used by PL/SQL. |
32 MB |
Table 1-6 PL/SQL general connection attributes
| Name | Description | Default |
|---|---|---|
|
|
Controls whether the PL/SQL compiler generates cross-reference information. |
|
|
|
Controls conditional compilation of PL/SQL units. |
|
|
|
Specifies the maximum amount of process heap memory in MB that PL/SQL can use for this connection. |
100 |
|
|
The optimization level that is used to compile PL/SQL library units. |
2 |
|
|
The number of seconds a PL/SQL procedure can run before being automatically terminated. |
30 seconds |
Table 1-7 IMDB Cache first connection attributes
| Name | Description | Default |
|---|---|---|
|
|
Enables the AWT propagation method to be used on Oracle tables. |
0 ( |
Table 1-8 IMDB Cache database attributes
| Name | Description | Default |
|---|---|---|
|
|
Indicates the number of threads that should be used to apply changes to the Oracle database. |
1 |
|
|
Enables cache grid. |
1 (Enabled) |
|
|
Sets the maximum message wait time. |
60 seconds |
Table 1-9 IMDB Cache general connection attributes
| Name | Description | Default |
|---|---|---|
|
|
Enables or disables transparent load of Oracle data to dynamic cache groups. |
1 (Dynamic cache group load is enabled) |
|
|
Determines if an error message is returned upon a transparent load failure. |
0 (Errors are not returned) |
|
|
The Oracle Service Name of the Oracle instance from which data is to be loaded into a TimesTen database. This attribute is only used by the cache agent. Set the |
None |
|
|
Identifies the password for the Oracle database that is being cached in TimesTen. |
None |
|
|
Specifies which SQL statements are executed locally in TimesTen and which SQL statements are passed through to Oracle for execution. |
0 |
|
|
Specifies whether to enable or disable the installation of Application Failover (TAF) and Fast Application Notification (FAN) callbacks. |
1 (Install the callbacks) |
Table 1-10 Client connection attributes
Table 1-11 Server connection attributes
| Name | Description | Default |
|---|---|---|
|
|
The maximum number of concurrent connections a child server process can handle. |
1 |
|
|
The desired number of server processes for the DSN. |
1 |
|
|
The size in KB of the thread stack for each connection. |
128 KB (32-bit systems) 256KB (64-bit systems) |
Data store attributes are set at data store creation time.The data store attributes are listed in Table 1-1, "Data store attributes" and described in detail in this section.
These attributes can be assigned values only during database creation by the instance administrator.
The data source name uniquely identifies the attributes to a connection. It serves two purposes:
As a unique identifier to the ODBC driver manager (if one is present), allowing it to associate a Data Store Name with a specific ODBC driver.
As one of potentially many name aliases to a single physical database where the name alias has unique attributes associated with it.
The database attributes can apply to either the data source name (connection to a database) or the Data Store Path Name (database).
On Windows, the data source name and all configuration information associated with the data source (including the database path name) are stored in the system registry. This information is used by the ODBC driver manager and by TimesTen.
Required privilege
Only the instance administrator can change the value of this attribute.
Setting
Set Data Source Name as follows:
| Where to set the attribute | How the attribute is represented | Setting |
|---|---|---|
C or Java programs or UNIX ODBC.INI file | DSN | A name that describes the DSN. |
| Windows ODBC Data Source Administrator | Data Source Name field | A name that describes the DSN. |
The database path name uniquely identifies the physical database. It is the full path name of the database and the file name prefix, for example: C:\data\AdminData. This name is not a file name. The actual database file names have suffixes, such as .ds0 and .log0, for example C:\data\AdminData.ds0 and C:\data\AdminData.log0.
You can use environment variables in the specification of the database path and name.
|
Note: You are required to specify the database path and name at database creation time. It cannot be altered after the database has been created. |
Required privilege
Only the instance administrator can change the value of this attribute.
Setting
| Where to set the attribute | How the attribute is represented | Setting |
|---|---|---|
C or Java programs or UNIX ODBC.INI file | DataStore | Full path to the physical database that the data source name references. |
| Windows ODBC Data Source Administrator | Data Store Path + Name field | Full path to the physical database that the data source name references. |
The database character set determines the character set in which data is stored.
|
Note: You are required to specify the database character set at database creation time only. It cannot be altered after the database has been created. If you do not specify a value for this attribute when creating a database, TimesTen returns error message 12701. |
Generally, your database character set should be chosen based on the data requirements. For example: Do you have data in Unicode or is your data in Japanese on UNIX (EUC) or Windows (SJIS)?
You should choose a connection character set that matches your terminal settings or data source. See "ConnectionCharacterSet".
When the database and connection character sets differ, TimesTen performs the data conversion internally based on the connection character set. If the connection and database character sets are the same, TimesTen does not need to convert or interpret the data set. Best performance occurs when connection and database character sets match, since no conversion is required.
To use this attribute you must specify a supported character set. For a list of character set names that can be used as a value for this attribute, see "Supported character sets" below.
There are several things to consider when choosing a character set for your database. For a discussion about these considerations, see "Choosing a database character set" in Oracle TimesTen In-Memory Database Operations Guide.
Required privilege
Only the instance administrator can change the value of this attribute.
Setting
Set DatabaseCharacterSet name as follows:
| Where to set the attribute | How the attribute is represented | Setting |
|---|---|---|
C or Java programs or UNIX ODBC.INI file | DatabaseCharacterSet | Specify the preferred character set. |
| Windows ODBC Data Source Administrator | Database Character Set list | Select the preferred character set from the list provided in the ODBC Data Source Administrator. |
The tables in this section describe the character sets supported in TimesTen.
| Name | Description |
|---|---|
JA16EUC | EUC 24-bit Japanese |
JA16EUCTILDE | The same as JA16EUC except for the way that the wave dash and the tilde are mapped to and from Unicode |
JA16SJIS | Shift-JIS 16-bit Japanese |
JA16SJISTILDE | The same as JA16SJIS except for the way that the wave dash and the tilde are mapped to and from Unicode |
KO16KSC5601 | KSC5601 16-bit Korean |
KO16MSWIN949 | Microsoft Windows Code Page 949 Korean |
TH8TISASCII | Thai Industrial Standard 620-2533 - ASCII 8-bit |
VN8MSWIN1258 | Microsoft Windows Code Page 1258 8-bit Vietnamese |
ZHS16CGB231280 | CGB2312-80 16-bit Simplified Chinese |
ZHS16GBK | GBK 16-bit Simplified Chinese |
ZHS32GB18030 | GB18030-2000 |
ZHT16BIG5 | BIG5 16-bit Traditional Chinese |
ZHT16HKSCS | Microsoft Windows Code Page 950 with Hong Kong Supplementary Character Set HKSCS-2001. Character set conversion to and from Unicode is based on Unicode 3.0. |
ZHT16MSWIN950 | Microsoft Windows Code Page 950 Traditional Chinese |
ZHT32EUC | EUC 32-bit Traditional Chinese |
| Name | Description |
|---|---|
BLT8CP921 | Latvian Standard LVS8-92(1) Windows/UNIX 8-bit Baltic |
BLT8ISO8859P13 | ISO 8859-13 Baltic |
BLT8MSWIN1257 | Microsoft Windows Code Page 1257 8-bit Baltic |
BLT8PC775 | IBM-PC Code Page 775 8-bit Baltic |
CEL8ISO8859P14 | ISO 8859-13 Celtic |
CL8ISO8859P5 | ISO 8859-5 Latin/Cyrillic |
CL8KOI8R | RELCOM Internet Standard 8-bit Latin/Cyrillic |
CL8KOI8U | KOI8 Ukrainian Cyrillic |
CL8MSWIN1251 | Microsoft Windows Code Page 1251 8-bit Latin/Cyrillic |
EE8ISO8859P2 | ISO 8859-2 East European |
EL8ISO8859P7 | ISO 8859-7 Latin/Greek |
ET8MSWIN923 | Microsoft Windows Code Page 923 8-bit Estonian |
EE8MSWIN1250 | Microsoft Windows Code Page 1250 8-bit East European |
EL8MSWIN1253 | Microsoft Windows Code Page 1253 8-bit Latin/Greek |
EL8PC737 | IBM-PC Code Page 737 8-bit Greek/Latin |
EE8PC852 | IBM-PC Code Page 852 8-bit East European |
LT8MSWIN921 | Microsoft Windows Code Page 921 8-bit Lithuanian |
NE8ISO8859P10 | ISO 8859-10 North European |
NEE8ISO8859P4 | ISO 8859-4 North and North-East European |
RU8PC866 | IBM-PC Code Page 866 8-bit Latin/Cyrillic |
SE8ISO8859P3 | ISO 8859-3 South European |
US7ASCII | ASCII 7-bit American |
US8PC437 | IBM-PC Code Page 437 8-bit American |
WE8ISO8859P1 | ISO 8859-1 West European |
WE8ISO8859P15 | ISO 8859-15 West European |
WE8MSWIN1252 | Microsoft Windows Code Page 1252 8-bit West European |
WE8PC850 | IBM-PC Code Page 850 8-bit West European |
WE8PC858 | IBM-PC Code Page 858 8-bit West European |
| Name | Description |
|---|---|
AR8ADOS720 | Arabic MS-DOS 720 Server 8-bit Latin/Arabic |
AR8ASMO8X | ASMO Extended 708 8-bit Latin/Arabic |
AR8ISO8859P6 | ISO 8859-6 Latin/Arabic |
AR8MSWIN1256 | Microsoft Windows Code Page 1256 8-Bit Latin/Arabic |
AZ8ISO8859P9E | ISO 8859-9 Latin Azerbaijani |
IW8ISO8859P8 | ISO 8859-8 Latin/Hebrew |
IW8MSWIN1255 | Microsoft Windows Code Page 1255 8-bit Latin/Hebrew |
TR8MSWIN1254 | Microsoft Windows Code Page 1254 8-bit Turkish |
TR8PC857 | IBM-PC Code Page 857 8-bit Turkish |
WE8ISO8859P9 | ISO 8859-9 West European & Turkish |
| Name | Description |
|---|---|
TIMESTEN8 | TimesTen legacy character semantics |
| Name | Description |
|---|---|
AL16UTF16 | Unicode 4.0 UTF-16 Universal character set. This is the implicit TimesTen national character set. |
AL32UTF8 | Unicode 4.0 UTF-8 Universal character set |
UTF8 | Unicode 3.0 UTF-8 Universal character set, CESU-8 compliant |
Optionally, set this attribute to help you identify the Data Source Name (DSN) and its attributes.
Required privilege
Only the instance administrator can change the value of this attribute.
Setting
Set Description as follows:
| Where to set the attribute | How the attribute is represented | Setting |
|---|---|---|
C or Java programs or UNIX ODBC.INI file | Description | Text description of the Data Source Name. This attribute is optional. |
| Windows ODBC Data Source Administrator | Description field | Text description of the Data Source Name. This attribute is optional. |
The LogDir attribute specifies the directory where database logs reside. Specifying this attribute allows you to place the transaction log files on a different I/O path from the database checkpoint files. This may improve throughput.
You can use environment variables in the specification of the transaction log file path name. For example, you can specify $HOME/AdminDS for the location of the database. See "Using environment variables in database path names" in Oracle TimesTen In-Memory Database Operations Guide for more information.
Required privilege
Only the instance administrator can change the value of this attribute.
Setting
Set LogDir as follows:
| Where to set the attribute | How the attribute is represented | Setting |
|---|---|---|
C or Java programs or UNIX ODBC.INI file | LogDir | Specifies the directory where transaction log files reside. |
| Windows ODBC Data Source Administrator | Transaction Log Directory field | Specifies the directory where transaction log files reside. |
The Preallocate attribute determines whether TimesTen preallocates file system space for the database when the database is created. Setting this attribute ensures that there is sufficient space for the database when the database is saved to the file system.
Using Preallocate=1 in combination with ttRestore or ttRepAdmin -duplicate and a value of PermSize that does not match the value of PermSize of the original database may result in two checkpoint files with different sizes. This has not been shown to have negative effects. However, the issue can be avoided completely either by using the same PermSize as the original database or by setting Preallocate=0.
When a duplicate operation is carried out, the duplicated store has behavior consistent with a Preallocate setting of 0, even if it is set to 1 on the original or duplicated database. The behavior is indicated by the size of the checkpoint files, which is the sum of the size of the data and size of the database header.
The checkpoint files are subsequently allowed to grow to the same size as checkpoint files on the master database (PermSize + database header), but the space is not preallocated. The checkpoint files increase in size as data is added.
The reason for this behavior is that PreAllocate is set at database creation time. It is not a first connection attribute. The duplicate operation is not a database creation operation, so the preallocate attribute is not honored.
Required privilege
Only the instance administrator can change the value of this attribute.
Setting
Set Preallocate as follows:
| Where to set the attribute | How the attribute is represented | Setting |
|---|---|---|
C or Java programs or UNIX ODBC.INI file | Preallocate | 0 (default) - Does not preallocate file system space for database when creating the database.
|
| Windows ODBC Data Source Administrator | Preallocate check box | unchecked (default) - Does not preallocate file system space for database when creating the database.
checked - Preallocates file system space for the database. |
|
Note: reallocating disk space for a large database is very time consuming. |
Enables automatic or user-define track-based parallel replication when used with the ReplicationParallelism attribute. By default, this attribute is set to enable automatic parallel replication.
Both automatic and user-defined parallel replication are available for active standby pair and regular replication schemes. In parallel replication multiple transmitters send from the master to multiple receivers on the subscriber.
All data stores in the replication scheme must use the same setting.
For applications that have very predictable transactional dependencies and do not require that the commit order on the receiver is the same as that on the originating database, you can increase replication throughput by using user-defined parallel replication: you can specify the number of transaction tracks and apply specific transactions to each track. All tracks are read, transmitted and applied in parallel.
For more details on configuring user-defined automatic parallel replication, see the Oracle TimesTen In-Memory Database Replication Guide.
This attribute is also sets parallel propagation for AWT cache groups. By default, this attribute enables parallel propagation of updates to Oracle. To learn more about parallel AWT caching, see "Configuring parallel propagation to Oracle tables" in the Oracle In-Memory Database Cache User's Guide.
Required privilege
Only the instance administrator can change the value of this attribute.
Setting
Set ReplicationApplyOrdering as follows:
| Where to set the attribute | How the attribute is represented | Setting |
|---|---|---|
C or Java programs or UNIX ODBC.INI file | ReplicationApplyOrdering | 0 - Specifies automatic parallel replication. (default)
1 -Specifies user-defined track-based parallel replication. |
| Windows ODBC Data Source Administrator | Replication Apply Ordering check box | unchecked - Specifies automatic parallel replication. (default)
checked - Specifies user-defined track-based parallel replication. |
This attribute specifies the number of tracks, or the number of transmitter/receiver pairs, used for parallel replication.
The default value for this attribute is 1. This value indicates that single-threaded replication occurs. If the value is greater than 1, the LogBufParallelism first connection attribute must be an integral multiple of ReplicationParallelism.
To configure parallel replication, set this attribute to a value from 2 to 32, indicating the number of transmitter/receiver pairs.
Restrictions and things to consider when specifying parallel replication include:
When parallel replication is enabled the Description column of the ttLogHolds built-in procedure displays one row per track per subscriber node.
We recommend setting the value of this attribute to a value no greater than half the value of the LogBufParallelism attribute. If you specify more replication tracks than log buffer threads, some of the replication tracks can remain empty.
Synchronous replication, including TWOSAFE and RETURN RECEIPT replication, is not supported with user-specified parallel replication.
To learn more about parallel replication, see "Configuring parallel replication" in the Oracle TimesTen In-Memory Database Replication Guide.
Required privilege
Only the instance administrator can change the value of this attribute.
Setting
Set ReplicationParallelism as follows:
| Where to set the attribute | How the attribute is represented | Setting |
|---|---|---|
C or Java programs or UNIX ODBC.INI file | ReplicationParallelism | n - A value between 1 and 32, indicating the number of tracks to replicate in parallel. The default is 1, single-threaded replication. |
| Windows ODBC Data Source Administrator | Replication Parallelism field | n - A value between 1 and 32, indicating the number of tracks to replicate in parallel. The default is 1, single-threaded replication. |
Set this attribute to create a temporary database. Temporary databases are not saved to the file system. They may, however, be shared and therefore require a data store path name. A temporary database is deleted when the last connection is closed. See "Database persistence" in Oracle TimesTen In-Memory Database Operations Guide for more information. You cannot assign the Temporary data store attribute to an existing permanent database.
|
Note: You cannot back up or replicate a temporary database. |
Required privilege
Only the instance administrator can change the value of this attribute.
Setting
Set Temporary as follows:
| Where to set the attribute | How the attribute is represented | Setting |
|---|---|---|
C or Java programs or UNIX ODBC.INI file | Temporary | 0 (default) - Creates permanent database.
|
| Windows ODBC Data Source Administrator | Temporary check box | unchecked (default) - Creates permanent database.
checked - Creates temporary database. |
Specifies whether the names and semantics of the data types follow Oracle or TimesTen type rules. TimesTen supports both Oracle and TimesTen data types. The type mode determines what names are used to specify each data type. In some cases, a data type has both an alias name and a fixed type name. In such a situation, you can use either name. The TimesTen type mode is included for backward compatibility. We recommend that you use the default setting, which is Oracle type mode.
When caching Oracle data in TimesTen, TypeMode must be set to 0.
See "Type specifications" in Oracle TimesTen In-Memory Database SQL Reference for a list of data types and their fixed and alias names.
Required privilege
Only the instance administrator can change the value of this attribute.
Setting
| Where to set the attribute | How the attribute is represented | Setting |
|---|---|---|
C or Java programs or UNIX ODBC.INI file | TypeMode | 0 (default) - Oracle type mode.
1 - TimesTen type mode. If no value is specified, either the default type mode or the type mode assigned when the database was created is used. |
| Windows ODBC Data Source Administrator | TypeMode dropdown list | 0 (default) - Oracle type mode.
1 - TimesTen type mode. If no value is specified, either the default type mode or the type mode assigned when the database was created is used. |
First connection attributes are set when a connection is made to an idle database (a database created by the instance administrator which currently has no connections) and persist for that connection and all subsequent connections until the last connection to this database is closed.
First connection attributes are listed in Table 1-2, "First connection attributes" and described in detail in this section.
If you try to connect to the database using attributes that are different from the first connection attribute settings, the new connection may be rejected or the attribute value may be ignored. However, for example, if existing connections have a LogFileSize of one size and a new connection specifies a LogFileSize of another size, TimesTen ignores the new value and returns a warning.
|
Note: Only the instance administrator can change a first connection attribute to a value other than the one currently in effect. To change the value of a first connection attribute, you must first shut down the database and then connect withADMIN privileges. (No privileges are required to change AutoCreate and ForceConnect.) |
If you connect to a database that has the AutoCreate attribute set and the database does not exist yet, the database is created automatically if you supplied a valid existing path. With AutoCreate set, TimesTen creates the database, but not the path to the database. If you attempt to connect to a database that does not exist and the AutoCreate attribute is not set, the connection fails.
Required privilege
No privilege is required to change the value of this attribute.
Setting
Set AutoCreate as follows:
| Where to set the attribute | How the attribute is represented | Setting |
|---|---|---|
C or Java programs or UNIX ODBC.INI file | AutoCreate | 0 - Does not create new database if database does not exist.
|
| Windows ODBC Data Source Administrator | AutoCreate check box | unchecked - Does not create new database if database does not exist.
checked (default) - Creates new database if database does not exist. |
Controls the frequency in seconds that TimesTen performs a background checkpoint. The counter used for the checkpoint condition is reset at the beginning of each checkpoint.
If both CkptFrequency and CkptLogVolume attributes have a value greater than 0, a checkpoint is performed when either of the two conditions becomes true. The values set by the ttCkptConfig built-in procedure replace the values set by these attributes.
In the case that your application attempts to perform a checkpoint operation while a background checkpoint is in process, TimesTen waits until the background checkpoint finishes and then executes the application's checkpoint. To turn off background checkpointing, set CkptFrequency=0 and CkptLogVolume=0.
The value of this attribute is "sticky" as it persists across database loads and unloads unless it is explicitly changed. The default value is only used during database creation. Subsequent first connections default to using the existing value stored in the database. If left unspecified (or empty in the Windows ODBC Data Source Administrator), the stored setting is used. To turn the attribute off, you must explicitly specify a value of 0.
Regardless of the value of this attribute, if a checkpoint fails, TimesTen attempts a checkpoint only once every 10 minutes. If a checkpoint failure occurs due to a lack of file system space, we recommend that you attempt a manual checkpoint as soon as space is available. Once any successful checkpoint occurs, background checkpointing reverts to the configured schedule.
Required privilege
Only the instance administrator can change the value of this attribute.
Setting
| Where to set the attribute | How the attribute is represented | Setting |
|---|---|---|
C or Java programs or UNIX ODBC.INI file | CkptFrequency | Enter a value in seconds for the frequency at which TimesTen should perform a background checkpoint. Default is 600. To specify the default or "existing" value, leave the value empty. A value of 0 means that checkpoint frequency is not considered when scheduling checkpoints. |
| Windows ODBC Data Source Administrator | Ckpt Frequency (secs) field | Enter a value in seconds for the frequency at which TimesTen should perform a background checkpoint. Default is 600. To specify the default or "existing" value, leave the field empty. A value of 0 means that checkpoint frequency is not considered when scheduling checkpoints. |
Controls the amount of data in megabytes that collects in the log between background checkpoints. The counter used for the checkpoint condition is reset at the beginning of each checkpoint.
If both CkptFrequency and CkptLogVolume attributes have a value greater than 0, a checkpoint is performed when either of the two conditions becomes true. The values set by the ttCkptConfig built-in procedure replace the values set by these attributes.
In the case that your application attempts to perform a checkpoint operation while a background checkpoint is in process, TimesTen waits until the background checkpoint finishes and then executes the application's checkpoint. To turn off background checkpointing, set CkptFrequency=0 and CkptLogVolume=0.
The value of this attribute is "sticky" as it persists across database loads and unloads unless it is explicitly changed. The default value is only used during database creation. Subsequent first connections default to using the existing value stored in the database. If left unspecified (or empty in the Windows ODBC Data Source Administrator), the stored setting is used. To turn the attribute off, you must explicitly specify a value of 0.
Required privilege
Only the instance administrator can change the value of this attribute.
Setting
| Where to set the attribute | How the attribute is represented | Setting |
|---|---|---|
C or Java programs or UNIX ODBC.INI file | CkptLogVolume | Specify the amount of data in megabytes that can accumulate in the transaction log file between background checkpoints. The default is 0. To specify the default or "existing" value, leave the value empty. A value of 0 means that log volume is not considered when scheduling checkpoints. |
| Windows ODBC Data Source Administrator | Ckpt LogVolume field | Specify the amount of data in megabytes that can accumulate in the transaction log file between background checkpoints. The default is 0. To specify the default or "existing" value, leave the field empty. A value of 0 means that log volume is not considered when scheduling checkpoints. |
Controls the maximum rate at which data should be written to disk during a checkpoint operation. This may be useful when the writing of checkpoints to disk interferes with other applications.
This rate is used by all background checkpoints and by checkpoints initiated by the ttCkpt and ttCkptBlocking built-in procedures. Foreground checkpoints (checkpoints taken during first connect and last disconnect) do not use this rate. The rate is specified in MB per second.
A value of 0 disables rate limitation. This is the default. The value can also be specified using the ttCkptConfig built-in procedure. The value set by the ttCkptConfig built-in procedure replaces the value set by this attribute.
The value of this attribute is "sticky" as it persists across database loads and unloads unless it is explicitly changed. The default value is only used during database creation. Subsequent first connections default to using the existing value stored in the database. If left unspecified (or empty in the Windows ODBC Data Source Administrator), the stored setting is used. To turn the attribute off, you must explicitly specify a value of 0. For existing databases that are migrated to this release, the value is initialized to 0. To use the current or default value, the attribute value should be left unspecified.
For more details about the benefits of and issues when using CkptRate, see "Setting the checkpoint rate" in Oracle TimesTen In-Memory Database Operations Guide.
Required privilege
Only the instance administrator can change the value of this attribute.
Setting
Set CkptRate as follows:
| Where to set the attribute | How the attribute is represented | Setting |
|---|---|---|
C or Java programs or UNIX ODBC.INI file | CkptRate | Specify the maximum rate in MB per second at which a checkpoint should be written to disk. A value of 0 indicates that the rate should not be limited. This is the default. |
| Windows ODBC Data Source Administrator | CkptRate field | Specify the maximum rate in MB per second at which a checkpoint should be written to disk. A value of 0 indicates that the rate should not be limited. This is the default. |
Indicates the upper bound on the number of user-specified concurrent connections to the database. TimesTen allocates one semaphore for each expected connection. If the number of connections exceeds the value of this attribute, TimesTen returns an error.
The number of current connections to a database can be determined by viewing the output from the ttStatus utility.
A Connections value of 0 or no value indicates that the default number of semaphores should be used. The maximum number of connections allowed is 2047. TimesTen reserves 47 connections for internal use, leaving a maximum of 2000 connections available to applications. As a guideline, set this value to the maximum number of expected application connections plus ten percent.
If you receive an error indicating that the number of connections exceeds the value of this attribute, increase the value until you no longer receive this error.
|
Note: The kernel must be configured with enough semaphores to handle all active databases. For details on setting semaphores for your system, see "Installation prerequisites" in Oracle TimesTen In-Memory Database Installation Guide. |
Required privilege
Only the instance administrator can change the value of this attribute.
Setting
Set Connections as follows:
| Where to set the attribute | How the attribute is represented | Setting |
|---|---|---|
C or Java programs or UNIX ODBC.INI file | Connections | 0 or no value - Indicates that the default value is used.
The default value is the lesser of An integer from |
| Windows ODBC Data Source Administrator | Connections field | 0 or no value - Indicates that the default value is used.
The default value is the lesser of An integer from |
When return receipt replication is used with the NONDURABLE TRANSMIT option, a failed master database is allowed to recover only by restoring its state from a subscriber database using the -duplicate option of the ttRepAdmin utility. In other words, the failed database cannot just come up and have replication bring it up to date because it may lose some transactions that were transmitted to the subscriber but not durably committed locally. The ForceConnect connection attribute overrides this restriction.
Required privilege
No privilege is required to change the value of this attribute.
Setting
Set ForceConnect as follows:
| Where to set the attribute | How the attribute is represented | Setting |
|---|---|---|
C or Java programs or UNIX ODBC.INI file | ForceConnect | 0 (default) - Do not allow connection to failed database if it is not properly restored from the corresponding subscriber database.
|
| Windows ODBC Data Source Administrator | ForceConnect check box | unchecked (default) - Do not allow connection to failed database if it is not properly restored from the corresponding subscriber database.
checked - Allow connection to a failed database even if it is not properly restored from the corresponding subscriber database. |
Determines whether the first connection to the database should proceed if TimesTen recovery encounters a defective log record.
Required privilege
Only the instance administrator can change the value of this attribute.
Setting
Set LogAutoTruncate as follows:
| Where to set the attribute | How the attribute is represented | Setting |
|---|---|---|
C or Java programs or UNIX ODBC.INI file | LogAutoTruncate | 0 - If a defective log record is encountered, terminate recovery and return an error to the connecting application. Checkpoint and transaction log files remain unmodified.
|
| Windows ODBC Data Source Administrator | LogAutoTruncate box | unchecked - If a defective log record is encountered, terminate recovery and return an error to the connecting application. Checkpoint and transaction log files remain unmodified.
checked (default) - If a defective log record is encountered, truncate the log at the defective record's location and continue with recovery. The original transaction log files are moved to a directory called |
The LogBufMB attribute specifies the size of the internal transaction log buffer in megabytes. The default log buffer size is 64 megabytes.
If you change the value of LogBufMB, you also may need to change the value of LogBufParallelism to satisfy the constraint that LogBufMB/LogBufParallelism >=8.
Required privilege
Only the instance administrator can change the value of this attribute.
Setting
Set LogBufMB as follows:
| Where to set the attribute | How the attribute is represented | Setting |
|---|---|---|
C or Java programs or UNIX ODBC.INI file | LogBufMB | n - Size of log buffer in megabytes.
If not set and the database exists, the existing value stored in the database is used.If not set and the database is being created, the default value of 64 is used. On 32-bit systems, the maximum log file is 1042 MB (1 GB). On 64-bit systems, the maximum value is 65,536 MB (64 GB). |
| Windows ODBC Data Source Administrator | Log Buffer Size (MB) field | Size of log buffer, in megabytes.
If not set and the database exists, the existi ng value stored in the database is used.If not set and the database is being created, the default value of 64 is used. On 32-bit systems, the maximum log file is 1042 MB (1 GB). On 64-bit systems, the maximum value is 65,536 MB (64 GB). |
The LogBufParallelism attribute specifies the number of transaction log buffer strands to which TimesTen writes log files before the log is written to disk, allowing for improved log performance. Each buffer has its own insertion latch. Records are inserted in any of the strands. The log flusher gathers records from all strands and writes them to the log files.
The maximum number of strands is 64. The default is 4.
If you change the value of LogBufParallelism, you also may need to change the value of LogBufMB to satisfy the constraint that LogBufMB/LogBufParallelism >=8.
Required privilege
Only the instance administrator can change the value of this attribute.
Setting
Set LogBufParallelism as follows:
| Where to set the attribute | How the attribute is represented | Setting |
|---|---|---|
C or Java programs or UNIX ODBC.INI file | LogBufParallelism | An integer value between 1 and 64. Default is 4. |
| Windows ODBC Data Source Administrator | Log Buffer Parallelism field | An integer value between 1 and 64. Default is 4. |
The LogFileSize attribute specifies the maximum size of transaction log files in megabytes. The minimum value is 8 MB. The default value is 64 MB. If you specify a size smaller than 8 MB, TimesTen returns an error message. Before TimesTen release 11.2.1.4, the minimum size was 1MB. If you created your database in a previous release of TimesTen and specified a log file size of less than 8 MB, you must increase the value assigned to this attribute to avoid an error.
Actual transaction log file sizes may be slightly smaller or larger than LogFileSize because log records cannot span transaction log files.
A value of zero indicates that either the default transaction log file size should be used if the database does not exist, or that the transaction log file size in effect for the most recent connection should be used if the database does exist.
Required privilege
Only the instance administrator can change the value of this attribute.
Setting
Set LogFileSize as follows:
| Where to set the attribute | How the attribute is represented | Setting |
|---|---|---|
C or Java programs or UNIX ODBC.INI file | LogFileSize | n - Size of transaction log file in megabytes. Default is 64 when the database is created and 0 (current size in effect) on subsequent connections. The minimum size is 8 MB.
On 32-bit systems, the maximum log file is 1042 MB (1 GB). On 64-bit systems, the maximum value is 65,536 MB (64 GB). |
| Windows ODBC Data Source Administrator | Log files Size (MB) field | Size of transaction log file in megabytes. Default is 64 when the database is created and 0 (current size in effect) on subsequent connections. The minimum size is 8 MB.
On 32-bit systems, the maximum log file is 1042 MB (1 GB). On 64-bit systems, the maximum value is 65,536 MB (64 GB). |
Controls the method used by TimesTen to write and sync log data to transaction log files. The overall throughput of a system can be significantly affected by the value of this attribute, especially if the application chooses to commit most transactions durably.
As a general rule, use the value 2 if most of your transactions commit durably and use the value 1 otherwise.
For best results, however, experiment with both values using a typical workload for your application and platform. Although application performance may be affected by this attribute, transaction durability is not affected. Changing the value of this attribute does not affect transaction durability in any way.
Required privilege
Only the instance administrator can change the value of this attribute.
Setting
Set LogFlushMethod as follows:
| Where to set the attribute | How the attribute is represented | Setting |
|---|---|---|
C or Java programs or UNIX ODBC.INI file | LogFlushMethod | 0 - Write data to the transaction log files using the previously used value.
|
| Windows ODBC Data Source Administrator | Log Flush Method dropdown list | 0 - Write data to the transaction log files using the previously used value.
|
See also
If the LogPurge attribute is set, TimesTen automatically removes transaction log files when they have been written to both checkpoint files and there are no transactions that still need the transaction log files' contents. The first time checkpoint is called, the contents of the transaction log files are written to one of the checkpoint files. When checkpoint is called the second time, TimesTen writes the contents of the transaction log files to the other checkpoint file.
TimesTen purges the transaction log files if all these conditions are met:
The contents of the transaction log files have been written to both checkpoint files.
The transaction log files are not pending incremental backup.
If replication is being used, the transaction log files have been replicated to all subscribers.
If XLA is being used, all XLA bookmarks have advanced beyond the transaction log files.
The transaction log files are not being used by any distributed transactions using the XA interface.
If this attribute is set to 0 or unchecked, unneeded transaction log files are appended with the.arch suffix. Applications can then delete the files.
See also MatchLogOpts.
Required privilege
Only the instance administrator can change the value of this attribute.
Setting
Set LogPurge as follows:
On Solaris, Linux and Windows 64-bit systems, TimesTen allows applications that connect to a shared database to specify whether the real memory should be locked while the database is being loaded into memory or while the store is in memory. If the physical memory used for the database is locked, the operating system's virtual memory sub-system cannot borrow that memory for other uses. No part of the database is ever paged out but this could lead to memory shortages in a system that is under configured with RAM. While memory locking can improve database load performance, it may impede other applications on the same computer.
On AIX the MemoryLock attribute is not implemented. The shared memory segment is locked when you use large pages, on AIX. You can lock the shared segment by using large pages. The Oracle TimesTen In-Memory Database Installation Guide contains more details about large pages.
The PL/SQL shared memory segment is not subject to MemoryLock.
Required privilege
Only the instance administrator can change the value of this attribute.
On Linux systems, set the groupname in the MemLock setting to be the same as the instance administrator in the /etc/security/limits.conf file. Set the value of MemLock to be at least as large as the TimesTen database shared memory segment.
On Solaris systems, the instance administrator must be root to set MemoryLock to 1 or 2. Setting MemoryLock to 3 or 4 enables use of Solaris "intimate shared memory".
Setting
Set MemoryLock as follows.
| Where to set the attribute | How the attribute is represented | Setting |
|---|---|---|
C or Java programs or UNIX ODBC.INI file | MemoryLock | 0 (default) - Does not lock memory.
|
| Windows ODBC Data Source Administrator | Memory Lock field | 0 (default) - Does not lock memory.
|
If the Overwrite attribute is set and there is an existing database with the same database path name as the new database, TimesTen destroys the existing database and creates a new empty database, as long as the existing database is not in use. If the Overwrite attribute is set and there is not a database with the specified database path name, TimesTen only creates a new database if the AutoCreate attribute is also set (see "AutoCreate"). Overwrite is ignored if AutoCreate is set to 0. Applications should use caution when specifying the Overwrite =1 attribute.
Required privilege
Only the instance administrator can change the value of this attribute. If a user other than an instance administrator attempts to connect to a database with Overwrite=1, TimesTen returns an error.
Setting
Set Overwrite as follows:
| Where to set the attribute | How the attribute is represented | Setting |
|---|---|---|
C or Java programs or UNIX ODBC.INI file | Overwrite | 0 (default) - Does not overwrite existing database with the same path name.
|
| Windows ODBC Data Source Administrator | Not available | Not available |
Indicates the size in MB of the permanent memory region for the database. You may increase PermSize at first connect but not decrease it. TimesTen returns a warning if you attempt to decrease the permanent memory region size. If the database does not exist, a PermSize value of 0 or no value indicates that the default size should be used. Default size is 32 MB. For an existing database, a value of 0 or no value indicates that the existing size should not be changed.
Once you have created a database, you can make the permanent partition larger, but not smaller. See "Specifying the size of a database" in Oracle TimesTen In-Memory Database Operations Guide.
The ttMigrate and ttDestroy utilities can also be used to change the Permanent Data Size, when appropriate.
Required privilege
Only the instance administrator can change the value of this attribute.
Setting
Set PermSize as follows:
| Where to set the attribute | How the attribute is represented | Setting |
|---|---|---|
C or Java programs or UNIX ODBC.INI file | PermSize | n - Size of permanent partition of the database, in megabytes; default is 32 MB for both 32-bit systems and 64-bit systems. Minimum size is 32 MB. |
| Windows ODBC Data Source Administrator | Permanent Data Size field | n - Size of permanent partition of the database, in megabytes; default is 32 MB for both 32-bit systems and 64-bit systems. Minimum size is 32 MB. |
This attribute controls the number of threads used to apply changes on the active master database to the standby master database in an active standby pair replication scheme. The default is 1. You can also set this attribute on one or more read-only subscribers in an active standby pair replication scheme to increase replication throughput from the standby master database to the subscribers.
By default, a receiver thread in the replication agent applies the changes to the standby master database. When this attribute is set to 2, an additional thread applies the changes. Databases must be hosted on systems that have two cores (or hardware threads) or more to take advantage of setting this attribute to 2.
If you set this attribute to 2 on the standby master database, you should also set it to 2 on the active master database to maintain increased throughput if there is a failover.
Required privilege
Only the instance administrator can change the value of this attribute.
Setting
Set ReceiverThreads as follows:
| Where to set the attribute | How the attribute is represented | Setting |
|---|---|---|
C or Java programs or UNIX ODBC.INI file | ReceiverThreads | n - The number of threads used to apply changes from the active master database to the standby master database. You can also set this attribute on one or more read-only subscribers in an active standby pair replication scheme to increase replication throughput from the standby master database to the subscribers.
The possible values are 1 and 2. Default is 1. |
| Windows ODBC Data Source Administrator | ReceiverThreads field | n - The number of threads used to apply changes from the active master database to the standby master database. You can also set this attribute on one or more read-only subscribers in an active standby pair replication scheme to increase replication throughput from the standby master database to the subscribers.
The possible values are 1 and 2. Default is 1. |
The RecoveryThreads attribute determines the number of threads used to rebuild indexes during recovery.
If RecoveryThreads=1, during recovery, indexes that must be rebuilt are done serially. If you have enough processors available to work on index rebuilds on your computer, setting this attribute to a number greater than 1 can improve recovery performance. The performance improvement occurs only if different processors can work on different indexes. There is no parallelism in index rebuild within the same index.
The value of RecoveryThreads can be any value up to the number of CPUs available on your system.
The default is 1 when the database is created. Upon subsequent connections, if the database must be recovered and RecoveryThreads is unspecified or has a value of 0, then TimesTen uses the previous setting for this attribute.
Required privilege
Only the instance administrator can change the value of this attribute.
Setting
Set RecoveryThreads as follows:
| Where to set the attribute | How the attribute is represented | Setting |
|---|---|---|
C or Java programs or UNIX ODBC.INI file | RecoveryThreads | n - The number of threads to use when rebuilding indexes during recovery. Default is 1 when the database is created and 0 on subsequent connections. |
| Windows ODBC Data Source Administrator | RecoveryThreads field | n - The number of threads to use when rebuilding indexes during recovery. Default is 1 when the database is created and 0 on subsequent connections. |
Notes
For a progress report on the recovery process, see the rebuild messages in the support log.
Set the number of threads low enough to leave sufficient resources on the server for other services/processes.
TempSize indicates the total amount of memory in MB allocated to the temporary region.
TempSize has no predefined value. If left unspecified, its value is determined from PermSize as follows:
If PermSize is less than 64MB, TempSize = 32 MB + ceiling(PermSize / 4 MB).
Otherwise, Tempsize = 40 MB + c eiling(PermSize / 8 MB).
TimesTen rounds the value up to the nearest MB.
If specified, TimesTen always honors the TempSize value. Since the temporary data partition is recreated each time a database is loaded, the TempSize attribute may be increased or decreased each time a database is loaded. For an existing database, a value of 0 or no value indicates that the existing size should not be changed. The minimum TempSize is 32 MB.
Required privilege
Only the instance administrator can change the value of this attribute.
Setting
Set TempSize as follows:
| Where to set the attribute | How the attribute is represented | Setting |
|---|---|---|
C or Java programs or UNIX ODBC.INI file | TempSize | n - Size of temporary partition of the database, in megabytes. Minimum size is 32 MB on all platforms. |
| Windows ODBC Data Source Administrator | Temporary Data Size field | n - Size of temporary partition of the database, in megabytes. Minimum size is 32 MB on all platforms. |
General connection attributes are set by each connection and persist for the duration of the connection. General connection attributes are listed in Table 1-3, "General connection attributes" and described in detail in this section.
This attribute is also available as a Client connection attribute.
This attribute allows you to attach a symbolic name to any database connection. Connection names are unique within a process.
The symbolic name is used to help identify the connection in various TimesTen administrative utilities, such as ttIsql, ttXactAdmin and ttStatus. This can be particularly useful with processes that make multiple connections to the database, as is typical with multithreaded applications or in the identification of remote clients.
The value of this attribute is intended to be dynamically defined at connection time using the connection string. The default value is the connecting executable file name. It can also be defined statically in the DSN definition. Values used for ConnectionName should follow SQL identifier syntax rules.
Required privilege
No privilege is required to change the value of this attribute.
Setting
Set ConnectionName as follows:
| Where to set the attribute | How the attribute is represented | Setting |
|---|---|---|
C or Java programs or UNIX ODBC.INI file | ConnectionName | Enter a string up to 30 characters that represents the name of the connection.If the specified or default connection name is in use, TimesTen assigns the name conn, where n is an integer greater than 0 to make the name unique.If not specified, the connecting process name. |
| Windows ODBC Data Source Administrator | Connection field | Enter a string up to 30 characters that represents the name of the connection.If the specified or default connection name is in use, TimesTen assigns the name conn, where n is an integer greater than 0 to make the name unique.If not specified, the connecting process name. |
This attribute controls transactional commit behavior in relation to DDL (Data Definition Language) statements
You can set it to the traditional TimesTen behavior or to the Oracle database behavior.
Traditionally, in TimesTen databases, DDL statements are executed as part of the current transaction and are committed or rolled back along with the rest of the transaction.
The Oracle database issues an implicit COMMIT before and after any DDL statement.
|
Note: IfPLSQL support is enabled, the DDLCommitBehavior must be the Oracle transactional commit behavior (value 0).
Do not use DDL statements in XA transactions. |
DDL statements include:
CREATE, ALTER or DROP of any database object (including tables, views, users procedures, indexes, etc.)
TRUNCATE
GRANT and REVOKE
Required privilege
No privilege is required to change the value of this attribute.
Setting
Set DDLCommitBehavior as follows:
| Where to set the attribute | How the attribute is represented | Setting |
|---|---|---|
C or Java programs or UNIX ODBC.INI file | DDLCommitBehavior | 0 (default) - Oracle database style behavior. An implicit transaction commit is done before the execution of the DDL statement and a durable commit is done after execution of DDL statements.
|
| Windows ODBC Data Source Administrator | DDLCommitBehavior field | 0 (default) - Oracle database style behavior. An implicit transaction commit is done before the execution of the DDL statement and a durable commit is done after execution of DDL statements.
|
Examples
Example 1-1 TimesTen commit behavior
AUTOCOMMIT OFF;
CREATE TABLE t1 (c1 Varchar2(10));
COMMIT;
INSERT INTO t1 VALUES('some data');
1 row inserted.
CREATE TABLE t2 (c1 INTEGER);
ROLLBACK;
SELECT * FROM t1;
0 rows found.
SELECT * FROM t2;
2206: Table ttuser.t2 not found
The command failed.
INSERT INTO t1 VALUES('more data');
1 row inserted.
CREATE TABLE t1 (c1 VARCHAR2(10));
2207: Table t1 already exists
The command failed.
ROLLBACK;
SELECT * FROM t1;
0 rows found.
Example 1-2 Oracle commit behavior
This example shows Oracle behavior (DDLCommitBehavior=0). In this example, the INSERTs and the creation of table t2 are committed. The second insert ('more data') is committed even though the DDL statement triggering the commit (duplicate create of table t1) fails:
-- implicit commit here
Command> CREATE TABLE t1 (c1 varchar2(10));
Table created.
-- implicit commit here
Command> COMMIT;
Commit complete.
Command> INSERT INTO t1 VALUES('some data');
1 row created.
-- implicit commit here
Command> CREATE TABLE t2 (c1 INTEGER);
Table created.
-- implicit commit here
SQL> ROLLBACK;
Rollback complete.
Command> SELECT * FROM t1;
C1
----------
some data
Command> SELECT * FROM t2;
no rows selected
Command> INSERT INTO t1 VALUES('more data');
1 row created.
-- implicit commit here
Command> CREATE TABLE t1 (c1 VARCHAR2(10));
CREATE TABLE t1 (c1 VARCHAR2(10))
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
-- implicit rollback
Command> ROLLBACK;
Rollback complete.
Command> SELECT * FROM t1;
C1
----------
some data
more data
Determines whether a table is included in an active standby pair replication scheme at table creation time when the DDLReplicationLevel connection attribute is set to 2.
The value may be modified by an ALTER SESSION statement, for example:
ALTER SESSION SET DDL_Replication_Action='EXCLUDE';
Values set by ALTER SESSION override the value set by this attribute.
Replication of DDL operations has these restrictions:
CREATE TABLE AS SELECT statements are not replicated.
The CREATE INDEX statement is replicated only when the index is created on an empty table.
For examples of altering an active standby pair, see "Altering an Active Standby Pair" in the Oracle TimesTen In-Memory Database Replication Guide.
When DDLCommitBehavior=0 (the default), DDL operations are automatically committed. When RETURN TWOSAFE has been specified, errors and timeouts may occur as described in "RETURN TWOSAFE" on in the Oracle TimesTen In-Memory Database Replication Guide. If a RETURN TWOSAFE timeout occurs, the DDL transaction is committed locally regardless of the LOCAL COMMIT ACTION that has been specified.
To learn more about replicating DDL, see "Making DDL changes in an active standby pair" in the Oracle TimesTen In-Memory Database Replication Guide.
Required privilege
ADMIN privilege is required if the value of this attribute is INCLUDE.
Setting
Set DDLReplicationAction as follows:
| Where to set the attribute | How the attribute is represented | Setting |
|---|---|---|
C or Java programs or UNIX ODBC.INI file | DDLReplicationAction | INCLUDE (default) - When a table is created, it is automatically added to the active standby pair scheme.
|
| Windows ODBC Data Source Administrator | DDLReplicationAction field | INCLUDE (default) - When a table is created, it is automatically added to the active standby pair scheme.
|
Enables replication of the following DDL (Data Definition Language) statements in an active standby replication scheme:
DDL statements include:
CREATE or DROP INDEX
CREATE or DROP SYNONYM
CREATE or DROP TABLE (including global temporary tables but not CREATE TABLE AS SELECT)
The value of this attribute may be modified by an ALTER SESSION statement, for example:
ALTER SESSION SET DDL_Replication_Level=1;
Values set by ALTER SESSION override the value set by this attribute.
Replication of DDL operations has these restrictions:
CREATE TABLE AS SELECT statements are not replicated.
The CREATE INDEX statement is replicated only when the index is created on an empty table.
To control whether a table is included in an active standby pair replication scheme at the time of table creation, use the DDLReplicationAction connection attribute.
For examples of altering an active standby pair, see "Altering an Active Standby Pair" in the Oracle TimesTen In-Memory Database Replication Guide.
To learn more about replicating DDL, see "Making DDL changes in an active standby pair" in the Oracle TimesTen In-Memory Database Replication Guide.
Required privilege
No privilege is required to change the value of this attribute.
Setting
Set DDLReplicationLevel as follows:
| Where to set the attribute | How the attribute is represented | Setting |
|---|---|---|
C or Java programs or UNIX ODBC.INI file | DDLReplicationLevel | 1 - Replicates ALTER TABLE ADD or DROP COLUMN to the standby database. Does not replicate CREATE and DROP operations for tables, indexes or synonyms to the standby database.
|
| Windows ODBC Data Source Administrator | DDL Replication Level field | 1 - Replicates ALTER TABLE ADD or DROP COLUMN to the standby database. Does not replicate CREATE and DROP operations for tables, indexes or synonyms to the standby database.
|
Allows an application to configure the level of diagnostics information generated by TimesTen for the connection. TimesTen diagnostics messages are warnings whose numbers lie within the range 20000 through 29999. Diagnostics connection attribute values are integers.
Required privilege
No privilege is required to change the value of this attribute.
Setting
Set Diagnostics as follows:
| Where to set the attribute | How the attribute is represented | Setting |
|---|---|---|
C or Java programs or UNIX ODBC.INI file | Diagnostics | 0 - No diagnostics messages are generated.
|
| Windows ODBC Data Source Administrator | Diagnostics field | 0 - No diagnostics messages are generated.
|
This attribute determines whether applications use traditional TimesTen parameter binding for duplicate occurrences of a parameter in a SQL statement or Oracle-style parameter binding.
Traditionally, in TimesTen, multiple instances of the same parameter name in a SQL statement are considered to be multiple occurrences of the same parameter. When assigning parameter numbers to parameters, TimesTen assigns parameter numbers only to the first occurrence of each parameter name. The second and subsequent occurrences of a given name do not get their own parameter numbers. In this case, A TimesTen application binds a value for every unique parameter in a SQL statement. It cannot bind different values for different occurrences of the same parameter name nor can it leave any parameters or parameter occurrences unbound.In Oracle Database, multiple instances of the same parameter name in a SQL statement are considered to be different parameters. When assigning parameter numbers, Oracle assigns a number to each parameter occurrence without regard to name duplication. An Oracle application, at a minimum, binds a value for the first occurrence of each parameter name. For the subsequent occurrences of a given parameter, the application can either leave the parameter occurrence unbound or it can bind a different value for the occurrence.
For more details on parameter binding, see Oracle TimesTen In-Memory Database SQL Reference.
Required privilege
No privilege is required to change the value of this attribute.
Setting
Set DuplicateBindMode as follows:
Notes
When using Oracle Call Interface, DuplicateBindMode must be set to 0.
When PLSQL is set to 1 and DuplicateBindMode is set to 1, PL/SQL programs may not issue SQL statements containing duplicate parameter names.
By default, DurableCommits is set to 0. This means that a log record is written to the file system when a transaction is committed, but the log record is not immediately written to disk. This reduces transaction execution time at the risk of losing some committed transactions if a failure occurs. When DurableCommits is set to 1, a log record is written to disk when the transaction is committed.
A connection can also call the ttDurableCommit built-in procedure to do durable commits explicitly on selected transactions. A call to ttDurableCommit flushes the log buffer to disk. The log buffer is shared among all connections and contains log records from transactions of all connections.
Log records are continually copied from the file system to disk. You can use LogFlushMethod to control when the file system is synchronized with the disk.
Required privilege
No privilege is required to change the value of this attribute.
Setting
Set DurableCommits as follows:
See also
By default, TimesTen uses read committed isolation. The Isolation attribute specifies the initial transaction isolation level for the connection. For a description of the isolation levels, see "Concurrency control through isolation and locking" in Oracle TimesTen In-Memory Database Operations Guide.
The value may be modified by an ALTER SESSION statement. For details, see Oracle TimesTen In-Memory Database SQL Reference.
CREATE CACHE GROUP, ALTER CACHE GROUP and DROP CACHE GROUP statements are not supported in serializable isolation mode.
If the passthrough or the propagate IMDB Cache feature is used, the TimesTen isolation level setting is inherited by the Oracle session. TimesTen serializable mode is mapped to Oracle's serializable mode. TimesTen read committed mode is mapped to Oracle's read committed mode. For more details on the passthrough attribute, see "PassThrough".
With PassThrough set to 3, you must use an ALTER SESSION statement to permanently modify the isolation level on the Oracle connection. For example on a connection to the DSN repdb1_1121:
Call ttIsql and connect to the DSN with PassThrough level 3:
% ttIsql; Command> connect "dsn=repdb1_1121;passtrhough=3"; Connection successful:. . .PassThrough=3; TypeMode=0; <default setting Autocommit=1>
Turn off AutoCommit:
Command> autocommit=0;
Temporarily change the PassThrough level to 0:
Command> passthrough=0;
Alter the isolation level to serializable:
Command> prepare 1 ALTER SESSION SET ISOLATION_LEVEL=serializable; Command> commit; Command> exec=1;
Required privilege
No privilege is required to change the value of this attribute.
Setting
Set Isolation as follows:
| Where to set the attribute | How the attribute is represented | Setting |
|---|---|---|
C or Java programs or UNIX ODBC.INI file | Isolation | 0 - Connects to database in serializable isolation mode.
|
| Windows ODBC Data Source Administrator | Isolation dropdown list | 0 - Connects to database in serializable isolation mode.
|
By default, TimesTen enables row-level locking for maximum concurrency. With row-level locking, transactions usually obtain locks on the individual rows that they access, although a transaction may obtain a lock on an entire table if TimesTen determines that doing so would result in better performance. Row-level locking is the best choice for most applications, as it provides the finest granularity of concurrency control. To use row-level locking, applications must set the LockLevel connection attribute to 0 (the default value). To cache Oracle tables, you must set row-level locking. In order to CREATE, DROP, or ALTER a user, you can only use row-level locking and thus, the Locklevel must be set to 0 before you can perform any of these operations.
To give every transaction in this connection exclusive access to the database, you can enable database-level locking by setting the LockLevel attribute to 1. Doing so may improve performance for some applications.
A connection can change the desired lock level at any time by calling the ttLockLevel built-in procedure. Connections can also wait for unavailable locks by calling the ttLockWait built-in procedure. Different connections can coexist with different levels of locking, but the presence of even one connection doing database-level locking leads to loss of concurrency. To display a list of all locks on a particular database you can use the ttXactAdmin utility.
When using PL/SQL in your applications, set LockLevel=0 and selectively change to database level locking for specific transactions that require that level of locking by using the ttLockLevel built-in procedure.
Required privilege
ADMIN privilege is required if the value of this attribute is 1.
Setting
Set LockLevel as follows:
Allows an application to configure the lock wait interval for the connection. The lock wait interval is the number of seconds to wait for a lock when there is contention on it. Sub-second LockWait values significant to tenths of a second can be specified using decimal format for the number of seconds. For example:
LockWait = 0.1
results in a lock wait of one tenth of a second.
LockWait may be set to any value between 0 and 1,000,000 inclusive to a precision of tenths of a second. The default is 10 seconds:
LockWait = 10.0
Actual lock wait response time is imprecise and may be exceeded by up to one tenth of a second, due to the scheduling of the agent that detects timeouts. This imprecision does not apply to zero second timeouts, which are always reported immediately.
The number of connections to a database can impact the time needed to resolve lock contentions. If you anticipate a large number of connections to the database, increase the lock wait interval.
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.
A connection can change the lock wait interval at any time by calling the ttLockWait built-in procedure.
To display a list of all locks on a particular database you can use the TimesTen utility ttXactAdmin.
Required privilege
No privilege is required to change the value of this attribute.
Setting
Set LockWait as follows:
| Where to set the attribute | How the attribute is represented | Setting |
|---|---|---|
C or Java programs or UNIX ODBC.INI file | LockWait | s - Seconds to wait for locking conflict resolution before timing out. Default is 10 seconds. |
| Windows ODBC Data Source Administrator | LockWait field | s - Seconds to wait for locking conflict resolution before timing out. Default is 10 seconds. |
The first connection to a database determines whether the transaction log files are purged. Any subsequent connection must specify the same value for the LogPurge attribute or TimesTen generates an error. If a connection does not know the current state of the LogPurge attribute, MatchLogOpts can be set so that the logging attributes match.
Required privilege
No privilege is required to change the value of this attribute.
Setting
Set MatchLogOpts as follows:
| Where to set the attribute | How the attribute is represented | Setting |
|---|---|---|
C or Java programs or UNIX ODBC.INI file | MatchLogOpts | 0 (default) - Value of LogPurge is used.
|
| Windows ODBC Data Source Administrator | Match Log Opts check box | unchecked (default) - Value of LogPurge is used.
checked - Value of |
Indicates the threshold percentage at which TimesTen issues out-of-memory warnings for the permanent partition of the database's memory. The database is considered no longer out of permanent memory if it falls 10% below this threshold. An application must call the built-in procedure ttWarnOnLowMemory to receive out-of-memory warnings. The threshold also applies to SNMP warnings. See "ttWarnOnLowMemory". Also see "Diagnostics through SNMP Traps" in Oracle TimesTen In-Memory Database Error Messages and SNMP Traps.
Required privilege
No privilege is required to change the value of this attribute.
Setting
Set PermWarnThreshold as follows:
| Where to set the attribute | How the attribute is represented | Setting |
|---|---|---|
C or Java programs or UNIX ODBC.INI file | PermWarnThreshold | p - Percentage at which warning should be issued. Default is 90% |
| Windows ODBC Data Source Administrator | Low Memory Warning Thresholds for Permanent Data field | p - Percentage at which warning should be issued. Default is 90%. |
When multiple connections execute the same command, they access common command structures controlled by a single command lock. To avoid sharing their commands and possibly placing contention on the lock, you can use PrivateCommands. This gives you better scaling at the cost of increased temporary space usage.
By default, the PrivateCommands is turned off and commands are shared.
Required privilege
No privilege is required to change the value of this attribute.
Setting
Set PrivateCommands as follows:
| Where to set the attribute | How the attribute is represented | Setting |
|---|---|---|
C or Java programs or UNIX ODBC.INI file | PrivateCommands | 0 (default) - Commands are shared with other connections.
|
| Windows ODBC Data Source Administrator | Private Commands field | 0 (default) - Commands are shared with other connections.
|
Notes
If there are many copies of the same command, all of them are invalidated by a DDL or statistics change. This means that reprepare of these multiple copies takes longer when PrivateCommands = 1. With more commands DDL execution can take slightly longer.
When using the PrivateCommands attribute, memory consumption can increase considerably if the attribute is not used cautiously. For example, if PrivateCommands=1 for an application that has 100 connections with 100 commands, there are 10,000 commands in the system: one private command for each connection.
The PWDCrypt contains an encrypted version of the corresponding PWD value. The value for PWD is stored in clear text, which does not allow special characters, in the .odbc.ini file on UNIX and in the Windows Registry on Windows. Any users who have access to the .odbc.ini file or Windows Registry can view the value for this attribute. The PWDCrypt attribute allows special characters, is case sensitive and contains the value of the encrypted password.
For security reasons, the PWDCrypt attribute should only be placed in User DSNs or user private ODBCINI files. The presence of the PWDCrypt in System DSNs allows any user to use the PWDCrypt value to connect to TimesTen, even though they have no knowledge of the cleartext password.
To generate the value for this attribute, run the ttuser utility.
Required privilege
No privilege is required to change the value of this attribute.
Notes
If PWD and PWDCrypt are both supplied, the PWD value is used. See "UID and PWD".
The PWD is not stored anywhere in the TimesTen system.
Setting
Set PWDCrypt as follows:
| Where to set the attribute | How the attribute is represented | Setting |
|---|---|---|
C or Java programs or UNIX ODBC.INI file | PWDCrypt | Enter the value generated by the ttuser utility. |
| Windows ODBC Data Source Administrator | PWDCrypt field | Enter the value generated by the ttuser utility. |
Use this attribute to write a warning to the support log and throw an SNMP trap when the execution time of a SQL statement exceeds the specified value. For queries executed by the replication agent, see Oracle TimesTen In-Memory Database Replication Guide. You cannot set a query threshold for a SQL statement that is executed by the cache agent. The value of QueryThreshold applies to all connections. It applies to all SQL statements except those executed by the replication agent or the cache agent.
The value of this attribute can be any integer equal to or greater than 0. The default value is 0. A value of 0 indicates that no warning is issued. The unit is seconds.
Required privilege
No privilege is required to change the value of this attribute.
Setting
Set QueryThreshold as follows:
| Where to set the attribute | How the attribute is represented | Setting |
|---|---|---|
C or Java programs or UNIX ODBC.INI file | QueryThreshold | A non-negative integer. Default is 0 and indicates that TimesTen does not return a warning. |
| Windows ODBC Data Source Administrator | QueryThreshold (secs) field | A non-negative integer. Default is 0 and indicates that TimesTen does not return a warning. |
For user defined replication, assigns a connection to a replication track. All transactions issued by the connection are assigned to this track, unless the track is altered.
To start user-specified parallel replication you must set a value for the ReplicationParallelism attribute, specifying the number of replication tracks to be applied in parallel. You must also set ReplicationApplyOrdering to 1.
The TTREP.REPPEERS system table Track_ID column shows the track associated with the connection.
You can use the ALTER SESSION SQL statement to assign or change the value of this attribute within a session.
Required privilege
No privilege is required to change the value of this attribute.
Setting
Set ReplicationTrack as follows:
| Where to set the attribute | How the attribute is represented | Setting |
|---|---|---|
C or Java programs or UNIX ODBC.INI file | ReplicationTrack | n - An integer between 1 and 64 that specifies the replication track to be used by transactions issued by the connection. |
| Windows ODBC Data Source Administrator | Replication Track field | n - An integer between 1 and 64 that specifies the replication track to be used by transactions issued by the connection. |
Use this attribute to specify the time limit in seconds within which the database should execute SQL statements.
The value of SQLQueryTimeout can be any integer equal to or greater than 0. The default value is 0. A value of 0 indicates that the query does not time out.
This attribute does not stop IMDB Cache operations that are being processed on Oracle. This includes passthrough statements, flushing, manual loading, manual refreshing, synchronous writethrough, and propagating.
Required privilege
No privilege is required to change the value of this attribute.
Setting
Set SQLQueryTimeout as follows:
| Where to set the attribute | How the attribute is represented | Setting |
|---|---|---|
C or Java programs or UNIX ODBC.INI file | SQLQueryTimeout | n - Time limit in seconds for which the database should execute SQL queries. |
| Windows ODBC Data Source Administrator | QueryTimeout (secs) field | n - Time limit in seconds for which the database should execute SQL queries. |
Indicates the threshold percentage at which TimesTen issues out-of- memory warnings for the temporary partition of the database's memory. The database is considered no longer out of temporary memory if it falls 10% below this threshold. An application must call the built-in procedure ttWarnOnLowMemory to receive out-of-memory warnings. The threshold also applies to SNMP warnings. See "ttWarnOnLowMemory". Also see "Diagnostics through SNMP Traps" in Oracle TimesTen In-Memory Database Error Messages and SNMP Traps
Required privilege
No privilege is required to change the value of this attribute.
Setting
Set TempWarnThreshold as follows:
| Where to set the attribute | How the attribute is represented | Setting |
|---|---|---|
C or Java programs or UNIX ODBC.INI file | TempWarnThreshold | p - Percentage at which warning should be issued. Default is 90%. |
| Windows ODBC Data Source Administrator | Low Memory Warning Thresholds for Temporary Data field | p - Percentage at which warning should be issued. Default is 90% |
A user ID and password must be provided by a user who is identified internally to TimesTen. Alternatively, an encrypted password can be supplied using the PWDCrypt attribute. Some TimesTen operations prompt for the UID and PWD of the user performing the operation.
For client/server applications, specify UID and PWD either in the Client DSN configuration or in the connection string. The UID and PWD values specified in a connection string take precedence over the values specified in the Client DSN configuration.
When caching Oracle tables, PWD specifies the TimesTen password while OraclePWD specifies the Oracle password.
Required privilege
No privilege is required to change the values of these attributes.
Setting
| Where to set the attribute | How the attribute is represented | Setting |
|---|---|---|
C or Java programs or UNIX ODBC.INI file | UID | Character string specifying the user ID. |
C or Java programs or UNIX ODBC.INI file | PWD | Character string specifying the password that corresponds to the user ID. |
| Windows ODBC Data Source Administrator | User ID field | Character string specifying the user ID. |
When an application requests a connection to a TimesTen database and the connection is not possible (perhaps during concurrent loading/recovery of a database), TimesTen normally waits for completion of the conflicting connection. In some cases, it can take some time for an application to connect to a database. If the WaitForConnect attribute is off and the database is not immediately accessible, TimesTen returns immediately an error. For a description of the error, look for the error message number in "Warnings and Errors" in Oracle TimesTen In-Memory Database Error Messages and SNMP Traps.
Required privilege
No privilege is required to change the value of this attribute.
Setting
Set WaitForConnect as follows:
| Where to set the attribute | How the attribute is represented | Setting |
|---|---|---|
C or Java programs or UNIX ODBC.INI file | WaitForConnect | 0 - Does not wait if connection to database fails.
|
| Windows ODBC Data Source Administrator | Wait For Connect check box | unchecked - Does not wait if connection to database fails.
checked (default) - Waits until connection to database is possible. |
NLS connection attributes are set by each connection and persist for the duration of the connection. These attributes control the globalization behaviors of the database. NLS general connection attributes are listed Table 1-4, "NLS general connection attributes" and described in detail in this section.
You can use the ALTER SESSION statement to change NLS parameters to override the values that are assigned to these attributes at connection time.
ConnectionCharacterSet is also available as a Client connection attribute.
This attribute specifies the character encoding for the connection, which may be different from the database character set. This can be useful when you have multiple connections to a database and one or more of those connections requires a character set that differs from that specified in the database.
The connection character set determines the character set in which data is displayed or presented.
Generally, you should choose a connection character set that matches your terminal settings or data source. Your database character set should be chosen based on the data requirements. For example: Do you have data in Unicode or is your data in Japanese on UNIX (EUC) or Windows (SJIS)?
When the database and connection character sets differ, TimesTen performs data conversion internally based on the connection character set. If the connection and database character sets are the same, TimesTen does not need to convert or interpret the data set. Best performance occurs when connection and database character sets match, since no conversion is required.
Parameters and SQL query text sent to the connect should be in the connection character set. Results and error messages returned by the connection are returned in the connection character set.
Character set conversions are not supported for the TIMESTEN8 character set. A ConnectionCharacterSet value of TIMESTEN8 results in an error if the value assigned to the DatabaseCharacterSet is not TIMESTEN8.
This attribute accepts the same values used for the DatabaseCharacterSet. For a list of character set names that can be used as a value for this attribute, see "Supported character sets".
Required privilege
No privilege is required to change the value of this attribute.
Setting
Set ConnectionCharacterSet as follows:
| Where to set the attribute | How the attribute is represented | Setting |
|---|---|---|
C or Java programs or UNIX ODBC.INI file | ConnectionCharacterSet | The default value for ConnectionCharacterSet is US7ASCII, except when the database character set is TIMESTEN8. |
| Windows ODBC Data Source Administrator | Connection CharacterSet list | The default value for ConnectionCharacterSet is US7ASCII, except when the database character set is TIMESTEN8. |
The NLS_LENGTH_SEMANTICS attribute is used to set the default length semantics configuration. Length semantics determines how the length of a character string is determined. The length can be treated as a sequence of characters or a sequence of bytes.
Required privilege
No privilege is required to change the value of this attribute.
Setting
Set NLS_LENGTH_SEMANTICS as follows:
| Where to set the attribute | How the attribute is represented | Setting |
|---|---|---|
C or Java programs or UNIX ODBC.INI file | NLS_LENGTH_ SEMANTICS | Specify either BYTE (default) or CHAR. |
| Windows ODBC Data Source Administrator | NLS_LENGTH_ SEMANTICS list | Select either BYTE (default) or CHAR. |
The NLS_NCHAR_CONV_EXCP attribute determines whether an error is reported when there is data loss during an implicit or explicit character type conversion between NCHAR/NVARCHAR2 data and CHAR/VARCHAR2 data. A replacement character is substituted for characters that cannot be converted.
Implicit and explicit conversions between CHAR and NCHAR are supported, except when using the TIMESTEN8 character set.
Required privilege
No privilege is required to change the value of this attribute.
Setting
Set NLS_NCHAR_CONV_EXCP as follows:
| Where to set the attribute | How the attribute is represented | Setting |
|---|---|---|
C or Java programs or UNIX ODBC.INI file | NLS_NCHAR_CONV_EXCP | 0 (default) - Errors are not reported when there is a data loss during character type conversion.
1 - Errors are reported when there is a data loss during character type conversion. |
| Windows ODBC Data Source Administrator | NLS_NCHAR_ CONV_EXCP checkbox | unchecked (default) - Error messages are not reported when there is a data loss during character type conversion.
checked - Error messages are reported when there is a data loss during character type conversion. |
The NLS_SORT attribute indicates which collating sequence to use for linguistic comparisons. It accepts the values listed in "Supported Linguistic Sorts." All these values can be modified to do case-insensitive sorts by appending _CI to the value. To perform accent-insensitive and case-insensitive sorts, append _AI to the value.
For materialized views and cache groups, TimesTen recommends that you explicitly specify the collating sequence using the NLSSORT SQL function rather than using this attribute in the connection string or DSN definition.
Operations involving character comparisons support linguistic case-sensitive collating sequences. Case-insensitive sorts may affect DISTINCT value interpretation.
NLS_SORT may affect many operations. The supported operations that are sensitive to collating sequence are:
MIN, MAX
BETWEEN
=, <>, >, >=, <, <=
DISTINCT
CASE
GROUP BY
HAVING
ORDER BY
IN
LIKE
Only BINARY sort is supported with the TIMESTEN8 character set.
NLS_SORT settings other than BINARY may have significant performance impact on character operations.
|
Note: Primary key indexes are always based on theBINARY collating sequence. Use of non-BINARY NLS_SORT equality searches cannot use the primary key index |
Required privilege
No privilege is required to change the value of this attribute.
Setting
Set NLS_SORT as follows:
| Where to set the attribute | How the attribute is represented | Setting |
|---|---|---|
C or Java programs or UNIX ODBC.INI file | NLS_SORT | Specify the linguistic sort sequence or BINARY (default). |
| Windows ODBC Data Source Administrator | NLS_SORT dropdown list | Specify the linguistic sort sequence or BINARY (default). |
The tables in this section list the supported values for the NLS_SORT general connection attribute and the NLS_SORT SQL function.
| Basic name | Extended name |
|---|---|
ARABIC | - |
ARABIC_MATCH | - |
ARABIC_ABJ_SORT | - |
ARABIC_ABJ_MATCH | - |
ASCII7 | - |
AZERBAIJANI | XAZERBAIJANI |
BENGALI | - |
BIG5 | - |
BINARY | - |
BULGARIAN | - |
CANADIAN FRENCH | - |
CATALAN | XCATALAN |
CROATIAN | XCROATIAN |
CZECH | XCZECH |
CZECH_PUNCTUATION | XCZECH_PUNCTUATION |
DANISH | XDANISH |
DUTCH | XDUTCH |
EBCDIC | - |
EEC_EURO | - |
EEC_EUROPA3 | - |
ESTONIAN | - |
FINNISH | - |
FRENCH | XFRENCH |
GERMAN | XGERMAN |
GERMAN_DIN | XGERMAN_DIN |
GBK | - |
GREEK | - |
HEBREW | - |
HKSCS | - |
HUNGARIAN | XHUNGARIAN |
ICELANDIC | - |
INDONESIAN | - |
ITALIAN | - |
LATIN | - |
LATVIAN | - |
LITHUANIAN | - |
MALAY | - |
NORWEGIAN | - |
POLISH | - |
PUNCTUATION | XPUNCTUATION |
ROMANIAN | - |
RUSSIAN | - |
SLOVAK | XSLOVAK |
SLOVENIAN | XSLOVENIAN |
SPANISH | XSPANISH |
SWEDISH | - |
SWISS | XSWISS |
THAI_DICTIONARY | - |
TURKISH | XTURKISH |
UKRAINIAN | - |
UNICODE_BINARY | - |
VIETNAMESE | - |
WEST_EUROPEAN | XWEST_EUROPEAN |
| Sort name | Description |
|---|---|
CANADIAN_M | Canadian French sort supports reverse secondary, special expanding characters. |
DANISH_M | Danish sort supports sorting uppercase characters before lowercase characters. |
FRENCH_M | French sort supports reverse sort for secondary. |
GENERIC_M | Generic sorting order which is based on ISO14651 and Unicode canonical equivalence rules but excluding compatible equivalence rules. |
JAPANESE_M | Japanese sort supports SJIS character set order and EUC characters which are not included in SJIS. |
KOREAN_M | Korean sort Hangul characters are based on Unicode binary order. Hanja characters based on pronunciation order. All Hangul characters are before Hanja characters. |
SPANISH_M | Traditional Spanish sort supports special contracting characters. |
THAI_M | Thai sort supports swap characters for some vowels and consonants. |
SCHINESE_RADICAL_M | Simplified Chinese sort is based on radical as primary order and number of strokes order as secondary order. |
SCHINESE_STROKE_M | Simplified Chinese sort uses number of strokes as primary order and radical as secondary order. |
SCHINESE_PINYIN_M | Simplified Chinese Pinyin sorting order. |
TCHINESE_RADICAL_M | Traditional Chinese sort based on radical as primary order and number of strokes order as secondary order. |
TCHINESE_STROKE_M | Traditional Chinese sort uses number of strokes as primary order and radical as secondary order. It supports supplementary characters. |
PL/SQL connection attributes are set by each connection and persist for the duration of the connection. These attributes control the behaviors of the database. PL/SQL first connection attributes are listed Table 1-5, "PL/SQL first connection attributes" and described in detail in this section.
This attribute determines whether PL/SQL is configured for the database.
Specifying PLSQL=1 enables PL/SQL use in the database. Specifying PLSQL=0 disables PL/SQL use in the database.
On platforms where PL/SQL is supported, and in TimesTen installations where PL/SQL support was enabled at installation time, the default is PLSQL=1. In other environments the default is PLSQL=0.
PL/SQL may be enabled when the database is initially created, or at any first connect afterwards. However, once PL/SQL support is enabled in a database, you cannot disable it later.
Configuring PL/SQL support in a database results in the creation of several "built-in" PL/SQL packages and procedures that are defined in Oracle TimesTen In-Memory Database PL/SQL Developer's Guide.
Some things to be aware of when setting this attribute are:
If an application connects to a database that has PL/SQL enabled, and the application or odbc.ini file specifies PLSQL=0, TimesTen returns a warning.
If an application connects to a database that does not have PL/SQL enabled, and the application or odbc.ini file specifies PLSQL=1, what happens depends on whether this is a first connect or a subsequent one. At first connect, the database is configured to support PL/SQL. Otherwise, TimesTen returns an error.
If PLSQL=0, all PL/SQL first and general connection attributes are ignored.
If PLSQL=0, any attempt to change the value of a PL/SQL general connection attributes returns an error.
If PLSQL=1, we recommend setting LockLevel=0 for the connection. If database level locking is enabled, some PL/SQL internal functions cannot be performed. You can use the ttLockLevel built-in procedure to selectively change to database-level locking only for those specific transactions that require it.
If PL/SQL support is enabled, the DDLCommitBehavior must be the Oracle transactional commit behavior (value 0).
Required privilege
Only the instance administrator can change the value of this attribute.
Setting
| Where to set the attribute | How the attribute is represented | Setting |
|---|---|---|
C or Java programs or UNIX ODBC.INI file | PLSQL | 0 - Indicates that PL/SQL is not enabled for the database.
|
| Windows ODBC Data Source Administrator | PL/SQL Enabled checkbox | checked - Enables PL/SQL for the database.
unchecked - Indicates that PL/SQL is not enabled for the database. |
Use of PL/SQL requires a shared memory segment. This shared memory contains recently-executed PL/SQL code, shared package state, and metadata associated with the operation of PL/SQL. This shared memory segment is separate from the one containing the TimesTen database.
This attribute determines the virtual address at which this shared memory segment is loaded into each process that uses the TimesTen "direct" drivers. This memory address must be identical in each process using TimesTen. You must specify the value as a hexadecimal address.
If PL/SQL use is enabled (PLSQL=1) and you have not specified a value for PLSQL_MEMORY_ADDRESS, TimesTen uses a platform-dependent default value.
The default values for each platform are designed to:
Maximize the amount of virtual space for your TimesTen database and for your applications.
Minimize the fragmentation of the virtual address space.
Avoid conflicts with other uses of virtual address apace.
The platform specific default memory addresses are:
| Operating system | Address |
|---|---|
| 32-bit Linux | 10000000 |
| 64-bit Linux | 0000007fa0000000 |
| 32-bit AIX | c0000000 |
| 64-bit AIX | 06ffffff00000000 |
| 32-bit Solaris | 10000000 |
| 64-bit Solaris | ffffff0000000000 |
| 32-bit Windows | 5B8C0000 |
Some things to consider when setting this attribute are:
If applications simultaneously connect to multiple TimesTen databases in direct mode, then each database must use a different value for value for PLSQL_MEMORY_ADDRESS.
The value of this attribute is stored persistently by TimesTen. This allows the attribute value to be properly specified in situations when the database is loaded automatically by TimesTen. For example, the database is automatically loaded if RamPolicy for the database is set to 1.
If the PL/SQL shared memory cannot be mapped at the appropriate address, TimesTen returns an error and the connection to the database fails.
The memory segment size is determined by the value of PLSQL_MEMORY_SIZE.
Required privilege
Only the instance administrator can change the value of this attribute.
Setting
Set PLSQL_MEMORY_ADDRESS as follows:
| Where to set the attribute | How the attribute is represented | Setting |
|---|---|---|
C or Java programs or UNIX ODBC.INI file | PLSQL_MEMORY_ADDRESS | A hexidecimal value that indicates the memory address for PL/SQL process. |
| Windows ODBC Data Source Administrator | PL/SQL Memory Address field | A hexidecimal value that indicates the memory address for PL/SQL process. |
Use of PL/SQL requires a shared memory segment. This attribute determines the size in megabytes of the shared memory segment used by PL/SQL, which is shared by all connections.
This shared memory contains recently-executed PL/SQL code, the shared package state, and metadata associated with the operation of PL/SQL. This shared memory segment is separate from the one containing the TimesTen database.
Some things to consider when setting this attribute are:
The value of this attribute is stored persistently by TimesTen. This allows the attribute value to be properly specified in situations when the database is loaded automatically by TimesTen. For example, the database is automatically loaded if RamPolicy for the database is set to 1.
The default memory size is 32 MB on UNIX systems and 23 MB on Windows 32-bit systems. The minimum size is 2MB, if PLSQL=1. For most PL/SQL users, the default memory size should be an adequate amount of memory. For databases that make extensive use of PL/SQL, specify a larger memory size. If the memory space is exhausted, ORA-4031 errors may occur during PL/SQL execution.
The address of the memory segment is determined by the value of PLSQL_MEMORY_ADDRESS.
For more information on how to calculate the size for PLSQL_MEMORY_SIZE, see "Calculate shared memory size for PL/SQL runtime" in Oracle TimesTen In-Memory Database Operations Guide.
Required privilege
Only the instance administrator can change the value of this attribute.
Setting
Set PLSQL_MEMORY_SIZE as follows:
| Where to set the attribute | How the attribute is represented | Setting |
|---|---|---|
C or Java programs or UNIX ODBC.INI file | PLSQL_MEMORY_SIZE | Specify a positive integer greater than 2 representing the size of the shared memory segment in megabytes. The default size is 32 MB. |
| Windows ODBC Data Source Administrator | PL/SQL Memory Size field | Specify a positive integer greater than 2 representing the size of the shared memory segment in megabytes. The default size is 23 MB. |
PL/SQL general connection attributes are set by each connection and persist for the duration of the connection. These attributes control the behaviors of the database. PL/SQL general connection attributes are listed in Table 1-6, "PL/SQL general connection attributes" and described in detail in this section.
You can use the ALTER SESSION statement to change PL/SQL parameters to override the values that are assigned to the PL/SQL general connection attributes at connection time. For details, see Oracle TimesTen In-Memory Database SQL Reference.
PLSCOPE_SETTINGS controls whether the PL/SQL compiler generates cross-reference information. Either all or no cross-references are generated.
Some things to consider when setting this attribute are:
The PLSCOPE_SETTINGS connection attribute determines the initial value of this attribute within a session. The value may be modified by an ALTER SESSION statement. If the attribute is specified in an ALTER SESSION statement in a database where PLSQL=0, an error is returned.
If this attribute is specified in a connection string or in the odbc.ini file and the application is connecting to a database where PLSQL=0, no error or warning results.
|
Note: For more details on this attribute, see Oracle TimesTen In-Memory Database PL/SQL Developer's Guide. |
Required privilege
No privilege is required to change the value of this attribute.
Setting
Set PLSCOPE_SETTINGS as follows:
| Where to set the attribute | How the attribute is represented | Setting |
|---|---|---|
C or Java programs or UNIX ODBC.INI file | PLSCOPE_SETTINGS | IDENTIFIERS:NONE (default)
|
| Windows ODBC Data Source Administrator | PLScope settings pulldown list | IDENTIFIERS:NONE (default)
|
This attribute can be used to set directives to control conditional compilation of PL/SQL units, which allows you to customize the functionality of a PL/SQL program depending on conditions that are checked. This is especially useful when applications may be deployed to multiple database environments. Possible uses include activating debugging or tracing features, or basing functionality on the version of the database.
Use this format:
PLSQL_CCFLAGS = 'v1:c1,v2:c2,...,vn:cn'
v1 has the form of an unquoted PL/SQL identifier. It is unrestricted and can be a reserved word or a keyword. The text is insensitive to case. Each one is known as a flag or flag name. Each vi can occur multiple times in the string, each occurrence can have a different flag value, and the flag values can be of different kinds.
c1 is one of the following: a PL/SQL boolean literal, a PLS_INTEGER literal, or the literal NULL. The text is insensitive to case. Each one is known as a flag value and corresponds to a flag name.
Required privilege
No privilege is required to change the value of this attribute.
Setting
| Where to set the attribute | How the attribute is represented | Setting |
|---|---|---|
C or Java programs or UNIX ODBC.INI file | PLSQL_CCFLAGS | 'A string literal with this format:
Default: null |
| Windows ODBC Data Source Administrator | PL/SQL CCFlags field | 'A string literal with this format:
Default: null |
You can use the ALTER SESSION SQL statement to change this attribute within a session.
This attribute specifies the maximum amount of process heap memory in megabytes that PL/SQL can use for the connection in which it is set.
Some things to consider when setting this attribute are:
PL/SQL does not allocate this memory until or unless it is needed. Many PL/SQL programs require only a small amount of memory. How you write your application can determine memory requirements. For example, using large VARRAYs in PL/SQL code can require a lot of memory.
If you attempt to allocate more memory than allowed, TimesTen returns an error.
The value can be modified with the ALTER SESSION statement.
See Oracle TimesTen In-Memory Database PL/SQL Developer's Guide for more information.
Required privilege
No privilege is required to change the value of this attribute.
Setting
Set PLSQL_CONN_MEM_LIMIT as follows:
| Where to set the attribute | How the attribute is represented | Setting |
|---|---|---|
C or Java programs or UNIX ODBC.INI file | PLSQL_CONN_MEM_LIMIT | An integer value in MB. Default value is 100MB. |
| Windows ODBC Data Source Administrator | PL/SQL Connection Memory Limit field | An integer value in MB. Default value is 100MB. |
This attribute specifies the optimization level to be used to compile PL/SQL library units. The higher the setting of this parameter, the more effort the compiler makes to optimize PL/SQL library units.
Some things to consider when setting this attribute are:
The PLSQL_OPTIMIZE_LEVEL connection attribute determines the initial value of this attribute within a session. The value may be modified by an ALTER SESSION statement. If the attribute is specified in an ALTER SESSION statement in a database where PLSQL=0, an error is returned.
If this attribute is specified in a connection string or in the odbc.ini file and the application is connecting to a database where PLSQL=0, no error or warning results.
|
Note: For more details on this attribute, see Oracle TimesTen In-Memory Database PL/SQL Developer's Guide. |
Required privilege
No privilege is required to change the value of this attribute.
Setting
Set PLSQL_OPTIMIZE_LEVEL as follows:
| Where to set the attribute | How the attribute is represented | Setting |
|---|---|---|
C or Java programs or UNIX ODBC.INI file | PLSQL_OPTIMIZE_LEVEL | For details on the settings for this attribute, see Oracle TimesTen In-Memory Database PL/SQL Developer's Guide.
The default value is 2. |
| Windows ODBC Data Source Administrator | PL/SQL Optimization Level pulldown list | For details on the settings for this attribute, see Oracle TimesTen In-Memory Database PL/SQL Developer's Guide.
The default value is 2. |
This attribute controls how long (in seconds) PL/SQL program units, including PL/SQL procedures, anonymous blocks and functions, are allowed to run before being automatically terminated.
This value may be modified with an ALTER SESSION statement. If this value is modified through ALTER SESSION, the new value impacts any PL/SQL program units that are currently running.
|
Note: If you are using PL/SQL, set thePLSQL_TIIMEOUT value to a value that is at least 5 seconds less than TTC_TIMEOUT. |
Required privilege
No privilege is required to change the value of this attribute.
Setting
| Where to set the attribute | How the attribute is represented | Setting |
|---|---|---|
C or Java programs or UNIX ODBC.INI file | PLSQL_TIMEOUT | A positive integer representing the number of seconds for the timeout value.
A value of 0 means that there is no timeout limit. The default value is 30 seconds. |
| Windows ODBC Data Source Administrator | PL/SQL Timeout field | A positive integer representing the number of seconds for the timeout value.
A value of 0 means that there is no timeout limit. The default value is 30 seconds. |
See Also
IMDB Cache first connection attributes are used only when you are using the IMDB Cache product. IMDB Cache first connection attributes are listed in Table 1-7, "IMDB Cache first connection attributes" and described in detail in this section.
Determines whether PL/SQL execution method or SQL array execution method is used for Asynchronous Writethrough propagation to apply changes to the Oracle server.
By default, Asynchronous Writethrough (AWT) uses PL/SQL execution method, CacheAWTMethod=1, AWT bundles all pending operations into a single PL/SQL collection that is sent to the Oracle server to be executed. This method can improve AWT throughput when there are mixed transactions and network latency between TimesTen and the Oracle server.CacheAWTMethod=0
The SQL array execution to apply changes within TimesTen to the Oracle database works well when the same type of operation is repeated. For example, array execution is very efficient when a user does an update that affects several rows of the table. Updates are grouped together and sent to the Oracle server in one batch.
PL/SQL execution method transparently falls back to array execution mode temporarily when it encounters one of the following:
A statement that is over 32761 bytes in length.
A statement that references a column of type BINARY FLOAT, BINARY DOUBLE and VARCHAR of length greater than 4000 bytes.
Specify the SQL execution method, CacheAWTMethod=0, if any AWT cache group contains a VARBINARY column.
The SYSTEMSTATS table contains information about the number of times the execution method temporarily falls back to SQL array execution.
|
Note: Use the same AWT execution method on all TimesTen nodes in any active standby pair replication scheme. |
Required privilege
Only the instance administrator can change the value of this attribute.
Setting
Set CacheAWTMethod as follows:
| Where to set the attribute | How the attribute is represented | Setting |
|---|---|---|
C or Java programs or UNIX ODBC.INI file | CacheAWTMethod | 0 - Use SQL array execution method.
|
| Windows ODBC Data Source Administrator | Cache AWT Method field | 0 - Use SQL array execution method.
|
IMDB Cache connection attributes are used only when you are using the IMDB Cache product. IMDB Cache data store attributes are listed and described in detail in this section.
CacheAwtParallelism indicates the number of threads that should be used to apply changes to the Oracle database. This attribute has a relationship to ReplicationParallelism and ReplicationApplyOrdering. The default is 1.
If you do not set this attribute or if you set it to the default value of 1, the number of threads that apply changes to Oracle is twice the setting for ReplicationParallelism. In this case, ReplicationParallelism can only be set to 16.
If both ReplicationParallelism and CacheAwtParallelism attributes are set, the value set in CacheAwtParallelism configures the number of threads used for parallel propagation. The setting for CacheAwtParallelism determines the number of apply threads for parallel propagation and the setting for ReplicationParallelism determines the number of threads for parallel replication.
To learn more about parallel AWT caching, see "Configuring parallel propagation to Oracle tables" in the Oracle In-Memory Database Cache User's Guide.
Required privilege
Only the instance administrator can change the value of this attribute.
Setting
Set CacheAWTParallelism as follows:
| Where to set the attribute | How the attribute is represented | Setting |
|---|---|---|
C or Java programs or UNIX ODBC.INI file | CacheAWTParallelism | n - An integer between 1 and 31 that indicates the number of threads that should be used to apply changes to Oracle database. The default is 1. |
| Windows ODBC Data Source Administrator | Cache AWT Parallelism field | n - An integer between 1 and 31 that indicates the number of threads that should be used to apply changes to Oracle database. The default is 1. |
Determines whether cache grid is enabled or disabled. The TimesTen database must be a member of a cache grid before you can create cache groups. The default is 1 (enabled).
Required privilege
Only the instance administrator can change the value of this attribute.
Setting
Set CacheGridEnable as follows:
| Where to set the attribute | How the attribute is represented | Setting |
|---|---|---|
C or Java programs or UNIX ODBC.INI file | CacheGridEnable | 0 - Cache groups can be defined outside of a cache grid.
|
| Windows ODBC Data Source Administrator | Cache Grid Enable check box | unchecked - Cache groups can be defined outside of a cache grid.
checked (default) - All cache groups in the database must be defined as members of a cache grid. |
Specifies the number of seconds that an application waits for a message response from a remote member in a cache grid.
The maximum wait time includes the value of this attribute added to the value of LockWait connection attribute.
For more information on caching Oracle data in a TimesTen cache grid, see Oracle In-Memory Database Cache User's Guide.
Required privilege
No privilege is required to change the value of this attribute.
Setting
Set CacheGridMsgWait as follows:
| Where to set the attribute | How the attribute is represented | Setting |
|---|---|---|
C or Java programs or UNIX ODBC.INI file | CacheGridMsgWait | Set to the number of seconds that TimesTen should wait for a cache grid message from a remote member. The default is 60 seconds. |
| Windows ODBC Data Source Administrator | Cache Grid Message Wait field | Set to the number of seconds that TimesTen should wait for a cache grid message from a remote member. The default is 60 seconds. |
IMDB Cache general connection attributes are used only when you are using the IMDB Cache product. IMDB Cache general connection attributes are listed in Table 1-9, "IMDB Cache general connection attributes" and described in detail in this section.
This attribute enables or disables dynamic load of Oracle data to a TimesTen dynamic cache group. By default, dynamic load of Oracle data is enabled.
To enable or disable dynamic load at the statement level and temporarily override the setting of this attribute, set the DynamicLoadEnable optimizer flag with the ttOptSetFlag built-in procedure.
|
Note: The value of this attribute overrides the dynamic load behavior of all dynamic cache groups for the current connection to the database. |
Required privilege
No privilege is required to change the value of this attribute.
Setting
Set DynamicLoadEnable as follows:
| Where to set the attribute | How the attribute is represented | Setting |
|---|---|---|
C or Java programs or UNIX ODBC.INI file | DynamicLoadEnable | 0 - Disables dynamic load of Oracle data to TimesTen dynamic cache groups for the current connection.
|
| Windows ODBC Data Source Administrator | Dynamic Load Enable field | 0 - Disables dynamic load of Oracle data to TimesTen dynamic cache groups for the current connection.
|
This attribute controls what happens when an application executes a SQL operation against a dynamic cache group and the SQL operation cannot use dynamic load.
With a value of 0, the SQL operation executes against whatever data is in the TimesTen cache tables and returns a result based on that data with no error indicated.
With a value of 1, any statement that cannot use dynamic load (even if it does not need dynamic load) fails with an error indicating that it is not dynamic load-compliant.
For more information on caching Oracle data in a TimesTen cache group, see Oracle In-Memory Database Cache User's Guide.
|
Note: To override the value of this attribute at the statement level, set theDynamicLoadErrorMode optimizer flag with the ttOptSetFlag built-in procedure. |
Required privilege
No privilege is required to change the value of this attribute.
Setting
Set DynamicLoadErrorMode as follows:
| Where to set the attribute | How the attribute is represented | Setting |
|---|---|---|
C or Java programs or UNIX ODBC.INI file | DynamicLoadErrorMode | 0 (default) - Statements execute against the cached data with no error.
|
| Windows ODBC Data Source Administrator | DynamicLoadErrorMode field | 0 (default) - Statements execute against the cached data with no error.
|
The OracleNetServiceName attribute is used with the IMDB Cache.
This attribute identifies the Service Name for the Oracle instance
To cache Oracle tables and enable communication with Oracle, you must specify an Oracle Service Name.
Required privilege
No privilege is required to change the value of this attribute.
Setting
Set OracleNetServiceName as follows:
| Where to set the attribute | How the attribute is represented | Setting |
|---|---|---|
C or Java programs or UNIX ODBC.INI file | OracleNetServiceName | Character string specifying the Oracle Service Name that is to be used as the Oracle ID. |
| Windows ODBC Data Source Administrator | OracleNetServiceName field | Character string specifying the Oracle Service Name that is to be used as the Oracle ID. |
This attribute is used with IMDB Cache. It identifies the password for the user specified by UID to connect to the Oracle database to perform cache operations.
No privilege is required to set the value of this attribute.
Setting
This attribute must be set in the connection string. On Linux, suppose you have defined the following odbc.ini file:
[myDSN] Datastore=/data/myDSN PermSize=128 DatabaseCharacterSet=AL32UTF8 ConnectionCharacterSet=AL32UTF8
Set OraclePWD for user ttuser by connecting to myDSN as follows:
% ttisql Copyright (c) 1996-2011, Oracle. All rights reserved. Type ? or "help" for help, type "exit" to quit ttIsql. Command> connect "dsn=myDSN;OraclePWD=mypwd"; Connection successful: DSN=beta4;UID=ttuser;DataStore=/data/myDSN;DatabaseCharacterSet=AL32UTF8; ConnectionCharacterSet=AL32UTF8;PermSize=128;TypeMode=0; (Default setting AutoCommit=1) Command>
On Windows, set OraclePWD in the connection string in the same way that it is set on Linux.
See also
This attribute is used with the IMDB Cache.
It specifies which SQL statements are executed only in the cache database and which SQL statements are passed through to the Oracle database. For more information about the IMDB Cache, see Oracle In-Memory Database Cache User's Guide and "CREATE CACHE GROUP" in Oracle TimesTen In-Memory Database SQL Reference.
The execution of a prepared PassThrough command assumes that the schema of dependent objects in the Oracle database has not changed since the prepare. If the schema has changed the PassThrough command may cause unexpected results from the Oracle database.
When passing SQL statements through to Oracle, use only TimesTen supported data types in column definitions. If the specified data type is not supported in TimesTen, the passthrough statement fails.
For information on changing the isolation level on the Oracle connection, when using this attribute, see "Isolation".
Required privilege
No privilege is required to change the value of this attribute.
Setting
Set PassThrough as follows.
| Where to set the attribute | How the attribute is represented | Setting |
|---|---|---|
C or Java programs or UNIX ODBC.INI file | PassThrough | 0 (default) - SQL statements are executed only on TimesTen.
|
| Windows ODBC Data Source Administrator | PassThrough List | 0 (default) - SQL statements are executed only on TimesTen.
|
Restrictions
Certain restrictions must be considered when using the passthrough feature. They include:
If the PassThrough attribute is set so that a query must be executed in the Oracle database, the query is sent to the Oracle database without any changes. If the query uses a synonym for a table in a cache group, then a synonym with the same name must be defined for the corresponding Oracle table for the query to be successful.
In the case that a SQL statement that uses TimesTen only syntax is passed through to the Oracle database, TimesTen returns an error message that indicates the syntax is not supported in Oracle.
Execution of a prepared passthrough command assumes that the schema of dependent objects in the Oracle database have not changed after the prepare. If the schema has changed, unexpected results can occur.
TimesTen does not include a cache invalidation feature. TimesTen does not verify that the cached tables are up to date. When a query is syntactically correct in TimesTen and the cache contains all the tables referenced in the query, the query is executed in TimesTen regardless of whether the cache is up to date.
The passthrough of Oracle INSERT, UPDATE and DELETE operations depends on the setting of the PassThrough attribute as described in the table above. IMDB Cache cannot detect INSERT, UPDATE and DELETE operations that are hidden in a trigger or stored procedure. Therefore, TimesTen cannot enforce the passthrough rule on hidden operations.
You cannot pass PL/SQL blocks through to the Oracle database.
The effects of a passthrough INSERT, UPDATE or DELETE operation on a read-only cache group are only seen after the transaction is committed and after the next autorefresh operation is completed.
There is no mechanism to detect or block updates on an Oracle table that is cached in a TimesTen synchronous writethrough cache group. Whether the updates are made by statements passed through the cache or from other Oracle applications, the changes are never reflected in IMDB Cache.
Oracle Call Interface (OCI) does not support a mechanism to describe the binding type of the input parameters. Ensure that your application supplies the correct SQL types for passthrough statements. The ODBC driver converts the C and SQL types and presents the converted data and the SQL type code to TimesTen. TimesTen presents the information to OCI. The length of the input binding values is restricted to 4000 for LONG and LONG RAW types.
At all passthrough levels, passthrough execution of DDL statements does not result in commits on the TimesTen side.
A transaction that contains operations that are replicated with RETURN TWOSAFE cannot have a PassThrough setting greater than 0. If PassThrough is greater than 0, an error is returned and the transaction must be rolled back.
When PassThrough is set to 0, 1, or 2, the following behavior occurs when a dynamic load condition exists:
A dynamic load can occur for a SELECT operation on cache tables in any dynamic cache group type.
A dynamic load for an INSERT, UPDATE or DELETE operation can only occur on cached tables with dynamic asynchronous or synchronous writethrough cache groups.
This attribute allows you to enable or disable the installation of Transparent Application Failover (TAF) and Fast Application Notification (FAN) callbacks when using Oracle Real Application Clusters (Oracle RAC) with IMDB Cache.
For more information about IMDB Cache, see Oracle In-Memory Database Cache User's Guide and "CREATE CACHE GROUP" in Oracle TimesTen In-Memory Database SQL Reference.
Required privilege
No privilege is required to change the value of this attribute.
Setting
| Where to set the attribute | How the attribute is represented | Setting |
|---|---|---|
C or Java programs or UNIX ODBC.INI file | RACCallback | 0 - Do not install TAF and FAN callbacks.
|
| Windows ODBC Data Source Administrator | RACCallback check box | unchecked - Do not install TAF and FAN callbacks.
checked (default) - Install the TAF and FAN callbacks. |
TimesTen Client connection attributes are used only when you are connecting to a TimesTen server from a TimesTen client application. TimesTen Client connection attributes are listed in Table 1-10, "Client connection attributes" and described in detail in this section.
In addition to the attributes listed in this section, some database attributes and general connection attributes are also available for client connections or impact the behavior of the connection. These attributes are:
To view the value of client attributes, use the ODBC function SQLGetConnectOptions. To learn more about this function see "Option support for SQLSetConnectOption and SQLGetConnectOptions" section of the Oracle TimesTen In-Memory Database C Developer's Guide.
When connecting to a TimesTen database using the TimesTen Client and Server, the TimesTen Client requires the network address and the TCP port number of the computer running the TimesTen Server. As a convenience, TimesTen allows you to define a logical server name that contains the network address and port number pair.
If you specify anything other than a logical server name for the TTC_Server attribute in the Client DSN definition, TimesTen Client assumes that the Server is running on the default TCP/IP port number. In such cases, if your Server is running on a port other than the default port, you must specify the port number in the ODBC connection string. For example:
"TTC_SERVER=server_host_name;TTC_SERVER_DSN=Server_DSN;TCP_PORT=server_port"
or
"DSN=Client_DSN;TCP_Port=server_port"
Required privilege
No privilege is required to change the value of this attribute.
Setting
| Where to set the attribute | How the attribute is represented | Setting |
|---|---|---|
| C or Java programs | TCP_Port | Specify the port number where the Server is listening. |
Windows ODBC Data Source Administrator and UNIX ODBC.INI file | TimesTen does not support specifying this attribute directly in a UNIX ODBC.INI file or in the Windows ODBC Data Source Administrator. Alternatively, TCP_Port can be defined in the logical server name. | N/A |
This attribute is used to specify the port number to use if an automatic failover occurs. See the description of TCP_Port for details on setting the value of this attribute and associated attributes.
See Oracle TimesTen In-Memory Database Operations Guide for more information on automatic client failover.
Required privilege
No privilege is required to change the value of this attribute.
Setting
| Where to set the attribute | How the attribute is represented | Setting |
|---|---|---|
| C or Java programs | TCP_Port2 | Specify the failover port number where the Server should listen. |
Windows ODBC Data Source Administrator and UNIX ODBC.INI file | TimesTen does not support specifying this attribute directly in a UNIX ODBC.INI file or in the Windows ODBC Data Source Administrator. Alternatively, TCP_Port can be defined in the logical server name. | N/A |
Specifies a port range for the port that the automatic client failover thread listens on for failover notifications in an active/standby replication configuration. The failover configuration allows a client application to connect to a new active node automatically if there is a failure on the current node.
Specifying a port range helps accommodate firewalls between the client and server systems. By default, TimesTen uses a port chosen by the operating system.
|
Note: Client failover is only supported when the client is part of an active/standby pair replication configurations. |
See Oracle TimesTen In-Memory Database Operations Guide for more information on automatic client failover.
Required privilege
No privilege is required to change the value of this attribute.
Setting
Set TTC_FailoverPortRange as follows:
| Where to set the attribute | How the attribute is represented | Setting |
|---|---|---|
C or Java programs or UNIX ODBC.INI file | TTC_FailoverPortRange | Specify a lower value and an upper value for the port numbers in the format lowervalue-uppervalue. |
| Windows ODBC Data Source Administrator | Failover Port Range field | Specify a lower value and an upper value for the port numbers in the format lowervalue-uppervalue. |
When connecting to a TimesTen database using the TimesTen Client and Server, the TimesTen Client requires the specification of the network address and TCP port number of the computer running the TimesTen Server. As a convenience, TimesTen allows you to define a logical server name that contains the network address and port number pair. If you specify anything other than a logical server name for this attribute, TimesTen Client assumes that the Server is running on the default TCP/IP port number. In such cases, if your Server is running on a port other than the default port, you must specify the port number in the ODBC connection string. For example:
"TTC_SERVER=server_host_name;TTC_SERVER_DSN=Server_DSN;TCP_PORT=server_port"
or
"DSN=Client_DSN;TCP_Port=server_port"
Once the logical server name is defined, it can be used as the value for the TTC_Server attribute in a Client DSN. Therefore, multiple Client DSNs referencing the same computer that is running the TimesTen Server can use the same logical server name for the value of the TTC_Server attribute instead of having to specify repeatedly the same network address and port number within each of the Client DSNs.
|
Note: TimesTen recommends that you specify a logical server name for theTTC_Server attribute. However, you can also specify a domain name server (DNS), host name or IP address for the TTC_Server attribute. If you do not use a logical server name and the TimesTen Server is listening on a non-default port number, you must provide the port number in the ODBC connection string. For example:
"TTC_SERVER=server_host_name;TTC_SERVER_DSN=Server_DSN; TCP_PORT=server_port" or "DSN=Client_DSN;TCP_Port=server_port" |
Required privilege
No privilege is required to change the value of this attribute.
Setting
Set TTC_Server as follows:
| Where to set the attribute | How the attribute is represented | Setting |
|---|---|---|
C or Java programs or UNIX ODBC.INI file | TTC_Server | Character string specifying the logical server. |
| Windows ODBC Data Source Administrator | Server Name or Network Address field | Character string specifying the logical server. |
This attribute is used to specify the logical server name to use if an automatic failover occurs. See the description of TTC_Server for details on setting the value of this attribute and associated attributes.
The value of this attribute can be the same as the value specified for TTC_Server if it is a virtual IP address.
If the client has already failed over and has connected to TTC_Server2 and the connection fails, it connects to TTC_Server. It alternately attempts to connect to TTC_Server and TTC_Server2 until the TTC_TIMEOUT attribute expires.
|
Note: Client failover is only supported when the client is part of an active/standby pair replication configurations. |
See Oracle TimesTen In-Memory Database Operations Guide for more information on automatic client failover.
Required privilege
No privilege is required to change the value of this attribute.
Setting
Set TTC_Server2 as follows:
| Where to set the attribute | How the attribute is represented | Setting |
|---|---|---|
C or Java programs or UNIX ODBC.INI file | TTC_Server2 | Character string specifying the logical server to be used if an automatic failover occurs. |
| Windows ODBC Data Source Administrator | Server Name or Network Address 2 field | Character string specifying the logical server to be used if an automatic failover occurs. |
The TTC_Server_DSN attribute specifies a Server DSN on the computer running the TimesTen Server.
On Windows, Server DSNs are the set of TimesTen System DSNs that use the TimesTen Data Manager driver. Use the ODBC Data Source Administrator to define Server DSNs.
On UNIX, Server DSNs are defined in the /var/TimesTen/ instance/sys.odbc.ini file. More details on this topic can be found in the platform-specific sections.
Required privilege
No privilege is required to change the value of this attribute.
Setting
Set TTC_Server_DSN as follows:
| Where to set the attribute | How the attribute is represented | Setting |
|---|---|---|
C or Java programs or UNIX ODBC.INI file | TTC_Server_DSN | Character string specifying the DSN that resides on the Server. |
| Windows ODBC Data Source Administrator | Server DSN field | Character string specifying the DSN that resides on the Server. |
This attribute is used to specify the Server DSN on the computer running the TimesTen Server. This is the Server DSN to be used if an automatic failover occurs. See the description of TTC_Server_DSN for details on setting the value of this attribute and associated attributes.
If a failover occurs, if the client cannot connect to TTC_Server_DSN or loses the connection to the DSN, it attempts to connect to TTC_Server_DSN2.
|
Note: Client failover is only supported when the client is part of an active/standby pair replication configurations. |
See Oracle TimesTen In-Memory Database Operations Guide for more information on automatic client failover.
Required privilege
No privilege is required to change the value of this attribute.
Setting
Set TTC_Server_DSN2 as follows:
| Where to set the attribute | How the attribute is represented | Setting |
|---|---|---|
C or Java programs or UNIX ODBC.INI file | TTC_Server_DSN2 | Character string specifying the DSN that resides on the Server to be used if an automatic failover occurs. |
| Windows ODBC Data Source Administrator | Server DSN2 field | Character string specifying the DSN that resides on the Server to be used if an automatic failover occurs. |
The TTC_Timeout attribute sets a maximum time limit, in seconds, for a network operation that is completed by using the TimesTen Client and Server. The TTC_Timeout attribute also determines the maximum number of seconds a TimesTen Client application waits for the result from the corresponding TimesTen Server process before timing out.
The value of TTC_TIMEOUT is used in the OS select() call on the client side of a C/S connection. It is not used by SQLExecute() or OCIStmtExecute().
A value of 0 indicates that client/server operations should not timeout. Setting of this attribute is optional. If this attribute is not set, the default timeout period is 60 seconds. The maximum timeout period is 99,999 seconds. Upon timeout, the operation is interrupted, the Client application receives a timeout error and the connection is terminated. For example, if the Client application is running long queries, you may want to increase the timeout interval.
For active standby pair failover scenarios, the minimum value is 60 seconds.
If you are using PL/SQL, set the PLSQL_TIMEOUT value to a value that is at least 5 seconds less than TTC_TIMEOUT.
For certain queries, the client application may also set the SQL_QUERY_TIMEOUT ODBC statement option. The TimesTen Client ODBC Driver requires that SQL_QUERY_TIMEOUT must be less than TTC_TIMEOUT, unless the network timeout is set to 0. In that case, the network operation does not timeout.
The query timeout can be set using the SQLSetConnectOption ODBC call before a connection is established to the database using either the SQLConnect or SQLDriverConnect ODBC calls. Alternatively, the query timeout can be set by calling either the SQLSetConnectOption or SQLSetStmtOption ODBC calls after a connection is established to the database.
When the query timeout is set before establishing a connection to the database, the client driver does not know the network timeout value at that point. Hence, later, at connect time, the client driver silently sets the query timeout to a value slightly smaller than the network timeout value if the following are true:
The network timeout value is greater than 0.
The query timeout value was 0, or greater than or equal to the network timeout value.
The timeout value can be set after establishing a connection by calling the ttIsql clienttimeout command. When the query timeout is set after establishing a connection to the database, the client driver returns an error if the network timeout value is greater than 0, and the query timeout value greater than or equal to the network timeout value. The SQLState is set to S1000.
This attribute is not supported when shared memory is used for Client/Server inter-process communication. If set, TimesTen ignores the attribute.
Required privilege
No privilege is required to change the value of this attribute.
Setting
Set TTC_Timeout as follows.
| Where to set the attribute | How the attribute is represented | Setting |
|---|---|---|
C or Java programs or UNIX ODBC.INI file | TTC_Timeout | A value between 0 and 99999 that represents the number of seconds that the TimesTen Client waits for a connection before timing out. (The default value is 60.) In an active standby pair failover scenario, the minimum value is 60. |
| Windows ODBC Data Source Administrator | Timeout Interval field | A value between 0 and 99999 that represents the number of seconds that the TimesTen Client waits for a connection before timing out. (The default value is 60.) In an active standby pair failover scenario, the minimum value is 60. |
Server connection attributes are specified in the Server DSN only and are read at first connection. See "Defining Server DSNs" in Oracle TimesTen In-Memory Database Operations Guide. The attributes are used to set the number of connections to a TimesTen server, the number of servers for each DSN and the size of each connection to the server. These attributes allow you to specify multiple client connections to a single Server. By default, TimesTen creates only one connection to a Server per child process.
|
Note: These attributes must be specified in the DSN. If these attributes are specified in a connection string, TimesTen ignores them and their values. |
There are also TimesTen main daemon options that can be used to specify multiple Server connections. In the case that both the daemon options and these attributes have been specified, the value of the attributes takes precedence.
Server connection attributes are listed in Table 1-11, "Server connection attributes" and described in detail in this section.
The MaxConnsPerServer attribute sets the maximum number of concurrent connections to the server which the DSN references.
If you want to support many connections to the Server, you must make sure that the per-process file descriptor limit for the UID that TimesTen is being run as is set to a value somewhat more than the number of concurrent child servers that are active. This is the number of anticipated concurrent client connections divided by MaxConnsPerServer.
The value of this attribute takes precedence over the setting of the value of the-maxConnsPerDSN option in the ttendaemon.options file. For details, see "Specifying multiple connections to the TimesTen Server" in Oracle TimesTen In-Memory Database Operations Guide.
For limits on the maximum number of connections to a TimesTen database, see Chapter 4, "System Limits".
Changes to TimesTen Server settings do not occur until the TimesTen server is restarted. To restart the Server, use the command ttDaemonAdmin -restartserver.
Required privilege
Only a user with operating system privileges on the system DSN in which this attribute is defined can change the value of this attribute to a value other than the one currently in effect.
Setting
Set MaxConnsPerServer as follows:
| Where to set the attribute | How the attribute is represented | Setting |
|---|---|---|
C or Java programs or UNIX ODBC.INI file | MaxConnsPerServer | A value between 1 and 2047. The default is 1. |
| Windows ODBC Data Source Administrator | Maximum Connections Per Server Process field | A value between 1 and 2047. The default is 1. |
The ServersPerDSN attribute specifies the number of DSNs that can connect to a Server DSN at any given time.
The value of this attribute is only meaningful if the value of MaxConnsPerServer is greater than one. If there is only one connection per Server, the child server uses the process' main stack.
This value of this attribute takes precedence over the setting of the value of the -serversPerDSN option in the ttendaemon.options file. For details, see "Specifying multiple connections to the TimesTen Server" in Oracle TimesTen In-Memory Database Operations Guide.
Changes to TimesTen Server settings do not occur until the TimesTen server is restarted. To restart the Server, use the command ttDaemonAdmin -restartserver.
Required privilege
Only a user with operating system privileges on the system DSN in which this attribute is defined can change the value of this attribute to a value other than the one currently in effect.
Setting
| Where to set the attribute | How the attribute is represented | Setting |
|---|---|---|
C or Java programs or UNIX ODBC.INI file | ServersPerDSN | A value between 1 and 2047. The default is 1. |
| Windows ODBC Data Source Administrator | Server Processes Per DSN field | A value between 1 and 2047. The default is 1. |
The ServerStackSize attribute value determines the size of the stack on the Server for each connection. The value of this attribute is only meaningful if the value of MaxConnsPerServer is greater than one. If there is only one connection per Server, the child server uses the process' main stack. It is also platform-dependent, as defined in the setting below.
This value of this attribute takes precedence over the setting of the -serverStackSize option in the ttendaemon.options file. For details, see "Specifying multiple connections to the TimesTen Server" in Oracle TimesTen In-Memory Database Operations Guide.
Changes to TimesTen Server settings do not occur until the TimesTen server is restarted. To restart the Server, use the command ttDaemonAdmin -restartserver.
Required privilege
Only a user with operating system privileges on the system DSN in which this attribute is defined can change the value of this attribute to a value other than the one currently in effect.
Setting
Set ServerStackSize as follows:
| Where to set the attribute | How the attribute is represented | Setting |
|---|---|---|
C or Java programs or UNIX ODBC.INI file | ServerStackSize | Valid values depend on the platform. The default is 128KB for 32-bit platforms and 256KB for 64-bit platforms.
If the
else 0 If the
r1.rlim_cur /1024 else 4096 The default is 128KB for 32-bit platforms and 256KB for 64-bit platforms. |
| Windows ODBC Data Source Administrator | Server Stack Size field | Valid values depend on the platform. The default is 128KB for 32-bit platforms and 256KB for 64-bit platforms.
If the
else 0 If the
r1.rlim_cur /1024 else 4096 |
