Blog

[[{“value”:”In the past, I’ve written about
the inefficiencies caused by referencing QTEMP tables in your query requests. Due to
the performance overhead of this approach, our IBM Expert Labs team is often
employed by clients to help them reduce the number of queries using QTEMP.
While
working with a customer recently, I was reminded of one reason where developers
in the past were forced to use QTEMP queries with stored procedures. While Db2
for i has supported stored procedures which return result sets for a very long
time, there were many releases where those result sets were not available to
invokers using embedded SQL. If the invoking program wanted to consume a result
set returned by a stored procedure, the program had to use an API-based
interface like the SQL Call Level Interface (CLI) or JDBC. So, if you were an RPG or COBOL programmer
you could use embedded SQL to call a stored procedure, but you were out of luck
when it came to using embedded SQL to consuming the contents of a result set
returned by a stored procedure.

This
limitation was what drove some developers to use QTEMP queries. The basic idea
is that if the stored procedure copied the contents of its result set into a
QTEMP table, then the invoker of the stored procedure just needed to know the
name of the QTEMP table in order to be able to consume the result set with a
simple query.

Here’s
an example of a logic from a stored procedure using the QTEMP technique to
return a result set to the invoker.
…– Create QTEMP result set table if it doesn’t exist in job yet
DECLARE CONTINUE HANDLER FOR SQLSTATE ‘42704’
CREATE TABLE qtemp.results
AS (SELECT ordID, ordCustID FROM orders
WHERE ordDate = inputDate) WITH NO DATA;

— Clear our result set rows from prior call
DELETE FROM qtemp.results;

— Populate with result set rows
INSERT INTO qtemp.results
(SELECT ordID, ordCustID FROM orders WHERE ordDate = inputDate);
…

The
invoker would then declare a cursor over the QTEMP table to consume the result
set with this simple query: SELECT ordID, ordCustID FROM qtemp.results. This
query is one reason that the QTEMP solution has slower performance. The
application needs to one run query to populate the QTEMP table and a second
query to unload the QTEMP table.

The
good news is that there’s a simpler and faster solution which has been
available since the IBM i 7.1 release. This solution is made possible through
Db2’s support for result set locator variables. Think of result set locator
variables like pointers. These result set locators enable the stored procedure
invoker to simply point at the result set returned by the procedure in order to
consume it. This approach is more efficient since it doesn’t require a copy of
the result set to be made like the QTEMP approach.

Using
the result set locators support, a stored procedure uses an SQL cursor to make
the result set contents available to the invoker. The following SQL
demonstrates how that is done.
CREATE PROCEDURE GetOrders()
LANGUAGE SQL
DYNAMIC RESULT SETS 1
BEGIN

DECLARE ordCursor CURSOR WITH RETURN FOR
(SELECT ordID, ordCustID FROM orders
WHERE ordDate = inputDate);

OPEN ordCursor;

END

The
consumption of this stored procedure’s result set is enabled by result set
locators as shown in the following snippet of SQL code.
DECLARE rs1 DECLARERESULT_SET_LOCATOR VARYING;

ASSOCIATE LOCATOR(rs1) WITH PROCEDURE GetOrders;

ALLOCATE rs1Cursor CURSOR FOR RESULT SET rs1;

FETCH rs1Cursor INTO ordIdVar, custIdVar;
…

After
declaring variable rs1 as a result set locator, the first step is executing the
ASSOCIATE LOCATOR statement to point the rs1 variable at the result set
returned by the GetOrders stored procedure.
The ALLOCATE CURSOR statement is the final step that needs to be
performed since a cursor is required to retrieve the rows contained in the
result set. In this example, the a cursor named rs1Cursor is allocated to fetch
the rows from the stored procedure result.

Now
that the two different coding methodologies have been compared, let’s talk
about performance. In my testing, I measured the performance of repeated calls
to the stored procedure in a single job. With distributed applications, it’s
very common for the same stored procedure to be called over and over again
within a database connection or job. My performance tests showed the
QTEMP-based solution to be 2-4.5x slower than the solution using result
set locators. The result set locator
performance advantage grew as the number of stored procedure calls increased.
When comparing the performance of 25 calls, the result set locator solution was
2x faster. The performance advantage of result set locators grew to 3.8x faster
with 150 procedure calls and to 4.5x faster with 300 procedure calls.

In my
performance tests, the stored procedure result set contained 25 rows. Thus,
each call to the QTEMP-based stored procedure results in 25 rows being copied
into the QTEMP table. The QTEMP solution performance would slow even further
with result sets that are larger in size. In addition, if the stored procedures
were being called in multiple jobs, the QTEMP solution would suffer from the
Plan Cache inefficiencies that I highlighted in my original QTEMP article.

There are some cases
where a QTEMP-based solution is the winning choice, but hopefully it’s now clear
that using a QTEMP table to return a stored procedure result set is clearly not
one of those situations.”}]] Read More

As I See It: The Forgotten Ones Doug Bidwell

IBM i PTF Guide, Volume 27, Numbers 3 And 4 Doug Bidwell

Talsco Weekly: Altcoins Positioned for 2025 Success patrick staudacher

QTEMP Performance Loses Again [email protected] (Kent Milligan – IBM Technology Expert Labs)

Database and Security Journaling on the IBM i Doug Demagistris