Thanks Chuck.
This is exactly what I was looking for.

Jeff Young
Sr. Programmer Analyst

On Wed, Nov 4, 2015 at 2:57 PM, CRPence <crpbottle@xxxxxxxxx> wrote:

On 04-Nov-2015 09:59 -0600, Jeff Young wrote:


Does anyone have a program that will retrieve the activation group
information for ILE programs in a library?
I am aware of the API that will get that info, but do not have the
time to "roll my own" program.
I am attempting to analyze the programs in specific libraries to
determine if any are still running the the Default Activation Group.


For just the ActGrp name? If so, the APIs are easy to code for
retrieving just that, and the list of objects can be generated easily from
[even a prompted] DSPOBJD request; a simple but crude CLP could probably be
coded relatively quick.

[
http://www.ibm.com/support/knowledgecenter/api/content/ssw_ibm_i_72/apis/qclrpgmi.htm
]
_Retrieve Program Information (QCLRPGMI) API_
[
http://www.ibm.com/support/knowledgecenter/api/content/ssw_ibm_i_72/apis/qbnrspgm.htm
]
_Retrieve Service Program Information (QBNRSPGM) API_

And FWiW, I decided to do effectively that, but using SQL [what I show,
is not recommended; probably better to just code as a CLP, but] By just
running the script included below, all the code could be made available
directly and ready-to-go without doing anything else, such that just
running queries [perhaps preceded by DSPOBJD] can extract and present the
desired information:

The following SQL script does [a very crude; Quick and Dirty] setup to
allow calling the respective API from the SQL [very discouraged, but
exhibits something as possible], and then creates a scalar function
[dependent on the Q&D as well as Q&D itself] to return *just* the ACTGRP
[the "Activation group attribute" from the API docs] according to the
program type. This is all just as easily done in a Q&D CLP doing
effectively the same [crudely ignoring issues and hard-coded non-template
offsets].

<code>

drop procedure QCLRPGMI
;
create procedure QCLRPGMI
( out rcvdta char(1000) for bit data
, in rcvlen int
, in fmtnam char( 8)
, in pgmnam char( 20)
, inout errcde char( 16)
) external name qsys/QCLRPGMI no sql
language pli parameter style general
;
drop procedure QBNRSPGM
;
create procedure QBNRSPGM
( out rcvdta char(1000) for bit data
, in rcvlen int
, in fmtnam char( 8)
, in pgmnam char( 20)
, inout errcde char( 16)
) external name qsys/QBNRSPGM no sql
language pli parameter style general
;
drop function ACTGRP
;
create function ACTGRP
( inppgm varchar( 20)
, inptyp varchar( 10)
) returns varchar(30)
language sql specific ACTGRP
not deterministic reads sql data
returns null on null input
disallow parallel
set option dbgview=*LIST
BEGIN
declare rcvdta char(1000) for bit data default x'00' ;
declare rcvlen int default 1000 ;
declare fmtpgm char( 8) default 'PGMI0100' ;
declare fmtsrv char( 8) default 'SPGI0100' ;
declare errcde char( 16) default x'0000000000000000' ;
declare pgmofs int default 369 ;
declare srvofs int default 92 ;
declare actgrpofs int ;
declare actgrpatr char( 30) ;
declare pgmnam char( 20) ;
declare continue handler for SQLEXCEPTION
return '*ERROR retrieving info' ;
set pgmnam = inppgm ;
case inptyp
when '*SRVPGM' then
call QBNRSPGM ( rcvdta, rcvlen, fmtsrv, pgmnam, errcde ) ;
set actgrpofs = srvofs ;
when '*PGM' then
call QCLRPGMI ( rcvdta, rcvlen, fmtpgm, pgmnam, errcde ) ;
set actgrpofs = pgmofs ;
-- otherwise actgrpofs is null so returns NULL
end case ;
return rtrim( substr( rcvdta, actgrpofs, 30 ) ) ;
END

</code>

The following query asks to list the *SRVPGM and *PGM objects from the
one library named in the values-row-clause [so 'QSYS2' could be replaced
accordingly], using the OBJECT_STATISTICS UDTF, and orders the results of
just the ILE programs by their ActGrp and Object Type, and then by object
name.

select "PgmName", "ObjType", "ActGrp"
from
( select LIBNAME concat '/' concat a.objname as "PgmName"
, a.OBJTYPE as "ObjType"
, actgrp( char(a.OBJNAME, 10) concat LIBNAME
, a.OBJTYPE ) as "ActGrp"
/* Replace 'QSYS2' with the desired Library Name: */
from ( values 'QSYS2' ) as c (LIBNAME)
, table( OBJECT_STATISTICS(LIBNAME, 'SRVPGM PGM') ) as a
) as n
where n."ActGrp"<>'' /* indicating *PGM apparently is non-ILE */
order by "ActGrp", "ObjType", "PgmName"

In lieu of that Table Function, the output from a Display Object
Description (DSPOBJD) command request could generate the list against which
to process; probably encapsulate the use of the UDF in a VIEW defined with
the table-reference naming the Output File (OUTFILE) from the DSPOBJD
request, and coding the expression for "ActGrp" instead as ACTGRP(odobnm
concat odlbnm, odobtp) And for similar data to the above query:

DSPOBJD QSYS2/*ALL (*SRVPGM *PGM)
OUTPUT(*OUTFILE) OUTFILE(QTEMP/ODPGMS) OUTMBR(*FIRST *REPLACE)
/* Replace QSYS2 accordingly */

RUNSQL 'create view qtemp/odpgmsvw as
(select odlbnm, odobnm, odobtp, actgrp
from (select odlbnm, odobnm, odobtp
, actgrp(odobnm concat odlbnm, odobtp) as actgrp
from qtemp/odpgms ) as a where actgrp<>space(1)
)' commit(*none) naming(*sys)

RUNQRY *NONE qtemp/odpgmsvw
/* per no ordering from above, probably instead, in SQL: */

SELECT * from qtemp/odpgmsvw order by actgrp, odobtp, odobnm

By repeating just the first request, the DSPOBJD, with a different
library name, then either the Run Query request or the SELECT query can
present the information for each successive library against which viewing
the results is desired.

--
Regards, Chuck

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.



This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2019 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].