I'm trying to figure out the best way to count the number of records matching specified criteria in large PF. (The file has over 50 million records currently and with some purging we hope to bring it down to around 20 mil.)
Anyway, I created the (SQL) view:
create view largePF99
( field1, field2, field3, count )
as select fldx,
substr( fldz,120,10 ),
count( * )
group by fldx,
substr( fldz,120,10 )
I then declared a cursor for the view in my RPGLE pgm, as I'll need multiple rows within this view to get the count I ultimately want.
The problem (as I'm sure you've already worked out) is that the *PGM runs runs for ages. Am I better off reading the base file in good old RPG or is there any other way I can improve this?
Thanks in advance.