Only inserts through Oracle Enterprise stage
Moderators: chulett, rschirm, roy
Only inserts through Oracle Enterprise stage
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.
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.
declare the order to be "insert then update" and if you only have inserts to do then the update clause will not get executed.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 45
- Joined: Thu Aug 31, 2006 3:13 am
- Location: Bangalore
-
- Participant
- Posts: 1
- Joined: Tue Mar 27, 2007 9:23 am
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.
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.
Sometimes that cannot be done due to other constraints.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
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".
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".
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
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.
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.
Only insert
Hi,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.
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).
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.
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.