Sending an E-mail via SQL

Sending an E-mail via SQL.sql

-- setup 
cl: STRTCPSVR SERVER(*SMTP)  ;
cl: ADDUSRSMTP USRPRF(SCOTTF);
cl: ADDUSRSMTP USRPRF(TIMMR);

-- Send SMTP E-mail Message (SNDSMTPEMM)
cl:SNDSMTPEMM RCP(('forstie@us.ibm.com' *pri)) SUBJECT('hello world again') NOTE('this is a new note');
cl:SNDSMTPEMM RCP(('forstie@us.ibm.com' *pri)) SUBJECT('hello world again') NOTE('this is a new note');

select * from SQLISFUN00.may17objs;

cl:SNDSMTPEMM RCP(('forstie@us.ibm.com')) SUBJECT('common 1 - ALLOBJ users - June 13') NOTE('Today''s ALLOBJ report:') ATTACH(('/home/timmr/ACSStuff/ALLOBJusers.xlsx' *EXCEL));
cl:SNDSMTPEMM RCP(('timmr@us.ibm.com')) SUBJECT('COMMON1 - ALLOBJ users - June 13') NOTE('Spreadsheet generated using SQL ... check ... IFS file emailed as an attachment using SQL ... double check') ATTACH(('/home/timmr/ACSStuff/ALLOBJusers.xlsx' *EXCEL));
cl:SNDSMTPEMM RCP(('forstie@us.ibm.com')) SUBJECT('COMMON1 - ALLOBJ users - June 13') NOTE('Spreadsheet generated using SQL ... check ... IFS file emailed as an attachment using SQL ... double check') ATTACH(('/home/timmr/ACSStuff/ALLOBJusers.xlsx' *EXCEL));


--
--  category:  DB2 for i Services
--  description: Send emails using SQL
--
cl: STRTCPSVR SERVER(*SMTP);
cl: ADDUSRSMTP USRPRF(SCOTTF);
CL: ALCOBJ OBJ((QSYS2/SYSLIMTBL *FILE *EXCL)) CONFLICT(*RQSRLS) ;
CL: DLCOBJ OBJ((QSYS2/SYSLIMTBL *FILE *EXCL));

create or replace trigger scottf.system_limits_large_file
    after insert on qsys2.syslimtbl
    referencing new as n for each row mode db2row
    set option usrprf = *owner, dynusrprf = *owner
  begin atomic
    declare v_cmdstmt varchar(2000);
    declare v_error integer;   
    declare v_host  varchar(10) ccsid 37;
    declare exit handler for sqlexception set v_error = 1;
    /* ------------------------------------------------------------------ */
    /* If a table has exceeded 80% of this limit, send an email alert     */
    /* ------------------------------------------------------------------ */
    /* 15000 == MAXIMUM NUMBER OF ALL ROWS IN A PARTITION                 */
    /*          (max size = 4,294,967,288)                                */
    /* ------------------------------------------------------------------ */
    if (n.limit_id = 15000 and 
        n.current_value > ((select supported_value from qsys2.sql_sizing where sizing_id = 15000) * 0.8)) then
      select host_name into v_host from qsys2.system_status_info;
      /* -- Send SMTP E-mail Message (SNDSMTPEMM) -- */
      set v_cmdstmt =
          'SNDSMTPEMM RCP((''forstie@us.ibm.com'' *pri)) SUBJECT(''' concat v_host concat 
          ' - System Limits Alert: ' concat n.system_schema_name concat '/' concat n.system_object_name concat
            ' size'') NOTE(''User: ' concat USER_NAME concat 
                           ' caused Table: ' concat n.system_schema_name  concat '/' concat
                                                    n.system_object_name  concat ' (' concat 
                                                    n.system_table_member concat
            ') to exceed 80% of the maximum size of a table. ROW COUNT =  ' concat 
             current_value concat ' '') ';
      call qsys2.qcmdexc(v_cmdstmt);      
    end if;
  end;

