Re: AW: More, Re: Questions about RPG service program as an SQL UDF

On 28 Feb 2013 12:18, James H. H. Lampert wrote:

If I do a SELECT * from the censored VIEW, in a STRSQL session, I
get results even faster than I do for counting the records (too fast
to manually stopwatch, albeit noticeably slower than the uncensored
view), and I can easily jump to the end without significant delay.

The session attributes can play a role. For the latter comment about getting to the end of the data... if the "Data Refresh" (REFRESH) parameter option is *ALWAYS versus *FORWARD, a report is not generated for every row prior to getting to the bottom. Instead the End is found and an effective scroll back ~19 rows, then read forward and generate the report for only those ~19 rows. There is also the Allow Copy Data (ALWCPYDTA) setting that can play a role. However most notable are the default optimization, isolation, and default update capabilities in STRSQL. The default there is effectively to have the same as if the following clauses were added: OPTIMIZE FOR 19 ROWS FOR READ ONLY WITH NC.

But executing a SELECT * from the censored VIEW via Squirrel SQL
seems to be a much slower process. I'm seeing QSYS2/QSQPTABL open
5 separate times, each one showing more I/O than the censored view
itself, and one of them showing about 20 times as much I/O.

The implementation of a UDF [only in older releases?] is via a cursor using that single-row TABLE, QSQPTABL in QSYS2; i.e. SELECT CENSORED(arg1, arg2) FROM QSYS2/QSQPTABL is how the data from the UDF is obtained. The same effect for implementation by such a cursor would be seen also in a SQL request to SET MySQLvar=CENSORED(arg1,arg2)

It might be worth trying a test with those three clauses noted above, to see if one of them might be a part of the problem.

I suppose there is a connection setting to establish the "copy data", but that is probably not an issue here... And even if not in the connection, perhaps available via the query initialization attributes?