RI problems

Paul Fegan

RI problems
Hi All,

I need as much help as I can get with an RI problem we're having here.
While testing a database change in our production clone, I dropped and
recreated a partitioned tablespace. Using the usual method, unload the
data, drop and recreate the tablespace, load back all the data reinstate
the RI etc (I didn't do the check data's on the children as the data isn't
changing). everything went fine no errors or failed utilities, all of the
data went into the partitions it came out of..... Initially I was trying
to convert the tablespace to use a DSSIZE of 4G to allow for future growth
but the last test I did just put the definition back EXACTLY the same way
it's defined in production.

In all test cases when we try to update the foreign key of one of the
children of the dropped and recreated parent, the update fails with a -530
RI check problem. When I subsequently run a check data (scope all) on the
child every row in the child gets tossed. I've check the parent and the
corresponding key values exist in the parent for all of the child tables
rows. So there is no valid reason for the -530. The parent in question has
11 children but only those children with foreign key columns that are
nullable appear to cause the problem. I've been unable to recreate the
problem in our test system so either the problem is volume related or it's
an issue with children created under version 5/6 with parents created
under version 8.

I've raised this with IBM and it's with level 2 support at the moment.

What I need is any thoughts of how to work around this problem. We know
it's a bug but we may not have time to wait for a fix. Does anyone have
any ideas how I can make this tablespace larger without dropping and
recreating it? Compression is an option but we use log analyzer (CA) to
track updates and I don't believe this will work on a compressed table
(anyone from CA out there?). The obvious solution is DSSIZE 2G or higher
but I don't think it's possible to do that without dropping and recreating
the tablespace. Any help would be gratefully received.

Thanks,
Paul

************************************************************
Opinions contained in this e-mail do not necessarily reflect
the opinions of the Queensland Department of Main Roads,
Queensland Transport or Maritime Safety Queensland, or
endorsed organisations utilising the same infrastructure.
If you have received this electronic mail message in error,
please immediately notify the sender and delete the message
from your computer.
************************************************************

---------------------------------------------------------------------------------
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

Steen Rasmussen

Re: RI problems
(in response to Paul Fegan)
Hello Paul,

Let me quickly address your concern regarding the compression and Log
Analyzer.
You do NOT need to be concerned. We will read the compression dictionary
from the active pageset in order to decompress the log-records - so you
are good to go.

If Log Analyzer need to read log-records from a point-in-time prior to a
reorg and the compression dictionary has been changed, we will need to
read an image copy prior to the reorg in order to get the compression
dictionary.

Let me know if you have other concerns, and we can take it off the list.

Steen Rasmussen
CA
Senior Consultant DB2 tools
Tel : +1-630-505-6673 (US direct)
Tel : +1-815-274-9589 (US mobile)
Tel : +45-22 15 44 98 (Europe mobile)
[login to unmask email]


---------------------------------------------------------------------------------
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

Carol Anne Sutfin

Re: RI problems
(in response to Steen Rasmussen)
Paul,

If the table you dropped was the parent table,
Did you first Alter the child tables to remove the foreign key references
first,
and if not were those references DELETE CASCADE.
And have you created them again.

If the table you dropped was the child table
did you remember to add back the references or included them in the table
define.

If the table you dropped was both a parent and a child table
check both of these things.

Carol Sutfin
Corporate DBA
Regions Financial Corp.
(205)261-5214
[login to unmask email]



Paul Fegan
<[login to unmask email]
NSPORT.QLD.GOV.AU To
> [login to unmask email]
Sent by: DB2 Data cc
Base Discussion
List Subject
<[login to unmask email] [DB2-L] RI problems
ORG>


01/10/2007 04:48
PM


Please respond to
DB2 Database
Discussion list
at IDUG
<[login to unmask email]
2-L.ORG>






Hi All,

