Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Star 2 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save forstie/d348d91297adbd63bf35814f8992f83b to your computer and use it in GitHub Desktop.
Save forstie/d348d91297adbd63bf35814f8992f83b to your computer and use it in GitHub Desktop.
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.
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;
@Ivaylo911
Copy link

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment