In this working example, we establish an SQL table which contains the spreadsheets we'd like to have generated. Each row in the table includes the query that will supply the data for the spreadsheet, and the location in the IFS where the spreadsheet should reside. Further, a different procedure emails the spreadsheets to an interested consumer. …

Generating spreadsheets with SQL.sql

-- =================================================================
-- 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:
--   2a) Use ACS to upload acsbundle.jar to /home/acs/acsbundle.jar
--   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 /home/acs/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';

Great. Thanks Scott.

Scott - a couple of questions.
Must you relocate acsbundle.jar or can you reference /QIBM/ProdData/Access/ACS/Base/acsbundle.jar? When ACS is updated do you need to copy the new file?

Also couldn't figure out why I was getting two emails but see there are two SNDSMTPEMM (78 & 114) - they have different subject lines but otherwise seem identical. Just wondering if I'm missing something or am running more than I need.

This looks like it will automate an audit requirement for us so very anxious to get it working.

Jeff

Hi Jeff,
When I initially created this Gist, ACS was not shipping with the IBM i operating system, so a one-time manual establishment of the acsbundle.jar was a required pre-req.
Now that acsbundle.jar ships with the HTTP PTF Group, SQL examples like this one can just point to the jar shipped by IBM.
When the jar is updated by an HTTP PTF Group level, I know of no change needed by the SQL consumer.
Note... I did not say that you need to copy the jar. Just use the path to point to the jar shipped by IBM.

generate_spreadsheets() - generates the spreadsheet to the IFS ... and ... emails it
coolstuff.email_spreadsheets () - simply shows how to send emails using SQL, including attachments found in the IFS

Glad to see that this can help.

That makes sense! That "locate" makes much more sense now.
Great - I get it so I should be able to just use generate_spreadsheets()

almost there - now I just need to capture job log and send it along too to meet audit requirements. I might just wrap a CL around this to do that with a CPYSPLF and add that to SNDSMTPEMM as attachment. I think the trick will be attaching the correct splf.

If it were me, I'd use this handy-dandy GENERATE_PDF scalar function that generates a PDF file in the Integrated File System containing the content of a spooled file.
https://www.ibm.com/docs/en/i/7.5?topic=services-generate-pdf-scalar-function

Knew you'd have a better way! Just have to make the name unique in a way that I can reference it in the ATTACH of the SNDSMTPEMM, which should be too hard. Thanks!