DB2 LUW

General

Support DB2 versions 8.2 - 9.5

8.1 with Fixpak 17 should also work

DB2 JDBC Drivers

We do *not* use Health Monitor

Tuner

Hints

Profiler

uses SNAPSHOT_STATEMENT

SNAPSHOT_APPL_INFO

SNAP_GET_STMT

SNAP_GET_APPL_INFO

Error

By default, DB2 Monitor flags are set to OFF. As a result, when attempting to launch a Profile job on a DB2 datasource, users may receive this message:

Solution #1

Users must enable the following DB2 Monitor Flags to start Profiling in DB2:

dft_mon_uow

dft_mon_stmt

dft_mon_timestamp

dft_mon_lock

dft_mon_bufpool

dft_mon_table

Launch DBArtisan to set Flags: DBArtisan DB2 Manager Configuration Before DB2 Monitor Flags are set to ON.

NOTE:

In DBArtisan, to set DB2 Monitor Flags to ‘ON’, users must specify that the ‘New Value:’ for each variable is actually set to ‘Yes’ as shown below.

DBArtisan DB2 Manager Configuration After DB2 Monitor Flags are set to Yes/ON.

Now reboot the DB2 datasource to enable the changes, launch DBOptimizer, and start your profiling session.

Solution #2 – Command Line Option

Set flags with "Update Monitor Switches". This can be done with the command line interface: In order to set the monitor switches on:

1)This must be done from the DB2 CLP, from the DB2 server. If you attempt to set the switches to ON from a client, you will receive this error:

C:\Program Files\IBM\SQLLIB\BIN>DB2 GET MONITOR SWITCHES

SQL1096N The command is not valid for this node type.

2)So, go to the DB2 server, START/PROGRAMS/IBM DB2/COMMAND LINE TOOLS/COMMAND LINE PROCESSOR

3)turn the monitor switches on:

db2 update dbm cfg using dft_mon_lock on dft_mon_bufpool on dft_mon_sort on dft_mon_stmt on dft_mon_table on dft_mon_uow on

db2stop

db2start

4)Check that the switches are turned on:

Connect to the server: the syntax is: Db2 connect to database user username password password (apparently when executing from the server you omit the initial “DB2”)

db2 => connect to gim user db2admin

Enter current password for db2admin

db2 => get monitor switches

Monitor Recording Switches

Switch list for db partition number 0

Buffer Pool Activity Information (BUFFERPOOL) = ON 03/05/2009 19:14:06.612574

Lock Information (LOCK) = ON 03/05/2009 19:14:06.612574

Sorting Information (SORT) = ON 03/05/2009 19:14:06.612574

SQL Statement Information (STATEMENT) = ON 03/05/2009 19:14:06.612574

Table Activity Information (TABLE) = ON 03/05/2009 19:14:06.612574

Take Timestamp Information (TIMESTAMP) = ON 03/05/2009 18:50:44.000342

Unit of Work Information (UOW) = ON 03/05/2009 19:14:06.612574