Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Sort by ICETOOL

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
Prasanthhere

Active User


Joined: 03 Aug 2005
Posts: 306

PostPosted: Mon Jul 17, 2006 3:38 pm    Post subject: Sort by ICETOOL
Reply with quote

My requirement is like this

I have 2 files FILE1 and FIle2 both of 80 in length and the key fields are the first 12 bytes.I want to compare the file1 with file2 and those records which differ in keyfields that is the first 12 should come in a dsn as inserted records and those records which are both in file1 and file2 but which differ in fields other than the keyfields should come as updated records in another DSN can u suggest a sorting method using ICETOOL

Thanks in advance
Back to top
View user's profile Send private message

Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Mon Jul 17, 2006 8:23 pm    Post subject: Re: Sort by ICETOOL
Reply with quote

See the Smart DFSORT Trick "Create files with matching and non-matching records (without and with duplicates)" at:

http://www.ibm.com/servers/storage/support/software/sort/mvs/tricks/

If you need more specific help, please show an example of the records in each input file and the expected output. If input file1 has duplicate records, show that in the example. If input file2 has duplicate records, show that in the example. Be as specific as possible.
Back to top
View user's profile Send private message
Prasanthhere

Active User


Joined: 03 Aug 2005
Posts: 306

PostPosted: Tue Jul 18, 2006 8:56 am    Post subject: Re: Sort by ICETOOL
Reply with quote

File1 and file2 have Duplicates

Suppose the file1 contents are like this

000000000001 A
000000000002 B
000000000003 C
000000000004 D
000000000005 E

and Suppose the file1 contents are like this

000000000006 A
000000000002 B
000000000003 D
000000000004 G
000000000008 E

the keyfields are first 12 in length and based pn this i want to generate 2 imput files with the following output

Inserted file should contain the following records
000000000006 A
000000000008 E
Updated file should contain the following records
000000000003 D
000000000004 G

i am comparing file2 with file1 records which are common in both file1 abnd file 2 gets eliminated

hope this is clear
Thanks in advance
Back to top
View user's profile Send private message
Prasanthhere

Active User


Joined: 03 Aug 2005
Posts: 306

PostPosted: Tue Jul 18, 2006 6:01 pm    Post subject: Re: Sort by ICETOOL
Reply with quote

Hi
Will be more preicse about the requirement.I have 2 files FIle1 and file2.File1 is the older one file and file2 is the newer version.

I am Comparing the file2 with file1

The condition is
A)The records which are common to both file1 and file2 need to be eliminted
B)IF the key is only 12 in length starting from position one and filelength is 80,So whatever records which are differing which have the same key in file1 and file2 and which differ only from position 13-80 need to created in a seperate DSN as updated records
c) the records which differ are in file2 and which differ in key field with file1 should come as inserted records
d)the records which differ are in file1 and which differ in key field with file2 should come as Deleted records

EG

File1
000000000001 AB
000000000002 A2
000000000003 A3
000000000004 A4
000000000005 A5
000000000011 A5
000000000012 A5
File1
000000000001 AD
000000000002 A2
000000000003 B3
000000000004 A4
000000000009 A5
000000000006 A6

And the final o/p file should be

Updated file o/p1

000000000001 AD
000000000003 B3

o/p2(inserted records)

000000000006 A6
000000000009 A5

o/p3(deleted records)

000000000011 A5
000000000012 A5


Hope this is clear


Thanks in advance
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Tue Jul 18, 2006 8:46 pm    Post subject:
Reply with quote

Well, assuming that o/p3 (deleted records) should also have the 000000000005 A5 record, I believe I understand what you want, and this DFSORT/ICETOOL job will do it:

Code:

//S1 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN1 DD DSN=...  input file1
//IN2 DD DSN=...  input file2
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//T2 DD DSN=&&T2,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS)
//UPDATE DD DSN=...  output file1
//INSERT DD DSN=...  output file2
//DELETE DD DSN=...  output file3
//TOOLIN DD *
COPY FROM(IN1) TO(T1) USING(CTL1)
COPY FROM(IN2) TO(T1) USING(CTL2)
SELECT FROM(T1) TO(T2) ON(1,80,CH) NODUPS
SPLICE FROM(T2) TO(UPDATE) ON(1,12,CH) -
  KEEPNODUPS WITH(1,81) USING(CTL3)
