6 Using the ttIsql Utility

Batch mode vs. interactive mode

The ttIsql utility can be used in two distinctly different ways: batch mode or interactive mode. When ttIsql is used in interactive mode, users type commands directly into ttIsql from the console. When ttIsql is used in batch mode, a prepared script of ttIsql commands is executed by specifying the name of the file containing the commands.

Batch mode is commonly used for the following types of tasks:

• Performing periodic maintenance operations including the updating of table statistics, compacting the data store and purging log files.

• Initializing a data store by creating tables, indexes and cache groups and then populating the tables with data.

• Generating simple reports by executing common queries.

Interactive mode is suited for the following types of tasks:

• Experimenting with TimesTen features, testing design alternatives and improving query performance.

• Solving data store problems by examining data store statistics.

• Any other data store tasks that are not performed routinely.

By default, when starting ttIsql from the shell, ttIsql is in interactive mode. The ttIsql utility prompts you to type in a valid ttIsql built-in command or SQL statement by printing the Command> prompt:

C:\>ttIsql

ttIsql (c) 1996-2009, Oracle. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.

Command>

Batch mode can be accessed in two different ways. The most common way is to specify the -f option on the ttIsql command line followed by the name of file to run.

For example, executing a file containing a CREATE TABLE statement will look like this:

C:\>ttIsql -f create.sql MY_DSN

ttIsql (c) 1996-2009, Oracle. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.

Command> connect "DSN=MY_DSN"
Connection successful: DSN=MY_DSN;DataStore=E:\ds\MY_DSN; DRIVER=E:\WINNT\System32\TTdv1121.dll;
(Default setting AutoCommit=1)

Command> run "create.sql"

CREATE TABLE LOOKUP (KEY NUMBER NOT NULL PRIMARY KEY, VALUE CHAR (64))

Command> exit
Disconnecting...
Done.

C:\>

The other way to use batch mode is to enter the run command directly from the interactive command prompt. The run command is followed by the name of the file containing ttIsql built-in commands and SQL statements to execute:

Command> run "create.sql";

CREATE TABLE LOOKUP (KEY NUMBER NOT NULL PRIMARY KEY, VALUE CHAR (64))
Command>

Customizing the ttIsql command prompt

You can customize the ttIsql command prompt by using the set command with the prompt attribute:

Command> set prompt MY_DSN;
MY_DSN

You can specify a string format (%c) that returns the name of the current connection:

Command> set prompt %c;
con1

If you want to embed spaces, you must quote the string:

Command> set prompt "MY_DSN %c> ";
MY_DSN con1>

Using ttIsql's online help

The ttIsql utility has an online version of command syntax definitions and descriptions for all built-in ttIsql commands. To access this online help from within ttIsql use the help command. To view a detailed description of any built-in ttIsql commands type the help command followed by one or more ttIsql commands to display help for. The example below displays the online description for the connect and disconnect commands.

Command> help connect disconnect
 
Arguments in <> are required.
Arguments in [] are optional.
 
Command Usage: connect [DSN|connection_string] [as <connection_id>]
Command Aliases: (none)
Description: Connects to the data source specified by the optional DSN or connection string argument. If an argument is not given, then the DSN or connection string from the last successful connection is used. A connection ID may optionally be specified, for use in referring to the connection when multiple connections are enabled. The DSN is used as the default connection ID. If that ID is already in use, the connection will be assigned the ID "conN", where N is some number larger than 0.
Requires an active connection: NO
Requires autocommit turned off: NO
Reports elapsed execution time: YES
Works only with a TimesTen data source: NO
Example: connect; -or- connect RunData; -or- connect "DSN=RunData";
-or- connect RunData as rundata1;
 
Command Usage: disconnect [all]
Command Aliases: (none)
Description: Disconnects from the currently connected data source or all connections when the "all" argument is included. If a transaction is active when disconnecting then the transaction will be rolled back automatically. If a connection exists when executing the "bye", "quit" or "exit" commands then the "disconnect" command will be executed automatically.
Requires an active connection: NO
Requires autocommit turned off: NO
Reports elapsed execution time: YES
Works only with a TimesTen data source: NO
Example: disconnect;

To view a short description of all ttIsql built-in commands type the help command without an argument. To view a detailed description of all built-in ttIsql commands type the help command followed by the all argument.

To view the list of attributes that can be set or shown by using ttIsql, enter:

