Results 1 to 13 of 13
  1. #1
    Join Date
    Feb 2007
    Location
    Bratislava, Slovakia
    Posts
    85

    Unanswered: resize tablespace with automatic storage

    Hi all,
    i have next problem:
    i need shrink tablespace size. this tablespace is managed by database and uses automatic storage option. By documentation i have not control over tablespace containers, database manager can increase automatically container size, when is full. but how to shrink container?
    some ideas ?
    thanks advance.



    LUW 9.1, linux

    db2 alter tablespace USERSPACE1 resize (all containers 1024 M)

    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL20318N Table space "USERSPACE1" of type "AUTOMATIC STORAGE" cannot bealtered using the "RESIZE" operation. SQLSTATE=42858
    Beer contains just a small amount of vitamines - that's why it's necessary to drink lot of it.

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    as the message indicates, ts containers with automatic storage can not be resized - I am checking if any other option.
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Feb 2007
    Location
    Bratislava, Slovakia
    Posts
    85
    przytula_guy: thanks for interest
    Beer contains just a small amount of vitamines - that's why it's necessary to drink lot of it.

  4. #4
    Join Date
    Jun 2006
    Posts
    471

    resize

    this is only possible in 9.5 with reduce option...
    Best Regards, Guy Przytula
    DB2 UDB LUW certified V6/7/8

  5. #5
    Join Date
    Feb 2007
    Location
    Bratislava, Slovakia
    Posts
    85
    guyprzytula: thanks for info. so i need drop/recreate db/tablespace without this incredible feature.
    Beer contains just a small amount of vitamines - that's why it's necessary to drink lot of it.

  6. #6
    Join Date
    Feb 2007
    Location
    Bratislava, Slovakia
    Posts
    85
    in 9.5 same error
    Beer contains just a small amount of vitamines - that's why it's necessary to drink lot of it.

  7. #7
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    have you used the reduce option in 9.5
    look at syntax :
    http://publib.boulder.ibm.com/infoce...c/r0000890.htm
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I think he is saying that command will not work with automatic storage. I was noticing that there are some severe limitations of automatic storage in terms of manual configuration, and I decided against it. Automatic sizing is sufficient for most people's needs.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  9. #9
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    yes it will in 9.5 you can reduce the size even with automatic storage
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  10. #10
    Join Date
    Dec 2011
    Posts
    3
    Quote Originally Posted by przytula_guy View Post
    yes it will in 9.5 you can reduce the size even with automatic storage
    would anyone care to elaborate on this?

    for whatever reason, our db2 v9.5.5 on win 2008-64 refuses to accept "reduce" as a valid command...

    am i missing somthing?

    trying... alter tabelspace userspace1 (all 50 percent)

    forgive my ignorance, this is a bit of a new realm for me.

  11. #11
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by phocas View Post

    forgive my ignorance, this is a bit of a new realm for me.
    In that case it might be a good idea to start reading the manuals. You would be able to learn, for example, that "for automatic storage table spaces, the REDUCE clause must not be followed by a database-container-clause, an all-containers-clause, or an on-db-partitions-clause."

    ALTER TABLESPACE

  12. #12
    Join Date
    Dec 2011
    Posts
    3
    thanks n_i... have been doing quite a bit of reading on this... and nobody is particularly clear on reduce... indeed there is a lot of contradictory information.


    Quote Originally Posted by n_i View Post
    In that case it might be a good idea to start reading the manuals. You would be able to learn, for example, that "for automatic storage table spaces, the REDUCE clause must not be followed by a database-container-clause, an all-containers-clause, or an on-db-partitions-clause."

    ALTER TABLESPACE
    Last edited by phocas; 12-22-11 at 18:43. Reason: some typos and such

  13. #13
    Join Date
    Dec 2011
    Posts
    3
    since not everyone is an expert...

    this worked for us...

    ALTER TABLESPACE USERSPACE1 REDUCE;

    IBM DB2 9.7 for Linux, UNIX and Windows Information Center

    change: 17 GB of free space went to 107 GB free space
    Last edited by phocas; 12-28-11 at 13:30. Reason: more info

Posting Permissions

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