[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 683
  • 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

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

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