Sending emails from DB2

I am sure you all know that you can configure db2 admin server and health monitor to send emails, but how do you send email from within
db2 luw itself. Much like SQLMAIL feature in our friend SQL Server , DB2 now have a mail feature. Starting from DB2 V9.7, there is an additional database configuration called SMTP_SERVER and built in stored procedure UTIL_MAIL.

In order to use the emailing feature you need to first set the SMTP_SERVER configuration on each database with your smtp server. Eg :

db2 update db cfg for rajudb1 using SMTP_SERVER mysmtp1.techsatwork.com

Once you have set the smtp_server, you can use the built-in stored procedure UTIL_MAIL to send emails like the example here:

db2 “call UTL_MAIL.SEND (‘rajudb1@db2server1.com’,’raju@techsatwork.com’,NULL,NULL,’warning email from rajudb1′,’Tablespace1 is nearing 80% full’)” ;

You can also use it in a more classier fashion :

BEGIN
  DECLARE v_sender VARCHAR(30);
  DECLARE v_recipients VARCHAR(60);
  DECLARE v_cc VARCHAR(60);
  DECLARE v_bcc VARCHAR(60);
  DECLARE v_subj VARCHAR(20);
  DECLARE v_msg VARCHAR(200);

 SET v_sender = ‘rajudb1@db2server1.com’;
 SET v_recipients = ‘raju@techsatwork.com’;
 SET v_cc = NULL; 
 SET v_bcc = NULL; 
 SET v_subj = ‘warning email from rajudb1’;
 SET v_msg = ‘Tablespace1 is nearing 80% full”;

 CALL UTL_MAIL.SEND(v_sender, v_recipients, v_cc, v_bcc, v_subj, v_msg);
END@

You can  execute this just as a sql file (db2 -td@ -svf email_sample.sql).   The maximum size for message field is 32K.   Try it on now and let db2 do the emailing for you.  Please comment on how you use this new feature.