1. Database Upgrade Guide
  2. What's New in This Release

6Preparing an IBM DB2 Database for a Siebel Upgrade

Verifying the IBM DB2 Client for a Siebel Upgrade

Environments: Development, production test, production.

Databases: IBM DB2 UDB only.

The Siebel Server supports only the 32-bit IBM DB2 client. Verify that you have not installed the 64-bit IBM DB2 client on the Siebel Servers. If you have installed the 64-bit IBM DB2 client, then replace it with the 32-bit client.

IBM supports the 32-bit IBM DB2 client working with 64-bit IBM DB2 databases.

Verifying IBM DB2 Sort Order for a Siebel Upgrade

Environments: Development, production test, production.

Databases: IBM DB2 only.

Binary sort order is required for the development environment upgrade and is strongly recommended for the production environment upgrades.

Sort order is specified during creation of the database. If you find that your IBM DB2 development database was not created using Identity sort order, then you must re-create your database using the option COLLATE USING IDENTITY.

If sort order is correct, but you are still encountering errors, contact your Oracle sales representative for Oracle Advanced Customer Services to request assistance from Oracle’s Application Expert Services to help in further analysis.

To verify that your database was created using Identity sort order

  1. Run the following query in the Siebel database: 

    select count (*) from SIEBEL.S_APP_VER where '$' > '/'

  2. Review the result.

    • If sort order is correct, then the result is as follows:

      1

--------------

0

(1) record selected.

    • If sort order is incorrect, then you must re-create the database, using this option:

      COLLATE USING IDENTITY

Setting IBM DB2 Parameters for a Siebel Upgrade

Environments: Development, production test, production.

Databases: IBM DB2 only.

Before upgrading an IBM DB2 database, verify that your database server meets or exceeds the following configuration criteria:

  • The DMS tablespace has at least 25% of free pages.

  • The file system has sufficient space to allow your DMS tablespace to grow.

  • Siebel tablespaces for IBM DB2 must be database-managed tablespaces (DMS) rather than system-managed tablespaces (SMS).

  • Verify that the tablespaces are not near their capacity. This can be done by connecting to the database and issuing the following command:

    DB2 list tablespaces show detail

The tables which follow provide upgrade-specific settings for the Database Manager and database. Use the following strategy to set parameters:

  • Set parameters using the recommendations in Siebel Installation Guide. Recommendations are located in the chapter on configuring the RDBMS.

  • For the upgrade, revise the configuration parameters listed in the tables which follow.

  • After the upgrade, reset the configuration parameters to the values listed in Siebel Installation Guide.

    IBM DB2 Database Manager Settings

    The Upgrade Setting column in the following table provides guidelines for setting configuration parameters specifically to optimize upgrade performance. Set these parameters for each IBM DB2 instance.

    Table IBM DB2 Database Manager Configuration Parameters

    Parameter Explanation Upgrade Setting

    SHEAPTHRES

    Sort heap threshold (4 KB)

    If you reset SHEAPTHRES or SORTHEAP, then rebinding the instance is recommended.

    Double the value allocated for SORTHEAP. See the following table.

      IBM DB2 Database Configuration Parameters

      The Upgrade Setting column in the following table provides guidelines for setting configuration parameters specifically to optimize upgrade performance. Set these parameters for each IBM DB2 instance.

      Table IBM DB2 Database Configuration Parameters

      Parameter Explanation Upgrade Setting

      SORTHEAP

      Sort list heap (4 KB)

      20000—40000 Recommended size; this might increase or decrease depending on the amount of memory in the database server computer and the size of the data.

      A 20000 setting allows SORTHEAP to increase up to 80 MB.

      MAXLOCKS

      Percentage of lock lists for each application

      5

      CHNGPGS_THRESH

      Changed pages threshold

      5

      logarchmeth1

      Primary log archive method configuration parameter

      OFF.

      To retain active log files for rollforward recovery, set logarchmeth1 to LOGRETAIN by issuing the command:

      UPDATE DB CFG USING logarchmeth1 LOGRETAIN.

      LOGFILSIZ

      Log file size (4 KB)

      Development environments: 8000-16000

      SOFTMAX

      Triggers bufferpool flushing

      50

        Verifying IBM DB2 Permissions for a Siebel Upgrade

        Environments: Development, production test, production.

        Databases: IBM DB2 only.

        Platforms: UNIX only.

        If you are running IBM DB2 on IBM AIX or Oracle Solaris, then perform the following steps before executing the Siebel Database upgrade.

        To verify IBM DB2 permissions

        1. Navigate to the instance home directory.

        2. Use the following command to verify that the directory sqllib/function/routine/sqlproc has write permission for the group:

          ls -ld sqllib/function/routine/sqlproc

        3. To authorize group write permission, enter the following command: 

          chmod g+w sqllib/function/routine/sqlproc

        Verifying IBM DB2 Instance Owner Permissions for a Siebel Upgrade

        Environments: Development, production test, production.

        Databases: IBM DB2 only.

        Platforms: UNIX only.

        If you are running IBM DB2 on AIX or Oracle Solaris, then verify that the Siebel Database instance owner belongs to the primary group of the fenced user. If the instance owner is not part of this group, then errors will occur during the Siebel Database upgrade.

        Creating IBM DB2 Temporary Tablespaces and Bufferpools for a Siebel Upgrade

        Environments: Development, production test, production.

        Databases: IBM DB2 only.

        If your RDBMS is IBM DB2, then verify that you have 16-KB and 32-KB temporary tablespaces to use for sorting and other SQL processing. Both the 16-KB and 32-KB temporary tablespaces require dedicated bufferpools.

          Creating a 16-KB Temporary Tablespace

          Use the following procedure to create a 16-KB temporary tablespace.

          To create a 16-KB temporary tablespace

          1. Create a 16-KB bufferpool with at least 5000 16-KB pages.

          2. Create a 16-KB temporary tablespace as system managed space (SMS) that can be expanded to 2 GB of storage.

            Creating a 32-KB Temporary Tablespace

            Use the following procedure to create a 32-KB temporary tablespace.

            To create a 32-KB temporary tablespace

            1. Create a 32-KB bufferpool with at least 1000 32-KB pages.

            2. Create a 32-KB temporary tablespace as SMS that can be expanded to 2 GB of storage.

              Analyzing IBM DB2 Custom Tablespace Requirements for a Siebel Upgrade

              Environments: Development, production test, production.

              Databases: IBM DB2 only.

              There are four standard database managed tablespaces (DMS) that hold Siebel tables and indexes: a 4-KB, 16-KB, 32-KB tablespace, for tables, and a tablespace to hold indexes. The upgrade process moves tables between these spaces as required.

              If you have placed Siebel tables in other tablespaces, then the upgrade process will not move these tables if they grow to exceed the tablespace size during the upgrade. If one of these tables has an estimated page size after upgrade greater than its current page size, then it will not fit in its tablespace after the upgrade, and the upgrade will fail.

              Oracle provides a sizing utility that determines whether tables will increase in size to the point that they must be moved to a larger tablespace.

              Run the utility before upgrading the database. If the sizing utility reports any problems, then you must resolve them before you proceed with the upgrade.

              To analyze tablespace requirements for IBM DB2

              1. Navigate to the following directory:

                Windows: SIEBEL_ROOT\bin

                UNIX: $SIEBEL_ROOT/bin

              2. Type the following command line:

                tblsize /U TABLEOWNER /P PASSWORD /C ODBC_DATASOURCE /F DDL_FILE/B DEFAULT_TABLESPACE /X DEFAULT_INDEXSPACE /K 16K_TABLESPACE /V 32K_TABLESPACE /Q REPORT_FILENAME /L LOG_FILENAME

                where:

                • TABLEOWNER is the tableowner

                • PASSWORD is the tableowner password

                • ODBC_DATASOURCE is the data source of the database

                • DDL_FILE is the absolute path to the DDL file (this file is called ddl.ctl, and it is located in the dbsrvr/DB2 directory)

                • DEFAULT_TABLESPACE is the name of the 4-KB page standard Siebel tablespace

                • DEFAULT_INDEXSPACE is the name of the standard Siebel index space

                • 16K_TABLESPACE is the name of the 16-KB page standard Siebel tablespace

                • 32K_TABLESPACE is the name of the 32-KB page standard Siebel tablespace

                • REPORT_FILENAME is the name of the report generated by the utility

                • LOG_FILENAME is the name of the log file (default: custtbl.log)

                  Example:

                  tblsize /U siebel /P siebel /C ssia /F d:\sea77\dbsrvr\DB2\ddl.ctl /B siebel_4k 
/X siebel_idx /K siebel_16k /V siebel_32k /Q d:\sba82\dbsrvr\DB2\report.txt /L 
$SIEBEL_ROOT/log/tblsize.log

              3. Review the report generated by the utility to determine whether the estimated table pagesize postupgrade is larger than the size of the actual custom table pagesize.

                An example of the report generated by this utility is displayed in the following example:

                Table Name = S_EVT_ACT
Custom Tablespace Id = 5
Custom Tablespace Name = CUST_TBS_EVT_ACT
Custom Tablespace Pagesize = 4096
Estimated Table Pagesize (postupgrade) = 5067
Status = Does not fit in its custom tablespace
                Caution: For each table that has Status: Does not fit in its custom tablespace, you must create a larger custom tablespace that is larger than the estimated table pagesize postupgrade.

              4. Move the tables from their old tablespaces to the new ones by running ddlmove.

                ddlmove is a utility for moving tables from one tablespace to another tablespace. This utility is located in the following directory:

                Windows: SIEBEL_ROOT\bin

                UNIX: $SIEBEL_ROOT/bin

              5. To run ddlmove, submit the following arguments:

                ddlmove /U TABLEOWNER /P TABLE_PASSWORD /C ODBC_DATASOURCE /E STOP_ON_DDL_ERROR/G GRANTEE /B TABLESPACE /X INDEX_TABLESPACE /M TABLE_NAME /L LOG_FILENAME/Z UCS2_DATABASE

                where:

                • TABLEOWNER is the tableowner of the database (required)

                • TABLE_PASSWORD is the password of the tableowner of the database (required)

                • ODBC_DATASOURCE is the data source of the database (default environment variable: SIEBEL_DATA_SOURCE)

                • STOP_ON_DDL_ERROR is the stop on DDL Error parameter (default: Y)

                • GRANTEE is the grantee for tables (SSE_ROLE)

                • TABLESPACE is the name of the tablespace that you are moving the table to

                • INDEX_TABLESPACE is the name of the index space that you are moving the table to

                • TABLE_NAME is the Table Name Like Support value (default: N)

                • LOG_FILENAME is the name of the log file (default: ddlmove.log)

                • UCS2_DATABASE specifies whether the database uses Unicode (default: N)

              Verifying the IBM DB2 Application Development Client for a Siebel Upgrade

              Environments: Development, production test, production.

              Databases: IBM DB2 only.

              The IBM DB2 Application Development Client must be installed on the RDBMS server. The following table lists the required IBM DB2 Application Development Client components.

              Table IBM DB2 Application Development Client Components

              Operating System IBM DB2 Application Development Client Components

              Microsoft Windows

              DB2 Application Development Client

              IBM AIX

              Application Development Tools (ADT)

              ADT Sample Programs

              HP-UX

              Application Development Tools for HP-UX

              Oracle Solaris

              Application Development Tools (ADT)

              ADT Sample Programs

              For information on installing the Application Development Client, see IBM documentation.

              Identifying IBM DB2 Long Columns for Truncation in a Siebel Upgrade

              Environments: Development, production test, 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.