Dropping, re-creating, or converting a table space

To make changes to a table space, you can drop the table space and then re-create it. These table space changes include changing SEGSIZE, changing the number of partitions, or converting a table space to a large table space.

About this task

Alternatively, you can use the ALTER TABLESPACE statement to change the table space type and attributes, such as BUFFERPOOL, DSSIZE, SEGSIZE, and MAXPARTITIONS. For more information, see Altering table spaces.

To change or convert a table space by dropping the table space and then recreating it:

1. Locate the original CREATE TABLE statement and all authorization statements for all tables in the table space (for example, TA1, TA2, TA3, … in TS1). If you cannot find these statements, query the DB2® catalog to determine the table's description, the description of all indexes and views on it, and all users with privileges on the table.
2. In another table space (for example, TS2), create tables TB1, TB2, TB3, … identical to TA1, TA2, TA3, ….
   
   For example, use a statement such as:

   CREATE TABLE TB1 LIKE TA1 IN TS2;

3. Optional: If necessary, unload the data. For example, use a statement such as:

   REORG TABLESPACE DSN8D91A.TS1 LOG NO SORTDATA UNLOAD EXTERNAL;

   Another way of unloading data from your old tables and loading the data into new tables is by using the INCURSOR option of the LOAD utility. This option uses the DB2 cross-loader function.

4. Optional: Alternatively, instead of unloading the data, you can insert the data from your old tables into the new tables by issuing an INSERT statement for each table. For example:

   INSERT INTO TB1
     SELECT * FROM TA1;

   If a table contains a ROWID column or an identity column and you want to keep the existing column values, you must define that column as GENERATED BY DEFAULT. If the ROWID column or identity column is defined with GENERATED ALWAYS, and you want DB2 to generate new values for that column, specify OVERRIDING USER VALUE on the INSERT statement with the subselect.
5. Drop the table space. For example, use a statement such as:

   DROP TABLESPACE TS1;

   The compression dictionary for the table space is dropped, if one exists. All tables in TS1 are dropped automatically.
6. Commit the DROP statement. You must commit the DROP TABLESPACE statement before creating a table space or index with the same name. When you drop a table space, all entries for that table space are dropped from SYSIBM.SYSCOPY. This makes recovery for that table space impossible from previous image copies.
7. Create the new table space, TS1, and grant the appropriate user privileges. You can also create a partitioned table space. For example, use a statement such as:

   CREATE TABLESPACE TS1
      IN DSN8D91A
      USING STOGROUP DSN8G910
        PRIQTY 4000
        SECQTY 130
        ERASE NO
    NUMPARTS 95
     (PARTITION 45 USING STOGROUP DSN8G910
        PRIQTY 4000
        SECQTY 130
        COMPRESS YES,
      PARTITION 62 USING STOGROUP DSN8G910
        PRIQTY 4000
        SECQTY 130
        COMPRESS NO)
    LOCKSIZE PAGE
    BUFFERPOOL BP1
    CLOSE NO;

8. Create new tables TA1, TA2, TA3, ….
9. Re-create indexes on the tables, and grant user privileges on those tables.
10. Issue an INSERT statement for each table. For example:

    INSERT INTO TA1
      SELECT * FROM TB1;

    If a table contains a ROWID column or an identity column and you want to keep the existing column values, you must define that column as GENERATED BY DEFAULT. If the ROWID column or identity column is defined with GENERATED ALWAYS, and you want DB2 to generate new values for that column, specify OVERRIDING USER VALUE on the INSERT statement with the subselect.

    

11. Drop table space TS2. If a table in the table space has been created with RESTRICT ON DROP, you must alter that table to remove the restriction before you can drop the table space.
12. Notify users to re-create any synonyms they had on TA1, TA2, TA3, ….
13. REBIND any packages that were invalidated as a result of dropping the table space.