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.
midrange.com
