Posted: Tue Nov 03, 2009 12:16 pm Post subject: DB2 Merge version 9
I have been looking at documentation for version 9 and found it a little lacking. I am wondering if any of you who are on DB2 version 9 have tried the following.
The syntax for the merge statement says that you can also return results from the merge by using a SELECT statement. I have been able to prove this using SPUFI.
I am attempting to do a merge using multiple rows. I am reading rows from a sequential file and loading arrays which are referenced in the merge using a COBOL program.
My question is how to return multiple rows from the merge? Do you build a cursor which contains the SELECT.....MERGE and then open the cursor and do a multi-row fetch? Has anyone attempted this?
Joined: 08 Oct 2004 Posts: 274 Topics: 52 Location: California
Posted: Tue Nov 03, 2009 12:30 pm Post subject:
jim haire,
I have been testing the 'MERGE' statement and I can't find any reference in the DB2 V9.1 for z/OS documentation for a Select. Am I missing something? _________________ Thanks,
NASCAR9
Yes, that documentation is out there. I am also testing this. We have a "sandbox" area where we're testing the new features from DB2 version 9. I can give you a little background of what I have set up.
I created a table called "MASTER" which contains all the people who have purchased items from our company. I created another table called TXN which contains all the transactions which have come in for the day.
I created the query below as an example of the merge and ran it in SPUFI. If the account number 1010101013 already exists in the database, the amount (101010.10) gets added to whatever amount exists for the record currently. If the account number does not exist, the record is inserted.
The column ACTION is returned as "UPD" if an update occurred. It returns an "INS" if the record didn't exist. Hope this helps explain how the SELECT works.
Code:
SELECT ACCT, FIRST_NAME, LAST_NAME, TOTAL_TXN, ACTION FROM FINAL TABLE
(MERGE INTO MASTER AS M INCLUDE(ACTION CHAR(3))
USING (VALUES ('1010101013', 'JOANNE', 'TENPINS',101010.10))
AS TXN (ACCT, FIRST_NAME, LAST_NAME, TXN_AMT)
ON M.ACCT = TXN.ACCT
WHEN MATCHED THEN
UPDATE SET TOTAL_TXN = TOTAL_TXN + TXN.TXN_AMT
,ACTION = 'UPD'
WHEN NOT MATCHED THEN
INSERT (ACCT, FIRST_NAME, LAST_NAME, TOTAL_TXN, ACTION)
VALUES (TXN.ACCT, TXN.FIRST_NAME, TXN.LAST_NAME, TXN.TXN_AMT, 'INS')
NOT ATOMIC CONTINUE ON SQLEXCEPTION);
Glad I could help. But my question hasn't been answered yet.
To go a little further in explaining what I'm trying to do, let me put my SQL from my COBOL program below.
:ACCT-TAB, :FIRST-NAME-TAB, :LAST-NAME-TAB, and :TXN-AMOUNT-TAB are all arrays I have defined in my program. They each occur 1000 times. I read records from a sequential file and load values from the file into each array. I add 1 to :WS-ROW-COUNT for each record I read.
The documentation I've found said I should be able to do this and merge 1000 records at a time. However, I haven't found any documentation or examples which explain how I can return the results of these 1000 merges through the SELECT. It seems to me like this might be a cursor/fetch scenario. Just not sure...
Code:
SELECT ACCT
,FIRST_NAME
,LAST_NAME
,TOTAL_TXN
,ACTION
FROM FINAL TABLE
(MERGE INTO MASTER AS M INCLUDE(ACTION CHAR(3))
USING (VALUES (:ACCT-TAB,:FIRST-NAME-TAB,
:LAST-NAME-TAB,:TXN-AMOUNT-TAB)
FOR :WS-ROW-COUNT ROWS)
AS TXN (ACCT, FIRST_NAME, LAST_NAME, TXN_AMT)
ON M.ACCT = TXN.ACCT
WHEN MATCHED THEN
UPDATE SET TOTAL_TXN = TOTAL_TXN + TXN.TXN_AMT
,ACTION = 'UPD'
WHEN NOT MATCHED THEN
INSERT (ACCT, FIRST_NAME, LAST_NAME, TOTAL_TXN, ACTION)
VALUES (TXN.ACCT
,TXN.FIRST_NAME
,TXN.LAST_NAME
,TXN.TXN_AMT
,'INS')
NOT ATOMIC CONTINUE ON SQLEXCEPTION)
Joined: 26 Nov 2002 Posts: 12370 Topics: 75 Location: San Jose
Posted: Wed Nov 04, 2009 1:48 pm Post subject:
jim haire,
I can't test , it but how about this?
Code:
SELECT Z.*
FROM (MERGE INTO MASTER AS M INCLUDE(ACTION CHAR(3))
USING (VALUES (:ACCT-TAB,:FIRST-NAME-TAB,
:LAST-NAME-TAB,:TXN-AMOUNT-TAB)
FOR :WS-ROW-COUNT ROWS)
AS TXN (ACCT, FIRST_NAME, LAST_NAME, TXN_AMT)
ON M.ACCT = TXN.ACCT
WHEN MATCHED THEN
UPDATE SET TOTAL_TXN = TOTAL_TXN + TXN.TXN_AMT
,ACTION = 'UPD'
WHEN NOT MATCHED THEN
INSERT (ACCT, FIRST_NAME, LAST_NAME, TOTAL_TXN, ACTION)
VALUES (TXN.ACCT
,TXN.FIRST_NAME
,TXN.LAST_NAME
,TXN.TXN_AMT
,'INS')
NOT ATOMIC CONTINUE ON SQLEXCEPTION) Z
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
