Merge statement in db2
amitab...@gmail.com
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
Lennart
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
amitab...@gmail.com
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.
Serge Rielau
> 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?
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
amitab...@gmail.com
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;
Lennart
[...]
> 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