SEO

#1 (**permalink**) 02-27-2008, 01:03 PM

I'm hoping to use SELECT FROM MERGE in one of my stored procedures,
but have not had any luck running my query. The sample query I'm using
is

SELECT TAPE_ID
FROM FINAL TABLE
(
MERGE INTO MERGETEST AS mt
USING
(
SELECT '3633' as TAPE_ID FROM sysibm.sysdummy1
) AS indata
ON mt.TAPE_ID = indata.TAPE_ID
WHEN NOT MATCHED THEN
INSERT ( TAPE_ID ) VALUES( '3633' )
ELSE IGNORE
);

We are using DB2 v9.1.0.356 and I have been reading about SELECT from
MERGE in a few articles. An older post here (http://groups.google.com/
group/comp.databases.ibm-db2/browse_thread/thread/
108e3a0eb3cb79d4/5da632c29830dde0?lnk=gst&q=select+from+merge&rnum= 3#)
suggests that is not possible, but the post is from Sep. 2006 and I
wonder if things have changed since. The article that mentions SELECT
FROM MERGE is http://www.db2portal.com/2007_04_01_archive.html and I
thought I saw the same in DB2 documentation as well.

Has anybody tried and succeeded with this statement?

Thanks,
Shi

#2 (**permalink**) 02-27-2008, 01:03 PM

Shiny wrote:
> I'm hoping to use SELECT FROM MERGE in one of my stored procedures,
> but have not had any luck running my query. The sample query I'm using
> is
>
> SELECT TAPE_ID
> FROM FINAL TABLE
> (
> MERGE INTO MERGETEST AS mt
> USING
> (
> SELECT '3633' as TAPE_ID FROM sysibm.sysdummy1
> ) AS indata
> ON mt.TAPE_ID = indata.TAPE_ID
> WHEN NOT MATCHED THEN
> INSERT ( TAPE_ID ) VALUES( '3633' )
> ELSE IGNORE
> );
>
>
> We are using DB2 v9.1.0.356 and I have been reading about SELECT from
> MERGE in a few articles. An older post here (http://groups.google.com/
> group/comp.databases.ibm-db2/browse_thread/thread/
> 108e3a0eb3cb79d4/5da632c29830dde0?lnk=gst&q=select+from+merge&rnum= 3#)
> suggests that is not possible, but the post is from Sep. 2006 and I
> wonder if things have changed since. The article that mentions SELECT
> FROM MERGE is http://www.db2portal.com/2007_04_01_archive.html and I
> thought I saw the same in DB2 documentation as well.
>
> Has anybody tried and succeeded with this statement?
SELECT FROM MERGE is supported only on DB2 9 for zOS
(and I'm green with envy!)
Now MERGE in DB2 9 for zOS however only allows the VALUES clause as a
source table.
Basically the SELECT FROM MERGE is geared towards NOT ATOMIC batch merges.

Replace:
> (SELECT '3633' as TAPE_ID FROM sysibm.sysdummy1)
with
(VALUES '3633') AS S(TAPE_ID)

That should do it on zOS.

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

SELECT FROM MERGE DB2 v9.1.0.356