Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Join and Omit records- Master File not to be reformatted.

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
shajeeth

New User


Joined: 25 May 2005
Posts: 20

PostPosted: Tue Jan 26, 2016 8:16 pm    Post subject: Join and Omit records- Master File not to be reformatted.
Reply with quote

Hi,

I have two files- Input File 1(FB/LRECL=162)- Master File and Input File 2:(FB/LRECL=24). The master file has a header(two records), data and Trailer(2 records). The format is repetitive which is standard


The header record1 always starts 1111 in first 4 bytes
The header record2 starts with 2222 in first 4 bytes
The trailer record1 starts with 8888 in first 4 bytes-- has the count of detail records + header and sum of amounts in Detail records
The trailer record2 starts with 9999 in first 4 bytes-- has the count of detail records + header + Trailer and sum of amounts in Detail records

I have a pull file(Input File2) which has key 24 bytes(Position 1:24 bytes) which exist in detail records(Position 5:24 bytes) of Input File 1. When i use join to extract/omit the records, as expected the output records are getting reformatted which is not expected.

I tried to split the files based on key and omit and concatenate, but not been able to do so.

There are sums and counts which needs to be re-counted, which i can perform in an additional step

Is there a way to retain the headers and trailers as they exist in input file and omit only records from detail records? Any suggestions/advice?



Input File 1:( FB/LRECL=162)
=============

Code:
1111AAAAAAAAAAAADDMMYYY        1234-XYZ
7777AAAAAAAAAAAA XYZCN CDEFGEH
2222AAAAAAAAAAAAXXXXXXXXXXXX GHJYE       10.00
3333AAAAAAAAAAAAXXXXXXXXXXXX MNOPQR
4444AAAAAAAAAAAAXXXXXXXXXXXX
2222AAAAAAAAAAAAYYYYYYYYYYYY QRETY       20.00
3333AAAAAAAAAAAAYYYYYYYYYYYY GEHEREW
4444AAAAAAAAAAAAYYYYYYYYYYYY
2222AAAAAAAAAAAAZZZZZZZZZZZZ RESTE      30.00     
3333AAAAAAAAAAAAZZZZZZZZZZZZ JHDSAJDSA
4444AAAAAAAAAAAAZZZZZZZZZZZZ ABCDEF
8888AAAAAAAAAAAA               0000011  00060.00   6236215
9999AAAAAAAAAAAA               0000013  00060.00   6236215
1111BBBBBBBBBBBBDDMMYYY        1234-XYZ
7777BBBBBBBBBBBB
2222BBBBBBBBBBBBCCCCCCCCCCCC MNOPQ       100.00
3333BBBBBBBBBBBBCCCCCCCCCCCC GDFSADAS
4444BBBBBBBBBBBBCCCCCCCCCCCC
2222BBBBBBBBBBBBDDDDDDDDDDDD STUVW       100.00
3333BBBBBBBBBBBBDDDDDDDDDDDD YTEYWQTE
4444BBBBBBBBBBBBDDDDDDDDDDDD
8888BBBBBBBBBBBB               0000008  00200.00   6236215
9999BBBBBBBBBBBB               0000010  00214.00   6236215

....


Input file 2:(FB/LRECL=24)
=============
Code:
AAAAAAAAAAAAXXXXXXXXXXXXX
AAAAAAAAAAAAYYYYYYYYYYYYY
BBBBBBBBBBBBDDDDDDDDDDDDD



Final Output Expected:
=====================

Code:
1111AAAAAAAAAAAADDMMYYY        1234-XYZ
7777AAAAAAAAAAAA XYZCN CDEFGEH
2222AAAAAAAAAAAAZZZZZZZZZZZZ RESTE      30.00     
3333AAAAAAAAAAAAZZZZZZZZZZZZ JHDSAJDSA
4444AAAAAAAAAAAAZZZZZZZZZZZZ ABCDEF
8888AAAAAAAAAAAA               0000011  00060.00   6236215
9999AAAAAAAAAAAA               0000013  00060.00   6236215
1111BBBBBBBBBBBBDDMMYYY        1234-XYZ
7777BBBBBBBBBBBB
2222BBBBBBBBBBBBCCCCCCCCCCCC MNOPQ       100.00
3333BBBBBBBBBBBBCCCCCCCCCCCC GDFSADAS
8888BBBBBBBBBBBB               0000008  00200.00   6236215
9999BBBBBBBBBBBB               0000010  00214.00   6236215

