Command Reference

db2batch - Benchmark Tool

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:

sysadm

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.

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

[ DB2 List of Books | Search the DB2 Books ]