Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Sending an E-mail via 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 ') ;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.