Although /processing/ all of the rows _in order to obtain_ the
desired result set, if the FETCH FIRST 100 is not working such that the
result set enables reading more than 100 rows, I would expect that to be
a defect.?
How selective are the ">= 50000" on both files? Is there an index on
that column as the first key? An EVI on that column, if not very
selective? Probably worthwhile to use Visual Explain to review the
implementation, and review for advised INDEX information.
I am not sure if the ALWCPYDTA() feature with the option to disallow
temporaries is valid with any UNION, but that could be used if allowed,
to force use of an [temporary] index. If disallowing temporary copy of
the data is supported for such a query, then CREATE INDEX of the
recommended, to avoid the query implementation requiring a full index
build for the selected rows each time [versus what it is apparently
using now, index, temporaries, and sort].
If it is acceptable to limit the full result set of the UNION data to
100 rows, is it similarly acceptable to limit both of the individual
sets that are then combined in the UNION? That can not be known from
what is given; and what the actual data is may allow it presently, but
it must be valid always, to even consider doing so. Not generally
likely, however if so, then generate a limited set from each SELECT
using a Common Table Expression for each, and then union those limited
sets.
With
CON as ( SELECT CVINST, <<SNIP>>> 'con', RRN(RMSCONPF)
FROM RMSCONPF WHERE CVINST >= 50000
ORDER BY 4, 6, 2
FETCH FIRST ## ROWS ONLY /* 50 or perhaps more rows
to ensure always valid */
,MOR as ( SELECT MTINST, <<SNIP>> 'mor', RRN(RMSMORPF)
FROM RMSMORPF WHERE MTINST >= 50000
ORDER BY 4, 6, 2
FETCH FIRST ## ROWS ONLY /* same comment as above */
select * from CON
union all
select * from MOR
ORDER BY 4, 6, 2
FETCH FIRST 100 ROWS ONLY /* may already be ensured */
OPTIMIZE FOR 20 ROWS FOR READ ONLY /* by now, moot */
FWiW: Given the stated as desired results, a Multi-Format Logical
File, probably with dynamic select for xxINST, may be a good choice for
use with RLA [Row Level Access], instead of using SQL. If such a keyed
DDS LF is created, then with CPYF CRTFILE(*YES) TOFILE(QTEMP/LIMITED)
[could even defer dynamic select to the copy request if the literal
compare value may change], a MFLF might still be best to enable the SQL
to continue to be used, making the SQL a non-UNION select of the copy
generated from the MFLF instead of the SQL.
Regards, Chuck
Bradley V. Stone wrote:
Yes, the problem I seem to be having is a UNION ALL. I added a
FETCH FIRST clause and I'm still getting all bazillion records
into the result set.
I want to tell it "after 100 rows, just stop looking". But I
suppose it's not that easy using a union.
Here's my SQL now:
<!--SELECT CVINST, CVSFX, CVSRNM, CVGVNM, CVOTHN, CVDATE, CVINTP,
CVSTAT, CVBOOK, CVPAGE, 'con', RRN(RMSCONPF)FROM RMSCONPF WHERE
CVINST >= 50000 UNION ALL SELECT MTINST, MTSFX, MTSRNM, MTGVNM,
MTOTHN, MTDATE, MTINTP, MTSTAT, MTBOOK, MTPAGE, 'mor',
RRN(RMSMORPF)FROM RMSMORPF WHERE MTINST >= 50000 ORDER BY 4, 6, 2
FETCH FIRST 100 ROWS ONLY OPTIMIZE FOR 20 ROWS FOR READ ONLY-->
midrange.com
