DB2 Version 9.7 for Linux, UNIX, and Windows

PKGCACHE procedure - generate a summary report of package cache metrics

The Package Cache Summary report lists the top statements accumulated in the package cache as measured by various metrics.

The PKGCACHE procedure is available starting with DB2® Version 9.7 Fix Pack 1.

Syntax

Read syntax diagramSkip visual syntax diagram
>>-MONREPORT.PKGCACHE--(--cache_interval--,--section_type--,--member--)-><

Parameters

cache_interval
An optional input argument of type INTEGER that specifies the report should only include data for package cache entries that have been updated in the past number of minutes specified by the cache_interval value. For example a cache_interval value of 60 produces a report based on package cache entries that have been updated in the past 60 minutes. Valid values are integers between 0 and 10080, which supports an interval of up to 7 days. If the argument is not specified (or if null is specified), the report includes data for package cache entries regardless of when they were added or updated.
section_type
An optional input argument of type CHAR(1) that specifies whether the report should include data for static SQL, dynamic SQL, or both. If the argument is not specified (or if null is specified), the report includes data for both types of SQL. Valid values are: d or D (for dynamic) and s or S (for static).
member
An optional input argument of type SMALLINT that determines whether to show data for a particular member or partition, or to show data summed across all members. If this argument is not specified (or if null is specified), the report shows values summed across all members. If a valid member number is specified, the report shows values for that member.

Authorization

The following privilege is required:
  • EXECUTE privilege on the MONREPORT module
The following examples demonstrate various ways to call the PKGCACHE procedure. The first example produces a report based on all statements in the package cache, with data aggregated across all members:
call monreport.pkgcache;
The next example produces a report based on both dynamic and static statements in the package cache for which metrics have been updated within the last 30 minutes, with data aggregated across all members:
call monreport.pkgcache(30);
The next example produces a report based on all dynamic statements in the package cache, with data aggregated across all members:
call monreport.pkgcache(DEFAULT, 'd');
The next example produces a report based on both dynamic and static statements in the package cache for which metrics have been updated within the last 30 minutes, with data specific to a member number 4:
call db2monreport.pkgcache(30, DEFAULT, 4);

Sample MONREPORT.PKGCACHE output - summary section

  -------------------------------------------------------------------------------- 
  Monitoring report - package cache                                                
  -------------------------------------------------------------------------------- 
  Database:                          WLMDISP1                                      
  Generated:                         07/09/2010 12:54:59                           
  -- Command options --                                                            
  CACHE_INTERVAL:                    All statements                                
  SECTION_TYPE:                      Dynamic and static SQL                        
  MEMBER:                            All                                           
                                                                                   
  ================================================================================ 
  Part 1 - Summaries by 'top' metrics                                              
                                                                                   
  Top 10 statements by TOTAL_CPU_TIME                                              
  -------------------------------------------------------------------------------- 
  #   TOTAL_       STMT_TEXT                                                       
      CPU_TIME                                                                     
  --  -----------  --------------------------------------------------------------- 
  1   1359020292   REFRESH TABLE BEVERAGES.STABLE270                                
  2   139839184    MERGE INTO BEVERAGES.ATABLE4 USING ( select ROW_NUMBER() OVER()  
  3   85401640     SELECT ALL  "Corr.2".R17 AS R28,"Corr.2"."Char 13" AS C218,SLEE 
  4   81057664     MERGE INTO BEVERAGES.ATABLE77 USING ( select ROW_NUMBER() OVER() 
  5   70487710     (SELECT DISTINCT  (CORR_1.I38) AS I94,CORR_1.BIG7 BIG168,SLEEMA 
  6   60477984     SELECT ALL  CORR_1.FLOAT8 F58,CORR_1.BIGINT1 AS "Bigint 66",COR 
  7   60336985     SELECT CORR_2.R237 AS R93,BEVERAGES.ALIAS166."TimeStamp 107" TS2 
  8   56160692     MERGE INTO BEVERAGES.ATABLE221 USING ( select ROW_NUMBER() OVER( 
  9   54206591     MERGE INTO BEVERAGES."Table 262" USING ( select ROW_NUMBER() OVE 
  10  47116331     MERGE INTO BEVERAGES."Alias 195" USING ( select ROW_NUMBER() OVE 
                                                                                   
  Top 10 statements by TOTAL_CPU TIME per exec                                     
  -------------------------------------------------------------------------------- 
  #   TOTAL_       STMT_TEXT                                                       
      CPU_TIME                                                                     
  --  -----------  --------------------------------------------------------------- 
  3   85401640     SELECT ALL  "Corr.2".R17 AS R28,"Corr.2"."Char 13" AS C218,SLEE 
  4   81057664     MERGE INTO BEVERAGES.ATABLE77 USING ( select ROW_NUMBER() OVER() 
  5   70487710     (SELECT DISTINCT  (CORR_1.I38) AS I94,CORR_1.BIG7 BIG168,SLEEMA 
  6   60477984     SELECT ALL  CORR_1.FLOAT8 F58,CORR_1.BIGINT1 AS "Bigint 66",COR 
  7   60336985     SELECT CORR_2.R237 AS R93,BEVERAGES.ALIAS166."TimeStamp 107" TS2 
  9   54206591     MERGE INTO BEVERAGES."Table 262" USING ( select ROW_NUMBER() OVE 
  10  47116331     MERGE INTO BEVERAGES."Alias 195" USING ( select ROW_NUMBER() OVE 
  2   46613061     MERGE INTO BEVERAGES.ATABLE4 USING ( select ROW_NUMBER() OVER()  
  11  45829234     MERGE INTO BEVERAGES.ALIAS192 USING ( select ROW_NUMBER() OVER() 
  12  37086592     MERGE INTO BEVERAGES.ALIAS285 USING ( select ROW_NUMBER() OVER() 
                                                                                   
  Top 10 statements by TOTAL_ACT_WAIT_TIME                                         
  -------------------------------------------------------------------------------- 
  #   TOTAL_ACT    LOCK_WAIT    STMT_TEXT                                          
      _WAIT_TIME   _TIME                                                           
  --  -----------  -----------  -------------------------------------------------- 
  1   837643570    51331865     REFRESH TABLE BEVERAGES.STABLE270                   
  2   62364041     0            MERGE INTO BEVERAGES.ATABLE4 USING ( select ROW_NUM 
  13  47557693     0            MERGE INTO BEVERAGES.TABLE492 USING ( select ROW_NU 
  10  33165279     0            MERGE INTO BEVERAGES."Alias 195" USING ( select ROW 
  14  22967180     0            MERGE INTO BEVERAGES.ALIAS15 USING ( select ROW_NUM 
  15  18601900     600006       MERGE INTO BEVERAGES.ATABLE206 USING ( select ROW_N 
  16  13210604     595033       INSERT INTO BEVERAGES.TABLE18000084  VALUES (?,?,DE 
  12  12269241     0            MERGE INTO BEVERAGES.ALIAS285 USING ( select ROW_NU 
  11  11376752     0            MERGE INTO BEVERAGES.ALIAS192 USING ( select ROW_NU 
  4   10801100     0            MERGE INTO BEVERAGES.ATABLE77 USING ( select ROW_NU 
                                                                                   
  Top 10 statements by TOTAL_ACT_WAIT_TIME per exec                                
  -------------------------------------------------------------------------------- 
  #   TOTAL_ACT    LOCK_WAIT    STMT_TEXT                                          
      _WAIT_TIME   _TIME                                                           
  --  -----------  -----------  -------------------------------------------------- 
  13  47557693     0            MERGE INTO BEVERAGES.TABLE492 USING ( select ROW_NU 
  10  33165279     0            MERGE INTO BEVERAGES."Alias 195" USING ( select ROW 
  2   20788013     0            MERGE INTO BEVERAGES.ATABLE4 USING ( select ROW_NUM 
  15  18601900     600006       MERGE INTO BEVERAGES.ATABLE206 USING ( select ROW_N 
  16  13210604     595033       INSERT INTO BEVERAGES.TABLE18000084  VALUES (?,?,DE 
  12  12269241     0            MERGE INTO BEVERAGES.ALIAS285 USING ( select ROW_NU 
  11  11376752     0            MERGE INTO BEVERAGES.ALIAS192 USING ( select ROW_NU 
  4   10801100     0            MERGE INTO BEVERAGES.ATABLE77 USING ( select ROW_NU 
  17  10122121     1687005      INSERT INTO BEVERAGES.TABLE429 ("Char 305",TM310,CL 
  9   8999691      0            MERGE INTO BEVERAGES."Table 262" USING ( select ROW 
                                                                                   
  Top 10 statements by ROWS_READ + ROWS_MODIFIED                                   
  -------------------------------------------------------------------------------- 
  #   ROWS_READ +    STMT_TEXT                                                     
      ROWS_MODIFIED                                                                
  --  -------------  ------------------------------------------------------------- 
  1   63477200       REFRESH TABLE BEVERAGES.STABLE270                              
  2   15529196       MERGE INTO BEVERAGES.ATABLE4 USING ( select ROW_NUMBER() OVER( 
  4   8285227        MERGE INTO BEVERAGES.ATABLE77 USING ( select ROW_NUMBER() OVER 
  14  6831053        MERGE INTO BEVERAGES.ALIAS15 USING ( select ROW_NUMBER() OVER( 
  9   5871401        MERGE INTO BEVERAGES."Table 262" USING ( select ROW_NUMBER() O 
  10  5451659        MERGE INTO BEVERAGES."Alias 195" USING ( select ROW_NUMBER() O 
  18  5434297        INSERT INTO BEVERAGES.TABLE1555 (D53,CLOB1,"VarChar 11",TM2,LV 
  8   4240138        MERGE INTO BEVERAGES.ATABLE221 USING ( select ROW_NUMBER() OVE 
  19  3540892        MERGE INTO BEVERAGES.TABLE267 USING ( select ROW_NUMBER() OVER 
  3   3018284        SELECT ALL  "Corr.2".R17 AS R28,"Corr.2"."Char 13" AS C218,SL 
                                                                                   
  Top 10 statements by ROWS_READ + ROWS_MODIFIED per exec                          
  -------------------------------------------------------------------------------- 
  #   ROWS_READ +    STMT_TEXT                                                     
      ROWS_MODIFIED                                                                
  --  -------------  ------------------------------------------------------------- 
  20  9189048        MERGE INTO BEVERAGES.ALIAS56 USING ( select ROW_NUMBER() OVER( 
  4   8285227        MERGE INTO BEVERAGES.ATABLE77 USING ( select ROW_NUMBER() OVER 
  9   5737472        MERGE INTO BEVERAGES."Table 262" USING ( select ROW_NUMBER() O 
  10  5451659        MERGE INTO BEVERAGES."Alias 195" USING ( select ROW_NUMBER() O 
  18  5434297        INSERT INTO BEVERAGES.TABLE1555 (D53,CLOB1,"VarChar 11",TM2,LV 
  2   5176398        MERGE INTO BEVERAGES.ATABLE4 USING ( select ROW_NUMBER() OVER( 
  3   3018284        SELECT ALL  "Corr.2".R17 AS R28,"Corr.2"."Char 13" AS C218,SL 
  21  2157781        DELETE FROM BEVERAGES.VIEW11000 WHERE CURRENT OF SQL_CURSH100C 
  14  1366210        MERGE INTO BEVERAGES.ALIAS15 USING ( select ROW_NUMBER() OVER( 
  7   1081100        SELECT CORR_2.R237 AS R93,BEVERAGES.ALIAS166."TimeStamp 107" T 
                                                                                   
  Top 10 statements by number of executions                                        
  -------------------------------------------------------------------------------- 
  #   Executions            STMT_TEXT                                              
  --  --------------------  ------------------------------------------------------ 
  22  11003                 SELECT CASE WHEN (:HV00013  :HI00013  <= :HV00012  :HI 
  23  2240                  CALL SAVE_EXEC_INFO (CAST(:HV00040  :HI00040  AS "SYSI 
  24  2240                  SET :HV00017  :HI00017  = RPAD(VARCHAR(:HV00035  :HI00 
  25  2240                  VALUES (:HV00028  :HI00028  + 1, :HV00026  :HI00026  + 
  26  2198                  CALL SAVE_EXEC_INFO (CAST(:HV00040  :HI00040  AS "SYSI 
  27  2198                  SET :HV00017  :HI00017  = RPAD(VARCHAR(:HV00035  :HI00 
  28  2198                  VALUES (:HV00028  :HI00028  + 1, :HV00026  :HI00026  + 
  29  2184                  CALL SAVE_EXEC_INFO (CAST(:HV00040  :HI00040  AS "SYSI 
  30  2184                  SET :HV00017  :HI00017  = RPAD(VARCHAR(:HV00035  :HI00 
  31  2184                  VALUES (:HV00028  :HI00028  + 1, :HV00026  :HI00026  + 
                                                                                   
  Top 10 statements by I/O wait time                                               
  -------------------------------------------------------------------------------- 
  #   I/O                   STMT_TEXT                                              
      wait time                                                                    
  --  --------------------  ------------------------------------------------------ 
  1   1710806               REFRESH TABLE BEVERAGES.STABLE270                       
  32  30691                 UPDATE BEVERAGES.ATABLE132 SET R19 = ?, BIG28 = ?, "Big 
  33  20167                 MERGE INTO BEVERAGES.TABLE66 USING ( select ROW_NUMBER( 
  34  19704                 MERGE INTO BEVERAGES.ALIAS53 USING ( select ROW_NUMBER( 
  11  17148                 MERGE INTO BEVERAGES.ALIAS192 USING ( select ROW_NUMBER 
  35  16012                 DELETE FROM BEVERAGES.ATABLE49 CORR_1 WHERE NOT CORR_1. 
  36  9658                  SELECT ALL  * FROM BEVERAGES.ATABLE137 WHERE DIGITS(SLE 
  37  5068                  SELECT ARRAY_AGG(P.EXECUTABLE_ID ORDER BY M.ROWS_READ_ 
  38  4620                  SELECT ARRAY_AGG(P.EXECUTABLE_ID ORDER BY M.WAIT_TIME  
  39  4408                  CALL COMMONREQMETRICS (:HV00017  :HI00017 , :HV00029   
                                                                                   
  Top 10 statements by I/O wait time per exec                                      
  -------------------------------------------------------------------------------- 
  #   I/O                   STMT_TEXT                                              
      wait time                                                                    
  --  --------------------  ------------------------------------------------------ 
  11  17148                 MERGE INTO BEVERAGES.ALIAS192 USING ( select ROW_NUMBER 
  35  16012                 DELETE FROM BEVERAGES.ATABLE49 CORR_1 WHERE NOT CORR_1. 
  32  10258                 UPDATE BEVERAGES.ATABLE132 SET R19 = ?, BIG28 = ?, "Big 
  36  9658                  SELECT ALL  * FROM BEVERAGES.ATABLE137 WHERE DIGITS(SLE 
  34  6568                  MERGE INTO BEVERAGES.ALIAS53 USING ( select ROW_NUMBER( 
  40  3580                  SELECT ALL  * FROM BEVERAGES.TABLE1711 AS CORR_1 WHERE  
  41  3201                  SELECT * FROM BEVERAGES.ATABLE182 WHERE BEVERAGES.ATABLE 
  33  3074                  MERGE INTO BEVERAGES.TABLE66 USING ( select ROW_NUMBER( 
  42  2554                  (SELECT DISTINCT  CAST(REAL(CORR_1.BIG26) AS BIGINT) B 
  43  2037                  SELECT ALL  * FROM BEVERAGES."Table 309" CORR_1 WHERE N 
                                                                                   
  ================================================================================ 

                                ...

  5702 record(s) selected.

  Return Status = 0

Sample MONREPORT.PKGCACHE output - executable IDs section

  Part 2 - EXECUTABLE_IDs for statements in Part 1                                 
                                                                                   
  #   EXECUTABLE_ID                                                                
  --  ---------------------------------------------------------------------------- 
  1   x'0000000000000000000000000000000000000000000000000000000000000000'          
  2   x'00000001000000140000000000000D2700000000000220100709115120268695'          
  3   x'00000001000000140000000000000E6C00000000000220100709120044209447'          
  4   x'00000001000000140000000000000DC600000000000220100709115410328241'          
  5   x'000000010000001400000000000002D900000000000220100709110749184021'          
  6   x'00000001000000140000000000000E9B00000000000220100709120155462463'          
  7   x'00000001000000140000000000000E9D00000000000220100709120157412003'          
  8   x'000000010000001400000000000011D500000000000220100709122659481990'          
  9   x'000000010000001400000000000009CE00000000000220100709113639631277'          
  10  x'0000000100000014000000000000100700000000000220100709120644845416'          
  11  x'00000001000000140000000000000D0B00000000000220100709115047075877'          
  12  x'000000010000001400000000000016EF00000000000220100709123907119008'          
  13  x'00000001000000140000000000000CED00000000000220100709114935030259'          
  14  x'000000010000001400000000000015C100000000000220100709123230712187'          
  15  x'000000010000001400000000000012C100000000000220100709122838186211'          
  16  x'0000000100000014000000000000019800000000000220100709110301099171'          
  17  x'000000010000001400000000000013A900000000000220100709123040915052'          
  18  x'000000010000001400000000000012C400000000000220100709122840383498'          
  19  x'000000010000001400000000000001DB00000000000220100709110315247381'          
  20  x'000000010000001400000000000015D200000000000220100709123250786793'          
  21  x'00000001000000140000000000001ADB00000000000220100709125234197746'          
  22  x'000000010000000A00000000000000EC00000001000120100617140232799231'          
  23  x'000000010000000A00000000000000ED0000003E000120100617140242790185'          
  24  x'000000010000000A00000000000000ED0000003F000120100617140242790185'          
  25  x'000000010000000A00000000000000ED00000040000120100617140242790185'          
  26  x'000000010000000A00000000000000ED00000030000120100617140242790185'          
  27  x'000000010000000A00000000000000ED00000031000120100617140242790185'          
  28  x'000000010000000A00000000000000ED00000032000120100617140242790185'          
  29  x'000000010000000A00000000000000ED00000022000120100617140242790185'          
  30  x'000000010000000A00000000000000ED00000023000120100617140242790185'          
  31  x'000000010000000A00000000000000ED00000024000120100617140242790185'          
  32  x'00000001000000140000000000001CB500000000000220100709130624085853'          
  33  x'000000010000001400000000000007FA00000000000220100709112357603412'          
  34  x'000000010000001400000000000011FE00000000000220100709122735864279'          
  35  x'0000000100000014000000000000177900000000000220100709124042190331'          
  36  x'0000000100000014000000000000120D00000000000220100709122739120454'          
  37  x'000000010000000A00000000000000ED0000003B000120100617140242790185'          
  38  x'000000010000000A00000000000000ED0000002D000120100617140242790185'          
  39  x'000000010000000A00000000000000EE00000048000120100617140330704745'          
  40  x'00000001000000140000000000000C0300000000000220100709114356148854'          
  41  x'00000001000000140000000000000C7E00000000000220100709114609007957'          
  42  x'0000000100000014000000000000013C00000000000220100709110248940246'          
  43  x'000000010000001400000000000001C400000000000220100709110306905882'
Parent topic: MONREPORT module