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

need unpaired and matched records from File-1


IBM Mainframe Forums -> JCL & VSAM
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
ramsri

Active User


Joined: 18 Oct 2008
Posts: 380
Location: India

PostPosted: Tue Aug 06, 2013 9:02 pm
Reply with quote

Hi,

I have two files (LRECL=16,RECFM=FB) and requirement is to get unpaired records from file-1 (detail records) and matched records also from file-1 (header and trailer).

File-1:
Code:

HEADER  00000136
XXXXA   tb9 CUPA
XXXXA   S1T SCAL
XXXXA   F8C WHOM
TRAILER


File-2:
Code:

HEADER  00000136
XXXXA   D9J CYCL
XXXXA   O6C CLER
XXXXA   tb9 CUPA
TRAILER


I wrote below sort card -
Code:

//SYSIN    DD  *                   
  JOINKEYS FILE=F1,FIELDS=(1,16,A)
  JOINKEYS FILE=F2,FIELDS=(1,16,A)
  JOIN UNPAIRED,F1,ONLY           
  SORT FIELDS=COPY                 
  END                             


and got two reords from file-1
Code:

XXXXA   S1T SCAL
XXXXA   F8C WHOM


but my desired output should appear as given below -

Code:

HEADER  00000136
XXXXA   S1T SCAL
XXXXA   F8C WHOM
TRAILER


How to get this? Please help.
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: Tue Aug 06, 2013 10:29 pm
Reply with quote

Some work, that's how :-)

You have "groups" of data, which have "keys within a group"?

Are the the keys witing a group both unique across your data and solely contiguous within the group. Ie, when you SORT on 1,16, are any records from on group going to be mixed in with another. Is it possible?

Are the HEADERs for the groups on the two files always identical for a group?

Do the TRAILERs simply contain the word TRAILER, or some other "constant" value, or 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: Wed Aug 07, 2013 11:10 am
Reply with quote

Quote:
to get unpaired records from file-1 (detail records) and matched records also from file-1 (header and trailer).

I'm sorry, but I didn't fully understand this part. Do you mean you need all the unpaired records from File1 with Header and Trailer. What if File1 and File2 have some matching records apart from Header and Trailer?
Back to top
View user's profile Send private message
Pandora-Box

Global Moderator


Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy

PostPosted: Wed Aug 07, 2013 11:24 am
Reply with quote

What you could do is something like below untested though

Code:
//SYSIN    DD  *                   
  JOINKEYS FILE=F1,FIELDS=(1,16,A)
  JOINKEYS FILE=F2,FIELDS=(1,16,A)
  JOIN UNPAIRED,F1,F2
  REFORMAT FIELDS=(F1:1,16,F2:1,16)
  INCLUDE COND=((1,16,CH,EQ,17,16,CH,AND,(1,7,CH,EQ,C'HEADER ',
  OR,1,7,CH,EQ,C'TRAILER')),OR,(17,16,CH,EQ,C' '))
  END           
Back to top
View user's profile Send private message
ramsri

Active User


Joined: 18 Oct 2008
Posts: 380
Location: India

PostPosted: Wed Aug 07, 2013 1:41 pm
Reply with quote

Pandora-box, thank you.....I got expected results.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Wed Aug 07, 2013 1:45 pm
Reply with quote

Ramsri,

Would you mind sharing the solution?
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: Wed Aug 07, 2013 4:53 pm
Reply with quote

Ramsri, as with your previous topic, I'm surprised you've got the results you want other than with the very limited data you've shown.

We're getting very good at solving your problems, we just have no clue as to how we do it :-)
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Wed Aug 07, 2013 5:23 pm
Reply with quote

Bill Woodger wrote:
We're getting very good at solving your problems, we just have no clue as to how we do it :-)
icon_biggrin.gif I thought the same Bill
Back to top
View user's profile Send private message
Pandora-Box

Global Moderator


Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy

PostPosted: Wed Aug 07, 2013 5:23 pm
Reply with quote

Quote:
We're getting very good at solving your problems, we just have no clue as to how we do it :-)


But frankly I was able to decrypt what he wanted icon_biggrin.gif

Edit : And yes I was expecting TS to tweek my post slightly icon_eek.gif
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Wed Aug 07, 2013 5:33 pm
Reply with quote

Pandora-Box wrote:
But frankly I was able to decrypt what he wanted icon_biggrin.gif

Edit : And yes I was expecting TS to tweek my post slightly icon_eek.gif
Pandora,

We can very well provide solutions to the example data provided, but most of the time, it may not be realistic. Say for example, the HEADER/TRAILER records may not exactly be the same in the real input files. So the solution provided should be based on realistic data capable of handling all input scenarios. That is the whole idea behind asking questions and nothing was answered here. Any solution provided without knowing the characteristics of real data would be solely based on assumptions and cannot be expected to work as intended.
Back to top
View user's profile Send private message
Pandora-Box

Global Moderator


Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy

PostPosted: Wed Aug 07, 2013 5:41 pm
Reply with quote

Arun,

I was mentioning about "tweek" for considering the files as SORTED.

Anyway apologies for providing a half-baked solution icon_redface.gif
Back to top
View user's profile Send private message
ramsri

Active User


Joined: 18 Oct 2008
Posts: 380
Location: India