commit;

select * from qsys2.tcpip_info;
select * from sysibmadm.env_sys_info;
select * from table(qsys2.get_job_info('*'));
select host_name from qsys2.system_status_info;


insert into qsys2.syslimtbl values(
current timestamp, 
0,1,15000, qsys2.job_name, 'TIMMR', 4000001000, 'PRODLIB', 'ORDERS', 'ORDERS', '*FILE', 1, NULL)

;

insert into qsys2.syslimtbl values(
current timestamp, 
0,1,15000, qsys2.job_name, 'TIMMR', 4000323000, 'PRODLIB', 'ORDERS', 'ORDERS', '*FILE', 1, NULL)

;


-- Description: Determine if any user triggers have been created over the System Limits table
select *
  from qsys2.systriggers
  where event_object_schema = 'QSYS2'
        and event_object_table = 'SYSLIMTBL';

select * from qsys2.syslimtbl
            where limit_id = 15000;
            

            
cl: SNDSMTPEMM RCP(('forstie@us.ibm.com' *pri)) SUBJECT('common1 alert LIB/FILE size') NOTE('Table: LIB/FILE (MBR) IS GETTING VERY LARGE - ROW COUNT =  1233 ') ;

Hi
Nice example.
Is it possible to 'assume' another 'from' user?
I declare myself.
I want to avoid to add a new smtpuser ADDUSRSMTP USRPRF(xxxx) for every user on our production system.
So is it possible to use another user to be assumed instead of the current user?
Like f.e. the SMBMJOB command which I can use on behavior of another user.
Thanks
Mathy Paesen

Hi,
Yes, there are some options.

1. The code that does the email send could run in a job where you strictly control the user. For example with the USER() parameter on SBMJOB.
2. Use SET SESSION AUTHORIZATION. For example:

-- swap to joeuser
set session authorization joeuser;
values user;
values SYSTEM_USER;
-- switch back to the system user
set session authorization SYSTEM_USER;
values user;

Thank you very much Scot. I used option 1 as work around yesterday. Option 2 is new for me and i surely hoongelach to use it. Cu on the next common event in europe 😉 Verzonden vanuit Outlook voor iOS<https://aka.ms/o0ukef>

…

________________________________ Van: Scott Forstie ***@***.***> Verzonden: Tuesday, January 17, 2023 4:47:33 PM Aan: forstie ***@***.***> CC: Comment ***@***.***> Onderwerp: Re: forstie/Sending an E-mail via SQL.sql @forstie commented on this gist.

________________________________ Hi, Yes, there are some options. 1. The code that does the email send could run in a job where you strictly control the user. For example with the USER() parameter on SBMJOB. 2. Use SET SESSION AUTHORIZATION. For example:

-- swap to joeuser set session authorization joeuser; values user; values SYSTEM_USER; -- switch back to the system user set session authorization SYSTEM_USER; values user; — Reply to this email directly, view it on GitHub<https://gist.github.com/afa4ab035c08ccfae50100b416efc648#gistcomment-4439791> or unsubscribe<https://github.com/notifications/unsubscribe-auth/AARP4EGLMZZTPMG5OPSAE4DWS25JNBFKMF2HI4TJMJ2XIZLTSKBKK5TBNR2WLJDHNFZXJJDOMFWWLK3UNBZGKYLEL52HS4DFQKSXMYLMOVS2I5DSOVS2I3TBNVS3W5DIOJSWCZC7OBQXE5DJMNUXAYLOORPWCY3UNF3GS5DZVRZXKYTKMVRXIX3UPFYGLK2HNFZXIQ3PNVWWK3TUUZ2G64DJMNZZDAVEOR4XAZNEM5UXG5FFOZQWY5LFVA4TMNZUGEYTKONHORZGSZ3HMVZKMY3SMVQXIZI>. 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>.