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

Syncsort - Help for joining two files with a key field


IBM Mainframe Forums -> JCL & VSAM
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
bnveena

New User


Joined: 29 May 2007
Posts: 50
Location: hyderabad

PostPosted: Wed Nov 18, 2009 11:46 am
Reply with quote

Hi ,

I have two files
File1 having 80 LRECL
File2 having 818 Lrecl

In file1 at position 11 with length 6 i have to match with the file2 at position 38 with length 6 and join the records in the file1 at position 43 length 35 and over lay in the in file3.

please can any one help in this regard
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Wed Nov 18, 2009 8:43 pm
Reply with quote

Hello,

Please post sample input data from both files and the output you want fro that sample input. Also, post the recfm and lrecl of the files.
Back to top
View user's profile Send private message
bnveena

New User


Joined: 29 May 2007
Posts: 50
Location: hyderabad

PostPosted: Thu Nov 19, 2009 8:38 am
Reply with quote

Hi ,

In file 1 i have data like Lrecl 80 Key field is at position 11 and length is 6
and the group name is at position 43 and length is 35
Code:
000001   India
000002   US
000003   Melbourne
000004   Denmark


In file2 the data is like Lrecl 818 Key field is at position 38 and length is 6

Code:
India         000001   53  63   45
US            000002   63  41   86
Melbourne     000003   78  87   98
Denmark       000004   69  58   47
pairs         000005   21  32   21
new york      000007   11  10   11

i want to join these two files. I want the records in file 1 as it is the details in the file 2 should come in my output file. the output should come like this

Code:
India         000001   53  63   45
US            000002   63  41   86
Melbourne     000003   78  87   98
Denmark       000004   69  58   47


Please help me in this regard... i tried with sync sort but i m getting sort exceeded and while joining using ice tool i m getting the records in the file 2 which are not in file 1
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Thu Nov 19, 2009 9:49 am
Reply with quote

Hello,

If you post the jcl, control statements, and complete diagnostic information from the "exceeded" run, someone may be able to help.

Please note that this topic has been moved to the JCL part of the forum as this is the place for Syncsort topics.
Back to top
View user's profile Send private message
bnveena

New User


Joined: 29 May 2007
Posts: 50
Location: hyderabad

PostPosted: Thu Nov 19, 2009 9:59 am
Reply with quote

Hi ,

Please find the sysout while i was doing with syncsort

SYSIN :
JOINKEYS FILE=F1,FIELDS=(11,6,A)
JOINKEYS FILE=F2,FIELDS=(38,6,A)
JOIN UNPAIRED,F1
REFORMAT FIELDS=(F1:43,35,F2:1,818)
SORT FIELDS=COPY
WER164B 7,000K BYTES OF VIRTUAL STORAGE AVAILABLE, MAX REQUESTED,
WER164B 0 BYTES RESERVE REQUESTED, 1,030,096 BYTES USED
WER146B 12K BYTES OF EMERGENCY SPACE ALLOCATED
WER481I JOINKEYS REFORMAT RECORD LENGTH= 853, TYPE = F
WER110I SORTOUT : RECFM=FB ; LRECL= 853; BLKSIZE= 27296
WER211B SYNCSMF CALLED BY SYNCSORT; RC=0000
WER486A ERROR IN JNF2 PROCESSING
WER482I JNF1 STATISTICS
WER483B 2,556K BYTES OF VIRTUAL STORAGE AVAILABLE, MAX REQUESTED,
WER483B 0 BYTES RESERVE REQUESTED, 1,780K BYTES USED
WER108I SORTJNF1 : RECFM=FB ; LRECL= 80; BLKSIZE= 27920
WER483B 1,524K BYTES OF VIRTUAL STORAGE AVAILABLE ABOVE THE 16MEG LINE,
WER483B 0 BYTES RESERVE REQUESTED, 1,524K BYTES USED
WER483B G=372
WER416B SORTJNF1 : EXCP'S=1,UNIT=3390,DEV=2C13,CHP=(E4E5E6E7E8E9EAEB,1),VOL=S9
WER416B TOTAL OF 1 EXCP'S ISSUED FOR SORTING
WER487I FILESIZE 12,720 BYTES
WER482I JNF2 STATISTICS
WER483B 3,816K BYTES OF VIRTUAL STORAGE AVAILABLE, MAX REQUESTED,
WER483B 0 BYTES RESERVE REQUESTED, 3,040K BYTES USED
WER108I SORTJNF2 : RECFM=FB ; LRECL= 818; BLKSIZE= 27812
WER483B 2,784K BYTES OF VIRTUAL STORAGE AVAILABLE ABOVE THE 16MEG LINE,
WER483B 0 BYTES RESERVE REQUESTED, 2,784K BYTES USED
WER483B G=912,B=69
WER483B 0 PREALLOCATED SORTWORK TRACKS, 0 DYNAMICALLY ALLOCATED,
WER483B 0 ACQUIRED IN SECONDARY EXTENTS, 0 RELEASED, TOTAL OF 0 TRACKS USED
WER046A SORT CAPACITY EXCEEDED
WER066A APROX RCD CNT 69
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Thu Nov 19, 2009 10:13 am
Reply with quote

