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

About Using Character Large Objects (CLOBs)

If you choose, you can store Siebel application objects defined as LONGVARCHARs as character large objects (CLOBs) on DB2 for z/OS. Siebel Business Applications support CLOBs that allow 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 LONGVARCHAR, because of the performance and storage characteristics of CLOBs in a DB2 for z/OS environment. However, if you have Siebel Marketing, change the LONGVARCHAR 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 LONGVARCHAR to CLOB to safeguard against space limitations that are inherent to the structure of z/OS.

Issues in Using a CLOB Column Instead of a LONGVARCHAR Column

If you use a CLOB column instead of a LONGVARCHAR column, you do not run into the 32-KB in-memory sort limit of DB2 for z/OS v7. 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 Database Server, which can impact response time. Also, writing data to CLOB columns is slower than writing to LONGVARCHAR 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 for 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.

For more information on the using LONGVARCHARs and CLOBs, see the Upgrade Guide for DB2 UDB for z/OS.

Siebel Tables Defined with LONG Columns in 32-KB Table Spaces

The following tables are defined in 32-KB table spaces in the Siebel application. Siebel Business Applications stores these tables in a 32-KB table space because the LONG columns in these tables need as much space as possible.

• S_BITMAP_DATA
• S_SCHMST_DBSCPT
• S_SERVICE_SCRPT
• S_NOTE
• S_SRC
• S_DMND_CRTN_PRG

Siebel Tables Defined with CLOB Columns

The following tables use CLOB columns in the Siebel application. 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. CLOB columns are also used if a table cannot be stored in a 32-KB table space because of the 32-KB in-memory sort limit of DB2 for z/OS v7.

• S_DMND_CRTN_PRG
• S_NOTE
• S_BITMAP_DATA