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

Need to sort the INPUT file based on the key
IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic Quick References
View previous topic :: View next topic  
Author Message
sindhuvava
Currently Banned
New User


Joined: 05 Dec 2006
Posts: 17
Location: Chennai
PostPosted: Wed Oct 17, 2007 10:42 am
 Reply with quote
This is the copybook layout of my input file:
Code:

05 SSN PIC X(09).
05 HICN PIC X(12).
05 FIRST-NAME PIC X(30).
05 MID-INT PIC X(1).
05 LAST-NAME PIC X(40).
05 BIRTH-DT PIC 9(8).
05 GNDR-CD PIC X(1).
05 COV-EFF-DT PIC 9(8).
05 COV-CANC-DT PIC 9(8).
05 UBOI PIC X(20).
05 REL-CD PIC X(2).
05 TRANS-TYPE PIC X(3).
05 DET-IND PIC X(1).
05 REASON PIC X(2).
05 SBSDY-EFF-DT PIC 9(8).
05 SBSDY-CANC-DT PIC 9(8).
05 APPL-ID PIC X(10).
05 NASCO-GRP-ID PIC X(9).
05 NASCO-SUBGRP-ID PIC X(4).
05 NASCO-PKG-NBR PIC X(3).
05 NASCO-SUB-ID PIC 9(13).
05 NASCO-DEP-ID PIC 9(2).
05 SRC-SYS-KEY PIC 9(3).
05 FILLER PIC X(15).
05 NOTIF-REAS PIC X(4).
05 FILLER PIC X(35).
05 Timestamp PIC X(14).
05 FILLER PIC X(127).


So i need to sort the INPUT file based on the key(NASCO-SUB-ID,NASCO-DEP-ID,SRC-SYS-KEY) to get the latest record with respect to the Timestamp.

The key which i have mentioned above forms the unique key for that input file. so i need to retrieve the latest record containing the highest timestamp from the input file for each unique key.

In the Input file, some of the records contains 'blanks' for the columns NASCO-SUB-ID,NASCO-DEP-ID respectively.

when i tried to sort using JCL, the records conatining blanks in the Key field are getting eliminated.

All the records contain SRC-SYS-KEY column in the input file.

please suggest me how to proceed.
Back to top
View user's profile Send private message
nuthan

Active User


Joined: 26 Sep 2005
Posts: 146
Location: Bangalore
PostPosted: Wed Oct 17, 2007 10:47 am
 Reply with quote
Can you give ur JCl sort card which u have used for this.
Back to top
View user's profile Send private message
sindhuvava
Currently Banned
New User


Joined: 05 Dec 2006
Posts: 17
Location: Chennai
PostPosted: Wed Oct 17, 2007 11:24 am
 Reply with quote
//STEP110 EXEC PGM=SORT
//SYSPRINT DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SORTIN DD DSN=TEST.RDSRSP.HISTORY.NASCO.FILE.OCT07,
// DISP=(OLD,KEEP)
//SORTOUT DD DSN=TEST.HISTORY.NASCO.FILE.OCT15.SORT,
// DISP=(NEW,CATLG,DELETE),
// UNIT=SYSDA,MGMTCLAS=WORK6M,
// LIKE=TEST.RDSRSP.HISTORY.NASCO.FILE.OCT07
//SYSIN DD *
SORT FIELDS=(188,13,CH,A,201,2,CH,A,203,3,CH,A,260,14,CH,D)
//*
//STEP120 EXEC PGM=SORT
//SYSPRINT DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SORTIN DD DSN=TEST.HISTORY.NASCO.FILE.OCT15.SORT,
// DISP=(OLD,KEEP)
//SORTOUT DD DSN=TEST.HISTORY.NASCO.FILE.OCT15.SORT.NEW,
// DISP=(NEW,CATLG,DELETE),
// UNIT=SYSDA,MGMTCLAS=WORK6M,
// LIKE=TEST.HISTORY.NASCO.FILE.OCT15.SORT
//SYSIN DD *
SORT FIELDS=(188,13,CH,A,201,2,CH,A,203,3,CH,A)
OPTION EQUALS
SUM FIELDS=NONE
//*


this was the jcl i used.

the lrecl is 400.
Back to top
View user's profile Send private message
nuthan

Active User


