Bookshelf Home | Contents | Index | PDF     

Siebel Data Quality Administration Guide > Installing Data Quality Products > Process of Installing the Oracle Data Quality Matching Server >

Creating Database Users and Tables for Oracle Data Quality Matching Server

Creating database users and tables involves executing a number of scripts on the Oracle Data Quality Matching Server database. The scripts that you must execute are located in the Oracle Data Quality Applications media pack on Oracle Software Delivery Cloud. Once the media pack is downloaded and installed, the scripts are located in the InformaticaIR/dbscript/ora folder. For example (idsuseru.sql):

C:/InformaticaIR/dbscript/ora/idsuseru.sql

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 Oracle Data Quality 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 Oracle Data Quality Matching Server on Microsoft Windows or on UNIX.
  • When setting up the database for Oracle Data Quality Matching Server on UNIX, you must set TNSNAmes.ora with an entry to the target database (Oracle Data Quality 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 Software Delivery Cloud. This task is a step in Process of Installing the Oracle Data Quality Matching Server.

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

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

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

  2. Log in to the database as the new database user (created in Step 1 with appropriate privileges to create and update Oracle Data Quality Matching Server tables), then execute the following SQL scripts to create other Oracle Data Quality Matching Server 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 Oracle Data Quality Matching Server 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 idstbora.sql to create control tables for the Oracle Data Quality Matching Server.
    2. Execute updsyncu.sql to create database objects required by the Oracle Data Quality Matching Server 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 updsynci.sql to create database objects required by the Oracle Data Quality 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 updsyncg.sql to create database objects required by the Oracle Data Quality Matching Server to synchronize data in SSA-ID tables with updates to user source tables.

      This script will create public synonyms for the Oracle Data Quality 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 4 describes some example odbc.ini configurations.

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

Universal Database (UDB)

UDB must be installed prior to the installation of Oracle Data Quality Matching Server.

IBM provides ODBC drivers for both Windows and UNIX operating systems, named db2cli and db2 respectively.

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

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

Microsoft SQL Server

Microsoft provides a Windows ODBC driver named sqlsrv32. It is configured by adding a new Data Source Name (DSN) using 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

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

Testing Connectivity

Use the dblist utility to test your ODBC configuration by connecting to a database whose connection string is provided with the -d parameter. An example of the output associated with a successful connection follows:

$SSABIN/dblist -c -dodb:99:ssa09/SSA09@ora920
Maximum connections per module: 1024
Linked databases: odb: sdb:
Driver Manager: 'Identity Systems ODBC Driver Manager 1.2.2.3'
ODBC Driver: 'ssaoci9 SSADB8 2.7.0.00MSVC60 Jun 8 2006 17:26:56'
DBMS Name: 'Oracle DBMS (9.2.0.6.0)'
Native DB type: 'ora'

    
Siebel Data Quality Administration Guide Copyright © 2013, Oracle and/or its affiliates. All rights reserved. Legal Notices.