/*
//CTL1CNTL DD *
  INREC OVERLAY=(81:C'11')
/*
//CTL2CNTL DD *
  INREC OVERLAY=(81:C'22')
/*
//CTL3CNTL DD *
  OUTFIL FNAMES=UPDATE,INCLUDE=(81,2,CH,EQ,C'21'),
    BUILD=(1,80)
  OUTFIL FNAMES=INSERT,INCLUDE=(81,2,CH,EQ,C'22'),
    BUILD=(1,80)
  OUTFIL FNAMES=DELETE,INCLUDE=(81,2,CH,EQ,C'11'),
    BUILD=(1,80)
/*
Back to top
View user's profile Send private message
Prasanthhere

Active User


Joined: 03 Aug 2005
Posts: 306

PostPosted: Wed Jul 19, 2006 12:48 pm    Post subject: Re: Sort by ICETOOL
Reply with quote

IF we are giving the INREC OVERLAY=(81:C'11') does it adds a header '11' from the 81byte onwards for file 1 and '22' as a header for the File2 records.How does it SPLICE the records again to get updated records as '21'inserted as 22 and deleted as 11 .Could you share your thoughts on this
Back to top
View user's profile Send private message
Prasanthhere

Active User


Joined: 03 Aug 2005
Posts: 306

PostPosted: Wed Jul 19, 2006 6:29 pm    Post subject: Re: Sort by ICETOOL
Reply with quote

To be more clear if i am having an 2 files

I/P file1

000000000001 AB
000000000002 AC
000000000003 AB

I/P file2

000000000001 AB
000000000002 AD
000000000004 AB

In the first step using CTL1

000000000001 AB 11
000000000002 AC 11
000000000003 AB 11

In the second step using CTL2

000000000001 AB 11
000000000001 AB 22
000000000002 AC 11
000000000002 AD 22
000000000003 AB 11
000000000004 AB 22

Third it will eliminates the duplicates


In the preceding steps how its splices the 2byte records ie,what is the procedure in splicing so that it gets 21 ,11,22 records

Hope this is clear
thanks in advance
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Wed Jul 19, 2006 8:52 pm    Post subject:
Reply with quote

WITH(1,81) tells DFSORT to splice positions 1-81 from the second record with each key (overlay record) and position 82 from the first record with each key (base record). So for the first pair of records with the same key:

000000000001 AB ... 11
000000000001 AB ... 22

we splice

000000000001 AB ... 2

in positions 1-81 from the second record and the 1 in position 82 from the first record to get:

000000000001 AB ... 21

For records that only appear in file1, no splicing occurs and the id will be 11. KEEPNODUPS ensures we keep these non-dup records.

For records that only appear in file2, no splicing occurs and the id will be 22. KEEPNODUPS ensures we keep these non-dup records.

For complete information on the SPLICE operator of DFSORT's ICETOOL, see Chapter 6 of "z/OS DFSORT Application Programming Guide". You can access all of the DFSORT books online from:

www.ibm.com/servers/storage/support/software/sort/mvs/srtmpub.html
Back to top
View user's profile Send private message
Prasanthhere

Active User


Joined: 03 Aug 2005
Posts: 306

PostPosted: Thu Jul 20, 2006 12:00 pm    Post subject: Re: Sort by ICETOOL
Reply with quote

Thanks a Lot Frank Yeager icon_biggrin.gif
Back to top
View user's profile Send private message
Prasanthhere

Active User


Joined: 03 Aug 2005
Posts: 306

PostPosted: Wed Aug 16, 2006 6:15 pm    Post subject: Re: Sort by ICETOOL
Reply with quote

Hi
My code is like this .
My key field is from position 1-3
Code:

//S1 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN1 DD DSN=...  input file1
//IN2 DD DSN=...  input file2
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//T2 DD DSN=&&T2,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS)
//UPDATE DD DSN=...  output file1
//INSERT DD DSN=...  output file2
//DELETE DD DSN=...  output file3
//TOOLIN DD *
COPY FROM(IN1) TO(T1) USING(CTL1)
COPY FROM(IN2) TO(T1) USING(CTL2)
SELECT FROM(T1) TO(T2) ON(1,80,CH) NODUPS
SPLICE FROM(T2) TO(UPDATE) ON(1,3,CH) -
  KEEPNODUPS WITH(1,81) USING(CTL3)
/*
//CTL1CNTL DD *
  INREC OVERLAY=(81:C'11')
/*
//CTL2CNTL DD *
  INREC OVERLAY=(81:C'22')
/*
//CTL3CNTL DD *
  OUTFIL FNAMES=UPDATE,INCLUDE=(81,2,CH,EQ,C'21'),
    BUILD=(1,80)
  OUTFIL FNAMES=INSERT,INCLUDE=(81,2,CH,EQ,C'22'),
    BUILD=(1,80)
  OUTFIL FNAMES=DELETE,INCLUDE=(81,2,CH,EQ,C'11'),
    BUILD=(1,80)
/*

I am comparing file1 with file2
File1 has the records like this

001Imhere
002Iamhere

File2 has records like this

001Iamhere
003Iamhere

When i ran the JCl I am getting o/p as

Inserted
003Iamhere

Deleted
002Iamhere

But the updated file is coming as blank instead of the expected o/p as

001Imhere
(Since it changed in position other than from 1-3)

Could you please share your thoughts on this?
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Wed Aug 16, 2006 10:54 pm    Post subject:
Reply with quote

Oh, I see what happened. The SELECT ON(1,80,CH) reversed the IN1 and IN2 records resulting in:

Code:

001Iamhere          22
001Imhere           11
002Iamhere          11
003Iamhere          22


instead of the expected

Code:

001Imhere           11
001Iamhere          22
002Iamhere          11
003Iamhere          22


so the spliced 001 record had a 12 id instead of a 21 id and was not included.

We need to add a SORT statement for the SPLICE operator to ensure that the file1 records come before the file2 records.

Here's the revised version of your DFSORT/ICETOOL job:

Code:

//S1 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN1 DD DSN=...  input file1
//IN2 DD DSN=...  input file2
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS) <--- MOD
//T2 DD DSN=&&T2,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS)  <--- not MOD
//UPDATE DD DSN=...  output file1
//INSERT DD DSN=...  output file2
//DELETE DD DSN=...  output file3
//TOOLIN DD *
COPY FROM(IN1) TO(T1) USING(CTL1)
COPY FROM(IN2) TO(T1) USING(CTL2)
SELECT FROM(T1) TO(T2) ON(1,80,CH) NODUPS
SPLICE FROM(T2) TO(UPDATE) ON(1,3,CH) -
  KEEPNODUPS WITH(1,81) USING(CTL3)
/*
//CTL1CNTL DD *
  INREC OVERLAY=(81:C'11')
/*
//CTL2CNTL DD *
  INREC OVERLAY=(81:C'22')
/*
//CTL3CNTL DD *
  SORT FIELDS=(1,3,CH,A,81,2,CH,A)    <------ added
  OUTFIL FNAMES=UPDATE,INCLUDE=(81,2,CH,EQ,C'21'),
    BUILD=(1,80)
  OUTFIL FNAMES=INSERT,INCLUDE=(81,2,CH,EQ,C'22'),
    BUILD=(1,80)
  OUTFIL FNAMES=DELETE,INCLUDE=(81,2,CH,EQ,C'11'),
    BUILD=(1,80)
/*
Back to top
View user's profile Send private message
Prasanthhere

Active User


Joined: 03 Aug 2005
Posts: 306

PostPosted: Thu Aug 17, 2006 3:55 pm    Post subject: Re: Sort by ICETOOL
Reply with quote

HI Frank

Thanks a lot for the help ...............

I have one more doubt

IN the above code If i want to have the record which is updated in the first also also need to be included in the deleted file

Like

File

001Iamhere
002Iamhere
004Iamhere

File2

001Iamher
003Iamhere
004Iamhere

So the inserted file should contain

003Iamhere

The updated file should contain

001Iamher (since the record changed in position other than 1-3)

and the deleted should contain both the updated records in file1 as well as the deleted records from file1

001Iamhere
002Iamhere

the key field is fron 1-3

Hope this is clear


Could you please share your thoughts on this one to.
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Thu Aug 17, 2006 8:10 pm    Post subject:
Reply with quote

That's easy. Just change the OUTFIL statement for DELETE to:

Code:

   OUTFIL FNAMES=DELETE,INCLUDE=(81,2,SS,EQ,C'11,21'),   
     BUILD=(1,80)                                         
Back to top
View user's profile Send private message
Prasanthhere

Active User


Joined: 03 Aug 2005
Posts: 306

PostPosted: Fri Aug 18, 2006 1:13 pm    Post subject: Re: Sort by ICETOOL
Reply with quote

Hi

Thanks for the above solution

The solution above i tried before posting

But the actual Scenario is If we include a condition like that it will give the record in file2 as deleted record. But i want the record from file1 that got updated as when compared to file2 as updated record For example


File1

001Iamhere
002Iamhere
004Iamhere

File2

001Iamher
003Iamhere
004Iamhere

so the inserted file should contain

003Iamhere

The updated file should contain

001Iamher (since the record changed in position other than 1-3)

and the deleted should contain both the updated records in file1 as well as the deleted records from file1

001Iamhere(this is the record from file1 and not from file2)
002Iamhere

If we include the condition as u specified it will produce a deleted records file as

001Iamher (Instead of the record from file1 which is 001Iamhere)
002Iamhere


Could u please share your views on this one
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Fri Aug 18, 2006 10:00 pm    Post subject:
Reply with quote

Oh, I see what you want. Just add KEEPBASE. So the DFSORT/ICETOOL job would be:

Code:

//S1 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN1 DD DSN=...  input file1
//IN2 DD DSN=...  input file2
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//T2 DD DSN=&&T2,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS) 
//UPDATE DD DSN=...  output file1
//INSERT DD DSN=...  output file2
//DELETE DD DSN=...  output file3
//TOOLIN DD *
COPY FROM(IN1) TO(T1) USING(CTL1)
COPY FROM(IN2) TO(T1) USING(CTL2)
SELECT FROM(T1) TO(T2) ON(1,80,CH) NODUPS
SPLICE FROM(T2) TO(UPDATE) ON(1,3,CH) -
  KEEPNODUPS WITH(1,81) USING(CTL3) KEEPBASE   <---
/*
//CTL1CNTL DD *
  INREC OVERLAY=(81:C'11')
/*
//CTL2CNTL DD *
  INREC OVERLAY=(81:C'22')
/*
//CTL3CNTL DD *
  SORT FIELDS=(1,3,CH,A,81,2,CH,A)   
  OUTFIL FNAMES=UPDATE,INCLUDE=(81,2,CH,EQ,C'21'),
    BUILD=(1,80)
  OUTFIL FNAMES=INSERT,INCLUDE=(81,2,CH,EQ,C'22'),
    BUILD=(1,80)
  OUTFIL FNAMES=DELETE,INCLUDE=(81,2,CH,EQ,C'11'),    <-----
    BUILD=(1,80)
/*
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Innovations in Your Mainframe Sort Arun Raj PC Guides & IT News 4 Wed Mar 15, 2017 10:38 pm
No new posts SORT VSAM file with each field one by... maxsubrat DFSORT/ICETOOL 6 Tue Mar 14, 2017 1:07 pm
No new posts SORT requirement sjiraga COBOL Programming 6 Thu Mar 09, 2017 9:11 pm
No new posts SORT to append Month and YEAR in MMYY... tisamf DFSORT/ICETOOL 1 Wed Mar 08, 2017 4:46 pm
No new posts Converting decimal to numeric using I... Balaryan DFSORT/ICETOOL 4 Thu Feb 02, 2017 11:27 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us