× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.