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
 

 

Add 1 day to an input date in a batch job

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Vibha Upadhyay

New User


Joined: 17 Mar 2011
Posts: 8
Location: India

PostPosted: Mon Dec 19, 2011 2:55 pm    Post subject: Add 1 day to an input date in a batch job
Reply with quote

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.
Back to top
View user's profile Send private message

prasun dhara

New User


Joined: 01 Mar 2008
Posts: 49
Location: kolkata

PostPosted: Mon Dec 19, 2011 8:48 pm    Post subject:
Reply with quote

You want ur SQL to read flat file :O. ??
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Mon Dec 19, 2011 9:06 pm    Post subject:
Reply with quote

why the uncommitted read on SYSIBM.SYSDUMMY1?
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Mon Dec 19, 2011 9:07 pm    Post subject: Re: Date query
Reply with quote

Vibha Upadhyay wrote:
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.


I hate to be the one to supply you with this bad news but you can't do that.
You will have to read the date into working storage then do the sql such as
Code:
EXEC SQL
SET :WS-DATE-PLUS1 = :WS-DATE + 1 DAYS
END-EXEC



It shouldn't take you more 10,000 lines of code to handle this.
Back to top
View user's profile Send private message
Vibha Upadhyay

New User


Joined: 17 Mar 2011
Posts: 8
Location: India

PostPosted: Mon Dec 19, 2011 10:42 pm    Post subject:
Reply with quote

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.
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: Mon Dec 19, 2011 10:46 pm    Post subject:
Reply with quote

Hello,

Quote:
I wanted to do this in JCL alone
Exactly NOTHING can be "done in JCL alone". . . JCL can only execute programs.

Which sort product is used on your system? Is there some reason you cannot get what you want thru a simple SQL query in batch?
Back to top
View user's profile Send private message
Vibha Upadhyay

New User


Joined: 17 Mar 2011
Posts: 8
Location: India

PostPosted: Mon Dec 19, 2011 11:18 pm    Post subject:
Reply with quote

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.
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: Mon Dec 19, 2011 11:40 pm    Post subject:
Reply with quote

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)?
Back to top
View user's profile Send private message
Vibha Upadhyay

New User


Joined: 17 Mar 2011
Posts: 8
Location: India

PostPosted: Mon Dec 19, 2011 11:51 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1738
Location: Bloomington, IL

PostPosted: Tue Dec 20, 2011 1:00 am    Post subject:
Reply with quote

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:
Code:
int_var = days2int(greg_date)
int_var += 1
greg_date = int2days(int_var

without using DB2 at all.
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: Tue Dec 20, 2011 1:29 am    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
don.leahy

Active Member


Joined: 06 Jul 2010
Posts: 642
Location: Whitby, ON, Canada

PostPosted: Tue Dec 20, 2011 1:46 am    Post subject:
Reply with quote

dick scherrer wrote:

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

Not to drift too far off topic, but I wrote a DFSORT E15 user exit that accepts an SQL statement as input and returns the table data to SORT for processing. It is not in production, and probably will never be, but it was an interesting exercise.
Back to top
View user's profile Send private message
Vibha Upadhyay

New User


Joined: 17 Mar 2011
Posts: 8
Location: India

PostPosted: Tue Dec 20, 2011 12:20 pm    Post subject:
Reply with quote

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.
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: Tue Dec 20, 2011 12:41 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
PeterHolland

Global Moderator


Joined: 27 Oct 2009
Posts: 2422
Location: Netherlands, Amstelveen

PostPosted: Tue Dec 20, 2011 12:41 pm    Post subject:
Reply with quote

Rexx ?
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7236

PostPosted: Tue Dec 20, 2011 1:26 pm    Post subject: Reply to: Date query
Reply with quote

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?
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Tue Dec 20, 2011 8:04 pm    Post subject:
Reply with quote

Actually, there is a way out of reading the data from a Data-set in "Batch DB2 Jobs". I've tried it this way
Code:
//STEP010 EXEC DB2TBTLO,UTILITY=DSNTEP2                 
//SYSUDUMP DD   SYSOUT=*                               
//ABNLIGNR DD DUMMY                                     
//SYSIMAG  DD DUMMY                                     
//SYSPRINT DD SYSOUT=*                                 
//SYSCTL   DD DUMMY                                     
//SYSIN   DD  *                                         
 SELECT * FROM OWNER.TABLE                             
   WHERE  EMPLOYEE_NO IN (                                           
//         DD DSN=FILE.CONTAIN.SOME.NUMBER,DISP=SHR
/*           
While you use it - edit the dataset FILE.CONTAIN.SOME.NUMBER by:
    1. Inserting commas in between two employee numbers
    2. End the flat file with a ");". (closing parentheses and a semi-colon)
Okay, said that - I've not experimented much to see if the "DATE function of DB2" can be employed on it; though if you have a huge list of employee-numbers in a DSN - above Job comes very handy.
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 -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Performing arithmetic on input field zh_lad DFSORT/ICETOOL 24 Tue Dec 06, 2016 8:04 pm
No new posts High CPU consumption Job using IAM fi... aswinir JCL & VSAM 8 Thu Dec 01, 2016 8:28 pm
No new posts How does a called pgm know if its cal... Graeme Westerman COBOL Programming 4 Tue Nov 29, 2016 9:25 pm
No new posts TOT & DATE parameter in ICEMAN hemanthj642 DFSORT/ICETOOL 4 Mon Nov 14, 2016 5:19 am
This topic is locked: you cannot edit posts or make replies. MIPS reduction for Batch job Virendra Shambharkar All Other Mainframe Topics 8 Mon Nov 07, 2016 4:02 pm


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