Joined: 26 Sep 2005
Posts: 146
Location: Bangalore
PostPosted: Wed Oct 17, 2007 11:33 am
 Reply with quote
The key( the three fields which u specified) wont be unique if the records contains 'blanks' for the columns NASCO-SUB-ID, NASCO-DEP-ID. so these recods fall under duplicate cond and was eliminated due to SUM fields.
Correct me if i am wrong, there will be one record which has SRC-SYS-KEY but NASCO-SUB-ID and NASCO-DEP-ID are balnk respectively.
Back to top
View user's profile Send private message
sindhuvava
Currently Banned
New User


Joined: 05 Dec 2006
Posts: 17
Location: Chennai
PostPosted: Wed Oct 17, 2007 11:38 am
 Reply with quote
Yes!! u r absolutely right!!!

can you provide me an solution how to get the required output

it would be really very much greatful to me..

thanks in advance!!!
Back to top
View user's profile Send private message
nuthan

Active User


Joined: 26 Sep 2005
Posts: 146
Location: Bangalore
PostPosted: Wed Oct 17, 2007 11:46 am
 Reply with quote
let me know how will you decide the uniqueness of records if the fields NASCO-SUB-ID, NASCO-DEP-ID are balnk. are there any other fields to decide this, if not then it is difficult to separate them out as they fall under one category.
Back to top
View user's profile Send private message
murmohk1

Senior Member


Joined: 29 Jun 2006
Posts: 1436
Location: Bangalore,India
PostPosted: Wed Oct 17, 2007 12:07 pm
 Reply with quote
Sindhu,

Quote:
05 NASCO-SUB-ID PIC 9(13).
05 NASCO-DEP-ID PIC 9(2).
05 SRC-SYS-KEY PIC 9(3).


Quote:
some of the records contains 'blanks' for the columns NASCO-SUB-ID,NASCO-DEP-ID respectively

What do you mean by 'blanks'? 'Keys' are declared as numerics (my understanding as per your layout).
Back to top
View user's profile Send private message
Aaru

Senior Member


Joined: 03 Jul 2007
Posts: 1287
Location: Chennai, India
PostPosted: Wed Oct 17, 2007 12:08 pm
 Reply with quote
Quote:
when i tried to sort using JCL, the records conatining blanks in the Key field are getting eliminated.


The records containing blanks in the key fields(NASCO-SUB-ID,NASCO-DEP-ID) will get eliminated only when the other field (SRC-SYS-KEY)has got the same(duplicate) values.

consider 2 records with the value in the 3 fields.

blank blank 100
blank blank 100

when you give SUMFIELDS= NONE these will be considered as duplicates.
According to my understanding You dont want this to be considered as duplicates right? Correct me if am wrong.
Back to top
View user's profile Send private message
sindhuvava
Currently Banned
New User


Joined: 05 Dec 2006
Posts: 17
Location: Chennai
PostPosted: Wed Oct 17, 2007 12:16 pm
 Reply with quote
Aaru,

Yes u r absolutely right!!!

i dont want those to be considered as duplicates.

05 NASCO-SUB-ID PIC 9(13).
05 NASCO-DEP-ID PIC 9(2).
05 SRC-SYS-KEY PIC 9(3).

Actually these are the three fields which forms the unique key for the table to be laoded.
Back to top
View user's profile Send private message
murmohk1

Senior Member


Joined: 29 Jun 2006
Posts: 1436
Location: Bangalore,India
PostPosted: Wed Oct 17, 2007 12:19 pm
 Reply with quote
Sindhu,
Quote:

Actually these are the three fields which forms the unique key for the table to be laoded.

Do you think unique key concept hold here (taking Aaru's example)?
Back to top
View user's profile Send private message
Aaru

Senior Member


Joined: 03 Jul 2007
Posts: 1287
Location: Chennai, India
PostPosted: Wed Oct 17, 2007 2:38 pm
 Reply with quote
Could you please try this.

1) Seperate the input file into two, one (File 1)without blanks in those two fields and the other (File 2) with blanks in those 2 fields. This can be done using OUTFIL\INCLUDE.

2) Sort the file 1 on those 3 fields and then eliminate the duplicates using SUM FIELDS=NONE

3) Sort the file on the 3rd key field and then eliminate the duplicates.

4) concatenate both the files to get the final output.

Techies, please advise if this can be done in a better way than is.
Back to top
View user's profile Send private message
sindhuvava
Currently Banned
New User


Joined: 05 Dec 2006
Posts: 17
Location: Chennai
PostPosted: Wed Oct 17, 2007 3:08 pm
 Reply with quote
Quote:
3) Sort the file on the 3rd key field and then eliminate the duplicates.



this will eliminate all the records which are having blanks in the other two fields. (sub-id,dep-id)
Back to top
View user's profile Send private message
Aaru

Senior Member


Joined: 03 Jul 2007
Posts: 1287
Location: Chennai, India
PostPosted: Wed Oct 17, 2007 3:25 pm
 Reply with quote
Sindhu,

Quote:
this will eliminate all the records which are having blanks in the other two fields. (sub-id,dep-id)


No, you are wrong. Consider this sort card.

Code:
//S1    EXEC  PGM=ICEMAN                               
//SYSOUT    DD  SYSOUT=*                               
//SORTIN DD *                                         
      001                                             
      001                                             
      004                                             
      002                                             
      004                                             
/*                                                     
//SORTOUT DD SYSOUT=*                                 
//SYSIN    DD    *                                     
  SORT FIELDS=(7,3,CH,A)                               
  SUM FIELDS=NONE                                     
/*                                                     



output:

Code:
 ***************************** Top of Data ******************************
       001                                                               
       002                                                               
       004                                                               
 **************************** Bottom of Data ****************************


This will not eliminate all the records which are having blanks in those 2 fields. It will just eliminate those records which has duplicate values in the 3 rd field with blanks in the other two fields.

Post your queries if you are not clear.
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 Oct 17, 2007 9:43 pm
 Reply with quote
Sindhu,

Here's a DFSORT/ICETOOL job that will do what you asked for:

Code:

//S1    EXEC  PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG  DD SYSOUT=*
//IN DD DSN=...  input file (FB/400)
//OUT DD DSN=...  output file (FB/400)
//TOOLIN   DD    *
SELECT FROM(IN) TO(OUT) ON(188,13,CH) ON(201,2,CH) ON(203,3,CH) -
  ON(401,8,ZD) FIRST USING(CTL1)
/*
//CTL1CNTL DD *
  INREC IFTHEN=(WHEN=INIT,OVERLAY=(401:8C'0')),
        IFTHEN=(WHEN=(188,13,CH,EQ,C' ',AND,201,2,CH,EQ,C' '),
          OVERLAY=(401:SEQNUM,8,ZD))
  SORT FIELDS=(188,13,CH,A,201,2,CH,A,203,3,CH,A,
     401,8,ZD,A,260,14,CH,D)
  OUTFIL FNAMES=OUT,BUILD=(1,400)
/*


If your input file had the following:

Code:

SUB-ID            DEP-ID SYS-KEY TIMESTAMP
CCCCCCCCCCCCC     DD     EEE     11111111111111
CCCCCCCCCCCCC     DD     EEE     22222222222222
AAAAAAAAAAAAA     BB     CCC     11111111111111
AAAAAAAAAAAAA     BB     CCC     22222222222222
AAAAAAAAAAAAA     BB     CCC     33333333333333
                         FFF     00000000000000
                         FFF     22222222222222
                         GGG     22222222222222
BBBBBBBBBBBBB     CC     DDD     33333333333333
                         QQQ     11111111111111
                         QQQ     22222222222222
                         QQQ     33333333333333


The output file would have:

Code:

SUB-ID            DEP-ID SYS-KEY TIMESTAMP
                         FFF     00000000000000
                         FFF     22222222222222
                         GGG     22222222222222
                         QQQ     11111111111111
                         QQQ     22222222222222
                         QQQ     33333333333333
AAAAAAAAAAAAA     BB     CCC     33333333333333
BBBBBBBBBBBBB     CC     DDD     33333333333333
CCCCCCCCCCCCC     DD     EEE     22222222222222
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 FTP VB File from Mainframe retaining ... JCL & VSAM 1
No new posts Need to set RC4 through JCL SORT DFSORT/ICETOOL 5
No new posts Extract the file name from another fi... DFSORT/ICETOOL 6
No new posts How to split large record length file... DFSORT/ICETOOL 10
No new posts Extracting Variable decimal numbers f... DFSORT/ICETOOL 17
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