Create files with matching and non matching records- SPLICE?

In this Mainframe Forum - You can post your queries on DFSORT, ICETOOL , SyncSort & JCL Utilities

Moderators: Frank Yaeger, Moderator Group

Post Reply
CraigHeron
Member
Posts: 3
Joined: Mon Oct 17, 2011 10:33 pm

Create files with matching and non matching records- SPLICE?

Post by CraigHeron » Mon Oct 17, 2011 10:50 pm

I've been reading up on the use of the SPLICE operator in ICETOOL and to be honest, I'm struggling a little with it. I have 2 input datasets, I want to compare one dataset with the other and output all records that have specific strings in both datasets to a 'Matching' dataset and the records that do not match, to a 'Not Matching' dataset.

Here's a sample of the INPUT datasets (we'll call them input1 and input2 for now).

INPUT1

02:02:41 * ESS26000,YPAT,G03794
02:02:41 * ESS25000,NEAT,G22857
02:04:41 * ESS26000,YPCS,G07829
02:04:47 * ESS26000,YPCT,G07076
02:32:41 * ESS26000,YPAT,G03795
02:41:41 * ESS26000,GKAT,G32147
02:42:41 * ESS26000,YPAT,G03796

INPUT2

DOWNLOAD: ESS26000,YPAT,G03794
DOWNLOAD: ESS25000,NEAT,G22857
DOWNLOAD: ESS26000,YPCS,G07829
DOWNLOAD: ESS26000,YPCT,G07076
DOWNLOAD: ESS26000,YPAT,G03795
DOWNLOAD: ESS26000,GKAT,G32147
DOWNLOAD: ESS26000,YPAT,G03796
DOWNLOAD: ESS25000,NEAT,G22858
DOWNLOAD: ESS25000,NEAT,G22859
DOWNLOAD: ESS25000,NECS,G22093

Both datasets are have the same record lenght (266), with the output datasets also needing to be 266. The fields I need to compare on start in column 1 for 20 in the input1 dataset, and column 61 for 20 in the input2 dataset (the 'ESS26000,YPAT,G03794' string for example)

If a string within these column boundaries appears in both input dataset, I want to output columns 1 to 33 from the input1 dataset and columns 61 to 266 from the input2 dataset into a the 'Matching' dataset.

If the string within these column boundaries appears does not appear in both datasets, then I want to output columns 1 to 266 from the input dataset when the unmatched string resides.

Maybe a fresh start tomorrow will help me sort this myself, but right now I feel like I've gone cross eye'd!

Help would be much appreciated.

Thanks in advance.

User avatar
Frank Yaeger
Moderator
Posts: 812
Joined: Sat Feb 18, 2006 5:45 am
Location: San Jose, CA
Contact:

Post by Frank Yaeger » Mon Oct 17, 2011 11:19 pm

DFSORT's JOINKEYS function would be a better choice than SPLICE. For more information, see the "Create files with matching and non-matching records" Smart DFSORT Trick at:

http://www.ibm.com/support/docview.wss? ... g3T7000094

I'd show you how to do what you want with JOINKEYS, but I can't really figure out what you want from your description and examples. You say you want to compare 1-20 in input1 with 61-20 input2 but those don't seem to match (e.g. 02:02:41 vs ESS26000,YPAT,G03794).

If you need more specific help, you need to explain more clearly what you're trying to do. Show an example of the records in each input file (relevant fields only) and what you expect for output. Explain the "rules" for getting from input to output. Give the starting position, length and format of each relevant field. Give the RECFM and LRECL of the input files. If file1 can have duplicates within it, show that in your example. If file2 can have duplicates within it, show that in your example.
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort

CraigHeron
Member
Posts: 3
Joined: Mon Oct 17, 2011 10:33 pm

Post by CraigHeron » Tue Oct 18, 2011 2:28 pm

