Only inserts through Oracle Enterprise stage

vnspn · Post by **vnspn** » Mon Mar 24, 2008 9:37 am

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, a INSERT and another UPDATE SQL.

In our job, we just need to do inserts of a few records. We do not need to do updates. In that case, how should the SQLs be defined.

1) Does update also takes place by default for each record inserted? Then, that might slow down the entire process by taking extra time for that update statement to be executed, right?

2) Is there a way to provide only the insert statement without the update?

Thank you.

ArndW · Post by **ArndW** » Mon Mar 24, 2008 9:44 am

declare the order to be "insert then update" and if you only have inserts to do then the update clause will not get executed.

vnspn · Post by **vnspn** » Mon Mar 24, 2008 10:20 am

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.

Sudhindra_ps · Post by **Sudhindra_ps** » Mon Mar 24, 2008 12:04 pm

hi,

You can write insert statements for both insert query placeholder as well as for update query place holder. This ensures you are not updating anything out there. As both SQL statements are of insert DML statements.

Thanks & regards
Sudhindra P S

vnspn · Post by **vnspn** » Mon Mar 24, 2008 1:11 pm

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?

philip_shajee · Post by **philip_shajee** » Mon Mar 24, 2008 2:35 pm

vnspn wrote: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?

The update SQL will get executed only if the Insert SQL fails.

vnspn · Post by **vnspn** » Mon Mar 24, 2008 3:48 pm

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 SQL statement in "Insert SQL" will be the one executed in our case.

kumar_s · Post by **kumar_s** » Mon Mar 24, 2008 4:43 pm

If there isn't any update why do you need to use Upsert. Why can't you use the Write mode as Load/ Append?

ArndW · Post by **ArndW** » Tue Mar 25, 2008 2:17 am

Sometimes that cannot be done due to other constraints.

Madhav_M · Post by **Madhav_M** » Tue Mar 25, 2008 3:55 am

Hi
you can just use update sql with explicit condition that fails update statement.. like
where
.
.
.
AND 1=0

this should solve your problem

vnspn · Post by **vnspn** » Tue Mar 25, 2008 9:06 am

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.

kumar_s · Post by **kumar_s** » Tue Mar 25, 2008 9:16 pm

What type of user defined SQL you have??
Is it selecting from a table and loading or getting input from DS job?
If now you can use ODBC stage. Where you have "Insert only" Option.
If you need to use OCI for sure, write another Update statement, where you have a wrong where clauss, which makes the statement dummy. Like "Where 1=2".

vnspn · Post by **vnspn** » Wed Mar 26, 2008 1:42 pm

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 stage has the option to do insert only. Do you think the DRS stage would give the same performance as Oracle Enterprise stage.

girija · Post by **girija** » Wed Mar 26, 2008 1:59 pm

vnspn wrote: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 stage has the option to do insert only. Do you think the DRS stage would give the same performance as Oracle Enterprise stage.

Hi,

You can follow these steps :

1. chose auto generated upsdate and insert.
2. Then chose user defined update and insert.
3. Add addtinal colum in insert statement and use oracle sequenc in values.
4. Check order of execution as : Insert then update
5. Chage the where condn. in update as where 0=1
7. Make sure there is no constraint defined in your target table( to ensure update never happen).

vnspn · Post by **vnspn** » Thu Mar 27, 2008 9:29 am

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 not get executed for any row. Then, why should we explicitly give the where condition in the Update SQL as 0=1 , when the Update SQL is not at all going to get executed.

Thanks.