|
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
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.