Re: SQL Tools and Performance

On 12-Mar-2015 08:18 -0500, Gerald Kern wrote:

<<SNIP>>
I have a relatively simple SQL statement, that when run on a green
screen via STRSQL takes less than 3 seconds to complete. The result
set contains about 600,000 records.

While the query may have /completed/, the fetching of the data likely did not. Presumably the output was run with output directed to the display rather than to a printer file [thus spooled] or to a database output file [effective OutFile()]. Set the /Data Refresh/ option to *FORWARD, then type *BOT in the report positioning and press Enter to force all the data to be read\FETCHed from the opened query.

However, if I use the Database Development perspective in RDi, or the
SQL Explorer plugin in Rdi or the standalone product AQT (advanced
query too, which supports DB2 for i), the same SQL statement takes
considerably longer and by considerably I mean upwards of 15 minutes
- if it does complete at all. (I like to use these tools because they
offer options for exporting the result set to excel which is great
for ad hoc requests.)

Often client interface requests assume an effective /batch/ operation, the ability to update the data, and run under isolation. Adding the following clauses to the end of a SELECT query will override explicitly that the request should be made as read-only, should be run without isolation, and should perform with the intent to return control with a subset of the results rather than the complete\final result-set:
OPTIMIZE FOR 20 ROWS FOR FETCH ONLY WITH NC

Does anyone know of any tricks to improve the performance of these
type of ODBC/JDBC connections so that they might take advantage of
superior SQL functionality available as when running SQL from
STRSQL?

The code processing the SQL is the same, irrespective the interface; i.e. neither interface then, is inherently superior to the other, as both are the same SQL engine processing the [same] input of dynamic SQL statements. However the Work Management under which they run [e.g. storage pools, priorities] play a big role; the *INTERACT is likely very different than the WM setup used for the client DB connections, for example.

By establishing similar performance-related environments along with similar /hints/ or requirements that will inputs to the optimizer for both interfaces, the performance should be quite close to identical [excepting the time to move the data /over the wire/ to the client] between the request local to the server and the request coming from the client.

The Start SQL Interactive Session (STRSQL) session will have established certain performance-related aspects, visible behind the F13=Services. Playing some role in impacts to performance are each of Commitment Control (COMMIT) [aka Isolation level], Allow Copy Data (ALWCPYDTA), Data Refresh, Sort Sequence (SRTSEQ) along with Language Identifier (LANGID), and SELECT Output (effective OUTPUT). For the latter, with 1=Display having been chosen, adjusting the N-ROWS of the OPTIMIZE clause to represent obtaining a /screen-full/ of data is implied, else OPTIMIZE FOR ALL ROWS is implied; the same operative assumptions do not apply globally across interfaces to the SQL engine, so some clauses allow providing /hints/ about what is desirable. Using such clauses allow each interface to mimic the other; other attributes can similarly be matched betwixt, but there may not be an interface as simple as a clause appended to the statement, such that other means such as connection options [or possibly query initialization options (INI aka QAQQINI) of Change Query Attributes (CHGQRYA) or equivalent in some cases] may be required to be established for the session rather than merely for the statement.