Command> help attributes

Using ttIsql's 'editline' feature for UNIX only

On UNIX systems, you can use the 'editline' library to set up emacs (default) or vi bindings that enable you to scroll through previous ttIsql commands, as well as edit and resubmit them. This feature is not available or needed on Windows.

To disable the 'editline' feature in ttIsql, use the ttIsql command set editline off.

The set up and keystroke information is described for each type of editor:

• Emacs binding

• vi binding

Using ttIsql's command history

The ttIsql utility stores a list of the last 100 commands executed within the current ttIsql session. The commands in this list can be viewed or executed again without having to type the entire command over. Both SQL statements and built-in ttIsql commands are stored in the history list. Use the history command ("h ") to view the list of previously executed commands. For example:

Command> h;
8 INSERT INTO T3 VALUES (3)
9 INSERT INTO T1 VALUES (4)
10 INSERT INTO T2 VALUES (5)
11 INSERT INTO T3 VALUES (6)
12 autocommit 0
13 showplan
14 SELECT * FROM T1, t2, t3 WHERE A=B AND B=C AND A=B
15 trytbllocks 0
16 tryserial 0
17 SELECT * FROM T1, t2, t3 WHERE A=B AND B=C AND A=B
Command>

The history command displays the last 10 SQL statements or ttIsql built-in commands executed. To display more than that last 10 commands specify the maximum number to display as an argument to the history command.

Each entry in the history list is identified by a unique number. The ! character followed by the number of the command can be used to execute the command again. For example:

Command>
Command> ! 12;

autocommit 0
Command>

To execute the last command again simply type a sequence of two ! characters:

Command> !!;

autocommit 0
Command>

To execute the last command that begins with a given string type the ! character followed by the first few letters of the command. For example:

Command> ! auto;

autocommit 0
Command>

Command> savehistory history.txt;

Command> savehistory -a history.txt;

Command> clearhistory;

Working with character sets

The ttIsql utility supports the character sets listed in "Supported character sets". The ability of ttIsql to display characters depends on the native OS locale settings of the terminal on which you are using ttIsql.

To override the locale-based output format, use the ncharencoding option or the -N option. The valid values for these options are LOCALE (the default) and ASCII. If you choose ASCII and ttIsql encounters a Unicode character, it displays it in escaped format.

You do not need to have an active connection to change the output method.

Working with transactions

The ttIsql utility has several built-in commands for managing transactions. These commands are summarized below:

• autocommit - Turns on or off the autocommit feature.

• commit - Commits the current transaction.

• commitdurable - Commits the current transaction and ensures that the committed work will be recovered in case of data store failure.

• rollback - Rolls back the current transaction.

• isolation - Changes the transaction isolation level.

• sqlquerytimeout - Specifies the number of seconds to wait for a SQL statement to execute before returning to the application.

When starting ttIsql the autocommit feature is turned on by default. In this mode every SQL operation against the data store is committed automatically. To turn the autocommit feature off execute ttIsql's built-in autocommit command with an argument of 0.

When autocommit is turned off transactions must be committed or rolled back manually by executing ttIsql's commit, commitdurable or rollback commands. The commitdurable command will ensure that the transaction's effect is preserved in case of data store failure.

The isolation command can be used to change the current connection's transaction isolation properties. The isolation can be changed only at the beginning of a transaction. The isolation command accepts one of the following constants: READ_COMMITTED and SERIALIZABLE. If the isolation command is executed without an argument then the current isolation level is reported.

The sqlquerytimeout command sets the timeout period for SQL statements. If the execution time of a SQL statement exceeds the number of seconds set by sqlquerytimeout, the SQL statement is not executed and an 6111 error is generated. For details, see "Setting a timeout value for executing SQL statements" in the Oracle TimesTen In-Memory Database Java Developer's Guide and "Setting a timeout value for executing SQL statements" in the Oracle TimesTen In-Memory Database C Developer's Guide.

The following example demonstrates the common use of ttIsql's built-in transaction management commands.

E:\>ttIsql
ttIsql (c) 1996-2009, Oracle. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.

