problem with viewing and importing Oracle metadata

[dsex100]

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?

[chulett]

I have also set up my environment variables according to documentation.

Please explain what this means, what exactly you set up where.

[dsex100]

I have an Oracle schema setup which contains tables.

These problems I'm having happen when I go to import the table definitions from the these tables into DataStage Manager.

[ray.wurlod]

I think what Craig meant was for you to reveal how you have set up connectivity to Oracle from the DataStage server. We're talking environment variables and tnsnames.ora particularly here.

[sankar.td]

Hi dsex,


Can you please send the link to down load Visual Studio 2003 C++ compiler.
I tried from Microsoft site .But it is not available .


Thanks in advance.

[chulett]

I think what Craig meant was for you to reveal how you have set up connectivity to Oracle from the DataStage server. We're talking environment variables and tnsnames.ora particularly here.

Yes, exactly.

[dsex100]

OK thank you,

Here are my environment variables from Datastage Administrator:

General:
PATH (Shell Search Path) empty
TMPDIR (Temporary Directory) empty

User Defined:
ORACLE_HOME F:\oracle\product\10.2.0\db_1
ORACLE_SID orcl

APT_COMPILEOPT -W/TP -W/EHa -DAPT_USE_ANSI_IOSTREAMS -c
APT_COMPILER cxx
APT_LINKER cxx
APT_LINKOPT -W/TP -W/dll -W/base:0x50000000

Here are my environment variables from the Windows Server 2003 Enterprise SP2 system:

Path

F:\PROGRA~1\MKSTOO~1\bin;F:\PROGRA~1\MKSTOO~1\bin\X11;F:\PROGRA~1\MKSTOO~1\mksnt;F:\Ascential\DataStage\PXEngine\bin;F:\oracle\product\10.2.0\db_1\bin;%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;F:\Program Files\Microsoft SQL Server\80\Tools\Binn\;F:\Program Files\Microsoft SQL Server\90\DTS\Binn\;F:\Program Files\Microsoft SQL Server\90\Tools\binn\;F:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\;F:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\


My oracle tnsnames.ora is the following:

# tnsnames.ora Network Configuration File: F:\oracle\product\10.2.0\db_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = MYPC.oracle.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.oracle.com)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

My oracle tnsnames.ora is located in F:\oracle\product\10.2.0\db_1\network\admin\


Could anyone see a problem?

[dsex100]

Hi dsex,


Can you please send the link to down load Visual Studio 2003 C++ compiler.
I tried from Microsoft site .But it is not available .


Thanks in advance.

i used the c++ compiler that is part of the visual studio 2003 enterprise architect edition installation.

the other stand alone one is not available any more, had to use this one

[dsex100]

Hi,

I was wondering if anyone could take a look at this one with the additional details I have posted..

[dsex100]

I finally got this working.

My problem was with my environment variables setup.

In the windows control panel, I noticed that there were "user -defined" as well as system variables. By putting all the variables in the appropriate "user defined" or system sections I was able to get it to work with Oracle 10g.

The only thing that I didn't find in the documentation was that I had to run the install.liborchoracle script to install the orchestra oracle libraries.

In doing that I noticed that my ORACLE_HOME was already setup howver the APT_ORCHHOME variable was not, so I had to export that and run install.liborchoracle.

Now I can connect to Oracle 10g from datastage !

thanks for your support !

[ray.wurlod]

Well done!

That was so much better than us guessing at any of the myriad other things that might have been the cause.

[nk_bastia]

Hi dsex100,

Could you elaborate on this. how could you able to solve this problem...please post it step by step.

[samratisking]

I finally got this working.

My problem was with my environment variables setup.

In the windows control panel, I noticed that there were "user -defined" as well as system variables. By putting all the variables in the appropriate "user defined" or system sections I was able to get it to work with Oracle 10g.

The only thing that I didn't find in the documentation was that I had to run the install.liborchoracle script to install the orchestra oracle libraries.

In doing that I noticed that my ORACLE_HOME was already setup howver the APT_ORCHHOME variable was not, so I had to export that and run install.liborchoracle.

Now I can connect to Oracle 10g from datastage !

thanks for your support !

Hi dsex100,

Please post the step by step method to correct this error. I m facing this error too. Also, how did u run the install.liborchoracle file. I am able to see the file in the "install" folder of my DSHOME.. but not sure how to execute it. Are there any changes that you have done to this file. I am using Oracle 10g as my client. Thanks.