Re: Variable length fields question

I can see that POV...

But I was thinking that if I had a VARCHAR(30) column and every record had
just a few bytes of data, from the documentation I'd expect a space savings
over char(30) that wouldn't be there.

OTOH, given the overhead of the aux SID (25 bytes was mentioned) I can't
really expect any savings for a VARCHAR(30).

I think a side note of some sort may be helpful, if for no other reason
than to help users decided between CHAR and VARCHAR.

Interestingly, the IBM DB2 for i Porting guide for MS SQL Server to IBM i
(v7 July 2010) has the following:
"Many SQL Server applications use the VARCHAR2 data type for very small
character strings. In these circumstances, it is better to port it to the
fixed-length DB2 data type CHAR(n), because it is more efficient and takes
less storage than the VARCHAR data type."

"If it is not possible to change the column definition from a
variable-length column to a fixed-length column, the ALLOCATE keyword can
be used to improve performance. ALLOCATE(0) is the default and causes
column value to be stored in the auxiliary overflow part of the row; this
is the best value if the goal is to save space. The ALLOCATE(N) keyword
allocates n bytes in the fixed portion of the row and only stores the
column in the overflow area when the column value exceeds N. "

As I read Birgitta's quote and my own recollection, I understand it as even
if I have VARCHAR(30) ALLOCATE(15), internally the DB will do ALLOCATE(30).

Personally, I think that behavior is smart. But I'm not sure it makes
sense to hide this as an implementation detail.

Charles









On Wed, Jul 30, 2014 at 11:17 AM, CRPence <CRPbottle@xxxxxxxxx> wrote:

On 30-Jul-2014 09:51 -0500, Charles Wilt wrote:

On Wed, Jul 30, 2014 at 9:15 AM, Birgitta Hauser wrote:

I found it in a presentation "DB2 UDB for iSeries: Coding

Techniques for SQL Performance" from Kent Milligan

"Internally, DB2 UDB for iSeries will automatically set the
Allocate value to the maximum length of the variable length
column whenever the column length is 30 bytes or less."

Seems the documentation should say that instead of explicitly saying
that it defaults to allocate(0).

No. That documentation is correct. Including that quote [alone or
additionally] would be documenting an /implementation detail/ that is
subject to change. The default for an unspecified ALLOCATE [as a value of
either VARLEN() for DDS or ALLOCATE() for DDL] *is* zero; that is
correct[ly documented]. How the database _implements_ any particular
allocation specification may be of interest to a reader [of the docs], but
implementation-specific details should be documented separately.

That could be a side-note either referencing additional documents about
database internals, or suggesting that, based merely on what [at the time
of writing] was a current /implementation detail/, results in any
varying-length data type column with a length specification of thirty or
fewer bytes to be maintained entirely within the dataspace [i.e. no
overflow to the aux SID] irrespective the specified allocation.

--
Regards, Chuck

--
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.