<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE html
  PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html lang="en-us" xml:lang="en-us">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<meta name="security" content="public" />
<meta name="Robots" content="index,follow" />
<meta http-equiv="PICS-Label" content='(PICS-1.1 "http://www.icra.org/ratingsv02.html" l gen true r (cz 1 lz 1 nz 1 oz 1 vz 1) "http://www.rsac.org/ratingsv01.html" l gen true r (n 0 s 0 v 0 l 0) "http://www.classify.org/safesurf/" l gen true r (SS~~000 1))' />
<meta name="DC.Type" content="reference" />
<meta name="DC.Title" content="Example: SQL statements in ILE RPG programs" />
<meta name="abstract" content="This sample program is written in the ILE RPG programming language." />
<meta name="description" content="This sample program is written in the ILE RPG programming language." />
<meta name="DC.subject" content="SQL, statements, ILE RPG program, sample programs, SQL statements in, SQL statements in, sample, program, coding examples, SQL statements in" />
<meta name="keywords" content="SQL, statements, ILE RPG program, sample programs, SQL statements in, SQL statements in, sample, program, coding examples, SQL statements in" />
<meta name="DC.Relation" scheme="URI" content="rzajpsamprog.htm" />
<meta name="DC.Relation" scheme="URI" content="rzajprpgi.htm" />
<meta name="copyright" content="(C) Copyright IBM Corporation 1998, 2006" />
<meta name="DC.Rights.Owner" content="(C) Copyright IBM Corporation 1998, 2006" />
<meta name="DC.Format" content="XHTML" />
<meta name="DC.Identifier" content="rzajpssile" />
<meta name="DC.Language" content="en-us" />
<!-- All rights reserved. Licensed Materials Property of IBM -->
<!-- US Government Users Restricted Rights -->
<!-- Use, duplication or disclosure restricted by -->
<!-- GSA ADP Schedule Contract with IBM Corp. -->
<link rel="stylesheet" type="text/css" href="./ibmdita.css" />
<link rel="stylesheet" type="text/css" href="./ic.css" />
<title>Example: SQL statements in ILE RPG programs</title>
</head>
<body id="rzajpssile"><a name="rzajpssile"><!-- --></a>
<!-- Java sync-link --><script language="Javascript" src="../rzahg/synch.js" type="text/javascript"></script>
<h1 class="topictitle1">Example: SQL statements in ILE RPG programs</h1>
<div><p>This sample program is written in the ILE RPG programming language.</p>
<div class="section"> <div class="note"><span class="notetitle">Note:</span> By using the code examples, you agree to the terms of the <a href="codedisclaimer.htm">Code license and disclaimer information</a>.</div>
</div>
<div class="example"> <div class="fignone" id="rzajpssile__samprp2"><a name="rzajpssile__samprp2"><!-- --></a><span class="figcap">Figure 1. Sample ILE RPG program using
SQL statements</span><img src="./delta.gif" alt="Start of change" /><pre>5722ST1 V5R4M0 060210        Create SQL ILE RPG Object         RPGLEEX             08/06/02 16:03:02   Page   1
Source type...............RPG
Object name...............CORPDATA/RPGLEEX
Source file...............CORPDATA/SRC
Member....................*OBJ
To source file............QTEMP/QSQLTEMP1
Options...................*XREF
RPG preprocessor options..*NONE
Listing option............*PRINT
Target release............V5R4M0
INCLUDE file..............*SRCFILE
Commit....................*CHG
Allow copy of data........*YES
Close SQL cursor..........*ENDMOD
Allow blocking............*READ
Delay PREPARE.............*NO
Generation level..........10
Printer file..............*LIBL/QSYSPRT
Date format...............*JOB
Date separator............*JOB
Time format...............*HMS
Time separator ...........*JOB
Replace...................*YES
Relational database.......*LOCAL
User .....................*CURRENT
RDB connect method........*DUW
Default collection........*NONE
Dynamic default
  collection..............*NO
Package name..............*OBJLIB/*OBJ
Path......................*NAMING
SQL rules.................*DB2
Created object type.......*PGM
Debugging view............*NONE
User profile..............*NAMING
Dynamic user profile......*USER
Sort sequence.............*JOB
Language ID...............*JOB
IBM SQL flagging..........*NOFLAG
ANS flagging..............*NONE
Text......................*SRCMBRTXT
Source file CCSID.........65535
Job CCSID.................65535
Decimal result options:
  Maximum precision.......31
  Maximum scale...........31
  Minimum divide scale....0
Compiler options..........*NONE  
Source member changed on 07/01/96  15:55:32
</pre><img src="./deltaend.gif" alt="End of change" />
</div>
</div>
<div class="example"><div class="fignone"><img src="./delta.gif" alt="Start of change" /><pre>5722ST1 V5R4M0 060210       Create SQL ILE RPG Object         RPGLEEX            08/06/02 16:03:02   Page   2
Record  *...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 ...+... 8 SEQNBR Last change Comments
    1        H                                                                                100
    2        F*  File declaration for QPRINT                                                  200
    3        F*                                                                               300
    4        FQPRINT    O    F  132        PRINTER                                            400
    5        D*                                                                               500
    6        D* Structure for report 1.                                                       600
    7        D*                                                                               700
    8      <span><span>1</span></span> DRPT1           E DS                  EXTNAME(PROJECT)                           800
    9        D*                                                                               900
   10        D                 DS                                                            1000
   11        D EMPNO                   1      6                                              1100
   12        D NAME                    7     36                                              1200
   13        D SALARY                 37     41P 2                                           1300
   14        D*                                                                              1400
   15        D* Structure for report 2.                                                      1500
   16        D*                                                                              1600
   17        DRPT2             DS                                                            1700
   18        D PRJNUM                  1      6                                              1800
   19        D PNAME                   7     42                                              1900
   20        D EMPCNT                 43     44B 0                                           2000
   21        D PRCOST                 45     49P 2                                           2100
   22        D*                                                                              2200
   23        D                 DS                                                            2300
   24        D WRKDAY                  1      2B 0                                           2400
   25        D COMMI                   3      6P 2                                           2500
   26        D RDATE                   7     16                                              2600
   27        D PERCNT                 17     20P 2                                           2700
   28         *                                                                              2800
   29      <span><span>2</span></span> C                   Z-ADD     253           WRKDAY                              2900
   30        C                   Z-ADD     2000.00       COMMI                               3000
   31        C                   Z-ADD     1.04          PERCNT                              3100
   32        C                   MOVEL     '1982-06-'    RDATE                               3200
   33        C                   MOVE      '01'          RDATE                               3300
   34        C                   SETON                                        LR             3400
   35        C*                                                                              3500
   36        C* Update the selected projects by the new percentage. If an                    3600
   37        C* error occurs during the update, ROLLBACK the changes.                        3700
   38        C*                                                                              3800
   39      <span><span>3</span></span> C/EXEC SQL WHENEVER SQLERROR GOTO UPDERR                                        3900
   40        C/END-EXEC                                                                      4000
   41        C*                                                                              4100
   42        C/EXEC SQL                                                                      4200
   43      <span><span>4</span></span> C+ UPDATE CORPDATA/EMPLOYEE                                                     4300
   44        C+    SET SALARY = SALARY * :PERCNT                                             4400
   45        C+    WHERE COMM &gt;= :COMMI                                                      4500
   46        C/END-EXEC                                                                      4600
   47        C*                                                                              4700
   48        C* Commit changes.                                                              4800
   49        C*                                                                              4900
   50      <span><span>5</span></span> C/EXEC SQL COMMIT                                                               5000
   51        C/END-EXEC                                                                      5100
   52        C*                                                                              5200
   53        C/EXEC SQL WHENEVER SQLERROR GO TO RPTERR                                       5300
   54        C/END-EXEC                                                                      5400
   55        C*                                                                              5500
   56        C* Report the updated statistics for each employee assigned to                  5600
   57        C* selected projects.                                                           5700
   58        C*                                                                              5800
                                                                   12000</pre><img src="./deltaend.gif" alt="End of change" />
</div>
</div>
<div class="example"><div class="fignone"><img src="./delta.gif" alt="Start of change" /><pre>5722ST1 V5R4M0 060210       Create SQL ILE RPG Object         RPGLEEX             08/06/02 16:03:02   Page   3
Record  *...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 ...+... 8  SEQNBR Last change Comments
 59        C* Write out the header for report 1.                                           5900
   60        C*                                                                              6000
   61        C                   EXCEPT    RECA                                              6100
   62      <span><span>6</span></span> C/EXEC SQL DECLARE C1 CURSOR FOR                                                6200
   63        C+    SELECT DISTINCT PROJNO, EMPPROJACT.EMPNO,                                 6300
   64        C+           LASTNAME||', '||FIRSTNME, SALARY                                   6400
   65        C+        FROM CORPDATA/EMPPROJACT, CORPDATA/EMPLOYEE                           6500
   66        C+        WHERE EMPPROJACT.EMPNO = EMPLOYEE.EMPNO AND                           6600
   67        C+              COMM &gt;= :COMMI                                                  6700
   68        C+        ORDER BY PROJNO, EMPNO                                                6800
   69        C/END-EXEC                                                                      6900
   70        C*                                                                              7000
   71      <span><span>7</span></span> C/EXEC SQL                                                                      7100
   72        C+  OPEN C1                                                                     7200
   73        C/END-EXEC                                                                      7300
   74        C*                                                                              7400
   75        C* Fetch and write the rows to QPRINT.                                          7500
   76        C*                                                                              7600
   77      <span><span>8</span></span> C/EXEC SQL WHENEVER NOT FOUND GO TO DONE1                                       7700
   78        C/END-EXEC                                                                      7800
   79        C     SQLCOD        DOUNE     0                                                 7900
   80        C/EXEC SQL                                                                      8000
   81      <span><span>9</span></span> C+   FETCH C1 INTO :PROJNO, :EMPNO, :NAME, :SALARY                              8100
   82        C/END-EXEC                                                                      8200
   83        C                   EXCEPT    RECB                                              8300
   84        C                   END                                                         8400
   85        C     DONE1         TAG                                                         8500
   86        C/EXEC SQL                                                                      8600
   87     <span><span>10</span></span> C+  CLOSE C1                                                                    8700
   88        C/END-EXEC                                                                      8800
   89        C*                                                                              8900
   90        C* For all project ending at a date later than the raise date                   9000
   91        C* (i.e. those projects potentially affected by the salary raises)              9100
   92        C* generate a report containing the project number, project name,               9200
   93        C* the count of employees participating in the project and the                  9300
   94        C* total salary cost of the project.                                            9400
   95        C*                                                                              9500
   96        C* Write out the header for report 2.                                           9600
   97        C*                                                                              9700
   98        C                   EXCEPT    RECC                                              9800
   99        C/EXEC SQL                                                                      9900
  100     <span><span>11</span></span> C+  DECLARE C2 CURSOR FOR                                                      10000
  101        C+    SELECT EMPPROJACT.PROJNO, PROJNAME, COUNT(*),                            10100
  102        C+       SUM((DAYS(EMENDATE) - DAYS(EMSTDATE)) * EMPTIME *                     10200
  103        C+           DECIMAL((SALARY/:WRKDAY),8,2))                                    10300
  104        C+    FROM CORPDATA/EMPPROJACT, CORPDATA/PROJECT, CORPDATA/EMPLOYEE            10400
  105        C+    WHERE EMPPROJACT.PROJNO = PROJECT.PROJNO AND                             10500
  106        C+          EMPPROJACT.EMPNO = EMPLOYEE.EMPNO AND                              10600
  107        C+          PRENDATE &gt; :RDATE                                                  10700
  108        C+    GROUP BY EMPPROJACT.PROJNO, PROJNAME                                     10800
  109        C+    ORDER BY 1                                                               10900
  110        C/END-EXEC                                                                     11000
  111        C*                                                                             11100
  112        C/EXEC SQL OPEN C2                                                             11200
  113        C/END-EXEC                                                                     11300
  114        C*                                                                             11400
  115        C* Fetch and write the rows to QPRINT.                                         11500
  116        C*                                                                             11600
  117        C/EXEC SQL WHENEVER NOT FOUND GO TO DONE2                                      11700
  118        C/END-EXEC                                                                     11800
  119        C     SQLCOD        DOUNE     0                                                11900
  120        C/EXEC SQL    
  121     <span><span>12</span></span> C+   FETCH C2 INTO :RPT2                                                       12100
  122        C/END-EXEC                                                                     12200
  123        C                   EXCEPT    RECD                                             12300</pre><img src="./deltaend.gif" alt="End of change" />
</div>
</div>
<div class="example"><div class="fignone"><img src="./delta.gif" alt="Start of change" /><pre>5722ST1 V5R4M0 060210    Create SQL ILE RPG Object         RPGLEEX               08/06/02 16:03:02   Page   4
  124        C                   END                                                        12400
  125        C     DONE2         TAG                                                        12500
  126        C/EXEC SQL CLOSE C2                                                            12600
  127        C/END-EXEC                                                                     12700
  128        C                   RETURN                                                     12800
  129        C*                                                                             12900
  130        C* Error occurred while updating table.  Inform user and rollback               13000
  131        C* changes.                                                                    13100
  132        C*                                                                             13200
  133        C     UPDERR        TAG                                                        13300
  134        C                   EXCEPT    RECE                                             13400
  135     <span><span>13</span></span> C/EXEC SQL WHENEVER SQLERROR CONTINUE                                          13500
  136        C/END-EXEC                                                                     13600
  137        C*                                                                             13700
  138     <span><span>14</span></span> C/EXEC SQL                                                                     13800
  139        C+   ROLLBACK                                                                  13900
  140        C/END-EXEC                                                                     14000
  141        C                   RETURN                                                     14100
  142        C*                                                                             14200
  143        C* Error occurred while generating reports.  Inform user and exit.              14300
  144        C*                                                                             14400
  145        C     RPTERR        TAG                                                        14500
  146        C                   EXCEPT    RECF                                             14600
  147        C*                                                                             14700
  148        C* All done.                                                                   14800
  149        C*                                                                             14900
  150        C     FINISH        TAG                                                        15000
  151        OQPRINT    E            RECA        0  2 01                                    15100
  152        O                                           42 'REPORT OF PROJECTS AFFEC'      15200
  153        O                                           64 'TED BY EMPLOYEE RAISES'        15300
  154        O          E            RECA        0  1                                       15400
  155        O                                            7 'PROJECT'                       15500
  156        O                                           17 'EMPLOYEE'                      15600
  157        O                                           32 'EMPLOYEE NAME'                 15700
  158        O                                           60 'SALARY'                        15800
  159        O          E            RECB        0  1                                       15900
  160        O                       PROJNO               6                                 16000
  161        O                       EMPNO               15                                 16100
  162        O                       NAME                50                                 16200
  163        O                       SALARY        L     61                                 16300
  164        O          E            RECC        2  2                                       16400
  165        O                                           42 'ACCUMULATED STATISTIC'         16500
  166        O                                           54 'S BY PROJECT'                  16600
  167        O          E            RECC        0  1                                       16700
  168        O                                            7 'PROJECT'                       16800
  169        O                                           56 'NUMBER OF'                     16900
  170        O                                           67 'TOTAL'                         17000
  171        O          E            RECC        0  2                                       17100
  172        O                                            6 'NUMBER'                        17200
  173        O                                           21 'PROJECT NAME'                  17300
  174        O                                           56 'EMPLOYEES'                     17400
  175        O                                           66 'COST'                          17500
  176        O          E            RECD        0  1                                       17600
  177        O                       PRJNUM               6                                 17700
  178        O                       PNAME               45                                 17800
  179        O                       EMPCNT        L     54                                 17900
  180        O                       PRCOST        L     70                                 18000
  181        O          E            RECE        0  1                                       18100
  182        O                                           28 '*** ERROR Occurred while'      18200
  183        O                                           52 ' updating table. SQLCODE'      18300
  184        O                                           53 '='                             18400
  185        O                       SQLCOD        L     62                                 18500
  186        O          E            RECF        0  1                                       18600
  187        O                                           28 '*** ERROR Occurred while'      18700
  188        O                                           52 ' generating reports. SQL'      18800
  189        O                                           57 'CODE='                         18900
  190        O                       SQLCOD        L     67                                 19000
                                * * * * *  E N D  O F  S O U R C E  * * * * *</pre><img src="./deltaend.gif" alt="End of change" />
</div>
</div>
<div class="example"><div class="fignone"><img src="./delta.gif" alt="Start of change" /><pre>5722ST1 V5R4M0 060210        Create SQL ILE RPG Object         RPGLEEX            08/06/02 16:03:02   Page   5
CROSS REFERENCE
Data Names                    Define    Reference
ACTNO                            62       SMALL INTEGER PRECISION(4,0) COLUMN (NOT NULL) IN CORPDATA.EMPPROJACT
BIRTHDATE                        42       DATE(10) COLUMN IN CORPDATA.EMPLOYEE
BONUS                            42       DECIMAL(9,2) COLUMN IN CORPDATA.EMPLOYEE
COMM                             ****     COLUMN
                                          42 62
COMM                             42       DECIMAL(9,2) COLUMN IN CORPDATA.EMPLOYEE
COMMI                            25       DECIMAL(7,2)
                                          42 62
CORPDATA                         ****     COLLECTION
                                          42 62 62 99 99 99
C1                               62       CURSOR
                                          71 80 86
C2                               99       CURSOR
                                          112 120 126
DEPTNO                           8        CHARACTER(3) IN RPT1
DEPTNO                           99       CHARACTER(3) COLUMN (NOT NULL) IN CORPDATA.PROJECT
DONE1                            85
DONE1                            ****     LABEL
                                          77
DONE2                            125
DONE2                            ****     LABEL
                                          117
EDLEVEL                          42       SMALL INTEGER PRECISION(4,0) COLUMN (NOT NULL) IN CORPDATA.EMPLOYEE
EMENDATE                         62       DATE(10) COLUMN IN CORPDATA.EMPPROJACT
EMENDATE                         ****     COLUMN
                                          99
EMPCNT                           20       SMALL INTEGER PRECISION(4,0) IN RPT2
EMPLOYEE                         ****     TABLE IN CORPDATA
                                          42 62 99
EMPLOYEE                         ****     TABLE
                                          62 99
EMPNO                            11       CHARACTER(6) DBCS-open
                                          80
EMPNO                            42       CHARACTER(6) COLUMN (NOT NULL) IN CORPDATA.EMPLOYEE
EMPNO                            ****     COLUMN IN EMPPROJACT
                                          62 62 62 99
EMPNO                            ****     COLUMN IN EMPLOYEE
                                          62 99
EMPNO                            62       CHARACTER(6) COLUMN (NOT NULL) IN CORPDATA.EMPPROJACT
EMPPROJACT                       ****     TABLE
                                          62 62 99 99 99 99
EMPPROJACT                       ****     TABLE IN CORPDATA
                                          62 99
EMPTIME                          62       DECIMAL(5,2) COLUMN IN CORPDATA.EMPPROJACT
EMPTIME                          ****     COLUMN
                                          99
EMSTDATE                         62       DATE(10) COLUMN IN CORPDATA.EMPPROJACT
EMSTDATE                         ****     COLUMN
                                          99
FINISH                           150
FIRSTNME                         42       VARCHAR(12) COLUMN (NOT NULL) IN CORPDATA.EMPLOYEE
FIRSTNME                         ****     COLUMN
                                          62
HIREDATE                         42       DATE(10) COLUMN IN CORPDATA.EMPLOYEE
JOB                              42       CHARACTER(8) COLUMN IN CORPDATA.EMPLOYEE
LASTNAME                         42       VARCHAR(15) COLUMN (NOT NULL) IN CORPDATA.EMPLOYEE
LASTNAME                         ****     COLUMN
                                          62
MAJPROJ                          8        CHARACTER(6) IN RPT1
MAJPROJ                          99       CHARACTER(6) COLUMN IN CORPDATA.PROJECT
MIDINIT                          42       CHARACTER(1) COLUMN (NOT NULL) IN CORPDATA.EMPLOYEE
NAME                             12       CHARACTER(30) DBCS-open
                                          80
PERCNT                           27       DECIMAL(7,2)
                                          42
PHONENO                          42       CHARACTER(4) COLUMN IN CORPDATA.EMPLOYEE
PNAME                            19       CHARACTER(36) DBCS-open IN RPT2
PRCOST                           21       DECIMAL(9,2) IN RPT2
PRENDATE                         8        DATE(8) IN RPT1</pre><img src="./deltaend.gif" alt="End of change" />
</div>
</div>
<div class="example"><div class="fignone"><img src="./delta.gif" alt="Start of change" /><pre>5722ST1 V5R4M0 060210    Create SQL ILE RPG Object         RPGLEEX              08/06/02 16:03:02   Page   6
PRENDATE                         ****     COLUMN
                                          99
PRENDATE                         99       DATE(10) COLUMN IN CORPDATA.PROJECT
PRJNUM                           18       CHARACTER(6) DBCS-open IN RPT2
CROSS REFERENCE
PROJECT                          ****     TABLE IN CORPDATA
                                          99
PROJECT                          ****     TABLE
                                          99
PROJNAME                         8        VARCHAR(24) IN RPT1
PROJNAME                         ****     COLUMN
                                          99 99
PROJNAME                         99       VARCHAR(24) COLUMN (NOT NULL) IN CORPDATA.PROJECT
PROJNO                           8        CHARACTER(6) IN RPT1
                                          80
PROJNO                           ****     COLUMN
                                          62 62
PROJNO                           62       CHARACTER(6) COLUMN (NOT NULL) IN CORPDATA.EMPPROJACT
PROJNO                           ****     COLUMN IN EMPPROJACT
                                          99 99 99
PROJNO                           ****     COLUMN IN PROJECT
                                          99
PROJNO                           99       CHARACTER(6) COLUMN (NOT NULL) IN CORPDATA.PROJECT
PRSTAFF                          8        DECIMAL(5,2) IN RPT1
PRSTAFF                          99       DECIMAL(5,2) COLUMN IN CORPDATA.PROJECT
PRSTDATE                         8        DATE(8) IN RPT1
PRSTDATE                         99       DATE(10) COLUMN IN CORPDATA.PROJECT
RDATE                            26       CHARACTER(10) DBCS-open
                                          99
RESPEMP                          8        CHARACTER(6) IN RPT1
RESPEMP                          99       CHARACTER(6) COLUMN (NOT NULL) IN CORPDATA.PROJECT
RPTERR                           145
RPTERR                           ****     LABEL
                                          53
RPT1                             8        STRUCTURE
RPT2                             17       STRUCTURE
                                          120
SALARY                           13       DECIMAL(9,2)
                                          80
SALARY                           ****     COLUMN
                                          42 42 62 99
SALARY                           42       DECIMAL(9,2) COLUMN IN CORPDATA.EMPLOYEE
SEX                              42       CHARACTER(1) COLUMN IN CORPDATA.EMPLOYEE
UPDERR                           133
UPDERR                           ****     LABEL
                                          39
WORKDEPT                         42       CHARACTER(3) COLUMN IN CORPDATA.EMPLOYEE
WRKDAY                           24       SMALL INTEGER PRECISION(4,0)
                                          99
No errors found in source
   190 Source records processed
                               * * * * *  E N D  O F  L I S T I N G  * * * * *</pre><img src="./deltaend.gif" alt="End of change" />
</div>
</div>
</div>
<div>
<div class="familylinks">
<div class="parentlink"><strong>Parent topic:</strong> <a href="rzajpsamprog.htm" title="This topic contains a sample application showing how to code SQL statements in each of the languages supported by the DB2 UDB for iSeries system.">Sample programs using DB2 UDB for iSeries statements</a></div>
</div>
<div class="relconcepts"><strong>Related concepts</strong><br />
<div><a href="rzajprpgi.htm" title="This topic describes the unique application and coding requirements for embedding SQL statements in an ILE RPG program. The coding requirements for host variables are defined.">Code SQL statements in ILE RPG applications</a></div>
</div>
</div>
</body>
</html>