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
 

 

ICETOOL JOIN - All recs needed from one input file

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

New User


Joined: 04 Feb 2008
Posts: 65
Location: Hyd

PostPosted: Fri Nov 20, 2009 7:18 pm    Post subject: ICETOOL JOIN - All recs needed from one input file
Reply with quote

Hi All,

Here i am trying to join two files using below sort card.
As per my requirement i should get all records from file2.
If file1 key = file2 key then i should get file2 rec + some file1 fields.
If file1 key not = file2 key then i should get just file2 record.

In my case i am getting all required fields from file1 and file2 but
i am not getting all file2 records.

Sort Card:
Code:

//IN1      DD DSN=file1,DISP=SHR         ---LRECL=859         
//IN2      DD DSN=file2,DISP=SHR         ---LRECL=94 
//T1       DD DSN=&&T1,                                       
//            DISP=(MOD,PASS),                                 
//            UNIT=SYSDA,SPACE=(CYL,(10,10),RLSE),             
//            DCB=(RECFM=FB)                                   
//OUT      DD DSN=output file,                 
//            DISP=(NEW,CATLG,DELETE),                         
//            UNIT=SYSID,SPACE=(CYL,(50,50),RLSE)             
//TOOLIN   DD *                                               
  COPY FROM(IN1) TO(T1) USING(CTL1)                           
  COPY FROM(IN2) TO(T1) USING(CTL2)                           
  SPLICE FROM(T1) TO(OUT) ON(1,6,CH) WITHALL WITH(1,94)       
/*                                                             
//CTL1CNTL DD *                                               
  INREC BUILD=(1:1,6,7:88X,95:7,35,207,1)                     
/*                                                             
//CTL2CNTL DD *                                               
  INREC BUILD=(1:1,94,95:36X)                                 
/*                                                             
//SYSOUT   DD SYSOUT=*                                   



Please let me know the modifications, the above sort card needed.

Thanks,
Kalyan V
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: Fri Nov 20, 2009 10:18 pm    Post subject:
Reply with quote

Showing a job that doesn't do what you want really doesn't help anyone help you.

Please show an example of the records in each input file (relevant fields only) and what you expect for output. Explain the "rules" for getting from input to output. Give the starting position, length and format of each relevant field. Give the RECFM and LRECL of the input files. If file1 can have duplicates within it, show that in your example. If file2 can have duplicates within it, show that in your example.
Back to top
View user's profile Send private message
kalyan.v

New User


Joined: 04 Feb 2008
Posts: 65
Location: Hyd

PostPosted: Mon Nov 23, 2009 2:38 pm    Post subject:
Reply with quote

Hi,

Please find the example here.

FILE-1 fields:LRECL=859
Code:


1(6)   7(35)                             201(1) 
001122       PIGGOTT HOSPITAL            3
004017    FT. SMITH RIM & BOW CO.        6         
004951      BANK OF LAKE VILLAGE         5
005143    MEDICAL CTR OF CALICO ROCK     7
005547    RANDOLPH CO CLERKS OFFICE      1
008034     1ST PARAGOULD BANKSHARE       2
008877     SEAARK MARINE, INC.           
009935     WARREN BANK AND TRUST CO.     2




FILE2 FIELDS:: LRECL=94
Code:



1(6) - Key                           
000384990086870   02NADINE    FARISH         ---------XXXXXX
000384990045444   02KATHRYN   PURCELL        ---------XXXXXX
000384980142903   01CLANCI    TAYLOR         ---------XXXXXX
000702V89564      01VICTOR    SMITH JR       ---------XXXXXX
001001960262464   01MICHIAL   HALTOM         ---------XXXXXX
001122980008456   03KRISTI    LAMBERT        ---------XXXXXX
001122U66812      02REGINA    MAURICIO       ---------XXXXXX
001122980381543   03VANESSA   FULLER         ---------XXXXXX
001122980019691   01ALICE     BRADSHAW       ---------XXXXXX
001122980019691   01ALICE     BRADSHAW       ---------XXXXXX
001122980008456   01KELLY     LAMBERT        ---------XXXXXX
001122980349736   01CHARLES   LAMBORN        ---------XXXXXX
001122980008456   01KELLY     LAMBERT        ---------XXXXXX
001122980397464   01BRITTNEY  BELCHER        ---------XXXXXX
001122980019691   01ALICE     BRADSHAW       ---------XXXXXX
001122L90442      01TANIYA    CARPENTER      ---------XXXXXX
001122980019691   01ALICE     BRADSHAW       ---------XXXXXX
     
     



OUTPUT FILE:
Code:


1(6) – Key                                                94:95(36)
000384990086870   02NADINE    FARISH         ---------XXXXXX
000384990045444   02KATHRYN   PURCELL        ---------XXXXXX
000384980142903   01CLANCI    TAYLOR         ---------XXXXXX
000702V89564      01VICTOR    SMITH JR       ---------XXXXXX
001001960262464   01MICHIAL   HALTOM         ---------XXXXXX
001122980008456   03KRISTI    LAMBERT        ---------XXXXXX PIGGOTT HOSPITAL            3
001122U66812      02REGINA    MAURICIO       ---------XXXXXX PIGGOTT HOSPITAL            3
001122980381543   03VANESSA   FULLER         ---------XXXXXX PIGGOTT HOSPITAL            3
001122980019691   01ALICE     BRADSHAW       ---------XXXXXX PIGGOTT HOSPITAL            3
001122980019691   01ALICE     BRADSHAW       ---------XXXXXX PIGGOTT HOSPITAL            3
001122980008456   01KELLY     LAMBERT        ---------XXXXXX PIGGOTT HOSPITAL            3
001122980349736   01CHARLES   LAMBORN        ---------XXXXXX PIGGOTT HOSPITAL            3
001122980008456   01KELLY     LAMBERT        ---------XXXXXX PIGGOTT HOSPITAL            3
001122980397464   01BRITTNEY  BELCHER        ---------XXXXXX PIGGOTT HOSPITAL            3
001122980019691   01ALICE     BRADSHAW       ---------XXXXXX PIGGOTT HOSPITAL            3
001122L90442      01TANIYA    CARPENTER      ---------XXXXXX PIGGOTT HOSPITAL            3
001122980019691   01ALICE     BRADSHAW       ---------XXXXXX PIGGOTT HOSPITAL            3



The above is my required output....

Thanks,
Kalyan V
Back to top
View user's profile Send private message
Skolusu

Senior Member


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

PostPosted: Mon Nov 23, 2009 11:00 pm    Post subject: Reply to: ICETOOL JOIN - All recs needed from one input file
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=*                                               
//FB895    DD DSN=Your FB 895 byte file,DISP=SHR
//FB094    DD DSN=Your FB 094 byte file,DISP=SHR
//TEMP     DD DSN=&&TEMP,DISP=(MOD,PASS),SPACE=(CYL,(X,Y),RLSE)       
//OUT      DD SYSOUT=*                                               
//TOOLIN   DD *                                                       
  COPY FROM(FB895) USING(CTL1)                                       
  COPY FROM(FB094) USING(CTL2)                                       
  SORT FROM(TEMP) USING(CTL3)                                         
//CTL1CNTL DD *                                                       
  OUTFIL FNAMES=TEMP,BUILD=(1,6,95:7,35,201,1,C'1')                   
//CTL2CNTL DD *                                                       
  OUTFIL FNAMES=TEMP,OVERLAY=(95:36X,C'2')                           
//CTL3CNTL DD *                                                       
  SORT FIELDS=(1,6,CH,A),EQUALS                                       
  OUTREC IFTHEN=(WHEN=INIT,OVERLAY=(133:SEQNUM,8,ZD,RESTART=(1,6))), 
  IFTHEN=(WHEN=GROUP,BEGIN=(133,8,ZD,EQ,1),PUSH=(95:95,36,132:131,1))
  OUTFIL FNAMES=OUT,INCLUDE=(131,2,ZD,GE,21),BUILD=(1,130)           
//*
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 Converting Variable block file to Fix... bhavana yalavarthi DFSORT/ICETOOL 10 Tue May 16, 2017 2:20 pm
No new posts Replace values in the input data Vikas Maharnawar DFSORT/ICETOOL 10 Thu May 11, 2017 2:18 pm
No new posts Using ICETOOL, how we can seperate th... bshkris SYNCSORT 5 Tue May 09, 2017 8:33 pm
No new posts Getting error while opening a variabl... apandey1 COBOL Programming 5 Fri May 05, 2017 12:22 pm
No new posts Issue with NDM process to transmit ES... chetanambi All Other Mainframe Topics 6 Wed May 03, 2017 10:52 am


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