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 data store 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 data store, 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 data stores, 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 foundThe command failed.INSERT INTO t1 VALUES('more data');1 row inserted.CREATE TABLE t1 (c1 VARCHAR2(10)); 2207: Table t1 already existsThe command failed.ROLLBACK;SELECT * FROM t1;0 rows found.

-- implicit commit hereCommand> CREATE TABLE t1 (c1 varchar2(10));Table created.                                                        -- implicit commit hereCommand> COMMIT;Commit complete.Command> INSERT INTO t1 VALUES('some data');1 row created.                                                       -- implicit commit hereCommand> CREATE TABLE t2 (c1 INTEGER);Table created.                                                     -- implicit commit hereSQL> ROLLBACK;Rollback complete.Command> SELECT * FROM t1;C1----------some dataCommand> SELECT * FROM t2;no rows selectedCommand> INSERT INTO t1 VALUES('more data');1 row created.                                                        -- implicit commit hereCommand> 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 rollbackCommand> ROLLBACK;Rollback complete.Command> SELECT * FROM t1;C1----------some datamore data

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 in the event of a failure. 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 isolation level for the connection. For a description of the isolation levels, see "Concurrency control" in Oracle TimesTen In-Memory Database Operations Guide.

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.

The value may be modified by an ALTER SESSION statement. For details, see Oracle TimesTen In-Memory Database SQL Reference.

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 data store, you can enable data store-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 data store-level locking leads to loss of concurrency. To display a list of all locks on a particular data store you can use the ttXactAdmin utility.

When using PL/SQL in your applications, set LockLevel=0 and selectively change to data store level locking for specific transactions that require that level of locking by using the ttLockLevel built-in procedure.

Required privilege

Setting LockLevel to 1 requires ADMIN privilege.

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.

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 data store 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 data store determines the type of logging that is performed and whether the transaction log files are purged. Any subsequent connection must specify the same values for the Logging and LogPurge attributes or an error will be generated. If a connection does not know the current state of these attributes, MatchLogOpts can be set so that the logging attributes will 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 data store's memory. The data store 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" and "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 will be 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 TimesTen to TimesTen 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:

SqlQueryTimeout

Use this attribute to specify the time limit in seconds within which the data store should execute SQL statements.

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 the query will 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 data store's memory. The data store 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" and "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 data store and the connection is not possible (perhaps during concurrent loading/recovery of a data store), TimesTen normally waits for completion of the conflicting connection. In some cases, it can take some time for an application to connect to a data store. If the WaitForConnect attribute is off and the data store 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: