Syncsort - Help for joining two files with a key field

bnveena · New User Joined: 29 May 2007 Posts: 50 Location: hyderabad

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

dick scherrer · Posted: Wed Nov 18, 2009 8:43 pm

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.

bnveena · New User Joined: 29 May 2007 Posts: 50 Location: hyderabad

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

dick scherrer · Posted: Thu Nov 19, 2009 9:49 am

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.

bnveena · New User Joined: 29 May 2007 Posts: 50 Location: hyderabad

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

dick scherrer · Posted: Thu Nov 19, 2009 10:13 am

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.

bnveena · New User Joined: 29 May 2007 Posts: 50 Location: hyderabad

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

dick scherrer · Posted: Thu Nov 19, 2009 10:48 am

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.

bnveena · New User Joined: 29 May 2007 Posts: 50 Location: hyderabad

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)
/*

darkstar13 · Posted: Thu Nov 19, 2009 12:00 pm

Hi bnveena,

I tried it with Syncsort, using your examples:

bnveena · New User Joined: 29 May 2007 Posts: 50 Location: hyderabad

Yes i want like that only... but for me in the same case i m getting abend 16 and giving sort capacity exceeded.

dick scherrer · Posted: Thu Nov 19, 2009 8:22 pm

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.

expat · Posted: Thu Nov 19, 2009 8:28 pm

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

Anuj Dhawan · Posted: Thu Nov 19, 2009 11:29 pm

And here they call it 3390... a lot more to learn in this shop...

dick scherrer · Posted: Fri Nov 20, 2009 1:28 am

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

d

gcicchet · Posted: Fri Nov 20, 2009 3:23 am

Hi,

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

Gerry