IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Filter group of records based on key


IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
mistah kurtz

Active User


Joined: 28 Jan 2012
Posts: 316
Location: Room: TREE(3). Hilbert's Hotel

PostPosted: Fri Jun 19, 2015 6:09 pm
Reply with quote

Hi,

I have two files. One file is having the key. And other file is having set of records for each key. A set of records always start at AB1 and ends at AB3.

Please find below a sample file:

Key File:
Code:
KEY-02
KEY-04


Data File:
Code:
AB1 SOME DATA
AB2 KEY-01
AB2 SOME DATA
AB3 SOME DATA
AB3 SOME DATA
AB1 SOME DATA
AB2 KEY-04
AB2 SOME DATA
AB3 SOME DATA
AB3 SOME DATA
AB1 SOME DATA
AB2 KEY-03
AB2 SOME DATA
AB3 SOME DATA
AB3 SOME DATA
AB1 SOME DATA
AB2 KEY-04
AB2 SOME DATA
AB3 SOME DATA
AB3 SOME DATA
AB1 SOME DATA
AB2 KEY-02
AB2 SOME DATA
AB3 SOME DATA
AB3 SOME DATA


From the above file, my expected output is:

Code:
AB1 SOME DATA
AB2 KEY-04
AB2 SOME DATA
AB3 SOME DATA
AB3 SOMEDATA
AB1 SOME DATA
AB2 KEY-04
AB2 SOME DATA
AB3 SOME DATA
AB3 SOMEDATA
AB1 SOME DATA
AB2 KEY-02
AB2 SOME DATA
AB3 SOME DATA
AB3 SOMEDATA


Could anyone please provide any help/pointers on how to push the Key which is coming into the 2nd record for each group of records.

EDIT:
Key will always be in the 2nd record of the group(first occurance of AB2).
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Fri Jun 19, 2015 7:22 pm
Reply with quote

I assume your question comes down to "how do I get the AB1 record included in the group?" and that you're happy with the rest (JNFnCNTL to use WHEN=GROUP to propagate the key to the subsequent records in the group, then a simple JOINKEYS)?

Well, you can't. Not in one step.

So you have to do something else if possible, and that may depend.

If your data is fixed-length, and its LRECL is less than 32760/2, then use WHEN=GROUP to identify the AB1 and PUSH the entire record, with RECORDS=2, onto the next record (which will be the AB2 with the key).

You have then the original AB1 to ignore (in the join itself, or the main task) and in OUTFIL when processing the AB2, write the stored AB1 first, and use the slash operator (/) to write the AB2 second.
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Sat Jun 20, 2015 7:08 am
Reply with quote

mistah kurtz,

As well as the RECFM and LRECL, I see we need more information, as looking at your data you have duplicate keys which are treated distinctly in the output and the output is in the original order.
Back to top
View user's profile Send private message
mistah kurtz

Active User


Joined: 28 Jan 2012
Posts: 316
Location: Room: TREE(3). Hilbert's Hotel

PostPosted: Sun Jun 21, 2015 3:22 pm
Reply with quote

Hi Bill,

I was trying to work with the approach that you had suggested. I have not been able to reach to a working solution till Friday. I would resume my work on Monday.

My file LRECL is 250 aid RECFM is FB.

Yes. Files may have duplicates set of records for a key, but the data will be different even if the key is same, that's why I need them all in my output, however ordering is not important.
Back to top
View user's profile Send private message
mistah kurtz

Active User


Joined: 28 Jan 2012
Posts: 316
Location: Room: TREE(3). Hilbert's Hotel

PostPosted: Sun Jun 21, 2015 9:23 pm
Reply with quote

Hi Bill,

Based on what you have advised I have prepared the below sort card(F1 is the Data File and F2 is the Key File):

Code:
//SYSIN    DD *                                               
     JOINKEYS FILE=F1,FIELDS=(41,6,A)
     JOINKEYS FILE=F2,FIELDS=(01,6,A)
     REFORMAT FIELDS=(F1:1,40)                                 
     OPTION   COPY
     OUTFIL   INREC=(WHEN=(21,3,CH,EQ,C'AB1'),BUILD=(1:21,20,/,1,20)),
              INREC=(WHEN=NONE,BUILD=(1,20))
//JNF1CNTL DD *
     INREC IFTHEN=(WHEN=GROUP,
                  BEGIN=(01,3,CH,EQ,C'AB1'),RECORDS=2,
                   PUSH=(21:1,20)),
     INREC IFTHEN=(WHEN=GROUP,
                  BEGIN=(21,3,CH,EQ,C'AB1'),
                   PUSH=(41:5,6))


I will test it on Monday and see if it works.
Back to top
View user's profile Send private message
mistah kurtz

Active User


Joined: 28 Jan 2012
Posts: 316
Location: Room: TREE(3). Hilbert's Hotel

PostPosted: Sun Jun 21, 2015 9:57 pm
Reply with quote

Hi.

I have not tested this sort card, just wrote it on notepad and posted here to know if its logically correct. There are some syntax errors in it, like INREC appearing twice in JNF1CNTL. I will update on Monday with the results.
Back to top
View user's profile Send private message
mistah kurtz

Active User


Joined: 28 Jan 2012
Posts: 316
Location: Room: TREE(3). Hilbert's Hotel

PostPosted: Mon Jun 22, 2015 12:50 pm
Reply with quote

I have tested with below sort card and it's working fine.

