Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2009

    Unanswered: SQL0290N Table space access not allowed

    Hi everyone.

    I did a search at the forum and found some threads regarding this topic, but any of them match the condition I have, then sorry for repeting. Let me tell you the whole story.

    We have a database running in a server that will have to be shut down for hardware maintenence. This DB is configured with tasks to do online backups daily and also registers LOGs:

    The result of the task is:
    Backup successful. The timestamp for this backup image is : 20110526051638

    We can have complete access to all tablespaces and all tables are working, with all REORGS (weekly) and RUNSTATS (daily) updated.

    I've prepared a substitute server which will run until we fix the old one. Then, I copied the online backup of the day to the new server and also the log files (including LOGARCHIVE).

    So, I began testing. I did a restore from our daily online backup:
    db2=> restore db <mydb> from f:\ taken at 20110526051638 replace history file comprlib "C:\Program Files\IBM\SQLLIB\BIN\db2compr.dll"

    After completion, I received the folloowing message:
    'SQL2563W The restore process has completed successfully, but one or more table spaces from the backup were not restored.'

    An attempt to view the tables at the Control Center gives me:
    '[IBM][CLI Driver] SQL1117N A connection to or activation of
    database "PRDTIBCO" cannot be made because of ROLL-FORWARD

    So I proceed to the rollforward:
    db2=> rollforward db prdtibco to end of logs

    And after that:
    db2=> rollforward db prdtibco stop

    And I get this from both commands:
    'SQL1271W Database "MYDB" is recovered but one or more table spaces are off-line on node(s) "0".'

    So I refresh the Control Center screen and finally get access to my database.
    Now I can access tables at the Schema SYSIBM and SYSTOOLS. Though, when I try to access the tables on my working schema I get:
    'SQL0290N Table space access is not allowed. SQLSTATE=55039'

    Then I proceed:
    db2=> connect to <MYDB>
    db2=> list tablespaces show detail

    I get the list of tablespaces where all are "normal" except the one where my working table resides:
    Tablespace ID = 2
    Name = USERSPACE1
    Type = Database managed space
    Contents = All permanent data. Large table space.
    State = 0x2001100
    Detailed explanation:
    Restore pending
    Storage must be defined
    Storage may be defined

    That's where I stop and call for your help.
    What should I do next so I regain access to my table at the substitute server?

    thanks in advance,

    P.S.: We tried HADR but gave up due to the enormous quantity of LOG files generated. It just ran us out of disk space.

  2. #2
    Join Date
    Jun 2003
    Toronto, Canada
    Provided Answers: 1
    I'd say that the path (or paths) where the USERSPACE1 containers are located on the old server are not available on the new one. If that's the case, you may need to do a redirected restore.

  3. #3
    Join Date
    Dec 2009
    Thanks! Will check that and return.

  4. #4
    Join Date
    Dec 2009
    You were right!

    But the problem goes a little further.

    The name of the instance installed in the old server was 'DB2TIBCO'. I did not take part in this process, because it was created before I was assigned to this job.

    The new server, on the other hand, was configured by me, and I found it curious that the installation process would not let me choose a name for the DB instance. It is always already showed as 'DB2' and I could not change it.

    This fact alone is responsible for this difference in paths and the difficulties I encountered doing backup and restore.

    So my solution took me more time, but worked well. I just exported the DB for delimited files and imported into the new server. Takes more time, but the results were OK. And now I have the server configured my way, up and running.

    thanks a lot

  5. #5
    Join Date
    Feb 2013

    I know this is the old thread. I have the same problem as you described. What did you mean when you say "I just exported the DB for delimited files and imported into the new server". Can you please explain a bit in more details?


  6. #6
    Join Date
    Feb 2012
    I also had the same messages when there is not enough storage on the disks to completely restore the database. So, be sure to have a few GB free...
    When I had this issue I also had around 2 GB free, but still it was not enough to restore the database in full, with all tablespaces.

  7. #7
    Join Date
    Feb 2013
    You are right. I did restore in a different way and realized that I didn't have enough disk space. Thanks.

Posting Permissions

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