Siebel Database Upgrade Guide for DB2 for z/OS > Basic Database Preparations for a Siebel Upgrade >

Rebuilding Target Tables Containing LONG VARCHAR Columns

This topic describes how to rebuild tables in the target database that contain LONG VARCHAR columns.

In previous Siebel CRM releases, LONG columns in the Siebel repository were mapped to LONG VARCHAR columns on z/OS databases. The DDLIMP utility has now been modified so that LONG columns are created on z/OS as VARCHAR columns with a maximum size of 16,350 characters. This change to DDLIMP can result in inconsistencies between the staging database, on which LONG columns are mapped to columns with a maximum size of 16,350, and the target database where a LONG VARCHAR column can be much larger.

The Siebel upgrade process requires that the staging database represents the target schema to be upgraded so differences in column definitions can cause issues during the upgrade. For example, a Siebel target table in a 32-KB table space can have a LONG VARCHAR column whose length exceeds 16,350 characters. However, when the same column is created in the staging database, it has a maximum length of 16,350 characters. In these circumstances, if the upgrade process attempts to add columns to the staging table as an additive change it will succeed, but will fail when the changes are applied to the target database.

To avoid potential issues during the target database upgrade, if a table in the target database resides in a table space within a 32-KB buffer pool, and if the number or size of the table columns will be increased during the upgrade process, then the table must be re-created so that it has the same column definitions as the corresponding staging database table.

NOTE:  You can rebuild target tables at any time before you start the upgrade but you must have completed this task before you apply Additive schema changes to the production staging database.

To rebuild target tables that contain LONG VARCHAR columns

1. Determine which target tables containing LONG VARCHAR columns need to be re-created.

   To do this, edit and then run the sample code listed in Sample Code for Generating a List of Tables to Rebuild.

2. Unload data from each non-empty table included in the list.
3. Drop each of the tables included in the list, including empty tables.
4. Synchronize the target database logical and physical schemas by launching the Database Configuration Wizard and selecting the Synchronize Schema Definition option. Specify values as follow:
   • When prompted to enter the database user name and password, specify values for the target database.
   • When prompted for the name of the repository with which the existing Siebel database is to be synchronized, specify the following values:
     • Production upgrades: Siebel Repository
     • Development upgrades: Prior Customer Repository

       The Wizard generates the DDL required to synchronize the Siebel database and the Siebel Repository. If you did not select the Run DDL Automatically installation option, then the Wizard generates files that you must apply on the z/OS database to re-create the tables you dropped in Step 3.

5. Reload the tables with the data that you unloaded in Step 2.

For detailed information on running the Synchronize Schema Definition process, see Implementing Siebel Business Applications on DB2 for z/OS.

Sample Code for Generating a List of Tables to Rebuild

The following sample SQL code can be used to generate a list of the target tables with LONG VARCHAR columns that need to be re-created before applying ADDITIVE schema changes.

Run this code against the staging schema after the additive changes are applied to the Siebel staging database. If no tables match the selection criteria in the code, then you do not have to rebuild any target tables.

--
-- CREATE A DROP LIST FOR LONGVARCHAR TABLES
--
SELECT
SUBSTR(
CONCAT(
CONCAT(' DROP TABLESPACE ' ,
CONCAT(STRIP(T.DBNAME) ,
CONCAT('.',
STRIP(T.TSNAME)
)
)
)
, ' /'
)
, 1, 36) AS STATEMENT
,CONCAT(' -- ', T.NAME) AS COMMENT
FROM SYSIBM.SYSTABLES AS T
WHERE
T.CREATOR = STAGING_TABLE_OWNER --<<< STAGING TABLE OWNER AND
T.NAME NOT LIKE 'EIM_%'
AND
EXISTS (SELECT 1 -- longvarchar table
FROM SYSIBM.SYSCOLUMNS C
WHERE C.TBCREATOR = T.CREATOR
AND C.TBNAME = T.NAME
AND C.COLTYPE = 'VARCHAR'
AND C.LENGTH=16350)
AND
EXISTS (SELECT 1 -- has been altered
FROM SYSIBM.SYSCOLUMNS K
WHERE K.TBCREATOR = T.CREATOR
AND K.TBNAME = T.NAME
AND K.ALTEREDTS != T.CREATEDTS)
ORDER BY 1
;

where STAGING_TABLE_OWNER is the staging database table owner in your environment.

The following is an example of a list of tables generated by running the query in the sample code:

DROP TABLESPACE D0000005.H1000000 / -- S_ORG_EXT
DROP TABLESPACE D0000006.H2000000 / -- S_NOTE_FUL_REQ
DROP TABLESPACE D0000007.H3000000 / -- S_NOTE_MDF