If you want to automate analysis of Collection Services, you can use this approach to use SQL to discern the library in use by Collection Services (CS) and the member name in the CS files that corresponds to the current day.

How to discern where to find todays Collection Services info.sql

begin 
execute immediate  'create or replace variable qpfrdata.current_CS_library varchar(10) for sbcs data';
execute immediate  'create or replace variable qpfrdata.current_CS_member varchar(10) for sbcs data';

call qsys2.qcmdexc('QSYS/CHKPFRCOL');
set (qpfrdata.current_CS_library,qpfrdata.current_CS_member) =
    (select rtrim(substr(message_tokens,1,10)) as cs_lib, rtrim(substr(message_tokens,11,10)) as cs_mbr
 from table(qsys2.joblog_info('*')) where message_id = 'CPI0A16' order by ordinal_position desc limit 1);

execute immediate 
'create or replace alias qtemp.goget_QAPMJOBWT for ' concat qpfrdata.current_CS_library concat '.QAPMJOBWT( ' concat qpfrdata.current_CS_member concat ')';
end;

select * from qtemp.goget_QAPMJOBWT;

Hi,
thank you for this extremely useful code. Can I ask for an advice if possible?
I seem to have an issue every night between 1:00 and 3:00AM where the system gives an error:

"SQL0204 - Q104000002 in *N type *MEM not found" and
"Row or object CURRENT_CS_LIBRARY in QPFRDATA type **N in use."

I perform your code for multiple libraries in order to collect a number of statistics at ones.

Can you advice where could be the issue?

The problem seems to be related to difference between the time zone of the application, which uses the ODBC driver and the system time zone. Is there a way to set the time zone of the session of the application?

For most clients, Collection Services cuts over to the new member once a day, just past midnight.
For the IBM i, there's a single time zone for the partition and its controlled by a system value.
Perhaps your code could handle the -204 and delay 60 seconds and then retry?
Would that suffice?

I think yes - thanks a lot for taking the time to answer. And thank you for all of the things you share here. Thanks to your help I was able to code in a small application gathering performance stats and integrating into Azure Monitor. Would never be possible without the help from here. Thank you again!

You're welcome and very good to hear that you are having success on this topic.
Scott

Hi,
if I may one additional question.
Do we have to use qsecofr in order to perform this procedure or some more restricted user would do?

You do not need to use the QSECOFR user profile.
You'd need *USE authority on CHKPFRCOL, which ships with *PUBLIC *EXCLUDE.
You'd also need select privilege to the CS library and file.

Thank you again for the quick response! It is great that we are able to interact with you in such a way!
Your help has been immense.
Thank you again.