Re: Performance solutions (DBMON)

[mbreit@xxxxxxxxxx]

For information on what various fields/values in the database monitor output
file mean, look in the "DB/2 for AS/400 Database Programming" manual which is
available on the web (along with all other AS/400 manuals) at:

http://publib.boulder.ibm.com/pubs/html/as400/online/homeeng1.htm

As for the specific field you asked about, QQRCOD (reason code), it has
different meanings depending on what type of record it appears with (QQRID is
the record ID).  Here is the information for the codes you asked about:

QQRID = 3000 means arrival sequence (table scan) was used to implement query
     QQRCOD in this case tells you why arrival sequence was chosen
          T1 = no indexes exist
          T2 = indexes exist, but none could be used
          T3 = Query Optimizer chose table scan over available indexes

QQRID = 3001 means an existing index was used to implement query
     QQRCOD in this case tells you why the specific index was chosen
          I1 = index chosen for record selection
          I2 = index chosen for ordering/grouping
          I3 = index chosen for record selection and ordering/grouping
          I4 = index chosen for nested group join
          I5 = index chosen for record selection using bitmap processing

QQRID = 3002 means a tempory index was created to implement the query
     QQRCOD in this case tells you why the temporary index was created
          I1 = index created for record selection
          I2 = index created for ordering/grouping
          I3 = index created for record selection and ordering/grouping
          I4 = index created for nested group join

Note that when the DBMON output recommends an index should be created
(QQIDXA='Y') you should not only look at what key fields it recommends but also
look at the actual SQL statement the recommendation was made for.  In some
cases, the output of DBMON will not recommend key fields required for ordering
and grouping.  By looking at the SQL statement you can see this.  Also, even SQL
statements that do not show up as recommending an index (QQIDXA='Y') can be
helped by having an index if either a table scan or temporary index is being
used and the statement executes many, many times.  The following query against
output from DBMON will show which SQL statements used the most cumulative CPU
and thus should be examined closer:

select sum(qqi4), count(*), qqucnt, qq1000 from <table>
     where qqrid = 1000 group by qqucnt, qq1000 order by 1 desc

Users looking for a way to analyze DBMON output without writing your own queries
should either look at enhancements in Operations Navigator with the latest
version of Client Access, or a third party product.  Operations Navigator in it
latest version, released at the same time as V4R4, contains graphical interface
to start/stop the monitor and also predefined reports that can be generated
using the GUI.  Centerfield Technologies has a product which is a windows based
for analyzing AS/400 DB2 SQL performance and uses the DBMON data plus its own
logic to walk you through the analysis of the system, a job, a SQL statement,
etc from a windows GUI.

Mike Breitbach  -  Software Engineer
IBM Rochester,  AS/400 ERP Development
email: mbreit@us.ibm.com


+---
| This is the BPCS Users Mailing List!
| To submit a new message, send your mail to BPCS-L@midrange.com.
| To subscribe to this list send email to BPCS-L-SUB@midrange.com.
| To unsubscribe from this list send email to BPCS-L-UNSUB@midrange.com.
| Questions should be directed to the list owner: dasmussen@aol.com
+---