Two questions

Dave Petronella

Two questions
Hi,

I have a couple of questions - any feedback would be appreciated.

1) DB2 logs from the first byte changed to the last byte changed
(or I believe from the first byte to end of record for variable length
records).
My assumption is that this is true regardless of the number of
columns specified in the UPDATE statement. For example, if we specify
10 columns the UPDATE SET list, but only the second set of 5
columns have changed, my assumption is DB2 will base what it logs on
the fact that only the second set of 5 values have changed and not
try to log the entire 10 columns worth of data simply because they
are in the UPDATE statement. Is this assumption correct?

2) DB2 v8 and DB2 v9 CM allows for non-unique DPSI indexes. Is
there any APAR that anyone knows of to allow Unique DPSIs in DB2 v9 CM?
Or do we have to wait until DB2 v9 NFM?

Thanks.
Dave.

________________________
David Petronella
Vice President
Data Resource Management / DB2 Database Administration
Pershing LLC, a BNY Mellon company
www.pershing.com
Office: (973) 360-7747
Fax: (973) 360-3121
Email: [login to unmask email]

Go Green. Think before you print.

******************************************************
IMPORTANT: Any information contained in this communication is intended for the use of the named individual or entity. All information contained in this communication is not intended or construed as an offer, solicitation, or a recommendation to purchase any security. Advice, suggestions or views presented in this communication are not necessarily those of Pershing LLC nor do they warrant a complete or accurate statement.

If you are not an intended party to this communication, please notify the sender and delete/destroy any and all copies of this communication. Unintended recipients shall not review, reproduce, disseminate nor disclose any information contained in this communication. Pershing LLC reserves the right to monitor and retain all incoming and outgoing communications as permitted by applicable law.

Email communications may contain viruses or other defects. Pershing LLC does not accept liability nor does it warrant that email communications are virus or defect free.
******************************************************

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** DB2 certification -> no additional charge
** Meet fellow DB2 users and leading DB2 consultants
_____________________________________________________________________

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

Phil Grainger

Re: Two questions
(in response to Dave Petronella)
HI David


1) Yes, DB2 only logs WHAT HAS CHANGED (not what you specify in the update statement) - from first changed byte to last changed byte
BUT be careful with variable length columns and reordered row format. Remember, if the LENGTH value changes then that's a change that needs to be logged. Imagine a 50 column table, in reordered row format, with 1 fixed column and 49 variable ones. Change the length of the LAST variable column and DB2 will have to log from the 49th length halfword (just before the first actual variable column) right through to the end of the 49th variable column. Not what you might expect

2) No - this is a DB2 9 CM only feature - and remember, DPSIs can only be unique if they contain the partitioning key in their own key

Hope this helps
Phil Grainger
Cogito Ltd.
[login to unmask email]
+44 (0) 1298 872 148
+44 (0) 7505 266 768
www.cogito.co.uk <blocked:: http://www.cogito.co.uk >

Attend IDUG 2011 - the premiere events for DB2 professionals.
IDUG North America < http://www.idug.org/na > , 2-6 May, Anaheim California
IDUG EMEA < http://www.idug.org/emea > , 14-18 November, Prague Czech Republic


From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Dave Petronella
Sent: 29 November 2010 18:32
To: [login to unmask email]
Subject: [DB2-L] Two questions


Hi,

I have a couple of questions - any feedback would be appreciated.

1) DB2 logs from the first byte changed to the last byte changed (or I believe from the first byte to end of record for variable length records).
My assumption is that this is true regardless of the number of columns specified in the UPDATE statement. For example, if we specify
10 columns the UPDATE SET list, but only the second set of 5 columns have changed, my assumption is DB2 will base what it logs on
the fact that only the second set of 5 values have changed and not try to log the entire 10 columns worth of data simply because they
are in the UPDATE statement. Is this assumption correct?

2) DB2 v8 and DB2 v9 CM allows for non-unique DPSI indexes. Is there any APAR that anyone knows of to allow Unique DPSIs in DB2 v9 CM?
Or do we have to wait until DB2 v9 NFM?

Thanks.
Dave.

________________________
David Petronella
Vice President
Data Resource Management / DB2 Database Administration
Pershing LLC, a BNY Mellon company
www.pershing.com
Office: (973) 360-7747
Fax: (973) 360-3121
Email: [login to unmask email]

Go Green. Think before you print.
******************************************************
IMPORTANT: Any information contained in this communication is intended for the use of the named individual or entity. All information contained in this communication is not intended or construed as an offer, solicitation, or a recommendation to purchase any security. Advice, suggestions or views presented in this communication are not necessarily those of Pershing LLC nor do they warrant a complete or accurate statement.

If you are not an intended party to this communication, please notify the sender and delete/destroy any and all copies of this communication. Unintended recipients shall not review, reproduce, disseminate nor disclose any information contained in this communication. Pershing LLC reserves the right to monitor and retain all incoming and outgoing communications as permitted by applicable law.

Email communications may contain viruses or other defects. Pershing LLC does not accept liability nor does it warrant that email communications are virus or defect free.
******************************************************
________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

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

