Prev: db2 sequence to reset automatically
Next: Database backup cron job consumes large amount of memory
From: Henry J. on 29 Jun 2010 11:39
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!
From: Serge Rielau on 1 Jul 2010 09:16
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.

--
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab

From: Henry J. on 1 Jul 2010 13:59
On Jul 1, 9:16 am, Serge Rielau <srie...(a)ca.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.
>
> --
> Serge Rielau
> SQL Architect DB2 for LUW
> IBM Toronto Lab

From my tests, it seems WITH UR at the end of MERGE is indeed
supported and working as you described.

Thanks.
From: Serge Rielau on 3 Jul 2010 08:54
On 7/1/2010 1:59 PM, Henry J. wrote:
> On Jul 1, 9:16 am, Serge Rielau<srie...(a)ca.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

From: Henry J. on 3 Jul 2010 13:58
On Jul 3, 8:54 am, Serge Rielau <srie...(a)ca.ibm.com> wrote:
> On 7/1/2010 1:59 PM, Henry J. wrote:
>
> > On Jul 1, 9:16 am, Serge Rielau<srie...(a)ca.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

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.
 |  Next  |  Last
Pages: 1 2
Prev: db2 sequence to reset automatically
Next: Database backup cron job consumes large amount of memory