Re: AW: Ideas for logicals (index).

[CRPence <CRPbottle@xxxxxxxxx>]

  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.