Upgrade Guide for DB2 UDB for z/OS and OS/390 > Upgrading the Database >

Migrating Address Data from Custom Extension Columns

Upgrades:

• From Release 6.x Siebel Industrial applications (except Communications, Media, and Energy) that you are upgrading directly to Release 7.7 Siebel Industrial applications
• From Release 6.x and 7.0.x Siebel Financial Services applications that you are upgrading to Release 7.7 Siebel Industrial applications
• From Release 6.x and 7.7 Siebel eBusiness applications that you are upgrading to Release 7.7 Siebel Industrial applications
• From Release 6.2.1 of Siebel Financial Services applications on IBM z/OS platforms that you are upgrading to Release 7.7.x of Siebel Industrial applications on IBM z/OS platforms

NOTE:  This topic does not apply to Siebel eBusiness applications (HOR) that you are upgrading to a later release of Siebel eBusiness applications (HOR).

This topic is part of an upgrade process. See How to Perform the Upgrade.

If you have custom extension columns on the Address table S_ADDR_ORG, you must manually edit your upgrade scripts to prevent loss of data during an upgrade. Perform the following task, and contact Siebel Technical Support or Siebel Expert Services if you need assistance.

Prerequisite: You must have run the Database Server Configuration utility to pause # 1. This creates the files required for this procedure.

To migrate Address data from custom extension columns

1. In the tmptable.ctl file, modify the definitions of S_ADDR_ORG_TMP and S_ADDR_PER_TMP to include your custom extension columns from the development environment.

   Here is the path to the file in the Siebel Database Server installation:

   Windows: DBSRVR_ROOT\DB2390

   UNIX: DBSRVR_ROOT/DB2390

   For example:

   [Object 2]

   Type = Table

   Name = S_ADDR_ORG_TMP

   Space = USERSPACE1

   Column 1 = ROW_ID WVARCHAR(15) NOTNULL

   Column 2 = CREATED TIMESTAMP NOTNULL DEFAULT %NOW%

   Column 3 = CREATED_BY WVARCHAR(15) NOTNULL

   Column 4 = LAST_UPD TIMESTAMP NOTNULL DEFAULT %NOW%

   Column 5 = LAST_UPD_BY WVARCHAR(15) NOTNULL

   Column 6 = DCKING_NUM NUMERIC(22,7) DEFAULT 0

   ... ... ...

   Column 46 = RURAL_ROUTE_NUM WVARCHAR(10)

   Column 47 = START_DT TIMESTAMP

   Column 48 = X_CUSTOM_EXTENSION_COLUMN1 WVARCHAR(15)

   Column 49 = X_CUSTOM_EXTENSION_COLUMN2 WVARCAHR(15)

2. Add all custom extension columns from S_ADDR_PER and S_ADDR_ORG to the S_CON_ADDR definition.
   • For development environment upgrades, use the ddl.ctl file to add these columns
   • For production environment upgrades, use the schema.ddl file to add these columns

     Here is the path to the file in the Siebel Database Server installation:

     Windows: DBSRVR_ROOT\DB2390

     UNIX: DBSRVR_ROOT/DB2390

