SQL Optimizer - Picks different Logicals/Index when same job run twice in a row

[Gxbuxton]

I created an index in the exact order(18 Fields) called AD214X12.
The first time I submit the job it picks a different logical AD214L09 and performs miserably
Message . . . . : All access paths were considered for file AD214M01.
Cause . . . . . : The query optimizer considered all access paths built over
member AD214M01 of file AD214M01 in library EPLIB. The list below shows the
access paths considered. If file AD214M01 in library EPLIB is a logical file
then the access paths specified are actually built over member AD214M01 of
physical file AD214M01 in library EPLIB. Following each access path name in
the list is a reason code which explains how the optimizer considered the
access path. EPLIB/AD214L07 4, EPLIB/AD214L01 4, EPLIB/AD214L12 11,
EPLIB/AD214X12 4, EPLIB/AD214L08 4, EPLIB/AD214L09 0. The reason codes
and their meanings follow: 0 - The access path was used to implement the
query. 1 - Access path was not in a valid state. The system invalidated the
access path. 2 - Access path was not in a valid state. The user requested
that the access path be rebuilt. 3 - Access path is a temporary access path
(resides in library QTEMP) and was not specified as the file to be queried.
4 - The cost to use this access path, as determined by the optimizer, was
higher than the cost associated with the chosen access method. 5 - The keys


The second time I submit and third it pick my index AD214X12
Message . . . . : All access paths were considered for file AD214M01.
Cause . . . . . : The query optimizer considered all access paths built ove
member AD214M01 of file AD214M01 in library EPLIB. The list below shows the
access paths considered. If file AD214M01 in library EPLIB is a logical fil
then the access paths specified are actually built over member AD214M01 of
physical file AD214M01 in library EPLIB. Following each access path name in
the list is a reason code which explains how the optimizer considered the
access path. EPLIB/AD214L09 4, EPLIB/AD214L07 4, EPLIB/AD214L01 4,
EPLIB/AD214L12 11, EPLIB/AD214L08 4, EPLIB/AD214X12 0. The reason codes
and their meanings follow: 0 - The access path was used to implement the
query. 1 - Access path was not in a valid state. The system invalidated the
access path. 2 - Access path was not in a valid state. The user requested
that the access path be rebuilt. 3 - Access path is a temporary access path
(resides in library QTEMP) and was not specified as the file to be queried.
4 - The cost to use this access path, as determined by the optimizer, was
higher than the cost associated with the chosen access method. 5 - The keys

Any help would be appreciated. This file had 106 million records. When it picks my index it runs in a few minutes but when it picks the other logical it would take days as it's only keyed on two of the fields.

[Rabbit]

It would be helpful to see the actual query you're trying to run. We would just be guessing otherwise.

For example, one guess on my part could be that you're using a calculation somewhere that precludes the use of the index on the first run. But once that first run is completed and the results have been cached by the server, then it is able to combine the cache with the other index so it doesn't have to recalculate the formula.

But that's just one possible explanation and may not even apply to your situation.