DB2 – how to call a stored procedure that returns a result set in another user defined table function

db2db2-10.5db2-luwstored-procedures

I have a db2 stored procedure that takes in some parameters, gets some data from somewhere and then returns a result set through a cursor.

Now I want to write a table function in db2, that will call this stored procedure, read from the result set and return the data in the result set as a table (eventually I want to use this table function in a join).

I would like to know if this is permitted in db2 (we're using DB2 v10.5), i.e. execute a stored procedure in a table function and fetch and read from the result set from the stored procedure.
If so, what is the right syntax for calling the stored procedure and reading the result set inside a table function in db2?
Thanks!

Best Answer

What you want to accomplish is certainly possible by using a pipelined table function, but it isn't necessarily a wise thing to do. For any non-trivial result set row-based processing is always significantly slower than set-based processing. In addition to that, by hiding your data access logic in a table function you deprive the query optimizer of any information about the statistics and other physical characteristics of the underlying tables and indexes. As a result, performance of such a chimera will lie anywhere between suboptimal and unacceptable.

Consider rethinking your approach; in most cases you should be able to implement your data retrieval and manipulation logic in a single query, may be wrapped in a view for convenience.

If you, however, insist on shooting yourself and those who come after you in your collective feet, here's how you might go about doing that.

CREATE OR REPLACE PROCEDURE inner 
LANGUAGE SQL
DYNAMIC RESULT SETS 1 
READS SQL DATA -- (1)
BEGIN
DECLARE c1 CURSOR 
WITH RETURN -- (2)
FOR SELECT tabschema, tabname FROM syscat.tables;
OPEN c1;
END

Footnotes:

  1. The READS SQL DATA here is important; a table function cannot have side effects, and so shouldn't the stored procedure it calls. We enforce this contract at compilation time.

  2. Make sure Db2 knows we want data in the caller, otherwise it will optimize it away.

Then the function itself:

CREATE OR REPLACE FUNCTION smitf()
RETURNS TABLE (
    out_schema VARCHAR(128), out_name VARCHAR(128)
)
READS SQL DATA
BEGIN 
DECLARE has_rows INT DEFAULT 1;
DECLARE schema_name VARCHAR(128);
DECLARE table_name VARCHAR(128);
DECLARE res1 RESULT_SET_LOCATOR VARYING;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET has_rows = 0;
CALL inner();
ASSOCIATE RESULT SET LOCATORS (res1) WITH PROCEDURE inner;
ALLOCATE cur1 CURSOR FOR RESULT SET res1;
FETCH cur1 INTO schema_name, table_name;
WHILE has_rows > 0 DO -- some bogus processing
    IF table_name NOT LIKE 'A%' THEN
        PIPE (LOWER(schema_name), LOWER(table_name)); -- magic is here
    END IF;
    FETCH cur1 INTO schema_name, table_name;
END WHILE;
RETURN;
END

and call it:

SELECT * FROM TABLE (smitf() CARDINALITY 100) WHERE ...

The CARDINALITY clause lets you tell the optimizer how many rows you expect the function to return, but it may not take this information into account, and even if it does, it may not help.

Related Question