Thanks Frank, I'd spotted the JOINKEYS function earlier in the day yesterday, but wasn't sure what was best to use (SPLICE or JOINKEYS). I'd really appreciate it if you could show me how to do what I need using JOINKEYS. As requested, here is a more details description of what I'm trying to do (please let me know if this isn't clear enough).


Here's an example from my INPUT1 file of the field I want to compare with my INPUT2 file. The start position is 14 for 20 CH (EBCDIC characters).

ESS26000,YPAT,G03794
ESS25000,NEAT,G22857
ESS26000,YPCS,G07829
ESS26000,YPCT,G07076
ESS26000,YPAT,G03795
ESS26000,GKAT,G32147

Here's an example of my INPUT2 file, start position 61 for 20 CH (EBCDIC characters)

ESS26000,YPCT,G07076
ESS26000,YPAT,G03795
ESS26000,GKAT,G32147
ESS26000,YPAT,G03796
ESS25000,NEAT,G22858
ESS25000,NEAT,G22859

All datasets (input and output) are the same RECFB=FB and LRECL=266.

If any of the records from the input1 dataset (start pos 14 for 20) match ANY of the records from the input2 dataset (start pos 61 for 20) then I want to output from start pos 1 for 33 from the input1 dataset record and start pos 82 for 80 from the input2 dataset record to an output dataset called 'matched' (a single output record, not two output records).

Any unmatched/unpaired records from the input1 dataset (start pos 14 for 20) need to be output from start pos 1 for 33 from the input1 dataset record to an output dataset called 'nomatch1'

Any unmatched/unpaired records from the input2 dataset (start pos 61 for 20) need to be output from start pos 82 for 80 from the input2 dataset record to an output dataset called 'nomatch2'

Here are 4 sample records from start pos 1 for 33 from the input1 dataset.

02:02:41 * ESS26000,YPAT,G03794
02:02:41 * ESS25000,NEAT,G22857
02:04:41 * ESS26000,YPCS,G07829
02:04:47 * ESS26000,YPCT,G07076

Here are 4 sample records from start pos 61 for 100 from the input2 dataset.

ESS26000,YPCS,G07829 * DOWNLOAD STATISTICS - TIME: .03 S. SIZE: 1690 B. RATE: 56333
ESS26000,YPCT,G07076 * DOWNLOAD STATISTICS - TIME: .01 S. SIZE: 487 B. RATE: 48700
ESS26000,YPAT,G03795 * DOWNLOAD STATISTICS - TIME: .03 S. SIZE: 742 B. RATE: 24733
ESS26000,GKAT,G32147 * DOWNLOAD STATISTICS - TIME: .12 S. SIZE: 2719 B. RATE: 22658

Here's an example of a 'matched' record in the output file.

02:04:41 * ESS26000,YPCS,G07829 * DOWNLOAD STATISTICS - TIME: .03 S. SIZE: 1690 B. RATE: 56333

User avatar
Frank Yaeger
Moderator
Posts: 812
Joined: Sat Feb 18, 2006 5:45 am
Location: San Jose, CA
Contact:

Post by Frank Yaeger » Tue Oct 18, 2011 11:05 pm

You can use a DFSORT job like the following to do what you asked for:

Code: Select all

//S1  EXEC  PGM=SORT
//SYSOUT    DD  SYSOUT=*
//IN1 DD DSN=...  input file1 (FB/266)
//IN2 DD DSN=...  input file2 (FB/266)
//MATCHED DD DSN=...  output file1 (FB/266)
//NOMATCH1 DD DSN=...  output file2 (FB/266)
//NOMATCH2 DD DSN=...  output file3 (FB/266)
//SYSIN    DD    *
  JOINKEYS F1=IN1,FIELDS=(14,20,A)
  JOINKEYS F2=IN2,FIELDS=(61,20,A)
  JOIN UNPAIRED,F1,F2
  REFORMAT FIELDS=(F1:1,33,F2:82,80,?)
  OPTION COPY
  OUTFIL FNAMES=MATCHED,INCLUDE=(114,1,CH,EQ,C'B'),
    BUILD=(1,114,266:X)
  OUTFIL FNAMES=NOMATCH1,INCLUDE=(114,1,CH,EQ,C'1'),
    BUILD=(1,33,266:X)
  OUTFIL FNAMES=NOMATCH2,INCLUDE=(114,1,CH,EQ,C'2'),
    BUILD=(34,80,266:X)
/*
Last edited by Frank Yaeger on Wed Oct 19, 2011 9:50 pm, edited 1 time in total.
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort

CraigHeron
Member
Posts: 3
Joined: Mon Oct 17, 2011 10:33 pm

Post by CraigHeron » Wed Oct 19, 2011 3:12 pm

Thanks very much for your help with this Frank. This worked perfectly. :D

Regards.
Craig....

Post Reply

FREE TUTORIALS

Tutorials
Free tutorials from mainframegurukul
  • JCL Tutorial
    Covers all important JCL concepts.
  • Cobol Tutorial
    This tutorials covers all Cobol Topics from STRING to COMP-3.
  • DB2 Tutorial
    DB2 Tutorial focuses on DB2 COBOL Programming.
  • SORT Tutorial
    This Tutorial covers all important aspects of DFSORT with examples
  • CICS Tutorial
    This CICS tutorial covers CICS concepts and CICS Basics, CICS COBOL Programming.
Interview
Mainframe Interview questions



Other References
Mainframe Tools and others