Db2

Db2

Connect with Db2, Informix, Netezza, open source, and other data experts to gain value from your data, share insights, and solve problems.

 View Only

  • 1.  How to get "Transaction Per Second"?

    Posted Thu June 15, 2023 09:07 AM
    Edited by Kristen Park Fri June 16, 2023 10:37 AM

    Hi Db2 Experts, 

    How to get the "Transaction Per Second" for Db2? Is there any command can get this value? Snapshot? Event Monitor? 



    ------------------------------
    Cui Mei
    IT Specialist
    IBM
    ------------------------------



  • 2.  RE: How to get "Transaction Per Second"?

    Posted Thu June 15, 2023 05:29 PM

    Hi,

    There are various transaction related metrics collected at different levels.
    For example, total_app_commits , total_app_rollbacks, which are collected at the database or connection level (and at a number of other levels as well).
    But these counters are incremented only for the corresponding object, no any history of their values is saved by Db2 itself.
    So, if you need to get transaction ratio for the database during some period of time (not from the database activation time till current), you should save somewhere these values (plus may be the same for internal commits & rollbacks) at start and end of a period of measurement, calculate the difference and divide the sum of these differences to the period duration.



    ------------------------------
    Mark Barinstein
    ------------------------------

    Original Message


  • 3.  RE: How to get "Transaction Per Second"?

    Posted Fri June 16, 2023 01:45 AM

    Hi Cui Mei,

    you might want to look into these two pages for the db2mon scripts. They do the delta, Mark mentioned earlier.

    https://www.ibm.com/docs/en/db2/11.1?topic=tuning-collecting-reporting-performance-monitor-data

    https://www.ibm.com/support/pages/db2mon-script-monitoring-performance

    You could also look into calling MON_GET_DBSUMMARY procendure to get a quick overview.

    https://www.ibm.com/docs/en/db2/11.1?topic=mv-mon-db-summary-retrieve-accumulated-metrics-across-all-members-database

    Cheers



    ------------------------------
    Roland Schock
    IBM Champion and IBM Gold Consultant
    ------------------------------

    Original Message


  • 4.  RE: How to get "Transaction Per Second"?

    Posted Fri June 16, 2023 04:56 AM

    Actually, my first answer wasn't correct.
    Db2 does support metrics aggregation on its own with event monitor for statistics and a set of corresponding table functions.
    It's probably better to start with the Resetting statistics on Db2 workload management objects article.

    Briefly:
    The wlm_collect_int database configuration parameter controls how often accumulated statistics is reset.
    You may use the uow_throughput - Unit of work throughput monitor element to get what you need.
    If an event monitor for statistics is active, this accumulated statistics is saved to the event monitor tables for further analysis if needed.
    In-memory statistics returned by the MON_GET_* table functions mentioned at the last link is collected since the last statistics reset.
    The MON_SAMPLE_* functions are for a custom interval collection passed as a parameter.
    You should aggregate the result, if you want to get the result for a whole database, since these functions / tables return / contain info for WLM objects (service classes and workloads).




    ------------------------------
    Mark Barinstein
    ------------------------------

    Original Message


  • 5.  RE: How to get "Transaction Per Second"?

    Posted Tue June 20, 2023 09:59 PM

    Thank you so much Roland and Mark replies, appreciate it. 



    ------------------------------
    Cui Mei
    IT Specialist
    IBM
    Petaling Jaya
    ------------------------------

    Original Message


  • 6.  RE: How to get "Transaction Per Second"?

    Posted Wed June 21, 2023 05:52 AM

    Here is the query that works best for me, after the workload or transactions you want to perform : 

    SELECT AGENT_ID, SNAP_TIME, COMMIT_SQL_STMTS
    FROM SYSIBMADM.MON_TRANSACTION_START
    WHERE SNAP_TIME = (SELECT MAX(SNAP_TIME) FROM SYSIBMADM.MON_TRANSACTION_START)



    ------------------------------
    Youssef Sbai Idrissi
    Software Engineer
    ------------------------------

    Original Message


  • 7.  RE: How to get "Transaction Per Second"?

    Posted Wed June 21, 2023 05:59 AM

    Wow. Thank you so much Youssef! This will be helpful. 



    ------------------------------
    Cui Mei
    IT Specialist
    IBM
    Petaling Jaya
    ------------------------------

    Original Message


  • 8.  RE: How to get "Transaction Per Second"?

    Posted Wed June 21, 2023 12:36 PM

    The view sysibmadm.MON_DB_SUMMARY which is created as part of the database creation will tell you the current number of commits and rollbacks, if you select that together with a timestamp you can get the counts every hour or every day and just divide the difference by the number of hours (divide by "number of seconds between snapshots" and multiply by 3600 will be more accurate).



    ------------------------------
    Tommy Petersen
    ------------------------------

    Original Message


  • 9.  RE: How to get "Transaction Per Second"?

    Posted Wed June 21, 2023 10:11 PM

    Thank you Tommy for your input. This is very useful for me too! 



    ------------------------------
    Cui Mei
    IT Specialist
    IBM
    Petaling Jaya
    ------------------------------

    Original Message