DB2 Table Update Columns

SUBSCRIBE DB2-L Anil Kale

DB2 Table Update Columns
Hi All !

While working with DB2 table in the MVS world, there used to a best
practice that said "For effective logging, specify most frequently updated
columns at the end of the row (record)".
I was wondering if that recommendation is still valid or has DB2 changed
over the years and that now its a non-issue.
Can someone throw some light on this topic.

Thanks.

Anil

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-videos.html has hundreds of video presentations!
Did you miss out on attending an IDUG conference?
Many of the presentations were recorded and are available on our website!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Stan Hoey

Re: DB2 Table Update Columns
(in response to SUBSCRIBE DB2-L Anil Kale)
My knowledge in this area is a little out of date and may no longer be
correct, but log records used to span from the first column updated to
the last column updated. Best practice was to group frequently updated
columns together. I don't think the position of the columns in the table
is a factor.

This assumes the table contains fixed length columns.

Stan Hoey



-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Anil
Sent: Tuesday, January 12, 2010 6:06 PM
To: [login to unmask email]
Subject: [DB2-L] DB2 Table Update Columns

Hi All !

While working with DB2 table in the MVS world, there used to a best
practice that said "For effective logging, specify most frequently
updated columns at the end of the row (record)".
I was wondering if that recommendation is still valid or has DB2 changed
over the years and that now its a non-issue.
Can someone throw some light on this topic.

Thanks.

Anil

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 *
http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-videos.html has hundreds of video presentations!
Did you miss out on attending an IDUG conference?
Many of the presentations were recorded and are available on our
website!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L
is the home of IDUG's DB2-L

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-videos.html has hundreds of video presentations!
Did you miss out on attending an IDUG conference?
Many of the presentations were recorded and are available on our website!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Ken McDonald

Re: DB2 Table Update Columns
(in response to Stan Hoey)
My (also slightly dated) experience was fixed length tables logged from the first changed byte to the last changed byte, not necessarily full columns. And for variable length rows, DB2 tended to log from the first changed byte to the end of the row... so putting frequently updated columns towards the end of the row was better for logging.

I think DB2 has gotten better about logging only from the first to last changed byte, even on variable length rows that do not change length. But, I have not looked closely (or recently) to confirm this.

z/OS DB2 Version 9 introduces Reordered Row Format (RRF) which impacts the physical order of columns in a row... on an RRF partition, all fixed length columns are now in column number sequence at the beginning of the row followed by offsets to the variable length columns and then the variable length column data. And, the length of the column is no longer the first two bytes of the variable column data, it is calculated from the offsets to the variable data. So, if you've grouped intermixed fixed and variable length columns in logical sequence at the end of a row, they will end up physically split on an RRF part.

Anil, I'm curious if are you having a logging throughput issue or is this an academic question. If you're having a throughput issue, can you consider striping the actives to improve I/O?

Regards,
Ken


-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Stan Hoey
Sent: Tuesday, January 12, 2010 12:49 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 Table Update Columns

My knowledge in this area is a little out of date and may no longer be
correct, but log records used to span from the first column updated to
the last column updated. Best practice was to group frequently updated
columns together. I don't think the position of the columns in the table
is a factor.

This assumes the table contains fixed length columns.

Stan Hoey



-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Anil
Sent: Tuesday, January 12, 2010 6:06 PM
To: [login to unmask email]
Subject: [DB2-L] DB2 Table Update Columns

Hi All !

While working with DB2 table in the MVS world, there used to a best
practice that said "For effective logging, specify most frequently
updated columns at the end of the row (record)".
I was wondering if that recommendation is still valid or has DB2 changed
over the years and that now its a non-issue.
Can someone throw some light on this topic.

Thanks.

Anil

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 *
http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-videos.html has hundreds of video presentations!
Did you miss out on attending an IDUG conference?
Many of the presentations were recorded and are available on our
website!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L
is the home of IDUG's DB2-L

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-videos.html has hundreds of video presentations!
Did you miss out on attending an IDUG conference?
Many of the presentations were recorded and are available on our website!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-videos.html has hundreds of video presentations!
Did you miss out on attending an IDUG conference?
Many of the presentations were recorded and are available on our website!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

George Toolan

Re: DB2 Table Update Columns
(in response to Ken McDonald)
The end of row was for variable length columns (now we have reordered row format). For logging updates, the thing to do was keep the columns that are updated together physically together (beginning/middle/end -- doesn't matter) since logging is from the first changed column to the last.
If you opt to log the entire row, none of this matters. If you update the variable length columns it doesn't make much difference if the variables are at the end and the row length indicator is at the beginning.

Regards,

George

-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Anil
Sent: Tuesday, January 12, 2010 1:06 PM
To: [login to unmask email]
Subject: [DB2-L] DB2 Table Update Columns

Hi All !

While working with DB2 table in the MVS world, there used to a best
practice that said "For effective logging, specify most frequently updated
columns at the end of the row (record)".
I was wondering if that recommendation is still valid or has DB2 changed
over the years and that now its a non-issue.
Can someone throw some light on this topic.

Thanks.

Anil

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-videos.html has hundreds of video presentations!
Did you miss out on attending an IDUG conference?
Many of the presentations were recorded and are available on our website!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-videos.html has hundreds of video presentations!
Did you miss out on attending an IDUG conference?
Many of the presentations were recorded and are available on our website!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Mike Bell

Re: DB2 Table Update Columns
(in response to George Toolan)
Yes, BUT - the order that the DDL specifies is not necessarily the order in
the physical table as of V9. Look for reordered row format in the
administration guide. IBM did not trust people to put the columns in the
'best' order.

The correct rule for logging is - for fixed length columns - start at the
first changed column and end at the last changed column. If there are any
variable length columns in that span, then log to the end of the row.

So reordered row format now forces all the variable length columns to the
end of the row no matter where they are in the DDL.

As far as I can tell, the fixed length columns are still in the DDL sequence
in the new row format but I really haven't done any testing and the manual
doesn't really say.

Mike
HLS Technologies

-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Stan Hoey
Sent: Tuesday, January 12, 2010 12:49 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 Table Update Columns

My knowledge in this area is a little out of date and may no longer be
correct, but log records used to span from the first column updated to the
last column updated. Best practice was to group frequently updated columns
together. I don't think the position of the columns in the table is a
factor.

This assumes the table contains fixed length columns.

Stan Hoey



-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Anil
Sent: Tuesday, January 12, 2010 6:06 PM
To: [login to unmask email]
Subject: [DB2-L] DB2 Table Update Columns

Hi All !

While working with DB2 table in the MVS world, there used to a best practice
that said "For effective logging, specify most frequently updated columns at
the end of the row (record)".
I was wondering if that recommendation is still valid or has DB2 changed
over the years and that now its a non-issue.
Can someone throw some light on this topic.

Thanks.

Anil

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA
* _____________________________________________________________________

http://www.idug.org/db2-videos.html has hundreds of video presentations!
Did you miss out on attending an IDUG conference?
Many of the presentations were recorded and are available on our website!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L
is the home of IDUG's DB2-L

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 *
http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-videos.html has hundreds of video presentations!
Did you miss out on attending an IDUG conference?
Many of the presentations were recorded and are available on our website!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is
the home of IDUG's DB2-L

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-videos.html has hundreds of video presentations!
Did you miss out on attending an IDUG conference?
Many of the presentations were recorded and are available on our website!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

SUBSCRIBE DB2-L Anil Kale

Re: DB2 Table Update Columns
(in response to Mike Bell)
Hi Ken,

The question is not necessarily academic. I am working on a mass insert
startegy using a COBOL program (COBOL program is not negotiable).
We want to estimate the time for a 250 million rows insert operation.
Keeping as aside the number of indexes and number of threads (parallel
insert jobs), I am examing the various parameters that would influence the
insert efficiency.
It is in this context that I am researching the overhead associated with
logging, and the alternatives to optimize the logging overhead.

Thanks for you response.

Anil

On Tue, 12 Jan 2010 14:21:00 -0600, McDonald, Ken
<[login to unmask email]> wrote:

>My (also slightly dated) experience was fixed length tables logged from the
first changed byte to the last changed byte, not necessarily full columns. And
for variable length rows, DB2 tended to log from the first changed byte to the
end of the row... so putting frequently updated columns towards the end of
the row was better for logging.
>
>I think DB2 has gotten better about logging only from the first to last
changed byte, even on variable length rows that do not change length. But, I
have not looked closely (or recently) to confirm this.
>
>z/OS DB2 Version 9 introduces Reordered Row Format (RRF) which
impacts the physical order of columns in a row... on an RRF partition, all
fixed length columns are now in column number sequence at the beginning of
the row followed by offsets to the variable length columns and then the
variable length column data. And, the length of the column is no longer the
first two bytes of the variable column data, it is calculated from the offsets to
the variable data. So, if you've grouped intermixed fixed and variable length
columns in logical sequence at the end of a row, they will end up physically
split on an RRF part.
>
>Anil, I'm curious if are you having a logging throughput issue or is this an
academic question. If you're having a throughput issue, can you consider
striping the actives to improve I/O?
>
>Regards,
>Ken
>
>
>-----Original Message-----
>From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Stan
Hoey
>Sent: Tuesday, January 12, 2010 12:49 PM
>To: [login to unmask email]
>Subject: Re: [DB2-L] DB2 Table Update Columns
>
>My knowledge in this area is a little out of date and may no longer be
>correct, but log records used to span from the first column updated to
>the last column updated. Best practice was to group frequently updated
>columns together. I don't think the position of the columns in the table
>is a factor.
>
>This assumes the table contains fixed length columns.
>
>Stan Hoey
>
>
>
>-----Original Message-----
>From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Anil
>Sent: Tuesday, January 12, 2010 6:06 PM
>To: [login to unmask email]
>Subject: [DB2-L] DB2 Table Update Columns
>
>Hi All !
>
>While working with DB2 table in the MVS world, there used to a best
>practice that said "For effective logging, specify most frequently
>updated columns at the end of the row (record)".
>I was wondering if that recommendation is still valid or has DB2 changed
>over the years and that now its a non-issue.
>Can someone throw some light on this topic.
>
>Thanks.
>
>Anil
>
>_________________________________________________________
____________
>
>* IDUG North America * Tampa, Florida, * May 10-14 2010 *
>http://IDUG.ORG/NA *
>_________________________________________________________
____________
>
>http://www.idug.org/db2-videos.html has hundreds of video presentations!
>Did you miss out on attending an IDUG conference?
>Many of the presentations were recorded and are available on our
>website!
>_________________________________________________________
____________
>
>If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L
>is the home of IDUG's DB2-L
>
>_________________________________________________________
____________
>
>* IDUG North America * Tampa, Florida, * May 10-14 2010 *
http://IDUG.ORG/NA *
>_________________________________________________________
____________
>
>http://www.idug.org/db2-videos.html has hundreds of video presentations!
>Did you miss out on attending an IDUG conference?
>Many of the presentations were recorded and are available on our website!
>_________________________________________________________
____________
>
>If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L
is the home of IDUG's DB2-L
>
>_________________________________________________________
____________
>
>* IDUG North America * Tampa, Florida, * May 10-14 2010 *
http://IDUG.ORG/NA *
>_________________________________________________________
____________
>
>http://www.idug.org/db2-videos.html has hundreds of video presentations!
>Did you miss out on attending an IDUG conference?
>Many of the presentations were recorded and are available on our website!
>_________________________________________________________
____________
>
>If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L
is the home of IDUG's DB2-L

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-videos.html has hundreds of video presentations!
Did you miss out on attending an IDUG conference?
Many of the presentations were recorded and are available on our website!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Roy Boxwell

Re: DB2 Table Update Columns
(in response to SUBSCRIBE DB2-L Anil Kale)
this also screams MEMBER CLUSTER very very loudly....



Roy Boxwell
SOFTWARE ENGINEERING GMBH
-Product Development-
Robert-Stolz-Straße 5
40470 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]
http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Siegfried Fürst, Gerhard Schubert




Anil <[login to unmask email]>
Gesendet von: IDUG DB2-L <[login to unmask email]>
18.01.2010 04:34
Bitte antworten an
IDUG DB2-L <[login to unmask email]>


An
[login to unmask email]
Kopie

Thema
Re: [DB2-L] DB2 Table Update Columns






Hi Ken,

The question is not necessarily academic. I am working on a mass insert
startegy using a COBOL program (COBOL program is not negotiable).
We want to estimate the time for a 250 million rows insert operation.
Keeping as aside the number of indexes and number of threads (parallel
insert jobs), I am examing the various parameters that would influence the

insert efficiency.
It is in this context that I am researching the overhead associated with
logging, and the alternatives to optimize the logging overhead.

Thanks for you response.

Anil

On Tue, 12 Jan 2010 14:21:00 -0600, McDonald, Ken
<[login to unmask email]> wrote:

>My (also slightly dated) experience was fixed length tables logged from
the
first changed byte to the last changed byte, not necessarily full columns.
And
for variable length rows, DB2 tended to log from the first changed byte to
the
end of the row... so putting frequently updated columns towards the end of

the row was better for logging.
>
>I think DB2 has gotten better about logging only from the first to last
changed byte, even on variable length rows that do not change length. But,
I
have not looked closely (or recently) to confirm this.
>
>z/OS DB2 Version 9 introduces Reordered Row Format (RRF) which
impacts the physical order of columns in a row... on an RRF partition, all

fixed length columns are now in column number sequence at the beginning of

the row followed by offsets to the variable length columns and then the
variable length column data. And, the length of the column is no longer
the
first two bytes of the variable column data, it is calculated from the
offsets to
the variable data. So, if you've grouped intermixed fixed and variable
length
columns in logical sequence at the end of a row, they will end up
physically
split on an RRF part.
>
>Anil, I'm curious if are you having a logging throughput issue or is this
an
academic question. If you're having a throughput issue, can you consider
striping the actives to improve I/O?
>
>Regards,
>Ken
>
>
>-----Original Message-----
>From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Stan
Hoey
>Sent: Tuesday, January 12, 2010 12:49 PM
>To: [login to unmask email]
>Subject: Re: [DB2-L] DB2 Table Update Columns
>
>My knowledge in this area is a little out of date and may no longer be
>correct, but log records used to span from the first column updated to
>the last column updated. Best practice was to group frequently updated
>columns together. I don't think the position of the columns in the table
>is a factor.
>
>This assumes the table contains fixed length columns.
>
>Stan Hoey
>
>
>
>-----Original Message-----
>From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Anil
>Sent: Tuesday, January 12, 2010 6:06 PM
>To: [login to unmask email]
>Subject: [DB2-L] DB2 Table Update Columns
>
>Hi All !
>
>While working with DB2 table in the MVS world, there used to a best
>practice that said "For effective logging, specify most frequently
>updated columns at the end of the row (record)".
>I was wondering if that recommendation is still valid or has DB2 changed
>over the years and that now its a non-issue.
>Can someone throw some light on this topic.
>
>Thanks.
>
>Anil
>
>_________________________________________________________
____________
>
>* IDUG North America * Tampa, Florida, * May 10-14 2010 *
>http://IDUG.ORG/NA *
>_________________________________________________________
____________
>
>http://www.idug.org/db2-videos.html has hundreds of video presentations!
>Did you miss out on attending an IDUG conference?
>Many of the presentations were recorded and are available on our
>website!
>_________________________________________________________
____________
>
>If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L
>is the home of IDUG's DB2-L
>
>_________________________________________________________
____________
>
>* IDUG North America * Tampa, Florida, * May 10-14 2010 *
http://IDUG.ORG/NA *
>_________________________________________________________
____________
>
>http://www.idug.org/db2-videos.html has hundreds of video presentations!
>Did you miss out on attending an IDUG conference?
>Many of the presentations were recorded and are available on our website!
>_________________________________________________________
____________
>
>If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L
is the home of IDUG's DB2-L
>
>_________________________________________________________
____________
>
>* IDUG North America * Tampa, Florida, * May 10-14 2010 *
http://IDUG.ORG/NA *
>_________________________________________________________
____________
>
>http://www.idug.org/db2-videos.html has hundreds of video presentations!
>Did you miss out on attending an IDUG conference?
>Many of the presentations were recorded and are available on our website!
>_________________________________________________________
____________
>
>If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L
is the home of IDUG's DB2-L

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 *
http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-videos.html has hundreds of video presentations!
Did you miss out on attending an IDUG conference?
Many of the presentations were recorded and are available on our website!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is
the home of IDUG's DB2-L


