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
 

 

Filter records using dates in different columns

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM
View previous topic :: :: View next topic  
Author Message
HappySrinu

Active User


Joined: 22 Jan 2008
Posts: 195
Location: India

PostPosted: Thu Jul 10, 2008 4:56 pm    Post subject: Filter records using dates in different columns
Reply with quote

have a requirement some thing like...

111 aaaa 01may07 20:30 30may07 20:10
221 bbbb 04may07 20:30 30may07 20:10
444 ccdc 01may08 20:30 30may08 20:10
444 ccdc 05may08 20:30 30may08 20:10

I am trying to use ICETOOL and want the records only having dates on MAY08.

Any suggestions pls
Back to top
View user's profile Send private message

karthikr44

Active User


Joined: 25 Aug 2007
Posts: 235
Location: Chennai

PostPosted: Thu Jul 10, 2008 5:47 pm    Post subject: Reply to: Filter records using dates in different columns
Reply with quote

Hi,

U can use the following

Code:

  SORT FIELDS=COPY
  INCLUDE COND=(01,80,SS,EQ,C'MAY08')


Regards
R KARTHIK
Back to top
View user's profile Send private message
HappySrinu

Active User


Joined: 22 Jan 2008
Posts: 195
Location: India

PostPosted: Thu Jul 10, 2008 7:07 pm    Post subject: Reply to: Filter records using dates in different columns
Reply with quote

Thanks karthik but I am going to run the same job every month. for which i don't want to change every month do run it.

Is there any other way
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Thu Jul 10, 2008 7:09 pm    Post subject: Reply to: Filter records using dates in different columns
Reply with quote

this is really a different question icon_biggrin.gif

still somebody has to do it ( the parameter change - I mean )
make it a two step job,
the first one building dynamically the control cards
the second the real thing
or use a scheduler facilities for parameter generation
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Thu Jul 10, 2008 7:26 pm    Post subject:
Reply with quote

It's amazing how quickly developers have returned to 2 digit years and unsortable, uncomparable date formats.
Back to top
View user's profile Send private message
gcicchet

Senior Member


Joined: 28 Jul 2006
Posts: 1703
Location: Australia

PostPosted: Thu Jul 10, 2008 7:57 pm    Post subject:
Reply with quote

Hi,

