Command Reference

db2sql92 - SQL92 Compliant SQL Statement Processor

Reads SQL statements from either a flat file or standard input, dynamically describes and prepares the statements, and returns an answer set. Supports concurrent connections to multiple databases.

Authorization

sysadm

Required Connection

None. This command establishes a database connection.

Command Syntax

>>-db2sql92----+-------------+---+----------------+------------->
               '--d--dbname--'   '--f--file_name--'
 
>-----+--------------------+---+-----------------------------+-->
      '--a--userid/passwd--'   '--r--outfile--+-----------+--'
                                              '-,outfile2-'
 
>-----+--------------+---+-------------------+------------------>
      |     .-on--.  |   |     .-short----.  |
      '--c--+-off-+--'   '--i--+-none-----+--'
                               +-long-----+
                               '-complete-'
 
>-----+--------------+---+---------------+---+---------------+-->
      '--o--options--'   |     .-off--.  |   |     .-off--.  |
                         '--v--+-on---+--'   '--s--+-on---+--'
 
>-----+-----+--------------------------------------------------><
      '--h--'

Command Parameters

-d dbname

An alias name for the database against which SQL statements are to be applied. The default is the value of the DB2DBDFT environment variable.

-f file_name

Name of an input file containing SQL statements. The default is standard input.

Identify comment text with two hyphens at the start of each line, that is, -- <comment>. If it is to be included in the output, mark the comment as follows: --#COMMENT <comment>.

A block is a number of SQL statements that are treated as one, that is, information is collected for all of those statements at once, instead of one at a time. Identify the beginning of a block of queries as follows: --#BGBLK. Identify the end of a block of queries as follows: --#EOBLK.

Specify one or more control options as follows: --#SET <control option> <value>. Valid control options are:

ROWS_FETCH: Number of rows to be fetched from the answer set. Valid values are -1 to n. The default value is -1 (all rows are to be fetched).
ROWS_OUT: Number of fetched rows to be sent to output. Valid values are -1 to n. The default value is -1 (all fetched rows are to be sent to output).
AUTOCOMMIT: Specifies autocommit on or off. Valid values are ON or OFF. The default value is ON.
PAUSE: Prompts the user to continue.
TIMESTAMP: Generates a time stamp.

-a userid/passwd

Name and password used to connect to the database.

-r outfile

An output file that will contain the query results. An optional outfile2 will contain a results summary. The default is standard output.

-c

Automatically commit changes resulting from each SQL statement.

-i

An elapsed time interval (in seconds).

none: Specifies that time information is not to be collected.
short: The run time for a query.
long: Elapsed time at the start of the next query.
complete: The time to prepare, execute, and fetch, expressed separately.

-o options

Control options. Valid options are:

f rows_fetch: Number of rows to be fetched from the answer set. Valid values are -1 to n. The default value is -1 (all rows are to be fetched).
r rows_out: Number of fetched rows to be sent to output. Valid values are -1 to n. The default value is -1 (all fetched rows are to be sent to output).

-v

Verbose. Send information to standard error during query processing. The default value is off.

-s

Summary Table. Provide a summary of elapsed times and CPU times, containing both the arithmetic and the geometric means of all collected values.

-h

Display help information. When this option is specified, all other options are ignored, and only the help information is displayed.

Usage Notes

The following can be executed from the db2sql92 command prompt:

All control options
SQL statements
CONNECT statements
commit work
help
quit

This tool supports switching between different databases during a single execution of the program. To do this, issue a CONNECT RESET and then one of the following on the db2sql92 command prompt (stdin):

   connect to database
   connect to database USER userid USING passwd

SQL statements can be up to 32 700 characters in length. Statements must be terminated by a semicolon.

SQL statements are executed with the repeatable read (RR) isolation level.

See Also

db2batch - Benchmark Tool.

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]