Hi all,

I have been given this nice little problem where a DB2 EXPLAIN statement is being run against a table but returning the following error...


db2 explain all with snapshot for "select count(*) from cbf.pt_register_header"

SQL0668N Operation not allowed when the underlying table (or a dependent
table) is in the Check Pending state. SQLSTATE=57016


I have checked the tables that the explain is trying to access and none are in check pending. I then searched the SYSCAT.TABLES for any table in the db with a STATUS = 'C' and there are none at all. I have tried ignoring this and running a SET INTEGRITY but that also fails saying that the table is not in check pending (not surprised really!!).

Has anybody seen this before and got around it ??

Thanks,

Hayden

Versions are as follows... DB2 7.2.5 (fp7) on NT (Win 2000, sp4)

Hi,
Can u please try doing the SET Integrity with this option,

db2 set integrity for <schema.tablename> materialized query, generated column, foreign key, staging, check immediate unchecked

Probably this would solve your problem.

regards

Nitin.

To add to what Nitin said,

If multiple tables are in Check Pending Status, you will have to do in a specifc sequence ... So, if you do set integrity for one table, and it still complains, go to the next and come back to it in the next cycle

HTH

Sathyaram

Thanks guys but the problem is that none of these tables are in check pending even though the DB2 error claims they are. The SYSCAT.TABLES has no tables with STATUS = 'C'. Tried the various SET INTEGRITY cmd's again but still the same. This is a bogus error I think??

Any messages in the db2diag.log ?

Sathyaram

Only backup messages in the db2diag.log for the past three days and no traps or dumps produced either... would rather not recreate the explain tables but could that be an option??

Is this different than the mainframe?

Check Pending is at the tablespace level...

Is there a DB2 Command processor for client server?

And there's usually a reason why it's in check pending..did a load fail?

Can't you run DB2 commands like

Try doing:
SET INTEGRITY FOR cpf.pt_register_header OFF;
SET INTEGRITY FOR cpf.pt_register_header IMMEDIATE CHECKED;

Using OFF will put the table in check pending and then you will definately be able to issue the SET INTEGRITY command. After which, try running your statement again.

Yes there is a command line for UDB but the cmd's are slightly different.

There wasn't a load and both the table & tablespace are fully accessible for normal usage, it's only the EXPLAIN tools that fail.

I have put the table into a check pending state using the SET INTEGRITY OFF then removed it again with CHECK IMMEDIATE UNCHECKED, verifying the status in syscat.tables at each step but the EXPLAIN still fails with a check pending... this is a crap error if ever there was one!!

Hi,

This is a wild guess that I am taking so it may or may not work. What i feel is that probably the system oriented data in your explain tables is not accessible due to what so ever reason.

Request you to please re-run the explain.ddl from the SQLLIB\misc directory and see if it works.

Hope it works!!!!

Nitin.