IBM Books

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. This tool is provided in the misc subdirectory of the instance sqllib directory.

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----+ |  +--o--options--+
   +--c--+-off-+-+  +--i--+-none-----+-+
                          +-long-----+
                          +-complete-+
 
>--+-------------+--+-------------+--+-----+-------------------><
   |     +-off-+ |  |     +-off-+ |  +--h--+
   +--v--+-on--+-+  +--s--+-on--+-+

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:

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 ]