RE: Unlimited size result sets

Good idea. but parameter d) could be the various sort order(s).  Which 
fields and ascending or descending.

Rob Berendt
-- 
"All creatures will make merry... under pain of death."
-Ming the Merciless (Flash Gordon)




"John Taylor" <lists@xxxxxxxxxxxxxxxxxxxx> 
Sent by: rpg400-l-bounces+rob=dekko.com@xxxxxxxxxxxx
12/18/2003 01:43 PM
Please respond to
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>


To
"'RPG programming on the AS400 / iSeries'" <rpg400-l@xxxxxxxxxxxx>
cc

Fax to

Subject
RE: Unlimited size result sets






Buck,

Given your situation, I'd try real hard to keep things simple. Replacing
an existing stored procedure with a sockets solution will complicate
things more without satisfying your Java programmers request. 

Here's how I'd do this:

1) Create the necessary UDF's to allow for an SQL select statement.
2) Create a stored procedure with following input parameter(s):

                   Required Parm
                   a) inSortOrder

        Optional Parms
        b) inSelectionCriteria_1
                   c) inSelectionCriteria_2
                   ......

3) Within the stored proc, build your SQL statement dynamically based on
the column(s) to sort, and the selection criteria.

4) Execute the statement, and return the result set to the caller.

Your Java pgmr will be responsible for specifying the column(s) to sort
on, and the selection critera (if any) on each call to the stored
procedure. Once she has the result set, she can choose to read 50
records, or 500,000. It's not your problem anymore, because you've given
her the option to sort on the client or the server. 

She'll be able to scroll through the complete dataset without having to
worry about maintaining start/end keys, and she can change the sort
order with one call (presumably in the onClick event of each table
column).


Regards,

John Taylor


> -----Original Message-----
> From: rpg400-l-bounces@xxxxxxxxxxxx 
> [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Buck
> Sent: Thursday, December 18, 2003 11:03 AM
> To: rpg400-l@xxxxxxxxxxxx
> Subject: Re: Unlimited size result sets
> 
> 
> John said:
> 
> > We aren't talking about the real world. We are talking about a Java 
> > Programmer wanting to prove her theory that loading the entire 
> > database to an in-memory program and flash sorts is faster than 
> > recursive calls. Let
> her
> > have her 15 minutes. When it ends up in the drink, we can 
> get back to
> issues
> > that really matter.
> 
> Yes, you've hit it exactly.  And since I am not a Java 
> Programmer, my advice on the matter is less than interesting 
> to Those Who Must Be Obeyed ('You are an iSeries guy.  You do 
> not know about Java.')  Regarding the socket solution; we've 
> bandied that about, and we were trying to come up with 
> something that could be swapped out as simply as possible 
> when the network guys come track me down.  Since we're 
> already using a stored procedure which returns a result set, 
> the obvious thing is to see how large a result set RPG can 
> return.  It looks like 16Mb is the reasonable limit without 
> getting into Teraspace.
> 
> There isn't a way to reduce the dataset: it is an itemised 
> corporate telephone bill for one month, and the spec calls 
> for the end user to be able to see it all.  Not my spec, but 
> definitely my problem.  For the record, my proposal is to 
> limit the result set to something in the neighbourhood of a 
> thousand rows, and use more sophisticated selection criteria 
> (in effect, alter the spec.)  One issue is that the database 
> does not currently contain enough columns which are 
> 'interesting' to the end-user; things like their internal 
> general ledger account and so on.
> 
> Thanks for the advice!
>   --buck
> 

_______________________________________________
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.