I have two files viz. FILE1 and FILE2 both are having key (2,16,ch)
Now I want to have a check whether FLDATE field in FILE2 (18,8,ch) is in between LPDATE (18,8,ch)
and PPDATE(60,8,ch) in FILE1, but for the same key value..
Can I do it using SORT or ICETOOL ?
And if the FLDATE is with in LPDATE and PPDATE then I want to write that record in a new file.
Plz note both i/p files are having same LRECL = 90
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
It's not clear to me what exactly you want to do. Please show a good example of the records in each input file and what you expect for output. If FILE1 can have duplicate keys, show that in the example. If FILE2 can have duplicate keys, show that in the example.
Pan no (2,19,ch) ACT DATE PLST DATE
(20,8,ch) (28,8,ch)
4546383420005566000 20050801 20060101
4546383420005764000 20050801 20060101
4546383420005889000 20050801 20060101
4546383420005988000 20050801 20060101
4546383420006192000 20050801 20060101
PAN number is the key field and there will not be any duplicates and LRECL = 90
Structure of FILE2
Code:
Pan no (2,19,ch) ACT DATE (20,8,ch)
4546383420005566000 20051001
4546383420005764000 20050831
4546383420005889000 20060501
4546383420005988000 20051001
PAN number is the key field and there will not be any duplicates and LRECL = 90
Now if the key of both the files are matching and corresponding ACT DATE (From FILE2) is greater than ACT DATE (From FILE1) and less than PLST DATE (from FILE1), then I need to write that PAN no in o/p file.
For e.g. 4546383420005566000 pan no is satisfying this criterion i.e. ACT DATE is in between range of ACT DATE and PLST DATE, so this Pan no should be there in o/p file.
Where as PAN 4546383420005889000 is not satisfying this criterion, so it should not be there in o/p file.
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
Well, you can't have pan-number as 2,19 and act-date starting in 20, since 2,19 would overlap 20. So I'll assume pan-number is really 1,19. If not, change the positions accordingly.
Here's a DFSORT/ICETOOL job that will do what you asked for.
Code:
//S1 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN1 DD DSN=... input file 1 (FB/90)
//IN2 DD DSN=... input file 2 (FB/90)
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//OUT DD DSN=... output file (FB/19)
//TOOLIN DD *
* IN1->T1: Reformat records to:
* |pan-number|act-date1|plst-date|blanks...|
COPY FROM(IN1) TO(T1) USING(CTL1)
* IN2->T1: Reformat records to:
* |pan-number|blanks...|blanks...|act-date2|
COPY FROM(IN2) TO(T1) USING(CTL2)
* Splice on pan-number to get following for matching records:
* |pan-number|act-date1|plst-date|act-date2|
* Keep pan-number from records with:
* act-date2 > act-date1 AND act-date2 < plst-date
SPLICE FROM(T1) TO(OUT) ON(1,19,CH) WITH(36,8) USING(CTL3)
/*
//CTL1CNTL DD *
OUTREC BUILD=(1,35,43:X)
/*
//CTL2CNTL DD *
OUTREC BUILD=(1,19,36:20,8)
/*
//CTL3CNTL DD *
OUTFIL FNAMES=OUT,
INCLUDE=(36,8,ZD,GT,20,8,ZD,AND,36,8,ZD,LT,28,8,ZD),
BUILD=(1,19)
/*
But now it seems to be have soem problem with BUILD...as its giving me error msg as follows:
TOOLMSG:
SYT000I SYNCTOOL RELEASE 1.4D - COPYRIGHT 2003 SYNCSORT INC.
SYT001I INITIAL PROCESSING MODE IS "STOP"
SYT002I "TOOLIN" INTERFACE BEING USED
* IN1->T1: REFORMAT RECORDS TO:
* |PAN-NUMBER|ACT-DATE1|PLST-DATE|BLANKS...|
COPY FROM(IN1) TO(T1) USING(CTL1)
SYT020I SYNCSORT CALLED WITH IDENTIFIER "0001"
DFSMSG:
SYNCSORT FOR Z/OS 1.1DRI TPF3A U.S. PATENTS: 4210961, 5117495 (C) 2002 SY
HSBC z/OS 1.6.0
PRODUCT LICENSED FOR CPU SERIAL NUMBER 2671A, MODEL 2084 314 LIC
CTL1CNTL :
OUTREC BUILD=(1,35,43:X)
*
WER428I CALLER-PROVIDED IDENTIFIER IS "0001"
WER268A OUTREC STATEMENT : SYNTAX ERROR
WER211B SYNCSMF CALLED BY SYNCSORT; RC=0000
WER449I SYNCSORT GLOBAL DSM SUBSYSTEM ACTIVE
After runnnig this you are going to have a file with matched records from both the i/p files.
Now you can use the second step to compare and omit the un wanted records.
Once the above JCL completes,the O/P will look like below.
its also not working as its giving me error as follows:
SYSIN :
JOINKEYS FILES=F1,FIELDS=(1,19,A)
*
JOINKEYS FILES=F2,FIELDS=(1,19,A)
*
REFORMAT FIELDS=(F1:1,40,F2:22,8)
*
SORT FIELDS=COPY
OUTREC FIELDS=(1,40,1X,41,8)
WER275A NO KEYWORDS FOUND ON CONTROL STATEMENT
WER275A NO KEYWORDS FOUND ON CONTROL STATEMENT
WER275A NO KEYWORDS FOUND ON CONTROL STATEMENT
WER211B SYNCSMF CALLED BY SYNCSORT; RC=0000
WER449I SYNCSORT GLOBAL DSM SUBSYSTEM ACTIVE
WER428I CALLER-PROVIDED IDENTIFIER IS "0001"
WER268A OUTREC STATEMENT : SYNTAX ERROR
WER211B SYNCSMF CALLED BY SYNCSORT; RC=0000
WER449I SYNCSORT GLOBAL DSM SUBSYSTEM ACTIVE
The above WER messages are the part of SYNCSORT product(You can see the SYNCSORT mentioned in the 3 rd and 4 th lines that I have pasted above).You can easily figure out the Product/release info with a simple SORT statement.
It is my mistake that I haven't mentioned that the above JCL work only if you are working on the latest release of SYNCSORT(SyncSort for z/OS Release 1.2).
JOIN PROCESSING is the new data utility added in this release to get the info from 2 files.It also has few new utilities like MOD(Modulas),REPEAT,SAMPLE,SPLITBY Etc..