problem with viewing and importing Oracle metadata
Posted: Sun Jun 14, 2009 8:13 pm
Hello,
I have installed the Visual Studio 2003 C++ compiler.
I have installed DataStage Enterprise Edition Server and Client on the same machine.
I have also installed the Oracle 10g server and client on the same machine.
I am trying to import metadata from the OE sample database that I created that comes with Oracle.
I have granted the OE user the following role as specified in the documentation:
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;
Once the role is created, grant it to users who will run DataStage jobs, as
follows:
GRANT DSXE to oe;
I have also set up my environment variables according to documentation.
My ultimate goal is to be able to view data from the Oracle Enterprise stage.
First I need to be able to import the table definition which is the objective of this post.
I am trying to import the table definition using 3 different table definition importer in Manger:
Orchestrate Schema Definitions
ODBC Table Defintions
Plug-in Meta Data Definitions
I have had some limited success.
Correct me if I'm wrong but are Orchestrate Schema Definitions the recommended way to import Oracle table definitions into datastage?
Ok this is what happens to me:
---------------------------------------------------------------------------------
I try to import table definitions with Orchestrate Schema Definitions:
Database Table: OE.INVENTORIES
For Owner: OE
DBMS Type: Oracle
Database Server: I leave this blank because the database is not on a remote server.
User Name: oe
Password: (this I enter correctly)
Ok so first I click on the browse button to try to browse for the table and get:
DataStage Error
Cannot get list of table names from database
then
*** Error executing command: orchdbutil tablenames -owner OE -dbtype oracle -dboptions *********
##I TFCN 000001 21:46:36(000) <main_program>
Ascential DataStage(tm) Enterprise Edition 7.5
Copyright (c) 2004, 1997-2004 Ascential Software Corporation.
All Rights Reserved
##I TCDU 000005 21:46:36(001) <main_program> Unable to access database {0}DB type 'oracle' presumed to be oracle.
##E TCDU 000002 21:46:36(002) <main_program> Unable to access database {0}Oracle: not present
##E TCDU 000006 21:46:36(003) <main_program> Unable to access database {0}Unable to load library 'orchoracle'.
So I click next with table name I typed in - instead of browsing and get:
DataStage Error
Cannot get schema definition for requested database table: OE.INVENTORIES (Oracle)
View output from server command?
Yes
*** Error executing command: orchdbutil show OE.INVENTORIES -dbtype oracle -dboptions *********
##I TFCN 000001 21:47:43(000) <main_program>
Ascential DataStage(tm) Enterprise Edition 7.5
Copyright (c) 2004, 1997-2004 Ascential Software Corporation.
All Rights Reserved
##I TCDU 000005 21:47:43(001) <main_program> Unable to access database {0}DB type 'oracle' presumed to be oracle.
##E TCDU 000002 21:47:43(002) <main_program> Unable to access database {0}Oracle: not present
##E TCDU 000006 21:47:43(003) <main_program> Unable to access database {0}Unable to load library 'orchoracle'.
------------------------------------------------------------------
I try to import table definitions using ODBC Table Defintions:
Import MetaData (ODBC)
Seen from: localhost
I select a system DSN that I created for oracle 10g.
User: oe
Pass: (correct password)
It retrieves all the tables.
I select the OE.INVENTORIES table that I tried with the Orchestrate definitions and do View Data, it shows me all the data.
If I select another table in the same schema for example:
OE.CUSTOMERS
I get an error:
DataStage Repository Interface
Error calling subroutine: DSD.Browse (Action=3); check DataStage is set up correctly in project p
Data Browser
Data source is empty
DataStage Repository Interface
Error calling subroutine: DSD.Browse (Action=2); check DataStage is set up correctly in project p
(The connection is broken (81002))
So I reselect the OE.INVENTORIES table that I was able to View Data on in the ODBC table definitions and click Import:
DataStage Repository Interface
Error calling subroutine: DSR_RECORD (Action=5); check DataStage is set up correctly in project p
(The connection is broken (81002))
DataStage Repository Interface
Error calling subroutine: DSR_WHEREUSED (Action=3); check DataStage is set up correctly in project p
(The connection is broken (81002))
and a final error message
Error in execution of helper subroutine - abandoned.
Confirm import of: table definintion
ODBC\oracle_10g\OE.INVENTORIES already exists
Are you sure you want to overwrite it?
(this is strange because I had imported it before and deleted it as well as the ODBC and oracle_10g folder and sub-folder from Manager)
so it's like trying to import it but giving me errors
so I click Yes Overwrite and it does but another error pops up
DataStage Repository Interface
Error calling subroutine: DSR_IMPORT (Action=3); check DataStage is set up correctly in project p
(The connection is broken (81002))
so I see the import progress window and I go to refresh the Manager interface to see the "new" table definitions and get another pop up error:
DataStage Repository Interface
Error calling subroutine: *DataStage*DSR_SELECT (Action=4); check DataStage is set up correctly in project p
(The connection is broken (81002))
I get the above error for each object category it tries to load up in the Manager interface and by this time I have to quit Manager and start it up again because I keep getting these DataStage Repository Interface messages.
The catch is that when I do repopen manager the imported table definitions are not there.
----------------------------------------------------------------------------------
When I try to import table definitions with Plug-in Meta Data Definitions:
I select ORAOCI9 Oracle OCI Stage version 1.1
Database Source Name: orcl
User Id: oe
Password: (I enter the correct password)
I click next and get a drop down of all the owners in my database - GOOD
I select Tables, Views, Fully Qualified Table Names, Column Comments.
I select the OE user.
I click next and can see a list of all the OE tables owned by the OE user.
I select OE.INVENTORIES and do View Data - no problem I see the data.
I select OE.CUSTOMERS and do View Data and get an error:
DataStage Repository Interface
DSBrowser..DSBrowser1: Oracle datatype not presently supported
DSBrowser..DSBrowser1.DSLink1: DSP.Open GCI $DSP.Open error -100.
So out of all the tables owned by OE I can View Data sucessfully on some while with others I get the "Oracle datatype not presently supported".
I want to be able to use all Oracle datatypes.
So I select OE.INVENTORIES and click Import:
SUCCESS, the table definition imports successfully
-----------------------------------------------------------------
Could anyone advise me as to which method or the best method to use to import your table definitions from Oracle into Manager in order to import sucessfully and consistently?
I have installed the Visual Studio 2003 C++ compiler.
I have installed DataStage Enterprise Edition Server and Client on the same machine.
I have also installed the Oracle 10g server and client on the same machine.
I am trying to import metadata from the OE sample database that I created that comes with Oracle.
I have granted the OE user the following role as specified in the documentation:
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;
Once the role is created, grant it to users who will run DataStage jobs, as
follows:
GRANT DSXE to oe;
I have also set up my environment variables according to documentation.
My ultimate goal is to be able to view data from the Oracle Enterprise stage.
First I need to be able to import the table definition which is the objective of this post.
I am trying to import the table definition using 3 different table definition importer in Manger:
Orchestrate Schema Definitions
ODBC Table Defintions
Plug-in Meta Data Definitions
I have had some limited success.
Correct me if I'm wrong but are Orchestrate Schema Definitions the recommended way to import Oracle table definitions into datastage?
Ok this is what happens to me:
---------------------------------------------------------------------------------
I try to import table definitions with Orchestrate Schema Definitions:
Database Table: OE.INVENTORIES
For Owner: OE
DBMS Type: Oracle
Database Server: I leave this blank because the database is not on a remote server.
User Name: oe
Password: (this I enter correctly)
Ok so first I click on the browse button to try to browse for the table and get:
DataStage Error
Cannot get list of table names from database
then
*** Error executing command: orchdbutil tablenames -owner OE -dbtype oracle -dboptions *********
##I TFCN 000001 21:46:36(000) <main_program>
Ascential DataStage(tm) Enterprise Edition 7.5
Copyright (c) 2004, 1997-2004 Ascential Software Corporation.
All Rights Reserved
##I TCDU 000005 21:46:36(001) <main_program> Unable to access database {0}DB type 'oracle' presumed to be oracle.
##E TCDU 000002 21:46:36(002) <main_program> Unable to access database {0}Oracle: not present
##E TCDU 000006 21:46:36(003) <main_program> Unable to access database {0}Unable to load library 'orchoracle'.
So I click next with table name I typed in - instead of browsing and get:
DataStage Error
Cannot get schema definition for requested database table: OE.INVENTORIES (Oracle)
View output from server command?
Yes
*** Error executing command: orchdbutil show OE.INVENTORIES -dbtype oracle -dboptions *********
##I TFCN 000001 21:47:43(000) <main_program>
Ascential DataStage(tm) Enterprise Edition 7.5
Copyright (c) 2004, 1997-2004 Ascential Software Corporation.
All Rights Reserved
##I TCDU 000005 21:47:43(001) <main_program> Unable to access database {0}DB type 'oracle' presumed to be oracle.
##E TCDU 000002 21:47:43(002) <main_program> Unable to access database {0}Oracle: not present
##E TCDU 000006 21:47:43(003) <main_program> Unable to access database {0}Unable to load library 'orchoracle'.
------------------------------------------------------------------
I try to import table definitions using ODBC Table Defintions:
Import MetaData (ODBC)
Seen from: localhost
I select a system DSN that I created for oracle 10g.
User: oe
Pass: (correct password)
It retrieves all the tables.
I select the OE.INVENTORIES table that I tried with the Orchestrate definitions and do View Data, it shows me all the data.
If I select another table in the same schema for example:
OE.CUSTOMERS
I get an error:
DataStage Repository Interface
Error calling subroutine: DSD.Browse (Action=3); check DataStage is set up correctly in project p
Data Browser
Data source is empty
DataStage Repository Interface
Error calling subroutine: DSD.Browse (Action=2); check DataStage is set up correctly in project p
(The connection is broken (81002))
So I reselect the OE.INVENTORIES table that I was able to View Data on in the ODBC table definitions and click Import:
DataStage Repository Interface
Error calling subroutine: DSR_RECORD (Action=5); check DataStage is set up correctly in project p
(The connection is broken (81002))
DataStage Repository Interface
Error calling subroutine: DSR_WHEREUSED (Action=3); check DataStage is set up correctly in project p
(The connection is broken (81002))
and a final error message
Error in execution of helper subroutine - abandoned.
Confirm import of: table definintion
ODBC\oracle_10g\OE.INVENTORIES already exists
Are you sure you want to overwrite it?
(this is strange because I had imported it before and deleted it as well as the ODBC and oracle_10g folder and sub-folder from Manager)
so it's like trying to import it but giving me errors
so I click Yes Overwrite and it does but another error pops up
DataStage Repository Interface
Error calling subroutine: DSR_IMPORT (Action=3); check DataStage is set up correctly in project p
(The connection is broken (81002))
so I see the import progress window and I go to refresh the Manager interface to see the "new" table definitions and get another pop up error:
DataStage Repository Interface
Error calling subroutine: *DataStage*DSR_SELECT (Action=4); check DataStage is set up correctly in project p
(The connection is broken (81002))
I get the above error for each object category it tries to load up in the Manager interface and by this time I have to quit Manager and start it up again because I keep getting these DataStage Repository Interface messages.
The catch is that when I do repopen manager the imported table definitions are not there.
----------------------------------------------------------------------------------
When I try to import table definitions with Plug-in Meta Data Definitions:
I select ORAOCI9 Oracle OCI Stage version 1.1
Database Source Name: orcl
User Id: oe
Password: (I enter the correct password)
I click next and get a drop down of all the owners in my database - GOOD
I select Tables, Views, Fully Qualified Table Names, Column Comments.
I select the OE user.
I click next and can see a list of all the OE tables owned by the OE user.
I select OE.INVENTORIES and do View Data - no problem I see the data.
I select OE.CUSTOMERS and do View Data and get an error:
DataStage Repository Interface
DSBrowser..DSBrowser1: Oracle datatype not presently supported
DSBrowser..DSBrowser1.DSLink1: DSP.Open GCI $DSP.Open error -100.
So out of all the tables owned by OE I can View Data sucessfully on some while with others I get the "Oracle datatype not presently supported".
I want to be able to use all Oracle datatypes.
So I select OE.INVENTORIES and click Import:
SUCCESS, the table definition imports successfully
-----------------------------------------------------------------
Could anyone advise me as to which method or the best method to use to import your table definitions from Oracle into Manager in order to import sucessfully and consistently?