View previous topic :: View next topic
|
Author |
Message |
vasanthanc
New User
Joined: 01 Apr 2005 Posts: 58
|
|
|
|
Please tell me how to use OUTFIL with INCLUDE to compare two datasets and create a new dataset with changed records. |
|
Back to top |
|
|
Frank Yaeger
DFSORT Developer
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
|
|
|
|
You need to give more details of what you're trying to do. Show an example of the records in each input file and what you want the output records to look like. Give the RECFM and LRECL of each input file. Give the starting position, length and format of each relevant field. |
|
Back to top |
|
|
vasanthanc
New User
Joined: 01 Apr 2005 Posts: 58
|
|
|
|
The record format is as below.
YEAR N 4
MONTH A 3
Region-CODE A 2
DEALER-CODE A 7
QTY N 2
TYPE A 3
This data is extracted on monthly basis and is stored in dataset in the above format. Once the new data is extracted, I need to compare that with the previous month extract. If there is any new record or change in values of any record needs to be moved to a different dataset. Hope you are clear with my requirement. |
|
Back to top |
|
|
Frank Yaeger
DFSORT Developer
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
|
|
|
|
Quote: |
Hope you are clear with my requirement. |
No, I'm not. I don't know what the field definitions you show mean. Does A 3 mean 3 characters? Does N 2 mean 2-byte numeric? What format? BI, PD, ZD?
What exactly is it you're comparing in the two files? Are you comparing record 1 in file 1 to record 1 in file 2, or a record with a particular field in file 1 with a record with the same field in file 2, or what? We don't know anything about your data or the result you're trying to get, so you need to be very specific about what you're trying to do to help others understand your requirement.
Please provide the information I asked for previously:
Quote: |
Show an example of the records in each input file and what you want the output records to look like. Give the RECFM and LRECL of each input file. Give the starting position, length and format of each relevant field. |
|
|
Back to top |
|
|
vasanthanc
New User
Joined: 01 Apr 2005 Posts: 58
|
|
|
|
I am sorry for the confusion. My requirement is very simple. I do have two flat files of same structure. The record length is 23 characters. Both the flat files are extracted from same database file but at different time. One was extracted this month and the other last month. I need to find the changed records and new records. All new records and the records which have changed values should be stored in another flat file with same structure.
Record structure is as below
YEAR (1-4)
MONTH (5-7)
Region-CODE (8-9)
DEALER-CODE (10-16)
QTY (17-18)
TYPE (19-21)
Sample File (Flat file extracted last month)
-------------
2006JAN01123456710LDD
2006FEB01123456708LDD
2006JAN01123456805HDD
2006FEB01123456807HDD
Sample File (Flat file extracted this month)
-------------
2006JAN01123456710LDD
2006FEB01123456708LDD
2006JAN01123456806HDD
2006FEB01123456807HDD
2006JAN01123456906HDD
2006FEB01123456907HDD
In the above file 3rd record have the value changed and the last two new records added. So the output file should have these three records
Output file expected
-----------------------
2006JAN01123456806HDD
2006JAN01123456906HDD
2006FEB01123456907HDD
Comparison needs to be on whole record. Need not to be on field level. Any single character change in a record should be considered.
Pls tell me how to do this? |
|
Back to top |
|
|
Frank Yaeger
DFSORT Developer
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
|
|
|
|
It looks like you want the file2 records that are not in file1, and you want them in their original order. Here's a DFSORT/ICETOOL job for that:
Code: |
//S1 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN1 DD DSN=... input file1 (FB/21)
//IN2 DD DSN=... input file2 (FB/21)
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//T2 DD DSN=&&T2,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//OUT DD DSN=.. output file (FB/21)
//TOOLIN DD *
* IN1->T1: Add blanks in 22-29 to identify file1 records.
COPY FROM(IN1) TO(T1) USING(CTL1)
* IN2->T1: Add seqnum in 22-29 to identify file2 records
* and to let us get the selected records back in their
* original order.
COPY FROM(IN2) TO(T1) USING(CTL2)
* T1->T2: Get non-matching file1/file2 records.
* Select those from file2.
SELECT FROM(T1) TO(T2) ON(1,21,BI) NODUPS USING(CTL3)
* T2->OUT: Use seqnum to sort the selected records into their
* original order. Remove seqnum.
SORT FROM(T2) TO(OUT) USING(CTL4)
/*
//CTL1CNTL DD *
INREC OVERLAY=(22:8X)
/*
//CTL2CNTL DD *
INREC OVERLAY=(22:SEQNUM,8,ZD)
/*
//CTL3CNTL DD *
OUTFIL FNAMES=T2,INCLUDE=(22,1,CH,NE,C' ')
/*
//CTL4CNTL DD *
SORT FIELDS=(22,8,ZD,A)
OUTREC BUILD=(1,21)
/*
|
|
|
Back to top |
|
|
manishram1
New User
Joined: 23 Mar 2006 Posts: 34
|
|
|
|
Hi,
Awesome Frank,the above JCL satisfied one of my requirement also.
Regards,
Manivelan P,
Mumbai |
|
Back to top |
|
|
vasanthanc
New User
Joined: 01 Apr 2005 Posts: 58
|
|
|
|
Thanks Frank |
|
Back to top |
|
|
|