_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-videos.html has hundreds of video presentations!
Did you miss out on attending an IDUG conference?
Many of the presentations were recorded and are available on our website!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Phil Grainger

Re: DB2 Table Update Columns
(in response to Roy Boxwell)
Hi Anil

I hope you are looking at the possibilities of multi-row inserts (inserting
from a COBOL array)?

This will probably give you a bigger performance boost than almost anything
else you can do with "parameters" and tweaking the table design to optimize
logging

Phil G
Grainger Database Solutions Ltd

-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Anil
Sent: 18 January 2010 03:35
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 Table Update Columns

Hi Ken,

The question is not necessarily academic. I am working on a mass insert
startegy using a COBOL program (COBOL program is not negotiable).
We want to estimate the time for a 250 million rows insert operation.
Keeping as aside the number of indexes and number of threads (parallel
insert jobs), I am examing the various parameters that would influence the
insert efficiency.
It is in this context that I am researching the overhead associated with
logging, and the alternatives to optimize the logging overhead.

Thanks for you response.

Anil

On Tue, 12 Jan 2010 14:21:00 -0600, McDonald, Ken
<[login to unmask email]> wrote:

>My (also slightly dated) experience was fixed length tables logged from the

first changed byte to the last changed byte, not necessarily full columns.
And
for variable length rows, DB2 tended to log from the first changed byte to
the
end of the row... so putting frequently updated columns towards the end of
the row was better for logging.
>
>I think DB2 has gotten better about logging only from the first to last
changed byte, even on variable length rows that do not change length. But,
I
have not looked closely (or recently) to confirm this.
>
>z/OS DB2 Version 9 introduces Reordered Row Format (RRF) which
impacts the physical order of columns in a row... on an RRF partition, all
fixed length columns are now in column number sequence at the beginning of
the row followed by offsets to the variable length columns and then the
variable length column data. And, the length of the column is no longer the

first two bytes of the variable column data, it is calculated from the
offsets to
the variable data. So, if you've grouped intermixed fixed and variable
length
columns in logical sequence at the end of a row, they will end up physically

split on an RRF part.
>
>Anil, I'm curious if are you having a logging throughput issue or is this
an
academic question. If you're having a throughput issue, can you consider
striping the actives to improve I/O?
>
>Regards,
>Ken
>
>
>-----Original Message-----
>From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Stan
Hoey
>Sent: Tuesday, January 12, 2010 12:49 PM
>To: [login to unmask email]
>Subject: Re: [DB2-L] DB2 Table Update Columns
>
>My knowledge in this area is a little out of date and may no longer be
>correct, but log records used to span from the first column updated to
>the last column updated. Best practice was to group frequently updated
>columns together. I don't think the position of the columns in the table
>is a factor.
>
>This assumes the table contains fixed length columns.
>
>Stan Hoey
>
>
>
>-----Original Message-----
>From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Anil
>Sent: Tuesday, January 12, 2010 6:06 PM
>To: [login to unmask email]
>Subject: [DB2-L] DB2 Table Update Columns
>
>Hi All !
>
>While working with DB2 table in the MVS world, there used to a best
>practice that said "For effective logging, specify most frequently
>updated columns at the end of the row (record)".
>I was wondering if that recommendation is still valid or has DB2 changed
>over the years and that now its a non-issue.
>Can someone throw some light on this topic.
>
>Thanks.
>
>Anil
>
>_________________________________________________________
____________
>
>* IDUG North America * Tampa, Florida, * May 10-14 2010 *
>http://IDUG.ORG/NA *
>_________________________________________________________
____________
>
>http://www.idug.org/db2-videos.html has hundreds of video presentations!
>Did you miss out on attending an IDUG conference?
>Many of the presentations were recorded and are available on our
>website!
>_________________________________________________________
____________
>
>If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L
>is the home of IDUG's DB2-L
>
>_________________________________________________________
____________
>
>* IDUG North America * Tampa, Florida, * May 10-14 2010 *
http://IDUG.ORG/NA *
>_________________________________________________________
____________
>
>http://www.idug.org/db2-videos.html has hundreds of video presentations!
>Did you miss out on attending an IDUG conference?
>Many of the presentations were recorded and are available on our website!
>_________________________________________________________
____________
>
>If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L
is the home of IDUG's DB2-L
>
>_________________________________________________________
____________
>
>* IDUG North America * Tampa, Florida, * May 10-14 2010 *
http://IDUG.ORG/NA *
>_________________________________________________________
____________
>
>http://www.idug.org/db2-videos.html has hundreds of video presentations!
>Did you miss out on attending an IDUG conference?
>Many of the presentations were recorded and are available on our website!
>_________________________________________________________
____________
>
>If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L
is the home of IDUG's DB2-L

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 *
http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-videos.html has hundreds of video presentations!
Did you miss out on attending an IDUG conference?
Many of the presentations were recorded and are available on our website!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is
the home of IDUG's DB2-L

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-videos.html has hundreds of video presentations!
Did you miss out on attending an IDUG conference?
Many of the presentations were recorded and are available on our website!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Ken McDonald

