Z/OS DB2 V7: Column Definitions in DB2 table

Vijayababu Sriramulu

Z/OS DB2 V7: Column Definitions in DB2 table
Dear list users,

Does the seqeunce of the columns matter, if there are no VARCHAR columns in my table. I have 10 columns to be defined in a table. Can i define these columns in any order or does it have a significance to define then in a specific order.

As i said, there are NO VARCHAR's in my table.

Thanks in advance for your comments/feedback.

Regards
Vijay

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Mallik

Re: Z/OS DB2 V7: Column Definitions in DB2 table
(in response to Vijayababu Sriramulu)
It may matter depending on the update activity on the table. You may want to keep the fields in the order of less frequent updated to more frequently updated fields.




"Sriramulu, Vijayababu" <[login to unmask email]> wrote:
Dear list users,

Does the seqeunce of the columns matter, if there are no VARCHAR columns in my table. I have 10 columns to be defined in a table. Can i define these columns in any order or does it have a significance to define then in a specific order.

As i said, there are NO VARCHAR's in my table.

Thanks in advance for your comments/feedback.

Regards
Vijay
--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm



---------------------------------
Yahoo! Photos – Showcase holiday pictures in hardcover
Photo Books. You design it and we’ll bind it!

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Vijayababu Sriramulu

Re: Z/OS DB2 V7: Column Definitions in DB2 table
(in response to Vijayababu Sriramulu)
Thanks for your response Mallik !!

Yes. This is a heavily updated table which will support multi threaded capabilities. We are planning for a LOCKSIZE of ROW.

I am just wondering what way it will help me to have the most updated columns in the end. I am still a novice to understand the logic behind this.

Please throw some more light on this.

Thanks & Regards
Vijay


________________________________

From: DB2 Data Base Discussion List on behalf of Mallik Thippana
Sent: Sat 1/14/2006 10:27 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Z/OS DB2 V7: Column Definitions in DB2 table


It may matter depending on the update activity on the table. You may want to keep the fields in the order of less frequent updated to more frequently updated fields.




"Sriramulu, Vijayababu" <[login to unmask email]> wrote:

Dear list users,

Does the seqeunce of the columns matter, if there are no VARCHAR columns in my table. I have 10 columns to be defined in a table. Can i define these columns in any order or does it have a significance to define then in a specific order.

As i said, there are NO VARCHAR's in my table.

Thanks in advance for your comments/feedback.

Regards
Vijay
--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm


________________________________

Yahoo! Photos - Showcase holiday pictures in hardcover
Photo Books <http://us.rd.yahoo.com/mail_us/taglines/photobooks/*http://pa.yahoo.com/*http://us.rd.yahoo.com/mail_us/taglines/photos/evt=38088/* http://pg.photos.yahoo.com/ph//page?.file=photobook_splash.html > . You design it and we'll bind it! --------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm


This e-mail transmission contains information that is confidential and may be privileged. It is intended only for the addressee(s) named above. If you receive this e-mail in error, please do not read, copy or disseminate it in any manner. If you are not the intended recipient, any disclosure, copying, distribution or use of the contents of this information is prohibited. Please reply to the message immediately by informing the sender that the message was misdirected. After replying, please erase it from your computer system. Your assistance in correcting this error is appreciated.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Vijayababu Sriramulu

Re: Z/OS DB2 V7: Column Definitions in DB2 table
(in response to Mallik)
Thanks for your response Mallik !!

Yes. This is a heavily updated table which will support multi threaded capabilities. We are planning for a LOCKSIZE of ROW.

I am just wondering what way it will help me to have the most updated columns in the end. I am still a novice to understand the logic behind this.

Please throw some more light on this.

Thanks & Regards
Vijay


________________________________

From: DB2 Data Base Discussion List on behalf of Mallik Thippana
Sent: Sat 1/14/2006 10:27 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Z/OS DB2 V7: Column Definitions in DB2 table


It may matter depending on the update activity on the table. You may want to keep the fields in the order of less frequent updated to more frequently updated fields.




"Sriramulu, Vijayababu" <[login to unmask email]> wrote:

Dear list users,

Does the seqeunce of the columns matter, if there are no VARCHAR columns in my table. I have 10 columns to be defined in a table. Can i define these columns in any order or does it have a significance to define then in a specific order.

