AW: RRN and Database Files -- RPG400-L

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.
A full table scan will be used, because indexes (or access paths) can only
be created over existing columns within the based table.
RRN is no "real" column and therefore no index can be used.
Even in release 6.1. where derived indexes, where new columns can be defined
can be created it is not possible to create an index over the RRN.

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?

It's not the maximum of integer, but the maximum of unsigned integers, i.e.
4.2 billion records. (and unsigned integers 10U 0 can be used within RPGIV)
4.2 billion records or 1.7 tera byte (depending which limit will be reached
first) is the maximum a not partitioned file (either DDS created or SQL
defined) can hold.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im
Auftrag von Charles Wilt
Gesendet: Friday, 09. January 2009 14:46
An: RPG programming on the IBM i / System i
Betreff: Re: RRN and Database Files

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.