table storage internals

[dbmsguy]

I remember either reading or hearing from a ways back that DB2 (UDB 7.2 on AIX specifically) automatically stores (internally) varchars and nullable columns at the end of the table.

This goes back to the old argument that varchars 'should' be put at the end of a table.

At any rate, I am trying to validate my recolection and am wondering if there is anyone out there with a good knowledge of internals who knows the answer. And better yet, where it might be documented.

Many thanks for any comments and opionions. Paul

http://www.dbmsguy.com

 

[gregsimpson]

The following link seems to back up your theory that the physical placement of VARCHARS is the end of the row.

http://www7b.boulder.ibm.com/dmdd/library/techarticle/adamache/0621_adamache.html#haste

However with columns which are nullable I believe the following to be the case. They are stored as you would expect(in the creation order). If you define a column as being nullable, it actually takes up 1 byte extra for an indicator on the database to say whether or not the field is set to NULL. This being the case there doesn't seem to be any reason to physically alter the placement of fields which may be NULL

 

[dbmsguy]

Thank you for the interesting link....

I might tend to disagree with you on the second point.

If, you do have a nullable column, and it is indeed null, then to the best of my knowledge, it does not actually store the placeholder. In other words, if you have a char(10) nullable and put in a null value, internally, it would only set the null indicator to null and not store the value. this being the case (and I can't prove it), then all values following it would have to be calculated for position based on null indicators. if it was at the end, the overhead of this would be less.

this of course would presume that i am actually correct in my assumption. I will continue to investigate to prove myself wrong.

anyone else have a thought? Paul

http://www.dbmsguy.com

 

[gregsimpson]

Paul,

the article below seems to back me up. It suggests that defining a 5 byte field as Nullable will mean the field takes up 6 bytes

http://www.db2mag.com/db_area/archives/2000/q1/programmer.shtml

 

[dbmsguy]

very good. thank you again Greg. Paul

http://www.dbmsguy.com