1. Implementing Siebel Business Applications on DB2 for z/OS
  2. Moving Tables Between Table Spaces

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.