DB2: Timing Queries with DB2BATCH


One can use the DB2 utility db2batch to collect runtime statistics on one's queries. To time commands, issue the following command:

% db2batch -d DB-Name -f SQL-file

The database DB-Name is the database where the queries / commands are to be executed. (For section A this semester, c3421a00; for section "B", COSC-3503-AK, da3503; and for section C, iq3421.) Note that the sql file SQL-file can contain multiple SQL or db2 statements. Each is timed separately. Naturally, these times may be affected by external factors such as system load, and so forth.

Try the command

% db2batch -h

and you will get a laundry list of all of db2batch's many options. For yet more, see the man page on db2batch at the IBM site.

db2batch -cli

Likewise, one can call db2batch as in

% db2batch -cli -d DB-Name -f SQL-file

The -cli flag above tells DB2 to run the db2batch command in "Call Level Interface" mode (hence, "CLI"). CLI is a library that makes it easy for one to write programs in, say, C or C++ and make database calls from within the program. This is a replacement paradigm for embedded SQL.

The DBA has granted you privileges for CLI. There is no need for you to use db2batch in CLI mode. This information at this point is here just for your interest.

CLI in itself is quite interesting. It is a generic standard that many database vendors now support. So the idea is that your CLI program which you are using here with DB2 could be used with, say, ORACLE with no changes required to your program. Let's see an embedded SQL program do that! At least that is the idea. However, because it is generic, not everything supported by DB2 is supported by CLI. CLI covers the core intersection of functionality across database systems. For example, DB2 supports the domain type real. CLI does not.

A CLI program does not need to be precompiled like an embedded SQL program does. The way CLI works, one links the appropriate CLI libraries to one's program at compile-time. This is a bit more elegant. I believe folks see CLI as a replacement for the old embedded SQL paradigm. The main drawback currently is CLI does not offer as much functionality as people often need.

Without the -cli flag, db2batch runs in dynamic SQL mode.

Other Timing Techniques

In UNIX, one could use a UNIX timing utility such as time. This can be used to time any command issued to UNIX, hence any db2 command. For example,

% time db2 -f SQL-file

The advantage is that this is simple. The disadvantage with respect to, say, db2batch is that it will not be as accurate, and it will not give you as much information. The reason it is not as accurate is that the time command will also measure certain OS overhead involved with the command and is probably more prone to "noise" from whatever else is going on in the OS at the time.

But if you are stuck and need to get your timing results out the door, time is an option.