IBM Mainframe Forum

by **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)

by **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.

Select all

//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')
//*

by **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.

by **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

by **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

Select all

//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')
//*

by **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

by **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

IBM Mainframe Forum

Join files

Join files

Re: Join files

Re: Join files

Re: Join files

Re: Join files

Re: Join files

Re: Join files