1. Database Upgrade Guide
  2. Identifying IBM DB2 Long Columns for Truncation in a Siebel Upgrade

Identifying IBM DB2 Long Columns for Truncation in a Siebel Upgrade

Environments: Development, Test and Production.

Databases: IBM DB2 only.

In Siebel CRM version 7.7, the maximum length for IBM DB2 long columns with a type of varchar was reduced to 16,350 from 16,383. Upgrading from version 7.5.3 truncates long varchar columns that exceed 16,350. To prevent a data truncation error that might cause transaction processing (txnproc) or transaction routing (txnroute) to fail, perform the steps in this task to identify these columns and reduce the data in these columns.

Caution: If you do not truncate the data in long varchar columns that exceed the maximum length specified in the following task, then a data truncated error occurs, and transaction processing and transaction routing might fail.

To identify and reduce the length of long varchar columns

  1. From any shell, open the script chk16350.bat (Windows) or chk16350.ksh (UNIX), and edit the following parameters as appropriate for your deployment:

    • SRC_USR is the username of the source database

    • SRC_PSWD is the password for the source database

    • SRC_TBLO is the tableowner of the source database

    • SRC_TBLO_PSWD is the tableowner password for the source database

    • SRC_ODBC is the ODBC data source name of the source database (edit the value CHANGE_ME)

    • SRC_REPOSITORY_NAME is the repository name of the source database

    • DBSRVR_ROOT is the directory where you installed the Siebel Database Server files on the Siebel Server, for example, C:\sba81\dbsrvr (Windows). Edit the value CHANGE_ME.

    • SIEBEL_ROOT is the directory where you installed the Siebel Server. For example, C:\sba81\siebsrvr (Windows). Edit the value CHANGE_ME.

    • VALID_RESULTS_DIR is the directory where you want the output files to be generated (edit the value CHANGE_ME); this must be an existing directory

      This script produces two files:

    • long_trunc_cols.rpt. This report identifies all long varchar columns that are longer than 16,350 characters.

    • update_trunc.sql. This SQL file generates update statements that truncate identified columns to 16,350 characters.

  2. Reduce the data in these columns using either of the following methods:

    • Manually review the columns in the long_trunc_cols.rpt report and manually reduce the size of each column identified.

    • Run update_trunc.sql using the IBM DB2 command line processor.