Example: SQL statements in ILE RPG programs
This example program is written in the ILE RPG programming language.
Note: By using the code examples, you agree to the terms
of the Code license and disclaimer information.
xxxxST1 VxRxMx yymmdd Create SQL ILE RPG Object RPGLEEX 11/11/13 11:23:30 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............VxRxMx
INCLUDE file..............*SRCFILE
Commit....................*CHG
Allow copy of data........*OPTIMIZE
Close SQL cursor..........*ENDMOD
Allow blocking............*ALLREAD
Delay PREPARE.............*NO
Concurrent access
resolution..............*DFT
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
Debugging encryption key..*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.........37
Job CCSID.................37
Decimal result options:
Maximum precision.......31
Maximum scale...........31
Minimum divide scale....0
DECFLOAT rounding mode....*HALFEVEN
Compiler options..........*NONE
Source member changed on 11/11/13 11:20:02
xxxxST1 VxRxMx yymmdd Create SQL ILE RPG Object RPGLEEX 11/11/13 11:23:30 Page 2
Record *...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 ...+... 8 SEQNBR Last change Comments
1 CTL-OPT; 000100
2 // File declaration for QPRINT 000200
3 // 000300
4 DCL-F QPRINT PRINTER(132) USAGE(*OUTPUT); 000400
5 // 000500
6 // Structure for report 1. 000600
7 // 000700
8 1 DCL-DS RPT1 EXT EXTNAME('CORPDATA/PROJECT'); 000800
9 END-DS; 000900
10 // 001000
11 DCL-DS *N; 001100
12 EMPNO CHAR(6); 001200
13 NAME CHAR(30); 001300
14 SALARY PACKED(9:2); 001400
15 END-DS; 001500
16 // 001600
17 // Structure for report 2. 001700
18 // 001800
19 DCL-DS RPT2; 001900
20 PRJNUM CHAR(6); 002000
21 PNAME CHAR(36); 002100
22 EMPCNT BINDEC(4:0); 002200
23 PRCOST PACKED(9:2); 002300
24 END-DS; 002400
25 // 002500
26 DCL-DS *N; 002600
27 WRKDAY BINDEC(4:0); 002700
28 COMMI PACKED(7:2); 002800
29 RDATE CHAR(10); 002900
30 PERCNT PACKED(7:2); 003000
31 END-DS; 003100
32 // 003200
33 2 WRKDAY = 253; 003300
34 COMMI = 2000.00; 003400
35 PERCNT = 1.04; 003500
36 RDATE = '1982-06-01'; 003600
37 // 003700
38 // Update the selected projects by the new percentage. If an 003800
39 // error occurs during the update, roll back the changes. 003900
40 // 004000
41 3 EXEC SQL WHENEVER SQLERROR GOTO UPDERR; 004100
42 // 004200
43 EXEC SQL 004300
44 4 UPDATE CORPDATA/EMPLOYEE 004400
45 SET SALARY = SALARY * :PERCNT 004500
46 WHERE COMM >= :COMMI; 004600
47 // 004700
48 // Commit changes. 004800
49 // 004900
50 5 EXEC SQL COMMIT; 005000
51 // 005100
52 EXEC SQL WHENEVER SQLERROR GO TO RPTERR; 005200
53 // 005300
54 // Report the updated statistics for each employee assigned to 005400
55 // selected projects. 005500
56 // 005600
57 // Write out the header for report 1. 005700
58 // 005800
59 EXCEPT RECA; 005900
60 6 EXEC SQL DECLARE C1 CURSOR FOR 006000
61 SELECT DISTINCT PROJNO, EMPPROJACT.EMPNO, 006100
62 LASTNAME||', '||FIRSTNME, SALARY 006200
63 FROM CORPDATA/EMPPROJACT, CORPDATA/EMPLOYEE 006300
64 WHERE EMPPROJACT.EMPNO = EMPLOYEE.EMPNO AND 006400
65 COMM >= :COMMI 006500
66 ORDER BY PROJNO, EMPNO; 006600
67 // 006700
68 7 EXEC SQL OPEN C1; 006800
xxxxST1 VxRxMx yymmdd Create SQL ILE RPG Object RPGLEEX 11/11/13 11:23:30 Page 3
69 // 006900
70 // Fetch and write the rows to QPRINT. 007000
71 // 007100
72 8 EXEC SQL WHENEVER NOT FOUND GO TO DONE1; 007200
73 DOU SQLCOD <> 0; 007300
74 9 EXEC SQL FETCH C1 INTO :PROJNO, :EMPNO, :NAME, :SALARY; 007400
75 EXCEPT RECB; 007500
76 ENDDO; 007600
77 C DONE1 TAG 007700
78 10 EXEC SQL CLOSE C1; 007800
79 // 007900
80 // For all project ending at a date later than the raise date 008000
81 // (that is, those projects potentially affected by the salary raises), 008100
82 // generate a report containing the project number, project name, 008200
83 // the count of employees participating in the project, and the 008300
84 // total salary cost of the project. 008400
85 // 008500
86 // Write out the header for report 2. 008600
87 // 008700
88 EXCEPT RECC; 008800
89 EXEC SQL 008900
90 11 DECLARE C2 CURSOR FOR 009000
91 SELECT EMPPROJACT.PROJNO, PROJNAME, COUNT(*), 009100
92 SUM((DAYS(EMENDATE) - DAYS(EMSTDATE)) * EMPTIME * 009200
93 DECIMAL((SALARY/:WRKDAY),8,2)) 009300
94 FROM CORPDATA/EMPPROJACT, CORPDATA/PROJECT, CORPDATA/EMPLOYEE 009400
95 WHERE EMPPROJACT.PROJNO = PROJECT.PROJNO AND 009500
96 EMPPROJACT.EMPNO = EMPLOYEE.EMPNO AND 009600
97 PRENDATE > :RDATE 009700
98 GROUP BY EMPPROJACT.PROJNO, PROJNAME 009800
99 ORDER BY 1; 009900
100 // 010000
101 EXEC SQL OPEN C2; 010100
102 // 010200
103 // Fetch and write the rows to QPRINT. 010300
104 // 010400
105 EXEC SQL WHENEVER NOT FOUND GO TO DONE2; 010500
106 DOU SQLCOD <> 0; 010600
107 12 EXEC SQL FETCH C2 INTO :RPT2; 010700
108 EXCEPT RECD; 010800
109 ENDDO; 010900
110 C DONE2 TAG 011000
111 EXEC SQL CLOSE C2; 011100
112 C GOTO FINISH 011200
113 // 011300
114 // Error occured while updating table. Inform user and rollback 011400
115 // changes. 011500
116 // 011600
117 C UPDERR TAG 011700
118 EXCEPT RECE; 011800
119 13 EXEC SQL WHENEVER SQLERROR CONTINUE; 011900
120 // 012000
121 14 EXEC SQL ROLLBACK; 012100
122 C GOTO FINISH 012200
123 // 012300
124 // Error occured while generating reports. Inform user and exit. 012400
125 // 012500
126 C RPTERR TAG 012600
127 EXCEPT RECF; 012700
128 // 012800
129 // All done. 012900
130 // 013000
131 C FINISH TAG 013100
132 *INLR = *ON; 013200
133 OQPRINT E RECA 0 2 01 013300
134 O 45 'REPORT OF PROJECTS AFFEC' 013400
135 O 58 'TED BY RAISES' 013500
136 O E RECA 0 1 013600
137 O 7 'PROJECT' 013700
138 O 14 'EMPID' 013800
139 O 32 'EMPLOYEE NAME' 013900
140 O 59 'SALARY' 014000
xxxxST1 VxRxMx yymmdd Create SQL ILE RPG Object RPGLEEX 11/11/13 11:23:30 Page 4
141 O E RECB 0 1 014100
142 O PROJNO 6 014200
143 O EMPNO 15 014300
144 O NAME 49 014400
145 O SALARY L 61 014500
146 O E RECC 2 2 014600
147 O 42 'ACCUMULATED STATISTIC' 014700
148 O 54 'S BY PROJECT' 014800
149 O E RECC 0 1 014900
150 O 7 'PROJECT' 015000
151 O 56 'NUMBER OF' 015100
152 O 67 'TOTAL' 015200
153 O E RECC 0 2 015300
154 O 6 'NUMBER' 015400
155 O 21 'PROJECT NAME' 015500
156 O 56 'EMPLOYEES' 015600
157 O 66 'COST' 015700
158 O E RECD 0 1 015800
159 O PRJNUM 6 015900
160 O PNAME 45 016000
161 O EMPCNT L 54 016100
162 O PRCOST L 70 016200
163 O E RECE 0 1 016300
164 O 28 '*** ERROR Occurred while' 016400
165 O 52 ' updating table. SQLCODE' 016500
166 O 53 '=' 016600
167 O SQLCOD L 62 016700
168 O E RECF 0 1 016800
169 O 28 '*** ERROR Occurred while' 016900
170 O 52 ' generating reports. SQL' 017000
171 O 57 'CODE=' 017100
172 O SQLCOD L 67 017200
* * * * * E N D O F S O U R C E * * * * * xxxxST1 VxRxMx yymmdd Create SQL ILE RPG Object RPGLEEX 11/11/13 11:23:30 Page 5
CROSS REFERENCE
Data Names Define Reference
ACTNO 63 SMALL INTEGER PRECISION(4,0) COLUMN (NOT NULL) IN CORPDATA.EMPPROJACT
BIRTHDATE 63 DATE(10) COLUMN IN CORPDATA.EMPLOYEE
BONUS 63 DECIMAL(9,2) COLUMN IN CORPDATA.EMPLOYEE
COMM **** COLUMN
46 65
COMM 63 DECIMAL(9,2) COLUMN IN CORPDATA.EMPLOYEE
COMMI 28 DECIMAL(7,2)
46 65
CORPDATA **** SCHEMA
44 63 63 94 94 94
C1 60 CURSOR
68 74 78
C2 90 CURSOR
101 107 111
DEPTNO 8 CHARACTER(3) IN RPT1
DEPTNO 94 CHARACTER(3) CCSID 37 COLUMN (NOT NULL) IN CORPDATA.PROJECT
DONE1 77 LABEL
72
DONE2 110 LABEL
105
EDLEVEL 63 SMALL INTEGER PRECISION(4,0) COLUMN (NOT NULL) IN CORPDATA.EMPLOYEE
EMENDATE 63 DATE(10) COLUMN IN CORPDATA.EMPPROJACT
EMENDATE **** COLUMN
92
EMPCNT 22 SMALL INTEGER PRECISION(4,0) IN RPT2
EMPLOYEE **** TABLE IN CORPDATA
44 63 94
EMPLOYEE **** TABLE
64 96
EMPNO 12 CHARACTER(6)
74
EMPNO **** COLUMN IN EMPPROJACT
64 66 96
EMPNO **** COLUMN IN EMPLOYEE
64 96
EMPNO 63 CHARACTER(6) CCSID 37 COLUMN (NOT NULL) IN CORPDATA.EMPPROJACT
EMPNO 63 CHARACTER(6) CCSID 37 COLUMN (NOT NULL) IN CORPDATA.EMPLOYEE
EMPPROJACT **** TABLE IN CORPDATA
63 94
EMPPROJACT **** TABLE
64 95 96 98
EMPTIME 63 DECIMAL(5,2) COLUMN IN CORPDATA.EMPPROJACT
CROSS REFERENCE
EMPTIME **** COLUMN
92
EMSTDATE 63 DATE(10) COLUMN IN CORPDATA.EMPPROJACT
EMSTDATE **** COLUMN
92
FINISH 131 LABEL
FIRSTNME **** COLUMN
62
FIRSTNME 63 VARCHAR(12) CCSID 37 COLUMN (NOT NULL) IN CORPDATA.EMPLOYEE
HIREDATE 63 DATE(10) COLUMN IN CORPDATA.EMPLOYEE
JOB 63 CHARACTER(8) CCSID 37 COLUMN IN CORPDATA.EMPLOYEE
LASTNAME **** COLUMN
62
LASTNAME 63 VARCHAR(15) CCSID 37 COLUMN (NOT NULL) IN CORPDATA.EMPLOYEE
MAJPROJ 8 CHARACTER(6) IN RPT1
MAJPROJ 94 CHARACTER(6) CCSID 37 COLUMN IN CORPDATA.PROJECT
MIDINIT 63 CHARACTER(1) CCSID 37 COLUMN (NOT NULL) IN CORPDATA.EMPLOYEE
NAME 13 CHARACTER(30)
74
PERCNT 30 DECIMAL(7,2)
45
PHONENO 63 CHARACTER(4) CCSID 37 COLUMN IN CORPDATA.EMPLOYEE
PNAME 21 CHARACTER(36) IN RPT2
PRCOST 23 DECIMAL(9,2) IN RPT2
PRENDATE 8 DATE(8) IN RPT1
PRENDATE **** COLUMN
97
PRENDATE 94 DATE(10) COLUMN IN CORPDATA.PROJECT
PRJNUM 20 CHARACTER(6) IN RPT2
xxxxST1 VxRxMx yymmdd Create SQL ILE RPG Object RPGLEEX 11/11/13 11:23:30 Page 6
CROSS REFERENCE
PROJECT **** TABLE IN CORPDATA
94
PROJECT **** TABLE
95
PROJNAME 8 VARCHAR(24) IN RPT1
PROJNAME **** COLUMN
91 98
PROJNAME 94 VARCHAR(24) CCSID 37 COLUMN (NOT NULL) IN CORPDATA.PROJECT
PROJNO 8 CHARACTER(6) IN RPT1
74
PROJNO **** COLUMN
61 66
PROJNO 63 CHARACTER(6) CCSID 37 COLUMN (NOT NULL) IN CORPDATA.EMPPROJACT
PROJNO **** COLUMN IN EMPPROJACT
95 98
PROJNO **** COLUMN IN PROJECT
95
PROJNO 94 CHARACTER(6) CCSID 37 COLUMN (NOT NULL) IN CORPDATA.PROJECT
PRSTAFF 8 DECIMAL(5,2) IN RPT1
PRSTAFF 94 DECIMAL(5,2) COLUMN IN CORPDATA.PROJECT
PRSTDATE 8 DATE(8) IN RPT1
PRSTDATE 94 DATE(10) COLUMN IN CORPDATA.PROJECT
RDATE 29 CHARACTER(10)
97
RESPEMP 8 CHARACTER(6) IN RPT1
RESPEMP 94 CHARACTER(6) CCSID 37 COLUMN (NOT NULL) IN CORPDATA.PROJECT
RPTERR 126 LABEL
52
RPT1 8 STRUCTURE
RPT2 19 STRUCTURE
107
SALARY 14 DECIMAL(9,2)
74
SALARY **** COLUMN
45 45 62 93
SALARY 63 DECIMAL(9,2) COLUMN IN CORPDATA.EMPLOYEE
SEX 63 CHARACTER(1) CCSID 37 COLUMN IN CORPDATA.EMPLOYEE
UPDERR 117 LABEL
41
WORKDEPT 63 CHARACTER(3) CCSID 37 COLUMN IN CORPDATA.EMPLOYEE
WRKDAY 27 SMALL INTEGER PRECISION(4,0)
93
No errors found in source
172 Source records processed
* * * * * E N D O F L I S T I N G * * * * *