| Oracle® TimesTen In-Memory Database Operations Guide 11g Release 2 (11.2.2) Part Number E21633-05 |
|
|
PDF · Mobi · ePub |
| Oracle® TimesTen In-Memory Database Operations Guide 11g Release 2 (11.2.2) Part Number E21633-05 |
|
|
PDF · Mobi · ePub |
The TimesTen ttIsql utility is a general tool for working with a TimesTen data source. The ttIsql command line interface is used to execute SQL statements and built-in ttIsql commands to perform various operations. Some common tasks that are typically accomplished using ttIsql include:
Database setup and maintenance. Creating tables and indexes, altering existing tables and updating table statistics can be performed quickly and easily using ttIsql.
Retrieval of information on database structures. The definitions for tables, indexes and cache groups can be retrieved using built-in ttIsql commands. In addition, the current size and state of the database can be displayed.
Optimizing database operations. The ttIsql utility can be used to alter and display query optimizer plans for the purpose of tuning SQL operations. The time required to execute various ODBC function calls can also be displayed.
The following sections describe how the ttIsql utility is used to perform these types of tasks:
For more information on ttIsql commands, see the "ttIsql" section in the Oracle TimesTen In-Memory Database Reference.
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 database and purging log files.
Initializing a database 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 database problems by examining database statistics.
Any other database 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. The following example starts ttIsql in interactive mode and then connects to a TimesTen database by executing the connect command with the MY_DSN DSN.
C:\>ttIsql Copyright (c) 1996-2011, Oracle. All rights reserved. Type ? or "help" for help, type "exit" to quit ttIsql. Command> connect MY_DSN; Connection successful: DSN=MY_DSN;DataStore=E:\ds\MY_DSN; DRIVER=E:\WINNT\System32\TTdv1122.dll; (Default setting AutoCommit=1) Command>
When connecting to the database using ttIsql, you can also specify the DSN or connection string on the ttIsql command line. The connect command is implicitly executed.
C:\>ttIsql MY_DSN Copyright (c) 1996-2011, Oracle. All rights reserved. Type ? or "help" for help, type "exit" to quit ttIsql. connect "DSN=MY_DSN"; Connection successful: DSN=MY_DSN;DataStore=E:\ds\MY_DSN; DRIVER=E:\WINNT\System32\TTdv1122.dll; (Default setting AutoCommit=1) 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 Copyright (c) 1996-2011, 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\TTdv1122.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>
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 "ttIsql" section in the Oracle TimesTen In-Memory Database Reference.
Usage: ttIsql [-h | -help | -helpcmds | -helpfull | -V]
ttIsql [-f <filename>]
[-v <verbosity>]
[-e <commands>]
[-interactive]
[-N <ncharEncoding>]
[-wait]
[{<DSN> | -connstr <connection_string>}]
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 database as shown below:
C:\>ttIsql Copyright (c) 1996-2011, 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\TTdv1122.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>
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>
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
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:
To use the emacs binding, create a file ~/.editrc and put "bind" on the last line of the file, run ttIsql. The editline lib will print the current bindings.
The keystrokes when using ttIsql with the emacs binding are:
| Keystroke | Action |
|---|---|
| <Left-Arrow> | Move the insertion point left. Back up. |
| <Right-Arrow> | Move the insertion point right. Move forward. |
| <Up-Arrow> | Scroll to the command prior to the one being displayed. Places the cursor at the end of the line. |
| <Down-Arrow> | Scroll to a more recent command history item and put the cursor at the end of the line. |
| <Ctrl-A> | Move the insertion point to the beginning of the line. |
| <Ctrl-E> | Move the insertion point to the end of the line. |
| <Ctrl-K> | "Kill" (Save and erase) the characters on the command line from the current position to the end of the line. |
| <Ctrl-Y> | "Yank" (Restore) the characters previously saved and insert them at the current insertion point. |
| <Ctrl-F> | Forward char - move forward 1 (see Right Arrow) |
| <Ctrl-B> | Backward char - move back 1 (see Left Arrow) |
| <Ctrl-P> | Previous History (see Up Arrow) |
| <Ctrl-N> | Next History (see up Down Arrow) |
To use the vi bindings, create a file ${HOME}/.editrc and put "bind-v" in the file, run ttIsql. To get the current settings, create a file ${HOME}/.editrc and put "bind" on the last line of the file. When you execute ttIsql, the editline lib will print the current bindings.
The keystrokes when using ttIsql with the vi binding are:
| Keystroke | Action |
|---|---|
| <Left-Arrow>, h | Move the insertion point left (back up) |
| <Right-Arrow>, l | Move the insertion point right (forward) |
| <Up-Arrow>, k | Scroll to the prior command in the history and put the cursor at the end of the line. |
| <Down-Arrow>, j | Scroll to the next command in the history and put the cursor at the end of the line. |
| ESC | Vi Command mode |
| 0, $ | Move the insertion point to the beginning of the line, Move to end of the line. |
| i, I | Insert mode, Insert mode at beginning of the line |
| a, A | Add ("Insert after") mode, Append at end of line |
| R | Replace mode |
| C | Change to end of line |
| B | Move to previous word |
| e | Move to end of word |
| <Ctrl-P> | Previous History (see Up Arrow) |
| <Ctrl-N> | Next History (see up Down Arrow) |
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>
You can save the list of commands that ttIsql stores by using the savehistory command:
Command> savehistory history.txt;
If the output file already exists, use the -a option to append the new command history to the file or the -f option to overwrite the file. The next example shows how to append new command history to an existing file.
Command> savehistory -a history.txt;
You can clear the list of commands that ttIsql stores by using the clearhistory command:
Command> clearhistory;
The ttIsql utility supports the character sets listed in "Supported character sets" in the Oracle TimesTen In-Memory Database Reference. The ability of ttIsql to display characters depends on the native operating system 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.
There are several ttIsql commands that display information on database structures. The most useful commands are summarized below:
dssize - Reports the current sizes of the permanent and temporary database partitions.
tablesize - Displays the size of tables that have been analyzed with the ttComputeTabSizes tool.
monitor - Displays a summary of the current state of the database.
Use the describe command to display information on individual database objects. Displays parameters for prepared SQL statements and built-in procedures. The argument to the describe command can be the name of a table, cache group, view, materialized view, materialized view log, sequence, synonym, 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 database. 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; Cache Group CACHEUSER.READCACHE: Cache Group Type: Read Only Autorefresh: Yes Autorefresh Mode: Incremental Autorefresh State: Paused Autorefresh Interval: 5 Seconds Autorefresh Status: ok Aging: No aging defined Root Table: ORATT.READTAB Table Type: Read Only Cache Group CACHEUSER.WRITECACHE: Cache Group Type: Asynchronous Writethrough global (Dynamic) Autorefresh: No Aging: LRU on Root Table: ORATT.WRITETAB Table Type: Propagate 2 cache groups found.
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 database.
Command> dssize PERM_ALLOCATED_SIZE: 32768 PERM_IN_USE_SIZE: 8615 PERM_IN_USE_HIGH_WATER: 8615 TEMP_ALLOCATED_SIZE: 40960 TEMP_IN_USE_SIZE: 5794 TEMP_IN_USE_HIGH_WATER: 8959
The tablesize command displays the detailed analysis of the amount of space used by a table. Once you execute the ttComputeTabSizes built-in procedure, which analyzes the table size of the indicated tables, the tablesize command displays the total size data for all analyzed tables.
Executing the tablesize command with no arguments displays available sizing information for all tables that have had the ttComputeTabSizes computation run. When you provide a table as an argument, tablesize displays available sizing only for the indicated table.
The syntax for tablesize is as follows:
tablesize [[owner_name_pattern.]table_name_pattern]
The following example invokes the ttComputeTabSizes built-in procedure to calculate the table size of the employees table. Then, the tablesize command displays the sizing information gathered for the employees table.
Command> call ttComputeTabSizes('employees');
Command> tablesize employees;
Sizes of USER1.EMPLOYEES:
INLINE_ALLOC_BYTES: 60432
NUM_USED_ROWS: 107
NUM_FREE_ROWS: 149
AVG_ROW_LEN: 236
OUT_OF_LINE_BYTES: 0
METADATA_BYTES: 1304
TOTAL_BYTES: 61736
LAST_UPDATED: 2011-06-29 12:55:28.000000
1 table found.
These values provide insights into overhead and how the total space is used for the table.
For example:
The NUM_FREE_ROWS value describes the number of rows allocated for the table, but not currently in use. Space occupied by free rows cannot be used by the system for storing other system objects or structures.
Use the TOTAL_BYTES value to calculate how much permanent space your table occupies.
LAST_UPDATED is the time of the last size computation. If you want a more recent computation, re-execute ttComputeTabSizes and display the new output.
You can find a description for each calculated value in the "SYS.ALL_TAB_SIZES" section in the Oracle TimesTen In-Memory Database System Tables and Views Reference.
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 database was loaded into memory.
Command> monitor; TIME_OF_1ST_CONNECT: Wed Apr 20 10:34:17 2011 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:
You can use ttIsql to list tables, indexes, views, sequences, synonyms, PL/SQL functions, procedures and packages in a database. Commands prefixed by all display all of this type of object. 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 database objects:
tables and alltables - Lists tables
indexes and allindexes - Lists indexes
views and allviews - Lists views
sequences and allsequences - Lists sequences
synonyms and allsynonyms - Lists synonyms
functions and allfunctions - Lists PL/SQL functions
procedures and allprocedures - Lists PL/SQL procedures
packages and allpackages - Lists PL/SQL packages
Note:
For details on each of these commands, see the "ttIsql" section in the Oracle TimesTen In-Memory Database Reference.The following example demonstrates the procedures and allprocedures commands. 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 database.
$ ttisql myDSN
Copyright (c) 1996-2011, 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-2011, 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.
You can view and set connection attributes with the ttIsql show and set commands. For a list of the attributes that you can view and set with ttIsql, see "Connection 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;
The ttIsql utility has several built-in commands for managing transactions. These commands are summarized below:
autocommit - Turns on or off the autocommit feature. This can also be set as an attribute of the set command.
commitdurable - Commits the current transaction and ensures that the committed work will be recovered in case of database failure.
isolation - Changes the transaction isolation level. This can also be set as an attribute of the set command.
sqlquerytimeout - Specifies the number of seconds to wait for a SQL statement to execute before returning to the application. This can also be set as an attribute of the set command.
When starting ttIsql, the autocommit feature is turned on by default, even within a SQL script. In this mode, every SQL operation against the database is committed automatically. When autocommit is turned off, then automatic commit depends on the setting for the DDLCommitBehavior connection attribute and the user executing DDL. For more information, see "Relationship between autocommit and DDLCommitBehavior".
To turn the autocommit feature off, execute the ttIsql autocommit command with an argument of 0. When autocommit is turned off, transactions must be committed or rolled back manually by executing the ttIsql commit, commitdurable or rollback commands. The commitdurable command ensures that the transaction's effect is preserved in case of database failure. If autocommit is off when ttIsql exits, any uncommitted statements will be rolled back and reported by ttIsql.
The ttIsql 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 modified without an argument then the current isolation level is reported.
The ttIsql sqlquerytimeout command sets the timeout period for SQL statements. If the execution time of a SQL statement exceeds the number of seconds set by the sqlquerytimeout command, the SQL statement is not executed and an 6111 error is generated. For details, see "Setting a timeout duration for SQL statements" in the Oracle TimesTen In-Memory Database Java Developer's Guide and "Setting a timeout duration for SQL statements" in the Oracle TimesTen In-Memory Database C Developer's Guide.
Note:
TimesTen rollback and query timeout features do not stop IMDB Cache operations that are being processed on Oracle. This includes passthrough statements, flushing, manual loading, manual refreshing, synchronous writethrough, propagating and dynamic loading.The following example demonstrates the common use of the ttIsql built-in transaction management commands.
E:\>ttIsql Copyright (c) 1996-2011, 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\TTdv1122.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.
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\System32\TTdv1122.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 "ttIsql" section in 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\TTdv1122.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>
Note:
For information about usingttIsql with PL/SQL host variables, see "Introduction to PL/SQL in the TimesTen Database" in Oracle TimesTen In-Memory Database PL/SQL Developer's Guide.The following sections describe how to declare, set and use bind variables in ttIsql:
You can declare and set variables and arrays in ttIsql that can be referenced in a SQL statement, SQL script, or PL/SQL block. The variables declared using the variable and setvariable command must be one of the following data types: NUMBER, CHAR, NCHAR, VARCHAR2, NVARCHAR2, CLOB, NCLOB, BLOB, or REFCURSOR. However, when binding arrays, Timesten supports only binding arrays of the NUMBER, CHAR, NCHAR, VARCHAR2, or NVARCHAR2 data types.
Note:
All variables that are declared exist for the life of the ttIsql session. However, if you declare a new variable with the same name, the new variable replaces the old variable.The following examples declare bind variables with the variable or var command for a number, character string, and an array. Each is assigned to a value either when declared or by using the setvariable or setvar command.
Note:
For details on the syntax for these commands, see "ttIsql" in the Oracle TimesTen In-Memory Database Reference.Command> VARIABLE house_number NUMBER := 268; Command> PRINT house_number; HOUSE_NUMBER : 268 Command> VARIABLE street_name VARCHAR2(15); Command> SETVARIABLE street_name := 'Oracle Parkway'; Command> VARIABLE occupants[5] VARCHAR2(15); Command> SETVARIABLE occupants[1] := 'Pat'; Command> SETVARIABLE occupants[2] := 'Terry'; Command> PRINT occupants; OCCUPANTS : ARRAY [ 5 ] (Current Size 2) OCCUPANTS[1] : Pat OCCUPANTS[2] : Terry
The following is an example of binding multiple values in an array using square brackets to delineate the values and commas to separate each value for the array:
Command> VARIABLE occupants[5] VARCHAR2(15) := ['Pat', 'Terry']; Command> PRINT occupants; OCCUPANTS : ARRAY [ 5 ] (Current Size 2) OCCUPANTS[1] : Pat OCCUPANTS[2] : Terry
When using array binds, PL/SQL enables you to bind each variable to a PL/SQL variable with the following declaration, where TypeName is any unique identifier for the PL/SQL data type and DataType can be specified as CHAR, NCHAR, VARCHAR2, or NVARCHAR2.
TYPE TypeName IS TABLE OF DataType(<precision>) INDEX BY BINARY_INTEGER;
If the variable is declared as array of NUMBER, you can bind it to a PL/SQL variable of the following data types: NUMBER, INTEGER, FLOAT, or DOUBLE PRECISION. To do so, use the appropriate declaration:
TYPE TypeName IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; TYPE TypeName IS TABLE OF INTEGER INDEX BY BINARY_INTEGER; TYPE TypeName IS TABLE OF FLOAT INDEX BY BINARY_INTEGER; TYPE TypeName IS TABLE OF DOUBLE PRECISION INDEX BY BINARY_INTEGER;
The following example declares the occupants VARCHAR2 array, which is then declared and used within a PL/SQL block:
Command> VARIABLE occupants[5] VARCHAR2(15);
Command> SETVARIABLE occupants[1] := 'Pat';
Command> SETVARIABLE occupants[2] := 'Terry';
Command> DECLARE
> TYPE occuname IS TABLE OF VARCHAR2(15) INDEX BY BINARY_INTEGER;
> x occuname;
> BEGIN
> x := :occupants;
> FOR LROW IN x.FIRST..x.LAST LOOP
> x(LROW) := x(LROW) || ' Doe';
> END LOOP;
> :occupants := x;
> END;
> /
PL/SQL procedure successfully completed.
Command> PRINT occupants;
OCCUPANTS : ARRAY [ 5 ] (Current Size 2)
OCCUPANTS[1] : Pat Doe
OCCUPANTS[2] : Terry Doe
When you set autovariables on in ttIsql, TimesTen creates an automatic bind variable named after each column in the last fetched row. An automatic bind variable can be used in the same manner of any bind variable.
The following example selects all rows from the employees table. Since all columns are retrieved, automatic variables are created and named for each column. The bind variable contains the last value retrieved for each column.
Command> SET AUTOVARIABLES ON; Command> SELECT * FROM employees; ... < 204, Hermann, Baer, HBAER, 515.123.8888, 1994-06-07 00:00:00, PR_REP, 10000, <NULL>, 101, 70 > < 205, Shelley, Higgins, SHIGGINS, 515.123.8080, 1994-06-07 00:00:00, AC_MGR, 12000, <NULL>, 101, 110 > < 206, William, Gietz, WGIETZ, 515.123.8181, 1994-06-07 00:00:00, AC_ACCOUNT, 8300, <NULL>, 205, 110 > Command> PRINT; EMPLOYEE_ID : 206 FIRST_NAME : William LAST_NAME : Gietz EMAIL : WGIETZ PHONE_NUMBER : 515.123.8181 HIRE_DATE : 1994-06-07 00:00:00 JOB_ID : AC_ACCOUNT SALARY : 8300 COMMISSION_PCT : <NULL> MANAGER_ID : 205 DEPARTMENT_ID : 110
If you provide an alias for a column name, the automatic bind variable name uses the alias, rather than the column name.
Command> SET AUTOVARIABLES ON; Command> SELECT employee_id ID, First_name SURNAME, last_name LASTNAME FROM employees; ID, SURNAME, LASTNAME ... < 204, Hermann, Baer > < 205, Shelley, Higgins > < 206, William, Gietz > 107 rows found. Command> PRINT; ID : 206 SURNAME : William LASTNAME : Gietz
For any query that fetches data without a known named column, set columnlabels on to show the column names. The following example shows that the columns returns from ttConfiguration built-in procedure are paramname and paramvalue.
Command> SET AUTOVARIABLES ON;
Command> SET COLUMNLABELS ON;
Command> call TTCONFIGURATION('PLSQL');
PARAMNAME, PARAMVALUE
< PLSQL, 1 >
1 row found.
Command> IF :paramvalue = 1 THEN "e:PLSQL is enabled";
PLSQL is enabled
Command> IF NOT :paramvalue = 1 THEN "e:PLSQL is not enabled";
You can also use the describe command to show the column names. The following example uses the describe command to display the column names for the ttConfiguration built-in procedure.
Command> DESCRIBE TTCONFIGURATION;
Procedure TTCONFIGURATION:
Parameters:
PARAMNAME TT_VARCHAR (30)
Columns:
PARAMNAME TT_VARCHAR (30) NOT NULL
PARAMVALUE TT_VARCHAR (1024)
1 procedure found.
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 the Oracle TimesTen In-Memory Database PL/SQL Developer's Guide for more examples.
You can pass data back to applications from PL/SQL by using OUT parameters. This example returns information about how full a TimesTen database 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_size * 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> VARIABLE permpct NUMBER
Command> VARIABLE permpctmax NUMBER
Command> VARIABLE temppct NUMBER
Command> VARIABLE 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.
The IF-THEN-ELSE command construct enables you to implement conditional branching logic in a ttIsql session. The IF command tests a condition and decides whether to execute commands within the THEN clause or the optional ELSE clause. The commands executed can be SQL statements, SQL scripts, PL/SQL blocks, or TimesTen utilities.
Note:
For details on the syntax of theIF-THEN-ELSE construct, see the "ttIsql" section in the Oracle TimesTen In-Memory Database Reference.The following example creates and tests a bind variable to see if PL/SQL is enabled. It uses the autovariables command to create the bind variable from the result of the call to ttConfiguration. The value can be tested within the IF-THEN-ELSE conditional by testing the paramvalue variable.
Note:
For more details on theautovariables command, see "Automatically creating bind variables for retrieved columns".Command> SET AUTOVARIABLES ON;
Command> CALL TTCONFIGURATION('PLSQL');
PARAMNAME, PARAMVALUE
< PLSQL, 1 >
1 row found.
Command> IF :paramvalue = 1 THEN "e:PLSQL is enabled"
> ELSE "e:PLSQL is not enabled";
PLSQL is enabled
The following example checks to see that the employees table exists. If it does not, it executes the SQL script that creates the employees table; otherwise, a message is printed out.
Command> IF 0 = "SELECT COUNT(*) FROM SYS.TABLES
> WHERE TBLNAME LIKE 'employees';"
> THEN "e:EMPLOYEES table already exists"
> ELSE "@HR_CRE_TT.SQL;";
EMPLOYEES table already exists
There may be a situation where you want to load the results of a SQL query from a back-end Oracle database into a single table on TimesTen without creating a cache grid, cache group, and cache table to contain the results. TimesTen provides the tools that will execute a user-provided SELECT statement on Oracle and load the result set into a table on TimesTen.
The following are the major steps that are performed to accomplish this task:
Create a table with the correct columns and data types on TimesTen.
Provide a SELECT statement that will be executed on Oracle to generate the desired result set.
Load the result set into the table on TimesTen.
TimesTen provides two methods to accomplish these tasks:
The ttIsql utility provides the createandloadfromoraquery command that, once provided the TimesTen table name and the SELECT statement, will automatically create the TimesTen table, execute the SELECT statement on Oracle, and load the result set into the TimesTen table. This command is described fully in "Use ttIsql to create a table and load SQL query results".
The ttTableSchemaFromOraQueryGet built-in procedure evaluates the user-provided SELECT statement to generate a CREATE TABLE statement that can be executed to create a table on TimesTen, which would be appropriate to receive the result set from the SELECT statement. The ttLoadFromOracle built-in procedure executes the SELECT statement on Oracle and loads the result set into the TimesTen table. These built-in procedures are described fully in "Use TimesTen built-in procedures to recommend a table and load SQL query results".
Both methods require the following:
Both the TimesTen and Oracle databases involved must be configured with the same national database character set.
When you connect to the TimesTen database, the connection must contain the same connection attributes that are required when using cache groups, as follows:
The user name, which must be the same on both the TimesTen and Oracle databases
Note:
The correct privileges must be granted to these users on each database for the SQL statements that will be executed on their behalf.The correct passwords for each user as appropriate in the PWD and OraclePWD connection attributes
The OracleServiceName connection attributes that identifies the Oracle database instance
For either method, the user provides the following:
The table name on the TimesTen database where the results of the SQL query will be loaded. If the owner of the table is not provided, the table is created with the current user as the owner. The table name is not required to be the same name as the table name on the Oracle database against which the SQL statement is executed. This table does not require a primary key. If the table already exists, a warning will be issued and the retrieved rows are appended to the table.
Optionally, the number of parallel threads that you would like to be used in parallel when loading the table with the result set. This defaults to four.
The SQL SELECT statement that will be executed against the Oracle database to obtain the required rows. The tables specified within this SELECT statement must be fully qualified, unless the tables are within the schema of the current Oracle user. The query cannot have any parameter bindings.
The SELECT list should contain either simple column references or column aliases. For example, any expressions in the SELECT list should be provided with a column alias. You can also use the column alias to avoid duplication of column names in the result table. For example, instead of using SELECT C1+1 FROM T1, use SELECT C1 + 1 C2 FROM T1, which would create a column named C2.
TimesTen evaluates the SELECT statement and uses the column names, data types, and nullability information to create the table on TimesTen into which the result set will be loaded. The column names and data types (either the same or mapped) are taken from the tables on Oracle involved in the SELECT statement. However, other Oracle table definition information (such as DEFAULT values, primary key, foreign key relationships, and so on) are not used when creating the CREATE TABLE statement for the TimesTen table.
Note:
If the evaluation returns any unsupported data types or if the query cannot be executed on Oracle, such as from a syntax error, a warning is logged and a comment is displayed for the unsupported column in the output. However, if the data type is not supported by TimesTen, you can cast the data type directly in theSELECT list to a TimesTen supported data type.The load process does not check that the column data types and sizes in the TimesTen table match the data types and sizes of the result set. Instead, the insert is attempted and if the column data types cannot be mapped, an error is returned. If the retrieved Oracle data from the SQL query exceeds the TimesTen column size, the data is truncated without a warning.
The load is automatically committed every 256 rows. If an error is encountered during the load, it will terminate the load, but will not roll back any committed transactions. Any errors returned from the Oracle database are reported in the same manner as when using cache groups.
Because you can use these methods to load into an existing TimesTen table, the following lists the restrictions for this situation:
You cannot load into system tables, dictionary tables, temporary tables, detail tables of views, materialized view tables, materialized view log tables, or tables already in a cache group. In addition, you cannot use a synonym for the table name.
If you load the result set into an existing table that is the referencing table (child table) of a foreign key constraint, the constraint will not be validated. As a result, rows that are missing a parent row may be loaded. Instead, you should verify all foreign keys after the table is loaded.
The following sections provide more details on each individual method:
The ttIsql utility provides the createandloadfromoraquery command, which takes a table name, the number of parallel threads, and a SELECT statement that will be executed on Oracle as input parameters. From these parameters, TimesTen performs the following:
Evaluates the SQL query and creates an appropriate table, if not already created, with the provided table name where the columns are those named in the SQL query with the same (or mapped) data types as those in the Oracle database tables from which the resulting data is retrieved.
Loads the results of the SQL query as executed on the Oracle database into this table. The call returns a single number indicating the number of rows loaded. Any subsequent calls to this command append retrieved rows to the table.
Note:
See thecreateandloadfromoraquery command in "ttIsql" in the Oracle TimesTen In-Memory Database Reference for full details on syntax, requirements, restrictions, and required privileges.The following ttIsql example connects providing the DSN, user name, password for the user on TimesTen, and the password for the same user name on the Oracle database. Then, it executes the createandloadfromoraquery command to evaluate the SELECT statement. The employees table is created on TimesTen with the same column names and data types as the columns and data types of the retrieved rows. Then, the table is populated with the result set from Oracle over two parallel threads.
ttisql "DSN=cachedb1_1122;UID=oratt;PWD=timesten;OraclePWD=oracle"
Copyright (c) 1996-2011, Oracle. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql
connect "DSN=mydb;UID=oratt;PWD=timesten;OraclePWD=oracle";
Connection successful: DSN=mydb;UID=oratt;
DataStore=/timesten/install/info/DemoDataStore/mydb;DatabaseCharacterSet=WE8DEC;
ConnectionCharacterSet=US7ASCII;DRIVER=/timesten/install/lib/libtten.so;
PermSize=40;TempSize=32;TypeMode=0;OracleNetServiceName=inst1;
(Default setting AutoCommit=1)
Command> createandloadfromoraquery employees 2 SELECT * FROM hr.employees;
Mapping query to this table:
CREATE TABLE "ORATT"."EMPLOYEES" (
"EMPLOYEE_ID" number(6,0) NOT NULL,
"FIRST_NAME" varchar2(20 byte),
"LAST_NAME" varchar2(25 byte) NOT NULL,
"EMAIL" varchar2(25 byte) NOT NULL,
"PHONE_NUMBER" varchar2(20 byte),
"HIRE_DATE" date NOT NULL,
"JOB_ID" varchar2(10 byte) NOT NULL,
"SALARY" number(8,2),
"COMMISSION_PCT" number(2,2),
"MANAGER_ID" number(6,0),
"DEPARTMENT_ID" number(4,0)
)
Table employees created
107 rows loaded from oracle.
Execute the DESCRIBE command to show the new table:
Note:
In this example, the table owner is not specified, so it defaults to the current user. In this example, the current user isoratt.Command> DESCRIBE employees;
Table ORATT.EMPLOYEES:
Columns:
EMPLOYEE_ID NUMBER (6) NOT NULL
FIRST_NAME VARCHAR2 (20) INLINE
LAST_NAME VARCHAR2 (25) INLINE NOT NULL
EMAIL VARCHAR2 (25) INLINE NOT NULL
PHONE_NUMBER VARCHAR2 (20) INLINE
HIRE_DATE DATE NOT NULL
JOB_ID VARCHAR2 (10) INLINE NOT NULL
SALARY NUMBER (8,2)
COMMISSION_PCT NUMBER (2,2)
MANAGER_ID NUMBER (6)
DEPARTMENT_ID NUMBER (4)
1 table found.
(primary key columns are indicated with *)
Command> SELECT * FROM employees;
< 114, Den, Raphaely, DRAPHEAL, 515.127.4561, 2002-12-07 00:00:00, PU_MAN,
11000, <NULL>, 100, 30 >
< 115, Alexander, Khoo, AKHOO, 515.127.4562, 2003-05-18 00:00:00, PU_CLERK,
3100, <NULL>, 114, 30 >
…
< 205, Shelley, Higgins, SHIGGINS, 515.123.8080, 2002-06-07 00:00:00,
AC_MGR, 12008, <NULL>, 101, 110 >
< 206, William, Gietz, WGIETZ, 515.123.8181, 2002-06-07 00:00:00,
AC_ACCOUNT, 8300, <NULL>, 205, 110 >
107 rows found.
The following example uses the createandloadfromoraquery command to create the oratt.emp table on TimesTen and populate it in parallel over four threads with data from the hr.employees table on the Oracle database, where employee_id is less than 200.
Command> createandloadfromoraquery emp 4 SELECT * FROM hr.employees
WHERE employee_id < 200;
Mapping query to this table:
CREATE TABLE "ORATT"."EMP" (
"EMPLOYEE_ID" number(6,0) NOT NULL,
"FIRST_NAME" varchar2(20 byte),
"LAST_NAME" varchar2(25 byte) NOT NULL,
"EMAIL" varchar2(25 byte) NOT NULL,
"PHONE_NUMBER" varchar2(20 byte),
"HIRE_DATE" date NOT NULL,
"JOB_ID" varchar2(10 byte) NOT NULL,
"SALARY" number(8,2),
"COMMISSION_PCT" number(2,2),
"MANAGER_ID" number(6,0),
"DEPARTMENT_ID" number(4,0)
)
Table emp created
100 rows loaded from oracle.
Then, the following createandloadfromoraquery retrieves all employees whose id is > 200 and the result set is appended to the existing table in TimesTen. A warning tells you that the table already exists and that 6 rows were added to it.
Command> createandloadfromoraquery emp 4 SELECT * FROM hr.employees WHERE employee_id > 200; Warning 2207: Table ORATT.EMP already exists 6 rows loaded from oracle.
While the createAndLoadFromOraQuery command automatically performs all of the tasks for creating the TimesTen table and loading the result set from Oracle into it, the following two built-in procedures separate the same functionality into the following two steps:
The ttTableSchemaFromOraQueryGet built-in procedure evaluates the SQL query and generates the CREATE TABLE SQL statement that you can choose to execute. In order to execute this statement, the user should have all required privileges to execute the query on Oracle. This enables you to view the table structure without execution. However, it does require you to execute the recommended CREATE TABLE statement yourself.
The ttLoadFromOracle built-in procedure executes the SQL query on the back-end Oracle database and then loads the result set into the TimesTen table. It requires the TimesTen table name where the results will be loaded, the Oracle SQL SELECT statement to obtain the required rows, and the number of parallel threads that you would like to be used in parallel when loading the table with this result set.
The call returns a single number indicating the number of rows loaded. Any subsequent calls append the retrieved rows to the table.
Note:
See "ttTableSchemaFromOraQueryGet" and "ttLoadFromOracle" in the Oracle TimesTen In-Memory Database Reference for full details on syntax, requirements, restrictions, and required privileges.The following example connects providing the DSN, user name, password for the user on TimesTen, the password for a user with the same name on the Oracle database, and the OracleNetServiceName for the Oracle database instance. Then, it executes the ttTableSchemaFromOraQueryGet built-in procedure to evaluate the SELECT statement and return a recommended CREATE TABLE statement for the employees table. Finally, the example executes the ttLoadFromOracle built-in procedure to load the employees table with the result set from Oracle. The load is performed in parallel over four threads, which is the default.
Note:
Ifautocommit is set to off, then the user must either commit or rollback manually after loading the table.$ ttisql "DSN=mydb;uid=oratt;pwd=timesten;
OraclePwd=oracle;OracleNetServiceName=inst1"
Copyright (c) 1996-2011, Oracle. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.
connect "DSN=mydb;uid=oratt;pwd=timesten;
OraclePwd=oracle;OracleNetServiceName=inst1";
Connection successful: DSN=mydb;UID=oratt;
DataStore=/timesten/install/info/DemoDataStore/mydb;
DatabaseCharacterSet=WE8DEC;ConnectionCharacterSet=US7ASCII;
DRIVER=/timesten/install/lib/libtten.so;PermSize=40;TempSize=32;
TypeMode=0;OracleNetServiceName=inst1;
(Default setting AutoCommit=1)
Command> call ttTableSchemaFromOraQueryGet('hr','employees',
'SELECT * FROM hr.employees');
< CREATE TABLE "HR"."EMPLOYEES" (
"EMPLOYEE_ID" number(6,0) NOT NULL,
"FIRST_NAME" varchar2(20 byte),
"LAST_NAME" varchar2(25 byte) NOT NULL,
"EMAIL" varchar2(25 byte) NOT NULL,
"PHONE_NUMBER" varchar2(20 byte),
"HIRE_DATE" date NOT NULL,
"JOB_ID" varchar2(10 byte) NOT NULL,
"SALARY" number(8,2),
"COMMISSION_PCT" number(2,2),
"MANAGER_ID" number(6,0),
"DEPARTMENT_ID" number(4,0)
) >
1 row found.
Command> CALL ttLoadFromOracle ('HR','EMPLOYEES','SELECT * FROM HR.EMPLOYEES');
< 107 >
1 row found.
Command> SELECT * FROM hr.employees;
< 100, Steven, King, SKING, 515.123.4567, 2003-06-17 00:00:00, AD_PRES, 24000, <NULL>, <NULL>, 90 >
< 101, Neena, Kochhar, NKOCHHAR, 515.123.4568, 2005-09-21 00:00:00, AD_VP, 17000, <NULL>, 100, 90 >
...
< 205, Shelley, Higgins, SHIGGINS, 515.123.8080, 2002-06-07 00:00:00, AC_MGR, 12008, <NULL>, 101, 110 >
< 206, William, Gietz, WGIETZ, 515.123.8181, 2002-06-07 00:00:00, AC_ACCOUNT, 8300, <NULL>, 205, 110 >
107 rows found.
The following sections describe how to view the query optimizer plans, commands in the SQL command cache, or query plans for commands in the SQL command cache:
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 ttIsql 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.
trytmphash - Enables or disables the use of temporary hash indexes.
trytmptable - Enables or disables the use of an intermediate results table.
trytmprange - Enables or disables the use of temporary range indexes.
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> INDEXED CONDITION: <NULL> NOT INDEXED: <NULL> STEP: 2 LEVEL: 3 OPERATION: TblLkSerialScan TBLNAME: T2 IXNAME: <NULL> INDEXED CONDITION: <NULL> NOT INDEXED: T1.A = T2.B AND T1.A = T2.B STEP: 3 LEVEL: 2 OPERATION: NestedLoop TBLNAME: <NULL> IXNAME: <NULL> INDEXED CONDITION: <NULL> NOT INDEXED: <NULL> STEP: 4 LEVEL: 2 OPERATION: TblLkSerialScan TBLNAME: T3 IXNAME: <NULL> INDEXED CONDITION: <NULL> NOT INDEXED: T1.A = T3.C AND T2.B = T3.C STEP: 5 LEVEL: 1 OPERATION: NestedLoop TBLNAME: <NULL> IXNAME: <NULL> INDEXED CONDITION: <NULL> NOT INDEXED: <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: TmpRangeScan TBLNAME: T1 IXNAME: <NULL> INDEXED CONDITION: <NULL> NOT INDEXED: <NULL> STEP: 2 LEVEL: 3 OPERATION: RowLkSerialScan TBLNAME: T2 IXNAME: <NULL> INDEXED CONDITION: <NULL> NOT INDEXED: T1.A = T2.B AND T1.A = T2.B STEP: 3 LEVEL: 2 OPERATION: NestedLoop TBLNAME: <NULL> IXNAME: <NULL> INDEXED CONDITION: <NULL> NOT INDEXED: <NULL> STEP: 4 LEVEL: 2 OPERATION: RowLkSerialScan TBLNAME: T3 IXNAME: <NULL> INDEXED CONDITION: <NULL> NOT INDEXED: T1.A = T3.C AND T2.B = T3.C STEP: 5 LEVEL: 1 OPERATION: NestedLoop TBLNAME: <NULL> IXNAME: <NULL> INDEXED CONDITION: <NULL> NOT INDEXED: <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 ttIsql 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, serial scans and nested loops.
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.
The following sections describe how to view commands and their explain plans:
The ttIsql cmdcache command invokes the ttSqlCmdCacheInfo built-in procedure to display the contents of the TimesTen SQL Command Cache. See "Displaying commands stored in the SQL Command Cache" for full details on this procedure.
If you execute the cmdcache command without parameters, the full SQL Command Cache contents are displayed. Identical to the ttSqlCmdCacheInfo built-in procedure, you can provide a command ID to specify a specific command to be displayed.
In addition, the ttIsql cmdcache command can filter the results so that only those commands that match a particular owner or query text are displayed.
The syntax for the cmdcache command is as follows:
cmdcache [[by {sqlcmdid | querytext | owner}] <query_substring>
If you provide the owner parameter, the results are filtered by the owner, identified by the <query_substring>, displayed within each returned command. If you provide the querytext parameter, the results are filtered so that all queries are displayed that contain the substring provided within the <query_substring>. If only the <query_substring> is provided, such as cmdcache <query_substring>, the command assumes to filter the query text by the <query_substring>.
The ttIsql explain command displays the query plan for an individual command.
If you provide a command ID from the SQL Command Cache, the explain command invokes the ttSqlCmdQueryPlan built-in procedure to display the query plan for an individual command in the TimesTen SQL Command Cache. If you want the explain plan displayed in a formatted method, execute the explain command instead of calling the ttSqlCmdQueryPlan built-in procedure. Both provide the same information, but the ttSqlCmdQueryPlan built-in procedure provides the data in a raw data format. See "Viewing query plans associated with commands stored in the SQL Command Cache" for full details on the ttSqlCmdQueryPlan built-in procedure.
If you provide a SQL statement or the history item number, the explain command executes the SQL statements necessary to display the explain plan for this particular SQL statement.
The syntax for the explain command is as follows:
explain [plan for] {[<Connid>.]<ttisqlcmdid> | sqlcmdid <sqlcmdid> | <sqlstmt>
| !<historyitem>}
Identical to the ttSqlCmdQueryPlan built-in procedure, you can provide a command ID to specify a specific command to be displayed. The command ID can be retrieved with the cmdcache command, as described in "View commands in the SQL Command Cache".
The following example provides an explain plan for command ID 38001456:
Command> EXPLAIN SQLCMDID 38001456; Query Optimizer Plan: Query Text: select * from all_objects where object_name = 'DBMS_OUTPUT' STEP: 1 LEVEL: 12 OPERATION: TblLkRangeScan TABLENAME: OBJ$ TABLEOWNERNAME: SYS INDEXNAME: USER$.I_OBJ INDEXEDPRED: NONINDEXEDPRED: (RTRIM( NAME )) = DBMS_OUTPUT;NOT( 10 = TYPE#) ; ( FLAGS ^ 128 = 0) ; STEP: 2 LEVEL: 12 OPERATION: RowLkRangeScan TABLENAME: OBJAUTH$ TABLEOWNERNAME: SYS INDEXNAME: OBJAUTH$.I_OBJAUTH1 INDEXEDPRED: ( (GRANTEE#=1 ) OR (GRANTEE#=10 ) ) AND ( (PRIVILEGE#=8 ) ) NONINDEXEDPRED: OBJ# = OBJ#; STEP: 3 LEVEL: 11 OPERATION: NestedLoop(Left OuterJoin) TABLENAME: TABLEOWNERNAME: INDEXNAME: INDEXEDPRED: NONINDEXEDPRED: ... STEP: 21 LEVEL: 1 OPERATION: Project TABLENAME: TABLEOWNERNAME: INDEXNAME: INDEXEDPRED: NONINDEXEDPRED: Command>
In addition, the ttIsql explain command can generate an explain plan for any SQL query you provide. For example, the following shows the explain plan for SQL query "SELECT * FROM DUAL;"
Command> EXPLAIN SELECT * FROM DUAL; Query Optimizer Plan: STEP: 1 LEVEL: 1 OPERATION: RowLkSerialScan TBLNAME: DUAL IXNAME: <NULL> INDEXED CONDITION: <NULL> NOT INDEXED: <NULL>
You can also retrieve explain plans based upon the command history. The following example shows how you explain a previously executed SQL statement using the history command ID:
Command> SELECT * FROM all_objects WHERE object_name = 'DBMS_OUTPUT'; < SYS, DBMS_OUTPUT, <NULL>, 241, <NULL>, PACKAGE, 2009-10-13 10:41:11, 2009-10-13 10:41:11, 2009-10-13:10:41:11, VALID, N, N, N, 1, <NULL> > < PUBLIC, DBMS_OUTPUT, <NULL>, 242, <NULL>, SYNONYM, 2009-10-13 10:41:11, 2009-10-13 10:41:11, 2009-10-13:10:41:11, INVALID, N, N, N, 1, <NULL> > < SYS, DBMS_OUTPUT, <NULL>, 243, <NULL>, PACKAGE BODY, 2009-10-13 10:41:11, 2009-10-13 10:41:11, 2009-10-13:10:41:11, VALID, N, N, N, 2, <NULL> > 3 rows found. Command> HISTORY; 1 connect "DSN=cache"; 2 help cmdcache; 3 cmdcache; 4 explain select * from dual; 5 select * from all_objects where object_name = 'DBMS_OUTPUT'; Command> EXPLAIN !5; Query Optimizer Plan: STEP: 1 LEVEL: 10 OPERATION: TblLkRangeScan TBLNAME: SYS.OBJ$ IXNAME: USER$.I_OBJ INDEXED CONDITION: <NULL> NOT INDEXED: O.FLAGS & 128 = 0 AND CAST(RTRIM (O.NAME) AS VARCHAR2(30 BYTE) INLINE) = 'DBMS_OUTPUT' AND O.TYPE# <> 10 STEP: 2 LEVEL: 10 OPERATION: RowLkRangeScan TBLNAME: SYS.OBJAUTH$ IXNAME: OBJAUTH$.I_OBJAUTH1 INDEXED CONDITION: (OA.GRANTEE# = 1 OR OA.GRANTEE# = 10) AND OA.PRIVILEGE# = 8 NOT INDEXED: OA.OBJ# = O.OBJ# STEP: 3 LEVEL: 9 OPERATION: NestedLoop(Left OuterJoin) TBLNAME: <NULL> IXNAME: <NULL> INDEXED CONDITION: <NULL> NOT INDEXED: <NULL> STEP: 4 LEVEL: 9 OPERATION: TblLkRangeScan TBLNAME: SYS.OBJAUTH$ IXNAME: OBJAUTH$.I_OBJAUTH1 INDEXED CONDITION: (OBJAUTH$.GRANTEE# = 1 OR OBJAUTH$.GRANTEE# = 10) AND (OBJAUTH$.PRIVILEGE# = 2 OR OBJAUTH$.PRIVILEGE# = 3 OR OBJAUTH$.PRIVILEGE# = 4 OR OBJAUTH$.PRIVILEGE# = 5 OR OBJAUTH$.PRIVILEGE# = 8) NOT INDEXED: O.OBJ# = OBJAUTH$.OBJ# ... STEP: 19 LEVEL: 1 OPERATION: NestedLoop(Left OuterJoin) TBLNAME: <NULL> IXNAME: <NULL> INDEXED CONDITION: <NULL> NOT INDEXED: O.OWNER# = 1 OR (O.TYPE# IN (7,8,9) AND (NOT( ISNULLROW (SYS.OBJAUTH$.ROWID)) OR NOT( ISNULLROW (SYS.SYSAUTH$.ROWID)))) OR (O.TYPE# IN (1,2,3,4,5) AND NOT( ISNULLROW (SYS.SYSAUTH$.ROWID))) OR (O.TYPE# = 6 AND NOT( ISNULLROW (SYS.SYSAUTH$.ROWID))) OR (O.TYPE# = 11 AND NOT( ISNULLROW (SYS.SYSAUTH$.ROWID))) OR (O.TYPE# NOT IN (7,8,9,11) AND NOT( ISNULLROW (SYS.OBJAUTH$.ROWID))) OR (O.TYPE# = 28 AND NOT( ISNULLROW (SYS.SYSAUTH$.ROWID))) OR (O.TYPE# = 23 AND NOT( ISNULLROW (SYS.SYSAUTH$.ROWID))) OR O.OWNER# = 10
You can perform the following on ODBC functions within ttIsql:
The ttIsql command attempts to cancel an ongoing ODBC function when the user presses Ctrl-C.
Information on the time required to execute common ODBC function calls can be displayed by using the ttIsql 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 the ttIsql 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\ TTdv1122.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>
Execute the WHENEVER SQLERROR command to prescribe what to do when a SQL error occurs. WHENEVER SQLERROR can be used to set up a recovery action for SQL statements, SQL script, or PL/SQL block.
By default, if a SQL error occurs while in ttIsql, the error information is displayed and ttIsql continues so that you can enter a new command. The default setting is WHENEVER SQLERROR CONTINUE NONE. You can also specify that ttIsql exits each time an error occurs, which may not be the best action for interactive use or when executing a SQL script or a PL/SQL block.
Note:
For syntax of theWHENEVER SQLERROR command, see the "ttIsql" section in the Oracle TimesTen In-Memory Database Reference.The following example uses EXIT to return an error code of 255 and executes a COMMIT statement to save all changes to the current connection before exiting ttIsql. The example retrieves the error code using the C shell echo $status command.
Command> WHENEVER SQLERROR EXIT 255 COMMIT; Command> SELECT emp_id FROM employee; 2206: Table PAT.EMPLOYEE not found WHENEVER SQLERROR exiting. $ echo $status 255
The following example demonstrates how the WHENEVER SQLERROR command can execute ttIsql commands or TimesTen utilities when an error occurs, even if the error is from another TimesTen utility:
Command> WHENEVER SQLERROR EXEC "DSSIZE;CALL TTSQLCMDCACHEINFOGET();"; Command> CALL TTCACHEPOLICYGET; 5010: No OracleNetServiceName specified in DSN The command failed. DSSIZE; PERM_ALLOCATED_SIZE: 32768 PERM_IN_USE_SIZE: 9204 PERM_IN_USE_HIGH_WATER: 9204 TEMP_ALLOCATED_SIZE: 40960 TEMP_IN_USE_SIZE: 7785 TEMP_IN_USE_HIGH_WATER: 7848 CALL TTSQLCMDCACHEINFOGET(); CMDCOUNT, FREEABLECOUNT, SIZE < 10, 7, 41800 > 1 row found.
The following demonstrates the SUPPRESS command option. It suppresses all error messages and continues to the next command. The example shows that the error messages can be turned back on in the existing connection with another command option, which in this case is the EXIT command.
Command> WHENEVER SQLERROR SUPPRESS;
Command> SELECT *;
Command> WHENEVER SQLERROR EXIT;
Command> SELECT *;
1001: Syntax error in SQL statement before or at: "", character position: 9
select *
^
WHENEVER SQLERROR exiting.
The following example sets a bind variable called retcode, the value of which is returned when a SQL error occurs:
Command> VARIABLE retcode NUMBER := 111;
Command> WHENEVER SQLERROR EXIT :retcode;
Command> INSERT INTO EMPLOYEES VALUES (
> 202, 'Pat', 'Fay', 'PFAY', '603.123.6666',
> TO_DATE ('17-AUG-1997', 'DD-MON-YYYY'),
> 'MK_REP', 6000, NULL, 201, 20);
907: Unique constraint (EMPLOYEES on PAT.EMPLOYEES) violated at Rowid
<BMUFVUAAACOAAAAIiB>
WHENEVER SQLERROR exiting.
$ echo $status;
111
|
 Copyright © 1996, 2012, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|