table in pending, how do I troubleshoot 1

[ddiamond]

When I try to select from a table, I am getting the following error:

SQL0668N Operation not allowed for reason code "1" on table "GENIUSDATA.NA_NAME_ADDRESS". SQLSTATE=57016

I believe this message means that the table is in a pending state, but how do I determine which state it is in?

I tried:
load from c:\gtd\data\na_name_address.txt of del terminate into geniusdata.na_name_address;

Got error:
SQL27902N LOAD RESTART/TERMINATE is not allowed on a table that is not in LOAD PENDING state.

So it is not in a load pending state. Does anyone have any other suggestions?

 

[ddiamond]

OK, I found the following IBM link. From it I pieced together the following query which appears to identify and fix all tables that have been left in a check pending state.

select 'set integrity for '||tabschema||'.'||tabname||' immediate checked ;' 
from syscat.tables 
where tabschema='GENIUSDATA' and not (status='N' and access_mode='F');

It appears to have work. In total I had 12 tables in a check pending state. Can anyone tell me what puts a table into a check pending state?

 

[gregsimpson]

ddiamond,

IBM link below, explaining how you got that status

http://publib.boulder.ibm.com/infoc...topic=/com.ibm.db2.udb.doc/admin/c0004593.htm

Cheers
Greg

 

[ddiamond]

Greg,

Thanks for the link. It seems to suggest that this is caused by a load operation which violated some form of referential integrity. That was certainly the case with one of the tables I loaded, but I'm puzzled by the other 11. I never loaded them.

- Dan

 

[ddiamond]

I think I figured it out. Those 11 other tables were descendant tables. I never specified in my load command whether or not the check pending state should cascade to descendants, so I am guessing by default it will.