Query Plan and resource usage? 
Author Message
 Query Plan and resource usage?
I am new to DB2.
Wondering How to view/find query plan?
And how to find out wether use connection is active or hung?

thanx a lot
-hsmith



Tue, 14 Sep 2004 05:06:44 GMT
 Query Plan and resource usage?

Hello Harry

I am assuming by query plan you mean the access plan chosen by DB2 for a
given SQL.  If so this can be found by using db2expln and db2exfmt
utilities.  To use these first you need to create EXPLAIN tables.  DB2/UDB
comes with DDLs to build the EXPLAIN tables.  Then you can use db2expln and
db2exfmt utilities.  db2expln takes an SQL statement and populates the
EXPLAIN tables with the query access plan information.  db2exfmt extracts
the access plan information from EXPLAIN tables and formats them into a
report.  You can read about these commands and the EXPLAIN tables in the DB2
UDB Administration guide

To find out if a user connection is active or hung first we need to find out
the application handle id assigned by DB2 for that connection.  This
information can be found by the command "db2 list applications show detail".
One of the columns in the output of the command is the application handle id
and another one is the DB2 Authorization id (basically the id used to
connect to DB2 by the user connection).  These would help you identify the
application handle id.  Once it is found you can look at the Status of the
application and the Last Status Change time in the same "db2 list
applications show detail" command output.  If an application connection is
inactive in DB2 it the Status will show as "UOW Wait".  If the connection's
status is "UOW Wait" and the Last Status Change time is some time in the
past (say 5 seconds from the current time) then you can conclude that
connection is inactive in DB2.

Hope this helps

Madhavan Balaji
IBM Certified Solution Expert for DB2/UDB 7.1 Admin

Quote:
> I am new to DB2.
> Wondering How to view/find query plan?
> And how to find out wether use connection is active or hung?

> thanx a lot
> -hsmith



Tue, 14 Sep 2004 09:57:45 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. CPU Usage 100% When Query Plan Shows Table Spool

2. SQL 7.0 Query Analyzer resource usage.

3. Why is Stored Proc plan slower than query plan

4. Execution plan,Query plan?

5. Locking Plan part of Query Plan?

6. Running job for maintenance plan causes RAM usage to hold at 50%

7. Interpretation of Explain Plan to Prevent IO Usage Error

8. Seemingly unrelated change causes increased resource usage

9. Enterprise Manager resource usage

10. Temdb Resource Usage

11. error log resource usage??

12. [INTERFACES] Two minor fixes to reduce resource usage in JDBC drivers


 
Powered by phpBB® Forum Software