Dropping and re-creating a table space to change its attributes

One approach for changing the attributes of a space is to drop the table space and create it again with different attributes. When you use this approach you must also take action to preserve the data in the table space.

Before you begin

For best results, use this procedure only to change attributes of a table space that cannot be changed with ALTER TABLESPACE statements. The techniques described here are intended for changing the attributes of non-UTS table spaces.

For partition-by-growth or partition-by-range universal table spaces, it is best to use ALTER TABLESPACE statements, which can change most attributes of a table space with pending definition changes. For deprecated non-UTS types, consider first converting the table space to a UTS type, and then use ALTER TABLESPACE statements to make the required changes.

Tip:

FL 504 If the table to be re-created uses a deprecated table space type, you might need to issue the following statement first to complete this task:

SET CURRENT APPLICATION COMPATIBILITY = 'V12R1M503'

Procedure

To drop and re-recreate a table space:

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.
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;
```
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.
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.
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.
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.

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;

Create the new tables TA1, TA2, TA3, ….
Re-create indexes on the tables, and grant user privileges on those tables.
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.
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.
Re-create any dependent objects on the new tables TA1, TA2, TA3, ….
REBIND any packages that were invalidated as a result of dropping the table space.