DFSORT Selecting all records today - 365 days
Hi all. I'm trying to develop a simple purge using DFSORT that drops all records in the input older than 1 year. The target field is an externalized DB2 timestamp. So far, the following works, sort of, but is not very elegant. Any suggestions? SORT FIELDS=(COPY) INCLUDE COND=(21,7,CH,EQ,C'2004-07',OR, 21,7,CH,EQ,C'2004-08',OR, 21,7,CH,EQ,C'2004-09',OR, 21,7,CH,EQ,C'2004-10',OR, 21,7,CH,EQ,C'2004-11',OR, 21,7,CH,EQ,C'2004-12',OR, 21,7,CH,EQ,C'2005-01',OR, 21,7,CH,EQ,C'2005-02',OR, 21,7,CH,EQ,C'2005-03',OR, 21,7,CH,EQ,C'2005-04',OR, 21,7,CH,EQ,C'2005-05',OR, 21,7,CH,EQ,C'2005-06',OR, 21,7,CH,EQ,C'2005-07') Edward Long -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to [EMAIL PROTECTED] with the message: GET IBM-MAIN INFO Search the archives at http://bama.ua.edu/archives/ibm-main.html
Re: DFSORT Selecting all records today - 365 days
-Original Message- From: IBM Mainframe Discussion List On Behalf Of Ed Long Hi all. I'm trying to develop a simple purge using DFSORT that drops all records in the input older than 1 year. The target field is an externalized DB2 timestamp. So far, the following works, sort of, but is not very elegant. Any suggestions? SORT FIELDS=(COPY) INCLUDE COND=(21,7,CH,EQ,C'2004-07',OR, ... 21,7,CH,EQ,C'2005-07') How about INCLUDE COND=(21,7,CH,GE,C'2004-07') or OMIT COND=(21,7,CH,LT,C'2004-07') ? -jc- -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to [EMAIL PROTECTED] with the message: GET IBM-MAIN INFO Search the archives at http://bama.ua.edu/archives/ibm-main.html
Re: DFSORT Selecting all records today - 365 days
On Tue, 2 Aug 2005 09:59:27 -0700 Ed Long [EMAIL PROTECTED] wrote: :Hi all. :I'm trying to develop a simple purge using DFSORT that drops all records in the input older than 1 year. The target field is an externalized DB2 timestamp. :So far, the following works, sort of, but is not very elegant. Any suggestions? :SORT FIELDS=(COPY) :INCLUDE COND=(21,7,CH,EQ,C'2004-07',OR, :21,7,CH,EQ,C'2004-08',OR, :21,7,CH,EQ,C'2004-09',OR, :21,7,CH,EQ,C'2004-10',OR, :21,7,CH,EQ,C'2004-11',OR, :21,7,CH,EQ,C'2004-12',OR, :21,7,CH,EQ,C'2005-01',OR, :21,7,CH,EQ,C'2005-02',OR, :21,7,CH,EQ,C'2005-03',OR, :21,7,CH,EQ,C'2005-04',OR, :21,7,CH,EQ,C'2005-05',OR, :21,7,CH,EQ,C'2005-06',OR, :21,7,CH,EQ,C'2005-07') Sort doesn't have a greater than / less than condition? -- Binyamin Dissen [EMAIL PROTECTED] http://www.dissensoftware.com Director, Dissen Software, Bar Grill - Israel Should you use the mailblocks package and expect a response from me, you should preauthorize the dissensoftware.com domain. I very rarely bother responding to challenge/response systems, especially those from irresponsible companies. -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to [EMAIL PROTECTED] with the message: GET IBM-MAIN INFO Search the archives at http://bama.ua.edu/archives/ibm-main.html
Re: DFSORT Selecting all records today - 365 days
- Original Message - From: Ed Long [EMAIL PROTECTED] Newsgroups: bit.listserv.ibm-main Sent: Tuesday, August 02, 2005 1:26 PM Subject: DFSORT Selecting all records today - 365 days Hi all. I'm trying to develop a simple purge using DFSORT that drops all records in the input older than 1 year. The target field is an externalized DB2 timestamp. So far, the following works, sort of, but is not very elegant. Any suggestions? SORT FIELDS=(COPY) INCLUDE COND=(21,7,CH,EQ,C'2004-07',OR, INCLUDE COND=(21,7,CH,GT,C'2004-06')??? -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to [EMAIL PROTECTED] with the message: GET IBM-MAIN INFO Search the archives at http://bama.ua.edu/archives/ibm-main.html
Re: DFSORT Selecting all records today - 365 days
Please accept this dual purpose response (both to John and to Ben). Yes, the include clause can be coded as GT,GE,LT,LE; however, for some, as yet, unknown reason, that construct continued to include older records. The present construct only includes the desired records, but is SQL like in its ponderousness. Thanks to both of you for the assist; I guess I was hoping for some kind of special register ala SQL as in GE (CURRENT DATE - 365 DAYS). Chase, John [EMAIL PROTECTED] wrote: -Original Message- From: IBM Mainframe Discussion List On Behalf Of Ed Long Hi all. I'm trying to develop a simple purge using DFSORT that drops all records in the input older than 1 year. The target field is an externalized DB2 timestamp. So far, the following works, sort of, but is not very elegant. Any suggestions? SORT FIELDS=(COPY) INCLUDE COND=(21,7,CH,EQ,C'2004-07',OR, ... 21,7,CH,EQ,C'2005-07') How about INCLUDE COND=(21,7,CH,GE,C'2004-07') or OMIT COND=(21,7,CH,LT,C'2004-07') ? -jc- -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to [EMAIL PROTECTED] with the message: GET IBM-MAIN INFO Search the archives at http://bama.ua.edu/archives/ibm-main.html Edward Long -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to [EMAIL PROTECTED] with the message: GET IBM-MAIN INFO Search the archives at http://bama.ua.edu/archives/ibm-main.html
Re: DFSORT Selecting all records today - 365 days
On 2-Aug-2005, [EMAIL PROTECTED] (Binyamin Dissen) wrote: :INCLUDE COND=(21,7,CH,EQ,C'2004-07',OR, :21,7,CH,EQ,C'2004-08',OR, :21,7,CH,EQ,C'2004-09',OR, :21,7,CH,EQ,C'2004-10',OR, :21,7,CH,EQ,C'2004-11',OR, :21,7,CH,EQ,C'2004-12',OR, :21,7,CH,EQ,C'2005-01',OR, :21,7,CH,EQ,C'2005-02',OR, :21,7,CH,EQ,C'2005-03',OR, :21,7,CH,EQ,C'2005-04',OR, :21,7,CH,EQ,C'2005-05',OR, :21,7,CH,EQ,C'2005-06',OR, :21,7,CH,EQ,C'2005-07') Sort doesn't have a greater than / less than condition? I think he's wanting to have that date recognized as a date field. Here's how DFSORT handles dates: http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/iceca107/3.7.12?ACTION=MATCHESREQUEST=dateTYPE=FUZZYSHELF=DT=19990113103506CASE=searchTopic=TOPICsearchText=TEXTsearchIndex=INDEXrank=RANKScrollTOP=FIRSTHIT#FIRSTHIT + You can use DFSORT's Y2 formats in conjunction with the century window in + effect, as follows: * + Use the full date formats (Y2T, Y2U, Y2V, Y2W, Y2X and Y2Y) to compare + a two-digit year date field to a two-digit year date constant (Y + constant) or to another two-digit year date field. * + Use the year formats (Y2C, Y2Z, Y2S, Y2P, Y2D and Y2B) to compare a + two-digit year field to a two-digit year constant (Y constant) or to + another two-digit year field. + For example, you can include only those records for which a Z'yymm' date + field is between January 1996 and March 2005. Or you can include only + those records for which a P'dddyy' field is less than another P'dddyy' + field. -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to [EMAIL PROTECTED] with the message: GET IBM-MAIN INFO Search the archives at http://bama.ua.edu/archives/ibm-main.html
Re: DFSORT Selecting all records today - 365 days
-Original Message- From: IBM Mainframe Discussion List On Behalf Of Ed Long Please accept this dual purpose response (both to John and to Ben). Yes, the include clause can be coded as GT,GE,LT,LE; however, for some, as yet, unknown reason, that construct continued to include older records. OK, how about: INCLUDE COND=(21,7,GE,C'2004-07,AND, 21,7,LE,C'2005-07'),FORMAT=CH -jc- -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to [EMAIL PROTECTED] with the message: GET IBM-MAIN INFO Search the archives at http://bama.ua.edu/archives/ibm-main.html
Re: DFSORT Selecting all records today - 365 days
I guess I was hoping for some kind of special register ala SQL as in GE (CURRENT DATE - 365 AYS). See http://www-03.ibm.com/servers/storage/support/software/sort/mvs/tricks/srtms t03.html#t07 and http://www-03.ibm.com/servers/storage/support/software/sort/mvs/tricks/srtms t03.html#t7r for possible techniques. [beware URL wrap] -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to [EMAIL PROTECTED] with the message: GET IBM-MAIN INFO Search the archives at http://bama.ua.edu/archives/ibm-main.html
Re: DFSORT Selecting all records today - 365 days
The self-contained REXX code example below will generate a formatted OMIT
statement (using a 365 day cutoff) to an output DD (passed as the REXX EXEC
invocation argument).
Sincerely,
Scott Barry
SBBWorks, Inc.
___
//STEP0EXEC PGM=IEBGENER
//SYSPRINT DD DUMMY
//SYSINDD DUMMY
//* TEMPORARY PDS ALLOCATION BELOW WITH MEMBER NAMED REXXPGM.
//SYSUT2 DD DISP=(NEW,PASS),
// DSN=REXXPDS(REXXPGM),
// UNIT=SYSALLDA,SPACE=(TRK,(1,1,1))
//* REXX CODE BELOW IS CREATED IN SYSUT2 MEMBER NAMED ABOVE.
//SYSUT1 DD DATA,DLM=ZZ
/* REXX */
arg outdd
cutoff = DATE('B') - 365
omitstr = substr(DATE('S',cutoff,'B'),1,4)||'-'||,
substr(DATE('S',cutoff,'B'),5,2)
queue OMIT COND=(21,7,CH,LT,C'omitstr')
EXECIO * DISKW outdd (FINIS
exit
ZZ
//*
//STEP1EXEC PGM=IRXJCL,PARM='REXXPGM SORTFILT'
//SYSEXEC DD DISP=(OLD,DELETE),DSN=REXXPDS
//SORTFILT DD SYSOUT=*
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD DUMMY
-Original Message-
From: IBM Mainframe Discussion List On Behalf Of Ed Long
Hi all.
I'm trying to develop a simple purge using DFSORT that drops
all records in the input older than 1 year. The target field
is an externalized DB2 timestamp.
So far, the following works, sort of, but is not very
elegant. Any suggestions?
SORT FIELDS=(COPY)
INCLUDE COND=(21,7,CH,EQ,C'2004-07',OR,
...
21,7,CH,EQ,C'2005-07')
--
For IBM-MAIN subscribe / signoff / archive access instructions,
send email to [EMAIL PROTECTED] with the message: GET IBM-MAIN INFO
Search the archives at http://bama.ua.edu/archives/ibm-main.html
Re: DFSORT Selecting all records today - 365 days
Ed Long wrote: Yes, the include clause can be coded as GT,GE,LT,LE; however, for some, as yet, unknown reason, that construct continued to include older records. Which construct? Older records with what dates? DFSORT has no problem handling GT, GE, LT, LE for INCLUDE or OMIT, so I'm wondering what the user error was here. :-) The present construct only includes the desired records, but is SQL like in its ponderousness. The following non-ponderous construct is equivalent to the ponderous construct and gives the same results: INCLUDE COND=(21,7,CH,GE,C'2004-07',AND,21,7,CH,LE,C'2005-07') Thanks to both of you for the assist; I guess I was hoping for some kind of special register ala SQL as in GE (CURRENT DATE - 365 DAYS). Well, you're mixing up current date formats here. If your date is a mm date (or variation), then you'd want to subtract 12 months, not 365 days. If your date was a mmdd or ddd variation, then you'd want to substract 365 days. DFSORT has DATE2 for mm, but doesn't allow addition or subraction of months from it. DFSORT has DATE1 for mmdd and DATE3 for ddd, but doesn't allow addition or subtraction of days from it. However, we consider that a high priority candidate for a future enhancement to DFSORT. Frank Yaeger - DFSORT Team (IBM) Specialties: ICETOOL, IFTHEN, OVERLAY, Symbols, Migration = DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort/ -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to [EMAIL PROTECTED] with the message: GET IBM-MAIN INFO Search the archives at http://bama.ua.edu/archives/ibm-main.html
Re: DFSORT Selecting all records today - 365 days
craig.schneiderwent wrote: See http://www-03.ibm.com/servers/storage/support/software/sort/mvs/tricks/srtms t03.html#t07 and http://www-03.ibm.com/servers/storage/support/software/sort/mvs/tricks/srtms t03.html#t7r for possible techniques. [beware URL wrap] The t07 trick is for date, not for date-n. The t7r trick will work for mmdd-365 and can be adapted for ddd-365, but will not work for mm-12. Frank Yaeger - DFSORT Team (IBM) Specialties: ICETOOL, IFTHEN, OVERLAY, Symbols, Migration = DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort/ -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to [EMAIL PROTECTED] with the message: GET IBM-MAIN INFO Search the archives at http://bama.ua.edu/archives/ibm-main.html
Re: DFSORT Selecting all records today - 365 days
Thanks to all for participating. Here we go: I did previously see all of the date stuff in the Fine Manual, as subsetted and reprinted by Howard. None of the examples that I could find- note to Frank, please increase the number of examples - include the externalized DB2 format as in 2004-07-25 (Note the dashes and the 4 digit year). Note that the subset reprinted by Howard is all 2 digit years. I saw no examples of a 4 digit year - 2 digit month - 2 digit Day etc. Any of the INCLUDES so far, either of Frank's or either of mine, suffer from the same maintenance problem. I'm not actually getting 365 days worth; I'm getting somewhere between 365 and 395 days worth depending upon the day of the month that the job runs on. The job runs weekly on Saturday. Once a month I have to remember to edit this control card to include the new month and delete the oldest. I have trouble remembering lunch. Scott's suggestion may be the slickest so far; essentially generating the control cards weekly just before use. I tried following Craig's URL's; no such page on either one. I'll come back to that later this afternoon. I am curious though that no one has yet suggested ICETOOL. Thanks again to all of you for the assist. Frank Yaeger [EMAIL PROTECTED] wrote: Ed Long wrote: Yes, the include clause can be coded as GT,GE,LT,LE; however, for some, as yet, unknown reason, that construct continued to include older records. Which construct? Older records with what dates? DFSORT has no problem handling GT, GE, LT, LE for INCLUDE or OMIT, so I'm wondering what the user error was here. :-) The present construct only includes the desired records, but is SQL like in its ponderousness. The following non-ponderous construct is equivalent to the ponderous construct and gives the same results: INCLUDE COND=(21,7,CH,GE,C'2004-07',AND,21,7,CH,LE,C'2005-07') Thanks to both of you for the assist; I guess I was hoping for some kind of special register ala SQL as in GE (CURRENT DATE - 365 DAYS). Well, you're mixing up current date formats here. If your date is a mm date (or variation), then you'd want to subtract 12 months, not 365 days. If your date was a mmdd or ddd variation, then you'd want to substract 365 days. DFSORT has DATE2 for mm, but doesn't allow addition or subraction of months from it. DFSORT has DATE1 for mmdd and DATE3 for ddd, but doesn't allow addition or subtraction of days from it. However, we consider that a high priority candidate for a future enhancement to DFSORT. Frank Yaeger - DFSORT Team (IBM) Specialties: ICETOOL, IFTHEN, OVERLAY, Symbols, Migration = DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort/ -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to [EMAIL PROTECTED] with the message: GET IBM-MAIN INFO Search the archives at http://bama.ua.edu/archives/ibm-main.html Edward Long -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to [EMAIL PROTECTED] with the message: GET IBM-MAIN INFO Search the archives at http://bama.ua.edu/archives/ibm-main.html
Re: DFSORT Selecting all records today - 365 days
Edward Long wrote:
I did previously see all of the date stuff in the Fine Manual, as
subsetted and reprinted by Howard. None of the examples that I could
find- note to Frank, please increase the number of examples -
include the externalized DB2 format as in 2004-07-25 (Note the
dashes and the 4 digit year). Note that the subset reprinted by
Howard is all 2 digit years. I saw no examples of a 4 digit year -
2 digit month - 2 digit Day etc.
I'll include such an example in the APG for the next go around.
Any of the INCLUDES so far, either of Frank's or either of mine,
suffer from the same maintenance problem. I'm not actually getting
365 days worth; I'm getting somewhere between 365 and 395 days worth
depending upon the day of the month that the job runs on. The job
runs weekly on Saturday. Once a month I have to remember to edit
this control card to include the new month and delete the oldest. I
have trouble remembering lunch.
I'm a little confused at this point. Is the date in your records a -mm
date or a -mm-dd date? I thought it was a -mm date and you wanted
to subtract 12. I didn't have a good trick for that. If it's actually a
-mm-dd date and you want to subtract 365 days from it, then the Smart
DFSORT Trick at:
http://www.ibm.com/servers/storage/support/software/sort/mvs/tricks/srtmst03.html#t7r
could be adapted to set up the the current -mm-dd date - 365 days as a
symbol and use it in the INCLUDE statement as follows:
//TSOBATCH EXEC PGM=IKJEFT1A,DYNAMNBR=200
//SYSEXEC DD DSN=userid.REXXD.REXX,DISP=SHR
//SYSPRINT DD SYSOUT=*
//SYSTSPRT DD SYSOUT=*
//OUTFILE DD DSN=S1,UNIT=SYSDA,SPACE=(TRK,(1,1)),
// DISP=(,PASS),LRECL=80,RECFM=FB,DSORG=PS
//SYSTSIN DD *
%REXXEDL
//S2EXEC PGM=ICEMAN
//SYSOUTDD SYSOUT=*
//SYMNAMES DD DSN=S1,DISP=(OLD,PASS)
//SORTIN DD DSN=... input file
//SORTOUT DD DSN=... output file
//SYSINDD*
OPTION COPY
INCLUDE COND=(21,10,CH,GE,RELDATE)
/*
REXXEDL would have the following REXX code (sorry if this isn't the best
REXX code, but my knowledge of REXX is spotty - anyway it should give you
the idea):
/* REXX */
EXECIO 0 DISKW OUTFILE(OPEN /* OPEN FILE */
A=DATE('S',DATE('B')-365,'B')
B=SUBSTR(A,1,4)-SUBSTR(A,5,2)-SUBSTR(A,7,2)
QUEUE RELDATE,C||'||B||'
EXECIO 1 DISKW OUTFILE (FINIS
FREE FI(OUTFILE)
This creates a DFSORT Symbol like this:
RELDATE,C'-mm-dd'
for the current date - 365 days.
Scott's suggestion may be the slickest so far; essentially
generating the control cards weekly just before use.
I think the solution above is similar to Scott's solution.
I tried following Craig's URL's; no such page on either one. I'll
come back to that later this afternoon.
Hmmm... this URL works:
http://www.ibm.com/servers/storage/support/software/sort/mvs/tricks/srtmst03.html#t7r
Make sure you have the whole thing since it wraps.
I am curious though that no one has yet suggested ICETOOL.
DFSORT has the INCLUDE and date functions. ICETOOL doesn't have any
extensions to that. You could wrap an ICETOOL job around the DFSORT
control statements, but it wouldn't really provide any additional function.
ICETOOL adds lots of functions to DFSORT, but this isn't one of them.
Thanks again to all of you for the assist.
Frank Yaeger - DFSORT Team (IBM)
Specialties: ICETOOL, IFTHEN, OVERLAY, Symbols, Migration
= DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort/
--
For IBM-MAIN subscribe / signoff / archive access instructions,
send email to [EMAIL PROTECTED] with the message: GET IBM-MAIN INFO
Search the archives at http://bama.ua.edu/archives/ibm-main.html
Re: DFSORT Selecting all records today - 365 days
Choices I see 1. use the DB2 date facilities when the unload tape is created. REORG with DISCARD processing will purge records with single column date manipulation. 2. use INREC to build a DFSORT type date field and then use OMIT logic from Frank that will let you do 1 year. Use OUTREC to remove the date field. 3. write a REXX to generate control cards or pass the file directly. I am waiting for the ability of DFSORT to invoke REXX directly. That would let me do all kind of fixes without ever writing a full program. Mike -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to [EMAIL PROTECTED] with the message: GET IBM-MAIN INFO Search the archives at http://bama.ua.edu/archives/ibm-main.html
