Solved

Calling a DB2 UDF from a stored procedure

Posted on 2009-04-08
5
660 Views
Last Modified: 2012-05-06
I am trying to call a UDF from a stored procedure.  I can call the UDF using SQL, but when I put that SQL in my stored procedure, it has trouble parsing the SQL.

Here is the error I get:

DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "GPMQ" was found following "ail', ''))
FROM".  Expected tokens may include:  "INTO".  LINE NUMBER=48.  SQLSTATE=42601

The SQL and stored procedure are in the Code section.
Here is the SQL that runs fine:
 

SELECT (GPMQ.rpamsUDF(ROUTE_C, 'TPM_SUMMARY', '1111', QNAME, QMANAGER, SERVER_URL, MQ_CHANNEL, MQ_PORT, 

                          APPL_ID, MSG_ID, 'TPM Summary', 'Detail', '')) 
 

Here is my stored procedure:
 
 

DROP PROCEDURE STL.TPM_SUMMARY_REPORT         $ 
 

------------------------------------------------------------------------------------------------ 

CREATE PROCEDURE STL.TPM_SUMMARY_REPORT (OUT p_summary_report VARCHAR(500))

    SPECIFIC STL.TPM_SUMMARY_REPORT

------------------------------------------------------------------------

-- SQL

------------------------------------------------------------------------

P1: BEGIN NOT ATOMIC

    DECLARE v_summary_report VARCHAR(500);

    DECLARE v_value VARCHAR(30);

    DECLARE v_not_found INTEGER DEFAULT 0;

    DECLARE v_ROUTE_C CHAR(5);

    DECLARE v_QNAME CHAR(20);

    DECLARE v_QMANAGER CHAR(20);

    DECLARE v_SERVER_URL CHAR(40);

    DECLARE v_MQ_CHANNEL CHAR(30);

    DECLARE v_MQ_PORT INTEGER;

    DECLARE v_APPL_ID CHAR(20);

    DECLARE v_MSG_ID CHAR(20);

    

    DECLARE tpm_sum_rpt_cursor CURSOR FOR

        SELECT 'Date: ' || cast(date(sent_ts) as char(10)) || ' Count: ' || cast(count(*) as char(10)) 

        From STL.TMS_FREIGHT_LOG  

        WHERE SENT = 'Y' 

          AND DATE(sent_ts) > CURRENT DATE - 14 days 

          GROUP BY DATE(sent_ts) ORDER BY DATE(sent_ts);
 

    OPEN tpm_sum_rpt_cursor;

    SET v_not_found = 0;

    FETCH tpm_sum_rpt_cursor INTO v_value;

    WHILE v_not_found = 0 DO

       IF v_summary_report IS NULL THEN

            SET v_summary_report = v_value;

       ELSE

            VALUES (v_summary_report || ' ' || v_value) INTO v_summary_report;

       END IF;

       FETCH tpm_sum_rpt_cursor INTO v_value;

    END WHILE;

    CLOSE tpm_sum_rpt_cursor;
 

    SELECT (GPMQ.rpamsUDF(ROUTE_C, 'TPM_SUMMARY', '1111', QNAME, QMANAGER, SERVER_URL, MQ_CHANNEL, MQ_PORT, 

                          APPL_ID, MSG_ID, 'TPM Summary', 'Detail', '')) 

    FROM GPMQ.RPAMS_ROUTER WHERE ROUTE_C='RT015';
 

    -- We use FATAL to ensure that this startup message is always logged.

    CALL STL.LOG_FATAL(NULL,'----- Ran TPM Summary Report -------');

    CALL STL.LOG_FATAL(NULL,'Results: ' || v_summary_report);
 

    SET p_summary_report = v_summary_report;

END P1

$

Open in new window

0
Comment
Question by:jribble
  • 3
  • 2
5 Comments
 
LVL 45

Expert Comment

by:Kdo
ID: 24097347
Hi jribble,

I'm hard pressed to see that the SQL "works fine".  SQL doesn't normally allow parenthesis around the element list, and DB2 requires that a SELECT statement have a FROM clause.

Try this:

SELECT GPMQ.rpamsUDF(ROUTE_C, 'TPM_SUMMARY', '1111', QNAME, QMANAGER, SERVER_URL, MQ_CHANNEL, MQ_PORT, APPL_ID, MSG_ID, 'TPM Summary', 'Detail', '')
FROM {yourtable}



Good Luck,
Kent

0
 

Author Comment

by:jribble
ID: 24097599
Sorry, I forgot the FROM clause when I pasted the SQL.  I dropped the parenthesis and the SQL still runs (maybe it ran preivously because I am using a query tool from which to run the query) .  Here is my updated SQL:

SELECT GPMQ.rpamsUDF(ROUTE_C, 'TPM_SUMMARY', '1111', QNAME, QMANAGER, SERVER_URL, MQ_CHANNEL, MQ_PORT, APPL_ID, MSG_ID, 'TPM Summary', 'Detail', '') FROM GPMQ.RPAMS_ROUTER WHERE ROUTE_C='RT015'

When I drop the parenthesis from my stored procedure:

    SELECT GPMQ.rpamsUDF(ROUTE_C, 'TPM_SUMMARY', '1111', QNAME, QMANAGER, SERVER_URL, MQ_CHANNEL, MQ_PORT,
                          APPL_ID, MSG_ID, 'TPM Summary', 'Detail', '') FROM GPMQ.RPAMS_ROUTER WHERE ROUTE_C='RT015';

I get the same error.



0
 
LVL 45

Accepted Solution

by:
Kdo earned 500 total points
ID: 24097682

Ahha...

Running that query in a stored procedure does nothing (except eat up system resources).  DB2 won't maintain a cursor for the query and the results of the query aren't stored anywhere.

The message, 'Expected tokens may include:  "INTO".' is DB2's rather confusing way of telling you that.

Modify the query to put the result into a variable:

SELECT ... INTO {variable name} FROM ...

or insert the results into another table:

INSERT INTO ... SELECT ... FROM ...

or define the query as a cursor

DECLARE C1 cursor for SELECT ... FROM ...


Good Luck,
Kent
0
 

Author Closing Comment

by:jribble
ID: 31568025
I should read the error messages more closely!  That did it - thanks!
0
 
LVL 45

Expert Comment

by:Kdo
ID: 24097998

IBM's error messages are typically only slightly better than, "general failure to do right".

Love their products, but they do need to work on their user-friendliness.   :)


0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now