Oracle^® Business Intelligence Applications Installation and Configuration Guide > Installing and Configuring Oracle BI Applications on Windows >

Creating Stored Procedures for DB2-UDB

The DAC uses siebstat and siebtrun stored procedures when running ETL processes. Typically, these stored procedures are available in your transactional database; they might not be available on the data warehouse database.

If you need to install the DB2 stored procedures manually, they are located in the installation directory \OracleBI\dwrep\siebproc\db2udb\. There is a sub-directory for each platform. For example, stored procedures for the Windows platform are stored in the sub-directory \OracleBI\dwrep\siebproc\db2udb\win32\.

Each platform-specific directory contains the following sub-directories:

• \siebproc\ (containing stored procedures for a 32-bit DB2 environment)
• \siebproc64\ (containing stored procedures for a 64-bit DB2 environment)

These directories also contain the files siebproc.sql and sqlproc.ksh, which are used to create the function. For more information, see To create DB2 stored procedures).

To verify the existence of these procedures, follow the steps below.

• From a DB2 command prompt or control center, issue the following SQL scripts:

  db2 => create table test_siebproc(id int);
  DB20000I The SQL command completed successfully.

  db2 => insert into test_siebproc(id) values (1);
  DB20000I The SQL command completed successfully.

  db2 => call siebtrun('TEST_SIEBPROC');
  SQL0805N Package "NULLID.SYSSH200" was not found. SQLSTATE=51002

  NOTE:  If you get an error message, you do not have the required stored procedures.

Creating stored procedures must be performed by the database administrator (for more information, see To create DB2 stored procedures).

To create DB2 stored procedures

1. Copy the DB2 stored procedure directory (i.e. \siebproc\ or \siebproc64\) from the appropriate platform-specific directory to a directory on the DB2 server side.

   For example, for a 32-bit DB2 environment on a Windows platform, you might copy the directory \OracleBI\dwrep\siebproc\db2udb\win32\siebproc\ to the directory d:\Program Files\SQLLIB\function\ on the DB2 server side.

   NOTE:  For more information about the location of DB2 stored procedures, see Creating Stored Procedures for DB2-UDB).

2. If you copied stored procedures for a 64-bit DB2 environment, on the DB2 server side, rename the \siebproc64\ directory to \siebproc\.

   For example, if you copied stored procedures to d:\Program Files\SQLLIB\function\siebproc64\, rename this directory to d:\Program Files\SQLLIB\function\siebproc\.

Once these procedures are created, you can verify that they exist. After the test is complete, you can drop the table TEST_SIEBPROC.