Hi,

I was experimenting with the load command and exported data from a small table to a del file, deleted everything from the table and then gave the load command to put the data back --

db2 load from tcontents.del of del "insert into tblname ( col1, col2)"

SQL3109N The utility is beginning to load data from file
"/home/db2inst1/tcontents.del".

SQL3500W The utility is beginning the "LOAD" phase at time "08/09/2011
20:01:44.080010".

SQL3519W Begin Load Consistency Point. Input record count = "0".

SQL3520W Load Consistency Point was successful.

SQL3110N The utility has completed processing. "10" rows were read from the
input file.

SQL3519W Begin Load Consistency Point. Input record count = "10".

SQL3520W Load Consistency Point was successful.

SQL3515W The utility has finished the "LOAD" phase at time "08/09/2011
20:01:44.637195".

SQL3500W The utility is beginning the "BUILD" phase at time "08/09/2011
20:01:44.640314".

SQL3213I The indexing mode is "REBUILD".

SQL3515W The utility has finished the "BUILD" phase at time "08/09/2011
20:01:44.687880".


Number of rows read = 10
Number of rows skipped = 0
Number of rows loaded = 10
Number of rows rejected = 0
Number of rows deleted = 0
Number of rows committed = 10



Then, if I try to select from the table-- I get this :

db2 "select * from tblname"

COL1 COL2
----------- -----------
SQL0668N Operation not allowed for reason code "1" on table
"db2inst1.tblname". SQLSTATE=57016


I looked up the error and found this --

The table is in the Set Integrity Pending No Access state. The integrity of the table is not enforced and the content of the table may be invalid. An operation on a parent table or an underlying table that is not in the Set Integrity Pending No Access state may also receive this error if a dependent table is in the Set Integrity Pending No Access state.

Execute the SET INTEGRITY statement with the IMMEDIATE CHECKED option on table table-name to bring the table out of the Set Integrity Pending No Access state. For a user maintained materialized query table, execute the statement with the IMMEDIATE UNCHECKED option instead of the IMMEDIATE CHECKED option.


So then I give this command to fix it --

db2 "set integrity for tblname IMMEDIATE CHECKED"
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0290N Table space access is not allowed. SQLSTATE=55039


What did I do wrong and how can i fix it? thanks!

Since the LOAD command is not logged, it places the affected tablespace into a BACKUP PENDING mode. You now must run a backup before you can access that tablespace.

For the future, you could use two options to avoid this:
- COPY YES - it will create a set of recovery data for the table (at the price of extra space and poorer performance) and the tablespace will not be placed into a BACKUP PENDING mode;
- NONRECOVERABLE - the tablespace will not be placed into a BACKUP PENDING mode, but you won't be able to recover the table if something goes wrong; you will have to drop and re-create it.

thanks, the odd thing is that I can access other tables that are in that tablespace. is this expected?

also, what options do you typically use when giving the load command? thanks!

It is if by access you mean SELECT.

It depends.

Thanks, I guess I was wondering if COPY and NONRECOVERABLE are options that are used regularly by others.. they both seem to have some major drawbacks.. but the state my tablespace is in right now is no good either.