Re: VARCHAR w/Allocate vs. CHAR

There is a trade-off on the iSeries implementation of VARCHARs - I don't know 
the internals for other RDBMS, but I do know some of the gory details on the 
iSeries. In particular, no other system uses the ALLOCATE that I know of. The 
trade-off is space vs. speed. The default ALLOCATE when using a CREATE TABLE is 
0, so the space is optimized. But every IO is at least doubled for the 
VARCHARs, because the actual data is kept in a completely different data space 
(each record has a pointer to from the physical file primary data space.

If the ALLOCATE is set and the data is contained within the allocated length, 
there is only 1 IO, otherwise the data is all in the auxiliary space and there 
is extra IOs. The IBM recommendation is, IIRC, set ALLOCATE to include 80-90% 
of the rows.

Also, with VARCHARs (and xLOBs and NULL status) there is extra space taken at 
the front of each record - without the above there is only the so-called DENT 
byte - the "deleted entry". These others add an AXENT or "auxiliary entry" that 
contains information for the auxiliary space and the NULL indications.

HTH
Vern

-------------- Original message -------------- 
From: "Ryan Hunt" <ryan.hunt@xxxxxxxxxxxxx> 

> Because I am, an MS SQL DBA that's had several DB2/400 questions on this 
> list, it was suggested to me that I read the red book "DB2 UDB for iSeries 
> Porting Guide: SQL Server to IBM eServer iSeries". This has turned out to 
> be a great read for me. 
> 
> On page 11 there is a section on character types that discusses the use of 
> CHAR over VARCHAR(50) or less. I have a schema that does not follow this 
> because I built it before reading this doc. Now, I'm curious about the 
> alter table statement and the ALLOCATE command. Am I better off altering 
> the schema (40 tables) to use CHAR instead and go through the process of 
> porting information over, or can I get similar performance by changing the 
> allocation attribute. 
> 
> I understand how ALLOCATE adjust my record/page level storage. However, is 
> there some other significant cost to using VARCHAR (i.e. CPU). 
> 
> Right now I am thinking I should alter my exsting tables using ALLOCATE to 
> keep things simple. 
> 
> Thoughts? 
> 
> 
> 
> -- 
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list 
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx 
> To subscribe, unsubscribe, or change list options, 
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l 
> or email: MIDRANGE-L-request@xxxxxxxxxxxx 
> Before posting, please take a moment to review the archives 
> at http://archive.midrange.com/midrange-l. 
>