Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

SQL Merge error -254

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

New User


Joined: 14 Dec 2015
Posts: 8
Location: United States

PostPosted: Wed Jun 01, 2016 8:28 pm    Post subject: SQL Merge error -254
Reply with quote

Hello, I am attempting to use a MERGE statement on a "clone" table.
I am getting a -254 -
Code:
A non-atomic merge statement attempted to process multiple rows of data, but errors occurred.

What I can't find is how to find our what my error(s) were so that I can fix the issue. This program will run "daily" and I have processed 3 days of data with no issues, it is my 4th day of data that is causing my problems. I was originally doing 1000 rows at a time. I have dropped it to 10 and I am abending on the 1st time of attempting the merge. Here is my displays upon doing a GET DIAGNOSTIC:
Code:
DB2 LAST ROW 000000000
DB2 NUMBER RESULT SETS 000000000
DB2 NUMBER ROWS 0000000000000000000000000000000
DB2 RETURN STATUS 000000000
MORE N
NUMBER 000000011
ROW COUNT 0000000000000000000000000000000
CONDITION NUMBER 000000001
DB2 ERROR CODE1 00000040}
DB2 ERROR CODE2 000000000
DB2 ERROR CODE3 000000000
DB2 ERROR CODE4 00000000J
DB2 LINE NUMBER 000000000
DB2 MESSAGE ID DSN00254E 
DB2 ERROR MODULE DSNXRMRG
DB2 REASON CODE 000000000
DB2 RETURNED SQLCODE 00000025M
DB2 ROW NUMBER 0000000000000000000000000000000
DB2 SQLERRD1 00000040}
DB2 SQLERRD2 000000000
DB2 SQLERRD3 000000000
DB2 SQLERRD4 00000000J
DB2 SQLERRD5 000000000
DB2 SQLERRD6 000000000
RETURN SQLSTATE 22530
MSG TEXT A NON-ATOMIC MERGE STATEMENT ATTEMPTED TO PROCESS MULTIPLE ROWS OF DATA, BUT ERRORS OCCURRED :?
Back to top
View user's profile Send private message

Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1738
Location: Bloomington, IL

PostPosted: Wed Jun 01, 2016 9:40 pm    Post subject:
Reply with quote

Please post your MERGE statement, using the Code tags to preserve alignment.
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Wed Jun 01, 2016 9:48 pm    Post subject:
Reply with quote

Shouldn't this clue you where is the problem or what row of 4th day is bad?
Code:
DB2 SQLERRD3 000000000
Back to top
View user's profile Send private message
Robin Sulsona

New User


Joined: 14 Dec 2015
Posts: 8
Location: United States

PostPosted: Wed Jun 01, 2016 11:19 pm    Post subject: SQLERRD3 meaning...
Reply with quote

It wasn't really a bad row in the input in core table. It was the fact that the clone table was in pending copy status. A load with NOCOPYPEND seems to have done the trick. I will be trying it for the next days data later today.
But how does SQLERRD3 with a value of 0 tell me what is wrong with the data?
For the "GET DIAGNOSTICS" command this is what that means:
DB2_SQLERRD3
Returns the value of sqlerrd(3) from the SQLCA that is returned by the
server. Otherwise, the value zero is returned. I got the ZERO version.
If this were a fetch I would agree with you but it is a MERGE statement.

Thanks
Robin
Back to top
View user's profile Send private message
Robin Sulsona

New User


Joined: 14 Dec 2015
Posts: 8
Location: United States

PostPosted: Wed Jun 01, 2016 11:32 pm    Post subject: Merge Statement as requested
Reply with quote

Code:
 MERGE INTO FANS_AGT_COMM_SUMMARY_CLONE AS MST
 USING (VALUES (:WT02-AGENT-ID, :WT02-USE-AGENT-ID
               ,:WT02-COMP-YEAR, :WT02-COMP-MONTH
               ,:WT02-COMPANY-REGION
               ,:WT02-COMP-PERIOD, :WT02-PRODUCT-CATEGORY
               ,:WT02-COMP-LEVEL, :WT02-PRIN-NONPRIN
               ,:WT02-INIT-COMP, :WT02-SUB-COMP
               ,:WT02-PAC-COMP, :WT02-TRAILS
               ,:WT02-INIT-IRA-COMP, :WT02-SUB-IRA-COMP
               ,:WT02-PAC-IRA-COMP, :WT02-IRA-TRAILS
               ,:WT02-BONUS, :WT02-OSJ-COMP)
                 FOR :WT02-MAX-ENTRIES ROWS) AS TX
              (AGENT_ID, USE_AGENT_ID, COMP_YEAR
              ,COMP_MONTH, COMPANY_REGION, COMP_PERIOD
              ,PRODUCT_CATEGORY, COMP_LEVEL
              ,PRIN_NONPRIN, INIT_COMP, SUB_COMP
              ,PAC_COMP, TRAILS, INIT_IRA_COMP
              ,SUB_IRA_COMP, PAC_IRA_COMP
              ,IRA_TRAILS, BONUS, OSJ_COMP)
        ON (MST.AGENT_ID           = TX.AGENT_ID         AND
              MST.USE_AGENT_ID     = TX.USE_AGENT_ID     AND
              MST.COMP_YEAR        = TX.COMP_YEAR        AND
              MST.COMP_MONTH       = TX.COMP_MONTH       AND
              MST.COMPANY_REGION   = TX.COMPANY_REGION   AND
              MST.COMP_PERIOD      = TX.COMP_PERIOD      AND
              MST.PRODUCT_CATEGORY = TX.PRODUCT_CATEGORY AND
              MST.COMP_LEVEL       = TX.COMP_LEVEL       AND
              MST.PRIN_NONPRIN     = TX.PRIN_NONPRIN)
            WHEN MATCHED THEN
                UPDATE SET MST.INIT_COMP     = MST.INIT_COMP
                                             + TX.INIT_COMP
                          ,MST.SUB_COMP      = MST.SUB_COMP
                                             + TX.SUB_COMP
                          ,MST.PAC_COMP      = MST.PAC_COMP
                                             + TX.PAC_COMP
                          ,MST.TRAILS        = MST.TRAILS
                                             + TX.TRAILS
                          ,MST.INIT_IRA_COMP = MST.INIT_IRA_COMP
                                             + TX.INIT_IRA_COMP
                          ,MST.SUB_IRA_COMP  = MST.SUB_IRA_COMP
                                             + TX.SUB_IRA_COMP
                          ,MST.PAC_IRA_COMP  = MST.PAC_IRA_COMP
                                             + TX.PAC_IRA_COMP
                         ,MST.IRA_TRAILS     = MST.IRA_TRAILS
                                             + TX.IRA_TRAILS
                         ,MST.BONUS          = TX.BONUS
                         ,MST.OSJ_COMP       = TX.OSJ_COMP
           WHEN NOT MATCHED THEN
               INSERT (AGENT_ID, USE_AGENT_ID
                      ,COMP_YEAR, COMP_MONTH
                      ,COMPANY_REGION, COMP_PERIOD
                      ,PRODUCT_CATEGORY, COMP_LEVEL
                      ,PRIN_NONPRIN, INIT_COMP, SUB_COMP
                      ,PAC_COMP, TRAILS, INIT_IRA_COMP
                      ,SUB_IRA_COMP, PAC_IRA_COMP, IRA_TRAILS
                      ,BONUS, OSJ_COMP)
        VALUES (TX.AGENT_ID, TX.USE_AGENT_ID
               ,TX.COMP_YEAR, TX.COMP_MONTH
               ,TX.COMPANY_REGION, TX.COMP_PERIOD
               ,TX.PRODUCT_CATEGORY, TX.COMP_LEVEL
               ,TX.PRIN_NONPRIN, TX.INIT_COMP
               ,TX.SUB_COMP, TX.PAC_COMP, TX.TRAILS
               ,TX.INIT_IRA_COMP, TX.SUB_IRA_COMP
               ,TX.PAC_IRA_COMP, TX.IRA_TRAILS
               ,TX.BONUS, TX.OSJ_COMP)
        NOT ATOMIC CONTINUE ON SQLEXCEPTION
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Thu Jun 02, 2016 1:19 am    Post subject:
Reply with quote