Phil Grainger

Re: Two questions
(in response to Phil Grainger)
A correction to something I posted last week

What I should have said is:


1) Yes, DB2 only logs WHAT HAS CHANGED (not what you specify in the update statement) - from first changed byte to last changed byte
BUT be careful with variable length columns and reordered row format. Remember, if the LENGTH of a column changes then the offset to all following columns will also have to change and that's a change that needs to be logged. Imagine a 50 column table, in reordered row format, with 1 fixed column and 49 variable ones. Change the length of the PENULTIMATE variable column and DB2 will have to log from the 49th offset halfword (just before the first actual variable column) right through to the end of the 48th variable column. Not what you might expect

Apologies for any confusion - of course RRF has a list of halfword OFFSETS not LENGTHS

Phil Grainger
Cogito Ltd.
[login to unmask email]
+44 (0) 1298 872 148
+44 (0) 7505 266 768
www.cogito.co.uk <blocked:: http://www.cogito.co.uk >

Attend IDUG 2011 - the premiere events for DB2 professionals.
IDUG North America < http://www.idug.org/na > , 2-6 May, Anaheim California
IDUG EMEA < http://www.idug.org/emea > , 14-18 November, Prague Czech Republic


From: Phil Grainger
Sent: 30 November 2010 09:36
To: [login to unmask email]
Subject: RE: [DB2-L] Two questions

HI David


2) Yes, DB2 only logs WHAT HAS CHANGED (not what you specify in the update statement) - from first changed byte to last changed byte
BUT be careful with variable length columns and reordered row format. Remember, if the LENGTH value changes then that's a change that needs to be logged. Imagine a 50 column table, in reordered row format, with 1 fixed column and 49 variable ones. Change the length of the LAST variable column and DB2 will have to log from the 49th length halfword (just before the first actual variable column) right through to the end of the 49th variable column. Not what you might expect

3) No - this is a DB2 9 CM only feature - and remember, DPSIs can only be unique if they contain the partitioning key in their own key

Hope this helps
Phil Grainger
Cogito Ltd.
[login to unmask email]
+44 (0) 1298 872 148
+44 (0) 7505 266 768
www.cogito.co.uk <blocked:: http://www.cogito.co.uk >

Attend IDUG 2011 - the premiere events for DB2 professionals.
IDUG North America < http://www.idug.org/na > , 2-6 May, Anaheim California
IDUG EMEA < http://www.idug.org/emea > , 14-18 November, Prague Czech Republic

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Dave Petronella
Sent: 29 November 2010 18:32
To: [login to unmask email]
Subject: [DB2-L] Two questions


Hi,

I have a couple of questions - any feedback would be appreciated.

1) DB2 logs from the first byte changed to the last byte changed (or I believe from the first byte to end of record for variable length records).
My assumption is that this is true regardless of the number of columns specified in the UPDATE statement. For example, if we specify
10 columns the UPDATE SET list, but only the second set of 5 columns have changed, my assumption is DB2 will base what it logs on
the fact that only the second set of 5 values have changed and not try to log the entire 10 columns worth of data simply because they
are in the UPDATE statement. Is this assumption correct?

2) DB2 v8 and DB2 v9 CM allows for non-unique DPSI indexes. Is there any APAR that anyone knows of to allow Unique DPSIs in DB2 v9 CM?
Or do we have to wait until DB2 v9 NFM?

Thanks.
Dave.

________________________
David Petronella
Vice President
Data Resource Management / DB2 Database Administration
Pershing LLC, a BNY Mellon company
www.pershing.com
Office: (973) 360-7747
Fax: (973) 360-3121
Email: [login to unmask email]

Go Green. Think before you print.
******************************************************
IMPORTANT: Any information contained in this communication is intended for the use of the named individual or entity. All information contained in this communication is not intended or construed as an offer, solicitation, or a recommendation to purchase any security. Advice, suggestions or views presented in this communication are not necessarily those of Pershing LLC nor do they warrant a complete or accurate statement.

If you are not an intended party to this communication, please notify the sender and delete/destroy any and all copies of this communication. Unintended recipients shall not review, reproduce, disseminate nor disclose any information contained in this communication. Pershing LLC reserves the right to monitor and retain all incoming and outgoing communications as permitted by applicable law.

Email communications may contain viruses or other defects. Pershing LLC does not accept liability nor does it warrant that email communications are virus or defect free.
******************************************************
________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

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

Dave Petronella

Re: Two questions
(in response to Phil Grainger)
Phil,

Thanks very much for the reply. This was a big help.

Dave.

________________________
David Petronella
Vice President
Data Resource Management / DB2 Database Administration
Pershing LLC, a BNY Mellon company
www.pershing.com
Office: (973) 360-7747
Fax: (973) 360-3121
Email: [login to unmask email]

Go Green. Think before you print.



From: Phil Grainger <[login to unmask email]>
To: [login to unmask email]
Date: 12/02/2010 04:16 PM
Subject: Re: [DB2-L] Two questions
Sent by: IDUG DB2-L <[login to unmask email]>



