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

Compare two files and copy the main and group records in fil


IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   This topic is locked: you cannot edit posts or make replies.
View previous topic :: View next topic  
Author Message
skgupta81

New User


Joined: 11 Nov 2008
Posts: 27
Location: Canada

PostPosted: Wed Aug 22, 2012 10:02 pm
Reply with quote

I am novice in DFSORT(coz I use DFSORT rarely) and trying to understand easy concepts but this one I am not able to think of. Can you please help me here.

I have Master record in second file. I want to compare this master record(which starts with M) with M record in first file and then copy this M record including the group(records following that M record, the first char could be N or F for such records but not M) records under it to third file. All non matching records should be discarded.


First file
===========
Code:
MSEAE40118315282X0080002SUMMER LEISURE PROMO          20120708+0000025000002000000000+00000.0000000000U000.00000002500+0000000000.00000XXX000250{AE4CAD200002572
FSEXXX0118315282X0080002AY11AA2012062820120703YVR YUL P             +000000572XXX 112                                                            AE4CAD300002572
FSEXRN0118315282X0080002QY11AA2012062920120703YUL YBG E             +000000063XRN8682                                                            AE4CAD300002572
MSEAE40118315282X0080002SUMMER LEISURE PROMO          20120708+0000025000002000000000+00000.0000000000U000.00000002500+0000000000.00000XRN000250{AE4CAD200002572
FSEXXX0118315282X0080002AY11AA2012062820120703YVR YUL P             +000000572XXX 112                                                            AE4CAD300002572
FSEXRN0118315282X0080002QY11AA2012062920120703YUL YBG E             +000000063XRN8682                                                            AE4CAD300002572
MSEAE40165361809X0080002SUMMER LEISURE PROMO          20120708+0000025000002000000000+00000.0000000000U000.00000002500+0000000000.00000XXX000250{AE4CAD200002572
FSEXXX0165361809X0080002AY10AA2012062920120703YUL LGA W             +000000324XXX 744                                                            AE4CAD300002572
FSEXXX0165361809X0080002AY10AA2012070220120705LGA YUL W             +000000324XXX 753                                                            AE4CAD300002572
MSEAE40175252238X0080002SUMMER LEISURE PROMO          20120708+0000025000002000000000+00000.0000000000U000.00000002500+0000000000.00000XXX000250{AE4CAD200002572
FSEXXX0175252238X0080002AY11AA2012070520120708YYZ YHZ P             +000000200XXX 606                                                            AE4CAD300002572
FSEXXX0175252238X0080002AY11AA2012062820120703YHZ YYZ P             +000000200XXX1131                                                            AE4CAD300002572
MSEAE40197387194X0080002SUMMER LEISURE PROMO          20120708+0000025000002000000000+00000.0000000000U000.00000002500+0000000000.00000XXX000250{AE4CAD200002572
FSEXXX0197387194X0080002AY10AA2012070420120707YYC YYZ V             +000001670XXX 110                                                            AE4CAD300002572


Second file
===========
Code:
]MSEAE40118315282X0080002SUMMER LEISURE PROMO          20120708+0000025000002000000000+00000.0000000000U000.00000002500+0000000000.00000XXX000250{AE4CAD200002572
MSEAE40165361809X0080002SUMMER LEISURE PROMO          20120708+0000025000002000000000+00000.0000000000U000.00000002500+0000000000.00000XXX000250{AE4CAD200002572
MSEAE40197387194X0080002SUMMER LEISURE PROMO          20120708+0000025000002000000000+00000.0000000000U000.00000002500+0000000000.00000XXX000250{AE4CAD200002572


Third file(Output)
==================
Code:

MSEAE40118315282X0080002SUMMER LEISURE PROMO          20120708+0000025000002000000000+00000.0000000000U000.00000002500+0000000000.00000XXX000250{AE4CAD200002572
FSEXXX0118315282X0080002AY11AA2012062820120703YVR YUL P             +000000572XXX 112                                                            AE4CAD300002572
FSEXRN0118315282X0080002QY11AA2012062920120703YUL YBG E             +000000063XRN8682                                                            AE4CAD300002572
MSEAE40165361809X0080002SUMMER LEISURE PROMO          20120708+0000025000002000000000+00000.0000000000U000.00000002500+0000000000.00000XXX000250{AE4CAD200002572
FSEXXX0165361809X0080002AY10AA2012062920120703YUL LGA W             +000000324XXX 744                                                            AE4CAD300002572
FSEXXX0165361809X0080002AY10AA2012070220120705LGA YUL W             +000000324XXX 753                                                            AE4CAD300002572
MSEAE40197387194X0080002SUMMER LEISURE PROMO          20120708+0000025000002000000000+00000.0000000000U000.00000002500+0000000000.00000XXX000250{AE4CAD200002572
FSEXXX0197387194X0080002AY10AA2012070420120707YYC YYZ V             +000001670XXX 110                                                            AE4CAD300002572


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

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Wed Aug 22, 2012 10:23 pm
Reply with quote

skgupta81,

1. What is the LRECL and RECFM of file1 and file2
2. What is the position and format of the key to be compared in both files.
3. Run the following JCL and show me the complete sysout , which helps us to determine the level of DFSORT.

Code:

//S1 EXEC PGM=ICEMAN
//SYSOUT   DD SYSOUT=*
//SORTIN DD *
RECORD
/*
//SORTOUT DD DUMMY
//SYSIN   DD   *
  OPTION COPY
//*
Back to top
View user's profile Send private message
skgupta81

New User


Joined: 11 Nov 2008
Posts: 27
Location: Canada

PostPosted: Thu Aug 23, 2012 12:20 am
Reply with quote

1)RECFM=FB,
2)LRECL=165

3)
1ICE143I 0 BLOCKSET COPY TECHNIQUE SELECTED
ICE250I 0 VISIT www.ibm.com/storage/dfsort FOR DFSORT PAPERS, EXAMPLES AND MORE
ICE000I 1 - CONTROL STATEMENTS FOR 5694-A01, Z/OS DFSORT V1R5 - 18:11 ON WED AUG 22, 2012 -
0 OPTION COPY
ICE201I E RECORD TYPE IS F - DATA STARTS IN POSITION 1
ICE751I 0 C5-K26318 C6-K90007 C7-K90000 C8-K23476 E9-K90007 C9-BASE E5-K31980 E7-K24705
ICE193I 0 ICEAM1 ENVIRONMENT IN EFFECT - ICEAM1 INSTALLATION MODULE SELECTED
ICE088I 0 I075663A.S1 . , INPUT LRECL = 80, BLKSIZE = 80, TYPE = FB
ICE093I 0 MAIN STORAGE = (MAX,6291456,6278238)
ICE156I 0 MAIN STORAGE ABOVE 16MB = (6200798,6200798)
ICE127I 0 OPTIONS: OVFLO=RC0 ,PAD=RC0 ,TRUNC=RC0 ,SPANINC=RC16,VLSCMP=N,SZERO=Y,RESET=Y,VSAMEMT=Y,DYNSPC=256
ICE128I 0 OPTIONS: SIZE=6291456,MAXLIM=1048576,MINLIM=450560,EQUALS=N,LIST=Y,ERET=RC16 ,MSGDDN=SYSOUT
ICE129I 0 OPTIONS: VIO=N,RESDNT=ALL ,SMF=SHORT,WRKSEC=Y,OUTSEC=Y,VERIFY=N,CHALT=N,DYNALOC=N ,ABCODE=MSG
ICE130I 0 OPTIONS: RESALL=4096,RESINV=0,SVC=109 ,CHECK=Y,WRKREL=Y,OUTREL=Y,CKPT=N,STIMER=Y,COBEXIT=COB2
ICE131I 0 OPTIONS: TMAXLIM=6291456,ARESALL=0,ARESINV=0,OVERRGN=65536,CINV=Y,CFW=Y,DSA=0
ICE132I 0 OPTIONS: VLSHRT=N,ZDPRINT=Y,IEXIT=N,TEXIT=N,LISTX=N,EFS=NONE ,EXITCK=S,PARMDDN=DFSPARM ,FSZEST=N
ICE133I 0 OPTIONS: HIPRMAX=OPTIMAL,DSPSIZE=MAX ,ODMAXBF=0,SOLRF=Y,VLLONG=N,VSAMIO=N,MOSIZE=MAX
ICE235I 0 OPTIONS: NULLOUT=RC0
ICE084I 0 BSAM ACCESS METHOD USED FOR SORTOUT
ICE084I 0 BSAM ACCESS METHOD USED FOR SORTIN
ICE751I 1 EF-K10929 F0-K30362 E8-K24705
ICE090I 0 OUTPUT LRECL = 80, BLKSIZE = 80, TYPE = FB
ICE055I 0 INSERT 0, DELETE 0
ICE054I 0 RECORDS - IN: 1, OUT: 1
ICE026I 1 SMF RECORD NOT WRITTEN TO THE SMF DATA SET(RC=20)
ICE052I 0 END OF DFSORT

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

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Thu Aug 23, 2012 2:57 am
Reply with quote

skgupta81,

Please read my question carefully once again and answer ALL the questions I asked.

And just for the record, DFSORT V1R5 is out of service and is unsupported. You need multiple passes of the data to get the desired results you want.
Back to top
View user's profile Send private message
skgupta81

New User


Joined: 11 Nov 2008
Posts: 27
Location: Canada

PostPosted: Thu Aug 23, 2012 3:49 am
Reply with quote

I am sorry.. Answers of first two questions.

Question 1: What is the LRECL and RECFM of file1 and file2

Both files have
RECFM=FB,
LRECL=165

2. What is the position and format of the key to be compared in both files.

The whole record in second file is the key. so the format is alphanumeric.
Above the long record(165) is seen in two lines but its actually a single line.

Please do let me know if you need more information.
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Thu Aug 23, 2012 4:47 am
Reply with quote

Use the following DFSORT/ICETOOL JCL which will give you the desired results

Code:

//STEP0100 EXEC PGM=ICETOOL                                       
//TOOLMSG  DD SYSOUT=*                                           
//DFSMSG   DD SYSOUT=*                                           
//INA      DD DSN=Your First FB 165 byte file,DISP=SHR
//INB      DD DSN=Your Second FB 165 byte file,DISP=SHR
//TMP      DD DSN=&&TMP,DISP=(MOD,PASS),SPACE=(CYL,(X,Y),RLSE)   
//OUT      DD SYSOUT=*                                           
//TOOLIN   DD *                                                   
  COPY FROM(INB) TO(TMP) USING(CTL1)                   
           
  SPLICE FROM(INA) TO(TMP) ON(331,8,CH) KEEPNODUPS KEEPBASE -     
  WITHALL WITH(01,165) USING(CTL2)                               

  SPLICE FROM(TMP) TO(OUT) ON(1,165,CH) WITHALL  -               
    WITH(166,166) KEEPNODUPS KEEPBASE USING(CTL3)                 
//*                                                               
//CTL1CNTL DD *                                                   
  INREC OVERLAY=(331:C'00')                                       
//*                                                               
//CTL2CNTL DD *                                                   
  OPTION COPY                                                     
  INREC IFTHEN=(WHEN=INIT,OVERLAY=(331:SEQNUM,8,ZD)),             
  IFTHEN=(WHEN=(1,1,CH,EQ,C'M'),OVERLAY=(166:1,165,SEQNUM,8,ZD)),
  IFTHEN=(WHEN=NONE,OVERLAY=(339:SEQNUM,8,ZD,                     
          331:331,8,ZD,SUB,339,8,ZD,M11,LENGTH=8))               
                                                                 
  OUTFIL FNAMES=TMP,BUILD=(166,165,1,165,C'11')                   
//*                                                               
//CTL3CNTL DD *                                                   
  OUTFIL FNAMES=OUT,INCLUDE=(331,2,ZD,EQ,10),BUILD=(166,165)     
//*
Back to top
View user's profile Send private message
skgupta81

New User


Joined: 11 Nov 2008
Posts: 27
Location: Canada

PostPosted: Fri Aug 24, 2012 3:14 am
Reply with quote

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

New User


Joined: 20 Apr 2024
Posts: 6
Location: Hyderabad

PostPosted: Wed Apr 24, 2024 3:52 am
Reply with quote

Hi SK Gupta, I have similar requirement of yours.

Can you or Skolusu help on the understanding.

I have 2 files, both of LRECL = 133 & RECFM = FB.
File 1 has key from position 22 to position 29.
File 2 has key from position 1 to position 8.

I need a ICETOOL condition when the key(1-8) from file 2 matches key(22-29) on file 1, write the complete record of the key match from file 1 to Out record data set(Output).

NOTE: File 2 has unique key records and File 1 has multiple keys and for each key they have multiple records/rows(I need the key record and the associated records/rows for that key.

Example:
File 1 Record Layout: Please find attachment for clear File 1 layout
Code:
----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
12345678XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
23456789XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
34567891XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX


File 2 Record Layout: Please find attachment for clear File 2 layout
Code:
----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
XXXX-XXXX-XXXXXXXX 12345678 XXXXX XXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXX
XXXXXXXXXXXXX
XXXXXXXXXXXXX
XXXXXXXXXXXXX
XXXXXXXXX XXXXXXXXXXXXX
XXXXXX XXXXXXXX XXXXXXXXXXXXX

XXXX-XXXX-XXXXXXXX 12345678 XXXXXXXX

XXXXXXXXXXXXX
XXXX-XXXX-XXXXXXXX 23456789 XXXXXXXX

XXXX-XXXX-XXXXXXXX 23456789 XXXXXXXX XXXXXXXX XXXXXXXXXXXXX
XXXXXXXXXXXXX
XXXXXXXXXXXXX
XXXXXXXXXXXXX
XXXXXXXXXXXXX


Expected output: Example if Key: 12345678 matches in file 1
Code:
----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
XXXX-XXXX-XXXXXXXX 12345678 XXXXX XXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXX
XXXXXXXXXXXXX
XXXXXXXXXXXXX
XXXXXXXXXXXXX
XXXXXXXXX XXXXXXXXXXXXX
XXXXXX XXXXXXXX XXXXXXXXXXXXX


When Key from File 2 matches key on File 1(even in multiple cases) write the complete record of file 1 into output
Example from above layout:
Key 12345678 from file 2 matches 2 times in file 1 with different record count like first occurrence in file 1 with key 12345678 has 8 records(including the Key record), I want all the 8 records into output (Matched records). Second occurrence with same key: 12345678 has 3 records which i want to put them in output 1(matched records) as well.

Please help me on this if you can.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3058
Location: NYC,USA

PostPosted: Wed Apr 24, 2024 5:20 am
Reply with quote

Please don’t tailgate old posts but instead start a new one..
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   This topic is locked: you cannot edit posts or make replies. View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DFSORT/ICETOOL

 


Similar Topics
Topic Forum Replies
No new posts To fetch records that has Ttamp value... DFSORT/ICETOOL 4
No new posts ICETOOL returns no records JCL & VSAM 1
No new posts Compare 2 files and retrive records f... DFSORT/ICETOOL 3
No new posts Compare 2 files and write Matched/Unm... JCL & VSAM 8
No new posts Write line by line from two files DFSORT/ICETOOL 7
Search our Forums:

Back to Top