oracle stage properties (remote server)

kirankota79 · Post by **kirankota79** » Thu Apr 19, 2007 3:13 pm

i have 2 oracle clients (8.1.7 and 9.2.0) on datastage server to connect a remote oracle 8i server. i have installed oracle 9i client and then i have installed oracle 8i client each having diffrerent oracle homes. Since it is pointed to oracle 9i home (since it is the first installed), i have changed it to oracle 8i home with home selector and my ODBC connection is working with oracle 8i also. i am also able to connect through the SQLPLUS to remote database with 8i. The problem is, when i use the ORACLE ENTERPRISE STAGE, in the properties - REMOTE SERVER i have given the TNS service name i have created in oracle 8i home, but it is not working. but when i change it to the TNS service name that i have in oracle 9i home it is still working. How come it is possible? i have changed the oracle home from 9i to 8i. i want to make use of the TNS service name in oracle 8i instead of oracle 9i. Can anybody help me?

thanks in advance.

ray.wurlod · Post by **ray.wurlod** » Thu Apr 19, 2007 3:26 pm

Make it a job parameter and check (in the job log) that the parameter is passed correctly. Actually, you could check more quickly than that using View Data if you are not using direct read.

kirankota79 · Post by **kirankota79** » Thu Apr 19, 2007 5:51 pm

when i try to view data it is giving as tns name error. but it is not the case....because i was able to connect sqlplus with same tns name.
please give me the possible solution.

chulett · Post by **chulett** » Thu Apr 19, 2007 6:00 pm

Not familiar with Windows Servers, but did you stop and restart DataStage after switching Oracle homes?

kirankota79 · Post by **kirankota79** » Thu Apr 19, 2007 6:04 pm

i have closed the designer and reopened.....should i reboot the windows server after changing the oracle home?

chulett · Post by **chulett** » Thu Apr 19, 2007 6:36 pm

No, not just Designer - I mean actually shutdown and restart DataStage itself. If you were on a UNIX server, I could walk you through it, but Windows? The service? Services? [shrug]

Worst case, yes - ensure no-one else is connected and then reboot the whole dang box.

kirankota79 · Post by **kirankota79** » Thu Apr 19, 2007 6:40 pm

craig....i just want to know how datastage looks for the TNS names. For example i have 2 TNSService names in oracle 9i home and 2 TNSService names in oracle 8i home. I am giving the remote server name as a TNSService name from oracle 8i home...but i feel it is still looking in oracle 9i home. and giving the error:

connect failed for env: vImpactPROD, user: networks, password: *****
Oracle error: -12154
ORA-12154: TNS:could not resolve service name

When i give the TNS name in sqlplus it is working without anyproblem and i dont have anyproblems with ODBC stage. Iam getting problems with Oracle Enterprise Stage. I hope my explaination makes sense.

chulett · Post by **chulett** » Thu Apr 19, 2007 8:16 pm

I understand your issue. Never having had the pleasure of working on a Windows-based DataStage server, I've stated I don't know the gory details on how it all works. My understanding from hanging out here is that registry entries and environment variables control all there.

Which home does your ORACLE_HOME environment variable point to? If you're sure you've switched to the 9i home and it isn't working, did you try restarting DataStage? If you can't do it, ask your DataStage Administrator to do it. Someone there must know how to accomplish that task.

kirankota79 · Post by **kirankota79** » Fri Apr 20, 2007 8:40 am

craig, there is nobody to help me. we dont have support from IBM? There is no datastage administrator.

chulett · Post by **chulett** » Fri Apr 20, 2007 11:16 am

That's... unfortunate. Unless someone more familiar with Windows Servers can step in here with specific instructions on how to 'shutdown' and 'restart' DataStage on that platform, I'm going to stick with my original suggestion: you go brute force and just shutdown/restart the whole dang box.

kirankota79 · Post by **kirankota79** » Fri Apr 20, 2007 12:49 pm

i have restarted the server ....but still it has the same issue.

chulett · Post by **chulett** » Fri Apr 20, 2007 12:57 pm

Then someone else here needs to step up and help you. Or your company needs to arrange for some 'official' Support for you. Sorry.

ray.wurlod · Post by **ray.wurlod** » Fri Apr 20, 2007 4:21 pm

Find out from whom your company purchased DataStage. Most (all?) resellers have a contractual obligation to provide first level support.

ivannavi · Post by **ivannavi** » Fri Apr 20, 2007 5:02 pm

What about changing all back to 9i when Datatstage "thought" everything was 9i? What thing didn't work then?
There are always some issues with multiple Oracle homes and third applications picking which one to use.

kirankota79 · Post by **kirankota79** » Fri Apr 20, 2007 7:25 pm

the source and target databases are oracle 8i and i have oracle 9i client on datastage server. I read in dsxchange, it is not advisable to use oracle 9i client to connect oracle 8i database in datastage perspective( to use oracle enterprise stage). I was able to read from source but when trying to write in target database with oracle 9i client (using 9i TNSService name in remote server of oracle stage) iam getting SIGSEGV error. so i installed the oracle 8i client (8.1.7) with another oracle home. and iam trying to use 8i home TNSService name to read and write to a target database. but the datastage is always looking the 9i home and not able to find the TNS service name of 8i that i gave in oracle stage.