Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2005
    Location
    USA
    Posts
    130

    Unanswered: DB2 Alter table space

    Hi,
    I am new to DB2 LUW world and from mainframe DBA back ground. We are in DB2V9.7 on linux. Tring to alter the tablespace parameter INITIALSIZE from 32M to 8K . Is this can be done through alter command. We defined all our tablespaces with initial size as 32MB and due to this running out of space. Is there any way can we change this size?

    Tried the below command. It didn't work.
    ALTER TABLESPACE REDUCE SIZE 8K

  2. #2
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    You cannot change the initialsize.
    Give more details of what you mean by "running out of space"?
    Do you mean, running of of space in the file-system, or do you mean running out of space inside a tablespace that is not configured to automatically extend itself?
    Why not configure the tablespace (via alter) to auto resize ?

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Check the SQL Reference Manual Vol 2 for alter tablespace command and examples.

    Hint: For DB2 z/OS it is beneficial and customary to have one tablespace for each table. For DB2 LUW that is not a good idea and there is no benefit. If you have some extremely large tables, they can go into their own tablespaces, otherwise group them so you have a reasonable number (and not just always one for each table). Indexes should reside in a separate tablespace(s), The main criteria for grouping tables and indexes into tablespaces is to get them in the desired bufferpool (assuming you have more than one).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Apr 2005
    Location
    USA
    Posts
    130
    Thank you so much.
    db2mor, Running out of space in file-system. All these tablespaces are defined with automatic storage enabled.

    Marcus_A, Thank you for the hint. I have created one tablespace and index space for each table as we do in z/OS. We have around 150 tables. May be I would rearrange them into groups.

  5. #5
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    So you have around 300 tablespaces then (150 tables, one for data one for index per table).
    Might be better to group.
    On LUW it is common to have index tablespaces and dataa tablespaces, of differing page-sizes as appropriate but grouped by functional area, or by schema, or some grouping that is sensible and appropriate for the application. Use dedicated tablespaces only for the largest tables (#card #avgrowlen).

  6. #6
    Join Date
    Mar 2012
    Location
    Canberra, Australia
    Posts
    38
    Seems like a small filesystem

Posting Permissions

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