Okay I'm doing a "fetch next rowset from cur for 100 rows". Simple no-scroll.
There are only 12 rows so it gets them into the cobol table successfully.
Returns sqlcode +100. Fine.
Process the 1st occurance in the table.
The program does some stuff with reporting etc.
Now I want to delete row 1 in the rowset.
"delete from table-name where current of cur for row :sub of rowset"
sub looks like: 05 sub pic s9(4) comp. Contains value 1.
The delete fails with sqlcode -490. "Number outside the range of allowable values".

Why?

It returned 12 rows, I'm trying to delete row 1 in the rowset. Why is "1" outside the range of allowable values?

I suppose you're using DB2 for z/OS Version 8 or 9.


SQLCODE -490 says, that the specified value is outside the allowed range.
For a positioned delete the range is 1 to 32767. So I suppose the variable sub is incorrectly initialised ( and doesn't contain 1).

If your cursor returns 12 rows and you want to delete row 15 of this rowset, you'll receive sqlcode -248

The host-variable does contain 1.
Here is the answer we found today. In typical IBM fashion this is obtusely documented.

The documentation says that the number must be between 1 and 32767. The problem is the length of host-variable. Even though only 12 rows were returned and we tried to delete row 1 which is certainly between 1 and 32767, that is irrelevant. Because the host variable was defined as PIC S9(4) COMP it could never contain the maximum of 32767. Never mind that the actual value was 1. So it produced a -490 which said the number was out-of-range. I guess it thought the number was between 1 and 9999. When we changed the variable to PIC S9(5), the delete worked.

I don't know Cobol, so I'm just guessing... I could imagine that DB2 takes the memory area you provided and interpreted the number found there. Since DB2 expects 5 bytes to hold the number and you gave only 4, DB2 read those 4 bytes plus one additional byte, which whacks off your number. Could someone with Cobol knowledge tell me whether this could be right or not?

I thought it might be reading past the end, which is what lead me to try changing the picture clause.

S9(1) COMP thru S9(4) COMP is a binary halfword (2)
S9(5) COMP thru S9(9) COMP is a binary fullword (4)
S9(10) COMP thru S9(18) COMP is a binary doubleword (8).
Each S9 specifies the number of decimal digits. (S means signed).

I'm not sure that's actually it though since host-variables can also be COMP-3 (Packed Decimal).

The manual is very unclear IMO that it wants the row in a 5 digit field. It just says the value must be between 1 and 32767. Not that the data area must be capable of holding the maximum. Anyway, it's fixed so I'm happy.

It is not a matter of bytes. PIC S9(4) COMP is a half word binary (2 bytes) and is the datatype used in COBOL for a DB2 smallint, so it does hold up to 32767. I think it is just a bug in DB2 and whomever wrote that part of DB2 made a mistake by rejecting that host variable.

Fully agree.
DB2 normally *only* accepts COBOL datatypes S9(4) COMP and S9(9) COMP for integer values (smallint & int); and an "S9(4) COMP" *can* hold the value 32767.
In all similar situations, DB2 (or rather: the precompiler) would reject an S9(5) COMP.
I would suggest you check which precompiler you are using; try switching to the COBOL built-in one if you currently use an external one, or vice versa.
If it's a bug (which I also think it is), it's most likely a precompiler bug.