IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

convert sort to dfsort
IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic Quick References
View previous topic :: View next topic  
Author Message
hikaps14

Active User


Joined: 02 Sep 2005
Posts: 189
Location: Noida
PostPosted: Wed Sep 24, 2008 11:19 am
 Reply with quote
Hi,

I am trying to convert SORT to ICETOOL,

This the original one:
//step EXEC PGM=SORT
//SORTJNF1 DD DSN=Input1,DISP=SHR
//*
//SORTJNF2 DD DSN=Input2,DISP=SHR
//*
//SORTOF01 DD DSN=Output,
// DISP=(NEW,CATLG,DELETE),
// UNIT=SYSDA,
// SPACE=(CYL,(20,50),RLSE),
// DCB=(RECFM=FB,LRECL=100,BLKSIZE=0)
//SORTWK01 DD UNIT=SYSDA,
// SPACE=(CYL,(200,200))
//SYSOUT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSABEND DD SYSOUT=*
//*
//SYSIN DD *
JOINKEYS FILE=F1,
FIELDS=(1,4,A)
JOINKEYS FILE=F2,
FIELDS=(1,4,A)
SORT FIELDS=COPY
REFORMAT FIELDS=(F1:01,50,F2:5,50)
OUTFIL FILES=01
END
*********************************************

New job:

//PS020 EXEC PGM=ICETOOL
//IN1 DD DSN=Input1,DISP=SHR
//IN2 DD DSN=Input2,DISP=SHR
//OUTP DD DSN=Output,
// DISP=(NEW,CATLG,DELETE),
// UNIT=SYSDA,
// SPACE=(CYL,(20,50),RLSE),
// DCB=(RECFM=FB,LRECL=100,BLKSIZE=0)
//TMP1 DD DSN=&&TEMP,DISP=(MOD,PASS),
// SPACE=(CYL,(10,10),RLSE),
// UNIT=SYSDA
//SORTWK01 DD UNIT=SYSDA,
// SPACE=(CYL,(200,200))
//SYSOUT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSABEND DD SYSOUT=*
//TOOLMSG DD SYSOUT=*
//DFHMSG DD SYSOUT=*
//SSMSG DD SYSOUT=*
//TOOLIN DD *
SORT FROM(IN1) TO(TMP1) USING(CP01)
SORT FROM(IN2) TO(TMP1) USING(CP02)
SPLICE FROM(TMP1) TO(OUTP) ON(1,4,CH) WITH(51,50) WITH(102,1)-
WITHALL USING(CP03)
/*
//CP01CNTL DD *
SORT FIELDS=(1,4,CH,A)
OUTREC BUILD=(1,50,101:C'11')
/*
//CP02CNTL DD *
SORT FIELDS=(1,4,CH,A)
OUTREC BUILD=(1,4,46X,5,50,101:C'22')
/*
//CP03CNTL DD *
OUTFIL FNAMES=OUTP,INCLUDE=(101,2,CH,EQ,C'12'),BUILD=(1,100)
/*


I am getting difference in both the output. The SORT job is returning me more records than ICETOOL job.
Although all the records present in ICETOOL o/p match perfectly with original outout, but no. of recs is the prob.

Could any one find out the difference b/w both the jobs.

Thanks in advance.
-Kapil.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
PostPosted: Wed Sep 24, 2008 11:29 am
 Reply with quote
Hello,

Please post the informational messages presented when you ran these jobs.

It appears you are using Syncsort not DFSORT.
Back to top
View user's profile Send private message
hikaps14

Active User


Joined: 02 Sep 2005
Posts: 189
Location: Noida
PostPosted: Wed Sep 24, 2008 11:33 am
 Reply with quote
SORT :

WER487I FILESIZE 39,000 BYTES
WER482I JNF2 STATISTICS

ICETOOL:
ICE084I 0 EXCP ACCESS METHOD USED FOR TMP1
ICE084I 0 EXCP ACCESS METHOD USED FOR IN1

I hope this helps.

Thanks.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
PostPosted: Wed Sep 24, 2008 11:41 am
 Reply with quote
Hello,

Well, you are on one of the few systems that has both DFSORT and Syncsort available icon_wink.gif

Have you done analysis to determine what records are tyhe "extra"s? Is the sort output the correct number of records or is the icetool output the correct number of records.

If this is not seen quickly, i'd suggest making some very small files to isolate which records cause the difference.
Back to top
View user's profile Send private message
hikaps14

Active User


Joined: 02 Sep 2005
Posts: 189
Location: Noida
PostPosted: Wed Sep 24, 2008 11:52 am
 Reply with quote
yes I am trying to find out any difference in the pattern of o/p records skipped in case of ICETOOL.

I am not an expert with SPLICE operator. So, are you able to find out any difference in sort cards of both the jobs.

Thanks.
Back to top
View user's profile Send private message
hikaps14

Active User


Joined: 02 Sep 2005
Posts: 189
Location: Noida
PostPosted: Wed Sep 24, 2008 12:58 pm
 Reply with quote
Hi,

I have found something.
Input File1 has duplicate keys. ICETOOL is ignoring those recs.

In case of SORT those duplicate keys are also getting into output.

Could anyone let me know how replicate same in ICETOOL.

Thanks.
Back to top
View user's profile Send private message
hikaps14

Active User


Joined: 02 Sep 2005
Posts: 189
Location: Noida
PostPosted: Wed Sep 24, 2008 1:39 pm
 Reply with quote
I have also tried reversing the order of input files. Still its not giving the desired output.

Both the input files has duplicates. I want all the matching records, can't skip any even if they are duplicate keys.

Thanks,
-Kapil.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
PostPosted: Wed Sep 24, 2008 8:08 pm
 Reply with quote
Hello,

I believe you want ALLDUPS in your splice.

There are examples right here in the DFSORT part of the forum.
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose
PostPosted: Wed Sep 24, 2008 10:34 pm
 Reply with quote
Quote:
Both the input files has duplicates. I want all the matching records, can't skip any even if they are duplicate keys.


hikaps14,

That would be a cartesian join ouput. Do you need records from both files ? What is the max number of duplicates for each key in each file?
Back to top
View user's profile Send private message
hikaps14

Active User


Joined: 02 Sep 2005
Posts: 189
Location: Noida
PostPosted: Thu Sep 25, 2008 3:07 pm
 Reply with quote
Hi,

yes, its a type of cartesian join I want. I have found that splice is not able to handle duplicates.

For time being I have interchaged the inout files. It's working fine for now. But I don't know if this will work in future.

What if both the files have duplicates.
The max no. of duplicate keys was around 10.

I am sure there must something in icetool to handle this scenario. It would be nice if someone could let me know how to proceed.

Thanks.
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose
PostPosted: Thu Sep 25, 2008 10:03 pm
 Reply with quote
The following DFSORT/ICETOOL JCl will give you the cartesian join results. This is coded to handle 10 duplicates in file 2 and the no: duplicates in file 1 can be any number.

Code:

//STEP0100 EXEC PGM=ICETOOL                                   
//TOOLMSG  DD SYSOUT=*                                         
//DFSMSG   DD SYSOUT=*                                         
//IN1      DD DSN=your input file1,
//            DISP=SHR
//IN2      DD dsn=your input file2,
//            DISP=SHR                                         
//T1       DD DSN=&&T1,DISP=(,PASS),SPACE=(CYL,(X,Y),RLSE)     
//T2       DD DSN=&&T2,DISP=(MOD,PASS),SPACE=(CYL,(X,Y),RLSE) 
//OUT      DD DSN=your output file,
//            DISP=(NEW,CATLG,DELETE),
//            UNIT=SYSDA,
//            SPACE=(CYL,(X,Y),RLSE)
/*
//TOOLIN   DD *                                                     
  SORT FROM(IN2) USING(CTL1)                                         
                                                                     
  SPLICE FROM(T1) TO(T2) ON(1,4,CH) KEEPNODUPS  -                   
  WITHEACH WITH(105,50) WITH(155,50)            -                   
  WITH(205,50) WITH(255,50) WITH(305,50)        -                   
  WITH(355,50) WITH(405,50) WITH(455,50) WITH(505,50)               
                                                                     
  COPY FROM(IN1) USING(CTL2)                                         
                                                                     
  SPLICE FROM(T2) TO(OUT) ON(1,4,CH) WITHALL WITH(01,54) USING(CTL3)
                                                                     
//CTL1CNTL DD *                                                     
  SORT FIELDS=(1,4,CH,A)                                           
  OUTREC IFTHEN=(WHEN=INIT,OVERLAY=(555:SEQNUM,2,ZD,RESTART=(1,4))),
  IFTHEN=(WHEN=(555,2,ZD,EQ,+01),BUILD=(1,4,050X,5,50)),           
  IFTHEN=(WHEN=(555,2,ZD,EQ,+02),BUILD=(1,4,100X,5,50)),           
  IFTHEN=(WHEN=(555,2,ZD,EQ,+03),BUILD=(1,4,150X,5,50)),           
  IFTHEN=(WHEN=(555,2,ZD,EQ,+04),BUILD=(1,4,200X,5,50)),           
  IFTHEN=(WHEN=(555,2,ZD,EQ,+05),BUILD=(1,4,250X,5,50)),           
  IFTHEN=(WHEN=(555,2,ZD,EQ,+06),BUILD=(1,4,300X,5,50)),           
  IFTHEN=(WHEN=(555,2,ZD,EQ,+07),BUILD=(1,4,350X,5,50)),           
  IFTHEN=(WHEN=(555,2,ZD,EQ,+08),BUILD=(1,4,400X,5,50)),           
  IFTHEN=(WHEN=(555,2,ZD,EQ,+09),BUILD=(1,4,450X,5,50)),           
  IFTHEN=(WHEN=(555,2,ZD,EQ,+10),BUILD=(1,4,500X,5,50))             
  OUTFIL FNAMES=T1,BUILD=(1,554)                                   
//CTL2CNTL DD *                                                     
  OUTFIL FNAMES=T2,BUILD=(1,54,554:X)                               
//CTL3CNTL DD *                                                   
  OUTFIL FNAMES=OUT,IFOUTLEN=104,                                 
  OMIT=(54,256,CH,EQ,C' ',AND,310,244,CH,EQ,C' '),               
  IFTHEN=(WHEN=(055,050,CH,GT,C' ',AND,                           
                105,256,CH,EQ,C' ',AND,361,193,CH,EQ,C' '),       
  BUILD=(1,104)),                                                 
  IFTHEN=(WHEN=(055,100,CH,GT,C' ',AND,                           
                155,256,CH,EQ,C' ',AND,411,143,CH,EQ,C' '),       
  BUILD=(1,104,/,1,54,105,50)),                                   
  IFTHEN=(WHEN=(055,150,CH,GT,C' ',AND,                           
                205,256,CH,EQ,C' ',AND,461,093,CH,EQ,C' '),       
  BUILD=(1,104,/,1,54,105,50,/,1,54,155,50)),                     
  IFTHEN=(WHEN=(055,200,CH,GT,C' ',AND,                           
                255,256,CH,EQ,C' ',AND,511,043,CH,EQ,C' '),       
  BUILD=(1,104,/,1,54,105,50,/,1,54,155,50,/,1,54,205,50)),       
  IFTHEN=(WHEN=(055,250,CH,GT,C' ',AND,305,249,CH,EQ,C' '),       
  BUILD=(1,104,/,1,54,105,50,/,1,54,155,50,/,1,54,205,50,/,       
         1,54,255,50)),                                           
  IFTHEN=(WHEN=(055,256,CH,GT,C' ',AND,311,44,CH,GT,C' ',AND,     
                355,199,CH,EQ,C' '),                             
  BUILD=(1,104,/,1,54,105,50,/,1,54,155,50,/,1,54,205,50,/,       
         1,54,255,50,/,1,54,305,50)),                             
  IFTHEN=(WHEN=(055,256,CH,GT,C' ',AND,311,94,CH,GT,C' ',AND,     
                405,149,CH,EQ,C' '),                             
  BUILD=(1,104,/,1,54,105,50,/,1,54,155,50,/,1,54,205,50,/,       
         1,54,255,50,/,1,54,305,50,/,1,54,355,50)),               
  IFTHEN=(WHEN=(055,256,CH,GT,C' ',AND,311,144,CH,GT,C' ',AND,   
                455,099,CH,EQ,C' '),                             
  BUILD=(1,104,/,1,54,105,50,/,1,54,155,50,/,1,54,205,50,/,       
         1,54,255,50,/,1,54,305,50,/,1,54,355,50,/,1,54,405,50)),
                               
  IFTHEN=(WHEN=(055,256,CH,GT,C' ',AND,311,194,CH,GT,C' ',AND,     
                505,049,CH,EQ,C' '),                               
  BUILD=(1,104,/,1,54,105,50,/,1,54,155,50,/,1,54,205,50,/,       
         1,54,255,50,/,1,54,305,50,/,1,54,355,50,/,1,54,405,50,/, 
         1,54,455,50)),                                           
  IFTHEN=(WHEN=(055,256,CH,GT,C' ',AND,311,243,CH,GT,C' '),       
  BUILD=(1,104,/,1,54,105,50,/,1,54,155,50,/,1,54,205,50,/,       
         1,54,255,50,/,1,54,305,50,/,1,54,355,50,/,1,54,405,50,/, 
         1,54,455,50,/,1,54,505,50))                               
/*
Back to top
View user's profile Send private message
hikaps14

Active User


Joined: 02 Sep 2005
Posts: 189
Location: Noida
PostPosted: Fri Sep 26, 2008 4:19 pm
 Reply with quote
Hi Skolusu,

I appreciate your efforts for writing sucha complex card for me.
Still I am looking for something simpler. This is too complex for me to understand. If I directly copy and paste this in my application then it will be hard to maintain this app in future.
:)

I mean as in syncsort sort joinkeys takes care of all the duplicates, can't we do anthing similar with ICETOOL.

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

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
PostPosted: Fri Sep 26, 2008 5:03 pm
 Reply with quote
hikaps14,

Just curious, why dont you implement this in Syncsort since you already have that installed? icon_rolleyes.gif
Back to top
View user's profile Send private message
hikaps14

Active User


Joined: 02 Sep 2005
Posts: 189
Location: Noida
PostPosted: Fri Sep 26, 2008 5:05 pm
 Reply with quote
We are shifting from syncsort to idfsort.
:)
Back to top
View user's profile Send private message
hikaps14

Active User


Joined: 02 Sep 2005
Posts: 189
Location: Noida
PostPosted: Fri Sep 26, 2008 7:14 pm
 Reply with quote
I just searched on google. I found something about icetool.

Icetool supports the below join types:
1-1
1-N

But I want to know if it can support
N-N joins.

As far as Syncsort is concerned, it supports all types of joins.

Could you any one comment on the above info. I looking for a many to many join with icetool.

Thanks.
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose
PostPosted: Tue Sep 30, 2008 3:41 am
 Reply with quote
hikaps14

Replied to your offline mail and the job should take care of your needs

Kolusu
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA
PostPosted: Wed Nov 18, 2009 3:28 am
 Reply with quote
DFSORT now supports JOINKEYS with z/OS DFSORT V1R5 PTF UK51706 or z/OS DFSORT V1R10 PTF UK51707 (Nov, 2009) and can do N-N joins.

For complete details on the JOINKEYS function and the other new functions available with the Nov, 2009 DFSORT PTF, see:

www.ibm.com/support/docview.w...d=isg3T7000174
Back to top
View user's profile Send private message
hikaps14

Active User


Joined: 02 Sep 2005
Posts: 189
Location: Noida
PostPosted: Wed Nov 18, 2009 7:46 pm
 Reply with quote
Thanks for the update Frank. I have always been a fan of dfsort. Gooi to hear about its new N-N join facility. I will try to explore it.

Thanks,
-Kapil.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DFSORT/ICETOOL

 

Similar Topics
Topic Forum Replies
No new posts Need to set RC4 through JCL SORT DFSORT/ICETOOL 5
No new posts How to split large record length file... DFSORT/ICETOOL 10
No new posts Modifying Date Format Using DFSORT DFSORT/ICETOOL 9
No new posts Replace Multiple Field values to Othe... DFSORT/ICETOOL 12
No new posts JCL sort card - get first day and las... JCL & VSAM 9
Search our Forums:

Back to Top
IBMMainframes.com is not an official and/or affiliated with IBM® in anyway
Board Rules | FAQ | Downloads | Wiki | SiteMap | Contact Us