Siebel EIM DELETE Process

This section explains different types of deletes, the steps involved in a delete process, manual procedure to be followed to accomplish an EIM delete process.

Delete Types:
There are three ways in which you can achieve the deletion of records through EIM. They are as follows:

DELETE EXACT – Where EIM uses a user key matching algorithm to identify the rows that needs to be deleted. This is done by matching the user key of the record in the interface table with that of the records in the base table. Once a match occurs, EIM deletes this record.
DELETE MATCHES – Where EIM deletes those records that match the conditional expression set in the IFB file using the parameter “DELETE MATCHES”.
DELETE ALL ROWS – Where EIM deletes all the rows from the base table irrespective of any conditions or user key matching.

Delete Process Steps:
Whenever an EIM delete process runs, it internally performs a sequence of tasks. Each task involves multiple passes where one pass is defined as an execution of one SQL statement.

Step1:
EIM performs an initialization process on EIM tables. It checks the CLEAR INTERFACE TABLE parameter defined in the IFB file. If this parameter is set to TRUE, it deletes all the rows in the EIM table for the specified batch number. The default value of the parameter depends on the type of delete (as explained in previous section) being performed.

Step2:
In this step EIM performs the actual deletion of the records. If the DELETE EXACT parameter is set to TRUE in the IFB file, EIM performs a user key matching process to identify those records that need to be deleted and deletes the same.

If the DELETE MATCHES parameter is defined in the IFB file, EIM uses this conditional expression and selects those records from the target base table and deletes them.
If the DELETE ALL ROWS parameter is set to TRUE in the IFB file, EIM deletes all the rows from the target base table.

Step3:
As a final step, EIM sets the IF_ROW_STAT of those records to “DELETED” in the EIM table. Also, when there’s a reference to this record in some other table, that record is deleted if the referenced column is a mandatory, otherwise the reference column is cleared.

Whenever a parent record is deleted, the child records are also deleted, if the foreign key column in the child table is mandatory, otherwise the foreign column is cleared.
The deletion of child record is decided based on the CASCADE DELETE ONLY parameter defined in the IFB file.

Manual Deleting Procedure:

Before you start the export process, it is necessary to take care of the following things.
Identify the Data: As a first step, identify the data you want to delete from the base table. Now check once or twice, whether you have selected the right data for deleting. Once the data is identified, decide what type of delete (explained in previous section) needs to be used.

Prepare EIM Table: Depending upon the type of delete, decide what data should be loaded into your EIM table. If you are achieving a delete through DELETE EXACT, you must load the data into EIM table in the mandatory columns with IF_ROW_STAT set to “FOR_DELETE”, the user key columns of the corresponding target and non-target table.

Edit Configuration File: Depending upon the type of DELETE you use, make changes in the configuration file. The configuration parameters are explained in the previous section. Always set the COMMIT EACH PASS & COMMIT EACH TABLE parameter to FALSE and ROLLBACK ON ERROR to TRUE, to avoid dangling references in case if a failure occurs during an EIM delete.

Verify Results: After executing the EIM task, verify the results by examining the EIM table. EIM stores the ROW_ID of the deleted rows in the EIM table, in T_DELETED_ROW_ID column. Query for the count of records whose IF_ROW_STAT = “DELETED”. Verify this count is equal to the count of records considered for deletion earlier.

------------------------END--------------------------

Siebel Interface Table

Interface tables act as staging area which holds the data that has to be imported, data exported from the Siebel base tables, data to be deleted and that to be merged. EIM process operates upon these tables and does the specified operation.

Every interface table has columns that get mapped to base tables, few mandatory columns and many temporary columns. The mandatory columns are ROW_ID, IF_ROW_STAT and IF_ROW_BATCH_NUM.
The temporary columns are those that are used by EIM task to manipulate data values at run times. These temporary columns store the ROW IDs of records, status of the record, uniqueness of the records, whether the record already exists in the database. Some temporary columns store the ROW ID values of deleted, merged, exported rows.

Every interface table, while loading data into, has to satisfy certain prerequisites as stated below:
Mandatory columns needs to be populated
All the required columns has to be populated
Populate the IF_ROW_BATCH_NUM with appropriate batch number
Set the IF_ROW_STAT column with meaningful values

Every Siebel interface table has 4 types of temporary columns as defined below:
UNQ column
EXS column
RID column
STA column

Each of the temporary columns has a naming format as follows:
T___
Example: T_ORG_EXT__UNQ, T_ORG_EXT__EXS, T_ORG_EXT__STA, T_ORG_EXT__RID


