About Moving Tables With LONG columns to Larger Table Spaces

By creating most tables with LONG columns in an 8-KB table space by default, almost all aspects of the Siebel application function without problems. However, depending on your business needs and the amount and type of data a LONG column is to contain, you might choose to define a Siebel table containing a LONG column in a larger or smaller table space.

For example, if your implementation involves a usage scenario in which very large notes, descriptions, emails, or other items up to 16,350 characters in length are stored, you can resolve this situation by moving the table to a larger table space, either 16 KB or 32 KB. Be aware, however, that if you move a table to a 32 KB table space, the following problems can occur:

• The LONG column can become very large, which increases the storage requirements for buffer pools and can result in wasted space because the Siebel application usage of LONG columns is limited to 16 KB. If additional data is stored in a LONG column, using a tool such as SPUFI, only the first 16,350 bytes of data is retrieved by the Siebel application. Therefore, it is recommended that before executing the DDL that creates a table with a LONG column in a 32-KB buffer pool, you change the physical column definition to VARCHAR (16,350).

• Large LONG columns in 32 KB table spaces can cause SQL SELECT statements to fail because the result set of a SELECT statement containing a LONG column can exceed the 32 KB in-memory sort limit of DB2 for z/OS. When DB2 for z/OS performs an in-memory sort, the full length of the LONG column is added to the total length of the result set. As a result, you might experience SQL0670N errors.

  If you encounter DB2 sort limit problems of this type, redefine the LONG column as a CLOB column and move it to a smaller table space. For information about converting LONG columns to CLOB columns, see Converting LONG VARCHAR Columns to CLOB Columns.