Run MERGE sql in Oracle Enterprise Stage

Havoc · Post by **Havoc** » Mon Mar 05, 2007 11:41 am

Hi,

I have a job design which looks like this :-

Row Generator Stage -> Oracle Enterprise Stage

The row generator stage is just a dummy stage to enable the Oracle Enterprise stage to run a complex merge sql query which updates a table.

When I run, the job I get this error :

OraADSTEST_TABLE,0: The call to sqlldr failed; the return code = 32512;

The Enterprise stage has a Write Method of Load and a Close Command sql which contains the complex merge sql query i was talking about.

Is there a better way (job-design) to to get this merge sql query to run ? Or am i doing something wrong here?

Thanks in advance

kcbland · Post by **kcbland** » Mon Mar 05, 2007 11:45 am

Command line execute sqlplus and run your query that way? Seems awfully weird to invoke sqlldr just to run a query.

Havoc · Post by **Havoc** » Mon Mar 05, 2007 12:19 pm

kcbland wrote:Command line execute sqlplus and run your query that way? Seems awfully weird to invoke sqlldr just to run a query.

Hmmm .. that wont work with the environment I've been setup in

Wont a merge sql query work if i set the write mode to upsert and give The query as a user generated one ? Its basically doing a self join and update on the same table ... When I try this , it inserts a NULL field on the column i'm trying to update ...

DSguru2B · Post by **DSguru2B** » Mon Mar 05, 2007 12:21 pm

It should work. Does your sql run as expected from sqlplus or your favourite sql tool?

Havoc · Post by **Havoc** » Mon Mar 05, 2007 12:30 pm

DSguru2B wrote:It should work. Does your sql run as expected from sqlplus or your favourite sql tool? ...

Yes it does ... Its running perfectly fine on my Oracle 9i install ...

The job design :

Row Generator -> Oracle Enterprise Stage (with Write method = Upsert and a User Generated SQL query)

The SQL query looks like :

merge into error a using ( select status, case rtrim(min(status) keep (dense_rank first order by errdt desc) over (partition by primary,tablenm)) when 'FAIL' then 'FAILURE' when 'RECVD' then 'MATCH' end new_status, rowid rid from error) b on ( a.rowid = b.rid ) when matched then update set status = new_status when not matched then insert values (null,null,null,null)

Now, when running this job... the status column gets updated to a NULL

kcbland · Post by **kcbland** » Mon Mar 05, 2007 12:33 pm

Havoc wrote: Hmmm .. that wont work with the environment I've been setup in

So, how does the DataStage Manager import metadata without a client connection? ODBC? I'm pretty sure you have sqlplus on your DataStage server (at least the node that has the DS Engine.) You need to check this as your method is not the best one to use.

You didn't set your Enterprise stage to use Upsert the first time, that's why it's running sqlldr. If you're using Upsert, that means it's doing an OCI connection, which means that your cluster is using sqlnet. If you're on a single node SMP, that means you should have Oracle client tools, and that means sqlplus.

DSguru2B · Post by **DSguru2B** » Mon Mar 05, 2007 12:40 pm

Ken has a very valid point. Get your command line sql working and run your query from command line as an after job subroutine 'ExecSh'

Havoc · Post by **Havoc** » Mon Mar 05, 2007 12:46 pm

kcbland wrote:
Havoc wrote: Hmmm .. that wont work with the environment I've been setup in
So, how does the DataStage Manager import metadata without a client connection? ODBC? I'm pretty sure you have sqlplus on your DataStage server (at least the node that has the DS Engine.) You need to check this as your method is not the best one to use.

You didn't set your Enterprise stage to use Upsert the first time, that's why it's running sqlldr. If you're using Upsert, that means it's doing an OCI connection, which means that your cluster is using sqlnet. If you're on a single node SMP, that means you should have Oracle client tools, and that means sqlplus.

Okay I just checked .. major goofup on my part .. connected to the Unix box .. but sqlplus , shell is not getting invoked with my userid . I know I need to add paths to my profile file. I dont know what to add though ...

can someone please share their experience on this ?

DSguru2B · Post by **DSguru2B** » Mon Mar 05, 2007 12:47 pm

Execute dsenv in $DSHOME. That should set up your ORACLE_HOME.

kcbland · Post by **kcbland** » Mon Mar 05, 2007 12:51 pm

Just remember shell commands are run under the userid of whoever is running the job, but the environment of the job is what is in the dsenv file in the DS Engine directory.

To get your userid working from a unix prompt, just look at the dsenv file and get the ORACLE_HOME set in your environment. Then you can run sqlpus, but you may have to put the Oracle binaries into your path. Again, the dsenv file has all of this. It may just be easiest to put a source of the dsenv file into your own .profile.