here is one way to generate the include statement
Code:
//S1       EXEC PGM=SORT                                               
//SYSOUT   DD SYSOUT=*                                                 
//SORTIN   DD *                                                         
RECORD                                                                 
/*                                                                     
//SORTOUT  DD SYSOUT=*                                                 
//SYSIN    DD *                                                         
  OPTION COPY                                                           
  INREC OVERLAY=(81:DATE1,                                             
  1:C' INCLUDE COND=(1,80,SS,EQ,C''',32:83,2,34:C''')')                 
  OUTREC IFTHEN=(WHEN=(85,2,CH,EQ,C'01'),                               
                OVERLAY=(029:C'JAN')),                                 
        IFTHEN=(WHEN=(85,2,CH,EQ,C'02'),                               
                OVERLAY=(029:C'FEB')),                                 
        IFTHEN=(WHEN=(85,2,CH,EQ,C'03'),                               
                OVERLAY=(029:C'MAR')),                                 
        IFTHEN=(WHEN=(85,2,CH,EQ,C'04'),                               
                OVERLAY=(029:C'APR')),                                 
        IFTHEN=(WHEN=(85,2,CH,EQ,C'05'),                               
                OVERLAY=(029:C'MAY')),                                 
        IFTHEN=(WHEN=(85,2,CH,EQ,C'06'),                               
                OVERLAY=(029:C'JUN')),                   
        IFTHEN=(WHEN=(85,2,CH,EQ,C'07'),                 
                OVERLAY=(029:C'JUL')),                   
        IFTHEN=(WHEN=(85,2,CH,EQ,C'08'),                 
                OVERLAY=(029:C'AUG')),                   
        IFTHEN=(WHEN=(85,2,CH,EQ,C'09'),                 
                OVERLAY=(029:C'SEP')),                   
        IFTHEN=(WHEN=(85,2,CH,EQ,C'10'),                 
                OVERLAY=(029:C'OCT')),                   
        IFTHEN=(WHEN=(85,2,CH,EQ,C'11'),                 
                OVERLAY=(029:C'NOV')),                   
        IFTHEN=(WHEN=(85,2,CH,EQ,C'12'),                 
                OVERLAY=(029:C'DEC'))                     
 OUTFIL BUILD=(1,80)                                     
/*                                                       



Gerry
Back to top
View user's profile Send private message
Antonio Barata
Warnings : 1

New User


Joined: 04 Apr 2007
Posts: 37
Location: Lisbon, Portugal

PostPosted: Thu Jul 10, 2008 8:34 pm    Post subject:
Reply with quote

Quote:
It's amazing how quickly developers have returned to 2 digit years and unsortable, uncomparable date formats.

If somebody has to think about it again, it will be in 2099 and I think nobody expects to be here by the time... icon_lol.gif
But, only 9 years have passed since the end of last century and you still have to deal with those dates.
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 Jul 10, 2008 10:45 pm    Post subject:
Reply with quote

Srini,

Assuming you want to test for monyy where mon is the current month and yy is the current year, here's a better DFSORT job to do what you asked for.

Code:

//S1    EXEC  PGM=ICEMAN
//SYSOUT    DD  SYSOUT=*
//SORTIN DD *
RECORD
/*
//SORTOUT DD DSN=&&S1,UNIT=SYSDA,SPACE=(TRK,(1,1)),DISP=(,PASS)
//SYSIN    DD    *
  OPTION COPY
  INREC IFOUTLEN=80,
   IFTHEN=(WHEN=INIT,BUILD=(DATE2)),
   IFTHEN=(WHEN=INIT,
    BUILD=(C'TDATE,''',
      5,2,CHANGE=(3,C'01',C'jan',C'02',C'feb',C'03',C'mar',
       C'04',C'apr',C'05',C'may',C'06',C'jun',C'07',C'jul',
       C'08',C'aug',C'09',C'sep',C'10',C'oct',C'11',C'nov',
       C'12',C'dec'),3,2,C''''))
/*
//S2  EXEC  PGM=ICEMAN
//SYSOUT    DD  SYSOUT=*
//SYMNAMES DD DSN=&&S1,DISP=(OLD,PASS)
//SORTIN DD DSN=... input file (FB/80)
//SORTOUT DD DSN=...  output file (FB/80)
//SYSIN    DD    *
  OPTION COPY
  INCLUDE COND=(1,80,SS,EQ,TDATE)
/*
Back to top
View user's profile Send private message
HappySrinu

Active User


Joined: 22 Jan 2008
Posts: 195
Location: India

PostPosted: Fri Jul 11, 2008 3:16 pm    Post subject: Reply to: Filter records using dates in different columns
Reply with quote

Thanks much for all your suggestions.

I would have mentioned before, but any way the file i have to use is LRECL=4096, BLKSIZE=4096 it's an CSV file.
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: Fri Jul 11, 2008 8:34 pm    Post subject:
Reply with quote

Quote:
the file i have to use is LRECL=4096


Then just change my DFSORT job to the following:

Code:

//S1    EXEC  PGM=ICEMAN
//SYSOUT    DD  SYSOUT=*
//SORTIN DD *
RECORD
/*
//SORTOUT DD DSN=&&S1,UNIT=SYSDA,SPACE=(TRK,(1,1)),DISP=(,PASS)
//SYSIN    DD    *
  OPTION COPY
  INREC IFOUTLEN=80,
   IFTHEN=(WHEN=INIT,BUILD=(DATE2)),
   IFTHEN=(WHEN=INIT,
    BUILD=(C'TDATE,''',
      5,2,CHANGE=(3,C'01',C'jan',C'02',C'feb',C'03',C'mar',
       C'04',C'apr',C'05',C'may',C'06',C'jun',C'07',C'jul',
       C'08',C'aug',C'09',C'sep',C'10',C'oct',C'11',C'nov',
       C'12',C'dec'),3,2,C''''))
/*
//S2  EXEC  PGM=ICEMAN
//SYSOUT    DD  SYSOUT=*
//SYMNAMES DD DSN=&&S1,DISP=(OLD,PASS)
//SORTIN DD DSN=... input file (FB/4096)
//SORTOUT DD DSN=...  output file (FB/4096)
//SYSIN    DD    *
  OPTION COPY
  INCLUDE COND=(1,4096,SS,EQ,TDATE)
/*
Back to top
View user's profile Send private message
HappySrinu

Active User


Joined: 22 Jan 2008
Posts: 195
Location: India

PostPosted: Mon Jul 14, 2008 3:43 pm    Post subject: Reply to: Filter records using dates in different columns
Reply with quote

Frank,
Thanks much.

quick clarifications again:
1. Does the input/output files should be FB or can i use VB also?
2. Does there is a way to pick exact column wise values instead of checking entire row giving as 1,4096 in input card.

Please clarify.
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 14, 2008 9:12 pm    Post subject:
Reply with quote

Quote:
Does the input/output files should be FB or can i use VB also?


VB would be slightly different. Whereas for FB, you'd use 1,n, in the INCLUDE statement, for VB you'd use 5,n-4. You would probably also want to use VLSCMP for VB:

Code:

   OPTION VLSCMP


Quote:
Does there is a way to pick exact column wise values instead of checking entire row giving as 1,4096 in input card.


Yes, of course - that's the simpler case. You would just use:

Code:

   INCLUDE COND=(p,5,EQ,EQ,TDATE)


where p is the starting position.

If you're not familiar with DFSORT and DFSORT's ICETOOL, I'd suggest reading through "z/OS DFSORT: Getting Started". It's an excellent tutorial, with lots of examples, that will show you how to use DFSORT, DFSORT's ICETOOL and DFSORT Symbols. You can access it online, along with all of the other DFSORT books, from:

www.ibm.com/servers/storage/support/software/sort/mvs/srtmpub.html
Back to top
View user's profile Send private message
HappySrinu

Active User


Joined: 22 Jan 2008
Posts: 195
Location: India

PostPosted: Wed Jul 16, 2008 11:12 am    Post subject: Reply to: Filter records using dates in different columns
Reply with quote

The link is very value added Frank.

Thanks much.
Back to top
View user's profile Send private message
HappySrinu

Active User


Joined: 22 Jan 2008
Posts: 195
Location: India

PostPosted: Wed Jul 16, 2008 4:37 pm    Post subject: Reply to: Filter records using dates in different columns
Reply with quote

I am trying to us jcl

Code:

//S2  EXEC  PGM=ICETOOL                       
//SYSOUT    DD  SYSOUT=*                       
//*SYMNAMES DD DSN=&&S1,DISP=(OLD,PASS)       
//SORTIN DD DISP=SHR,DSN=XXXX.NNNN.PPPPPP 
//SORTOUT DD DISP=SHR,DSN=XXXX.NNNN.PPPPPP
//SYSIN    DD    *                             
  OPTION COPY VLSCMP                           
  INCLUDE COND=(5,4092,SS,EQ,C'MAY08')         
/*     


XXXX.NNNN.PPPPPP - is VB length of 4096.

but unable to get the required output.

want to check with hard coding month&yr instead of generating in previous for testing purpose but RC:20
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 Jul 16, 2008 9:04 pm    Post subject:
Reply with quote

You have PGM=ICETOOL instead of PGM=ICEMAN. PGM=ICETOOL requires different DD statements which explains why you got the RC=20 (no message data set).

It appears you are trying to use the same data set for input and output. You shouldn't do that with COPY.

Also, you have a blank instead of a comma between COPY and VLSCMP so VLSCMP is ignored as a remark.

Your job should look like this:

Code:

//S2  EXEC  PGM=ICEMAN                       
//SYSOUT    DD  SYSOUT=*                       
//*SYMNAMES DD DSN=&&S1,DISP=(OLD,PASS)       
//SORTIN DD DISP=SHR,DSN=XXXX.NNNN.PPPPPP 
//SORTOUT DD DSN=...  output file
//SYSIN    DD    *                             
  OPTION COPY,VLSCMP                           
  INCLUDE COND=(5,4092,SS,EQ,C'MAY08')         
/*     
Back to top
View user's profile Send private message
HappySrinu

Active User


Joined: 22 Jan 2008
Posts: 195
Location: India

PostPosted: Thu Jul 17, 2008 6:37 pm    Post subject: Reply to: Filter records using dates in different columns
Reply with quote

Frank,
thanks for your code, i used it end up with below message. It more looks i can't use ICEMAN on this machine.

PRODUCT LICENSED FOR CPU SERIAL NUMBER 38F4E, MODEL 2094 713
SYSIN :
OPTION COPY,VLSCMP
*
INCLUDE COND=(5,4092,SS,EQ,C'MAY08')
WER268A OPTION STATEMENT : SYNTAX ERROR
Back to top
View user's profile Send private message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8593
Location: Back in jolly old England

PostPosted: Thu Jul 17, 2008 6:44 pm    Post subject:
Reply with quote

Methinks that the WER* message codes indicates that you are using SYNCSORT not DFSORT
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 -> JCL & VSAM All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts How to split the records using the am... vnktrrd DFSORT/ICETOOL 24 Fri Oct 28, 2016 7:33 pm
No new posts Comparing Decimal and CHAR columns rakesh17684 DB2 7 Thu Oct 20, 2016 2:33 am
No new posts Sort records based on numeric field. Alks SYNCSORT 2 Wed Oct 19, 2016 10:14 pm


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