Data Vault Administrator Guide

6.5
- 6.5.1
- 6.5 HotFix 1
- 6.4.4
- 6.4.3 HotFix 1
- 6.4.3

Back Next

ssasql SQL Mode Commands

ssasql system commands may be executed from either operating mode. Unlike modal commands, system commands must fit entirely on the current prompt line. Any command prefixed by a period that is not recognized as a system command is passed to the operating system shell for execution.

.DATA

The .DATA command switches the display of fetched data off or on. When switched off, ssasql performs query fetches but does not show the retrieved data. Only the number of rows returned and the column headers are displayed.

.EXIT

The .EXIT command returns the user to the operating system shell. Ssasql automatically disconnects all currently active sessions initiated from the client.

.EXPORT [ BULK ] { SQL-statement
} INTO { |pipe
| 'file
' }

CSV [ 'delimiter-character
'

'column-separator-character
'

'row-separator-character
'

'null-character
' ]

The .EXPORT CSV command exports the results of a query to a flat file or pipe in the comma-separated value (CSV) format. The full SELECT statement is included as a parameter for this command, enclosed by curly braces { }
. A terminating semicolon is optional for the SELECT statement. If exporting to a file, the specified file path/name must be contained in single quotation marks. If exporting to a pipe, the pipe number (from 0
to 255
) must be specified without quotes, preceded by a pipe symbol ( |
).

The optional special characters (value delimiter, column separator, row separator, and null indicator) are each represented by a single character in either ASCII or hexadecimal form, and enclosed by single quotation marks. These special characters are defined as follows:

delimiter-character: The delimiter character is used to delimit a value if it contains a double quotation mark ("
), comma (,
), or newline character (\n
). By default, this delimiter character is a double quotation mark ("
). For example, if the source value is:
oh,my
by default the value will be written to the CSV file as:
"oh,my"
that the .EXPORT CSV operation will escape a double quotation mark by preceding it with another double quotation mark, if it appears in a string value. So if the source value is this:
xyz""123
and the default delimiter ("
) is used, the following is the string that will be exported:
"xyz""""123"
column-separator-character: The column separator character is used to separate the fields in each record. By default, this character is a comma (,
).
row-separator-character: The row separator character is used to delimit rows (records). By default, this character is a newline (\n
).
null-character: The null character is used to represent a null value in the exported data. There is no default null character.
that the special characters can be omitted only if there are no null values in the exported data, as there is no default null character.
There is a "bulk fetch" option that allows data to be exported at a faster rate than with the standard .EXPORT. To enable bulk fetching, simply include the BULK keyword after the .EXPORT keyword and before the SQL clause.
Example:
.EXPORT BULK { SELECT id, ddate, price, qty, sku FROM s1.t1 WHERE ddate > '2004-01-01' } INTO 'C:\exp.dat' CSV '\x22' ',' '\x0a' '‡'
In the above .EXPORT statement, the results of the embedded SELECT statement are to be written to the specified file (exp.dat
), via bulk fetching from the server. Here, the escape character is \x22
(the hexadecimal representation of the double quotation mark character), the column separator is a comma ( ,
), the row separator is \x0a
(the hexadecimal representation of the linefeed character), and the null character is the ASCII "double dagger" symbol
( ‡
).

.HELP

The .HELP command displays a list of all system commands and their syntax.

.HEX

The .HEX command switches the hexadecimal display of binary data on or off. When turned off, unprintable characters are output as periods (‘.
’). This command is useful when querying BLOB columns.

.INDEX

The .INDEX command toggles the display of a row index. When turned on, returned rows will have an extra column called ID
, which contains the numerical order in which the rows were fetched starting from 1. By default, row indexing is turned off.

.INTERVAL [ REPEAT | NO REPEAT ] [ n
] SQL-statement
;

The .INTERVAL command executes the specified SQL statement and displays n
rows, sampled at regular intervals from the result set. If the n
argument is not supplied, the value is assumed to be 100
. When the REPEAT option is included (which is also the default), the number of rows specified is the number of rows displayed, even if it is greater than the total number of rows in the result set. If necessary, some or all of the displayed rows are duplicated. When the NO REPEAT option is specified, there is no row duplication, and the number of rows displayed can be less than n.

The SQL statement must end with a semicolon ( ;
).

.MAXLENGTH

The .MAXLENGTH command switches full display of character data on or off. By default, ssasql limits the data display to 40
characters.

.MAXROWS n

The .MAXROWS command specifies the maximum number of rows (n
) returned by a query. That is, if n
is less than the number of rows normally returned by a query, an arbitrary subset of n
rows will be returned instead. The default is 0
(unlimited).

While this system command is enabled, the true number of rows returned by a query will be obscured by the MAXROWS limitation. To disable the command, use .MAXROWS 0
.

.MEASURE

The .MEASURE command switches the timing of query fetches on or off. When switched on, query output will also display the elapsed time for command execution, each fetch stage (512 KB of data per fetch stage), and the query as a whole.

.NULLS [ string
]

The .NULLS command establishes a display string for null values returned by SQL SELECT statements. Ssasql will display the character string provided in the string argument in place of any null values returned by SQL queries. Consider this example:

SQL:1> .NULLS N/A

After this command is executed, ssasql will return the string 'N/A' in place of any nulls returned by SELECT statements. To clear the null string setting, execute the .NULLS command without an argument.

.OUTPUT

The .OUTPUT command switches session logging on or off. When session logging is on, all ssasql input and output is recorded in a file named inter.out
in the current working directory. If logging is turned off and then on again within a single ssasql session, ssasql continues to append to the output file without overwriting its contents. Turning on logging in a subsequent session, however, will overwrite any preexisting output file. A session can be started with logging turned on by including the -o
flag in the ssasql invocation.

When session logging is enabled, executing queries that produce large result sets can cause the log file to grow quite large. Eventually, disk space can be exhausted, and Data Vault Service will terminate abnormally. If possible, avoid logging large queries, or else use the .QUIET or .DATA command to hide the results of such queries.

.PROMPT

The .PROMPT command switches the SQL prompt display (that is, SQL:n
>) on and off. A session can be started with the prompt display turned off by including the -p
flag in the ssasql invocation.

.QUIET

The .QUIET command switches SELECT statement output off or on. After execution of the .QUIET command, ssasql returns only a count of the rows constituting the SELECT statement result, and not the actual data.

.RUN file

The .RUN command executes a batch of commands, either contained in a host file, or entered from the console. The file
argument must be the name of an ASCII text file containing valid commands for the current mode, or system commands.

Consider the following example:

SQL:1> .RUN sql.txt

The contents of the file sql.txt
must be valid Data Vault Service SQL statements, since ssasql is in SQL mode (unless system mode change commands are included in the file). Ssasql executes the statements in the order of their placement in the file. If a hyphen ( -
) is specified for the file argument, ssasql accepts valid commands from the console until Ctrl+D
is entered (indicating end of input).

.SESS [ command ]

The .SESS command, when executed in SQL mode, changes the current operating mode to Session mode. If the optional command
argument is included, it must be a valid Session mode command. The Session mode command specified is executed without leaving the current ssasql mode.

.SQL [ command ]

The .SQL command, when executed in Session mode, changes the current operating mode to SQL mode. If the optional command
argument is included, it must be a valid Data Vault Service SQL statement. The specified SQL command is executed without leaving the current ssasql mode.

.STARTROW n

The .STARTROW command specifies the row in the result set from which to start fetching. For example, if n
is 5, the first four rows in the result set are not displayed by ssasql. By default, all rows are displayed (n
= 1).

.STATS

The .STATS command displays the current values of ssasql system variables as well as the database connection status for the client.

ssasql STATS Display: The .STATS display has the following appearance:
SQL:3> .STATS current status NULL string: N/A OUTPUT flag: ON PROMPT flag: ON TITLES flag: ON DATA flag: ON
WARNINGS flag: OFF
QUIET flag: OFF INDEX flag: OFF MEASURE flag: OFF MAXLENGTH flag: OFF HEX flag: OFF MAXROWS: DISABLED INTERVAL REPEAT: TRUE INTERVAL rows: 100 STARTROW: 1 CONNECTIONS: * 1: CN1@db01 (Nucleus 4.1.1994.0) SERIALIZABLE 2: CN1@db01 (Nucleus 4.1.1994.0) SERIALIZABLE
NULL string. Displays the NULL string specified using the .NULLS
system command. Here, the string has been set to 'N/A.'
OUTPUT flag. Displays OUTPUT flag status as set by the -o
option flag or the .OUTPUT
system command (ON or OFF).
PROMPT flag. Displays PROMPT flag status as set by the -p
option flag or the .PROMPT
system command (ON or OFF).
TITLES flag. Displays TITLES flag status as set by the -t
option flag or the .TITLES
system command (ON or OFF).
DATA flag. Displays the DATA flag status as set by the .DATA
system command (ON or OFF).
WARNINGS flag. Displays WARNINGS flag status as set by the -w
option flag or the .WARNINGS
system command (ON or OFF).
QUIET flag. Displays QUIET flag status as set by the .QUIET
system command (ON or OFF).
INDEX flag. Displays INDEX flag status as set by the .INDEX
system command (ON or OFF).
MEASURE flag. Displays the MEASURE flag status as set by the .MEASURE
system command (ON or OFF).
MAXLENGTH flag. Displays MAXLENGTH flag status as set by the .MAXLENGTH
system command (ON or OFF).
HEX flag. Displays HEX flag status as set by the
.HEX
system command (ON or OFF).
MAXROWS. Displays the MAXROWS value specified using the .MAXROWS system command. Here, the maximum number of rows has not been set explicitly, so it defaults to unlimited.
INTERVAL REPEAT. Displays whether the .INTERVAL
option is set to REPEAT or not.
INTERVAL rows. Displays the number of rows set by the .INTERVAL
command (100 by default).
STARTROW. Displays the starting row for fetches as set by the .STARTROW
system command (1 by default).
CONNECTIONS. Displays the database sessions (that is, connections) maintained by the current client. The server and database instance name are separated by the “at” symbol ( @
), and an asterisk ( *
) indicates the current session. As well, the server software version and the transaction mode (SERIALIZABLE or EXCLUSIVE) are displayed.

.SYSTEM [ command
]

The .SYSTEM command passes control to the operating system for execution of the command specified by the command
argument. This is equivalent to typing .
command
, since all commands prefixed by a period that are unrecognized by ssasql are passed to the operating system for execution.

.TIME

The .TIME command displays the current time. If executed subsequently during the same ssasql session, it displays the time elapsed since the previous invocation of the command.

.TITLES

The .TITLES command switches the display of column names in the headings of query results on and off. A session can be started with the .TITLES display turned off by including the -t
flag in the ssasql invocation.

.WARNINGS

The .WARNINGS command switches the display of SQL warning messages on or off. A session can be started with SQL warning messages suppressed by including the -w
flag in the ssasql invocation.

Rename Saved Search

Table of Contents

Data Vault Administrator Guide

Data Vault Administrator Guide

ssasql SQL Mode Commands

ssasql SQL Mode Commands