Add 1 day to an input date in a batch job

Vibha Upadhyay · New User Joined: 17 Mar 2011 Posts: 8 Location: India

Hi,

My requirement is to add 1 day to an input date in a batch job. The input date would be in a flat file. I want to use something on the lines of the below query but I am not sure how to give a dataset name as a parameter for the DATE function. I want the DATE function to work like this: SELECT DATE(date from input file) . Please let me know if it is possible and if yes, can anyone please share the syntax for the same.

SELECT
DATE('2011-12-19')+ 1 DAYS
FROM SYSIBM.SYSDUMMY1
WITH UR;

Please let me know if my question is not clear enough.

Thanks a lot.

prasun dhara · New User Joined: 01 Mar 2008 Posts: 49 Location: kolkata

You want ur SQL to read flat file :O. ??

dbzTHEdinosauer · Posted: Mon Dec 19, 2011 9:06 pm

why the uncommitted read on SYSIBM.SYSDUMMY1?

Craq Giegerich · Posted: Mon Dec 19, 2011 9:07 pm

Vibha Upadhyay · New User Joined: 17 Mar 2011 Posts: 8 Location: India

Thanks everyone for the reply. That helps a lot. I wanted to do this in JCL alone but looks like this is not possible without a cobol program.

Sorry for the 'WITH UR'. That was not required I realised now.

dick scherrer · Posted: Mon Dec 19, 2011 10:46 pm

Hello,

Vibha Upadhyay · New User Joined: 17 Mar 2011 Posts: 8 Location: India

Hi,

I am sorry I meant I tried to do add 1 day to a date through SYNCSORT but did not get any success. We can do it very simply through DFSORT by using the following :

INREC BUILD=(1,8,Y4T,ADDDAYS,+1,TOGREG=Y4T)

But unfortunately we have only SYNCSORT on our system and the way to go about it is
1. To convert a gregorian date to julian date
2. Add 1 to the julian date
3. Convert the julian date back to gregorian date.

There is not much documentation available on SYNCSORT so I was not able to get much help on writing a sort step that does the above.

dick scherrer · Posted: Mon Dec 19, 2011 11:40 pm

Hello,

There is sufficient documentation on Syncsort available. . .

Which release of Syncsort is being used? Part of your problem may be the system has an old release of Syncsort. The current release of Syncsort supports a DATEADD feature - which sounds like what you want.

If you were interested in a Syncsort solution, why ask about db2 (even in the db2 part of the forum)?

Vibha Upadhyay · New User Joined: 17 Mar 2011 Posts: 8 Location: India

Yes my system has an old release of Syncsort (The dateadd feature is not supported ) and so i thought of a DB2 alternative solution to achieve the Date add functionality. Hence posted this question in the DB2 forum.

Akatsukami · Posted: Tue Dec 20, 2011 1:00 am

If your compilers are at all contemporary, the languages available to you should have facilities for converting Gregorian date to some sort of integer value (probably Lilian day number, but that's neither here nor there). So your pseudo-code will be:

dick scherrer · Posted: Tue Dec 20, 2011 1:29 am

Hello,

Have you implemented something yet?

Older releases of Syncsort support executing an SQL query via Syncsort. Or run the SQL in batch via IKJEFT01.

don.leahy · Posted: Tue Dec 20, 2011 1:46 am

Vibha Upadhyay · New User Joined: 17 Mar 2011 Posts: 8 Location: India

Hi,

I can run SQL in batch via IKJEFT01 but my question is how do I implement the DATE + 1 logic where the DATE would be different each time (has to read from an input file) instead of a hardcoded date like below:
SELECT
DATE('2011-12-19')+ 1 DAYS
FROM SYSIBM.SYSDUMMY1
WITH UR;

Fromt the replies above i see this is not possible without using a cobol program. Please let me know if i am missing something here.

dick scherrer · Posted: Tue Dec 20, 2011 12:41 pm

Hello,

Possibly:

Read the external data and build a dynamic sql query for the arithmetic would be one way.

Or:

Look into generating the SELECT after reading the date and writing this to a file to be used in the batch query.

PeterHolland · Posted: Tue Dec 20, 2011 12:41 pm

Rexx ?

Bill Woodger · Posted: Tue Dec 20, 2011 1:26 pm

If you want a date from a dataset for your SQL, you'll need to use some language that will process datasets and understand where your date is on a record. As Mr Akatsukami and Peter Holland have pointed out already, the language needn't be Cobol.

Are you going to do a query with the date so-formed, or do you just want a new date?

Anuj Dhawan · Posted: Tue Dec 20, 2011 8:04 pm

Actually, there is a way out of reading the data from a Data-set in "Batch DB2 Jobs". I've tried it this way