Creating Siebel ROW_ID through EIM

The unique identifier associated with every record in Siebel Enterprise databases is known as a Row ID. The column in which this value is found is ROW_ID and it is present on every table. The ROW_ID is unique for an entity. For example, the ROW_IDs for the same person in S_PARTY, S_CONTACT, and S_CONTACT_X are the same because they each refer to the same person.

Row IDs are used extensively throughout Siebel Enterprises to access specific records. Although users access records by a User Primary Key (such as Opportunity Name), it is more efficient for the Siebel Enterprise to store and access related data via the Row ID.  

The Row ID is a base-36 sequence number generated using a confidential, proprietary algorithm that ensures no duplication, thus protecting the referential integrity of the database. The ROW_ID column is VARCHAR(15), which may contain one or two non-numeric symbols (plus signs or hyphens, or both).

The format of the ROW_ID is one of the following:

CP-NS --> Records created by the user interface

CP+NP+NS --> Records created by Interface Manager (EIM)

CP-NP-NS --> Records created by EIM

(Starting in Siebel versions 6.2 and higher, and Siebel version 7)

where:

CP = Corporate Prefix, up to 2 alphanumeric characters

NP = Next Prefix, up to 6 alphanumeric characters

NS = Next Suffix, up to 7 alphanumeric characters

The maximum length of the ROW_ID is 15 alphanumeric characters. The corporate prefix will always be unique for any database (main or local). The server maintains its original value, and mobile databases created against that particular server database are always assigned a new, unique value.

The Siebel ROW_ID is a combination of the S_SEQUENCE_S and information from S_SSA_ID table. All connected users share the same prefix, which is obtained from the table S_SSA_ID on the server database. Remote Users are assigned a unique prefix when they are db-extracted. This value is also stored in the S_SSA_ID table on the local database.

Suffix is generated using an internal algorithm handled by the source code. When a new record is created through the user interface, the Siebel application reads the value of the current NS column from S_SSA_ID table and increments this value by a value more than 1 – for performance reasons, generally 50. The client caches these fifty potential ROW_IDs for future inserts. A new record entered from the user interface may result in many inserts to the underlying tables, depending on the business components used. When the client disconnects, cached ROW_IDs are lost.

The combination of the prefix and suffix generates a unique row_id. Since Siebel does not expose the algorithm behind generation of row ids, the ROW_ID generation is internal to the EIM process. Therefore Siebel provides user keys to map to a unique record in the base table.

During EIM, the EIM table columns, which map to the user keys of the base table, are populated with values so as to map to a unique record. If that record is to be updated by EIM later, the same user key values have to be populated in the corresponding EIM table columns. Otherwise, it will not resolve into the correct base table record.

For example, PERSON_ID and BU_ID constitute the user keys for S_CONTACT. The corresponding EIM_CONTACT columns are CON_PERSON_UID and CON_BU. When a new record is imported in S_CONTACT using EIM_CONTACT, the CON_PERSON_UID and CON_BU should uniquely identify a Contact record. Now, for updating some fields for this Contact using EIM_CONTACT, CON_PERSON_UID and CON_BU have to be populated with the same set of values that were used during the initial load in order to map to the same Contact record in the base table.

Similarly while loading a child table, which references the row id of the master table, the EIM table columns that map to the foreign-key column of the child table must be populated in the same way the user keys of the master table were populated.

For example, S_CONTACT_XM is a child table of S_CONTACT. The column PAR_ROW_ID of S_CONTACT_XM references the ROW_ID of S_CONTACT. S_CONTACT_XM is loaded by EIM_CON_DTL. The columns of EIM_CON_DTL that need to be populated to generate the PAR_ROW_ID of S_CONTACT_XM are CON_BU, CON_PERSON_UID and CON_PRIV_FLG. These EIM columns should be populated with the same set of values that were used to populate its parent S_CONTACT record through EIM_CONTACT. Note that though PRIV_FLG is not a part of the user key of S_CONTACT, but it is a required column and is part of the foreign-key mapping of its child table.

Database Tuning Tips for EIM (For Oracle Only)

1. Table Fragmentation:

Prior to running EIM it is important to clean up fragmented objects, especially those that will be used during the EIM processing. The following SQL statement can be used to identify objects with greater than 10 extents:

SELECT segment_name,segment_type,tablespace_name,extents
FROM dba_segments
WHERE owner = (Siebel table_owner)
and extents > 9;

To fix fragmentation, the objects will need to be rebuilt with appropriate storage parameters. Always be careful when rebuilding objects because of defaults, triggers, etc. on the objects.

2. Optimizer Mode:

Oracle optimization mode can also affect EIM performance. The Siebel application has been found to perform better under RULE based optimization under normal application usage. While there have been cases where it has been found that Cost based optimization has improved EIM performance this should only be attempted as a last resort and must be switched back to RULE for online usage. Be aware that Siebel only supports Rule-Based optimization. Optimization mode can be verified by running the following query:

SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME = ‘OPTIMIZER_MODE’;

3. Purging an EIM table:

When purging data from the EIM table, use the TRUNCATE command as opposed to DELETE. The TRUNCATE command will release the data blocks and reset the high water mark while DELETE will not, thus causing additional blocks to be read during processing. Also, be sure to drop and re-create the indexes on the EIM table to release the empty blocks.

4. Index creation:

When working with large volumes of data in the EIM tables, index build time can be costly when refreshing the EIM table with a new data set. To improve the performance of the index build use the UNRECOVERABLE (Oracle 7.3) or NOLOGGING (Oracle 8) option. This will prevent Oracle from writing to the REDO LOG files. Users can also improve index build time by creating multiple SQL scripts to create the indexes and running these scripts in parallel through SQLPlus. Below is a sample SQL statement demonstrating the syntax for using the UNRECOVERABLE or NOLOGGING option:

CREATE INDEX S_SAMPLE_M1 ON
S_SAMPLE (SAMPLE_ID)
TABLESPACE TS_INDX
STORAGE (INITIAL 10M NEXT 5M PCTINCREASE 0)
UNRECOVERABLE/NOLOGGING; (choose one based on the Oracle version)

5. Disable archive logging:

It is recommended that Archive logging be disabled during the initial data loads. This can be enabled after the data loads are complete to provide for “point-in-time” recovery.

6. FREELIST parameter:

Multiple EIM processes can be executed against an EIM table provided they all use different batches or batch ranges. The concern is that contention for locks on common objects may be experienced. To run multiple jobs in parallel against the same EIM table, check that the ‘FREELIST’ parameter is set appropriately for the tables and indexes used in the EIM processing. This would include the EIM tables and indexes as well as the base tables and indexes. The value of this parameter specifies the number of block ids that will be stored in memory which are available for record insertion. As a rule of thumb, users should set this to at least half of the intended number of parallel jobs to be run against the same EIM table (example, a FREELIST setting of 10 should permit up to 20 parallel jobs against the same EIM table). This parameter is set at the time of object creation and the default for this parameter is 1. To check the value of this parameter for a particular object the following query can be used:

SELECT SEGMENT_NAME, SEGMENT_TYPE, FREELISTS
FROM DBA_SEGMENTS
WHERE SEGMENT_NAME=’<OBJECT NAME TO BE CHECKED>’;

To change this parameter the object must be rebuilt. Again, be careful when rebuilding objects because of defaults, triggers, etc. on the objects. To rebuild the object follow the steps below:

1. Export the data from the table with the grants.
2. Drop the table.
3. Re-create the table with the desired FREELIST parameter.
4. Import the data back into the table
5. Rebuild the indexes with the desired FREELIST parameter.

7. Caching tables:

One other measure that can help performance is to pin small tables that are frequently accessed in cache. The value of BUFFER_POOL_KEEP determines the 'keep pool', example, the portion of the buffer cache that will not be flushed by the LRU algorithm. The 'keep pool' allows one to 'pin' certain tables in memory, thus improving performance for accessing those tables. This will ensure that after the first time that the table is accessed it will always be in memory. Otherwise it is possible that the table will get pushed out of memory and will require disk access the next time used. Keep in mind that the amount of memory allocated to the ‘keep’ area is subtracted from the overall buffer cache memory (defined by DB_BLOCK_BUFFERS). A good candidate for this would be the S_LST_OF_VAL table. The syntax for pinning a table in cache is as follows:

ALTER TABLE S_LST_OF_VAL CACHE;

Using SQLPROFILE Parameter in IFB file

The inclusion of this parameter will greatly simplify the task of identifying the most expensive SQL statements. The insert of the following statement in the header section of the “IFB” file will place the most expensive SQL statements into the file:

SQLPROFILE = c:\temp\eimsql.sql

Below is an example of the file “eimsql.sql”:

<Start of the file – list of most expensive queries>

<…list of queries continues>

<Statistics by step and by pass>

<…list of statistics continues>

<SQL statements>

<…list of SQL statements continues>