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.