Re: Checking Thresholds in a file using SQL

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.