ODI – Basics of Repositories


There are two types of repositories in Oracle Data Integrator:

  • Master Repository: This is a data structure containing information on the topology of the company’s IT resources, on security and on version management of projects and data models. This repository is stored on a relational database accessible in client/server mode from the different Oracle Data Integrator modules. In general, you need only one master repository. However, it may be necessary to create several master repositories in one of the following cases:
    • Project construction over several sites not linked by a high-speed network (off-site development, for example).
    • Necessity to clearly separate the operating environments (development, test, production), including on the database containing the master repository. This may be the case if these environments are on several sites.
  • Work Repository: This is a data structure containing information about data models, projects, and their use. This repository is stored on a relational database accessible in client/server mode from the different Oracle Data Integrator modules. Several work repositories can be created with several master repositories if necessary. However, a work repository can be linked with only one master repository for version management purposes.

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

Creating Repository Storage Spaces

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

Your master repository can be stored in the same schema as one of your work repositories. However, 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 odim to host the Master repository and a schema odiw to host the work repository.

The schemas are created by the following SQL commands:

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, such as odim and odiw

MY_PASS corresponds to the password you have given it <MY_TBS> the Oracle tablespace where the data will be stored

MY_TEMP temporary default tablespace

Microsoft SQL Server 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 which have these databases by default.

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

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.

Note: Oracle recommends configuring the Microsoft SQL Server databases that store the repository information with a case-sensitive collation. This enables reverse-engineering and creating multiple objects with the same name but a different case (for example: tablename andTableNAme).

DB2/400 Create a library odim to host the Master repository and a schema odiw to host the work repository. Create two users odim and odiw who have these libraries by default.

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

DB2/UDB Prerequisites:

  • Master and work repository users must have access to tablespaces with minimum 16k pagesize
  • The database must have a temporary tablespace with minimum 16 k

For example:

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

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. In ODI Studio, open the New Gallery dialog by choosing File > New.
  2. In the New Gallery dialog, 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 JDBC Url are synchronized and the default values are technology dependent.

    • User: The user ID / login of the owner of the tables (for example, odim).
    • Password: This user’s password.
    • DBA User: The database administrator’s username.
    • DBA Password: This user’s password.
  6. Specify Repository Configuration parameters as needed.
  7. Click Test Connection 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. The value must be SUPERVISOR.
      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
      Supervisor Password This user’s password

       

      Click Next.

      Specify the password storage details for your data servers (that is, sources and targets, which are defined in the Topology):

      • Select Internal Password Storage if you want to store passwords in the Oracle Data Integrator master repository
      • Select External Password Storage if you want use JPS Credential Store Framework (CSF) to store the data server and context passwords in a remote credential store. Indicate theMBean Server Parameters to access the credential store.

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

Creating a Work Repository

A master repository can have one or more work repositories associated with it; a work repository, however, can only be associated with one master repository.

To create a new work repository:

  1. In the Topology Navigator, open the Repositories panel.
  2. Right-click the Work Repositories node and select New Work Repository.

    The Work Repository Creation 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 JDBC Url are synchronized and the default values are technology dependent.

      Oracle recommends using the full machine name instead of localhost in the JDBC URL to avoid connection issues. For example, for remote clients, the client (ODI Studio or SDK) is on a different machine than the work repository and localhost points to the current client machine instead of the one hosting the work repository.

    • 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 Connection 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 in attaching the work repository to the master repository.
    • 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 step 6.
  6. Specify the Oracle Data Integrator work repository properties:
    • Name: Give a unique name to your work repository (for example: DEVWORKREP1).
    • Password: Optional. Enter a password required for attaching this work repository to a different master. If you leave this option blank, no password is required for this operation.
    • 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 mappings, procedures, and so on). A development repository also includes 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.

Connecting to a Work Repository

To connect to a work repository, click the “Connect to repository” button in ODI Studio, and enter the credentials you specified in Step 8 above

If you did not create a work repository Login Name in Step 8 of Creating a Work Repository, in order to connect, you must create a login by performing the following steps:

  1. In ODI Studio, open the New Gallery dialog by choosing File > New.
  2. In the New Gallery dialog, 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 generic alias (for example: Repository)
    • 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.
    • 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 dropdown list.
    • Driver Name: The complete driver name
    • Hurl: The URL used to establish the JDBC connection to the database hosting the repository
  7. Click Test to check 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.

Changing a Work Repository Password

To change a work repository password:

  1. In the Repositories tree of the Topology Navigator, expand the Work Repositories node.
  2. Double-click a work repository, or right-click and select Open. The Work Repository Editor opens.
  3. On the Definition tab of the Work Repository Editor, click Change password.
  4. Enter the current password, and enter the new password twice.
  5. Click OK.

Attaching and Detaching (Deleting) a Work Repository

Attaching a work repository consists of linking an existing work repository to the current master repository. This existing work repository already exists in the database and has been previously detached from this or another master repository.

Deleting a work repository detaches it, by deleting its link to the master repository. This is an opposite operation to attaching. This operation does not destroy the work repository content.

Attaching a Work Repository

To attach a work repository to a master repository:

  1. In the Topology Navigator, go to the Repositories panel.
  2. Right-click the Work Repositories node and select New Work Repository.

    The Work Repository Creation Wizard opens.

  3. Specify the Oracle Data Integrator work repository connection details as follows:
    • Technology: From the list, select the technology that will 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 JDBC Url are synchronized and the default values are technology dependent

    • 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 Connection to check the connection is working.
  5. Click Next.
  6. Specify the Password of the Oracle Data Integrator work repository to attach.
  7. Click Next.
  8. Specify the Name of the Oracle Data Integrator work repository to attach.
  9. Click Finish.

Deleting a Work Repository

To detach a repository, delete the link from the master repository using the following procedure:

  1. In the Topology Navigator, expand the Repositories panel.
  2. Expand the Work Repositories node and right-click the work repository you want to delete.
  3. Select Delete.
  4. In the Confirmation dialog click Yes.
  5. The work repository is detached from the master repository and is removed from the Repositories panel in Topology Navigator.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s