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

Merge two records without a common field


IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
thoulath

New User


Joined: 15 Sep 2010
Posts: 18
Location: chennai

PostPosted: Wed Sep 15, 2010 10:58 am
Reply with quote

Hi
This is my case. there are two input files
FILE1:
------------
lno offset
1 20
5 5
5 4
1 200
3 10
FILE2:
------------
name
acct-no
ph-no
code
address
city
1.In FILE1 field1(LNO) length is 12 and field2(OFFSET) starts from position 13.
2.In FILE2 NAME field starts from position 13. Upto pos 12 it is space.

Expected OUTPUT:
lno name offset
1 acct-no 20
5 ph-no 5
5 code 4
1 address 200
3 city 10

In the output Field1 and Field3 are from FILE1 and Field2 is from FILE2.

Please someone give a solution...
[/quote]
Back to top
View user's profile Send private message
smijoss

Active User


Joined: 30 Aug 2007
Posts: 114
Location: pune

PostPosted: Wed Sep 15, 2010 11:03 am
Reply with quote

wud u please paste the exact data using the code tag
Back to top
View user's profile Send private message
thoulath

New User


Joined: 15 Sep 2010
Posts: 18
Location: chennai

PostPosted: Wed Sep 15, 2010 11:15 am
Reply with quote

FILE1:
Code:

----+----1----+----2----+----3----+----4
----------- ----------------------------
COPYBOOK :                             
LEVELNAME              GROUP      OFFSET
01                     GROUP      0     
05                     -          6     
05                     -          6     
----------- ----------------------------
COPYBOOK :                             
LEVELNAME              GROUP      OFFSET
01                     GROUP      0     
05                     -          6     
05                     -          6     
----------- ---------------------------- 


FILE2:
Code:

----+----1----+----2----+----3----+----4----+
            ------------------------------   
             AE12ATDT                       
            FIELDNAME                       
            AE12AT-PRESENTMENT-RECORD.       
            AE12AT-PRES-ACCT-NBR             
            AE12AT-PRES-ACCT-NBR             
            ------------------------------   
             AE1240DT                       
            FIELDNAME                       
            AE1240-PRESENTMENT-RECORD.       
            AE1240-PRES-ACCT-NBR             
            AE1240-PRES-ACCT-NBR             
            ------------------------------     


In FILE1 group and offset together is one field.
Back to top
View user's profile Send private message
smijoss

Active User


Joined: 30 Aug 2007
Posts: 114
Location: pune

PostPosted: Wed Sep 15, 2010 12:00 pm
Reply with quote

Code:
output :

----------- ------------------------------ ----------------------------
COPYBOOK :   AE12ATDT                                                 
LEVELNAME   FIELDNAME                                 GROUP      OFFSET
01          AE12AT-PRESENTMENT-RECORD.                GROUP      0     
05          AE12AT-PRES-ACCT-NBR                      -          6     
05          AE12AT-PRES-ACCT-NBR                      -          6     
----------- ------------------------------ ----------------------------
COPYBOOK :   AE1240DT                                                 
LEVELNAME   FIELDNAME                                 GROUP      OFFSET
01          AE1240-PRESENTMENT-RECORD.                GROUP      0     
05          AE1240-PRES-ACCT-NBR                      -          6     
05          AE1240-PRES-ACCT-NBR                      -          6     
----------- ------------------------------ ----------------------------



