We have WLM enabled for a specific workload coming from a user ID -> This workload is enabled to collected the activity data with details [corresponding event monitors are turned on]
My requirement is to get the execution time for a specific stored procedure for the last one month. I found out the package name corresponding to this stored procedure and quried WLM_EVENT and WLM_EVENT_STMT table to find that. Here is the query that I wrote.
db2 "select a.TIME_STARTED, timestampdiff(1,timestamp(a.time_completed)-timestamp(a.time_started)) as exec_time_in_micro_sec, b.package_name, a.AGENT_ID, a.APPL_ID, a.PARENT_UOW_ID,a.PARTITION_NUMBER,b.section_numbe r,a.SESSION_AUTH_ID, a.UOW_ID from db2wlm.WLM_EVENT a, db2wlm.WLM_EVENT_STMT b, syscat.workloads c where a.appl_id=b.appl_id and a.uow_id=b.uow_id and a.workload_id=c.workloadid and b.package_name in ='XXXXXX' with ur"
But for each combination of applicationID.packagename, there are 1000's of records that were returned. How do I filter the number of rows corresponding to a specific stored procedure call from these thousands ? Only after doing so, I will be able to calculate the sum of all those execution times and can conclude that this is the execution time for each stored proc call.
Not sure how to look.. will we have to look for unique combination of applid/UOWID/pkgname/sec_number Or what ?
Any help in this question will be truly appreciated.