Command Reference
Reads SQL statements from either a flat file or standard input, dynamically
prepares and describes the statements, and returns an answer set.
Authorization
One of the following:
Required Connection
None. This command establishes a database connection.
Command Syntax
>>-db2batch----d--dbname----+----------------+------------------>
'--f--file_name--'
>-----+--------------------+---+-----------------------------+-->
'--a--userid/passwd--' '--r--outfile--+-----------+--'
'-,outfile2-'
>-----+--------------+---+-------------------+------------------>
| .-on--. | | .-short----. |
'--c--+-off-+--' '--i--+-long-----+--'
'-complete-'
>-----+--------------+---+---------------+---+--------------+--->
'--o--options--' | .-off--. | | .-on--. |
'--v--+-on---+--' '--s--+-off-+--'
>-----+---------------+---+---------------------+--------------->
| .-off--. | '--p--+--s---------+--'
'--q--+-on---+--' '--t--table--'
>-----+------------------------+---+-----+---------------------><
'--cli--+-------------+--' '--h--'
'-cache-size--'
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).
- PERF_DETAIL
- Specifies the level of performance information to be returned.
Valid values are:
- 0
- No timing is to be done.
- 1
- Return elapsed time only.
- 2
- Return elapsed time and CPU time.
- 3
- Return a summary of monitoring information.
- 4
- Return a snapshot for the database manager, the database, the application,
and the statement (the latter is returned only if autocommit is off, and
single statements, not blocks of statements, are being processed).
- 5
- Return a snapshot for the database manager, the database, the application,
and the statement (the latter is returned only if autocommit is off, and
single statements, not blocks of statements, are being processed). Also
return a snapshot for the bufferpools, table spaces and FCM (an FCM snapshot
is only available in a multi-node environment).
The default value is 1. A value >1 is only valid on
DB2 Version 2 servers.
- DELIMITER
- A one- or two-character end-of-statement delimiter. The default
value is a semicolon (;).
- SLEEP
- Number of seconds to sleep. Valid values are 1 to
n.
- PAUSE
- Prompts the user to continue.
- TIMESTAMP
- Generates a time stamp.
- -a userid/passwd
- Name and password used to connect to the database. The slash
(/) must be included.
- -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).
- short
- The time taken to open the cursor, complete the fetch, and close the
cursor.
- long
- The elapsed time from the start of one query to the start of the next
query, including pause and sleep times, and command overhead.
- 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).
- p perf_detail
- Specifies the level of performance information to be returned.
Valid values are:
- 0
- No timing is to be done.
- 1
- Return elapsed time only.
- 2
- Return elapsed time and CPU time.
- 3
- Return a summary of monitoring information.
- 4
- Return a snapshot for the database manager, the database, the application,
and the statement (the latter is returned only if autocommit is off, and
single statements, not blocks of statements, are being processed).
- 5
- Return a snapshot for the database manager, the database, the application,
and the statement (the latter is returned only if autocommit is off, and
single statements, not blocks of statements, are being processed). Also
return a snapshot for the bufferpools, table spaces and FCM (an FCM snapshot
is only available in a multi-node environment).
- o query_optimization_class
- Sets the query optimization class. For a description of valid
values, see the Administration Guide.
- e explain_mode
- Sets the explain mode under which db2batch runs. The
explain tables must be created prior to using this option. Valid values
are:
- 0
- Run query only (default).
- 1
- Populate explain tables only. This option populates the explain
tables and causes explain snapshots to be taken.
- 2
- Populate explain tables and run query. This option populates the
explain tables and causes explain snapshots to be taken.
- -v
- Verbose. Send information to standard error during query
processing. The default value is off.
- -s
- Summary Table. Provide a summary table for each query or block of
queries, containing elapsed time (if selected), CPU times (if selected), the
rows fetched, and the rows printed. The arithmetic and geometric means
for elapsed time and CPU times are provided if they were collected.
- -q
- Query output. Valid values are:
- on
- Print only the non-delimited output of the query.
- off
- Print the output of the query and all associated information. This
is the default.
- del
- Print only the delimited output of the query.
- -p
- Parallel (MPP only). Valid values are:
- -s
- Single table or collocated join query. If this option is specified,
the NODENUMBER function will be added to the WHERE clause of the query, and a
temporary table will not be created. This option is valid only if the
query contains a single table in the FROM clause, or if the tables contained
in the FROM clause are collocated.
- -t table
- Specifies the table to use for an INSERT INTO statement. If the
query contains multiple tables in the FROM clause, and the tables are not
collocated, the result set must first be inserted into a temporary table, and
then a SELECT from this temporary table must be performed on all nodes.
If neither the -s nor the -t option is specified, the tool creates a
temporary table by default.
If a local output file is specified (using the -r
option), the output from each node will go into a separate file with the same
name on each node). If a file that is on an NFS-mounted file system is
specified, all of the output will go into this file.
- -cli
- Run db2batch in CLI mode. The default is to use embedded
dynamic SQL. The statement memory can be set manually, using the
cache-size parameter.
- cache-size
- Size of the statement memory, expressed as number of statements.
The default value is 25. If the utility encounters an SQL
statement that has already been prepared, it will reuse the old plans.
This parameter can only be set when db2batch is run in CLI
mode.
- -h
- Display help information. When this option is specified, all other
options are ignored, and only the help information is displayed.
Examples
For a detailed discussion on the use of db2batch, see the Administration Guide.
Usage Notes
Although SQL statements can be up to 32 698 characters in length,
no text line in the input file can exceed 3 898 characters, and long
statements must be divided among several lines. Statements must be
terminated by a delimiter (the default is a semicolon).
SQL statements are executed with the repeatable read (RR) isolation
level.
See Also
db2sql92 - SQL92 Compliant SQL Statement Processor.
[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]
[ DB2 List of Books |
Search the DB2 Books ]