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
 

 

ICETOOL - Date format DB2 (DD.MM.YYYY)

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

New User


Joined: 27 Aug 2008
Posts: 32
Location: Brazil

PostPosted: Thu Aug 28, 2008 8:26 am    Post subject: ICETOOL - Date format DB2 (DD.MM.YYYY)
Reply with quote

Hello,

I need some help here. My Dataset is a UNLOAD (Table DB2), then I need a way to sort this file by field in date format (DD.MM.YYYY) and excluding dates less than "22.07.2008". Is there a way to use ICETOOL or SORT without INREC/OUTREC, just using OMIT or INCLUDE option?


Sorry, my English isn't good!
Back to top
View user's profile Send private message

Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2275
Location: @my desk

PostPosted: Thu Aug 28, 2008 10:34 am    Post subject:
Reply with quote

Carlos,

Can you post here the following.

1 . Sample input and output records
2 . LRECL/RECFM of your input and output files, positions of relevant fields

Thanks,
Arun
Back to top
View user's profile Send private message
jackare

New User


Joined: 27 Aug 2008
Posts: 32
Location: Brazil

PostPosted: Thu Aug 28, 2008 5:24 pm    Post subject:
Reply with quote

arcvns wrote:
Carlos,

Can you post here the following.

1 . Sample input and output records
2 . LRECL/RECFM of your input and output files, positions of relevant fields

Thanks,
Arun


Ok.. It is:

The input file is:

03.03.2008RIBEIRAO
03.03.2008RIBEIRAO
30.04.2008GUATAPARA
30.04.2008GUATAPARA
01.04.2008ITAPUI
03.04.2008JAU
03.04.2008SALES OLIVEIRA
20.05.2008GUATAPARA
13.05.2008JAU
30.07.2008GUATAPARA
30.07.2008GUATAPARA
30.07.2008GUATAPARA
21.08.2008GUATAPARA
13.08.2008MINEIROS

Position from 01 to 10 - field "date of generation" - format DD.MM.YYYY
Position from 11 to 25 - field "City" - format char(15)
LRECL is 25 e RECFM is FB
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2275
Location: @my desk

PostPosted: Thu Aug 28, 2008 6:22 pm    Post subject:
Reply with quote

Carlos,

Here's a SORT job which would serve your requirement.
Code:
//STEP1    EXEC PGM=SORT                       
//SYSOUT   DD SYSOUT=*                         
//SORTOUT  DD SYSOUT=*                         
//SORTIN   DD *                                 
03.03.2008RIBEIRAO                             
03.03.2008RIBEIRAO                             
30.04.2008GUATAPARA                             
30.04.2008GUATAPARA                             
01.04.2008ITAPUI                               
03.04.2008JAU                                   
03.04.2008SALES OLIVEIRA                       
20.05.2008GUATAPARA                             
13.05.2008JAU                                   
30.07.2008GUATAPARA                             
30.07.2008GUATAPARA                             
30.07.2008GUATAPARA                             
21.08.2008GUATAPARA                             
13.08.2008MINEIROS                             
//SYSIN    DD *                                 
  INREC OVERLAY=(26:7,4,4,2,1,2)               
  SORT FIELDS=(26,8,ZD,A)                       
  OUTFIL OMIT=(26,8,ZD,LT,20080722),BUILD=(1,25)
/*


SORTOUT
Code:
30.07.2008GUATAPARA
30.07.2008GUATAPARA
30.07.2008GUATAPARA
13.08.2008MINEIROS
21.08.2008GUATAPARA


Thanks,
Arun
Back to top
View user's profile Send private message
jackare

New User


Joined: 27 Aug 2008
Posts: 32
Location: Brazil

PostPosted: Thu Aug 28, 2008 8:40 pm    Post subject:
Reply with quote

Ok! Thanks a lot! I gonna try this. However, is there another way using "DATE? format"as I saw in the topic http://ibmmainframes.com/viewtopic.php?t=32000.
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: Thu Aug 28, 2008 10:01 pm    Post subject:
Reply with quote

Quote:
Is there a way to use ICETOOL or SORT without INREC/OUTREC, just using OMIT or INCLUDE option?


No, because the DD.MM.YYYY format of your date cannot be collated correctly without transforming it to YYYYMMDD. If your date was in a form that could be collated correctly without transforming (such as YYYY.MM.DD) then you could use INCLUDE or OMIT directly.

Quote:
However, is there another way using "DATE? format"


I assume you mean DATEn (e.g. DATE1). DATEn is used for the current date in various forms (or a date relative to the current date such as DATE1-30). Since you said you want to use a specific date of "22.07.2008", DATEn isn't relevant. If you want to use the current date, then you could use OMIT with an appropriate form of DATEn vs the transformed date.
Back to top
View user's profile Send private message
jackare

New User


Joined: 27 Aug 2008
Posts: 32
Location: Brazil

PostPosted: Fri Aug 29, 2008 12:20 am    Post subject:
Reply with quote

OK! Thanks a lot!
I going to use the arcvns's tip.
Thanks Frank !
Thanks arcvns!
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 Validate date and numeric fields and ... Rick Silvers DFSORT/ICETOOL 6 Thu May 11, 2017 6:51 pm
No new posts Using ICETOOL, how we can seperate th... bshkris SYNCSORT 5 Tue May 09, 2017 8:33 pm
No new posts LISTIDR compiled date/time jerryte IBM Tools 3 Thu Apr 20, 2017 7:37 pm
No new posts Group elements using ICETOOL Learncoholic DFSORT/ICETOOL 2 Fri Apr 14, 2017 11:01 am
No new posts SUBSTR for Date functions Shaheen Shaik DB2 4 Thu Apr 06, 2017 2:14 pm


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