Guru: Arranging Query Logic in DB2 for i Routines
January 7, 2019 Michael Sansoterra
Hey, Mike:
We use a stored procedure to return the result set to Java and display the results to a screen. I would like to make this stored procedure put the result set into a temporary table and then do some processing on the temporary table. Is this possible and if so how can I achieve this? Thanks.
Your dilemma is understandable: stored procedure result sets are great when data needs to be returned to a client. However, once generated, the result set cannot be joined, sorted, or stored in a temporary table. Thankfully, a user-defined table function (UDTF) allows a developer to perform additional operations on a query result set.
The easiest way to accomplish this goal is to move the logic of your query from the stored procedure into a user-defined table function. Thereafter, to prevent duplicate query code, the stored procedure can be refactored to use the UDTF to build the result set for your Java program. The UDTF will provide a way to store the result set in a temporary table.
Let’s look at a simple example. Say the below code is your stored procedure that receives a customer ID and returns a result set of customer orders (using IBM i 7.3 but it should work on earlier versions):
CREATE OR REPLACE PROCEDURE GetOrders (@CustomerId IN INT)
LANGUAGE SQL
RESULT SETS 1
NO EXTERNAL ACTION
BEGIN
DECLARE SalesOrders CURSOR FOR
SELECT SalesOrderId,CustomerId,
OrderDate,ShipDate,SubTotal
FROM SalesOrderHeader
WHERE CustomerId=@CustomerId
ORDER BY SalesOrderId;
OPEN SalesOrders;
SET RESULT SETS CURSOR SalesOrders;
END;
The procedure can be invoked as follows:
CALL GetOrders (11091);
The SELECT statement to build the result set contains the important logic and can be incorporated in an SQL UDTF as follows:
CREATE OR REPLACE FUNCTION GetOrders (
@CustomerID INT)
RETURNS TABLE (
SalesOrderId INT,
CustomerId INT,
OrderDate DATE,
ShipDate DATE,
SUBTOTAL DEC(19,4))
LANGUAGE SQL
NO EXTERNAL ACTION
DISALLOW PARALLEL
SET OPTION COMMIT=*NONE,USRPRF=*OWNER,DATFMT=*ISO
RETURN
SELECT SalesOrderId,CustomerId,
OrderDate,ShipDate,SubTotal
FROM SalesOrderHeader
WHERE CustomerId=@CustomerId
ORDER BY SalesOrderId;
Notice the SELECT statement is the same in both routines. The UDTF can be invoked as shown here and will return the same result set as the stored procedure:
SELECT * FROM TABLE(GetOrders (11091)) Orders
Now you can also create a temporary table using the function and the CREATE TABLE AS statement:
CREATE TABLE TEMP_ORDERS AS ( SELECT * FROM TABLE(GetOrders (11091)) Orders ) WITH DATA;
Just a quick word of warning, be careful about using an ORDER BY in a UDTF as it will often be ignored/overridden by a host query.
Unless IBM changed it recently, the CREATE TABLE AS statement doesn’t support substitution parameters, so passing the customer number may require the use of EXECUTE IMMEDIATE. Here is a cheapskate example of how to do this using SQL:
BEGIN
DECLARE @SQL VARCHAR(1000);
DECLARE @CUSTOMER_ID INT DEFAULT 11091;
SET @SQL=
'CREATE TABLE TEMP_ORDERS AS
(SELECT * FROM TABLE(GetOrders (' || VARCHAR(@CUSTOMER_ID) || ')) Orders)
WITH DATA';
EXECUTE IMMEDIATE @SQL;
END;
Another approach to this problem that isn’t as messy as EXECUTE IMMEDIATE is to use a global variable to store the customer ID, since you can pass that variable into the function.
Back to the task at hand, to minimize the duplication of logic and queries, you can replace the original SELECT statement in the stored procedure with a reference to the table function:
CREATE OR REPLACE PROCEDURE GetOrders (@CustomerId IN INT) LANGUAGE SQL RESULT SETS 1 NO EXTERNAL ACTION BEGIN DECLARE SalesOrders CURSOR FOR SELECT Orders.* FROM TABLE(GetOrders(@CustomerID)) ORDERS; OPEN SalesOrders; SET RESULT SETS CURSOR SalesOrders; END;
This technique is beneficial when code reuse is desired. However, code reuse is often harmful to performance so, depending on your situation, you may not want to adopt this approach. At the very least, a table function wrapper is going to impose some overhead on the stored procedure. But if your UDTF logic is simple and your IBM i is at i 7.2 TR4 or later, then DB2 may do you a favor and run your UDTF “inline” which should minimize the performance tax of using a UDTF.
If for some reason your stored procedure is complicated enough so that you can’t reasonably move the code that builds the result set into a UDTF, then it is possible to build an external UDTF that wraps the stored procedure result set. The UDTF invokes the stored procedure (the opposite of what I showed above), captures and returns the result set, which allows a developer to further manipulate the data using DML. In a future tip, I will demonstrate how to create a Java UDTF wrapper to do this.
A general design principle to take away is this: a UDTF is more versatile than a stored procedure for building result sets that may require further operations. If the performance tax is not too high and code reuse is desirable, code your result set queries in UDTFs. This specific advice only applies to building result sets; UDTFs should not necessarily be used to replace your view or materialized query table definitions!
