forstie · July 26, 2024 23:45 · EdgardoEhiyan · Jul 26, 2024
diff --git a/Data driven emails from IBM i.sql b/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;
	--
	-- 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;