I need as much help as I can get with an RI problem we're having here.
While testing a database change in our production clone, I dropped and
recreated a partitioned tablespace. Using the usual method, unload the
data, drop and recreate the tablespace, load back all the data reinstate
the RI etc (I didn't do the check data's on the children as the data isn't
changing). everything went fine no errors or failed utilities, all of the
data went into the partitions it came out of..... Initially I was trying
to convert the tablespace to use a DSSIZE of 4G to allow for future growth
but the last test I did just put the definition back EXACTLY the same way
it's defined in production.

In all test cases when we try to update the foreign key of one of the
children of the dropped and recreated parent, the update fails with a -530
RI check problem. When I subsequently run a check data (scope all) on the
child every row in the child gets tossed. I've check the parent and the
corresponding key values exist in the parent for all of the child tables
rows. So there is no valid reason for the -530. The parent in question has
11 children but only those children with foreign key columns that are
nullable appear to cause the problem. I've been unable to recreate the
problem in our test system so either the problem is volume related or it's
an issue with children created under version 5/6 with parents created
under version 8.

I've raised this with IBM and it's with level 2 support at the moment.

What I need is any thoughts of how to work around this problem. We know
it's a bug but we may not have time to wait for a fix. Does anyone have
any ideas how I can make this tablespace larger without dropping and
recreating it? Compression is an option but we use log analyzer (CA) to
track updates and I don't believe this will work on a compressed table
(anyone from CA out there?). The obvious solution is DSSIZE 2G or higher
but I don't think it's possible to do that without dropping and recreating
the tablespace. Any help would be gratefully received.

Thanks,
Paul

************************************************************
Opinions contained in this e-mail do not necessarily reflect
the opinions of the Queensland Department of Main Roads,
Queensland Transport or Maritime Safety Queensland, or
endorsed organisations utilising the same infrastructure.
If you have received this electronic mail message in error,
please immediately notify the sender and delete the message
from your computer.
************************************************************

---------------------------------------------------------------------------------

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

Paul Fegan

Re: RI problems
(in response to Carol Anne Sutfin)
Carol,

I didn't drop the RI constraints explicitly but when the parent is dropped
the foreign keys would also have been removed. They aren't defined as
DELETE CASCADE and they were redefined after the parent was recreated and
reloaded.

Paul



Carol Sutfin <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
11/01/2007 09:45 AM
Please respond to
DB2 Database Discussion list at IDUG <[login to unmask email]>


To
[login to unmask email]
cc

Subject
Re: [DB2-L] RI problems





Paul,

If the table you dropped was the parent table,
Did you first Alter the child tables to remove the foreign key references
first,
and if not were those references DELETE CASCADE.
And have you created them again.

If the table you dropped was the child table
did you remember to add back the references or included them in the table
define.

If the table you dropped was both a parent and a child table
check both of these things.

Carol Sutfin
Corporate DBA
Regions Financial Corp.
(205)261-5214
[login to unmask email]



Paul Fegan
<[login to unmask email]
NSPORT.QLD.GOV.AU To
> [login to unmask email]
Sent by: DB2 Data cc
Base Discussion
List Subject
<[login to unmask email] [DB2-L] RI problems
ORG>


01/10/2007 04:48
PM


Please respond to
DB2 Database
Discussion list
at IDUG
<[login to unmask email]
2-L.ORG>






Hi All,

I need as much help as I can get with an RI problem we're having here.
While testing a database change in our production clone, I dropped and
recreated a partitioned tablespace. Using the usual method, unload the
data, drop and recreate the tablespace, load back all the data reinstate
the RI etc (I didn't do the check data's on the children as the data isn't
changing). everything went fine no errors or failed utilities, all of the
data went into the partitions it came out of..... Initially I was trying
to convert the tablespace to use a DSSIZE of 4G to allow for future growth
but the last test I did just put the definition back EXACTLY the same way
it's defined in production.

