MVSFORUMS.com A Community of and for MVS Professionals
View previous topic :: View next topic
Author
Message
rama krishna reddy Beginner Joined: 18 Sep 2006 Posts: 31 Topics: 13 Location: Hyderabad
Posted: Wed Jul 23, 2008 7:51 am Post subject: Facing problem while matching 3 files using synctool
Hi ,
I have 3 VB files say file1,file2 and file3 with length as 554
As for my requirement i need to extract file1 records which are not in both file2 and file3 (i mean file1 uncommon records not existing in both file2 and file3) .
I have coded syncsort jcl but iam facing problem.
Code:
//STEP0100 EXEC PGM=SYNCTOOL
//*
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN1 DD DSN=TEST.CLMFACTS.RSS.INDPPO.IBCD.INPFLE.ACPHY,DISP=SHR
//*
//IN2 DD DSN=TEST.CLMFACTS.RSS.INDPPO.IBCD.INPFLE.ACADD,DISP=SHR
//*
//IN3 DD DSN=TEST.CLMFACTS.RSS.INDPPO.IBCD.INPFLE.ACSPC,DISP=SHR
//*
//T1 DD DSN=&T1,SPACE=(CYL,(5,5),RLSE),DISP=(,PASS)
//T2 DD DSN=&T2,SPACE=(CYL,(5,5),RLSE),DISP=(,PASS)
//T3 DD DSN=&T3,SPACE=(CYL,(5,5),RLSE),DISP=(,PASS)
//INT DD DSN=*.T1,DISP=(OLD,PASS),VOL=REF=*.T1
// DD DSN=*.T2,DISP=(OLD,PASS),VOL=REF=*.T2
// DD DSN=*.T3,DISP=(OLD,PASS),VOL=REF=*.T3
//FILEA DD SYSOUT=*
//FILEB DD SYSOUT=*
//FILEC DD SYSOUT=*
//OUT DD SYSOUT=*
//TOOLIN DD *
SORT FROM(IN1) USING(CTL1)
SORT FROM(IN2) USING(CTL2)
SORT FROM(IN3) USING(CTL3)
SORT FROM(INT) USING(CTL4)
//CTL1CNTL DD *
INREC FIELDS=(1:1,4,5:5,12,17:5X,22:17,1,18,536,C'FILE1',C'1')
SORT FIELDS=(5,12,CH,A,22,1,CH,A)
OUTFIL FNAMES=T1
//CTL2CNTL DD *
SORT FIELDS=(5,12,CH,A,22,1,CH,A), OUTFIL FNAMES=T2
OUTREC=(1,554,C'FILE2',C'2')
//CTL3CNTL DD *
SORT FIELDS=(5,12,CH,A,22,1,CH,A), OUTFIL FNAMES=T3
OUTREC=(1,554,C'FILE3',C'3')
//CTL4CNTL DD *
SORT FIELDS=(1,10,CH,A)
SUM FIELDS=(560,1,ZD)
OUTFIL FNAMES=OUT,INCLUDE=(555,5,CH,EQ,C'FILE1',AND,560,1,CH,EQ,C'3')
OUTREC=(1,554)
/*
-----------------------------------------iam getting out put as --------
Code:
SYNCSORT FOR Z/OS 1.3.0.1NI U.S. PATENTS: 4210961, 5117495 (C) 2007 SYNCSO
GLICOA z/OS 1.7.1
SYNCSORT LICENSED FOR CPU SERIAL NUMBER 1200D, MODEL 2094 605 LICEN
CTL1CNTL :
INREC FIELDS=(1:1,4,5:5,12,17:5X,22:17,1,18,536,C'FILE1',C'1') 0030100
SORT FIELDS=(5,12,CH,A,22,1,CH,A) 0031000
OUTFIL FNAMES=T1 0031010
PARMLIST :
OPTION RESINV=0,ARESINV=0,MSGDDN=DFSMSG,SORTIN=IN1,SORTDD=CTL1,DYNALLOC
WER428I CALLER-PROVIDED IDENTIFIER IS "0001"
WER276B SYSDIAG= 1588812, 5297380, 5297380, 1702467
WER164B 24,604K BYTES OF VIRTUAL STORAGE AVAILABLE, MAX REQUESTED,
WER164B 0 BYTES RESERVE REQUESTED, 24,556K BYTES USED
WER146B 20K BYTES OF EMERGENCY SPACE ALLOCATED
WER108I IN1 : RECFM=VB ; LRECL= 554; BLKSIZE= 27998
WER257I INREC RECORD LENGTH = 564
WER238I POTENTIALLY INEFFICIENT USE OF INREC
WER110I T1 : RECFM=VB ; LRECL= 564; BLKSIZE= 27998
WER410B 23,576K BYTES OF VIRTUAL STORAGE AVAILABLE ABOVE THE 16MEG LINE,
WER410B 0 BYTES RESERVE REQUESTED, 23,564K BYTES USED
WER036B G=1398,SEGLEN=4280
WER244A INREC - SHORT RECORD
WER211B SYNCSMF CALLED BY SYNCSORT; RC=0000
-----------------------------------
Ii tried with both synctool and icetool same error iam getting
Please help me regarding the same .
Thanks
Rama krisha
Back to top
dbzTHEdinosauer Supermod Joined: 20 Oct 2006 Posts: 1411 Topics: 26 Location: germany
Posted: Wed Jul 23, 2008 8:13 am Post subject:
as I am now almost blind since you did not bother to use BBcode to post this, I can only assume you are also blind.
try starting with: WER257I INREC RECORD LENGTH = 564
someone else will have to help you with the parms. I only know enough that if you say your INPUT length is 554, and your INREC parms describes 564, I assume there is a problem with your INREC statement......as the error msg indicates:
WER238I POTENTIALLY INEFFICIENT USE OF INREC
you need to start reading the messages provided by the utility. _________________ Dick Brenholtz
American living in Varel, Germany
Back to top
rama krishna reddy Beginner Joined: 18 Sep 2006 Posts: 31 Topics: 13 Location: Hyderabad
Posted: Wed Jul 23, 2008 9:18 am Post subject: Facing problem while matching 3 files using synctool
Hi ,
it seems length of input file records data is less than 554
for some records, so iam facing this problem.
Please let me know what i need to do in this case.
i mean for vb files all the records won't be having full length data .
Thanks
Rama krishna reddy
Back to top
dbzTHEdinosauer Supermod Joined: 20 Oct 2006 Posts: 1411 Topics: 26 Location: germany
Posted: Wed Jul 23, 2008 9:37 am Post subject:
this is syncsort. try search on all of alisa m's posts. _________________ Dick Brenholtz
American living in Varel, Germany
Back to top
amargulies Beginner Joined: 10 Jan 2007 Posts: 123 Topics: 0
Posted: Wed Jul 23, 2008 3:48 pm Post subject:
Instead of passing the data 4 times in your SYNCTOOL application, have you considered a 2-step JOIN?
Here is an example:
Code: //STEP1 EXEC PGM=SORT
//SORTJNF1 DD DSN=INPUT.FILE1
//SORTJNF2 DD DSN=INPUT.FILE2
//SORTOUT DD DSN=&&OUT,DISP=(NEW,PASS)
//SYSOUT DD SYSOUT=*
//SYSIN DD *
JOINKEYS FILE=F1,FIELDS=(...
JOINKEYS FILE=F2,FIELDS=(...
JOIN UNPAIRED,F1,ONLY
REFORMAT FIELDS=(F1:1,4,5)
SORT FIELDS=COPY
//**********************************
//STEP2 EXEC PGM=SORT
//SORTJNF1 DD DSN=&&OUT
//SORTJNF2 DD DSN=INPUT.FILE3
//SORTOUT DD DSN=FINAL.OUTPUT
//SYSOUT DD SYSOUT=*
//SYSIN DD *
JOINKEYS FILE=F1,FIELDS=(...
JOINKEYS FILE=F2,FIELDS=(...
JOIN UNPAIRED,F1,ONLY
REFORMAT FIELDS=(F1:1,4,5)
SORT FIELDS=COPY
/*
_________________ Alissa Margulies
SyncSort Mainframe Product Services
201-930-8260
zos_tech@syncsort.com
Back to top
rama krishna reddy Beginner Joined: 18 Sep 2006 Posts: 31 Topics: 13 Location: Hyderabad
Posted: Thu Jul 31, 2008 1:56 am Post subject: matching 3 files using synctool
Hello Alissa Margulies
I have done using joinkeys it is working fine ,but as for my requirement
i need to match 3 files file-1,file-2,file-3
1)need to extract file-1 records which are there in both file2 and file-3
into file-4 and remaining records into file-5
2)need to extract file-2 records which are there in both file1 and file-3
into file-6 and remaining records into file-7
3)2)need to extract file-3 records which are there in both file1 and file-2
into file-8 and remaining records into file-9
So from 3 files i need to extarct 6 files.
if i use join keys i need to use toally 6 sort cards as shown below is there any way to do with less number of sort cards
Code:
//*******************************************************************
//*JOIN FILE#1 PHYSICIAN NAME AND DEGREE AND FILE#2 PHYSICIAN ADDRESS
//********************************************************************
//STEP1 EXEC PGM=SORT
//SORTJNF1 DD DSN=TEST.CLMFACTS.RSS.INDPPO.IBCD.INPFLE.ACPHY,DISP=SHR
//SORTJNF2 DD DSN=TEST.CLMFACTS.RSS.INDPPO.IBCD.INPFLE.ACADD,DISP=SHR
//SORTOF01 DD SYSOUT=*
//SORTOUT DD DSN=TEST.CLMFACTS.RK.PHYADD1.NEW,DISP=(,CATLG),
// DATACLAS=SMSALLOC,
// DCB=(LRECL=333,RECFM=FB,BLKSIZE=0)
//SYSOUT DD SYSOUT=*
//SYSIN DD *
JOINKEYS FILES=F1,FIELDS=(5,12,A,17,1,A)
JOINKEYS FILES=F2,FIELDS=(5,12,A,22,1,A)
REFORMAT FIELDS=(F1:5,139,F2:17,194)
SORT FIELDS=COPY
/*
//************************************************
//**** JOIN FILE#1 ,FILE#2 AND FILE#3
//************************************************
//STEP2 EXEC PGM=SORT
//SORTJNF1 DD DSN=TEST.CLMFACTS.RK.PHYADD1,DISP=SHR
//SORTJNF2 DD DSN=TEST.CLMFACTS.RSS.INDPPO.IBCD.INPFLE.ACSPC,DISP=SHR
//SORTOF01 DD SYSOUT=*
//SORTOUT DD DSN=TEST.CLMFACTS.RK.PHYADD2.NEW1,DISP=(,CATLG),
// DATACLAS=SMSALLOC,
// DCB=(LRECL=388,RECFM=FB,BLKSIZE=0)
//SYSOUT DD SYSOUT=*
//SYSIN DD *
JOINKEYS FILES=F1,FIELDS=(1,12,A,13,1,A)
JOINKEYS FILES=F2,FIELDS=(5,12,A,22,1,A)
REFORMAT FIELDS=(F1:1,333,F2:17,55)
SORT FIELDS=COPY
/*
****************************************************************
**** EXTRACT ONLY ADD RECORDS
*****************************************************************
//STEP3 EXEC PGM=SORT
//SORTIN DD DSN=TEST.CLMFACTS.RK.PHYADD2.NEW1,DISP=SHR
//SORTOUT DD DSN=TEST.CLMFACTS.RK.PHYADD2.NEW,DISP=(,CATLG),
// DATACLAS=SMSALLOC,
// DCB=(LRECL=388,RECFM=FB,BLKSIZE=0)
//SYSOUT DD SYSOUT=*
//SYSIN DD *
SORT FIELDS=COPY
INCLUDE COND=(13,1,CH,EQ,C'A')
/*
-----------------------------------------------------------------------
//********************************************************************
//*FILE#1 RECORDS WHICH ARE NOT AVAILABLE IN FILE#2 AND FILE#3
//* IT EXTRACTS UNCOMMON RECORDS OF FILE#1
//*********************************************************************
//STEP01 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTJNF1 DD DSN=TEST.CLMFACTS.RSS.INDPPO.IBCD.INPFLE.ACPHY,
// DISP=SHR
//SORTJNF2 DD DSN=TEST.CLMFACTS.RK.PHYADD2.NEW,
// DISP=SHR
//SORTOUT DD DSN=TEST.CLMFACTS.RK.PHYNAMED,
// DISP=(NEW,CATLG,DELETE),
// UNIT=SYSDA,
// SPACE=(CYL,(1,1),RLSE)
//SYSIN DD *
JOINKEYS FILES=F1,FIELDS=(5,12,A,17,1,A)
JOINKEYS FILES=F2,FIELDS=(1,12,A,13,1,A)
SORT FIELDS=COPY
JOIN UNPAIRED,F1,ONLY
/*
//********************************************************************
//*FILE#2 RECORDS WHICH ARE NOT AVAILABLE IN FILE#1 AND FILE#2
//* IT EXTRACTS UNCOMMON RECORDS OF FILE#2
//*********************************************************************
//STEP02 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTJNF1 DD DSN=TEST.CLMFACTS.RSS.INDPPO.IBCD.INPFLE.ACADD,
// DISP=SHR
//SORTJNF2 DD DSN=TEST.CLMFACTS.RK.PHYADD2.NEW,
// DISP=SHR
//SORTOUT DD DSN=TEST.CLMFACTS.RK.PHYADDPH,
// DISP=(NEW,CATLG,DELETE),
// UNIT=SYSDA,
// SPACE=(CYL,(1,1),RLSE)
//SYSIN DD *
JOINKEYS FILES=F1,FIELDS=(5,12,A,22,1,A)
JOINKEYS FILES=F2,FIELDS=(1,12,A,13,1,A)
SORT FIELDS=COPY
JOIN UNPAIRED,F1,ONLY
/*
//********************************************************************
//*FILE#3 RECORDS WHICH ARE NOT AVAILABLE IN FILE#1 AND FILE#2
//* IT EXTRACTS UNCOMMON RECORDS OF FILE#3
//*********************************************************************
//STEP03 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTJNF1 DD DSN=TEST.CLMFACTS.RSS.INDPPO.IBCD.INPFLE.ACSPC,
// DISP=SHR
//SORTJNF2 DD DSN=TEST.CLMFACTS.RK.PHYADD2.NEW,
// DISP=SHR
//SORTOUT DD DSN=TEST.CLMFACTS.RK.PHYACS,
// DISP=(NEW,CATLG,DELETE),
// UNIT=SYSDA,
// SPACE=(CYL,(1,1),RLSE)
//SYSIN DD *
JOINKEYS FILES=F1,FIELDS=(5,12,A,22,1,A)
JOINKEYS FILES=F2,FIELDS=(1,12,A,13,1,A)
SORT FIELDS=COPY
JOIN UNPAIRED,F1,ONLY
/*
//********************************************************************
//*FILE#1 RECORDS WHICH ARE AVAILABLE IN BOTH FILE#2 AND FILE#3
//* IT EXTRACTS COMMON RECORDS OF FILE#1
//*********************************************************************
//STEP01 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTJNF1 DD DSN=TEST.CLMFACTS.RSS.INDPPO.IBCD.INPFLE.ACPHY,
// DISP=SHR
//SORTJNF2 DD DSN=TEST.CLMFACTS.RK.PHYNAMED,
// DISP=SHR
//SORTOUT DD DSN=TEST.CLMFACTS.RK.COMMON.PHYNAMED,
// DISP=(NEW,CATLG,DELETE),
// UNIT=SYSDA,
// SPACE=(CYL,(1,1),RLSE)
//SYSIN DD *
JOINKEYS FILES=F1,FIELDS=(5,12,A,17,1,A)
JOINKEYS FILES=F2,FIELDS=(5,12,A,17,1,A)
SORT FIELDS=COPY
JOIN UNPAIRED,F1,ONLY
/*
//********************************************************************
//*FILE#2 RECORDS WHICH ARE AVAILABLE IN BOTH FILE#1 AND FILE#2
//* IT EXTRACTS COMMON RECORDS OF FILE#2
//*********************************************************************
//STEP02 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTJNF1 DD DSN=TEST.CLMFACTS.RSS.INDPPO.IBCD.INPFLE.ACADD,
// DISP=SHR
//SORTJNF2 DD DSN=TEST.CLMFACTS.RK.PHYADDPH,
// DISP=SHR
//SORTOUT DD DSN=TEST.CLMFACTS.RK.COMMON.PHYADDPH,
// DISP=(NEW,CATLG,DELETE),
// UNIT=SYSDA,
// SPACE=(CYL,(1,1),RLSE)
//SYSIN DD *
JOINKEYS FILES=F1,FIELDS=(5,12,A,22,1,A)
JOINKEYS FILES=F2,FIELDS=(5,12,A,22,1,A)
SORT FIELDS=COPY
JOIN UNPAIRED,F1,ONLY
/*
//********************************************************************
//*FILE#3 RECORDS WHICH ARE AVAILABLE IN BOTH FILE#1 AND FILE#2
//* IT EXTRACTS COMMON RECORDS OF FILE#3
//*********************************************************************
//STEP03 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTJNF1 DD DSN=TEST.CLMFACTS.RSS.INDPPO.IBCD.INPFLE.ACSPC,
// DISP=SHR
//SORTJNF2 DD DSN=TEST.CLMFACTS.RK.PHYACS,
// DISP=SHR
//SORTOUT DD DSN=TEST.CLMFACTS.RK.COMMON.PHYACS,
// DISP=(NEW,CATLG,DELETE),
// UNIT=SYSDA,
// SPACE=(CYL,(1,1),RLSE)
//SYSIN DD *
JOINKEYS FILES=F1,FIELDS=(5,12,A,22,1,A)
JOINKEYS FILES=F2,FIELDS=(5,12,A,22,1,A)
SORT FIELDS=COPY
JOIN UNPAIRED,F1,ONLY
/*
-------------------------------------------------------------
I need to use many sort cards if i go for joinkeys thats why iam trying with synctool
Please let me know if there is any better way to do this.
Thanks
Rama krishna reddy
Back to top
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum