Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2004
    Posts
    268

    Unanswered: SQL0290N Table space access is not allowed.

    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.

  2. #2
    Join Date
    Jan 2003
    Posts
    1,605
    Quote Originally Posted by mdx34
    "SQL0290N Table space access is not allowed. SQLSTATE=55039" error.
    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.

  3. #3
    Join Date
    Oct 2004
    Posts
    268
    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 ?

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Try reading the manual about the LOAD command options. In your case you might be interested in the COPY YES and NONRECOVERABLE options.
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Oct 2004
    Posts
    268
    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............

  6. #6
    Join Date
    Oct 2004
    Posts
    268
    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.

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You said it yourself above already:
    I ran the "db2 list tablespaces" which gave me "0x0020" on userspace1 table space (Table space backup pending).
    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).
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  8. #8
    Join Date
    Apr 2008
    Posts
    51
    Either include NONRECOVERABLE keyword on your LOAD command or take a backup after LOAD.
    db2topgun.com

Posting Permissions

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