Loading

Embedded DB2 SQL and Cursor in Easytrieve

SQL statements in Easytrieve should be prefixed with the word, SQL.
For eg,

 SQL SELECT 1 FROM +
 SYSIBM.SYSDUMMY1 INTO :WS-INT

+ statement is used for statement continuation in the next line.

Host variable datatype mapping between Easytrieve and DB2.

DB2 Datatype Number of Bytes Easytrieve variable
INTEGER 4 WS-INT W 4 B
SMALLINT 2 WS-SMALLINT W 2 B
DATE 10 WS-DATE W 10 A
CHAR(25) 25 WS-CHAR-25 W 25 A
TIMESTAMP 26 WS-TIMESTAMP W 26 A
NULL INDICATOR 2 WS-NULLIND W 2 B
DECIMAL(16,2) 8 WS-DECI1 W 8 P 2
DECIMAL(16,5) 8 WS-DECI2 W 8 P 5
DECIMAL(17,5) 9 WS-DECI3 W 9 P 5

How to get a SQL’s return code?

Use the SQLCODE variable to check the DB2 SQL return code.

PARM SSID('DBXX')
 EMP-ID   W  6 N
 EMP-NAME W 25 A
 JOB INPUT NULL
 SQL                              +
  SELECT EMP_ID, EMP_NAME FROM    +
  EMPLOYEE INTO                   +
  :EMP-ID, :EMP-NAME
  CASE SQLCODE
  WHEN +0
     DISPLAY 'EMP_ID :' EMP-ID
     DISPLAY 'EMP_NAME :' EMP-NAME
  WHEN +100
     DISPLAY 'NO ROW FOUND'
  OTHERWISE
     DISPLAY 'ERROR. SQLCODE ' SQLCODE
 END-CASE
 STOP

How to Run an Update query and get the number of rows updated?

SQLERRD(3) = number of rows updated from an Update query or deleted by a delete query

PARM SSID('DBXX')
 JOB INPUT NULL
 SQL                              +
  UPDATE EMPLOYEE                 +
   SET SALARY = SALARY * 1.05     +
   WHERE EMP_ID = 113114
*
  DISPLAY 'SQLCODE IS   : ' SQLCODE
  DISPLAY 'ROWS UPDATED : ' SQLERRD(3)
*
 STOP

How to declare a Cursor and Fetch records?

PARM SSID('DBXX')
 EMP-ID   W  6 N
 EMP-NAME W 25 A
*
 SQL DECLARE C1 CURSOR FOR        +
  SELECT EMP_ID, EMP_NAME FROM    +
  EMPLOYEE                        +
  WHERE EMP_ID > 5
*
 JOB INPUT NULL
 SQL OPEN C1
 DISPLAY ' SQL CODE : ' SQLCODE
*
 DO WHILE SQLCODE NE +100
    SQL FETCH C1 INTO             +
          :EMP-ID                 +
         ,:EMP-NAME

    DISPLAY ' SQL CODE : ' SQLCODE
    IF SQLCODE NE 0 AND SQLCODE NE 100
       DISPLAY 'ERROR : SQLCODE : ' SQLCODE
       STOP
    END-IF
    IF SQLCODE EQ 0
       DISPLAY 'EMP-ID      :' EMP-ID
       DISPLAY 'EMP-NAME    :' EMP-NAME
    END-IF
 END-DO
*
 SQL CLOSE C1
 DISPLAY ' SQL CODE : ' SQLCODE
*
 STOP

Also consider reading

http://www.angelfire.com/folk/anoop/easydb2.pdf

http://www.mvsforums.com/helpboards/viewtopic.php?t=478

http://ibmmainframes.com/about27446.html

Multi Row Fetch (Thanks to Roy for providing this link)

http://www.hiperformance-software.de/documents/DB2%20V8%20Multiple%20Row%20Fetch%20-%20Concepts%20V22.pdf

Related Posts




blog comments powered by Disqus

There's 10 Comments So Far

  •   Roy Reynolds
    March 8th, 2012 at 2:58 pm

    You might reconsider the title for the ‘Multi Row Fetch using a Cursor’ section.
    It is not a DB2 multi-row fetch according to IBM/DB2’s definition. Your example is just a ‘Fetch many rows from a Cursor’s result set, one at a time.’
    Multi-Row Fetch is the process of getting many rows from the Cursor’s result set in one fetch and loading them to arrays, one array for each column mentioned in the cursor. The array size (OCCURS nn) must be the same for every column.
    Once I figure out a real coding example, I’ll send it.
    Cheers,
    Roy

  •   Karthik
    March 9th, 2012 at 11:41 am

    Hi Roy,
    Sorry, I have now corrected the heading for Cursor usage example. Can I post your sample code in this page?
    Thanks,
    Karthik

  •   Raja
    August 13th, 2012 at 7:51 pm

    Sometimes we will handle the -811 in cobol-db2 program (with out using the cursor).

    Similarly I want to get the -811 in eztrieve. Is this possible?

    Your answer will be greatly appreciated !!!!!!!!!

  •   Karthik
    August 14th, 2012 at 10:00 am

    Hi Raja,

    yes, we can handle -811 in Easytrieve (when a singleton select returns more than one record)

    IF SQLCODE EQ -811
    DISPLAY ‘ERROR -811. MORE THAN ONE ROW RETRIEVED. USE A CURSOR’
    END-IF

  •   Raja
    August 14th, 2012 at 6:04 pm

    Thanks for the response…………

    Hi Karthik,

    If you share this with piece of code. it will be helpful for understanding….

    Thanks in advance!!!!

  •   seven
    December 25th, 2012 at 3:03 am

    hi header
    can you send me a integrated program.thanks in advance

  •   Ram
    January 22nd, 2013 at 10:08 am

    Hello Kartihk,

    I need a sample program, Calling cobol module from easytrieve.

    Any examples would be a great help!

  •   Jason
    August 8th, 2013 at 5:59 pm

    I’m getting a -514.

    I’m using a cursor to loop a fetch and another cursor to retrieve a row based on my looped fetch. I’m doing this with two different tables. Once I finish the first table, I process the second table. When I process the second table I get the -514 on the open of the cursor that’s retrieving based on what was retrieved in the looped fetch. I am closing the cursor before opening it each time.

    -514 in the manual = CUSROR not in a PREPARED state. This makes no sense to me.

  •   PRASUN DE
    April 7th, 2014 at 8:48 pm

    Null indicator definition should be modified to

    WS-NULLIND W 2 B 0

    Program would not compile in easytrieve without the 0 at the end

  •   John Hessenthaler
    August 14th, 2014 at 1:19 pm

    Can anyone tell me why this works in Easytrieve:
    SQL DECLARE EOM_DATE CURSOR FOR +
    SELECT (DATE(‘1995-01-01’) + 1 MONTH – 1 DAY) +
    FROM SYSIBM.SYSDUMMY1 +
    WITH UR

    And this does not, I get a -418:
    SQL DECLARE EOM_DATE CURSOR FOR +
    SELECT (DATE(:W-HOLD-DATE) + 1 MONTH – 1 DAY) +
    FROM SYSIBM.SYSDUMMY1 +
    WITH UR

    W-HOLD-DATE W 10 A VALUE ‘ ‘

    ******B204 SQL – SQL ERROR, SQLCODE IS -418
    ******B204 SQL – DSNT408I SQLCODE = -418, ERROR: A STATEMENT STRING TO BE PREPARED CONTAINS AN INVALID
    *******B204 SQL – USE OF PARAMETER MARKERS
    *******B204 SQL – DSNT418I SQLSTATE = 42610 SQLSTATE RETURN CODE

Share your thoughts, leave a comment!