It's UNION ALL.
I have a multiformat logical looking at 30 tables and columns over millions
of records in a transaction-heavy environment to provide a very
high-function shipment tracing application with subsecond response. Many
customers' programmers think there's some hog of a background process at
work when it's just a big ol' logical. It's been fun to demonstrate this
feature--I'm yakking along and watching users point at the screen, nudge
each other, and whisper about how this could affect their customer service
and web applications. Bottom line: the effort-to-value ratio is off the
chart.
But we have to grow up and I've built an SQL version using UNION ALL. The
hard part was keeping all the columns the same size across every format
(rename and redefine columns to accomplish this) and IIRC SQL was, overall,
fussier than DDS in this regard. DDS and SQL require the key fields to
have identical attributes (and you'll be using *NONE) but you can have a
variable number of non-key fields in DDS. In database terms, a multiformat
logical can be a "covered" index but that's not necessarily true with a
UNION ALL when you're looking at multiple tables with varying numbers of
columns.
A notable gotcha is the inability to have an ORDER BY on a view but I'm
working on a straight SELECT implementation to test performance.
The downside of the MF approach is recreating/rebuilding can take a long
time, even with today's hardware.
Depending upon the application, an alternate solution is to build a single
table to hold your search data and then populate that data via triggers.
The after-insert trigger writes a new row; the after-update trigger updates
the row; the after-delete trigger tags the row as deleted. This is a more
robust design because a multiformat logical can be put out of action if an
underlying PF is undergoing a system operation (RSTxxx, RGZPFM, post-IPML
index rebuilt).
On Tue, Aug 1, 2023 at 2:08 PM Justin Taylor <jtaylor.0ab@xxxxxxxxx> wrote:
I have a multi-format LF that combines two PFs. That works fine for RPG
RLA, but not SQL (SQLSTATE 42857). I'm sure I've read somewhere that it's
possible to do this with a SQL index, which would allow both keyed RLA and
SQL. For the life of me, I can't find it.
Can someone throw me a bone?
--
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.
midrange.com
