SEO

#1 (**permalink**) 02-27-2008, 12:43 PM

I want to trap errors (RI errors), if any, that might turn up during
merge in DB2. Is there some feature like the one in Oracle:

MERGE INTO...
WHEN MATCHED THEN UPDATE...
WHEN NOT MATCHED THEN INSERT...
LOG ERRORS INTO...

I mean is there some LOG ERRORS INTO or an equivalent clause in case
of DB2 merge stmt.

Thanks
amitabh

#2 (**permalink**) 02-27-2008, 12:43 PM

amitabh.mehra@gmail.com wrote:
> I want to trap errors (RI errors), if any, that might turn up during
> merge in DB2. Is there some feature like the one in Oracle:
>
> MERGE INTO...
> WHEN MATCHED THEN UPDATE...
> WHEN NOT MATCHED THEN INSERT...
> LOG ERRORS INTO...
>
>
> I mean is there some LOG ERRORS INTO or an equivalent clause in case
> of DB2 merge stmt.

I dont think there is one (and AFAIK it is not defined in the standard
either). Does Oracle have the same feature for insert and update stmts?

/Lennart

#3 (**permalink**) 02-27-2008, 12:43 PM

On Jun 6, 2:08 pm, Lennart <erik.lennart.jons...@gmail.com> wrote:
> amitabh.me...@gmail.com wrote:
> > I want to trap errors (RI errors), if any, that might turn up during
> > merge in DB2. Is there some feature like the one in Oracle:
>
> > MERGE INTO...
> > WHEN MATCHED THEN UPDATE...
> > WHEN NOT MATCHED THEN INSERT...
> > LOG ERRORS INTO...
>
> > I mean is there some LOG ERRORS INTO or an equivalent clause in case
> > of DB2 merge stmt.
>
> I dont think there is one (and AFAIK it is not defined in the standard
> either). Does Oracle have the same feature for insert and update stmts?
>
> /Lennart

In oracle the "log errors into.." clause was able to trap the RI
errors and move them to a table i specified. the syntax of that table
is oracle specific.

This is the scenario that i want to do:

I have arnd 10 merge stmts stored in a table. In my stored procedure,
i just fetch these stmts and execute them. Is there some mechanism by
which if there is a RI exception, then that particular merge stmt is
left and control continues with other stmts in the list?

Earlier i was thinking of trapping these errors in diff table (like in
oracle). Since this is not possible, i will be more than happy if the
above mentioned thing can be done.

#4 (**permalink**) 02-27-2008, 12:43 PM

amitabh.mehra@gmail.com wrote:
> I have around 10 merge stmts stored in a table. In my stored procedure,
> i just fetch these stmts and execute them. Is there some mechanism by
> which if there is a RI exception, then that particular merge stmt is
> left and control continues with other stmts in the list?
Of course. All you need is to catch the RI error (SQLSTATE 23503).

Are all these MERGE statements on different tables? Ideally you would
simply sort them properly.
Or you test the RI condition with the MERGE.
DB2 allows the addition of a predicate to the WHEN [NOT] MATCHED clause.
Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

#5 (**permalink**) 02-27-2008, 12:43 PM

On Jun 6, 4:51 pm, Serge Rielau <srie...@ca.ibm.com> wrote:
> amitabh.me...@gmail.com wrote:
> > I have around 10 merge stmts stored in a table. In my stored procedure,
> > i just fetch these stmts and execute them. Is there some mechanism by
> > which if there is a RI exception, then that particular merge stmt is
> > left and control continues with other stmts in the list?
>
> Of course. All you need is to catch the RI error (SQLSTATE 23503).
>
> Are all these MERGE statements on different tables? Ideally you would
> simply sort them properly.
> Or you test the RI condition with the MERGE.
> DB2 allows the addition of a predicate to the WHEN [NOT] MATCHED clause.
> Cheers
> Serge
>
> --
> Serge Rielau
> DB2 Solutions Development
> IBM Toronto Lab

Serge

My mistake, i wrote the wrong scenerio... instead of many merge stmt,
there is just one merge stmt.. but many records in a table, out of
which few have RI errors. I want to put all good ones into another
table using this merge stmt. The problem is that when exception occurs
(RI) while merging, none of the record (even good ones) are
transferred. Is there some way that the good records get transferred
inspite of the RI exception gettting thrown?

The for loop I was using is like:

FOR insertSQL AS
(SELECT SQLSTRING, TABLE_NAME FROM my_table)
DO
SET sqlRun = insertSQL.SQLSTRING;
EXECUTE IMMEDIATE sqlRun;
-- COMMIT;

END FOR;
COMMIT;

#6 (**permalink**) 02-27-2008, 12:43 PM

amitabh.mehra@gmail.com wrote:
[...]
> My mistake, i wrote the wrong scenerio... instead of many merge stmt,
> there is just one merge stmt.. but many records in a table, out of
> which few have RI errors. I want to put all good ones into another
> table using this merge stmt. The problem is that when exception occurs
> (RI) while merging, none of the record (even good ones) are
> transferred. Is there some way that the good records get transferred
> inspite of the RI exception gettting thrown?
>

I usually handle situations like that by adding predicates in the using
clause that correspond to the constraints that might fail. I.e assuming
a table like

create table T (
t int not null primary key,
u int not null
)

alter table T add constaint C
check (u between 5 and 9)

alter table T add constaint FK
foreign key (u)
references T2 (u)

would mean

merge into T
using ( select * from T3
where u between 5 and 9
and exists (
select 1 from T2
where T3.u = T2.u
) etc
)
when matched ...

Then make a report of the bad ones

select * from T3
where NOT (u between 5 and 9
and exists (
select 1 from T2
where T3.u = T2.u
))

Just a thought

/Lennart

#7 (**permalink**) 02-27-2008, 12:43 PM

That would be my advise as well.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Merge statement in db2