3. In the preschm_sia.sql file, add the custom extension columns for S_ADDR_PER and S_ADDR_ORG to the migration scripts.

   Here is the path to the file in the Siebel Database Server installation:

   Windows: DBSRVR_ROOT\DB2390\upgrade\version

   UNIX: DBSRVR_ROOT/DB2390/upgrade/version

   For example:

   insert into S_ADDR_ORG_TMP (

   ROW_ID,CREATED,CREATED_BY,LAST_UPD,LAST_UPD_BY,

   DCKING_NUM,MODIFICATION_NUM,CONFLICT_ID,ACTIVE_FLG,ADDR_NAME,

   BL_ADDR_FLG,MAIN_ADDR_FLG,DISA_CLEANSE_FLG,NAME_LOCK_FLG,OU_ID,

   SHIP_ADDR_FLG,LATITUDE,LONGITUDE,ADDR,ADDR_LINE_2,

   ADDR_LINE_3,ADDR_NUM,ADDR_TYPE_CD,AREA_ID,CITY,

   COMMENTS,COUNTRY,COUNTY,DESCRIPTOR,DFLT_SHIP_PRIO_CD,

   EMAIL_ADDR,FAX_PH_NUM,PH_NUM,INTEGRATION_ID,INTEGRATION2_ID,

   INTEGRATION3_ID,PROVINCE,STATE,TRNSPRT_ZONE_CD,ZIPCODE,

   ADDR_MAIL_CD,ADDR_SUB_CD,BU_ID,END_DT,RURAL_ROUTE_NUM,START_DT

   ,X_CUSTOM_EXTENSION_COLUMN1 , X_CUSTOM_EXTENSION_COLUMN2 )

   select ROW_ID,CREATED,CREATED_BY,LAST_UPD,LAST_UPD_BY,

   DCKING_NUM,MODIFICATION_NUM,CONFLICT_ID,ACTIVE_FLG,ADDR_NAME,

   BL_ADDR_FLG,MAIN_ADDR_FLG,DISA_CLEANSE_FLG,NAME_LOCK_FLG,OU_ID,

   SHIP_ADDR_FLG,LATITUDE,LONGITUDE,ADDR,ADDR_LINE_2,

   ADDR_LINE_3,ADDR_NUM,ADDR_TYPE_CD,AREA_ID,CITY,

   COMMENTS,COUNTRY,COUNTY,DESCRIPTOR,DFLT_SHIP_PRIO_CD,

   EMAIL_ADDR,FAX_PH_NUM,PH_NUM,INTEGRATION_ID,INTEGRATION2_ID,

   INTEGRATION3_ID,PROVINCE,STATE,TRNSPRT_ZONE_CD,ZIPCODE,

   ADDR_MAIL_CD,ADDR_SUB_CD,BU_ID,END_DT,RURAL_ROUTE_NUM,START_DT

   ,X_CUSTOM_EXTENSION_COLUMN1 , X_CUSTOM_EXTENSION_COLUMN2

   from S_ADDR_ORG

4. Edit the preschm_sia.sql file to populate S_CON_ADDR from the temporary tables with the newly added custom extension columns.

   Here is the path to the file in the Siebel Database Server installation:

   Windows: DBSRVR_ROOT\DB2390\upgrade\version

   UNIX: DBSRVR_ROOT/DB2390/upgrade/version

   For example:

   insert into S_CON_ADDR

   (ROW_ID,

   CREATED, CREATED_BY, LAST_UPD, LAST_UPD_BY, MODIFICATION_NUM,

   CONFLICT_ID, ADDR_PER_ID, ADDR_TYPE_CD, ACCNT_ID,

   EMAIL_ADDR, FAX_PH_NUM, PH_NUM, DFLT_SHIP_PRIO_CD,

   TRNSPRT_ZONE_CD, BRICK_ID,

   SHIP_ADDR_FLG,ADDR_MAIL_CD,BL_ADDR_FLG,END_DT,MAIN_ADDR_FLG,START_DT, X_CUSTOM_EXTENSION_COLUMN1 , X_CUSTOM_EXTENSION_COLUMN2 )

   select

   s_sequence_pkg.get_next_rowid,

   CREATED, CREATED_BY, LAST_UPD, LAST_UPD_BY, MODIFICATION_NUM,

   CONFLICT_ID, NEW_ROW_ID, ADDR_TYPE_CD, OU_ID,

   EMAIL_ADDR, FAX_PH_NUM, PH_NUM, DFLT_SHIP_PRIO_CD,

   TRNSPRT_ZONE_CD , AREA_ID,

   SHIP_ADDR_FLG,ADDR_MAIL_CD,BL_ADDR_FLG,END_DT,MAIN_ADDR_FLG,START_DT

   , X_CUSTOM_EXTENSION_COLUMN1 , X_CUSTOM_EXTENSION_COLUMN2

   FROM S_ADDR_ORG_TMP SAOT

   WHERE

   NOT EXISTS

   (SELECT 'x' FROM S_CON_ADDR SCA1 , S_ADDR_PER TMP

   WHERE SCA1.ACCNT_ID = SAOT.OU_ID

   AND SCA1.ADDR_PER_ID = TMP.ROW_ID

   AND TMP.ADDR_NAME = SAOT.ADDR_NAME

   )

   AND SAOT.OU_ID IS NOT NULL

   To view the data in your custom extension columns, you must configure your application to expose the custom extension columns added to S_CON_ADDR.

5. Development environment upgrades only. In Siebel Tools, add the custom extension columns defined in the previous steps to S_CON_ADDR and S_ADDR_PER.

NOTE:  Confirm that the preschema_sia.jcl file includes all necessary extension columns for the data migration before ftping the file to the zSeries host.