Command> connect "DSN=MY_DSN";
Connection successful: DSN=MY_DSN;DataStore=E:\ds\MY_DSN;DRIVER=E:\WINNT\System32\ TTdv1121.dll;
(Default setting AutoCommit=1)
Command> autocommit 0;
Command> CREATE TABLE LOOKUP (KEY NUMBER NOT NULL PRIMARY KEY, VALUE CHAR (64));
Command> commit;
Command> INSERT INTO LOOKUP VALUES (1, 'ABC');
1 row inserted.
Command> SELECT * FROM LOOKUP;
< 1, ABC >
1 row found.
Command> rollback;
Command> SELECT * FROM LOOKUP;
0 rows found.
Command> isolation;
isolation = READ_COMMITTED
Command> commitdurable;
Command> sqlquerytimeout 10;
Command> sqlquerytimeout;
Query timeout = 10 seconds
Command> disconnect;
Disconnecting...
Command> exit;
Done.
E:\>

Displaying data store information

There are several ttIsql commands that display information on data store structures. The most useful commands are summarized below:

• describe - Displays information on tables, views, sequences, PL/SQL functions, PL/SQL procedures, PL/SQL packages, prepared SQL statements and built-in procedures.

• cachegroups - Displays the attributes of cache groups.

• dssize - Reports the current sizes of the permanent and temporary data store partitions.

• monitor - Displays a summary of the current state of the data store.

Use the describe command to display information on table and result set columns as well as parameters for prepared SQL statements and built-in procedures. The argument to the describe command can be the name of a table, a built-in procedure, a SQL statement or a command ID for a previously prepared SQL statement, a PL/SQL function, PL/SQL procedure or PL/SQL package. The describe command requires a semicolon character to terminate the command.

Command> CREATE TABLE T1 (KEY NUMBER NOT NULL PRIMARY KEY, VALUE CHAR (64));
Command> describe T1
       > ;

Table USER.T1:
  Columns:
   *KEY                            NUMBER NOT NULL
    VALUE                          CHAR (64)
1 table found.

(primary key columns are indicated with *)
Command> describe SELECT * FROM T1 WHERE KEY=?;

Prepared Statement:
  Parameters:
    Parameter 1                    NUMBER
  Columns:
    KEY NUMBER                     NOT NULL
    VALUE                          CHAR (64)
Command> describe ttOptUseIndex;

Procedure TTOPTUSEINDEX:
  Parameters:
    Parameter INDOPTION            VARCHAR (1024)
  Columns:
    (none)

1 procedure found.
Command>

The cachegroups command is used to provide detailed information on cache groups defined in the current data store. The attributes of the root and child tables defined in the cache group are displayed in addition to the WHERE clauses associated with the cache group. The argument to the cachegroups command is the name of the cache group that you want to display information for.

Command> cachegroups MY_CACHE_GROUP

Cache Group USER.MY_CACHE_GROUP:

  Duration: 40 Minutes
  Root Table: USER.T1
  Where Clause: (T1.KEY < 100)
  Type: Not Propagate

  Child Table: USER.T2
  Where Clause: (none)
  Type: Propagate

1 cache group found.
Command>

The dssize command is used to report the current memory status of the permanent and temporary partitions as well as the maximum, allocated and in-use sizes for the data store.

The monitor command displays all of the information provided by the dssize command plus additional statistics on the number of connections, checkpoints, lock timeouts, commits, rollbacks and other information collected since the last time the data store was loaded into memory.

