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

LONG Columns on DB2 UDB for z/OS and OS/390 and Other DBMS Platforms

Siebel uses LONG columns on databases servers to store arbitrarily long character data up to 16350 characters. Examples include scripts, emails, notes, and descriptions. On all RDBMS platforms, except DB2 UDB for z/OS and OS/390, 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 UDB for z/OS and OS/390, a LONG column defined in the Siebel schema is mapped to a LONGVARCHAR column, which always has a size of 32700 bytes.

On DB2 UDB for z/OS and OS/390, 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.

The size of the LONGVARCHAR column is roughly calculated by the size of the table space subtracted by all non-LONGVARCHAR columns. In other words, a LONGVARCHAR column on DB2 UDB for z/OS and OS/390 would take up the rest of the space available 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 would result in the LONGVARCHAR column having a size of about 11 KB. For more information on size calculation of LONGVARCHAR columns, refer to vendor documentation on the IBM Web site.

How Siebel Systems Uses LONG Columns on DB2 UDB for z/OS and OS/390

Creating all LONGVARCHAR columns on DB2 UDB for z/OS and OS/390 with length 16350 would effectively force all tables to a 32-KB tablespace. Doing so will increase buffer pool storage requirements as it needs twice as much storage as a 16-KB tablespace. So, in order to minimize the number of tables created in 32-KB tablespaces, the Siebel installation process by default creates tables with LONGVARCHAR columns in a 16-KB tablespace. This is done by assuming that LONGVARCHAR columns have a logical length of 4046 bytes, which in effect forces the table to be created in at least a 16-KB tablespace.

NOTE:  Although logically 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 UDB for z/OS and OS/390.

By doing this the resulting size of LONGVARCHAR columns will vary between 4046 bytes to 16320 bytes. With this approach, almost all aspects of the Siebel application function without any 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 two following choices:

• Move the table to a 32-KB tablespace.
• Use a CLOB column instead of a LONGVARCHAR column.

Before implementing either of the two solutions above, you should first understand the potential issues that may arise from either of the two solutions.

About Moving the Table to a 32-KB Tablespace

By moving a table to a 32-KB tablespace, the LONGVARCHAR column would have a length up to 30 KB. As mentioned before, doing this will double the storage requirements for bufferpools. Another side effect is that the result set of a join SELECT statement containing a LONGVARCHAR column could be more than 32 KB. When the result set does exceed 32 KB, then you may run into the 32-KB in memory sort limit of DB2 UDB for z/OS and OS/390. When DB2 UDB for z/OS and OS/390 performs an in-memory sort, the full length of the LONGVARCHAR column is added to the total row width. As a result, you may run into SQL0670N errors.

About Using a CLOB Column Instead of a LONGVARCHAR Column

By using a CLOB column instead of a LONGVARCHAR column, you will not run into the 32-KB in-memory sort limit of DB2 UDB for z/OS and OS/390. The reason is DB2 UDB for z/OS and OS/390 does not account for the actual length of the CLOB column in the total row width to be sorted in memory. However, although there are no functional limitations, there is impact on performance and storage (DASD). Fetching data from CLOB columns requires extra network flows between the client and database server. This can impact response time. Also, writing data to CLOB columns is slower than writing to LONGVARCHAR columns. So, if the table in question is frequently used, then moving to CLOB columns is not an option.

Using CLOB columns will most likely increase the amount of storage (DASD) needed. The reason is the space for the total width of the CLOB column will be allocated and used even if only one byte of data is in the CLOB column.

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

Aside from performance and storage tradeoffs, perhaps the most important tradeoff for using CLOBs is 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. This additional overhead should also be considered before deciding on using CLOB columns.

About Defining Siebel Tables with LONG Columns in 32-KB Tablespaces

The following tables are defined in 32-KB tablespaces in the Siebel application. Siebel eBusiness Applications moved these tables to a 32-KB tablespace, 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

About Using CLOB Columns in the Siebel Application

The following three tables use CLOB columns in the Siebel application. Siebel eBusiness Applications used CLOB columns for these tables because the tables belong to the Siebel repository and read and write activity to them is small. Also, CLOB columns are used if the table could not be put in a 32-KB table space because of the 32-KB in-memory sort limit of DB2 UDB for z/OS and OS/390.

• S_DMND_CRTN_PRG
• S_NOTE
• S_BITMAP_DATA