Trafodion Command Interface Guide

-h or -host

-u or -user

-p or -password

SPOOL ON or LOG ON

SPOOL log-file or LOG log-file

SPOOL OFF

LOG OFF

script-file

wild-card-pattern

(section-name)

You must enter the command on one line. The command does not require an SQL terminator.

Space is disallowed between the @ sign and the first character of the script name.

For additional considerations, see the OBEY Command.

This @ command runs the script file from the local directory (the same directory where you are running TrafCI):

SQL> @ddl.sql

This @ command runs the script file in the specified directory on aWindows workstation:

 SQL> @c:\my_files\ddl.sql

This @ command runs the script file in the specified directory on a Linux or UNIX workstation:

SQL> @./my_files/ddl.sql

You must enter the command on one line.

The command does not require an SQL terminator.

value

command

SQL-terminator

You must enter the command on one line. The command does not require an SQL terminator.

The ALIAS command lasts only for the duration of the session.

An alias on an alias is not supported.

This command creates an alias named .OS to perform the LOCALHOST (LH) command:

SQL> ALIAS .OS AS LH;

This command executes the new ALIAS with the ls option:

SQL> .OS ls

trafci-perl.pl trafci-python.py trafci.cmd trafci.pl trafci.py trafci.sh

This command creates an alias named .GOTO to perform the GOTO command:

SQL> ALIAS .GOTO AS GOTO;
SQL> .GOTO mylabel

This command creates an alias named USE to perform the SET SCHEMA operation, uses the alias to set the schema to TRAFODION.USR, and checks the current schema to verify that the alias worked correctly:

SQL> ALIAS use AS "SET SCHEMA";
SQL> use TRAFODION.USR;
SQL> SHOW SCHEMA

SCHEMA USR

You must enter the command on one line.

The CLEAR command does not require an SQL terminator.

username

/password

@hostname

You must enter the command on one line. The command does not require an SQL terminator.

If TrafCI was invoked with the -noconnect launch parameter, then TrafCI prompts you for the values.

If the user name or password contains space or special characters, then you must put the name or password within double quotes.

This command creates a new connection to the Trafodion database from the current or existing TrafCI session:

SQL> CONNECT

User Name: user1
Password:

Connected to Trafodion

This command creates a new connection to the Trafodion database from the current or existing TrafCI session:

SQL> CONNECT user1/password

Connected to Trafodion

This command creates a new connection to the Trafodion database from the current or existing TrafCI session:

SQL> CONNECT user1/password@host0101

Connected to Trafodion

This command creates a new connection to the Trafodion database from the current or existing TrafCI session:

SQL> CONNECT user2

Password:

Connected to Trafodion

time

If seconds or minutes are not specified, then the default is seconds.

The maximum delay limit is 3600 seconds. You can override this value by setting trafci.maxDelayLimit in _JAVA_OPTIONS. The unit is seconds for trafci.maxDelayLimit.

This command does not require an SQL terminator.

This DELAY command puts the TrafCI session to sleep for 5 seconds before executing the next command:

SQL> DELAY 5 secs
SQL> SHOW VIEWS

This DELAY command puts TrafCI session to sleep for 5 minutes before executing the next command, which is to exit the session:

SQL> DELAY 5 mins
SQL> EXIT

status

condition

You must enter the command on one line. The command does not require an SQL terminator.

After you disconnect from the Trafodion database, you can still run these interface commands:

You must enter the command on one line. The command does not require an SQL terminator.

ENV displays these attributes:

This ENV command displays the attributes of the current session:

SQL> ENV

COLSEP           " "
HISTOPT          DEFAULT [No expansion of script files]
IDLETIMEOUT      0 min(s) [Never Expires]
LIST_COUNT       0 [All Rows]
LOG FILE         c:\session.txt
LOG OPTIONS      APPEND,CMDTEXT ON
MARKUP           RAW
PROMPT           SQL>
SCHEMA           SEABASE
SERVER           sqws135.houston.host.com:37800
SQLTERMINATOR    ;
STATISTICS       OFF
TIME             OFF
TIMING           OFF
USER             user1

This ENV command shows the effect of setting various session attributes:

4:16:43 PM > ENV

COLSEP           " "
HISTOPT          DEFAULT [No expansion of script files]
IDLETIMEOUT      30 min(s)
LIST_COUNT       0 [All Rows]
LOG              OFF
MARKUP           RAW
PROMPT           SQL>
SCHEMA           SEABASE
SERVER           sqws135.houston.host.com:37800
SQLTERMINATOR    ;
STATISTICS       OFF
TIME             OFF
TIMING           OFF
USER             user1

4:16:49 PM >

status

condition

This command disconnects from and exits TrafCI, which disappears from the screen:

SQL> EXIT

In a script file, the conditional exit command causes the script file to quit running and disconnect from and exit TrafCI when the previously run command returns error code 4082:

LOG c:\errorCode.log
SELECT * FROM employee;
EXIT IF errorcode=4082
LOG OFF

SQL> SELECT * FROM employee;

**** ERROR[4082] Table, view or stored procedure TRAFODION.USR.EMPLOYEE does not exist or is inaccessible.

SQL> EXIT IF errorcode=4082

The following two examples are equivalent:

SQL> EXIT -1 IF LASTERROR <> 0
SQL> EXIT WITH -1 IF LASTERROR != 0

This example exits TrafCI if the last error code is equal to 4082:

SQL> EXIT WITH 82 IF LASTERROR == 4082
SQL> EXIT -- default status is 0

text

number

You must enter the command on one line. The command does not require an SQL terminator.

You cannot execute this command in a script file. You can execute this command only at a command prompt.

As each line of the command is displayed, you can modify the line by entering these editing commands (in uppercase or lowercase letters) on the line below the displayed command line:

Reexecute the most recent command that begins with SH:

SQL> FC SH
SQL> SHOW SCHEMA
....

SQL> FC SH
SQL> SHOW SCHEMA
....

SCHEMA PERSNL

SQL>

Correct an SQL statement that you entered incorrectly by using the delete (D) editing command:

SQL> SELECT * FROM persnl.employee;

*** ERROR[15001] A syntax error occurred at or before:
SELECCT * FROM persnl.employee;
      ^
