Matching with three output record



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

Matching with three output record

Postby pulcinella » Thu Jan 27, 2011 3:45 am

Good morning.

First of all excuse if I do not write English well. I need your help

I have a file of 130 positions with three fields:

1.-CAMPO1 of 9 positions
2.-CAMPO2 of 119 positions
3.-CAMPO3 of 2 positions

The key is the CAMPO1. Has not duplicated. Always it has a letter and 8 numbers. The CAMPO2 is not important (comes informed but the information is not useful). The CAMPO3 is not important either. It has the values 01, 02 and 03 but they are not used

This file has 189 records

---------------------

I want to maching with another file of 332 positions. Has the following fields:

1.-CAMPO1 of 6 positions (hexadecimal format)
2.-CAMPO2 of 1 position. It is not important
3.-CAMPO3 of 9 positions. (coincides with the CAMPO1 of the previous file)
4.-CAMPO4 of 310 positions. It is not important
5.-CAMPO5 of 4 positions (the field has the values '0049' or other values
6.-CAMPO6 of 2 positions. It is not important

The file is arranged by the CAMPO3.

This file has 1.482.230 records

--------------------

I need to do a maching generating THREE FILES

If the CAMPO5 (the second file) is 0049 and the CAMPO1 (the first file) coincides with the CAMPO3 (the second file) I will write a file of 136 positions with the following structure:

1.-CAMPO1 of 9 positions (of the first file)
2.-CAMPO2 of 119 positions (of the first file)
3.-CAMPO3 of 2 positions (of the first file)
4.-CAMPO4 of 6 positions in hexadecimal (CAMPO1 of the second file)

If the CAMPO1 (the first file) coincides with the CAMPO3 (the second file) but the CAMPO5 (the second file) is not 0049 I will write a file of 139 positions with the following structure

1.-CAMPO1 of 9 positions (of the first file)
2.-CAMPO2 of 119 positions (of the first file)
3.-CAMPO3 of 2 positions (of the first file)
4.-CAMPO4 of 5 positions (with the literal ERROR)
5.-CAMPO5 of 4 positions (with the correct value of the CAMPO5 of the second file) (example: 0065, 0154, 167)

If he(she) was not finding the CAMPO1 (the first file) in the second file (CAMPO3) I will write a third file of 143 positions with the following structure

1.-CAMPO1 of 9 positions (of the first file)
2.-CAMPO2 of 119 positions (of the first file)
3.-CAMPO3 of 2 positions (of the first file)
4.-CAMPO4 of 5 positions (with the literal ERROR)
5.-CAMPO5 of 9 positions (With the literal NOT FOUND)

Thank's for your help

(I think that I have ICE201I F or G DFSORT function level. Tomorrow I verified DFSORT function level)
pulcinella
 
Posts: 114
Joined: Mon Dec 10, 2007 10:18 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Matching with three output record

Postby Frank Yaeger » Thu Jan 27, 2011 5:07 am

If I understand what you want correctly (which I might not), I believe the DFSORT job below will do it. I made some assumptions as follows:

Input file1 is already sorted by the CAMPO1 field and does not have any duplicate records.

Input file2 is already sorted by the CAMP03 field and does not have any duplicate records.

Records with a CAMPO3 field in file2 that does not match a CAMPO1 field in file1 should not appear in the output files.

//S1 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//IN1 DD DSN=...   input file1 (FB/130)
//IN2 DD DSN=...   input file2 (FB/332)
//OUT1 DD DSN=...  output file1 (FB/136)
//OUT2 DD DSN=...  output file1 (FB/139)
//OUT2 DD DSN=...  output file1 (FB/144)
//SYSIN DD *
  JOINKEYS F1=IN1,FIELDS=(1,9,A),SORTED
  JOINKEYS F2=IN2,FIELDS=(8,9,A),SORTED
  JOIN UNPAIRED,F1
  REFORMAT FIELDS=(F1:1,130,?,F2:1,6,327,4)
  OPTION COPY
  OUTFIL FNAMES=OUT1,
    INCLUDE=(131,1,CH,EQ,C'B',AND,138,4,CH,EQ,C'0049'),
    BUILD=(1,130,132,6)
  OUTFIL FNAMES=OUT2,
    INCLUDE=(131,1,CH,EQ,C'B',AND,138,4,CH,NE,C'0049'),
    BUILD=(1,130,C'ERROR',138,4)
  OUTFIL FNAMES=OUT3,
    INCLUDE=(131,1,CH,EQ,C'1'),
    BUILD=(1,130,C'ERROR',C'NOT FOUND')
/*


If my assumptions are incorrect, let me know.

If the job doesn't do what you want for any set of records, then show me an example of the input and expected output for that situation.
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
User avatar
Frank Yaeger
Global moderator
 
Posts: 1079
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 15 times

Re: Matching with three output record

Postby pulcinella » Thu Jan 27, 2011 7:59 pm

Frank,

Thanks a lot of. In spite of the difficult thing that it could be the explanation you have understood, it perfectly. Indeed FILE 1 is ordered by campo1 and, in no case, it can have duplicates FILE 2 cannot take duplicates. In case of having them, it would be an error. In any case it would control, it in a previous step to eliminate the record. It is ordered by the CAMPO3.

In FILE 1 they will only appear the correct registries, the equals. The rest of cases (it does not find, are not equal) will go to the other files of exit.

I will do a test right now. If it did not work would comment it to you and it would give examples you.
pulcinella
 
Posts: 114
Joined: Mon Dec 10, 2007 10:18 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Matching with three output record

Postby pulcinella » Thu Jan 27, 2011 9:34 pm

There was one third part of the process I did that it in cobol and works but, if outside possible, it wanted to know if it could take control of the ICETOOL. I am certainly if although my friends (a little incredulous) tell me that if with invites me to himself to a dinner ;-)

The file obtained in the previous step (OUT1), I am going to format it to have a new file of entry (IN1) of 9 positions with the following structure:

1. - CAMPO1 of 1 position (new Field. With a letter)
2. - CAMPO2 of 6 positions (it is the CAMPO4 of the file of exit OUT1)
3. - CAMPO3 of 2 positions (it is the CAMPO3 of the file of exit OUT1)

This file will be arranged by three fields. The first two fields CANNOT have copies (let's Remember that campo3 it could have the values: 01, 02 or 03). Example:

J 000001 01
J 000001 03
J 000002 01
J 000002 02
J 000010 01
J 000010 03


I want to compare this file with another file (IN2) that has 250 positions whose structure is:

1. - CAMPO1 of 10 positions (it is not important)
2. - CAMPO2 of 39 positions
3. - CAMPO3 of 8 positions (it is not important)
4. - CAMPO4 of 1 position
5. - CAMPO5 of 6 positions
6. - CAMPO6 of 4 positions (it is not important)
7. - CAMPO7 of 1 position (it coincides with the CAMPO1 of the IN1)
8. - CAMPO8 of 6 position (it coincides with the CAMPO2 of the IN1)
9. - CAMPO9 of 2 position (it is not important)
10. - CAMPO10 of 173 positions (it is not important)

The file is arranged by the fields: CAMPO2, CAMPO4, CAMPO5, CAMPO7, CAMPO8 and CAMPO9. The fields CAMPO2 and CAMPO4 can have copies; other fields not

I want to obtain a third file (OUT1) of 10 positions with the following structure

1. - CAMPO1 of 1 position (it coincides with the CAMPO5 of the IN2)
2. - CAMPO2 of 6 position (it coincides with the CAMPO6 of the IN2)
3. - CAMPO3 of 2 position (meter times that there are '01)
3. - CAMPO4 of 2 position (meter times that there are '02)
3. - CAMPO5 of 2 position (meter times that there are '03)
3. - CAMPO6 of 3 position (meter)

Here the part complicated to explain comes:

I want to compare the fields CAMPO1 and CAMPO2 of the file IN1 with the fields CAMPO6 and CAMPO7 of the file IN2

If they are equal if the field CAMPO9 has 01, 1 will add up to an internal variable (it will be the one that later I stored in the CAMPO3 of the file OUT1); if the CAMPO9 has 02, I will add 1 to another internal variable (different from the previous one and that will be stored in the CAMPO4 of the file OUT1); if the CAMPO9 has 03, I will add 1 to a third variable (different from the previous ones and that will be stored in the CAMPO5 of the file OUT1).

I will only write in the exit file when the field CAMPO2, CAMPO4 and CAMPO5 should be changed by them. In this moment, I will record in the exit file the field CAMPO4 and CAMPO5 and will inform the fields CAMPO3, CAMPO4 and CAMPO5 with the meter used (01, 02, 03) and the CAMPO6 like entire meter. That is to say:

IN1

J 000001 01
J 000002 02
J 000003 03
J 000015 02
J 000017 01
J 000019 01
J 000031 02
J 000037 02
J 000043 03
J 000052 02
J 000066 03


IN2

...... xxxxxxxxxxx.... XAAAAAA..... J000001............ (there corresponds with the value 01 of the file IN1)
...... xxxxxxxxxxx.... XAAAAAA..... J000003............ (there corresponds with the value 03 of the file IN1)
...... xxxxxxxxxxx.... XBBBBBB..... J000001............ (there corresponds with the value 01 of the file IN1)
...... xxxxxxxxxxx.... XBBBBBB..... J000003............ (there corresponds with the value 03 of the file IN1)
...... xxxxxxxxxxx.... XBBBBBB..... J000018............ (value does not exist)
...... xxxxxxxxxxx.... XBBBBBB..... J000019............ (there corresponds with the value 01 of the file IN1)
...... xxxxxxxxxxx.... XBBBBBB..... J000022............ (value does not exist)
...... xxxxxxxxxxx.... XBBBBBB..... J000043............ (there corresponds with the value 03 of the file IN1)
...... xxxxxxxxxxx.... XBBBBBB..... J000052............ (there corresponds with the value 02 of the file IN1)
...... xxxxxxxxxxx.... XBBBBBB..... J000066............ (there corresponds with the value 03 of the file IN1)

OUT1

XBBBBBB 02 01 03 06
(only 1 record. The first one 02 is the meter of all the times he appears '01; the meter of all the times is 01 it appears '02; the meter of all the times is 03 it appears '03; 06 there is the sum of three values)

It is as if the file IN1 was loaded in an internal table and only he was reading the file IN2 to look for equivalence.

Since you can see this second part it is difficult to explain but it seems simple. Since I have commented on the program cobowl it works but I would like to be able to have it in JCL to see if it is possible of doing. If you should need some another clarification or some example do not hesitate to comment to me on it.

Thanks a lot. Attentively
Pulcinella
pulcinella
 
Posts: 114
Joined: Mon Dec 10, 2007 10:18 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Matching with three output record

Postby Frank Yaeger » Fri Jan 28, 2011 4:26 am

According to your description, I think the output you actually want in OUT1 is:

XAAAAAA010001002
XBBBBBB020103006


Here's the DFSORT job I think you need:

//S1 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//IN1 DD DSN=...  input file1 (FB/9)
//IN2 DD DSN=...  input file2 (FB/250)
//OUT DD DSN=...  output file (FB/16)
//SYSIN DD *
  JOINKEYS F1=IN1,FIELDS=(1,7,A)
  JOINKEYS F2=IN2,FIELDS=(8,7,A)
  REFORMAT FIELDS=(F2:1,7,F1:10,4)
  SORT FIELDS=(1,7,CH,A)
  OUTFIL FNAMES=OUT,REMOVECC,NODETAIL,
    BUILD=(16X),
    SECTIONS=(1,7,
      TRAILER3=(1,7,
        TOT=(8,1,ZD,EDIT=(TT)),
        TOT=(9,1,ZD,EDIT=(TT)),
        TOT=(10,1,ZD,EDIT=(TT)),
        TOT=(11,1,ZD,EDIT=(TTT))))
/*
//JNF1CNTL DD *
  INREC IFTHEN=(WHEN=INIT,OVERLAY=(10:C'0001')),
    IFTHEN=(WHEN=(8,2,CH,EQ,C'01'),OVERLAY=(10:C'1')),
    IFTHEN=(WHEN=(8,2,CH,EQ,C'02'),OVERLAY=(11:C'1')),
    IFTHEN=(WHEN=(8,2,CH,EQ,C'03'),OVERLAY=(12:C'1'))
/*
//JNF2CNTL DD *
  INREC BUILD=(1:58,7,8:69,7)
/*
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
User avatar
Frank Yaeger
Global moderator
 
Posts: 1079
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 15 times

Re: Matching with three output record

Postby pulcinella » Fri Jan 28, 2011 7:57 pm

Hello Frank.

Great!!! One thousand million of thanks for the first example. It works correctly. Nevertheless I have been a case that it would be necessary to contemplate. It is the following one: if a record has in the CAMPO5 '0049 and the following record (the field CAMPO3 equal) has in the CAMPO5 another value (for example '0068') proves that he writes it to me two times: one in the file OK and other in erroneous file. I need that if he writes it to me in the file of records OK, he does not write it to me again in the file of erroneous records since I have to send the erroneous file to a person for his study.

it is necessary to bear in mind that 0049 it cannot come in the first record but it can come in the second, third one, etc (depending on the times that repeat the records). This is necessary to contemplate it somehow so that if it finds 0049 in any record (any time that the CAMPO3 is the same) he writes it and the rest ignores them (independently of the record where it is 0049)

Thank you very much for your help
pulcinella
 
Posts: 114
Joined: Mon Dec 10, 2007 10:18 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Matching with three output record

Postby Frank Yaeger » Sat Jan 29, 2011 1:25 am

In my original explanation of the first job, I said:

Input file1 is already sorted by the CAMPO1 field and does not have any duplicate records.

Input file2 is already sorted by the CAMP03 field and does not have any duplicate records.


Are you now saying that file2 can have duplicate records on the CAMPO3 field? If so, that changes things.

Here's the input files I used to test the first example:

Input file1
A00000001A...ARR     
A00000002B...BSS     
A00000004C...CTT     
B00000002D...DUU     
B00000003E...EVV     
B00000004E...FWW     

Input file2

AAAAAAXA00000001...0049XX   
BBBBBBXA00000002...0016XX   
CCCCCCXA00000003...0035XX   
DDDDDDXA00000004...0049XX   
EEEEEEXB00000001...0049XX   
FFFFFFXB00000003...0028XX   


Please show records for the additional input file variations you want the job to deal, with and show the expected records in the three output files. With a complete example, I can give you a complete solution.
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
User avatar
Frank Yaeger
Global moderator
 
Posts: 1079
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 15 times

Re: Matching with three output record

Postby pulcinella » Sat Jan 29, 2011 3:15 pm

Frank excuses.

I am sorry about it enormously. I do not try to justify myself but, when I answered you the first time, the file had no copies but they changed it to me on Friday morning. It seems that there will not be changes of last hour.

Now it turns out that the CAMPO3 has duplicated but NOT the rest of fields, that is to say, CAMPO3 will be able to be equal but for this case I will not have copies not in CAMPO1 not in CAMPO5. It could have copies in the fields: CAMPO2, CAMPO5, CAMPO6 but they are not important for me comparative. That is to say:

3 possible cases

(IN1)

A00000001A... ARR (the first case)
A00000002B... BSS (the second case)
B00000003E... EVV (third case)
A00000004C... CTT (the first case)

(IN2)

AAAAAAXA00000001... 0049XX
BBBBBBXA00000001... 0067xx
AAAAAAXA00000002... 0008xx
CCCCCCXA00000002... 0011xx
DDDDDDXA00000002... 0015xx
EEEEEEXA00000002... 0019xx
CCCCCCXA00000004... 0023xx
EEEEEEXA00000004... 0034xx
RRRRRRXA00000004... 0049xx
TTTTTTXA00000004... 0069xx
VVVVVVXA00000004... 0078xx

(OUT1)

A00000001A... ARRAAAAAA (the first record)
A00000004C... CTTTTTTTT (ninth record)

(OUT2)

A00000002B... BSSERROR0008
A00000002B... BSSERROR0011
A00000002B... BSSERROR0015
A00000002B... BSSERROR0019

(OUT3)

B00000003E... EVVERRORNOTFND

If you should not understand ask me again. The idea is clear in the head but on having written, I can be wrong

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

Re: Matching with three output record

Postby Frank Yaeger » Tue Feb 01, 2011 4:17 am

A few questions to make sure I understand what you want.

Question 1

IN1
A00000001A... ARR (the first case)
A00000002B... BSS (the second case)
B00000003E... EVV (third case)
A00000004C... CTT (the first case)

Can the records actually be out of order this way (A, A, B, A in position 1), or will they be in order like this (A, A, A, B in position 1)?

A00000001A... ARR (the first case)
A00000002B... BSS (the second case)
A00000004C... CTT (the first case)
B00000003E... EVV (third case)

Question 2

OUT2
A00000004C... CTTTTTTTT (ninth record)

Should this be:

A00000004C... CRRRRRRRR (ninth record)

That is, you would get the Rs from the 0049 record rather than the Ts from the 0069 record.

Question 3. Is it possible to have two or more 0049 records with the same CAMPO3 field, e.g.

EEEEEEXA00000005..0034xx
RRRRRRXA00000005..0049xx
TTTTTTXA00000005..0069xx
VVVVVVXA00000005..0049xx

If so, what would you want in the output data sets for this set of records - the first 0049 record? the last 0049 record? both 0049 records?
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
User avatar
Frank Yaeger
Global moderator
 
Posts: 1079
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 15 times

Re: Matching with three output record

Postby pulcinella » Tue Feb 01, 2011 5:28 am

Answer question1 --> Are you reason. They Will be in order like this (A, A, A, B in position 1). This is order by 9 positions. Then B00000003 cannot be between A00000002 and A00000004. First A00000002, after A00000004 and then B00000003... EXCUSE ME!!!

Answer question2 --> Reason again. Must be A00000004C... CRRRRRRRR (ninth record)... EXCUSE ME AGAIN!!!

Answer question3 --> It must not be possible but in any case I think that is an incongruence and must be verified more quickly. If you can write all records (that this question3) at OUT3 with message ERROR DUPLICATE (for example) is the best solution for me. (If it's not possible I write only the first case at OUT1)

Excuse me but When i put the example was be very later and I cannot see the error

Thanks again
Pulcinella
pulcinella
 
Posts: 114
Joined: Mon Dec 10, 2007 10:18 pm
Has thanked: 0 time
Been thanked: 0 time

Next

Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post