RPG with SQL
RPG can use SQL to add flexibility.
SQL Options
SET OPTION overrides compile keywords in the same manner as CTL-OPT does for RPG compiler options. It also offers some keywords that are not on the CRTBNDPGM command.
Exec sql
set option
Commit = *None,
Naming = *Sys,
DECRESULT = (63, 63, 0),
CLOSQLCSR = *ENDMOD,
TIMFMT = *USA,
DatFmt = *ISO
COMPILEOPT = 'OPTION(*SRCSTMT *NODEBUGIO *NOUNREF) +
ALWNULL(*USRCTL) +
DFTACTGRP(*NO) +
BNDDIR(*LIBL/TEST) +
DBGVIEW(*LIST)' ;
SET OPTIONS
| Keyword | Description |
|---|---|
| ALWBLK | pecifies whether the database manager can use row blocking |
| ALWCPYDTA | Specifies whether a copy of the data can be used in a SELECT statement. |
| CLOSQLCSR | Specifies when SQL cursors are implicitly closed |
| COMMIT | determines use of commitment control within the program |
| COMPILEOPT | overrides/specifies compiler options |
| CONACC | Specifies the concurrent access resolution to use for select statements |
| DATFMT | determines the format for dates |
| DATSEP | determines the format for date separator characters |
| DBGVIEW | allows you to specify *NONE, *SOURCE, *STMT and *LIST |
| DECFLTRND | Specifies the DECFLOAT rounding mode used for static SQL statements |
| DECMPT | Specifies the symbol that you want to represent the decimal point |
| DECRESULT | Specifies the precision & scale, and minimum divide scale used for decimal operations |
| DFTRDBCOL | default schema name used for the unqualified names of tables, views, indexes, and SQL packages |
| DLYPRP | Specifies whether the dynamic statement validation for a PREPARE statement is delayed |
| DYNDFTCOL | schema name specified for the DFTRDBCOL parameter is also used for dynamic statements |
| DYNUSRPRF | the user profile to be used for dynamic SQL statements |
| EVENTF | Specifies whether an event file will be generated |
| EXTIND | specifies how to treat indicator variable values passed for SQL statements |
| LANGID | the language identifier to be used when SRTSEQ(*LANGIDUNQ) or SRTSEQ(*LANGIDSHR) is specified |
| MONITOR | specifies whether the statements should be identified as user or system, when a database monitor is run |
| NAMING | Specifies naming convention to be used (*SYS or *SQL) |
| OPTLOB | specifies whether accesses to XML and LOBs can be optimized when accessing through DRDA |
| OUTPUT | specifies whether the precompiler and compiler listings are generated. |
| RDBCNNMTH | Specifies the semantics used for CONNECT statements |
| SQLCA | specifies whether the fields in an SQLCA will be set after each SQL statement. |
| SQLCURRULE | specifies the semantics used for SQL statements (*DB2 or *STD) |
| SQLPATH | Specifies the path to be used to find procedures, functions, and user defined types in static SQL statements |
| SRTSEQ | secifies the collating sequence table to be used for string comparisons in SQL statements |
| TGTRLS | specifies the release of the operating system (VxRxMx) |
| TIMFMT | determines the format for the time used when accessing time result columns |
| TIMSEP | determines the format for time separators |
| USRPRF | the user profile to be used when the program is run, including the authority |
This is an example of a program that has SQL instructions embedded in the RPG source code.
ctl-Opt DEBUG(*YES) OPTION(*SRCSTMT : *NODEBUGIO)
DFTACTGRP(*NO) Main(CSG076RP) EXTBININT(*YES)
ActGrp('DLYRPT') BndDir( 'QC2LE' );
********************************************************************
* Program Name : CSG076RP *
* Program Description : CSG NCOA return summary *
* Date : 1/22/19 *
* Author : Steve Croy *
********************************************************************
* *
* Modification Log *
* *
* Project Marker Date Description *
* *
********************************************************************
Dcl-F CSG076PR printer(*ext) oflind(*in76) usropn ;
Dcl-PR Exit Extproc('exit') ;
*N uns(3:0) value ;
End-PR;
Dcl-DS textRecords Occurs(100);
inCode char(4) ;
codTxt char(50) ;
trans zoned(7:0) ;
charge zoned(7:2) ;
END-DS;
Dcl-C SQLstsOK '00000' ;
Dcl-C SQLstsEOF '02000' ;
Dcl-S rows int(10) ;
Dcl-S rowCount int(10) ;
Dcl-S x int(10) ;
Dcl-S billable zoned(9:2) ;
Dcl-S records int(10) ;
Dcl-S firstDate date ;
Dcl-S lastDate date ;
Dcl-S dateToday date ;
Exec SQL
set option
Commit = *None,
Naming = *Sys,
DECRESULT = (63, 63, 0),
datfmt = *ISO ;
Dcl-Proc CSG076RP ;
Dcl-PI *N extPgm('CSG076RP') ;
startDate char(8) const options(*NoPASS) ;
EndDate char(8) const options(*NoPASS) ;
END-PI;
Monitor ;
If %parms < 2 ;
firstDate = %date() ;
lastDate = firstDate + %days(1) ;
Else ;
firstDate = %date(startDate:*ISO0) ;
lastDate = %date(endDate:*ISO0) ;
Endif ;
On-error ;
exit(0) ;
EndMon ;
Clear *all TextRecords ;
rowCount = 0 ;
records = 0 ;
Exec SQL Close csg076c1 ;
//* create cursor for report
Exec SQL
Declare csg076c1 insensitive cursor for
( Select
a.ncrtnc, a.ncdesc, count(*) as returns,
sum(a.ncchrg) as charge
From csgrtnpf a, csgncapf b
Where a.ncrtnc = b.ncnrtn and date(b.ncents)
Between :firstDate and :lastDate
Group By a.ncrtnc, a.ncdesc ) ;
Exec SQL Open csg076c1 ;
EXEC SQL
get diagnostics :rows = db2_number_rows;
If not %open(csg076pr) ;
open csg076pr ;
EndIf ;
write csg076h1 ; // Write title line
write csg076h2 ; // Write report heading
If rows > 0 ;
Exec SQL fetch csg076c1 for :rows rows into :textRecords ;
Exec SQL Close csg076c1 ;
For x = 1 to rows ;
%occur(textRecords) = x ;
write csg076d1 ;
billable += charge ;
EndFor ;
EndIf;
write csg076t1 ; // Write total line
IF %open(csg076pr) ;
close csg076pr ;
EndIf ;
*InLR = *On;
exit(0) ;
Return ;
End-Proc CSG076RP ;