RE: Secondary Keys

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



 

                    D.BALE@handleman.

                    com                      To:     RPG400-L@midrange.com

                    Sent by:                 cc:

                    owner-rpg400-l@mi        Subject:     Re: Secondary Keys

                    drange.com

 

 

                    12/06/00 02:23 PM

                    Please respond to

                    RPG400-L

 

 





User Index - object type *USRIDX.  You use APIs to create, maintain (load,
sequence, retrieve), and delete.  Once the data's in there, it's VERY fast.
However, not having thought too thoroughly of your requirements in my
original
response, loading the required data into it may take just as long, if not
longer, than the OPNQRYF suggestion you are opposed to.

What kind of and how much data are you talking about?  How dynamic is the
data
in the files you're attempting to join?

I'm not sure I could recommend your "light bulb" idea.  That could be a
monster to develop and set up.

This may all be moot - something I hadn't thought about was the JDUPSEQ
keyword someone else mentioned - if this is workable for you, then a user
index is probably overkill for your application.  Also, I have little
working
knowledge of SQL; someone else mentioned that as a possible solution for
you.

Good luck!

Dan Bale
IT - AS/400
Handleman Company
248-362-4400  Ext. 4952

-------------------------- Original Message --------------------------

Dan,

When you say "user index" are you talking about a work file with only the
index? I think that will still cause to much of a delay when entering the
application.

Or maybe the light bulb just came on. Are you thinking of building the
index in the morning and maintaining it along with the primary file
throughout the day?

Patrick Conner
www.ConnecTown.com
(828) 244-0822





D.BALE@handleman.com
To:     RPG400-L@midrange.com
Sent by:
cc:owner-rpg400-l@midrange.com
Subject:     Re: Secondary Keys

                    12/06/00 10:58 AM

                    Please respond to RPG400-L

It's been awhile, but I do not believe you can define a key field from a
secondary file in a join logical.  My workaround for that has been to use
OPNQRYF to do that, but that may not be practical for an interactive app.

Another possibility for you is to load your data destined for your subfile
into a user index and load the sorted data from there into the subfile.

Dan Bale
IT - AS/400
Handleman Company
248-362-4400  Ext. 4952

-------------------------- Original Message --------------------------

I have a maintenance program that can be displayed with multiple sorts. It
is set up to read different a logical based on what the user keys as a sort
code. However, some information in the subfile is from secondary files and
they want the subfile sorted on those fields. I looked at doing a join
logical, but I can't tell where I'm allowed to use the fields from the
secondary file as key fields. Anyone have any suggestions?

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
+---




+---
| 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
+---
+---
| 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
+---