-
-
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.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- | |
-- 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