As i said, there are NO VARCHAR's in my table.

Thanks in advance for your comments/feedback.

Regards
Vijay
--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm


________________________________

Yahoo! Photos - Showcase holiday pictures in hardcover
Photo Books <http://us.rd.yahoo.com/mail_us/taglines/photobooks/*http://pa.yahoo.com/*http://us.rd.yahoo.com/mail_us/taglines/photos/evt=38088/* http://pg.photos.yahoo.com/ph//page?.file=photobook_splash.html > . You design it and we'll bind it! --------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Mike Bell

Re: Z/OS DB2 V7: Column Definitions in DB2 table
(in response to Vijayababu Sriramulu)
In the DB2 admin guide - subject logging - APPENDIX1.3.1.1.4 Classes of
changes to data
Update data | The old and new values of the changed data. |
| |
| ° On redo, the new data is replaced. |
| ° On undo, the old data is replaced.
What the manual doesn't say but is covered in the DBA class is the rules of
how DB2 collects the undo and redo data.
If there are no varchar columns, DB2 logs from first changed byte to the
last changed byte
If there are varchar columns, DB2 logs from first changed byte to the end of
the row.
So it is advantageous to DB2 logging performance to place columns together
that are updated at similar logic points. And if there are varchar columns
put them and the updated columns at the end of the row.

Most DB2 systems are not constrained by logging performance and after a few
rounds of alter add columns, etc your column placement is less than optimal
but most DBA's will attempt to do the analysis for the high update volume
tables.

INSERT and DELETE of course have to log the entire row.

Mike
HLS Technologies

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Sriramulu, Vijayababu
Sent: Sunday, January 15, 2006 5:07 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Z/OS DB2 V7: Column Definitions in DB2 table

Thanks for your response Mallik !!

Yes. This is a heavily updated table which will support multi threaded
capabilities. We are planning for a LOCKSIZE of ROW.

I am just wondering what way it will help me to have the most updated
columns in the end. I am still a novice to understand the logic behind this.


Please throw some more light on this.

Thanks & Regards
Vijay


________________________________

From: DB2 Data Base Discussion List on behalf of Mallik Thippana
Sent: Sat 1/14/2006 10:27 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Z/OS DB2 V7: Column Definitions in DB2 table


It may matter depending on the update activity on the table. You may want to
keep the fields in the order of less frequent updated to more frequently
updated fields.




"Sriramulu, Vijayababu" <[login to unmask email]> wrote:

Dear list users,

Does the seqeunce of the columns matter, if there are no VARCHAR
columns in my table. I have 10 columns to be defined in a table. Can i
define these columns in any order or does it have a significance to define
then in a specific order.

As i said, there are NO VARCHAR's in my table.

Thanks in advance for your comments/feedback.

Regards
Vijay

----------------------------------------------------------------------------
----- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm


________________________________

Yahoo! Photos – Showcase holiday pictures in hardcover
Photo Books
<http://us.rd.yahoo.com/mail_us/taglines/photobooks/*http://pa.yahoo.com/*ht
tp://us.rd.yahoo.com/mail_us/taglines/photos/evt=38088/*http://pg.photos.yah
oo.com/ph//page?.file=photobook_splash.html> . You design it and we’ll bind
it!
----------------------------------------------------------------------------
----- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm
----------------------------------------------------------------------------
----- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm


---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.510 / Virus Database: 307 - Release Date: 8/14/2003



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.510 / Virus Database: 307 - Release Date: 8/14/2003


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Vijayababu Sriramulu

Re: Z/OS DB2 V7: Column Definitions in DB2 table
(in response to Mike Bell)
That explains me the logic behind this.

Thanks a Ton Mike for your nice explanation !!!

Regards
Vijay


________________________________

From: DB2 Data Base Discussion List on behalf of Mike Bell
Sent: Sun 1/15/2006 11:31 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Z/OS DB2 V7: Column Definitions in DB2 table



In the DB2 admin guide - subject logging - APPENDIX1.3.1.1.4 Classes of
changes to data
Update data | The old and new values of the changed data. |
| |
| ° On redo, the new data is replaced. |
| ° On undo, the old data is replaced.
What the manual doesn't say but is covered in the DBA class is the rules of
how DB2 collects the undo and redo data.
If there are no varchar columns, DB2 logs from first changed byte to the
last changed byte
If there are varchar columns, DB2 logs from first changed byte to the end of
the row.
So it is advantageous to DB2 logging performance to place columns together
that are updated at similar logic points. And if there are varchar columns
put them and the updated columns at the end of the row.

Most DB2 systems are not constrained by logging performance and after a few
rounds of alter add columns, etc your column placement is less than optimal
but most DBA's will attempt to do the analysis for the high update volume
tables.

INSERT and DELETE of course have to log the entire row.

Mike
HLS Technologies

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Sriramulu, Vijayababu
Sent: Sunday, January 15, 2006 5:07 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Z/OS DB2 V7: Column Definitions in DB2 table

Thanks for your response Mallik !!

Yes. This is a heavily updated table which will support multi threaded
capabilities. We are planning for a LOCKSIZE of ROW.

I am just wondering what way it will help me to have the most updated
columns in the end. I am still a novice to understand the logic behind this.


Please throw some more light on this.

Thanks & Regards
Vijay


________________________________

From: DB2 Data Base Discussion List on behalf of Mallik Thippana
Sent: Sat 1/14/2006 10:27 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Z/OS DB2 V7: Column Definitions in DB2 table


It may matter depending on the update activity on the table. You may want to
keep the fields in the order of less frequent updated to more frequently
updated fields.




"Sriramulu, Vijayababu" <[login to unmask email]> wrote:

Dear list users,

Does the seqeunce of the columns matter, if there are no VARCHAR
columns in my table. I have 10 columns to be defined in a table. Can i
define these columns in any order or does it have a significance to define
then in a specific order.

As i said, there are NO VARCHAR's in my table.

Thanks in advance for your comments/feedback.

Regards
Vijay

----------------------------------------------------------------------------
----- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm


________________________________

Yahoo! Photos - Showcase holiday pictures in hardcover
Photo Books
<http://us.rd.yahoo.com/mail_us/taglines/photobooks/*http://pa.yahoo.com/*ht
tp://us.rd.yahoo.com/mail_us/taglines/photos/evt=38088/*http://pg.photos.yah
oo.com/ph//page?.file=photobook_splash.html> . You design it and we'll bind
it!
----------------------------------------------------------------------------
----- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm
----------------------------------------------------------------------------
----- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm


---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.510 / Virus Database: 307 - Release Date: 8/14/2003



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.510 / Virus Database: 307 - Release Date: 8/14/2003


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm



---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Phil Grainger

Re: Z/OS DB2 V7: Column Definitions in DB2 table
(in response to Vijayababu Sriramulu)
Currently the recommendations for column placement in a row, go as
follows:

1. Put all the variable columns at the END of the row. This is because,
DB2 can quite easily find the nth column of a set of fixed columns, just
by adding their lengths together. However, once variable columns are
involved, DB2 has to look at each columns length field to see how far to
"skip"
2. Put all the updated columns TOGETHER as DB2 logs from first changed
byte to last changed byte

Is (1) more or less important than (2)?

"It depends"

For a predominantly read-only table, (1) is the priority

For a predominantly updated table, (2) is the priority

And remember, these rules can (and do) change between releases of DB2


Phil Grainger
CA
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125 752
[login to unmask email]



________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Sriramulu, Vijayababu
Sent: 15 January 2006 11:07
To: [login to unmask email]
Subject: Re: [DB2-L] Z/OS DB2 V7: Column Definitions in DB2 table


Thanks for your response Mallik !!

Yes. This is a heavily updated table which will support multi threaded
capabilities. We are planning for a LOCKSIZE of ROW.

I am just wondering what way it will help me to have the most updated
columns in the end. I am still a novice to understand the logic behind
this.

Please throw some more light on this.

Thanks & Regards
Vijay


________________________________

From: DB2 Data Base Discussion List on behalf of Mallik Thippana
Sent: Sat 1/14/2006 10:27 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Z/OS DB2 V7: Column Definitions in DB2 table


It may matter depending on the update activity on the table. You may
want to keep the fields in the order of less frequent updated to more
frequently updated fields.




"Sriramulu, Vijayababu" <[login to unmask email]> wrote:

Dear list users,

Does the seqeunce of the columns matter, if there are no VARCHAR
columns in my table. I have 10 columns to be defined in a table. Can i
define these columns in any order or does it have a significance to
define then in a specific order.

As i said, there are NO VARCHAR's in my table.

Thanks in advance for your comments/feedback.

Regards
Vijay

------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm


________________________________

Yahoo! Photos - Showcase holiday pictures in hardcover
Photo Books
<http://us.rd.yahoo.com/mail_us/taglines/photobooks/*http://pa.yahoo.com
/*http://us.rd.yahoo.com/mail_us/taglines/photos/evt=38088/*http://pg.ph
otos.yahoo.com/ph//page?.file=photobook_splash.html> . You design it and
we'll bind it!
------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm
------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

This e-mail transmission contains information that is confidential and
may be privileged. It is intended only for the addressee(s) named above.
If you receive this e-mail in error, please do not read, copy or
disseminate it in any manner. If you are not the intended recipient, any
disclosure, copying, distribution or use of the contents of this
information is prohibited. Please reply to the message immediately by
informing the sender that the message was misdirected. After replying,
please erase it from your computer system. Your assistance in correcting
this error is
appreciated.------------------------------------------------------------
--------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go
to the archives and home page at
http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

cass cheng

Re: Z/OS DB2 V7: Column Definitions in DB2 table
(in response to Phil Grainger)
Hi Phil,

How about the case 2 when the table is having all fixed length but the
tablespace is compressed one. In that case the row length is varibale
one. So in that case the updates would be logged from the start of the
changed bytes to end of changed bytes or entire row length would be
updated.

I feel with V7 the entire row length woulod be updated.

Thanking you in advance for the updates.

Cass

----- Original Message -----
From: "Grainger, Phil"
To: [login to unmask email]
Subject: Re: [DB2-L] Z/OS DB2 V7: Column Definitions in DB2 table
Date: Thu, 19 Jan 2006 10:57:21 -0000

Currently the recommendations for column placement in a row, go as
follows: 1. Put all the variable columns at the END of the row. This is
because, DB2 can quite easily find the nth column of a set of fixed
columns, just by adding their lengths together. However, once variable
columns are involved, DB2 has to look at each columns length field to see
how far to "skip"2. Put all the updated columns TOGETHER as DB2 logs from
first changed byte to last changed byte Is (1) more or less important
than (2)? "It depends" For a predominantly read-only table, (1) is the
priority For a predominantly updated table, (2) is the priority And
remember, these rules can (and do) change between releases of DB2

Phil Grainger
CA
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125 752
[login to unmask email]

--
___________________________________________________
Play 100s of games for FREE! http://games.mail.com/


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

cass cheng

Re: Z/OS DB2 V7: Column Definitions in DB2 table
(in response to Phil Grainger)
Hi Phil,

How about the case 2 when the table is having all fixed length but the
tablespace is compressed one. In that case the row length is varibale
one. So in that case the updates would be logged from the start of the
changed bytes to end of changed bytes or entire row length would be
updated.

I feel with V7 the entire row length woulod be updated.

Thanking you in advance for the updates.

Cass

----- Original Message -----
From: "Grainger, Phil"
To: [login to unmask email]
Subject: Re: [DB2-L] Z/OS DB2 V7: Column Definitions in DB2 table
Date: Thu, 19 Jan 2006 10:57:21 -0000

Currently the recommendations for column placement in a row, go as
follows: 1. Put all the variable columns at the END of the row. This is
because, DB2 can quite easily find the nth column of a set of fixed
columns, just by adding their lengths together. However, once variable
columns are involved, DB2 has to look at each columns length field to see
how far to "skip"2. Put all the updated columns TOGETHER as DB2 logs from
first changed byte to last changed byte Is (1) more or less important
than (2)? "It depends" For a predominantly read-only table, (1) is the
priority For a predominantly updated table, (2) is the priority And
remember, these rules can (and do) change between releases of DB2

Phil Grainger
CA
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125 752
[login to unmask email]

--
___________________________________________________
Play 100s of games for FREE! http://games.mail.com/


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Phil Grainger

Re: Z/OS DB2 V7: Column Definitions in DB2 table
(in response to cass cheng)
Starting with v6 (I think) DB2 took the row length out of the row itself
and logs it separately, so if yuo extend a row at the end (for example)
you'd only log the length field and the bytes that changed

Of course, with compression, a LOT of the row could change, and
therefore would get logged

At this point I tend to agree with Roger Miller who sometimes says
things to the effect that "if you are worrying about logging you are
probably worrying about the wrong things" - in other words, most of the
time, logging is never a problem to be fixed

Phil Grainger
CA
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125 752
[login to unmask email]



________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of cass cheng
Sent: 19 January 2006 14:56
To: [login to unmask email]
Subject: Re: [DB2-L] Z/OS DB2 V7: Column Definitions in DB2 table



Hi Phil,

How about the case 2 when the table is having all fixed length but the
tablespace is compressed one. In that case the row length is varibale
one. So in that case the updates would be logged from the start of the
changed bytes to end of changed bytes or entire row length would be
updated.

I feel with V7 the entire row length woulod be updated.

Thanking you in advance for the updates.

Cass



----- Original Message -----
From: "Grainger, Phil"
To: [login to unmask email]
Subject: Re: [DB2-L] Z/OS DB2 V7: Column Definitions in DB2
table
Date: Thu, 19 Jan 2006 10:57:21 -0000


Currently the recommendations for column placement in a row, go
as follows:

1. Put all the variable columns at the END of the row. This is
because, DB2 can quite easily find the nth column of a set of fixed
columns, just by adding their lengths together. However, once variable
columns are involved, DB2 has to look at each columns length field to
see how far to "skip"
2. Put all the updated columns TOGETHER as DB2 logs from first
changed byte to last changed byte

Is (1) more or less important than (2)?

"It depends"

For a predominantly read-only table, (1) is the priority

For a predominantly updated table, (2) is the priority

And remember, these rules can (and do) change between releases
of DB2


Phil Grainger
CA
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125 752
[login to unmask email]





--

___________________________________________________
Play 100s of games for FREE! http://games.mail.com/

------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Phil Grainger

Re: Z/OS DB2 V7: Column Definitions in DB2 table
(in response to Phil Grainger)

[Grainger, Phil]
Starting with v6 (I think) DB2 took the row length out of the row
itself and logs it separately, so if yuo extend a row at the end (for
example) you'd only log the length field and the bytes that changed

Of course, with compression, a LOT of the row could change, and
therefore would get logged

At this point I tend to agree with Roger Miller who sometimes says
things to the effect that "if you are worrying about logging you are
probably worrying about the wrong things" - in other words, most of the
time, logging is never a problem to be fixed

Phil Grainger
CA
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125 752
[login to unmask email]



________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of cass cheng
Sent: 19 January 2006 14:56
To: [login to unmask email]
Subject: Re: [DB2-L] Z/OS DB2 V7: Column Definitions in DB2 table



Hi Phil,

How about the case 2 when the table is having all fixed length but the
tablespace is compressed one. In that case the row length is varibale
one. So in that case the updates would be logged from the start of the
changed bytes to end of changed bytes or entire row length would be
updated.

I feel with V7 the entire row length woulod be updated.

Thanking you in advance for the updates.

Cass



----- Original Message -----
From: "Grainger, Phil"
To: [login to unmask email]
Subject: Re: [DB2-L] Z/OS DB2 V7: Column Definitions in DB2
table
Date: Thu, 19 Jan 2006 10:57:21 -0000


Currently the recommendations for column placement in a row, go
as follows:

1. Put all the variable columns at the END of the row. This is
because, DB2 can quite easily find the nth column of a set of fixed
columns, just by adding their lengths together. However, once variable
columns are involved, DB2 has to look at each columns length field to
see how far to "skip"
2. Put all the updated columns TOGETHER as DB2 logs from first
changed byte to last changed byte

Is (1) more or less important than (2)?

"It depends"

For a predominantly read-only table, (1) is the priority

For a predominantly updated table, (2) is the priority

And remember, these rules can (and do) change between releases
of DB2


Phil Grainger
CA
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125 752
[login to unmask email]





--

___________________________________________________
Play 100s of games for FREE! http://games.mail.com/

------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm
------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm
------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm