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

ICETOOL JOIN - All recs needed from one input file


IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic
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
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 Developer


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

PostPosted: Fri Nov 20, 2009 10:18 pm
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
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
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DFSORT/ICETOOL

 


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 Need help for File Aid JCL to extract... Compuware & Other Tools 23
Search our Forums:

Back to Top