Results 1 to 14 of 14
  1. #1
    Join Date
    May 2003
    Posts
    369

    Unanswered: SQL0290N Table space access is not allowed. SQLSTATE=55039

    One of our development DB2 UDB 7.1 boxes crashed this morning and after the unix sysadmin rebooted the system I cannot connect to our DB2 database, get the following errors:

    db2 connect to database

    SQL0290N Table space access is not allowed. SQLSTATE=55039

    How can I resolve this problem so that I can reconnect to our database?
    Thanks!

    Scott

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    SQL0290N Table space access is not allowed.

    Explanation: A process attempted to access a table space which is in an invalid state for which the intended access is not allowed.

    - If the table space is in a quiesced state, only processes which also hold the table space in a quiesced state are allowed access to the table space.
    - If the table space is in any other state, only the process which is performing the action specified is allowed access to the table space.
    - A system or user temporary table space cannot be dropped which contains active system or declared temporary tables.
    - The SET CONTAINER api cannot be used to set the container list unless the table space is in a "restore pending" state.

    Details can be found in the system error log and/or the database manager error log.

    User Response: Possible actions include:

    - If the table space is in a quiesced state, attempt to acquire a quiesced share or quiesced update state on the table space. Or, attempt to quiesce reset the table space.
    - If the table space is in any other state, wait until the table space has returned to normal state before attempting to access the table space.

    Refer to the Administration Guide for further information about the table space states. sqlcode: -290 sqlstate: 55039

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: SQL0290N Table space access is not allowed. SQLSTATE=55039

    Do a list tablespae command to check the status of the tablespace ...

    Since you say, your database crashed and you have restarted, look into the db2diag.log file to check whether your crash recovery has completed successfully ... Also look at other messages now and messages at the time of crash(eg, a load running at the time of crash) ...

    HTH

    Sathyaram

  4. #4
    Join Date
    May 2003
    Posts
    369

    cannot connect to database!

    I cannot even connect to the database. what can I do to get database connectivity? We are running DB2 UDB 7.1 for AIX

    Thanks

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: cannot connect to database!

    I remember to have experienced this about 2 years back, when the dev machine was powered off ....

    It turned out to be that crash recovery took 'ages' to complete .....

    You will certainly need to check your db2diag.log ...

    Cheers

    Sathyaram

    Originally posted by mixxalot
    I cannot even connect to the database. what can I do to get database connectivity? We are running DB2 UDB 7.1 for AIX

    Thanks

  6. #6
    Join Date
    May 2003
    Posts
    369

    restore backup and recovery process tips

    I need to do a full backup and recovery any tips?

    thanks
    scott

  7. #7
    Join Date
    Mar 2003
    Posts
    343
    Do you have a previous full backup of the database? Is forward recovery enabled?

    If you are in EEE environment remember to copy out the db2nodes.cfg from sqllib and anything else which you may have had (like db2gov.cfg etc).

    Please copy the logs out of the active log directory/ies to another location before you start so that you have the original ones just in case.

    Try to ensure that you have a copy of dbm and db cfgs and db2set -all and the ddl, if that is possible(db2looks).

    If in EEE recover the catalog first.

    Then do a db2_all "<<-0< ..." restore for the other nodes.

    If recovering from an online backup, you will have to rollforward all nodes to a minimum point in time - do a rollforward query to determine what that point in time should be. Run both restores and rollforwards from the catalog node.

    Read the documentation before you proceed.

    Hope this helps a little. Good luck!

  8. #8
    Join Date
    May 2003
    Posts
    369

    restore fails for db2 with tsm

    # db2 "restore database mydatabase use TSM"
    SQL2062N An error occurred while accessing media
    "/home/db2myinstance1/sqllib/adsm/libadsm.a". Reason code: "406".
    db2inst1@av3: /usr/tivoli/tsm/client/ba/bin >

    has anyone had this problem with running a restore with db2 and tsm?

  9. #9
    Join Date
    Jun 2005
    Posts
    4
    one gets tired of these folks who say do this do that - when one cannot even connect to the database. How can one do restore if one can not connect to the database ?. How can one list tablespaces when one cannot connect to the database. BAH. Useless advice. MIXXALOT you have my sympathy. I too have a database I can not connect to and I appear to be able to do nothing with it.

  10. #10
    Join Date
    Jul 2002
    Location
    ATL
    Posts
    170
    I dont see a reason why you are getting pissed. This is a forum and people are only trying to help each other. I dont think people have intentions of giving a run around.

    If you want a accurate answer with limited details , open a PMR with IBM. They will give you accurate information as they are getting paid.

  11. #11
    Join Date
    Oct 2006
    Posts
    15
    Hi
    Procedure

    To restart a database with damaged table spaces:


    1. Invoke an unqualified restart database operation. It will succeed if there are no damaged table spaces. If it fails (SQL0290N), look in the administration notification log file for a complete list of table spaces that are currently damaged.
    2. If you are willing to drop all of the damaged table spaces, initiate another restart database operation, listing all of the damaged table spaces under the DROP PENDING TABLESPACES option. If a damaged table space is included in the DROP PENDING TABLESPACES list, the table space is put into drop pending state, and you must drop the table space after the recovery operation is complete.

    The restart operation continues without recovering the specified table spaces. If a damaged table space is not included in the DROP PENDING TABLESPACES list, the restart database operation fails with SQL0290N.

    Note:

    Including a table space name in the DROP PENDING TABLESPACES list does not mean that the table space will be in drop pending state. It will be placed in this state only if the table space is found to be damaged during the restart operation.
    3. If the restart database operation is successful, invoke the LIST TABLESPACES command to find out which table spaces are in drop pending state.
    4. Issue DROP TABLESPACE statements to drop each of the table spaces that are in drop pending state. Once you have done this, you will be able to reclaim the space that the damaged table spaces were using or recreate the table spaces.
    5. If you are unwilling to drop and lose the data in the damaged table spaces, you can:
    * Fix the damaged containers (without losing the original data).
    * Reissue the RESTART DATABASE command.
    * Perform a database restore operation.

    Regards
    Rajesh Krishnan

  12. #12
    Join Date
    Nov 2010
    Posts
    1
    Thanks Rajesh...For detailing the procedure, I am very much in the same position (SQLSTATE=55039) where I need to to log in to a damaged database. You mention in your post:
    * Fix the damaged containers (without losing the original data).
    * Reissue the RESTART DATABASE command.
    * Perform a database restore operation.

    Can you explain a bit more about this option....as I dont want to drop the tablespaces because it seems like those are system tablespaces and dropping them would meen unrecoverable database.

  13. #13
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Rajesh hasn't posted on this forum in more than 3 years, so not sure if he is still around. If you have SQLSTATE=55039 the first thing to do is find out what STATE the tablespace is in such that access is not allowed. Do a "db2 list tablespaces show detail" to find the state of the tablespace in question. You may get a hex value for state, and you will have to translate that using the DB2 documentation (or try google 'db2 tablespace state').
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  14. #14
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    and if you cannot activate and/or connect to the database, you can try running db2dart with the /dtsf switch to dump tablespace information.

Posting Permissions

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