0

Hi is there some easy way to find out what is transaction per minute value for the database? Should I use db2 get snapshot? We are using DB2 9.7 ESE on Linux

Thank you in advance

Improve this question
3
  • The answer will depend on the DB2 version and platform. Commented Feb 19, 2014 at 17:22
  • Hi thank you for reply its DB2 ESE 9.7 on Linux. Does this helps you? Commented Feb 19, 2014 at 17:25
  • can you please assist me if this configuration is visible somewhere in shapshot file? how to find it? Commented Feb 19, 2014 at 18:02

2 Answers 2

Reset to default
3

Since you are indeed interested in the transaction log sizing, not performance, you will get better information observing the actual log usage instead of the number of transactions. 

$ db2 get snapshot for database on sample | grep "Log space" Log space available to the database (Bytes)= 52893203 Log space used by the database (Bytes) = 94797
Improve this answer
5
  • Hi, thanks again. This statistics will show only for the time period when I turned MONITOR SWITCHES ON then do the GET SNAPSHOT and then MONITOR SWITCHES OFF? Am I right? Commented Feb 19, 2014 at 19:27
  • I did it: [ctginst1@tt-db-02 NODE0000]$ db2 get snapshot for database on maxdb71 | grep "Log space" Log space available to the database (Bytes)= 466439444 Log space used by the database (Bytes) = 1028844 Commented Feb 19, 2014 at 19:37
  • What does Log space used by the database shows? Current usage of ACTIVE logs (which are not archived) or total usage since last restart of database? Commented Feb 19, 2014 at 19:39
  • It's the total of log space used by all currently active (not committed) transactions. I think you may want to start reading the manual, as everything you're asking is actually described there. Commented Feb 19, 2014 at 19:44
  • thank you for your info. I foudn this also select snapshot_timestamp, db_conn_time, commit_sql_stmts, rollback_sql_stmts, timestampdiff(4,snapshot_timestamp-db_conn_time) as minutes,db_conn_time, (commit_sql_stmts+rollback_sql_stmts)/timestampdiff(4,snapshot_timestamp-db_conn_time) as transactions_per_minute from sysibmadm.snapdb Commented Feb 20, 2014 at 11:25
3

There is no "per minute" metric available, you will need to query the monitor repeatedly and divide the delta of the metric you're after by the time interval since the previous query. The following two queries will return the total number of application units of work and the total number of completed units of work, respectively.

SELECT TOTAL_APP_COMMITS + TOTAL_APP_ROLLBACKS FROM SYSIBMADM.MON_DB_SUMMARY SELECT SUM(TOTAL_APP_COMMITS) + SUM(INT_COMMITS) + SUM(TOTAL_APP_ROLLBACKS) + SUM(INT_ROLLBACKS) FROM TABLE(MON_GET_WORKLOAD('',-2))

There are other monitor views that you may find useful: http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.sql.rtn.doc/doc/r0023485.html#r0023485__mon_23485

You can obtain the same information using the GET SNAPSHOT command, but the use of old-style snapshot monitor is not recommended. For example, you can try this:

db2 get snapshot for database on sample | grep -Ei "commit|rollback"
Improve this answer
12
  • When I try to run your first command I am getting error that table does not exist. if I run your second command I am getting null value as result (is it because my DB is now in CIRCULAR mode) this seems that does not help me Commented Feb 19, 2014 at 18:27
  • Check out the note at pic.dhe.ibm.com/infocenter/db2luw/v9r7/topic/…: If your database was created in Version 9.7 before Fix Pack 1, to run this routine you must have already run the db2updv97 command. Commented Feb 19, 2014 at 18:33
  • These are db info: fix pakc 0:[ctginst1@tt-db-02 NODE0000]$ db2level DB21085I Instance "ctginst1" uses "64" bits and DB2 code release "SQL09070" with level identifier "08010107". Informational tokens are "DB2 v9.7.0.0", "s090521", "LINUXAMD6497", and Fix Pack "0". Commented Feb 19, 2014 at 18:35
  • OK, so you'll need to upgrade to a more recent fix pack. Until then you can still use the GET SNAPSHOT command to obtain the same metrics, but the use of traditional snapshots is not recommended. Commented Feb 19, 2014 at 18:41
  • 1
    Clearly, if you are not seeing the error, you have enough log space for your current workload. Commented Feb 19, 2014 at 19:20

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.