2 Administering Repositories
This chapter includes the following sections:
See Also:
-
For more information about creating ODI repositories in RCU, see Installing and Configuring Oracle Data Integrator.
-
For more information about upgrading ODI repositories, see Upgrading Oracle Data Integrator.
Introduction to Oracle Data Integrator 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.
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.
Creating Repository Storage Spaces
Oracle Data Integrator repositories can be installed on database engines supported by Oracle Fusion Middleware 12c. For the latest list of supported databases versions as well as the requirements for each database, see:
http://www.oracle.com/technetwork/middleware/ias/downloads/fusion-certification-100350.html
For each database that will contain a repository, a storage space must be created.
Caution:
For reasons of maintenance and back-up, Oracle strongly recommends that repositories be stored separately from your application data (for example, in a different schema for an Oracle database, or in a different database for Microsoft SQL Server).
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 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 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 and TableNAme). |
Sybase ASE |
Create a database Use I-SQL to launch the following commands: isql -Usa -P<sa password> -S<Server> use master go disk init name = "<Device Name>", physname = "<Actual path>", size = <Size> go create database <database name> on <Device Name>='<Size>' log on <Device Name>='<Size>' WITH OVERRIDE go use <database name> go sp_addlogin '<DB USERNAME>', '<password>', <database name> go sp_adduser '<DB USERNAME>' go grant CREATE TABLE to <DB USERNAME> Note: Use a different device for log and data. Also, |
MySQL |
|
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:
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:
Oracle Data Integrator begins creating your master repository. You can follow the procedure on your Messages – Log. To test your master repository, refer to Connecting to the Master Repository.
Note:
By default, the master repository uses AES-128 for encryption. However, if you want to create a master repository with the encryption algorithm AES-256, you need to update the odi.conf
file to include the following:
AddVMOption: Doracle.odi.encryption.algo=AES
AddVMOption: Doracle.odi.encryption.keylen=256
For information regarding AES, see Advanced Encryption Standard.
Connecting to the Master Repository
To connect to the master repository, follow the instructions in the Connecting to the Master Repository section in Installing and Configuring Oracle Data Integrator.
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:
For more information, refer to Connecting to a Work Repository.
Connecting to a Work Repository
To connect to a work repository, click Connect to repository in ODI Studio, and enter the credentials you specified in Step 8 of Creating a Work Repository.
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:
Changing a Work Repository Password
To change a work repository password:
- In the Repositories tree of the Topology Navigator, expand the Work Repositories node.
- Double-click a work repository, or right-click and select Open. The Work Repository Editor opens.
- On the Definition tab of the Work Repository Editor, click Change password.
- Enter the current password, and enter the new password twice.
- Click OK.
Advanced Actions for Administering Repositories
Advanced actions for administering repositories do not concern the creation process of repositories. The actions described in this section deal with advanced actions performed on already existing repositories. Once the repositories are created you may want to switch the password storage or you may need to recover the password storage after a credential store crash. Actions dealing with password handling are covered in Setting Up External Password Storage. For information regarding the export and import of master and work repositories, see the Exporting and Importing chapter in Developing Integration Projects with Oracle Data Integrator.
This section contains the following topics:
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:
-
In the Topology Navigator, go to the Repositories panel.
-
Right-click the Work Repositories node and select New Work Repository.
The Work Repository Creation Wizard opens.
-
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.
-
-
Click Test Connection to check the connection is working.
-
Click Next.
-
Specify the Password of the Oracle Data Integrator work repository to attach.
-
Click Next.
-
Specify the Name of the Oracle Data Integrator work repository to attach.
-
Click Finish.
Deleting a Work Repository
To detach a repository, delete the link from the master repository using the following procedure:
- In the Topology Navigator, expand the Repositories panel.
- Expand the Work Repositories node and right-click the work repository you want to delete.
- Select Delete.
- In the Confirmation dialog click Yes.
- The work repository is detached from the master repository and is removed from the Repositories panel in Topology Navigator.
Erasing a Work Repository
Deleting a work repository is equivalent to detaching a work repository from the master repository. For more information, refer to Attaching and Detaching (Deleting) a Work Repository.
Erasing a work repository consists of deleting the work repository from the database.
WARNING:
Erasing your work repository is an irreversible operation. All information stored in the work repository will be definitively deleted, including the metadata of your models, projects and run-time information such as scenarios, schedules, and logs.
Erasing a Work Repository
To erase a work repository from the database:
- In the Topology Navigator, expand the Repositories panel.
- Expand the Work Repositories node and right-click the work repository you want to delete.
- Select Erase from Database.
- In the Confirmation dialog click Yes, if you want to definitively erase the work repository from the database.
- The work repository is erased from the database and is deleted from the Repositories panel in Topology Navigator.
Configuring Repository Connections
Concurrent connections to the repository database may be controlled and limited by the database engine where the repository is stored. On Oracle the initialization parameter limiting the number of connections is processes. When running a large number of parallel executions, you may need to tune the database to increase the maximum number of connections allowed to the repository database.
The number of connections required depends on the number of ODI Studio instances and Load Plan steps running:
-
Each ODI Studio session requires two database connections (one to the master, one to the work repository) for the duration of the session, and also a third database connection is required for a security check for a very short period when the session begins.
-
For non-Oracle databases, each Load Plan step consumes an additional connection as a lock while the Load Plan is being executed.