Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Selecting only the records of one file that have the common

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

New User


Joined: 04 Apr 2007
Posts: 37
Location: Lisbon, Portugal

PostPosted: Mon Jul 07, 2008 11:00 pm    Post subject: Selecting only the records of one file that have the common
Reply with quote

Hi
I have two files with some fields that are common to them.
Is there a way of selecting only the records of one file that have the common fields equal to file 2?

If I have the 2 follwoing files:

FILE 1

aaaa 0123 XXXX 20080101 REC
bbbb 0132 YYYY 20080201 REC
cccc 0145 ZZZZ 20080301 REV
dddd 0143 VVVV 20080301 REX

File 2
aaaa 0123 213502415 20080101 REC
cccc 0145 565256456 20080301 REV

I want copy to the output file records 1 and 3 from file 1, because they have fields 1, 2 and 4 equal to those in file 2

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

Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Mon Jul 07, 2008 11:19 pm    Post subject:
Reply with quote

Here's a DFSORT/ICETOOL job that will do what you asked for. I assumed that your input files have RECFM=FB and LRECL=80, but the job can be changed appropriately for other attributes. I also assumed, as shown in your example, that there are no duplicate records within input file1 and no duplicate records within input file2.

Code:

//S1    EXEC  PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG  DD SYSOUT=*
//IN2 DD DSN=...  input file2 (FB/80)
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS)
//CON DD DSN=...  input file1 (FB/80)
//    DD DSN=*.T1,VOL=REF=*.T1,DISP=(OLD,PASS)
//OUT DD SYSOUT=*
//TOOLIN DD *
COPY FROM(IN2) TO(T1) USING(CTL1)
SELECT FROM(CON) TO(OUT) ON(1,9,CH) ON(16,8,CH) FIRSTDUP
/*
//CTL1CNTL DD *
  INREC BUILD=(1,9,16:21,8,80:X)
/*
Back to top
View user's profile Send private message
Antonio Barata
Warnings : 1

New User


Joined: 04 Apr 2007
Posts: 37
Location: Lisbon, Portugal

PostPosted: Tue Jul 08, 2008 2:31 am    Post subject: Reply to: Selecting only the records of one file that have t
Reply with quote

Thanks Frank
I think that I might have duplicate records within one of the files but, I will try that.
We are no very familiar with ICETOOL. Can you give me the link to the manual, just to read it?

Thanks
Back to top
View user's profile Send private message
Antonio Barata
Warnings : 1

New User


Joined: 04 Apr 2007
Posts: 37
Location: Lisbon, Portugal

PostPosted: Tue Jul 08, 2008 2:32 am    Post subject: Reply to: Selecting only the records of one file that have t
Reply with quote

Sorry.
I just saw the links in the forum.
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Tue Jul 08, 2008 3:04 am    Post subject:
Reply with quote

Quote:
I think that I might have duplicate records within one of the files but, I will try that.


If you have duplicate records in one of the files, you will probably need a different DFSORT/ICETOOL solution. Show a better example (with duplicates) of the records in your input files and what you expect for output. Give the RECFM and LRECL of the input files. Give the starting position, length and format of each relevant field.

Quote:
We are no very familiar with ICETOOL. Can you give me the link to the manual, just to read it?


DFSORT's ICETOOL is fully documented in Chapter 6 of "z/OS DFSORT Application Programming Guide" which you can access, along with all of the other DFSORT books, from:

www.ibm.com/servers/storage/support/software/sort/mvs/srtmpub.html

If you're not familiar with DFSORT and DFSORT's ICETOOL, I'd suggest reading through "z/OS DFSORT: Getting Started". It's an excellent tutorial, with lots of examples, that will show you how to use DFSORT, DFSORT's ICETOOL and DFSORT Symbols. You can access it online, from the link above.
Back to top
View user's profile Send private message
Antonio Barata
Warnings : 1

New User


Joined: 04 Apr 2007
Posts: 37
Location: Lisbon, Portugal

PostPosted: Wed Jul 09, 2008 3:36 am    Post subject:
Reply with quote

Quote:
If you have duplicate records in one of the files, you will probably need a different DFSORT/ICETOOL solution. Show a better example (with duplicates) of the records in your input files and what you expect for output. Give the RECFM and LRECL of the input files. Give the starting position, length and format of each relevant field.

Input file 1 is FB and 535 in length.
Input file 2 is FB and 84 in length.

The matching fields for file 1 are, with displacement, length and type:
1,3,PD
50,9,PD
75,16,CH

and for file 2 :
18,4,CH
27,17,CH
45,16,CH

I think that, because the matching fields are of different types, they must be converted first, so the types are identical. Am I right?
And yes, I can have duplicate records on file 1 but, in that case I want all the matching records in the output, including the duplicated.
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Wed Jul 09, 2008 3:50 am    Post subject:
Reply with quote

Quote:
I think that, because the matching fields are of different types, they must be converted first, so the types are identical. Am I right?


Yes. You say that the first two fields are PD vs CH. PD is numeric. Are the CH fields actually ZD (numeric) or some other type of character values?

Please show a good example of the records in each input file (with duplicates where appropriate) and what you expect for output. Show the PD fields in hex or as P'd...d'. Explain the "rules" for getting from input to output.
Back to top
View user's profile Send private message
Antonio Barata
Warnings : 1

New User


Joined: 04 Apr 2007
Posts: 37
Location: Lisbon, Portugal

PostPosted: Wed Jul 09, 2008 4:15 am    Post subject: Reply to: Selecting only the records of one file that have t
Reply with quote

Yes the CH fields are numeric (ZD).
As I'm already home, I will get some concrete cases tomorrow as soon as I get to the office.
I took a look at the manual and, I really enjoyed it.
It can save you lots of time and effort in development.
Thanks
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Wed Jul 09, 2008 5:00 am    Post subject:
Reply with quote

Quote:
I took a look at the manual and, I really enjoyed it.
It can save you lots of time and effort in development.


Wow. You don't often hear somebody say they enjoyed an IBM manual. As the developer who wrote the DFSORT books and added many of the new functions over the years, I appreciate the kind words. icon_biggrin.gif
Back to top
View user's profile Send private message
Antonio Barata
Warnings : 1

New User


Joined: 04 Apr 2007
Posts: 37
Location: Lisbon, Portugal

PostPosted: Thu Jul 10, 2008 6:21 pm    Post subject:
Reply with quote

Quote:

You don't often hear somebody say they enjoyed an IBM manual.

Yes, it's not very usual to year that. I see the reading of manuals as a way to get more knowledge on a particular matter. And if you find there some stuff that you really didn't know about and, additionally it saves you time and effort, it's great.
Back to top
View user's profile Send private message
Antonio Barata
Warnings : 1

New User


Joined: 04 Apr 2007
Posts: 37
Location: Lisbon, Portugal

PostPosted: Thu Jul 10, 2008 7:32 pm    Post subject:
Reply with quote

Quote:
Please show a good example of the records in each input file (with duplicates where appropriate) and what you expect for output.

I think this will illustrate what I need. The spaces between each field in file 1 are just for easy reading
Code:
File 1
Fld1                    Fld2                Fld3
018 04030010444444444 000000500 44444444 CCCDEDEC44444444
00C 315C001C000000000 00001000C 00000000 1243562300000000  Rec 1

018 04030010444444444 000000500 44444444 CCCDEDEC44444444
00C 315C001C000000000 00001000C 00000000 1243562300000000  Rec 2

018 04030010444444444 000003050 44444444 CCCDEDEC44444444
00C 315C001C000000000 00002000C 00000000 1243562300000000  Rec 3

016 04030010444444444 000000050 44444444 DDDCECEC44444444
01C 315C001C000000000 00000020C 00000000 3243532200000000  Rec 4


File 2
Fld1              Fld2                      Fld3
00108 01230ADK3LF 00000000010050000 5862896 ABDLVOSC        XXX  Rec 1
00109 01230ADK3LF 00000000010050000 5862896 ABDLVOSC        XXX  Rec 2
00116 01230ADK3LF 00000000000002500 5862896 LKMCVCSB        XXX  Rec 3


I want records 1 and 2 from file 1, because all 3 fields match (Fld1, Fld2 and Fld3) with record 1 from file 2.
In record 3, Fld 2 doesn't match so, it won't pass. Record 4 should also be on the output file, because all 3 selection fields match those in record 3 from file 2.
I think that it may be difficult to do without programming.
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Thu Jul 10, 2008 11:45 pm    Post subject:
Reply with quote

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

Code:

//S1   EXEC  PGM=ICETOOL
//TOOLMSG   DD  SYSOUT=*
//DFSMSG    DD  SYSOUT=*
//IN1 DD DSN=...  input file 1 (FB/535)
//IN2 DD DSN=...  input file 2 (FB/84)
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//OUT DD DSN=...  output file (FB/535)
//TOOLIN DD *
COPY FROM(IN2) TO(T1) USING(CTL1)
COPY FROM(IN1) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(OUT) ON(1,3,PD) ON(50,9,PD) ON(75,16,CH) -
  WITHALL WITH(1,536) USING(CTL3)
/*
//CTL1CNTL DD *
  INREC BUILD=(1:18,4,ZD,TO=PD,LENGTH=3,
    50:27,17,ZD,TO=PD,LENGTH=9,
    75:45,16,536:C'BB')
/*
//CTL2CNTL DD *
  INREC OVERLAY=(536:C'VV')
/*
//CTL3CNTL DD *
  OUTFIL FNAMES=OUT,INCLUDE=(536,2,CH,EQ,C'VB'),
    BUILD=(1,535)
/*
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 sort records based on length exceeds ... maxsubrat DFSORT/ICETOOL 7 Wed Oct 04, 2017 4:48 pm
No new posts Addition of two records on 2 fields amar143 SYNCSORT 8 Tue Oct 03, 2017 11:14 am
No new posts Execute JCL step based on the content... sprikitik JCL & VSAM 2 Tue Oct 03, 2017 10:03 am
This topic is locked: you cannot edit posts or make replies. PS file data should be passed as symb... d_sarlie JCL & VSAM 15 Tue Oct 03, 2017 5:18 am
No new posts Garbage on output file Hervey Martinez SYNCSORT 4 Wed Sep 27, 2017 12:50 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us