Reads SQL statements and XQuery statements from either
a flat file or standard input, dynamically prepares and describes
the statements, and returns an answer set.
This tool can work
in both a single partition database and in a multiple partition database.
Through
the tool's optional parameters you are able to control the number
of rows to be fetched from the answer set, the number of fetched
rows to be sent to the output file or standard output, and the level
of performance information to be returned.
The output default
is to use standard output. You can name the output file for the results
summary.
Authorization
The same authority level
as that required by the SQL statements or the XQuery statements to
be read.
To
use the p option, which specifies the level of performance information,
or to use the e option, which sets the explain mode, you require SYSMON
authority.
Required connection
None. This command
establishes a database connection.
Command syntax
>>-db2batch--+-------------+--+----------------+---------------->
'- -d--dbname-' '- -f--file_name-'
>--+--------------------+--+----------------------+------------->
'- -a--userid/passwd-' '- -m--parameters_file-'
>--+-------------+--+-------------------------------------+----->
'- -t--delcol-' '- -r--result_file--+---------------+-'
'-,summary_file-'
>--+-------------------------------------+--+--------------+---->
'- -z--output_file--+---------------+-' | .-on--. |
'-,summary_file-' '- -c--+-off-+-'
>--+-------------------+--+--------------+---------------------->
| .-short----. | | .-on--. |
'- -i--+-long-----+-' '- -g--+-off-+-'
'-complete-'
>--+--------------------+--+-----------------+------------------>
| .-32768-----. | | .-on--. |
'- -w--+-col_width-+-' '- -time--+-off-+-'
>--+-----------------------+------------------------------------>
'- -cli--+------------+-'
'-cache-size-'
>--+-------------------------------+--+---------------------+--->
| .--------------------. | | .----------. |
| V .-hold-. | | | V | |
'- -msw----switches--+-on---+-+-' '- -mss----snapshot-+-'
'-off--'
>--+---------------+--+----------------+--+--------------+------>
| .-RR-. | '- -car--+-CC--+-' '- -o--options-'
'- -iso--+-RS-+-' '-WFO-'
+-CS-+
'-UR-'
>--+--------------+--+--------------+--+--------------+--------->
| .-off-. | | .-on--. | | .-off-. |
'- -v--+-on--+-' '- -s--+-off-+-' '- -q--+-on--+-'
'-del-'
>--+---------------------+--+-----+----------------------------><
'- -l--stmt_delimiter-' +- -h-+
+- -u-+
'- -?-'
Command parameters
- -d dbname
- An alias name for the database against which SQL statements and
XQuery statements are to be applied. If this option is not specified,
the value of the DB2DBDFT environment variable is
used.
- -f file_name
- Name of an input file containing SQL statements and XQuery statements.
The default is standard input.
Identify comment text
by adding two hyphens in front of the comment text, that is, --comment.
All text following the two hyphens until the end of the line is treated
as a comment. Strings delimited with single or double quotation marks
may contain two adjacent hyphens, and are treated as string constants
rather than comments. To include a comment in the output, mark it
as follows: --#COMMENT comment.
A block is a group of SQL statements and XQuery
statements that are treated as one. By default, information is collected
for all of the statements in the block at once, rather than 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.
Blocks of queries can be included in a repeating loop by specifying
a repeat count when defining the block, as follows: --#BGBLK repeat_count.
Statements in the block will be prepared only on the first iteration
of the loop.
You can use #PARAM directives
or a parameter file to specify the parameter values for a given statement
and a given iteration of a block. See the -m option
below for details.
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).
If a value of 0 is used, then no rows are fetched
and no error message is returned.
- 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 perf_detail
- Specifies the level of performance information to be returned.
Valid values are:
- 0
- Do not return any timing information or monitoring snapshots.
- 1
- Return elapsed time only.
- 2
- Return elapsed time and a snapshot for the application.
- 3
- Return elapsed time, and a snapshot for the database
manager,
the database, and the application.
- 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). The snapshot will not include hash join information.
- 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 buffer pools,
table spaces and FCM (an FCM snapshot is only available in a multi-database-partition
environment). The snapshot will not include hash join information.
The default value is 1. A value >1
is only valid on DB2® Version
2 and DB2 database servers,
and is not currently supported on host machines.
- ERROR_STOP
- Specifies whether or not db2batch should stop
running when a non-critical error occurs. Valid values are:
- no
- Continue running when a non-critical error occurs. This is the
default option.
- yes
- Stop running when a non-critical error occurs.
- 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.
- SNAPSHOT snapshot
- Specifies the monitoring snapshots to take. See the -mss option
for the snapshots that can be taken.
- TIMESTAMP
- Generates a time stamp.
- TIMING
- Print timing information. Valid values are:
- ON
- Timing information is printed. This is the default.
- OFF
- Timing information is not printed.
- -a userid/passwd
- Specifies the user ID and password used to connect to the database.
The slash (/) must be included.
- -m parameters_file
- Specifies an input file with parameter values to bind to the SQL
statement parameter markers before executing a statement. The default
is to not bind parameters.
If a parameters file is used, then
each line specifies the parameter values for a given statement and
a given iteration of a block. If instead #PARAM directives are used,
multiple values and even parameter ranges are specified in advance
for each parameter of each statement, and on each iteration of the
block a random value is chosen from the specified sets for each parameter.
#PARAM directives and a parameters file cannot be mixed.
Parameter
Value Format:
-36.6 'DB2' X'0AB2' G'...' NULL
12 'batch' x'32ef' N'...' null
+1.345E-6 'db2 batch' X'afD4' g'...' Null
Each parameter is defined like a SQL constant, and
is separated from other parameters by whitespace. Non-delimited text
represents a number, plain delimited (') text represents a single
byte character string, 'x' or 'X' prefixed text enclosed in single
quotation marks (') represents a binary string encoded as pairs of
hex digits, 'g', 'G', 'n', or 'N' prefixed text enclosed in single
quotation marks (') represents a graphic string composed of double
byte characters, and 'NULL' (case insensitive) represents a null value.
To
specify XML data, use delimited (') text, such as '<last>Brown</last>'.
Parameter Input File Format:
Line X lists the set
of parameters to supply to the Xth SQL statement that is executed
in the input file. If blocks of statements are not repeated, then
this corresponds to the Xth SQL statement that is listed in the input
file. A blank line represents no parameters for the corresponding
SQL statement. The number of parameters and their types must agree
with the number of parameters and the types expected by the SQL statement.
Parameter Directive Format:
--#PARAM [single | start:end | start:step:end] [...]
Each
parameter directive specifies a set of parameter values from which
one random value is selected for each execution of the query. Sets
are composed of both single parameter values and parameter value
ranges. Parameter value ranges are specified by placing a colon (':')
between two valid parameter values, with whitespace being an optional
separator. A third parameter value can be placed between the start
and end values to be used as a step size which overrides the default.
Each parameter range is the equivalent of specifying the single values
of 'start', 'start+step', 'start+2*step', ... 'start+n*step' where n is
chosen such that 'start+n*step' >= 'end' but 'start+(n+1)*step' >
'end'. While parameter directives can be used to specify sets of values
for any type of parameter (even NULL), ranges are only supported on
numeric parameter values (integers and decimal numbers).When
running a stored procedure, update the parameters_file with
dummy values for both IN or OUT parameters.
- -t delcol
- Specifies a single character column separator. Specify -t
TAB for a tab column delimiter or -t SPACE for
a space column delimiter. By default, a space is used when the -q
on option is set, and a comma is used when the -q
del option is set.
- -r result_file [,summary_file]
- Specifies an output file that will contain the query results.
The default is standard output. Error messages are returned in the
standard error. If the optional summary_file is
specified, it will contain the summary table.
- -z output_file [,summary_file]
- Specifies an output file that will contain the query results and
any error messages returned. The default is standard output. Error
messages are also returned in the standard error. If the optional summary_file is
specified, it will contain the summary table. This option is available
starting in Version 9.7 Fix Pack 1.
- -c
- Automatically commit changes resulting from each statement. The
default is ON.
- -i
- Specifies to measure elapsed time intervals. Valid values are:
- short
- Measure the elapsed time to run each statement. This is the default.
- long
- Measure the elapsed time to run each statement including overhead
between statements.
- complete
- Measure the elapsed time to run each statement where the prepare,
execute, and fetch times are reported separately.
- -g
- Specifies whether timing is reported by block or by statement.
Valid values are:
- on
- A snapshot is taken for the entire block and only block timing
is reported in the summary table. This is the default.
- off
- A snapshot is taken and summary table timing is reported for each
statement executed in the block.
- -w
- Specifies the maximum column width of the result set, with an
allowable range of 0 to 2 G. Data is truncated to this width when
displayed, unless the data cannot be truncated. You can increase this
setting to eliminate the warning CLI0002W and get a more accurate
fetch time. The default maximum width is 32768 columns.
- -time
- Specifies whether or not to report the timing information. Valid
values are:
- on
- Timing is reported. This is the default.
- off
- Timing is not reported.
- -cli
- Embedded dynamic SQL mode, previously the default mode for the db2batch,
command is no longer supported. This command only runs in CLI mode.
The -cli option exists for backwards compatibility.
Specifying it (including the optional cache-size argument)
will not cause errors, but will be ignored internally.
- -msw switch
- Sets the state of each specified monitor switch. You can specify
any of the following: uow, statement, table, bufferpool, lock, sort,
and timestamp. The special switch all sets
all of the above switches. For each switch that you specify you must
choose one of:
- hold
- The state of the switch is unchanged. This is the default.
- on
- The switch is turned ON.
- off
- The switch is turned OFF.
- -mss snapshot
- Specifies the monitoring snapshots that should be taken after
each statement or block is executed, depending on the -g option.
More than one snapshot can be taken at a time, with the information
from all snapshots combined into one large table before printing.
The possible snapshots are: applinfo_all, dbase_applinfo, dcs_applinfo_all, db2, dbase, dbase_all, dcs_dbase, dcs_dbase_all, dbase_remote, dbase_remote_all, agent_id, dbase_appls, appl_all, dcs_appl_all, dcs_appl_handle, dcs_dbase_appls,
dbase_appls_remote, appl_remote_all, dbase_tables, appl_locks_agent_id, dbase_locks, dbase_tablespaces, bufferpools_all, dbase_bufferpools,
and dynamic_sql.
The special snapshot all takes
all of the above snapshots. Any snapshots involving an appl ID are
not supported in favour of their agent ID (application handle) equivalents.
By default, no monitoring snapshots are taken.
- -iso
- Specifies the isolation level, which determines how data is locked
and isolated from other processes while the data is being accessed.
By default, db2batch uses the RR isolation level.
The TxnIsolation configuration keyword in the db2cli.ini file
does not affect db2batch. To run this command with
an isolation level other than RR, the -iso parameter
must be specified.
- RR
- Repeatable read (ODBC Serializable). This is the default.
- RS
- Read stability (ODBC Repeatable Read).
- CS
- Cursor stability (ODBC Read Committed).
- UR
- Uncommitted read (ODBC Read Uncommitted).
- -car
- Specifies the concurrent access resolution to use for the db2batch operation.
The -car parameter requires a properly configured
database server and the isolation level parameter -iso set
to CS.
- CC
- Specifies that the db2batch operation should
use the currently committed version of the data for applicable scans
when it is in the process of being updated or deleted. Rows in the
process of being inserted can be skipped. This option applies when
the isolation level in effect is Cursor Stability or Read Stability
(for Read Stability it skips uncommitted inserts only) and is ignored
otherwise. Applicable scans include read-only scans that can be part
of a read-only statement as well as a non read-only statement.
- WFO
- Specifies
that the db2batch operation should wait for the
outcome of an operation. For Cursor Stability and higher scans, db2batch will
wait for the commit or rollback when encountering data in the process
of being updated or deleted. Rows in the process of being inserted
are not skipped.
- -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). If a value
of 0 is used, then no rows are fetched and no error
message is returned.
- 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
- Do not return any timing information or monitoring snapshots.
- 1
- Return elapsed time only.
- 2
- Return elapsed time and a snapshot for the application.
- 3
- Return elapsed time, and a snapshot for the database
manager,
the database, and the application.
- 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 buffer pools,
table spaces and FCM (an FCM snapshot is only available in a multi-database-partition
environment).
The default value is 1. A
value >1 is only valid on DB2 Version
2 and DB2 database servers,
and is not currently supported on host machines.
- o query_optimization_class
- Sets the query optimization class. Valid values are 0, 1, 2, 3,
5, 7, or 9. The default is -1 to use the current optimization class.
- 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:
- no
- Run query only (default).
- explain
- Populate explain tables only. This option populates the explain
tables and causes explain snapshots to be taken.
- yes
- Populate explain tables and run query. This option populates the
explain tables and causes explain snapshots to be taken.
- s error_stop
- Specifies whether or not db2batch should stop
running when a non-critical error occurs. Valid values are:
- no
- Continue running when a non-critical error occurs. This is the
default option.
- yes
- Stop running when a non-critical error occurs.
- -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 with arithmetic and geometric
means, the rows fetched, and the rows output.
- -q
- Query output. Valid values are:
- off
- Output the query results and all associated information. This
is the default.
- on
- Output only query results in non-delimited format.
- del
- Output only query results in delimited format.
- -l stmt_delimiter
- Specifies the termination character (statement delimiter). The
delimiter can be 1 or 2 characters. The default is a semi-colon (';').
- -h | -u | -?
- Displays help information. When this option is specified, all
other options are ignored, and only the help information is displayed.
Examples
- The following is sample output from the command db2batch
-d crystl -f update.sql
* Timestamp: Thu Feb 02 2006 10:06:13 EST
---------------------------------------------
* SQL Statement Number 1:
create table demo (c1 bigint, c2 double, c3 varchar(8));
* Elapsed Time is: 0.101091 seconds
---------------------------------------------
* SQL Statement Number 2:
insert into demo values (-9223372036854775808, -0.000000000000005, 'demo');
* Elapsed Time is: 0.002926 seconds
---------------------------------------------
* SQL Statement Number 3:
insert into demo values (9223372036854775807, 0.000000000000005, 'demodemo');
* Elapsed Time is: 0.005676 seconds
---------------------------------------------
* SQL Statement Number 4:
select * from demo;
C1 C2 C3
-------------------- ---------------------- --------
-9223372036854775808 -5.00000000000000E-015 demo
9223372036854775807 +5.00000000000000E-015 demodemo
* 2 row(s) fetched, 2 row(s) output.
* Elapsed Time is: 0.001104 seconds
---------------------------------------------
* SQL Statement Number 5:
drop table demo;
* Elapsed Time is: 0.176135 seconds
* Summary Table:
Type Number Repetitions Total Time (s) Min Time (s) Max Time (s)
--------- ----------- ----------- -------------- -------------- --------------
Statement 1 1 0.101091 0.101091 0.101091
Statement 2 1 0.002926 0.002926 0.002926
Statement 3 1 0.005676 0.005676 0.005676
Statement 4 1 0.001104 0.001104 0.001104
Statement 5 1 0.176135 0.176135 0.176135
Arithmetic Mean Geometric Mean Row(s) Fetched Row(s) Output
--------------- -------------- -------------- -------------
0.101091 0.101091 0 0
0.002926 0.002926 0 0
0.005676 0.005676 0 0
0.001104 0.001104 2 2
0.176135 0.176135 0 0
* Total Entries: 5
* Total Time: 0.286932 seconds
* Minimum Time: 0.001104 seconds
* Maximum Time: 0.176135 seconds
* Arithmetic Mean Time: 0.057386 seconds
* Geometric Mean Time: 0.012670 seconds
---------------------------------------------
* Timestamp: Thu Feb 02 2006 10:06:13 EST
Usage notes
- All SQL statements must be terminated by a delimiter (default
';') set by the --#SET DELIMITER command. This delimiter can be 1
or 2 characters.
- SQL statement length is limited only by available memory and the
interface used. Statements can break over multiple lines, but multiple
statements are not allowed on a single line.
- Input file line length is limited only be available memory.
- c automatically issues CONNECT and CONNECT RESET statements.
- PAUSE and SLEEP are timed when long is specified
for the -i timing option.
- Explain tables must be created before explain options can be used.
- All command line options and input file statements are case insensitive
with respect to db2batch.
- db2batch supports the following data types:
INTEGER, CHAR, VARCHAR, LONG VARCHAR, FLOAT, SMALLINT, BIGINT, DECIMAL,
DATE, TIME, TIMESTAMP, CLOB, GRAPHIC, VARGRAPHIC, LONGVARGRAPHIC,
DBCLOB, BLOB, and XML.
- --#SET PERF_DETAIL perf_detail (or -o
p perf_detail) provides a quick way to
obtain monitoring output. If the performance detail level is > 1,
all monitor switches are turned on internally by db2batch.
If more precise control of monitoring output is needed, use the options -msw and -mss (or
--#SET SNAPSHOT).
- If you specify -r and -z option
together, the -r option is ignored as the -z option
includes what the -r specifies.