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
 

 

Select some records using DFSORT

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

New User


Joined: 07 Jun 2007
Posts: 31
Location: Spain

PostPosted: Fri May 30, 2008 12:27 pm    Post subject: Select some records using DFSORT
Reply with quote

INPUT:

Code:

00001 I
00001 E
00001 I
00003 I
00003 E
00004 E
00004 I


OUTPUT:

Code:

00001 I
00001 E
00001 I
00004 E
00004 I



I just need in the output the recs that have a 'E' character, and this is not in the last rec for the recs with the same key.

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

Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Fri May 30, 2008 9:46 pm    Post subject:
Reply with quote

Quote:

I just need in the output the recs that have a 'E' character, and this is not in the last rec for the recs with the same key.


gabriel.ryoga,

Why haven't you picked 00003 records also which has an E key?
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Fri May 30, 2008 10:20 pm    Post subject: Reply to: Select some records
Reply with quote

You must take a decision icon_biggrin.gif ...
change the sample output or change the description

Quote:
I just need in the output the recs that have a 'E' character, and this is not in the last rec for the recs with the same key.


from the description the output should be
Code:

00001 E
00004 E


or if You want the description to match the output You posted You should have written
... output the record groups for which exists an E record which is not the last one of the group ...
Back to top
View user's profile Send private message
vvmanyam

New User


Joined: 16 Apr 2008
Posts: 86
Location: Bangalore

PostPosted: Wed Jun 04, 2008 6:55 pm    Post subject: Reply to: Select some records
Reply with quote

I have assumed that requrement as this :-
If the value 'E' is present for the last record of a block if same key
entire block of records will be neglected.
For this the jcl looks like this
Code:

//STEPS200 EXEC PGM=ICETOOL                                             
//TOOLMSG  DD SYSOUT=*                                                 
//DFSMSG   DD SYSOUT=*                                                 
//IN1      DD *                                                         
00001 I                                                                 
00001 E                                                                 
00001 I                                                                 
00003 I                                                                 
00003 E                                                                 
00004 E                                                                 
00004 I                                                                 
//*                                                                     
//TOOLIN   DD *                                                         
  SORT FROM(IN1) TO(OUT1) USING(CTL1)                                   
  SPLICE FROM(OUT1) TO(OUT2) ON(1,5,CH) -                               
   KEEPBASE WITHALL WITH(1,58)                                         
  SORT FROM(OUT2) TO(OUT3) USING(CTL2)                                 
  SPLICE FROM(OUT3) TO(OUT4) ON(1,5,CH) -                               
   KEEPBASE WITHALL WITH(1,66)                                         
  SORT FROM(OUT4) TO(OUT5) USING(CTL3)                                 
//CTL1CNTL DD *                                                               
  INREC OVERLAY=(51:SEQNUM,8,ZD,RESTART=(1,5),                                 
                 59:SEQNUM,8,ZD,RESTART=(1,5))                                 
  SORT FIELDS=(1,5,CH,A,51,8,ZD,D)                                             
//CTL2CNTL DD *                                                               
  INREC IFTHEN=(WHEN=(51,8,CH,EQ,59,8,CH,AND,7,1,CH,EQ,C'E'),                 
                OVERLAY=(67:C'N'))                                             
  SORT FIELDS=COPY                                                             
//*                                                                           
//CTL3CNTL DD *                                                               
  INCLUDE COND=(67,1,CH,NE,C'N')                                               
  SORT FIELDS=(1,5,CH,A,51,8,CH,A)                                             
  OUTREC BUILD=(1,50)                                                         
//*                                                                           
//OUTDD    DD DSN=&T1,UNIT=SYSDA,SPACE=(CYL,(1,1)),DISP=  (MOD,PASS)             
//OUT1     DD DSN=&T1,UNIT=SYSDA,SPACE=(CYL,(1,1)),DISP=(MOD,PASS)             
//OUT2     DD DSN=&T1,UNIT=SYSDA,SPACE=(CYL,(1,1)),DISP=(MOD,PASS)             
//OUT3     DD DSN=&T1,UNIT=SYSDA,SPACE=(CYL,(1,1)),DISP=(MOD,PASS)             
//OUT4     DD DSN=&T1,UNIT=SYSDA,SPACE=(CYL,(1,1)),DISP=(MOD,PASS)             
//OUT5      DD  DSN=...... ,DISP=(,CATLG,DELETE),                       
//            SPACE=(50,(10,5),RLSE),AVGREC=K,                               
//            LRECL=50,RECFM=FB                                           


This is a simple one but with more data passes
probably frank may provide in less no of passes
This jcl will give you the required output
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Wed Jun 04, 2008 10:13 pm    Post subject:
Reply with quote

gabriel.ryoga,

Assuming that there is always an E record in the group, here is a one pass DFSORT JCl which will give you the desired results removing the group which has E record as the last record. I assumed the file to be 80 bytes with FB RECFM

Code:

//STEP0100 EXEC PGM=ICETOOL                                       
//TOOLMSG  DD SYSOUT=*                                             
//DFSMSG   DD SYSOUT=*                                             
//IN       DD *                                                   
00001 I                                                           
00001 E                                                           
00001 I                                                           
00003 I                                                           
00003 E                                                           
00004 E                                                           
00004 I                                                           
//OUT      DD SYSOUT=*                                             
//TOOLIN   DD *                                                   
  SPLICE FROM(IN) ON(1,5,CH) TO(OUT) WITHALL WITH(01,80) -         
  KEEPBASE KEEPNODUPS USING(CTL1)                                 
//CTL1CNTL DD *                                                   
  SORT FIELDS=(1,5,CH,A,81,8,CH,D)                                 
  INREC IFTHEN=(WHEN=INIT,OVERLAY=(81:SEQNUM,8,ZD,RESTART=(1,5))),
        IFTHEN=(WHEN=(7,1,SS,EQ,C'EI'),OVERLAY=(89:7,1))           
                                                                   
   OUTFIL FNAMES=OUT,BUILD=(01,80),OMIT=(89,1,CH,EQ,C'E')         
//*                                                               
Back to top
View user's profile Send private message
vvmanyam

New User


Joined: 16 Apr 2008
Posts: 86
Location: Bangalore

PostPosted: Thu Jun 05, 2008 8:43 am    Post subject: Reply to: Select some records
Reply with quote

Nice solutoin from Kolusu ....
Back to top
View user's profile Send private message
V S Amarendra Reddy

Active User


Joined: 13 Sep 2006
Posts: 189
Location: INDIA

PostPosted: Thu Jun 05, 2008 1:44 pm    Post subject: Reply to: Select some records
Reply with quote

Skolusu,

gabriel.ryoga asked for the ouput as to remove the group which has the last record as 'E'. As per him his ouput should be
Code:

00001 I
00001 E
00001 I
00004 E
00004 I


but after executing the JCL given I got the ouput as below.
Code:

00001 I
00001 E
00001 I
00003 E
00003 I
00004 I
00004 E


Here the 00003 key records are not omitted. Can you please explain the ouput you have got after the execution of the jcl


Regards
Amar
Back to top
View user's profile Send private message
vvmanyam

New User


Joined: 16 Apr 2008
Posts: 86
Location: Bangalore

PostPosted: Thu Jun 05, 2008 1:51 pm    Post subject: Reply to: Select some records
Reply with quote

Hi Amar,
A small typing mistake.. icon_lol.gif
Code:

IFTHEN=(WHEN=(7,1,SS,EQ,C'EI')

it should be
Code:

IFTHEN=(WHEN=(7,1,SS,EQ,C'E'),


This will give you the desired result

Thanks,
Balu
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Thu Jun 05, 2008 1:55 pm    Post subject: Reply to: Select some records
Reply with quote

why not wait for the TS ( Thread started ) to clarify his wishes??
as I said in my previous post ...
the description asks for something, the sample is something else!
until then everything is just pure speculation
Back to top
View user's profile Send private message
vvmanyam

New User


Joined: 16 Apr 2008
Posts: 86
Location: Bangalore

PostPosted: Thu Jun 05, 2008 3:13 pm    Post subject: Reply to: Select some records using DFSORT
Reply with quote

Hi Amar,
Even I faced this problem initially ..
But there is no problem with the logic what is suggested..
I don't know why it is not working..
There is some problem occuring whlie we do the INREC in the ctrl card.
some length issue which I am unable to understand.
So, the SPLICE statement is not producing the result what we are expecting
But when you change length of the input file from 80 to 100 or
add sequence no.at position 51 and letter 'E' at 59

Then it is working..
Try this out..
Code:

//TOOLIN   DD *                                                   
  SPLICE FROM(IN) ON(1,5,CH) TO(OUT) WITHALL WITH(01,50) -       
  KEEPBASE KEEPNODUPS USING(CTL1)                                 
//CTL1CNTL DD *                                                   
  SORT FIELDS=(1,5,CH,A,51,8,CH,D)                               
  INREC IFTHEN=(WHEN=INIT,OVERLAY=(51:SEQNUM,8,ZD,RESTART=(1,5))),
        IFTHEN=(WHEN=(7,1,SS,EQ,C'E'),OVERLAY=(59:7,1))           
                                                                 
   OUTFIL FNAMES=OUT,BUILD=(01,80),OMIT=(59,1,CH,EQ,C'E')         
//*                                                               


Thanks,
Balu
Back to top
View user's profile Send private message
V S Amarendra Reddy

Active User


Joined: 13 Sep 2006
Posts: 189
Location: INDIA

PostPosted: Thu Jun 05, 2008 3:39 pm    Post subject:
Reply with quote

enrico sorichetti,

I accept your words. You are correct let the TS to clarify his wishes.

But I have given trail of executing the Skolusu's JCL.So I have posted my doubt.

vvmanyam,

Still its not orking with the change you told. Please try to execute one more time.

Regards
Amar
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Thu Jun 05, 2008 3:43 pm    Post subject: Reply to: Select some records using DFSORT
Reply with quote

Hi Amar,
looks like You have a duplicate post issue
maybe Your sort product is not that smart icon_biggrin.gif
I will delete the first one
this is the third one exactly with the same content
Back to top
View user's profile Send private message
V S Amarendra Reddy

Active User


Joined: 13 Sep 2006
Posts: 189
Location: INDIA

PostPosted: Thu Jun 05, 2008 4:18 pm    Post subject:
Reply with quote

Hi enrico sorichetti

Sorry for misunderstanding. I don't have any duplicate post issue nor I have this kind of requirement icon_smile.gif . I was just trying out the issue and the solution JCL provided by Skolusu. But since the output was not matching with the required output by gabriel.ryoga. So I thought I will intimate the same to Skolusu. So I have posted my first reply for this thread.

vvmanyam,
I got this output with your suggestions.
Code:

00004 I
00004 E


I don't think its matching with the output required as the 00001 key records are out.Let Skolusu explain the output he got. He could have got correctly. There is some small thing gone wrong.


Regards
Amar
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Thu Jun 05, 2008 4:21 pm    Post subject: Reply to: Select some records using DFSORT
Reply with quote

I was joking about Your sort product not being smart enough to detect and delete duplicate posts icon_smile.gif
Back to top
View user's profile Send private message
vvmanyam

New User


Joined: 16 Apr 2008
Posts: 86
Location: Bangalore

PostPosted: Thu Jun 05, 2008 4:27 pm    Post subject:
Reply with quote

I have tested it before posting..
even now I tested it
The ouput is matching
Input
Code:

00001 I
00001 E
00001 I
00003 I
00003 E
00004 E
00004 I

output
Code:

00001 I
00001 E
00001 I
00004 I
00004 E

Thanks,
Balu
Back to top
View user's profile Send private message
V S Amarendra Reddy

Active User


Joined: 13 Sep 2006
Posts: 189
Location: INDIA

PostPosted: Thu Jun 05, 2008 4:37 pm    Post subject:
Reply with quote

Hi Balu,

Yes you are correct. Its working.Actually I have changed the input records as below.
Code:

00001 I
00001 E
00003 I
00003 E
00001 I
00004 E
00004 I


Since the code is sorting the records based on the key in ascending order and then the sequence number in descending order. I just made a trail by giving this kind of input. Then it is skipping 00001 key records. I think it should not. Shouldn't it??

Regards
Amar
Back to top
View user's profile Send private message
V S Amarendra Reddy

Active User


Joined: 13 Sep 2006
Posts: 189
Location: INDIA

PostPosted: Thu Jun 05, 2008 4:40 pm    Post subject:
Reply with quote

By the way one more change to your code.

The BUILD shoud be (01,50).Its been mentioned as
Code:
 OUTFIL FNAMES=OUT,BUILD=(01,80),OMIT=(59,1,CH,EQ,C'E') 

Instead it should be
Code:

 OUTFIL FNAMES=OUT,BUILD=(01,50),OMIT=(59,1,CH,EQ,C'E') 

icon_smile.gif

What do you say?
Back to top
View user's profile Send private message
vvmanyam

New User


Joined: 16 Apr 2008
Posts: 86
Location: Bangalore

PostPosted: Thu Jun 05, 2008 4:48 pm    Post subject:
Reply with quote

Hi Amar,
This case fails because we are adding the sequence no in the INREC which executes before sorting the data.
For above given input by you
we need to code a step which sort the data according to the key and then splice using the same control card.

Hope this is clear
Thanks,
Balu
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Thu Jun 05, 2008 9:52 pm    Post subject:
Reply with quote

V S Amarendra Reddy,

The solution I posted DOES produce the correct results eliminating the 0003 records. Trust me when I DO post a solution I post it after testing and getting the desired results. I have no idea as to why you don't get the same results without looking at your DFSMSG.

Probable reasons could be

1. You did not copy the control cards as is
2. You are NOT using DFSORT
3. Or you have a DFSORT version which does NOT support INREC processing with splice

Look at the ICE201I message . If you see a zero then your version does not support INREC processing with splice.

Quote:
ICE201I 0 RECORD TYPE IS F - DATA STARTS IN POSITION 1


If you have the latest PTF then the message would have an E

Quote:
ICE201I E RECORD TYPE IS F - DATA STARTS IN POSITION 1
Back to top
View user's profile Send private message
V S Amarendra Reddy

Active User


Joined: 13 Sep 2006
Posts: 189
Location: INDIA

PostPosted: Thu Jun 05, 2008 10:00 pm    Post subject:
Reply with quote

Skolusu,

Apologies!!!You are correct.I don't have latest PTFs. I just have seen my DFSMSG. You are perfect. I have seen the zero in ICE201I. So I think my shop doesn't have latest PTFs installed. So it is not supporting INREC with SPLICE.

Thanks for clarifying the same. icon_biggrin.gif

Regards
Amar
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 Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts How to get the remainder and quotient... vnktrrd DFSORT/ICETOOL 2 Mon Oct 31, 2016 10:59 am
No new posts How to split the records using the am... vnktrrd DFSORT/ICETOOL 24 Fri Oct 28, 2016 7:33 pm
No new posts Sort records based on numeric field. Alks SYNCSORT 2 Wed Oct 19, 2016 10:14 pm


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