In all test cases when we try to update the foreign key of one of the
children of the dropped and recreated parent, the update fails with a -530
RI check problem. When I subsequently run a check data (scope all) on the
child every row in the child gets tossed. I've check the parent and the
corresponding key values exist in the parent for all of the child tables
rows. So there is no valid reason for the -530. The parent in question has
11 children but only those children with foreign key columns that are
nullable appear to cause the problem. I've been unable to recreate the
problem in our test system so either the problem is volume related or it's
an issue with children created under version 5/6 with parents created
under version 8.

I've raised this with IBM and it's with level 2 support at the moment.

What I need is any thoughts of how to work around this problem. We know
it's a bug but we may not have time to wait for a fix. Does anyone have
any ideas how I can make this tablespace larger without dropping and
recreating it? Compression is an option but we use log analyzer (CA) to
track updates and I don't believe this will work on a compressed table
(anyone from CA out there?). The obvious solution is DSSIZE 2G or higher
but I don't think it's possible to do that without dropping and recreating
the tablespace. Any help would be gratefully received.

Thanks,
Paul

************************************************************
Opinions contained in this e-mail do not necessarily reflect
the opinions of the Queensland Department of Main Roads,
Queensland Transport or Maritime Safety Queensland, or
endorsed organisations utilising the same infrastructure.
If you have received this electronic mail message in error,
please immediately notify the sender and delete the message
from your computer.
************************************************************

---------------------------------------------------------------------------------

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

Michael Ebert

