IBM Mainframe Forum

by **vespa** » Wed Aug 25, 2010 11:19 am

Hello,

I have 2 input files and want to create output files for the following:
* matched key that appear on both file1 and 2
* key that appears only on file1
* key that appears only on file2

These 2 input files are Fixed Block, recl=80, key is in same place, position 1-10;
Output file is also FB, recl=80; I used the ICETOOL SPLICE and it seemed to worked,
however, I have a couple questions:

1/ When I build output the file, how can I remove the identifier 'VV' BB' 'VB'?
2/ Currently the key is character (1,10,CH), will this solution works if the key is unsigned packed (1,10,PD)?
3/ If I need to have another output file that has matched key (pos. 1 to 10), but the detail does not match
(pos 11 to pos 80), how can I do it? Do I need to add another identifier?
4/ To sort the output files by the key, do I need an extra step to sort it or can I sort it at the same time that I build the output files?

Thanks.

input file 1

Select all

RECORD1 1 20100706

RECORD3 3 20100706

RECORD2 2 20100706

RECORD4 4 20100706

Input file 2

Select all

RECORD1 1 20100706

RECORD3 3 20100706

RECORD6 6 20100706

RECORD2 2 20100706

RECORD5 5 20100706

Output - key matched on both file1 and 2

Select all

RECORD1 1 20100706 VB

RECORD2 2 20100706 VB

RECORD3 3 20100706 VB

Output - key only in file 1

Select all

RECORD4 4 20100706 BB

Output file - key only in file 2

Select all

RECORD5 5 20100706 VV

RECORD6 6 20100706 VV

JCL:

Select all

//PS030 EXEC PGM=ICETOOL

//TOOLMSG DD SYSOUT=*

//DFSMSG DD SYSOUT=*

//IN1 DD DSN=SAT.SP.TEST.FILE1,DISP=SHR

//IN2 DD DSN=SAT.SP.TEST.FILE2,DISP=SHR

//T1 DD DSN=&&T1,

// UNIT=SYSDA,

// SPACE=(CYL,(10,10),RLSE),

// DISP=(MOD,PASS)

//OUT12 DD DSN=SAT.SP.TEST.FILE.FIL1FIL2.MATCH,

// DISP=OLD

//* DISP=(,CATLG,DELETE),

//* UNIT=SYSDA,

//* SPACE=(80,(250,100),RLSE),AVGREC=K,

//* DCB=(MODEL,RECFM=FB,LRECL=80,BLKSIZE=0)

//OUT1 DD DSN=SAT.SP.TEST.FILE.NOMATCH.FILE1,

// DISP=OLD

//* DISP=(,CATLG,DELETE),

//* UNIT=SYSDA,

//* SPACE=(80,(250,100),RLSE),AVGREC=K,

//* DCB=(MODEL,RECFM=FB,LRECL=80,BLKSIZE=0)

//OUT2 DD DSN=SAT.SP.TEST.FILE.NOMATCH.FILE2,

// DISP=OLD

//* DISP=(,CATLG,DELETE),

//* UNIT=SYSDA,

//* SPACE=(80,(250,100),RLSE),AVGREC=K,

//* DCB=(MODEL,RECFM=FB,LRECL=80,BLKSIZE=0)

//TOOLIN DD *

* ADD 'BB' IDENTIFIER FOR FILE1 RECORDS

COPY FROM(IN1) TO(T1) USING(CTL1)

* ADD 'VV' IDENTIFIER FOR FILE2 RECORDS

COPY FROM(IN2) TO(T1) USING(CTL2)

* SPLICE TO MATCH UP RECORDS AND WRITE THEM TO THEIR

* APPROPRIATE OUTPUT FILES.

SPLICE FROM(T1) TO(OUT12) ON(1,10,CH) WITH(70,1) -

USING(CTL3) KEEPNODUPS