SQL> FC
SQL> SELECCT * FROM persnl.employee;
....     d
SQL>SELECT * FROM persnl.employee;
....

Correct an SQL statement that you entered incorrectly by using more than one editing command:

SQL> SELT * FROMM persnl.employee;

*** ERROR[15001] A syntax error occurred at or before:
SELT * FROMM persnl.employee;
   ^
SQL> FC
SQL> SELT *  FROMM persnl.employee;
....    iEX//   d
SQL> SELECT * FROM persnl.employee;
....

Modify a previously executed statement by replacing a value in the WHERE clause with another value:

SQL> SELECT first_name, last_name
+> FROM persnl.employee
+> WHERE jobcode=111;

--- 0 row(s) selected.

SQL> FC
SQL> SELECT first_name, last_name
....
SQL> FROM persnl.employee
....
SQL> WHERE jobcode=111;
                   450
....
SQL> WHERE jobcode=450;
....

Modify a previously executed statement by replacing a column name in the select list with another column name:

SQL> SELECT first_name, last_name
+> FROM persnl.employee
+> WHERE jobcode=450;

FIRST_NAME      LAST_NAME
--------------- --------------------
MANFRED         CONRAD
WALTER          LANCASTER
JOHN            JONES
KARL            HELMSTED
THOMAS          SPINNER

--- 5 row(s) selected.

SQL> FC
SQL> SELECT first_name, last_name
....        R   empnum,
SQL> SELECT     empnum, last_name
....

SQL> FROM persnl.employee
....

SQL> WHERE jobcode=450;
....

EMPNUM LAST_NAME
------ --------------------
   180 CONRAD
   215 LANCASTER
   216 JONES
   225 HELMSTED
   232 SPINNER

--- 5 row(s) selected.
SQL>

label

You must enter the command on one line.

The GOTO command cannot currently jump back in the command history; it is a forward-only command.

This HELP command lists all the interface commands that are supported:

SQL> HELP

This HELP command lists all the SET commands that are supported:

SQL> HELP SET

This HELP command lists all the SHOW commands that are supported:

SQL> HELP SHOW

This HELP command shows help text for SET IDLETIMEOUT:

SQL> HELP SET IDLETIMEOUT

number

You must enter the command on one line. The command does not require an SQL terminator.

You can use the FC command to edit and reexecute a command in the history buffer, or use the REPEAT command to reexecute a command without modifying it. See FC Command or REPEAT Command.

condition

