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 (at marker <<< 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 (at marker <<< 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 (at marker <<< 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 preceding statement.

 Create or Replace Procedure IM_MAINTENANCE
 (
      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;