DB2 LUW
Contents
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
Profiler
- uses SNAPSHOT_STATEMENT
- SNAPSHOT_APPL_INFO
- SNAP_GET_STMT
- SNAP_GET_APPL_INFO
- uses SNAPSHOT_STATEMENT
- 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
Tuner
- In order to extract the explain plan we create a table on DB2 named EMBARCADERO_EXPLAIN_PLAN.
Errata
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