Implementing Siebel Business Applications on DB2 UDB for z/OS > Customizing a Development Environment > Siebel Tools Considerations on DB2 for z/OS >

How Siebel Systems Implements LONG Columns on DB2 for z/OS

Siebel Business Applications use LONG columns on Database Servers to store arbitrarily long character data up to 16350 characters. Examples include scripts, emails, notes, and descriptions. On all RDBMS platforms, except DB2 for z/OS, the size of LONG columns is fixed and is independent of the size of the table space in which the table is defined.

On DB2 for z/OS, a LONG column defined in the Siebel Schema is mapped to a LONGVARCHAR column, which always has a size of 32700 bytes.

On DB2 for z/OS, the size of a LONGVARCHAR column varies depending on two factors:

• The size of the table space in which the table is defined.
• The combined byte size of all non-LONGVARCHAR columns of the table.

You can estimate the size of the LONGVARCHAR column by calculating the size of the table space minus the size of all non-LONGVARCHAR columns; the LONGVARCHAR column takes up the remainder of the space in the table space. For example, a table containing one LONGVARCHAR column, defined in a 16-KB table space, with a combined byte size for non-LONGVARCHAR columns of 5 KB, results in the LONGVARCHAR column having a size of about 11 KB. For more information on calculating the size of LONGVARCHAR columns, refer to the vendor documentation on the IBM Web site.

How Siebel Systems Stores Tables with LONGVARCHAR Columns

If Siebel Systems created all LONGVARCHAR columns on DB2 for z/OS with a length of 16350 characters, this would effectively force all tables to a 32-KB table space; this table space size increases buffer pool storage requirements as it requires twice as much storage as a 16-KB table space. To minimize the number of tables created in 32-KB table spaces, during the Siebel installation process, tables with LONGVARCHAR columns are created in a 16-KB table space by default. Siebel Business Applications assume that LONGVARCHAR columns have a logical length of 4046 bytes, which forces the table to be created in a 16-KB table space.

NOTE:  Although Siebel Systems sets the LONGVARCHAR column to have a logical length of 4046 bytes, the physical length is not specified in the CREATE TABLE statements for the LONGVARCHAR column. So the resulting length of the LONGVARCHAR field is still calculated by the algorithm defined by DB2 for z/OS.

By taking this approach, almost all aspects of the Siebel application function without problems. However, if your implementation involves a usage scenario in which very large notes, descriptions, emails, or other items up to 16350 in length are stored, then you need to resolve this by considering one of the following choices:

• Move the table to a 32-KB table space.

  If you move a table to a 32-KB table space, the LONGVARCHAR column can be up to 30 KB in length, doubling the storage requirements for buffer pools. Moreover, the result set of a join SELECT statement containing a LONGVARCHAR column can exceed 32 KB. When the result set does exceed 32 KB, you may run into the 32-KB in-memory sort limit of DB2 for z/OS v7. When DB2 for z/OS performs an in-memory sort, the full length of the LONGVARCHAR column is added to the total row width. As a result, you may experience SQL0670N errors.

• Use a CLOB column instead of a LONGVARCHAR column.

  For further information, see About Using Character Large Objects (CLOBs).