OS390 - char and varchar

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Pete H - 04 Mar 2004 19:36 GMT

Hi All;
On DB2 (OS390) v7.1 is there any reason to avoid the use of varchar()
datatype for columns less than 50 bytes in length? Current DBA
standard is to only use varchar when length of character field exceeds
50 and I'm trying to determine if this makes sense.

I have a star schema with MANY character columns in the dimensions
that are 25-40 bytes in length and it seems to me that I'd potentially
save a great deal of DASD by defining them as varchar(nn)...

Thanks for any advice or input.

Pete H

Reply to this Message

Mark A - 04 Mar 2004 23:58 GMT

> Hi All;
> On DB2 (OS390) v7.1 is there any reason to avoid the use of varchar()
[quoted text clipped - 9 lines]
>
> Pete H

If that column never gets updated (with a larger value) go ahead and make it
varchar. If the column is updated, it might not fit back on the same page,
and that can cause lots of performance problems if that happens frequently
on lots of rows.

Varchar has storage overhead of 2 bytes (half word binary) to indicate the
length of the data.

Reply to this Message

Anton Versteeg - 05 Mar 2004 08:57 GMT

Also note that on OS/390 you can compress data (albeit at the cost of
some CPU overhead).
So a char with a lot of blanks (or other repeating characters) might
take the same or perhaps even less space than a varchar.

>
>
[quoted text clipped - 22 lines]
>
>

Signature

Anton Versteeg
IBM Certified DB2 Specialist
IBM Netherlands

Reply to this Message