Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Need a job to extract monthly data from db2 table

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

New User


Joined: 20 Jan 2004
Posts: 42

PostPosted: Tue Apr 22, 2008 4:19 pm    Post subject: Need a job to extract monthly data from db2 table
Reply with quote

My job is scheduled on last date of every month and this job would retrieve the current month's data from the DB2 table. Could you plesse let me know how retrieval is possible.

Sakthi
Back to top
View user's profile Send private message

the_gautam

Active User


Joined: 05 Jun 2005
Posts: 165
Location: Bangalore

PostPosted: Tue Apr 22, 2008 4:40 pm    Post subject:
Reply with quote

what is the structure of the DB2 table?
you can retrieve the data easily if the table is containing any date or timestamp field.
Back to top
View user's profile Send private message
vasanthkumarhb

Active User


Joined: 06 Sep 2007
Posts: 276
Location: Bang,iflex

PostPosted: Tue Apr 22, 2008 4:48 pm    Post subject:
Reply with quote

Hi,

If you have month entity in your table you can do it as below.


Code:
SELECT var1, var2, var3, var4,month
FROM TABLE
WHERE month = 'march'
GROUP BY var1,var2,month,var3,var4;
Back to top
View user's profile Send private message
shakti

New User


Joined: 20 Jan 2004
Posts: 42

PostPosted: Wed Apr 23, 2008 10:46 am    Post subject: Reply to: Need a job to extract monthly data from db2 table
Reply with quote

Hi Gautam,

Thanks for the clarification.

Regarding your query, I have a table with timestamp as one of the column.

Could you please let me know how the retrival query could be?

Thanks in advance.

Shakti
Back to top
View user's profile Send private message
vasanthkumarhb

Active User


Joined: 06 Sep 2007
Posts: 276
Location: Bang,iflex

PostPosted: Wed Apr 23, 2008 12:45 pm    Post subject:
Reply with quote

Hi,

the variable contain the TIMESTAMP value which constitute date as well as time up to microseconds.

you take the substring of the timestamp to compare in the query to get the complete last month data.

check this query and try.


Code:
SELECT * FROM my_table WHERE SUBSTRING(timestamp_val,1,8) = DATE '2002-10-03';
Back to top
View user's profile Send private message
Prajesh_v_p

Active User


Joined: 24 May 2006
Posts: 133
Location: India

PostPosted: Wed Apr 23, 2008 1:26 pm    Post subject:
Reply with quote

If I understand your requirement currectly, Will not the below solution work?

WHERE MONTH(timestamp_val) = MONTH(CURRENT DATE)

Thanks,
Prajesh
Back to top
View user's profile Send private message
the_gautam

Active User


Joined: 05 Jun 2005
Posts: 165
Location: Bangalore

PostPosted: Wed Apr 23, 2008 1:41 pm    Post subject:
Reply with quote

Code:
SELECT * FROM MY_TABLE WHERE MONTH(timestamp_val) = MONTH(CURRENT DATE)
Back to top
View user's profile Send private message
Bharath Bhat

Active User


Joined: 20 Mar 2008
Posts: 283
Location: chennai

PostPosted: Wed Apr 23, 2008 1:45 pm    Post subject:
Reply with quote

Prajesh_v_p wrote:
If I understand your requirement currectly, Will not the below solution work?

WHERE MONTH(timestamp_val) = MONTH(CURRENT DATE)

Thanks,
Prajesh

It will work Prajesh. icon_biggrin.gif
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


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

PostPosted: Wed Apr 23, 2008 5:42 pm    Post subject:
Reply with quote

What if there is more than 1 years worth of data in the table?


Code:
WHERE DATE(TIMESTAMP_VAL) BETWEEN (CURRENT DATE - (DAY(CURRENT DATE) - 1) DAYS)
AND CURRENT DATE


What will you do if the query can't be executed on the last day of the month?
What will you do if there is additional data added to the table after the query is executed and before the end of the day?
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 Insert a Row_number into table useit DB2 2 Tue Sep 19, 2017 1:07 pm
No new posts how to see when the last read access ... Mike 1304 DB2 1 Tue Sep 12, 2017 7:52 pm
This topic is locked: you cannot edit posts or make replies. Extract all "IF" Statements... Adarsh Damodaran CLIST & REXX 1 Wed Sep 06, 2017 9:28 am
No new posts PC (UTF-8) -> z/OS (EBCDIC) -> ... prino All Other Mainframe Topics 4 Fri Sep 01, 2017 1:47 am
No new posts Question related to Data dictionary f... rexx77 SYNCSORT 3 Thu Aug 31, 2017 7:23 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us