Index Advisor and JDEdwards performance on the AS/400

This is an easy job that can bear a lot of fruit for the AS/400 – Index rationalisation.

Firstly, run some performance monitors over the 400 and trace activity for an hour or two.  For a busy system this could easily write 4GB an hour of logs to your trace file.

View the summary of these traces and look at your read and write / statement ratios

Look at your select to insert and update ratios and get a general feel how you might benefit from more indexes (generally you will).  The above shows a fairly normal read:write ratio of 20:1.

You can go through the details of these files and look for the indexes that the 400 had to make on the fly (fix them) and look at the index recommendations that comes from the period, you can easily generate them.

BUT, here is the best thing that the 400 offers – add your production library into the schemas that you see user database, databasename.

This will be recording (depending on certain system settings and OS release levels) ALL recommended indexes for that library since the beginning of time (perhaps not literally, but I think that the 400 has been around for that long, well perhaps not literally – hehe).

Right click the library / schema that you want to see the indexes advisements for and choose “Index advisor”.

It shows you the following:

Holy moly, that is totally AWESOME for tuning.  Create the indexes that you want with another SIMPLE right click “create index”

Don’t stress, create index does not just create the index, you gotta name it and do a few other things.  You get the screen above.  You can then copy the details out (Show SQL)

/* Creating index PRODDTA.F4108_SRM01
Sort Sequence: None (Sort by hexadecimal value) */
CREATE INDEX PRODDTA.F4108_SRM01 ON PRODDTA.F4108 (IOMCU ASC , IOLOTN ASC , IODOCO ASC );

Wow, this has been recommended 60 billion times, I might just add it!

Remember to di this for ALL libraries that might get used.  You’ll be surprised when doing it for SY900, SVM900, PRODCTL, DD900 and OL900 ! 

Brace yourself for a faster system.

Reset all of the stats and do it again a few months later.

Extra for Experts! http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=%2Frzajq%2Fidxadvisor.htm

The index advisor information can be found in the Database Monitor view 3020 - Index advised (SQE).

The advisor information is stored in columns QQIDXA, QQIDXK and QQIDXD. When the QQIDXA column contains a value of 'Y' the optimizer is advising you to create an index using the key columns shown in column QQIDXD. The intention of creating this index is to improve the performance of the query.

In the list of key columns contained in column QQIDXD the optimizer has listed what it considers the suggested primary and secondary key columns. Primary key columns are columns that should significantly reduce the number of keys selected based on the corresponding query selection. Secondary key columns are columns that may or may not significantly reduce the number of keys selected.

Column QQIDXK contains the number of suggested primary key columns that are listed in column QQIDXD. These are the left-most suggested key columns. The remaining key columns are considered secondary key columns and are listed in order of expected selectivity based on the query. For example, assuming QQIDXK contains the value of 4 and QQIDXD specifies 7 key columns, then the first 4 key columns specified in QQIDXK is the primary key columns. The remaining 3 key columns are the suggested secondary key columns.

http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=%2Frzajq%2Fidxadvisor.htm

Create View QQQ3020 as 
  (SELECT QQRID as Row_ID, 
          QQTIME as Time_Created, 
          QQJFLD as Join_Column, 
          QQRDBN as Relational_Database_Name, 
          QQSYS as System_Name, 
          QQJOB as Job_Name, 
          QQUSER as Job_User, 
          QQJNUM as Job_Number, 
          QQI9 as Thread_ID, 
          QQUCNT as Unique_Count, 
          QQUDEF as User_Defined,  
          QQQDTN as Unique_SubSelect_Number, 
          QQQDTL as SubSelect_Nested_Level, 
          QQMATN as Materialized_View_Subselect_Number, 
          QQMATL as Materialized_View_Nested_Level, 
          QVP15E as Materialized_View_Union_Level, 
          QVP15A as Decomposed_Subselect_Number, 
          QVP15B as Total_Number_Decomposed_SubSelects, 
          QVP15C as Decomposed_SubSelect_Reason_Code,
          QVP15D as Starting_Decomposed_SubSelect,
          QQTLN as System_Table_Schema, 
          QQTFN as System_Table_Name, 
          QQTMN as Member_Name, 
          QQPTLN as System_Base_Table_Schema, 
          QQPTFN as System_Base_Table_Name, 
          QQPTMN as Base_Member_Name, 
          QVPLIB as Base_Table_Schema,
          QVPTBL as Base_Table_Name,
          QQTOTR as Table_Total_Rows, 
          QQEPT as Estimated_Processing_Time,   
          QQIDXA as Index_is_Advised, 
          QQIDXD as Index_Advised_Columns_Short_List,
          QQ1000L as Index_Advised_Columns_Long_List,
          QQI1 as Number_of_Advised_Columns,
          QQI2 as Number_of_Advised_Primary_Columns,   
          QQRCOD as Reason_Code, 
          QVRCNT as Unique_Refresh_Counter, 
          QVC1F as Type_of_Index_Advised, 
          QQNTNM as NLSS_Table, 
          QQNLNM as NLSS_Library
   FROM   UserLib/DBMONTable
   WHERE  QQRID=3020)