Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
DB2 Merge statement

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
knn9413

New User


Joined: 23 Jul 2009
Posts: 17
Location: US

PostPosted: Sat Feb 27, 2010 3:23 am    Post subject: DB2 Merge statement
Reply with quote

I did see a couple of threads out there but did not get anywhere using the suggestions.. we are using db2 v9.1. I tried using the merge statement first using the VALUES and that worked fine.
The second format of the statement keeps giving me an error. I do not understand why

My statement was
MERGE INTO DB1.TABLE1 TAB1
USING
(
SELECT COL1, COL2
FROM DB1.TABLE2
WHERE COL1 = 3
) AS TAB2 (COL1, COL2)
ON TAB1.COL1 = TAB2.COL1 // Note that COL1 in both the tables are the primary keys and have the same values i.e. they will always match
WHEN MATCHED THEN
UPDATE SET TAB2.COL2 = TAB1.COL2
WHEN NOT MATCHED THEN
INSERT (COL1, COL2 )
VALUES (TAB2.COL1,TAB2.COL2)

I get the error
DSNT408I SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD SELECT. TOKEN VALUES
WAS EXPECTED
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 2 0 0 -1 224 506 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000002' X'00000000' X'00000000' X'FFFFFFFF'
X'000000E0' X'000001FA' SQL DIAGNOSTIC INFORMATION

Appreciate help in advance
Back to top
View user's profile Send private message

knn9413

New User


Joined: 23 Jul 2009
Posts: 17
Location: US

PostPosted: Sat Feb 27, 2010 3:24 am    Post subject:
Reply with quote

I should have asked: Is this format of merge only valid in db2-udb and not on the z/os
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Sat Feb 27, 2010 5:16 am    Post subject:
Reply with quote

Hello,

MERGE is valid in DB2 Z/os.

This may help - the MERGE info from the db2 v 9 manual:

Nowhere that i have a logon has v9 yet. . .

I don't know if MERGE will work if your system is still in compatability mode.
Back to top
View user's profile Send private message
knn9413

New User


Joined: 23 Jul 2009
Posts: 17
Location: US

PostPosted: Sat Feb 27, 2010 7:42 am    Post subject:
Reply with quote

dick scherrer wrote:
Hello,

MERGE is valid in DB2 Z/os.

This may help - the MERGE info from the db2 v 9 manual:

Nowhere that i have a logon has v9 yet. . .

I don't know if MERGE will work if your system is still in compatability mode.


I do understand it is valid. Like I mentioned, it works in the following format. The place I work has the db2 in the NFM

merge into tab1
using values (?,?) etc..

However , I only get the error when I use it the way that I mentioned it in the first post. That is the reason I wanted to know if this particular format is valid in z/os. The manual does seem to say it is valid...
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Sat Feb 27, 2010 8:04 am    Post subject:
Reply with quote

Hello,

My Bad - i didn't paste the link earlier:
http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/handheld/Connected/BOOKS/dsnsqk10/5.85

I suspect you have this manual available also. . .

I don't know if this "fits" your situation, but the MERGE sql i've seen uses a SELECT from a MERGE rather than an ebbedded SELECT within the MERGE.

Unfortunately, i don't have anywhere to run an experiment. . . icon_sad.gif
Back to top
View user's profile Send private message
knn9413

New User


Joined: 23 Jul 2009
Posts: 17
Location: US

PostPosted: Sat Feb 27, 2010 8:17 am    Post subject:
Reply with quote

Yes, I do have that.. Thanks for attempting to help though.. Appreciate it.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Tue Mar 02, 2010 5:48 pm    Post subject:
Reply with quote

according to the manual : subselect as source table is not implemented
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts WER268A OUTREC STATEMENT : SYNTAX E... frozenblood87 SYNCSORT 12 Sat Aug 26, 2017 9:45 pm
No new posts Suppress value reported by BREAK in D... Learncoholic DFSORT/ICETOOL 3 Wed Aug 16, 2017 6:03 pm
No new posts ON 2 AND EVERY 1 - Statement ??? UmeySan COBOL Programming 2 Tue Jul 25, 2017 1:20 pm
This topic is locked: you cannot edit posts or make replies. Merge two files and update Trailer re... Yashashri JCL & VSAM 1 Thu May 04, 2017 12:54 pm
No new posts To Merge mutliple records into a sing... anandgbe DFSORT/ICETOOL 6 Wed Feb 22, 2017 8:49 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us