There are few exceptions for this naming rule.
Examples: T_ACCNTPOST__STA, T_OPTYPOSTN__RID, T_PRODLNPOSTN__RID
The temporary columns that have suffix UNQ, stores Y or N values if the interface record’s user keys are not same as another interface record’s user keys in the same batch.
The temporary columns that have suffix EXS, stores Y or N values if the interface record can be mapped to an existing base record (i.e. base record exists for the same user keys).
The temporary columns that have suffix RID, stores the ROW_ID of the record already existing in the base table or generated ROW_ID for new records in the interface table.
The temporary columns that have suffix STA, stores the pass number of the EIM process where it failed. Example: If the EIM process failed while processing a record in the S_ORG_EXT table, the temporary column T_ORG_EXT__STA is set the EIM pass number at that point of time.

-----------------------------------END-----------------------------------------

Different IF_ROW_STAT errors for Siebel EIM Import Process

Depending upon the type of failure, the IF_ROW_STAT column of the interface table records are set with different kinds of values.
AMBIGUOUS: Two rows in the base table have same user key but different conflict IDs.
DUP_RECORD_EXISTS: One or more rows that exist in the interface table exactly matches with base table record.
DUP_RECORD_IN_EIM_TBL: One or more rows have the same user key of another row within the interface table.
FOREIGN_KEY: When a required foreign key column in the target table could not be resolved.
IMPORTED: The rows got successfully imported for both target and not-target base tables.
IMPORT_REJECTED: A user-specified filter query failed for this row. Rows which were eliminated as a result of FILTER_QUERY.
IN_PROGRESS: This value is set in the first step, the initialization step, of the import process.
NOT_ALLOWED: This value is set when a particular operation is not permitted.
PARTIALLY_IMPORTED: The rows got successfully imported for target table but failed for one or more non-target tables.
PICKLIST_VALUES: Required bounded pick list value could not be resolved.
REQUIRED_COLS: One or more required columns were found NULL.
ROLLBACK: When EIM encounters an error during a roll back operation, this value is set.
SQL_ERROR: This value is set, when EIM fails importing, when the transaction logging is enabled.

---------------------END-----------------------------

Column Level Data Encryption for Siebel 8.0

NOTE: Please do not follow the steps given in the Bookshelf. Please refer the following steps to go about column level data encryption. If you want more details, refer the Bookshelf
Pre-requisites:
1) Note down all the Columns which you want to encrypt.
2) Columns which already has encrypted and unencrypted data together might have problems getting encrypted.
3) Please find out all the existing Enterprise Parameters Name and corresponding passwords.

Please follow the steps in order which they are mentioned.
Step 1.0) Changing the keyfile password.
For upgrading the Encryption level to 256-bit, we need to install the Siebel Strong Encryption Pack (SSEP). But before installing the SSEP we need to change the keyfile password.
Note: If we install the SSEP before changing the keyfile password, you will have to reinstall the Siebel Server, Siebel Application etc. Again.
Step 1) shut down any server components that are configured to use encryption.
Step2) The key file is stored in the Server Bin Directory as shown below
Step 3) Run the key Database Manager Utility on the server command prompt location.
keydbmgr.exe \u db_username \p db_password \l language \c config_file
Where u - User Name (e.g.: SADMIN)
p - Password (eg: SADMIN)
l – Language (e.g.: ENU)
c – Full location of the Configuration file

Step 2.0) Install Siebel Strong Encryption Pack
After changing the password, please install the Siebel Strong Encryption Pack (SSEP). For instructions to install the SSEP refer ‘Siebel Strong Encryption Pack Installation Guide ’.
Installing the SSEP will be mandatory if you want to use higher encryption for AES and RC2.
After installing the SSEP, the following files appear in the BIN subdirectory of the location
Keydbupgrade
sslcrsaxxx.dll
Where xxx refers to the key length that you selected

Step 3.0) Increase the Encryption Level
Step 1) first, take a backup of the keyfile.
Step 2) Make sure that the Siebel Gateway Name Server and the Siebel Server are running.
Step 3) Execute the following command on the server side command prompt.
(Keydbupgrade.exe is located in the Bin Directory on the server)

Keydbupgrade.exe /u db_username /p db_password /l language /c config_file
Where u - User Name (e.g.: SADMIN)
p - Password (e.g.: SADMIN)
l – Language (e.g.: ENU)
c – Full location of the Configuration file

Step 4) Adding a new Encryption key
Step 1) shut down any server components which are running.
Step 2) On the server side command prompt run
(Under server\bin directory)
>>Keydbmgr.exe /u db_username /p db_password /l ENU /c config_file
Step 3) to add an encryption key to the key file
>>enter 2.
Step d) Enter any seed data (e.g.: Temppass)
(Any seed data will do as long as it is at least seven characters long)
Step 4) Exit the utility by entering 3.
There should not be any error messages while exiting the utility.
Step 5) Distribute the keyfile to all Siebel Servers.
Step 6) Restart the server components which you had shut down.
By executing the above steps you ensure that a new key has been generated in the keyfile.To confirm, check the modified date of the keyfile.
Note: Please take a backup again of this keyfile and store it elsewhere.

Step 5) Resetting the Enterprise parameters
This is a very important step. Please do not miss this.
Basically, we are re-encrypting the parameters that have their values encrypted. If they are not re-encrypted to the new encryption level, Siebel Server attempts to decrypt the encrypted parameters using the original encryption key and compare it to the password entered.
For resetting the Enterprise Parameters, you will have to log on the Server Manager Command-Line Interface. (For minute details refer Siebel System Administration Guide)
Before starting you will need the Enterprise Server Name, Siebel Server Name and the Gateway name Server name.
Step 1) Go to the Sever side Command prompt. In the Bin directory, you will find srvrmgr.exe.
Run it using the following command,
Srvrmgr.exe /g gateway /e Enterprise /S Server /u SADMIN /p SADMIN
Where Gateway – Name of the Gateway
Enterprise – Name of the Enterprise
Server – Name of the Siebel Server
If you are not able to connect do not give the Siebel Server address.
There are many Enterprise parameters that you will have to change. They are
ApplicationPassword
CRC
ClientDBAPwd
CustomSecAdpt_CRC
CustomSecAdpt_TrustToken
DBPassword
DBSecAdpt_CRC
DSPassword
DSPrivUserPass
DbaPwd
ExtDBPassword
ExtPasswd
KeyFilePassword
MailPassword
NewDbaPwd
Password
PrivUserPass
SAPRfcPassword
TableOwnPass
TrustToken
Step 2) Change each parameter with the following command
>>Change ent param ApplicationPassword=
>>Change ent param ClientDBAPwd =
and so on…
Change the Password of all the parameters which are mentioned above. Some of them you might not be using, in that case you will get that the parameter is not found. Just ignore it.
NOTE: Please find the existing parameters from the concerned people and as to what they want to set it to.
Step 3) Exit the sever manager command line.

Step 6) Distribute the keyfile to all the servers.

Step 7) Upgrading the existing encrypted and un-encrypted data to use the new encryption level.
Step 1) Configuring the Columns required for Encryption
We need to configure the required column and a new column needs to be created. This is an index key.
Step 2) Creating an Input file
We need to create a input file which contains all the Columns which needs to be encrypted. Name the file as ‘encrypt_columns.inp’. You can encrypt all the columns at once or one at a time.
It is saved and stored in the BIN directory in the server folder.
Format of the Input file is as follows
[S_CONTACT]
X_CC_NUM S_CONTACT X_CCNUM_ENCRPKEY_REF
WHERE (blank if you do not want any condition)
[S_CONTACT_X]
X_SOC_SECURITY_NUM S_CONTACT_X X_SECURITY_ENCRPKEY_REF
WHERE (blank if you do not want any condition)
The utility to run the Encryption is ‘encryptupg.exe’ and it is stored in the Server\Bin Directory.
From SIEBEL_ROOT\siebsrvr\bin, enter the following command:

>>encryptupg.exe /f FromEncrytionStrength /t ToEncryptionStrength /j InputFileName
/l Language /u UserName /p Password /c ConfigurationFile /L LogFile
Where /f – FromEncrytionStrength (e.g. NONE/RC2)
/t - ToEncryptionStrength (e.g. AES)
/j - InputFileName (full path of the Input file (encrypt_columns.inp))
/l - Language (e.g. ENU)
/u - UserName (e.g. SADMIN)
/p - Password (e.g. SADMIN)
/c - ConfigurationFile (full path of the Configuration file)
/L - LogFile (by default it is encryptupg.log—this is optional)

-------------------------END-------------------------

EIM Data Load Order for any new Siebel Implementation

Many a time we wonder what would be the ideal Data Load order for a new Siebel Implementation. There are different ways to load the Administrative data such as the LOVs, Responsibilities etc. like the use of an Excel loader. But for those who are not comfortable with VB coding, the best way is to use EIM.
Following is the load order that should be used:

1. Administrative Data (Ex:List of values,Responsibilities,Views)
2. Business Units (Organizations)
3. Positions
4. Account
5. Contact
6. Address
7. Employees
8. Products
9. Opportunities
10. Personal Accounts
11. Quotes
12. Documents
13. Forecasts
14. Fulfillment
15. Marketing Campaigns
16. CPG Promotion Management
17. CPG Product Movement
18. Service Requests
19. Product Defects
20. Activities and Appointments
21. Notes
22. File Attachments
In some cases, the import order might change slightly depending upon the business requirements.

-------------------------END-------------------------