Using the DSNUPROC Load utility, I am loading a specific partition table space for a DB2 table that has 100 partitions. (i,e. I am loading partiton 009). This table has two child tables that are controlled by referential-integrity. Both of these child tables also have 100 partitions each. I am finding that after I load the single partition for the parent table, all 100 partitions table-spaces for each of the child tables, are in check-pending status. I would have hoped that only the corresponding partition table-space (i.e. 009) for the parent table partition that I loaded would be in check-pending status. For example, if partition 009 was loaded for the parent, then I would have hoped that only the partition 009 children table spaces would be in check-pending status. This unfavorable results means that in order to remove the check-pending status, the DBA has to intervene, or I have to re-load all 100 partitions for the child tables as opposed to just reloading the corresponding partition for the child tables. Is there something that I am coding wrong on my load syntax to render this unfavorable result ? Or is there something that may set up incorrectly in the database.

Here is my load statement: (assume I a loading partition 009)

LOAD DATA INDDN UNLDDSNA REUSE LOG NO NOCOPYPEND
ERRDDN SYSERR DISCARDDN SYSDISC ENFORCE CONSTRAINTS
INTO TABLE PTESTA1.SROP PART 009 REPLACE
etc. etc. (<== here is where the column value specifics begin)

The above was also tried without the REUSE statement and the same results occurred.

Any assistance would be appreciated.

db2dcs

db2dcs, You don't mention which version of DB2 or the operating system, but I will assume z/OS from the Load syntax.

My guess is that because the RI rule is based on column(s) and the column(s) are in all partitions, the entire Child table is put into Check Pending (not just individual partitions). I don't process this way and don't have first hand experience.

If you are positive that only one partition would be affected due to the partitioning key values, you can use the Repair utility to turn the Check Pending status on the table off and then Load Replace the one Partition you want to process.

The Check Utility (I believe) is another way to do this but it takes longer and requires another table to write the rows that violate RI. But it will ensure the integrity of your data.

Thank-you 'Stealth DBA'.

Sorry for not providing the environment specifices. I am having this problem with DB2 version 8 running on Z/OS.
I am a developer and at this shop, the ability to run the REPAIR utility or CHECK DATA utility - even in just our test environmnents, is usually only granted to DBA's. In fact, we just tried it moments ago in the testing environment, and as expected, it failed on an authority error. Similarly, we do not have the authority to do a FORCE START.
The partitioning rules for the children are the same primary-key column range of values used for the parent. So we are pretty sure that by just loading the same child partitions that were loaded for the parent, would be adequate. In fact, our DBA was also surprised that all child partitions were left in check-pending status. Furthermore, this 2002 IDUG link that I found (and which I display further below), reveals that someone else who encountered this problem was equally surprised and/or dissappointed. While I do see the argument for rendering all partitions in check-pending status, I also see a stronger argument - where assuming the right controls were in place, that one should only apply the Check Pending status to the child partition(s) that is/are directly sub-ordinate to the parent partition(s) that got loaded. This would be consistent with the partition isolationism that one wants to achieve by having partitions to begin with. Here is that IDUG link. The responders to the question posted on the link also could not provide a solution other than running the CHECK DATA option.
LISTSERV 15.5 - DB2-L Archives

DB2DCS,
Kind of funny going back to that post as I was one of the original responders. I stand by my statement from back then that RI and partitioning are separate beasts and that RI enforcement does not look at the underlying structure of the child(ren) tables to see if they have the same identical partitioning as the parent. Therefore, the entire child tablespace is placed into a CHECK PEND state and DB2 is working as designed. Granted that would be a nice upgrade and you could let your local IBM rep know that you would like to see that enhancement put on a wish list, but I wouldn't hold my breath. I'm just surprised your DBA staff allows you load authority, even if it is in test system. The only place we allow development staff load authority is in a single playground tablespace that is used by all. If someone does a load replace or anything of that sort they only upset the rest of the devlopment staff and do not interfere with any application (test or otherwise).
Dave Nance

Thanks Dave for your response.

I agree that one should not hold one's breath for IBM to introduce our wish for partition-synchronized R.I. between a parent and a child. In fact, since that IDUG thread where you responded was from the year 2002, we can guess that the originator of that thread probably also had that wish; and evidentally the wish has been unfulfilled for at least 7 years old.

However, based on a suggestion in that IDUG link to use 'RESUME YES', I rolled up my sleeves and created an automated process that does the following:

(1) empities out from the the children and parent, all rows in the specified partition in each child table. The method used for emptying the the children tables is "LOAD INTO TABLE abcdef PART xxx RESUME NO REPLACE YES; coupled with with a DUMMY input. The method used for emptying the parent is a COBOL-DB2 program that reads in all the records from the parent partition and then performs SQL deletes. Using a SQL delete statement will allow one to delete all pre-existing rows in the partition without rendering the check-pending status in the children.

(2) We then refresh the parent table partition using a LOAD INTO TABLE xxxyyy PART xxx RESUME YES method.

(3) That is followed by refreshing the respective child tables using LOAD INTO TABLE PART xxx RESUME NO REPLACE operations against the child tables - coupled with real input this time and not DUMMY input.

The various utility cards are automatically generated based on entering as parameters, the desired tables, partition-number, etc.; so the JCL procedure which does all this is quite volumnous. However, the results are worth it.

regards to everyone

db2dcs