"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

         

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

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

Explain Plan

db2exfmt - explain table format command
Optimization Profiles