UDTFs based on sqlrpgle programs performance issues

[LUCAG]

Hi everybody,
I am in the need to build a pivot table from a bunch of different tables (tables non pf-dta since they are generated via ddl and not by dds).
To reach my gol i was thinking to build a UDTFs recycling some already existing rpg routine we use on such tables.
In the example provided by Scott the method to access the data is the legacy mode.
In my scenario i will use Exec Sql.
Do you think that there may be any performance issues? Tables contains millions of rows(records) to be analyzed and to minimize the effort i will re-use some views already in production.

[Scott Klement]

Hi everybody,
I am in the need to build a pivot table from a bunch of different tables (tables non pf-dta since they are generated via ddl and not by dds).
To reach my gol i was thinking to build a UDTFs recycling some already existing rpg routine we use on such tables.

Sorry, I'm not familiar with the term "pivot table." I've heard people say they make these in Excel, but never learned what they were, and not really sure how it relates to an RPG/database discussion.

In the example provided by Scott the method to access the data is the legacy mode.
In my scenario i will use Exec Sql.
Do you think that there may be any performance issues? Tables contains millions of rows(records) to be analyzed and to minimize the effort i will re-use some views already in production.

I assume by "legacy mode" you mean that I used native I/O for the database access? That was done to make the example simpler to an existing RPGer to learn.

There's certainly nothing wrong with using SQL -- you'll have to change the phrase "NO SQL" to the appropriate phrase (reads sql data or modifies sql data).

Performance problems aren't any different between UDTFs vs any other SQL statement. Native I/O when written the right way can be very efficient, but when written the wrong way can perform poorly. The same with SQL, statements where the proper indices and other things are built will perform well, those that aren't well built/configured won't. Really has nothing to do with whether a UDTF is involved.

[LUCAG]

Scott, thanks for your reply and suggestion.
I think you were being ironic about pivot tables

[Scott Klement]

I think you were being ironic about pivot tables

Sorry, I don't understand. Where do you see irony?