Wednesday, January 28, 2015

Faster imports of Siebel Repositories

So, it is just another day in your SADMIN life. The development team just finished cooking their configurations and now they want to test their doing in another Siebel Enterprise, which implicates that you need to import a new Siebel Repository.

Well, all you need is to launch Database Server Configuration Utility, go over all those dialog boxes filling the fields as necessary and choosing the correct options to import a Siebel Repository.

Lame try... if you ever did that on MS Windows, didn't you ever pay attention to the command line application that is invoked when you finish entering the details? That guy over there execute "repimexp", which is a command line program that you can actually use in your own.

Well, by using this program you can select different options that the Database Server Configuration Utility will not expose to you. And two of those options will allow you to dramatically improve the speed of importing a new Siebel repository.

Don't bother looking around a documentation that gives you all those details... there are only at command line help message. Let's review it:

bash-3.2$ repimexp
Siebel Enterprise Applications Repository Import/Export Utility, Version [21238] LANG_INDEPENDENT
Copyright (c) 1990-2008, Oracle. All rights reserved.

The Programs (which include both the software and documentation) contain
proprietary information; they are provided under a license agreement containing
restrictions on use and disclosure and are also protected by copyright, patent,
and other intellectual and industrial property laws. Reverse engineering,
disassembly, or decompilation of the Programs, except to the extent required to
obtain interoperability with other independently created software or as specified
by law, is prohibited.

Oracle, JD Edwards, PeopleSoft, and Siebel are registered trademarks of
Oracle Corporation and/or its affiliates. Other names may be trademarks
of their respective owners.

If you have received this software in error, please notify Oracle Corporation
immediately at 1.800.ORACLE1.


Error: Value missing for argument 'Rows Per Commit'.
-------------- required parameters -----------------

/C <ODBC data source>   ODBC data source.
                          Default: SIEBEL_DATA_SOURCE environment variable.
/U <userName>           User name
/P <password>           Password
/D <table owner>        Siebel database table owner.
                          Default: SIEBEL_TABLE_OWNER environment variable.
/R <repository>         Repository name, default: Siebel Repository
/F <dataFile>           Import/Export/Dump repository data file's name

------------- choose which action you want to take by /A -------------
------------- and select the sub-parameters accordingly --------------

/A <D|I|X|E>            Dump/Import/Import_INTL/Export actions

/A D                    Action: Dump basic data info from the datafile

/A I                    Action: Import base tables and (optional) INTL tables
  /G <languages>       (Optional) Specify what languages to import, e.g. ENU,FRA,ITA
                                  ALL for all languages. Default: no language import
  /X <Codepage#>       (Optional) Verify (not really import) the data file against
                                  a codepage character set, e.g. CP1252,CP874 ...
  /H <number>          (Optional) Number of rows per commit
  /8 <moduleFile>      (Optional) Module list filename
  /K <Y|N>             (Optional) Preserve DB system column values (Default: N)
  /J <string>          (Optional) DB system column source (DB_LAST_UPD_SRC) (Default: repimexp)
  /Z <number>          (Optional) Array Insert Size (Default: 5)

  /A X                    Action: Import INTL tables only
  /G <languages>       Specify what languages to import, e.g. ENU,FRA,ITA
                          ALL for all languages
  /O <Y|N>             (Optional) Abort INTL import if unable to resolve parent row
                                  in server repository, i.e. orphans
                                  Default: N
  /I <Y|N>             (Optional) Abort INTL table import if insert fails
                                  Default: Y
  /A E                    Action: Export
  /1 <exp rep userName>       (Optional) Default: to same as User name
  /2 <exp rep password>       (Optional) Default: to same as Password
  /3 <exp rep ODBC dt src>    (Optional) Default: to same as ODBC data source
  /4 <exp rep table owner>    (Optional) Default: to same as Siebel database table owner
  /5 <exp rep repository>     (Optional) Default: to same as Repository name
  /E <Y|N>                    (Optional) Export prototype data. Default:N
  /S <Signature>              (Optional) Repository signature
  /N <0|1|2>                  (Optional)
                                  0: no change.
                                  1: change CREATED_BY, UPDATED_BY, OWNER_BRANCH
                                  2: change CREATED_BY, UPDATED_BY, dates columns, OWNER_BRANCH
                                  Default: 1

------------ other optional parameters ---------------

/L <logFile>            (Optional) Log output messages to this file as well.
/W <lang code>          (Optional) The language env where this program is running.
                                  Default: SIEBEL_LANGUAGE, if not set ENU
/B <appServer root>     (Optional) Siebel server installation directory to
                                  override SIEBEL_HOME environment variable.
/T <Y|N>                (Optional) Test/Debug use only, do not import into database
/V <Y|N>                (Optional) Verify data.  For import, default: Y
                                  For export, default: N
/M <Y|N>                (Optional) Commit changes even if verification failed.
                                  Default: N

I highlighted the options that will help you get better performance.

The /H is the one that you will use in most cases, and probably the one that gives you the best improvement of speed. It's is basically allowing you to set the number of rows imported before doing a single commit. If you don't set this option, the program will commit every line inserted, which is a very secure operation... but also a lot slower.

It is all about trading off data consistence with I/O speed. The point is, the worst thing that might happen with a repository import that went bad is that you will need to erase it and repeat the operation. Not a big deal in most of cases.

That said, talk to your DBA about how much lines he considers reasonable inserting before a single commit to avoid using all your UNDO (or whatever other term is used by the DBMS Siebel is installed over) space and set the /H option with it.

The /G options allows you to select which languages you want to import. If you have a multilingual repository but do not need all those languages, just select the one you need it. That will reduce the number of rows to be imported. This parameter is not as good as /H but it will help anyway.

Another tip to improve Siebel Repository maintenance speed is to keep only the most recent ones imported (see Doc Id 761894.1). By my experience, keeping the current and the previous one should be enough. You don't need to try doing "big data" with Siebel Repositories. If you are worried about backups, just use the .dat files for that and erase all of those older repositories. Your DBA will thank you later for that.


  1. Thanks for posting such an informative article. Can you please let us know some default value for /H argument for different types of DB like Oracle and MS SQL ?

    1. I'm afraid I don't. You definitely should talk with the responsible DBA for that.