Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

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

2 Answers 2

up vote 2 down vote accepted

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
share|improve this answer
    
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? –  Dejan Feb 19 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 –  Dejan Feb 19 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? –  Dejan Feb 19 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. –  mustaccio Feb 19 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 –  Dejan Feb 20 at 11:25

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"
share|improve this answer
    
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 –  Dejan Feb 19 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. –  mustaccio Feb 19 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". –  Dejan Feb 19 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. –  mustaccio Feb 19 at 18:41
1  
Clearly, if you are not seeing the error, you have enough log space for your current workload. –  mustaccio Feb 19 at 19:20

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.