In the PIPE: Compiled SQL PL and PL/SQL Table Functions
Comment (1) Visits (14107)
IntroductionA table function, as you will likely know, is a function that returns a number of rows instead of a single scalar value. Table functions can therefore be invoked in the FROM clause of an SQL statement, while scalar functions get invoked in expressions.
Table functions are nothing new to DB2. They have been around with C and Java implementations since DB2 V5.1.
And in DB2 7.1 we made them available in inline SQL PL by using the RETURN statement as the body of the table function.
The C and Java table function implementation however, is fundamentally different compared to that of an inline SQL PL table function.
In the host language solution the table function body would be driven by DB2 in multiple phases.
In host languages the function is called in up to 5 modes:
This concept allows the table function to execute only the amount of code needed to return the required rows, one row at a time.
After each FETCH, control is returned to the invoking SQL and the table function remains dormant until it is either called to CLOSE or to FETCH another row.
By contrast, for inline SQL PL table function there is one RETURN statement which produces the entire result set. This means that the table function produces the entire result set in order to process even the first FETCH.
In some cases, producing this entire result set requires defining and populating a temporary table in the SQL PL table function if you don't want to write an external table function in C or Java.
With the new PIPE statement in Fixpack 2 of Version 10.1, you can now write this table function in SQL PL without directly defining a temporary table or an array.
Let's look at an example of using the PIPE in table function. For illustration purposes, I've defined a simple table function called NEXT52 that returns the date for the same day of the week for the next 52 weeks, along with the associated ISO week number.
create or replace function next52 (start_ts timestamp)
Now we use this function in the from clause of a query with an input date.
select * from tabl
But this query is returning all the rows anyway and does not really take advantage of piping the rows. So, I'll just fetch the first 5 rows.
select * from tabl
That worked, but how do you know the function stopped processing after producing the fifth row?
LOOPCOUNT in NEXT52
To illustrate this, I introduce a global variable called LOOPCOUNT as a way to count the iterations within the function. A global variable is global to my connected session and therefore can be assigned within the function and be checked after running the query. I then replace the NEXT52 function to include some additional lines.
create or replace variable loopcount integer
The LOOPCOUNT global variable is initialized to 0 and then incremented at the end of the WHILE loop. I've thrown in another assignment after the WHILE loop to add 1000 so that we can see if the function gets to that part of the logic.
A value of 1052 means that we ran through the WHILE loop all 52 times and then processed to the end of the function.
select * from tabl
Interesting. It returns 5 rows and only increments LOOPCOUNT 4 times. This means that once it produced the fifth row, control was never returned to NEXT52 function. It stopped processing!
This illustrates that if your query that uses the table function does not fetch all the rows that could be piped out, the function effectively terminates at the last PIPE statement that was produced a row. Therefore, you should not expect to process any more statements after the PIPE statement if your cursor closes before reading all of the rows. Unlike the external table functions, there is no additional invocation at CLOSE of the cursor.
I've demonstrated this behaviour using the FETCH FIRST clause on the query, but the same thing happens processing a cursor. When you stop issuing FETCH statements before fetching all the possible rows that the function could produce, the function stops processing at the PIPE statement that produced the last fetched row if blocking is not used for the cursor. If blocking is used for the cursor, the function stops at the last PIPE statement of the last row fetched into the blocking buffer used by the cursor.
Go ahead and put your data in the pipe! Use of the PIPE statement in a table function to create a pipelined function that generates the result set one row at a time.
Posted by: Rick Swagerman