( {variable-name | value} {operator} {variable-name | value}

variable-name

{ LASTERROR
| RECCOUNT
| ACTIVITYCOUNT
| ERRORCODE
| [%]any ENV variable | any SQL parameter
}

value

operator

== | =

<> | != | ~= | ^=

>

>=

<

<=

action

SQL Terminator

IF…THEN is itself an action. Thus, nested IF…THEN statements are allowed.

An action must end with the SQL terminator, even if the action is an interface command.

label

This command creates a label using a string of characters:

SQL> LABEL MyNewLabel

This command creates a label using a quoted string:

SQL> LABEL "Trafodion Label"

You must enter the command on one line. The command does not require an SQL terminator.

The LOCALHOST command has a limitation. When input is entered for the operating system commands (for example, date, time, and cmd), the input is not visible until you hit the enter key.

If the SET TIMING is set to ON, the elapsed time information is displayed.

If you are using a Windows system, dir lists the contents of the directory name. Similarly, if you are on a UNIX system you enter LOCALHOST LS to display the contents of the folder.

SQL> LOCALHOST dir

Volume in drive C is E-Client
Volume Serial Number is DC4F-5B3B

Directory of c:\Program Files (x86)\Apache Software Foundation\Trafodion Command

Interface\bin 05/11/2105 01:17 PM <DIR>
05/11/2105 01:17 PM <DIR>
05/16/2105 09:47 AM      1,042 trafci-perl.pl
05/16/2105 09:47 AM      1,017 trafci-python.pl
05/16/2105 09:47 AM        752 trafci.cmd
05/16/2105 09:47 AM      1,416 trafci.pl
05/16/2105 09:47 AM      2,388 trafci.py
05/16/2105 09:47 AM      3,003 trafci.sh
          6 Files(s) 19,491 bytes
          2 Dir (s) 57,686,646,784 bytes free

SQL> LH mkdir c:\trafci -- Will create a directory c:\trafci on your local machine.

This command displays the elapsed time information because the SET TIMING command is set to ON:

SQL> SET TIMING ON
SQL> LOCALHOST ls

trafci-perl.pl
trafci-python.py
trafci.cmd
trafci.pl
trafci.py
trafci.sh

Elapsed :00:00:00.078

ON

CLEAR

QUIET

CMDTEXT ON

CMDTEXT OFF

log-file

log-file CLEAR

OFF

You must enter the command on one line. The command does not require an SQL terminator.

Use a unique name for each log file to avoid writing information from different TrafCI sessions into the same log file.

This command starts the logging process and records information to the sqlspool.lst file in the bin directory:

SQL> LOG ON

This command starts the logging process and appends new information to an existing log file, persnl_updates.log, in the local directory (the same directory where you are running TrafCI):

SQL> LOG persnl_updates.log

This command starts the logging process and appends new information to a log file, sales_updates.log, in the specified directory on a Windows workstation:

SQL> LOG c:\log_files\sales_updates.log

This command starts the logging process and appends new information to a log file, sales_updates.log, in the specified directory on a Linux or UNIX workstation:

SQL> LOG ./log_files/sales_updates.log

This command starts the logging process and clears existing information from the log file before logging new information to the file:

SQL> LOG persnl_ddl.log CLEAR

This command start the logging process, clears existing information from the log file, and specifies that the command text and log header is not displayed in the log file:

SQL> LOG c:\temp\a.txt clear, CMDTEXT OFF
SQL> (SELECT * FROM trafodion.toi.job
+>;

JOBCODE JOBDESC
------- ------------------
100     MANAGER
450     PROGRAMMER 900 SECRETARY
300     SALESREP
500     ACCOUNTANT
400     SYSTEM ANALYST
250     ASSEMBLER
420     ENGINEER
600     ADMINISTRATOR
200     PRODUCTION SUPV

--- 10 row(s) selected.

SQL> log off

Output of c:\temp\a.txt

JOBCODE JOBDESC
------- ------------------
100     MANAGER
450     PROGRAMMER 900 SECRETARY
300     SALESREP
500     ACCOUNTANT
400     SYSTEM ANALYST
250     ASSEMBLER
420     ENGINEER
600     ADMINISTRATOR
200     PRODUCTION SUPV

--- 10 row(s) selected

This command start the logging process, clears existing information from the log file, specifies that no output appears on the console window, and the quiet option is enabled:

SQL> LOG c:\temp\b.txt CLEAR, CMDTEXT OFF, QUIET
SQL> SELECT
+> FROM trafodion.toi.job; +
SQL> LOG OFF

Output of c:\temp\b.txt

JOBCODE JOBDESC
------- ------------------
100     MANAGER
450     PROGRAMMER 900 SECRETARY
300     SALESREP
500     ACCOUNTANT
400     SYSTEM ANALYST
250     ASSEMBLER
420     ENGINEER
600     ADMINISTRATOR
200     PRODUCTION SUPV

--- 10 row(s) selected

SQL> LOG OFF

script-file

wild-card-pattern

(section-name)

You must enter the command on one line. The command does not require an SQL terminator.

Put a space between OBEY and the first character of the file name.

You can execute this command in a script file.

Before putting dependent SQL statements across multiple files, consider the order of the file execution. If a directory is not passed to the OBEY command, the file or wild card is assumed to be in the current working directory.

If the (*) is issued in the OBEY command, all files are executed in the current directory. Some of the files in the directory could be binary files. The OBEY command tries to read those binary files and junk or invalid characters are displayed on the console. For example, this command causes invalid characters to be displayed on the console:

SQL> OBEY C:\trafci\bin\

OBEY detects recursive obey files (for example, an SQL file that calls OBEY on itself) and prevents infinite loops using a max depth environment variable. If no variable is passed to the JVM, the default depth is set to 10. To change this depth (for example to a value of 20), pass a Java environment variable as follows:

-Dtrafci.obeydepth=20

This OBEY command runs the script file from the local directory (the same directory where you are running TrafCI):

SQL> OBEY ddl.sql

This OBEY command runs the script file in the specified directory on Windows.

SQL> OBEY c:\my_files\ddl.sql

This OBEY command runs the script file in the specified directory on a Linux or UNIX workstation:

SQL> OBEY ./my_files/ddl.sql

This sample file contains sections to be used in conjunction with the OBEY command:

?section droptable
DROP TABLE course ;

?section create
CREATE TABLE course ( cno VARCHAR(3) NOT NULL
                    , cname VARCHAR(22) NOT NULL
                    , cdescp VARCHAR(25) NOT NULL
                    , cred INT
                    , clabfee NUMERIC(5,2)
                    , cdept VARCHAR(4) NOT NULL
                    , PRIMARY KEY (cno)
                    ) ;

?section insert
INSERT INTO course VALUES ('C11', 'Intro to CS','for Rookies',3, 100, 'CIS') ;
INSERT INTO course VALUES ('C22', 'Data Structures','Very Useful',3, 50, 'CIS') ;
INSERT INTO course VALUES ('C33', 'Discrete Mathematics', 'Absolutely Necessary',3, 0,'CIS') ;

?section select
SELECT * FROM course ;

?section delete
PURGEDATA course;

SQL> OBEY C:\Command Interfaces\course.sql (create)

SQL> ?section create
SQL> CREATE TABLE course
+>(
+> cno VARCHAR(3) NOT NULL,
+> cname VARCHAR(22) NOT NULL,
+> cdescp VARCHAR(25) NOT NULL,
+> cred INT,
+> clabfee NUMERIC(5,2),
+> cdept VARCHAR(4) NOT NULL,
+> PRIMARY KEY (cno)
+>) ;

--- SQL Operation complete.

SQL> OBEY C:\Command Interfaces\course.sql (insert)

SQL> ?section insert
SQL> INSERT INTO course VALUES
+> ('C11', 'Intro to CS','For Rookies',3, 100, 'CIS');

--- 1 row(s) inserted.

SQL> INSERT INTO course VALUES
+> ('C22', 'Data Structures','Very Useful',3, 50, 'CIS');

--- 1 row(s) inserted.

SQL> INSERT INTO course VALUES
+> ('C33', 'Discrete Mathematics', 'Absolutely Necessary',3, 0, 'CIS');

--- 1 row(s) inserted.

This command executes all files with .sql extension:

SQL> OBEY c:\trafci\.sql;
SQL> OBEY c:\trafci

This command executes all files beginning with the word "script" and contains one character after the word script and ends with .sql extenstion. For example: script1.sql, script2.sql, scriptZ.sqland so on.

SQL> OBEY C:\trafci\script?.sql

This command executes all files that contain the word "test". This includes the files that do not end with .sql extension.

SQL> OBEY C:\trafci\test

This command executes all files that begin with the word "script" and contains one character after the word "script" and ends with an extenstion prefixed by a dot. For example: script1.sql, script2.bat, scriptZ.txt, and so on.

SQL> OBEY C:\trafci\script?.

This command executes all files that have .txt extension in the current directory, the directory in which the command interface was launched.

SQL> OBEY .txt;

This command prompts the user to enter the script filename or a pattern. The default value is *.sql.

SQL> OBEY;

Enter the script filename [.sql]:

-d | -defaults

{-sd | -scriptsdir} scripts-directory

{-e | -extension} file-extension

{-ld | -logsdir} log-directory

{-o | -overwrite} {y | n}

{-c | -connections} num

You must enter the command on one line. The command does not require an SQL terminator.

If you execute the PRUN command without any arguments, then TrafCI prompts you for the PRUN arguments. If you specify one or more options, then the PRUN command runs without prompting you for more input. In the non-interactive mode, if any options are not specified, PRUN uses the default values.

The -d or -defaults option cannot be specified with any other option.

The PRUN log files also contain the log end time.

PRUN does not support the SPOOL or LOG commands. Those commands are ignored in PRUN script files.

The environment values from the main session (which are available through the SET commands) are propagated to new sessions started via PRUN. However, prepared statements and parameters are bound only to the main user session.

For a summary of all errors and warnings that occurred during the PRUN operation, go to the error subdirectory in the same directory as the log files (for example, C:\log\error) and open the prun.err.log summary file.

For details about the errors that occurred during the execution of a script file, open each individual log file (script-file.sql.log).

To use PRUN, enter the PRUN command in the TrafCI session:

SQL> PRUN

Enter  as input to stop the current prun session
--------------------------------------------------
Enter the scripts directory              : c:\ddl_scripts
Enter the script file extension[sql]     :
Enter the logs directory[scripts dir]    : c:\log
Overwrite the log files (y/n)[n]?        : y
Enter the number of connections(2-248)[2]: 3

Status: In Progress.......

         __________________________________________________
                  PARALLELRUN(PRUN) SUMMARY
         __________________________________________________
         Total files present............................. 3
         Total files processed........................... 3
         Total queries processed........................ 40
         Total errors.................................... 4
         Total warnings.................................. 0
         Total successes................................ 36
         Total connections............................... 5
         Total connection failures....................... 0

         Please verify the error log file c:\log\error\prun.err.log
SQL>

This PRUN command initiates a parallel run operation with the -d option:

SQL> PRUN -d
SQL> PRUN -scriptsdir ./prun/sql -e sql -ld ./prun/logs -o y -connections 5

PRUN options are -scriptsdir    c:/_trafci/prun
                 -logsdir       c:/_trafci/prun/logs
                 -extension     sql
                 -overwrite     y
                 -connections   5
Status: Complete

         __________________________________________________
                  PARALLELRUN(PRUN) SUMMARY
         __________________________________________________
         Total files present............................ 99
         Total files processed.......................... 99
         Total queries processed....................... 198
         Total errors.................................... 0
         Total warnings.................................. 0
         Total warnings.................................. 0
         Total connections............................... 5
         Total connection failures....................... 0

===========================================================================
PRUN completed at May 20, 2105 9:33:21 AM
===========================================================================

PRUN can be started in non-interactive mode using the -q parameter of trafci.cmd or trafci.sh, thus requiring no input:

trafci.cmd -h 16.123.456.78
-u user1 -p host1
-q "PRUN -sd c:/_trafci/prun -o y -c 3"

PRUN can be started in non-interactive mode from an OBEY file:

SQL> OBEY startPrun.txt
SQL> PRUN -sd c:/_trafci/prun -ld c:/_trafci/prun/logs -e sql -o y -c 5

PRUN options are -scriptsdir    c:/_trafci/prun
                 -logsdir       c:/_trafci/prun/logs
                 -extension     sql
                 -overwrite     yes
                 -connections   5
Status: Complete

status

condition

This command disconnects from and exits TrafCI, which disappears from the screen:

SQL> QUIT

In a script file, the conditional exit command causes the script file to quit running and disconnect from and exit TrafCI when the previously run command returns error code 4082:

SQL> LOG c:\errorCode.log
SQL> SELECT * FROM employee;
SQL> QUIT IF errorcode=4082
SQL> LOG OFF

SQL> SELECT * FROM employee;

**** ERROR[4082] Table, view or stored procedure TRAFODION.USR.EMPLOYEE does not exist or is inaccessible.

SQL> QUIT IF errorcode=4082

This command creates a new connection to the Trafodion database using the login credentials of the last successful connection:

SQL> RECONNECT

Connected to Trafodion

text

number

Display the previously executed commands and reexecute the second to the last command:

SQL> HISTORY

1> SET IDLETIMEOUT 0
2> LOG ON
3> SET SCHEMA persnl;
4> SELECT * FROM employee;
5> SHOW TABLES
6> SELECT * FROM dept;
7> SHOW VIEWS
8> SELECT *  FROM emplist;

SQL>
SQL> REPEAT -2

SHOW VIEWS
VIEW NAMES
-------------------------------------------------------------
EMPLIST  MGRLIST

SQL>

Reexecute the fifth command in the history buffer:

SQL> REPEAT 5

SHOW TABLES
TABLE NAMES
-------------------------------------------------------------
DEPT     EMPLOYEE   JOB         PROJECT

SQL>

Reexecute the SHOW TABLES command:

SQL> REPEAT SHOW

SHOW TABLES
TABLE NAMES
-------------------------------------------------------------
DEPT     EMPLOYEE   JOB         PROJECT

SQL>

This command resets the last error in the current session:

SQL> SELECT * FROM emp;

**** ERROR[4082]Object TRAFODION.SCH.EMP does not exist or is inaccessible.

SQL> SHOW LASTERROR

LASTERROR 4082

SQL> RESET LASTERROR
SQL> SHOW LASTERROR

LASTERROR 0

param-name

You must enter the command on one line. The command does not require an SQL terminator.

To clear several parameter values but not all, you must use a separate RESET PARAM command for each parameter.

This command clears the setting of the ?sal (salary) parameter, and the SET PARAM command resets it to a new value:

SQL> RESET PARAM ?sal +
SQL> SET PARAM ?sal 80000.00

You must enter the command on one line.

The command does not require an SQL terminator.

This command executes the previously executed SELECT statement:

SQL> SELECT COUNT(*) FROM persnl.employee;

(EXPR)
--------------------
62

--- 1 row(s) selected.

SQL> RUN

(EXPR)
--------------------
62

--- 1 row(s) selected.

SQL>

file-name

CLEAR

You must enter the command on one line. The command does not require an SQL terminator.

If the specified file already exists, TrafCI appends newer session-history information to the file.

This command clears the contents of an existing file named history.txt in the local directory (the same directory where you are running TrafCI) and saves the session history in the file:

SQL> SAVEHIST history.txt CLEAR
SQL>

This command saves the session history in a file named hist.txt in the specified directory on a Windows workstation:

SQL> SAVEHIST c:\log_files\hist.txt
SQL>

This command saves the session history in a file named hist.txt in the specified directory on a Linux or UNIX workstation:

SQL> SAVEHIST ./log_files/hist.txt
SQL>

You must enter the command on one line.

The SET COLSEP command has no effect if the markup is set to HTML,XML, or CSV.

This command specifies the separator as a “|”(pipe):

SQL> SET COLSEP |
SQL> SHOW COLSEP
COLSEP "|"
SQL> SELECT * FROM employee;

EMPNUM|EMPNAME       |REGNUM|BRANCHNUM|JOB
------|--------------|------|---------|--------
|    1|ROGER GREEN   |    99|        1|MANAGER
|   23|JERRY HOWARD  |     2|        1|MANAGER
|   29|JACK RAYMOND  |     1|        1|MANAGER
|   32|THOMAS RUDLOFF|     5|        3|MANAGER
|   39|KLAUS SAFFERT |     5|        2|MANAGER

--- 5 row(s) selected.

value

You must enter the command on one line.

The command does not require an SQL terminator.

This command sets the fetchsize to 1:

SQL> SET FETCHSIZE 1
SQL> SHOW FETCHSIZE

FETCHSIZE 1

SQL> SELECT * FROM stream(t1);

C1      C2      C3
------- ------- -------
TEST1   TEST2   TEST3
AAA     BBB     CCC

This command shows only the obey commands added to the history buffer.

SQL> SHOW HISTOPT

HISTOPT DEFAULT [No expansion of script files]

SQL> OBEY e:\scripts\nobey\insert2.sql

SQL> ?SECTION insert

SQL> SET SCHEMA trafodion.sch;

--- SQL operation complete.

SQL>  INSERT INTO course1 VALUES
+> ('C11', 'Intro to CS','For Rookies',3, 100,'CIS');

--- 1 row(s) inserted.

SQL>  INSERT INTO course1 VALUES
+> ('C55', 'Computer Arch.','VON Neumann''S Mach.',3, 100, 'CIS');

--- 1 row(s) inserted.

This command shows all the commands added to the history buffer.

SQL> SET HISTOPT ALL
SQL> OBEY e:\scripts\nobey\insert2.sql

?SECTION insert

SQL>  set schema trafodion.sch;

--- SQL operation complete.

SQL>  INSERT INTO course1 VALUES
+> ('C11','Intro to CS','For Rookies',3, 100, 'CIS');

---1 row(s) inserted.

SQL>  INSERT INTO course1 VALUES
+> ('C55','Computer Arch.','Von Neumann''s Mach.',3,100, 'CIS');

---1 row(s) inserted.

SQL> HISTORY;

1> SHOW HISTOPT
2> OBEY e:\scripts\nobey\insert2.sql
3> HISTORY;
4> SET HISTOPT ALL
5> SET SCHEMA trafodion.sch;
6> INSERT INTO course1 VALUES
     ('C11','Intro to CS','For Rookies',3, 100, 'CIS');
7> INSERT INTO course1 VALUES
     ('C55','Computer Arch.','Von Neumann''s MACH.',3,100, 'CIS');

value

You must enter the command on one line. The command does not require an SQL terminator.

If you execute this command in a script file, it affects the session in which the script file runs. You can specify this command in PRUN script files. However, running this command from a PRUN script file does not affect the idle timeout value for the current session.

To reset the default timeout value, enter this command:

SET IDLETIMEOUT 30

This command sets the idle timeout value to four hours:

SQL> SET IDLETIMEOUT 240

This command sets the idle timeout value to an infinite amount of time so that the session never expires:

SQL> SET IDLETIMEOUT 0

To reset the idle timeout to the default, enter this command:

SQL> SET IDLETIMEOUT 30
SQL>

num-rows

You must enter the command on one line. The command does not require an SQL terminator.

To reset the number of displayed rows, enter this command:

SET LIST_COUNT 0

This command specifies that the number of rows to be displayed by SELECT statements is five:

SQL> SET LIST_count 5
SQL> SELECT empnum, first_name, last_name FROM persnl.employee ORDER BY empnum;

EMPNUM FIRST_NAME      LAST_NAME
------ --------------- --------------------
     1 ROGER           GREEN
    23 JERRY           HOWARD
    29 JANE            RAYMOND
    32 THOMAS          RUDLOFF
    39 KLAUS           SAFFERT

--- 5 row(s) selected. LIST_COUNT was reached.

SQL>

This command resets the number of displayed rows to all rows:

SQL> SET LIST_COUNT 0
SQL> SELECT empnum, first_name, last_name
+> FROM persnl.employee
+> ORDER BY empnum;

EMPNUM FIRST_NAME      LAST_NAME
------ --------------- --------------------
     1 ROGER           GREEN
    23 JERRY           HOWARD
    29 JANE            RAYMOND
    32 THOMAS          RUDLOFF
    39 KLAUS           SAFFERT
    43 PAUL            WINTER
    65 RACHEL          MCKAY
...
   995 Walt            Farley

--- 62 row(s) selected.

SQL>

You must enter the command on one line.

If the MARKUP format is CSV or COLSEP, the column header information and status messages are not displayed.

For the XML and HTML markup format, the syntax and interface errors is consistent XML and HTML markup is displayed.

For XML markup, any occurrence of ]]> that appear in the error message or invalid query are replaced with ]]>.