Re: DB2 Table Update Columns
(in response to Phil Grainger)
Hi Anil,

I agree with Phil that multi-row insert will be a better approach for performance. The entire row will be logged for an insert no matter the order of columns. And, restating my original comment, if logging I/O is an issue, consider striping the actives to improve the I/O rates.

Another thought (this comes from my ignorance of not having coded in COBOL since the mid 80s)... Can you multi-task in COBOL to allow for multiple concurrent threads to DB2? You'll have to consider index structure and commit frequency to avoid contention if all of the tasks are updating a single table, but inserting with multiple threads in a single job can improve throughput too.

Regards,
Ken

-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Phil Grainger
Sent: Monday, January 18, 2010 2:46 AM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 Table Update Columns

Hi Anil

I hope you are looking at the possibilities of multi-row inserts (inserting
from a COBOL array)?

This will probably give you a bigger performance boost than almost anything
else you can do with "parameters" and tweaking the table design to optimize
logging

Phil G
Grainger Database Solutions Ltd

-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Anil
Sent: 18 January 2010 03:35
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 Table Update Columns

Hi Ken,

The question is not necessarily academic. I am working on a mass insert
startegy using a COBOL program (COBOL program is not negotiable).
We want to estimate the time for a 250 million rows insert operation.
Keeping as aside the number of indexes and number of threads (parallel
insert jobs), I am examing the various parameters that would influence the
insert efficiency.
It is in this context that I am researching the overhead associated with
logging, and the alternatives to optimize the logging overhead.

Thanks for you response.

Anil

On Tue, 12 Jan 2010 14:21:00 -0600, McDonald, Ken
<[login to unmask email]> wrote:

>My (also slightly dated) experience was fixed length tables logged from the

first changed byte to the last changed byte, not necessarily full columns.
And
for variable length rows, DB2 tended to log from the first changed byte to
the
end of the row... so putting frequently updated columns towards the end of
the row was better for logging.
>
>I think DB2 has gotten better about logging only from the first to last
changed byte, even on variable length rows that do not change length. But,
I
have not looked closely (or recently) to confirm this.
>
>z/OS DB2 Version 9 introduces Reordered Row Format (RRF) which
impacts the physical order of columns in a row... on an RRF partition, all
fixed length columns are now in column number sequence at the beginning of
the row followed by offsets to the variable length columns and then the
variable length column data. And, the length of the column is no longer the

first two bytes of the variable column data, it is calculated from the
offsets to
the variable data. So, if you've grouped intermixed fixed and variable
length
columns in logical sequence at the end of a row, they will end up physically