....

coded, for what is worth
Back to top
View user's profile Send private message

Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1713
Location: UK

PostPosted: Wed Jan 27, 2016 6:07 pm    Post subject:
Reply with quote

What do you mean by 'reformatted'? Did you really mean re-sequenced?
Show your control cards.
Back to top
View user's profile Send private message
shajeeth

New User


Joined: 25 May 2005
Posts: 20

PostPosted: Wed Jan 27, 2016 7:06 pm    Post subject:
Reply with quote

Nic Clouston wrote:
What do you mean by 'reformatted'? Did you really mean re-sequenced?
Show your control cards.


I meant to not modify the header and trailer records and only omit the detail records using join
Below is my sortcard- straight forward join

Code:
* CONTROL STATEMENTS FOR JOINKEYS APPLICATION       
  JOINKEYS F1=MASTER,FIELDS=(5,24,A)                 
  JOINKEYS F2=PULL,FIELDS=(1,24,A)                   
  JOIN UNPAIRED F1,F2                               
  REFORMAT FIELDS=(F1:1,162,F2:1,24,?)               
  SORT FIELDS=COPY                                   
  OUTFIL FNAMES=SORTOF1,INCLUDE=(187,1,CH,EQ,C'B'), 
  BUILD=(1,162)                                     
  OUTFIL FNAMES=SORTOF2,INCLUDE=(187,1,CH,EQ,C'1'), 
  BUILD=(1,162)                                     
  OUTFIL FNAMES=SORTOF2,INCLUDE=(187,1,CH,EQ,C'2'), 
  BUILD=(163,24)       


But this takes into account the header and trailer while joining and messes the format of the output file

Code'd
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7225

PostPosted: Wed Jan 27, 2016 8:50 pm    Post subject: Reply to: Join and Omit records- Master File not to be reformatted.
Reply with quote

It looks like the main file does need to be sorted in the JOINKEYS (which you are doing), but that would mess up the order of the headers/trailers.

If you attach a sequence number to the records, in JNF1CNTL, and SORT on the sequence number in the JOINKEYS main task, that should fix the order.

Ideally you could do the adjustment of totals in the main task as well. It would be rare that you would need a separate step for that.
Back to top
View user's profile Send private message
shajeeth

New User


Joined: 25 May 2005
Posts: 20

PostPosted: Wed Jan 27, 2016 9:54 pm    Post subject: Re: Reply to: Join and Omit records- Master File not to be reformatted.
Reply with quote

Bill Woodger wrote:
It looks like the main file does need to be sorted in the JOINKEYS (which you are doing), but that would mess up the order of the headers/trailers.

If you attach a sequence number to the records, in JNF1CNTL, and SORT on the sequence number in the JOINKEYS main task, that should fix the order.

Ideally you could do the adjustment of totals in the main task as well. It would be rare that you would need a separate step for that.


Bill,

To clarify,

The sequence number addition to the records will work if there is a single header and trailer in the record?
The header details and trailer are repetitive in format.

Thanks,

Shajeeth
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7225

PostPosted: Wed Jan 27, 2016 11:23 pm    Post subject: Reply to: Join and Omit records- Master File not to be reformatted.
Reply with quote

I'm not sure what you were trying to clarify.

It looks like 7777s are a header as well.

1111s, 7777s, 8888s and 9999s don't have a key, unless for "false hits" (so check that that is not possible with the data).

2222s seem to have a key, so will be stripped off if the key matches.

All your output (unmatched F1s, matches) that make up the new file can have a sequence number on, and then you can return them to their original input sequence, even though you've now excluded some records (ie the relative sequence will remain the same).

The number of headers/trailers does not matter.

Remember to chop the sequence number off (OUTREC or OUTFIL) after you've used it.

The trickier part is updating the totals, especially whatever amounts appear on the 8888 and 9999 records because those don't make sense at first glance.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Add PD field from 2nd file to PD in 1st Sushant Garje DFSORT/ICETOOL 6 Thu Dec 01, 2016 4:32 pm
No new posts File Aid to File Manager conversion murali3955 IBM Tools 4 Thu Nov 24, 2016 3:41 pm
No new posts Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm
No new posts How to convert the VBM file to VB or... Sulabh Agrawal JCL & VSAM 4 Fri Nov 18, 2016 1:04 pm
No new posts CICS Roll back partially - Need to re... dwijadas CICS 4 Wed Nov 16, 2016 4:30 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us