Setting check pending status back for a tablespace.

gylbharat · Posted: Wed Jul 17, 2013 2:30 pm

Hi,

Recently, I added a foreign key on a table to make it dependent on a table - with delete rule as cascade delete.

After adding the foreign key the tablespace went into check pending status as expected.

I ran the check data utility with DELETE NO option to just check for any exceptions. There were around 10 stale records in the child table which did not had a matching parent.

Later, I forced the CHKP flag, and re-ran the check data utility with DELETE NO option, this time also 10 stale records were listed as expected but the tablespace was still in RW mode. I think the tablespace flag should have been reset to CHKP status. but this did not happen as expected.

Please let me know my if my understanding is correct? If the check data utility does not set the CHKP flag again, then how we can set the CHKP flag in case of RI violations?

Pandora-Box · Posted: Wed Jul 17, 2013 4:11 pm

What doesn Foreign key do then?

gylbharat · Posted: Wed Jul 17, 2013 5:10 pm

I did not understand.

sushanth bobby · Posted: Thu Jul 18, 2013 10:19 am

Tablespace had status just RW or RW,CHKP...

gylbharat · Posted: Thu Jul 18, 2013 1:39 pm

Hi,

I found the issue.. we recently migrated to v10 and there is a zparm which determines if the flag has to be set for CHECK DATA utility.

From Manual:
If the utility finds any violation of constraints, the table space that is checked is not
put into the CHECK-pending status. You can force the prior behavior, that a table
space is put into CHECK-pending status when violations or constraints are
detected, by specifying CHECK_SETCHKP=Y on the CHECK_SETCHKP system
parameter.

sushanth bobby · Posted: Thu Jul 18, 2013 3:46 pm

gylbharat,

You said in the beginning, you have FORCEd off the CHECK-PENDING. If this zParm setting is set, you never would have got check-pending in the first place.

Anyhow Interesting zPARM. Have you checked in your site, whether it is set or not.

I don't why somebody would want an inconsistent data and that too they don't want to know about it and convinced IBM to make a zParm for it.

Regards,
Sushanth

gylbharat · Posted: Thu Jul 18, 2013 6:52 pm

I added a foreign key... which set the flag to check pending...

This Zparm option only affects check data utility... yes at our site it is set to NO.

gylbharat · Posted: Thu Jul 18, 2013 6:53 pm

Check data will have RC as 04 if there are violations but will not put the table in restrictive state if this option is set as NO.