The request... send emails using data driven email recipient specifications. The implementation is 100% SQLcandoit.

Data driven emails from IBM i.sql

--
-- Subject: Data driven emails from IBM i
-- Author: Scott Forstie  
-- Date  : May, 2024
-- Features Used : This Gist uses SQL PL, listagg, SYSTOOLS.GENERATE_SPREADSHEET, SYSTOOLS.SEND_EMAIL, and database know-how
--
-- Now that it's simple to generate spreadsheets and send emails from the IBM i, the request was to 
-- send emails and NOT have the recipient(s) of the email hard-coded.
--
-- One solution is found below. Store the email recipients within a Db2 for i table and 
-- use the LISTAGG built-in function to build the email TO, CC, and BCC lists.
--
-- #SQLcandoit
--
-- https://www.ibm.com/docs/en/i/7.5?topic=services-generate-spreadsheet-scalar-function

--
-- Create an SQL table that contains who should be emailed, by team name
--
create table coolstuff.email 
(team varchar(50), email_address for column emailaddr varchar(100));

--
-- The following table could contain more designations for email delivery.
-- You choose how complex and elaborate the data model becomes.
--
insert into coolstuff.email values('Sec_team', '[email protected]');
insert into coolstuff.email values('Sec_team', '[email protected]');
select * from coolstuff.email;
stop;

--
-- Test how LISTAGG can transform multiple column values into a single, comma separated list
--
select
  listagg(
    cast(email_address as clob(1m)), ', ') within group (order by email_address)
    as EMAIL_LIST
  from coolstuff.email
  where team = 'Sec_team';
stop;  

--
-- IBM Security configuration (for purposes of demonstration)
--
select * from qsys2.security_info;
stop;

-- One time setup. 
-- Note that user in the following line is the USER SQL special register that returns
-- the value of the current user of the connection/job.
-- 
-- call qsys2.qcmdexc('QSYS/ADDUSRSMTP  USRPRF(' concat user concat ')');
-- 

--
-- Built a Spreadsheet that resides within Scott's home in the IFS
--
VALUES SYSTOOLS.GENERATE_SPREADSHEET(
  PATH_NAME         => '/home/scottf/sec_info',        
  SPREADSHEET_QUERY => 'select * from qsys2.security_info',    
  SPREADSHEET_TYPE  => 'xlsx',    
  COLUMN_HEADINGS   => 'NONE'   
);
stop;

--
-- Is the spreadsheet created?
--
select PATH_NAME, OBJECT_TYPE, OBJECT_CHANGE_TIMESTAMP
  from table (
      qsys2.ifs_object_statistics('/home/scottf/', SUBTREE_DIRECTORIES => 'YES')
    );
stop;

--
-- What is the name of the current IBM i?
--
select host_name from sysibmadm.env_sys_info;
stop;
          
--
-- Send the email, with an attachment, 
-- but use a Db2 file (coolstuff.email) for the email addresses
--
values SYSTOOLS.SEND_EMAIL(
  TO_EMAIL => (select
        listagg(cast(email_address as clob(1m)), ', ') 
        within group (order by email_address)
          as EMAIL_LIST
        from coolstuff.email
        where team = 'Sec_team'), 
  SUBJECT => 'Security config on IBM i: ' concat (select host_name
          from sysibmadm.env_sys_info), 
  BODY => 'Security config captured on ' concat
      current timestamp, 
  ATTACHMENT => '/home/scottf/sec_info.xlsx');
stop;

Never mind - looks like this feature is not available yet. https://www.ibm.com/support/pages/systoolssendemail says it is enhanced in SF99704 level 28 but I don't see it as available yet. I will wait a couple of weeks and try again.

Hello Scott, I have a question for you, for example I tried to send these 2 statements:

VALUES SYSTOOLS.GENERATE_SPREADSHEET( PATH_NAME => '/home/qsecofr/sec_info', SPREADSHEET_QUERY => 'select * from qsys2.security_info', SPREADSHEET_TYPE => ' xlsx', COLUMN_HEADINGS => 'COLUMN' );

VALUES SYSTOOLS.SEND_EMAIL(TO_EMAIL => '[email protected]', SUBJECT => 'Security config on IBM i:', BODY => 'Security config captured on DEVECON', ATTACHMENT => '/home/ QSECOFR/sec_info.xlsx');

to a file in the IFS and call it from a CL
" RUNSQLSTM SRCSTMF('home/QSECOFR/xlsxv2.sql') NAMING(*SQL) COMMIT(*NONE) "
and it always cancels, it may be that it only allows executing these functions "VALUES SYSTOOLS.GENERATE_SPREADSHEET and VALUES SYSTOOLS.SEND_EMAIL only from the ACS environment (Run SQL script) and does not allow doing it in another way manner ? thanks

Hi.
To implement this in RUNSQLSTM, the style needs to be adjusted.
Here's a working example... this is just one valid solution, there are other options.

begin
  declare rv integer;
  values SYSTOOLS.GENERATE_SPREADSHEET(
      PATH_NAME => '/home/scottff/sec_info',
      SPREADSHEET_QUERY => 'select * from qsys2.security_info', SPREADSHEET_TYPE => 'xlsx',
      COLUMN_HEADINGS => 'COLUMN') into rv;
  values SYSTOOLS.SEND_EMAIL(
      TO_EMAIL => '[email protected]', SUBJECT => 'Security config on IBM i:',
      BODY => 'Security config captured on DEVECON', ATTACHMENT => '/home/scottf/sec_info.xlsx')
    into rv;
end;

Also, please note that there were 2 syntax problems that I corrected:

1. ' xlsx', <--- leading blank not allowed
2. => '/home/ QSECOFR/sec_info.xlsx'); <-- blank character in the path, not allowed

Thanks Scott, you are the Best !!! work perfect !!! Regards El vie, 26 jul 2024 a las 19:50, Scott Forstie ***@***.***>) escribió:

…

***@***.**** commented on this gist. ------------------------------ Also, please note that there were 2 syntax problems that I corrected: 1. ' xlsx', <--- leading blank not allowed 2. => '/home/ QSECOFR/sec_info.xlsx'); <-- blank character in the path, not allowed — Reply to this email directly, view it on GitHub <https://gist.github.com/forstie/34bed39dde775cc292ada670dd6ea20f#gistcomment-5134816> or unsubscribe <https://github.com/notifications/unsubscribe-auth/AFQOQ3TQG3CKNY3UWLC5QJLZOLHELBFKMF2HI4TJMJ2XIZLTSKBKK5TBNR2WLJDUOJ2WLJDOMFWWLO3UNBZGKYLEL5YGC4TUNFRWS4DBNZ2F6YLDORUXM2LUPGBKK5TBNR2WLJDHNFZXJJDOMFWWLK3UNBZGKYLEL52HS4DFVRZXKYTKMVRXIX3UPFYGLK2HNFZXIQ3PNVWWK3TUUZ2G64DJMNZZDAVEOR4XAZNEM5UXG5FFOZQWY5LFVEYTGMBTGIYTMMRWU52HE2LHM5SXFJTDOJSWC5DF> . You are receiving this email because you commented on the thread. Triage notifications on the go with GitHub Mobile for iOS <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android <https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub> .