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... as you probably 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 on 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... they are only at command line help message. Let's review it:

bash-3.2$ repimexp

Siebel Enterprise Applications Repository Import/Export Utility, Version 8.1.1.7 [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.

repimexp

Error: Value missing for argument 'Rows Per Commit'.

Usage:

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

Installing openSSL inside your Perlbrew

A while ago I had to install some monitoring scripts in reasonable old Linux servers. The script would require a substantial number of Perl modules from CPAN and I was not interested in running the issue to convince the administration responsible team (I didn't have root access) to install those modules for me.

The easiest way to solve that was installing Perlbrew. Perlbrew allows anyone to download perl source code, compile and install anywhere, including your own home directory (Perlbrew is available only for UNIX-like operational systems at the moment I'm writing this post).

Since I didn't have direct access to the internet on those servers, what I did was create a VM with the same Linux distribution and version and install Perlbrew from there, connecting to CPAN and installing all dependencies that I need too.

After that I just had to generate a tarball from everything and move to those Linux servers. Another wonder from Perl community made my day.

After some days of having those test scripts working, I had receive a new testing request: validate if some webpages were working fine. Perl has a lot of tools to help with that, so no issue here. The problem is that those webpages where available only through TLS and the version available of openSSL was so old that newer versions of IO::Socket::SSL would simple not compile with it.

Old libs and no play makes Jack a dull boy

IO::Socket::SSL depends on Net::SSLeay, that will then look for shared libraries of openSSL to compile C/XS code.If there are changes in the expected interface, this code will not compile at all.

Some how I would need to avoid using the present openSSL binaries and download and install a newer one inside my Perlbrew setup. I spent an unpleasant time trying to do that (I'm not a C programmer) because first I thought that I should use static libraries, than I tried to force shared libraries... and during all this time I was being stomped by SELinux (yes, it was installed in all those servers).

It turned out at the end that a much more simple was available: just compile openSSL with standard options. And some changes regarding system variables. Let's review my recipe on that:

1. install Perlbrew: you probably already know how to do that. If not, just visit it's website for instructions.
2. Install openSSL: no magic here, the simplest
3. Installing Perl modules that depends on openSSL

Step 2 is pretty easy. I suggest to set up the option --prefix as a sub directory under the Perlbrew install. For instance, that might be something like:

./config shared --prefix=$PERLBREW_ROOT/openssl
make
make test
make install

For Step3, some previous setup is necessary.

Create a test file named "openssl_env" in your $HOME with the following content:

export OPENSSL=$PERLBREW_ROOT/openssl
export C_INCLUDE_PATH=$OPENSSL/include
export LIBRARY_PATH=$OPENSSL/lib
export LD_LIBRARY_PATH=$LIBRARY_PATH:$LD_LIBRARY_PATH
export OPENSSL_PREFIX=$PERLBREW_ROOT/openssl

Now go back to the shell and type:

perl -MCPAN -e shell
get Net::SSLeay
look Net::SSLeay

perl Makefile.PL
make
make test
make install
exit

Make sure all tests from "make test" worked fine. If not, check if the declared variables paths are correct.

Now edit again the openssl_env file and comment the C_INCLUDE_PATH variable. Should be unnecessary to have it setup unless you need to compile more stuff.

Now add the following to the end of your .bashrc (or whatever other shell configuration file you're using):

source $HOME/openssl_env


Now, go back to the CPAN shell to install IO::Socket::SSL:

perl -MCPAN -e shell install IO::Socket::SSL

If you did everything right, now you should have a plenty functional customized Perl with openSSL working!