[WEB400] SQL commands needed to do ROLL-UP and ROLL-DOWN

["Peter Vidal" <Peter_Vidal@xxxxxxxx>]

List:

I was looking on the archives but I did not find something similar to my
situation.  I am working on a CGI that lists manufacturing operations but it
takes "juice" from the network.  I am using a SELECT command to do this:

SELECT Mst.ordno, Mst.fitem, Mst.fitwh, Mst.fdesc, Mst.ostat, (Mst.orqty +
Mst.qtdev), (Mst.orqty + Mst.qtdev - Mst.qtscp - Mst.qtyrc), Mst.qtscp,
Rtg.opseq, Rtg.opstc, (Mst.orqty + Mst.qtdev - Rtg.tqctd - Mst.qtscp),
Rtg.scrap, Pls.psstat, Pls.psadd, Pls.psclor, Pls.pstype, Emj.subtp, Emj.ostat,
Mst.ratio, Rtg.awrkc, Rtg.uusamx
FROM $(AMFLIB).momast Mst
     left outer join $(AMFLIB).morout Rtg on Mst.ordno = Rtg.ordno
     left outer join $(COLIB1).planst Pls on Mst.ordno = Pls.psordr and
Mst.fitem = Pls.psitem
     left outer join $(PPLDTA).empjob Emj on '$(ENVCD)' = Emj.envcod and $(emp)
= Emj.badge and Mst.ordno = Emj.ordno and Rtg.opseq = Emj.opseq
WHERE Rtg.awrkc = '$(wkctr)' and Mst.ostat BETWEEN '10' and '40' and Rtg.opstc
BETWEEN '10' AND '30' and Emj.badge is Null $(whereclause)
$(orderby)

I have ROLL-UP and ROLL-DOWN symbols so when the user click on them, the
corresponding action occurs; however, the whole file (with matching records) was
loaded.  I heard/read that now in 5.2 you can predetermine the number of record
to read in SQL.

Questions:
1) How I define the specific number of records?
2) How I must code if PAGEUP or PAGEDOWN is requested? an additional call to the
SQL?

3) Recommendations to improve performance? We have a brand new iSeries 825(V5.2)

TIA,

Peter Vidal
Pall Aeropower Corp.
SR Programmer Analyst
WWW.Pall.com / 727-539-8448, x2414

"A good player makes himself look good; a great player makes the team look
good."
Author unknown