db2 "list applications show detail"
This will give you a hint as to which connections are currently executing and if there are a number of connections in a lock wait status.
If you observe lock waits, you should next take a deeper look into the lock contention with the command:
db2 "get snapshot for locks on DBNAME"
This verbose report will show you which application connections are holding locks and which are waiting. Compounding matters, some of the connections that are waiting may have other connections waiting on them. A real snowball effect can occur if you have LOCKTIMEOUT set to -1 (infinity, never time out) or set higher than 30 seconds.
In the case of lock contention, your remedy usually involves killing the elephant with the command:
db2 "force application (application-handle) "
You are more likely to have lock contention issues in an OLTP database than a Data Warehouse (DW) database. OLTP databases are more prone to mosquito infestations than elephants, but the occasional elephant has been known to wreak havoc in the best tuned OLTP databases. Your DW databases, in the absence of a optimum physical design, are highly prone to the type of elephant that every DBA fears most - the type of long running, resource consuming query that sucks the life out of your system, makes the lights go dim, and causes angry phone calls demanding to know "What's happening right now??!!??!!"
OK, so, assuming your database is free of lock contention elephants, let us next try to find and kill the ones that are sucking the life out of your system.
To find details about connections that currently exist in your database, you need to use the command:
db2 "get snapshot for applications on DBNAME" Click Here to see sample output from this command
If you have a DB2 9 database, a SQL snapshot command can be used as an alternative:
db2 "select * from SYSIBMADM.SNAPAPPL"
Of course, you will likely want to request specific columns and add sort criteria to the above query, but first let's chat about the important numbers. You want to look for application connections that have high costs and inefficient ratios:
db2 "force application (application-handle)"
Before we wrap this up, let's return to that DB2 9 SQL Snapshot Query and make it a little more useful:
SELECT SUBSTR ( DB_NAME,1,8 ) AS DB_NAME, AGENT_ID AS FORCEAPPLID, ROWS_READ, ROWS_WRITTEN, (ROWS_READ / (ROWS_SELECTED + 1)) AS IREF, (AGENT_USR_CPU_TIME_S + AGENT_SYS_CPU_TIME_S) AS CPUSEC FROM SYSIBMADM.SNAPAPPL WHERE UOW_STOP_TIME IS NULL ORDER BY ROWS_READ DESC
This query may help you identify your elephants and show you the Application ID that you would want to FORCE. I added +1 to ROWS_SELECTED because I'm lazy and didn't want to write a CASE statement.
I fully realize there are many smart people worldwide who read this blog, and I am sure some of you have your own "war chest" of your favorite queries that you use. If you like, please post a comment and share your favorites. Posting anonymously (no fancy account required) only takes a minute.
Some clients and customers tell me they'd like a performance monitoring tool that would show them every SQL statement, its resources consumed, who did it, when, and from where. Folks, this is Database Activity Monitoring or Auditing. If you want to see every statement, then you are obsessed with elephant hunting. Elephant hunting is not workload performance management and does not provide long term tuning and optimization. The next blog post on mosquito extermination will more fully address statement workload analysis and long term optimization for greater efficiency, speed, and performance reliability.
The Shameless Marketing Moment
|DBI's Brother-Eagle™ for DB2 LUW is a real-time monitor for DB2 database performance that automatically computes several key metrics for you. Best of all, the Standard Edition is FREE. Enterprise Edition enables additional drill downs to connection and lock activity, and you can easily FORCE connections if you like. So, if ELEPHANT HUNTING is your sport, Brother-Eagle® is your ideal tool.|
Just for Fun!
Read my other blog for more thoughts and topics.
If you use the internet and enjoy downloading movies, music, and more, the subject of Net Neutrality may interest you. Visit www.savetheinternet.com and learn more.
With kindest regards,
President & CEO, DBI
IBM GOLD Consultant
DBI is an IBM Advanced, Industry Optimized, Business Partner
DBI is an Oracle Technology Network Partner
DBI provides products and services to over 2,000 customers worldwide
Your Performance IS Our Business