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
 

 

Extracing records for a given period (in ddmmyy format)

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

Senior Member


Joined: 29 Jun 2006
Posts: 1439
Location: Bangalore,India

PostPosted: Thu Jan 18, 2007 5:49 pm    Post subject: Extracing records for a given period (in ddmmyy format)
Reply with quote

Hi All,

I have a date column (format ddmmyy) as -

Code:


3 BATC-DATE-OPENED              4/BI   250698
3 BATC-DATE-CLOSED              4/BI   260698



My requirement is to extract all the records based on -

(1) Both opened and closed date must be equal

and

(2) Records for a month (say If Im running the job on 18 Jan 2007, I should get all the records between dates 17 December 2006 to 18 Jan 2007).

I went through the SORTTRCK, but couldn't find anything with ddmmyy format.

Regards,
Murali
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 Jan 18, 2007 9:27 pm    Post subject:
Reply with quote

Some conversion will be necessary.

"Records for a month" - will 30 days do?

What is the RECFM and LRECL of your input file?
Back to top
View user's profile Send private message
murmohk1

Senior Member


Joined: 29 Jun 2006
Posts: 1439
Location: Bangalore,India

PostPosted: Fri Jan 19, 2007 10:49 am    Post subject: Re: Extracing records for a given period (in ddmmyy format)
Reply with quote

Im coding a new job for extracting the records and the job run is not scheduled yet.

But its been decided that the job has to go as a monthly job. Assuming the run is on 1st of every month (mostly this is going to happen), the job has to extract all the records whose date fall in the previous month.

Ex: If the job runs on 1 Jan 2007, the final file should have records between 01 Dec 2006 to 31 Dec 2006.

Note: Since the job run is not deceided yet, I had given example of 18 Jan 2007 in my previous post.


I had tried the following for converting DDMMYY to YYMMDD-

Code:



Lrecl = 267
Recfm=FB

Key -
    BATC-BATC-KEY PIC X(14)  @ col 9

Date Variables -
    BATC-DATE-OPENED        PIC 9(06)   COMP   (starts at 61 col)
    BATC-DATE-CLOSED        PIC 9(06)   COMP   (starts at 65 col)



Step 1:

//SORTIN DD DSN=W.G.PRODSCHM.POL.BATC,DISP=SHR   BATCH FILE of lrecl-267
//*
//SORTOUT  DD DSN=ISTEST.CR7011.W.G.PRODSCHM.POL.BATCCNVT,
//    DISP=(,CATLG,DELETE),SPACE=(CYL,(900,100),RLSE),
//    LRECL=271,RECFM=FB
//*
//SYSIN  DD *

** convert dates from BI to ZD format
INREC FIELDS=(1,60,
     61,4,BI,TO=ZD,LENGTH=6,        * BATC-DATE-OPENED
     65,4,BI,TO=ZD,LENGTH=6,        * BATC-DATE-CLOSED
     69,199)

SORT FIELDS=COPY

OUTREC FIELDS=(1,60,
               65,2,     ** YY COMES FIRST FOR BATC-DATE-OPENED       
               63,2,     ** MM FOLLOWS
               61,2,     ** DD
               71,2,     ** YY COMES FIRST FOR BATC-DATE-CLOSED       
               69,2,     ** MM FOLLOWS
               67,2,     ** DD
               69,199)
/*






Kindly let me know, is there a way to extract the data for a month (considering all the options explained above)?

Thanks & Regards,
Murali
Back to top
View user's profile Send private message
manihcl85
Warnings : 1

New User


Joined: 11 Jan 2007
Posts: 52
Location: chennai

PostPosted: Fri Jan 19, 2007 3:02 pm    Post subject:
Reply with quote

write a rexx routine to change the date and submit the member. and also write an jcl to kick off the rexx program. and schedule the same jcl according to ur requirment.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Jan 19, 2007 9:58 pm    Post subject:
Reply with quote

Hello,

If there is ever a chance that this job will be run on the the 30th or 31st of a month (some kind of scheduling exception), this will need to be accounted for when determining which data to use.
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 Jan 19, 2007 10:02 pm    Post subject:
Reply with quote

Murali,

If I understand correctly what you want, it seems like you're overcomplicating it. It seems you want to keep the records from the previous month that have the same open and close date. You can compare the binary fields directly to determine if the open and close date are the same. You can check for the previous month by comparing the yymm portion of the date to yymm-1. Here's a DFSORT job that does that.

If you really want to check for the previous 30 days rather than the previous month, let me know and I can show you how to modify the job to do that.

You'll need z/OS DFSORT V1R5 PTF UK90007 or DFSORT R14 PTF UK90006 (April, 2006) in order to use DATE2-1. If you don't have the April, 2006 PTF, ask your System Programmer to install it (it's free). For complete details on all of the new DFSORT and ICETOOL functions available with the April, 2006 PTF, see:

www.ibm.com/servers/storage/support/software/sort/mvs/peug/

Code:

//S1    EXEC  PGM=ICEMAN
//SYSOUT    DD  SYSOUT=*
//SORTIN DD DSN=...  input file (FB/267)
//SORTOUT DD DSN=... output file (FB/267)
//SYSIN    DD    *
  OPTION COPY
* Convert ddmmyy in BI format to Z'ddmmyy'
  INREC OVERLAY=(268:61,4,BI,TO=ZD,LENGTH=6,
                 274:65,4,BI,TO=ZD,LENGTH=6,
* Convert Z'ddmmyy' to C'20yymm'
                 280:C'20',272,2,270,2)
* Keep record if open date equals close date and
* 20yymm date equals yyyymm-1 date
  OUTFIL INCLUDE=(61,4,BI,EQ,65,4,BI,AND,
    280,6,CH,EQ,DATE2-1),
    BUILD=(1,267)
/*
Back to top
View user's profile Send private message
murmohk1

Senior Member


Joined: 29 Jun 2006
Posts: 1439
Location: Bangalore,India

PostPosted: Mon Jan 22, 2007 8:43 am    Post subject:
Reply with quote

Thanks all and particularly Frank for the solution. Depending on job schedule, I will seek your help again.

Regards,
Murali
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 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 Match or compare two files in VB Format anatol DFSORT/ICETOOL 14 Thu Nov 03, 2016 7:41 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 Low values Results from VARCHAR FORMAT balaji81_k DB2 10 Thu Oct 20, 2016 1:18 am


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