oracle stage properties (remote server)

ivannavi · Post by **ivannavi** » Sat Apr 21, 2007 1:25 am

When you were writing to the database were you using load method?
Did upsert work?

kirankota79 · Post by **kirankota79** » Sat Apr 21, 2007 11:30 pm

i have to create a new table in the target database, so i am using load and create method. i am not using upsert method.

ivannavi · Post by **ivannavi** » Sun Apr 22, 2007 12:34 am

So, have you tried the option I suggested to you in this thread?
viewtopic.php?t=109257

If it doesn't help, you will probably have to remove Oracle 9. I don't know how to completely remove it without clean reinstall of Windows because it leaves to much registry entries after uninstall. I hope just Uninstall from Oracle Installer will be good enough.

kirankota79 · Post by **kirankota79** » Sun Apr 22, 2007 8:06 pm

the problem has solved for me (not sure if it is a permanent solution). what i have done is, after changing the oracle home from 9i to 8i, i have added an environment variable for windows 2003 server with name TNS_ADMIN with value pointing to ORACLE_HOME/network/admin of 8i, and it has worked. i.e., datastage was able to find the tnsnames in 8i. but i also tried to include the 9i ORACLE_HOME/network/admin in the same TNS_ADMIN variable, again it is considering only oracle 9i, so i removed it and left it with 8i only. In addition to it i have set the APT_ORALCE_LOAD_OPTIONS variable in datastage to OPTIONS(DIRECT=FALSE, PARALLEL=TRUE) so it was able to write the target database. the same thing i have done using oracle 9i, but it was not working. but it is working with oracle 8i. That means using client 9i, it is not possible to write into Oracle 8i database and we need oracle 8i client to write an Oracle 8i database. I have done this only as a test case, but i dont know if i face any other issues in future , so i want to keep the post open for some days. once everything goes fine, i will close it.

ivannavi · Post by **ivannavi** » Mon Apr 23, 2007 9:16 am

Best that you can do is have only Oracle 8i. The sooner you do it the less trouble you get. Good luck.

csrazdan · Post by **csrazdan** » Mon Apr 23, 2007 1:23 pm

Check you registry and see if you ahve TNS_ADMIN pointing to 9i tnsnames.ora file. You will also have to check which DataStage Oracle shared object you are using. I don't know about Windows but in UNIX you can find it in $APT_ORCHHOME/lib directory. Following is an example from UNIX system, see if similar directory structure is availabel for windows.

$ cd $APT_ORCHHOME/lib
$ ls -ltr | grep orchora
-rwxr-xr-x 1 dsadm dstage 1754127 Aug 2 2006 orchoracle9i.o
-rwxr-xr-x 1 dsadm dstage 1753855 Aug 2 2006 orchoracle8i.o
-rwxr-xr-x 1 dsadm dstage 1754051 Aug 2 2006 orchoracle10g.o
lrwxrwxrwx 1 dsadm dstage 14 Sep 22 2006 orchoracle.o -> orchoracle9i.o
lrwxrwxrwx 1 dsadm dstage 22 Sep 22 2006 liborchoracleaix3.so -> liborchoracle9iaix3.so

In this example orchoracle.o is pointing to orchoracle9i.o shared object. If you want to use 8i shared object then orchoracle.o should be pointing to orchoracle8i.o.

Hope it helps...