IBM Mainframe Forum

I have a file(VB LRECL=71) with the following columns:

FIELD1= CHAR(6)
KEY1=CHAR(16)
TIMESTAMP=CHAR(26) DB2 Timestamp
KEY2=2 bytes Number
FIELD2=CHAR(16)

INPUT
----------
yyxxxxAAAAAAAAAAAAAAAA2009-01-01-01.00.00.0000000011234567890123456
yyxxxxAAAAAAAAAAAAAAAA2009-01-01-02.00.00.0000000011234567890123456
yyxxxxAAAAAAAAAAAAAAAA2009-01-01-00.00.00.0000000021234567890123457
yyxxxxBBBBBBBBBBBBBBBB2009-01-01-00.00.00.0000000011234567890123456

Records are grouped by KEY1.
If there are records with KEY2 =1 for a group, select the records with KEY2=1 for which TIMESTAMP has the lowest value.
If there are records with KEY2 !=1 for a group, select the record for which TIMESTAMP has the lowest value.

There are two groups(Unique KEY1): AAAAAAAAAAAAAAAA and BBBBBBBBBBBBBBBB.
For group AAAAAAAAAAAAAAAA, we have two records with KEY2=1. So we select the record that has the lowest TIMESTAMP.
For group BBBBBBBBBBBBBBBB, we do not have records with KEY2=1. So we select the record that has the lowest TIMESTAMP.

Our output will have two records, one for each group.

OUTPUT
----------
yyxxxxAAAAAAAAAAAAAAAA2009-01-01-01.00.00.0000000011234567890123456
yyxxxxBBBBBBBBBBBBBBBB2009-01-01-00.00.00.0000000011234567890123456

Can ICETOOL be used to produced this result?

Your description of the data does NOT match your example. Your description has lengths adding up to 66 bytes, but your example has records of 67 bytes:



It appears you have an extra 0 byte before KEY2, but I can't tell for sure.

For group BBBBBBBBBBBBBBBB, we do not have records with KEY2=1. So we select the record that has the lowest TIMESTAMP.

It appears to me that for this group, you do have a record with KEY2=1. But maybe that's because your example doesn't match your description.

Please show an accurate example of your input records and expected output records. Show all possible variations. Use bbcode tags around your examples.

Here is my INPUT and desired OUTPUT:

Column <Record> indicates the line number in the file and is not a data field.



Output contains only one record from each group(unique KEY1).

For the unique key KEY1=AAAAAAAAAAAAAAAA, there are two records with KEY2=01.
So selection will be made from these two records(Record 1 and 2) based on the value in TIMESTAMP column.
Since TIMESTAMP for record 1 is less then that of record 2, record 1 is selected from INPUT file(2009-01-01-01.00.00.0000000 < 2009-01-01-02.00.00.0000000).


There is only one record for KEY1=BBBBBBBBBBBBBBBB. So record 4 is selected from the INPUT file.

There are two records for KEY1=CCCCCCCCCCCCCCCC. None of these records have KEY2=01.
So selection will made based on the record with lowest TIMESTAMP. We select record 6 as it has the lowest TIMESTAMP.

I have tried to solve it in four steps as follows:

1. Split the INPUT file into two. FILE1 has records with KEY2=1 and FILE2 has records with KEY2!=1.
2. Sort FILE1 using KEY1 and TIMESTAMP with FIRSTDUP.
3. Sort FILE2 using KEY1 and TIMESTAMP with FIRSTDUP.
4. Sort FILE1 and FILE2 (concatenated file) using KEY1 and KEY2 and use FIRSTDUP to get the first record.

How can I resolve this in just one step?

Here's a DFSORT/ICETOOL job that will do what you asked for in one pass: