Re: ODBC connections and QAQQINI -- MIDRANGE-L

You can override the optimization goal in the ODBC connection string...
QUERYOPTIMIZEGOAL

Specifies the optimization goal for queries. This parameter corresponds to
the QAQQINI option called OPTIMIZATION_GOAL. For more information, refer to
the QAQQINI option in the Db2 for i SQL Reference.
Possible values:
• 0 = Use the goal of *ALLIO if extended dynamic support is enabled,
otherwise use the *FIRSTIO goal.
• 1 = *FIRSTIO - Return the first block of data as fast as possible.
• 2 = *ALLIO - Optimize as if the complete result set will be read by the
application. Default: 0

As far as QAQQINI, that can also be set on the connection string
QAQQINILIB
Specifies a query options file library. When a query options file library
is specified the driver will issue the command CHGQRYA passing the library
name for the QRYOPTLIB parameter. The command is issued immediately after
the connection is established. This option should only be used when
debugging problems or when recommended by support as enabling it will
adversely affect performance.

HTH,
Charles

On Thu, Sep 5, 2024 at 3:51 PM Patrik Schindler <poc@xxxxxxxxxx> wrote:

Hello,

the last couple of days I've been skimming through the DB2 for AS/400
Database Programming PDF (SC41-5701-02), and I've learned quite some things!

First of all, I now know precisely what I mean by "SQL is slow compared to
the traditional API calls": Accessing records through the Query Component
and Query Optimizer add considerable delay between pressing enter on a menu
for starting an application program, and the (load paged) subfile content
being displayed on the screen eventually. Of course there is much less
initial processing involved when using the traditional APIs. Note: I'm
using a model 150 running V4R5 for hobbyist purposes.

There's also quite some information about OPNQRYF in said PDF. OPNQRYF can
be hinted to desired query optimization strategies. My attention was caught
by OPTIMIZE(*FIRSTIO).

I've read about the possibility that using strsql in 5250 possibly
optimizes queries differently comparing to e. g. using xDBC. See here:
https://stackoverflow.com/questions/15704878/why-does-my-select-query-take-so-much-longer-to-run-on-the-web-server-than-on-th
— scroll down to the first answer, mentioning *FIRSTIO.

I wonder how I can use a different query optimization file for ODBC
connections. As far as I'm aware, ODBC connections are handled through
QZDASOINIT, but that one gets spawned dynamically for incoming connections.
Now I'm lost for ideas how to not touch the default QAQQINI but enable
quicker first record delivery through ODBC connections.

Maybe changing SYSLIBLE for QSYSWRK would do, pointing to a library with a
custom QAQQINI?

Other ideas?

:wq! PoC

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.