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

How to insert a field from another file?
IBM Mainframe Forums -> JCL & VSAM
Post new topic   Reply to topic Quick References
View previous topic :: View next topic  
Author Message
sabarikanth

New User


Joined: 07 Jun 2010
Posts: 59
Location: coimbatore
PostPosted: Thu Feb 02, 2012 1:36 am
 Reply with quote
Hi all,

I have a requirement as i need to compare two files based on a condition and I have to insert a field from second file to the first file,
Here is an example:

Input file 1:
Code:

S.No:1
Name: ABC
Age:20
Sex: male
Accnum: 12345
S.No:2
Name: DEF
Age:21
Sex: male
Accnum: 67890
S.No:3
Name: ZDE
Age:25
Sex: male
Accnum: 99945
Input file 2:
Code:

12345 QWE
67890 POI
56862 QWE
My output should be:
Code:

S.No:1
Name: ABC QWE
Age:20
Sex: male
Accnum: 12345
S.No:2
Name: DEF POI
Age:21
Sex: male
Accnum: 67890
S.No:3
Name: ZDE
Age:25
Sex: male
Accnum: 99945


here i have to match the Accnum field (in both the input files 1 and 2) and if matched insert it after Name field (in file 1). Can someone help on this.

Note: The position of Name field and Accnum field are constant.
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1788
Location: Bloomington, IL
PostPosted: Thu Feb 02, 2012 2:16 am
 Reply with quote
  1. May we assume that the "Accnum" record in the first data set is invariably three records (ETA) after the "Name" record?
  2. Are both data sets ordered by "Accnum"?
  3. Do you have a preferred means of accomplishing this? (*SORT, COBOL, PL/I, Rexx, LISP)
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: Thu Feb 02, 2012 2:17 am
 Reply with quote
Sab,

What is the RECFM and LRECL of input file1?

What is the RECFM and LRECL of input file2?

In input file 1, are there always 5 records (S.No:, Name:, Age:, Sex:, Accnum:) for each Accnum group?
Back to top
View user's profile Send private message
sabarikanth

New User


Joined: 07 Jun 2010
Posts: 59
Location: coimbatore
PostPosted: Thu Feb 02, 2012 2:43 am
 Reply with quote
Akatsukami:

Quote:
May we assume that the "Accnum" record in the first data set is invariably three records (ETA) after the "Name" record?
Accnum (char 5) can be at any place after Name field in the first file.

Quote:
Are both data sets ordered by "Accnum"?

File-2 is sorted based on Accnum.
Quote:

Do you have a preferred means of accomplishing this? (*SORT, COBOL, PL/I, Rexx, LISP)

Mostly Syncsort will do. But cobol is allowed.

Frank Yaeger:

RECFM and LRECL is FB and 80 respectively for both file-1 and file-2.

Quote:
In input file 1, are there always 5 records (S.No:, Name:, Age:, Sex:, Accnum:) for each Accnum group
?

No input file-1 may have more than 5 records((S.No:, Name:, Age:, Sex:, Accnum:) for each Accnum group.
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: Thu Feb 02, 2012 3:00 am
 Reply with quote
Quote:
Mostly Syncsort will do


Since you want Syncsort, not DFSORT, I'm out.
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1788
Location: Bloomington, IL
PostPosted: Thu Feb 02, 2012 3:03 am
 Reply with quote
Frank Yaeger wrote:
Quote:
Mostly Syncsort will do


Since you want Syncsort, not DFSORT, I'm out.

Being neither a Syncsort maven nor a COBOL programmer, I must also recuse myself.
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
PostPosted: Thu Feb 02, 2012 3:46 am
 Reply with quote
So you have a file which contains fields instead of records? I bet someone thought that was a great idea.

You didn't answer the recently recused's question fully - is the first file, the groups of fields, in Accnum order?

Is there a limit to the number of fields making up these "distributed" records?

If so, in Cobol, it is just a modified two-file-match, storing the "distributed" record in a table, knowing where the Name is and adding it from the second file and flushing the table in the order it was stored.

If the first file is not in Accnum order, make it so.

In Syncsort, I don't know. I don't have documentation even.

If you can fully describe the file 1, how the combinations might appear, minimum/maximum no of fields per group. LRECL and RECFM.
Back to top
View user's profile Send private message
sabarikanth

New User


Joined: 07 Jun 2010
Posts: 59
Location: coimbatore
PostPosted: Thu Feb 02, 2012 4:21 am
 Reply with quote
Bill,
Input File-1:

File-1 has records in group. I'm not sure how many it may be. The group of records will start with S.no and end with ENDMSG. And also it is not defined as only S.no, Name, Age, Sex, Accnum will be in the group (this group may vary with records), As shown below

Code:

S.No:1
Name: ABC
Age:20
Sex: male
Accnum: 12345
ENDMSG
S.No:2
Name: DEF
Age:21
Sex: male
Addr: <Unknown>
Accnum: 67890
City: XX
State: YY
ENDMSG
S.No:3
Name: ZDE
Age:25
Sex: male
Accnum: 99945
Company: III
Code:87944555
ENDMSG


this file is sorted based on S.no. LRECL=80 RECFM=FB.

Input FILE-2:

File-2 has Accnum and Extname as shown below,

Code:
12345 QWE
67890 POI
56862 QWE


this file is sorted based on Accnum. Lrecl=80 and Recf=fb

My output:

i have to merge these two datasets with Accnum as basis. Once the Accnum matches I have to write the Extname from File-2 in Name field of File-1 as shown below,
Code:

S.No:1
Name: ABC QWE
Age:20
Sex: male
Accnum: 12345
ENDMSG
S.No:2
Name: DEF POI
Age:21
Sex: male
Addr: <Unknown>
Accnum: 67890
City: XX
State: YY
ENDMSG
S.No:3
Name: ZDE
Age:25
Sex: male
Accnum: 99945
Company: III
Code:87944555
ENDMSG


O/p file should be Recfm=Fb and Lrecl=80.

I hope i'm clear little bit.
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
PostPosted: Thu Feb 02, 2012 4:45 am
 Reply with quote
Personnaly I'd read file 1, making a new output file, 240-byte records, consisting of S.No, Name and Accnum.

Sort that file on Accnum.

Join to file 2 (with Sort) to get the name updated on the new file.

Sort on S.No.

Match to File 1 on S.No. Take next Name record and update it for your final output.

There may be a Sort-only solution using GROUP, but you'd have to investigate that yourself or hope someone who knows comes along.
Back to top
View user's profile Send private message
gcicchet

Senior Member


Joined: 28 Jul 2006
Posts: 1702
Location: Australia
PostPosted: Fri Feb 03, 2012 3:28 pm
 Reply with quote
Hi.

this may assist
Code:
//STEP0100 EXEC PGM=SORT                                           
//SYSOUT   DD SYSOUT=*                                             
//SORTIN   DD *                                                   
S.NO:1                                                             
NAME: ABC                                                         
AGE:20                                                             
SEX: MALE                                                         
ACCNUM: 12345                                                     
ENDMSG                                                             
S.NO:2                                                             
NAME: DEF                                                         
AGE:21                                                             
SEX: MALE                                                         
ADDR: <UNKNOWN>                                                   
ACCNUM: 67890                                                     
CITY: XX                                                           
STATE: YY                                                         
ENDMSG                                                           
S.NO:3                                                           
NAME: ZDE                                                         
AGE:25                                                           
SEX: MALE                                                         
ACCNUM: 99945                                                     
COMPANY: III                                                     
CODE:87944555                                                     
ENDMSG                                                           
//SORTOUT  DD DSN=&&T1,                                           
//            DISP=(,PASS,DELETE),                               
//            UNIT=SYSDA,                                         
//            SPACE=(TRK,(10,5),RLSE)                             
//SYSIN    DD *                                                   
  INREC IFTHEN=(WHEN=INIT,OVERLAY=(50:SEQNUM,8,ZD,70:1,30)),     
        IFTHEN=(WHEN=GROUP,                                       
                BEGIN=(1,5,CH,EQ,C'S.NO:'),                       
                  END=(1,6,CH,EQ,C'ENDMSG'),                     
                 PUSH=(60:ID=8)),                                 
        IFTHEN=(WHEN=(1,7,CH,EQ,C'ACCNUM:'),                 
               OVERLAY=(70:X'0000000000000000'))             
  SORT FIELDS=(60,40,BI,A)                                   
 OUTREC IFTHEN=(WHEN=GROUP,                                   
                BEGIN=(70,8,CH,EQ,X'0000000000000000'),       
                 PUSH=(100:78,5))                             
/*                                                           
//STEP0200 EXEC PGM=SORT                                       
//SYSOUT   DD SYSOUT=*                                         
//SORTJNF1 DD *                                               
12345 QWE                                                     
67890 POI                                                     
56862 QWE                                                     
/*                                                           
//SORTJNF2 DD DSN=&&T1,DISP=SHR                               
//SORTOUT  DD SYSOUT=*                                       
//SYSIN    DD *                                               
  JOINKEYS FILE=F1,FIELDS=(001,5,A)                           
  JOINKEYS FILE=F2,FIELDS=(100,5,A)                           
  JOIN UNPAIRED,F2                                   
  REFORMAT FIELDS=(F1:01,10,                         
                   F2:01,30,50,8)                   
  SORT FIELDS=(41,8,BI,A)                           
  OUTREC IFTHEN=(WHEN=(11,5,CH,EQ,C'NAME:'),         
                 BUILD=(11,10,7,3,80:X)),           
         IFTHEN=(WHEN=NONE,                         
                 BUILD=(11,30,80:X))                 
/*                                                   



Gerry
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
PostPosted: Sat Apr 21, 2012 4:19 am
 Reply with quote
I finally connected current questions back to this topic, and reminded myself of the three weeks I spent (on-and-off, about four days) to get this into one JOINKEYS step, by which time it was a bit stale.

Started again from scratch this evening, for practice:

Code:
//STEP0100 EXEC PGM=SORT
//SYSOUT   DD SYSOUT=*
//SORTOUT  DD SYSOUT=*
//SYSIN    DD *
  JOINKEYS F1=INA,FIELDS=(91,5,A)
  JOINKEYS F2=INB,FIELDS=(1,5,A)
  JOIN UNPAIRED,F1
  REFORMAT FIELDS=(F1:1,100,?,F2:7,3)
  SORT FIELDS=(81,10,CH,A)
  OUTREC IFTHEN=(WHEN=GROUP,KEYBEGIN=(81,5),
                 PUSH=(91:1,80,102,3,96,5)),
         IFTHEN=(WHEN=(1,5,CH,EQ,C'NAME:'),
                 OVERLAY=(11:171,3))
  OUTFIL OMIT=(86,5,CH,EQ,C'00000'),
         IFOUTLEN=(80),
         IFTHEN=(WHEN=(86,5,CH,EQ,174,5,CH),
                BUILD=(1,80,/,
                       91,80))
//JNF1CNTL DD *
  INREC IFTHEN=(WHEN=GROUP,
                 BEGIN=(1,5,CH,EQ,C'S.NO:'),
                 PUSH=(81:ID=5,SEQ=5)),
        IFTHEN=(WHEN=(1,7,CH,EQ,C'ACCNUM:'),
                 OVERLAY=(91:9,5,86,5,ZD,SUB,+1,EDIT=(TTTTT),
                         86:C'00000'))
                                                             
//INA      DD *
S.NO:1
NAME: ABC
AGE:20
SEX: MALE
ACCNUM: 12345
ENDMSG
S.NO:2
NAME: DEF
AGE:21
SEX: MALE
ADDR: <UNKNOWN>
ACCNUM: 67890
CITY: XX
STATE: YY
ENDMSG
S.NO:3
NAME: ZDE
AGE:25
SEX: MALE
ACCNUM: 99945
COMPANY: III
CODE:87944555
ENDMSG
//INB      DD *
12345 QWE
67890 POI
56862 QWE


Gives:
Code:

S.NO:1         
NAME: ABC QWE 
AGE:20         
SEX: MALE     
ACCNUM: 12345 
ENDMSG         
S.NO:2         
NAME: DEF POI 
AGE:21         
SEX: MALE     
ADDR: <UNKNOWN>
ACCNUM: 67890 
CITY: XX       
STATE: YY     
ENDMSG         
S.NO:3         
NAME: ZDE     
AGE:25         
SEX: MALE     
ACCNUM: 99945 
COMPANY: III   
CODE:87944555 
ENDMSG   
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
PostPosted: Sat Apr 21, 2012 6:04 am
 Reply with quote
Ah, I forgot the other reason for not posting it.

Sab, don't get too excited. You are using Syncsort, so up to you to find out if you have the JNFnCNTL files.
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 -> JCL & VSAM

 

Similar Topics
Topic Forum Replies
No new posts Compare 2 files and retrive records f... DFSORT/ICETOOL 3
No new posts FTP VB File from Mainframe retaining ... JCL & VSAM 8
No new posts Extract the file name from another fi... DFSORT/ICETOOL 6
No new posts How to split large record length file... DFSORT/ICETOOL 10
No new posts Extracting Variable decimal numbers f... DFSORT/ICETOOL 17
Search our Forums:

Back to Top
IBMMainframes.com is not an official and/or affiliated with IBM® in anyway
Board Rules | FAQ | Downloads | Wiki | SiteMap | Contact Us