Re: SYSIXADV Question.

Hi Mike,

The system table SYSIXADV was introduced with release V5R4. In this table
all from either CQE (classic query engine) or SQE (SQL Query engine) adviced
indexes are stored, including how often was this index adviced and when it
was adviced for the last time. Also information about maintained temporary
indexes (MTI), i.e. temporary indexes created by the SQE that can be used by
all jobs and remain existent until the last reference to this index
disapears from the SQE plan cache.

If your production machine is not yet on release V5R4 you'll not find this
table.

Before release V5R4 you have several other methods to analyze your SQL
statements to determine whether an index is adviced or not:
1. Before executing a SQL statement interactively or calling a program with
embedded SQL, just execute the CL-Command STRDBG (Start Debug). Now almost
all SQL messages (including index advices) get stored in the joblog.
2. Execute the SQL-Statement with iSeries Navigator's Run an SQL script and
use Visual Explain to analyze the SQL statement. Visual Explain has an
integrated index advisor, that shows and allows you to create all adviced
indexes.
3. Start an Database/Performance monitor with either the CL command STRDBMON
or with iSeries Navigator and analyze the result after.

For more information about SQL optimization just have a look at the
following redbooks:
SQL Performance Diagnosis on IBM DB2 Universal Database for iSeries (based
on Release V5R3)
http://www.redbooks.ibm.com/abstracts/sg246654.html?Open

OnDemand SQL Performance Analysis Simplified on DB2 for i5/OS in V5R4
http://www.redbooks.ibm.com/abstracts/sg247326.html?Open

Database Performance and Query Optimization
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/rzajq/rzajq.pdf

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them?  Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von eftimios
pantzopoulos
Gesendet: Thursday, March 22, 2007 10:52
An: midrange-l@xxxxxxxxxxxx
Betreff: SYSIXADV Question.


I've been looking at the various aspects of tuning SQLs at a site I've just 
started working at. I saw some documentation concerning the SYSIXADV table 
in the catalog held in QSYS2. I found this table on our development box when

I was 'immersing' myself in this topic and thought excitedly went to our 
production box to see what the advisor thought about some poor running SQLs,

but I couldn't find it!

So I guess the questions are:

Should this file exist?
Does it need to be configured/initialised in some way?
Does it's non-appearance on the production machine reflect some sort of 
system deficiency?


I've also been reading some of the material in mid-range regading the use of

a DBA on iSeries sites, and the consensus seems to be that there's not 
enough work to keep a full-time DBA occupied. This new site I'm at has a 
massive financial application and has some 50 developers, some of whom know 
SQL, but none seem to be aware of the DB optimization features available.

Surely sites of this site elsewhere in the iSeries world have one or more 
people who are conversant with this sort of stuff?

I post my SQL questions here as it's the only list which seems to be 
relevant. Is it time for an SQL-L List?


Thanks in Advance,

Mike Pantzopoulos

_________________________________________________________________
Advertisement: Want FREE talk & text to 5 Telstra numbers? Find out how  
http://a.ninemsn.com.au/b.aspx?URL=http%3A%2F%2Fadsfac%2Enet%2Flink%2Easp%3F
cc%3DTEL243%2E40035%2E0%26clk%3D1%26creativeID%3D56076&_t=761565722&_r=Hotma
il_email_tagline_1March07&_m=EXT