RE: Subfile "sort" thoughts -- RPG400-L

That's an interesting idea.
I've used user indexes before, but only in the C language on the 400.
So, the thinking is to stuff the value for the "sort by this field" into the
index and put the rest of the data into the other location in the user
index. I seem to recall that User Indexes have an associated space so that
might be a good solution. 
Another option I was considering is the SORT APIs. But since I've never used
them...


Bob Cozzi
Cozzi Consulting
www.rpgiv.com


-----Original Message-----
From: rpg400-l-bounces+cozzi=rpgiv.com@xxxxxxxxxxxx
[mailto:rpg400-l-bounces+cozzi=rpgiv.com@xxxxxxxxxxxx] On Behalf Of
Chevalier, Rick
Sent: Wednesday, August 13, 2003 10:54 AM
To: 'RPG programming on the AS400 / iSeries'
Subject: RE: Subfile "sort" thoughts

Bob,

I'm not completely convinced this would perform well enough given the number
of records you are working with but I'll throw it out as an idea anyway.

What about using a user index to order the records?  The index portion of
each entry can contain the field to sort by which gives flexibility there.
Entries can be written or read in large blocks since the API's work with
buffers so I/O is minimized.  API functionality mimics chain, read, reade
file access.  There are the API calls though.

Just a thought,

Rick

-----Original Message-----
From: Bob cozzi [mailto:cozzi@xxxxxxxxx]
Sent: Wednesday, August 13, 2003 9:52 AM
To: rpgiv@xxxxxxxxxxxxxxx; 'RPG programming on the AS400 / iSeries'
Subject: Subfile "sort" thoughts

I have a situation were I need to help implement a subfile sort.

There is a Work-with panel that contains about 7 fields, all of which the
end-user wants to be able to sort by.

Nothing to unusual about this so far.

But in this situation the file is actually a dynamic file created with an
SQL UNION ALL statement that ends up producing about 9 million records.

I was thinking about a page=size subfile and will offer the end user a
filtering option to weed out unwanted transactions.

What I'm wondering is how to attack the subfile sort in this context; 9
million records+ doesn't lend itself well to any conventional techniques,
such as sorting a multiple occurrence data structure or even dynamic/runtime
querying via SQL or Open Query File as the performance will suck.

So I'm wondering about building an SQL view out of the UNION ALL statement,
and then using SQL further (pre-runtime) to create an index over each of the
7 fields. But I'm not sure if this is the right solution.

Comments? Suggestions?



Bob Cozzi

Cozzi Consulting

www.rpgiv.com

_______________________________________________
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.