Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2003
    Posts
    4

    Unanswered: DB2 EXPLAIN - Tables in check pending

    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)

  2. #2
    Join Date
    Jul 2003
    Location
    Austin, TX, USA
    Posts
    278

    Check Pending.

    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.

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: Check Pending.

    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

    Originally posted by nitingm
    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.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  4. #4
    Join Date
    Nov 2003
    Posts
    4

    Re: Check Pending.

    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??

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: Check Pending.

    Any messages in the db2diag.log ?

    Sathyaram

    Originally posted by Hayden Lawrence
    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??
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  6. #6
    Join Date
    Nov 2003
    Posts
    4

    Re: Check Pending.

    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??

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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

    Code:
    -START DB(SLD000DA) SPACE(XPKFOOTR) ACCESS(FORCE)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Jul 2002
    Posts
    3
    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.

  9. #9
    Join Date
    Nov 2003
    Posts
    4
    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!!

  10. #10
    Join Date
    Jul 2003
    Location
    Austin, TX, USA
    Posts
    278

    Explicitly Run Explain.ddl

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •