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
 

 

Extract records from two files to multiple files based on ke

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

New User


Joined: 23 Dec 2009
Posts: 2
Location: chennai

PostPosted: Thu Dec 31, 2009 12:55 am    Post subject: Extract records from two files to multiple files based on ke
Reply with quote

My requirement is to extract records from two files FA FB and write into three different output files F1 F2 F3 on following conditions

FA has 8 million recrds and can grow big (linear) LRECL=256 key at 201
FB will have 50k records. LRECL=80, key at pos 1

for each key in FB i want to extract all matchin records in FA to F1
all unmatched records from FA to F2 and
unmatched records in FB to F3

eg.
FA
ssssssssssss123eeeeeeeeee
xxxxxxxxxx123ssssssssssss
xxxxxxxxxx234ssssssssssss
yyyyyyyyyy234ssssssssssss
xxxxxxxxxx456ssssssssssss
yyyyyyyyyy456eeeeeeeeee


FB

123
234
567
789

Output files should be
F1
ssssssssssss123eeeeeeeeee
xxxxxxxxxx123ssssssssssss
xxxxxxxxxx234ssssssssssss
yyyyyyyyyy234ssssssssssss

F2
xxxxxxxxxx456ssssssssssss
yyyyyyyyyy456eeeeeeeeee

F3
567
789

Can you please help me out.

The file FA an F1 are tape files, all others are DASD.
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 Dec 31, 2009 5:47 am    Post subject:
Reply with quote

You can either do this with DFSORT using JOINKEYS or with DFSORT's ICETOOL using SPLICE. There are plenty of examples of both on this board.

If you need more specific help on this, I need some more information:

Tell me the RECFM of FA and FB.

What is the length of the key (it appears to be 3 bytes in your example, but I want to make sure)?

Also, run this job and show me the //SYSOUT messages so I can tell which level of DFSORT you have:

Code:

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

New User


Joined: 23 Dec 2009
Posts: 2
Location: chennai

PostPosted: Thu Dec 31, 2009 4:03 pm    Post subject:
Reply with quote

Frank Thanks for you help in this.

The RECFM is FB for all files. And the Key length is 12 bytes.

Attached the SYSOUT of the sort code.

Thanks again and Wish u a VERY HAPPY NEW YEAR

Code:

ICE143I 0 BLOCKSET     COPY  TECHNIQUE SELECTED
ICE250I 0 VISIT http://www.ibm.com/storage/dfsort FOR DFSORT PAPERS, EXAMPLES AND MORE
ICE000I 1 - CONTROL STATEMENTS FOR 5694-A01, Z/OS DFSORT V1R5 - 06:46 ON THU DEC 31, 2009 -
OPTION COPY                                                           
ICE201I F RECORD TYPE IS F - DATA STARTS IN POSITION 1
ICE751I 0 C5-K90013 C6-K90013 C7-K90000 C8-K42135 E9-K90013 C9-BASE   E5-K44563 E7-K44563
ICE193I 0 ICEAM1 ENVIRONMENT IN EFFECT - ICEAM1 INSTALLATION MODULE SELECTED
ICE088I 0 DVKYMF28.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=8      ,DSPSIZE=128 ,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-Q84357 E8-K44563
ICE090I 0 OUTPUT LRECL = 80, BLKSIZE = 80, TYPE = FB
ICE055I 0 INSERT 0, DELETE 0
ICE054I 0 RECORDS - IN: 1, OUT: 1
ICE052I 0 END OF DFSORT
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: Sat Jan 02, 2010 11:37 pm    Post subject:
Reply with quote

You don't have the Nov, 2009 DFSORT PTF, so you can only use SPLICE, not JOINKEYS. Hosever, I'll show both ways.

JOINKEYS would be more efficient. Here's the DFSORT job with JOINKEYS:

Code:

//JK    EXEC  PGM=SORT
//SYSOUT    DD  SYSOUT=*
//FA DD DSN=...  input file A (FB/256)
//FB DD DSN=...  input file B (FB/80)
//F1 DD DSN=...  output file F1 (FB/256)
//F2 DD DSN=...  output file F2 (FB/256)
//F3 DD DSN=...  output file F3 (FB/80)
//SYSIN    DD    *
  JOINKEYS F1=FB,FIELDS=(1,12,A),SORTED
  JOINKEYS F2=FA,FIELDS=(201,12,A),SORTED
  JOIN UNPAIRED,F1,F2
  REFORMAT FIELDS=(?,F1:1,12,F2:1,256)
  OPTION COPY
  OUTFIL FNAMES=F1,INCLUDE=(1,1,CH,EQ,C'B'),
    BUILD=(14,256)
  OUTFIL FNAMES=F2,INCLUDE=(1,1,CH,EQ,C'2'),
    BUILD=(14,256)
  OUTFIL FNAMES=F3,INCLUDE=(1,1,CH,EQ,C'1'),
    BUILD=(2,12,80:X)
/*


Here's the DFSORT job with SPLICE:

Code:

//S1   EXEC  PGM=ICETOOL
//TOOLMSG   DD  SYSOUT=*
//DFSMSG    DD  SYSOUT=*
//FA DD DSN=...  input file A (FB/256)
//FB DD DSN=...  input file B (FB/80)
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//F1 DD DSN=...  output file F1 (FB/256)
//F2 DD DSN=...  output file F2 (FB/256)
//F3 DD DSN=...  output file F3 (FB/80)
//TOOLIN DD *
COPY FROM(FB) TO(T1) USING(CTL1)
COPY FROM(FA) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(F1) ON(201,12,CH) KEEPNODUPS -
  WITHALL WITH(1,200) WITH(213,45) USING(CTL3)
SPLICE FROM(T1) TO(F2) ON(201,12,CH) KEEPNODUPS KEEPBASE -
  WITHALL WITH(1,200) WITH(213,45) USING(CTL4)
/*
//CTL1CNTL DD *
  INREC BUILD=(201:1,12,257:C'BB')
/*
//CTL2CNTL DD *
  INREC OVERLAY=(257:C'VV')
/*
//CTL3CNTL DD *
  OUTFIL FNAMES=F1,INCLUDE=(257,2,CH,EQ,C'VB'),
    BUILD=(1,256)
  OUTFIL FNAMES=F3,INCLUDE=(257,2,CH,EQ,C'BB'),
    BUILD=(201,12,80:X)
/*
//CTL4CNTL DD *
  OUTFIL FNAMES=F2,INCLUDE=(257,2,CH,EQ,C'VV'),
    BUILD=(1,256)
/*
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 Data replication from multiple Db2 ta... kishpra DB2 5 Mon Mar 27, 2017 9:58 pm
No new posts Unable to catalog a gdg dataset resid... Shovan JCL & VSAM 7 Fri Mar 24, 2017 2:24 pm
No new posts Append data from two files into a sin... Praveen04 DFSORT/ICETOOL 5 Thu Mar 16, 2017 7:29 pm
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts To display values of multiple rows in... vinu78 DB2 2 Thu Mar 09, 2017 1:20 pm


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