Using an Oracle database for the metadata repository

If you are using the Oracle database, you can use the following workaround to install multiple instances of the metadata repository on the same Oracle database.

Note: Use this workaround if you are using only the InfoSphere DataStage component from the InfoSphere Information suite of products.
To use the Oracle database for the metadata repository, complete the following steps:
  1. Connect to the Oracle database.
  2. Create the IBM Information Server metadata repository database by using the following script.
    CREATE TABLESPACE <XMETA_TS> DATAFILE < datafile_full_path> SIZE 1G 
AUTOEXTEND ON EXTENT MANAGEMENT LOCAL AUTOALLOCATE ONLINE; 

CREATE USER <XMETA_USER> IDENTIFIED BY <XMETA_USER> DEFAULT TABLESPACE <XMETA_TS> 
TEMPORARY TABLESPACE TEMP QUOTA UNLIMITED ON <XMETA_TS>; 

GRANT CREATE SESSION TO <XMETA_USER>; 
GRANT CREATE ANY TABLE TO <XMETA_USER>; 
GRANT COMMENT ANY TABLE TO <XMETA_USER>; 
GRANT CREATE ANY INDEX TO <XMETA_USER>; 
GRANT INSERT ANY TABLE TO <XMETA_USER>; 
GRANT DROP ANY TABLE TO <XMETA_USER>; 
GRANT CREATE ANY VIEW TO <XMETA_USER>; 
GRANT DROP ANY VIEW TO <XMETA_USER>; 
GRANT CREATE TRIGGER TO <XMETA_USER>; 
GRANT CREATE PROCEDURE TO <XMETA_USER>; 
GRANT CREATE SEQUENCE TO <XMETA_USER>; 
GRANT ADMINISTER DATABASE TRIGGER TO <XMETA_USER>; 
GRANT EXECUTE ON SYS.DBMS_LOB TO <XMETA_USER>;
  3. Create the following views. Ensure that the views are not already present in the database.
    Replace N with any number.

CREATE USER CMVIEWS_N IDENTIFIED BY <XMETA_USER> DEFAULT TABLESPACE <XMETA_USER> 
TEMPORARY TABLESPACE TEMP QUOTA UNLIMITED ON <XMETA_USER> PASSWORD EXPIRE ACCOUNT LOCK; 

CREATE USER IGVIEWS_N IDENTIFIED BY <XMETA_USER> DEFAULT TABLESPACE <XMETA_USER> 
TEMPORARY TABLESPACE TEMP QUOTA UNLIMITED ON <XMETA_USER> PASSWORD EXPIRE ACCOUNT LOCK; 

CREATE USER IAVIEWS_N IDENTIFIED BY <XMETA_USER> DEFAULT TABLESPACE <XMETA_USER> 
TEMPORARY TABLESPACE TEMP QUOTA UNLIMITED ON <XMETA_USER> PASSWORD EXPIRE ACCOUNT LOCK; 

CREATE USER CEFVIEWS_N IDENTIFIED BY <XMETA_USER> DEFAULT TABLESPACE <XMETA_USER> 
TEMPORARY TABLESPACE TEMP QUOTA UNLIMITED ON <XMETA_USER> PASSWORD EXPIRE ACCOUNT LOCK; 

CREATE USER REMVIEWS_N IDENTIFIED BY <XMETA_USER> DEFAULT TABLESPACE <XMETA_USER> 
TEMPORARY TABLESPACE TEMP QUOTA UNLIMITED ON xmetao12 PASSWORD EXPIRE ACCOUNT LOCK;
  4. Configure the IBM Information Server staging area by using the following script.
    CREATE TABLESPACE <XMETA_STG> DATAFILE < datafile_full_path> SIZE 1G 
AUTOEXTEND ON EXTENT MANAGEMENT LOCAL AUTOALLOCATE ONLINE; 

CREATE USER <XMETA_STG_USER> IDENTIFIED BY <XMETA_STG_USER> DEFAULT TABLESPACE <XMETA_STG_USER> 
TEMPORARY TABLESPACE TEMP QUOTA UNLIMITED ON <XMETA_STG_USER>; 

GRANT CREATE SESSION TO <XMETA_STG_USER>; 
GRANT CREATE ANY TABLE TO <XMETA_STG_USER>; 
GRANT COMMENT ANY TABLE TO <XMETA_STG_USER>; 
GRANT CREATE ANY INDEX TO <XMETA_STG_USER>; 
GRANT INSERT ANY TABLE TO <XMETA_STG_USER>; 
GRANT DROP ANY TABLE TO <XMETA_STG_USER>; 
GRANT CREATE ANY VIEW TO <XMETA_STG_USER>; 
GRANT DROP ANY VIEW TO <XMETA_STG_USER>; 
GRANT CREATE TRIGGER TO <XMETA_STG_USER>; 
GRANT CREATE PROCEDURE TO <XMETA_STG_USER>; 
GRANT CREATE SEQUENCE TO <XMETA_STG_USER>; 
GRANT ADMINISTER DATABASE TRIGGER TO <XMETA_STG_USER>; 
GRANT EXECUTE ON SYS.DBMS_LOB TO <XMETA_STG_USER>
  5. Locate the InfoSphere DataStage installation package on the server where you will install InfoSphere DataStage. Unzip the installation package.
  6. Navigate to the is-suite\payloads\ISModels\ISMODELSServer\resources\ folder.
  7. Open the sqlview_schema.properties file and rename the CMVIEWS, IGVIEWS, and IAVIEWS.
    Original data in the file:
com.ibm.iis.xmeta.model.sqlView.defaultSchema.ASCLModel=CMVIEWS
com.ibm.iis.xmeta.model.sqlView.defaultSchema.ASCLCustomAttribute=CMVIEWS
com.ibm.iis.xmeta.model.sqlView.defaultSchema.GlossaryExtensions=IGVIEWS
com.ibm.iis.xmeta.model.sqlView.defaultSchema.Insight=IGVIEWS
com.ibm.iis.xmeta.model.sqlView.defaultSchema.investigate=IAVIEWS

Data after editing the file:
Replace N with any number.

com.ibm.iis.xmeta.model.sqlView.defaultSchema.ASCLModel=CMVIEWS_N
com.ibm.iis.xmeta.model.sqlView.defaultSchema.ASCLCustomAttribute=CMVIEWS_N
com.ibm.iis.xmeta.model.sqlView.defaultSchema.GlossaryExtensions=IGVIEWS_N
com.ibm.iis.xmeta.model.sqlView.defaultSchema.Insight=IGVIEWS_N
com.ibm.iis.xmeta.model.sqlView.defaultSchema.investigate=IAVIEWS_N
  8. Install InfoSphere DataStage by using the installation wizard and following these guidelines.
    • Start the DataStage installation process using the installation wizard.
      Note: If an exception occurs during the installation, click Continue installation to proceed with the installation.
    • For the Installation Type Selection, select New Installation.
    • From the Tier Selection step, select Services Tier and Engine Tier. Do not select the Metadata Repository Tier.
    • In the Database Connection step, provide the database access information for the Oracle database that hosts the metadata repository.

      Provide the hostname, port , Database name, username , password created in steps 2, 3, and 4.

Parent topic: Metadata Repository for multiple instances of InfoSphere DataStage