DB2 V8.1 FP14 RHL3.0

We are having problems for the last 2 days after we enabled the ARCHIVE LOGING last sunday. We have an off-line backup at 23:00 PM then we have database load (1 table only - Not too large) at 4:00 AM. After the load, at 4:30 AM we run a stored procedure which selects particular data from the table loaded at 4:00 AM and populates other tables (20 or so tables).

This morning at 11:30 AM, Client could not logon to application that connects to database because of

"SQL0290N Table space access is not allowed. SQLSTATE=55039" error.

Does the load or the stored proc that distributes the data locks the table space into,

db2tbst 0x0020 (backup pending) mode ? What else can put the Table Space into backup pending mode ?

It seemed like it was working this morning.

Do I need to do anything after the table load on a database with ARCHIVE LOGING turned on ?

I run a query to see if any of the tables are in "Check Pending" status but there were none.

The only error in the db2diag.log file is:


2008-04-01-11.12.09.776177-240 I3314821G348 LEVEL: Severe
PID : 29698 TID : 3053605760 PROC : db2bp
INSTANCE: db2inst1 NODE : 000
FUNCTION: DB2 UDB, database utilities, sqlubConnectDatabase, probe:1280
MESSAGE : DiagData
DATA #1 : Hexdump, 4 bytes
0xB57B6F94 : F5FB FFFF ....

2008-04-01-11.12.09.776498-240 I3315170G328 LEVEL: Error
PID : 29698 TID : 3053605760 PROC : db2bp
INSTANCE: db2inst1 NODE : 000
FUNCTION: DB2 UDB, database utilities, sqlubConnectDatabase, probe:1280
DATA #1 : Hexdump, 4 bytes
0xB57B6F94 : F5FB FFFF ....

2008-04-01-11.12.28.501030-240 I3315499G348 LEVEL: Severe
PID : 29698 TID : 3053605760 PROC : db2bp
INSTANCE: db2inst1 NODE : 000
FUNCTION: DB2 UDB, database utilities, sqlubConnectDatabase, probe:1280
MESSAGE : DiagData
DATA #1 : Hexdump, 4 bytes
0xB57B6F94 : F5FB FFFF ....

2008-04-01-11.12.28.501190-240 I3315848G328 LEVEL: Error
PID : 29698 TID : 3053605760 PROC : db2bp
INSTANCE: db2inst1 NODE : 000
FUNCTION: DB2 UDB, database utilities, sqlubConnectDatabase, probe:1280
DATA #1 : Hexdump, 4 bytes
0xB57B6F94 : F5FB FFFF ....

I will appreciate any help.

Archive logging with load commands produces this error.

Execute command:
db2 list tablespaces
and check for State. It is very possible that you need to do the set integrity or you need to do the backup of database. It depends what is the state of tablespace.

Thanks for a quick reply.............As I said before, there are no tables that is on "check pending" state.
I ran the "db2 list tablespaces" which gave me "0x0020" on userspace1 table space (Table space backup pending).

This is a production database and I can not backup tablespace while it is in use (At that point I have no choice because the clients can not get in).

I want to avoid this problem before clients get in the database period. If I have to backup tablespace/DB, has to be before 7:00 AM

Is this the only solution ?

Try reading the manual about the LOAD command options. In your case you might be interested in the COPY YES and NONRECOVERABLE options.

Thanks..............We are using "LOAD REPLACE" option. We want the load to be logged. What are my options after the load ?

I don't think "set integrity.........." would help since the table is not on check pending state. The only thing I can do is a backup (Full or tablespace). Any other solution ?

Thanks again............

Ok..........I found out that this is a default behavior. From IBM Load command site "LOAD with COPY NO on a recoverable database leaves the table space in a backup pending state"..............So that said it all.

Thanks every one.

You said it yourself above already:
The whole purpose of LOAD is to bypass logging because that gives you better performance. But if logging is bypassed, you cannot recover the database because the log information is mandatory for that. Thus, you can either create a COPY while loading the data, and this copy can be used for recovery purposes, or you take a backup after the load. In case of a subsequent failure, you can use this backup image for recovery.

Yet another option is to use IMPORT instead of LOAD. IMPORT runs regular INSERT statements (which causes triggers to be fired, verifies check constraints, and also writes log records).

Either include NONRECOVERABLE keyword on your LOAD command or take a backup after LOAD.