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
 

 

Need to sort the INPUT file based on the key

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
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    Post subject: Need to sort the INPUT file based on the key
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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: 1439
Location: Bangalore,India

PostPosted: Wed Oct 17, 2007 12:07 pm    Post subject:
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: 1288
Location: Chennai, India

PostPosted: Wed Oct 17, 2007 12:08 pm    Post subject: Reply to: Need help in sorting
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    Post subject:
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: 1439
Location: Bangalore,India

PostPosted: Wed Oct 17, 2007 12:19 pm    Post subject:
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: 1288
Location: Chennai, India

PostPosted: Wed Oct 17, 2007 2:38 pm    Post subject: Reply to: Need to sort the INPUT file based on the key
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    Post subject: Reply to: Need to sort the INPUT file based on the key
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: 1288
Location: Chennai, India

PostPosted: Wed Oct 17, 2007 3:25 pm    Post subject: Reply to: Need to sort the INPUT file based on the key
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 Moderator


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

PostPosted: Wed Oct 17, 2007 9:43 pm    Post subject:
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    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
This topic is locked: you cannot edit posts or make replies. Fetching data from BAI File arunsoods JCL & VSAM 1 Wed Jul 19, 2017 4:28 pm
No new posts Write out NODUPS but just from one file Jay Villaverde DFSORT/ICETOOL 8 Fri Jul 14, 2017 12:44 am
No new posts SORT to unpack a feild shr_amar DFSORT/ICETOOL 7 Fri Jul 07, 2017 4:32 am
No new posts How to add header with Date(YYMMDD) i... Rajan Moorthy DFSORT/ICETOOL 2 Thu Jul 06, 2017 11:44 pm
No new posts How to write Specific Fields from Mul... Padhu SYNCSORT 6 Thu Jul 06, 2017 10:26 am


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