SQL Language Guide : A. Terminal Monitor : Terminal Monitor Commands
 
Share this page                  
Terminal Monitor Commands
Terminal Monitor commands can manipulate the contents of the query buffer or your environment. Unlike the SQL statements that are buffered until the \go command is issued, terminal monitor commands are executed as soon as the Return key is pressed.
All Terminal Monitor commands must be preceded with a backslash (\). If a backslash is entered literally, it must be enclosed in quotes. For example, the following statement inserts a backslash into the Test table:
INSERT INTO test VALUES('\')\g
Some Terminal Monitor commands accept a file name as an argument. These commands must appear alone on a single line. The Terminal Monitor interprets all characters appearing on the line after such commands as a file name. Those Terminal Monitor commands that do not accept arguments can be stacked on a single line. For example:
\date\go\date
returns the date and time before and after execution of the SQL statements in the query buffer.
Terminal Monitor commands include:
\ansistamp
Displays timestamps in ANSI format.
\append or \a
Appends to the query buffer. Typing \append after completion of a query overrides the auto-clear feature and guarantees that the query buffer is not reset until executed again.
\[no]bell
Includes (\bell) or does not include (\nobell) a bell (that is, Ctrl+G) with \continue or \go.
Default: \nobell
\branch [label] | ?{ expression} operator value label
Permits arbitrary branching in an \include file.
If followed by label it is an unconditional branch; if followed by ?{expression} operator value label it is a conditional branch.
expression
Made up of a macro. Spaces within an expression terminates the expression.
operators
Can be one of: +, -, *, /, >, >=, <, <=, and =. The left unary operator “!” can be used to indicate logical negation.
value
Dependent on the expression.
label
A conditional branch.
\chdir or \cd dir_name
Changes the working directory of the monitor to the named directory.
\colformat column_name [column_format]
Establishes a column name/format pair to be used in result sets.
column_name is the name of a column.
column_format is a Report-Writer style format string that defines the format for column output. If omitted, any defined format string for the specified column name is removed.
Examples:
* \colformat d1 d'03/02/1901'
* \colformat f2 +f10.7
* \colformat s3 -c3
* SELECT date('now') d1, 1.5 f2, 'testing' s3\g
Executing . . .
 
┌──────────┬──────────┬───┐
│d1        │f2        │s3 │
├──────────┼──────────┼───┤
│07/09/2011│ 1.5000000│tes│
└──────────┴──────────┴───┘
\[no]continue or [no]co
Continues (\continue) or does not continue (\nocontinue) statement processing on error. The error message is displayed.
Default: \co. The default can be changed by setting II_TM_ON_ERROR.
\date
Prints the current date and time.
\editor or \edit or \ed or \e [filename]
Invokes a text editor.
Enter the text editor, designated by the startup file, of the operating system. Use the appropriate editor exit command to return to the Terminal Monitor.
If no file name is given, the current contents of the query buffer are sent to the editor, and upon return, the query buffer is replaced with the edited query.
If a file name is given, the query buffer is written to that file, and upon return, the file contains the edited query, but the query buffer remains unchanged.
\eval or \v
Puts the result back into the macro workspace.
You can use the \eval and \list commands to test a macro invocation before executing it explicitly with \g.
\go or \g
Processes the current query. The contents of the buffer are transmitted to the DBMS Server and run.
\include or \i filename
Reads the named file into the query buffer. Backslash characters in the file are processed as they are read.
\list or \l
Prints macros on the terminal. You can use the \eval and \list commands to test a macro invocation before executing it explicitly with \g.
\[no]macro
Enables (\macro) or disables (\nomacro) macro definition.
The default is \nomacro.
\mark label
Sets a label for \branch. Branching in Terminal Monitor is only permitted in a \include file.
Must be followed with a label that a \branch will go to. Can be before or after the \branch operator.
\[no]padding
Resets padding (\padding) to the default, which is that columns are space-padded to column width defined by data type; column titles may be truncated.
\nopadding trims space padding from column output; column titles are shown in full.
\print or \p
Prints the current query. The contents of the buffer are printed on the user terminal. Silent mode has no affect on this command.
\quit or \q
Exits the Terminal Monitor.
\read filename
Reads the named file into the query buffer. Backslash characters in the file are processed as they are read.
\[no]redirect or \[no]redir filename
Redirects row output to the specified file. All other output, such as errors and row counts, are sent to the terminal. \noredir disables redirected row output.
\reset or \r
Empties the workspace before you enter the next query (resets the query buffer).
\[no]runtime or \[no]rt or \rt [block|statement]
Switches into “run time” mode, which prints the time taken (to six decimal places) to execute the SQL. Elapsed time can be shown for transaction (block) or statement (the default).
* \rt
* SELECT count(*) FROM foo\g
Executing . . .
┌──────────────────────┐
│col1                  │
├──────────────────────┤
│             514198976│
└──────────────────────┘
(1 row in 0.065921 secs)
\script [filename]
Toggles between writing and not writing subsequent SQL statements and their results to the specified file.
If no file name is supplied with the \script command the output is written to a file called script.ing in the current directory. If a file name is supplied on the \script command that terminates logging, the file name is ignored.
Use this command to save result tables from SQL statements for output. The \script command does not impede the terminal output of your session.
\shell or \sh or \s [command] | [filename]
Escapes to the operating system.
On Linux, escapes to the shell (command line interpreter). Press Ctrl+D or type exit to exit the shell and return to the Terminal Monitor.
\showcf
Displays all column name/format pairs defined with \colformat.
* \showcf
d1 : d'03/02/1901'
f2 : +f10.7
s3 : -c3
Note:  If a format is specified that is invalid for the data type selected, a warning message is displayed and the results are returned as if no format was specified. This can misalign the output because the column title width will reflect the specified column format. For example:
* SELECT 100000 s3 UNION SELECT 20000\g
returns E_FM600B_FMT_DATATYPE_INVALID beside the affected rows.
\[no]silent or \nosil
Turns on (\silent) or turns off (\nosilent) silent mode.
\[no]suppress
Suppresses (\suppress) or does not suppress (\nosuppress) the printing of the resulting data that is returned from the query.
\time
Prints the current date and time.
\timestamp or \ts
Prints the current date and time to two decimal places:
24-May-2012 09:29:18.17
\[no]titles
Turns on (\titles) or turns off (\notitles) column titles.
\[no]trim
Trims (\trim) space padding from column output; column titles are shown in full.
\notrim resets padding to the default, which is that columns are space-padded to column width defined by data type; column titles may be truncated.
\vdelimiter or \vdelim char
Sets the column separator to the specified character.
\vdelimiter uses the first ten non-blank bytes and ignores any extra characters; this allows for multi-byte character sets, such as UTF8.
char can be one of the following:
SPACE
Changes the vertical separator to a space character.
TAB
Changes the vertical separator to a tab character.
NONE
Changes the vertical separator to an empty string.
\vdelim with no character specified resets the separator to the default character, which is one of the following:
Line-drawing character if output is to a terminal
| if output is to a file or other command
Space if in silent mode
Character as specified with –v flag on the command line
By default, silent mode shows column titles and separates columns with a space. If a vertical separator is specified, either with –v or \vdelimiter, the new separator is used, but in silent mode no leading and trailing separator is shown.
Note:  Both \vdelimiter and –v replace the | with the specified character. They do not create a delimited file. For example \vdelimiter SPACE or –v" " creates a file where the columns are separated by a space, but spaces will pad the column data and spaces may be in the data itself.
\[no]vertical or \[no]vert
Enables or disables vertical output, in which each column is displayed one output row after another with a “table row” heading.
* SELECT first 3 table_name, table_owner FROM iitables\g
Executing . . .
 
**** Row : 1 ****
table_name : iiprotect
table_owner : $ingres
 
**** Row : 2 ****
table_name : iiddb_nodecosts
table_owner : $ingres
 
**** Row : 3 ****
table_name : iirole
table_owner : $ingres
 
(3 rows)
\write or \w filename
Writes the contents of the query buffer to the named file.