|
Upgrade Guide for DB2 UDB for z/OS > Upgrading the Database >
Migrating Address Data from Custom Extension Columns
Upgrades:
- From Release 6.2.1 of Siebel Financial Services applications on IBM DB2 z/OS systems to Release 7.8.x of Siebel Industry applications on IBM DB2 z/OS platforms
- From Release 7.0.x of Siebel Financial Services applications to Release 7.8 of Siebel Industry applications
- From Release 7.8 of Siebel Business applications to Release 7.8 of Siebel Industry applications
NOTE: This topic does not apply to Siebel Business applications (HOR) that you are upgrading to a later release of Siebel Business applications (HOR).
Environments: Development, production test, production. 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
- Open the ddl.ctl file with a text editor.
NOTE: When you edit any of the upgrade files, they must be saved in UTF-8 encoding. Opening a file using Microsoft Notepad and clicking File > Save automatically saves the existing encoding.
Here is the path to the file in the Siebel Database Server installation:
Windows: DBSRVR_ROOT\DB2390
UNIX: DBSRVR_ROOT/DB2390
- Add the custom extension columns from
S_ADDR_PER and S_ADDR_ORG that are attributes of the association of the address to contact or account to the S_CON_ADDR definition.
Example of the file:
[Object nnn]
Type = Table
Name = S_CON_ADDR
Space = USERSPACE1
Column 1 = ROW_ID VARCHAR(15) OTNULL
Column 2 = CREATED TIMESTAMP NOTNULL DEFAULT %NOW%
Column 3 = CREATED_BY VARCHAR(15) NOTNULL
Column 4 = LAST_UPD TIMESTAMP NOTNULL DEFAULT %NOW%
Column 5 = LAST_UPD_BY VARCHAR(15) NOTNULL
Column 6 = DCKING_NUM NUMERIC(22,7) DEFAULT 0
... ... ...
Column 46 = RURAL_ROUTE_NUM VARCHAR(10)
Column 47 = START_DT TIMESTAMP
Column 48 = X_CUSTOM_EXTENSION_COLUMN1 VARCHAR(15)
Column 49 = X_CUSTOM_EXTENSION_COLUMN2 VARCHAR(15)
- In the ddl.ctl file, add the custom extension columns from
S_ADDR_ORG that are attributes of the address to the S_ADDR_PER definition (provided corresponding columns do not already exist on S_ADDR_PER) similar to the example illustrated in the previous step.
- In the file preschm_sia.sql, add the custom extension columns for
S_CON_ADDR to the migration scripts if any custom extension column was added to the definition of S_CON_ADDR in the ddl.ctl file in the preceding steps.
Here is the path to the file in the Siebel Database Server installation:
Windows: DBSRVR_ROOT\DB2390\DBOUTPUT\UPGRADE
UNIX: DBSRVR_ROOT/DB2390/DBOUTPUT/UPGRADE/
Example of the script (this example is for inserts into S_CON_ADDR from S_ADDR_PER. (The example also applies to custom extension columns to be inserted on S_CON_ADDR from S_ADDR_ORG):
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
,CONTACT_ID
,EMAIL_ADDR
,FAX_PH_NUM
,PH_NUM
,ADDR_MAIL_CD
,END_DT
,START_DT
,ACTIVE_FLG
,X_CUSTOM_EXTENSION_COLUMN1
,X_CUSTOM_ EXTENSION_COLUMN2)
select
ROW_ID
,CREATED
,CREATED_BY
,LAST_UPD
,LAST_UPD_BY
,MODIFICATION_NUM
,CONFLICT_ID
,ROW_ID
,ADDR_TYPE_CD
,PER_ID
,EMAIL_ADDR
,FAX_PH_NUM
,PH_NUM
,ADDR_MAIL_CD
,END_DT
,START_DT
,case
when ACTIVE_FLG is null then 'Y'
else ACTIVE_FLG
end
,X_CUSTOM_ EXTENSION_COLUMN1
,X_CUSTOM_ EXTENSION_COLUMN2
from S_ADDR_PER APT
where not exists
(select 'x' from S_CON_ADDR CAD
where CAD.CONTACT_ID = APT.PER_ID
and CAD.ADDR_PER_ID = APT.ROW_ID
and CAD.CONFLICT_ID = APT.CONFLICT_ID
)
and not exists
(select 'x' from S_CON_ADDR CAD1
where CAD1.ROW_ID = APT.ROW_ID
)
and APT.PER_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.
- In the file preschm_sia.sql, add the custom extension columns for
S_ADDR_PER to the migration script if any custom extension column was added to the definition of S_ADDR_PER in the ddl.ctl file.
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
Example of the file:
insert into S_ADDR_PER
(ROW_ID
,CREATED
,CREATED_BY
,LAST_UPD
,LAST_UPD_BY
,MODIFICATION_NUM
,CONFLICT_ID
,DISA_CLEANSE_FLG
,ADDR
,ADDR_NUM
,ADDR_TYPE_CD
,CITY
,COMMENTS
,COUNTRY
,COUNTY
,INTEGRATION_ID
,INTEGRATION2_ID
,INTEGRATION3_ID
,PROVINCE
,STATE
,ZIPCODE
,ADDR_NAME
,NAME_LOCK_FLG
,DESCRIPTOR
,EMAIL_ADDR
,FAX_PH_NUM
,PH_NUM
,ADDR_LINE_2
,ADDR_LINE_3
,DCKING_NUM
,LATITUDE
,LONGITUDE
,ACTIVE_FLG
,ADDR_MAIL_CD
,ADDR_SUB_CD
,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
,MODIFICATION_NUM
,CONFLICT_ID
,DISA_CLEANSE_FLG
,ADDR
,ADDR_NUM
,ADDR_TYPE_CD
,CITY
,COMMENTS
,COUNTRY
,COUNTY
,INTEGRATION_ID
,INTEGRATION2_ID
,INTEGRATION3_ID
,PROVINCE
,STATE
,ZIPCODE
,case when ADDR_NAME = (select t.DUP_ADDR_NAME
from TMPTBL_ADDR t
where t.DUP_ADDR_NAME = S_ADDR_ORG.ADDR_NAME and t.MIN_ROW_ID <> S_ADDR_ORG.ROW_ID
)
then {fn concat({fn concat({fn rtrim({fn substring(ADDR_NAME,1,99-{fn length(ROW_ID)})})},':')},ROW_ID)}
else ADDR_NAME
end
,NAME_LOCK_FLG
,DESCRIPTOR
,EMAIL_ADDR
,FAX_PH_NUM
,PH_NUM
,ADDR_LINE_2
,ADDR_LINE_3
,DCKING_NUM
,LATITUDE
,LONGITUDE
,ACTIVE_FLG
,ADDR_MAIL_CD
,ADDR_SUB_CD
,END_DT
,RURAL_ROUTE_NUM
,START_DT
,X_CUSTOM_EXTENSION_COLUMN1
,X_CUSTOM_EXTENSION_COLUMN2
from S_ADDR_ORG
To view the data in your custom extension columns, you must configure your application to expose the custom extension columns added to S_ADDR_PER.
- 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.
|
