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

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

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:

Option	Description
`-connStr` `connection_string`	An ODBC connection string containing the name of the data store, the server name and DSN (if necessary) and any relevant connection attributes.
`DSN`	Specifies an ODBC data source name of the data store to be connected.
`-e` `commands`	Specifies a semi-colon separated list of ttIsql commands to execute on start up.
`-f` `filename`	Read SQL commands from `filename`.
`-h` `-help` -?	Prints a usage message and exits.
`-helpcmds`	Prints a short list of the interactive commands.
`-helpfull`	Prints a full description of the interactive commands.
`-interactive`	Forces interactive mode. This is useful when running from an emacs comint buffer.
`-N` `ncharEncoding`	Specifies the character encoding method for NCHAR output. Valid values are LOCALE or ASCII. LOCALE (the default) sets the output format to the locale-based setting. If no value is specified, TimesTen uses the system's native language characters.
`-V \| -version`	Prints the release number of ttIsql and exits.
`-v` `verbosity`	Specifies the verbosity level. One of: 0 - Shows error information only. If all commands succeed, there is no output. 1 - The basic output generated by commands is displayed. 2 - (the default) Same as level 1, plus it shows more detailed results of commands.At this level simplified SQL error and information messages are displayed. In addition, ttIsql commands that are read from an external file are echoed to the display. 3 - Same as level 2, with more detailed error and information messages. 4 - Same as level 3, plus complete error and information messages are displayed. Also displayed are messages about prepared commands, "success" messages for each command that succeeded and content of XLA records.
-`wait`	Waits until successful connect.

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:

Command	Description
`allfunctions` `[[owner_name_pattern.]table_name_pattern]`	Lists, in a single column, the names of all the PL/SQL functions that match the given pattern selected from SYS.ALL_OBJECTS. When a pattern is missing, the pattern defaults to "%". If passthrough is enabled, lists PL/SQL functions matching the pattern in the Oracle database. See the functions command.
`allindexes` `[[owner_name_pattern`.`]table_name_pattern`]	Describes the indexes that it finds on the tables that match the input pattern selected from SYS.ALL_OBJECTS. When a pattern is missing, the patterns default to "%". If passthrough is enabled, lists indexes on tables matching the pattern in the Oracle database. See the indexes command.
`allpackages` `[[owner_name_pattern`.`]table_name_pattern]`	Lists, in a single column, the names of all the PL/SQL packages that match the given pattern selected from SYS.ALL_OBJECTS. When a pattern is missing, the patterns default to "%". If passthrough is enabled, lists PL/SQL packages matching the pattern in the Oracle database. See the packages command.
`allprocedures` [[`owner_name_pattern.]procedure_name_ pattern]`	Lists, in a single column, the names of all the PL/SQL procedures that match the given pattern selected from SYS.ALL_OBJECTS. When a pattern is missing, the pattern defaults to "%". If passthrough is enabled, lists PL/SQL procedures matching the pattern in the Oracle database. See the procedures command.
`allsequences` `[[owner_name_pattern.]table_name_pattern]]`	Lists, in a single column, the names of all the sequences that match the given pattern selected from SYS.ALL_OBJECTS. When a pattern is missing, the pattern defaults to "%". If passthrough is enabled, lists sequences on tables matching the pattern in the Oracle database. See the sequences command.
`alltables` `[[owner_name_pattern.]table_name_pattern]]`	Lists, in a single column, the names of all the tables that match the given pattern selected from SYS.ALL_OBJECTS. When a pattern is missing, the pattern defaults to "%". If passthrough is enabled, lists tables matching the pattern in the Oracle database. See the tables command.
`allviews` `[[owner_name_pattern.]view_name_pattern]]`	Lists, in a single column, the names of all the views that match the specified pattern selected from SYS.ALL_OBJECTS. When a pattern is missing, the pattern defaults to "%". If passthrough is enabled, lists views matching the pattern in the Oracle database. See the views command.
`builtins` `[builtin_name_ pattern]`	Lists, in a single column, the names of all the TimesTen built-in procedures that match the given pattern. When the pattern is missing, the pattern defaults to "%". See the procedures command.
`bye` `exit`	Exits ttIsql.
`cachegroups [[cache_group_owner_pattern.cache_group_name_pattern]]`	Reports information on cache groups defined in the currently connected data source, including the state of any dead data stores that contain autorefresh cache groups. If the optional argument is not specified then information on all cache groups in the current data source is reported.
`clearhistory`	Clears the history buffer. Also see `history` and `savehistory`.
`clienttimeout` `[timeeout` `seconds]`	Sets the client timeout value in seconds for the current connection.
`cachesqlget` `[ASYNCHRONOUS_WRITETHROUGH \| INCREMENTAL_AUTOREFRESH]` `[[cache_group_owner.]cache_group_name]` `{INSTALL \| UNINSTALL}` `[filename]`	Generates an Oracle SQL*Plus compatible script for the installation or uninstallation of Oracle objects associated with a a read-only cache group, a user managed cache group with incremental autorefresh or an AWT cache group. If INSTALL is specified, the Oracle SQL statement to install the Oracle objects is generated. If UNINSTALL is specified, the Oracle SQL statement used to remove the Oracle objects is generated. If a cache group is not specified with UNINSTALL, a SQL statement to remove all Oracle objects in the AUTOREFRESH user's account is generated. If the optional `filename` argument is included, the generated SQL statement is saved to the specified external file. If the external file already exists, its contents are destroyed before writing to the file.
`close` `[connect_id.]command_id]` `closeall`	Closes the prepared command identified by connection name connect_id and command ID command_id. If command_id is not specified, closes the most recent command. If `closeall` is selected, closes all currently open prepared commands.
`commit`	Commits the current transaction (durably if DurableCommits=1 for the connection).
`commitdurable`	Commits the current transaction durably.
`compact`	Compacts the data store.
`connect` `[connection_string` `\|[[DSN][as]connid` `[adding]` `[connection_string` `\|` `DSN]` `[as` `connid]`	Connects to the data store with the specified ODBC `connection_string`. If no password is supplied in this format, ttIsql prompts for the password. If no user is given, ttIsql attempts to connect using the user name of the current user as indicated by the operating system. If `as` `connid` is specified, you can explicitly name the connection. The `connid` must be only alphanumeric characters, is case sensitive, must start with an alpha character and can only be a maximum of 30 characters in length. The name of `connid` is automatically supplied to the ConnectionName general connection attribute. If the connect fails, the current connection is set to a special reserved connection named "none," which is never connected to anything. When `adding` is specified, it refers to creating a new connection to the DSN specified by `DSN` or by the connection string.
`define` `name` `[=` `value]`	Defines a string substitution alias. If no value is provided, ttIsql displays the current definition for the specified name. You must `set define o`n to enable command substitution. See "Set/show attributes".
`describe [[owner_pattern.]` `name_pattern` \| `procedure_name_pattern` `\|sql_statement` `\|` `[connect_id.]command_id` `\|*]`	List information on tables, views, sequences, PL/SQL functions, PL/SQL procedures, PL/SQL packages, and TimesTen built-in procedures in that order when the argument is `[owner_pattern`.`]name_pattern`. Otherwise lists the specific objects that match the given pattern. Describes the parameters and results columns when the argument is `sql_statement`. If passthrough is set to 3, lists information about the same types of objects in the Oracle database. If `*` is specified, reports the prepared statements for all connections. The command alias is `desc`.
`disconnect` [all]	Disconnects from the data store. If `all` is specified, disconnects and closes all connections. When disconnect finishes, the current connection is set to the reserved connection named "none."
`dssize` `[k\|m]`	Prints data store size information in KB or MB. The default is KB.
`e:` `msg` `PROMPT` `msg`	Echoes the specified messages, terminated by the end of the line. A semicolon is not required to end the line. Messages are not echoed if verbosity is set to 0.
`exec` `[connect_id.]command_id] \|` `PLSQLSTMT`	Executes the prepared command command_id on connection connect_id or executes a PL/SQL statement. The `connect_id` optionally names a ttIsql connection and command_id is an integer from 1 to 255.If `PLSQLSTMT` is supplied, ttIsql prepends the statement with `BEGIN` and appends the statement with `END`, thus allowing the PLSQL statement to execute. If no argument is supplied, executes the most recent command.
`execandfetch` `[connect_id.]command_id]`	Executes and fetches all results from prepared command command_id on connection connect_id. If command_id is not specified, executes and fetches all results from the most recent command.
`explain [plan for]` `sqlstmt`	Explains the plan for the specified SQL statement.
`fetchall` `[connect_id.]command_id]`	Fetches all results from prepared command `command_id` on connection `connect_id`. If `command_id` is not specified, fetches all results from the most recent command. The command must already have been executed using `exec`.
`fetchone` `[connect_id.]command_id]`	Fetches one result from prepared command `command_id` on connection `connect_id`. If `command_id` is not specified, fetches one result from the most recent command. The command must already have been executed using `exec`.
`free` [`connect_id.`]`command_id`]	Frees prepared command `command_id` on connection `connect_id`. If no command is specified, frees the most recent command.
`functions` `[object_name_pattern]`	Lists, in a single column, the names of PL/SQL functions owned by the current user that match the given pattern. When a name pattern is missing, the pattern defaults to "%". If passthrough is enabled, lists PL/SQL functions matching the pattern in the Oracle database. See the allfunctions command.
`help` `[command` `[command ...]\| all \|` `comments \| attributes]`	Prints brief or detailed help information for commands. If specific commands are given as arguments then detailed help for each command is printed. If you don't know the exact name of a command, try typing just a few characters that may be part of the command name. ttIsql searches and displays help for any commands that include the characters. If `all` is given as an argument then detailed help for all commands is printed. If `comments` is given as an argument then information on using ttIsql comments within scripts is printed. If `attributes` is given as an argument then information on the set/show attributes is printed. If no argument is given then brief help information for all commands is printed.
`history` [`-r] [num_commands]`	Lists previously executed commands. The `num_commands` parameter specifies the number of commands to list. If this parameter is omitted, the previous ten commands are listed by default. If the`-r` parameter is specified, commands are listed in reverse order. The history list stores up to 100 of the most recently executed commands. Use the `clearhistory` command to clear the history. See the savehistory command.
`host` `os_command`	Executes an operating system command. The command is executed in the same console as ttIsql. This command sets the environment variable TT_CONNSTR in the environment of the process it creates. The value of the variable is the connection string of the current connection.
`indexes` `[table_name_pattern]`	Describes the indexes that it finds on the tables owned by the current user that match the input pattern. When a name pattern is missing, the pattern defaults to "%". If passthrough is enabled, lists indexes on tables matching the pattern in the Oracle database. See the allindexes command.
`monitor`	Formats the contents of the MONITOR table for easy viewing.
`packages` `[object_name_pattern]`	Lists, in a single column, the names of PL/SQL packages owned by the current user that match the given pattern. When a name pattern is missing, the pattern defaults to "%". If passthrough is enabled, lists PL/SQL packages matching the pattern in the Oracle database. See the allpackages command.
`prepare` `[[connid`.`]command_id]SQL_Statement`	Prepares the specified SQL statement. If the `command_id` argument is not specified the `command_id` is assigned automatically. The `command_id` argument can take a value between 0 and 255 inclusive. If `connid` is specified, switches to the given connection ID. The `connid` must be only alphanumeric characters and are case insensitive.
`print` `[variable]`	Prints the value of the specified bind variable or all variables if no variable is specified. If the variable is a refcursor, then the results are fetched and printed.
`procedures` `[procedure_name_ pattern]`	Lists, in a single column, the names of PL/SQL procedures owned by the current user that match the given pattern. When a name pattern is missing, the pattern defaults to "%". If passthrough is enabled, lists PL/SQL procedures matching the pattern in the Oracle database. See the builtins and allprocedures commands.
`quit`	Exits ttIsql.
`remark` `msg`	Specifies that the message on the line should be treated as a comment. When `rem` or `remark` is the first word on the line, ttIsql reads the line and ignores it.
`repschemes` `[[scheme_owner_pattern.]scheme_name_pattern]`	Reports information on replication schemes defined in the currently connected data source. This information includes the attributes of all elements associated with the replication schemes. If the optional argument is not specified then information on all replication schemes defined in the current data source is reported.
`retryconnect [0\|1]`	Disables(0) or enables(1) the wait for connection retry feature. If the connection retry feature is enabled then connection attempts to a data source that initially fail due to a temporary situation are retried until the connection attempt succeeds. For example, if data source recovery is in progress when attempting to connect, the connection retry feature causes the connect command to continue to attempt a connection until the recovery process is complete. If the optional argument is omitted then the connection retry feature is enabled by default.
`rollback`	Rolls back the current transaction. AutoCommit must be off. This command does not stop IMDB Cache operations on Oracle, including passthrough statements, flushing, manual loading, manual refreshing, synchronous writethrough, propagating and transparent loading.
`run` `filename` `[arguments]\|` `start` `filename` `[arguments...]\|` `@@` `filename` `[arguments...]\|` `@` `filename` `[arguments...]`	Reads and executes SQL commands from filename. The run command can be nested up to five levels. The `@@` command is identical to the `@` command only if the file is specified with an absolute path. When `@` is used with a relative path, the path is relative to the startup directory of `ttIsql`. When `@@` is used, it is relative to the currently running input file. Therefore `@@` is useful when used in a script that needs to call other scripts. It does not matter what directory the invoker of ttIsql is in when the script is run. See "Example parameters of command string substitution" for a description of `arguments`.
`savehistory` `[-a \| -f]` `outputfile`	Writes the history buffer to the specified `output` file. Only command, no parameter values are saved in the output file. Therefore, a script may not be able to replay the history from the output file. If the `output` file already exists, you must specify either the `-a` or `-f` option. If `-a` is specified, the history is appended to the specified `output` file. If `-f` is specified, the history overwrites the contents of the specified `output` file. See the clearhistory and history commands.
`sequences[sequence_name_pattern]`	Lists, in a single column, the names of sequences owned by the current user that match the given pattern. When a name pattern is missing, the pattern defaults to "%". If passthrough is enabled, lists sequences on tables matching the pattern in the Oracle database. See the `allsequences` command.
`set` `attribute` `[value]`	Sets the specified attribute to the specified value. If no value is specified, displays the current value of the specified attribute. For a description of accepted attributes, see "Set/show attributes".
`setjoinorder` `tblNames` `[...]`	Specifies the join order for the optimizer. AutoCommit must be off.
`setuseindex` `index_name,correlation_name,` `{0 \| 1}` `[;...]`	Sets the index hint for the query optimizer.
`show {all` `\|` `attribute}`	Displays the value for the specified data store attribute or displays all the attributes. For a description of accepted attributes, see "Set/show attributes".
`showjoinorder {0 \| 1}`	Enables or disables the storing of join orders. 0 - Disables the storing of join orders 1 - Enables the storing of join orders. Call the `ttoptshowjoinorder` built-in procedure explicitly to display the join order after SELECT, UPDATE, DELETE or MERGE SQL statements.
`sleep` `[n]`	Suspends execution for `n` seconds. If `n` is not specified then execution is suspended for 1 second.
`spool` `filename` `{[option]` `\|` `OFF}`	Writes a copy of the terminal output to the file filename. If you do not provide an extension to the filename, the filename has the extension `.lst`. The available options include CREATE - Creates a new file. APPEND - Appends output to an existing file. REPLACE (default) - Overwrites an existing file. When you specify the value OFF, the spooling behavior is terminated and the output file is closed. If you specify a spool command while one is already running, the active spool is closed and a new files is opened.
`sqlcolumns [owner_name_pattern.]table_name_pattern`	Prints results of an ODBC call to `SQLColumns`.
`sqlgetinfo` `infotype`	Prints results of an ODBC call to `SQLGetInfo`.
`sqlstatistics [[owner_name_pattern.]table_name_pattern]`	Prints results of an ODBC call to `SQLStatistics`.
`sqltables[[owner_name_pattern.]table_name_pattern]`	Prints results of a call to SQLTables. The pattern is a string containing an underscore ( _ ) to match any single character or a percent sign (%) to match zero or more characters.
`statsclear` `[[owner_name.]table_name]`	Clears statistics for specified table (or all tables if no table is specified).
`statsestimate` `[[owner_name.]table_name] {n` `rows` `\|` `p` `percent}`	Estimates statistics for specified table (or all tables if no table is specified).
`statsupdate` `[[owner_name_pattern.]table_name_pattern]`	Updates statistics for specified table (or all tables if no table is specified).
`tables` `[table_name_pattern]]`	Lists, in a single column, the names of tables owned by the current user that match the given pattern. When a name pattern is missing, the pattern defaults to "%". If passthrough is enabled, lists tables matching the pattern in the Oracle database. See the alltables command.
`undefine` `name`	Undefines a string substitution alias.
`unsetjoinorder`	Clears join order advice to optimizer. AutoCommit must be off.
`unsetuseindex`	Clears the index hint for the query optimizer.
`use` `[conn_id]`	Displays the list of current connections and their IDs. If connid is specified, switches to the given connection ID. If `use` fails to locate the connection id, the current connection is set to the reserved connection named "none." See the connect command.)
`variable [variable` `[type]]`	Declares a bind variable that can be referenced in a statement, or displays the definition of the variable.
`version`	Reports version information.
`views` `[table_name_pattern]`	Lists, in a single column, the names of views owned by the current user that match the given pattern. When a name pattern is missing, the pattern defaults to "%". If passthrough is enabled, lists views matching the pattern in the Oracle database. See the allviews command.
`xlabookmarkdelete` `id`	Deletes a persistent XLA bookmark. If a bookmark to delete is not specified then the status of all current XLA bookmarks is reported. See "XLA Reference" in Oracle TimesTen In-Memory Database C Developer's Guide. Requires ADMIN privilege or object ownership.

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:

Attribute	Description
`all`	With show command only. Displays the setting of all the ttIsql commands.
`autocommit [1\|0]`	Turns AutoCommit off and on. If no argument is given, displays the current setting.
`columnlabels [0 \| 1]`	Turns the `columnlabels` feature off (0) or on (1). If no argument is specified, the current value of `columnlabels` is displayed. The initial value of `columnlabels` is off (0) after connecting to a data source. When the value is on (1), the column names are displayed before the SQL results. You can also enable this attribute without specifying the set command.
`connstr`	Prints the connection string returned from the driver from the `SQLDriverConnect` call. This is the same string printed when ttIsql successfully connects to a data store.
`define [&\|c\|on\|off]`	Sets the character used to prefix substitution variables to `c`. ON or OFF controls whether ttIsql scans commands for substitution variables and replaces them with their values. ON changes the value of `c` back to the default "&". (It does not change it to the most recently used character.) Default value for ttIsql is OFF (no variable substitution). See "Example parameters using "variable" and "print"" for an explanation of the default.
`dynamicloadenable [on\|off]`	Enables or disables dynamic load of Oracle data to a TimesTen dynamic cache group. By default, dynamic load of Oracle data is enabled.
`echo [on \| off]`	With the `set` command, prints the commands listed in a `run`, `@` or `@@` script to the terminal as they are executed. If off, the output of the commands is printed but the commands themselves are not printed.
`editline [0 \| 1]`	Turns the editline function off and on. By default, editline is on. If editline is turned off, the backspace character deletes full characters, but the rest of editline capabilities are unavailable.
`err \| error \|errors [.objecttype[schema.]` `name]`	Shows errors command display error information about the given PL/SQL object.If no object type or object name is supplied, ttIsql assumes the PL/SQL object that you last attempted to create and retrieves the errors for that object. If no errors associated with the given object are found, or there was no previous PL/SQL DDL, then ttIsql displays "No errors."
`feedback [on \| off]` `rows`	Controls the display of status messages after statement execution. When `rows` is specified, if the statement affected more than the specified number of rows, then the feedback indicates the number of affected rows. If the number of rows affected is less than the specified threshold, the number of rows is not printed.Feedback is not provided for tables, views, sequences, materialized views or indexes. It is available for PL/SQL objects.
`isolation [{READ_COMMITTED \| 1}\| {SERIALIZABLE \| 0}]`	Sets isolation level. If no argument is supplied, displays the current value. You can also enable this attribute without specifying the set command.
`multipleconnections [1 \| ON] mc [1 \| ON]`	Reports or enables handling of multiple connections.By default, ttIsql allows the user to have one open connection at a time. If the argument 1 or `ON` is specified the prompt is changed to include the current connection and all multipleconnection features are enabled. If no value is supplied, the command displays the value of the `multipleconnections` setting. You can also enable this attribute without specifying the set command.
`ncharencoding` [`encoding`]	Specifies the character encoding method for NCHAR output. Valid values are LOCALE or ASCII. LOCALE sets the output format to the locale-based setting. If no value is specified, TimesTen uses the system's native language characters. You can also enable this attribute without specifying the set command.
`optfirstrow` [1\|0]	Enables or disables First Row Optimization. If the optional argument is omitted, First Row Optimization is enabled. You can also enable this attribute without specifying the set command.
`optprofile`	Prints the current optimizer flag settings and join order. This attribute cannot be used with the set command.
`passthrough [0\|1\|2\|3\|4\|5]`	Sets the IMDB Cache passthrough level for the current transaction. AutoCommit must be off to execute this command. 0 - SQL statements are executed only against TimesTen. 1 - Statements other than INSERT, DELETE or UPDATE and DDL are passed through if they generate a syntax error in TimesTen or if one or more tables referenced within the statement are not in TimesTen. All INSERT, DELETE and UPDATE statements will be passed through if the target table cannot be found in TimesTen. DDL statements will not be passed through. 2 - Same as 1, plus any INSERT, UPDATE and DELETE statement performed on READONLY cache group tables is passed through. 3 - All SQL statements, except COMMIT and ROLLBACK, and TimesTen built-in procedures that set or get optimizer flags are passed through. COMMIT and ROLLBACK are executed on both TimesTen and Oracle. 4 - All SELECT statements on global cache groups tables that cannot use transparent load are executed on Oracle. 5 - All SELECT statements on global cache groups tables that cannot use transparent load are executed on Oracle. The SELECT statement is not executed until after all committed changes to the global cache group are propagated to Oracle. If no optional argument is supplied, the current setting is displayed. After the transaction, the passthrough value is reset to the value defined in the connection string or in the DSN or the default setting if no value was supplied to either. You can also enable this attribute without specifying the set command. Note: Some Oracle objects may not be described by ttIsql.
`prefetchcount` [`prefetch_count_size`]	Sets the prefetch count size for the current connection. If the optional argument is omitted, the current prefetch count size is reported. Setting the prefetch count size can improve result set fetch performance. The `prefetch_count_size` argument can take an integer value between 0 and 128 inclusive. You can also enable this attribute without specifying the set command.
`prompt [string]`	Replaces the `Command>` prompt with the specified string. To specify a prompt with spaces, you must quote the string. The leading and trailing quotes are removed. A prompt can have a string format specifier (`%c`) embedded. The `%c` is expanded with the name of the current connection.
`querythreshold [seconds]`	With the `show` command, displays the value of the Query Threshold first connection attribute. With the `set` command, modifies the value of the QueryThreshold first connection attribute that was set in the connection string or `odbc.ini` file. Specify a value in seconds that indicates the number of seconds that a query can execute before TimesTen writes a warning to the support log or throws an SNMP trap.
`serveroutput [on \| off]`	With the `set` command set to on, after each executed SQL statement, displays any available output. This output is available for debugging I/O purposes, if the PL/SQL DBMS_OUTPUT package is set to store the output so that it can be retrieved using this command. The default is off, (no server output is displayed) as performance may be slower when using this command. If you set serveroutput to on, TimesTen uses an unlimited buffer size. DBMS_OUTPUT.ENABLE is per connection, therefore `set serveroutput on` affects the current connection only.
`showplan [0 \| 1]`	Enables (1) or disables (0) the display of plans for selects/updates/deletes in this transaction. If the argument is omitted, the display of plans is enabled. AutoCommit must be off. You can also enable this attribute without specifying the set command.
`sqlquerytimeout` [`seconds`]	Specifies the number of seconds to wait for a SQL statement to execute before returning to the application for all subsequent calls. If no time or 0 seconds is specified, displays the current timeout value. The value of `seconds` must be equal to or greater than 0.This attribute does not stop IMDB Cache operations on Oracle, including passthrough statements, flushing, manual loading, manual refreshing, synchronous writethrough, propagating, and transparent loading. You can also enable this attribute without specifying the set command.
`timing [1\|0]`	Enables or disables printing of query timing. You can also enable this attribute without specifying the set command.
`tryhash [1\|0]`	Enables or disables use of hash indexes by optimizer. AutoCommit must be off. You can also enable this attribute without specifying the set command.
`trymaterialize [1\|0]`	Enables or disables materialization by optimizer. AutoCommit must be off. You can also enable this attribute without specifying the set command.
`trymergejoin [1\|0]`	Enables or disables use of merge joins by optimizer. AutoCommit must be off. You can also enable this attribute without specifying the set command.
`trynestedloopjoin [1\|0]`	Enables or disables use of nested loop joins by optimizer. AutoCommit must be off. You can also enable this attribute without specifying the set command.
`tryrowid [1\|0]`	Enables or disables rowID scan hint.
`tryrowlocks [1\|0]`	Enables or disables use of row-level locking by the optimizer. AutoCommit must be off. You can also enable this attribute without specifying the set command.
`tryserial [1\|0]`	Enables or disables use of serial scans by optimizer. AutoCommit must be off. You can also enable this attribute without specifying the set command.
`trytmphash [1\|0]`	Enables or disables use of temporary hashes by optimizer. AutoCommit must be off. You can also enable this attribute without specifying the set command.
`trytbllocks [1\|0]`	Enables or disables use of table-level locking by the optimizer. AutoCommit must be off. You can also set this attribute without specifying the set command.
`trytmptable [1\|0]`	Enables or disables use of temporary tables by optimizer. AutoCommit must be off. You can also enable this attribute without specifying the set command.
`trytmprange [1\|0]`	Enables or disables use of temporary range indexes by optimizer. AutoCommit must be off. You can also enable this attribute without specifying the set command.
`tryrange [1\|0]`	Enables or disables use of range indexes by optimizer. AutoCommit must be off. You can also enable this attribute without specifying the set command.
`verbosity` [`level`]	Changes the verbosity level. The verbosity level argument can be an integer value of 0,1,2,3 or 4. If the optional argument is omitted then the current verbosity level is reported. You can also enable this attribute without specifying the set command.
`vertical` [{0 \| off} \| {1 \| on} \| `statement`]	Sets or displays the current value of the vertical setting. The default value is 0 (off). If statement is supplied, the command temporarily turns vertical on for the given statement. This form is only useful when the vertical flag is already turned off. The `vertical` setting controls the display format of result sets. When set, the result sets are displayed in a vertical format where each column is on a separate line and is displayed with a column label. You can also enable this attribute without specifying the set command.

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:

Keystroke	Action
Left Arrow	Moves the insertion point left (back).
Right Arrow	Moves the insertion point right (forward).
Up Arrow	Scroll to the command prior to the one being displayed. Places the cursor at the end of the line.
Up Arrow <RETURN>	Scrolls to the PL/SQL block prior to the one being displayed.
Down Arrow	Scrolls to a more recent command history item and puts the cursor at the end of the line.
Down Arrow <RETURN>	Scrolls to the next PL/SQL block after the one being displayed.
Ctrl-A	Moves the insertion point to the beginning of the line.
Ctrl-E	Moves the insertion point to the end of the line.
Ctrl-K	"Kill" - Saves and erases the characters on the command line from the current position to the end of the line.
Ctrl-Y	"Yank"- Restores the characters previously saved and inserts them at the current insertion point.
Ctrl-F	Forward character - move forward one character. (See Right Arrow.)
Ctrl-B	Backward character - moved back one character. (See Left Arrow.)
Ctrl-P	Previous history. (See Up Arrow.)
Ctrl-N	Next history. (See Down Arrow.)

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 aborted. 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) > 110Enter Parameter 2 'B' (NUMBER) > 99< X >1 row found.Command> var a number;Command> exec :a := 110;PL/SQL procedure successfully completed.Command> print aA                    : 110Command> 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> printA                    : 110B                    : 99Command> 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 data store if it does not already 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 attributes:

Command> show all; 
Connection independent attribute values: columnlabels = 0 (OFF) editline = 1 (ON) multipleconnections = 0 (OFF) ncharencoding = LOCALE prompt = 'Command> ' verbosity = 2 vertical = 0 (OFF) Connection specific attribute values: autocommit = 1 (ON) Connection String = DSN=DS1121;UID=joeuser;DataStore=/DS/ DS1121;DatabaseCharacterSet=AL32UTF8;ConnectionCharacterSet=AL32UTF 8;DRIVER=/opt/TimesTen/tt1121/lib/libtten.so;PermSize=20;TempSize=20;TypeMode=1; isolation = READ_COMMITTED Prefetch count = 5 Query timeout = 0 seconds (no timeout) Current Optimizer Settings: Scan: 1 Hash: 1 Range: 1 TmpHash: 1 TmpRange: 1 TmpTable: 1 NestedLoop: 1 MergeJoin: 1 GenPlan: 0 TblLock: 1 RowLock: 1 Rowid: 1 FirstRow: 0 IndexedOr: 1 PassThrough: 0 BranchAndBound: 1 ForceCompile: 0 CrViewSemCheck: 1 ShowJoinOrder: 0 CrViewSemCheck: 1 Current Join Order:<>
Command>

Prepare and exec an SQL statement.

ttIsql (c) 1996-2009, 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 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 will prompt 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 ONCommand> @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

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.