Skip Headers
Oracle® Fusion Middleware Installation Guide for Oracle Data Integrator
11g Release 1 (11.1.1.7.0)

Part Number E16453-06
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

E Creating Repositories with Oracle Data Integrator Studio

This appendix describes how to create and administer Oracle Data Integrator repositories. An overview of the repositories used in Oracle Data Integrator is provided.

This appendix includes the following sections:

E.1 Introduction to Oracle Data Integrator Repositories

There are two types of repositories in Oracle Data Integrator:

The standard method for creating repositories is using Repository Creation Utility (RCU). RCU automatically manages storage space as well as repository creation. However, if you want to create the repositories manually, it is possible to manually create and configure the repositories.

The steps needed to create and configure repositories are detailed in the following sections:

Note:

Oracle recommends that you regularly perform the following maintenance operations: purge the execution logs in order to reduce the Work Repository size, and back up the Oracle Data Integrator repositories on the database.

Advanced actions for administering repositories are detailed in "Advanced Actions for Administering Repositories" in the Oracle Fusion Middleware Developer's Guide for Oracle Data Integrator.

E.2 Creating Repository Storage Spaces

Oracle Data Integrator repositories can be installed on database engines supported by Oracle Fusion Middleware 11g. For the latest list of supported databases versions as well as the requirements for each database, see:

http://www.oracle.com/technology/software/products/ias/files/fusion_certification.html

For each database that will contain a repository, a storage space must be created.

Caution:

Oracle recommends that you store repositories in a different space from where your application data is kept (for example in a different schema for an Oracle database, or in a different database for Sybase or Microsoft SQL Server).

Your Master Repository can be stored in the same schema as one of your Work Repositories. A schema can only have one Master Repository and you cannot create two different Work Repositories in the same schema.

The examples in the following table are supplied as a guide:

Technology Steps to follow

Oracle

Create a schema to host the Master Repository and a schema to host the Work Repository.

The schemas are created by executing the following SQL commands for each of the odim and odiw schemas:

Master Repository:

SQL> create user MY_SCHEMA identified by MY_PASS
       default tablespace MY_TBS 
       temporary tablespace MY_TEMP; 
SQL> grant connect, resource to MY_SCHEMA;

Work Repository:

SQL> create user MY_SCHEMA identified by MY_PASS
       default tablespace MY_TBS 
       temporary tablespace MY_TEMP; 
SQL> grant connect, resource to MY_SCHEMA;
SQL> grant execute on dbms_lock to MY_SCHEMA;

Where:

MY_SCHEMA corresponds to the name of the schema you want to create (for example, odim for the Master Repository and odiw for the Work Repository).

MY_PASS corresponds to the password you have given to the default tablespace MY_TBS. This is the Oracle tablespace where the data will be stored.

MY_TEMP is the temporary default tablespace.

NOTE: The Work Repository database user requires the grant execute privilege on dbms_lock for the Work Repository schema.

If you are creating the repository on Oracle Database 12c, you must assign a quota to allow the user to create objects. Choose the quota to accommodate the expected size of your repository. If you are not sure about the size of your repository, you can grant unlimited tablespace. For example:

GRANT UNLIMITED TABLESPACE TO odi_DB_repos_user

Microsoft SQL Server or Sybase ASE

Create a database db_odim to host the Master Repository and a database db_odiw to host the Work Repository. Create two logins, odim and odiw, that contain these databases by default.

Use Enterprise Manager to create the two databases db_odim and db_odiw.

NOTE: It is recommended that you configure the Microsoft SQL Server databases storing repository information with a case-sensitive collation. This enables reverse-engineering and allows you to create multiples objects with the same name but a different case (for example: tablename and TableName).

Use Query Analyzer or I-SQL to launch the following commands:

CREATE LOGIN mylogin
     WITH PASSWORD = 'mypass',
     DEFAULT_DATABASE = defaultbase,
     DEFAULT_LANGUAGE = us_english;
USE defaultbase;
CREATE USER dbo FOR LOGIN mylogin;
GO

Where:

mylogin corresponds to odim or odiw.

mypass corresponds to a password for these logins.

defaultbase corresponds to db_odim and db_odiw respectively.

DB2/400

Create a library odim to host the Master Repository and a library odiw to host the Work Repository. Create two users odim and odiw who have these libraries by default.

Master and work repository users should be granted access to tablespaces with at least 16k pagesize. The database must have temporary tablespace of at least 16k.

Use the following commands to se the pagesize:

CREATE  LARGE  TABLESPACE ODI16 PAGESIZE 16 K  MANAGED BY AUTOMATIC STORAGE ;
 
