1 Connection Attributes

Data store attributes

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.

Data Source Name

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:

DataStore

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.

Required privilege

Only the instance administrator can change the value of this attribute.

Setting

Set DataStore as follows:

DatabaseCharacterSet

The database character set determines the character set in which data is stored.

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:

Supported character sets

The tables in this section describe the character sets supported in TimesTen.

Asian character sets

European character sets

Middle Eastern character sets

TimesTen character set

Universal character sets

See also

ConnectionCharacterSet

Description

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:

LogDir

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:

Preallocate

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:

ReplicationApplyOrdering

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:

ReplicationParallelism

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:

Temporary

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.

Required privilege

Only the instance administrator can change the value of this attribute.

Setting

Set Temporary as follows:

TypeMode

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

Set TypeMode as follows:

First connection attributes

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.

AutoCreate

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:

CkptFrequency

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

Set CkptFrequency as follows:

CkptLogVolume

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

Set CkptLogVolume as follows:

CkptRate

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:

Connections

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.

Required privilege

Only the instance administrator can change the value of this attribute.

Setting

Set Connections as follows:

ForceConnect

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:

LogAutoTruncate

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:

LogBufMB

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:

LogBufParallelism

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:

LogFileSize

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:

LogFlushMethod

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:

See also

DurableCommits

LogPurge

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:

MemoryLock

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.

Overwrite

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:

PermSize

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:

ReceiverThreads

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:

RecoveryThreads

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:

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

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 + ceiling(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:

General connection attributes

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.

ConnectionName

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:

DDLCommitBehavior

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.

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:

Examples

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.

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

DDLReplicationAction

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:

DDLReplicationLevel

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:

Diagnostics

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:

DuplicateBindMode

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.

DurableCommits

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

LogFlushMethod

Isolation

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:

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

2. Turn off AutoCommit:

   Command> autocommit=0;
   

3. Temporarily change the PassThrough level to 0:

   Command> passthrough=0;
   

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

LockLevel

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:

LockWait

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:

MatchLogOpts

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:

PermWarnThreshold

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:

PrivateCommands

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:

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.

PWDCrypt

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:

QueryThreshold

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:

ReplicationTrack

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:

SQLQueryTimeout

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:

TempWarnThreshold

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:

UID and PWD

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

Set UID and PWD as follows:

WaitForConnect

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:

NLS general connection attributes

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

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:

NLS_LENGTH_SEMANTICS

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:

NLS_NCHAR_CONV_EXCP

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:

NLS_SORT

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.

Required privilege

No privilege is required to change the value of this attribute.

Setting

Set NLS_SORT as follows:

Supported linguistic sorts

The tables in this section list the supported values for the NLS_SORT general connection attribute and the NLS_SORT SQL function.

Monolingual linguistic sorts

Multilingual linguistic sorts

PL/SQL first connection attributes

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.

PLSQL

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

Set PLSQL as follows:

PLSQL_MEMORY_ADDRESS

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:

1. Maximize the amount of virtual space for your TimesTen database and for your applications.

2. Minimize the fragmentation of the virtual address space.

3. Avoid conflicts with other uses of virtual address apace.

The platform specific default memory addresses are:

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:

PLSQL_MEMORY_SIZE

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:

PL/SQL general connection attributes

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

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.

Required privilege

No privilege is required to change the value of this attribute.

Setting

Set PLSCOPE_SETTINGS as follows:

PLSQL_CCFLAGS

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

Set PLSQL_CCFLAGS as follows:

You can use the ALTER SESSION SQL statement to change this attribute within a session.

PLSQL_CONN_MEM_LIMIT

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:

PLSQL_OPTIMIZE_LEVEL

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.

Required privilege

No privilege is required to change the value of this attribute.

Setting

Set PLSQL_OPTIMIZE_LEVEL as follows:

PLSQL_TIMEOUT

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.

Required privilege

No privilege is required to change the value of this attribute.

Setting

Set PLSQL_TIMEOUT as follows:

See Also

TTC_Timeout

IMDB Cache first connection attributes

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.

CacheAWTMethod

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.

Required privilege

Only the instance administrator can change the value of this attribute.

Setting

Set CacheAWTMethod as follows:

IMDB Cache database attributes

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

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:

CacheGridEnable

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:

CacheGridMsgWait

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:

IMDB Cache general connection attributes

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.

DynamicLoadEnable

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.

Required privilege

No privilege is required to change the value of this attribute.

Setting

Set DynamicLoadEnable as follows:

DynamicLoadErrorMode

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.

Required privilege

No privilege is required to change the value of this attribute.

Setting

Set DynamicLoadErrorMode as follows:

OracleNetServiceName

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:

OraclePWD

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.

Required privilege

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

"UID and PWD"

PassThrough

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.

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.

RACCallback

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

Set RACCallback as follows:

TimesTen Client connection attributes

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:

• ConnectionCharacterSet

• ConnectionName

• UID and PWD

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.

TCP_Port

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

Set TCP_Port as follows:

TCP_Port2

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

Set TCP_Port2 as follows:

TTC_FailoverPortRange

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.

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:

TTC_Server

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.

"TTC_SERVER=server_host_name;TTC_SERVER_DSN=Server_DSN;
TCP_PORT=server_port"

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

TTC_Server2

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.

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:

TTC_Server_DSN

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:

TTC_Server_DSN2

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.

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:

TTC_Timeout

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.

Server connection attributes

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.

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.

MaxConnsPerServer

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:

ServersPerDSN

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

Set ServersPerDSN as follows:

ServerStackSize

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: