3 Utilities

ttAdmin

Description

Allows you to:

• Specify policies to automatically or manually load and unload databases from RAM.

• Specify policies to automatically or manually start and stop replication agents for specified databases.

• Start and stop TimesTen cache agents for caching data from Oracle tables. The cache agent is a process that handles Oracle database access on behalf of a TimesTen database. It also handles the aging and autorefresh of the cache groups in the TimesTen database. Before using any cache features, you must start the cache agent. Cache options require that you specify a value for the OracleNetServiceName in the DSN.

Required privilege

This utility requires no privileges to query the database.

Replication options require the ADMIN privilege.

Cache options require the CACHE_MANAGER privilege.

All other options require the ADMIN privilege.

If authentication information is not supplied in the connection string or DSN, this utility prompts for a user ID and password before continuing.

Syntax

ttAdmin {-h | -help | -?}

ttAdmin {-V | -version}

ttAdmin [-ramPolicy always|manual|inUse [-ramGrace secs] ]
[-ramLoad] [-ramUnload]
[-autoreload | -noautoreload]
[-repPolicy always|manual|norestart]
[-reqpQueryThresholdGet]
[-reqpQueryThresholdSet seconds]
[-repStart | -repStop]
[[-cacheUidGet] |
 [-cacheUidPwdSet -cacheUid uid [-cachePwd pwd]] |
 [-cachePolicy always|manual|norestart] |
 [-cacheStart] | 
 [-cacheStop [-stopTimeout seconds]]]
[-query]
{-connStr connection_string | DSN} 

Options

ttAdmin has the options:

Examples

A database referred to by DSN SalesData is used by some very performance sensitive applications. So that applications do not have to wait for the database to be loaded from disk into RAM, this database must always remain in RAM. To achieve this, use:

ttAdmin -ramPolicy always SalesData

The SalesData database is normally always resident in RAM. However, it is not being used at all today and should be loaded only when applications are connected to it. To change the RAM policy, use:

ttAdmin -ramPolicy inUse SalesData

To manually control whether the SalesData database is loaded into RAM and to load it now, use the following.

ttAdmin -ramPolicy manual -ramLoad SalesData

To manually unload the SalesData database from RAM, thus preventing any new applications from connecting to the database, use:

ttAdmin -ramPolicy manual -ramUnload SalesData

A database referred to by DSN History is not always in use. Permanently loading it into RAM unnecessarily uses memory. This database is idle for long periods, but when it is in use multiple users connect to it in rapid succession. To improve performance, it may be best to keep the database in RAM when applications are connected to it and to keep it in RAM for 5 minutes (300 seconds) after the last user disconnects. With this RAM policy, as long as applications are connected to the database, the database remains in RAM. To set this policy, use:

ttAdmin -ramPolicy inUse -ramGrace 300 History

A database referred to by DSN SalesData is used to cache Oracle data. Use the following ttAdmin command to start the cache agent for the SalesData DSN:

ttAdmin -cacheStart SalesData

You can also use the -cachePolicy option to ask the TimesTen data manager daemon to start the cache agent every time the data manager itself is started. Use:

ttAdmin -cachePolicy always SalesData

To turn off the automatic start of cache agent, use:

ttAdmin -cachePolicy manual SalesData

To set the cache administration user ID and password, the -cacheUidPwdSet flag with the -cacheUid and -cachePwd options can be used with ttAdmin. For example, if the cache administration user ID and password on the database SalesData should be scott and tiger respectively, use:

ttAdmin -cacheUidPwdSet -cacheUid scott -cachPwd tiger SalesData

To get the current cache administration user ID for the SalesData DSN, use:

ttAdmin -cacheUidGet SalesData

ttAdmin displays the following output:

Cache User Id: scott
RAM Residence Policy: inUse
Replication Agent Policy: manual
Replication Manually Started: False
Cache Agent Policy: manual
Cache Agent Manually Started: False

Notes

If TimesTen is installed as a user instance, and the user attempts to start the cache agent for a database with a relative path, TimesTen looks for the database relative to where it is running, and fails. Therefore, a relative path should not be used in this scenario. For example, on Windows, if you have specified the path for the database as DataStore=./dsn1 and attempt to start the cache agent with the command ttAdmin -cacheStart dsn1, the cache agent does not start because it looks for the database in install_dir\srv\dsn1. For UNIX it looks in a directory in /var/TimesTen/instance/.

When using autorefresh (automatic propagation from oracle to timesten) or asynchronous writethrough cache groups, you must specify the cache administration user ID and password. This user account is used to perform autorefresh and asynchronous writethrough operations.

To load Oracle data, the TimesTen cache agent must be running. This requires that the ORACLE_HOME environment variable be set to the path of the Oracle installation. See the Oracle In-Memory Database Cache User's Guide for more details. For details on other environment variables that you may want to set, see "Environment variables" in Oracle TimesTen In-Memory Database Installation Guide.

This utility is supported only for TimesTen Data Manager DSNs. It is not supported for TimesTen Client DSNs.

If ttAdmin is used with -repStart and it does not find a replication definition, the replication agent is not started and ttAdmin prints out an error message. For example:

$ ttAdmin -repstart repl1
*** [TimesTen][TimesTen 11.2.2.0.0 ODBC Driver][TimesTen]TT8191: 
This store (repl1 on my_host) is not involved in a replication scheme -- 
file "eeProc.c", lineno 11016, procedure "RepAdmin()"
*** ODBC Error = S1000, TimesTen Error = 8191

If ttAdmin is used with the -ramPolicy always option, a persistent system connection is created on the database. The -ramPolicy always option can only be specified on shared databases.

The only -ramPolicy value supported for temporary databases is the -ramPolicy manual option with the -ramLoad option specified at the same time.

If ttAdmin is used with -repPolicy manual (the default) or -repPolicy always, then the -ramPolicy always option should also be used. This ensures that the replication agent begins recovery after a failure as quickly as possible.

See also

ttAdoptStores

Description

On UNIX systems, use this utility to move databases from a TimesTen instance to a new TimesTen instance that is of the same major release, but of a different minor release. For example, you can move files from TimesTen 11.2.2.0.0 to TimesTen 11.2.2.1.0.

This utility is useful for testing a minor release of Times with an existing database. You can install the new release of TimesTen and move one or more databases to the new release without uninstalling the old TimesTen release.

You must run the ttAdoptStores utility from the destination instance.

Required privilege

This utility must be run by the TimesTen instance administrator. The instance administrator must be the same user for both the old and new TimesTen instance.

Syntax

ttadoptstores {-h | -help | -?}
ttadoptstores {-V | -version}
ttadoptstores [-quiet] -dspath path
ttadoptstores [-quiet] -instpath path

Options

ttAdoptStores has the options:

Examples

To adopt the database /my/data/stores/ds, use:

ttadoptstores -dspath /my/data/stores/ds

To adopt all the databases in the directory /opt/TimesTen/ instance1, use:

ttadoptstores -instpath /opt/TimesTen/instance1

Note

You cannot adopt temporary databases.

If an instance being adopted is part of a replication scheme, port numbers must match on each side of the replication scheme, unless a port number was specified as the value of the -remoteDaemonPort option during a ttRepAdmin -duplicate operation. Generally, all instances involved in the replication scheme must be updated at the same time.

This utility does not copy any sys.odbc.ini entries. You must move these files manually.

ttBackup

Description

Creates a backup copy of a database that can be restored at a later time using the ttRestore utility. If the database is in use at the time of backup, it must be in shared mode to enable ttBackup. For an overview of the TimesTen backup and restore facility, see "Migration, Backup, and Restoration" in Oracle TimesTen In-Memory Database Installation Guide.

Required privilege

This utility requires the ADMIN privilege.

If authentication information is not supplied in the connection string or DSN, this utility prompts for a user ID and password before continuing.

Syntax

ttBackup {-h | -help | -?}
ttBackup {-V | -version}
ttBackup -dir directory [-type backupType]
[-fname fileprefix] [-force]
{-connStr connection_string | DSN} 

Options

ttBackup has the options:

Examples

To perform a full file backup of the FastIns database to the backup directory in/users/pat/TimesTen/backups, use:

ttBackup -type fileFullEnable -dir /users/pat/TimesTen/backups FastIns

To copy the FastIns database to the file FastIns.back, use:

ttBackup -type streamFull FastIns > FastIns.back

On UNIX, to save the FastIns database to a backup tape, use:

ttBackup -type streamFull FastIns | dd bs=64k of=/dev/rmt0

To back up a database named origDSN to the directory /users/rob/tmp and restore it to the database named restoredDSN, use:

ttBackup -type fileFull -dir /users/rob/tmp -fname restored origDSN
ttRestore -dir /users/rob/tmp -fname restored restoredDSN

Notes

The ttBackup utility and the ttRestore utility can be used to backup and restore databases only when the first three numbers of the TimesTen release are the same. For example, you can backup and restore files between releases 11.2.2.2.0 and 11.2.2.3.0. You cannot backup and restore files between releases 11.2.1.9.0 and 11.2.2.3.0. You can use the ttMigrate or ttMigrateCS utility to migrate databases across major releases, operating systems, operating system bit levels or CPU types.

When an incremental backup has been enabled, TimesTen creates a backup hold in the transaction log file. This hold can be seen using the ttLogHolds built-in procedure. The backup hold is used to determine which log records should be backed up upon subsequent incremental backups. Only changes since the last incremental backup are updated. A side effect to creating the backup hold is that it prevents transaction log files from being purged upon a checkpoint operation until the hold is advanced by performing another incremental backup or removed by disabling incremental backups.

Transactions that commit after the start of the backup operation are not reflected in the backup.

Up to one checkpoint and one backup may be active at the same time, with these limitations:

• A backup never needs to wait for a checkpoint to complete.

• A backup may need to wait for another backup to complete.

• A checkpoint may need to wait for a backup to complete.

Databases containing cache groups can be backed up as normal with the ttBackup utility. However, when restoring such a backup, special consideration is required as the restored data within the cache groups may be out of date or out of sync with the data in the backend Oracle database. See the section on "Backing up and restoring a database with cache groups" in the Oracle In-Memory Database Cache User's Guide for details.

You cannot back up temporary databases.

See also

ttBulkCp

Description

Copies data between TimesTen tables and ASCII files. ttBulkCp has two modes:

• In copy-in mode rows are copied into an existing TimesTen table from one or more ASCII files (or stdin).

• In copy-out mode an entire TimesTen table is copied to a single ASCII output file (or stdout).

On UNIX, this utility is supported for TimesTen Data Manager DSNs. For Client DSNs, use the utility ttBulkCpCS.This utility only copies out the objects owned by the user executing the utility, and those objects for which the owner has SELECT privileges. If the owner executing the utility has the ADMIN privilege, ttBulkCP copies out all objects.

Required privilege

This utility requires the INSERT privilege on the tables it copies information into. It requires the SELECT privilege on the tables it copies information from. If authentication information is not supplied in the connection string or DSN, this utility prompts for a user ID and password before continuing.

Syntax

ttBulkCp {-h | -help | -? | -helpfull}

ttBulkCp {-V | -version}

ttBulkCp -i [-cp numTrans | final] [-d errLevel] 
[-e errorFile] [-m maxErrs] [-sc] [-t errLevel]
[-u errLevel] [-v 0|1] [-xp numRows | rollback] 
[-Cc | -Cnone] [-tformat timeFormat] [-dateMode dateMode] 
[-tsformat timeStampFormat] [-dformat | -D dateFormat] 
[-F firstRow] [-L lastRow] [-N ncharEncoding] [-Q 0|1] 
[-S errLevel] {-connStr connection_string | DSN} 
[owner.]tableName [dataFile ...]

ttBulkCp -o [-sc] [-v 0|1] [-A 0|1] [-Cc | -Cnone] 
[-nullFormat formatStr}
[-tformat timeFormat] [-tsformat timeStampFormat] 
[-dateMode dateMode] [-dformat | -D dateFormat]
[-N ncharEncoding] [-noForceSerializable | -forceSerializable]
[-tsprec precision] [-Q 0|1] 
{-connStr connection_string | DSN} [owner.]tblName 
[dataFile]

Options

ttBulkCp has the following options.

Warning: This output was produced using a
non-serializable isolation level. It may therefore not
reflect a transaction-consistent state of the table.

The following options can be used in copy-out (-o) mode only. You must have SELECT privileges on the specified tables.

The following options can be used in copy-in (-i) mode only. You must have INSERT privileges on the specified tables.

datafile format

Every line of a ttBulkCp input file is one of the following: a blank line, a comment line, an attribute line or a data line.

• Blank lines are lines with no characters at all, including whitespace characters (space and tab). Blank lines are ignored by ttBulkCp.

• Comment lines begin with the comment character. The default comment character is #; this default can be overridden with the -C command-line option or the COMMENTCHAR file attribute (see "File attribute line format"). The comment character must be the first character on the line. Comment lines are ignored by ttBulkCp. Comments at the end of data lines are not supported.

• File attribute lines are used for setting file attributes that control the formatting of the datafile. Attribute lines begin with the ten-character sequence ##ttBulkCp. The full syntax for attribute lines is described in "File attribute line format". Attribute lines can appear anywhere in the datafile.

• Data lines contain the rows of the table being copied. Data lines in the datafile and rows of the table correspond one-to-one; that is, each data line completely describes exactly one row. Each data line consists of a list of column values separated by the field separator character. The default field separator is a comma (,). This default can be overridden by the -s command-line option or the FSEP file attribute. The full syntax for data lines is described in "Data line format".

File attribute line format

The format of an attribute line is:

##ttBulkCp[:attribute=value]...

Attribute lines always begin with the ten-character sequence ##ttBulkCp, even if the comment character is not #. This sequence is followed by zero or more file attribute settings, each preceded by a colon.

File attribute settings remain in effect until the end of the input file or until they are changed by another attribute line in the same input file. The values of any file attributes that are omitted in an attribute line are left unchanged.

Most command line options take precedence over the values in the file attributes that are supported by ttBulkCp. The CHARACTERSET attribute is the only file attribute that overrides command line options.

The file attributes are:

• CHARACTERSET: Specifies the character set to be used to interpret the data file. If the file attribute is not set, the character set used to interpret the file will be the one specified in the ConnectionCharacterSet connection attribute. For best performance, the value of the DatabaseCharacterSet connection attribute should match either the ConnectionCharacterSet connection attribute or this file attribute. If the character set supplied in ConnectionCharacterSet connection attribute or in this file attribute is different than the actual character set of the file, ttBulkCp may interpret data incorrectly.

• VERSION: Specifies the version of the file format used in the file, expressed as major.minor. The only supported version is 1.0.

• DATEMODE: Specifies whether an Oracle DATE type is specified as simple date or as timestamp.

• FSEP: Specifies the field separator character used in the file. The field separator can be set to \t (tab) or any of the characters: ~ ! @ # $ % ^ & * ( ) = : ; | < > ? , /

• QUOTES: Indicates whether character string values in the file are enclosed in double quotes. The value can be 0, to indicate that strings are not quoted, or 1, to indicate that strings are quoted. This value can be overridden with the -Q option.

• COMMENTCHAR: Specifies the comment character used in the file. The comment character can be set to \t (tab) or any of the characters: ~ ! @ # $ % ^ & * ( ) = : ; | < > ? , /

The comment character can also be set to the value none, which disables the use of comments in the datafile.

• DFORMAT: Sets the date format. Legal values are described in "Date, time and timestamp values". When a custom format is used, it should be enclosed in single quotes. This value can be overridden with the -D/-dformat command-line option. See also TFORMAT and TSFORMAT.

• NCHARENCODING: Indicates the encoding to be used for the NCHAR and NVARCHAR2 data types. The value may be either ASCII or UTF-8.

• TFORMAT: Indicates the time format. Legal values are described in "Date, time and timestamp values". When a custom format is used, it should be enclosed in single quotes. This value can be overridden with the -tformat command-line option. See also DFORMAT and TSFORMAT.

• TSFORMAT: Sets the timestamp format. Legal values are described in "Date, time and timestamp values". When a custom format is used, it should be enclosed in single quotes. This value can be overridden with the -tsformat command-line option. See also DFORMAT and TFORMAT.

Examples

The following header line sets the field separator character to $ and disables quoting of character strings:

##ttBulkCp:FSEP=$:QUOTES=0

The following header line disables comments and sets the date format to the Oracle format:

##ttBulkCp:COMMENTCHAR=none:DFORMAT=Oracle

The following header line set the date format to a custom format:

##ttBulkCp:DFORMAT='Mon DD, YYYY'

Data line format

Data lines contain the row data of the table being copied. Each data line corresponds to a row of the table; rows cannot span input-file lines. A data line consists of a list of column values separated by the field separator character. Unnecessary whitespace characters should not be placed either before or after the field separator. The format of each value is determined by its type.

NULL values

NULL values can either be expressed as NULL (all capitals, no quotes) or as empty fields.

Character and unicode strings

CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, NCLOB: If quoting of character strings is enabled (the default), then strings and characters must be enclosed in double quotes. If quoting of character strings is disabled, then any double-quote characters in the string are considered to be part of the string itself. ttBulkCp recognizes the following backslash-escapes inside a character string, regardless of whether quoting of strings is enabled:

• \" The double-quote character. If character-string quoting is enabled, then all double quote characters in the string must be escaped with a backslash. If character-string quoting is disabled, then it is permissible, but not necessary, to use the backslash.

• \t The tab character.

• \n The newline character.

• \r The carriage return character.

• \\ The backslash character.

• \xyz (CHAR and VARCHAR2 only) The character whose ASCII value is xyz, where xyz is a three-character octal number, as in \033.

• \uxyzw (NCHAR and NVARCHAR2 only) The character whose unicode value is xyzw, where xyzw is a four-digit hexadecimal number, as in\ufe4a. The \uxyzw notation is supported in both UTF-8 and ASCII encoding modes.

In addition, any of the ~ ! @ # $ % ^ & * ( ) = : ; | < > ? , / characters can be escaped with a backslash. Although it is unnecessary to escape these characters in most cases, doing so prevents them from being mistaken for a comment character or a field separator when character-string quoting is disabled.

If character-string quoting is enabled, the empty string (represented as " ") is distinct from NULL. If character-string quoting is disabled, then empty strings cannot be represented, as they cannot be distinguished from NULL.

For unicode strings, unicode characters encoded using UTF-8 multibyte sequences are supported in the UTF-8 encoding mode only. If these sequences are used with the ASCII encoding mode, ttBulkCp interprets each byte in the sequence as a separate character.

For fixed-length CHAR and NCHAR fields, strings that are shorter than the field length are padded with blanks. For VARCHAR2 and NVARCHAR2 fields, the string is entered into TimesTen exactly as given in the datafile. Trailing blanks are neither added nor removed.

Binary values

BINARY, VARBINARY, BLOB: If quoting of character strings is enabled (the default), binary values are delimited by curly braces ({...}). If quoting of character strings is disabled, then curly braces should not be used. Whether character-string quoting is enabled or disabled, binary values may start with an optional 0x or 0X.

Each byte of binary data is expressed as two hexadecimal digits. For example, the four-byte binary string:

01101000 11001010 01001001 11101111

would be expressed as the eight-character hexadecimal string:

68CA49EF

Digits represented by the letters A through F can either be upper- or lower-case. The hexadecimal string cannot contain white spaces. Because each pair of characters in the hexadecimal string is converted to a single binary byte, the hexadecimal string must contain an even number of characters. For fixed-length binary fields, if the given value is shorter than the column length, the value is padded with zeros on the right. For varbinary values, the binary value is inserted into TimesTen exactly as given in the datafile.

If character-string quoting is enabled, a zero-length binary value (represented as { }) is distinct from NULL. If character-string quoting is disabled, then zero-length binary values cannot be represented, as they cannot be distinguished from NULL.

Integer values

TINYINT, SMALLINT, INTEGER, BIGINT: Integer values consist of an optional sign followed by one or more digits. Integer values may not use E-notation. Examples:

-14 98765 +186

Floating-point values

REAL, FLOAT, DOUBLE: Floating-point values can be expressed with or without decimal points and may use E-notation. Examples:

3.1415
-0.00004
1.1e-3
5e3
.56
-682
-.62E-4
170.

Fixed-point values

DECIMAL, NUMERIC: Decimal values can be expressed with or without decimal points. Decimal values may not use E-notation. Examples:

5
-19.5
-11
000
-.1234
45.
-57.0
0.8888

Inf, -Inf and NaN values

Inf, -Inf and Nan values: Infinity and Not a Number values can be represented as strings to represent the corresponding constant value (all are case insensitive):

TimesTen outputs the values as: NAN, INF and -Inf.

Date, time and timestamp values

Formats for date, time and timestamp values can be specified either by selecting a fixed datetime format or by defining a custom datetime format. The custom datetime formats are defined using format specifiers similar to those used by the TO_DATE and TO_CHAR SQL functions, as described in the following table.

In many cases, it is not necessary to define the timestamp format, even when a custom date or time format is used, because the default TimesTen format (DF*TF+FF) is defined in terms of the date and time formats. Therefore, setting the date format sets not only the format for date values, but also for the date portion of timestamp values. Similarly, setting the timestamp format affects both time values and the time portion of the timestamp values.

Fixed, date, time and timestamp formats

For date values, the fixed formats are:

For time values, the only fixed format is ODBC:

For timestamp values, the fixed formats are:

The default timestamp value is: 'DF*TF+FF'

Examples

The following input file is for a table with five columns: two char columns, a double column, an integer column and a varbinary column. In the "Mountain View" line, the last three columns have NULL values.

##ttBulkCp
# This is a comment.
###### So is this.
# The following line is a blank line.

"New York","New York",-345.09,12,{12EF87A4E5}
"Milan","Italy",0,0,{0x458F}
"Paris","France",1.4E12,NULL,{F009}
"Tokyo","Japan",-4.5E-18,26,{0x00}
"Mountain View","California",,,

Here is an equivalent input file in which quotes are disabled, the comment character is '$' and the field separator is '|':

##ttBulkCp:QUOTES=0:COMMENTCHAR=$:FSEP=|
$ This is a comment.
$$$$$$ So is this.
$ The following line is a blank line.

New York|New York|-345.09|12|12EF87A4E5
Milan|Italy|0|0|0x458F
Paris|France|1.4E12|NULL|F009
Tokyo|Japan|-4.5E-18|26|0x00
Mountain View|California|||

The following command dumps the contents of table mytbl from database mystore into a file called mytbl.dump.

ttBulkCp -o DSN=mystore mytbl mytbl.dump

The following command loads the rows listed in file mytbl.dump into a table called mytbl on database mystore, placing any error messages into the file mytbl.err.

ttBulkCp -i -e mytbl.err DSN=mystore mytbl mytbl.dump

The above command terminates after the first error occurs. To force the copy to continue until the end of the input file (or a fatal error), use -m 0, as in:

ttBulkCp -i -e mytbl.err -m 0 DSN=mystore mytbl mytbl.dump

To ignore errors caused by constraint violations, use -d ignore:

ttBulkCp -i -e mytbl.err -d ignore DSN=mystore mytbl mytbl.dump

Notes

ttBulkCp explicitly sets the Overwrite connection attribute to 0, to prevent accidental destruction of a database. For more information, see "Overwrite".

Real, float or double values may be rounded to zero when the floating point number is small.

The connection attribute PassThrough with a non-zero value is not supported with this utility and will return an error.

When specifying date, time and timestamp formats, incomplete or redundant formats are not allowed in input mode. Specifiers that reference fields that are not present in the data type (for example a minute specifier in a date format) return errors in copy-out mode. In copy-in mode, the values of those specifiers are ignored.

The following caveats apply when disabling quoted strings in the ttBulkCp datafile:

• Empty strings and zero-length binary values cannot be expressed, as they cannot be distinguished from NULL.

• If the field separator character appears inside a character string, it must be escaped with a backslash or else it is treated as an actual field separator.

• If a data line begins with a character string and that string begins with the comment character, that character must be escaped with a backslash or else the line is treated as a comment. Setting the comment character to none can prevent this, as long as there are no actual comments in the file.

For UTF-8, NCHAR are converted to UTF-8 encoding and then output. UTF-8 input is converted to NCHAR.

