1	Embedded SQL in RPG
2	Agenda Overview Advantages to using Embedded SQL Preprocessor Rules for Embedding SQL Static SQL & Dynamic SQL Execute Immediate & Prepare/Execute Serial & Scrollable Cursors Error Handling Debug/Performance
3	Goals & Assumptions You come out of this session with a concise set of examples of how to use the most common types of Embedded SQL. We’ll cover most commonly used techniques. There are a number of additional things you can do with SQL in RPG programs that we will not cover – remote databases, use of the SQL Descriptor Area, Large Objects We'll focus solely on RPGLE programs, though the principles and much of the syntax are the same or close to the same for other languages. I’m assuming a basic knowledge of SQL syntax
4	Notes on the Sample Code All programs are very trivial, designed solely to illustrate the SQL being discussed Database file PFDATA is used – the result of a DSPOBJD *OUTFILE Error handling is either minimal or not included All sample code will compile and execute as long as the PFDATA file exists – anything else you need to do to compile it will be specified in the source All sample code is available for download on my website – www.gamacheconsulting.com
5	Advantages Can make your programs easier to understand Can reduce need to create logical files for selection & sorting Simplify program logic by selecting only the necessary fields, in the sort order you need them Industry standard – once you’re comfortable with iSeries SQL you’ll be able to read SQL in SQL Server, Oracle or most other versions of SQL
6	The Pre-processor Preprocessor is part of DB2 Query Manager and SQL Development Kit which is required to compile a program with embedded SQL (This is a separate chargeable product) Source code containing Embedded SQL is run through the SQL preprocessor before creating the program object It replaces SQL with calls to SQL functions Creates an output source file QSQLTEMP1 in QTEMP – SQL is commented out and replaced by calls to SQL routines If the pre-process fails, you’ll get a listing with the results – similar to an RPG compile listing. Run time support for programs with embedded SQL is included in the base OS
7	Syntax Rules Enter on C specs Start with /EXEC SQL with the slash in position 7 Continuation lines have a + in position 7 End it with /END-EXEC Source type must by SQLRPGLE or SQLRPG Use a capital C and you can press F4 to prompt it – won’t prompt with a lower case C
8	SQLCA Data Structure An SQLCA is a set of variables that is updated at the end of the execution of every SQL statement. Brought in automatically by the compiler (when type = SQLRPGxx) DS updated when a SQL statement completes – successfully or unsuccessfully Key fields for our discussion are: SQLCOD = result code SQLSTT = result state -– message number SQLER3 = Number of rows modified by the statement Complete details in the SQL Reference book – Appendix B
9	Compile Options Commitment Control option Only affects SQL commands, not RPG Updates, etc. If it’s not *NONE – then your tables had better be journalled Precompiler options to specify the naming convention used If you are connecting to non-iSeries databases, you must use SQL naming
10	Compile Options Create SQL ILE RPG Object (CRTSQLRPGI) Type choices, press Enter. Object . . . . . . . . . . . . . > SQL1 Name Library . . . . . . . . . . . > CURLIB Name, CURLIB Source file . . . . . . . . . . > QRPGLESRC Name, QRPGLESRC Library . . . . . . . . . . . > CURLIB Name, LIBL, CURLIB Source member . . . . . . . . . > SQL1 Name, OBJ Commitment control . . . . . . . CHG CHG, ALL, CS, NONE... Compile type . . . . . . . . . . > PGM PGM, SRVPGM, MODULE Listing output . . . . . . . . . NONE NONE, PRINT Additional Parameters Precompiler options . . . . . . XREF XREF, NOXREF, GEN... SQL - for SQL naming convention collection-name.table-name SYS - for QSYS naming - library/file Debugging view . . . . . . . . . NONE SOURCE, NONE To source file . . . . . . . . . QSQLTEMP1 Name Library . . . . . . . . . . . QTEMP Name, LIBL, *CURLIB
11	Simple Update with Static SQL Simplest possible example Demonstrates the basic syntax Includes all the required elements SQL statements on the C specs Basic error handling checking the SQLSTT (SQL State) variable Same basic style for running any SQL statement that does NOT return data
12	Simple Update with Static SQL ************************************************************** * Sample program to run a single SQL statement using * static SQL ************************************************************** C/exec sql C+ update pfdata set ODSSZE = ODSSZE +1 C/end-exec /free if sqlstt <> '00000'; // handle the error condition else; // completed normally - show how many records were updated dsply sqler3; endif; inlr = on; /end-free
13	Basic Update using a Variable Only slightly more complex Useful for fix programs you have to run more than once Uses an RPG program variable in the SQL statement Variables are listed in the RPG as :varname Sample program call: CALL PGM(SQL2) PARM(‘TEST' 'MYLIB')
14	Basic Update using a Variable ************************************************************** * Sample program to run a single SQL statement using * static SQL with a parameter ************************************************************** D Parm1 s 6a D Parm2 s 10a C entry plist C parm parm1 C parm parm2 C/exec sql C+ update pfdata set ODDDAT = :parm1 C+ where ODLBNM = :parm2 C/end-exec /free if sqlstt <> '00000'; // handle the error condition else; // completed normally - show how many records were updated dsply sqler3; endif; inlr = *on; /end-free
15	Execute Immediate Useful when you need to build your SQL statement on the fly based on variables or parameters Need to include any quotes, etc – the SQL needs to be syntactically correct You should be able to paste it into an Interactive SQL session and execute it Get your quotes correct!
16	Execute Immediate ************************************************************** * Sample program to run a single SQL statement using an * EXECUTE IMMEDIATE after building the SQL on the fly ************************************************************ *** call sql3 '''abc''' DParm1 s 10a DSQLStmt s 100a inz('Update pfdata set - D odddat = ') C entry plist C parm parm1 /free // Build the SQL statement to be executed SQLStmt = %trim(SQLStmt) + parm1; /end-free C/exec sql C+ execute immediate :SQLStmt C/end-exec /free if sqlstt <> '00000'; // handle the error condition else; // completed normally - show how many records were updated dsply sqler3; endif; inlr = *on; /end-free
17	Prepare & Execute using a parameter marker Prepare is more efficient when you are running the SQL more than once Parameter markers in the SQL allow you to vary the statement Be careful with %TRIM – leave a blank between each SQL clause Using can be multiple variables, comma separated – cannot be array elements Make sure parms are listed in the same order as in the SQL
18	"************************************************************" ************************************************************ * Sample program to run multiple EXECUTE's after a single * PREPARE, for each record in an array * Each one is dynamically built based on the array and * a program parameter. ************************************************************ ************************************************************ * Replace the compile time array with your own library names * to test this program ************************************************************** D Parm1 s 5a D UpdLib s 10a D x s 3s 0 D msg s 50a D SQLStmt s 100a inz('Update pfdata set - D odssze = ') D SQLWhere s 50a inz(' where odlbnm = ?') D LibNames s 10a dim(3) perrcd(1) ctdata C *entry plist C parm parm1 /free // build base SQL statement with parameter marker SQLStmt = %trim(SQLStmt) + ' ' + parm1 + ' ' + %trim(SQLWhere); // It now looks like this: // Update pfdata set oddsze = 123 where odlbnm = ? /end-free // prepare that SQL statement C/exec sql C+ prepare SQLString from :SQLStmt C/end-exec
19	"/free" /free for x = 1 to 3; // EXECUTE does not like arrays, so move it to a char field updLib = libnames(x); /end-free * Execute SQL passing a value to be used for the parameter C/exec sql C+ execute SQLString using :updlib C/end-exec /free select; when sqlstt = '00000'; // completed normally - show how many records were updated msg = %char(sqler3) + ' records found for ' + updlib; dsply msg; when sqlstt = '02000'; // handle the no records found msg = 'No records found for ' + updlib; dsply msg; other; // some other error condition msg = 'Error ' + sqlstt + ' trying to update ' + updlib; dsply msg; endsl; endfor; inlr = on; /end-free C* ** CTDATA LibNames LibA LibB LIBC
20	Update Statement with Dynamic SQL Build a valid SQL string in code using RPG concatenation Use %Trim around any variables that are not variable length Build the string so that you can put a debug breakpoint after it’s built You want to be able to view that string and copy it into Interactive SQL to test it Execute it with execute immediate :SQLStmt Similar to the last example but this one uses Execute Immediate instead of a Prepare & Execute
21	"************************************************************" ************************************************************ * Sample program to run multiple EXECUTE IMMEDIATES * one, for each record in an array * Each one is dynamically built based on the array and * a program parameter. ************************************************************** DParm1 s 5a DUpdLib s 10a D x s 3s 0 D msg s 50a D SQLStmt s 200a * ------------------------------------------------------------- * File PFDATA was created by doing a DSPOBJD to an outfile * ------------------------------------------------------------- D SQLMain s 100a inz('Update pfdata set - D odssze = ') D SQLWhere s 50a inz(' where odlbnm = ') D LibNames s 10a dim(3) perrcd(1) ctdata C *entry plist C parm parm1 /free for x = 1 to 3; updLib = libnames(x); // Build the complete SQL statement SQLStmt = %trim(SQLMain) + parm1 + %trim(SQLWhere) + '''' + %trim(updlib) + ''''; /end-free
22	"C/exec sql" C/exec sql C+ execute immediate :SQLStmt C/end-exec /free select; when sqlstt = '00000'; // completed normally - show how many records were updated msg = %char(sqler3) + ' records found for ' + updlib; dsply msg; when sqlstt = '02000'; // handle the no records found msg = 'No records found for ' + updlib; dsply msg; other; // some other error condition msg = 'Error ' + sqlstt + ' trying to update ' + updlib; dsply msg; endsl; endfor; inlr = on; /end-free C* ** CTDATA LibNames LibA LibB Mylib
23	Select statement that returns a single row If your Select statement returns a single row, you can use Select Into Fields just need to be the correct data type (char or numeric, etc.) If you have a truncation issue, the SQLCOD/SQLSTT will tell you
24	Select statement that returns a single row ************************************************************** * Sample program with a SELECT statement that returns one record ************************************************************** D MaxSize s 15s 0 D AvgSize s 15s 0 C/exec sql C+ select avg(odobsz), max(odobsz) C+ into :avgsize, :maxsize from pfdata C/end-exec C if sqlstt <> '00000' * Handle the error C endif C C move on inlr
25	Using a Host Indicator to flag Null values or data errors The format is -> :host-identifier INDICATOR :host-indicator Can be used in SELECT INTO, FETCH, SET variable, VALUES INTO The indicator can specify the following: Null value was returned – indicator = -1 Null value due to data mapping error – indicator = -2 Characters could not be converted Numeric conversion error (underflow or overflow) Arithmetic expression error (division by 0) Date or timestamp conversion error (a date or timestamp that is not within the valid range of the dates for the specified format) String representation of the datetime value is not valid Mixed data not properly formed A numeric value that is not valid Argument of SUBSTR scalar function is out of range Original length of a truncated string Record the seconds portion of a time if the time is truncated on assignment to a host variable.
26	Using a Host Indicator to flag Null values or data errors D MaxSize s 15s 0 D AvgSize s 15s 0 D MaxTxt s 30a D DivVal s 15s 0 D Ind_avg s 5I 0 D Ind_max s 5I 0 D Ind_txt s 5I 0 D Ind_div s 5I 0 C/exec sql C+ select avg(odobsz), max(odobsz), max(odobtx), 9/0 C+ into :avgsize :Ind_avg, :maxsize :Ind_Max, C+ :maxtxt :Ind_txt, :divval :ind_div C+ from pfdata C/end-exec
27	Using a Cursor – Serial Vs. Scrollable A cursor is just a pointer to where you are in a file Two types of SQL cursors Serial Cursor Forward only Can be updateable Scrollable May change direction Updateable
28	Serial Cursors Each row is read only once per Open Open positions the cursor before the first row Fetch moves the cursor to the next row If the Fetch contains an “ INTO host variables” then the row’s values are loaded into those variables Normally you would repeat this until SQLCOD = 100 or SQLSTT = ‘02000’ which is End of File You would then Close the cursor You can reopen the cursor after closing it
29	Scrollable Cursor Other than the positioning options, the use of a scrollable cursor is very similar to a serial cursor NEXT Positions the cursor on the next row. This is the default if no position is specified. PRIOR Positions the cursor on the previous row. FIRST Positions the cursor on the first row. LAST Positions the cursor on the last row. BEFORE Positions the cursor before the first row. AFTER Positions the cursor after the last row. CURRENT Does not change the cursor position. RELATIVE n - Evaluates a host variable or integer n in relationship to the cursor’s current position. For example, if n is -1, the cursor is positioned on the previous row of the result table. If n is +3, the cursor is positioned three rows after the current row. Host variables cannot be specified for the BEFORE and AFTER position options. These are similar to a SETLL or SETGT FETCH AFTER FROM C1 will move the cursor After the last record in the file
30	Cursor Statements DECLARE CURSOR - statement to define and name the cursor and specify the rows to be retrieved with the embedded select statement This replaces the RPG F spec OPEN and CLOSE statements to open and close the cursor within the program. The cursor must be opened before any rows can be retrieved. FETCH statement to retrieve rows from the cursor’s result table or to position the cursor on another row. Equivalent to an RPG READ or READE UPDATE ... WHERE CURRENT OF statement to update the current row of a cursor. DELETE ... WHERE CURRENT OF statement to delete the current row of a cursor.
31	The Basics of Reading via SQL Use the external data structure to define the fields in the RPG Declare the cursor – must be prior to the Open in the source Open the cursor Fetch the next record and load data from the cursor into RPG variables Repeat that Fetch until End of File is found, indicated by SQLSTT = ‘02000’ Close the Cursor
32	"************************************************************" ************************************************************ * Sample program to read selected fields from file via SQL ************************************************************** * This defines all the fields in the record D InRec e ds extname(pfdata) C/exec sql C+ declare c1 cursor for C+ select odobnm, odlbnm from pfdata C/end-exec C/exec sql C+ open c1 C/end-exec C/exec sql C+ fetch c1 into :odobnm, :odlbnm C/end-exec C dou sqlstt <> '00000' * Do something with each record C/exec sql C+ fetch c1 into :odobnm, :odlbnm C/end-exec C enddo C C/exec sql C+ close c1 C/end-exec C move on inlr
33	Defining a Cursor DECLARE cursor-name INSENSITIVE DYNAMIC SCROLL CURSOR FOR SELECT column-1, column-2 ,... FROM table-name ,... WHERE column-1 = expression ... FOR UPDATE OF column-a, column-b ,... INSENSITIVE Specifies that once the cursor is opened, it does not have sensitivity to inserts, updates, or deletes performed by this or any other activation group. The cursor is read-only and a temporary result is created when the cursor is opened. The SELECT statement cannot contain a FOR UPDATE clause and the application must allow a copy of the data (ALWCPYDTA(OPTIMIZE) or ALWCPYDTA(YES)). SCROLL Specifies that the cursor is scrollable. The cursor may or may not have immediate sensitivity to inserts, updates, and deletes done by other activation groups. If DYNAMIC is not specified, the cursor is read-only. SELECT statement cannot contain a FOR UPDATE clause. DYNAMIC SCROLL - cursor is updateable if the result table is updateable, The cursor will usually have immediate sensitivity to inserts, updates, and deletes done by other application processes. There are some exceptions, check the SQL Reference manual for them (Chapter 5 – Declare Cursor)
34	Fetch For a Serial Cursor FETCH cursor-name INTO :host variable-1[, :host variable-2] ... For a Scrollable Cursor FETCH RELATIVE integer FROM cursor-name INTO :host variable-1[, :host variable-2] ...
35	Updating the Current Row UPDATE table-name SET column-1 = value [, column-2 = value] ... WHERE CURRENT OF cursor-name Updates only one row—the current row Requires that the columns updated be named previously in the FOR UPDATE OF clause of the DECLARE CURSOR statement, if an ORDER BY clause was also specified Cursor does not move with an Update
36	Updates via a Cursor On Select statement use “For Update of” followed by columns you want to update On Update statement use “Where Current of” followed by the Cursor name You can Update a column not listed in the Select clause
37	"************************************************************" ************************************************************ * Sample program to read selected fields from file via SQL ************************************************************** * This defines all the fields in the record D InRec e ds extname(pfdata) C/exec sql C+ declare c1 cursor for C+ select odobnm, odlbnm, odobtx from pfdata C+ for update of odobtx C/end-exec C/exec sql C+ open c1 C/end-exec C/exec sql C+ fetch c1 into :odobnm, :odlbnm, :odobtx C/end-exec C dou sqlstt <> '00000' C/exec sql C+ Update pfdata C+ set odobtx = ‘Fred’ C+ Where current of c1 C/end-exec C/exec sql C+ fetch c1 into :odobnm, :odlbnm C/end-exec C enddo C/exec sql C+ close c1 C/end-exec C move on inlr
38	Can’t Use FOR UPDATE OF when… The first FROM clause identifies more than one table or view. The first FROM clause identifies a read-only view. The first SELECT clause specifies the keyword DISTINCT. The first FROM clause identifies a user-defined table function. The outer subselect contains a GROUP BY clause. The outer subselect contains a HAVING clause. The first SELECT clause contains a column function. The select-statement contains a UNION or UNION ALL operator. The select-statement contains an ORDER BY clause, and the FOR UPDATE OF clause and DYNAMIC SCROLL are not specified. The select-statement includes a FOR FETCH ONLY clause. The SCROLL keyword is specified without DYNAMIC. The select list includes a DATALINK column and a FOR UPDATE OF clause is not specified. The first subselect requires a temporary result table. The select-statement includes a FETCH FIRST n ROWS ONLY.
39	Deleting the Current Row DELETE FROM table-name WHERE CURRENT OF cursor-name Deletes only one row—the current row Uses the WHERE CURRENT OF to identify the cursor that points to the row to be deleted Must Fetch before doing another Update or Delete
40	Multi-Row Fetch FETCH cursor-name FOR number-of-rows ROWS INTO data-structure Retrieves multiple rows with a single Fetch Up to 32767 rows Blocking is controlled by the number of rows on the Fetch, OVRDBF is ignored Once the data is retrieved, the cursor is positioned on the last row retrieved. Fetch into a Multi-occurrence Data structure DS subfields must be contiguous, starting in position 1 with no overlaps
41	Fetch into Multi-Occurrence DS DPFDATA E DS OCCURS(10) ... C/EXEC SQL C+ DECLARE C1 CURSOR FOR C+ SELECT * C+ FROM PFDATA C/END-EXEC ... C/EXEC SQL C+ FETCH C1 FOR 10 ROWS C+ INTO :PFDATA C/END-EXEC
42	Updates When Retrieving Multiple Rows Works basically just like a forward only cursor with a couple of exceptions When the Fetch completes, the cursor will be on the last record of the block If you want to update an earlier record in the block, you’ll need to do figure out how many records to move back then execute: FETCH RELATIVE :NUMBACK FROM c1 Then reposition to the beginning of the block and re-fetch the block: FETCH RELATIVE :NUMBACK FROM c1 FOR 5 ROWS INTO :DEPTINFO
43	Error Handling SQLCOD vs. SQLSTT – both will work. Both are specified in the SQL standard, but SQLCODE has been marked deprecated in the 1992 edition of the standard and has been dropped in the 1999 edition. Therefore, new applications are strongly encouraged to use SQLSTATE. If there is an error – SQLCOD is negative SQLSTT positions 1-2 will be > ‘02’ If there is a warning SQLCOD is positive SQLSTT positions 1-2 will be ‘01’ If everything is perfect SQLCOD = 0 SQLSTT = ‘00000’ Details in DB2 Universal Database for iSeries SQL Messages and Codes
44	Error Handling Always check SQL State or SQL Code immediately after a statement executes. Your RPG will not bomb with a SQL failure Every SQLCODE has a corresponding message in message file QSQLMSG. The message identifier for any SQLCODE is constructed by appending the absolute value (5 digits) of the SQLCODE to SQ and changing the third character to ’L’ if the third character is ’0’. For example, if the SQLCODE is 30070, the message identifier is SQ30070. If the SQLCODE is -0204, the message identifier is SQL0204. When running in debug mode, SQL places a message corresponding to the SQLCODE in the job log for each SQL statement run. If you are not running in debug mode and get a negative SQLCODE, you will get a message in the job log also. SQL Message Finder - http://publib.boulder.ibm.com/iseries/v5r2/ic2924/index.htm
45	Debugging Same as regular RPG program Stepping through the SQL may take more than one step due to it actually being a call to a SQL program Need the Debugging view compile option set to *SOURCE
46	Performance Run SQL under debug and review joblog for messages regarding suggested indexes to build. Joblog will contain lots of informative messages including Access Paths that need to be created and suggestions for you to create permanent access paths to speed up processing.
47	Performance – sample msgs call sqltest '123' PREPARE of statement SQLSTRING completed. ODP reused. ODP not deleted. Row not found for UPDATE. DSPLY No records found for TSTLIB1 ODP reused. ODP not deleted. Row not found for UPDATE. DSPLY No records found for TSTLIB2 ODP reused. ODP not deleted. Row not found for UPDATE. DSPLY No records found for TSTLIB3
48	Performance – sample msgs All access paths were considered for file FILEA. Additional access path reason codes were used. All access paths were considered for file FILEB. Additional access path reason codes were used. Access path built for file FILEB. File FILEA processed in join position 1. Access path suggestion for file FILEA. File FILEB processed in join position 2.
49	Performance – sample msgs Additional Message Information Message ID . . . . . . : CPI432F Date sent . . . . . . : 11/12/03 Time sent . . . . . . : 10:27:27 Message . . . . : Access path suggestion for file FHPCKMST. Cause . . . . . : To improve performance the query optimizer is suggesting a permanent access path be built with the key fields it is recommending. The access path will access records from member FILEA of file FILEA in library TSTLIB. In the list of key fields that follow, the query optimizer is recommending the first 1 key fields as primary key fields. The remaining key fields are considered secondary key fields and are listed in order of expected selectivity based on this query. Primary key fields are fields that significantly reduce the number of keys selected based on the corresponding selection predicate. Secondary key fields are fields that may or may not significantly reduce the number of keys selected. It is up to the user to
50	Resources www.iseriesnetwork.com Using AS/400 Database Monitor and Visual Explain http://www.redbooks.ibm.com/redpapers/pdfs/redp0502.pdf SQL Message Finder http://publib.boulder.ibm.com/iseries/v5r2/ic2924/info/rzala/rzalafinder.htm SQL Reference http://publib.boulder.ibm.com/iseries/v5r2/ic2924/info/db2/rbafzmst.pdf SQL Programming Concepts http://publib.boulder.ibm.com/iseries/v5r2/ic2924/info/sqlp/rbafymst.pdf SQL Programming with Host Languages http://publib.boulder.ibm.com/iseries/v5r2/ic2924/info/rzajp/rzajpmst.pdf
51	Thanks!!!! Fred Gamache fred@gamacheconsulting.com www.gamacheconsulting.com