I am creating a new database. I have asked this question before, but
not received a definite answer.

Question:

In Creating Tables, is it better to have all fixed-length fields first,
followed by variable length? Or, does it matter?

Some have said it matters, some have said, "It's a myth."

SS

"Stanley Sinclair" <stanleysinclair@bellsouth.net> wrote in message
news:1113776053.658218.140820@l41g2000cwc.googlegr oups.com...
>I am creating a new database. I have asked this question before, but
> not received a definite answer.
>
> Question:
>
> In Creating Tables, is it better to have all fixed-length fields first,
> followed by variable length? Or, does it matter?
>
> Some have said it matters, some have said, "It's a myth."
>
> SS

On DB2 for z/OS it matters. On DB2 for LUW, it will put the varchar columns
at the end for you, regardless of the order you specify (I don't know about
if you add a fixed length column), so it doesn't matter.

The main benefit of putting varchar at the end of the row has to do with the
amount of data logged for updates (not inserts or deletes) to the row, so it
may not be a factor in many applications.

Actually for varchar columns, the fixed piece of a varchar column (length, null, etc.) is
inline in the row physically on disk ... it is only the data portion of the varchar
column that is at the end of the physical row in the data page and a ptr from the fixed
piece tells DB2 how to find the data for that column in that row.

--

Bob
Engagement Specialist - DB2 Information Management Software - IBM Software Group
IBM Toronto Lab
[My comments are solely my own and are not meant to represent an official IBM position -
ask my cat!]


"Mark A" <nobody@nowhere.com> wrote in message news:RJadnW-aAcyZdv_fRVn-2A@comcast.com...
> "Stanley Sinclair" <stanleysinclair@bellsouth.net> wrote in message
> news:1113776053.658218.140820@l41g2000cwc.googlegr oups.com...
> >I am creating a new database. I have asked this question before, but
> > not received a definite answer.
> >
> > Question:
> >
> > In Creating Tables, is it better to have all fixed-length fields first,
> > followed by variable length? Or, does it matter?
> >
> > Some have said it matters, some have said, "It's a myth."
> >
> > SS
>
> On DB2 for z/OS it matters. On DB2 for LUW, it will put the varchar columns
> at the end for you, regardless of the order you specify (I don't know about
> if you add a fixed length column), so it doesn't matter.
>
> The main benefit of putting varchar at the end of the row has to do with the
> amount of data logged for updates (not inserts or deletes) to the row, so it
> may not be a factor in many applications.
>
>