Implementing Siebel Business Applications on DB2 UDB for z/OS > Customizing a Development Environment > How Siebel Implements LONG Columns on DB2 for z/OS >

About Converting LONG VARCHARs to CLOBs

In Siebel 8.0, Siebel application objects can be defined as CLOB data types in the Siebel repository; such objects can have up to 128 KB of data for a single data element in a table row.

If you choose, you can also store Siebel application objects defined as LONG VARCHARs as character large objects (CLOBs) on DB2 for z/OS. Siebel objects that are defined as LONG VARCHARs but stored as CLOBs can have up to 32 KB of data for a single data element in a table row.

The default setting for these objects in the Siebel Schema is LONG VARCHAR, because of the performance and storage characteristics of CLOBs in a DB2 for z/OS environment. However, if you have Siebel Marketing, change the LONG VARCHAR column in the S_NOTE table to a CLOB.

The Siebel installation, upgrade, and migration processes create all auxiliary objects necessary to support CLOBs, so that their use is transparent. However, CLOBs are turned off by default. You must turn them on for each table by setting Clobs = Yes in the storage control file.

NOTE:  If you write your own scripts, it is recommended that you convert columns that contain scripts from LONG VARCHAR to CLOB to safeguard against space limitations that are inherent to the structure of z/OS.

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 V8. 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, using a CLOB column has an impact on performance and storage (DASD).

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 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.

IBM and third-party vendor tools have limited or no support for CLOB columns. Compression is also not supported for LOB table spaces.

Aside from performance and storage tradeoffs, perhaps the most important tradeoff 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 the CLOB column. You need to consider this additional overhead before deciding to use CLOB columns.

Siebel Tables Defined with LONG Columns in 32 KB Table Spaces

The S_SERVICE_SCRPT table is defined in a 32 KB table space in the Siebel application for databases that use ASCII or EBCDIC encoding formats because the LONG column in this table needs as much space as possible.

Siebel Tables Defined with LONG Columns That Are Created as CLOBs

The following Siebel tables have columns defined as LONG VARCHARs that are converted to CLOB columns on z/OS (Clobs is set to Yes in the storage control file). Siebel Business Applications use CLOB columns for these tables because they belong to the Siebel repository and read and write activity to them is small:

  • S_NOTE

Siebel Tables Defined with CLOB Columns

The following Siebel tables have columns that are defined as CLOB columns in the Siebel repository. Activity on these tables is quite high, but these tables cannot be stored in 32 KB table spaces because they have very large columns (greater than 128 KB) and so could encounter the in-memory sort limit of DB2 for z/OS V8:

  • S_TU_LOG
  • S_TU_LOG_X_01
  • S_TU_LOG_X_02
  • S_TU_LOG_X_03
  • S_TU_LOG_X_04
  • S_TU_LOG_X_05
Implementing Siebel Business Applications on DB2 UDB for z/OS Copyright © 2008, Oracle. All rights reserved.