Re: RRN and Database Files

["Charles Wilt" <charles.wilt@xxxxxxxxx>]

I should add that with v5r4, IBM added the ROW_NUMBER() function that
you can use to return page at a time.
http://www.code400.com/forum/archive/index.php?t-4869.html

with x as (Select Row_Number() Over(order By Key1, Key2, ... KeyN) as RowNbr,
a.*
from MyTable a
Where ....
order by Key1, Key2, .... KeyN)
select * from x
where RowNbr between 10 and 20
Order By RowNbr

I don't know which is faster....

Charles

On Fri, Jan 9, 2009 at 8:45 AM, Charles Wilt <charles.wilt@xxxxxxxxx> wrote:
> First off, don't use RRN if you're looking for performance.
>
> For reasons unknown, the query engine on the i will do a full table
> scan when you have RRN in the where clause of a statement.
>
> Try this on a large file:
>  select * from someFile A where rrn(a) = 100
>
>
> The best solution would be for the client to save the primary key
> fields of the last record to pass back to you as a starting value to
> use.
>
> An alternative, is to have the client save & return a page number.
> There's an article on System i Network that talks about using it in
> CGI programs http://systeminetwork.com/article/use-sql-paging-cgi-programs
>
> But you'll need a paid membership to read it.  You can google for "sql
> paging query" and see mostly SQL server versions of the technique.
>
> The technique is pretty simply and useful for more than just the IBM
> i, the following display page 6 (records 25-30) when paging 5 records
> at a time.
>
> SELECT * FROM
>   (SELECT * FROM
>      (SELECT * FROM sometable
>       ORDER BY mykey ASC FETCH FIRST 30 ROWS ONLY) AS T1
>   ORDER BY mykey DESC FETCH FIRST 5 ROWS ONLY) AS T2
> ORDER BY mykey
>
> And it performs pretty decent for the the pages early in the results
> set.  But for example, asking for page 9000 out of a 10000 page result
> set will take lots of time & memory.
>
> HTH,
> Charles
>
>
> On Fri, Jan 9, 2009 at 7:50 AM, Jonathan Mason
> <jonathan.mason@xxxxxxxxxxxxxxxx> wrote:
> > We have an RPG400 program that reads through a JDE transaction file and
> > then returns consolidated data back to it's calling Java client.  Each
> > time the program returns data to the Java client it remains open for the
> > next call and accepts a parameter that indicates where in the program it
> > needs to restart from.
> >
> > Because of performance issues the call from Java was modified recently
> > so that instead of retrieving all transactions for a customer it now
> > retrieves blocks of 50 or so transactions.
> >
> > This is now causing a further problem where multiple users are accessing
> > the same customers transaction list and the same Websphere connection is
> > used by both jobs in turn.
> >
> > I need to modify the RPG program so that it positions the file back to
> > the last record read by the Java client regardless of whether the
> > program instance has been used by any other client.
> >
> > I'm looking at using the RRN and passing this back to Java when I return
> > the data and then chaining with this when the program restarts and there
> > has been a change in client.  I rarely use RRN processing, so this has
> > raised some questions:
> >
> > 1) If the logical file is defined as keyed in the program can I chain to
> > it using RRN or would I have to read the physical by RRN and then chain
> > to the logical using the key values picked up from the physical?
> >
> > 2) I can get the RRN from the INFDS, but looking through the manuals,
> > Google and the archives I keep seeing that the maximum size of a 4 byte
> > binary (integer) field is 2,147,483,647 (i.e. 2.147 billion records).
> > In RPG400 I have to use the INFDS to get the RRN for the record, but is
> > 2.147 billion still the maximum number of records I can have in a file
> > or can I have more?
> >
> > 3) If the file can hold more than 2.147 billion records can I get the
> > RRN for these "additional" records within the RPG400 program or is the
> > larger RRN only available through the RECNO keyword in RPG IV?
> >
> > Thanks in advance
> >
> > Jonathan
> >
> >
> >
> >
> > _______________________________________________________
> > This message was sent using NOCC v1.14 webmail software
> > _______________________________________________________
> >
> >
> >
> >
> > --
> > This is the RPG programming on the IBM i / System i (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.