This article presents a case study where a data fix is required on Siebel data. The data fix is performed using a combination of:
1. PLSQL to identify the data and populate the corresponding EIM table with the data to update.
2. Siebel EIM to load the data from EIM tables to Siebel base tables.
Let's say that a data problem exists where for all records in table S_ORG_EXT the MASTER_OU_ID column should be equal to the ROW_ID column however there are thousands of records where the MASTER_OU_ID columns is not equal to ROW_ID. Therefore we want to use Siebel EIM to bulk fix so that for all records in table S_ORG_EXT where MASTER_OU_ID <> ROW_ID that we set MASTER_OU_ID = ROW_ID.
I am going to use the same methodology that I discussed in the Siebel EIM Mapping - Step by Step Guide. Please see this guide for more detailed information on the steps below.
1. Determine base table: S_ORG_EXT, S_PARTY (S_ORG_EXT and S_PARTY always go together as do S_CONTACT and S_PARTY and S_POSTN and S_PARTY when using Siebel EIM).
2. EIM mode is to update existing data no need to map required columns in EIM tables, only user key is required to identify the record to be updated.
User key columns for S_ORG_EXT: NAME, BU_ID, LOC.
User key columns for S_PARTY: PARTY_UID, PARTY_TYPE_CD
Data update columns: MASTER_OU_ID.
3. Siebel EIM table that can be used: EIM_ACCNT_CUT.
4. EIM to Base table mappings for data update columns:
MASTER_ACCNT_BU/MASTER_ACCNT_LOC/MASTER_ACCNT_NAME > S_ORG_EXT.MASTER_OU_ID
5. Required base table columns are not required to be populated as this is a Siebel EIM update not an insert.
6. EIM to Base table mappings for user keys:
AC_NAME > S_ORG_EXT.NAME
AC_LOC > S_ORG_EXT.LOC
AC_BU > S_ORG_EXT.BU_ID
PARTY_TYPE_CD > S_PARTY.PARTY_TYPE_CD
PARTY_UID > S_PARTY.PARTY_UID
7. Other required EIM table columns:
ROW_ID = a unique row number
CREATED = default this to SYSDATE
CREATED_BY = default this to 'SADMIN' – it doesn't matter
IF_ROW_BATCH_NUM = this is the batch number for the EIM job (417) can be any number
IF_ROW_STAT = this is defaulted to FOR_IMPORT if Siebel EIM insert/update
8. Now we need to create a SQL statement that will insert into EIM for all records in S_ORG_EXT where MASTER_OU_ID <> ROW_ID. Here is the SQL for Oracle database:
1: INSERT INTO EIM_ACCNT_CUT
2: (
3: ROW_ID
4: ,CREATED
5: ,CREATED_BY
6: ,IF_ROW_STAT
7: ,IF_ROW_BATCH_NUM
8: ,PARTY_TYPE_CD
9: ,PARTY_UID
10: ,AC_NAME
11: ,AC_LOC
12: ,AC_BU
13: ,MASTER_ACCNT_NAME
14: ,MASTER_ACCNT_LOC
15: ,MASTER_ACCNT_BU
16: )
17: SELECT
18: r.RN
19: ,SYSDATE
20: ,'SADMIN'
21: ,'FOR_IMPORT'
22: ,417 + TRUNC(r.RN/5000)
23: ,r.PARTY_TYPE_CD
24: ,r.PARTY_UID
25: ,r.NAME
26: ,r.LOC
27: ,'Default Organization'
28: ,r.NAME
29: ,r.LOC
30: ,'Default Organization'
31: FROM
32: (
33: SELECT ROWNUM RN
34: ,par.PARTY_TYPE_CD
35: ,par.PARTY_UID
36: ,org.NAME
37: ,org.LOC
38: ,org.NAME
39: FROM S_ORG_EXT org
40: ,S_PARTY par
41: WHERE par.ROW_ID = org.PAR_ROW_ID
42: AND org.MASTER_OU_ID <> org.ROW_ID
43: ) r;
44:
45: COMMIT;
Note the following:
- The SQL is formed so that for every 5000 records the batch number is incremented (417 + TRUNC(r.RN/5000). This is because Siebel EIM tasks must be executed in batches of no more than 5000 records due to performance reasons.
- The join between S_ORG_EXT and S_PARTY.
- The statement inserts a record into EIM_ACCNT_CUT for every record in S_ORG_EXT and corresponding S_PARTY record where S_ORG_EXT.MASTER_OU_ID <> S_ORG_EXT.ROW_ID.
9. Next step is to create the ifb configuration file which controls the EIM task. File master_ou_id.ifb was created which contains the following:
[Siebel Interface Manager]
[Task1]
TYPE=IMPORT
BATCH=417
TABLE= EIM_ACCNT_CUT
ONLY BASE TABLES = S_ORG_EXT, S_PARTY
ONLY BASE COLUMNS = S_ORG_EXT.MASTER_OU_ID, S_ORG_EXT.NAME, S_ORG_EXT.BU_ID
[Task2]
TYPE=IMPORT
BATCH=418
TABLE= EIM_ACCNT_CUT
ONLY BASE TABLES = S_ORG_EXT, S_PARTY
ONLY BASE COLUMNS = S_ORG_EXT.MASTER_OU_ID, S_ORG_EXT.NAME, S_ORG_EXT.BU_ID
[Task3]
TYPE=IMPORT
BATCH=419
TABLE= EIM_ACCNT_CUT
ONLY BASE TABLES = S_ORG_EXT, S_PARTY
ONLY BASE COLUMNS = S_ORG_EXT.MASTER_OU_ID, S_ORG_EXT.NAME, S_ORG_EXT.BU_ID
[MASTER_OU_ID_UPDATE]
TYPE=SHELL
INCLUDE="Task1"
INCLUDE="Task2"
INCLUDE="Task3"
The above file specifies that there is a main process called MASTER_OU_ID_UPDATE which is a shell that runs 3 sub tasks called Task1, Task2 and Task3 each with a different batch number. The reason there is 3 sub batches is because batches are divided into groups of 5000 records. Therefore you should determine the amount of batches by the amount of data that needs to be updated with EIM in the target environment. This is achieved by determining the number of records returned by the SQL statement above that is part of the insert statement. You then divide this number by 5000 to determine the number of batches. The number of records returned by the above SQL statement in the target environment is 14000, therefore to accommodate this data in batches of 5000 records we need 3 batches.
For the above ifb file, note the following:
- TYPE=IMPORT specifies that the EIM task is an insert/update not a delete or merge.
- BATCH=417 specifies the batch number for the EIM process defined.
- TABLE= is used to define the EIM table used for the EIM task.
- ONLY BASE TABLES = is used to limit the base tables that are inserted/updated for the EIM task
- ONLY BASE COLUMNS = is used to ensure that only the target data column MASTER_OU_ID and S_ORG_EXT user key columns are to be updated.
10. The import process is now ready to commence. Therefore execute the insert statement from step 8 using a SQL editor in the target environment.
Once this has completed, you need to copy the ifb file to the %SIEBROOT%/admin directory (where %SIEBROOT% is the directory path to the Siebel server directory root - usually where the siebsrvr directory is) on the Siebel server host. This is the directory where EIM automatically looks for ifb files.
Now it is time to execute the EIM task. Login to the Siebel application and navigate to Administration - Server Management > Jobs. Create a new job record specifying the following parameters:
Component/Job = Enterprise Integration Manager
Configuration File = master_ou_id.ifb
Process = MASTER_OU_ID_UPDATE
Start the job. The job status goes to Active. Wait for the job to change status.
11. Once the job has completed you need to verify that the job completed successfully. Using SQL, perform a query to verify that the data has now been fixed:
SELECT COUNT(1)
FROM S_ORG_EXT org
,S_PARTY par
WHERE par.ROW_ID = org.PAR_ROW_ID
AND org.MASTER_OU_ID <> org.ROW_ID;
This query should return 0 if the data fix had worked. If the query does not return 0 you need to determine why the EIM job did not work. This is a matter of checking the status of the EIM records that were not fixed and checking the EIM log file for errors. If all EIM records were imported successfully the IF_ROW_STAT column in the EIM table would be updated to value 'IMPORTED'. If not IMPORTED, then the status would indicate why the EIM record did not import.
If you cannot determine why the job has failed from the IF_ROW_STAT column, then check the log file. An easy way to find the EIM log file is that it would be the latest created log file on the Siebel server with first 3 letters of the log file name being "EIM". Do a search in the log file for keyword "severity" this will find all the EIM failures in the log file quickly. The severity of each failure will be number (eg. Severity 8 ) usually severity 8 does not cause the actual EIM job to fail, severity 8 usually means that a foreign key was not resolved to a foreign table record. Therefore when you find any failures of severity 7 or less, the associated error information will tell you what you need to know.
Another good method to use to determine if data was imported is to use the "EIM ROW_ID Proc Column". This column is found in Siebel Tools > Object Explorer > EIM Interface Table > EIM Table Mapping. This column specifies the column in the Siebel EIM table which is populated with the ROW_ID of the corresponding base table of the EIM Table Mapping base table.
For example, for the current example of the EIM_ACCNT_CUT EIM table importing data to the S_ORG_EXT table. In Siebel Tools Object Explorer, go to EIM Interface Table, query for the EIM_ACCNT_CUT interface table. Then expand EIM Interface Table in the object explorer and click on the EIM Table Mapping. Then query for Destination Table = S_ORG_EXT. Now for this record the "EIM ROW_ID Proc Column" = T_ORG_EXT__RID. Therefore this column in EIM_ACCNT_CUT will be populated with the value in ROW_ID for the S_ORG_EXT record if the EIM import was successful.
Therefore it is possible to verify import through querying through this mechanism. For example:
1: SELECT *
2: FROM EIM_ACCNT_CUT
3: WHERE T_ORG_EXT__RID IS NULL;
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