Look for Get Diagnostics.
Code:
SQLCA and GET DIAGNOSTICS considerations:
The GET DIAGNOSTICS statement can be used immediately after the MERGE statement to check which input rows fail during the merge operation. The GET DIAGNOSTICS statement information item, NUMBER, indicates the number of conditions that are raised. The GET DIAGNOSTICS condition information item, DB2_ROW_NUMBER, indicates the input source rows that cause an error.
or here
So you need really DB2_ROW_NUMBER to know the last successful row and in your case it is zero means none got processed and that means either the first row is bad one or something wrong with the table which is the case now.
You told us everything went good for last 3 days and on 4th daya you started getting this issue means mostly the row is not good unless what you found is true. in future DBA's are the first POC's in such cases as we can't look inside what went wrong from database side.
Back to top
View user's profile Send private message
Robin Sulsona

New User


Joined: 14 Dec 2015
Posts: 8
Location: United States

PostPosted: Thu Jun 02, 2016 1:28 am    Post subject: Merge Statement issue resolved
Reply with quote

Unfortunately, I am the first here to use the MERGE statement and none of the DBAs knew why I was getting the -254. One of them found that the table (clone) was in copy pending status but when I ran the image copy not knowing that I had to run a slightly different version of the copy my table was still in copy pending. So we didn't pursue that path any longer until I went looking to see if I had to do something special for the tablespace with the clone in it. Found I did and I then did that but it was then that I realized I was loading the clone table from the unload of the "master" table and the load cards didn't have a NOCOPYPEND on them. Once I fixed that in my process via "batch" FILE MANAGER, it loaded and the process ran. I have since run Day 9 and all is back to normal. But I also found that the first entry in the GET DIAGNOSTICS is more at the "process" level and that you really need to dump each individual CONDITION entry to see what if anything is wrong with that row of data. Once I did that and saw that I had a -904 then it was clear what I needed to do. It would have been nice to have had a simpler way to find this but I now know a lot more about the MERGE statement and will be able to put together a "needs to know" document for our internal Wiki so others will not have to go thru this pain.
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Thu Jun 02, 2016 10:51 pm    Post subject:
Reply with quote

So the bottom line is , for cloned table with MERGE statement , you get SQLCODE=-254 instead of -904 like usual insert for COPYPEND state.
Back to top
View user's profile Send private message
Robin Sulsona

New User


Joined: 14 Dec 2015
Posts: 8
Location: United States

PostPosted: Thu Jun 02, 2016 11:34 pm    Post subject: Reply to: SQL Merge error -254
Reply with quote

Yes, and we are now past our issues with this process and back on our testing track. Thanks for the assistance. It will be passed on to our staff for future use.
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 S922 Error yuvan ABENDS & Debugging 3 Fri Dec 02, 2016 6:58 pm
No new posts Invoke Webservice Fails with DFHPI100... divated CICS 2 Thu Nov 24, 2016 5:57 pm
No new posts Error during restore rename archanamuthukrishnan All Other Mainframe Topics 2 Fri Oct 14, 2016 3:30 pm
No new posts Error IEC161I 052(009,XXXRS00)-084 wh... amitc23 JCL & VSAM 10 Wed Oct 12, 2016 5:00 pm
No new posts IDMS/DC-COBOL program - SNAP error wh... rakeshsekar1987 IDMS/ADSO 5 Tue Sep 13, 2016 8:28 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us