IFB to delete all records from S_ORG_EXT table that were created on the previous day

Presently I came across one of my friend's requirement for an IFB to delete all records from S_ORG_EXT table that were created on the previous day and with value of SRV_PROVDR_FLG as Y, so that IFB can be scheduled to run on a particular interval and delete process can be automated.

Here, I am sharing it with you guys and you can make use of it on any table and for deleting records in any time period just by changing the parameters given, as per your requirement.

How to Load Responsibilities and Views through EIM

Lets create three temporary tables firstly, say TEMP_VIEW,TEMP_RESP and TEMP_VIEW_RESP. These tables can be used as below :-

TEMP_VIEW - to store information about Views such as view name and view description
TEMP_RESP - to store information about Responsibilities such as responsibility name, responsibility description and responsibility organization.
TEMP_VIEW_RESP - to store views and their associated responsibilities.

At first, you have import data to load to these temporary tables. From these temporary tables data should be imported into the interface tables in three stages. At first, you have to import the views to 'S_APP_VIEW', then responsibilities to 'S_RESP' and then have to populate inter table 'S_APP_VIEW_RESP' with view-responsibility association information.

Let's below look into the script used for loading the Views and Responsibilities:-

Export All Rows IFB File

Here, Let's look at the IFB file which can be used to export entire records from a Siebel base table. For that, you have to just include the parameter 'EXPORT ALL ROWS' in the IFB file and its value should be set to true. 
Here is the IFB file to be used :-

[Siebel Interface Manager]
PROCESS = CASE_EXPORT
[CASE_EXPORT]
TYPE = SHELL
INCLUDE = "EXPORT_CASE"

Export Matches IFB File

Here, Let's look at the IFB file which can be used to export certain amount of records that matches a condition.If u need only some specific records to be exported from a Siebel database, you can make use of this.

The thing you have to do is, you have to specify the SQL statement to select the specific records needed with in the 'EXPORT MATCHES' parameter in the IFB.
Here is the IFB file used :-

[Siebel Interface Manager]
PROCESS = CASE_EXPORT
[CASE_EXPORT]
TYPE = SHELL
INCLUDE = "EXPORT_CASE"

EIM Interview Questions

Q 1 : When I tried to set the primary employee for the position through EIM, it is not getting updated. Why?

Ans :
While updating the primary employee for position, apart from setting the POSTN_PR_EMP column in the EIM table, you should also define the MISC SQL parameter in your IFB file.

Q 2 : Although setting the trace flags, I’m not able to see log information in the log file. Why?
Ans :
You should configure your component event levels as described below :-

                               Event Type                                                  Log Level
                            ----------------------                                              ---------------
                            SQL Tracing                                                          4
                            SQL Summary                                                      4
                            Task Configuration                                                 4
                            Component Tracing                                                3

Q 3 : What does IFB stands for?
Ans :
IFB stands for InterFace Builder.

Q 4 : Can I use more than one IFB file for an EIM process?
Ans :
No, it’s not possible. You can use shell process in case if you want to do more than one operation in a single EIM task.

EIM Merge Process Overview

• This process merges multiple records into one surviving record.
• During Merge Process we identify the Parent record and Child records to be merged into that.
• Records loaded in the identified interface table are matched against the corresponding base tables based on the user primary keys.
• All the references (ROW_ID) of Child records will be replaced by parent record and Child records will be deleted.
• While Populating the EIM ,IF_ROW_MERGE_ID is used to distinguish between  parent and child records by EIM process.

EIM Delete Process Overview

• This process deletes the records from the Siebel base tables.
• Deletes records from the base table by identifying the records in the base on the basis of the user keys specified in the corresponding interface table.
• Deletes all records from the Siebel base table if the parameter DELETE ALL ROWS is specified as true. For this process the records are not populated in the interface table. Even if the records are populated on using this parameter EIM component skips these records and proceed by deleting all the rows in the given base table
• If  the DELETE MATCHES parameter is specified then only those records are deleted that matches the condition in the WHERE clause.  
• On successful deletion the IF_ROW_STAT column of the interface table will have the status as DELETED.

EIM Export Process Overview

• This process allows to pull the existing records from the Siebel base tables.
• The interface table need to be correctly identified.
• Create the configuration file with suitable parameters as in EXPORT ALL or EXPORT MATCHES according to the requirement
• Start the EIM job manually by logging into the application and navigating to Administration – Server or via the command line.

EIM Import Process Overview

• The process drives the new record inserts and updates on existing records in the Siebel base table.
• The interface table need to be correctly identified.
• Load the business data into the identified interface tables. 
• Create the configuration file.
• Set the value of IF_ROW_STAT to FOR_IMPORT before load.
• Make sure all the user key columns and the required columns for the targeted base tables are populated in the corresponding columns mapped in the interface tables.
• Start the EIM job manually by logging into the application and navigating to Administration – Server or via the command line.

An Overview to EIM Process

There are four types of process in EIM. They are :-

• Import Process.
• Export Process.
• Delete Process.
• Merge Process.

Import Process

• Import Process is basically used to Insert or Update the data from external system into the Siebel base tables.
• Identify and validate the data to be imported.
• Identify the column mappings and user key columns of the data to be imported.
• Import the data in the order of the business model.
• Set the value of IF_ROW_STAT to FOR_IMPORT before load which will get changed to IMPORTED after process successfully commits.

To know more on import process, click here . . .

Merge Process Parameters in IFB

These are the parameters which are only applicable to EIM Merge process and are written with in the Process section in an IFB file used for merge process. They are :-

SET BASED LOGGING

• This parameter specifies whether to enable the set based logging.
• When enabled, a separate log entry will be generated for all the rows in each table that were processed by EIM. This allows greater performance.
• When disabled, each and every row processed by EIM will have an entry in the transaction table.
• Valid values are TRUE or FALSE.
• Default value is TRUE.
• Example: SET BASED LOGGING = TRUE

Delete Process Parameters in IFB

These are the parameters which are only applicable to EIM Delete process and are written with in the Process section in an IFB file used for delete process. They are :-

CASCADE DELETE ONLY

• Optional parameter that tells whether to delete the child records when a parent record is deleted.
• Valid values are TRUE or FALSE.
• Default value is FALSE.
• If set to FALSE, EIM deletes the parent record and sets the foreign key column in the child records to NULL.
• Example: CASCADE DELETE ONLY = TRUE

Export Process Parameters in IFB

These are the parameters which are only applicable to EIM Export process and are written with in the Process section in an IFB file used for export process. They are :-

ATTACHMENT DIRECTORY

• Optional parameter that specifies the directory path to where the attachments have to be exported.
• This directory path should exist on one of the Siebel server machine.
• In case of remote machine, map the path to a drive and specify the drive name to this parameter.
• Default value is <SIEBEL_HOME>\OUTPUT
• Example: ATTACHMENT DIRECTORY = X:, Where X: is mapped to \\CRMDEV02\siebsrvr\OUTPUT

Import Process Parameters in IFB

These are the parameters which are only applicable to EIM Import process and are written with in the Process section in an IFB file used for import process. They are :-

COMMIT OPERATIONS

• Optional parameter that specifies the commit frequency while logging the transactions.
• Value specifies the number of records processed before a commit is done.
• Applicable only if DOCKING: TRANSACTION LOGGING is set in System Preferences.
• Default value is 0, which means commit has to be made at the end of EIM process.
• Example: COMMIT OPERATIONS = 100

General Process Parameters in IFB

These are the parameters which are common to all the EIM process (Import,Export,Delete,Merge) and are written with in the Process section in an IFB file. They are :-

BATCH

• Required field that specifies the batch number, which is used to identify the set of records that should be considered for this EIM process.
• This batch number corresponds to the number specified in the IF_ROW_BATCH_NUM column of the EIM table.
• Valid values are 0 to 2147483647 (2³¹ – 1).
• Batch numbers can be specified in ranges or as a comma delimited format.
• Example: BATCH = 100 – 110, BATCH = 100, 103, 120.
• If batch number is specified wrongly in IFB file, the EIM throws an error stating, no records found for the stated batch number.

General Header Parameters in IFB

These are the global parameters which are used in an IFB file and are written with in the Header section in an IFB file. They are :-

CONNECT

• Specifies the ODBC source name of the database to be used

LOG TRANSACTIONS TO FILE

• This parameter specifies whether the transactions to be logged to a file or docking transaction table.
• Valid values are TRUE or FALSE.
• If set to TRUE, the transaction details are logged into the directory <SIEBEL_FILE_SYSTEM>EIM.
• If set to FALSE, the transaction details are logged into the S_DOCK_TXN_LOG table.
• This parameter doesn’t make sense, if DOCKING: TRANSACTION LOGGING parameter in System Preferences is set to FALSE.

IFB Parameters for EIM

The parameters used in an IFB are classified as follows:

• General Header Parameters.
• General Process Parameters.
• Import Process Parameters.
• Export Process Parameters.
• Delete Process Parameters.
• Merge Process Parameters.

General Header Parameters

These are the global parameters which are used in an IFB file and are written with in the Header section in an IFB file. They are :-

• CONNECT
• LOG TRANSACTIONS TO FILE
• PASSWORD
• PROCESS
• [Siebel Interface Manager]
• TABLEOWNER
• USER NAME

To know more on general header parameters, click here . . .

EIM Configuration File (IFB File)

An EIM process reads a configuration file, where different kinds of parameters are set for an EIM process. The configuration file, in short IFB file, defines the type of process, EIM table involved, batch number, and lots of other parameters.

An IFB file has two sections :-

1. Header Section.
2. Process Section.

Header Section

The header section defines the parameters that are common to all the processes defined in the file. 

Process Section

The process section defines parameters which are specific to that particular section.

Temporary Columns in EIM Tables

EIM tables (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.

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_<base table without prefix S_>__<temporary column type> "

       Examples: 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

Siebel Interface Tables (EIM Tables)

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 mainly 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.

An Overview on EIM Functions and its requirements

Siebel EIM can be used to achieve four kinds of functionality in which all the functions deal with the data directly. The functions are :-

• Import new and revised data into Siebel Base Tables.
• Export existing data from Siebel Base Tables.
• Delete existing data from Siebel Base Tables.
• Merge existing data in Siebel Base Tables.

Importing Data

Data importing comes into picture under following business requirements:

• Initial implementation of Siebel application (Loading product data from the legacy database).
• Maintaining the Siebel database in long run (Importing archived data).
• Periodical updating of data into Siebel database from a non Siebel database.

Data Flow in EIM

Siebel EIM is server task that deals with the exchange of data between intermediary tables called interface tables and Siebel base tables.
In Siebel, we should not populate data directly into the base tables. At first, we have to populate data from legacy (an external database or a flat file) into the interface tables (EIM tables) and then from the interface table to its corresponding base table by running the EIM job.

EIM Mandatory Columns

For an EIM process to be successful , we must populate values in a certain set of columns.
The Mandatory Columns in an EIM Process are :

• ROW_ID
• IF_ROW_BATCH_NUM
• IF_ROW_STAT
• IF_ROW_MERGE_ID

In this set of four columns, IF_ROW_MERGE_ID is only mandatory when we are carrying out EIM Merge process. So we can look on to these columns in detail and what data to be populated on these.

An Introduction to EIM

EIM stands for Enterprise Integration Manager. Siebel EIM is a process provided by the Siebel CRM package, in order to import the legacy data, export, delete or merge existing data. It is a SIEBEL Component which is used for Data Migration from legacy as well as other Siebel systems. EIM is much fascinating and challenging as area in Siebel to work on.

Why EIM:

Siebel has very unique and complicated Database structure which already has relationship defined between 'n' number of tables and hence it is not recommended to insert, delete or update data stored in what is known as Base Tables in Siebel. During migration, lot of things can happen which can lead to data integrity issues. To answer all those concerns Siebel has provided EIM.
For most of base tables in Siebel, an EIM table is also in existence which allows you to play with data and perform thing that you normally wouldn’t want to perform on base tables. So, you can import-export data to and from EIM tables.

EIM is mainly used :

• To Import bulk amounts of data from external sources to base table.
• To Delete bulk amounts of data.
• To Update bulk amounts of data.

Discussion Forum

Lets discuss here and learn ourselves. This space is dedicated for discussions where you can comment your thoughts, ideas, experiences, queries on topics like Siebel EIM, SQL etc. 

Also you can mail your queries, ideas, experiences, or feedback's  to 'eimtoolbox@gmail.com' and i will post them in blog on your behalf.