Re: Checking Thresholds in a file using SQL

I created the Query as an SQL Function and called it from an SQLRPGLE
program.

When trying this on 100,000 records, I found that though the query
statement executed online (under strsql), always ran in a couple of
seconds, when the RPG program was submitted to batch, the job sometimes ran
in a couple of seconds and sometimes took over 90 minutes to complete. The
input file was exactly the same. One job would be done in a few seconds and
the next one would take 90 minutes. And after a few tries it would again
complete in a few seconds.

Any thoughts or suggestions?

I am extremely wary of putting this in production, not knowing why this is
happening, and how to control it.

Vinay



On Wed, Oct 21, 2015 at 11:01 AM, CRPence <crpbottle@xxxxxxxxx> wrote:

On 21-Oct-2015 08:23 -0500, Vinay Gavankar wrote:

<<SNIP>>I have a follow-up question:

The file is a DDS defined multi-member file. It has a logical over it
by K1(not the actual field name by the way), but no index with RRN.

During processing I could build a Table and index on the fly in QTEMP
and copy data to it before running my SQL.

Given that the file may have anywhere between a handful to a couple
of million records, and hundreds of such files are being processed in
a day (by different jobs, one file per job), would it make sense
(performance wise) to go the table/index route or process the data
directly from the input file?

I tend to expect that data-copy activity often will result in one of the
least efficient implementations, so when possible, I avoid that action. If
the data will not be loaded directly in the the file with the desired key
such that either the access path was maintained or [typically better]
delayed\built after the load, then probably I would not _reload_ the data
into such a file merely to make that key available. Implicit in that
comment, is that loading the data directly into the table with the defined
access path, is probably preferable over the current implementation; and
that for larger data sets, changing the maintenance to be effectively
turned off or delayed to be rebuilt only after the load, may have
additional value over forcing the access path to be maintained /during/ the
load. The Change Logical File (CHGLF) has the Maintenance (MAINT)
parameter that enables setting and resetting how\when the key is [re]built
and\or maintained.

An exception to that avoidance would be: If there are many queries run
over that data [and what I offered, the data would get queried multiple
times, in a way that I presume the key-by-RRN included would be useful to
the optimizer] *and* the SQL Query Engine (SQE) would not be creating for
me, that desired\expected access path as a Maintained Temporary Index
(MTI). I expect however, that the MTI would be created with those
attributes\keys [as I doubt there is a restriction on SQE to generate an
MTI that includes RRN], so my having done the data-copy as a data reload
would have added the overhead of yet-another-copy of the data with little
benefit [other than perhaps pre-scheduling the access path (aka index)
builds rather than having them built at run-time]. And that overhead,
being typical, is exactly why I find the data-copy to be the scourge that
often it is revealed to be.


--
Regards, Chuck

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