Stored procedure
Stored procedures are a way to extend database functionality. Wikipedia calls a stored procedure a subroutine, which is as good a definition as any. Stored procedures (SP from here on) can be used to:
- Encapsulate complex processing
- Update a table. Sure, UPDATE does that, but then the end user (web developer) needs to be familiar with your database.
- Return data to SQL such as a result set. Again, isolate the database from the UI. A result set can consolidate columns from several tables, in essence hiding the JOINS from the ultimate end user of the data.
- Provide a service to SQL like RTVSYSVAL.
IBM have a 2006 Redbook entitled Stored Procedures, Triggers, and User-Defined Functions on DB2 Universal Database for iSeries. It's old but it provides a foundation for understanding stored procedures.
SPs are an inherent part of DB2; they are part of the database. On other platforms, the logic section of SPs is written using SQL. That's a choice on DB2 for i as well, and this is called an SQL (or MAIN) Stored Procedure. We have another choice as well, and that is to write the logic using an HLL like RPG. This is called an External stored procedure. In either case, you register a SP with DB2 via the SQL CREATE PROCEDURE statement. To use a stored procedure in your SQL, use the CALL statement.
While all programs on the IBM i can be called as stored procedures, note that CL commands are not programs. One way around this is to use QSYS.QCMDEXEC() or QSYS.QCMDEXEC2() stored procedures. You can also use the CL: prefix to use CL commands directly in your own SQL stored procedure. For example:
CL: CHGCURLIB CURLIB(MYLIB);
An external SP interfaces with DB2 via the parameter list. There are several parameter styles, depending on whether you will need to handle NULL values or not.
TODO:
- Example tables
- Example SQL SP
- One return variable
- Result set
- Example RPGLE SP
- One return variable
- Result set
- Multiple parameter list usage
- GENERAL
- WITH NULLS
- Canonical documentation for parameter lists
- Consume a result set. Java?
Error Handling in MAIN Stored Procedures
The following is an abbreviates SQL stored procedure that demonstrates three methods of error handling.
The first method <<< 1 >>> is to declare a global error handler. The global handler can be for either exit processing or continuation processing. It can also handle any combination of exception, warning, and/or not found conditions.
The second method <<< 2 >>> is to declare a local error handler. This one must be inside of a BEGIN ... END statement block. When the statement block ends the local error handler is no longer in effect.
The third method <<< 3 >>> is to explicitly check the SQL state from the last statement executed. This involves coding a Get Current Diagnostics Condition 1 statement to retrieve the returned SQL state. The presence of this statement prevents SQL from performing default error handling for the preceeding statement.
Create or Replace Procedure IM_MAINT
(
In CompNumb Char(5)
, In ItemNumb Char(18)
)
Dynamic Result Sets 1
Language SQL
Deterministic
Modifies SQL Data
Program Type MAIN
New Savepoint Level
Specific IMMAINTSP
Set Option AlwBlk = *AllRead
, AlwCpyDta = *Optimize
, CloSqlCsr = *EndActGrp
, Commit = *CHG
, DatFmt = *ISO
, DbgView = *Source
, DecResult = (31,31,00)
, DftRdbCol = *None
, DynDftCol = *No
, DynUsrPrf = *User
, ExtInd = *YES
, LangId = ENU
, Output = *Print
, SrtSeq = *Hex
, UsrPrf = *User
MyProc: Begin
---------------------------------------------------------------------------
-- variable definitions
---------------------------------------------------------------------------
Declare CompId BigInt; -- company master id
Declare DtaLib Char(10);
Declare ItmTid BigInt; -- item number id
Declare SqlStmt VarChar(3000);
Declare IGNORE_VALUE SmallInt Default -7;
Declare CurState Char(5);
Declare ErrState Char(5);
Declare ErrPrfx Char(11) Default 'IMMAINTSP: ';
Declare ErrText VarChar(300);
Declare IMMAINTSP_CURSOR Cursor With Return For IMMAINTSP_STMT;
---------------------------------------------------------------------------
-- global exit handler
---------------------------------------------------------------------------
Declare Exit Handler for SQLEXCEPTION, SQLWARNING, NOT FOUND <<< 1 >>>
Begin
Get Current Diagnostics Condition 1
ErrState = Returned_SqlState;
Set ErrText = ErrPrfx || ErrText || ' State=' || ErrState;
Signal SqlState '88W00' Set Message_Text = ErrText;
End;
---------------------------------------------------------------------------
-- clean up alias definition if needed
---------------------------------------------------------------------------
Begin
Declare Continue Handler for SQLSTATE '42704' Begin End; -- notfnd <<< 2 >>>
Set ErrText = 'Initial cleanup of alias failed.';
Drop Alias QTEMP/IMMAINTA;
End;
---------------------------------------------------------------------------
-- validate company master using static SQL
---------------------------------------------------------------------------
Set ErrText = 'Company number validation failed.';
Select CiCompPTid, LibCde
Into CompId, DtaLib
From SHR460/CICOMPPV02
Where CIORGKY = CompNumb;
IF CompId is NULL
Or DtaLib is NULL Then
Set ErrText = 'Company number not on file or data library missing.';
Signal SqlState '88W01' Set Message_Text = ErrText;
End If;
---------------------------------------------------------------------------
-- point to local company data library
---------------------------------------------------------------------------
Set ErrText = 'Set path failed.';
Set Path = "QSYS","QSYS2","SYSPROC","SYSIBMADM"
,"QTEMP",DtaLib,"SHR460","PGM460","PTF460"
,"QGPL"; -- for dynamic SQL
Set ErrText = 'Setting schema '||DtaLib||' failed.';
Set Schema DtaLib;
Get Current Diagnostics Condition 1 CurState = Returned_SqlState; <<< 3 >>>
If Substr(CurState,1,2) <> '00'
Or Not Exists (Select 1 from QSYS2/SYSSCHEMAS
where System_Schema_Name = BackupLibrary) Then
Signal SqlState '88W02' Set Message_Text = ErrText;
End If;
-- ...snip...
---------------------------------------------------------------------------
-- return to caller
---------------------------------------------------------------------------
Return;
End;