Hello,

Please also post the jcl that was submitted. How many records are there in each file?

The wer046a message says the process used all available SORTWK space.
Back to top
View user's profile Send private message
bnveena

New User


Joined: 29 May 2007
Posts: 50
Location: hyderabad

PostPosted: Thu Nov 19, 2009 10:16 am
Reply with quote

Hi ,

Please find the JCL i ve submitted

//STEP010 EXEC PGM=SYNCSORT
//***************************************************************
//SYSOUT DD SYSOUT=*
//SORTWK01 DD UNIT=WORK,SPACE=(CYL,(10,10))
//SORTWK02 DD UNIT=WORK,SPACE=(CYL,(10,10))
//SORTWK03 DD UNIT=WORK,SPACE=(CYL,(10,10))
//SORTWK04 DD UNIT=WORK,SPACE=(CYL,(10,10))
//SORTWK05 DD UNIT=WORK,SPACE=(CYL,(10,10))
//SORTJNF1 DD DSN=ATID.DSXK.DW2002.HOLDA,DISP=SHR
//SORTJNF2 DD DSN=ATID.DVNB.ITSCLMH.SRT2,DISP=SHR
//SORTOUT DD DSN=ATID.DVNB.DW2002.JOIN0,
// DISP=(NEW,CATLG,DELETE),DCB=(MODEL.DSCB),
// UNIT=SYSDA,SPACE=(CYL,(10,10),RLSE)
//SYSIN DD *
JOINKEYS FILE=F1,FIELDS=(11,6,A)
JOINKEYS FILE=F2,FIELDS=(38,6,A)
JOIN UNPAIRED,F1
REFORMAT FIELDS=(F1:43,35,F2:1,818)
SORT FIELDS=(11,6,CH,A)
/*

in first file there are 159 records

and in second file there are 4035 records
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Thu Nov 19, 2009 10:48 am
Reply with quote

Hello,

Is WORK the proper unit for sortwork on this system?

Is there enough space available? You might try an experiment with a primary of 25 and no secondary specified in the jcl. You might also try sysda instead of work - just as an experiment.
Back to top
View user's profile Send private message
bnveena

New User


Joined: 29 May 2007
Posts: 50
Location: hyderabad

PostPosted: Thu Nov 19, 2009 10:53 am
Reply with quote

With all possible parm parameters using MAX sort i tried but of no use it is giving the same error and i tried using as many work space with maximum cylinders.

I think it is not working with syncsort. With icetool it is joining but i m getting more records in out file.

Please find the icetool jcl and can u please tell me whether i m going correct or not

//STEP040 EXEC PGM=ICETOOL

//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//JNA DD DSN=ATID.DSXK.DW2002.HOLDA,DISP=SHR
//JNB DD DSN=ATID.DVNB.ITSCLMH.SRT2,DISP=SHR
//T1 DD DSN=&&T1,
// UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE),
// DISP=(MOD,PASS)
//OUT DD DSN=ATID.DVNB.DW2002.JOIN0,
// DISP=(NEW,CATLG,DELETE),DCB=(MODEL.DSCB),
// UNIT=SYSDA,SPACE=(CYL,(10,10),RLSE)
//TOOLIN DD *
COPY FROM(JNA) TO(T1) USING(CTL1)
COPY FROM(JNB) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(OUT) ON(1,6,CH) WITH(42,818)
//CTL1CNTL DD *
OUTREC BUILD=(1:11,6,7:43,35,42:818X)
/*
//CTL2CNTL DD *
OUTREC BUILD=(1:38,6,7:35X,42:1,818)
/*
Back to top
View user's profile Send private message
darkstar13

New User


Joined: 06 Nov 2008
Posts: 46
Location: Manila, Philippines

PostPosted: Thu Nov 19, 2009 12:00 pm
Reply with quote

Hi bnveena,

I tried it with Syncsort, using your examples:
Code:
//STEP060  EXEC PGM=SORT,COND=(0,NE),REGION=4096K       
//SYSOUT   DD   SYSOUT=*                               
//SYSUDUMP DD   SYSOUT=Y                               
//SORTJNF1 DD DSN=TST.INFILE1,                         
//         DISP=SHR                                     
//SORTJNF2 DD DSN=TST.INFILE2,                         
//         DISP=SHR                                     
//SORTOUT  DD DSN=TST.OUTFILE,                         
//         UNIT=SYSDA,                                 
//         DISP=(,CATLG,DELETE),                       
//         SPACE=(CYL,(20,10),RLSE)                     
//SYSIN    DD    *                                     
  JOINKEYS FILES=F1,FIELDS=(11,6,A)                     
  JOINKEYS FILES=F2,FIELDS=(38,6,A)                     
  REFORMAT FIELDS=(F1:43,35,F2:1,818),FILL=X'FF'       
  JOIN UNPAIRED,F1                                     
  SORT FIELDS=(11,6,CH,A)                               


With file 1

Code:
DIT TST.INFILE1                                     Columns 00001 00072
 ===>                                                  Scroll ===> CSR 
----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
***************************** Top of Data ******************************
          000001                          INDIA_________________________
          000002                          US____________________________
          000003                          MELBOURNE_____________________
          000004                          DENMARK_______________________


and File 2:
Code:
DIT TST.INFILE2                                     Columns 00001 00072
 ===>                                                  Scroll ===> CSR 
----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
***************************** Top of Data ******************************
XXXXXXXXXXXXXXXXXXXXXXXIndia         000001   53  63   45               
XXXXXXXXXXXXXXXXXXXXXXXUS            000002   63  41   86               
XXXXXXXXXXXXXXXXXXXXXXXMelbourne     000003   78  87   98               
XXXXXXXXXXXXXXXXXXXXXXXDenmark       000004   69  58   47               
XXXXXXXXXXXXXXXXXXXXXXXpairs         000005   21  32   21               
XXXXXXXXXXXXXXXXXXXXXXXnew york      000007   11  10   11               


and got

Code:

DIT TST.OUTFILE                                     Columns 00001 000
 ===>                                                  Scroll ===> CS
----+----1----+----2----+----3----+----4----+----5----+----6----+----
***************************** Top of Data ***************************
-Warning- The UNDO command is not available until you change         
          your edit profile using the command RECOVERY ON.           
INDIA__________________________    XXXXXXXXXXXXXXXXXXXXXXXIndia     
US_____________________________    XXXXXXXXXXXXXXXXXXXXXXXUS         
MELBOURNE______________________    XXXXXXXXXXXXXXXXXXXXXXXMelbourne 
DENMARK________________________    XXXXXXXXXXXXXXXXXXXXXXXDenmark   


Code:

DIT TST.OUTFILE                                     Columns 00070 00141
 ===>                                                  Scroll ===> CSR
7----+----8----+----9----+----0----+----1----+----2----+----3----+----4
***************************** Top of Data *****************************
-Warning- The UNDO command is not available until you change           
          your edit profile using the command RECOVERY ON.             
   000001   53  63   45                                               
   000002   63  41   86                                               
   000003   78  87   98                                               
   000004   69  58   47                                               


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

New User


Joined: 29 May 2007
Posts: 50
Location: hyderabad

PostPosted: Thu Nov 19, 2009 12:08 pm
Reply with quote

Yes i want like that only... but for me in the same case i m getting abend 16 and giving sort capacity exceeded.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Thu Nov 19, 2009 8:22 pm
Reply with quote

Hello,

You need to resolve the issue with your support people. The problem is specific to your environment and we do not have access to that environment.

Quote:
With all possible parm parameters using MAX sort i tried
Why is MAX sort mentioned?

Did you use sysda instead of work for the sortwk unit? You might also try removing all of the sortwk dd's as another experiment.
Back to top
View user's profile Send private message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8797
Location: Welsh Wales

PostPosted: Thu Nov 19, 2009 8:28 pm
Reply with quote

Dick, the esoteric for the DASD is something that can vary from site to site. I have worked in quite a few where the temporary dataset pool was accessed via UNIT=WORK
Back to top
View user's profile Send private message
Anuj Dhawan

Superior Member


Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India

PostPosted: Thu Nov 19, 2009 11:29 pm
Reply with quote

And here they call it 3390... a lot more to learn in this shop...
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Fri Nov 20, 2009 1:28 am
Reply with quote

Hi Expat,

Yup, i've seen work, temp, scrat, and a few others, but sometimes they are extremely low on space<g>. Stuff that should be transient (or not exist at all) winds up cluttering the volumes. . . I do remember one place where a programmer asked me why he couldn't create a particular dataset. Seems like they'd gotten "not cat 2" many, many times and had an allocation on every volume in the group. Only one in the catalog, but a vtoc entry on every available volume. . .

I thought that sysda might show something. . .

I'm also still confused by the "MAX sort" reference.

We'll see icon_wink.gif

d
Back to top
View user's profile Send private message
gcicchet

Senior Member


Joined: 28 Jul 2006
Posts: 1702
Location: Australia

PostPosted: Fri Nov 20, 2009 3:23 am
Reply with quote

Hi,

see link ibmmainframes.com/viewtopic.php?t=39616&highlight


Gerry
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 Write line by line from two files DFSORT/ICETOOL 7
No new posts Compare only first records of the fil... SYNCSORT 7
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 Joinkeys - 5 output files DFSORT/ICETOOL 7
Search our Forums:

Back to Top