Retrieve_Program_Information UDTF

I wrote my first UDTF following a presentation by Scott Klement. Basically you pass in 3 parameters:
Library_name: library name or *ALL *CURLIB *LIBL *USRLIBL
Object_type: *PGM, *SRVPGM or *ALL to get both
Object_name: program or service program name or null to get all

It then queries qsys2.object_statistics to get a list of the above.

Then it forks off and uses either the retrieve program information or the retrieve service program information api.

Sample use:
select t.*
from table(Retrieve_Program_information('ROB', '*SRVPGM', 'HELLOWORLD')) as t
;

select t.*
from table(Retrieve_Program_information('ROB', '*PGM', 'AAASQL')) as t
;
select t.*
from table(Retrieve_Program_information('ROB', '*PGM', 'AADELETEME')) as t
;
select t.*
from table(Retrieve_Program_information('ROB', '*PGM', 'CHKDOMJOBS')) as t
;
select t.*
from table(Retrieve_Program_information('ROB', '*ALL', 'HELLOWORLD')) as t
;
select t.*
from table(Retrieve_Program_information('ROB', '*ALL')) as t
;

Completely **free format RPG

Sample output:
https://imgur.com/Y5eb60F

Raw, rude and crude but gets what I needed done.

Since figuring out how to debug such a beast using RDi was rough I have a bunch of lines like this I need to strip out:
command = 'SNDMSG MSG(''sql_state on Open ' +
sql_state + ' ' + library_name + ' ' + object_type_list + ' ' + object_name +
''') TOUSR(ROB)';
CommandLength = %len(%trim(command));
callp qcmdexc(command: CommandLength);

Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com