Code:
//SYSIN    DD *                               
     JOINKEYS FILE=F1,FIELDS=(41,6,A)
     JOINKEYS FILE=F2,FIELDS=(01,6,A)
     REFORMAT FIELDS=(F1:1,40)               
     OPTION   COPY
     OUTFIL   INREC=(WHEN=(21,3,CH,EQ,C'AB1'),
                    BUILD=(1:21,20,/,1,20)),
              INREC=(WHEN=NONE,
                    BUILD=(1,20))
//JNF1CNTL DD *
     INREC IFTHEN=(WHEN=GROUP,
                  BEGIN=(01,3,CH,EQ,C'AB1'),RECORDS=2,
                   PUSH=(21:1,20)),
           IFTHEN=(WHEN=GROUP,
                  BEGIN=(21,3,CH,EQ,C'AB1'),
                   PUSH=(41:5,6))


Thanks a lot!! icon_smile.gif
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Mon Jun 22, 2015 1:04 pm
Reply with quote

I think you've typo'ed a couple of AB2s to AB1, the one in the main task, and the second in the JNF2CNTL.

Probably not significant, but if the order of F2 is already good to go, you can use SORTED,NOSEQCK for the JOINKEYS for that file.

Note, JOINKEYS uses SORT with EQUALS. If you were SORTing the data outside of JOINKEYS you'd have to specify EQUALS yourself, else the data from two sets with the same key could become intermingled.
Back to top
View user's profile Send private message
mistah kurtz

Active User


Joined: 28 Jan 2012
Posts: 316
Location: Room: TREE(3). Hilbert's Hotel

PostPosted: Mon Jun 22, 2015 3:14 pm
Reply with quote

No I'm using the AB1 record that got pushed to the end of AB2 at both the places.

As there are two AB2 records and I was getting incorrect output (one extra blank line after the 2nd AB2). And in JNF1CNTL, KEY was not getting poulated on all the records again because of the 2nd AB2 record.

Neither KEY file nor the data file are in sorted order. In my data file records with same keys can be in any postion, not necessarily in consecutive order.
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Mon Jun 22, 2015 3:43 pm
Reply with quote

OK, I didn't realise the key-holding record could not be uniquely identified other than by it's position.

You have the potential (and only your data can tell to magnitude of the potential) of a false-hit for your AB1.

Your second WHEN=GROUP is operating on the AB1 record as well as the first AB2 record. If 5.6 on AB1 is sufficiently similar (actual content, not contextual content) to 5,6 on the first AB2 then you can get a problem.

I'd put a SEQ=1 on the first PUSH and use that with AND in the second WHEN=GROUP for a value of two, to ensure that only the AB2 of that first group (which will be the first AB2) is affected. Can be similar in the main task.

Also you should ensure that a key of blanks is not a valid key.

Where doing matching like this, you must always think about false-hits. There may be something which looks convenient with your data, but which can be undone if you are not careful.

For instance, it would seem that instead of using the AND I suggested, you could simply check the SEQ-field for two. But if you did that, if the AB2 key record happened to be missing, you'd generate some key off the next record and when unlucky that will match, etc. Type of think which can lie around for a long time before biting, at the worst possible moment :-)

WHEN=GROUP is not like WHEN=(logicalexpression). Each WHEN=GROUP will be processed for the current record, irrespective of whether the condition is matched. WHEN=(logicalexpression) processing will cease after the first match. An important difference.
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Mon Jun 22, 2015 3:56 pm
Reply with quote

Code:
AB1 123456789  stuff after the not-key
AB2 123456 stuff after the key
AB2 SOME DATA
AB3 SOME DATA
AB3 SOME DATA
AB1 SOME DATA
AB2 123457 stuff after the key
AB2 SOME DATA
AB3 SOME DATA
AB3 SOME DATA
AB1 SOME DATA
AB2 KEY-03 stuff after the key
AB2 SOME DATA
AB3 SOME DATA
AB3 SOME DATA
AB1 SOME DATA
AB2 123456  stuff after the key
AB2 SOME DATA
AB3 SOME DATA
AB3 SOME DATA
AB1 123456987  stuff after the not-key
AB2 KEY-02 stuff after the key
AB2 SOME DATA
AB3 SOME DATA
AB3 SOME DATA


With:

Code:
KEY-02
123456


Produces what?
Back to top
View user's profile Send private message
mistah kurtz

Active User


Joined: 28 Jan 2012
Posts: 316
Location: Room: TREE(3). Hilbert's Hotel

PostPosted: Mon Jun 22, 2015 5:34 pm
Reply with quote

Hi Bill,

Thanks for highlighting these false-hit scenarios. But looking at the sample files that I have been provided, it looks like both these sceanrios will never be present in the data file.
Quote:
If 5.6 on AB1 is sufficiently similar (actual content, not contextual content) to 5,6 on the first AB2 then you can get a problem.

Each record (AB1, AB2, AB3) has different layout and the data in the correspoding postion in AB1 will always be spaces and in AB2 it will always be the key.

Quote:
if the AB2 key record happened to be missing

This record with the key will always be present, however it's possible that the 2nd AB2 record is missing.

Nevertheless I think it would be better to put the check for SEQ to make sure that im picking up the correct record. (And besides Mainfarme is not the source system for this data file.)
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DFSORT/ICETOOL

 


Similar Topics
Topic Forum Replies
No new posts Compare only first records of the fil... SYNCSORT 7
No new posts Pulling a fixed number of records fro... DB2 2
No new posts Join multiple records using splice DFSORT/ICETOOL 5
No new posts EZT program to build a flat file with... All Other Mainframe Topics 9
No new posts JCL sortcard to print only the records DFSORT/ICETOOL 11
Search our Forums:

Back to Top