Use of multiple oracle clients from datastage

[ratikmishra1]

I've oracle 10g client installed on the datastage server. The necessary oracle orch libraries are linked to it.

I 've some 9i databases for which I need to run jobs that does direct path load.

With 10g client I am unable to do this.

I installed the 9i client in a separate location.

Is there a way I can use the 9i client for some jobs to do direct path load for the 9i databases?

Thanks
Rati

[chulett]

Sure, simply override the Oracle specific environment variables in those jobs - things like ORACLE_HOME, LD_LIBRARY_PATH, etc. You could do that by adding them as user-defined variables in the project, where you could set their value to $ENV and then override their default values after bringing them into the 9i jobs.

[ratikmishra1]

Thanks Chutlet.

I've AIX running 8.1 Fx pack 1.

I tried setting LIBPATH and PATH to point to oracle 9i client.
Before running the direct path load I defined the following

export APT_ORACLE_LOAD_OPTIONS='OPTIONS(DIRECT=TRUE, PARALLEL=FALSE, SKIP_INDEX_MAINTENANCE=TRUE,SILENT=FEEDBACK)'

Added the following links to my local folder and kept them first in the path.
orchoracle.so -> /opt/IBM/InformationServer/Server/DSComponents/bin/orchoracle9i.o
orchoracle.o -> /opt/IBM/InformationServer/Server/DSComponents/bin/orchoracle9i.o

When I run, I still get the following error in the log file
SQL*Loader-925: Error while uldlpim: OCIStmtExecute
ORA-00904: "MESSAGE_NUM": invalid identifier

SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.

Table PARTNERDBA.ACXIOM_PROSPECT_B:
200035 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.


Funny thing is the data gets loaded but he index rebuild fails.

[chulett]

Completely separate issue. Usually means you've allowed duplicates in a unique index and that should be noted somewhere in the log file.

[ratikmishra1]

Why do you say that? In any case went for the work around. Got rid of the enterprise stage, developed a ksh script to do the load from dataset. It is working.

But, it should work the way you suggested. the problem is I guess AIX, the way it loads and caches the library is not helping in this case.

Thanks
Rati

[chulett]

I said that because of your "Funny thing is the data gets loaded but he index rebuild fails" comment. The only time I've seen that is with unique indexes. A direct path sqlldr session disables all constraints and then, if you've loaded records with duplicate keys (which it will now happily allow) the index rebuild will fail as it can no longer establish the unique constraint when it goes to do the 'rebuild'. Is your index in fact unique? Was the index in an UNUSABLE status afterwards?

[ratikmishra1]

Theere is no unique issues here as I am able to execute the build index statements successfully after the load is complete.

After talking to IBM they suggested that this could be because of oracle client incompatibility for direct path load.

And since orchoracle.o is already pre-linked to point to 10g client orchoracle10g.o and pre-loaded in memory AIX it is not loading it again even if it point to 9i client in my run-time environment as I am using the Id "orchoracle.o".


Thanks
Rati