How to read a whole file and retrieve data from the DB2 database

Products

• PanAudit Plus
• Easytrieve Report Generator
• PAN/SQL
• Endevor
• Endevor Natural Integration
• Endevor - ECLIPSE Plugin
• Endevor - Enterprise Workbench

Issue/Introduction

This example reads the (whole) personnel file and retrieves data from the database based on employee number.

PARM SSID(xxxx)
FILE PERSNL
P-EMPNO   9 5 A
*
SQL   INCLUDE FROM owner.DB2FILE1
SQL   DECLARE C1 CURSOR FOR                +
      SELECT DEPT, LASTNAME, FIRSTNAME,    +
      NETPAY FROM owner.DB2FILE1           +
      WHERE EMPNO = :P-EMPNO
*
JOB INPUT PERSNL
SQL OPEN C1
PERFORM CHECK-SQL-CODE
DO WHILE SQLCODE NE 100
 SQL FETCH C1 INTO :DEPT, :LASTNAME, :FIRSTNAME, :NETPAY
PERFORM CHECK-SQL-CODE
 IF SQLCODE NE 100
   PRINT RPRT1
 END-IF
END-DO
SQL CLOSE C1
PERFORM CHECK-SQL-CODE
*
CHECK-SQL-CODE. PROC
  IF SQLCODE NE 0 AND SQLCODE NE 100
    DISPLAY 'ERROR - SQLCODE = ' SQLCODE
 STOP EXECUTE
  END-IF
END-PROC
*
REPORT RPRT1 LINESIZE 80
  TITLE 'EXAMPLE FOR INPUT FILE'
  LINE DEPT, P-EMPNO, LASTNAME, FIRSTNAME, NETPAY

Environment

Release: EDBMSU00200-11.5-Easytrieve-Report Generator-Option for DB2-MSU
Component: