Configuring Oracle database connectivity in a parallel environment in DataStage

Configuring access to Oracle databases includes granting the appropriate access level to users.

Granting access to the Oracle parallel server

To access the Oracle parallel server (OPS), users must have SELECT access to the sys.gv_$instance and sys.v_$cache tables.

1.          Run the following SQL statements:

            GRANT select ON sys.gv_$instance TO public;
            GRANT select ON sys.v_$cache TO public;

2.          If the Oracle parallel server is not installed, set the APT_ORACLE_NO_OPS environment variable to disable OPS checking on the Oracle Enterprise stage.

Granting user privileges to run Oracle Enterprise stage jobs

You must grant privileges to each user who runs jobs that contain the Oracle Enterprise stage.

1.          Create the user-defined environment variable ORACLE_HOME and set it to the $ORACLE_HOME path, such as /disk3/oracle10.

2.          Create the user-defined environment variable ORACLE_SID and set it to the correct service name, such as ODBCSOL.

3.          Add ORACLE_HOME/bin to your PATH and ORACLE_HOME/lib to your operating system's library path (LIBPATH, LD_LIBRARY_PATH, or SHLIB_PATH).

4.          Obtain login privileges to Oracle by using a valid Oracle user name and password. The user name and password must be recognized by Oracle before you attempt to access it.

5.          Obtain the SELECT privilege on the following tables:

DBA_EXTENTS

DBA_DATA_FILES

DBA_TAB_PARTITONS

DBA_TAB_SUBPARTITIONS

DBA_OBJECTS

ALL_PART_INDEXES

ALL_PART_TABLES

ALL_INDEXES

SYS.GV_$INSTANCE (required only if you use the Oracle parallel server) 

6.          Verify that the APT_ORCHHOME/bin directory is before the ORACLE_HOME/bin directory in the PATH statement.

7.          Create a role that has SELECT privileges on the tables. The following code creates a role named DSXE, which grants SELECT privileges on the required tables:

           CREATE ROLE DSXE;

            GRANT SELECT on sys.dba_extents to DSXE;

            GRANT SELECT on sys.dba_data_files to DSXE;

            GRANT SELECT on sys.dba_tab_partitions to DSXE;

            GRANT SELECT on sys.dba_tab_subpartitions to DSXE;

            GRANT SELECT on sys.dba_objects to DSXE;

            GRANT SELECT on sys.all_part_indexes to DSXE;

            GRANT SELECT on sys.all_part_tables to DSXE;

            GRANT SELECT on sys.all_indexes to DSXE;

8.          Enter this command to grant the role to users who run jobs:

            GRANT role_name to oracle_user;

njoy the simplicity.......
Atul Singh