Implementing Siebel Business Applications on DB2 for z/OS > Customizing a Development Environment > About Siebel Tables and CLOB Columns >

Converting LONG VARCHAR Columns to CLOB Columns

Siebel application objects defined as LONG columns in the Siebel Schema are defined as LONG VARCHAR data types in the Siebel Repository. If you choose, you can store these columns as character large objects (CLOBs) on DB2 for z/OS.

The default setting for these objects in the Siebel Schema is LONG because of the performance and storage characteristics of CLOBs in a DB2 for z/OS environment. These characteristics are described in Issues in Using CLOB Columns Instead of LONG VARCHAR Columns. However, it is recommended that you store LONG VARCHAR objects in the Siebel Repository as CLOBs in the following circumstances:

• If you have Siebel Marketing, change the LONG column in the S_NOTE table to a CLOB.
• If you write your own scripts, it is recommended that you convert LONG columns that contain scripts to CLOB columns to safeguard against space limitations that are inherent to the structure of z/OS.

NOTE:  If you choose to store LONG VARCHAR objects as CLOB objects, the logical and physical schema definitions of the object are not synchronized. As a result, when you run the dbchck utility, errors are generated for the relevant columns; these can be ignored.

The Siebel installation, upgrade, and migration processes create all auxiliary objects necessary to support CLOBs so that their use is transparent. However, CLOBs are not enabled by default. To store Siebel application objects defined in the Siebel Repository as LONG VARCHAR data types as CLOB data types, perform the following procedure.

To convert LONG VARCHAR columns in a table to CLOB columns

1. Edit the storage control file as described in About Modifying Storage Control Files.
2. Locate the table object for which you want to enable CLOB columns, and set the value of Clobs to YES.

   All LONG VARCHAR columns defined for the table in the Siebel Repository are created as CLOB columns in the physical Siebel Schema.

Issues in Using CLOB Columns Instead of LONG VARCHAR Columns

If you use a CLOB column instead of a LONG VARCHAR column, you do not run into the in-memory sort limit of DB2 for z/OS. This memory limitation does not occur because DB2 for z/OS does not account for the actual length of the CLOB column in estimating the total row width to be sorted in memory. However, there are a number of issues in using CLOB columns as follows:

• Using a CLOB column has an impact on performance.

  Fetching data from CLOB columns requires extra network flows between the client and the z/OS host, which can impact response time. Use CLOB data in form applets but be cautious about using CLOB data in a Siebel list applet if you want to keep the volume of data in network flows to reasonable levels. Writing data to CLOB columns is also slower than writing to LONG VARCHAR columns so for tables that are frequently used, moving to CLOB columns is not an option.

• Using a CLOB column has an impact on storage (DASD).

  Using CLOB columns generally increases the amount of storage (DASD) needed, because space is allocated for the total width of the CLOB column even if the CLOB column contains only one byte of data.

• Compression is not supported for LOB table spaces.
• Perhaps the most important trade-off of using CLOBs relates to recovery considerations. Using CLOBs with LOG YES provides a point of forward recovery. However, there is a cost associated with logging the contents of a CLOB column. You need to consider this additional overhead before deciding to use CLOB columns.