RE: Secondary Keys

["Shaw, David" <dshaw@xxxxxxxxxxx>]

Stan,

Embedded SQL uses the query engine the same way that OPNQRYF does, so any
difference between them would solely be due to the way that the two
interfaces surface the tuning controls.  If the key field(s) that Patrick
needs to sort on are ONLY in the secondary file, it's possible that OPNQRYF
could be "persuaded" to not build the temporary file using the
ALWCPYDTA(*NO) OPTIMIZE(*FIRSTIO) combination that Ken Sims suggested - note
that if it needs to build the temp file but has ALWCPYDTA(*NO), it will fail
with an error message to that effect.  I think embedded SQL can also be
"persuaded", I just don't know without reading and experimenting how to do
that right now.

If there are key fields from BOTH physicals in the sort, I don't believe the
engine can build an index without a temporary file.  If a temporary file is
acceptable in the application design, consideration should be given to
allowing the engine to use a sort rather than an index build by specifying
ALWCPYDTA(*OPTIMIZE) - the online help for the ALWCPYDTA parameter explains
very clearly what other parameter settings are required for this, when it
helps, and when it hurts.

In considering the temporary file, keep in mind that if the selected result
set is small, building it can be very quick.  Another possibility is to
build the temp file yourself, using CPYF or a program, then run OPNQRYF or
SQL over the result.  This can perform very well, if the result set is small
and easily extracted.  Since this is a maintenance program, it might be
worthwhile to build the temp yourself, then update both it AND the base
data, rather than refreshing it after every update.  I know it's not very
elegant, but it can provide the same effect with much faster response for
the user.

Dave Shaw
Spartan International, Inc.
Spartanburg, SC
To subscribe to the MAPICS-L mailing list send email to
MAPICS-L-SUB@midrange.com or go to www.midrange.com and follow the
instructions.

-----Original Message-----
From: MCPARTLAND, Stan [mailto:stanley.mcpartland@bently.com]

Patrick,

Since your data is very dynamic OPNQRYF is probably not a solution because
of the temporary result file generated by OPNQRYF when there are keys in the
secondary files.  OPNQRYF solves the no keys in secondary file limitation by
copying all data to a new physical file.  Updates to the database are not
reflected in the temporary result file and vice versa.  I suspect imbedded
SQL may do the same, but I've never run a test.  Does anyone know how
imbedded SQL handles this situation?

Regards,
Stan

-----Original Message-----
From: Contractor1@Parkdalemills.com
[mailto:Contractor1@Parkdalemills.com]
Sent: Wednesday, 06 December, 2000 1:33 PM
To: RPG400-L@midrange.com
Subject: Re: Secondary Keys



I didn't like the "light bulb" either. Not knowing about *UsrIdx objects I
thought you were describing a work file. So, nevermind there. Thanks for
clearing it up for me.

The data will be very dynamic because the join is for the maintenance
program. Every time the user goes into this application they potentially
change the data. The file is a product routings file. It contains location,
product base attributes, product option attributes, and time data. We are
implementing I2. At first it was only a few thousand records, but one of
the product attributes went from no decimal positions to one and is now be
considered for two decimal positions. So, a few thousand will now be a few
hundred thousand.

For those of you assuming a normalized database. Forget about it. The
attributes are the key values.

I'm not opposed the OpnQryF if that is the best solution. It's been a long
time since I've used SQLRPG and I didn't even know of the *UsrIdx. I'm
looking for what is best. Which probably means some form of a redesign with
a base product routings and a program to explode the actual individual
routings.

Patrick Conner
www.ConnecTown.com
(828) 244-0822
+---
| This is the RPG/400 Mailing List!
| To submit a new message, send your mail to RPG400-L@midrange.com.
| To subscribe to this list send email to RPG400-L-SUB@midrange.com.
| To unsubscribe from this list send email to RPG400-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---