Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2009
    Posts
    15

    Unanswered: using DB2v9,How to send email from stored procedure?

    How to send email from stored procedure?

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    in developers work there is a sample, how to call an external command/program from within a sp. see os_cmd..
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Dec 2009
    Posts
    15

    using DB2v9,How to send email from stored procedure?

    Happy New Year

    could you please share with me developer work link?

    Thanks

  4. #4
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  5. #5
    Join Date
    Dec 2009
    Posts
    15
    ================================================== ==========
    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.

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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...
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Dec 2009
    Posts
    15
    UTL_SMTP is a package It's belongs to sysibmadm Schema.but susibmadm schema is not in database.

  8. #8
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  9. #9
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •