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
 

 

Query regarding comparision using ICETOOL.
Goto page 1, 2  Next
 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
bhoja

New User


Joined: 31 Jan 2006
Posts: 49

PostPosted: Wed May 30, 2007 2:15 pm    Post subject: Query regarding comparision using ICETOOL.
Reply with quote

Hi,
I have one querry regarding comparision using ICETOOL.
There are 2 files(fileA and fileB), need to compare it.
There is a field run-date in both the files. While comparing, if the record of fileA is not present in fileB and if the run-date is less than 21 days then only, we need to move this record to fileC.

Raj
Back to top
View user's profile Send private message

murmohk1

Senior Member


Joined: 29 Jun 2006
Posts: 1439
Location: Bangalore,India

PostPosted: Wed May 30, 2007 2:24 pm    Post subject:
Reply with quote

Raj,

Could you provide the rundate format (yyyymmdd etc...)?
Back to top
View user's profile Send private message
bhoja

New User


Joined: 31 Jan 2006
Posts: 49

PostPosted: Wed May 30, 2007 2:31 pm    Post subject:
Reply with quote

Murali,
it is yyyy-mm-dd
Back to top
View user's profile Send private message
murmohk1

Senior Member


Joined: 29 Jun 2006
Posts: 1439
Location: Bangalore,India

PostPosted: Wed May 30, 2007 2:49 pm    Post subject:
Reply with quote

Raj,

Download sorttrck.pdf from the following link -

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

The topic Create files with matching and non-matching records gives the records which are available in fileA only. In the next step run the above OP file against the following sort card -

Assumptions -

Your run date is between 1-10 columns.
LRECL=80; RECFM=FB

Code:
//SYSIN DD *
* put yyyy-mm-dd as yyyymmdd @ 81 col temp
  INREC OVERLAY=(81:1,4,   * YYYY
                6,2,   *MM
                9,2)   *DD
  SORT FIELDS=COPY
  INCLUDE COND=(81,8,CH,GT,DATE2-21)
  OUTREC BUILD=(1,80)
/*
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 May 30, 2007 7:51 pm    Post subject:
Reply with quote

Raj,

If you want a more specific solution:

Please give an example of the records in each input file and what you expect for output.

Please give the RECFM and LRECL of each input file.
Back to top
View user's profile Send private message
bhoja

New User


Joined: 31 Jan 2006
Posts: 49

PostPosted: Wed May 30, 2007 8:25 pm    Post subject:
Reply with quote

Murali,
Thanks a lot
I am modifying my JCL to the requirement. Once I complete I will let you know.

Raj
Back to top
View user's profile Send private message
bhoja

New User


Joined: 31 Jan 2006
Posts: 49

PostPosted: Thu May 31, 2007 12:19 pm    Post subject:
Reply with quote

Hi Murali,
Here I am sending you the the, Kindly look into this and tell me how to modify.

Code:

//ICE001 EXEC PGM=ICETOOL
//*
//TOOLMSG DD SYSOUT=*
//DFSMSG  DD SYSOUT=*
//ICEIN01 DD DSN=***************
//ICEIN02 DD DSN=****************
//TEMP01  DD DSN=&TEMP01,SPACE=(CYL,(5,5),RLSE),DISP=(,PASS)
//TEMP02  DD DSN=&TEMP02,SPACE=(CYL,(5,5),RLSE),DISP=(,PASS)
//INTR    DD DSN=*.TEMP01,DISP=(OLD,PASS),VOL=REF=*.TEMP01
//        DD DSN=*.TEMP02,DISP=(OLD,PASS),VOL=REF=*.TEMP02
//FILEA   DD DSN=*******************
//FILEB   DD DSN=*******************
//OUT     DD DSN=*******************
//TOOLIN  DD *
  SORT FROM(ICEIN01) USING(CTL1)
  SORT FROM(ICEIN02) USING(CTL2)
  SORT FROM(INTR) USING(CTL3)
//CTL1CNTL DD *
    SORT FIELDS=(1,21,CH,A)
    OUTFIL FNAMES=TEMP01,OUTREC=(1,107,C'1')
//CTL2CNTL DD *
    SORT FIELDS=(1,21,CH,A)
    OUTFIL FNAMES=TEMP02,OUTREC=(1,107,C'2')
//CTL3CNTL DD *
    SORT FIELDS=(1,21,CH,A)
    SUM FIELDS=(108,1,ZD)
    OUTFIL FNAMES=OUT,INCLUDE=(108,1,ZD,EQ,3),OUTREC=(1,107)
    OUTFIL FNAMES=FILEA,INCLUDE=(108,1,CH,EQ,C'1'),OUTREC=(1,107)
    OUTFIL FNAMES=FILEB,INCLUDE=(108,1,CH,EQ,C'2'),OUTREC=(1,107)
/*
//*
//

Now my requirementis,
There is a field Run-date(yyyy-mm-dd) in both the files ICEIN01 and ICEIN02. Now my requirementis a record found in both the files it
should have to write in file OUT. If the record of file ICEIN01,not present in file ICEIN02 and Run-date is less than 21days,
those records I need to write into file A, and the records present only in ICEIN02 has to write in file B.
The above code is doing the same thing except the run-date part. It is not taking into account the run-date.
Murali, can you please tell me how to modify this?
Back to top
View user's profile Send private message
murmohk1

Senior Member


Joined: 29 Jun 2006
Posts: 1439
Location: Bangalore,India

PostPosted: Thu May 31, 2007 12:34 pm    Post subject:
Reply with quote

Raj,

The above step extract all the records into 3 files (irrespective whether the run date falls with the 21 day range or not).

Quote:
It is not taking into account the run-date


Add the following step after ICE001 step.

Code:

//STEP2 EXEC PGM=SORT
//SORTIN DD DSN=FILEA from ICE001 step goes here
//SORTOUT DD DSN=......
//SYSOUT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSIN DD *
   COPY THE CONTROL CARD PROVIDED IN MY EARLIER POST WITH REQ CHANGES
/*


Hope this solves your problem.
Back to top
View user's profile Send private message
bhoja

New User


Joined: 31 Jan 2006
Posts: 49

PostPosted: Thu May 31, 2007 2:18 pm    Post subject: Re: Query regarding comparision using ICETOOL.
Reply with quote

Murali, I am getting the following error, while submiting the sort card

Code:

//S2 EXEC PGM=SORT
//SORTIN DD DSN=FILEA
//SORTOUT DD DSN=====
//SYSOUT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSIN DD *
  INREC OVERLAY=(108:1,4,   *YYYY
                 6,2,       *MM
                 9,2)       *DD
  SORT FIELDS=COPY
  INCLUDE COND=(108,8,CH,GT,DATE2-21)
  OUTREC BUILD=(1,107)


Error what I am getting is

Code:

ICE201I E RECORD TYPE IS F - DATA STARTS IN POSITION 1
Back to top
View user's profile Send private message
murmohk1

Senior Member


Joined: 29 Jun 2006
Posts: 1439
Location: Bangalore,India

PostPosted: Thu May 31, 2007 2:26 pm    Post subject:
Reply with quote

Raj,

Please post the complete error messages.
Back to top
View user's profile Send private message
bhoja

New User


Joined: 31 Jan 2006
Posts: 49

PostPosted: Thu May 31, 2007 2:34 pm    Post subject:
Reply with quote

This is the only error I am getting

Code:

ICE201I E RECORD TYPE IS F - DATA STARTS IN POSITION 1     
ICE027A 2 END OF          FIELD BEYOND MAXIMUM RECORD LENGTH
ICE751I 0 C5-K90007 C6-K90007 C7-K90000 C8-K90007 E7-K11698
ICE052I 3 END OF DFSORT                                     


Raj
Back to top
View user's profile Send private message
murmohk1

Senior Member


Joined: 29 Jun 2006
Posts: 1439
Location: Bangalore,India

PostPosted: Thu May 31, 2007 3:04 pm    Post subject:
Reply with quote

Bhoja,

Im very sorry. I got confused with the sort processing. Try this -

Code:
//STEP2 EXEC PGM=ICETOOL
//SYSOUT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//FILEA DD DSN=FILEA
//TEMPFILE DD DSN=TEMP.FILE,DISP=(MOD,CATLG,CATLG),
//        LRECL=115,RECFM=FB,............
//OUTFILE DD DSN=YOUR.OPFILE,.....
//SYSIN DD *
  COPY FROM(FILEA) TO(TEMPFILE) USING(FRST)
  COPY FROM(TEMPFILE) USING(SECD)
/*
//FRSTCNTL DD *
  INREC OVERLAY==(108:1,4, *YYYY
                                6,2, *MM
                                9,2) *DD
/*
//SECDCNTL DD *
   OUTFIL FNAMES=OUTFILE,
   INCLUDE=(108,8,CH,GT,DATE2-21),
   BUILD=(1,107)
/*
Back to top
View user's profile Send private message
bhoja

New User


Joined: 31 Jan 2006
Posts: 49

PostPosted: Thu May 31, 2007 3:29 pm    Post subject:
Reply with quote

Thank a lot. I will try this

Raj
Back to top
View user's profile Send private message
bhoja

New User


Joined: 31 Jan 2006
Posts: 49

PostPosted: Thu May 31, 2007 5:39 pm    Post subject:
Reply with quote

Hi Murali,
The above code is giving simply RC 20, not throwing any error and not getting the output

Raj
Back to top
View user's profile Send private message
murmohk1

Senior Member


Joined: 29 Jun 2006
Posts: 1439
Location: Bangalore,India

PostPosted: Thu May 31, 2007 5:52 pm    Post subject:
Reply with quote

Please post your spool messages.
Back to top
View user's profile Send private message
bhoja

New User


Joined: 31 Jan 2006
Posts: 49

PostPosted: Thu May 31, 2007 6:03 pm    Post subject:
Reply with quote

This is the only message I am getting
IEF142I YD27DN0 ICE002 - STEP WAS EXECUTED - COND CODE 0020
Back to top
View user's profile Send private message
murmohk1

Senior Member


Joined: 29 Jun 2006
Posts: 1439
Location: Bangalore,India

PostPosted: Thu May 31, 2007 6:12 pm    Post subject:
Reply with quote

In the sysout of step ICE002, you will get some messages (your control card etc...). Provide post those.
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 May 31, 2007 9:01 pm    Post subject:
Reply with quote

Quote:
If you want a more specific solution:

Please give an example of the records in each input file and what you expect for output.

Please give the RECFM and LRECL of each input file.


Bhoja,

Well, I offered to help, but I guess you weren't interested in my offer.

As to the reason why you're getting RC=20, it's because Murali mixed up DFSORT and ICETOOL JCL. He has:

Code:

//STEP2 EXEC PGM=ICETOOL
//SYSOUT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//FILEA DD DSN=FILEA
//TEMPFILE DD DSN=TEMP.FILE,DISP=(MOD,CATLG,CATLG),
//        LRECL=115,RECFM=FB,............
//OUTFILE DD DSN=YOUR.OPFILE,.....
//SYSIN DD *


It should be:

Code:

//STEP2 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//FILEA DD DSN=FILEA
//TEMPFILE DD DSN=TEMP.FILE,DISP=(MOD,CATLG,CATLG),
//        LRECL=115,RECFM=FB,............
//OUTFILE DD DSN=YOUR.OPFILE,.....
//TOOLIN DD *


If you want to continue to work with Murali, feel free. If you want my help, supply the information I asked for.
Back to top
View user's profile Send private message
bhoja

New User


Joined: 31 Jan 2006
Posts: 49

PostPosted: Fri Jun 01, 2007 11:45 am    Post subject:
Reply with quote

Hi,
I tried with TOOLIN DD * also, still I am getting the same RC 20 without showing any error message.

Raj
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 Jun 01, 2007 10:27 pm    Post subject:
Reply with quote

Sigh. Look at my post again. You also have to change:

//SYSOUT DD SYSOUT=*

to

//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
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
Goto page 1, 2  Next
Page 1 of 2

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts How to delete second instance from Fl... Gunapala CN DFSORT/ICETOOL 6 Tue Oct 18, 2016 11:42 pm
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm
No new posts ICETOOL/SYNCSORT - SYSPRINT output nartcr DFSORT/ICETOOL 17 Wed Oct 05, 2016 10:46 pm


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