Command line execute sqlplus and run your query that way? Seems awfully weird to invoke sqlldr just to run a query.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
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 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
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.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
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 ?
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.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle