|
View previous topic :: View next topic
|
| Author |
Message |
grayWolf
New User
Joined: 04 Oct 2010
Posts: 19
Location: Land of broken dreams
|
|
|
|
Hey All,
There are two tables in two different tablespaces and yeah having a parent-child relationship.
My requirement was to make a mass update of the parent table and I achieved it by following the process of:
"1. unload from the table 2.use Sort to change the file according to my requirement 3. load the table back with the modified table."
This worked fine.
But the issue is the child table is going into "check-pending" status. I checked that using the command.
| Code: |
| -DISPLAY DB(database name) SPACE(tablespace name) CLAIMERS |
The result was:
| Code: |
NAME TYPE PART STATUS CONNID CORRID CLAIMINFO
-------- ---- ----- ----------------- -------- ------------ --------
TS name TS RW,CHKP |
What I thought was since am 'not' changing any key fields in the parent table, its fine to load the parent without taking care of the child.
So, what is the real cause of this?! |
|
| Back to top |
|
 |
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
db2 doesn't know that, does he?
If you are sure you can run a repair on the child.
Otherwise run a check |
|
| Back to top |
|
|
grayWolf
New User
Joined: 04 Oct 2010
Posts: 19
Location: Land of broken dreams
|
|
|
|
| Quote: |
| db2 doesn't know that, does he? |
Umm well, what I thought was if a child is not in sync with the parent, then a check pending status would be set.
and in my case since its not different (because the key fields aren't changed) then a CHKP shouldn't be set, ideally!!
I agree that this isnt the only situation when Check pending is set. i meant in the present context
Correct me if am wrong. |
|
| Back to top |
|
|
daveporcelan
Active Member
Joined: 01 Dec 2006
Posts: 792
Location: Pennsylvania
|
|
|
|
Did you use ENFORCE CONTRAINTS option while doing the LOAD?
If you did not (ENFORCE NO), then DB2 can not be sure the referential integrity has not been violated. The CHECK PENDING flag will be set.
This is no big deal, if you are sure your data is good, then RESET the flag.
We do this all the time. We load several hundred tables with enforce no, then reset ALL the check pending flags, no matter if they were set or not.
P.S. - We only do this in test databases. |
|
| Back to top |
|
|
Ronald Burr
Active User
Joined: 22 Oct 2009
Posts: 293
Location: U.S.A.
|
|
|
|
A brief search of the DB2 Utilities Manual provides this insight:
| DB2 Utility Manual wrote: |
| Suppose that you choose to replace the contents of the project table by using LOAD REPLACE. While doing that, you let LOAD enforce its referential and table check constraints, so that the project table contains only valid records at the end of the job; it is not in the CHECK-pending status. However, its dependent, the project activity table, is placed in CHECK-pending status: some of its rows might have project numbers that are no longer present in the project table. |
Ergo, it looks very much like what you have experienced is exactly what should be expected - the dependent table ends up as Check-Pending. |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
If you do a load replace, it might be that some childs get orphaned.
It doesn't check this during the load.
DB2 will not allow to have a unsecure status, so it puts the children in checkpending. |
|
| Back to top |
|
|
grayWolf
New User
Joined: 04 Oct 2010
Posts: 19
Location: Land of broken dreams
|
|
|
|
| Quote: |
Did you use ENFORCE CONTRAINTS option while doing the LOAD?
If you did not (ENFORCE NO), then DB2 can not be sure the referential integrity has not been violated. The CHECK PENDING flag will be set.
|
I haven't used this and we are going to run this job in Production environment. Some senior people decided against Resetting the Check pending flag from the JCL(no idea why!). So, we suggested telling the DBA to reset the status and bring back the table to RW in Prod.
This wouldn't be an issue or will it be?!
Coming to Ronald's explanation, as suggested in the manual
| Quote: |
| However, its dependent, the project activity table, is placed in CHECK-pending status: some of its rows might have project numbers that are no longer present in the project table. |
Its very true that the Child goes into CHKPND status if it has rows that are not present in the Parent. But in my case, the rows would be the same.
This topic is going into a discussion rather than a query, its a good learning  |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| grayWolf wrote: |
| the Child goes into CHKPND status if it has rows that are not present in the Parent. |
It is not "if it has", it is "because it may have". There is no way to know that without scanning the child table.
If DB2 would scan all the childs without asking a lot of cpu and I/O time would be wasted.
It is not a discussion, it is a failure of understanding. |
|
| Back to top |
|
|
grayWolf
New User
Joined: 04 Oct 2010
Posts: 19
Location: Land of broken dreams
|
|
|
|
| Quote: |
It is not a discussion, it is a failure of understanding.
|
Agreed! |
|
| Back to top |
|
|
|
|