Re: RI problems
(in response to Paul Fegan)
First of all, most likely DB2 is right and you're misreading something -
possibly something wasn't recreated just as equal as you think. Then, I'm
still not sure what exactly you did. If you add RI to a table that is not
empty, then the table will be placed in CHECKP (RO) until you run CHECK
DATA - which you say you didn't? To clarify things, it might help if you
specify the structure of the dropped/recreated table (parent you said),
all children (including # of rows), all involved indexes and all RI
definitions.

It wouldn't matter if the RIs were DELETE CASCADE. If you drop a table,
dependent RI relationships are dropped first, so there is no cascade
effect. Put another way, RI works only with DML, not with DDL.

Dr. Michael Ebert
DB2 & Oracle Database Administrator
aMaDEUS Data Processing
Erding / Munich, Germany




Paul Fegan <[login to unmask email]>
To
[login to unmask email]
cc

bcc

Subject
Re: [DB2-L] RI problems





Paul Fegan <[login to unmask email]>
Please respond to DB2 Database Discussion list at IDUG
<[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
11-01-07 01:43


Carol,

I didn't drop the RI constraints explicitly but when the parent is dropped

the foreign keys would also have been removed. They aren't defined as
DELETE CASCADE and they were redefined after the parent was recreated and
reloaded.

Paul



Carol Sutfin <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
11/01/2007 09:45 AM
Please respond to
DB2 Database Discussion list at IDUG <[login to unmask email]>



Paul,

If the table you dropped was the parent table,
Did you first Alter the child tables to remove the foreign key references
first,
and if not were those references DELETE CASCADE.
And have you created them again.

If the table you dropped was the child table
did you remember to add back the references or included them in the table
define.

If the table you dropped was both a parent and a child table
check both of these things.

Carol Sutfin
Corporate DBA
Regions Financial Corp.
(205)261-5214
[login to unmask email]



Paul Fegan
<[login to unmask email]
NSPORT.QLD.GOV.AU


Hi All,

I need as much help as I can get with an RI problem we're having here.
While testing a database change in our production clone, I dropped and
recreated a partitioned tablespace. Using the usual method, unload the
data, drop and recreate the tablespace, load back all the data reinstate
the RI etc (I didn't do the check data's on the children as the data isn't
changing). everything went fine no errors or failed utilities, all of the
data went into the partitions it came out of..... Initially I was trying
to convert the tablespace to use a DSSIZE of 4G to allow for future growth
but the last test I did just put the definition back EXACTLY the same way
it's defined in production.

In all test cases when we try to update the foreign key of one of the
children of the dropped and recreated parent, the update fails with a -530
RI check problem. When I subsequently run a check data (scope all) on the
child every row in the child gets tossed. I've check the parent and the
corresponding key values exist in the parent for all of the child tables
rows. So there is no valid reason for the -530. The parent in question has
11 children but only those children with foreign key columns that are
nullable appear to cause the problem. I've been unable to recreate the
problem in our test system so either the problem is volume related or it's
an issue with children created under version 5/6 with parents created
under version 8.

I've raised this with IBM and it's with level 2 support at the moment.

What I need is any thoughts of how to work around this problem. We know
it's a bug but we may not have time to wait for a fix. Does anyone have
any ideas how I can make this tablespace larger without dropping and
recreating it? Compression is an option but we use log analyzer (CA) to
track updates and I don't believe this will work on a compressed table
(anyone from CA out there?). The obvious solution is DSSIZE 2G or higher
but I don't think it's possible to do that without dropping and recreating
the tablespace. Any help would be gratefully received.

Thanks,
Paul

---------------------------------------------------------------------------------
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

Paul Fegan

Re: RI problems
(in response to Michael Ebert)
All,

We got a fix for the problem from IBM (PK34002/UK19884). The PTF in
question reads as a problem to do with on the check data utility but it's
a bit more painful than that. If you have RI defined on your tables and
the table was created under V6 of DB2 (maybe V7 as well) and you need to
drop and recreate one of the parents then you may hit this problem. This
is process we followed which exposed the problem.


Parent and child created on V6 of DB2
Child table had a nullable foreign key defined pointing to the parent
Unload parent
Drop parent and recreate with DSSIZE specified
Reload parent
Redefine Foreign Keys
image copy
Repair set no check pend on all children and the parent
Runstats and rebind

At this point a check data on any of the children that have nullable
Foreign Keys pointing at the parent will fail tossing all of the data in
the child. The RI is however only partially broken. All the children that
have foreign keys defined as not null still work and if you add a new row
to the parent and point an existing row in the child at the new parent row
it works. Any update to the child pointing existing child rows to old but
existing parent rows will fail.

If you use RI extensively, I would recommend that you get this PTF on
ASAP. It's very new and isn't contained in the December RSU as it was only
released on the 6th of December.

Paul




Michael Ebert <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
11/01/2007 05:27 PM
Please respond to
DB2 Database Discussion list at IDUG <[login to unmask email]>


To
[login to unmask email]
cc

Subject
Re: [DB2-L] RI problems





First of all, most likely DB2 is right and you're misreading something -
possibly something wasn't recreated just as equal as you think. Then, I'm
still not sure what exactly you did. If you add RI to a table that is not
empty, then the table will be placed in CHECKP (RO) until you run CHECK
DATA - which you say you didn't? To clarify things, it might help if you
specify the structure of the dropped/recreated table (parent you said),
all children (including # of rows), all involved indexes and all RI
definitions.

It wouldn't matter if the RIs were DELETE CASCADE. If you drop a table,
dependent RI relationships are dropped first, so there is no cascade
effect. Put another way, RI works only with DML, not with DDL.

Dr. Michael Ebert
DB2 & Oracle Database Administrator
aMaDEUS Data Processing
Erding / Munich, Germany




Paul Fegan <[login to unmask email]>
To
[login to unmask email]
cc

bcc

Subject
Re: [DB2-L] RI problems





Paul Fegan <[login to unmask email]>
Please respond to DB2 Database Discussion list at IDUG
<[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
11-01-07 01:43


Carol,

I didn't drop the RI constraints explicitly but when the parent is dropped

the foreign keys would also have been removed. They aren't defined as
DELETE CASCADE and they were redefined after the parent was recreated and
reloaded.

Paul



Carol Sutfin <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
11/01/2007 09:45 AM
Please respond to
DB2 Database Discussion list at IDUG <[login to unmask email]>



Paul,

If the table you dropped was the parent table,
Did you first Alter the child tables to remove the foreign key references
first,
and if not were those references DELETE CASCADE.
And have you created them again.

If the table you dropped was the child table
did you remember to add back the references or included them in the table
define.

If the table you dropped was both a parent and a child table
check both of these things.

Carol Sutfin
Corporate DBA
Regions Financial Corp.
(205)261-5214
[login to unmask email]



Paul Fegan
<[login to unmask email]
NSPORT.QLD.GOV.AU


Hi All,

I need as much help as I can get with an RI problem we're having here.
While testing a database change in our production clone, I dropped and
recreated a partitioned tablespace. Using the usual method, unload the
data, drop and recreate the tablespace, load back all the data reinstate
the RI etc (I didn't do the check data's on the children as the data isn't
changing). everything went fine no errors or failed utilities, all of the
data went into the partitions it came out of..... Initially I was trying
to convert the tablespace to use a DSSIZE of 4G to allow for future growth
but the last test I did just put the definition back EXACTLY the same way
it's defined in production.

In all test cases when we try to update the foreign key of one of the
children of the dropped and recreated parent, the update fails with a -530
RI check problem. When I subsequently run a check data (scope all) on the
child every row in the child gets tossed. I've check the parent and the
corresponding key values exist in the parent for all of the child tables
rows. So there is no valid reason for the -530. The parent in question has
11 children but only those children with foreign key columns that are
nullable appear to cause the problem. I've been unable to recreate the
problem in our test system so either the problem is volume related or it's
an issue with children created under version 5/6 with parents created
under version 8.

I've raised this with IBM and it's with level 2 support at the moment.

What I need is any thoughts of how to work around this problem. We know
it's a bug but we may not have time to wait for a fix. Does anyone have
any ideas how I can make this tablespace larger without dropping and
recreating it? Compression is an option but we use log analyzer (CA) to
track updates and I don't believe this will work on a compressed table
(anyone from CA out there?). The obvious solution is DSSIZE 2G or higher
but I don't think it's possible to do that without dropping and recreating
the tablespace. Any help would be gratefully received.

Thanks,
Paul

---------------------------------------------------------------------------------
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



************************************************************
Opinions contained in this e-mail do not necessarily reflect
the opinions of the Queensland Department of Main Roads,
Queensland Transport or Maritime Safety Queensland, or
endorsed organisations utilising the same infrastructure.
If you have received this electronic mail message in error,
please immediately notify the sender and delete the message
from your computer.
************************************************************


---------------------------------------------------------------------------------
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

Paul Fegan

Re: RI problems
(in response to Paul Fegan)
All,

We got a fix for the problem from IBM (PK34002/UK19884). The PTF in
question reads as a problem to do with on the check data utility but it's
a bit more painful than that. If you have RI defined on your tables and
the table was created under V6 of DB2 (maybe V7 as well) and you need to
drop and recreate one of the parents then you may hit this problem. This
is process we followed which exposed the problem.


Parent and child created on V6 of DB2
Child table had a nullable foreign key defined pointing to the parent
Unload parent
Drop parent and recreate with DSSIZE specified
Reload parent
Redefine Foreign Keys
image copy
Repair set no check pend on all children and the parent
Runstats and rebind

At this point a check data on any of the children that have nullable
Foreign Keys pointing at the parent will fail tossing all of the data in
the child. The RI is however only partially broken. All the children that
have foreign keys defined as not null still work and if you add a new row
to the parent and point an existing row in the child at the new parent row
it works. Any update to the child pointing existing child rows to old but
existing parent rows will fail.

If you use RI extensively, I would recommend that you get this PTF on
ASAP. It's very new and isn't contained in the December RSU as it was only
released on the 6th of December.

Paul




Michael Ebert <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
11/01/2007 05:27 PM
Please respond to
DB2 Database Discussion list at IDUG <[login to unmask email]>


To
[login to unmask email]
cc

Subject
Re: [DB2-L] RI problems





First of all, most likely DB2 is right and you're misreading something -
possibly something wasn't recreated just as equal as you think. Then, I'm
still not sure what exactly you did. If you add RI to a table that is not
empty, then the table will be placed in CHECKP (RO) until you run CHECK
DATA - which you say you didn't? To clarify things, it might help if you
specify the structure of the dropped/recreated table (parent you said),
all children (including # of rows), all involved indexes and all RI
definitions.

It wouldn't matter if the RIs were DELETE CASCADE. If you drop a table,
dependent RI relationships are dropped first, so there is no cascade
effect. Put another way, RI works only with DML, not with DDL.

Dr. Michael Ebert
DB2 & Oracle Database Administrator
aMaDEUS Data Processing
Erding / Munich, Germany




Paul Fegan <[login to unmask email]>
To
[login to unmask email]
cc

bcc

Subject
Re: [DB2-L] RI problems





Paul Fegan <[login to unmask email]>
Please respond to DB2 Database Discussion list at IDUG
<[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
11-01-07 01:43


Carol,

I didn't drop the RI constraints explicitly but when the parent is dropped

the foreign keys would also have been removed. They aren't defined as
DELETE CASCADE and they were redefined after the parent was recreated and
reloaded.

Paul



Carol Sutfin <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
11/01/2007 09:45 AM
Please respond to
DB2 Database Discussion list at IDUG <[login to unmask email]>



Paul,

If the table you dropped was the parent table,
Did you first Alter the child tables to remove the foreign key references
first,
and if not were those references DELETE CASCADE.
And have you created them again.

If the table you dropped was the child table
did you remember to add back the references or included them in the table
define.

If the table you dropped was both a parent and a child table
check both of these things.

Carol Sutfin
Corporate DBA
Regions Financial Corp.
(205)261-5214
[login to unmask email]



Paul Fegan
<[login to unmask email]
NSPORT.QLD.GOV.AU


Hi All,

I need as much help as I can get with an RI problem we're having here.
While testing a database change in our production clone, I dropped and
recreated a partitioned tablespace. Using the usual method, unload the
data, drop and recreate the tablespace, load back all the data reinstate
the RI etc (I didn't do the check data's on the children as the data isn't
changing). everything went fine no errors or failed utilities, all of the
data went into the partitions it came out of..... Initially I was trying
to convert the tablespace to use a DSSIZE of 4G to allow for future growth
but the last test I did just put the definition back EXACTLY the same way
it's defined in production.

In all test cases when we try to update the foreign key of one of the
children of the dropped and recreated parent, the update fails with a -530
RI check problem. When I subsequently run a check data (scope all) on the
child every row in the child gets tossed. I've check the parent and the
corresponding key values exist in the parent for all of the child tables
rows. So there is no valid reason for the -530. The parent in question has
11 children but only those children with foreign key columns that are
nullable appear to cause the problem. I've been unable to recreate the
problem in our test system so either the problem is volume related or it's
an issue with children created under version 5/6 with parents created
under version 8.

I've raised this with IBM and it's with level 2 support at the moment.

What I need is any thoughts of how to work around this problem. We know
it's a bug but we may not have time to wait for a fix. Does anyone have
any ideas how I can make this tablespace larger without dropping and
recreating it? Compression is an option but we use log analyzer (CA) to
track updates and I don't believe this will work on a compressed table
(anyone from CA out there?). The obvious solution is DSSIZE 2G or higher
but I don't think it's possible to do that without dropping and recreating
the tablespace. Any help would be gratefully received.

Thanks,
Paul

---------------------------------------------------------------------------------
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



************************************************************
Opinions contained in this e-mail do not necessarily reflect
the opinions of the Queensland Department of Main Roads,
Queensland Transport or Maritime Safety Queensland, or
endorsed organisations utilising the same infrastructure.
If you have received this electronic mail message in error,
please immediately notify the sender and delete the message
from your computer.
************************************************************


---------------------------------------------------------------------------------
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