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
 

 

JCL - Pick up records with same values in a column

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

New User


Joined: 12 Jun 2008
Posts: 8
Location: Bangalore

PostPosted: Mon Jul 21, 2008 4:18 pm    Post subject: JCL - Pick up records with same values in a column
Reply with quote

Hi all,

I have a requirement like this:
I have to sort a file based on update date field in descending order. I have to write into another file records with the latest datetime.

Eg:

asdfsfsf123423424sfffgdg200807210510
asdfsfsf123423424sfffgdg200807210510
asdfsfsf123423424sfffgdg200807210510
asdfsfsf123423424sfffgdg200807210520
asdfsfsf123423424sfffgdg200807200510

I want in the output first three records alone.In simple terms I have to stop after the sortkey changes its value from first value

Please help!
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: Mon Jul 21, 2008 9:58 pm    Post subject:
Reply with quote

It's not clear what you want to do. You mention a sortkey, but it isn't clear what that sortkey is. It isn't clear if you want to keep records with the latest datetime of all of the records, or of each set of records with a particular key.

You need to do a better job of explaining what you want to do. Show a better example of the records in your input file with multiple sets of records to be kept if that's relevant, and your expected output. Explain the "rules" for getting from input to output. Give the RECFM and LRECL of the input file. Give the starting position, length and format of all relevant fields and keys.
Back to top
View user's profile Send private message
Prema Suresh

New User


Joined: 12 Jun 2008
Posts: 8
Location: Bangalore

PostPosted: Tue Jul 22, 2008 9:29 am    Post subject: Reply to: JCL - Pick up records with same values in a column
Reply with quote

I am sorry Frank. In an urgency I have posted with insufficient information.

I have a file whose last 8 characters form the update date information in the format yyyymmdd. Say a file of LRECL 80 with last 8 characters
forming the date field.
Input:

1234 asdf efgh ..........20080721
5678 asdf efgh ..........20080722
1111 asdf efgh ..........20080722
2222 asdf efgh ..........20080721
3333 asdf efgh ..........20080722

I wish to sort this file based on update date and then extract the records which were updated latest (the latest may/ may not be current date - Please give solution for both - 1. If i need to extract the file based on update date=current date - how to dynamically retrieve the system date and hence extract those records? 2. If i need to extract the records updated latest which is not equal to current date, then i should sort based on update date in descending order and then extract top few records till the update date doesn't change for the consecutive record from the previous one).

So required output:

5678 asdf efgh 20080722
1111 asdf efgh 20080722
3333 asdf efgh 20080722

Sort key is the last nine characters.

Please let me know in case you need further details
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: Tue Jul 22, 2008 11:42 pm    Post subject:
Reply with quote

Quote:
If i need to extract the file based on update date=current date


You can use a DFSORT job like this:

Code:

//CURDATE   EXEC  PGM=ICEMAN
//SYSOUT    DD  SYSOUT=*
//SORTIN DD DSN=...  input file
//SORTOUT DD DSN=...  output file
//SYSIN    DD    *
  OPTION COPY
  INCLUDE COND=(73,8,ZD,EQ,DATE1P)
/*


Quote:
If i need to extract the records updated latest


You can use a DFSORT job like this:

Code:

//HIDT1  EXEC  PGM=ICEMAN
//SYSOUT    DD  SYSOUT=*
//SORTIN DD DSN=...  input file
//SORTOUT DD DSN=&&S1,UNIT=SYSDA,SPACE=(TRK,(1,1)),DISP=(,PASS)
//SYSIN    DD    *
  OPTION COPY
* Create a DFSORT symbol as follows:
* HIDATE,+yyyymmdd
* where yyyymmdd is the latest date
  OUTFIL REMOVECC,NODETAIL,
    BUILD=(80X),
    TRAILER1=('HIDATE,+',MAX=(73,8,ZD,TO=ZD,LENGTH=8))
/*
//HIDT2    EXEC  PGM=ICEMAN
//SYSOUT    DD  SYSOUT=*
//SYMNAMES DD DSN=&&S1,DISP=(OLD,PASS)
//SORTIN DD DSN=...  input file
//SORTOUT DD DSN=...  output file
//SYSIN    DD    *
  OPTION COPY
* Use the HIDATE symbol in the INCLUDE statement
  INCLUDE COND=(73,8,ZD,EQ,HIDATE)
/*
Back to top
View user's profile Send private message
Prema Suresh

New User


Joined: 12 Jun 2008
Posts: 8
Location: Bangalore

PostPosted: Fri Aug 01, 2008 1:57 pm    Post subject:
Reply with quote

Thanks Frank. It worked.

Thanks a lot for your help!
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 DB2 query Using Recursion, Converting... smilewithashu2 DB2 1 Tue Jan 03, 2017 12:50 pm
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
No new posts Removing Duplicates based on certain ... chandracdac DFSORT/ICETOOL 8 Fri Dec 09, 2016 4:40 am
No new posts Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm
No new posts SQZ pipe delimited file with 1 column... Rick Silvers DFSORT/ICETOOL 2 Sun Nov 06, 2016 8:11 pm


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