 | |
use "with ur" in a merge statement?
comp.databases.ibm-db2 comp.databases.ibm-db2
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
use "with ur" in a merge statement? -
06-29-2010
, 10:39 AM
#2
| |||
| |||
|
|
What would happen if "with ur" is appended at the end of a merge statement? Would it have the same effect as in a "insert into ... select from ..." statement, i.e., no lock is acquired on the tables in the "using" clause? Thanks! You would have to append it to the select in the USING clause. |
not checked that).
If it were supported then everything except for the select over teh
MERGe target would be UR (including any subqueries in the SET or VALUES
and MATCHED clauses.
--
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab
#3
| |||
| |||
|
|
On 6/29/2010 11:39 AM, Henry J. wrote:> What would happen if "with ur" isappended at the end of a merge statement? * Would it have the same effect as in a "insert into ... select from ..." statement, i.e., no lock is acquired on the tables in the "using" clause? Thanks! You would have to append it to the select in the USING clause. I do not think we support WITH UR at teh end of MERGE (although I have not checked that). If it were supported then everything except for the select over teh MERGe target would be UR (including any subqueries in the SET or VALUES and MATCHED clauses. -- Serge Rielau SQL Architect DB2 for LUW IBM Toronto Lab |
supported and working as you described.
Thanks.
#4
| |||
| |||
|
|
On Jul 1, 9:16 am, Serge Rielau<srie... (AT) ca (DOT) ibm.com> wrote: On 6/29/2010 11:39 AM, Henry J. wrote:> What would happen if "with ur" is appended at the end of a merge statement? Would it have the same effect as in a "insert into ... select from ..." statement, i.e., no lock is acquired on the tables in the "using" clause? Thanks! You would have to append it to the select in the USING clause. I do not think we support WITH UR at teh end of MERGE (although I have not checked that). If it were supported then everything except for the select over teh MERGe target would be UR (including any subqueries in the SET or VALUES and MATCHED clauses. From my tests, it seems WITH UR at the end of MERGE is indeed supported and working as you described. *phew* :-) |
--
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab
#5
| |||
| |||
|
|
On 7/1/2010 1:59 PM, Henry J. wrote: On Jul 1, 9:16 am, Serge Rielau<srie... (AT) ca (DOT) ibm.com> *wrote: On 6/29/2010 11:39 AM, Henry J. wrote:> *What would happen if "withur" is appended at the end of a merge statement? * Would it have the same effect as in a "insert into ... select from ..." statement, i.e., no lock is acquired on the tables in the "using" clause? Thanks! You would have to append it to the select in the USING clause. I do not think we support WITHURat teh end of MERGE (although I have not checked that). If it were supported then everything except for the select over teh MERGe target would beUR(including any subqueries in the SET or VALUES and MATCHED clauses. *From my tests, it seems WITHURat the end of MERGE is indeed supported and working as you described. *phew* :-) -- Serge Rielau SQL Architect DB2 for LUW IBM Toronto Lab |
merge statement? It's what happened when I added "WITH UR" to a merge
statement. Hard to tell if the slowness was caused by other factors,
as this was the only change... Thanks.
#6
| |||
| |||
|
|
Serge, could it be possible that adding "WITH UR" would slow down a merge statement? It's what happened when I added "WITH UR" to a merge statement. Hard to tell if the slowness was caused by other factors, as this was the only change... Thanks. Hmmm,... |
A WITH UR on a subquery I could fathom as restricting what the optimizer
can do, but a "global" WITH UR should have the same effect as setting
the isolation level for the session.
--
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.