Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save forstie/b124640c25bf05001ae5ef5fd7e4e370 to your computer and use it in GitHub Desktop.
Save forstie/b124640c25bf05001ae5ef5fd7e4e370 to your computer and use it in GitHub Desktop.
This example simplifies a previous gist. ACS is now being shipped on your IBM i via PTFs. Subsequent PTFs will ship when major enhancements are made to ACS.
--
-- Now that ACS is shipped on the IBM i via PTFs, we no longer need to manually move the
-- acsbundle.jar onto the IBM i. Just apply the latest ACS PTFs and reference the jar
-- at: /QIBM/proddata/Access/ACS/Base/acsbundle.jar
--
-- Initial PTFs of ACS
-- V7R4M0 SI71900
-- V7R3M0 SI71934
--
-- =================================================================
-- Author: Scott Forstie
-- Email : forstie@us.ibm.com
-- Date : January 10, 2020
-- =================================================================
--
-- Setup:
-- 1) create and populate the spreadsheet generator table
-- 2) change the procedure source code:
-- 2b) Change forstie@us.ibm.com to your email address. <==== Please don't email me your spreadsheets :)
-- 2c) Change /system=COMMON1 to use the name of your IBM i
-- 2d) Change the ADDUSRSMTP user profile to be your user profile
--
cl: STRTCPSVR SERVER(*SMTP) ;
cl: ADDUSRSMTP USRPRF(SCOTTF);
create schema coolstuff;
create table coolstuff.spreadsheet_generator
(IFS_PATH varchar(1000),
Spreadsheet_query varchar(10000)
);
truncate coolstuff.spreadsheet_generator;
insert into coolstuff.spreadsheet_generator
values ('/home/SCOTTF/systemnameGroupPTFCurrency',
'SELECT * FROM systools.group_ptf_currency');
insert into coolstuff.spreadsheet_generator
values ('/home/SCOTTF/top10spool',
'select user_name, sum(size) as total_spool_space from qsys2.output_queue_entries_basic group by user_name order by total_spool_space desc limit 10');
insert into coolstuff.spreadsheet_generator
values ('/home/SCOTTF/ALLOBJusers',
'select authorization_name,status,no_password_indicator,previous_signon,text_description
from qsys2.user_info where special_authorities like ''''%*ALLOBJ%'''' or authorization_name in (select user_profile_name
from qsys2.group_profile_entries where group_profile_name in (select authorization_name
from qsys2.user_info where special_authorities like ''''%*ALLOBJ%'''')) order by authorization_name');
select * from coolstuff.spreadsheet_generator;
stop;
--
-- 1 time setup:
-- =============
-- a) Locate ACS (acsbundle.jar) in the IFS at /home/acs/acsbundle.jar
-- b) Update the code below to use the name of your system
-- change COMMON1 to your system name
-- If you're on IBM i 7.3 or higher, with a current Db2 PTF group, just query QSYS2.SYSTEM_STATUS_INFO.
-- select partition_name from qsys2.system_status_info
--
create or replace procedure coolstuff.generate_spreadsheets()
begin
declare cmdtext clob(2k);
declare v_cmdstmt varchar(2000);
declare v_ifs_path varchar(1000);
declare v_ifs_full_path varchar(2000);
declare v_spreadsheet_query varchar(10000);
declare not_found condition for '02000';
declare at_end integer default 0;
declare spreadsheets cursor for
select ifs_path, spreadsheet_query
from coolstuff.spreadsheet_generator;
declare continue handler for sqlexception set at_end = 1;
declare continue handler for not_found set at_end = 1;
open spreadsheets;
fetch from spreadsheets into v_ifs_path, v_spreadsheet_query;
while (at_end = 0) do
set v_ifs_full_path = v_ifs_path concat lpad(month(current date), 2, 0) concat
lpad(day(current date), 2, 0) concat year(current date) concat '.xlsx';
set cmdtext =
'STRQSH CMD(''java -Dcom.ibm.iaccess.ActLikeExternal=true -jar /QIBM/proddata/Access/ACS/Base/acsbundle.jar '
concat '/plugin=cldownload /system=COMMON1 /clientfile=' concat v_ifs_full_path
concat ' /sql="' concat v_spreadsheet_query concat '"'')';
-- call systools.lprintf('Speadsheet being generated: ' concat v_ifs_full_path);
call qsys2.qcmdexc(cmdtext);
set v_cmdstmt =
'SNDSMTPEMM RCP((''forstie@us.ibm.com'' *pri)) SUBJECT(''COMMON1 report:' concat
v_ifs_full_path concat ''') NOTE(''Common1 report ' concat
lpad(month(current date), 2, 0) concat lpad(day(current date), 2, 0) concat
year(current date) concat ''') ATTACH(''' concat v_ifs_full_path concat ''')';
-- call systools.lprintf('Speadsheet being emailed: ' concat v_ifs_full_path);
call qsys2.qcmdexc(v_cmdstmt);
fetch from spreadsheets into v_ifs_path, v_spreadsheet_query;
end while;
close spreadsheets;
end;
stop;
call coolstuff.generate_spreadsheets();
stop;
create or replace procedure coolstuff.email_spreadsheets ()
begin
declare cmdtext clob(2k);
declare v_cmdstmt varchar(2000);
declare v_ifs_path varchar(1000);
declare v_ifs_full_path varchar(2000);
declare v_spreadsheet_query varchar(10000);
declare not_found condition for '02000';
declare at_end integer default 0;
declare spreadsheets cursor for
select ifs_path, spreadsheet_query
from coolstuff.spreadsheet_generator;
declare continue handler for sqlexception set at_end = 1;
declare continue handler for not_found set at_end = 1;
open spreadsheets;
fetch from spreadsheets into v_ifs_path, v_spreadsheet_query;
while (at_end = 0) do
set v_ifs_full_path = v_ifs_path concat lpad(month(current date), 2, 0) concat
lpad(day(current date), 2, 0) concat year(current date) concat '.xlsx';
call systools.lprintf('Speadsheet being emailed: ' concat v_ifs_full_path);
set v_cmdstmt =
'SNDSMTPEMM RCP((''forstie@us.ibm.com'' *pri)) SUBJECT(''COMMON1 report'') NOTE(''Common1 report '
concat lpad(month(current date), 2, 0) concat
lpad(day(current date), 2, 0) concat year(current date) concat
''') ATTACH(''' concat v_ifs_full_path concat ''')';
call qsys2.qcmdexc(v_cmdstmt);
fetch from spreadsheets into v_ifs_path, v_spreadsheet_query;
end while;
close spreadsheets;
end;
stop;
call coolstuff.email_spreadsheets();
stop;
-- Create the spreadsheets on a schedule
cl: ADDJOBSCDE JOB(SSHEETS) CMD(RUNSQL SQL('call coolstuff.generate_spreadsheets()') COMMIT(*NONE) NAMING(*SQL)) FRQ(*WEEKLY) SCDDATE(*NONE) SCDDAY(*ALL) SCDTIME(235500) ;
stop;
--
-- description: Review the setup
--
select *
from qsys2.scheduled_job_info
where scheduled_job_name = 'SSHEETS';
@m-tyler
Copy link

m-tyler commented Mar 25, 2022

Scott,
In this example, you initiate JAVA with the property of "com.ibm.iaccess.ActLikeExternal=true". What does this do and is there documentation for his? Its not added in the next version of the emailing examples.

Thanks, Matt

@forstie
Copy link
Author

forstie commented Mar 25, 2022

Hi Matt,
CLDOWNLOAD is part of ACS.
The doc I've seen is found here: https://www.ibm.com/support/pages/ibm-i-access-acs-getting-started
I suggest opening a support ticket if you still have questions.
Scott

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