Join files



IBM's flagship sort product DFSORT for sorting, merging, copying, data manipulation and reporting. Includes ICETOOL and ICEGENER

Join files

Postby pulcinella » Tue Feb 08, 2011 10:25 pm

Good Morning

I have two files.

FILE1
-------
FIELD1 PIC x(1) --> 1 POSITIONS (One letter: 'F' or 'J')
FIELD2 PIC S9(9) COMP3 --> 5 POSITIONS hexadecimal
FIELD3 PIC X(4) --> 4 POSITIONS (not important)
FIELD4 PIC S9(15) COMP3 --> 8 POSITIONS (import field. It's not important)

FILE2
-------
FIELD1 PIC x(5) --> 5 POSITIONS (numeric)
FIELD2 PIC x(1) --> 1 POSITIONS (One letter: 'F' or 'J')
FIELD3 PIC S9(9) COMP3 --> 5 POSITIONS hexadecimal
FIELD4 PIC x(2) --> 2 POSITIONS (not important)
FIELD5 PIC S9(5) COMP3 --> 3 POSITIONS (not important)

The Key of FILE1 is FIELD1 and FIELD2. They have ordered by both fields and have not duplicates
The Key of FILE2 is FIELD2 and FIELD3. They have ordered by both fields and have not duplicates

I need create two output files:
- The first, with the same structure that input FILE1 + FIELD2, FIELD4 and FILE5 of FILE2. In this OUTPUT FILE1 write records that found at two files

- The second with the same structure that input FILE1 + '00000'. In this OUTPUT FILE2 write records that I have not found at file2

FILE1
F00001aaaabbbbbbbb
F00002aaaabbbbbbbb
F00005aaaabbbbbbbb
F00006aaaabbbbbbbb
J00001aaaabbbbbbbb
J00004aaaabbbbbbbb
J00005aaaabbbbbbbb


FILE2
22222F0000102AAA
11111F0000501BBB
22222F0000601CCC
55555J0000502CCC

OUTPUT FILE1 (28 positions)
F00001aaaabbbbbbbb2222202AAA
F00005aaaabbbbbbbb1111101BBB
F00006aaaabbbbbbbb2222201CCC
J00005aaaabbbbbbbb5555502CCC

OUTPUT FILE1 (23 positions)
F00002aaaabbbbbbbb00000
J00001aaaabbbbbbbb00000
J00004aaaabbbbbbbb00000

I use ICE201I G sort.

Thank you very much

(FI.- I want to be what it's the manual that I have read for this example because I have more similar example to do)
pulcinella
 
Posts: 114
Joined: Mon Dec 10, 2007 10:18 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Join files

Postby skolusu » Tue Feb 08, 2011 11:11 pm

I need create two output files:
- The first, with the same structure that input FILE1 + FIELD2, FIELD4 and FILE5 of FILE2. In this OUTPUT FILE1 write records that found at two files


pulcinella,

I am guessing that you had a typo , you actually need field 1 from file2.

Assuming that FIELD2 of file 1 and FIELD3 of file2 have only positive numbers, then the following DFSORT JCL will give you the desired results. If you have negative numbers then we need to normalize the packed decimal fields before joining. Let me know if that is the case.


//STEP0100 EXEC PGM=SORT                                           
//SYSOUT   DD SYSOUT=*                                             
//INA      DD DSN=Your input file 1 of lrecl 18,DISP=SHR
//INB      DD DSN=Your input file 2 of lrecl 16,DISP=SHR
//OUT1     DD SYSOUT=*                                             
//OUT2     DD SYSOUT=*                                             
//SYSIN    DD *                                                   
  OPTION COPY                                                     
  JOINKEYS F1=INA,FIELDS=(1,6,A),SORTED,NOSEQCK
  JOINKEYS F2=INB,FIELDS=(6,6,A),SORTED,NOSEQCK
  JOIN UNPAIRED                                                   
  REFORMAT FIELDS=(?,F1:1,18,F2:1,5,12,5)                         
  OUTFIL FNAMES=OUT1,INCLUDE=(1,1,CH,EQ,C'B'),BUILD=(2,28)         
  OUTFIL FNAMES=OUT2,INCLUDE=(1,1,CH,EQ,C'1'),BUILD=(2,18,5C'0')   
//*
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
skolusu
 
Posts: 586
Joined: Wed Apr 02, 2008 10:38 pm
Has thanked: 0 time
Been thanked: 39 times

Re: Join files

Postby pulcinella » Wed Feb 09, 2011 1:25 pm

Thanks for your help Skolusu.
You're right. I had not a typo. You understand me correctly.
pulcinella
 
Posts: 114
Joined: Mon Dec 10, 2007 10:18 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Join files

Postby pulcinella » Wed Feb 09, 2011 10:05 pm

I want to say that I have a typo sorry...

A last thing...

If I in the entry file had duplicates and in the second one not: what would it have to use to treat the duplicates? If I found ALL records by KEY at two input files, write at OUTPUT1; if not found write at OUTPUT2.

In this case, the INPUT1 has 153 positions; the first 6 positions are the KEY and is ordered. The last 147 positions are not important. The INPUT2 is equal that the first example

INPUT FILE1 (153 position)
F00001aaaabbbbcccc...
F00001aaaabbbbtttt...
F00002aaaabbbbbbbb...
F00005aaaabbbbbbbb...
F00005aaaabbbbrrrr...
F00006aaaabbbbbbbb...
J00001aaaabbbbbbbb...
J00004aaaabbbbbbbb...
J00005aaaabbbbbbbb...

INPUT FILE2 (16 position)
22222F0000102AAA
11111F0000501BBB
22222F0000601CCC
55555J0000502CCC

OUTPUT FILE1 (163 positions)
F00001aaaabbbbcccc...2222202AAA
F00001aaaabbbbtttt...2222202AAA
F00005aaaabbbbbbbb...1111101BBB
F00005aaaabbbbrrrr...1111101BBB
F00006aaaabbbbbbbb...2222201CCC
J00005aaaabbbbbbbb...5555502CCC

OUTPUT FILE1 (158 positions)
F00002aaaabbbbbbbb....00000
J00001aaaabbbbbbbb....00000
J00004aaaabbbbbbbb....00000

Thank you very much and excuse me again
pulcinella
 
Posts: 114
Joined: Mon Dec 10, 2007 10:18 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Join files

Postby skolusu » Wed Feb 09, 2011 10:37 pm

pulcinella,

Except the length changes there is absolutely no change in the control cards. Use the following control cards

//SYSIN    DD *                                                   
  OPTION COPY                                                     
  JOINKEYS F1=INA,FIELDS=(1,6,A),SORTED,NOSEQCK                   
  JOINKEYS F2=INB,FIELDS=(6,6,A),SORTED,NOSEQCK                   
  JOIN UNPAIRED                                                   
  REFORMAT FIELDS=(?,F1:1,153,F2:1,5,12,5)                       
  OUTFIL FNAMES=OUT1,INCLUDE=(1,1,CH,EQ,C'B'),BUILD=(2,163)       
  OUTFIL FNAMES=OUT2,INCLUDE=(1,1,CH,EQ,C'1'),BUILD=(2,153,5C'0')
//*
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
skolusu
 
Posts: 586
Joined: Wed Apr 02, 2008 10:38 pm
Has thanked: 0 time
Been thanked: 39 times

Re: Join files

Postby pulcinella » Thu Feb 10, 2011 3:32 am

Before sending the second question, I did the test and he would swear that in case
of having duplicated - only in the first file because the second one will never have them -
there were records recorded in the OUTPUT FILE1 and others in the OUTPUT FILE2
(when really they should be in the first one).

I can have been wrong on having done the test. I will check it and if I comment to you
on slightly anomalous meeting.

Thank you and excuses me
pulcinella
 
Posts: 114
Joined: Mon Dec 10, 2007 10:18 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Join files

Postby pulcinella » Thu Feb 10, 2011 2:42 pm

Skolusu.

You were right. I was surprised not to find a command to duplicate records in documentation.
It works perfectly.

When you have many records and have in your head you do not see many things clearly.
Thanks for everything
pulcinella
 
Posts: 114
Joined: Mon Dec 10, 2007 10:18 pm
Has thanked: 0 time
Been thanked: 0 time


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post