I could use some help with indexing on the AS400. I have 5 reports that use the UNION ALL command to link together a similar SQL command about 30 different times. A very common representation of that SQL is:

PRODDTA.TBL_Chart.Account AS Account,
PRODDTA.TBL_Chart.Description AS Descp,
PRODDTA.TBL_BSLevel3.Level3Group AS Level3,
PRODDTA.TBL_BSLevel3.L3Order AS L3Order,
PRODDTA.TBL_BSLevel4.Level4Group AS Level4,
PRODDTA.TBL_BSLevel4.L4Order AS L4Order,
PRODDTA.TBL_BSLevel6.Level6Group AS Level6,
PRODDTA.TBL_BSLevel6.L6Order AS L6Order,
0 AS BAL3,
0 AS BAL6,
0 AS BAL9,
0 AS BALFOR,
PRODDTA.F0911LA.GLAA AS DISBAL, 0 AS ADDBAL ,0 AS ACQBAL ,0 AS RECBAL,0 AS TRNBAL,0 AS RETBAL,0 AS NRCBAL

FROM PRODDTA.TBL_Chart,PRODDTA.F0911LA,PRODDTA.F0006, PRODDTA.TBL_BSLevel3, PRODDTA.TBL_BSLevel4, PRODDTA.TBL_BSLevel5, PRODDTA.TBL_BSLevel6

WHERE PRODDTA.TBL_CHART.Account = PRODDTA.F0911LA.GLOBJ
AND PRODDTA.TBL_BSLevel3.Level3Group = PRODDTA.TBL_BSLevel4.Level3Group
AND PRODDTA.TBL_BSLevel4.Level4Group = PRODDTA.TBL_BSLevel5.Level4Group
AND PRODDTA.TBL_BSLevel5.Level5Group = PRODDTA.TBL_BSLevel6.Level5Group
AND PRODDTA.TBL_Chart.BSLevel7Group = PRODDTA.TBL_BSLevel6.Level6Group
AND PRODDTA.F0911LA.GLCTRY = 20
AND PRODDTA.F0911LA.GLFY = 03
AND PRODDTA.F0911LA.GLLT = 'AA'
AND PRODDTA.F0911LA.GLPOST = 'P'
AND PRODDTA.F0911LA.GLPN <= 9
AND PRODDTA.F0911LA.GLPN >= 9
AND PRODDTA.F0911LA.GLDCT = 'JD'
AND PRODDTA.F0911LA.GLCO = PRODDTA.F0006.MCCO
AND PRODDTA.F0911LA.GLMCU = PRODDTA.F0006.MCMCU
AND PRODDTA.F0006.MCRP04 = 'C'
AND PRODDTA.F0006.MCRP05 <> 'HPI'

My problem is with the F0911LA table. I have built an index for the following fields in the following order

CREATE INDEX PRODDTA.F0911_CIB1

ON PRODDTA.F0911 ( GLCTRY ASC , GLFY ASC , GLLT ASC , GLPOST ASC , GLPN ASC , GLDCT ASC , GLCO ASC , GLMCU ASC , GLOBJ ASC

This matches up with the order of the WHERE clause beginning with the where clause join and the following params. However, while the index is consistently used, the AS400 is building a temporary index on the GLOBJ column for every instance of the SQL rather than using the GLOBJ in the index. Furthermore, I have built a separate index with only that column and that is not used either. The temp index being built as many as 8 times in each report is really slowing things down.

What am I doing wrong?

Thanks in advance.

Ryan

What's a temp index?

And why this?

AND PRODDTA.F0911LA.GLPN <= 9
AND PRODDTA.F0911LA.GLPN >= 9

Also it looks like is the F0911LA driver...

Why not make a derived table out of that with you predicates...THEN join it to the other tables...

The AS400 will build a temporary index on the fly (much like a hash table) which takes more processor, and much more disk than using sufficient pre-built indexes. This is what I am trying to avoid. The <=9 and >=9 was a typo. It should have been <=9, >=8 (August 1 - Sept 1).

F0911LA is a logical file for F0911. The developers seem to have a habit of using logicals to view the parent file rather than going straight to the parent. I am trying to get them to change this since the size of the pages is 4K for a logical and I believe 64K for a regular file.

Regarding the derived table...I'm not sure since I am the admin rather than the one of the report writers. Maybe it has to do with our reporting software.

Is my logic correct for creating this index??