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,
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>.