Re: AW: Ideas for logicals (index).

The default page size had always been based on the length of the key. That is, the 8K [perhaps 4K many releases ago] was and is not a default generally, only for /short/ keys, whereby the default page size grows given /longer/ keys. I do not recall the algorithm to decide based on key length, nor what page sizes [of those noted in the v6r1 doc link] are used on releases where the PAGESIZE keyword is not available; a disassembled listing of QDBCRTME may show the support values in plain-text [searching the spool file on PAGESIZE]. The 64K was and remains the default pagesize for an SQL INDEX, and aside from any other exclusion rules, the SQL can use a DDS created LF with a pagesize of 64K [explicitly or implicitly] with no differences in performance; i.e. both the SQL and DDS objects of the same attributes are effectively the same.

http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/topic/rzajq/rzajqpagesize.htm

Also consider that /perform better/ is a YMMV statement. A memory starved system can easily see worse performance for use of a larger pagesize index for SELECT, and depending on the UPDATE and DELETE activity a larger pagesize can easily be at a minimum slightly worse performing even when not memory constrained, due to randomness of the I/O. A larger pagesize is *not* a panacea; a larger memory footprint results, and that does not equate with better performance except when the data in memory can remain resident. Just as I often warn about changing ACCPTHSIZ() without fully understanding how the index will be used, I would similarly warn that blindly choosing a larger page size is a poor idea which can similarly have unforeseen consequences. The Dan C text, unless it has been improved, had suggested almost blind adherence to /bigger is better/; almost no mention of caveats. The conclusions are made based on testing in very specific environments which may not reflect those of actual customers, so it is very unfortunate IMO how the material had been presented as definitive in ability to achieve /better performance/ when the best answer is always "it depends."

Regards, Chuck

Michael_Schutte wrote:

Instead of creating the SQL index first, can I just
specify 64k into the PAGESIZE keyword?
FYI our default in *KEYLEN not 8k.

Birgitta Hauser wrote:

... yet another comment about indexes and keyed logical files: An SQL index has per default a page size of 64K, while a DDS
described logical file only has per default a page size of 8K. Because of the larger page size an SQL index cannot share
access path with a DDS described logical file.

In this way, if you first create an index and after a logical
file with the <<SNIP>>

SQL statements that can use SQL indexes will perform better
than if they have to use DDS described logical files with the
same key fields instead.