DB2 Version 9.7 for Linux, UNIX, and Windows

UTL_SMTP module

The UTL_SMTP module provides the capability to send email over the Simple Mail Transfer Protocol (SMTP).

The UTL_SMTP module includes the following routines.

Table 1. System-defined routines available in the UTL_SMTP module
Routine Name Description
CLOSE_DATA procedure Ends an email message.
COMMAND procedure Execute an SMTP command.
COMMAND_REPLIES procedure Execute an SMTP command where multiple reply lines are expected.
DATA procedure Specify the body of an email message.
EHLO procedure Perform initial handshaking with an SMTP server and return extended information.
HELO procedure Perform initial handshaking with an SMTP server.
HELP procedure Send the HELP command.
MAIL procedure Start a mail transaction.
NOOP procedure Send the null command.
OPEN_CONNECTION function Open a connection.
OPEN_CONNECTION procedure Open a connection.
OPEN_DATA procedure Send the DATA command.
QUIT procedure Terminate the SMTP session and disconnect.
RCPT procedure Specify the recipient of an email message.
RSET procedure Terminate the current mail transaction.
VRFY procedure Validate an email address.
WRITE_DATA procedure Write a portion of the email message.
WRITE_RAW_DATA procedure Write a portion of the email message consisting of RAW data.
The following table lists the public variables available in the module.
Table 2. System-defined types available in the UTL_SMTP module
Public variable Data type Description
connection RECORD Description of an SMTP connection.
reply RECORD SMTP reply line.
The CONNECTION record type provides a description of an SMTP connection.
ALTER MODULE SYSIBMADM.UTL_SMTP PUBLISH TYPE connection AS ROW
(
  /* name or IP address of the remote host running SMTP server */
    host VARCHAR(255),
  /* SMTP server port number */
    port INTEGER,
  /* transfer timeout in seconds */
    tx_timeout INTEGER,
);
The REPLY record type provides a description of an SMTP reply line. REPLIES is an array of SMTP reply lines.
ALTER MODULE SYSIBMADM.UTL_SMTP PUBLISH TYPE reply AS ROW
(
  /* 3 digit reply code received from the SMTP server */
    code INTEGER,
  /* the text of the message received from the SMTP server */
    text VARCHAR(508)
);

Examples

Example 1: The following procedure constructs and sends a text email message using the UTL_SMTP module.
CREATE OR REPLACE PROCEDURE send_mail(
IN p_sender VARCHAR(4096),
IN p_recipient VARCHAR(4096),
IN p_subj VARCHAR(4096),
IN p_msg VARCHAR(4096),
IN p_mailhost VARCHAR(4096))
SPECIFIC send_mail
LANGUAGE SQL
BEGIN
  DECLARE v_conn UTL_SMTP.CONNECTION;
  DECLARE v_crlf VARCHAR(2);
  DECLARE v_port INTEGER CONSTANT 25;

  SET v_crlf = CHR(13) || CHR(10);
  SET v_conn = UTL_SMTP.OPEN_CONNECTION(p_mailhost, v_port, 10);
  CALL UTL_SMTP.HELO(v_conn, p_mailhost);
  CALL UTL_SMTP.MAIL(v_conn, p_sender);
  CALL UTL_SMTP.RCPT(v_conn, p_recipient);
  CALL UTL_SMTP.DATA(
    v_conn,
    'Date: ' || TO_CHAR(SYSDATE, 'Dy, DD Mon YYYY HH24:MI:SS') || v_crlf ||
    'From: ' || p_sender || v_crlf ||
    'To: ' || p_recipient || v_crlf ||
    'Subject: ' || p_subj || v_crlf ||
    p_msg);
  CALL UTL_SMTP.QUIT(v_conn);
END@

CALL send_mail('bwayne@mycorp.com','pparker@mycorp.com','Holiday Party',
'Are you planning to attend?','smtp.mycorp.com')@
Example 2: The following example uses the OPEN_DATA, WRITE_DATA, and CLOSE_DATA procedures instead of the DATA procedure.
CREATE OR REPLACE PROCEDURE send_mail_2(
IN p_sender VARCHAR(4096), 
IN p_recipient VARCHAR(4096), 
IN p_subj VARCHAR(4096), 
IN p_msg VARCHAR(4096), 
IN p_mailhost VARCHAR(4096)) SPECIFIC send_mail_2
LANGUAGE SQL
BEGIN
  DECLARE v_conn UTL_SMTP.CONNECTION;
  DECLARE v_crlf VARCHAR(2);
  DECLARE v_port INTEGER CONSTANT 25;

  SET v_crlf = CHR(13) || CHR(10);
  SET v_conn = UTL_SMTP.OPEN_CONNECTION(p_mailhost, v_port, 10);
  CALL UTL_SMTP.HELO(v_conn, p_mailhost);
  CALL UTL_SMTP.MAIL(v_conn, p_sender);
  CALL UTL_SMTP.RCPT(v_conn, p_recipient);
  CALL UTL_SMTP.OPEN_DATA(v_conn);
  CALL UTL_SMTP.WRITE_DATA(v_conn, 'From: ' || p_sender || v_crlf);
  CALL UTL_SMTP.WRITE_DATA(v_conn, 'To: ' || p_recipient || v_crlf);
  CALL UTL_SMTP.WRITE_DATA(v_conn, 'Subject: ' || p_subj || v_crlf);
  CALL UTL_SMTP.WRITE_DATA(v_conn, v_crlf || p_msg);
  CALL UTL_SMTP.CLOSE_DATA(v_conn);
  CALL UTL_SMTP.QUIT(v_conn);
END@

CALL send_mail_2('bwayne@mycorp.com','pparker@mycorp.com','Holiday Party',
'Are you planning to attend?','smtp.mycorp.com')@