Command> monitor; 
TIME_OF_1ST_CONNECT: Mon Feb 23 11:32:49 2009 
DS_CONNECTS: 11 
DS_DISCONNECTS: 0 
DS_CHECKPOINTS: 0 
DS_CHECKPOINTS_FUZZY: 0 
DS_COMPACTS: 0 
PERM_ALLOCATED_SIZE: 40960 
PERM_IN_USE_SIZE: 5174 
PERM_IN_USE_HIGH_WATER: 5174 
TEMP_ALLOCATED_SIZE: 18432 
TEMP_IN_USE_SIZE: 4527 
TEMP_IN_USE_HIGH_WATER: 4527 
SYS18: 0 
TPL_FETCHES: 0 
TPL_EXECS: 0 
CACHE_HITS: 0 
PASSTHROUGH_COUNT: 0 
XACT_BEGINS: 2 
XACT_COMMITS: 1 
XACT_D_COMMITS: 0 
XACT_ROLLBACKS: 0 
LOG_FORCES: 0 
DEADLOCKS: 0 
LOCK_TIMEOUTS: 0 
LOCK_GRANTS_IMMED: 17 
LOCK_GRANTS_WAIT: 0 
SYS19: 0 
CMD_PREPARES: 1 
CMD_REPREPARES: 0 
CMD_TEMP_INDEXES: 0 
LAST_LOG_FILE: 0 
REPHOLD_LOG_FILE: -1 
REPHOLD_LOG_OFF: -1 
REP_XACT_COUNT: 0 
REP_CONFLICT_COUNT: 0 
REP_PEER_CONNECTIONS: 0 
REP_PEER_RETRIES: 0 
FIRST_LOG_FILE: 0 
LOG_BYTES_TO_LOG_BUFFER: 64 
LOG_FS_READS: 0 
LOG_FS_WRITES: 0 
LOG_BUFFER_WAITS: 0 
CHECKPOINT_BYTES_WRITTEN: 0 
CURSOR_OPENS: 1 
CURSOR_CLOSES: 1 
SYS3: 0 
SYS4: 0 
SYS5: 0 
SYS6: 0 
CHECKPOINT_BLOCKS_WRITTEN: 0 
CHECKPOINT_WRITES: 0 
REQUIRED_RECOVERY: 0 
SYS11: 0 
SYS12: 1 
TYPE_MODE: 0 
SYS13: 0 
SYS14: 0 
SYS15: 0 
SYS16: 0 
SYS17: 0 
SYS9: 

Creating and executing PL/SQL blocks

You can create and execute PL/SQL blocks from the ttIsql command line.

Set serveroutput on to display results generated from the PL/SQL block:

Command> set serveroutput on

Create an anonymous block that puts a text line in the output buffer. Note that the block must be terminated with a slash (/).

Command> BEGIN
       > DBMS_OUTPUT.put_line(
       >   'Welcome!');
       > END;
       > /
Welcome!
PL/SQL procedure successfully completed.
Command>

See "Introduction to PL/SQL in the TimesTen Database" in Oracle TimesTen In-Memory Database PL/SQL Developer's Guide for more examples.

Using OUT parameters

You can pass data back to applications from PL/SQL by using OUT parameters. This example returns information about how full a TimesTen data store is.

Create the tt_space_info PL/SQL procedure and use SQL to provide values for the permpct, permmaxpct, temppct, and tempmaxpct parameters.

Command> CREATE OR REPLACE PROCEDURE tt_space_info
       >   (permpct    OUT PLS_INTEGER,
       >    permmaxpct OUT PLS_INTEGER,
       >    temppct    OUT PLS_INTEGER,
       >    tempmaxpct OUT PLS_INTEGER) AS
       >    monitor    sys.monitor%ROWTYPE;
       > BEGIN
       >   SELECT * INTO monitor FROM sys.monitor;
       >   permpct := monitor.perm_in_use_size * 100 / monitor.perm_allocated_size;
       >   permmaxpct := monitor.perm_in_use_high_water * 100 / monitor.perm_allocated_size;
       >   temppct := monitor.temp_in_use * 100 / monitor.temp_allocated_size;
       >   tempmaxpct := monitor.temp_in_use_high_water * 100 / monitor.temp_allocated_size;
       > END;
       >/

Procedure created.

Declare the variables and call tt_space_info. The parameter values are passed back to ttIsql so they can be printed:

Command> VAR permpct NUMBER
Command> VAR permpctmax NUMBER
Command> VAR temppct NUMBER
Command> VAR temppctmax NUMBER
Command> BEGIN
       >   tt_space_info(:permpct, :permpctmax, :temppct, :temppctmax);
       > END;
       >/

PL/SQL procedure successfully completed.

Command> PRINT permpct;
PERMPCT              : 4

Command> PRINT permpctmax;
PERMPCTMAX           : 4

Command> PRINT temppct;
TEMPPCT              : 11

Command> PRINT temppctmax;
TEMPPCTMAX           : 11

You can also pass back a statement handle that can be executed by a PL/SQL statement with an OUT refcursor parameter. The PL/SQL statement can choose the query associated with the cursor. The following example opens a refcursor, which randomly chooses between ascending or descending order.

Command> VARIABLE ref REFCURSOR;
Command> BEGIN
    >    IF (mod(dbms_random.random(), 2) = 0) THEN
    >     open :ref for select object_name from SYS.ALL_OBJECTS order by 1 asc;
    >    ELSE
    >     open :ref for select object_name from SYS.ALL_OBJECTS order by 1 desc;
    >    end if;
    >   END;
    >   /

