Importing Oracle Meta

als110 · Post by **als110** » Wed Aug 17, 2005 12:48 pm

I have successfully imported sql server meta data into ps, however when I try to do the import on an Oracle DB it times out with the following messages:

MetaDate Loader Done.
Problem Number 88: The server program was terminated unxepectedly.
Please Call Technical Support.

Any Suggestions??

ray.wurlod · Post by **ray.wurlod** » Wed Aug 17, 2005 4:24 pm

Is the Oracle instance running? For example can you connect to it using sqlplus from the ProfileStage machine? Indeed, is the Oracle client software installed and configured on that machine?

tcj · Post by **tcj** » Thu Aug 18, 2005 1:27 am

I get the same error trying to do a table analysis on a oracle table that has 800K rows.

Support has said that the error 88 occurs due to a message timeout. Was asked to changed a setting in the MGMPgmInit.int file to 300.

Didn't fixed the problem and still working on a solution for a fix.

Table analysis is fine on the other tables.

Tim

als110 · Post by **als110** » Thu Aug 18, 2005 7:29 am

Yes oracle is running on this computer and I am able to connect to the instatance through other sql tools.

ray.wurlod · Post by **ray.wurlod** » Fri Aug 19, 2005 2:55 am

Is Oracle busy doing other stuff? The DBA can help you to track down what's being asked of Oracle at any particular time, and maybe why timeouts are occurring.

als110 · Post by **als110** » Mon Sep 19, 2005 1:16 pm

Does anyone have any other ideas on this, I can import the meta data if I use an analysis server that is installed on my local windows machine but I can not import it using the analysis server on my unix box. I now am getting the following error:

Unable to connect to database using DSN/service 'AHQ2' Review connection information and correct if needed. Connection Error: CoreOdbc::_ExecOpenDbSql_ODBC: Error in opening database connection to 'AHQ2'

Here is my odbc entry for this db:

[AHQ2]
Driver=/etl/Ascential/DataStage/branded_odbc/lib/VMora19.so
Description=DataDirect 4.20 Oracle Wire Protocol
ApplicationUsingThreads=1
ArraySize=60000
CachedCursorLimit=32
CachedDescLimit=0
CatalogIncludesSynonyms=1
CatalogOptions=1
DefaultLongDataBuffLen=1024
DescribeAtPrepare=0
EnableDescribeParam=0
EnableNcharSupport=1
EnableScrollableCursors=1
EnableStaticCursorsForLongData=0
EnableTimestampWithTimeZone=0
HostName=mke1a1qrac01
ServerName=ahq2a.world
LocalTimeZoneOffset=
LockTimeOut=-1
LogonID=
Password=
PortNumber=1521

This db is up and running and I am able to open up a sql client on this DB.
Thanks

Aaron

jefbuckeye · Post by **jefbuckeye** » Mon Sep 19, 2005 9:17 pm

als110 wrote:Does anyone have any other ideas on this, I can import the meta data if I use an analysis server that is installed on my local windows machine but I can not import it using the analysis server on my unix box. I now am getting the following error:

Unable to connect to database using DSN/service 'AHQ2' Review connection information and correct if needed. Connection Error: CoreOdbc::_ExecOpenDbSql_ODBC: Error in opening database connection to 'AHQ2'

Here is my odbc entry for this db:

[AHQ2]
Driver=/etl/Ascential/DataStage/branded_odbc/lib/VMora19.so
Description=DataDirect 4.20 Oracle Wire Protocol
ApplicationUsingThreads=1
ArraySize=60000
CachedCursorLimit=32
CachedDescLimit=0
CatalogIncludesSynonyms=1
CatalogOptions=1
DefaultLongDataBuffLen=1024
DescribeAtPrepare=0
EnableDescribeParam=0
EnableNcharSupport=1
EnableScrollableCursors=1
EnableStaticCursorsForLongData=0
EnableTimestampWithTimeZone=0
HostName=mke1a1qrac01
ServerName=ahq2a.world
LocalTimeZoneOffset=
LockTimeOut=-1
LogonID=
Password=
PortNumber=1521

This db is up and running and I am able to open up a sql client on this DB.
Thanks

Aaron

You may want to change the server name to the IP address that hosts the Oracle Database. I have seen this sort of error with other products when the host machine was using WINS instead of DNS which often makes it invisible to the UNIX world.

als110 · Post by **als110** » Tue Sep 20, 2005 8:08 am

I changed to the ip address and I get the exact same error and in fact it just errors out faster with the ip than the box name.

als110 · Post by **als110** » Wed Sep 21, 2005 9:59 am

Ok so as it turns out I was never connecting to the db at all, I made a few changes to the odbc instance and I can now connect to the db but I am still getting no where, I can't even select tables here is the message I get now:

Error Retreiving Table List.
Error maybe database or schema in this connection does not have contain and tables. Please see the latest error log

As I have stated before I have no probelm connecting to this db if the analysis server is on my local windows machine but when I try to connection through my unix box i get this error. I have successfully connected to the db on the unix box through sqlplus so I know the db is up and running.

Bala R · Post by **Bala R** » Wed Sep 21, 2005 11:22 am

Can you check whether the user id with which you are logging into unix from the DataStage client has read permission on tnsnames.ora file?

als110 · Post by **als110** » Thu Sep 22, 2005 8:10 am

Yes I do Have Read Permission on the tnsnames.ora file