Joined: 14 Dec 2015 Posts: 10 Location: United States
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 :?
Joined: 14 Dec 2015 Posts: 10 Location: United States
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.
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.
Joined: 14 Dec 2015 Posts: 10 Location: United States
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.
Joined: 14 Dec 2015 Posts: 10 Location: United States
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.