PostPosted: Wed Aug 07, 2013 6:05 pm
Reply with quote

Arun/Bill, I must admit that I changed starting letter to be O instead of X when tried it in reality.... icon_redface.gif Otherwise, I was ending up getting in wrong results icon_cry.gif

icon_idea.gif I added
Code:
OUTREC FIELDS=(1,16)
to Pandora's solution.

Thanks.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Wed Aug 07, 2013 7:55 pm
Reply with quote

ramsri wrote:
I changed starting letter to be O instead of X when tried it in reality
I have no clue on what this is about. Anyways does n't matter as you have got your card working!

I would say a JOIN UNPAIRED,F1 would have been sufficient.

Or possibly even a JOIN UNPAIRED,F1,ONLY, but I can only guess with the limited info provided so far.
Back to top
View user's profile Send private message
Pandora-Box

Global Moderator


Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy

PostPosted: Thu Aug 08, 2013 11:45 am
Reply with quote

Quote:
I would say a JOIN UNPAIRED,F1 would have been sufficient.

Or possibly even a JOIN UNPAIRED,F1,ONLY, but I can only guess with the limited info provided so far.


Just curious How do you get the header and Trailer?
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Thu Aug 08, 2013 1:21 pm
Reply with quote

Pandora,

Code:
INCLUDE COND=(1,7,SS,EQ,C'HEADER ,TRAILER',OR,.....
This will extract the header and trailer records into output if you are doing a JOIN,UNPAIRED,F1. But you need to add logic to retain the order of header and trailer records. The 'solution' provided does not handle this.

Or if the header and trailer records have static data, we can have a JOIN UNPAIRED,F1,ONLY and hard-code the header and trailer records using OUTFIL HEADER1/TRAILER1.

But with the OP ignoring all the questions/details requested, one can only guess.
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: Thu Aug 08, 2013 1:42 pm
Reply with quote

Yes Arun. As this shows:

Quote:
I must admit that I changed starting letter to be O instead of X when tried it in reality...


Only by changuing the first X in the sample data to an O did the solution "work", as otherwise the TRAILER would appear before the data.

Ramsri, you will never get that to work with more than one group of data, or unless all you data starts with characters less than the trailer does.

We haven't even got to ask you if you want to retain the order of data within the group.
Back to top
View user's profile Send private message
Pandora-Box

Global Moderator


Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy

PostPosted: Thu Aug 08, 2013 2:03 pm
Reply with quote

Ok I get that Arun

Thanks for the clarification
Back to top
View user's profile Send private message
ramsri

Active User


Joined: 18 Oct 2008
Posts: 380
Location: India

PostPosted: Sun Aug 11, 2013 10:12 am
Reply with quote

Bill/Arun, there are no multiple groups within this file. There is always one header and trailer with detail records in between. I wonder if it is still achievable if the starting letter is some B or P.

Thanks.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Mon Aug 12, 2013 2:52 am
Reply with quote

Hello,

What happened when you tried?

If you have not tried, do so and post the results if there is a question/problem.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Mon Aug 12, 2013 6:04 pm
Reply with quote

Bill Woodger wrote:
Are the HEADERs for the groups on the two files always identical for a group?

Do the TRAILERs simply contain the word TRAILER, or some other "constant" value, or what?
Ramsri,

Do you find it difficult to answer to all of these?
Back to top
View user's profile Send private message
ramsri

Active User


Joined: 18 Oct 2008
Posts: 380
Location: India

PostPosted: Tue Aug 13, 2013 1:41 pm
Reply with quote

Arun,

Quote:

Are the HEADERs for the groups on the two files always identical for a group?


The file will have only one HEADER and yes.....they are identical.

Quote:

Do the TRAILERs simply contain the word TRAILER, or some other "constant" value, or what?


Yes......it is just TRAILER and nothing.

Thanks.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Tue Aug 13, 2013 9:05 pm
Reply with quote

Hello,

To Repeat:
Quote:
What happened when you tried?

If you have not tried, do so and post the results if there is a question/problem.


If you are unwilling to do things suggested and reply as to what happened, we should start locking your topics. . .
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 Aug 16, 2013 12:59 am
Reply with quote

Ramsri,

I've lost track of whether you can use JNFnCNTL files.

If not, use OMIT on the JOINKEYS for your F2 to get rid of the header and trailer.

They will then "pop up" on your F1,ONLY output. In the wrong order.

You'll have to SORT them. Append an X'00' to the header, X'FF' to the trailer and C'1' to each data record. SORT on with, with EQUALS.

Or make a sequence number, set to zero for the header, and all 9 for the trailer. SORT on that, with NOEQUALS.

Don't forget to cut the record back down afterwards (OUTREC BUILD=).

If you can use JNF1CNTL, then the processing to order the header and trailer can be done in there and the cut-down in the REFORMAT.
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 -> JCL & VSAM

 


Similar Topics
Topic Forum Replies
No new posts How to split large record length file... DFSORT/ICETOOL 10
No new posts Extracting Variable decimal numbers f... DFSORT/ICETOOL 17
No new posts SFTP Issue - destination file record ... All Other Mainframe Topics 2
No new posts Access to non cataloged VSAM file JCL & VSAM 18
No new posts Compare only first records of the fil... SYNCSORT 7
Search our Forums:

Back to Top