DB2 10.5 for Linux, UNIX, and Windows

db2batch - Benchmark tool command

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 optional parameters of the tool, 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 -o p option, which specifies the level of performance information, or to use the -o 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 following section on -m option 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 the additional processing time 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 options: uow, statement, table, bufferpool, lock, sort, and timestamp. The special switch all sets all of the switches mentioned previously. For each switch that you specify, you must choose one of the following values:

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 snapshots. Any snapshots involving an appl ID are not supported in favor 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

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 before 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 example 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.