Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Check pending status for a tablespace!

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
grayWolf

New User


Joined: 04 Oct 2010
Posts: 19
Location: Land of broken dreams

PostPosted: Mon Feb 21, 2011 4:54 pm    Post subject: Check pending status for a tablespace!
Reply with quote

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
View user's profile Send private message

GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1280
Location: Belgium

PostPosted: Mon Feb 21, 2011 5:29 pm    Post subject:
Reply with quote

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
View user's profile Send private message
grayWolf

New User


Joined: 04 Oct 2010
Posts: 19
Location: Land of broken dreams

PostPosted: Mon Feb 21, 2011 8:25 pm    Post subject:
Reply with quote

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
View user's profile Send private message
daveporcelan

Active Member


Joined: 01 Dec 2006
Posts: 662
Location: Pennsylvania

PostPosted: Mon Feb 21, 2011 8:45 pm    Post subject:
Reply with 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.

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
View user's profile Send private message
Ronald Burr

Active User


Joined: 22 Oct 2009
Posts: 293
Location: U.S.A.

PostPosted: Mon Feb 21, 2011 8:53 pm    Post subject:
Reply with quote

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
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1280
Location: Belgium

PostPosted: Mon Feb 21, 2011 8:58 pm    Post subject:
Reply with quote

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
View user's profile Send private message
grayWolf

New User


Joined: 04 Oct 2010
Posts: 19
Location: Land of broken dreams

PostPosted: Tue Feb 22, 2011 12:11 pm    Post subject:
Reply with quote

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 icon_biggrin.gif
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1280
Location: Belgium

PostPosted: Tue Feb 22, 2011 2:07 pm    Post subject:
Reply with quote

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
View user's profile Send private message
grayWolf

New User


Joined: 04 Oct 2010
Posts: 19
Location: Land of broken dreams

PostPosted: Tue Feb 22, 2011 3:18 pm    Post subject:
Reply with quote

Quote:
It is not a discussion, it is a failure of understanding.

Agreed!
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Check Binary Values for symbols gandikk CA Products 3 Tue Mar 21, 2017 5:11 am
This topic is locked: you cannot edit posts or make replies. session stuck in TPX with status ACL amitc23 CA Products 1 Tue Dec 13, 2016 12:53 pm
No new posts What is the command to check MODE of ... rohanthengal CLIST & REXX 7 Fri Nov 18, 2016 1:48 pm
No new posts Check System time(Minute) Using TIME1... balaji81_k DFSORT/ICETOOL 5 Fri Nov 11, 2016 10:53 am
No new posts How to resolve IMS DB status code FM Priyanka Boga IMS DB/DC 3 Fri Jun 10, 2016 1:54 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us