On 10/18/13 5:41 AM, Alan Cassidy wrote:
Chuck, you're talking about turning the DS-array into rows, right?
Yes. Effectively, a means to "create cursors out of the arrays" as
inquired in the OP. Whether the effect is /easily/ accomplished, given
the current [but unknown] coding and requirements for the OP, is unknown.
Meaning you'd still have to do a temporary table like in QTEMP
using embedded SQL,
No temporary table created, nor using any SQL; still just generating
the data. The existing program already generates the data, but into DS
arrays. A program that would implement a UDF as a table-function [so
too for a scalar-function] could be a CLP or any other non-SQL program;
any use of embedded SQL is not required. For example, I have a CLLE
program defined as a UDTF, that returns each library name in the user
portion of the library list for the current job, as row data. The only
SQL that is required, is definitional; i.e. the CREATE FUNCTION.
and use that to return as a result set, or for the Defined Table
Function? A table function has to be an actual table, after all...
The User Defined Table Function only /logically/ defines a table.
The effect is much more like a VIEW; an effective logical VIEW that
defines a program to select data rather than a SELECT statement. The
UDF defines the interface that will be utilized to obtain the rows and
then to return\present those rows to the SQL in the logically defined
format; the user program identified by the EXTERNAL NAME implements both
obtaining the data [from anywhere, by any means] and returning the data
[to the SQL].
No "actual table" gets created in\by the program that implements the
UDTF, only rows are created; i.e. row-data that matches the logical
definition, the Record Format, of /the table/ that was defined by the
RETURNS TABLE clause of the CREATE FUNCTION statement. However, the
implementation of a query selecting data from that UDTF, *may* effect
the creation of a true-temporary file for that table-function
invocation. FWiW, a truly temporary database file is much less overhead
than any CREATE TABLE that a user could effect, even using the TEMPORARY
keyword, because those are always permanent objects even if created into
the QTEMP library.
The current program the OP has [as alluded], could then change to
invoke the UDTF that will generate a /result set/ using the SET RESULT
SET from a declared cursor. With that change, the program would no
longer require multiple ARRAY result sets; i.e. that program could
return cursors as result-sets, optionally including up to one still
defined as an array, having changed any other ARRAY result sets to a
declared cursor of a SELECT statement that names the UDTF in the
FROM-clause.
My first thought was that you cannot directly plop re-define such
a structure directly to a result table, but I've been surprised
before...
Indeed, not simply and directly re-defined. Instead, a requirement
to /rework/ the code of the existing program. First, creating a new
executable, to produce one row at a time, as the external program that
implements the UDTF. Thus no longer generating each new entry in an
array element, instead, since, generating the data as a row buffer for
each [call_type=] FETCH as was defined by the contract between the
database and the external program; i.e. the contract was the UDTF, as
established by the SQL CREATE FUNCTION ... RETURNS TABLE ..., and that
defines how the program is implemented to interact with the SQL.
Second, the existing program is modified to, instead of returning an
ARRAY as a result-set, returning the cursor that is the query of the UDTF.
Here is a link to an article with actual UDTF sources:
http://www.ibm.com/developerworks/ibmi/library/i-power-of-udtf/
Regards, Chuck
On Thursday, October 17, 2013 6:47 PM CRPence wrote:
On 10/17/13 8:43 AM, Sarvapriya Tripathi wrote:
That is why I wondered if there could be a work around by
creating Cursors out <ed: of> arrays.
The work to generate each /array/ result-set could be reworked
to provide results as a returns-table of a User Defined Table
Function (UDTF), and then the program returning the result sets
would declare the cursor for the:
SELECT ... FROM TABLE(the_udtf()) AS Cor_Nm
midrange.com
