Siebel Database Upgrade Guide for DB2 for z/OS > Upgrading the Target Database >

Dropping Partitioned EIM Tables

During the target database upgrade, EIM tables are dropped after additive changes are applied to the database and are later re-created when the nonadditive schema changes are applied to the database.

When a partitioned EIM table is dropped, however, the table space is also dropped. Because table space creation is considered an additive change, the upgrade attempts to create EIM table spaces for partitioned EIM tables when other additive changes are applied to the target database, that is, before the partitioned EIM tables and related table spaces have been dropped.

To avoid processing errors during the target database upgrade, therefore, you must manually change the sequence in which partitioned EIM tables are dropped. Drop partitioned EIM tables before you apply additive schema changes to the target database.

The following procedure describes how to change the sequence in which partitioned EIM tables are dropped.

To drop partitioned EIM tables

1. Run the sample code listed in Sample Code for Generating a List of Table Spaces to Drop to generate a list of the table spaces that contain partitioned EIM tables.

   A DROP command is also generated for each of the table spaces.

2. Run the generated DROP commands against the target database before you apply additive schema updates.

   The table spaces are re-created when you apply additive upgrade changes to the database.

3. Edit the data set SIEBEL.INSTALL.JCL(INFDRPT) and delete the steps that drop the table spaces containing partitioned EIM tables, that is, delete the steps that drop the table spaces you previously dropped in Step 2.

   The data set SIEBEL.INSTALL.JCL(INFDRPT) is run as part of the process of Preparing the Target Database for the Upgrade.

Sample Code for Generating a List of Table Spaces to Drop

The following sample SQL code can be used to generate a list of the table spaces that must be dropped before applying additive schema changes.

The table spaces can be determined with the following SQL:

--
-- CREATE DROP STATEMENTS FOR EIM PARTITIONED TABLES
--
SET CURRENT SQLID='xxxxxxxx' ; <-- set to current TARGET tableowner
SELECT
SUBSTR(
CONCAT(
CONCAT(' DROP TABLESPACE ' ,
CONCAT(STRIP(S.DBNAME) ,
CONCAT('.',
STRIP(S.NAME)
)
)
)
, ' /'
)
, 1, 36) AS STATEMENT
FROM SYSIBM.SYSTABLES T
, SYSIBM.SYSTABLESPACE S
WHERE T.CREATOR = CURRENT SQLID
AND T.NAME LIKE 'EIM_%'
AND T.DBNAME=S.DBNAME
AND T.TSNAME=S.NAME
AND T.TYPE='T'
AND S.PARTITIONS>0
ORDER BY 1;

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

DROP TABLESPACE D0020004.H0004000 /

DROP TABLESPACE D0020010.H0010000 /

DROP TABLESPACE D0020031.H0031000 /

DROP TABLESPACE D0020065.H0065000 /

DROP TABLESPACE D0020102.H0102000 /

DROP TABLESPACE D0020194.H0194000 /

DROP TABLESPACE D0020255.H0255000 /

DROP TABLESPACE D0020309.H0309000 /