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')@