Error calling subroutine (Ascential DS Server Version 7.1)

ajayone · Post by **ajayone** » Sat Jul 08, 2006 10:54 pm

================================================
Error calling subroutine: DSD.Browse (Action=2); check DataStage is set up correctly in project ORA
Error calling subroutine: DSR_EXECJOB , DSR_RECORD, DSR_EXECJOB, DSR_JOB (Action=4); check DataStage is set up correctly in project ORA
(Internal Error (81021)
=================================================
Ascential DS Server Version 7.1 on WinXP: I receive this error message when I click on View Data option on Stage ODBC data define box after defining the Input/Output Data.

Please advice how to avoide this error.

Thanks,

DSguru2B · Post by **DSguru2B** » Sun Jul 09, 2006 12:38 am

Welcome aboard

What database are you accessing?
Are you getting this error message with only this job? Or is it in all the jobs in which you are using an odbc stage and trying to view the data ?
Look at this post and read Ray's comment on "Internal error (81021)"

ajayone · Post by **ajayone** » Sun Jul 09, 2006 9:47 am

DSguru2B wrote:Welcome aboard
What database are you accessing?
Are you getting this error message with only this job? Or is it in all the jobs in which you are using an odbc stage and trying to view the data ?
Look at this post and read Ray's comment on "Internal error (81021)"

Thank you for your response. I am only getting this error while using ODBC transforming into odbc or ODBC into sequential thru Oracle 9i tables. It works fine with sequential files or .txt files into sequential files or .txt files.

However some tables on ODBC (Oracle tables) works without error. I just couldn't figure this out why only certain table works and others don't.

Please advice.

Ajay

DSguru2B · Post by **DSguru2B** » Sun Jul 09, 2006 9:56 am

How many columns are you pulling in the odbc stage?
What if you access the table via OCI stage? do you still get that kind of problem?

ajayone · Post by **ajayone** » Sun Jul 09, 2006 7:59 pm

DSguru2B wrote:How many columns are you pulling in the odbc stage?
What if you access the table via OCI stage? do you still get that kind of problem?

Just less than 6 columns. One of my table has only two columns, which also fails. I also tried OCI stage as you suggested but still received following message:

"test..Oracle_OCI_9i_0: ORA-12154: TNS:could not resolve service name"

Any suggestions? Do you think reinstalling the data stage will solve this issue? Does anyone have any idea if this has anything to do with window's login system? I usually use "omit" during log in but also tried my window's password - both ways work OK to get into the stage but I receive same error.

Do you think some of my trigger or table constraints causing this problem? I am trying to build a sample warehouse using simple tables (job, employee, dept).

ArndW · Post by **ArndW** » Mon Jul 10, 2006 3:41 am

The TNS error is a common one when things are misconfigured. Since you are using ODBC on Windows the best, quickest and easiest way to check your system DSN is to use MSQUERY (a free program that comes with Microsoft Office or can be downloaded separately). If you can't connect from MSQUERY then the problem is with your Oracle connectivity or configuration and not within DataStage.

ajayone · Post by **ajayone** » Wed Jul 12, 2006 5:43 pm

ArndW wrote:The TNS error is a common one when things are misconfigured. Since you are using ODBC on Windows the best, quickest and easiest way to check your system DSN is to use MSQUERY (a free program that comes with Microsoft Office or can be downloaded separately). If you can't connect from MSQUERY then the problem is with your Oracle connectivity or configuration and not within DataStage.

I tried to connect thru MSQUERY but it gives me ODBC error. I think you are right about oracel configuration not working. I will try to reinstall Oracle and see if that will solve the problem. So far I have had no luck. I even tried re-installing the Office.

Krazykoolrohit · Post by **Krazykoolrohit** » Thu Jul 13, 2006 7:41 am

check your DSN. is the server name correct there.

DSguru2B · Post by **DSguru2B** » Thu Jul 13, 2006 7:47 am

Krazykoolrohit wrote:check your DSN. is the server name correct there.

The last test was tried outside datastage which proved that there is something wrong with the odbc connection. No server name, just the odbc configuration that needs to be fixed.

Krazykoolrohit · Post by **Krazykoolrohit** » Thu Jul 13, 2006 8:54 am

doesnt that ODBC connection contain a server name and port and all that stuff?

DSguru2B · Post by **DSguru2B** » Thu Jul 13, 2006 9:19 am

True. But its not DataStage .odbc.ini file. Its the system dsn itself as the OP is not able to get it working via MSQUERY

ajayone · Post by **ajayone** » Thu Jul 13, 2006 7:03 pm

DSguru2B wrote:True. But its not DataStage .odbc.ini file. Its the system dsn itself as the OP is not able to get it working via MSQUERY

Thanks so much for everyone's response. I am completely new on this field. How do I re-configure ODBC connection?

I reinstalled ORACLE 9i again and it seems to work fine for most of my tables but I still receive this error on few tables. Is there any tool to fix the ODBC connection in case of error?

ray.wurlod · Post by **ray.wurlod** » Thu Jul 13, 2006 9:06 pm

Fix your ODBC data source name definitions using the ODBC Administrator (odbcad32.exe).

ajayone · Post by **ajayone** » Sun Jul 16, 2006 8:30 am

ray.wurlod wrote:Fix your ODBC data source name definitions using the ODBC Administrator (odbcad32.exe). ...

I went in ODBC data source administrator and tested the connection. ODBC driver connection connects successfully to my data source ORA.
For example: I am trying to get maximum value from timestamp (TS) field to get target table updated. I ran user define sql (SELECT MAX(SCOTT.CTL_LOT.TS) FROM SCOTT.CTL_LOT
WHERE SCOTT.CTL_LOT.CTLID = 100;) on stage property. But when I click on View Data, it gives me the error ("Error calling subroutine: DSD.Browse (Action=3); check DataStage is set up correctly in project ORA"). But very similar sql will give the correct result on SQL Plus.

Is there anything else I can try?

One thing however, when I install Oracle 9i, installation wizard automatically created Ora81 (C:\Oracle\Ora81\inventory\ContentsXML) but I manually created Ora9i to install the rest of the system files. I had to do this because on my first installation ODBC administrator recognized my oracle version as Oracle8i for some reason. So I figured ODBC driver takes that naem from whatever folder name initial installation provides.

ktm5050 · Post by **ktm5050** » Mon Sep 04, 2006 9:14 am

I had similar problems with ODBC so I switched to Dynamic RDBMS stage. It worked fine after the switch. I encountered problem using ODBC on any table with timestamp but after I switched to Dynamic RDBMS stage, everything seem to work fine. I have read some articles about ODBC and it's connection to DB. It loses connection for no good reason.