Exporting Data

This chapter explains how to export data from the Siebel base tables into the EIM tables. This chapter is organized into the following sections:

Overview of EIM Export Processing

To export data, EIM reads the data in the Siebel database tables and places the information in the appropriate EIM tables. You can then copy data from the EIM tables into another database. The export process generally populates the applicable EIM table with a row for every Siebel base table row encountered. As a consequence, where EIM tables have mappings to multiple Siebel base tables, one export operation can generate multiple rows within the EIM table governing the rows encountered within the Siebel base tables.

During its multiple passes through the EIM tables, EIM performs the following tasks:

EIM initializes the EIM tables for export.
It applies filter logic to select rows for exporting.
EIM updates EIM table rows to indicate the export status.

EIM then provides comprehensive status information about each export process. When the process ends, you should review this information. See EIM Export Process for more details on how EIM functions in the export process.

The following tasks comprise an EIM export process:

Upon completion of the EIM process, your database administrator can access the EIM tables and extract the data for use in a non-Siebel application.

EIM Export Process

To export tables of data, EIM performs a sequence of tasks. Each task involves multiple passes; at least one pass is required for each EIM table included in the process.

To export data to EIM tables, EIM performs the following steps:

EIM initializes EIM tables for export.
If clear interface Table in the configuration file is true, all rows with the specified batch number are deleted. Otherwise, a warning is issued if rows already exist with the specified batch number. The default configuration file is default.ifb.
It uses export parameter expressions in the configuration file to locate and export table rows:
- If export all rows is true, ignore any export matches parameters and export all rows.
- If export all rows is false, use export matches parameters to locate specific rows.
Set if_row_stat to exported for rows that are successfully exported.
For parent tables, EIM locates child table rows and exports them to their corresponding EIM tables.

Note: Transaction logging does not occur during export operations because Siebel base table values are not modified.

Preparing the EIM Tables for Export Processing

Unlike other Open Interfaces processes, an export process requires minimal preparation of the EIM tables. During the first step of export processing, EIM inspects each EIM table involved in the process. If EIM finds a row whose if_row_batch_num matches the batch number for this export process, it does one of the following:

Clear the row if the CLEAR INTERFACE TABLES parameter is set to TRUE in the EIM configuration file
Issue a warning if the CLEAR INTERFACE TABLES parameter is set to FALSE in the EIM configuration file

For information on the CLEAR INTERFACE TABLES parameter, see Parameters Used for Exports in Both the Header and Process Sections.

Checking Existing Rows Batch Numbers

Before you initiate an export process, you should verify that rows do not contain an if_row_batch_num matching the batch number you plan to use. If such rows do exist, you should either make sure that they do not contain data you need to preserve, or change the batch number for the export process. In each row that you are exporting, you may also want to set the IF_ROW_STAT column to FOR_EXPORT.

Preserved Column Values

The values for the LAST_UPD and CREATED columns in the EIM tables always contain the values for the LAST_UPD and CREATED columns from the target base table. For example, if you use the EIM_CONTACT interface table to export data from the S_CONTACT and S_ADDR_PER base tables, the values of the EIM_CONTACT.LAST_UPD and EIM_CONTACT.CREATED columns contain the data from the S_CONTACT.LAST_UPD and S_CONTACT.CREATED columns, respectively.

EIM Tables Not Supported for Export Processes

Due to the complexity of the associated base tables, EIM export processes to the following interface tables are not supported:

EIM_ACCSRCPIDTL
EIM_CRSE_TSTRUN
EIM_IC_CALC
EIM_IC_PERF_HST
EIM_MDF

For more information on special columns, see EIM Table Columns. For general information on EIM tables, see Siebel EIM Tables.

Editing the Configuration File for Export Processing

This section describes the header and process sections that you need in the EIM configuration file to properly configure EIM for an export process. For general information on the EIM configuration file, see EIM Configuration File.

Before export processing begins, you must change the configuration file to support this function. Such changes include:

Editing the configuration file header and process sections using the parameters specific to export processes. For general information on the EIM configuration file, see EIM Configuration File.
Altering configuration file settings for the following purposes:

Header Section Parameters Used for Exports

Parameters in the header section generally apply to all types of processes. For a description of the necessary contents in the header section, see Header Section Parameters Generic to All EIM Processes.

Process Section Parameters Used for Exports

Parameters in the process section apply only to that specific process and override any corresponding value in the header section for the specific process. This section describes the parameters used in the process section that are specific to an export process. For generic parameters that can be used in all EIM processes, see Process Section Parameters Generic to All EIM Processes.

To export data, you must define at least one process with type = export. The following example contains lines that may be used in the EIM configuration file to define an export process from the S_PARTY table and its extension tables.

[Export Accounts]
  TYPE = EXPORT
  BATCH = 2
  TABLE = EIM_ACCOUNT
  EXPORT ALL ROWS = TRUE

Note: For performance reasons, you should limit the number of tables to export in a single process section to five or less.

Parameters Used for Exports in Both the Header and Process Sections

The following table describes the parameters that can appear in either the header section or a process section, and are specific to an export process. For generic parameters that can be used in all EIM processes, see Process Section Parameters Generic to All EIM Processes.

Parameter	Description
ATTACHMENT DIRECTORY	(Default is SIEBEL_HOME\OUTPUT) Specifies the directory to be used for exporting attachments. Before specifying a directory, make sure the directory exists on a Siebel Server machine and you have read and write access to it. Example: ATTACHMENT DIRECTORY = SIEBEL_HOME\OUTPUT (for Windows) ATTACHMENT DIRECTORY = “SIEBEL_HOME/OUTPUT” (for UNIX) If the export of an attachment fails, the export process continues and EIM writes a message in the trace file.
CLEAR INTERFACE TABLE	Specifies whether existing rows in the EIM table for the given batch number should be deleted. The default value is TRUE.
EXPORT ALL ROWS	Specifies that all rows in the target base table and secondary tables are to be exported. The default value is FALSE. Existing values in the EIM table and export matches expressions are ignored. For all columns to export using an EIM table (both data from the base table and data from related child tables), you need to make sure this parameter is set to TRUE (you may need to add this line if it does not currently exist) in the .IFB file. Note: Rows from child tables of related child tables are not exported until they have been mapped.
EXPORT MATCHES	WHERE clause fragment. Example: `EXPORT MATCHES = (NAME LIKE “GEN%”)` For more information on the EXPORT MATCHES parameter, see EXPORT MATCHES Parameter.

EXPORT MATCHES Parameter

The EXPORT MATCHES parameter specifies a where clause expression for filtering base table rows. The value is in two parts: the Siebel EIM table name and the filter expression that goes against the target base table. The expression is applied against the target base table for the EIM table.

The expression is a self-contained where clause expression (without the where) and should use only literal values or unqualified column names from the base table. There must also be a space separating the operator from the operand.

Note: Complex SQL WHERE clauses like subqueries are not supported.

EXPORT MATCHES can be used only against a target base table, or against a non-target base table that is an extension table of S_PARTY when the target table is S_PARTY. For more information, see the following.

The syntax to use with the EXPORT MATCHES parameter depends on whether the target base table is S_PARTY or not.

Note: The column names included in the criteria (that is, in “(...criteria...)” syntax) must be columns from the target base table or the table that is specified for the EXPORT MATCHES parameter.

Syntax for EXPORT MATCHES with S_PARTY as the Target Base Table

The syntax shown in this topic is for use with the EXPORT MATCHES parameter if the EIM table’s target table is S_PARTY. Allowed syntax includes the following:

EXPORT MATCHES = S_PARTY, (...criteria...)
EXPORT MATCHES = <non-target base table name of Siebel Extension type>,
(...criteria...)

Note: When using the EXPORT MATCHES parameter against a non-target base table, you must still include the target table in the export.

The following syntax is not allowed:

EXPORT MATCHES = <EIM table name>, (...criteria...)
EXPORT MATCHES = (...criteria...)

Syntax for EXPORT MATCHES with Target Base Tables Other Than S_PARTY

The syntax shown here is for use with the EXPORT MATCHES parameter if the EIM table’s target table is not S_PARTY. Allowed syntax includes the following:

EXPORT MATCHES = <EIM table name>, (...criteria...)
EXPORT MATCHES = <target base table name>, (...criteria...)
EXPORT MATCHES = (...criteria...)

The following syntax is not allowed:

EXPORT MATCHES = <non-target base table name>, (...criteria...)

To check whether a base table is of Siebel extension type

In Siebel Tools, navigate to the Table control and query a table name.

Check the Type property value. If the Type property value contains ‘Extension (Siebel),’ then the table is a Siebel extension type table.

Exporting All Data Rows

To export all rows from the tables that are mapped in an EIM table, set the export all rows parameter for the file to true in the specific export batch section of the EIM configuration file. The following example contains lines that may be used in the EIM configuration file to export all data rows from the accounts table.

[Export Accounts]
  TYPE = EXPORT
  BATCH = 2
  TABLE = EIM_ACCOUNT
  EXPORT ALL ROWS = TRUE

Prior to exporting, make sure that your database administrator has allocated enough space for the EIM table into which data will be exported.

Exporting Selected Data Rows

To export selected rows from base tables, set the export all rows parameter as follows:

EXPORT ALL ROWS = FALSE

Specify one or more export matches expressions to define the rows you want exported in this batch.

Exporting Recursive Relationships

Siebel applications support multilevel hierarchies for defining accounts, products, and product lines. For example, a product’s bill of materials may involve levels for components, assemblies, and sub-assemblies. Similarly, a parent account may have multiple child accounts for company divisions and wholly owned subsidiaries. These child accounts may be further organized into subaccounts such as regions and offices. Siebel applications support an unlimited number of levels within account, product, and product line structures.

Exporting LOV and MLOV Data

When exporting List of Values (LOV) data, whether from an LOV column or a multilingual LOV (MLOV) column, the EIM engine populates the EIM table column with the display value of a specific language. The difference between the two cases is the following:

When exporting from an LOV column, the EIM engine exports the display value stored in the column.
When exporting from an MLOV column, EIM translates MLOV values during the export process. You do not need to populate the EIM table columns prior to the export. The EIM engine looks up the language-specific display value for the Language Independent Code (LIC) stored in the MLOV column, and puts the display value in the EIM table column.

Note: If you are exporting from an MLOV, you must set the LIC parameter to the appropriate language first. EIM exports the display value for the language specified.

For more information on how EIM processes LOV and MLOV data, see Importing LOV and MLOV Data.

Running an Export Process

You may run an export process once you have:

Identified the data for export processing
Prepared the related EIM tables
Modified the EIM configuration file accordingly

Run the export process by completing the procedures in Running EIM.

If you are exporting data that pertains to organizations and divisions, it may be necessary to run additional SQL statements against the EIM table to complete the export of names from the S_BU base table (used for organizations).

To populate the BU columns from the S_BU base table

In the Admin directory within the Siebel Server root directory, open the file named eim_export_lookup_bu_name.sql.
Locate the appropriate SQL statement for the base table that you are exporting.
Modify this SQL statement if necessary and run it against the EIM table to populate the BU columns from the S_BU base table.

Checking Export Results

When an export process ends, you should carefully check the results to verify that data was successfully exported. During each export process, EIM writes comprehensive status and diagnostic information to several destinations.

Viewing a List of Exported Rows

You can verify export results by checking a list of exported rows, as described in the following procedure.

To view a list of exported rows

Query the appropriate EIM tables for rows whose if_row_batch_num equals the batch number for the export.
The value of if_row_stat should be exported.

If error flags, SQL trace flags, or trace flags were activated for the EIM process, you can also use the trace file to view the results of the EIM process. For more information on viewing the trace file, see Viewing the EIM Log File.

Extracting Data from the EIM Tables

Upon completion of an export process, the database administrator can use appropriate tools (such as native SQL) to extract data from the EIM tables for subsequent use by an external application. The following examples illustrate when to perform this process:

If you have exported employee information for transfer to a human resources application.
If you want to load customer information for a specific accounting application. Begin by exporting your customer information from the Siebel database.