Re: Can I detect when an access path is created?

Ken,

In a nutshell, yes you can.  You need to use STRDBMON at the command line
to switch on the database monitor (& switch off after with ENDDBMON).  You
can switch on for a particular job, but you may not be able to predict
which QZDASOINIT job will end up processing your SQL request, so pesonally
I usually switch it on for *ALL.  The database monitor log files can get
very large very fast, so I would be careful about running on a production
system.

There's a field QQIDXA in the database monitor file created which will tell
you whether the query optimizer recommends that you create an index in
order to help it process a particular query, so you can search the file for
records where QQIDXA = 'Y' (Note this is not quite what you're asking for -
sometimes it will create a temporary index to retrieve data but NOT
"recommend" it).  The file is not that easy to read - you can view it
"nicely" using iSeries Navigator's "visual explain" feature but that
requires being able to identify the records that you want to look at.  So I
could make good use of the file and perform searches like looking for
QQIDXA = 'Y', I had to write a small tool to be able to view it.  There is
a very useful IBM RedPaper about the database monitor and how to make best
use of it (redp0502.pdf).

However practically I've found tuning queries to not be as simple as just
switching on the database monitor, throwing all possible SQL statements at
the iSeries, and then looking through the DB monitor logs to see what
indexes it suggests.  There's another redbook "Indexing strategies for DB2
for iSeries" which if you can read & digest everything this says (which
gets quite complex for SQL queries involving more than a few tables), it
basically tells you how to design the perfect indexes for every query,
rather than relying on the DB monitor to suggest them.

Hope this helps,

Nigel Gay,
Computer Patent Annuities.



                                                                           
             TitanRebel                                                    
             <TitanRebel@comca                                             
             st.net>                                                    To 
             Sent by:                  Java Programming on and around the  
             java400-l-bounces         iSeries / AS400                     
             @midrange.com             <java400-l@xxxxxxxxxxxx>            
                                                                        cc 
                                                                           
             27/10/2005 11:25                                      Subject 
                                       Can I detect when an access path is 
                                       created?                            
             Please respond to                                             
             Java Programming                                              
             on and around the                                             
              iSeries / AS400                                              
             <java400-l@midran                                             
                  ge.com>                                                  
                                                                           
                                                                           




I would like to detect when the iSeries must create an index (access
path) to retrieve data.  Is this possible?  The purpose would be to see
if any SQL statements have "slipped through the cracks" and identify
them to be "tuned".  Also, it would identify when/if an index or logical
doesn't exist on that particular iSeries (these JDBC apps run on
hundreds of iSeries).

In a perfect world... I would be able to look at the log and see any SQL
statement that forced the iSeries to create an index/access path.  Any
suggestions?

Thanks for any help!

Ken K.
--
This is the Java Programming on and around the iSeries / AS400 (JAVA400-L)
mailing list
To post a message email: JAVA400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/java400-l
or email: JAVA400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/java400-l.





********************************************************************************
The information in this message is confidential and may be legally
privileged. It is intended solely for the addressee; access to this
email by anyone else is unauthorised.

If you are not the intended recipient: (1) you are kindly requested
to return a copy of this message to the sender indicating that you
have received it in error, and to destroy the received copy; and (2)
any disclosure or distribution of this message, as well as any action
taken or omitted to be taken in reliance on its content, is prohibited
and may be unlawful.
********************************************************************************