Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2008
    Posts
    5

    Unanswered: Positioned delete on multi-row fetch

    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?

  2. #2
    Join Date
    Dec 2005
    Posts
    273
    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
    Last edited by umayer; 12-10-08 at 05:56.

  3. #3
    Join Date
    Dec 2008
    Posts
    5

    Answer

    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.

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Dec 2008
    Posts
    5
    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.

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by stolze
    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?
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by Marcus_A
    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.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •