AS400 optimizer

[blom0344]

AS400 optimizer is driving me nuts again.
With DB2 UDB on Windows I basically indexed the proper fields,maintained statististics and reorganized if indicated.
Only in rare occasions the optimizer seemed to get lost.

Not so on AS400. I had to recompile a facttable and rebuild the indices according to plan. Prior to the recompiling, a 'heavy' query took about 60 seconds to complete.
Query performance is not down to 12-20 minutes, with explain plans that show a variety of access paths.

Does anyone know good solid documentation on the AS400 optimizer?


Ties Blom
Information analyst

 

[juliane26]

How the optimizer works in detail is probably one very well kept IBM secret ....

If you can, you might want to open a PMR and let IBM explain what the problem is. That sometimes helps. Of course, for that it is useful to have before and after statistics and access plan.
Did you change anything else, like a new fixpak ?


Juliane

 

[blom0344]

Well, I did some further investigating.
It seems that our current version V5R2 does not really support Star Schema joins. This enhancement is available with V5R3 though, so we should probably upgrade the machine.

Strangely enough, performance was about equal to my old DB2 UDB environment on windows (with much slower CPU and 4 times less of memory) before I had to recompile. I thought that was pretty good

With UDB I just ran statistics on a table. With AS400 I need to specify which fields of a table should be analyzed, which I guess should be the indices. Still, it is quite a disappointment...

Ties Blom
Information analyst

 

[ddiamond]

blom0344,

What is a Star Schema join?

 

[blom0344]

Basically the type of structure used in datawarehousing, a big facttable surrounded by dimensiontables.
The dimensiontables are used to take 'slices' from the facttable..

Ties Blom
Information analyst