Bookshelf Home | Contents | Index | PDF     

Siebel Data Quality Administration Guide > Setting Up Oracle Data Quality Matching Server for Data Matching > Process of Setting Up ODQ Matching Server for Data Matching  >

Creating Database Users and Tables for ODQ Matching Server

Creating database users and tables involves executing a number of scripts on the ODQ Matching Server database. The scripts that you must execute are located in the Oracle Data Quality Applications media pack on Oracle E-Delivery. Once the media pack is downloaded and installed, the scripts are located in the NM3_2807_XXX folder where XXX is the name of the operating system.   

You must open these scripts and modify them as required, depending on the database that you are using. For example, complete the steps in the following procedure to create database users and database tables for ODQ Matching Server if using an Oracle database. Note the following:

  • The procedure is similar if using Microsoft SQL Server, UDB, or DB2 on OS/390. However, you must modify the SQL scripts according to the database that you are using.
  • The procedure is also similar whether creating database users and database tables for ODQ Matching Server on Microsoft Windows or on UNIX.
  • When setting up the database for ODQ Matching Server on UNIX, you must set TNSNAmes.ora with an entry to the target database (ODQ Matching Server database), and perform connectivity testing using SQLPLUS if required.

    For more information about testing the connectivity on UNIX, see the relevant documentation included in Siebel Business Applications Third-Party Bookshelf in the product media pack on Oracle E-Delivery.

To create database users and tables for ODQ Matching Server if using an Oracle database

  1. Log in to the database as database administrator, then execute the 1_x.sql script to create a new database user with appropriate privileges to create and update ODQ Matching Server tables.

    NOTE:  You must be logged in as database administrator to execute 1_x.sql.

  2. Log in to the database as the new database user (created in Step 1 with appropriate privileges to create and update IIR tables), then execute the following SQL scripts to create other IIR database tables, such as IDT and IDX tables. You can execute the following SQL scripts in any order:

    NOTE:  IDT tables store the copy of source records in the IIR database. IDX tables store the index keys for IDT tables. Each IDT table can have one or more IDX tables associated with it.

    1. Execute 2_idstbora.sql to create control tables for ODQ Matching Server.
    2. Execute 3_updsyncu.sql to create database objects required by IIR to synchronize data in ID tables with updates to user source tables.

      Run this script on all databases containing user source tables that require synchronization, and also before loading any ID tables that require synchronization.

    3. Execute 4_updsynci.sql to create database objects required by ODQ Matching Server to synchronize data in ID tables with updates to user source tables.

      Run this script on the database which will contain IDTs, and also before loading any ID tables that require synchronization.

    4. Execute 5_updsyncg.sql to create database objects required by ODQ Matching Server to synchronize data in SSA-ID tables with updates to user source tables.

      This script will create public synonyms for ODQ Matching Server objects created on user source table databases. This script must be run by someone (for example, the database administrator) who has the privilege to CREATE PUBLIC SYNONYM. Run this script after running updsyncu.sql. Use the same userid to run _updsynci.sql as you did to run _updsyncu.sql.

      NOTE:  You must be logged in as database administrator to execute _updsyncg.sql.

Table 30 shows some example odbc.ini configurations.

Table 30. Example odbc.ini Configuration Blocks
Database
Description
Example odbc.ini Configurations

Oracle Database 10g

The Oracle database driver works out-of-the box and is named %SSABIN%\ssaoci{8|9}.dll on Windows, and $SSABIN/libssaoci{8|9}.s{o|l} on UNIX.

There are no special setup requirements, other than adding configuration blocks to your odbc.ini file.

The ODBC_Driver name can be either ssaoci8 or ssaoci9. The former must be used with Oracle 8 client libraries and does not support Unicode data. The latter can be used with Oracle 9 (or later) client libraries and supports Unicode access.

When using the ssaoci9 driver with Oracle Database 10g client software, the connectivity test might fail on some UNIX operating systems. This occurs because the driver has been linked with libclntsh.so.9.0, which is not distributed with Oracle Database 10g. Oracle normally provides backward compatibility by adding symbolic links to redirect requests for older versions of the library to the current version. Unfortunately, by default, this practice is restricted to minor versions only (for example, 9.0-9.2). To overcome the problem, locate the appropriate Oracle lib directory (lib, lib32, or lib64) and add a symbolic link. For example:

cd $ORACLE_HOME/lib32
ln -s ./libclntsh.so libclntsh.so.9.0

[ora10g]
ssadriver = ssaoci9
ssaunixdriver = ssaoci9
server = ora10g.mynet8tns.name

Microsoft SQL Server

Microsoft provides a Windows ODBC driver named sqlsrv32. It is configured by adding a new Data Source Name (DSN) by navigating to Control Panel, Administrative Tools, Data Sources (ODBC).

For more information about the sqlsrv32 driver, see the appropriate Microsoft manuals for specific details.

The ODBC_Driver name is sqlsrv32 and the Native_DB_Service is the server name (-S parameter of the osql and bcp utilities).

The SQL Server Native Client (sqlncli.dll) can be used as an alternative driver.

[production]
DataSourceName = msq2003
ssadriver = sqlsrv32
server = mySQLServer

Universal Database (UDB)

For more information about the db2cli and db2 drivers, see the appropriate UDB manuals for full details.

UDB must be installed prior to the installation of the ODQ Matching Server.

[test-udb]
DataSourceName = udb8
ssadriver = db2cli
ssaunixdriver = db2
server = UDB_database_alias

Sybase

For more information about the sybdrvodb drivers, see the appropriate Sybase manuals for installation specifics.

[production]
DataSourceName = ase150
ssadriver = sybdrvodb
ssaunixdriver = sybdrvodb
server = mySybaseServer
    
Siebel Data Quality Administration Guide Copyright © 2011, Oracle and/or its affiliates. All rights reserved. Legal Notices.