AW: Arrays -- RPG400-L

Hi,

If you are already on release V5R3M0, I'd suggest using a
MQT (Materialized Query Tables) with the appropriate indexes on it.
A Materialized Query Table (MQT) is a table whose definition is
based on a query?s result. An MQT typically contains precomputed
results based on the data existing in the table(s) on which it?s based.

You need a SQL REFRESH statement to populate it.
Because you want to summarize your rows, the MQT has much less rows
and even columns than the base tables and the access must be much
faster than using the original tables.

Because MQTs are physical files it should be possible to add a
primary or unique constraint, to guarantee a predefined sequence.
Like all SQL tables, views and indexes, you even can get access on
MQTs with native I/O.

Birgitta


-----Ursprüngliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]Im Auftrag von Tony Carolla
Gesendet: Dienstag, 29. März 2005 22:51
An: RPG programming on the AS400 / iSeries
Betreff: Re: Arrays


Hmmm... Well, I because the number of columns is finite, I would
probably create seven empty temp files in QTEMP, each one with all of
the subfile columns, and keyed based on one of the seven columns.  The
first time the screen was displayed, I would populate the default sort
order temp file with the results, then populate the subfile from this
temp file.  Then, when a new column was selected, I would simply copy
from the original temp file to one of the other seven, and populate
the subfile from the new temp file.

The copy would always be done from the default sort order temp file,
and it would be done using blocked reads and writes (you could even
size the blocking based on the number of records in the temp file),
and using DSs to read and write to.  This would prevent having to
re-populate the subfile from the database files, and only focus on the
result set.


On Tue, 29 Mar 2005 15:41:00 -0500, rob@xxxxxxxxx <rob@xxxxxxxxx> wrote:
> Ok, I understand what you say about performance tuning.  RPG has those
> issues too, like figuring out record blocking, primary vs SETLL/READ, etc.
>
> Some application questions though.
> 1)  If a user said "On this inquiry screen I would like to position my
> cursor on any column and sort by that column (and you have seven columns)
> would you:
> a - Create 7 logicals and use RPG native I/O
> b - Front end the code with OPNQRYF (but since I don't use that I forget
> if you have to redefine the key on the f-spec to match the sort order)
> c - Use SQL
> d - Tell the user it can't be done.
> e - Offload the data to another platform and have them manipulate it
> there.
> f - Put it on your to-do list, but always at the bottom priority to never
> get done.
>
> Rob Berendt
> --
> Group Dekko Services, LLC
> Dept 01.073
> PO Box 2000
> Dock 108
> 6928N 400E
> Kendallville, IN 46755
> http://www.dekko.com
>
> Tony Carolla <carolla@xxxxxxxxx>
> Sent by: rpg400-l-bounces@xxxxxxxxxxxx
> 03/29/2005 02:03 PM
> Please respond to
> RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>
>
> To
> RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>
> cc
>
> Subject
> Re: Arrays
>
>
> <snip>
> > SQL is well worth the coin paid to obtain it.  Harness it's power &
> you'll
> > never want to type the command WRKQRY again....
> < Thanks,
> < Tommy Holden
> </snip>
>
> Haven't typed in WRKQRY in years (~10).
>
> > I don't find it "that complex".
> > Even Joe Pluta occasionally finds SQL useful.  I think that if you never
> > use it, then it is like the person who finds he can get everything done
> > with a hammer; when it is the only tool in their toolbox.
> >
> > Rob Berendt
>
> I don't find SQL too complex by any means.  I find it simple, and have
> used it on many platforms.
>
> I guess I see it this way:  You can build your SQL statements, and
> build your summaries "on the fly" with no extra code (SQL is code,
> BTW), but when you run them, and they take long, what can you do?
> Index.  Study the query optimizer -- what is it choosing?  Why won't
> it use my index?  Which type of index?  EVI?  Can I bribe the crazy
> thing?  Will it use the index when the user hits the view a little
> differently?
>
> I have spent alot of time studying indexing, on DB2 and SQL server as
> well.  It is fascinating, but I just find it easier to analyze the
> data request I am building, and make my own 'access plan', write the
> code to do it, and it's done.  I would bet that any quick-running SQL
> statement can be made to run just as fast or even faster using RPG.
>
> --
> "Enter any 11-digit prime number to continue..."
> --
> 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.
>
> --
> 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.
>
>


--
"Enter any 11-digit prime number to continue..."
--
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.