as400 - how to determine which columns a temp index used 1

[ddiamond]

This is a continuation of thread thread178-1136501 which was closed about 2 years ago. I'm trying to figure out what index I need to create in order to prevent the system from creating a temporary index. I was told that if I turned on debugging, and then checked my job log, it would tell me which fields are being indexed. So I turned on debugging strdbg, ran my query, and checked the job log. This is what it contained:

**** Starting optimizer debug message for query .
All access paths were considered for file ZUMADF00.
Additional access path reason codes were used.
All access paths were considered for file POLICYINFO.
Access path built for file POLICYINFO.
File ZUMADF00 processed in join position 1.
File POLICYINFO processed in join position 2.
Query options retrieved file QAQQINI in library QUSRSYS.
**** Ending debug message for query .

Still doesn't tell me which fields are in the temp index.
It tried using i-series navigator visual explain as well. When I click on the icon for the temp index, it gives me the following information:
Key Columns of Index Created: *Map ascend, *Map ascend.
What does *map mean? This join really should be a no-brainer. Both tables have identical primary keys, and I'm joining on the primary key. Yet the optimizer would rather create a temp index than use the primary key.

 

[fredericofonseca]

did you press F1 in each one of the messages? It might give you more info.

run query online, not on batch mode, once finished press F10, go to the messages in question, and press F1 in each one.

It is possible to do more, but I would need to have a bit more information about the files being used on the query and the FULL query definition. Also OS version.


Note that a GROUP BY or a ORDER BY might create a temporary index no matter what you join the tables on.




Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[ddiamond]

Thanks Frederico. F1 did the trick. The temp index was built over *MAP ASC, *MAP ASC. The debug message further explained that *MAP refers to a derived expression instead of a single field. And unfortunately, when *MAP is used, a perminant index cannot be created to replace the temp index. I still can't understand why it want to create an index over a calculated expression. When I get a chance, I will post more details about my query. It has no group by's, order by's, or expressions.

BTW I am on V5R3. In a couple days we will upgrade to V5R4, so hopefully that will help.

 

[ddiamond]

I finally figured it out. Although both tables had the same primary keys with the same data types - char(6), char(3), one table had a ccsid of 37, while the other had a ccsid 285. This meant a data type conversion still had to be done. Once I altered the tables to both have the same ccsid, the optimizer was able to use the primary key indexes instead of temp indexes to preform the joins.