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.