[DB2-L] Updating Primary Key

Walter Janißen

[DB2-L] Updating Primary Key
Darren

In general it is not possible to run a delete/insert on the parent table, either the delete rule is restrict, then it is not allowed if dependent rows exist or the delete rule is cascade, which will delete all dependent rows. But Updates are not possible either if dependent rows exist. I think, you have to insert these rows into the parent table, update the foreign key in the dependent tables and then delete the old rows in the parent table.

Mit freundlichen Grüßen
Walter Janißen

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Laufzeitarchitektur
Victoriaplatz 2
40198 Düsseldorf
mailto:[login to unmask email]

Vorsitzender des Aufsichtsrats: Jürgen Vetter
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Dr. Christian Nymphius, Dr. Michael Regauer, Wolfgang Schön.
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf, HRB 37996

-----Ursprüngliche Nachricht-----
Von: DB2 Data Base Discussion List [mailto:[login to unmask email] Im Auftrag von Darren Kilpatrick
Gesendet: Donnerstag, 15. Januar 2009 19:42
An: [login to unmask email]
Betreff: [DB2-L] Updating Primary Key

I need to update the columns in the primary key of a parent table. Should I run the update, or should I run a delete/insert? We are on DB2 Version 8 for z/OS


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Paul Ogborne

Re: Updating Primary Key
(in response to Walter Janißen)
Hi Darren,

As mentioned by others you can run the update and if partitioned providing?PARTKEYU is appropriately set.
Two things to consider however:
1) The above is a great way to quickly disorganise your data.
2) Once the UPDATE is coded then you are stuck with your PARTKEYU setting.
(and (3!) if you wish to at least pay 'lip service' to the "Relational Model", then a DELETE/INSERT is rather more?appropriate in my opinion).

Regards,
Paul.


-----Original Message-----
From: Darren Kilpatrick <[login to unmask email]>
To: [login to unmask email]
Sent: Thu, 15 Jan 2009 18:41
Subject: [DB2-L] Updating Primary Key



I need to update the columns in the primary key of a parent table. Should I
run the update, or should I run a delete/insert? We are on DB2 Version 8 for
z/OS


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You
should have gotten an e-mail with the temporary password assigned to your
account. Please log in and update your member profile. If you are not already an
IDUG.org member, please register at http://www.idug.org/component/juser/register.html


________________________________________________________________________
AOL Email goes Mobile! You can now read your AOL Emails whilst on the move. Sign up for a free AOL Email account with unlimited storage today.

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Dee Reins

Re: Updating Primary Key
(in response to Paul Ogborne)
I design the database tables, such that the Primary key is a number field that keep incrementing, like a sequence number. Then I make a unique index on the table column values, and cluster on that index.
Now, all i have to do is modify a field in that index, and everything is good to go. That table and index get reorged with the next DB2 reorg I have scheduled.

In your situation, update the key, followed by an online reorg of the table.


> -----Original Message-----
> From: Darren Kilpatrick <[login to unmask email]>
> To: [login to unmask email]
> Sent: Thu, 15 Jan 2009 18:41
> Subject: [DB2-L] Updating Primary Key
>
>
>
> I need to update the columns in the primary key of a parent table. Should I
> run the update, or should I run a delete/insert? We are on DB2 Version 8 for
> z/OS
>
>
> ______________________________________________________________________
>
> * IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
> ______________________________________________________________________
>
>
>
> IDUG.org was recently updated requiring members to use a new password. You
> should have gotten an e-mail with the temporary password assigned to your
> account. Please log in and update your member profile. If you are not already an
> IDUG.org member, please register at
> http://www.idug.org/component/juser/register.html
>
>
> ________________________________________________________________________
> AOL Email goes Mobile! You can now read your AOL Emails whilst on the move. Sign
> up for a free AOL Email account with unlimited storage today.
>
> ______________________________________________________________________
>
> * IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/Events *
> ______________________________________________________________________
>
>
>
>
> IDUG.org was recently updated requiring members to use a new password. You
> should have gotten an e-mail with the temporary password assigned to your
> account. Please log in and update your member profile. If you are not already an
> IDUG.org member, please register at
> http://www.idug.org/component/juser/register.html



______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html
Attachments

  • import1 (3.7k)

Peter Vanroose

