The SQLs while DB2 is up and running can be captured in different ways such as:
- Query column STMT_TEXT from table functions such as
- MON_GET_ACTIVITY or WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES, and MON_GET_ACTIVITY_DETAILS –> SQLs that have been submitted but not yet completed or currently running SQLs
- MON_GET_PKG_CACHE_STMT and MON_GET_PKG_CACHE_STMT_DETAILS –> For SQLs that have completed and are in package cache
It is necessary to understand the difference between SQLs that are currently running and those which are in package cache or have been completed. We can find out details about the SQLs that are currently running like their origin – from where they came from like identifications such as work load, client connection parameters etc. These are like rivers (currently running SQLs) ready to flow into an ocean (Package cache). The SQLs that are in package cache looses its origin like from where it came from.
Their is no guarantee that you will always find a SQL in the package cache depending upon how active the system is and how frequently SQLs are being flushed out of this cache to make room for other SQLs ready to get dumped in this cache.
The view MON_CURRENT_SQL shows similar information for the SQLs that are currently running. The view TOP_DYNAMIC_SQL shows SQLs from the package cache. These two views show subset of the information that you get from the table functions as shown above. Which one should you use – monitoring table functions as they are efficient particularly for multi-partition databases and for pureScale members.
DB2 also provides event monitor to collect SQLs including the ones that were evicted from the package cache and static as well as dynamic SQL statements. You can create an event monitor like:
create event monitor evmon_pkgcache for package cache write to table manualstart set event monitor evmon_pkgcache state 1
and run SQL SELECT * FROM PKGCACHE_EVMON_PKGCACHE
to get the SQL statement text and choose other parameters for analysis. Please notice MANUALSTART. The default is AUTOSTART in which the event monitor will start automatically on next database activation. This is a bad idea. You should control when to start and stop the event monitors as they are expensive.
But, this is a sledge hammer approach and I normally do not use this. This is a too much drag on the system.
I like to use a approach that has the least impact and only concerns with a particular type of the work load that I am interested in. The idea is to minimize the impact on the monitored database.
This approach requires that you use a proper method of identifying the workload. If you run command db2 list applications
and you see the name as db2jcc_appl
, you are not doing your job right. It means that you have failed to ask the application developers to properly identify the application names. This can be done by variety of methods such as by setting the proper JCC driver properties etc. It requires a separate write-up as this is a long topic. It is easy to do from any application server and requires some work for Java and non-Java applications. Assuming that you have a proper way of defining your workload, follow this least impact method to capture the SQL.
We like to call it SQL fishing.
Use these SQLs and customize them as per your needs.
Step-1 create workload wl_dsm applname('DSMRtMonBg','DSMRtMonFg', 'DSMRtMonPram') disable collect aggregate unit of work data; Step-2 create work class set user_wcs (work class expensive_dml work type dml for timeroncost from 1000 to unbounded); Step-3 create work action set user_was for workload wl_dsm using work class set user_wcs ( work action expensive_action on work class expensive_dml collect activity data with details ) disable; Step-4 create threshold th_expsql for workload wl_dsm activities enforcement database disable when estimatedsqlcost > 1000 collect activity data on all members with details continue; Step-5 create event monitor db2thresviolations for threshold violations write to table manualstart; Step-6 create event monitor db2activities for activities write to table manualstart; set event monitor db2thresviolations state 1; set event monitor db2activities state 1; alter workload wl_dsm enable; alter work action set user_was enable; alter threshold th_expsql enable; set workload to automatic;
I wrote above to find out the expensive SQL statements thrown from the application Data Server Manager to see the SQLs that have costs more than 1000 and just capture those.
First I define the WORKLOAD and this is the statement that you have to define it as per your needs and look DB2 Knowledge Center for the syntax to see what other features that you could use. This step should be done carefully to just focus only one application at a time and to bundle all database connections in single workload.
The second and third statements are optional and I just created to create a turnstile counter to report on the SQLs that were expensive compared to the total SQLs so that I have something to compare how improvements are occurring. Since my work action set name was USER_WAS, I could then just look SELECT * FROM table (MON_GET_WORK_ACTION_SET_STATS('USER_WAS',-1)) as t
for total activity under expensive SQL.
The 4th step is the most important as I am creating a threshold for the workload (only selected thing) for SQL cost greater than 1000. This is called SQL fishing as I am throwing a net in the ocean and I am not catching all fish but the one I need more than a particular size. If you understand how THRESHOLDS can be used, you can do wonderful things in DB2. Now, this threshold domain is only for the workload but the enforcement is at the database level.
The 5th and 6th steps are for creating the event monitors for the threshold violations and capturing the SQLs. The rests are enable statements and starting the event monitors.
Start your application and let it run for the designated time and then run the following SQL to capture the count of threshold violations and the SQLs that were expensive.
select count(*) from THRESHOLDVIOLATIONS_DB2THRESVIOLATIONS; SELECT TIME_OF_VIOLATION, STMT_TEXT STMT_TEXT FROM THRESHOLDVIOLATIONS_DB2THRESVIOLATIONS TV, ACTIVITYSTMT_DB2ACTIVITIES A WHERE TV.APPL_ID = A.APPL_ID AND TV.UOW_ID = A.UOW_ID AND TV.ACTIVITY_ID = A.ACTIVITY_ID;
Once you have captured the SQL texts, do not forget to turn-off the event monitors.
This is the light approach and should be used to filter the SQLs that you do not need by using the threshold intelligently.
I am giving you the template for SQL fishing and control and I helped one of the largest bank to control the execution in such a fashion to monitor and control selectively.
SET WORKLOAD TO SYSDEFAULTADMWORKLOAD; CREATE SERVICE CLASS USERS_SC DISABLE; CREATE WORKLOAD USER_WL SESSION_USER ROLE ('BATCHROLE') DISABLE; ALTER WORKLOAD USER_WL SERVICE CLASS USERS_SC; GRANT USAGE ON WORKLOAD USER_WL TO ROLE BATCHROLE; CREATE WORK CLASS SET USER_WL_WORK_CLASS_SET ( WORK CLASS COSTLY_DML WORK TYPE DML FOR TIMERONCOST FROM 500000 TO 6000000, WORK CLASS EXPENSIVE_DML WORK TYPE DML FOR TIMERONCOST FROM 6000001 TO UNBOUNDED ); CREATE WORK ACTION SET USER_WL_WORK_ACTION_SET FOR WORKLOAD USER_WL USING WORK CLASS SET USER_WL_WORK_CLASS_SET ( WORK ACTION COSTLY_ACTION_ALLOW ON WORK CLASS COSTLY_DML WHEN CONCURRENTDBCOORDACTIVITIES > 5 AND QUEUEDACTIVITIES UNBOUNDED COLLECT ACTIVITY DATA WITH DETAILS CONTINUE, WORK ACTION EXPENSIVE_ACTION_PREVENT ON WORK CLASS EXPENSIVE_DML PREVENT EXECUTION ) DISABLE; ALTER SERVICE CLASS USERS_SC ENABLE; ALTER WORKLOAD USER_WL ENABLE; ALTER WORK ACTION SET USER_WL_WORK_ACTION_SET ENABLE; SET WORKLOAD TO AUTOMATIC;
Now the above template is simple. I just defined a service class without any attributes. You can use resource control as you like. To be very frank, I am not a fan of resource control in the first place. But, that is my personal preference as I sincerely believe that the true WLM is achieved through concurrency control and through the proper usage of the thresholds.
Then I create a workload. This is the most important step as how you create it to club activities that you see as an application.
Then I create a work class set to define costly and expensive SQLs. Please don’t go over the difference between costly and expensive. You can call them peter and pan.
Then, I define a work action set that has concurrency control and a threshold to prevent SQLs that are expensive. The number that you use for concurrency control should be 2 or 4 less than the number of cores/threads you have in your system. This will not overwhelm the system for large number of queries to run concurrently. If there are no cores available then the query will wait which is far better than large number of queries than the available cores you have available.
Now optionally you can create an ACTIVITY event monitor as shown previously and capture the SQLs that are costly. The expensive ones are zapped automatically. Please notice that we did not create a threshold in this case and we used the WORK ACTION SET capability to do two things in one step for two defined WORK CLASS SETS.
The WLM is one of the best feature in DB2 but least understood and least implemented. What you learned above will work provided you define the workload appropriately.