How to report on the execution count of a stored procedure?

Version 1
    Share:|

    This document contains official content from the BMC Software Knowledge Base. It is automatically updated when the knowledge article is modified.


    PRODUCT:

    MainView for DB2



    QUESTION:

    How to report on the execution count of a stored procedure?


    ANSWER:

    You can report on the execution count of stored procedure using DB2 field QPACSWITCH. QPACSWITCH field reports on the number of times package was invoked from a different package. For the first package run by an application, the initial call counts as a package switch. If this package called a nested package (such as a trigger, UDF, or stored procedure), a switch will not be counted upon return from such a package. 

    1) Data Collector batch report BACCPKSR - Thread accounting package summary report field  "Exec Count" .  
     

    1                       BACCPKSR: THREAD ACCOUNTING PKG SUMMARY                          02/16/11 14:14:48    PAGE     1      DS Group : DSNDIA        Member: DIA1     DB2 SSID : DIA1 9.1    Location: DIA                                                       Actual From: 2011-02-15-00.08.37                                                                                                         to: 2011-02-15-22.35.56                                     /------------------------------- Averages per package execution -----------------------------\     Collection ID/      Thrd  Exec Stmt GetPage   Class 2     CPU Time                 Class 8      I/O        Lock      Group     Package Name       Count Count Count   Count Elapsd Time     (All)     zIIP Time   Wait Time  Wait Time  Wait Time  Wait Time     -----------------  ----- ----- ----- ------- ----------- ----------- ----------- ----------- ---------- ---------- ----------  +  ABCCOLL              28    42    42     211  00:00.2569  00:00.0131  00:00.0000  00:00.0000 00:00.0000 00:00.0000 00:00.0000     ABCSPPKG                                                     5.12%       0.00%       0.00%      0.00%      0.00%      0.00%
      

    2) Performance Reporter DB2 table  DMRAPDTL - Package accounting detail table column PKGSWITCH.  

     


    Article Number:

    000125983


    Article Type:

    FAQ/Procedural



      Looking for additional information?    Search BMC Support  or  Browse Knowledge Articles