Re: Updating Primary Key
(in response to Dee Reins)
[login to unmask email] wrote:
> ... But Updates are not possible either if dependent rows exist.
> I think, you have to insert these rows into the parent table,
> update the foreign key in the dependent tables and then
> delete the old rows in the parent table.

Alternatively, and if DDL is allowed, you could
(1) ALTER TABLE dependent-table DROP FOREIGN KEY name
(2) update both the PKs and the corresponding KFs
(3) ALTER TABLE dependent-table ADD FOREIGN KEY name .....

This is one of the situations where we suffer from a lacking ON UPDATE
CASCADE rule for a foreign key in DB2 ...

-- Peter Vanroose.

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Troy Coleman

Re: Updating Primary Key
(in response to Peter Vanroose)
Wow I just keep learning all the time. I have not run into that as an
issue. I guess I've been lucky. :-)

Troy


Taddei, Cathy wrote:
> I don't think that will work for tables created before the zparm PARTKEYU was introduced in v5 -- the UPDATES column in SYSCOLUMNS would have been set to 'N' for partitioning key columns back then. There is a way to change that without dropping and recreating the table, but I haven't tried it yet.
>
> Regards,
> Cathy Taddei
>
> -----Original Message-----
> From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Troy Coleman
> Sent: Thursday, January 15, 2009 2:06 PM
> To: [login to unmask email]
> Subject: Re: Updating Primary Key
>
> I would handle it as an update.
> It used to be an issue if you had the table partitioned and you wanted
> to change the columns used in the partitioning limit keys.
> But that was changed through zparm support to allow for updates on the
> limit key.
>
> I have not done any performance testing on an update versus delete/insert.
> But I would think the delete/insert will cost you more since that is two
> trips to DB2 versus one.
>
> Troy Coleman
>
> Darren Kilpatrick wrote:
>
>> I need to update the columns in the primary key of a parent table. Should I
>> run the update, or should I run a delete/insert? We are on DB2 Version 8 for
>> z/OS
>>
>>
>
> ------------------------------------------------------------------------------
>
> This email is confidential and may be legally privileged.
>
> It is intended solely for the addressee. Access to this email by anyone else, unless expressly approved by the sender or an authorized addressee, is unauthorized.
>
> If you are not the intended recipient, any disclosure, copying, distribution or any action omitted or taken in reliance on it, is prohibited and may be unlawful. If you believe that you have received this email in error, please contact the sender, delete this e-mail and destroy all copies.
>
> ======
>
>
> ______________________________________________________________________
>
> * IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
> ______________________________________________________________________
>
>
>
> IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html
>
>

______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Troy Coleman

Re: AW: [DB2-L] Updating Primary Key
(in response to Troy Coleman)
Ah good point Darren. I over looked the key word "parent" table. I was
just thinking of a table with a primary key.
You are correct if you are using DB2 "system RI".

Troy

[login to unmask email] wrote:
> Darren
>
> In general it is not possible to run a delete/insert on the parent table, either the delete rule is restrict, then it is not allowed if dependent rows exist or the delete rule is cascade, which will delete all dependent rows. But Updates are not possible either if dependent rows exist. I think, you have to insert these rows into the parent table, update the foreign key in the dependent tables and then delete the old rows in the parent table.
>
> Mit freundlichen Grüßen
> Walter Janißen
>
> ITERGO Informationstechnologie GmbH
> Anwendungsentwicklung
> Laufzeitarchitektur
> Victoriaplatz 2
> 40198 Düsseldorf
> mailto:[login to unmask email]
>
> Vorsitzender des Aufsichtsrats: Jürgen Vetter
> Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
> Dr. Christian Nymphius, Dr. Michael Regauer, Wolfgang Schön.
> Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf, HRB 37996
>
> -----Ursprüngliche Nachricht-----
> Von: DB2 Data Base Discussion List [mailto:[login to unmask email] Im Auftrag von Darren Kilpatrick
> Gesendet: Donnerstag, 15. Januar 2009 19:42
> An: [login to unmask email]
> Betreff: [DB2-L] Updating Primary Key
>
> I need to update the columns in the primary key of a parent table. Should I run the update, or should I run a delete/insert? We are on DB2 Version 8 for z/OS
>
>
> ______________________________________________________________________
>
> * IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
> ______________________________________________________________________
>
>
>
> IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html
>
> ______________________________________________________________________
>
> * IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/Events *
> ______________________________________________________________________
>
>
>
>
> IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html
>
>

______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html