db2batch: sql性能基准测试工具

语法：
db2batch -d 数据库 -f demo.dml -a usr/passwd -o r 0 p 2  //r 0：不显示sql执行结果，p 2：显示sql执行消耗时间和cpu
     
demo.dml:
--#COMMENT this is a comment -begin
sql dml stmt;
如果一组sql希望一起执行：
--#BGBLK
sql dml stmt
--#EOBLK
--#COMMENT this is a comment - end


Notice:
1. db2batch执行sql的是时候，隔离级别是RR

贴几个例子

db2batch
Description: Benchmark tool. Reads SQL statements and dynamically prepares and describes the statements.

Windows/Linux/UNIX Examples
Command Location: \sqllib\misc (Windows)
Command Location: ~/sqllib/misc (all UNIX)
Authorization: same authority as required for SQL statement to be read

Assume: Input file “db2batch.sql”: SQL statement: “select lastname, firstnme, deptname, count(*) from employee”

Example 1: Determine how long SQL statement will take to run (p 1). No fetched rows will be sent to output (r 0). Output sent to stdout.
db2batch -d sample -f db2batch.sql -o p 1 r 0

Example 2: Typical db2batch requested by DB2 Service Team. Gathers all available snapshots and monitoring information (p5), optimizer level your query is running (o 5), 1 row fetched will be sent to output (r1), gathers elapsed time intervals for prepare, execute and fetch phase (-i complete). Output sent to file “output.txt”.
db2batch -d sample -f db2batch.sql -i complete -o p 5 o 5 r 1 -r output.txt

Example 3: Same output as Example 2. Set control options in db2batch.sql: --#SET PERF_DETAIL 5 ROWS_OUT 1 select lastname, firstnme, deptname, count(*)from employee;
db2batch -d sample -f db2batch.sql -i complete -o o 5 -r output.txt

Example 4: Gather a summary of monitoring information (p3), fetch all rows (f -1), output all rows (r -1), determine time to open cursor, complete the fetch, and close cursor (-i short). Output sent to file “output.txt”
db2batch -d sample -f db2batch.sql -i short -o p 3 f -1 r -1 -r output.txt

Example 5: Gathers all available snapshots and monitoring information (p 5), 5 rows fetched will be sent to output (r 5), populate the explain tables and run query (e 2). Output sent to file “output.txt”
db2batch -d sample -f db2batch.sql -o p 5 r 5 e 2 -r output.txt

Example 6: Gather statistics of SQL statement in CLI mode. Output sent to file “output.txt”
db2batch -d sample -f db2batch.sql -cli -r output.txt

Notes Statements in input file must be terminated by a delimiter (default is semicolon).

没想到抄来的东西，居然有错。
db2batch.sql改成这样好了：
select lastname, firstnme, count(*) from employee group by lastname, firstnme;

[[i] 本帖最后由 beginner-bj 于 2006-12-12 19:57 编辑 [/i]]