PL/SQL procedure successfully completed.

To fetch the result set from the refcursor, use the PRINT command:

Command> PRINT ref 
REF           : 
< ACCESS$ >
< ALL_ARGUMENTS >
< ALL_COL_PRIVS >
< ALL_DEPENDENCIES >
...
143 rows found.

Or if the result set was ordered in descending order, the following would print:

Command> PRINT ref
REF          : 
< XLASUBSCRIPTIONS >
< WARNING_SETTINGS$ >
< VIEWS >
...
143 rows found.

Displaying information about PL/SQL objects

You can use ttIsql to list PL/SQL functions, procedures and packages in a data store. Commands prefixed by all display all objects. For example, the functions command lists PL/SQL functions that are owned by the user, whereas allfunctions lists all PL/SQL functions. You can optionally specify patterns for object owners and object names.

Use these commands to list PL/SQL objects:

• functions and allfunctions - Lists PL/SQL functions

• procedures and allprocedures - Lists PL/SQL procedures

• packages and allpackages - Lists PL/SQL packages

This example shows that user terry creates a procedure called proc1 while connected to myDSN. Note that a slash character (/) is entered on a new line following the PL/SQL statements.

The procedures command and the allprocedures command show that it is the only PL/SQL procedure in the data store.

$ ttisql myDSN
Copyright (c) 1996-2009, Oracle.  All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.
connect "DSN=myDSN";
Connection successful: DSN=myDSN;UID=terry;DataStore=/scratch/terry/myDSN;DatabaseCharacter Set=AL32UTF8;ConnectionCharacterSet=US7ASCII;PermSize=32;TypeMode=0;
(Default setting AutoCommit=1)
Command> create or replace procedure proc1 as begin null; end;
       > /
Procedure created.
Command> procedures;
  TERRY.PROC1
1 procedure found.
Command> allprocedures;
  TERRY.PROC1
1 procedure found.

Now connect to the same DSN as pat and create a procedure called q. The allprocedures command shows the PL/SQL procedures created by terry and pat.

$ ttisql "dsn=myDSN;uid=PAT"
Copyright (c) 1996-2009, Oracle.  All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.
connect "dsn=myDSN;uid=PAT";
Connection successful: DSN=myDSN;UID=PAT;DataStore=/scratch/terry/myDSN;DatabaseCharacterSet=AL32UTF8;
ConnectionCharacterSet=US7ASCII;PermSize=32;TypeMode=0;
(Default setting AutoCommit=1)
Command> create or replace procedure q as begin null; end;
       > /
Procedure created.
Command> procedures;
  PAT.Q
1 procedure found.
Command> allprocedures;
  TERRY.PROC1
  PAT.Q
2 procedures found.

Viewing and setting data store attributes

You can view and set data store attributes with the ttIsql show and set commands. For a list of the attributes that you can view and set with ttIsql, see "Data Store Attributes" in Oracle TimesTen In-Memory Database Reference.

To view the setting for the Passthrough attribute, enter:

Command> show passthrough;
PassThrough = 0

To change the Passthrough setting, enter:

Command> set passthrough 1;

Viewing and changing query optimizer plans

The built-in showplan command is used to display the query optimizer plans used by the TimesTen Data Manager for executing queries. In addition, ttIsql contains built-in query optimizer hint commands for altering the query optimizer plan. By using the showplan command in conjunction with the built-in commands summarized below, the optimum execution plan can be designed. For detailed information on the TimesTen query optimizer see "The TimesTen Query Optimizer".

• optprofile - Displays the current optimizer hint settings and join order.

• setjoinorder - Sets the join order.

• setuseindex - Sets the index hint.

• tryhash - Enables or disables the use of hash indexes.

• trymergejoin - Enables or disables merge joins.

• trynestedloopjoin - Enables or disables nested loop joins.

• tryserial - Enables or disables serial scans.

• trytmphash - Enables or disables the use of temporary hash indexes.

• trytmptable - Enables or disables the use of an intermediate results table.

• trytmpttree - Enables or disables the use of temporary range indexes.

• tryttree - Enables or disables the use of range indexes.

• tryrowid - Enables or disables the use of rowid scans.

• trytbllocks - Enables or disables the use of table locks.

• unsetjoinorder - Clears the join order.

• unsetuseindex - Clears the index hint.

