Implementing Siebel Business Applications on DB2 for z/OS > Customizing a Development Environment > How Siebel Tables with LONG Columns Are Stored >

Moving Tables Between Table Spaces

You can move tables containing LONG columns to larger or smaller table spaces as appropriate for your environment. Circumstances in which you might consider moving tables between table spaces include the following:

• If a table containing a LONG column is defined in a 32-KB table space and if SELECT queries are failing because the row length of the table exceeds the 32-KB limit, then the column must be converted to a CLOB and the table can be placed in a smaller table space.
• DB2 for z/OS creates LONG columns as VARCHAR columns that are set to the maximum size possible. It is recommended that, where possible, you change the physical definition of the LONG column to a VARCHAR column of a fixed length, and move the table containing the column to a smaller table space.

  In determining the size of the VARCHAR column, take into account that if the column does not exceed the buffer pool size limit, the column size can be easily increased at a later time using an ALTER statement, which is an additive schema change. However, if the column size must be decreased at a later time, you must drop the table and then re-create it, which is a non-additive schema change that involves shutting down the production database.

The following procedure outlines the steps in moving a table with a LONG column to a larger or smaller table space.

To move a table to a table space of a different size

1. Determine the appropriate table space size for the table using the guidelines in Determining the Table Space Size for Tables with LONG Columns.
2. Unload the data in the table.
3. Delete the table using the SQL DROP command.
4. Re-create the table in a table space of a different size.

   If the table is the only table defined in the table space, when re-creating the table you can specify the same table space name, provided that you specify a different buffer pool size. If the table is defined in a table space with several other tables, when re-creating the table, create it in a new table space where the table is the only table in the table space.

5. Load the data back into the table.

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.