job :
Code:
//S1   EXEC  PGM=SORT                             
//SYSOUT DD SYSOUT=*                               
//SORTJNF1 DD *                                   
----------- ----------------------------           
COPYBOOK :                                         
LEVELNAME              GROUP      OFFSET           
01                     GROUP      0               
05                     -          6               
05                     -          6               
----------- ----------------------------           
COPYBOOK :                                         
LEVELNAME              GROUP      OFFSET           
01                     GROUP      0               
05                     -          6               
05                     -          6               
----------- ----------------------------           
/*                                                 
//SORTJNF2 DD *                                   
            ------------------------------         
             AE12ATDT                             
            FIELDNAME                             
            AE12AT-PRESENTMENT-RECORD.             
            AE12AT-PRES-ACCT-NBR               
            AE12AT-PRES-ACCT-NBR               
            ------------------------------     
             AE1240DT                           
            FIELDNAME                           
            AE1240-PRESENTMENT-RECORD.         
            AE1240-PRES-ACCT-NBR               
            AE1240-PRES-ACCT-NBR               
            ------------------------------     
/*                                             
//SORTOUT DD DSN=TEMP.TLM.TEST.DEL9,           
//        DISP=(NEW,CATLG),UNIT=DISK,           
//        SPACE=(27998,(400,100),RLSE)         
//JNF1CNTL DD *                                 
   INREC OVERLAY=(81:SEQNUM,8,ZD)               
//JNF2CNTL DD *                                 
   INREC OVERLAY=(81:SEQNUM,8,ZD)               
//SYSIN DD *                                   
   JOINKEYS FILES=F1,FIELDS=(81,8,A),SORTED     
   JOINKEYS FILES=F2,FIELDS=(81,8,A),SORTED     
   REFORMAT FIELDS=(F1:1,12,F2:13,31,F1:13,31) 
   OPTION COPY



is this wat you want ?
Back to top
View user's profile Send private message
thoulath

New User


Joined: 15 Sep 2010
Posts: 18
Location: chennai

PostPosted: Wed Sep 15, 2010 12:14 pm
Reply with quote

Thank you.. this is the output which I require..
I tried using the following lines of code:
Code:

JOINKEYS FILE=F1,FIELDS=(1,11,A)           
JOINKEYS FILE=F2,FIELDS=(1,11,A)           
JOIN UNPAIRED F1,F2                         
REFORMAT FIELDS=(F1:1,11,F2:13,30,F1:13,557)
SORT FIELDS=COPY                           


I got SORTJNF1 NOT DEFINED error. what mistake I have made? and can you explain the solution given by you[/code]
Back to top
View user's profile Send private message
smijoss

Active User


Joined: 30 Aug 2007
Posts: 114
Location: pune

PostPosted: Wed Sep 15, 2010 12:16 pm
Reply with quote

paste the complete STEP and the ERROR message
Back to top
View user's profile Send private message
smijoss

Active User


Joined: 30 Aug 2007
Posts: 114
Location: pune

PostPosted: Wed Sep 15, 2010 12:18 pm
Reply with quote

The logic is simple :
and sequence numbers to both files and then join then using the seqnum icon_smile.gif

u can apply the same logic in ICETOOL using using SPLICE WITHANY
Back to top
View user's profile Send private message
thoulath

New User


Joined: 15 Sep 2010
Posts: 18
Location: chennai

PostPosted: Wed Sep 15, 2010 12:35 pm
Reply with quote

icon_smile.gif thanks a lot..
Code:

//MERGE    EXEC PGM=SORT,COND=(0,NE)                       
//SYSPRINT DD SYSOUT=*                                     
//SYSOUT   DD SYSOUT=*                                     
//IN01     DD DSN=BPTN.RTD.S679353.REPORT.TEST1.D,DISP=SHR 
//IN02     DD DSN=BPTN.RTD.S679353.REPORT.TEST2.D,DISP=SHR 
//SORTOUT  DD DSN=BPTN.RTD.S679353.REPORT.TEST3.D,         
//            DISP=(NEW,CATLG,DELETE),                     
//            UNIT=SYSOUT,                                 
//            SPACE=(CYL,(5,10),RLSE),                     
//            DCB=(RECFM=FB,LRECL=600,BLKSIZE=0)           
//SYSIN    DD *                                             
  JOINKEYS FILES=F1,FIELDS=(1,11,A)                         
  JOINKEYS FILES=F2,FIELDS=(1,11,A)                         
  JOIN UNPAIRED F1,F2                                       
  REFORMAT FIELDS=(F1:1,11,F2:13,30,F1:13,557)             
  SORT FIELDS=COPY                                         


error:
Code:

ICE143I 0 BLOCKSET     COPY  TECHNIQUE SELECTED                                 
ICE250I 0 VISIT http://www.ibm.com/storage/dfsort FOR DFSORT PAPERS, EXAMPLES AN
ICE000I 1 - CONTROL STATEMENTS FOR 5694-A01, Z/OS DFSORT V1R10 - 02:42 ON WED SE
            JOINKEYS FILES=F1,FIELDS=(1,11,A)                                   
            JOINKEYS FILES=F2,FIELDS=(1,11,A)                                   
            JOIN UNPAIRED F1,F2                                                 
            REFORMAT FIELDS=(F1:1,11,F2:13,30,F1:13,557)                       
            SORT FIELDS=COPY                                                   
ICE056A 2 SORTJNF1 NOT DEFINED                                                 
ICE751I 0 C5-K51707 C6-K51707 C7-K54603 E7-K51707                               
ICE052I 3 END OF DFSORT                                                         
Back to top
View user's profile Send private message
thoulath

New User


Joined: 15 Sep 2010
Posts: 18
Location: chennai

PostPosted: Wed Sep 15, 2010 12:42 pm
Reply with quote

I fixed it ..I gave my ddnames

JOINKEYS F1=IN01,FIELDS=(1,11,A)
JOINKEYS F2=IN02,FIELDS=(1,11,A)
Back to top
View user's profile Send private message
smijoss

Active User


Joined: 30 Aug 2007
Posts: 114
Location: pune

PostPosted: Wed Sep 15, 2010 12:43 pm
Reply with quote

Quote:
//IN01 DD DSN=BPTN.RTD.S679353.REPORT.TEST1.D,DISP=SHR
//IN02 DD DSN=BPTN.RTD.S679353.REPORT.TEST2.D,DISP=SHR


as the trace suggest , "SORTJNF1 NOT DEFINED "

you have 2 options :
either change ddname IN01 to SORTJNF1 and IN02 to SORTJNF2

or
Code:
//SYSIN    DD *                                             
  JOINKEYS F1=IN01,FIELDS=(1,11,A)                         
  JOINKEYS F2=IN02,FIELDS=(1,11,A)                         
  JOIN UNPAIRED F1,F2                                       
  REFORMAT FIELDS=(F1:1,11,F2:13,30,F1:13,557)             
  SORT FIELDS=COPY   
[/code]
Back to top
View user's profile Send private message
thoulath

New User


Joined: 15 Sep 2010
Posts: 18
Location: chennai

PostPosted: Wed Sep 15, 2010 12:59 pm
Reply with quote

thank you icon_smile.gif
Back to top
View user's profile Send private message
thoulath

New User


Joined: 15 Sep 2010
Posts: 18
Location: chennai

PostPosted: Wed Sep 15, 2010 4:14 pm
Reply with quote

Can we join without generating sequence number?
Back to top
View user's profile Send private message
smijoss

Active User


Joined: 30 Aug 2007
Posts: 114
Location: pune

PostPosted: Wed Sep 15, 2010 4:49 pm
Reply with quote

to merge to records there has to be something comon

how do we join two records if there is nothing in common ?
Unles u write a cobol code and read both files together one records at a time and join them
Back to top
View user's profile Send private message
thoulath

New User


Joined: 15 Sep 2010
Posts: 18
Location: chennai

PostPosted: Wed Sep 15, 2010 4:55 pm
Reply with quote

that is how the requirement is.. need merge all records and fromat it
Back to top
View user's profile Send private message
smijoss

Active User


Joined: 30 Aug 2007
Posts: 114
Location: pune

PostPosted: Wed Sep 15, 2010 5:17 pm
Reply with quote

explain logically how do u merge these records

1st record of file 1 with 1st records of file 2

nth rec of file 1 with nth record of file 2 ... right ...

u have not explained clearly , u sak something and then use exactly tht opposite ...
u have used "JOIN UNPAIRED F1,F2"

where as ur requirement was a simple merge ???
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: Wed Sep 15, 2010 10:39 pm
Reply with quote

Thoulath,

Note also that

Code:

  JOIN UNPAIRED F1,F2


is missing the comma after UNPAIRED, so it is actually interpreted as:

Code:

  JOIN UNPAIRED


rather than:

Code:

  JOIN UNPAIRED,F1,F2
Back to top
View user's profile Send private message
thoulath

New User


Joined: 15 Sep 2010
Posts: 18
Location: chennai

PostPosted: Thu Sep 16, 2010 2:08 pm
Reply with quote

my requirement is to merge two files as mentioned in the beginning. and that merging is to be done without generating any sequence number. like a one to one mapping between two input files.

I dont know much about sorting and icetool in jcl..I just tried using the below code to know whether it will give me the output. while running it I got the "SORTJNF1 NOT DEFINED " error. So asked you the reason. my requirement is not based on the code below. It was a trial given by me.
Code:
//SYSIN DD *
JOINKEYS F1=IN01,FIELDS=(1,11,A)
JOINKEYS F2=IN02,FIELDS=(1,11,A)
JOIN UNPAIRED F1,F2
REFORMAT FIELDS=(F1:1,11,F2:13,30,F1:13,557)
SORT FIELDS=COPY
[/code]
Back to top
View user's profile Send private message
thoulath

New User


Joined: 15 Sep 2010
Posts: 18
Location: chennai

PostPosted: Thu Sep 16, 2010 2:12 pm
Reply with quote

Now there is a change in the requirement. The second file is of length 30.As mentioned before it doesnt have spaces initially. Totally it has only the 30 bytes fieldname.

Now the file2 is like shown below:
Code:

----+----1----+----2----+----3
******************************
------------------------------
 AE12ATDT                     
FIELDNAME                     
AE12AT-PRESENTMENT-RECORD.   
AE12AT-PRES-ACCT-NBR         
AE12AT-PRES-ACCT-NBR         
------------------------------
 AE1240DT                     
FIELDNAME                     
AE1240-PRESENTMENT-RECORD.   
AE1240-PRES-ACCT-NBR         
AE1240-PRES-ACCT-NBR         
------------------------------
Back to top
View user's profile Send private message
smijoss

Active User


Joined: 30 Aug 2007
Posts: 114
Location: pune

PostPosted: Thu Sep 16, 2010 4:24 pm
Reply with quote

Quote:
my requirement is to merge two files as mentioned in the beginning. and that merging is to be done without generating any sequence number. like a one to one mapping between two input files.


adding a sequence number is essential to do one to one mapping , and what difference does it make as it is going to be removed from your output once mapping is done ???



Quote:
It was a trial given by me.

y give trials and test y patience , why not mention the actual requirement in the first place ...........
[/code]
Back to top
View user's profile Send private message
daveporcelan

Active Member


Joined: 01 Dec 2006
Posts: 792
Location: Pennsylvania

PostPosted: Thu Sep 16, 2010 8:03 pm
Reply with quote

thoulath,

smijoss provided 95% percent of what you need in his earlier post

You now only need to do 5% which consists of:

1) Understand that the sequence number is only temporary. It is not part of the output, but provides the mapping needed to satisfy the 'requirement'

2) Learn more about the sort product in your shop, if you plan of using it.

3) Be able to modify the solution provided based on your changing requirement

4) Collect 100% of the money (and glory) for doing 5% of the work
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 577
Location: USA

PostPosted: Thu Sep 16, 2010 8:32 pm
Reply with quote

thoulath,
Use below job AS IS and see if it matches your expected output.
Code:

//STEP01   EXEC  PGM=SORT                           
//SYSOUT   DD SYSOUT=*                               
//SORTJNF1 DD YOUR.FILE1                             
//SORTJNF2 DD YOUR.FILE2                             
//SORTOUT  DD SYSOUT=*                               
//JNF1CNTL DD *                                     
   INREC OVERLAY=(81:SEQNUM,8,ZD)                   
/*                                                   
//JNF2CNTL DD *                                     
   INREC OVERLAY=(81:SEQNUM,8,ZD)                   
/*                                                   
//SYSIN    DD *                                     
   JOINKEYS FILES=F1,FIELDS=(81,8,A),SORTED,NOSEQCK 
   JOINKEYS FILES=F2,FIELDS=(81,8,A),SORTED,NOSEQCK 
   REFORMAT FIELDS=(F1:1,12,F2:01,31,F1:13,31)       
   OPTION COPY                                       
/*                                                   

OUTPUT
Code:
----------- ------------------------------ ----------------------------
COPYBOOK :   AE12ATDT                                                   
LEVELNAME   FIELDNAME                                 GROUP      OFFSET
01          AE12AT-PRESENTMENT-RECORD.                GROUP      0     
05          AE12AT-PRES-ACCT-NBR                      -          6     
05          AE12AT-PRES-ACCT-NBR                      -          6     
----------- ------------------------------ ----------------------------
COPYBOOK :   AE1240DT                                                   
LEVELNAME   FIELDNAME                                 GROUP      OFFSET
01          AE1240-PRESENTMENT-RECORD.                GROUP      0     
05          AE1240-PRES-ACCT-NBR                      -          6     
05          AE1240-PRES-ACCT-NBR                      -          6     
----------- ------------------------------ ----------------------------

Thanks,
Back to top
View user's profile Send private message
thoulath

New User


Joined: 15 Sep 2010
Posts: 18
Location: chennai

PostPosted: Fri Sep 17, 2010 11:07 am
Reply with quote

Thank you for all
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 Compare only first records of the fil... SYNCSORT 7
No new posts Pulling a fixed number of records fro... DB2 2
No new posts Replace Multiple Field values to Othe... DFSORT/ICETOOL 12
No new posts Merge two VSAM KSDS files into third ... JCL & VSAM 6
No new posts Join multiple records using splice DFSORT/ICETOOL 5
Search our Forums:

Back to Top