When error messages are output as HTML markup, both the > (greater than) and < (less than) symbols are replaced with their escaped versions: > and <, respectively. An example of the formatted error messages are show below.

This command specifies results be displayed in HTML:

SQL> SET MARKUP HTML
SQL> SELECT c.custnum, c.custnum, ordernum, order_date
+> FROM customer c, orders o where c.custnum=o.custnum;

<TABLE>
<!--SELECT c.custnum, c.custname,ordernum,order_date
FROM customer c, orders o where c.custnum=o.custnum;-->
<tr>
  <th>CUSTNUM</th>
  <th>CUSTNAME</th>
  <th>ORDERNUM</th>
  <th>ORDER_DATE</th>
</tr>
<tr>
  <td>143</td>
  <td>STEVENS SUPPLY</td>
  <td>700510</td>
  <td>2105-05-01</td>
</tr>
<tr>
  <td>3333</td>
  <td>NATIONAL UTILITIES</td>
  <td>600480</td>
  <td>2105-05-12</td>
</tr>
<tr>
  <td>7777</td>
  <td>SLEEP WELL HOTELS</td>
  <td>100250</td>
  <td>2105-01-23</td>
</tr>
<!-- --- 3 row(s) selected.-->
</TABLE>

To set the application to format output as HTML:

SQL> SET MARKUP HTML

SQL> SET MARKUP <invalid>

<?xml version="1.0"?>
<Results>
  <Query>
    <![CDATA[set markup <invalid ]]>
  </Query>
  <ErrorList>
    <Error id="1">
    <ErrorCode>NVCI001</ErrorCode>
    <ErrorMsg> <![CDATA[
ERROR: A syntax error occurred at or before:
set markup <invalid>
           ^ ]]
    </ErrorMsg>
  </ErrorList>
</Results>

This command specifies results be displayed in CSV:

SQL> SET MARKUP CSV
SQL> SELECT c.custnum, c.custnum, ordernum, order_date
+> FROM customer c,orders o where c.custnum=o.custnum;

143,STEVENS SUPPLY ,700510,2105-05-01
3333,NATIONAL UTILITIES,600480,2105-05-12
7777,SLEEPWELL HOTELS ,100250,2105-01-23
324,PREMIER INSURANCE ,500450,2105-04-20
926,METALL-AG. ,200300,2105-02-06
123,BROWN MEDICAL CO ,200490,2105-03-19
123,BROWN MEDICAL CO ,300380,2105-03-19
543,FRESNO STATE BANK ,300350,2105-03-03
5635,ROYAL CHEMICALS ,101220,2105-05-21
21,CENTRAL UNIVERSITY,200320,2105-02-17
1234,DATASPEED ,100210,2105-04-10
3210,BESTFOOD MARKETS ,800660,2105-05-09

This command specifies results be displayed in XML:

SQL> SET MARKUP XML
SQL> SELECT * FROM author

<?xml version="1.0"?>
<Results>
  <Query>
    <![CDATA[select  from author;]]>
  </Query>
  <rowid="1">
    <AUTHORID>91111</AUTHORID>
    <AUTHORNAME>Bjarne Stroustrup</AUTHORNAME>
  </row>
  <rowid="2">
    <AUTHORID>444444</AUTHORID>
    <AUTHORNAME>John Steinbeck</AUTHORNAME>
  </row>
  <rowid="3">
    <AUTHORID>2323423</AUTHORID>
    <AUTHORNAME>Irwin Shaw</AUTHORNAME>
  </row>
  <rowid="4">
    <AUTHORID>93333</AUTHORID>
    <AUTHORNAME>Martin Fowler</AUTHORNAME>
  </row>
  <rowid="5">
    <AUTHORID>92222</AUTHORID>
    <AUTHORNAME>Grady Booch</AUTHORNAME>
  </row>
  <rowid="6">
    <AUTHORID>84758345</AUTHORID>
    <AUTHORNAME>Judy Blume</AUTHORNAME>
  </row>
  <rowid="7">
    <AUTHORID>89832473</AUTHORID>
    <AUTHORNAME>Barbara Kingsolver</AUTHORNAME>
  </row>
  <Status> <![CDATA[-- 7 row(s) selected .]]></Status>
</Results>

To set the application to format output as XML:

SQL> SET MARKUP XML

SQL> SET MARKUP <]]>

<?xml version="1.0"?>
<Results>
  <Query>
    <![CDATA[set markup <]]&#62; ]]>>
  </Query>
  <ErrorList>
    <Error id="1">
    <ErrorCode>UNKNOWN ERROR CODE</ErrorCode
    <ErrorMessage> <![CDATA[
ERROR: A syntax error occurred at or before:
set markup <]]&#62;>
           ^ ]]<>
    </ErrorMessage>
  </ErrorList>
</Results>

This command displays CSV like output using the COLSEP value as a separator.

SQL> SET COLSEP |
SQL> SET MARKUP COLSEP
SQL> SELECT * FROM employee;

32|THOMAS       |RUDLOFF      |2000|100|138000.40
39|KLAUS        |SAFFERT      |3200|100|75000.00
89|PETER        |SMITH        |3300|300|37000.40
29|JANE         |RAYMOND      |3000|100|136000.00
65|RACHEL       |MCKAY        |4000|100|118000.00
75|TIM          |WALKER       |3000|300|320000.00
11|ROGER        |GREEN        |9000|100|175500.00
93|DONALD       |TAYLOR       |3100|300|33000.00

param-name

UTF8

param-value

You must enter the command on one line. The command does not require an SQL terminator.

Use separate SET PARAM commands to name and assign values to each unique parameter in a prepared SQL statement before running the EXECUTE statement.

Parameter names are case-sensitive. If you specify a parameter name in lowercase in the SET PARAM command, you must specify it in lowercase in other statements, such as DML statements or EXECUTE.

The name of a named parameter (?param-name) in a DML statement must be identical to the parameter name (param-name) that you specify in a SET PARAM command.

This command sets a value for the ?sal (salary) parameter:

SQL> SET PARAM ?sal 40000.00

This command sets a character string value, GREEN, for the ?lastname parameter:

SQL> SET PARAM ?lastname GREEN

These commands set values for named parameters in a subsequent SELECT statement:

SQL> SET PARAM ?sal 80000.00
SQL> SET PARAM ?job 100
SQL> SELECT * FROM persnl.employee WHERE salary = ?sal AND jobcode = ?job;

EMPNUM FIRST_NAME      LAST_NAME            DEPTNUM JOBCODE SALARY
------ --------------- -------------------- ------- ------- ----------
    72 GLENN           THOMAS                  3300     100   80000.00

--- 1 row(s) selected.

SQL>

This command sets a character string value, Peña, which is in UTF8 format, for the ?lastname parameter:

SQL> SET PARAM ?lastname UTF8'Peña'

This command sets a character string value, which uses the UTF8 character set and is in hexadecimal notation, for the ?lastname parameter:

SQL> SET PARAM ?lastname UTF8x'5065266e74696c64653b61'

string

%USER

%SERVER

%SCHEMA

You must enter the command on one line. The command does not require an SQL terminator.

To reset the default prompt, enter this command:

SET PROMPT

This SET PROMPT command sets the SQL prompt to ENTER>:

SQL> SET PROMPT Enter>
ENTER>

To reset the SQL prompt to the default, enter this SET PROMPT command:

ENTER> SET PROMPT +
SQL>

This command displays the session user name for the prompt:

SQL> SET PROMPT %user>
user1>

This command displays the session host name and port number for the prompt:

SQL> SET PROMPT %server>
sqws135.houston.host.com:22900>

This command displays the session schema for the prompt:

SQL> SET PROMPT "Schema %schema:"
Schema USR:

This command displays multiple session variables:

SQL> SET PROMPT %USER@%SCHEMA> user1@USR>
user1@USR>set prompt %SERVER:%USER>
sqws135.houston.host.com:22900:user1>
sqws135.houston.host.com:22900:user1> SET PROMPT "%schema CI> "
USR CI>

string

%USER

%SERVER

%SCHEMA

You must enter the command on one line. The command does not require an SQL terminator.

To reset the default SQL prompt, enter this command:

SET SQLPROMPT

This command sets the SQL prompt to ENTER>:

SQL> SET SQLPROMPT Enter>
ENTER>

To reset the SQL prompt to the default, enter this command:

ENTER> SET SQLPROMPT
SQL>

This command displays the session user name for the prompt:

SQL> SET SQLPROMPT %user>
user1>

This command displays the session host name and port number for the prompt:

SQL> SET SQLPROMPT %server>
sqws135.houston.host.com:22900>

This command displays the session schema for the prompt:

SQL> SET SQLPROMPT "Schema %schema:"
Schema USR:

This command displays multiple session variables:

SQL> SET SQLPROMPT %USER@%SCHEMA>
user1@USR>

SQL> SET SQLPROMPT %SERVER:%USER>
sqws135.houston.host.com:22900:user1>
sqws135.houston.host.com:22900:user1> SET SQLPROMPT "%schema CI> "
USR CI>

string

You must enter the command on one line. The command does not require an SQL terminator.

Do not include a reserved word as an SQL terminator.

If you execute this command in a script file, it affects not only the SQL statements in the script file but all subsequent SQL statements that are run in the current session. If you set the SQL terminator in a script file, reset the default terminator at the end of the script file.

To reset the default SQL terminator (;), enter this command:

SET SQLTERMINATOR ;

This command sets the SQL terminator to a period (.):

SQL> SET SQLTERMINATOR .

This command sets the SQL terminator to a word, go:

SQL> SET SQLTERMINATOR go

SQL> SELECT * FROM persnl.employee go

To reset the SQL terminator to the default, enter this command:

SQL> SET SQLTERMINATOR ;

This command shows the default output format as PERTABLE:

SQL> SET STATISTICS ON
SQL> SELECT * FROM job;

JOBCODE JOBDESC
------- ------------------
100     MANAGER
450     PROGRAMMER
900     SECRETARY
300     SALESREP
500     ACCOUNTANT
400     SYSTEM ANALYST
250     ASSEMBLER
420     ENGINEER
600     ADMINISTRATOR
200     PRODUCTION SUPV

--- 11 row(s) selected.

Start Time             2105/05/18 21:45:34.082329
End Time               2105/05/18 21:45:34.300265
Elapsed Time                      00:00:00.217936
Compile Time                      00:00:00.002423
Execution Time                    00:00:00.218750

Table Name   Records  Records  Disk  Message  Message  Lock  Lock  Disk Process
            Accessed     Used  I/Os    Count    Bytes  Escl  Wait     Busy Time
TRAFODION.TOI.JOB
                   2       2      0       4     15232     0     0           363


SQL>

ON

OFF

You must enter the command on one line. The command does not require an SQL terminator.

The default is a 24-hour military style display. The additional argument of 12h allows the time to be displayed in a 12-hour AM/PM style.

This command causes the local time to be displayed in the SQL prompt:

SQL> SET TIME ON
14:17:17 SQL>

This command causes the local time to be displayed in 12-hour AM/PM style in the SQL prompt:

SQL> SET TIME ON 12H
2:17:17 PM SQL>

This command turns off the local time in the SQL prompt:

2:17:17 PM SQL> SET TIME OFF
SQL>

ON

OFF

You must enter the command on one line. The command does not require an SQL terminator.

The elapsed time value includes compile and execution time plus any network I/O time and client-side processing time.

This command displays the elapsed time of SQL statements:

SQL> SET TIMING ON

This command turns off the elapsed time:

SQL> SET TIMING OFF

This command shows the record count of the previous executed SQL statement:

SQL> SHOW ACTIVITYCOUNT

ACTIVITYCOUNT 0

alias-name

wild-card-pattern

This command displays a list of the available aliases:

SQL> SHOW ALIAS

.OS AS LH
.GOTO AS GOTO
USE AS SET SCHEMA

This command displays the .GOTO alias:

SQL> SHOW ALIAS .GOTO

.GOTO AS GOTO

This command displays the .FOO alias:

SQL> SHOW ALIAS .FOO

No aliases found.

This command displays all aliases beginning with the letter S:

SQL> SHOW ALIAS S*

SEL AS SELECT
SHOWTIME AS SHOW TIME
ST AS SHOW TABLES

This command displays all the aliases in the current TrafCI session:

SQL> SHOW ALIASES

.OS AS LH
.GOTO AS GOTO
USE AS SET SCHEMA

You must enter the command on one line. The command does not require an SQL terminator.

If the SET TIMING command is set to ON, the elapsed time information is displayed.

This command shows that the current catalog of the session is TRAFODION:

SQL> SHOW CATALOG

CATALOG TRAFODION

You must enter the command on one line. The command does not require an SQL terminator.

If the SET TIMING command is set to ON, the elapsed time information is displayed.

This command displays the column separator.

SQL> SHOW COLSEP

COLSEP " "

SQL> SET COLSEP
SQL> SHOW COLSEP

COLSEP ""

This command displays the column separator.

SQL> SHOW COLSEP

COLSEP " "

SQL> SET COLSEP
SQL> SHOW COLSEP

COLSEP ""

This command displays the error of the last SQL statement that was executed:

SQL> SHOW ERRORCODE

ERRORCODE 29481

These commands display the fetch size in the current TrafCI session, set the fetch size to a new value, and then redisplay the fetch size:

SQL> SHOW FETCHSIZE

FETCHSIZE 0 [Default]

SQL> SET FETCHSIZE 1
SQL> SHOW FETCHSIZE

FETCHSIZE 1

You must enter the command on one line.

If the SET TIMING command is set to ON, the elapsed time information is displayed.

This command displays the value set for the history option:

SQL> SHOW HISTOPT

HISTOPT DEFAULT [No expansion of script files]

SQL> SET HISTOPT ALL
SQL> SHOW HISTOPT

HISTOPT ALL

You must enter the command on one line. The command does not require an SQL terminator.

If the SET TIMING command is set to ON, the elapsed time information is displayed.

This command shows that the idle timeout value of the session is 30 minutes, which is the default:

SQL> SHOW IDLETIMEOUT

IDLETIMEOUT 30 min(s)

Elapsed time:00:00:00:078

This command shows that the idle timeout value of the session is four hours:

SQL> SHOW IDLETIMEOUT

IDLETIMEOUT 240 min(s)

This command shows that the idle timeout value is an infinite amount of time, meaning that the session never expires:

SQL> SHOW IDLETIMEOUT

IDLETIMEOUT 0 min(s) [Never Expires]

This command displays the elapsed time information because SET TIMING command is enabled:

SQL> SET TIMING ON
SQL> SHOW IDLETIMEOUT

IDLETIMEOUT 0 min(s) [Never Expires]

Elapsed time:00:00:00:078

You must enter the command on one line. The command does not require an SQL terminator.

If the SET TIMING command is set to ON, the elapsed time information is displayed.

This command shows the last error in the current session:

SQL> SELECT * FROM emp;

**** ERROR[4082]Object TRAFODION.SCH.EMP does not exist or is inaccessible.

SQL> SHOW LASTERROR

LASTERROR 4082

You must enter the command on one line. The command does not require an SQL terminator.

If the SET TIMING command is set to ON, the elapsed time information is displayed.

This command shows that SELECT statements return all rows in the current session:

SQL> SHOW LIST_COUNT

LISTCOUNT 0 [All Rows]

Elapsed time:00:00:00:078

This command shows that the maximum number of rows to be displayed by SELECT statements in the session is five:

SQL> SET LIST_COUNT 5
SQL> SHOW LIST_COUNT

LIST_COUNT 5

Elapsed time:00:00:00:078

You must enter the command on one line.

If the SET TIMING command is set to ON, the elapsed time information is displayed.

This command displays the value set for the markup option:

SQL> SHOW MARKUP

MARKUP RAW

Elapsed time:00:00:00:078

You must enter the command on one line. The command does not require an SQL terminator.

If the SET TIMING command is set to ON, the elapsed time information is displayed.

This command shows that parameters that are set for the current session:

SQL> SHOW PARAM

lastname GREEN
dn 1500
sal 40000.00

This command shows that when no parameters exist, the SHOW PARAM command displays an error message:

SQL> SHOW PARAM

No parameters found.

You must enter the command on one line. The command does not require an SQL terminator.

If the SET TIMING command is set to ON, the elapsed time information is displayed.

This command shows all the prepared statements, by default:

SQL> SHOW PREPARED

S1
       SELECT *  FROM t1
S2
       SELECT * FROM student
T1
       SELECT * FROM test123

SQL> SHOW PREPARED s%

S1
       SELECT * FROM t1
S2
       SELECT * FROM student

SQL> SHOW PREPARED t%

T1
       SELECT * FROM test123

You must enter the command on one line. The command does not need an SQL terminator.

If the SET TIMING command is set to ON, the elapsed time information is displayed.

This command displays the record count of the SQL statement that was executed last:

SQL> SELECT * FROM employee;
SQL> SHOW RECCOUNT

RECCOUNT 62

You must enter the command on one line. The command does not need an SQL terminator.

The command does not need an SQL terminator.

This command displays the process name, \g4t3028.houston.host.com:0.$Z0000M2, of the DCS server that is handling the current connection:

SQL> SHOW REMOTEPROCESS

REMOTE PROCESS \g4t3028.houston.host.com:0.$Z0000M2

SQL>

You must enter the command on one line. The command does not require an SQL terminator.

If the SET TIMING command is set to ON, the elapsed time information is displayed.

This command shows that the current schema of the session is PERSNL:

SQL> SHOW SCHEMA

SCHEMA PERSNL

You must enter the command on one line. The command does not require an SQL terminator.

If the SET TIMING command is set to ON, the elapsed time information is displayed.

SHOW SESSION or SESSION displays these attributes:

This SHOW SESSION command displays the attributes of the current session:

SQL> SHOW SESSION

COLSEP             " "
HISTOPT            DEFAULT [No expansion of script files]
IDLETIMEOUT        0 min(s) [Never Expires]
LIST_COUNT         0 [All Rows]
LOG FILE           c:\session.txt
LOG OPTIONS        APPEND,CMDTEXT ON
MARKUP             RAW
PROMPT             SQL>
SCHEMA             SEABASE
SERVER             sqws135.houston.host.com:37800
SQLTERMINATOR      ;
STATISTICS         OFF
TIME               OFF
TIMING             OFF
USER               user1

This SESSION command shows the effect of setting various session attributes:

SQL> SESSION

COLSEP             " "
HISTOPT            DEFAULT [No expansion of script files]
IDLETIMEOUT        30 min(s)
LIST_COUNT         0 [All Rows]
LOG                OFF
MARKUP             RAW
PROMPT             SQL>
SCHEMA             SEABASE
SERVER             sqws135.houston.host.com:37800
SQLTERMINATOR      ;
STATISTICS         OFF
TIME               OFF
TIMING             OFF
USER               user1

SQL>

You must enter the command on one line. The command does not require an SQL terminator.

If the SET TIMING command is set to ON, the elapsed time information is displayed.

This command shows that the SQL prompt for the current session is SQL>:

SQL> SHOW SQLPROMPT
SQLPROMPT SQL>