Load gives "Tablespace access not allowed" 2

[Dagon]

I have a script which:

drop indexes from a table
declares a cursor
loads from the cursor (using DB2 load)
re-creates the index

This worked fine on the development system under DB2 8.1. However, when I run it on the production system, the create index step fails with:

create unique index edwdmrt.xpfrodis01 on edwdmrt.tfrodis (room_facc_on_accom_unit_id, inventory_date_id, duration_id, named_date_offset_id)
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

Does anyone know what could be causing this ?

 

[gregsimpson]

Dagon,

did the load complete okay.. You've not been left in some utility like state from the load perhaps, maybe load pending etc. Have you run

"db2 list tablespaces"

and checked the state?

Look at the value in the state field and if it's anything other than the usual 0x0000, try the following

db2tbst xxxxxx

where xxxxxx is the value in the state field. This should at least tell you what state it's in.

Cheers
Greg

 

[Dagon]

The load completed successfully and I can even select data from the tables. The tablespaces are in a different state ( 0x0020), which is "backup pending".

 

[gregsimpson]

Dagon,

I'm interested in how this happened.

Were you using COPY NO or did you have logretain and userexit disabled?

Please let me know if you figure out what stopped it working in one environment, when it already worked in another.

Cheers for the star.
Greg

 

[Dagon]

Having consulted the documentation, it turns out I need to use the "NONRECOVERABLE" or "COPY YES" options to prevent the tablespace being put into a "backup pending" state. It didn't occur on the development system because we have parameters set differently there (logretain and userexit were disabled, as you said). I have changed the load commands to use NONRECOVERABLE because we don't need to recover these tables.