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
 

 

convert sort to dfsort

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
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    Post subject: convert sort to dfsort
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

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Sep 24, 2008 11:29 am    Post subject:
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    Post subject: Reply to: convert sort to dfsort
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

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Sep 24, 2008 11:41 am    Post subject:
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    Post subject: Reply to: convert sort to dfsort
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    Post subject: Reply to: convert sort to dfsort
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    Post subject: Reply to: convert sort to dfsort
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

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Sep 24, 2008 8:08 pm    Post subject:
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    Post subject: Reply to: convert sort to dfsort
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    Post subject: Reply to: convert sort to dfsort
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    Post subject: Reply to: convert sort to dfsort
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    Post subject: Reply to: convert sort to dfsort
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: 2163
Location: @my desk

PostPosted: Fri Sep 26, 2008 5:03 pm    Post subject:
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    Post subject: Reply to: convert sort to dfsort
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    Post subject: Reply to: convert sort to dfsort
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    Post subject:
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 Moderator


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

PostPosted: Wed Nov 18, 2009 3:28 am    Post subject:
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:

http://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    Post subject: Reply to: convert sort to dfsort
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    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 Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm
No new posts How to convert the VBM file to VB or... Sulabh Agrawal JCL & VSAM 4 Fri Nov 18, 2016 1:04 pm
No new posts Convert +9999999999999.99 to S9(12)V9(3) vnktrrd DFSORT/ICETOOL 8 Thu Nov 17, 2016 8:15 pm
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 convert PD to ZD ram_vizag DFSORT/ICETOOL 5 Sat Nov 05, 2016 3:06 am


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