When using the showplan command and the query optimizer hint commands the autocommit feature must be turned off. Use ttIsql's autocommit built-in command to turn autocommit off.

The example below shows how these commands can be used to change the query optimizer execution plan.

Command> CREATE TABLE T1 (A NUMBER);
Command> CREATE TABLE T2 (B NUMBER);
Command> CREATE TABLE T3 (C NUMBER);
Command>
Command> INSERT INTO T1 VALUES (3);
1 row inserted.
Command> INSERT INTO T2 VALUES (3);
1 row inserted.
Command> INSERT INTO T3 VALUES (3);
1 row inserted.
Command> INSERT INTO T1 VALUES (4);
1 row inserted.
Command> INSERT INTO T2 VALUES (5);
1 row inserted.
Command> INSERT INTO T3 VALUES (6);
1 row inserted.
Command>
Command> autocommit 0;
Command> showplan;
Command> SELECT * FROM T1, T2, T3 WHERE A=B AND B=C AND A=B;

Query Optimizer Plan:

  STEP: 1
  LEVEL: 3
  OPERATION: TblLkSerialScan
  TBLNAME: T1
  IXNAME: <NULL>
  PRED: <NULL>
  OTHERPRED: <NULL>

  STEP: 2
  LEVEL: 3
  OPERATION: TblLkSerialScan
  TBLNAME: T2
  IXNAME: <NULL>
  PRED: <NULL>
  OTHERPRED: T1.A = T2.B AND T1.A = T2.B

  STEP: 3
  LEVEL: 2
  OPERATION: NestedLoop
  TBLNAME: <NULL>
  IXNAME: <NULL>
  PRED: <NULL>
  OTHERPRED: <NULL>

  STEP: 4
  LEVEL: 2
  OPERATION: TblLkSerialScan
  TBLNAME: T3
  IXNAME: <NULL>
  PRED: <NULL>
  OTHERPRED: T2.B = T3.C

  STEP: 5
  LEVEL: 1
  OPERATION: NestedLoop
  TBLNAME: <NULL>
  IXNAME: <NULL>
  PRED: <NULL>
  OTHERPRED: <NULL>

< 3, 3, 3 >
1 row found.
Command> trytbllocks 0;
Command> tryserial 0;
Command> SELECT * FROM T1, t2, t3 WHERE A=B AND B=C AND A=B;

Query Optimizer Plan:

  STEP: 1
  LEVEL: 3
  OPERATION: TmpTtreeScan
  TBLNAME: T1
  IXNAME: <NULL>
  PRED: <NULL>
  OTHERPRED: <NULL>

  STEP: 2
  LEVEL: 3
  OPERATION: TmpTtreeScan
  TBLNAME: T2
  IXNAME: <NULL>
  PRED: T2.B >= T1.A
  OTHERPRED: <NULL>

  STEP: 3
  LEVEL: 2
  OPERATION: MergeJoin
  TBLNAME: <NULL>
  IXNAME: <NULL>
  PRED: T1.A = T2.B AND T1.A = T2.B
  OTHERPRED: <NULL>

  STEP: 4
  LEVEL: 2
  OPERATION: TmpTtreeScan
  TBLNAME: T3
  IXNAME: <NULL>
  PRED: <NULL>
  OTHERPRED: T2.B = T3.C

  STEP: 5
  LEVEL: 1
  OPERATION: NestedLoop
  TBLNAME: <NULL>
  IXNAME: <NULL>
  PRED: <NULL>
  OTHERPRED: <NULL>

< 3, 3, 3 >
1 row found.
Command>

In this example a query against three tables is executed and the query optimizer plan is displayed. The first version of the query simply uses the query optimizer's default execution plan. However, in the second version the trytbllocks and tryserial built-in hint commands have been used to alter the query optimizer's plan. Instead of using serial scans and nested loop joins the second version of the query uses temporary index scans and merge joins.

In this way the showplan command in conjunction with ttIsql's built-in query optimizer hint commands can be used to quickly determine which execution plan should be used to meet application requirements.

Timing ODBC function calls

Information on the time required to execute common ODBC function calls can be displayed by using ttIsql's timing command. When the timing feature is enabled many built-in ttIsql commands will report the elapsed execution time associated with the primary ODBC function call corresponding to the ttIsql command that is executed.

For example, when executing ttIsql's connect command several ODBC function calls are executed, however, the primary ODBC function call associated with connect is SQLDriverConnect and this is the function call that is timed and reported as shown below.

Command> timing 1;
Command> connect "DSN=MY_DSN";
Connection successful: DSN=MY_DSN;DataStore=E:\ds\MY_DSN;DRIVER=E:\WINNT\System32\ TTdv1121.dll;
(Default setting AutoCommit=1)
Execution time (SQLDriverConnect) = 1.2626 seconds.
Command>

In the example above, the SQLDriverConnect call took about 1.26 seconds to execute.

When using the timing command to measure queries, the time required to execute the query plus the time required to fetch the query results is measured. To avoid measuring the time to format and print query results to the display, set the verbosity level to 0 before executing the query.

Command> timing 1;
Command> verbosity 0;
Command> SELECT * FROM T1;
Execution time (SQLExecute + FetchLoop) = 0.064210 seconds.
Command>

Working with prepared and parameterized SQL statements

Preparing a SQL statement just once and then executing it multiple times is much more efficient for TimesTen applications than re-preparing the statement each time it is to be executed. ttIsql has a set of built-in commands to work with prepared SQL statements. These commands are summarized below:

• prepare - Prepares a SQL statement. Corresponds to a SQLPrepare ODBC call.

• exec - Executes a previously prepared statement. Corresponds to a SQLExecute ODBC call.

• execandfetch - Executes a previously prepared statement and fetches all result rows. Corresponds to a SQLExecute call followed by one or more calls to SQLFetch.

• fetchall - Fetches all result rows for a previously executed statement. Corresponds to one or more SQLFetch calls.

• fetchone - Fetches only one row for a previously executed statement. Corresponds to exactly one SQLFetch call.

• close - Closes the result set cursor on a previously executed statement that generated a result set. Corresponds to a SQLFreeStmt call with the SQL_CLOSE option.

• free - Closes a previously prepared statement. Corresponds to a SQLFreeStmt call with the SQL_DROP option.

• describe - Describes the prepared statement including the input parameters and the result columns.

The ttIsql utility prepared statement commands also handle SQL statement parameter markers. When parameter markers are included in a prepared SQL statement, ttIsql will automatically prompt for the value of each parameter in the statement at execution time.

The example below uses the prepared statement commands of the ttIsql utility to prepare an INSERT statement into a table containing a NUMBER and a CHAR column. The statement is prepared and then executed twice with different values for each of the statement's two parameters. The ttIsql utility timing command is used to display the elapsed time required to executed the primary ODBC function call associated with each command.

Command> connect "DSN=MY_DSN";
Connection successful: DSN=MY_DSN;DataStore=E:\ds\MY_DSN;DRIVER=E:\WINNT\Sys tem32\TTdv1121.dll;
(Default setting AutoCommit=1)
Command> timing 1;Command> create table t1 (key number not null primary key, value char(20));Execution time (SQLExecute) = 0.007247 seconds.Command> prepare insert into t1 values (:f, :g);Execution time (SQLPrepare) = 0.000603 seconds.Command> exec;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 'F' (NUMBER) > 1;Enter Parameter 2 'G' (CHAR) > 'abc';1 row inserted.Execution time (SQLExecute) = 0.000454 seconds.Command> exec;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 help command.Enter Parameter 1 'F' (NUMBER) > 2;Enter Parameter 2 'G' (CHAR) > 'def';1 row inserted.Execution time (SQLExecute) = 0.000300 seconds.Command> free;Command> select * from t1;< 1, abc                  >< 2, def                  >2 rows found.Execution time (SQLExecute + Fetch Loop) = 0.000226 seconds.Command> disconnect;Disconnecting...Execution time (SQLDisconnect) = 2.911396 seconds. 
Command>

In the example above, the prepare command is immediately followed by the SQL statement to prepare. Whenever a SQL statement is prepared in ttIsql, a unique command ID is assigned to the prepared statement. The ttIsql utility uses this ID to keep track of multiple prepared statements. A maximum of 256 prepared statements can exist in a ttIsql session simultaneously. When the free command is executed, the command ID is automatically disassociated from the prepared SQL statement.

To see the command IDs generated by ttIsql when using the prepared statement commands, set the verbosity level to 4 using the verbosity command before preparing the statement, or use the describe * command to list all prepared statements with their IDs.