/*

//CTL1CNTL DD *

* ADD 'BB' (BASE) IDENTIFIER TO FILE1 RECORDS.

INREC OVERLAY=(70:C'BB')

/*

//CTL2CNTL DD *

* ADD 'VV' (BASE) IDENTIFIER TO FILE2 RECORDS.

INREC OVERLAY=(70:C'VV')

/*

//CTL3CNTL DD *

* WRITE MATCHING RECORDS TO OUT12 FILE

OUTFIL FNAMES=OUT12,INCLUDE=(70,2,CH,EQ,C'VB'),BUILD=(1,80)

* WRITE FILE1 ONLY RECORDS TO OUT1 FILE

OUTFIL FNAMES=OUT1,INCLUDE=(70,2,CH,EQ,C'BB'),BUILD=(1,80)

* WRITE FILE2 ONLY RECORDS TO OUT2 FILE

OUTFIL FNAMES=OUT2,INCLUDE=(70,2,CH,EQ,C'VV'),BUILD=(1,80)

/*

by **Frank Yaeger** » Thu Aug 26, 2010 1:32 am

Why did you put the id in the middle of the records instead of at the end of the records? If you put the id at the end of the records (e.g. 81-82), then BUILD=(1,80) at the end will remove the id.

Select all

* ADD 'BB' IDENTIFIER FOR FILE1 RECORDS
COPY FROM(IN1) TO(T1) USING(CTL1)
* ADD 'VV' IDENTIFIER FOR FILE2 RECORDS
COPY FROM(IN2) TO(T1) USING(CTL2)
* SPLICE TO MATCH UP RECORDS AND WRITE THEM TO THEIR
* APPROPRIATE OUTPUT FILES.
SPLICE FROM(T1) TO(OUT12) ON(1,10,CH) WITH(81,1) -
USING(CTL3) KEEPNODUPS
/*
//CTL1CNTL DD *
* ADD 'BB' (BASE) IDENTIFIER TO FILE1 RECORDS.
INREC OVERLAY=(81:C'BB')
/*
//CTL2CNTL DD *
* ADD 'VV' (BASE) IDENTIFIER TO FILE2 RECORDS.
INREC OVERLAY=(81:C'VV')
/*
//CTL3CNTL DD *
* WRITE MATCHING RECORDS TO OUT12 FILE
OUTFIL FNAMES=OUT12,INCLUDE=(81,2,CH,EQ,C'VB'),BUILD=(1,80)
* WRITE FILE1 ONLY RECORDS TO OUT1 FILE
OUTFIL FNAMES=OUT1,INCLUDE=(81,2,CH,EQ,C'BB'),BUILD=(1,80)
* WRITE FILE2 ONLY RECORDS TO OUT2 FILE
OUTFIL FNAMES=OUT2,INCLUDE=(81,2,CH,EQ,C'VV'),BUILD=(1,80)

Currently the key is character (1,10,CH), will this solution works if the key is unsigned packed (1,10,PD)?

You can use ON(1,10,PD) ... that will work for a valid PD value. I don't know what you mean by unsigned packed - do you mean it has a sign but the sign is always the same (e.g. C) or something else?

3/ If I need to have another output file that has matched key (pos. 1 to 10), but the detail does not match (pos 11 to pos 80), how can I do it? Do I need to add another identifier?

I'm not sure what you mean by this. Show an example of input and expected output and explain the rules.

4/ To sort the output files by the key, do I need an extra step to sort it or can I sort it at the same time that I build the output files?

When you use ON(1,10,CH) for SPLICE, the records are automatically sorted by 1,10,CH. You don't need an extra step.

by **vespa** » Thu Aug 26, 2010 9:35 am

Frank,

Thanks for the info, I made the change and the id was removed.

Regard the unsigned pack field, below is an example of the 1st record in the file, the key is the 1st 6 bytes, unsigned and pack
in the copy book, I sorted the file using BI option instead of PD and it's sorted o.k.

Select all

.............h.'..........r..................Ã Ã¡Ã«..&!!<.Ã¤<Ã«Ã¤....

000000000000080B02100000009000000000000000000445425554244542222

000010000000080E2AC00000029000000000000000000453B00AAC03C330000

One more question, if I need to write out 2 additional output files from this process,
outfile4 has records from file 1 with matched key and outfile5 has record from file 2 with
matched key, how can I do this? In a different process, we might need to compare these 2 files for differences
starting from column 11 to 80.

Output file 4: Records from file 1 that has matched key

Select all

RECORD1 1 20100706

RECORD2 2 20100706

RECORD3 3 20100706

Output file 5: Records from file 2 that has matched key

Select all

RECORD1 1 20100707

RECORD2 2 20100707

RECORD3 3 20100707

by **Frank Yaeger** » Thu Aug 26, 2010 10:07 pm

Yes, BI would be the correct format to use for those values.

To answer your other question, I'd like some more information about your DFSORT level. Please run the following job and post the //SYSOUT messages you receive:

Select all

//S1 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD *
RECORD
//SORTOUT DD DUMMY
//SYSIN DD *
OPTION COPY
/*

by **vespa** » Fri Aug 27, 2010 8:48 pm

Sysout messages

Select all

ICE805I 1 JOBNAME: #DPTEST , STEPNAME: STEP1
ICE802I 0 BLOCKSET TECHNIQUE IN CONTROL
ICE201I F RECORD TYPE IS F - DATA STARTS IN POSITION 1
ICE751I 0 C5-K90013 C6-K90013 C7-K90000 C8-K42135 E9-K90013 C9-BASE E5-K35433 E7-K24705
ICE143I 0 BLOCKSET COPY TECHNIQUE SELECTED
ICE250I 0 VISIT http://www.ibm.com/storage/dfsort FOR DFSORT PAPERS, EXAMPLES AND MORE
ICE000I 1 - CONTROL STATEMENTS FOR 5694-A01, Z/OS DFSORT V1R5 - 09:43 ON FRI AUG 27, 2010 -
OPTION COPY 00120000
ICE201I F RECORD TYPE IS F - DATA STARTS IN POSITION 1
ICE751I 0 C5-K90013 C6-K90013 C7-K90000 C8-K42135 E9-K90013 C9-BASE E5-K35433 E7-K24705
ICE193I 0 ICEAM1 ENVIRONMENT IN EFFECT - ICEAM1 INSTALLATION MODULE SELECTED
ICE088I 0 #DPTEST.STEP1 . , INPUT LRECL = 80, BLKSIZE = 80, TYPE = FB
ICE093I 0 MAIN STORAGE = (MAX,2097152,2083934)
ICE156I 0 MAIN STORAGE ABOVE 16MB = (2006494,2006494)
ICE127I 0 OPTIONS: OVFLO=RC0 ,PAD=RC0 ,TRUNC=RC0 ,SPANINC=RC16,VLSCMP=N,SZERO=Y,RESET=Y,VSAMEMT=Y,DYNSPC=256
ICE128I 0 OPTIONS: SIZE=2097152,MAXLIM=1048576,MINLIM=450560,EQUALS=N,LIST=Y,ERET=ABEND,MSGDDN=SYSOUT
ICE129I 0 OPTIONS: VIO=N,RESDNT=ALL ,SMF=FULL ,WRKSEC=Y,OUTSEC=Y,VERIFY=N,CHALT=N,DYNALOC=N ,ABCODE=MSG
ICE130I 0 OPTIONS: RESALL=4096,RESINV=0,SVC=109 ,CHECK=Y,WRKREL=Y,OUTREL=Y,CKPT=N,STIMER=Y,COBEXIT=COB2
ICE131I 0 OPTIONS: TMAXLIM=2097152,ARESALL=0,ARESINV=0,OVERRGN=65536,CINV=Y,CFW=Y,DSA=0
ICE132I 0 OPTIONS: VLSHRT=N,ZDPRINT=Y,IEXIT=N,TEXIT=N,LISTX=N,EFS=NONE ,EXITCK=S,PARMDDN=DFSPARM ,FSZEST=N
ICE133I 0 OPTIONS: HIPRMAX=OPTIMAL,DSPSIZE=300 ,ODMAXBF=0,SOLRF=Y,VLLONG=N,VSAMIO=N,MOSIZE=MAX
ICE235I 0 OPTIONS: NULLOUT=RC0
ICE084I 0 BSAM ACCESS METHOD USED FOR SORTOUT
ICE084I 0 BSAM ACCESS METHOD USED FOR SORTIN
ICE889I 0 CT=MAX , SB=4, L=0, D=0000
ICE902I 0 O AZ00 I AZ00
ICE751I 1 EF-K10929 F0-K30362 E8-K90013
ICE900I 0 CON=1,MUV=0,VOL=1,ENU=0,SBK=0,SRC=0,VEM=0
ICE090I 0 OUTPUT LRECL = 80, BLKSIZE = 80, TYPE = FB
ICE055I 0 INSERT 0, DELETE 0
ICE054I 0 RECORDS - IN: 1, OUT: 1
ICE891I 1 2075888 WMAIN, 8040 CMAIN, MAX CALLOC, N SCN, B BA, 0 AZ, 0 BZ, YY QC
ICE892I 1 80 RIN 80 BLI 80 BLO 78 RUN 0 BUN 2086 CPU 00 CVC
ICE893I 1 0 XIN 0 WIN 0 GIN NDEN PFP B6D CM000 CIX UPTH LMD VS RUX
ICE026I 1 SMF RECORD NOT WRITTEN TO THE SMF DATA SET(RC=20)
ICE052I 0 END OF DFSORT

by **Frank Yaeger** » Fri Aug 27, 2010 11:48 pm

This would be easy to do in one pass if you could use JOINKEYS, but you don't have the DFSORT PTF that supports it. How about just adding another SELECT operator to create OUT4. Here's the revised DFSORT/ICETOOL job:

Select all

//PS030 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN1 DD DSN=... input file1
//IN2 DD DSN=... input file2
//T1 DD DSN=&&T1,
// UNIT=SYSDA,
// SPACE=(CYL,(10,10),RLSE),
// DISP=(MOD,PASS)
//OUT4 DD DSN=... match file1
//OUT5 DD DSN=... match file2
//OUT1 DD DSN=... file1 only
//OUT2 DD DSN=... file2 only
//TOOLIN DD *
* ADD 'BB' IDENTIFIER FOR FILE1 RECORDS
COPY FROM(IN1) TO(T1) USING(CTL1)
* ADD 'VV' IDENTIFIER FOR FILE2 RECORDS
COPY FROM(IN2) TO(T1) USING(CTL2)
* SPLICE TO MATCH UP RECORDS AND WRITE THEM TO THEIR
* APPROPRIATE OUTPUT FILES.
SPLICE FROM(T1) TO(OUT5) ON(1,10,CH) WITH(81,1) -
USING(CTL3) KEEPNODUPS
SELECT FROM(T1) TO(OUT4) ON(1,10,CH) FIRSTDUP USING(CTL4)
/*
//CTL1CNTL DD *
* ADD 'BB' (BASE) IDENTIFIER TO FILE1 RECORDS.
INREC OVERLAY=(81:C'BB')
/*
//CTL2CNTL DD *
* ADD 'VV' (BASE) IDENTIFIER TO FILE2 RECORDS.
INREC OVERLAY=(81:C'VV')
/*
//CTL3CNTL DD *
* WRITE MATCHING RECORDS TO OUT12 FILE
OUTFIL FNAMES=OUT5,INCLUDE=(81,2,CH,EQ,C'VB'),BUILD=(1,80)
* WRITE FILE1 ONLY RECORDS TO OUT1 FILE
OUTFIL FNAMES=OUT1,INCLUDE=(81,2,CH,EQ,C'BB'),BUILD=(1,80)
* WRITE FILE2 ONLY RECORDS TO OUT2 FILE
OUTFIL FNAMES=OUT2,INCLUDE=(81,2,CH,EQ,C'VV'),BUILD=(1,80)
/*
//CTL4CNTL DD *
OUTFIL FNAMES=OUT4,BUILD=(1,80)
/*

by **vespa** » Thu Oct 07, 2010 12:16 am

Thanks Frank, it worked !!

IBM Mainframe Forum

Files with match and no match key

Files with match and no match key

Re: Files with match and no match key

Re: Files with match and no match key

Re: Files with match and no match key

Re: Files with match and no match key

Re: Files with match and no match key

Re: Files with match and no match key