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

Share your thoughts, leave a comment!