DB2 - L

 View Only
  • 1.  Db2 12 for z/OS Package Allocations

    Posted Oct 19, 2022 09:53 AM

    OK, I know I have asked several times about package allocations on the accounting reports, but my application behavior has changed and revealed a new dynamic. So, I want to address this subject again.


    On the Db2 accounting report there are two fields I use to count executions of a Db2 native SQL procedure. This is what I have been doing:


    Number of rollups. This I have been using to measure the number of times a unit of work invokes a stored procedure, rolled up. In other words, if I summarize accounting records based upon a userid and the summary shows 100 commits then I assume 100 transactions. If the package rollups is 100 then I assume that for those 100 transactions the stored procedure was used 100% of the time. Then there is "number of allocations". If that number is 400 I assume that the average number of times that stored procedure was called is 4 per transaction.


    My application was previously showing about 6 allocations per commit, so I assumed 6 calls to the stored procedure per transaction. Now, the application behavior has changed dramatically (there was a new release). Several new stored procedures have been introduced and the need for multiple calls per transaction to the older stored procedure is gone. Everything looks great, except that the "number of allocations" is exactly twice the number of rollups. The developers say that the procedures are called once per transaction, and I believe them. The new procedures show the same thing, package allocations are exactly 2 times the rollup count. One of the new stored procedures simply executes a SQL statement and returns the result.


    So, I am thinking that the number of package allocations for a native stored procedure is counted twice.








  • 2.  RE: Db2 12 for z/OS Package Allocations

    Posted Oct 19, 2022 09:59 AM
    Oh, and to through a loop into this whole thing. One of the stored procedures sometimes calls an autonomous stored procedure. I get package statistics for that autonomous stored procedure and the number of rollups and number of allocations match perfectly. Ugh!