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
 

 

Table Update

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

New User


Joined: 23 Dec 2009
Posts: 3
Location: Hyderabad

PostPosted: Thu Feb 11, 2010 3:49 pm    Post subject: Table Update
Reply with quote

Hi,

I have a daily job which runs from Mon to frid, it checks for any updates in a table, if any would send it to a daily report. We use a PARAM which updates the current date. My query now is to check if any updates are done on sat and sun while running the job on monday with the current date, can anybody suggest me how to check for the prvs to days updates in the table which running the monday job with the current date


Thanks
Kumar.
Back to top
View user's profile Send private message

Anuj Dhawan

Senior Member


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

PostPosted: Thu Feb 11, 2010 4:30 pm    Post subject:
Reply with quote

Your post does not explain much about your problem and what exactly you are looking for.

Quote:
We use a PARAM which updates the current date
I believe you meant "PARMeter"?
Quote:
suggest me how to check for the prvs to days updates in the table
Does the bold text mean,"the previous two days"?

Does the table have any "date column"?
Back to top
View user's profile Send private message
ak47va47

New User


Joined: 23 Dec 2009
Posts: 3
Location: Hyderabad

PostPosted: Thu Feb 11, 2010 4:38 pm    Post subject:
Reply with quote

Hi,

Yes it means parameters and its prvs two days. The table does have a date column.


Thanks
Kumar.
Back to top
View user's profile Send private message
Kjeld

Active User


Joined: 15 Dec 2009
Posts: 365
Location: Denmark

PostPosted: Thu Feb 11, 2010 4:42 pm    Post subject:
Reply with quote

In the start of program SET host-variable TO CURRENT DATE-2 DAYS (please check the exact syntax yourself).

Then select all rows updated on that date or later.
Back to top
View user's profile Send private message
ak47va47

New User


Joined: 23 Dec 2009
Posts: 3
Location: Hyderabad

PostPosted: Thu Feb 11, 2010 4:51 pm    Post subject:
Reply with quote

Hi Kjeld,

Your suggestion works good on Monday, but from tues to frid, we need only current date, so how would it work on these days?
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


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

PostPosted: Thu Feb 11, 2010 4:56 pm    Post subject:
Reply with quote

On other days don't subtract 2 from CURRENT DATE ...
Back to top
View user's profile Send private message
Kjeld

Active User


Joined: 15 Dec 2009
Posts: 365
Location: Denmark

PostPosted: Thu Feb 11, 2010 5:17 pm    Post subject:
Reply with quote

Hi Anuj,

You beat me to it... icon_smile.gif
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


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

PostPosted: Thu Feb 11, 2010 5:24 pm    Post subject:
Reply with quote

Beating a System Analyst wasn't easy, Kjeld ..icon_smile.gif.
Back to top
View user's profile Send private message
PeterHolland

Global Moderator


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

PostPosted: Thu Feb 11, 2010 5:32 pm    Post subject:
Reply with quote

Maybe for Michael Jackson. icon_mad.gif
Back to top
View user's profile Send private message
Kjeld

Active User


Joined: 15 Dec 2009
Posts: 365
Location: Denmark

PostPosted: Thu Feb 11, 2010 5:52 pm    Post subject:
Reply with quote

Yeah, he beat me to the final resting place icon_rolleyes.gif
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Thu Feb 11, 2010 8:30 pm    Post subject:
Reply with quote

You could use dayofweek()

Code:
current_date - case when dayofweek(current date) = 2 then 2 else 0 end days
Back to top
View user's profile Send private message
Kjeld

Active User


Joined: 15 Dec 2009
Posts: 365
Location: Denmark

PostPosted: Fri Feb 12, 2010 5:28 am    Post subject:
Reply with quote

Should it not be:
Code:
current_date - case when dayofweek(current date) = 6 then 2 else 0 end days

as dayofweek function returns 1 for Sundays. An alternative could be using dayofweek_iso, which returns 1 for Mondays, and in which case the test value should be 5.

Which function you use will depend on your site's preferences, I guess most European sites will prefer dayofweek_iso.
Back to top
View user's profile Send private message
Kylash V

New User


Joined: 13 Feb 2010
Posts: 5
Location: Chennai

PostPosted: Sun Feb 14, 2010 12:09 am    Post subject: Reply to: Table Update
Reply with quote

Hi ,
I guess the logic explained above would work fine if Monday was a working day . Consider a scenario where Monday is a holiday and the batch actually would start only on Tuesday for that week.
So, you would require to report updates that happened on Sat and Sun to your table only on Tuesday's batch.
I do not have a work-around for this currently. But, wanted to point out this if applicable to your system
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: Sun Feb 14, 2010 1:05 am    Post subject:
Reply with quote

Hello,

Many organizations (especially manufacturing and service organizations) have a "calendar subsystem" for this kind of requirement.

The calendar "knows" all of the "business days" and this is used to determine if "today" is a business day and how many non-business days preceeded "today". This calendar is also used to calculate how many business days there were/are between "today" and some past/future date.

These calendar processes are implemented using database, vsam, or other means. They are usually a/some callable routine(s) rather than included inline. They usually include the capability to add non-business days to accomodate things like a blizzard, hurricane, or other situation that causes some day(s) to become non-business days.

This is not done at an individua program level but rather for an entire application or may even be for the whole organization (rare).
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 -913/-911 Deadlock during UPDATE stat... NoSleep319 DB2 5 Fri Nov 18, 2016 12:37 am
No new posts How to update a portion of text in a ... Bill Woodger DFSORT/ICETOOL 25 Wed Nov 09, 2016 9:41 pm
No new posts Updating Cursor row withour using FOR... chandan.inst DB2 15 Tue Nov 08, 2016 11:17 am
No new posts Need Rexx Program to Load in to DB2 T... Hareesh Verma CLIST & REXX 22 Tue Sep 20, 2016 2:59 pm
No new posts Command UNIQUE CONSTRAINT on any tabl... rohanthengal DB2 2 Thu Aug 18, 2016 3:48 pm


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