IBM DB2 for i: Code example

RPG language programming example

    H
     F*  File declaration for QPRINT
     F*
     FQPRINT  O   F     132            PRINTER
     I*
     I* Structure for report 1.
     I*
     IRPT1      E DSPROJECT
     I              PROJNAME                        PROJNM
     I              RESPEMP                         RESEM
     I              PRSTAFF                         STAFF
     I              PRSTDATE                        PRSTD
     I              PRENDATE                        PREND
     I              MAJPROJ                         MAJPRJ
     I*
     I            DS
     I                                        1   6 EMPNO
     I                                        7  36 NAME
     I                                    P  37  412SALARY
     I*
     I* Structure for report 2.
     I*
     IRPT2        DS
     I                                        1   6 PRJNUM
     I                                        7  42 PNAME
     I                                    B  43  440EMPCNT
     I                                    P  45  492PRCOST
     I*
     I            DS
     I                                    B   1   20WRKDAY
     I                                    P   3   62COMMI
     I                                        7  16 RDATE
     I                                    P  17  202PERCNT
     C*
     C                     Z-ADD253       WRKDAY
     C                     Z-ADD2000.00   COMMI
     C                     Z-ADD1.04      PERCNT
     C                     MOVEL'1982-06-'RDATE
     C                     MOVE '01'      RDATE
     C                     SETON                     LR
     C*
     C* Update the selected projects by the new percentage. If an
     C* error occurs during the update, ROLLBACK the changes.
     C*
     C/EXEC SQL WHENEVER SQLERROR GOTO UPDERR
     C/END-EXEC
     C*
     C/EXEC SQL
     C+ UPDATE CORPDATA/EMPLOYEE
     C+    SET SALARY = SALARY * :PERCNT
     C+    WHERE COMM >= :COMMI
     C/END-EXEC
     C*
     C* Commit changes.
     C*
     C/EXEC SQL COMMIT
     C/END-EXEC
     C*
     C/EXEC SQL WHENEVER SQLERROR GO TO RPTERR
     C/END-EXEC
     C*
     C* Report the updated statistics for each employee assigned to
     C* selected projects.
     C*
     C* Write out the header for report 1.
     C*
     C                     EXCPTRECA
     C/EXEC SQL DECLARE C1 CURSOR FOR
     C+    SELECT DISTINCT PROJNO, EMP_ACT.EMPNO,
     C+           LASTNAME||', '||FIRSTNME, SALARY
     C+        FROM CORPDATA/EMP_ACT, CORPDATA/EMPLOYEE
     C+        WHERE EMP_ACT.EMPNO = EMPLOYEE.EMPNO AND
     C+              COMM >= :COMMI
     C+        ORDER BY PROJNO, EMPNO
     C/END-EXEC
     C*
     C/EXEC SQL
     C+  OPEN C1
     C/END-EXEC
     C*
     C* Fetch and write the rows to QPRINT.
     C*
     C/EXEC SQL WHENEVER NOT FOUND GO TO DONE1
     C/END-EXEC
     C           SQLCOD    DOUNE0
     C/EXEC SQL
     C+   FETCH C1 INTO :PROJNO,:EMPNO,:NAME, :SALARY
     C/END-EXEC
     C                     EXCPTRECB
     C                     END
     C           DONE1     TAG
     C/EXEC SQL
     C+  CLOSE C1
     C/END-EXEC
     C*
     C* For all project ending at a date later than the raise date
     C* (i.e. those projects potentially affected by the salary raises)
     C* generate a report containing the project number, project name,
     C* the count of employees participating in the project and the
     C* total salary cost of the project.
     C*
     C* Write out the header for report 2.
     C*
     C                     EXCPTRECC
     C/EXEC SQL
     C+  DECLARE C2 CURSOR FOR
     C+    SELECT EMP_ACT.PROJNO, PROJNAME, COUNT(*),
     C+       SUM((DAYS(EMENDATE) - DAYS(EMSTDATE)) * EMPTIME *
     C+           DECIMAL((SALARY/:WRKDAY),8,2))
     C+    FROM CORPDATA/EMP_ACT, CORPDATA/PROJECT, CORPDATA/EMPLOYEE
     C+    WHERE EMP_ACT.PROJNO = PROJECT.PROJNO AND
     C+          EMP_ACT.EMPNO = EMPLOYEE.EMPNO AND
     C+          PRENDATE > :RDATE
     C+    GROUP BY EMP_ACT.PROJNO, PROJNAME
     C+    ORDER BY 1
     C/END-EXEC
     C*
     C/EXEC SQL OPEN C2
     C/END-EXEC
     C*
     C* Fetch and write the rows to QPRINT.
     C*
     C/EXEC SQL WHENEVER NOT FOUND GO TO DONE2
     C/END-EXEC
     C           SQLCOD    DOUNE0
     C/EXEC SQL
     C+   FETCH C2 INTO :RPT2
     C/END-EXEC
     C                     EXCPTRECD
     C                     END
     C           DONE2     TAG
     C/EXEC SQL CLOSE C2
     C/END-EXEC
     C                     RETRN
     C*
     C* Error occurred while updating table.  Inform user and rollback
     C* changes.
     C*
     C           UPDERR    TAG
     C                     EXCPTRECE
     C/EXEC SQL WHENEVER SQLERROR CONTINUE
     C/END-EXEC
     C*
     C/EXEC SQL
     C+   ROLLBACK
     C/END-EXEC
     C                     RETRN
     C*
     C* Error occurred while generating reports.  Inform user and exit.
     C*
     C           RPTERR    TAG
     C                     EXCPTRECF
     C*
     C* All done.
     C*
     C           FINISH    TAG
     OQPRINT  E 0201           RECA
     O                                   45 'REPORT OF PROJECTS AFFEC'
     O                                   64 'TED BY EMPLOYEE RAISES'
     O        E 01             RECA
     O                                    7 'PROJECT'
     O                                   17 'EMPLOYEE'
     O                                   32 'EMPLOYEE NAME'
     O                                   60 'SALARY'
     O        E 01             RECB
     O                         PROJNO     6
     O                         EMPNO     15
     O                         NAME      50
     O                         SALARYL   61
     O        E 22             RECC
     O                                   42 'ACCUMULATED STATISTIC'
     O                                   54 'S BY PROJECT'
     O        E 01             RECC
     O                                    7 'PROJECT'
     O                                   56 'NUMBER OF'
     O                                   67 'TOTAL'
     O        E 02             RECC
     O                                    6 'NUMBER'
     O                                   21 'PROJECT NAME'
     O                                   56 'EMPLOYEES'
     O                                   66 'COST'
     O        E 01             RECD
     O                         PRJNUM     6
     O                         PNAME     45
     O                         EMPCNTL   54
     O                         PRCOSTL   70
     O        E 01             RECE
     O                                   28 '*** ERROR Occurred while'
     O                                   52 ' updating table. SQLCODE'
     O                                   53 '='
     O                         SQLCODL   62
     O        E 01             RECF
     O                                   28 '*** ERROR Occurred while'
     O                                   52 ' generating reports. SQL'
     O                                   57 'CODE='
     O                         SQLCODL   67

