How to send email from stored procedure?

in developers work there is a sample, how to call an external command/program from within a sp. see os_cmd..

Happy New Year

could you please share with me developer work link?

Thanks

have a look here
Yet Another Article on Advanced Scripting

================================================== ==========
About DB2 Administration Tools Environment
================================================== ==========
DB2 administration tools level:
Product identifier SQL09013
Level identifier 01040107
Level DB2 v9.1.300.257
Build level s070719
PTF WR21392
================================================== ==========
Java development kit (JDK):
Level IBM Corporation 1.5.0
================================================== ==========

WHile running procedure i am getting following error
========================================
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0204N "UTL_SMTP.CONNECTION" is an undefined name. LINE NUMBER=10.
SQLSTATE=42704

SQL0204N "UTL_SMTP.CONNECTION " is an undefined name.
==========================================

CREATE PROCEDURE SSOGDF.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 DEFAULT 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:MIS') || 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@

Please guide me.

You didn't create the UTL_SMTP.OPEN_CONNECTION function or the UTL_SMTP.MAIL stored procedures.

In any case, the above approach is bound to be very problematic. You carry connection information from one UDF/stored procedure call to the next - but you don't even know if the UDF/stored procedure will be executed in the same process as the previous call. So if the data type UTL_SMTP.CONNECTION (whatever that is) does not include all the information of the email to be sent, this is shaky at best.

p.s: To really respond to your problem, you should let us know where the UTL_SMTP stuff comes from...

UTL_SMTP is a package It's belongs to sysibmadm Schema.but susibmadm schema is not in database.

Maybe you didn't follow some upgrade procedure at some point? Try running the "db2updv9" utility... If that doesn't help, you may have a DB2 version that doesn't match with your documentation, i.e. those functions are not yet in the version you are running.

Looks complicated..... Why not the KISS approach:
- it is easy (so I am told) to send an email from java
- you can use the java-language for Stored Procedures
- you can call one SP from another SP
- so, why not create one dedicated "send-mail-java" SP and call that when required?