Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save dancarlosgabriel/e7257f1d7be947a930256a94585c1203 to your computer and use it in GitHub Desktop.
Save dancarlosgabriel/e7257f1d7be947a930256a94585c1203 to your computer and use it in GitHub Desktop.
The request... send emails using data driven email recipient specifications. The implementation is 100% SQLcandoit.
--
-- 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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment