Skip to content

Instantly share code, notes, and snippets.

@forstie
Last active June 13, 2024 02:48
Show Gist options
  • Save forstie/34bed39dde775cc292ada670dd6ea20f to your computer and use it in GitHub Desktop.
Save forstie/34bed39dde775cc292ada670dd6ea20f 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', 'forstie@us.ibm.com');
insert into coolstuff.email values('Sec_team', 'timmr@us.ibm.com');
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;
@dbridwell1
Copy link

I am unable to send emails to multiple recipients in systools.send_email. I get SQLSTATE 38501, code -438. Invalid email.
When I retrieve the source for the send_email function it has the following:
SET V_CMDSTMT =
'QSYS/SNDSMTPEMM RCP((''' CONCAT TO_EMAIL CONCAT ''' *PRI)) SUBJECT(''' CONCAT
SUBJECT CONCAT ''') NOTE(''' CONCAT BODY CONCAT ''') ' CONCAT
CASE WHEN ATTACHMENT IS NOT NULL THEN 'ATTACH((''' CONCAT ATTACHMENT CONCAT '''))'
ELSE ''
END ;
It does not appear to try and build multiple recipeints.
Does this require a PTF to upgrade the send_email function to support multiple recipients?

@dbridwell1
Copy link

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.

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