SQL Access Paths - Try DDL Index

[FWSoftware@xxxxxxx]

Hi Phyllis,

Next time try a DDL index instead of straight DDS. Exactly what is a DDL Index? 
A file Index object type created using SQL, unlike a DDS index that is created 
with DDS source file code that is compiled.
Example:
CREATE INDEX BPCS62F/IWIU03 ON BPCS62F/IWI (WID,WPROD,WWHS)

What are the differences between DDL indexes and DDS logical file indexes? 
DDS indexes use a Page size of 4-8K, DDL indexes use 64K page size. This larger 
page size makes indexes more attractive to the query optimizer and allows the 
database paging to be more efficient on a query. SQL fetches utilizing DDL 
indexes can move 8x times the data back and forth into memory. 
So, when data is retrieved into memory DDL indexes can transfer 64K were as DDS 
indexes can only transfer 4K before having to return to DASD for more data. 

Can a DDS created index share the same access path and page size as a DDL index?
Yes, by creating DDL indexes with the same key fields as a DDS index access 
path sharing is possible. Just a few easy steps are required to accomplish 
this. 
1. Save DDS logical file index into a save file. 
2. Delete the DDS logical file object.
3. Create a DDL index with the same key fields as the DDS logical file index.
4. Restore the DDS logical file index.

Presto, the DDS logical file index can now share in the 64K page size.  

Try it.

Dave Trevino - FrameWork Software Consulting
FWSOFTWARE@xxxxxxx