kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
|
 Posted: Tue Jul 29, 2003 3:44 pm Post subject: |
 |
|
Vijay,
Even though sort products are capable of performing arthmetic functions, they still cannot be used in date calculations.so if you are looking for a pure sort solution then the answer is 'NO'
However you can generate dynamic control card using db2 unload utility which will create calculate the date subtracting 2 years from current date.
check the following example. I am assuming that your packed decimal field starts in position 1 for 5 bytes.
| Code: |
//STEP0100 EXEC PGM=IKJEFT01
//*
//SYSTSPRT DD SYSOUT=*,DCB=BLKSIZE=121
//SYSPRINT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(XXXX)
RUN PROGRAM(DSNTIAUL) -
PLAN(DSNTIAUL) -
PARMS('SQL') -
LIB('XXXX.RUNLIB.LOAD')
//SYSREC00 DD DSN=&T1,DISP=(,PASS),SPACE=(TRK,(1,1),RLSE)
//SYSPUNCH DD SYSOUT=*
//SYSIN DD *
SELECT CHAR(' INCLUDE COND=(1,5,PD,GT,')
,SUBSTR(CHAR(CURRENT DATE - 2 YEARS),1,4)
,SUBSTR(CHAR(CURRENT DATE - 2 YEARS),6,2)
,SUBSTR(CHAR(CURRENT DATE - 2 YEARS),9,2)
,CHAR(')')
,CHAR(' ')
FROM SYSIBM.SYSDUMMY1
;
//*
|
The output from the above step is :
| Code: |
INCLUDE COND=(1,5,PD,GT,20010729)
|
Now this dataset is taken as control card for the sort step and copies the records which have date greater than 20010729.
| Code: |
//STEP0200 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD DSN=YOUR INPUT FILE,
// DISP=SHR
//SORTOUT DD DSN=YOUR OUTPUT DATASET,
// DISP=(NEW,CATLG,DELETE),
// UNIT=SYSDA,
// SPACE=(CYL,(X,Y),RLSE)
//SYSIN DD *
SORT FIELDS=COPY
// DD DSN=&T1,DISP=OLD
//*
|
Hope this helps...
cheers
kolusu
PS: please post in small letters as posting caps means as if you are yelling. |
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
