IBMi External Procedures in TURNOVER

Turnover (the one written by Softlanding that does Change Management, not the little pastry that is full of apple and tastes delicious) is a pretty cool Software Change Management application native to IBMi. But of course, you already knew that.

But, did you know that you can promote an EXTERNAL PROCEDURE to a specific library and then have it invoke its program component from that library, or from any library that you care to define to Turnover or of course from the *LIBL?

It’s all down to these basic rules:

• &LIBRARY is the variable that TURNOVER will replace with the name of your target library during promotion
• if you quote the procedure name (ie: ‘program’) then it must have either a hardcoded library (‘QGPL/THING‘) or the variable (‘&LIBRARY/THING)
• If you do not specify the library name (ie: EXTERNAL NAME THING) then *LIBL will be used to call the THING program

Example *EXTPRC using &LIBRARY VARIABLE:

create or replace procedure MYPROCEDURE (
        out ACCOUNTNUM9 dec(9, 0),
        in BIRTHDATE dec(6, 0),
        in DRIVERSLIC char(25),
        in DRIVERSLICSTATE char(2),
        in PASSPORTID char(20),
        in OTHERID char(30),
        in RZIPCODE char(15),
        in LASTNAME char(25),
        in FIRSTNAME char(25),
        in RADDRESS1 char(25),
        out RETURN_CODE char(25),
        out RETURN_MSG char(100)
      )
    dynamic result sets 2
    language RPGLE
    specific MYPROCEDURE
    not deterministic
    modifies sql data
    called on null input
    external name '&LIBRARY/MYPROCEDURE'
    parameter style general;

And of course, remember to qualify the TURNOVER library name replacement variable:

external name '&LIBRARY/MYPROCEDURE'

will result in it calling it from the specific library rather than the *LIBL.

Testing how Stored Procedures are defined

Here is an example of me using this SQL statement will show all stored procedures with the words CHKCASACT as part of their name:

If you want to check the stored proc settings use SQL

SELECT * FROM qsys2.sysprocs WHERE SPECIFIC_NAME like '%CHKCASACT%'

It took me a lot of head scratching to figure it out… and YES…. I know I probably could have just opened a manual and found that out but this really makes promoting procedures a lot simpler!

So, the simple rules are:

• Always use CREATE OR REPLACE PROCEDURE
• Use the &LIBRARY variable which will be replaced by the TURNOVER library at runtime