Selecting Records based on a condition



IBM's flagship sort product DFSORT for sorting, merging, copying, data manipulation and reporting. Includes ICETOOL and ICEGENER

Selecting Records based on a condition

Postby aravind83 » Tue Oct 13, 2009 12:35 am

Hi,

Need help in DFSORT.

I/P File :- RECFM=VB and LRECL=5230. The following is the sample input file
col1 is an 10 char fixed length. This will be randomly generated.
col2 is a single char fixed length. The values for this field are either 1 or 2.

col1********* col2******
123456abcd 1
123456abcd 1
123456abcd 2
123456acde 1
123456acde 1
123456acde 1
123456acde 1
123456acde 1
123456acde 1
123789abcf 1
123789abcf 1
123789abcf 1
123789abcf 1
123789abcf 2

Now i am trying to generate a o/p file as suggested below. The condition is that i would need all those records who have both 1 and 2 in col2 in the file i.e.

col1********* col2******
123456abcd 1
123456abcd 1
123456abcd 2
123789abcf 1
123789abcf 1
123789abcf 1
123789abcf 1
123789abcf 2

The file paramters will remain the same i.e RECFM=VB and LRECL=5230
aravind83
 
Posts: 3
Joined: Sun Oct 11, 2009 10:51 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Selecting Records based on a condition

 

Re: Selecting Records based on a condition

Postby skolusu » Tue Oct 13, 2009 1:34 am

Aravind,

The following DFSORT JCL will give you the desired results. Since your input is VB , col1 actually starts at 14 (10 + 4 byte RDW) and col2 starts at pos 25

//STEP0100 EXEC PGM=SORT                                   
//SYSOUT   DD SYSOUT=*                                     
//SORTIN   DD DSN=your input vb 5230 file,DISP=SHR
//SORTOUT  DD DSN=&&T1,DISP=(,PASS),SPACE=(CYL,(X,Y),RLSE) 
//SYSIN    DD *                                             
  SORT FIELDS=COPY                                         
  INCLUDE COND=(25,1,CH,EQ,C'2')                           
  OUTREC OVERLAY=(25:C'0')                                 
/*                                                         
//STEP0200 EXEC PGM=SORT                                   
//SYSOUT   DD SYSOUT=*                                     
//SORTIN   DD DSN=&&T1,DISP=SHR                             
//         DD DSN=your input vb 5230 file,DISP=SHR
//SORTOUT  DD SYSOUT=*                                     
//SYSIN    DD *                                             
  SORT FIELDS=(14,10,CH,A,25,1,CH,A),EQUALS                 
  OUTREC IFTHEN=(WHEN=INIT,                                 
  BUILD=(1,4,25,1,X,SEQNUM,8,ZD,RESTART=(14,10),5)),       
  IFTHEN=(WHEN=GROUP,BEGIN=(7,8,ZD,EQ,1),PUSH=(6:5,1))     
  OUTFIL INCLUDE=(5,2,SS,EQ,C'10,20'),BUILD=(1,4,15)       
/*   
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
skolusu
 
Posts: 586
Joined: Wed Apr 02, 2008 10:38 pm
Has thanked: 0 time
Been thanked: 39 times

Re: Selecting Records based on a condition

Postby aravind83 » Tue Oct 13, 2009 9:35 am

I will try it out..Thanq very much for the reply Skolusu
aravind83
 
Posts: 3
Joined: Sun Oct 11, 2009 10:51 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Selecting Records based on a condition

Postby aravind83 » Tue Oct 13, 2009 10:20 pm

Hi Skolusu,

I have run the sort codes as suggested with a slight tweak to adjust the positions and is working perfectly fine. Thanq very very much. But would another small help from you i.e. can u please give me an high level overview of the second sort stmts i.e. for step STEP0200 so as to understand as to how this works.

Thankq once again,
Aravind
aravind83
 
Posts: 3
Joined: Sun Oct 11, 2009 10:51 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Selecting Records based on a condition

Postby skolusu » Fri Oct 16, 2009 1:49 am

aravind83 ,

Step0100 will create a temp file with all the '2' records and put a value of '0' at pos 25
----+----1----+----2----+
             123456ABCD 0
             123789ABCF 0


step0200 will concatenate the temp file and original input and sort on the key fields and this how it looks like before OUTREC

----+----1----+----2----+
             123456ABCD 0
             123456ABCD 1
             123456ABCD 1
             123456ABCD 2

             123456ACDE 1
             123456ACDE 1
             123456ACDE 1
             123456ACDE 1
             123456ACDE 1
             123456ACDE 1

             123789ABCF 0
             123789ABCF 1
             123789ABCF 1
             123789ABCF 1
             123789ABCF 1
             123789ABCF 2


OUTREC IFTHEN=(WHEN=INIT,
BUILD=(1,4,25,1,X,SEQNUM,8,ZD,RESTART=(14,10),5)),


This will put the indicator in pos 5 and also a seqnum at pos 7 whenever the key(14,10) changes

    0 00000001 123456ABCD 0
    1 00000002 123456ABCD 1
    1 00000003 123456ABCD 1
    2 00000004 123456ABCD 2
    1 00000001 123456ACDE 1
    1 00000002 123456ACDE 1
    1 00000003 123456ACDE 1
    1 00000004 123456ACDE 1
    1 00000005 123456ACDE 1
    1 00000006 123456ACDE 1
    0 00000001 123789ABCF 0
    1 00000002 123789ABCF 1
    1 00000003 123789ABCF 1
    1 00000004 123789ABCF 1
    1 00000005 123789ABCF 1
    2 00000006 123789ABCF 2



IFTHEN=(WHEN=GROUP,BEGIN=(7,8,ZD,EQ,1),PUSH=(6:5,1))
OUTFIL INCLUDE=(5,2,SS,EQ,C'10,20'),BUILD=(1,4,15)


now if the seqnum we have at pos 7 we push down the value at 5 on pos 6 . now the contents will look like this
00 00000001 123456ABCD 0 
10 00000002 123456ABCD 1 
10 00000003 123456ABCD 1 
20 00000004 123456ABCD 2 
11 00000001 123456ACDE 1 
11 00000002 123456ACDE 1 
11 00000003 123456ACDE 1 
11 00000004 123456ACDE 1 
11 00000005 123456ACDE 1 
11 00000006 123456ACDE 1 
00 00000001 123789ABCF 0 
10 00000002 123789ABCF 1 
10 00000003 123789ABCF 1 
10 00000004 123789ABCF 1 
10 00000005 123789ABCF 1 
20 00000006 123789ABCF 2 


Using OUTFIL include we only pick the values 10 and 20 and remove the unwanted fields we created.

I suggest that you read the User Guide for DFSORT PTF UK90013 paper (sortugpf.pdf) which explains in detail about the GROUP and other new functions

http://www.ibm.com/support/docview.wss? ... g3T7000085
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
skolusu
 
Posts: 586
Joined: Wed Apr 02, 2008 10:38 pm
Has thanked: 0 time
Been thanked: 39 times


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post