Command IDs can be referenced explicitly when using ttIsql's prepared statement commands. For a complete description of the syntax of ttIsql's prepared statement commands see the Oracle TimesTen In-Memory Database Reference or type help at the ttIsql command prompt.

The example below prepares and executes a SELECT statement with a predicate containing one NUMBER parameter. The fetchone command is used to fetch the result row generated by the statement. The showplan command is used to display the execution plan used by the TimesTen query optimizer when the statement is executed. In addition, the verbosity level is set to 4 so that the command ID used by ttIsql to keep track of the prepared statement is displayed.

Command> connect "DSN=MY_DSN";
Connection successful: DSN=MY_DSN;DataStore=E:\ds\MY_DSN;DRIVER=E:\WINNT\Sys tem32\TTdv1121.dll;
(Default setting AutoCommit=1)
The command succeeded.
Command> CREATE TABLE T1 (KEY NUMBER NOT NULL PRIMARY KEY, VALUE CHAR (64));
The command succeeded.
Command> INSERT INTO T1 VALUES (1, 'abc');
1 row inserted.
The command succeeded.
Command> autocommit 0;
The command succeeded.
Command> showplan 1;
The command succeeded.
Command> verbosity 4;
The command succeeded.
Command> prepare SELECT * FROM T1 WHERE KEY=?;
Assigning new prepared command id = 0.

Query Optimizer Plan:

  STEP: 1
  LEVEL: 1
  OPERATION: RowLkHashScan
  TBLNAME: T1
  IXNAME: T1
  PRED: T1.KEY = qmark_1
  OTHERPRED: <NULL>

The command succeeded.
Command> exec;

Executing prepared command id = 0.
Type '?;' for help on entering parameter values.
Type '*;' to abort the parameter entry process.

Enter Parameter 1 (NUMBER) >1;
The command succeeded.
Command> fetchone;
Fetching prepared command id = 0.
< 1, abc >
1 row found.
The command succeeded.
Command> close;
Closing prepared command id = 0.
The command succeeded.
Command> free;
Freeing prepared command id = 0.
The command succeeded.
Command> commit;
The command succeeded.
Command> disconnect;
Disconnecting...
The command succeeded.
Command>

Defining default settings with the TTISQL environment variable

The ttIsql utility can be customized to automatically execute a set of command line options every time a ttIsql session is started from the command prompt. This is accomplished by setting an environment variable called TTISQL to the value of the ttIsql command line that you prefer. A summary of ttIsql command line options is shown below. For a complete description of the ttIsql command line options, see the Oracle TimesTen In-Memory Database Reference.

Usage: ttIsql [-h | -help | -helpcmds | -helpfull | -V]
              [-connStr <connection_string>]
              [-f <filename>]
              [-v <verbosity>]
              [-e <initialization_commands>]
              [-interactive]
              [-N <ncharencoding>]
              [-wait]

The TTISQL environment variable has the same syntax requirements as the ttIsql command line. When ttIsql starts up it reads the value of the TTISQL environment variable and applies all options specified by the variable to the current ttIsql session. If a particular command line option is specified in both the TTISQL environment variable and the command line then the command line version will always take precedence.

The procedure for setting the value of an environment variable differs based on the platform and shell that ttIsql is started from. As an example, setting the TTISQL environment variable on Windows could look like this:

C:\>set TTISQL=-connStr "DSN=MY_DSN" -e "autocommit 0;dssize;"

In this example, ttIsql will automatically connect to a DSN called MY_DSN, turn off autocommit and display the size of the data store as shown below:

C:\>ttIsql

ttIsql (c) 1996-2009, Oracle. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.

Command> connect "DSN=MY_DSN";
Connection successful: DSN=MY_DSN;DataStore=E:\ds\MY_DSN;DRIVER=E:\WINNT\System32\TTdv1121.dll;
(Default setting AutoCommit=1)
Command> autocommit 0;

Command> alltables;
  SYS.ACCESS$
  SYS.ARGUMENT$
  SYS.CACHE_GROUP
  SYS.COLUMNS
  SYS.COLUMN_HISTORY
  SYS.COL_STATS
  SYS.DEPENDENCY$
  SYS.DIR$
  SYS.DUAL
  SYS.ERROR$
  SYS.IDL_CHAR$
  SYS.IDL_SB4$
  SYS.IDL_UB1$
  SYS.IDL_UB2$
  SYS.INDEXES
  SYS.MONITOR
...
59 tables found.
Command>

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>