GRANT USE OF TABLESPACE ODI16 TO USER ODIREPOS; 

Note: The libraries must be created in the form of SQL collections.

MySQL

If you are using MySQL (release 5.5.14 or higher) to setup a repository, and the database charset is set to utf8, you will need to add the following parameters to the repository's jdbc URL to operate non-ASCII data correctly in Oracle Data Integrator Studio:

useUnicode=yes&characterEncoding=UTF-8 

E.3 Creating the Master Repository

Creating the Master Repository creates an empty repository structure and seeds metadata (for example, technology definitions, or built-in security profiles) into this repository structure.

To create the Master Repository:

  1. Open the New Gallery by choosing File > New.

  2. In the New Gallery, in the Categories tree, select ODI.

  3. Select from the Items list the Master Repository Creation Wizard.

  4. Click OK.

    The Master Repository Creation wizard opens.

  5. Specify the Database Connection parameters as follows:

    • Technology: From the list, select the technology that will host your Master Repository. Default is Oracle.

    • JDBC Driver: The driver used to access the technology, that will host the repository.

    • JDBC URL: The URL used to establish the JDBC connection to the database.

      Note that the parameters JDBC Driver and URL are synchronized and the default values are technology dependant.

    • User: The user ID / login of the owner of the tables (for example, odim).

    • Password: This user's password.

    • DBA User: Enter the Database Administrator user ID.

    • DBA Password: Enter the DBA's password.

  6. Specify the Repository Configuration parameters as follows:

  7. Click Test to test the connection to your Master Repository.

    The Information dialog opens and informs you whether the connection has been established. If the connection fails, fix the connection to your Master Repository before moving to next step.

  8. Click Next.

  9. Do one of the following:

    • Select Use ODI Authentication to manage users using ODI's internal security system and enter the following Supervisor login information:

      Properties Description

      Supervisor User

      User name of the ODI supervisor.

      This name should be SUPERVISOR (all CAPS - in Oracle Data Integrator user names are case-sensitive).

      Supervisor Password

      This user's password

      Confirm Password

      This user's password


    • Select Use External Authentication to use an external enterprise identity store, such as Oracle Internet Directory, to manage user authentication and enter the following supervisor login information:

      Properties Description

      Supervisor User

      User name of the ODI supervisor.

      This name should be SUPERVISOR (all CAPS - in Oracle Data Integrator user names are case-sensitive).

      Supervisor Password

      This user's password


      Note:

      In order to use the external authentication option, ODI Studio has to be configured for external authentication. See "Setting Up External Authentication" in Oracle Fusion Middleware Developer's Guide for Oracle Data Integrator for more information and restart ODI Studio.

  10. Click Next.

  11. Specify the password storage details:

    • Select Internal Password Storage if you want to store passwords in the Oracle Data Integrator Master Repository

    • Select External Password Storage if you want to use JPS Credential Store Framework (CSF) to store the data server and context passwords in a remote credential store. Indicate the MBean Server Parameters to access the credential store. Refer to "Managing the Security in Oracle Data Integrator" in Oracle Fusion Middleware Developer's Guide for Oracle Data Integrator for more information.

  12. In the Master Repository Creation Wizard click Finish to validate your entries.

Oracle Data Integrator begins creating your Master Repository. You can follow the procedure on your Messages – Log. To test your Master Repository, refer to Section E.4, "Connecting to the Master Repository".

E.4 Connecting to the Master Repository

To connect to the Master repository:

  1. Open the New Gallery by choosing File > New.

  2. In the New Gallery, in the Categories tree, select ODI.

  3. Select from the Items list Create a New ODI Repository Login.

  4. Click OK.

    The Repository Connection Information dialog appears.

  5. Specify the Oracle Data Integrator connection details as follows:

    • Login name: A connection alias (for example: Repository)

      The connection alias is used to connect to the Master Repositories and then to create the Work Repository as described in Section E.5.

    • User: The ODI Supervisor user name you have defined when creating the Master Repository or an ODI user name you have defined in the Security Navigator after having created the Master Repository.

      Note that the Supervisor user name is case-sensitive, and must be entered exactly as previously defined (for example, SUPERVISOR in all CAPS).

    • Password: The ODI supervisor password you have defined when creating the Master Repository or an ODI user password you have defined in the Security Navigator after having created the Master Repository.

  6. Specify the Database Connection (Master Repository) details as follows:

    • User: Database user ID/login of the schema (database, library) that contains the ODI Master Repository.

    • Password: This user's password.

    • Driver List: Select the driver required to connect to the DBMS supporting the Master Repository you have just created from the drop-down list.

    • Driver Name: The complete driver name.

    • URL: The URL used to establish the JDBC connection to the database hosting the repository.

  7. Click Test to check that the connection is working.

  8. Click OK to validate your entries.

E.5 Creating a Work Repository

Several Work Repositories can be designated with several Master Repositories if necessary. However, a Work Repository can be linked with only one Master Repository for version management purposes.

To launch a Work Repository creation:

  1. In the Topology Navigator, go to the Repositories panel.

  2. Right-click the Work Repositories node and select New Work Repository.

    The Create Work Repository Wizard opens.

  3. Specify the Oracle Data Integrator Work Repository connection details as follows:

    • Technology: Choose the technology of the server to host your Work Repository. Default is Oracle.

    • JDBC Driver: The driver used to access the technology, that will host the repository.

    • JDBC URL: The complete path of the data server to host the Work Repository.

      Note that the parameters JDBC Driver and URL are synchronized and the default values are technology dependant

    • User: User ID / login of the owner of the tables you are going to create and host of the Work Repository.

    • Password: This user's password.

  4. Click Test to verify that the connection is working.

  5. Click Next.

    Oracle Data Integrator verifies whether a Work Repository already exists on the connection specified in Step 3:

    • If an existing Work Repository is detected on this connection, the next steps will consist of attaching the Work Repository to the Master Repository. Refer to "Attaching and Deleting a Work Repository" in Oracle Fusion Middleware Developer's Guide for Oracle Data Integrator for further instructions.

    • If no Work Repository is detected on this connection, a new Work Repository is created. Continue with the creation of a new Work Repository and provide the Work Repository details in following step.

  6. Specify the Oracle Data Integrator Work Repository properties:

    • ID: A specific ID for the new repository, rather than the default 0.

      Note:

      Oracle recommends that this ID is unique and not used for any other Master Repository, as it affects imports and exports between repositories. For more information, see "Exporting/Importing" in Oracle Fusion Middleware Developer's Guide for Oracle Data Integrator.

    • Name: Give a unique name to your Work Repository (for example: DEVWORKREP1).

    • Password: Enter the password for the Work Repository.

    • Type: Select the type for the Work Repository:

      • Development: This type of repository allows management of design-time objects such as data models and projects (including interfaces, procedures, etc). A development repository includes also the run-time objects (scenarios and sessions). This type of repository is suitable for development environments.

      • Execution: This type of repository only includes run-time objects (scenarios, schedules and sessions). It allows launching and monitoring of data integration jobs in Operator Navigator. Such a repository cannot contain any design-time artifacts. Designer Navigator cannot be used with it. An execution repository is suitable for production environments.

  7. Click Finish.

  8. The Create Work Repository login dialog opens. If you want to create a login for the Work Repository, click Yes and you will be asked to enter the Login Name in a new dialog. If you do not want to create a Work Repository login, click No.

  9. Click Save in the toolbar.

For more information, refer to Section E.6, "Connecting to a Work Repository".

E.6 Connecting to a Work Repository

To connect to an existing Work Repository and launch Designer Navigator:

  1. Open the New Gallery by choosing File > New.

  2. In the New Gallery, in the Categories tree, select ODI.

  3. Select from the Items list Create a New ODI Repository Login.

  4. Click OK.

    The Repository Connection Information dialog opens.

  5. Specify the Oracle Data Integrator connection details as follows:

    • Login name: A connection alias (for example: Repository)

      The connection alias is used to connect to the Work Repositories and then to create the Master Repository as described in Section E.4.

    • User: The ODI supervisor user name you have defined when creating the Master Repository or an ODI user name you have defined in the Security Navigator after having created the Master Repository.

      Note that the Supervisor user name is case-sensitive, and must be entered exactly as previously defined (for example, SUPERVISOR in all CAPS).

    • Password: The ODI supervisor password you have defined when creating the Master Repository or an ODI user password you have defined in the Security Navigator after having created the Master Repository.

  6. Specify the Database Connection (Master Repository) details as follows:

    • User: Database user ID/login of the schema (database, library) that contains the ODI Master Repository.

    • Password: This user's password.

    • Driver List: Select the driver required to connect to the DBMS supporting the Master Repository you have just created from the drop-down list.

    • Driver Name: The complete driver name.

    • URL: The URL used to establish the JDBC connection to the database hosting the repository.

  7. Click Test to verify that the connection is working.

  8. Select Work Repository and specify the Work Repository details as follows:

    • Work repository name: The name you gave your Work Repository in the previous step (WorkRep1 in the example). You can display the list of Work Repositories available in your Master Repository by clicking on the button to the right of this field.

  9. Click OK to validate your entries.