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.

This utility also has a parallel export function in the EEE environment. This function facilitates queries that define the data to be exported, and can create a file on individual partitions containing the portion of exported data resident on that partition.

Authorization

The same authority level as that required by the SQL statements to be read.

In parallel mode, users must have the authorization to run db2_all. For more information, see the Administration Guide.

Required Connection

None. This command establishes a database connection.

Command Syntax

>>-db2batch----d--dbname----+----------------+------------------>
                            '--f--file_name--'
 
>-----+--------------------+---+-------------+------------------>
      '--a--userid/passwd--'   '--t--delcol--'
 
>-----+-----------------------------+---+--------------+-------->
      '--r--outfile--+-----------+--'   |     .-on--.  |
                     '-,outfile2-'      '--c--+-off-+--'
 
>-----+-------------------+---+--------------+------------------>
      |     .-short----.  |   '--o--options--'
      '--i--+-long-----+--'
            '-complete-'
 
>-----+---------------+---+--------------+---+---------------+-->
      |     .-off--.  |   |     .-on--.  |   |     .-off--.  |
      '--v--+-on---+--'   '--s--+-off-+--'   '--q--+-on---+--'
                                                   '-del--'
 
>-----+--------+---+--------------------+----------------------->
      '--l--x--'   '--p--+-s---------+--'
                         +-t--table--+
                         '-d---------'
 
>-----+------------------------+---+-----+---------------------><
      '--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.

-t delcol

Specifies a single character column separator.

Note: To include a tab column delimiter use -t TAB.

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

-l x

Specifies the termination character.

-p

Parallel (EEE only). Only SELECT statements are supported in this mode. Output names must have a fully qualified path. Valid values are:

s

Single table or collocated join query. SELECT statements cannot contain only column functions. This is a requirement of the NODENUMBER function which is added to the 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.

If this option is specified and the query contains a GROUP BY clause, the columns specified in GROUP BY must be a superset of the tables partitioning key.

t table

Specifies the name of an existing table to use as the staging table to populate with the export data. If the query contains multiple tables in the FROM clause, and the tables are not collocated, the result set is inserted into the specified table and a SELECT is issued in parallel on all partitions to generate the files with the export data.

d

Creates a system table in IBMDEFAULTGROUP to be used for an INSERT INTO statement. If the query contains multiple tables in the FROM clause, and the tables are not collocated, the result set is inserted into the specified table and a SELECT is issued in parallel on all partitions to generate the files with the export data.

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.

Usage Notes

Although SQL statements can be up to 65 535 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

For a detailed discussion on the use of db2batch, see the Administration Guide.

For a detailed discussion on parallel export, see the Data Movement Utilities Guide and Reference.

db2sql92 - SQL92 Compliant SQL Statement Processor.

[ Top of Page | Previous Page | Next Page ]