Results 1 to 13 of 13
  1. #1
    Join Date
    Jul 2003
    Posts
    6

    Angry Unanswered: Tablespce unauthorized

    Hi,
    My DB2 version is 7.2.6 on Aix 5.1
    I can't select any data on a tablespace wich is created with automatics parameters "USERSPACE1"
    I'm beginer in DB2, Could you help me to solve this problem.

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

    Re: Tablespce unauthorized

    Please post the error message ....

    Cheers
    Sathyaram

    Originally posted by spincema
    Hi,
    My DB2 version is 7.2.6 on Aix 5.1
    I can't select any data on a tablespace wich is created with automatics parameters "USERSPACE1"
    I'm beginer in DB2, Could you help me to solve this problem.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

    Please also read the Must Read before posting theme for useful tips
    http://dbforums.com/t854783.html

    Hope this helps,
    Grofaty

  4. #4
    Join Date
    Jul 2003
    Posts
    6

    Re: Tablespce unauthorized

    Error Message :

    SQL0290N Acces unauthorized SQLSTATE=55039


    Originally posted by sathyaram_s
    Please post the error message ....

    Cheers
    Sathyaram

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

    Re: Tablespce unauthorized

    connect to the database and do

    db2 list tablespaces show detail

    and post the output ...

    The problem is that the tablespace is not in 'Normal' State ...

    When you get an error, refer message reference for more info ..... You can also do

    db2 ? <err>
    eg: db2 ? SQL0290N

    H:\>db2 ? SQL0290N

    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.

    o 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.

    o 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.

    o A system or user temporary table space cannot be dropped
    which contains active system or declared temporary tables.

    o 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:

    o 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.

    o 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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  6. #6
    Join Date
    Jul 2003
    Posts
    6
    I'm sorry but the administration guide does'nt offer any help about this subject.
    I launched thix command ALTER TABLESPACE <tablespacename> SWITCH ONLINE but nothing change.

  7. #7
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    But, what was the state of the tablespace ? (listed by db2 list tablespaces command)

    Cheers

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  8. #8
    Join Date
    Jul 2003
    Posts
    6
    I'm sorry but i don't know how to launch this command. My tools are DB Control Center and DB Command Center.

  9. #9
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    On the command centre, connect to the database and then issue

    list tablespaces

    command


    Cheers

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  10. #10
    Join Date
    Jul 2003
    Posts
    6

    List of table space

    Hi,
    This is the return of the command (in french, sorry) when all my tablespaces are availables.
    I would like to precise that the problem is the same if i create the tablespace with the "system management" or a "manual management".
    In the case i create my tablespace with "manual management" i give a space of 40Mo for the contener. The tablespace contains 10 tables with no more than 30 records. But i always have this problem.
    Someone of my company told me that the problem could come from the number of transaction on the table of the tablespace. It's true that we are in a development phasis but i don't think that 1000 transactions per day can't be managed. Do i have to reorganise the tablespace each nigth, and how to do ?
    Thank you very much for your help.


    Return
    ************************************************** **********
    Espaces table pour base de données en cours



    ID espace table = 0

    Nom = SYSCATSPACE

    Type = Espace SMS

    Contenu = Toutes les données

    Etat = 0x0000

    Informations détaillées :

    Normal



    ID espace table = 1

    Nom = TEMPSPACE1

    Type = Espace SMS

    Contenu = Données temporaires système

    Etat = 0x0000

    Informations détaillées :

    Normal



    ID espace table = 2

    Nom = SDTORSF

    Type = Espace DMS

    Contenu = Toutes les données

    Etat = 0x0000

    Informations détaillées :

    Normal



    ID espace table = 3

    Nom = SDTPCAL

    Type = Espace DMS

    Contenu = Toutes les données

    Etat = 0x0000

    Informations détaillées :

    Normal



    ID espace table = 4

    Nom = SDTCOID

    Type = Espace DMS

    Contenu = Toutes les données

    Etat = 0x0000

    Informations détaillées :

    Normal



    ID espace table = 5

    Nom = SDTEVEN

    Type = Espace DMS

    Contenu = Toutes les données

    Etat = 0x0000

    Informations détaillées :

    Normal

    ************************************************** **********

  11. #11
    Join Date
    Mar 2003
    Posts
    343
    All the tablespaces listed above are in a normal state. However, none of them seem to have the name userspace1 which is the one the post seems to be about. Also, tablespace access not allowed is not about the tablespace running out of space - it is about the state of the tablespace. How is the table in question loaded? Are the users using autoloader? If so ensure that the non-recoverable parameter is used - this will ensure that the tablespace is not in a backup pending state. The other thing that occurs to mind is that someone loaded one of the tables in the tablespace, the load failed or stalled and left the table in quiesced exclusive state. One way to test out which one it is is by the list tablespaces command. Another way is to issues a select against one of the tables - if you continue to get the error then the tablespace was quiesced - if you can access data with a select statement but cannot load/insert data, then the tablespace is in backup pending.

    Hope this helps.

  12. #12
    Join Date
    Jul 2003
    Posts
    6

    Non-recoverable database ?

    Thank you for your explanations but i've got no skills in DB2 for the moment and i don't know the commands wich allow me to know if my database is recoverable or not.
    Our users don't use autoloader
    I think that my tablespace locks the table in quiesced exclusive state because i can't select any data on it. In that case what to do to in order to make the table un-quiesced.
    Thank you very much for your advises and explanations.

  13. #13
    Join Date
    Mar 2003
    Posts
    343
    If you do not know who quiesced the tablespace, it will be difficult to release the lock. It depends on how important it is. If you require recoverability, issue a db2dart. If it is ok to loose the data and you have the ddl for bot table and tablespace, drop the tablespace, recreate it and create the table again.

Posting Permissions

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