×
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 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.
As an Amazon Associate we earn from qualifying purchases.