Recent site activity

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

          Users should be a part of SYSMON
 
uses     SNAPSHOT_STATEMENT
                     SNAPSHOT_APPL_INFO
                     SNAP_GET_STMT
                     SNAP_GET_APPL_INFO       
 
         Error
     
              
 
"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:
 
       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

If all permissions are correct, two other options to try:
  1. 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:
  2. 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.


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

    DB2 Docs

    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 )
SNAPAPPL_INFO
SNAPSHOT_STATEMENT
Lock Avoidance
Long running queries and lock waits
Performance Tuning using the Configuration Advisor
Administrative views verses Table Functions

     Tuner related 
Explain Plan
db2exfmt - explain table format command
Optimization Profiles

Subpages (1): DB2 Hints
Comments