Why does DB2 on AS400 occasionally create a temporary index on the fly

[ddiamond]

On some of my queries, the AS400 chooses to build a temporary index on the fly instead of using existing indexes. Why does it do this? Building a new index is very time consuming and the fields in the inner join already have an index.

 

[MdnghtPgmr]

ddiamond,

It does so based on your selection criteria and sorts. If you look at the job log you will find messages that tell you what logicals (indexes) you can build to prevent Query Manger from doing this on the fly. Of course you must decide if having a perminate logical (index) is worth it or if letting Query Manger build it on the fly is the better way to go.

HTH,
MdnghtPgmr

 

[ddiamond]

Thanks for the tip. I'm not sorting the data, but I do have a group by clause. Could a group by cause it to create a temporary logical?

 

[MdnghtPgmr]

ddiamond,

Yes. A group by implies a sort.

HTH,
MdnghtPgmr

 

[blom0344]

ddiamond,

I am struggling with the same problems on one of our AS400 machines.
From information gathered version V5R2 is not really optimized for complicated datawarehouse type of queries. In many cases the optimizer refuses to use an index that is already created. Compared to DB2 UDB the AS400 optimizer is a letdown. Query-time has increased quit a bit when we migrated from DB2 UDB to AS400
I have a good article on the subject , but it is in Dutch.
The bottomline is , that V5R3 has many improvements for complex queries that should allow the optimizer to create a more effective way to deal with them...


Ties Blom
Information analyst

 

[ddiamond]

Thanks blom0344. How recently was V5R3 released? How difficult is it to upgrade from V5R2 to V5R3?

 

[blom0344]

It should be a fairly basic upgrade.
Backup the system, perform the upgrade and all PTF's.
V5R3 was released in june 2004.

Some of the enhancements:

1. supports Star-Schema Join model
2. define constraints beforehand, so optimizer will not look beyond certain boundaries
3. First attempt to deliver Material Query Table (syntax only)
4.Caching resultsets that are often used
5. Support for modeliing-tool 'Rational'
6. Unicode enhancements
7. Introducing sequence objects as superset of identity column
8. New operators Except en Intersect.
9. Larger tables possible
10. New graphic interfaces / new index analyzer

Ties Blom
Information analyst

 

[ddiamond]

2. define constraints beforehand, so optimizer will not look beyond certain boundaries

Is that anything like oracle hints?

 

[blom0344]

I don't think so, what is meant here is ,that if a column can only take values between 1 and 100 , then you can add a sort of constraint to that column , so the optimizer knows it will not have to take care of values outside the 1-100 range...

Ties Blom
Information analyst

 

[ddiamond]

MdnghtPgmr,

If you look at the job log you will find messages that tell you what logicals (indexes) you can build to prevent Query Manger from doing this on the fly.

I entered wrkactjob, found my job, entered 5 (display), entered 10 (display job log). Got the following results:

Job 519751/QUSER/QZDASOINIT started on 02/03/06 at 10:23:02 in subsystem
  QUSRWRK in QSYS. Job entered system on 02/03/06 at 10:23:02.
User PDDIAMOND from client 192.168.183.52 connected to server.
MSYSCONF in XGGNDTA type *FILE not found.

I don't see anything about the temporary index it built on the file zunidf00. Although it mentioned file not found, the query seems to produce the correct results. So where should I be looking to determine what index it tried to build?

Another strange thing. When I list active files, it list zunidl11 as one of the active files. That logical file contains the pre-defined index that I want it to use. So it almost looks as though it took the time to create a temporary index, and then used a pre-defined index instead. Does any of this make any sense?
- Dan

 

[ddiamond]

Another strange thing. When I list active files, it list zunidl11 as one of the active files. That logical file contains the pre-defined index that I want it to use. So it almost looks as though it took the time to create a temporary index, and then used a pre-defined index instead. Does any of this make any sense?

Ignore that final paragraph from my post. I just realized I am joining to zunidf00 twice with different aliases and different join criteria. On one of the joins, it is successfully finding an index (zunidl11) and on the other one it is not, so it is building a temporary index. So I'm back to the original question of how to determine what temporary index it is building.

 

[Mercury2]

Turn debug on in the same job (strdbg) and you'll be able to see the indexes that sql is using and/or building on the fly.