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
------------------------------

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
------------------------------

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
------------------------------

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
------------------------------

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

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

Original Message:
Sent: Fri June 16, 2023 04:55 AM
From: Mark Barinstein
Subject: How to get "Transaction Per Second"?

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
------------------------------

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
------------------------------

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

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

Original Message:
Sent: Wed June 21, 2023 05:51 AM
From: Youssef Sbai Idrissi
Subject: How to get "Transaction Per Second"?

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
------------------------------

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
------------------------------

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

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

Original Message:
Sent: Wed June 21, 2023 12:36 PM
From: Tommy Petersen
Subject: How to get "Transaction Per Second"?

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
------------------------------