A correction to something I posted last week

What I should have said is:

1) Yes, DB2 only logs WHAT HAS CHANGED (not what you specify in the
update statement) ? from first changed byte to last changed byte
BUT be careful with variable length columns and reordered row format.
Remember, if the LENGTH of a column changes then the offset to all
following columns will also have to change and that?s a change that needs
to be logged. Imagine a 50 column table, in reordered row format, with 1
fixed column and 49 variable ones. Change the length of the PENULTIMATE
variable column and DB2 will have to log from the 49th offset halfword
(just before the first actual variable column) right through to the end of
the 48th variable column. Not what you might expect

Apologies for any confusion ? of course RRF has a list of halfword OFFSETS
not LENGTHS

Phil Grainger
Cogito Ltd.
[login to unmask email]
+44 (0) 1298 872 148
+44 (0) 7505 266 768
www.cogito.co.uk

Attend IDUG 2011 - the premiere events for DB2 professionals.
IDUG North America, 2-6 May, Anaheim California
IDUG EMEA, 14-18 November, Prague Czech Republic


From: Phil Grainger
Sent: 30 November 2010 09:36
To: [login to unmask email]
Subject: RE: [DB2-L] Two questions

HI David

2) Yes, DB2 only logs WHAT HAS CHANGED (not what you specify in the
update statement) ? from first changed byte to last changed byte
BUT be careful with variable length columns and reordered row format.
Remember, if the LENGTH value changes then that?s a change that needs to
be logged. Imagine a 50 column table, in reordered row format, with 1
fixed column and 49 variable ones. Change the length of the LAST variable
column and DB2 will have to log from the 49th length halfword (just before
the first actual variable column) right through to the end of the 49th
variable column. Not what you might expect
3) No ? this is a DB2 9 CM only feature ? and remember, DPSIs can
only be unique if they contain the partitioning key in their own key

Hope this helps
Phil Grainger
Cogito Ltd.
[login to unmask email]
+44 (0) 1298 872 148
+44 (0) 7505 266 768
www.cogito.co.uk

Attend IDUG 2011 - the premiere events for DB2 professionals.
IDUG North America, 2-6 May, Anaheim California
IDUG EMEA, 14-18 November, Prague Czech Republic

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Dave Petronella
Sent: 29 November 2010 18:32
To: [login to unmask email]
Subject: [DB2-L] Two questions


Hi,

I have a couple of questions - any feedback would be appreciated.

1) DB2 logs from the first byte changed to the last byte changed
(or I believe from the first byte to end of record for variable length
records).
My assumption is that this is true regardless of the number of
columns specified in the UPDATE statement. For example, if we specify
10 columns the UPDATE SET list, but only the second set of 5
columns have changed, my assumption is DB2 will base what it logs on
the fact that only the second set of 5 values have changed and not
try to log the entire 10 columns worth of data simply because they
are in the UPDATE statement. Is this assumption correct?

2) DB2 v8 and DB2 v9 CM allows for non-unique DPSI indexes. Is
there any APAR that anyone knows of to allow Unique DPSIs in DB2 v9 CM?
Or do we have to wait until DB2 v9 NFM?

Thanks.
Dave.

________________________
David Petronella
Vice President
Data Resource Management / DB2 Database Administration
Pershing LLC, a BNY Mellon company
www.pershing.com
Office: (973) 360-7747
Fax: (973) 360-3121
Email: [login to unmask email]

Go Green. Think before you print.
******************************************************
IMPORTANT: Any information contained in this communication is intended for
the use of the named individual or entity. All information contained in
this communication is not intended or construed as an offer, solicitation,
or a recommendation to purchase any security. Advice, suggestions or views
presented in this communication are not necessarily those of Pershing LLC
nor do they warrant a complete or accurate statement.

If you are not an intended party to this communication, please notify the
sender and delete/destroy any and all copies of this communication.
Unintended recipients shall not review, reproduce, disseminate nor
disclose any information contained in this communication. Pershing LLC
reserves the right to monitor and retain all incoming and outgoing
communications as permitted by applicable law.

Email communications may contain viruses or other defects. Pershing LLC
does not accept liability nor does it warrant that email communications
are virus or defect free.
******************************************************


The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
are not already an IDUG member, please register here.



The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
are not already an IDUG member, please register here.

******************************************************
IMPORTANT: Any information contained in this communication is intended for the use of the named individual or entity. All information contained in this communication is not intended or construed as an offer, solicitation, or a recommendation to purchase any security. Advice, suggestions or views presented in this communication are not necessarily those of Pershing LLC nor do they warrant a complete or accurate statement.

If you are not an intended party to this communication, please notify the sender and delete/destroy any and all copies of this communication. Unintended recipients shall not review, reproduce, disseminate nor disclose any information contained in this communication. Pershing LLC reserves the right to monitor and retain all incoming and outgoing communications as permitted by applicable law.

Email communications may contain viruses or other defects. Pershing LLC does not accept liability nor does it warrant that email communications are virus or defect free.
******************************************************

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

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