RE: SQL syntax to count all records of any date in a file

The one Charles provided is the one I need, results outputed to a
spoolfile.

I'm testing an updated purge process of a 3rd party app.
The original purge was working, (so we thought).
The purge deletes the records from a PF and puts a copy in a purge file
(D00*)
The updated purge process ran for 4 days on our R&D LPAR.
Date range was 01/01/01 thru 05/31/12 (keeping 4 years worth).
It purge records from day 1, some of which were missed by the current
purge.

My intention is to use the SQL to show the purge results, not only over
the D00* files, but the original PF.
select EKACD5,count(*) from uppenwork/D001CCEKCP group by EKACD5

These files are very large, hundreds of millions of records.
Three issues.
1) The count and group by still give a very large output result, (93
pages) difficult to summarize
....
101/03/16 155,546
101/03/17 1,120
101/03/18 129
101/03/19 3,738
101/03/20 5,925
101/03/21 5,103
101/03/22 4,460
101/03/23 5,400
101/03/24 1,095
101/03/25 177,641
...

2) The count summary was not sorted, thus I need to also add an order by
to the SQL.
SELECT EKACD5,count(*) FROM pauls/ccekcpp GROUP BY EKACD5 ORDER
BY EKACD5
3) Because the files are so large, the SQL may impact performance when
running, 77% of CPU, probably should submit to batch. (I've never submitted
an SQL to batch)

Paul




-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
CRPence
Sent: Monday, August 08, 2016 1:06 PM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: SQL syntax to count all records of any date in a file

On 08-Aug-2016 11:36 -0500, Charles Wilt wrote:

one additional possibility..

count the number of each date:

select date_column_name, count(*)
from table-reference
group by date_column_name

I figured that was quite possibly, even probably, the desired query.
But I purposely chose not to offer that query in my first reply; that if
I had correctly intuited [the intention of] the scenario so vaguely
described in the OP, then having provided the most likely as-desired
resolution might encourage Pavlovian behavior. Better IMO, to avoid
immediately rewarding poorly-described issues with a probably-desirable
response, and instead suggesting\alluding the OP might actually extend some
effort to better describe the issue; delaying the reward [being the
presumed-likely desirable SELECT query] pending receipt of a good\improved
problem description -- or perhaps upon clarification, some entirely
different SELECT query as the response.

--
Regards, Chuck

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.