Support DB2 versions 8.2 - 9.5
8.1 with Fixpak 17 should also work
DB2 JDBC Drivers
We do *not* use Health MonitorTuner
Users should be a part of SYSMON
"One or more errors have occurred that prevent session profiling against this data source. Examine the details below and consult your data source administrator and /or the data source documentation to resolve the problem(s).
Error: The monitor flags need to be switchED on. (BUFFERPOOL, LOCK, SORT, STATEMENT, TABLE, UOW, TIMESTAMP)"
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
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.
reboot the DB2 datasource to enable the changes, launch DBOptimizer, and start
your profiling session.
Solution #2 – command
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
If all permissions are correct, two other options to try:
- Check your DB2 client version. If it is older (less than 9.5, based on the data source you are connecting to), I suggest updating to the latest version. If you are already up to date, or this is not something that is easily done, then the other option is to:
- Try a pure JDBC connection (that doesn’t go through your locally installed DB2 client):
- Disconnect from the data source.
- Open up the data source properties and select the radio option ‘Use a direct connection’, along with the host, port and database to connect to.
- Once you have a working connection (unless the server is configured to refuse JDBC connections, this shouldn’t be difficult to do), try re-connecting and profiling.
Host/Instance: usually the actual machine name within your network (alternatively, you can enter the machine’s IP address here).
Port: the DB2 default port is 50000, but you may have multiple instances running on the same machine, so it could vary.
Database: the database this user should connect to.
Schema ID: optional, you can leave it blank.
Function path: optional, you can leave it blank.