Example of Moving a Table to a Table Space of a Different Size

This topic gives one example of moving a table to a table space of a different size. You might use this feature differently, depending on your business model. In this example, the LONG column in the table does not exceed 4000 bytes in length so the table can be defined at the physical schema level as a VARCHAR in an 8-KB table space.

To move a table with a LONG column to a different table space

1. Unload the data from the table, casting the LONG column as a VARCHAR column with a maximum length of 4000 bytes.

   Defining a LONG column as a VARCHAR data type with a defined length means that even if you define the table containing the column in a larger table space, the length of the column is limited in size; this is beneficial when performing database upgrades.

   SELECT
   CLASS_ID,
   COMMENTS,
   …
   NAME,
   PROCEDURE_NAME,
   REPOSITORY_ID,
   ROW_ID,
   SSE_FLG,
   SST_FLG,
   SSV_FLG,
   USER_AGENT_CD,
   CAST(column_name AS VarChar(4000)) AS column_name
   FROM tableowner.tablename WITH UR;

   where:

   • column_name is the name of the LONG column

   • tableowner.tablename is the name of the table owner and table containing the LONG column

2. Use the SQL DROP command to remove the original table, then re-create the table in an appropriately sized table space, in this example, an 8-KB table space.

3. Load the data back into the table.