DSXchange

My approach that I tried was,

Write Method : Upsert
Upsert Mode : Auto-generated Update & Insert
Insert SQL : <auto-generated Insert SQL>
Update SQL : <auto-generated Update SQL>

what value did you assign to $APT_ORACLE_LOAD_OPTIONS? Is that table partitioned? Are there any constraints/indexes on that table and how did you maintain the indexes/constraints during loading? How many records are to be loaded and how many records exist in that table? ============================...

The Load Method tried was Append...

Hi, We have a process were we would need to load data in range of a million into an Oracle table. We started using the Write Method as Load in the Oracle Enterprise stage as default, thinking that this should be the fastest as it uses Oracle loader concept. But, just by chance we gave a try with Wri...

Hi, Yeah, I get your point, but... If so see, there were previous replies in this same post that, the SQL in Update placeholder would get executed only if the SQL in Insert placeholder fails. As per this, in out case all incoming rows would get inserted through the Insert SQL and the Update SQL will...

The user-defined SQL is loading data from DS job only. It just uses a Oracle sequence in it to generate a sequence number. Hope we can use the ODBC stage only if we have an ODBC connection. But we do not have a ODBC connection to the Oracle database. But, I think we could try using DRS stage. DRS st...

Yes ArndW, we cannot use Load with Append, because, we have a user-defined SQL statement. We use a user-defined SQL to create a sequence number using Oracle sequence.

Since the Parameter data is very few, you can use a Server job to read the data from Table, and using transformer, UtilityRunJob can be used to call the job and pass the extracted value to the job as ... I would like to clarify on what you are saying. You mean to have a separate Server job to run t...

Ok, the SQL given in "Update SQL" will execute only in the SQL given in "Insert SQL" fails. So, what is the real difference on whether I give an Insert statement or a Update statement in the "Update SQL" place holder. In both cases, its not going to get executed as the ...

But, this doesn't sound correct.

If we have Insert statements at both Insert SQL and Update SQL, then the same row it going to get inserted twice, is it not?

Thanks ArndW.

So that means, even though I provide an Update SQL, update does not take place, because an Insert has taken place for that row.

Thanks for the information.

Hi, We are in process of migrating some of the jobs from Server to Parallel. We supply a user-defined SQL to do an insert in the server job's Oracle OCI stage. Now in a parallel job's, Oracle Enterprise stage, when we select the Write Method to be "Upsert", we are supposed to give 2 SQLs, ...

Hi kumar_s,

This looks to be a nice idea. But, how can the output of a SQL statement be passed to a parameter? Can you please give a brief note on how to do that.

Thank you.

No, that cannot be done. The column we fetch from the Reference table is an ID column. So, the value would be different / change when migrated to different enviornments. Also, we have to do a similar kind of ID column fetching from different reference tables in different jobs. So, if we hard code in...

Thanks for this solution Craig.

We were thinking if there is a way to do it without hard-coding any static key-value. Could there be a solution to use the lookup stage without the key