View previous topic :: View next topic
Author
Message
Danielle.Filteau New User Joined: 02 Dec 2014Posts: 16 Location: Canada
I've got 2 input files, F1=LRECL=80, F2,LRECL=200.
Only the rows that match in both should be kept.
The output file needs fields from both inputs, and needs to be reformatted.
The output file should be 200 bytes.
For some reason the output is showing only as 128 bytes. Not sure what's wrong.
Code:
//BLDACTV EXEC PGM=SYNCSORT,COND=(0,LT)
//SORTJNF1 DD DISP=SHR,DSN=DXF125.SXSC.NDVRC1.CCIDVAL
//SORTJNF2 DD DISP=SHR,DSN=DXF125.SXSC.NDVRC1.CCIDPIT.NEXTDAY
//MATCH DD DISP=(NEW,CATLG,DELETE),
// DSN=DXF125.SXSC.NDVRC1.CCIDVAL.ACTSRCH.JOIN.F,
// SPACE=(CYL,(5,5)),UNIT=SYSDA,DCB=(RECFM=FB,LRECL=200)
//SYSOUT DD SYSOUT=*
//SORTWK01 DD DSN=&&TEMP,SPACE=(CYL,(2)),UNIT=SYSDA
//SORTWK02 DD DSN=&&TEMP,SPACE=(CYL,(2)),UNIT=SYSDA
//SORTWK03 DD DSN=&&TEMP,SPACE=(CYL,(2)),UNIT=SYSDA
//SORTWK04 DD DSN=&&TEMP,SPACE=(CYL,(2)),UNIT=SYSDA
//SORTWK05 DD DSN=&&TEMP,SPACE=(CYL,(2)),UNIT=SYSDA
//SORTMSG DD SYSOUT=*
//SYSIN DD *
----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
SORT FIELDS=COPY
JOINKEYS FILE=F1,
FIELDS=(3,12,A,25,8,A,34,1,A,36,8,A,45,8,A,16,8,A,54,8,A),
FORMAT=CH
JOINKEYS FILE=F2,
FIELDS=(3,12,A,16,8,A,25,1,A,27,8,A,36,8,A,145,8,A,155,8,A),
FORMAT=CH
REFORMAT FIELDS=(F1:3,12,25,8,34,1,36,8,45,8, X
F2:45,8,56,8,120,8,130,8,75,43,165,8,175,8)
OUTFIL FNAMES=MATCH,
BUILD=(3,12,X,16,8,X,25,1,X,27,8,X,36,8,X,45,8,X,54,8,X, X
64,8,X,73,8,X,83,43,X,124,8,X,133,8,61X),
OMIT=(45,8,CH,EQ,C'00010101')
END
Code:
SYNCSORT FOR Z/OS 2.1.3.0R U.S. PATENTS: 4210961, 5117495 (C) 2014 SYNCS
CANADA REVENUE AGENCY z/OS 2.1.0
SYNCSORT LICENSED FOR CPU SERIAL NUMBER 12C27, MODEL 2827 715 LICE
SYSIN :
SORT FIELDS=COPY
JOINKEYS FILE=F1,
FIELDS=(3,12,A,25,8,A,34,1,A,36,8,A,45,8,A,16,8,A,54,8,A),
FORMAT=CH
JOINKEYS FILE=F2,
FIELDS=(3,12,A,16,8,A,25,1,A,27,8,A,36,8,A,145,8,A,155,8,A),
FORMAT=CH
REFORMAT FIELDS=(F1:3,12,25,8,34,1,36,8,45,8, X
F2:45,8,56,8,120,8,130,8,75,43,165,8,175,8)
OUTFIL FNAMES=MATCH,
BUILD=(3,12,X,16,8,X,25,1,X,27,8,X,36,8,X,45,8,X,54,8,X, X
64,8,X,73,8,X,83,43,X,124,8,X,133,8,61X),
OMIT=(45,8,CH,EQ,C'00010101')
END
WER813I INSTALLATION OPTIONS IN MFX LOAD LIBRARY WILL BE USED
WER276B SYSDIAG= 11297146, 20497476, 20497476, 22994850
WER164B 9,720K BYTES OF VIRTUAL STORAGE AVAILABLE, MAX REQUESTED,
WER164B 0 BYTES RESERVE REQUESTED, 1,020K BYTES USED
WER481I JOINKEYS REFORMAT RECORD LENGTH= 128, TYPE = F
WER230A MATCH OUTREC FIELD OUTSIDE RANGE
WER211B SYNCSMF CALLED BY SYNCSORT; RC=0000
WER449I SYNCSORT GLOBAL DSM SUBSYSTEM ACTIVE
WER482I JNF1 STATISTICS
WER483B 3,160K BYTES OF VIRTUAL STORAGE AVAILABLE, MAX REQUESTED,
WER483B 0 BYTES RESERVE REQUESTED, 1,020K BYTES USED
WER108I SORTJNF1 : RECFM=FB ; LRECL= 80; BLKSIZE= 27920
WER073I SORTJNF1 : DSNAME=DXF125.SXSC.NDVRC1.CCIDVAL
WER482I JNF2 STATISTICS
WER483B 6,072K BYTES OF VIRTUAL STORAGE AVAILABLE, MAX REQUESTED,
WER483B 0 BYTES RESERVE REQUESTED, 1,020K BYTES USED
WER108I SORTJNF2 : RECFM=FB ; LRECL= 200; BLKSIZE= 27800
WER073I SORTJNF2 : DSNAME=DXF125.SXSC.NDVRC1.CCIDPIT.NEXTDAY
The output should be lined up as this:
Code:
* CCID Env # System Subsys CCID-Eff CCID-Xpry CreateBy Create-Dt Comments LstUpdBy LstUpdDt
*---+----1----+----2----+----3----+----4----+----5----+----6----+----7-----+----8----+----9----+----0----+----1----+----2----+----3----+----4
I understand why the reformat fields are 128 bytes, but the outfil is not, what parameter is missing?
Back to top
sergeyken Senior Member Joined: 29 Apr 2008Posts: 2011 Location: USA
The fields defined in BUILD= as "124,8,...133,8" are outside of REFORMAT fields
Back to top
Danielle.Filteau New User Joined: 02 Dec 2014Posts: 16 Location: Canada
Yes, I understand that.
I need the output to have blanks between the fields, which the REFORMAT doesn't allow, so the OUTFIL is the correct output format. What should I be specifying instead so that this works? Do I need to split this into 2 steps?
Back to top
Bill Woodger Moderator Emeritus Joined: 09 Mar 2011Posts: 7309 Location: Inside the Matrix
Where did you get FORMAT= for FIELDS on JOINKEYS? You have no choice.
You don't need to use the continuation column to continue a line, it will just naturally continue if ending ", " or ": " (you can include a comment after that, blank, just for information).
Since you can continue without the X, try the effect of defining each field on its own line, and see how much quicker it is to add up the lengths.
Sergeyken has spotted your actual problem.
Back to top
Bill Woodger Moderator Emeritus Joined: 09 Mar 2011Posts: 7309 Location: Inside the Matrix
You either have not specified everything in your REFORMAT, or you have misspecified something in your OUTFIL BUILD. We can have no clue which, so I'll guess one way, and Sergeyken the other, so one of use will be correct :-)
My guess is Heads.
Back to top
sergeyken Senior Member Joined: 29 Apr 2008Posts: 2011 Location: USA
All fields copied by BUILD parameter from REFORMAT record MUST BE within the size of REFORMAT FIELDS.
A 128-bytes record comes as input to OUTFIL BUILD; it cannot extract fields "124,8,...133,8" from it!!!
Back to top
Danielle.Filteau New User Joined: 02 Dec 2014Posts: 16 Location: Canada
Thank you for your help, it finally made sense. This did work and gave me my output lined up the way it needed to be.
Code:
//BLDACTV EXEC PGM=SYNCSORT,COND=(0,LT)
//SORTJNF1 DD DISP=SHR,DSN=SXSC.NDVRC1.CCIDVAL
//SORTJNF2 DD DISP=SHR,DSN=SXSC.NDVRC1.CCIDPIT
//*SORTOUT DD DISP=OLD,
//* DSN=DXF125.SXSC.NDVRC1.CCIDVAL.ACTSRCH
//SORTOUT DD DISP=(NEW,CATLG,DELETE),
// DSN=DXF125.SXSC.NDVRC1.CCIDVAL.ACTSRCH.SORTOUT,
// SPACE=(CYL,(5,5)),UNIT=SYSDA,DCB=(RECFM=FB,LRECL=129)
//MATCH DD DISP=(NEW,CATLG,DELETE),
// DSN=DXF125.SXSC.NDVRC1.CCIDVAL.ACTSRCH.JOIN.M,
// SPACE=(CYL,(5,5)),UNIT=SYSDA,DCB=(RECFM=FB,LRECL=200)
//SYSOUT DD SYSOUT=*
//SORTWK01 DD DSN=&&TEMP,SPACE=(CYL,(2)),UNIT=SYSDA
//SORTWK02 DD DSN=&&TEMP,SPACE=(CYL,(2)),UNIT=SYSDA
//SORTWK03 DD DSN=&&TEMP,SPACE=(CYL,(2)),UNIT=SYSDA
//SORTWK04 DD DSN=&&TEMP,SPACE=(CYL,(2)),UNIT=SYSDA
//SORTWK05 DD DSN=&&TEMP,SPACE=(CYL,(2)),UNIT=SYSDA
//SORTMSG DD SYSOUT=*
//*******************************************************************
//* JOINKEYS CCIDVAL FIELDS: (IN ORDER, ASCENDING)
//* CCID, ENVIRONMENT, STAGE, SYSTEM, SUBSYSTEM, USERID, TYPE
//*
//* JOINKEYS CCIDPIT FIELDS: (IN ORDER, ASCENDING)
//* CCID, ENVIRONMENT, STAGE, SYSTEM, SUBSYSTEM, USERID, TYPE
//*
//* SORTOUT FORMAT:
//* FROM CCIDVAL: CCID, ENVIRONMENT, STAGE, SYSTEM, SUBSYSTEM,
//* FROM CCIDPIT: CCID-EFF,CCID-XPRY,CREATE-UID,CREATE-DT, COMMENT,
//* LST-UPD-UID,LST-UPD-DT,EXPRY-FLAG. REST OF ROW
//* IS FILLER TO PAD TO 200 CHARACTERS.
//* REPORT FORMAT:
//* CCID, ENVIRONMENT, STAGE, SYSTEM, SUBSYSTEM,CCID-EFF,CCID-XPRY,
//* CREATE-UID,CREATE-DT, COMMENT, LST-UPD-UID, LST-UPD-DT
//* REST OF ROW SET TO BLANKS.
//*
//*******************************************************************
//SYSIN DD *
SORT FIELDS=COPY
JOINKEYS FILE=F1,
FIELDS=(3,12,A,25,8,A,34,1,A,36,8,A,45,8,A,16,8,A,54,8,A),
FORMAT=CH
JOINKEYS FILE=F2,
FIELDS=(3,12,A,16,8,A,25,1,A,27,8,A,36,8,A,145,8,A,155,8,A),
FORMAT=CH
REFORMAT FIELDS=(F1:3,12,25,8,34,1,36,8,45,8,
F2:45,8,56,8,120,8,130,8,75,43,165,8,175,8,
67,1)
OUTFIL FNAMES=MATCH,
BUILD=(2X,1,12,X,13,8,X,21,1,X,22,8,X,30,8,X,38,8,X,46,8,2X,
54,8,X,62,8,2X,70,43,X,113,8,X,121,8,57X),
OMIT=((38,8,CH,EQ,C'00010101'),AND,(129,1,CH,EQ,C'Y'),OR,
(1,1,CH,EQ,X'40'))
END
/*
//
Back to top
sergeyken Senior Member Joined: 29 Apr 2008Posts: 2011 Location: USA
Just FYI:
1) Work files //SORTWKnn are not required by SYNCSORT as explicitly defined since long time - you can reduce your JCL lines almost twice.
2) Parameters FILE=F1/2 can be replaced with F1=fname1 / F2=fname2 - for clarity.
Back to top
Arun Raj Moderator Joined: 17 Oct 2006Posts: 2481 Location: @my desk
sergeyken wrote:
Parameters FILE=F1/2 can be replaced with F1=fname1 / F2=fname2 - for clarity
Last time when I was at a Syncsort (IIRC it was 1.4.2) shop, it did not seem to have this feature. Just curious which Syncsort version supports this.
Back to top
Danielle.Filteau New User Joined: 02 Dec 2014Posts: 16 Location: Canada
Sergeyken is right, I could use F1=ddname instead of FILE=F1. This is in the manual for Syncsort MFX r2.1.
Back to top
Arun Raj Moderator Joined: 17 Oct 2006Posts: 2481 Location: @my desk
Danielle.Filteau - Cool. Good to know Syncsort has 'acquired' this DFSORT feature now. I am at a DFSORT shop these days. So no idea about what is happening on the other side. Thanks.
Back to top
Please enable JavaScript!