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)
Try to add an encoded vector index with the date column as key and add an
include clause for the count(*)
CREATE ENCODED VECTOR INDEX YourSchema.YourIndex
ON YourSchema.YourTable (YouKeyFld ASC)
INCLUDE(Count(*)) ;
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
-----Ursprüngliche Nachricht-----
Von: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von
Steinmetz, Paul
Gesendet: Monday, 08.8 2016 20:49
An: 'midrange-l@xxxxxxxxxxxx'
Betreff: 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.
midrange.com
