{"id":1144,"date":"2011-08-06T03:57:33","date_gmt":"2011-08-05T21:57:33","guid":{"rendered":"http:\/\/techsatwork.com\/blog\/2011\/08\/sending-email-from-db2\/"},"modified":"2016-01-07T11:31:05","modified_gmt":"2016-01-07T05:31:05","slug":"sending-email-from-db2","status":"publish","type":"post","link":"https:\/\/techsatwork.com\/?p=1144","title":{"rendered":"Sending emails from DB2"},"content":{"rendered":"

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
\ndb2 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\u00c2\u00a0SMTP_SERVER and built in stored procedure UTIL_MAIL<\/a>.<\/p>\n

In order to use the emailing feature you need to first set the SMTP_SERVER configuration on each database with your smtp server. Eg :<\/p>\n

db2 update db cfg for rajudb1 using SMTP_SERVER mysmtp1.techsatwork.com<\/span><\/p>\n

Once you have set the smtp_server, you can use the built-in stored procedure UTIL_MAIL to send emails like the example here:<\/p>\n

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

You can also use it in a more classier fashion :<\/p>\n

BEGIN<\/span>
\n\u00c2\u00a0 DECLARE v_sender VARCHAR(30);<\/span>
\n\u00c2\u00a0 DECLARE v_recipients VARCHAR(60);<\/span>
\n\u00c2\u00a0 DECLARE v_cc VARCHAR(60);<\/span>
\n\u00c2\u00a0 DECLARE v_bcc VARCHAR(60);<\/span>
\n\u00c2\u00a0 DECLARE v_subj VARCHAR(20);<\/span>
\n\u00c2\u00a0 DECLARE v_msg VARCHAR(200);<\/span><\/p>\n

\u00c2\u00a0SET v_sender = ‘rajudb1@db2server1.com’;<\/span>
\n\u00c2\u00a0SET v_recipients = ‘raju@techsatwork.com’;<\/span>
\n\u00c2\u00a0SET v_cc = NULL;\u00c2\u00a0<\/span>
\n\u00c2\u00a0SET v_bcc = NULL;\u00c2\u00a0<\/span>
\n\u00c2\u00a0SET v_subj = ‘warning email from rajudb1’;<\/span>
\n\u00c2\u00a0SET v_msg = ‘Tablespace1 is nearing 80% full”;<\/span><\/p>\n

\u00c2\u00a0CALL UTL_MAIL.SEND(v_sender, v_recipients, v_cc, v_bcc, v_subj, v_msg);<\/span>
\nEND@<\/span><\/p>\n

You can \u00c2\u00a0execute this just as a sql file (db2 -td@ -svf email_sample.sql). \u00c2\u00a0 The maximum size for message field is 32K. \u00c2\u00a0 Try it on now and let db2 do the emailing for you. \u00c2\u00a0Please comment on how you use this new feature.<\/p>\n","protected":false},"excerpt":{"rendered":"

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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-container-style":"default","site-container-layout":"default","site-sidebar-layout":"default","site-transparent-header":"default","disable-article-header":"default","disable-site-header":"default","disable-site-footer":"default","disable-content-area-spacing":"default","footnotes":""},"categories":[303,3,358],"tags":[660,663,661,667,666,659,662,664,665],"class_list":["post-1144","post","type-post","status-publish","format-standard","hentry","category-database","category-db2","category-how-to","tag-db2-emails","tag-db2-smtp","tag-emailing-from-db2","tag-how-to-send-email-from-stored-procedure","tag-mailing-from-db2-db2-notifications","tag-sending-emails-from-db2","tag-smtp-on-db2","tag-smtp_server","tag-util_mail"],"_links":{"self":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/1144","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1144"}],"version-history":[{"count":6,"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/1144\/revisions"}],"predecessor-version":[{"id":1498,"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/1144\/revisions\/1498"}],"wp:attachment":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1144"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1144"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1144"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}