Contact IBM

Browse Power Systems

Next generation applications for big data and analytics and cognitive computing are providing unprecedented insights into opportunities, threats and efficiencies. IBM Power Systems is at the forefront of delivering solutions to gain faster insights from analyzing both structured information and unstructured big data. With the secure, flexible and open platform of IBM Power Systems plus solutions and software, organizations can outpace their competitors by delivering faster services, providing differentiated offerings and turning operational cost into investment opportunity.

To draw insights and make better decisions, businesses rely on the secure, flexible and open platform of IBM Power Systems. Built with the first processor designed for big data workloads, the design of Power Systems combines the computing power, memory bandwidth and I/O in ways that are easier to consume and manage, building on strong resiliency, availability and security.

IBM Power Systems deliver flexibility and choice of operating systems to enable your business to support the next generation applications for big data and analytics and cognitive computing that are transforming how organizations work today. Whether running 1, 2, or all 3 - coupled with PowerVM, they maximize the benefit of Power Systems in your business.

Transform your business with Systems Software that enables virtualization, high availability, flexibility, security and compliance on Power Systems™. IBM’s integrated approach to developing Systems and Systems Software together delivers optimized results with Power Systems.

As an open innovation platform, Power Systems is optimized for big data and analytics performance and to deliver scale-out economics and security for the cloud. IBM and IBM Business Partner solutions exploit key capabilities in IBM Power Systems.

Over the last five years thousands of clients have migrated to IBM Power Systems. Learn how Power Systems has helped them support next generation applications for big data and analytics and cognitive computing on an open platform for choice while improving business performance, reducing risk, and establishing a platform for growth.

Additional information