split on an RRF part.
>
>Anil, I'm curious if are you having a logging throughput issue or is this
an
academic question. If you're having a throughput issue, can you consider
striping the actives to improve I/O?
>
>Regards,
>Ken
>
>
>-----Original Message-----
>From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Stan
Hoey
>Sent: Tuesday, January 12, 2010 12:49 PM
>To: [login to unmask email]
>Subject: Re: [DB2-L] DB2 Table Update Columns
>
>My knowledge in this area is a little out of date and may no longer be
>correct, but log records used to span from the first column updated to
>the last column updated. Best practice was to group frequently updated
>columns together. I don't think the position of the columns in the table
>is a factor.
>
>This assumes the table contains fixed length columns.
>
>Stan Hoey
>
>
>
>-----Original Message-----
>From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Anil
>Sent: Tuesday, January 12, 2010 6:06 PM
>To: [login to unmask email]
>Subject: [DB2-L] DB2 Table Update Columns
>
>Hi All !
>
>While working with DB2 table in the MVS world, there used to a best
>practice that said "For effective logging, specify most frequently
>updated columns at the end of the row (record)".
>I was wondering if that recommendation is still valid or has DB2 changed
>over the years and that now its a non-issue.
>Can someone throw some light on this topic.
>
>Thanks.
>
>Anil
>
>_________________________________________________________
____________
>
>* IDUG North America * Tampa, Florida, * May 10-14 2010 *
>http://IDUG.ORG/NA *
>_________________________________________________________
____________
>
>http://www.idug.org/db2-videos.html has hundreds of video presentations!
>Did you miss out on attending an IDUG conference?
>Many of the presentations were recorded and are available on our
>website!
>_________________________________________________________
____________
>
>If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L
>is the home of IDUG's DB2-L
>
>_________________________________________________________
____________
>
>* IDUG North America * Tampa, Florida, * May 10-14 2010 *
http://IDUG.ORG/NA *
>_________________________________________________________
____________
>
>http://www.idug.org/db2-videos.html has hundreds of video presentations!
>Did you miss out on attending an IDUG conference?
>Many of the presentations were recorded and are available on our website!
>_________________________________________________________
____________
>
>If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L
is the home of IDUG's DB2-L
>
>_________________________________________________________
____________
>
>* IDUG North America * Tampa, Florida, * May 10-14 2010 *
http://IDUG.ORG/NA *
>_________________________________________________________
____________
>
>http://www.idug.org/db2-videos.html has hundreds of video presentations!
>Did you miss out on attending an IDUG conference?
>Many of the presentations were recorded and are available on our website!
>_________________________________________________________
____________
>
>If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L
is the home of IDUG's DB2-L

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 *
http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-videos.html has hundreds of video presentations!
Did you miss out on attending an IDUG conference?
Many of the presentations were recorded and are available on our website!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is
the home of IDUG's DB2-L

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-videos.html has hundreds of video presentations!
Did you miss out on attending an IDUG conference?
Many of the presentations were recorded and are available on our website!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-videos.html has hundreds of video presentations!
Did you miss out on attending an IDUG conference?
Many of the presentations were recorded and are available on our website!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Jack Campbell

Re: DB2 Table Update Columns
(in response to Ken McDonald)
Anil,

Here are a few options I can think of:

1) Break the data into some kind of logical group, and run multiple versions
of the insert in parallel...........there will be some index contention. But if you
consider breaking by PARTITION (preferred), or CLUSTER seqeunce, you
will be able to up your insert rate.

2) Consider dropping performance indexes, and recreating afterwards (if
this is a one of process)

3) As mentioned LOG contention could be an issue for parallel processing

4) If you are DATA SHARING, consider the benefit of running on a single
LPAR to reduce GBP contention.

Regards

Jack

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-videos.html has hundreds of video presentations!
Did you miss out on attending an IDUG conference?
Many of the presentations were recorded and are available on our website!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Mike Bracey

Re: DB2 Table Update Columns
(in response to Jack Campbell)
Hello Anil
You might pick up some tips from the presentation on "Optimizing INSERT
Performance" by John Campbell:
http://www-01.ibm.com/software/os/systemz/telecon/dec1/
Register to get the foils and listen to the recorded presentation.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/events/index.html is your DB2 Events calendar! RUG meetings,
Webcasts, Conferences- what is going on next?
RUG leaders- get your events on the calendar today!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L