Re: Checking Thresholds in a file using SQL

The input file that comes in is a flat input file, which gets written to a
multi-member file (with fields). I was thinking of operating on this file.
The member gets cleared after the load process completes. So in essence it
is just a temporary transaction file.

Do you think these kind of statistics would be available for this kind of
file?

On Tue, Oct 20, 2015 at 4:34 PM, Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>
wrote:

Hi Vinay

When IBM started collecting statistics on files, cardinality was one of
the things - how many distinct values, and frequent values - there is the
List Statistics Collection Details (QDBSTLDS, QdbstListDetailStatistics)
API that can give you results. It won't give the number of every value,
probably, but the most frequent will be tehre.

There are also now some system catalog views related to statistics that
probably can be used more easily.

This does assume a certain OS level - statistics have been around since
the new query engine came on the scene, around 2001. I don't know when some
of the system catalogs appeared.

HTH
Vern


On 10/20/2015 3:08 PM, Vinay Gavankar wrote:

Clarifying with an example.

K1 is Member Id
F1 is First Name
F2 is Last Name
F3 is Address

etc.

I know First name Last name would not change normally for the same Member
Id, but Address could be different, so treat this just as an example.

If the file has more than 500 Member Ids (the defined threshold) with same
Address, or more than 300 Members with same Last Name or more than 1000
Members with same first name, I want to abort.

If I can get the highest count for each field then I can do the checking
for the thresholds separately.

Vinay

On Tue, Oct 20, 2015 at 2:07 PM, Alan Shore <ashore@xxxxxxxx> wrote:

Hi Vinay

Question
Is F1 one of the 10 fields and K1 is the other 9
OR
Is F1 nine of the 10 fields and K1 is the other 1
OR
Some other combination

Alan Shore
E-mail : ASHORE@xxxxxxxx
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
'If you're going through hell, keep going.'
Winston Churchill

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
Vinay Gavankar
Sent: Tuesday, October 20, 2015 1:59 PM
To: Midrange Systems Technical Discussion
Subject: Checking Thresholds in a file using SQL

Hi,

I have a requirement where I need to check for number times a value
occurs
in a field in the file.

The file has a field K1 (non-unique) and another field F1

For the records with same value of K1, F1 can have different values. I
need to consider only the value of last record (highest RRN).
If my file has 3 records for K1=A (in RRN sequence)
A XXX
A YYY
A ZZZ

then I want to consider only ZZZ for processing.

I need to get a count of unique, non-blank values of F1 in the file
(considering only the last record for K1).

To give an example, if my full file looks like this:

A XXX
A YYY
*A ZZZ*
*B XXX*
C MMM
*C ZZZ*
*D XXX*

*E UUU*

*F ZZZ*
G (blanks)
H (blanks)

then my counts should be:
ZZZ 3
XXX 2
UUU 1

If the highest count is greater than a certain number (threshold), I have
to take a particular action.

So basically, I just need the highest count. The value of F1 for which
highest count occurred is not important.

Is this possible to do with sql (embedded in RPGLE).

Or what would be the most efficient (I/O and CPU wise) way of doing this?

The file actually has 10 fields (F1 thru f10) and the above needs to be
done for all the 10 fields (could be a different threshold value for all
fields).

Any help will be greatly appreciated.

Thanks
Vinay
--
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.

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

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