For ASCII, those NCHAR values that correspond to ASCII characters are output as ASCII. For those NCHAR values outside of the ASCII range, the escaped Unicode format is used.

On Windows, this utility is supported for all TimesTen Data Manager and Client DSNs.

It is recommended that you do not run DDL SQL commands while running ttBulkCp to avoid lock contention issues for your application.

See also

ttCacheAdvisor

Description

Assists in configuring an Oracle In-Memory Database (IMDB) Cache for optimal performance and minimal storage overhead. This utility evaluates a captured SQL workload running on a target Oracle database, or an existing SQL tuning set. It also evaluates the schema definitions from the target database.

This utility analyzes the table and column usage patterns, and recommends TimesTen cache group definitions to improve the workload performance by generating a report and an implementation script. It also optionally evaluates the performance of the recommended cache and compares it with the performance of the target Oracle database.

Required privilege

This utility requires the instance administrator privilege.

Syntax

General analysis

ttCacheAdvisor
  -oraTarget
      {-oraConn Oracle_connection_string} ...
      -oraDirObject Oracle_directory_object
      [{-oraDirNfs path} | {-ftp network_connection_string}]
  -oraRepository
      -oraConn Oracle_connection_string
      -oraDirObject Oracle_directory_object
      [{-oraDirNfs path} | {-ftp network_connection_string}]
  -ttConn TimesTen_connection_string
  [-captureCursorCache minutes] [-flushSharedPool] [-plsqlInfo]]
  [-cacheSize {{size{MB|GB|TB}} | UNLIMITED}]
  [-writethruThreshold percent%]
  [-report path]
  [-evalSqlPerf [maximum[%]]]
  [-task task_name]
  [-description string]
  [-rerun]
  [-showSql]
  [{-import [-noSchema] path} ...]
  [-add
    {-tableAttrib [owner.]table_name
      {-pk '(column_name, ...)' |
              -fk '(column_name, ...) REFERENCES (primary_key_column_name, ...)
         [ON DELETE CASCADE]' |
       -where '(predicate)'} ...
    } ...]
  [-drop
    {-tableAttrib [owner.]table_name
      {-pk | -fk | -where} ...
    } ...]
  [{{-add | -drop} -sqlSet [owner.]sql_set_name}
  [{-command | @} path]

You can repeat the options for -oraTarget to specify multiple users for the same target Oracle database to analyze objects from more than one schema. There can be only one Oracle repository specification in a Cache Advisor evaluation run.

String values may be optionally enclosed in single or double quotes. If the string begins with a dash (-), then the string must be enclosed in single or double quotes.

Print a usage message and exit

ttCacheAdvisor {{-help | -h} [option]}

Print the release number of ttCacheAdvisor and exit

ttCacheAdvisor {-V | -version}

Export a workload and schema definitions from the target Oracle database to files:

ttCacheAdvisor
  [{-command|@} path]
  {-oraTarget
     -oraConn oracle_connection_string
     -oraDirObject oracle_directory_object
     [{-oraDirNfs| -ftp} network_connection_string]
  } ...
  -captureCursorCache minutes [-flushSharedPool]
  -export [-zip] path
  [-showSql]

Display a summary or details of existing tasks in the repository Oracle database:

ttCacheAdvisor  [{-command|@} path]  -oraRepository -oraConn oracle_connection_string
  -showTask [task_name]
  [-showSql]

Drop an existing task from the repository Oracle database:

ttCacheAdvisor   [{-command|@} path]  -oraRepository -oraConn oracle_connection_string
  -dropTask task_name
  [-showSql]

For more information about using Cache Advisor to capture and analyze a SQL workload running on a target Oracle database, see Oracle In-Memory Database Cache User's Guide.

Options

ttCacheAdvisor has the following options:

Examples

Use ftp as the network connection to transfer files between the target Oracle system, repository Oracle system, and TimesTen system. Capture a SQL workaround on the target database for a duration of 30 minutes. Flush the shared pool on the target database before capturing the workload.

% ttCacheAdvisor -oraTarget -oraConn orauser@targetdb \
-oraDirObject targetdir -ftp targetuser@targethost \
-oraRepository -oraConn ttcacheadvisor@repositorydb \
-oraDirObject repositorydir -ftp repositoryuser@repositoryhost \
-ttConn "DSN=cacheadvisor;UID=cacheuser" \
-captureCursorCache 30 -flushSharedPool -task testtask

Use NFS as the network connection to transfer files between the target Oracle system, repository Oracle system, and TimesTen system. Capture a SQL workaround on the target database for a duration of 15 minutes. Evaluate the performance of all SQL statements executed in the TimesTen database and in the target database. Write the HTML report files to the /home/ttuser/cacheadvreport directory.

% ttCacheAdvisor -oraTarget -oraConn orauser@targetdb \
-oraDirObject targetdir -oraDirNfs /home/ttuser/targetdir \
-oraRepository -oraConn ttcacheadvisor@repositorydb \
-oraDirObject repositorydir -oraDirNfs /home/ttuser/repositorydir \
-ttConn "DSN=cacheadvisor;UID=cacheuser" \
-report /home/ttuser/cacheadvreport -captureCursorCache 15 -evalSqlPerf

Use NFS as the network connection to transfer files between the target Oracle system, repository Oracle system, and TimesTen system. Capture a SQL workaround on the target database for a duration of 10 minutes. Flush the shared pool on the target database before capturing the workload. Export the captured workload into a single file named cacheadvcapture.zip written to the /home/ttuser directory.

% ttCacheAdvisor -oraTarget -oraConn orauser@targetdb \
-oraDirObject targetdir -oraDirNfs /home/ttuser/targetdir \
-captureCursorCache 10 -flushSharedPool -export -zip /home/ttuser/cacheadvcapture

Use NFS as the network connection to transfer files between the target Oracle system, repository Oracle system, and TimesTen system. Import and analyze the SQL workaround from the /home/ttuser/cacheadvcapture.zip file. Write the HTML report files to the /home/ttuser/cacheadvreport directory.

% ttCacheAdvisor -oraTarget -oraConn orauser@targetdb \
-oraDirObject targetdir -oraDirNfs /home/ttuser/targetdir \
-oraRepository -oraConn ttcacheadvisor@repositorydb \
-oraDirObject repositorydir -oraDirNfs /home/ttuser/repositorydir \
-ttConn "DSN=cacheadvisor;UID=cacheuser" \
-report /home/ttuser/cacheadvreport -task temptask \
-import /home/ttuser/cacheadvcapture

ttCapture

Description

Captures information about the state of TimesTen at the time the command is used. This information may be useful in diagnosing problems. Sometimes TimesTen Customer Support must make repeated incremental requests for information to diagnose a customer's problem in the field.

The information captured by this utility may be requested by TimesTen Customer Support and may be sent with your support email.

The utility does not interpret errors. It only collects information about the state of things and sends output to the ttcapture.date.number.log file in the directory from which you invoke the ttCapture utility. This utility collects general information that is usually relevant to support cases.

Required privilege

This utility requires the instance administrator privilege.

If authentication information is not supplied in the connection string or DSN, this utility prompts for a user ID and password before continuing.

Syntax

ttCapture {-h | -help | -?}
ttCapture {-V | -version}
ttCapture [-noinstinfo] [-nosysinfo] [-stdout | -dest dir] [-logdir dir] 
 [dspath | DSN]

Options

ttCapture has the options:

Examples

To capture data on the test_db database and write the database checkpoint files to the directory D:\my_data\recover\test_db, use:

ttCapture -dest "D:\my_data\recover\test_db" test_db

Note

This utility is supported only where the TimesTen Data Manager is installed.

ttCheck

Description

Performs internal consistency checking within a TimesTen database. You can specify a specific structure to be checked and a desired level of checking.

Required privilege

This utility requires the ADMIN privilege.

If authentication information is not supplied in the connection string or DSN, this utility prompts for a user ID and password before continuing.

Syntax

ttCheck {-h | -help | -?}
ttCheck {-V | -version}
ttCheck [ [-blkDir] [-compHeap] [-header] [-heap] [-indexHeap] [-log]
[-permBlkDir] [-permHeap] [-tempBlkDir] [-tmpHeap]
[-tables tblName [...]] [-users userName [...]]
[-level levelNum] ] [...]
[-m maxErrors] [-f outFile] [-v verbosity]
{DSN | [-connstr] connection_string | dspath}

Options

ttCheck has the options:

Examples

To perform a check of all structures in the test_db database, use:

ttCheck test_db

To perform a sanity check of all structures in the test_db database, use:

ttCheck -level 1 test_db

To perform a check of all tables in the test_db database, use:

ttCheck -tables test_db

To check the physical structures and row contents of all tables in the test_db database, use:

ttCheck -tables -level 3 test_db

To perform a sanity check of all heap structures, row contents and indexes of all tables in the test_db database, use the following.

ttCheck -heap -level 1 -tables -level 4 test_db

To check the physical structures and row contents of tables tab1 and tab2 in the test_db database, use:

ttCheck -tables tab1 tab2 -level 3 test_db

Notes

While primarily intended for use by TimesTen customer support to diagnose problems with internal data structures of a TimesTen database, the information returned by ttCheck may be useful to system administrators and developers.

The ttCheck utility should be run when there are no active transactions on the system. If run on a shared database and other transactions are active, ttCheck may return errors when the database is in fact intact.

The ttCheck utility checks views in the same manner as other tables in a database. The utility cannot verify that the contents of a view matches view query's result.

If no structures are specified, ttCheck checks all structures. No errors are returned if a specified table's name or user is not found.

This utility may take some time to run. Verbosity level 2 allows you to print a progress report.

This utility is supported only where the TimesTen Data Manager is installed.

ttCWAdmin

Description

Manages TimesTen active standby pairs that take advantage of the high availability framework of Oracle Clusterware. This utility starts administrative processes, generates scripts and performs other functions to administer active standby pairs and the corresponding Clusterware resources.

For more information about using Oracle Clusterware to manage TimesTen active standby pairs, see Oracle TimesTen In-Memory Database Replication Guide.

These commands are available only with advanced high availability:

• ttCWAdmin -addMasterHosts

• ttCWAdmin -addSubscriberHosts

• ttCWAdmin -createVIPs

• ttCWAdmin -delMasterHosts

• ttCWAdmin -delSubscriberHosts

• ttCWAdmin -dropVIPs

These commands fail with basic high availability.

Required privilege

On Windows 2008, a user with Administrators privileges can execute all commands as Administrator. On other supported Windows platforms, any user that has Administrator privileges can execute all commands in this utility.

On UNIX, the root user can execute all commands in this utility. These commands must be executed by the root user:

• ttCWAdmin -addMasterHosts

• ttCWAdmin -addSubscriberHosts

• ttCWAdmin -createVIPs

• ttCWAdmin -delMasterHosts

• ttCWAdmin -delSubscriberHosts

• ttCWAdmin -ocrConfig

• ttCWAdmin -dropVIPs

The admin user can execute all other commands in this utility.

If authentication information is not supplied in the connection string or DSN, this utility prompts for a user ID and password before continuing.

Syntax

ttCWAdmin {-h | -help | -?}

ttCWAdmin {-V | -version}

ttCWAdmin -init [-hosts "host_name1, host_name2[, ...]"]

ttCWAdmin {-createVIPs | -dropVIPs | -create | -drop | -restore | -start |
           -stop | -status} [-ttclusterini path] [-dsn DSN]

ttCWAdmin -switch -dsn DSN

ttCWAdmin -relocate -dsn DSN

ttCWAdmin -ocrConfig

ttCWAdmin -beginAlterSchema -dsn DSN

ttCWAdmin -endAlterSchema -dsn DSN

ttCWAdmin -addMasterHosts [-hosts "host_name1, host_name2[, ...]"] -dsn DSN

ttCWAdmin -delMasterHosts [-hosts "host_name1, host_name2[, ...]"] -dsn DSN

ttCWAdmin -addSubscriberHosts [-hosts "host_name1, host_name2[, ...]"] -dsn DSN

ttCWAdmin -delSubscriberHosts [-hosts "host_name1, host_name2[, ...]"] -dsn DSN

ttCWAdmin -shutdown [-hosts "host_name1, host_name2[, ...]"]

Options

ttCWAdmin has these general options:

Examples

To create and start an active standby pair managed by Oracle Clusterware, using the clusterDSN DSN, enter:

ttCWAdmin -create -dsn clusterDSN
ttCWAdmin -start -dsn clusterDSN

To stop and drop an active standby pair managed by Oracle Clusterware, using the clusterDSN DSN, enter:

ttCWAdmin -stop -dsn clusterDSN
ttCWAdmin -drop -dsn clusterDSN

Notes

When you use Oracle Clusterware with TimesTen, you cannot use these commands and SQL statements:

• CREATE ACTIVE STANDBY PAIR, ALTER ACTIVE STANDBY PAIR and DROP ACTIVE STANDBY PAIR SQL statements.

• The -cacheStart and -cacheStop options of the ttAdmin utility after the active standby pair has been created.

• The -duplicate option of the ttRepAdmin utility.

• The ttRepStart and ttRepStop built-in procedures.

• Built-in procedures for managing a cache grid when the active standby pair in a cluster is a member of a grid.

• The -repStart and -repStop options of the ttAdmin utility.

In addition, do not call ttDaemonAdmin -stop before calling ttCWAdmin -shutdown.

The TimesTen integration with Oracle Clusterware accomplishes these operations with the ttCWAdmin utility and the attributes in the cluster.oracle.ini file.

ttDaemonAdmin

Description

Starts and stops the TimesTen main daemon and Server.

Required privilege

This utility requires the instance administrator privilege.

Syntax

ttDaemonAdmin {-h | -help | -?}
ttDaemonAdmin {-V | -version}
ttDaemonAdmin [-force] {-start | -stop | -restart}
ttDaemonAdmin [-startserver | -restartserver]
ttDaemonAdmin [-force] -stopserver

Options

ttDaemonAdmin has the options:

Notes

Changes to the TimesTen Server options are temporary. To permanently set or disable the TimesTen Server options, you must change the options in the ttendaemon.options file.

The -force option should be used with caution, as it may leave databases in a state where you must perform recovery procedures.

When you use this utility on Windows Vista, you must be running with Windows Administrative privileges.When you stop the daemon (ttDaemonAdmin -stop), first stop all application connections to the database. This includes stopping the replication agent and the cache agent, if they are running. This decreases startup time when the daemon is restarted. In addition, not stopping application connections or agents can result in the database becoming in validated.

If the CRS agent is running, you must stop it on the local host before stopping the TimesTen main daemon (ttDaemonAdmin -stop). If you do not stop the CRS agent, the main daemon stops temporarily with this command, but then restarts. To stop the CRS agent, use:

ttcwadmin -shutdown -hosts localhost

When you use this utility to restart the server, the TimesTen daemon reads the ttendaemon.options files to see if it has been changed since it was last read. If the file has been changed, TimesTen checks for the values of the options:

-server -serverShmIpc -serverShmSize -noserverlog

See also

For a description of all daemon options and instructions for changing the ttendaemon.options file, see "Managing TimesTen daemon options" in Oracle TimesTen In-Memory Database Operations Guide.

ttDaemonLog

Description

TimesTen uses a TimesTen daemon (referred to as the TimesTen Data Manager Service on Windows) and other background processes, known as subdaemons and agents, to manage access to the either the "user" or "error" log.

By default, TimesTen messages are stored in:

• A user error log that contains information you may need to see. Generally, these messages contain information on actions you may need to take.

• A support log containing everything in the user error log plus information of use by TimesTen Customer Support.

The ttDaemonLog utility allows you to control the type of events that are written to and fetched from the TimesTen user and error logs.

There are two versions of the ttDaemonLog utility:

• ttDaemonLog for Windows

• ttDaemonLog for UNIX

Required privilege

This utility requires the instance administrator privilege.

ttDaemonLog for Windows

On Windows, TimesTen user and error log messages are written to the Windows Application Event Log. The ttDaemonLog utility controls which events are written to and fetched from the log and displayed to stdout.

Syntax

ttDaemonLog {-h | -help | -?}
ttDaemonLog {-V | -version}
ttdaemonlog [-show type] [-b | -r | -s] [-f] [-maxlines]
[-loglevel level [DSN | [-connstr] connStr]]
[-[no]logcomponent component [DSN | [-connstr] connStr]]
[-logreset] [-msg messagestring] [-setquiet | -setverbose]
[-n computer]

Options

ttDaemonLog has the options:

Examples

By default, the ttDaemonLog utility logs messages and errors from all the TimesTen components. You can narrow the scope of what is written to the log by setting the -nologcomponent option. The -nologcomponent option can be applied to selected databases or all databases.

For example, to prevent messages and errors related to replication for all databases from being written to the log, enter:

ttDaemonLog -nologcomponent replication

To prevent messages and errors related to replication for the masterdsn database from being written to the log, enter:

ttDaemonLog -nologcomponent replication masterdsn

If, you want to prevent both replication and IMDB Cache errors and messages from being written, enter:

ttDaemonLog -nologcomponent replication
ttDaemonLog -nologcomponent cache

If, after setting a -nologcomponent option, you want to re-enable writing errors for a component, you can use the -logcomponent option. For example, if after preventing both replication and IMDB Cache errors from being logged, as shown in the example above, you want to re-enable logging of replication errors, enter:

ttDaemonLog -logcomponent replication

To re-enable logging for all TimesTen components, you can use the -logreset option:

ttDaemonLog -logreset

To display all the output from the TimesTen daemon and server on your local computer, use:

ttDaemonLog

To display the log output from the host computer named, backup1, use:

ttDaemonLog -n backup1

To write the log output to the file C:\TimesTen\logout\log1, use:

ttDaemonLog -file C:\TimesTen\logout\log1

The TimesTen Server generates a message each time an application connects to or disconnects from a client DSN if these messages were specified to be generated during installation. To display just the server log messages, use:

ttDaemonLog -show server

To display just the replication agent messages, use:

ttDaemonLog -show replication

To display just the cache agent messages, use:

ttDaemonLog -show ora

To display all messages from the TimesTen processes, use:

ttDaemonLog -show all

To restore logging to its default "verbose" level, use the -setverbose option:

ttDaemonLog -setverbose

Notes

While primarily intended for use by TimesTen customer support, this information may be useful to system administrators and developers.

This utility is supported only where the TimesTen Data Manager is installed.

To permanently set or disable verbose logging, you must change the options in the ttendaemon.options file. See "Modifying informational messages" in the Oracle TimesTen In-Memory Database Operations Guide.

ttDaemonLog for UNIX

Description

On UNIX, ttDaemonLog fetches all TimesTen events from the file generated by syslogd(1). It displays all events to stdout.

The TimesTen daemon (timestend) records its event log via syslog(2). The eventual disposition of the log information depends on the configuration of your /etc/syslog.conf file, which you can customize to log or ignore messages selectively. Messages can be logged into various files depending on the configuration of the file. These files can grow to be quite large. You should prune them periodically to conserve disk space.

Syntax

ttDaemonLog {-h | -help | -?}
ttDaemonLog {-V | -version}
ttDaemonLog [-show type] [-b | -r | -s] [-f] [-integer] [-file filename]
 [-facility name] [-loglevel level [DSN | [-connstr] connStr]] [-[no]logcomponent
 component [DSN | [-connstr] connStr]] [-logreset] [-msg string] [-setquiet | 
 -setverbose]

Options

ttDaemonLog has the options:

Examples

Except for the example with the -n option, all the examples shown under "ttDaemonLog for Windows" also apply to the UNIX version of ttDaemonLog. The following examples, show the use of some of the UNIX-specific options.

To write the log output to the file /var/adm/syslog/syslog.log, use:

ttDaemonLog -file /var/adm/syslog/syslog.log

To direct logging to the local7 facility, use.

ttDaemonLog -facility local7

Notes

While primarily intended for use by TimesTen customer support, this information may be useful to system administrators and developers.

This utility is supported only where the TimesTen Data Manager is installed.

To permanently set or disable verbose logging, you must change the options in the ttendaemon.options file. For information on this file and on configuring sys.log, see "Modifying informational messages" in the Oracle TimesTen In-Memory Database Operations Guide.

ttDestroy

Description

Destroys a database including all checkpoint files, transaction logs and daemon catalog entries (though not the DSNs).

Required privilege

This utility requires the instance administrator privilege.

Syntax

ttDestroy {-h | -help | -?}
ttDestroy {-V | -version}
ttDestroy [[-wait] [-timeout secs]] [-force] {-connStr connection_string | 
 DSN | dspath}

Options

ttDestroy has the options:

Example

ttDestroy /users/pat/TimesTen/Daily/F112697

Notes

Using ttDestroy is the only way to delete a database completely and safely. Do not remove database checkpoint or transaction log files manually.

This utility is supported only where the TimesTen Data Manager is installed.

In the case that the database to be destroyed is part of a cache grid, ttDestroy performs a detaches the database from the grid.

ttDestroy does not perform cleanup of Oracle objects from autorefresh or AWT cache groups. If there are autorefresh or AWT cache groups in the database, execute the cachecleanup.sql script to clean up the cache objects in Oracle for that particular database, to generate Oracle SQL to perform cleanup after the database has been destroyed.

ttIsql

Description

You can execute SQL statements and call TimesTen built-in procedures from ttIsql. You can execute SQL interactively from the command line. For a detailed description on running SQL from ttIsql, use the -helpfull option. In addition, you can call a TimesTen built-in procedure with call procedure-name.

The ttIsql command attempts to cancel an ongoing ODBC function when the user presses Ctrl-C.

On UNIX, this utility is supported for TimesTen Data Manager DSNs. Use ttIsqlCS for client/server DSNs.

The ttIsql utility starts with AUTOCOMMIT turned on, even when running a script. You can turn AUTOCOMMIT off and back on as necessary.

For more details on the ttIsql utility, see the chapter "Using the ttIsql Utility" in the Oracle TimesTen In-Memory Database Operations Guide

Required privilege

This utility requires no privileges.

Syntax

ttIsql {-h | -help | -? | -helpcmds | - helpfull}
ttIsql {-V | -version}
ttIsql [-f inputFile] [-v verbosity] [-e commands | sql_statement] 
[-interactive] [-N ncharEncoding] [-wait] {-connStr connection_string | DSN}

Options

ttIsql has the options:

Commands

Also see the list of ttIsql "Set/show attributes".

Boolean commands can accept the values "ON" and "OFF" in place of "1" and "0".

ttIsql has the commands:

variable array_name 
'[' array_size ']'
 data_type(n):= 
'[' value1, ... valuex ']'

Syntax for the IF-THEN-ELSE command construct

This section provides the syntax for the IF-THEN-ELSE construct. For more details on using the IF-THEN-ELSE command construct, see "Conditional control with the IF-THEN-ELSE command construct" in the Oracle TimesTen In-Memory Database Operations Guide.

IF [NOT] 
   { Literal1 | :BindVariable1 } 
   { =  | IN } 
   { Literal2 | :BindVariable2 | SelectStatement } 
 THEN "ThenCommands" 
 [ ELSE "ElseCommands" ] ;

Restrictions for the IF-THEN-ELSE construct are as follows:

• You cannot compare variables of the LOB data type.

• The values are compared case-sensitive with strcmp. A character padded value might not match a VARCHAR2 because of the padding.

Syntax for the WHENEVER SQLERROR command

Execute the WHENEVER SQLERROR command to prescribe what to do when a SQL error occurs. For more details and examples on how to use the WHENEVER SQLERROR command, see "Error recovery with the WHENEVER SQLERROR" command in the Oracle TimesTen In-Memory Database Operations Guide.

WHENEVER SQLERROR { ExitClause | ContinueClause | SUPPRESS |
     SLEEP Number | ExecuteClause }

When you specify EXIT, always exit ttIsql if an error occurs. ExitClause is as follows:

EXIT [ SUCCESS | FAILURE | WARNING | Number | :BindVariable ]
 [ COMMIT | COMMIT ALL | ROLLBACK ]

When you specify CONTINUE, ttIsql continues to the next command, even if an error occurs. ContinueClause is as follows:

CONTINUE [ COMMIT | COMMIT ALL | ROLLBACK | NONE ]

Execute specified commands before continuing. ExecuteClause is as follows:

EXECUTE "Cmd1;Cmd2;...;"

The WHENEVER SQLERROR command options are as follows:

• EXIT: Always exit ttIsql if an error occurs. Specify what is performed before ttIsql exits with one of the following. SUCCESS is the default option for EXIT.

  • SUCCESS or FAILURE or WARNING: Return SUCCESS (value 0), FAILURE (value 1), or WARNING (value 2) to the operating system after ttIsql exits for any SQL error.

  • Number: Specify a number from 0 to 255 that is returned to the operating system as a return code. Once ttIsql exits, you can retrieve the error return code with the appropriate operating system commands. For example, use echo $status in the C shell (csh) or echo $? in the Bourne shell (sh) to display the return code.

    The return code can be retrieved and processed within batch command files to programmatically detect and respond to unexpected events.

  • :BindVariable: Returns the value in a bind variable that was previously created in ttIsql with the variable command. The value of the variable at the time of the error is returned to the operating system in the same manner as the Number option.

    Note:

    The bind variable used within the WHENEVER SQLERROR command cannot be defined as a LOB, REFCURSOR, or any array data type.

  In addition, you can specify whether to commit or rollback all changes before exiting ttIsql.

  • COMMIT: Executes a COMMIT and saves changes only in the current connection before exiting. The other connections exit with the normal disconnect processing, which rolls back any uncommitted changes.

  • COMMIT ALL: Executes a COMMIT and saves changes in all connections before exiting.

  • ROLLBACK: Before exiting, executes a ROLLBACK and abandons changes in the current connection and, by default, in all other connections. The other connections exit with the normal disconnect processing, which automatically rolls back any uncommitted changes.

• CONTINUE: Do not exit if an error occurs. The SQL error is displayed, but the error does not cause ttIsql to exit. The following options enable you to specify what is done before continuing to the next ttIsql command:

  • NONE: This is the default. Take no action before continuing.

  • COMMIT: Executes a COMMIT and saves changes in the current connection before continuing.

  • COMMIT ALL: Executes a COMMIT and saves changes in all connections before continuing.

  • ROLLBACK: Before continuing, executes a ROLLBACK and abandons changes in the current connection and, by default, in all other connections. The other connections exit with the normal disconnect processing, which automatically rolls back any uncommitted changes.

• SUPPRESS: Do not show any error messages and continue.

• SLEEP: Sleep for a specified number of seconds before continuing.

• EXECUTE: Execute specified commands before continuing. Each command is separated from the other commands by a semicolon (;). If any command triggers additional errors, those errors may cause additional actions that could potentially result in a looping condition.

Set/show attributes

Also see the list of ttIsql "Commands". Some commands appear here as attributes of the set command. In that case, they can be used with or without the set command.

Boolean attributes can accept the values "ON" and "OFF" in place of "1" and "0".

ttIsql set supports these attributes:

Comment syntax

The types of comment markers are:

# [comment_text]
-- [comment_text]
/* [comment_text] */

The C-style comments ( /* [comment_text] */) can span multiple lines.

The comments delimited by the

#

and the

-

characters should not span multiple lines. If a comment marker is encountered while processing a line, then the remainder of the line is ignored.

'--' at the beginning of a line is considered a SQL comment. The line is considered a comment and no part of the line is included in the processing of the SQL statement. A line that begins with '--+' is interpreted as a segment of a SQL statement.

The comment markers can work in the middle of a line.

Example:

monitor; /*this is a comment after a ttIsql command*/

Command history

ttIsql implements a csh-like command history.

Command Usage: history [-r] [num_commands]

Description: Lists previously executed commands. The num_commands parameter specifies the number of commands to list. If the -r parameter is specified, commands are listed in reverse order.

Command Usage: ! [command_id|command_string| !]

Description: Executes a command in the history list. If a command_id argument is specified, the command in the history list associated with this ID is executed again. If the command_string argument is specified, the most recent command in the history list that begins with command_string is executed again. If the ! argument is specified then the most recently executed command is executed again.

Example: "!!;" -or- "!10;" -or- "!con;"

Also see the clearhistory, history, savehistory commands.

Command shortcuts

By default, ttIsql supports keystroke shortcuts when entering commands. To turn this feature off, use:

Command> set editline=0;

The bindings available are:

Parameters

With dynamic parameters, you are prompted for input for each parameter on a separate line. Values for parameters are specified the same way literals are specified in SQL.

SQL_TIMESTAMP columns can be added using dynamic parameters. (For example, values like '1998-09-08 12:1212').

Parameter values must be terminated with a semicolon character.

The possible types of values that can be entered are:

• Numeric literals. Example: 1234.5

• Time, date or timestamp literals within single quotation marks. Examples:

  '12:30:00''2000-10-29''2000-10-29 12:30:00''2000-10-29 12:30:00.123456'
  

• Unicode string literals within single quotation marks preceded by 'N'. Example: N'abc'

• A NULL value. Example: NULL

• The '*' character which indicates that the parameter input process should be stopped. Example: *

• The '?' character prints the parameter input help information. Example: ?

Example parameters of command string substitution

Command> select * from dual where :a > 100 and :b < 100;
Type '?' for help on entering parameter values.
Type '*' to end prompting and abort the command.
Type '-' to leave the parameter unbound.
Type '/;' to leave the remaining parameters unbound and execute the command.

Enter Parameter 1 'A' (NUMBER) > 110
Enter Parameter 2 'B' (NUMBER) > 99
< X >
1 row found.
Command> var a number;
Command> exec :a := 110;

PL/SQL procedure successfully completed.

Command> print a
A                    : 110
Command> var b number;
Command> exec :b := 99;

PL/SQL procedure successfully completed.

Command> select * from dual where :a > 100 and :b < 100;
< X >
1 row found.
Command> print
A                    : 110
B                    : 99
Command> select * from dual where :a > 100 and :b < 100 and :c > 0;
Enter Parameter 3 'C' (NUMBER) > 1
< X >
1 row found.
Command>

Default options

You can set the default command-line options by exporting an environment variable called TTISQL. The value of the TTISQL environment variable is a string with the same syntax requirements as the TTISQL command line. If the same option is present in the TTISQL environment variable and the command line then the command line version always takes precedence.

Examples

Execute commands from ttIsql.inp.

ttIsql -f ttIsql.inp

Enable all output. Connect to DSN RunData and create the database if it does not exist.

ttIsql -v 4 -connStr "DSN=RunData;AutoCreate=1"

Print the interactive commands.

ttIsql -helpcmds

Print the full help text.

ttIsql -helpfull

Display the setting for all ttIsql set/show attributes:

Command> show all; 
Connection independent attribute values: 

autoprint = 0 (OFF)
columnlabels = 0 (OFF)
define = 0 (OFF)
echo 1 (ON)
FEEDBACK ON
multipleconnections =0 (OFF)
ncharencoding = LOCALE (US7ASCII)
prompt = 'COMMAND>'
timing = 0 (OFF)
verbosity = 2
vertrical = 0 (OFF)

Connection specific attribute values:

autocommit = 1 (ON)
Client timeout = 0
Connection String DSN=repdb1_1121;UID=timesten; DataStore=/DS/repdb1_1121;
 DatabaseCharacterSet=AL32UTF8; ConnectionCharacterSet=US7ASCII;
 DRIVER=/opt/TimesTen/tt1122/lib/libtten.so; PermSize=20;TempSize=20;TypeMode=1; 
No errors.
isolation = READ_COMMITTED
Prefetch count = 5
Query threshold = 0 seconds (no threshold)
Query timeout = 0 seconds (no timeout)
serveroutput OFF

Current Optimizer Settings:
    Scan: 1
    Hash: 1
    Range: 1
    TmpHash: 1
    TmpTable: 1
    NestedLoop: 1
    MergeJoin: 1
    GenPlan: 0
    TblLock: 1
    RowLock: 1
    Rowid: 1
    FirstRow: 1
    IndexedOr: 1
    PassThrough: 0
    BranchAndBound: 1
    ForceCompile: 0
    CrViewSemCheck: 1
    ShowJoinOrder: 0
    CrViewSemCheck: 1
    UserBoyerMooreStringSearch: 0
    DynamicLoadEnable: 1
    DynamicLoadErrorMode: 0
    NoRemRowIdOpt: 0

Current Join Order:
    <>

Command

Prepare and exec an SQL statement.

ttIsql (c) 1996-2011, TimesTen, Inc. All rights reserved.
ttIsql -connStr "DSN=RunData"
Type ? or "help" for help, type "exit" to quit ttIsql.
(Default setting AutoCommit=1)
Command> prepare 1 SELECT * FROM my_table;
Command> exec 1;
Command> fetchall;

Example vertical command:

Command> call ttlogholds;
< 0, 265352, Checkpoint , DS.ds0 >
< 0, 265408, Checkpoint , DS.ds1 >
2 rows found.
Command> vertical call ttlogholds;

 HOLDLFN:       0

 HOLDLFO:       265352
 TYPE:          Checkpoint
 DESCRIPTION:   DS.ds0
 HOLDLFN:       0

 HOLDLFO:       265408
 TYPE:          Checkpoint
 DESCRIPTION:   DS.ds1
 2 rows found.

Command>

To create a new user, use single quotes around the password name for an internal user:

ttIsql -connStr "DSN=RunData"
ttIsql (c) 1996-2000, TimesTen, Inc. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.
(Default setting AutoCommit=1)
Command> CREATE USER terry IDENDTIFIED BY `secret';

To delete the XLA bookmark mybookmark, use:

ttIsql -connStr "DSN=RunData"
ttIsql (c) 1996-2000, TimesTen, Inc. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql. (Default setting
AutoCommit=1) 
Command> xlabookmarkdelete;
XLA Bookmark: mybookmark
 Read Log File:  0
 Read Offset:    268288
 Purge Log File: 0
 Purge Offset:   268288
 PID:            2004
 In Use:         No
1 bookmark found.

Command> xlabookmarkdelete mybookmark;

Command> xlabookmarkdelete;

0 bookmarks found.

Example of managing XLA bookmarks

You can use the xlabookmarkdelete command to both check the status of the current XLA bookmarks and delete them. This command requires XLA privilege or object ownership.

For example, when running the XLA application, 'xlaSimple', you can check the bookmark status by entering:

Command> xlabookmarkdelete;

XLA Bookmark: xlaSimple
  Read Log File: 0
  Read Offset: 630000
  Purge Log File: 0
  Purge Offset: 629960
  PID: 2808
  In Use: No
1 bookmark found.

To delete the bookmark, enter:

Command> xlabookmarkdelete xlaSimple;
Command>

Example parameters using "variable" and "print"

Substitution in ttIsql is modeled after substitution in SQL*Plus. The substitution feature is enabled by set define on or set define substitution_char'. The substitution character when the user specifies 'on' is '&'. It is disabled with 'set define off'.By default, substitution is off. The default is off because the & choice for substitution character conflicts with TimesTen's use of ampersand as the BIT AND operator.When enabled, the alphanumeric identifier following the substitution character is replace by the value assigned to that identifier. When disabled, the expansion is not performed.New definitions can be defined even when substitution is off. You can use the define command to list the definitions ttIsql predefines.

Command> show define
define = 0 (OFF)
Command> define
DEFINE            _PID = "9042" (CHAR)
DEFINE      _O_VERSION = "TimesTen Release 11.2.1.0.0" (CHAR)
Command> select '&_O_VERSION' from dual;
< &_O_VERSION >
1 row found.
Command> set define on
Command> SELECT '&_O_VERSION' FROM DUAL;
< TimesTen Release 11.2.1.0.0 >
1 row found.

If the value is not defined, ttIsql prompts you for the value.When prompting and only one substitution character is used before the identifier, the identifier is defined only for the life of the one statement.If two substitution characters are used and the value is prompted, it acts as if you have explicitly defined the identifier.

Command> SELECT '&a' FROM DUAL;
Enter value for a> hi
< hi >
1 row found.
Command> define a
symbol a is UNDEFINED
The command failed.
Command> SELECT '&&a' FROM DUAL;
Enter value for a> hi there
< hi there >
1 row found.
Command> define a
DEFINE               a = "hi there" (CHAR)

Additional definitions are created with the define command:

Command> define tblname = sys.dual
Command> define tblname
DEFINE         tblname = "sys.dual" (CHAR)
Command> select * from &tblname;
< X >
1 row found.

Arguments to the run command are automatically defined to '&1', '&2', ... when you add them to the run or @ (and @@) commands:Given this script:

CREATE TABLE &1 ( a INT PRIMARY KEY, b CHAR(10) );
INSERT INTO &1 VALUES (1, '&2');
INSERT INTO &1 VALUES (2, '&3');SELECT * FROM &1;

Use the script:

Command> SET DEFINE ON
Command> @POPULATE mytable Joe Bob;

CREATE TABLE &1 ( a INT PRIMARY KEY, b CHAR(10) );
INSERT INTO &1 VALUES (1, '&2');
1 row inserted.

INSERT INTO &1 VALUES (2, '&3');
1 row inserted.

SELECT * FROM &1;
< 1, Joe        >
< 2, Bob        >
2 rows found.
Command>

This example uses the variable command. It deletes an employee from the employee table. Declare empid and name as variables with the same data types as employee_id and last_name. Delete the row, returning employee_id and last_name into the variables. Verify that the correct row was deleted.

Command> VARIABLE empid NUMBER(6) NOT NULL;
Command> VARIABLE name VARCHAR2(25) INLINE NOT NULL;
Command> DELETE FROM employees WHERE last_name='Ernst'
       > RETURNING employee_id, last_name INTO :empid,:name;
1 row deleted.
Command> PRINT empid name;
EMPID                : 104
NAME                 : Ernst

Notes

The ttIsql utility supports only generic REF CURSOR variables, not specific REF CURSOR types.

Multiple ttIsql commands are allowed per line separated by semicolons.

The ttIsql utility command line accepts multiline PL/SQL statements, such as anonymous blocks, that are terminated with the "/" on it's own line. For example:

Command> set serveroutput on
Command> BEGIN
> dbms_ouput.put_line ('Hi There');
> END;
>/
Hi There

PL/SQL block successfully executed.

Command>

For UTF-8, NCHAR values are converted to UTF-8 encoding and then output.

For ASCII, those NCHAR values that correspond to ASCII characters are output as ASCII. For those NCHAR values outside of the ASCII range, the escaped Unicode format is used. For example:

U+3042 HIRAGANA LETTER A

is output as

Command> SELECT c1 FROM t1;
< a\u3042 >

NCHAR parameters must be entered as ASCII N-quoted literals:

Command> prepare SELECT * FROM t1 WHERE c1 = ?; 
Command> exec;

Type '?;' for help on entering parameter values. Type '*;' to abort the parameter entry process.

Enter Parameter 1> N'XY';

On Windows, this utility is supported for all TimesTen Data Manager and Client DSNs.

ttMigrate

Description

Performs one of these operations:

• Saves a migrate object from a TimesTen database into a binary datafile.

• Restores the migrate object from the binary datafile into a TimesTen database.

• Examines the contents of a binary datafile created by this utility.

Migrated objects include:

• Tables

• Cache group definitions

• Views and materialized views

• Materialized view log definitions

• Sequences

• Replication schemes

The ttMigrate utility is used when upgrading major release versions of TimesTen, since database checkpoint and log files are not compatible between major releases. For an example, see the Oracle TimesTen In-Memory Database Installation Guide.

When you migrate a database into Release 11.2.1 from a previous release, users and user privileges are not migrated. When you migrate a database between releases of Release 11.2.1 or into a release later than Release 11.2.1, users and user privileges are migrated.

Binary files produced by this utility are platform-dependent. For example a binary file produced on Windows must be restored on Windows. Use the ttBulkCp or ttMigrateCS (UNIX only) utility to copy data between platforms.

The ttMigrate utility can be used to copy data between bit-levels within the same architecture. For example, it can be used to move data from a 32-bit Solaris system to a 64-bit Solaris system. The -relaxedUpgrade option must be used when changing bit-levels and the database should not be involved in a replication scheme, in this case.

On UNIX, this utility is supported for TimesTen Data Manager DSNs. For TimesTen Client DSNs, use the utility ttMigrateCS.

Required privilege

This utility requires various privileges depending on the options specified. In general, a user must be the instance administrator or have the ADMIN privilege to use this utility.

Using the -r option requires the instance administrator privilege, as it generally creates a database. If the database has been created at the time this option is used, it requires CREATE ANY TABLE, CREATE ANY SEQUENCE, CREATE ANY VIEW, CREATE ANY MATERIALIZED VIEW, CREATE ANY CACHE GROUP, CREATE ANY INDEX privileges and ADMIN if autocreation of users is necessary. If the database is involved in replication or IMDB Cache, then CACHE_MANAGER is also required.

Using the -c option to capture an entire database requires the ADMIN privilege. If the database is involved in replication or IMDB Cache, then CACHE_MANAGER is also required. Using the -c option to capture a subset of the database objects (tables, views, materialized views, cache groups, sequences) requires SELECT ANY TABLE and SELECT ANY SEQUENCE privileges.

Syntax

ttMigrate {-h | -help | -?}

ttMigrate {-V | -version}

To create or append a binary datafile, use:

ttMigrate {-a | -c} [-v verbosity] [-nf] [-nr] [-fixNaN] [-saveAsCharset charset]
[-exactUpgrade | -relaxedUpgrade] 
[-convertTypesToOra | -convertTypestoTT]] 
{-connStr connection_string | DSN} dataFile [objectOwner.]objectName

To restore a database from a binary datafile created by this utility, use:

ttMigrate -r -relaxedUpgrade  [-numThreads n] [-inline rule] [-v verbosity]
[-fixNaN] [-nf] [-nr] [-C chkPtFreq] [-localhosthostName] 
[-n noCharsetConversion] [-cacheUid uid [-cachePwd pwd]]
[-convertCGtypes]  [-autorefreshPaused] [-convertTypesToOra | -convertTypesToTT]  
[-noAutoCreateUsers] -connStr connection_string | DSN} dataFile 
[[objectOwner.]objectName]

or

ttMigrate -r -exactUpgrade  [-numThreads n] [-vverbosity] [-fixNaN] [-nf] [-nr] 
[-C chkPtFreq] [-localhost hostName] [-noCharsetConversion]
[-cacheUid uid [-cachePwd pwd]] [-convertCGtypes]
[-updateStats | -estimateStats percentRows] {-connStr connection_string | DSN}
dataFile [[objectOwner.]objectName]

To list or display the contents of a binary datafile created by this utility, use:

ttMigrate {-l | -L | -d | -D} dataFile [[objectowner.]name ...]

Options

ttMigrate has the options:.

Modes

Create mode (-c) and Append mode (-a)

In Create mode, ttMigrate saves migrate objects from a TimesTen database into a new binary datafile. If the datafile does not exist, ttMigrate creates it. Otherwise, ttMigrate overwrites the existing file, destroying its contents.

The datafile format used by ttMigrate is independent of any release of TimesTen, so it is possible to use ttMigrate to migrate data from one TimesTen release to another.

In Append mode, ttMigrate appends migrate objects from a TimesTen database to an existing datafile. If the datafile does not exist, ttMigrate creates it.

For each ordinary (non-cached) table, ttMigrate saves:

• The table description: the name and type of each of the table's columns, including primary key and nullability information.

• The table's index definitions: the name of each index and the columns contained in the index. The actual contents of the index are not saved; ttMigrate only saves the information needed to rebuild the index when the table is restored.

• The table's foreign key definitions. You can disable the saving of foreign key definitions using the -nf option.

• The rows of the table. You can disable the saving of rows using the -nr option.

For each cache group, ttMigrate saves the following:

• The cache group definition: the cache group owner and name, the names of all tables in the cache group and any relevant cache group settings, such as the cache group duration.

  Note:

  After ttMigrate has been used to restore a database, all autorefresh cache groups in the restored database have AUTOREFRESH state set to OFF, no matter how it was set on the source database. After restoring a cache group with ttMigrate -r, reset its AUTOREFRESH STATE to ON by using the ALTER CACHE GROUP statement (this can be done programmatically or with the ttIsql utility.

• All the cached tables in the cache group: the table name, column information, table attributes (propagate or read-only), WHERE clause, if any, foreign key definitions and index definitions.

For each view, ttMigrate saves the following:

• All the same information as a normal table.

• The query defining the view.

For each sequence, ttMigrate saves the following:

• The complete definition of the sequence.

• The sequence's current value.

For each user (except the instance administrator), ttMigrate saves the following:

• User name.

• The user's encrypted password.

• Privileges that have been granted to the user.

For PUBLIC, ttMigrate saves all privileges that have been granted to PUBLIC after database creation.

If there are any replication schemes defined, ttMigrate saves all the of the TTREP tables containing the replication schemes. Replication schemes should have names that are unique from all other database objects. It is not possible to migrate a replication scheme with the same name as any other database object.

By default, ttMigrate saves all database objects and users in the database to the datafile, including tables, views, cache groups, sequences, users and replication schemes. Alternatively, you can give a list of database objects to be saved on the command line, except for replication schemes. The names in this list can contain the wildcard characters % (which matches one or more characters) and _ (which matches a single character). ttMigrate saves all database objects that match any of the given patterns. You do not need to be fully qualify names: If a name is given with no owner, ttMigrate saves all database objects that match the specified name or pattern, regardless of their owners.

You cannot save cached tables independently of their cache groups. If you list a cached table on the command line without also listing the corresponding cache group ttMigrate issues an error.

Use the -v option to control the information that ttMigrate prints while the save is in progress.

Restore mode (-r)

In Restore mode, ttMigrate restores all database objects from a datafile into a TimesTen database.

For each ordinary (non-cached) table, ttMigrate restores:

• The table, using the original owner, table name, column names, types and nullability and the original primary key.

• The table's foreign keys. You can use the -nf flag to disable the restoration of foreign keys.

• All indexes on the table.

• All rows of the table. You can use the -nr flag to disable the restoration of rows.

For each cache group, ttMigrate restores:

• The cache group definition, using the original cache group owner and name.

• Each cached table in the cache group, using the original table names, column names, types and nullability, the original primary key, the table attributes (PROPAGATE or READONLY), and the WHERE clause, if any.

• The foreign key definitions of the cached tables.

• All the indexes on the cached tables.

  Note:

  The ttMigrate utility does not restore the rows of cached tables, even if you have not specified the -nr option. The foreign key definitions of the cached tables are always restored, regardless of the use of the -nf option, as they are needed to maintain the integrity of the cache group.

By default, the -exactUpgrade option is set during restore.

By default, ttMigrate restores all tables and cache groups in the datafile. Alternatively, you can list specific tables and cache groups to be restored on the command line. The names in this list must be fully qualified and cannot use wildcard characters.

You cannot restore cached tables independently of their cache groups. If you list a cached table on the command line without also listing the corresponding cache group, then ttMigrate issues an error.

Use the -v option to control the information that ttMigrate prints while the restoration is in progress.

The -inline option may be used to control whether variable length columns are restored as INLINE or NOT INLINE. See "Type specifications" in Oracle TimesTen In-Memory Database SQL Reference. In the default mode, -inlinepreserve, ttMigrate restores all variable-length columns with the same INLINE or NOT INLINE setting with which they were saved. In the other two modes, -inlinedsDefault and -inlinemaxlen, ttMigrate restores variable-length columns equal to or shorter than a threshold length as INLINE, and restores all other variable length columns as NOT INLINE. For-inlinedsDefault, this threshold is the default automatic INLINE length for a TimesTen database. The -inlinemaxlen mode restores variable length columns with a user-specified threshold length of maxlen as INLINE, and all other variable length columns as NOT INLINE, even if they were saved as INLINE. If maxlen is 0, then all variable-length columns are restored as NOT INLINE.

List mode (-l) and Long-list mode (-L)

In List mode, ttMigrate lists the names of database objects in the specified datafile, including cached tables and the replication scheme TTREP tables.

In Long-list mode, ttMigrate lists the names of database objects in the datafile, including cached tables and the replication scheme TTREP tables, along with the number of rows in each table and the index definitions for each table, the query defining each view and the specifications for each sequence.

By default, ttMigrate lists the replication scheme name and all the database objects in the file. Alternatively you can provide a list of names of database objects on the command line. The names in this list must be fully qualified and cannot use wildcard characters.

Describe mode (-d)

In Describe mode, ttMigrate gives a short description for database objects in the specified file.

For each table, ttMigrate lists the table name, the number of rows in the table, and the table's column definitions, primary key and foreign keys. For cached tables, ttMigrate also lists the table attributes (PROPAGATE or READONLY) and the table's WHERE clause, if any.

For views, ttMigrate also lists the query defining the view.

For cache groups, ttMigrate lists the cache group name, the number of tables in the cache group, the cache group duration and describes each cached table in the cache group.

For replication schemes, ttMigrate lists the replication scheme name and all the TTREP replication scheme tables in the same manner as user tables.

By default, ttMigrate describes all the database objects in the file. Alternatively, you can provide a list of names of database objects on the command line. The names in this list must be fully qualified and cannot use wildcard characters.

Long-describe mode (-D)

In Long-describe mode, ttMigrate gives a full description for database objects in the specified file.

For each table, ttMigrate lists the table's name and the number of rows in the table, the table's column definitions, primary key, foreign keys and index definitions. For cached tables, ttMigrate also lists the table attributes (PROPAGATE or READONLY) and the table's WHERE clause, if any.

For cache groups, ttMigrate lists the cache group name, the number of tables in the cache group, the cache group duration and describes each cached table in the cache group.

For sequences, ttMigrate lists all the values used to define the sequence and its current value.

For replication schemes, ttMigrate lists all the TTREP replication scheme tables in the same manner as user tables.

By default, ttMigrate describes all of database objects in the file. Alternatively, you can provide a list of names of database objects on the command line. The names in this list must be fully qualified and cannot use wildcard characters.

TimesTen to Oracle data type conversions

Both TimesTen and Oracle data types are supported in TimesTen 11.2.2 When migrating a database from an earlier version of TimesTen to TimesTen release 11.2.2, you can convert the data types in your database to the default Oracle type mode. This is not required, however.

In replication, the type mode must be the same on both sides of the replication scheme. Therefore you cannot convert the data types as part of an online upgrade, as TimesTen releases previous to 11.2.2 do not support Oracle data types.

To convert from TimesTen data types to Oracle data types, use the -convertTypesToOra option.

The -convertTypesToOra option instructs ttMigrate to make the following type conversions as it saves or restores tables:

For information on data types, see "Data Types" in the Oracle TimesTen In-Memory Database SQL Reference.

Oracle to TimesTen data type conversions

When migrating tables backward from TimesTen release 11.2.2 to an earlier version of TimesTen, you may need to convert Oracle data types to TimesTen data types, as the Oracle data types were not supported in releases before 11.2.2.

To convert from Oracle data types to TimesTen data types, use the -convertTypesToTT option.

The -convertTypesToTT option instructs the ttMigrate utility to make the following type conversions as it saves or restores tables:

For information on data types, see "Data Types" in the Oracle TimesTen In-Memory Database SQL Reference.

Cache group data type conversions

When restoring a database that contains cache groups from a TimesTen release that is earlier than 7.0, use the -convertCGTypes. option to convert the data type of columns from pre-7.0 types to more clearly map with the data types of the columns in the Oracle database with which the cache group is associated.

The following table describes the type mapping.

For information on data types, see "Data Types" in Oracle TimesTen In-Memory Database SQL Reference and "Mappings between Oracle and TimesTen data types" in Oracle In-Memory Database Cache User's Guide.

Return codes

The ttMigrate utility restore (-r) and create (-c) commands return the following exit codes:

0 - All objects were successfully created or restored.

1 - Some objects successfully created or restored. Some objects could not be created or restored due to errors.

2 - Fatal error, for example, could not connect or could not open the data file.

3 - CTRL-C or another signal received during the create or restore operation.

Examples

The following command dumps all database objects from database SalesDS into a file called sales.ttm. If sales.ttm exists, ttMigrate overwrites it.

ttMigrate -c DSN=SalesDS sales.ttm

This command appends all database objects in the SalesDS database owned by user MARY to sales.ttm:

ttMigrate -a DSN=SalesDS sales.ttm MARY.%

This command restores all database objects from sales.ttm into the SalesDS database:

ttMigrate -r DSN=SalesDS sales.ttm

This command restores MARY.PENDING and MARY.COMPLETED from sales.ttm into SalesDS (case is ignored in migrate objects):

ttMigrate -r DSN=SalesDS sales.ttm MARY.PENDINGMARY.COMPLETED

This command lists all migrate objects saved in sales.ttm:

ttMigrate -l sales.ttm

Notes

When migrating backward into a release of the Oracle TimesTen In-Memory Database that does not support features in the current release, TimesTen generally issues a warning and continues without migrating the unsupported features. In a few cases, where objects have undergone conversion, ttMigrate may fail and return an error message. This may be the case with conversions of data types, character sets and primary key representation.

The following restrictions, limitations and suggestions should be considered before preparing to use ttMigrate.

Asynchronous materialized view: When migrating to a previous release, asynchronous materialized views are ignored and TimesTen returns a warning.

Cache groups: In restore mode, the presence of foreign key dependencies between tables may require ttMigrate to reorder tables to ensure that a child table is not restored before a parent table.

When migrating databases that contain cache groups from a previous release of TimesTen to TimesTen 7.0 or greater, you must use the option -convertTypesToOra. See "Cache group data type conversions" for a description of the data type mapping.

Character columns in cached tables must have not only the same length but also the same byte semantics as the underlying Oracle tables. Cache group migration fails when there is a mismatch in the length or length semantics of any of its cached tables.

The connection attribute PassThrough with a non-zero value is not supported with this utility and will return an error.

Character sets: By default, ttMigrate stores table data in the database character set, unless you have specified the -saveAsCharset option. At restore time, conversion to another character set can be achieved by migrating the table into a database that has a different database character set. When migrating data from a release of TimesTen that is earlier than 7.0, TimesTen assumes that the data is in the target database's character set. If the data is not in the same database character set as the target database, the data may not be restored correctly.

When migrating columns with BYTE length semantics between two databases that both support NLS but with different database character sets, it is possible for migration to fail if the columns in the new database are not large enough to hold the values in the migrate file. This could happen, for example, if the source database uses a character set whose maximum byte-length is 4 and the destination database uses a character set whose maximum byte-length is 2.

TimesTen issues a warning whenever character set conversion takes place to alert you to the possibility of data loss due to conversion.

Data type conversions: When migrating data from a pre-7.0 release of TimesTen, you must explicitly request data type conversions, using either the -convertTypesToOra or the -convertTypesToTT options.

ttMigrate saves the length semantic annotation (BYTE or CHAR) of CHAR and VARCHAR columns and restores these annotations when restoring into TimesTen releases that support them. When migrating backward into a TimesTen release that does not support these annotations, columns with CHAR length semantics are converted to BYTE length, but their lengths are adjusted to match the byte length of the original columns. When migrating forward from a release that does not support these annotations, BYTE length semantics are used.

Foreign key dependencies: In restore mode, the presence of foreign key dependencies between tables may require ttMigrate to reorder tables to ensure that a child table is not restored before any of its parents. Such dependencies can also prevent a child table from being restored if any of its parent tables were not restored. For example, when restoring a table A that has a foreign key dependency on a table B, ttMigrate first checks to verify that table B exists in the database. If table B is not found, ttMigrate delays the restoration of table A until table B is restored. If table B is not restored as part of the ttMigrate session, TimesTen prints an error message indicating that table A could not be restored due to an unresolved dependency.

Indexes: TimesTen supports range indexes as primary-key indexes into TimesTen releases that support this feature. When migrating backward into a release that does not support range indexes as primary-key indexes, the primary keys are restored as hash indexes of the default size. When migrating forward from a release that does not support range indexes as primary-key indexes, the primary keys are restored as hash indexes of the same size as the original index.

TimesTen also supports bitmap indexes. When migrating backward into a release that does not support bitmap indexes, ttMigrate converts the bitmap indexes to range indexes.

INLINE columns: When migrating TimesTen tables that contain INLINE variable length columns to a release of TimesTen that is earlier than 5.1, you must explicitly use the -relaxedUpgrade option. Using the default -exactUpgrade option results in an error. The INLINE column attributes are maintained, unless you specify otherwise using the -inline option.

Materialized view logs: TimesTen does not save the content of materialized view logs, only the definition.

Replication: Before attempting a full store migrate of replicated stores, make sure the host name and database name are the same for both the source and destination databases.

System views: TimesTen does not save the definitions or content of system vies during migration.

Other considerations: Because ttMigrate uses a binary format, you cannot use ttMigrate to:

• Migrate databases between hardware platforms.

• Restore data saved with ttBackup or use ttBackup to restore data saved with ttMigrate.

Platforms: You can use ttMigrate to migrate databases between 32- and 64-bit platforms if the two platforms are otherwise exactly the same, including having the same chipset. Follow the examples in the Oracle TimesTen In-Memory Database Installation Guide.

• On Windows, you can use ttMigrate to access databases from any release of TimesTen. On Windows, this utility is supported for all TimesTen Data Manager and Client DSNs.

• On UNIX, the release of ttMigrate must match the release of the database you are connecting to.

It is recommended that you do not run DDL SQL commands while running ttMigrate to avoid lock contention issues for your application.

See also

ttmodinstall

Description

Modifies specified settings for an installation.

Required privilege

This utility requires the instance administrator privilege.

Syntax

ttmodinstall {-h | -help | -?}
ttmodinstall {-V | -version}
ttmodinstall -port portNumber
ttmodinstall -tns_admin path
ttmodinstall -enablePLSQL 
ttmodinstall -crs

Options

ttmodinstall has the options:

Examples

To change the port number of the TimesTen instance to 12345, use:

ttmodinstall -port 12345

Notes

You must shut down all TimesTen operations to use this utility. This utility stops and then restarts the TimesTen daemon before making any changes to the instance.

ttRepAdmin

Description

Displays existing replication definitions and monitors replication status. The ttRepAdmin utility is also used when upgrading to a new release of TimesTen, as described in Oracle TimesTen In-Memory Database Installation Guide.

Required privilege

This utility requires the ADMIN privilege.

Syntax

ttRepAdmin {-h | -help | -?}
ttRepadmin {-V | -version}
ttRepAdmin -self -list [-scheme [owner.]schemeName]
       {DSN | -connStr connectionString}

ttRepAdmin -receiver [-name receiverName]
      [-host receiverHostName] [-state receiverState] [-reset]
      [-list] [-scheme [owner.]schemeName]
      {DSN | -connStr connectionString} 

ttRepAdmin -log {DSN | -connStr connectionString}

ttRepAdmin -showstatus {-awtmoninfo} {DSN | -connStr connectionString}

ttRepAdmin -showconfig {DSN | -connStr connectionString}

ttRepAdmin -bookmark {DSN | -connStr connectionString}

ttRepAdmin -wait [-name receiverName] [-host receiverHostName] 
      [-timeout seconds] {DSN | -connStr connectionString}

ttRepAdmin -duplicate -from srcDataStoreName
      -host srcDataStoreHost
      [-localIP localIPAddress] [-remoteIP remoteIPAddress]
      [-setMasterRepStart] [-ramLoad] [-delXla]
      [-UID userId] [-PWD pwd | -PWDCrypt encryptedPwd]
      [-drop { [owner.]table ... | [owner.]sequence |ALL }]
      [-truncate { [owner.]table ... | ALL }]
      [-compression 0 | 1] [-bandwidthmax maxKbytesPerSec]
      [-initCacheDr [-noDRTruncate][-nThreads]]
      [-keepCG [-cacheUid cacheUid [-cachePwd cachePwd]] 
         [-recoveringNode | -deferCacheUpdate]
      | -nokeepCG]
      [-remoteDaemonPort portNo] [-verbosity {0|1|2}]
      [-localhost localHostName]
      {destDSN | -connStr connectionString}

ttRepAdmin operations

The ttRepAdmin utility is used for many replication operations. These operations fall into the following categories:

• Help and version information

• Database information

• Subscriber database operations

• Duplicate a database

• Wait for updates to complete

• Replication status

Help and version information

Use this form of ttRepAdmin to obtain help and the current version of TimesTen.

ttRepAdmin {-h | -help | -?}
ttRepadmin {-V | -version}

Database information

Use this form of ttRepAdmin to obtain summary information about a database.

ttRepAdmin -self -list [-scheme [owner.]schemeName]
{DSN | -connStr connectionString}

Options

ttRepAdmin -self -list has the options:

Examples

ttRepAdmin -self -list my_dsn

The above syntax prints out information about the replication definition of the database my_dsn.

Subscriber database operations

Use this form of ttRepAdmin to check the status or reset the state of a subscriber (receiver) database.

ttRepAdmin -receiver [-name receiverName]
[-host receiverHostName] 
       [-state receiverState] [-reset]
       [-list] [-scheme [owner.]schemeName]
       {DSN | -connStr connectionString}

Options

ttRepAdmin -receiver has the options:

Examples

ttRepAdmin -receiver -list my_dsn

The above syntax lists replication information for all the subscribers of the master database, my_dsn.

ttRepAdmin -receiver -name rep_dsn -list my_dsn

The above syntax lists replication information for the rep_dsn subscriber of the master database, my_dsn.

ttRepAdmin -receiver -name rep_dsn -reset my_dsn

The above syntax resets the replication bookmark with respect to the rep_dsn subscriber of the master database. Should only be used when migrating a replicated database with ttMigrate or ttBulkCp.

ttRepAdmin -receiver -name rep_dsn -state Start my_dsn

The above syntax resets the replication state of the rep_dsn subscriber database to the Start state with respect to the master database, my_dsn.

Duplicate a database

Use this form of ttRepAdmin to create a new database with the same contents as the master database.

The following must be true for you to perform the ttRepAdmin -duplicate:

• Only the instance administrator can run ttRepAdmin -duplicate.

• The instance administrator must have the same operating system username on both source and target computer to execute ttRepAdmin -duplicate.

• You must provide the user name and password with the -UID and -PWD options for an internal user with the ADMIN privilege on the source database.

• You must run ttRepAdmin on the target host.

• The DSN specified must be a direct-mode DSN, not a server DSN.

Before running the ttRepAdmin -duplicate command, use ttStatus to be sure that the replication agent is started for the source database.

ttRepAdmin -duplicate -from srcDataStoreName
       -host srcDataStoreHost
      [-localIP localIPAddress] [-remoteIP remoteIPAddress]
      [-setMasterRepStart] [-ramLoad] [-delXla]
      -UID userId (-PWD pwd | -PWDCrypt encryptedPwd)
      [-drop { [owner.]table ... | [owner.]sequence |ALL }]
      [-truncate { [owner.]table ... | ALL }]
      [-compression 0 | 1] [-bandwidthmax maxKbytesPerSec]
      [-initCacheDr [-noDRTruncate] [-nThreads]]
      [-keepCG [-cacheUid cacheUid [-cachePwd cachePwd]] 
      [-recoveringNode | -deferCacheUpdate]
      |-nokeepCG]
      [-remoteDaemonPort portNo] [-verbosity {0|1|2}]
      [-localhost localHostName]
      {destDSN | -connStr connectionString}

Options

ttRepAdmin -duplicate has the options:

Examples

CREATE USER ttuser IDENTIFIED BY ttuser;
User created.

GRANT admin TO ttuser;

ttRepAdmin -duplicate -from ds1 -host "server1" 
           -UID ttuser -PWD ttuser
           -connStr "dsn=ds2;UID=ttuser;PWD=ttuser" 

ttRepAdmin -duplicate -from dsn1 -host host1 -uid ttuser -pwd ttuser 
        -keepCG -cacheuid orauser -cacheuid orapwd "DSN=dsn2;UID=;PWD="

ttRepAdmin -duplicate -from srcDataStoreName -host srcDataStoreHost
        -setMasterRepStart -ramLoad
        -UID timesten_user -PWD timesten_user]
        -localhost localHostName 
        [destDSN | -connStr connectionString ]

Notes

This utility can duplicate any temporary table definition in a database, but it does not replicate the contents of temporary tables.

You cannot use this utility to duplicate databases across major releases of TimesTen.

Wait for updates to complete

Use this form of ttRepAdmin to assure that all the updates in the log are replicated to all subscribers before call returns.

ttRepAdmin -wait [-name receiverName] [-host receiverHostName]
[-timeout seconds] {DSN | -connStr connectionString} 

Options

ttRepAdmin -wait has the options:

Examples

ttRepAdmin -wait -name receiverName -host receiverHostName
-timeout seconds -dsn DSN 

The above syntax provides a way to ensure that all updates, committed at the time this program was invoked, have been transmitted to the subscriber, receiverName, and the subscriber has acknowledged that all those updates have been durably committed at the subscriber database. The timeout in seconds limits the wait.

ttRepAdmin -wait -dsn DSN

In the above syntax, if no timeout and no subscriber name are specified, ttRepAdmin does not return until all updates committed at the time this program was invoked have been transmitted to all subscribers and all subscribers have acknowledged that all those updates have been durably committed at the subscriber database.

Replication status

Use this form of ttRepAdmin to check the size of the transaction log files, bookmark position, or replication configuration of a master database.

ttRepAdmin -log {DSN | -connStr connectionString}
ttRepAdmin -showstatus {-awtmoninfo} {DSN | -connStr connectionString}
ttRepAdmin -showconfig {DSN | -connStr connectionString}
ttRepAdmin -bookmark {DSN | -connStr connectionString}

Options

The ttRepAdmin monitor operations have the options:

Result set

If AWT monitoring is enabled, this utility displays the following information in addition to other ttRepAdmin -showstatus output.

• TimesTen processing time: The total number of milliseconds spent in processing AWT transaction data since monitoring was enabled.

• Oracle bookmark management time: The total number of milliseconds spent in managing AWT metadata on Oracle since monitoring was enabled.

• Oracle execute time: The total number of milliseconds spent in OCI preparation, binding and execution for AWT SQL operations since monitoring was enabled. This statistic includes network latency between TimesTen and Oracle.

• Oracle commit time: The total number of milliseconds spent in committing AWT updates on Oracle since monitoring was enabled. This statistic includes network latency between TimesTen and Oracle.

• Time since monitoring was started.

• Total number of TimesTen row operations: The total number of rows updated in AWT cache groups since monitoring was enabled.

• Total number of TimesTen transactions: The total number of transactions in AWT cache groups since monitoring was enabled.

• Total number of flushes to Oracle: The total number of times that TimesTen data has been sent to Oracle.

The output also includes the percentage of time spent on TimesTen processing, Oracle bookmark management, Oracle execution and Oracle commits.

Examples

ttRepAdmin -log DSN

The above syntax reports the number of transaction log files that replication is retaining to transmit updates to other databases. The replication agent retains a transaction log file until all updates in that transaction log file have been successfully transferred to each subscriber database.

ttRepAdmin -showconfig DSN

The above syntax reports the entire replication configuration. It lists all the subscribers for the specified DSN, the names and details of the tables being replicated, and all the subscriptions.

ttRepAdmin -showstatus DSN

The above syntax reports the current state of the database for the specified DSN. The output includes the state of all the threads in the replication agents for the replicated databases, bookmark locations, port numbers, and communication protocols.

ttRepAdmin -bookmark DSN

The above syntax prints out the log sequence numbers of the earliest log record still needed by replication, the last log record written to disk, and the last log record generated.

ttRepAdmin -showstatus -awtmoninfo myDSN

[other -showstatus output]
...
AWT Monitoring statistics
--------------------------
TimesTen processing time : 0.689000 millisecs (0.164307 %)
   Oracle bookmark management time : 3.229000 millisecs (0.770027%)
   Oracle execute time : 342.908000 millisecs (81.774043 %)
   Oracle commit time : 72.450000 millisecs (17.277315 %)
   Time since monitoring was started: 8528.641000 millisecs
   Cache-connect Operational Stats :
      Total Number of TimesTen row operations : 2
      Total Number of TimesTen transactions : 2
      Total Number of flushes to Oracle : 2

The above syntax and output shows the AWT monitoring status.

Notes

The ttRepAdmin utility is supported only for TimesTen Data Manager DSNs. It is not supported for TimesTen Client DSNs.

You must use the -scheme option when specifying more than one replication scheme, or when more than one scheme exists involving the specified database.

Using SQL configuration, you can create multiple replication schemes in the same database. If there is only one replication scheme, the ttRepAdmin utility automatically determines the scheme. If there is more than one scheme, you must use the ttRepAdmin -scheme option to specify which scheme to use.

When configuring replication for databases with the same name on different hosts, you can indicate which database you wish to operate on by using -host. For example, if all the subscribers have the name DATA, you can set the replication state on host SW1 with:

ttRepAdmin -receiver -name DATA -host SW1 -state start DSN

See also

For a full description of TimesTen Replication, see Oracle TimesTen In-Memory Database Replication Guide.

For upgrade examples, see "TimesTen Upgrades" in Oracle TimesTen In-Memory Database Installation Guide.

ttRestore

Description

Creates a database from a backup that has been created using the ttBackup utility. If the database exists, ttRestore does not overwrite it.

The attributes in the ttRestore connection string can contain any of the first connection or general connection attributes. It can also include the data store attribute LogDir. All other data store attributes are copied from the backup files. This allows the restored database to be relocated.

The ttRestore action is somewhat more powerful than a first connect, as it can move the database. It is somewhat less powerful than creating a new database, as it cannot override the data store attributes, except for the LogDir attribute.

For an overview of the TimesTen backup and restore facility, see "Migration, Backup, and Restoration" in the Oracle TimesTen In-Memory Database Installation Guide.

Required privilege

This utility requires the instance administrator privilege.

Syntax

ttRestore {-h | -help | -?}
ttRestore {-V | -version}
ttRestore [-fname filePrefix] [-noconn] -dir directory 
       {DSN | -connStr connectionString}
ttRestore -i [-noconn] {DSN | -connStr connection_String}

Options

ttRestore has the options:

Example

ttRestore -dir /users/pat/TimesTen/backups
-fname FastInsBkup "DSN=FastIns"

To back up a database named origDSN to the directory /users/rob/tmp and restore it to database named restoredDSN, use:

ttBackup -dir /users/rob/tmp -fname restored "dsn=origDSN"
ttRestore -dir /users/rob/tmp -fname restored "dsn=restoredDSN"

The value of fname is the name that you want for the prefix portion of the backup file name.

On UNIX, to restore a tape backup to the FastIns database, use:

dd bs=64k if=/dev/rmt0 | ttRestore -i DSN=FastIns

Notes

The ttBackup utility and the ttRestore utility can be used to backup and restore databases only when the first three numbers of the TimesTen release are the same. For example, you can backup and restore files between releases 11.2.2.2.0 and 11.2.2.3.0. You cannot backup and restore files between releases 11.2.1.9.0 and 11.2.2.3.0. You can use the ttMigrate or ttMigrateCS utility to migrate databases across major releases, operating systems, operating system bit levels or CPU types

Databases containing cache groups can be backed up as normal with the ttBackup utility. However, when restoring such a backup, special consideration is required as the restored data within the cache groups may be out of date or out of sync with the data in the backend Oracle database. See the section on "Backing up and restoring a database with cache groups" in the Oracle In-Memory Database Cache User's Guide for details.

See also

ttSchema

Description

Prints out the schema, or selected objects, of a database. The utility can list the following schema objects that are found in SQL CREATE statements:

• Tables

• Indexes

• Cache group definitions

• Sequences

• Views

• Materialized view logs

• Column definitions, including partition information

• PL/SQL program units

The level of detail in the listing and the objects listed are controlled by options. The output represents a point in time snapshot of the state of a database rather than a history of how the database came to arrive at its current state, perhaps through ALTER statements. An entire database, including data, cannot be completely reconstructed from the output of ttSchema. The output of ttSchema can be played back by the ttIsql utility in order to rebuild the full schema of a database.

On UNIX, this utility is supported for TimesTen Data Manager DSNs. For TimesTen Client DSNs, use the utility ttSchemaCS.