Joined: 02 Dec 2002 Posts: 618 Topics: 172 Location: Stockholm, Sweden
Posted: Fri Dec 17, 2021 11:44 am Post subject: SQLCODE -811 on multi-row merge
I don't think I've ever seen this and would welcome any suggestions as to the problem. First of all, my multi-row MERGE cobol statement
Code:
PD EXEC SQL
PD SELECT STATUS
PD INTO :W-MERGE-STATUS
PD FROM FINAL TABLE
PD (
PD MERGE INTO TGECNAV
PD AS T
PD INCLUDE (STATUS CHAR(1))
PD using
PD (values
PD (
PD :CNAVMR-ID-KORTNR
PD ,:CNAVMR-ID-CNA
PD ,:CNAVMR-DA-SKAPAD
PD ,:CNAVMR-DA-SKICKAD
PD ,:CNAVMR-ID-SYSTEM
PD )
PD FOR :AA-CNAVMR-ROWS ROWS
PD ) as S
PD * S is used by DB2 so as to refer to the SQL
PD * variables that are used IF we need to do an insert
PD (
PD ID_KORTNR
PD ,ID_CNA
PD ,DA_SKAPAD
PD ,DA_SKICKAD
PD ,ID_SYSTEM
PD )
PD * This ensures that DB2 "knows" whether the row exists
PD * and will need to be updated or inserted
PD ON T.ID_KORTNR = S.ID_KORTNR
PD * and ..... add any extra selection conditions here
PD
PD when matched then
PD * NB NB NB NB NB
PD * Don't forget to remove columns that DON'T need to be
PD * updated, otherwise you COULD get HORRENDOUS response
PD * times as a result
PD * NB NB NB NB NB
PD update set
PD ID_CNA = S.ID_CNA
PD ,STATUS = 'U'
PD
PD when not matched then
PD insert
PD (
PD ID_KORTNR
PD ,ID_CNA
PD ,DA_SKAPAD
PD ,DA_SKICKAD
PD ,ID_SYSTEM
PD ,STATUS
PD )
PD values
PD (
PD S.ID_KORTNR
PD ,S.ID_CNA
PD ,S.DA_SKAPAD
PD ,S.DA_SKICKAD
PD ,S.ID_SYSTEM
PD ,'I'
PD )
NOT ATOMIC CONTINUE ON SQLEXCEPTION
PD )
PD end-exec
Let's see if I can include what I assume are the pertinent details.
First of all, the code works fine in I deliberately set AA-CNAVMR-ROWS to 1 (ie, i's a multi-row merge, but there was only one row to merge).
As soon as I build up 2 rows and try to do a MR merge on those 2 rows, I receive -811.
The table has a primary key on ID_KORTNR and 2 index clumns ID_KORTNR and DA_SKICKAD.
The 2 values in ID_KORTNR are unique.
The idea is to insert a new row into TGECNAV if the ID_KORTNR value is missing, otherwise simply to update the column ID_CNA only.
This is code I generate via a Rexx script. I've used it for quite some years but it's been a while since I last used it. Could something have changed in DB2 versions?
I don't remember ever having to include the NOT ATOMIC statement but that could be me.
If I remove NOT ATOMIC, the compiler complains about the
Code:
FOR :AA-CNAVMR-ROWS ROWS
FWIW, is defined as (in case DB2 is sensitive to that)
Code:
05 AA-CNAVMR-ROWS PIC s9(9) COMP-4.
My definitions for the table are as follows:-
Code:
AC 01 TGECNAVMR.
AC 05 CNAVMR-OCCURS-COUNT PIC S9(9) COMP value 10.
AC 05 CNAVMR-ID-KORTNR PIC X(16) OCCURS 10.
AC 05 CNAVMR-ID-CNA PIC X(76) OCCURS 10.
AC 05 CNAVMR-DA-SKAPAD PIC X(26) OCCURS 10.
AC 05 CNAVMR-DA-SKICKAD PIC X(26) OCCURS 10.
AC 05 CNAVMR-ID-SYSTEM PIC X(2) OCCURS 10.
Anyone with any suggestions. Obviously I googled the 811 error, but it talks about a table of more than one row, and I can't understand where that is coming from (unless it's some DB2 processing under the covers I'm not aware of) _________________ Michael
Joined: 02 Dec 2002 Posts: 618 Topics: 172 Location: Stockholm, Sweden
Posted: Sat Dec 18, 2021 1:18 am Post subject:
Okay. After some experimentation (baby steps changing one thing at a time), it seems that the problem is in usage of the FINAL TABLE and the STATUS column.
If I comment out that code, then it works ...... and that is obviously (?) the problem.
Could it simply be that you can't use the SQL I'm using (with the final table) for a multi-row merge?
Assuming that the 2 rows both perform an update, that would result in 2 rows in the final table. The select at the start doesn't include something like a FETCH FIRST ROW ONLY (which would remove the -811).
I'll google a bit more, but what would happen if I try and merge 10 rows, of which 6 are updates and 4 are inserts. What would happen with the STATUS column? Would it contain an I or a U?
I'll experiment with TWO columns maybe, one for updates and one for inserts and use a counter instead and see what happens.
More to follow. _________________ Michael
Joined: 02 Dec 2002 Posts: 618 Topics: 172 Location: Stockholm, Sweden
Posted: Sat Dec 18, 2021 2:13 am Post subject:
After some experimentation, I'm going to go with "if you do a multi-row merge, you can't know how many rows have been updated/inserted for each merge call"
Here was my (what I thought logical) take on the required SQL
Code:
EXEC SQL
* SELECT STATUS_U
* ,STATUS_I
* INTO :ac-merge-update
* ,:ac-merge-insert
* FROM FINAL TABLE
* (
MERGE INTO TGECNAV
AS T
* INCLUDE (
* STATUS_U SMALLINT
* ,STATUS_I SMALLINT
* )
using
(values
(
:CNAVMR-ID-KORTNR
,:CNAVMR-ID-CNA
,:CNAVMR-DA-SKAPAD
,:CNAVMR-DA-SKICKAD
,:CNAVMR-ID-SYSTEM
)
FOR :AA-CNAVMR-ROWS ROWS
) as S
(
ID_KORTNR
,ID_CNA
,DA_SKAPAD
,DA_SKICKAD
,ID_SYSTEM
)
ON T.ID_KORTNR = S.ID_KORTNR
when matched then
update set
ID_CNA = S.ID_CNA
* ,t.STATUS_U = t.STATUS_U + 1
when not matched then
insert
(
ID_KORTNR
,ID_CNA
,DA_SKAPAD
,DA_SKICKAD
,ID_SYSTEM
* ,t.STATUS_I
)
values
(
S.ID_KORTNR
,S.ID_CNA
,S.DA_SKAPAD
,S.DA_SKICKAD
,S.ID_SYSTEM
* ,t.status_i = t.status_i + 1
)
NOT ATOMIC CONTINUE ON SQLEXCEPTION
* )
end-exec
You can probably see where I was trying to go with this. Create a final table containing 2 columns; both numeric counters, one for updates and one for inserts.
I then realized that the insert statement probably couldn't include what in effect was an update statement (t.status_i + 1), so I commented that out.
Trouble is, with the
Quote:
,t.STATUS_U = t.STATUS_U + 1
line, I was receiving SQL -121, so that didn't work either. (The idea behind that was to know how many rows were updated in the MERGE and subtract that from SQLERRD(3) so I know how many rows were inserted) Remember, SQLERRD(3) gives you the total number of changed lines, both updated AND inserted.
So unless someone can come up with something smart in DB2, I'm going to assume that counting updated, inserted or deleted rows in a multi-row merge is a non-starter. _________________ Michael
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
