| View previous topic :: View next topic |
| Author |
Message |
jim haire
Beginner
Joined: 30 Dec 2002
Posts: 140
Topics: 40
|
 Posted: Tue Oct 13, 2020 2:32 pm Post subject: Write out the results of a query against SYSIBM.SYSDUMMY1 |
 |
|
I am trying to write out a sequential file with one record which will contain a date. The sequential file would contain a 10 byte field in CCYY-MM-DD format.
The query would be something like this:
SELECT CURRENT DATE - DAY(CURRENT DATE) DAYS - 5 YEARS
FROM SYSIBM.SYSDUMMY1
I was hoping to be able to then use the date in a sort where I would do a SORT INCLUDE using the value in the sequential dataset in the date comparison on my include statement.
I tried this using DSNTEP4, but was getting more information than what I needed. I thought about doing an unload, but it seemed kind of strange to use a utility on a one row table where you aren't really retrieving any value contained in the table.
Is there another way? |
|
| Back to top |
|
 |
jim haire
Beginner
Joined: 30 Dec 2002
Posts: 140
Topics: 40
|
| Posted: Tue Oct 13, 2020 3:26 pm Post subject: |
|
|
| We were able to do this with an UNLOAD. Needed to use DIRECT NO as one of the parameters, otherwise we got an error because it is a catalog table and the unload didn't like that we were unloading it. |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12377
Topics: 75
Location: San Jose
|
| Posted: Tue Oct 13, 2020 4:39 pm Post subject: Re: Write out the results of a query against SYSIBM.SYSDUMMY |
|
|
| jim haire wrote: | I am trying to write out a sequential file with one record which will contain a date. The sequential file would contain a 10 byte field in CCYY-MM-DD format.
The query would be something like this:
| Code: |
SELECT CURRENT DATE - DAY(CURRENT DATE) DAYS - 5 YEARS
FROM SYSIBM.SYSDUMMY1
|
I was hoping to be able to then use the date in a sort where I would do a SORT INCLUDE using the value in the sequential dataset in the date comparison on my include statement. |
Jim Haire,
You don't have to generate the Date using DB2. DFSORT has the ability of calculating date subtracting months/days/years from the current date. If you tell me your requirement of using this generated date as an INCLUDE condition then I can show you a DFSORT job that will do the same.
or a simple example like this. Your DB2 query is generating the last day of previous month 5 years ago(2015-09-30). DATE2 is of CCYYMM format and if you subtract 61 months ( 5 * 12 = 60 + 1 month ) will give you 2015-09 and we can select the records using that.
| Code: |
//STEP0100 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD *
2015-10-01 - SHOULD BE DROPPED
2015-09-30 - PICK
2016-09-30 - SHOULD BE DROPPED
2020-10-13 - SHOULD BE DROPPED
//SORTOUT DD SYSOUT=*
//SYSIN DD *
OPTION COPY
INCLUDE COND=(01,06,CH,EQ,DATE2(-)-61)
/* |
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
