MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

DFSORT Help - Get the latest record

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities
View previous topic :: View next topic  
Author Message
somuk
Beginner


Joined: 04 Feb 2003
Posts: 113
Topics: 37
PostPosted: Fri Feb 20, 2009 12:33 pm    Post subject: DFSORT Help - Get the latest record Reply with quote
Hi,
Please help me to resolve the following requirement using DFSORT.
I have a file which is having duplicates and need to remove this duplicates based on some conditions.

The COBOL layout is given below (taken from File aid)
Code:

--------- FIELD LEVEL/NAME ---------- -PICTURE- -NUMBER START     END  LENGTH 
NR-NA-REPLY-REC1                                            1      25      25 
5 NR-KEY                              GROUP          1      1      23      23 
  10 NR-TRANSIT                       S9(5)          2      1       3       3 
  10 NR-SERVICE-IND                   S999           3      4       5       2 
  10 NR-ACCT-NO                       S9(7)          4      6       9       4 
  10 NR-SERIAL                        S9(9)          5     10      14       5 
  10 NR-AMT                           S9(9)V99       6     15      20       6 
  10 NR-YYDDD                         S9(5)          7     21      23       3 

The date field (NR-YYDDD) is in YYDDD foramt. I have to remove the duplicates based on the above fields and get the record which is having the latest date (NR-YYDDD). I mean the output file will have the latest record.
The file is FB and LRECL=338. Please help
Please note that we don't have the latest PTF available. A snap shot of the current function level of DFSORT is given below:
Code:

1ICE143I 0 BLOCKSET     COPY  TECHNIQUE SELECTED
 ICE250I 0 VISIT http://www.ibm.com/storage/dfsort FOR DFSORT PAPERS, EXAMPLES AND MORE
 ICE000I 1 - CONTROL STATEMENTS FOR 5694-A01, Z/OS DFSORT V1R5 - 03:02 ON WED NOV 12, 2008 -
0            OPTION COPY                                                           00001450
 ICE201I E RECORD TYPE IS F - DATA STARTS IN POSITION 1
 ICE751I 0 C5-K26318 C6-K90007 C7-K90000 C8-K23476 E9-K90007 C9-BASE   E5-K24705 E7-K24705
 ICE193I 0 ICEAM1 ENVIRONMENT IN EFFECT - ICEAM1 INSTALLATION MODULE SELECTED
 ICE088I 0 QSDK7G92.STEP0100.        , INPUT LRECL = 80, BLKSIZE = 80, TYPE = FB
 ICE093I 0 MAIN STORAGE = (MAX,6291456,6278238)
 ICE156I 0 MAIN STORAGE ABOVE 16MB = (6200798,6200798)
 ICE127I 0 OPTIONS: OVFLO=RC0 ,PAD=RC0 ,TRUNC=RC0 ,SPANINC=RC16,VLSCMP=N,SZERO=Y,RESET=Y,VSAMEMT=Y,DYNSPC=256
 ICE128I 0 OPTIONS: SIZE=6291456,MAXLIM=1048576,MINLIM=450560,EQUALS=N,LIST=Y,ERET=ABEND,MSGDDN=SYSOUT
 ICE129I 0 OPTIONS: VIO=N,RESDNT=ALL ,SMF=FULL ,WRKSEC=Y,OUTSEC=Y,VERIFY=N,CHALT=N,DYNALOC=N             ,ABCODE=MSG
 ICE130I 0 OPTIONS: RESALL=4096,RESINV=0,SVC=109 ,CHECK=Y,WRKREL=Y,OUTREL=Y,CKPT=N,STIMER=Y,COBEXIT=COB2
 ICE131I 0 OPTIONS: TMAXLIM=6291456,ARESALL=0,ARESINV=0,OVERRGN=65536,CINV=Y,CFW=N,DSA=0
 ICE132I 0 OPTIONS: VLSHRT=N,ZDPRINT=Y,IEXIT=N,TEXIT=N,LISTX=N,EFS=NONE    ,EXITCK=S,PARMDDN=DFSPARM ,FSZEST=N
 ICE133I 0 OPTIONS: HIPRMAX=OPTIMAL,DSPSIZE=MAX ,ODMAXBF=0,SOLRF=Y,VLLONG=N,VSAMIO=N,MOSIZE=0
 ICE235I 0 OPTIONS: NULLOUT=RC0
 ICE084I 0 BSAM ACCESS METHOD USED FOR SORTOUT
 ICE084I 0 BSAM ACCESS METHOD USED FOR SORTIN
 ICE751I 1 EF-K10929 F0-Q84357 E8-K24705
 ICE090I 0 OUTPUT LRECL = 80, BLKSIZE = 80, TYPE = FB
 ICE055I 0 INSERT 0, DELETE 0
 ICE054I 0 RECORDS - IN: 1, OUT: 1
 ICE052I 0 END OF DFSORT


_________________
Regds,
Somu
Back to top
View user's profile Send private message Yahoo Messenger
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
PostPosted: Fri Feb 20, 2009 1:32 pm    Post subject: Reply with quote
somuk,

Use the following DFSORT/ICETOOL Job

Code:

//STEP0200 EXEC PGM=ICETOOL
//TOOLMSG  DD SYSOUT=*     
//DFSMSG   DD SYSOUT=*     
//IN       DD DSN=Your input file,
//            DISP=SHR
//OUT      DD SYSOUT=*                                 
//TOOLIN   DD *                                       
  SELECT FROM(IN) TO(OUT) ON(1,20,BI) LAST USING(CTL1)
//CTL1CNTL DD *                                       
  SORT FIELDS=(01,3,PD,A,    $ NR-TRANSIT             
               04,2,PD,A,    $ NR-SERVICE-IND         
               06,4,PD,A,    $ NR-ACCT-NO             
               10,5,PD,A,    $ NR-SERIAL               
               15,6,PD,A,    $ NR-AMT                 
               21,3,Y2U,A)   $ NR-YYYDD               
/*

_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
somuk
Beginner


Joined: 04 Feb 2003
Posts: 113
Topics: 37
PostPosted: Sat Feb 21, 2009 5:55 am    Post subject: Reply with quote
Hi Kolusu,
Thanks a lot for the solution. Sorry to bother you again. Actually the file is VB and the LRECL=299. I added an offset of 4 to the control card but it's giving a return code of 16 in the
Code:

SELECT FROM(IN) TO(OUT) ON(5,20,BI) LAST USING(CTL1)
statement. Please note that I changed the CTL1 also to reflect the changes:
Code:

//CTL1CNTL DD *
  SORT FIELDS=(05,3,PD,A,
               08,2,PD,A,
               10,4,PD,A,
               14,5,PD,A,
               19,6,PD,A,
               25,3,Y2U,A)
/*

Am I doing something wrong. Please advise
_________________
Regds,
Somu
Back to top
View user's profile Send private message Yahoo Messenger
Frank Yaeger
Sort Forum Moderator
Sort Forum Moderator


Joined: 02 Dec 2002
Posts: 1618
Topics: 31
Location: San Jose
PostPosted: Sat Feb 21, 2009 11:00 am    Post subject: Reply with quote
The return code 16 would be accompanied by an error message in //DFSMSG. So you need to show the messages you received in //DFSMSG before we can help.
_________________
Frank Yaeger - DFSORT Development Team (IBM)
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
Back to top
View user's profile Send private message Send e-mail Visit poster's website
somuk
Beginner


Joined: 04 Feb 2003
Posts: 113
Topics: 37
PostPosted: Sat Feb 21, 2009 1:32 pm    Post subject: Reply with quote
Hi Frank,
I could not get the entire DFSMSG (don't have access to the mainframe now, but kept the screen shot for the reference). I do not know whether this incomplete DFSMSG will give any input of the error message
Code:

SORT FIELDS=(05,3,PD,A,
               08,2,PD,A,
               10,4,PD,A,
               14,5,PD,A,
               19,6,PD,A,
               25,3,Y2U,A)
ICE146I 0 END OF STATEMENTS FROM CTL1CNTL - PARAMETER LIST STATEMENTS FOLLOW
DEBUG NOABEND,ESTAE
OPTION MSGDDN=DFSMSG,LIST,MSGPRT=ALL,RESINV=0,SORTDD=CTL1,SORTIN=IN,
RTOUT=OUT,DYNALLOC,SZERO,EQUALS,NOVLSHRT,LOCALE=NONE,N
HECK
SORT FIELDS=(5,20,BI,A)
MODS E35=(ICE35DU,12288)
ICE201I F RECORD TYPE IS V - DATA STARTS IN POSITION 5
ICE168I 0 CENTURY WINDOW FOR Y2 FORMAT FIELDS IS FROM 1929 TO 2028
ICE751I 0 C5-K90014 C6- K90014 C7-BASE  C8- K90014 E4-BASE C9-BASE E5-K38900


_________________
Regds,
Somu
Back to top
View user's profile Send private message Yahoo Messenger
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
PostPosted: Sat Feb 21, 2009 2:54 pm    Post subject: Reply with quote
somuk,

It is incomplete. We need to see the Complete DFSMSG messages
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
somuk
Beginner


Joined: 04 Feb 2003
Posts: 113
Topics: 37
PostPosted: Sun Feb 22, 2009 9:27 am    Post subject: Reply with quote
Kolusu/Frank,
Please find the DFSMSG below.
Code:

1ICE200I 0 IDENTIFIER FROM CALLING PROGRAM IS 0001
 ICE143I 0 BLOCKSET     SORT  TECHNIQUE SELECTED
 ICE250I 0 VISIT http://www.ibm.com/storage/dfsort FOR DFSORT PAPERS, EXAMPLES AND MORE
 ICE000I 0 - CONTROL STATEMENTS FOR 5694-A01, Z/OS DFSORT V1R10 - 01:09 ON SUN FEB 22, 2009 -
0            SORT FIELDS=(05,3,PD,A,                                               00170001
                          08,2,PD,A,                                               00180001
                          10,4,PD,A,                                               00190001
                          14,5,PD,A,                                               00200001
                          19,6,PD,A,                                               00210001
                          25,3,Y2U,A)                                              00220001
 ICE146I 0 END OF STATEMENTS FROM CTL1CNTL - PARAMETER LIST STATEMENTS FOLLOW
           DEBUG NOABEND,ESTAE
           OPTION MSGDDN=DFSMSG,LIST,MSGPRT=ALL,RESINV=0,SORTDD=CTL1,SORTIN=IN,SOR*
                          TOUT=OUT,DYNALLOC,SZERO,EQUALS,NOVLSHRT,LOCALE=NONE,NOCH*
                          ECK
           SORT FIELDS=(5,20,BI,A)
           MODS E35=(ICE35DU,12288)
 ICE201I F RECORD TYPE IS V - DATA STARTS IN POSITION 5
 ICE168I 0 CENTURY WINDOW FOR Y2 FORMAT FIELDS IS FROM 1929 TO 2028
 ICE751I 0 C5-K90014 C6-K90014 C7-BASE   C8-K90014 E4-BASE   C9-BASE   E5-K38900 E6-BASE   C4-K38900 E7-BASE
 ICE193I 0 ICEAM2 INVOCATION ENVIRONMENT IN EFFECT - ICEAM2 ENVIRONMENT SELECTED
 ICE088I 0 QF5M5CFA.STEP0200.        , INPUT LRECL = 299, BLKSIZE = 27966, TYPE = VB
 ICE093I 0 MAIN STORAGE = (MAX,6291456,6291456)
 ICE156I 0 MAIN STORAGE ABOVE 16MB = (5783552,5767168)
 ICE127I 0 OPTIONS: OVFLO=RC0 ,PAD=RC0 ,TRUNC=RC0 ,SPANINC=RC16,VLSCMP=N,SZERO=Y,RESET=Y,VSAMEMT=Y,DYNSPC=256
 ICE128I 0 OPTIONS: SIZE=6291456,MAXLIM=524288,MINLIM=450560,EQUALS=Y,LIST=Y,ERET=RC16 ,MSGDDN=DFSMSG
 ICE129I 0 OPTIONS: VIO=N,RESDNT=ALL ,SMF=FULL ,WRKSEC=Y,OUTSEC=Y,VERIFY=N,CHALT=N,DYNALOC=(SYSDA   ,010),ABCODE=MSG
 ICE130I 0 OPTIONS: RESALL=4096,RESINV=0,SVC=109 ,CHECK=N,WRKREL=N,OUTREL=N,CKPT=N,COBEXIT=COB2
 ICE131I 0 OPTIONS: TMAXLIM=6291456,ARESALL=0,ARESINV=0,OVERRGN=65536,CINV=Y,CFW=N,DSA=0
 ICE132I 0 OPTIONS: VLSHRT=N,ZDPRINT=Y,IEXIT=N,TEXIT=N,LISTX=N,EFS=NONE    ,EXITCK=S,PARMDDN=DFSPARM ,FSZEST=N
 ICE133I 0 OPTIONS: HIPRMAX=OPTIMAL,DSPSIZE=MAX ,ODMAXBF=0,SOLRF=Y,VLLONG=N,VSAMIO=N,MOSIZE=0
 ICE235I 0 OPTIONS: NULLOUT=RC0
 ICE084I 0 EXCP ACCESS METHOD USED FOR OUT
 ICE084I 0 EXCP ACCESS METHOD USED FOR IN
 ICE750I 0 DC 27962 TC 0 CS DSVXX KSZ 32 VSZ 32
 ICE752I 0 FSZ=27962 BC  IGN=0 E  AVG=149 0  WSP=37 C  DYN=2 53216
 ICE218A 0 22 BYTE VARIABLE RECORD IS SHORTER THAN 27 BYTE MINIMUM FOR          FIELDS
 ICE751I 1 D8-BASE   D4-K38900 E8-K38900
 ICE052I 0 END OF DFSORT



When I override the default NOVLSHRT option in the control card the Job started running fine.
Code:

  OPTION VLSHRT
  SORT FIELDS=(05,3,PD,A,
........................
........................

_________________
Regds,
Somu
Back to top
View user's profile Send private message Yahoo Messenger
somuk
Beginner


Joined: 04 Feb 2003
Posts: 113
Topics: 37
PostPosted: Mon Feb 23, 2009 5:02 am    Post subject: Reply with quote
Hi Kolusu/Frank,
I totally forgot about one thing (my bad memory). When we remove the duplicates I need to update the Trailer record accordingly. The trailer is defined as follows (Fileaid view)
Code:

 5 NAO-MAST-TRAILER-TRANSIT            S9(5)          1      1       3       3
 5 FILLER                              X(4)           2      4       7       4
 5 NAO-MAST-TRAILER-REC-CNT            S9(7)          3      8      11       4
 5 NAO-MAST-TRAILER-AMT                S9(11)V99      4     12      18       7

The first two fields
Code:

5 NAO-MAST-TRAILER-TRANSIT            S9(5)          1      1       3       3
5 FILLER                              X(4)           2      4       7       4

is taken from the existing file (NAO-MAST-TRAILER-TRANSIT will be always 99999- So it will be the last record in our output)
But the other two fields are
NAO-MAST-TRAILER-REC-CNT - This is total number of records including the trailer.
NAO-MAST-TRAILER-AMT - Sum of NR-AMT of the detail records

Is it possible to change the trailer as well? Please advise.
_________________
Regds,
Somu
Back to top
View user's profile Send private message Yahoo Messenger
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
PostPosted: Mon Feb 23, 2009 12:06 pm    Post subject: Reply with quote
somuk,

I assumed that you don't have any other info on the trailer record apart from the fields you mentioned. You mentioned that NAO-MAST-TRAILER-TRANSIT will be always 99999- . I assuming that it is negative 99999. If it is a positive value then just the change the '99999Dd' on trailer to '99999C'. Use the following control cards.
Code:

//TOOLIN   DD *                                       
  SELECT FROM(IN) TO(OUT) ON(5,20,BI) LAST USING(CTL1)
//CTL1CNTL DD *                                       
  OPTION VLSHRT                                       
  SORT FIELDS=(05,3,PD,A,    $ NR-TRANSIT             
               08,2,PD,A,    $ NR-SERVICE-IND         
               10,4,PD,A,    $ NR-ACCT-NO             
               14,5,PD,A,    $ NR-SERIAL               
               19,6,PD,A,    $ NR-AMT                 
               25,3,Y2U,A)   $ NR-YYYDD               
                                                       
  OUTFIL FNAMES=OUT,REMOVECC,                         
  TRAILER1=(5:X'99999D',4X,                           
            COUNT=(PD,LENGTH=4),                       
            TOT=(19,6,PD,PD,LENGTH=7))                 
/*

_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
somuk
Beginner


Joined: 04 Feb 2003
Posts: 113
Topics: 37
PostPosted: Mon Feb 23, 2009 1:35 pm    Post subject: Reply with quote
Thanks Kolusu.
My apologies, I think I was not clear in my earlier post. The input file is already having a trailer. I just need to update the total count of records and total amount to reflect our SORT criteria. I mean we dropped some records using the SORT so accordingly the existing trailer has to be updated to reflect the new total record count and new total amount

Sample Input Trailer:
Code:


99999     23         1000.00

Where 23: total count
1000.00: total amount

Sample Output Trailer:
Code:


99999     20         800.00

I want to preserve the first 7 bytes in the original input trailer as it is(becuase it's not advisable to hard code these first 7 bytes ) and update only the other two fields:
NAO-MAST-TRAILER-REC-CNT - This is total number of records including the trailer.
NAO-MAST-TRAILER-AMT - Sum of NR-AMT of the detail records

Also for verification purpose can I capture the removed (dropped duplicates) in a separate file. Please advise
_________________
Regds,
Somu
Back to top
View user's profile Send private message Yahoo Messenger
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
PostPosted: Mon Feb 23, 2009 6:06 pm    Post subject: Reply with quote
somuk,

Somuk,

If you want to preserve the original trailer then you have to have more than 1 pass of data. If you are ok with dynamically generating the trailer record then you can use this JCL

Code:

//STEP0100 EXEC PGM=ICETOOL
//TOOLMSG  DD SYSOUT=*     
//DFSMSG   DD SYSOUT=*     
//IN       DD DSN=Your input file,
//            DISP=SHR
//OUT      DD SYSOUT=*                                               
//OTHER    DD SYSOUT=*                                               
//TOOLIN   DD *                                                       
  SELECT FROM(IN) TO(OUT) DISCARD(OTHER) ON(1,20,BI) LAST USING(CTL1)
//CTL1CNTL DD *                                                       
  OMIT COND=(5,3,PD,EQ,-99999)                                       
  OPTION VLSHRT                                                       
  SORT FIELDS=(05,3,PD,A,    $ NR-TRANSIT                             
               08,2,PD,A,    $ NR-SERVICE-IND                         
               10,4,PD,A,    $ NR-ACCT-NO                             
               14,5,PD,A,    $ NR-SERIAL                             
               19,6,PD,A,    $ NR-AMT                                 
               25,3,Y2U,A)   $ NR-YYYDD                               
                                                                     
  OUTFIL FNAMES=OUT,REMOVECC,                                         
  TRAILER1=(5:X'99999D',4X,                                           
            COUNT=(PD,LENGTH=4),                                     
            TOT=(19,6,PD,PD,LENGTH=7))                               
/*


If you do need to retain the original trailer record then here is the 2 pass solution

Code:

//STEP0100 EXEC PGM=ICETOOL
//TOOLMSG  DD SYSOUT=*     
//DFSMSG   DD SYSOUT=*     
//IN       DD DSN=Your input file,
//            DISP=SHR
//T1       DD DSN=&&T1,DISP=(,PASS),SPACE=(CYL,(1,1),RLSE)         
//OUT      DD SYSOUT=*                                             
//OTHER    DD SYSOUT=*                                             
//TOOLIN   DD *                                                     
  SELECT FROM(IN) TO(T1) DISCARD(OTHER) ON(5,20,BI) LAST USING(CTL1)
  SORT FROM(T1) TO(OUT) USING(CTL2)                                 
//CTL1CNTL DD *                                                     
  INREC IFTHEN=(WHEN=INIT,BUILD=(1,4,C'1',23X,5)),                 
  IFTHEN=(WHEN=(29,3,PD,EQ,-99999),                                 
  OVERLAY=(5:C'9',13:+0,TO=PD,LENGTH=4,20:+0,TO=PD,LENGTH=6)),     
  IFTHEN=(WHEN=NONE,OVERLAY=(6:29,23))                             
                                                                   
  SORT FIELDS=(05,1,CH,A,    $ RECORD-IND                           
               06,3,PD,A,    $ NR-TRANSIT                           
               09,2,PD,A,    $ NR-SERVICE-IND                       
               11,4,PD,A,    $ NR-ACCT-NO                           
               15,5,PD,A,    $ NR-SERIAL                           
               20,6,PD,A,    $ NR-AMT                               
               26,3,Y2U,A)   $ NR-YYYDD                             
                                                                   
  OUTFIL FNAMES=T1,REMOVECC,                                       
  IFTHEN=(WHEN=(5,1,ZD,EQ,9),                                       
  BUILD=(1,4,5,1,29,7,13,4,X'00',20,6,29)),                         
  IFTHEN=(WHEN=NONE,                                               
  BUILD=(1,4,5,1,SEQNUM,7,PD,                                       
         13:+0,TO=PD,LENGTH=4,17:+0,TO=PD,LENGTH=7,29)),           
  TRAILER1=(5,1,29,7,COUNT=(PD,LENGTH=4),TOT=(20,6,PD,PD,LENGTH=7))
                                                                   
  OUTFIL FNAMES=OTHER,BUILD=(1,4,29)                               
/*
//CTL2CNTL DD *                                                     
  SORT FIELDS=(05,8,CH,A)                                           
  SUM FIELDS=(13,4,PD,17,7,PD)                                     
  OUTREC IFTHEN=(WHEN=(5,1,ZD,EQ,9),OVERLAY=(31:13,11))             
  OUTFIL FNAMES=OUT,BUILD=(1,4,24)                                 
/*

_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
somuk
Beginner


Joined: 04 Feb 2003
Posts: 113
Topics: 37
PostPosted: Wed Feb 25, 2009 6:05 am    Post subject: Reply with quote
Hats Off to you Kolusu !!!
As always the solution works like a charm.
_________________
Regds,
Somu
Back to top
View user's profile Send private message Yahoo Messenger
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group