- Support DB2 versions 8.2 - 9.5
- 8.1 with Fixpak 17 should also work
- DB2 JDBC Drivers
- We do *not* use Health Monitor
- uses SNAPSHOT_STATEMENT
- uses SNAPSHOT_STATEMENT
- 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:
- Users must enable the following DB2 Monitor Flags to start Profiling in DB2:
- Launch DBArtisan to set Flags: DBArtisan DB2 Manager Configuration Before DB2 Monitor Flags are set to ON.
- 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
- 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
- In order to extract the explain plan we create a table on DB2 named EMBARCADERO_EXPLAIN_PLAN.
non DB Optimizer DB2 information that maybe related
- Profiler related
- LIST APPLICATIONS Command
- UOW Waiting = This Unit of Work is Waiting, i.e. it's idle.
- Connect Completed = Also idle. I don't understand the distinction.
- Compiling = The pre-cursor to executing.
- UOW Executing = It's busy.
- To see just the busy applications, is
- db2 list applications show detail | egrep -v 'UOW Waiting|Connect Completed' | cut -c1-133
- (thanks to http://rick.jasperfamily.org/aix/db2 )
- Tuner related