Results 1 to 14 of 14
  1. #1
    Join Date
    Dec 2002
    Location
    Madrid - Spain
    Posts
    422
    Provided Answers: 1

    Unanswered: cannot drop tablespaces temporary

    Hi colleagues,

    I create a tablespaces temporary for use in load, now i need to drop the tablespaces temporary and the system show me this error.
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0291N State transition not allowed on table space. SQLSTATE=55039

    Thank you for you help.
    DBA DB2 for LUW

  2. #2
    Join Date
    Dec 2002
    Location
    Madrid - Spain
    Posts
    422
    Provided Answers: 1
    I am using BD2 UDB V8 and O.S. AIX 5.3.

    Thank you.
    DBA DB2 for LUW

  3. #3
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by georgipa View Post
    I am using BD2 UDB V8 and O.S. AIX 5.3.

    Thank you.
    Long time since I've used V8, but I guess you can figure out the current state of the ts with:

    db2 list tablespaces show detail

    Are all the load operations finished?
    --
    Lennart

  4. #4
    Join Date
    Dec 2002
    Location
    Madrid - Spain
    Posts
    422
    Provided Answers: 1
    The Operation load has finish, this is the output of:
    db2 list tablespaces show detail |more

    Tablespace ID = 20
    Name = TEMPORAL2
    Type = System managed space
    Contents = System Temporary data
    State = 0x0000
    Detailed explanation:
    Normal
    Total pages = 713611
    Useable pages = 713611
    Used pages = 713611
    Free pages = Not applicable
    High water mark (pages) = Not applicable
    Page size (bytes) = 4096
    Extent size (pages) = 32
    Prefetch size (pages) = 32
    Number of containers = 1
    Minimum recovery time = 2012-05-23-06.29.25.000000

    I need drop this tablespaces.
    Thank you.
    DBA DB2 for LUW

  5. #5
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Check if there are any locks on any tables in that tablespace (and release them), or locks on the tablespace itself (then force those). Then retry.

  6. #6
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by georgipa View Post
    The Operation load has finish, this is the output of:
    db2 list tablespaces show detail |more

    Tablespace ID = 20
    Name = TEMPORAL2
    Type = System managed space
    Contents = System Temporary data
    State = 0x0000
    Detailed explanation:
    Normal
    Total pages = 713611
    Useable pages = 713611
    Used pages = 713611
    Free pages = Not applicable
    High water mark (pages) = Not applicable
    Page size (bytes) = 4096
    Extent size (pages) = 32
    Prefetch size (pages) = 32
    Number of containers = 1
    Minimum recovery time = 2012-05-23-06.29.25.000000

    I need drop this tablespaces.
    Thank you.
    Just guessing, do you have another 4K system temporary tablespace availible, or have that been dropped? In case this is the only one of it's kind, perhaps you can't drop it?
    --
    Lennart

  7. #7
    Join Date
    Dec 2002
    Location
    Madrid - Spain
    Posts
    422
    Provided Answers: 1
    yes, There are other tablespaces with page size 4k.
    Thank you.
    DBA DB2 for LUW

  8. #8
    Join Date
    Nov 2011
    Posts
    334
    Why your temporary tablespace does not free the space ?
    Useable pages = 713611
    Used pages = 713611
    Maybe it is in use by any other application..

  9. #9
    Join Date
    Dec 2002
    Location
    Madrid - Spain
    Posts
    422
    Provided Answers: 1
    Hi,
    the tablespace was created for a load of 555,000,000 rows in a table.
    how to know if other applications is using the tablespaces?
    Thank you.
    DBA DB2 for LUW

  10. #10
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    db2 get snapshot for tables on <dbname>

    this must have a list of temp tables (easy to identify) and the name will of the temp table will tell you the appl id.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  11. #11
    Join Date
    Dec 2002
    Location
    Madrid - Spain
    Posts
    422
    Provided Answers: 1
    excuse me,the temporary table structure you mention that you have?
    Thank you.
    DBA DB2 for LUW

  12. #12
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Quote Originally Posted by georgipa View Post
    excuse me,the temporary table structure you mention that you have?
    Thank you.
    what temporary table structure ?
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  13. #13
    Join Date
    Dec 2002
    Location
    Madrid - Spain
    Posts
    422
    Provided Answers: 1
    In one your anwers you comment this:
    db2 get snapshot for tables on <dbname>

    this must have a list of temp tables (easy to identify) and the name will of the temp table will tell you the appl id.

    My question is how form have this temp tables?

    Thank you.
    DBA DB2 for LUW

  14. #14
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Dont know why you are this asking this question. You could have tried the command to get the info

    In any case, here it is


    Table Schema = <65024><MYSCHEMA >
    Table Name = TEMP (00001,00005)
    Table Type = Temporary
    Data Object Pages = 16
    Rows Read = 729
    Rows Written = 3223
    Overflows = 0
    Page Reorgs = 0
    here the temp table is in tablespace 1 and used by application 65024

    db2 list tablespaces will tell you more about the temp tablespace.

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

Posting Permissions

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