Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 680
  • Last Modified:

Calling a DB2 UDF from a stored procedure

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
jribble
Asked:
jribble
  • 3
  • 2
1 Solution
 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
jribbleAuthor Commented:
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
 
Kent OlsenData Warehouse Architect / DBACommented:

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
 
jribbleAuthor Commented:
I should read the error messages more closely!  That did it - thanks!
0
 
Kent OlsenData Warehouse Architect / DBACommented:

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now