Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Calling a DB2 UDF from a stored procedure

Posted on 2009-04-08
5
Medium Priority
?
677 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 46

Expert Comment

by:Kent Olsen
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 46

Accepted Solution

by:
Kent Olsen earned 2000 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 46

Expert Comment

by:Kent Olsen
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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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 (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Suggested Courses

730 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