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
 

 

How do we combine two files with a key

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

New User


Joined: 07 Feb 2007
Posts: 22
Location: hyderabad

PostPosted: Thu Feb 21, 2008 2:42 pm    Post subject: How do we combine two files with a key
Reply with quote

There are two files with key in different positions in two files.

FILE 1: key position: 3-5

AA001RECS
AB010RECS
AB009RECS
CH007RECS

FILE 2: key position: 1-3

001SOP
002PQK
007KLM
009NMJ
010HUE

Output file needs to be like

AA001RECSSOP
AB010RECSHUE
AB009RECSNMJ
CH007RECSKLM

I want the records in output file to be in same order as File 1 with corresponding details from the file2 appended at the end of the record.

Any help is appreciated.

Srikar
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 Feb 21, 2008 10:02 pm    Post subject:
Reply with quote

Srikar,

Here's a DFSORT/ICETOOL job that will do what you asked for. I assumed your input files have RECFM=FB and LRECL=80, but you can change the job appropriately for other attributes.

Code:

//S1    EXEC  PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG  DD SYSOUT=*
//IN1 DD *
AA001RECS
AB010RECS
AB009RECS
CH007RECS
/*
//IN2 DD *
001SOP
002PQK
007KLM
009NMJ
010HUE
/*
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//T2 DD DSN=&&T2,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS)
//OUT DD SYSOUT=*
//TOOLIN DD *
COPY FROM(IN1) TO(T1) USING(CTL1)
COPY FROM(IN2) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(T2) ON(3,3,CH) WITH(10,3)
SORT FROM(T2) TO(OUT) USING(CTL3)
/*
//CTL1CNTL DD *
  INREC OVERLAY=(81:SEQNUM,8,ZD)
/*
//CTL2CNTL DD *
  INREC BUILD=(3:1,3,10:4,3,81:8X)
/*
//CTL3CNTL DD *
  SORT FIELDS=(81,8,ZD,A)
  OUTREC BUILD=(1,80)
/*
Back to top
View user's profile Send private message
srsathish

New User


Joined: 20 Feb 2007
Posts: 6
Location: Westborough, MA

PostPosted: Fri Feb 22, 2008 1:16 pm    Post subject: Reply to: How do we combine two files with a key
Reply with quote

Hi Frank,

Could you please give me a brief explanation on the syntax in the following lines. i.e., how is it functioning.

1) SPLICE FROM(T1) TO(T2) ON(3,3,CH) WITH(10,3)
2) INREC OVERLAY=(81:SEQNUM,8,ZD)
3) INREC BUILD=(3:1,3,10:4,3,81:8X)
4) SORT FIELDS=(81,8,ZD,A)
OUTREC BUILD=(1,80)

thanks in advance.

-sr sathish
Back to top
View user's profile Send private message
srikar tenali

New User


Joined: 07 Feb 2007
Posts: 22
Location: hyderabad

PostPosted: Fri Feb 22, 2008 2:41 pm    Post subject: Reply to: How do we combine two files with a key
Reply with quote

How do we code if the following conditions needs to be satisfied.
1. If the corresponding record of the second file has 'M' or 'K' in the 6th position, then only the record should be written in the output file.
2. The output file needs to be in same format as first file, no extra details needs to be appended.

Thanks,
Srikar
Back to top
View user's profile Send private message
srikar tenali

New User


Joined: 07 Feb 2007
Posts: 22
Location: hyderabad

PostPosted: Fri Feb 22, 2008 5:00 pm    Post subject: Reply to: How do we combine two files with a key
Reply with quote

Missed out some conditions on the above question.
Read the complete question here.

There are two files with key in different positions in two files.

FILE 1: key position: 1-2, foreign key position: 3-5

AA001RECS
BB001RECS
CC010RECS
DD010RECS
EE010RECS
FF009RECS
GG007RECS

FILE 2: key position: 1-3

001AAAY
002BBBY
007CCCN
009DDDY
010HUEN

Output file needs to be like

AA001RECS
BB001RECS
FF009RECS

Only those records of first file, whose corresponding record in the second file has 'Y' in the 7th position must be copied into the output file.
For Eg:
first record of the first file
AA001RECS
its corresponding record in the second file is
001AAAY, as this has Y in 7th position. this record should be copied into the output file.

Thanks in advance.
Srikar
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 Feb 22, 2008 10:58 pm    Post subject:
Reply with quote

Srikar,

You can use a DFSORT/ICETOOL job like the following for your new requirement. Since you didn't say the records needed to be in their original order this time, I assumed they didn't. The job can be changed appropriately if they do. I put comments in the job to explain what it's doing. For complete details on DFSORT's SPLICE operator, see:

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/ICE1CA20/6.13?DT=20060615185603

Code:

//S1    EXEC  PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG  DD SYSOUT=*
//IN1 DD *
AA001RECS
BB001RECS
CC010RECS
DD010RECS
EE010RECS
FF009RECS
GG007RECS
/*
//IN2 DD *
001AAAY
002BBBY
007CCCN
009DDDY
010HUEN
/*
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//OUT DD SYSOUT=*
//TOOLIN DD *
* COPY IN2 TO T1 USING CTL1CNTL STATEMENTS.
* ONLY KEEP IN2 RECORDS WITH 'Y'.
* REFORMAT IN2 RECORDS TO:
* |   |KEY|   |BB|
COPY FROM(IN2) TO(T1) USING(CTL1)
* COPY IN1 TO T1 USING CTL2CNTL STATEMENTS.
* REFORMAT IN1 RECORDS TO:
* |...|KEY|...|VV|
COPY FROM(IN1) TO(T1) USING(CTL2)
* SPLICE T1 RECORDS ON KEY WITH POSITIONS 1-81
* FROM REFORMATTED IN1 RECORDS AND POSITION 82 OF
* REFORMATTED IN2 RECORDS TO GET:
* |...|KEY|...|ID|
* KEEP RECORDS WITH ID OF 'VB' (MATCH IN IN2 AND IN1)
* REMOVED ID.
SPLICE FROM(T1) TO(OUT) ON(3,3,CH) -
  WITHALL WITH(1,81) USING(CTL3)
/*
//CTL1CNTL DD *
  INCLUDE COND=(7,1,CH,EQ,C'Y')
  INREC BUILD=(3:1,3,81:C'BB')
/*
//CTL2CNTL DD *
  INREC OVERLAY=(81:C'VV')
/*
//CTL3CNTL DD *
  OUTFIL FNAMES=OUT,INCLUDE=(81,2,CH,EQ,C'VB'),
    BUILD=(1,80)
/*
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 Append data from two files into a sin... Praveen04 DFSORT/ICETOOL 5 Thu Mar 16, 2017 7:29 pm
No new posts High CPU consumption Job using IAM fi... aswinir JCL & VSAM 15 Thu Dec 01, 2016 8:28 pm
No new posts Match or compare two files in VB Format anatol DFSORT/ICETOOL 14 Thu Nov 03, 2016 7:41 pm
This topic is locked: you cannot edit posts or make replies. How to use 2 input files in control c... Gunapala CN DFSORT/ICETOOL 23 Thu Oct 13, 2016 3:42 pm
No new posts Adding records from two files into on... shiitiizz SYNCSORT 4 Mon Sep 19, 2016 8:41 pm


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