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
 

 

Cursor usage and Temporary tables??

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

New User


Joined: 27 May 2009
Posts: 22
Location: India

PostPosted: Fri Jul 23, 2010 6:57 pm    Post subject: Cursor usage and Temporary tables??
Reply with quote

Hi Friends,
I have a requirement. I have followed few approach, pls assist me.

I have 2 tables T1 And T2. From table T1 I am Fetching DATE which is Integer format like 20080522, now i need i need to fetch date from T2(I need to extract from timestamp) and it gives me in the format: yyyy-mm-dd. (I need to fetch few more data from T2 but i want to use them after satisfying the condition. Condition is date of T2 > date of T1, if yes I need to find out :
1. Difference of timestamps (In and Out)
2. maximum Of the Difference
3. Minimum Of the Difference
4. AVG of of the Differences
5. total records

I have 2 options :-
1. fetch date from T1 move to Ws-variable in yyyy-mm-yy.
fetch date from T2' timestamp in format YYYY-mm-dd,

Compare them, if t2 > T1, fetch one more time T2 with required operstions Like Timestamp diff, MAX, MIN, AVG , Total Records processed. But If I do this How do I Discriminate or store the Modified filds to perform MAX, MIN or AVG.

2. During First Fetc of T2, i can perform All the required operations, but it will calculate for all the data and I want only after a particular date, i.e after last processing.

Please suggest, If I am not clear let me know.

1 more sloution that I can think is to Compare T1 and T2's date, if condition True, store calculate and store the fields in temporary table so that MAX, MIN and AVG function can be performed.

thnak you.
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: Fri Jul 23, 2010 8:56 pm    Post subject:
Reply with quote

Hello,

It may help someone help you if you post some sample "input" rows/columns and the result you want from that sample input . . .

Only the columns needed for selection need be shown. Please use the "Code" tag to preserve the alighment of the data and improve the readability.
Back to top
View user's profile Send private message
naziashaffi

New User


Joined: 27 May 2009
Posts: 22
Location: India

PostPosted: Sat Jul 24, 2010 6:33 am    Post subject:
Reply with quote

Table1-T1
Code:


Date_col

20081015
20081120
20080526
20090625
20101221



Table2- T2
Code:


Timestamp_IN_col         Timestamp_out_Col
2007-05-22-4.06.8..   2007-05-22-5.07.8..
2008-01-20-2.03.5..   2008-01-20-2.03.5..
2008-05-15-6.08.7..   2008-05-15-8.09.8..



Here, I need to fetch date from table-1 and from Table-2(from Timestamp, IN or Out).
Problem is : T1 has ccyymmdd format and T2 has yyyy-mm-dd format.
Can I change ccyymmdd to ccyy-mm-dd and check the greater than condition from date fetched out of Timestamp???

If yes, then please tell me how to calculate the difference between 2 timestamps and their AVG, is it same like Integers/numbers or we need to break them in parts.

My requirement is : Once the date is compared and T2's date is greater than T1's date, then I have to calculate : 1) Difference of In and Out Timestamps, 2) MAX among that Difference, 3) MiN among that Difference,
4) AvG of that Difference, 5) Total number of records processed.

Please help me.
Thank you
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: Sat Jul 24, 2010 8:27 am    Post subject:
Reply with quote

Hello,

Please post what you want as the result(s) of the query(ies) with that sample data.

At first look, this does not appear to be a "single query" requirement.
Back to top
View user's profile Send private message
naziashaffi

New User


Joined: 27 May 2009
Posts: 22
Location: India

PostPosted: Sat Jul 24, 2010 8:58 am    Post subject:
Reply with quote

dick scherrer wrote:
Hello,

At first look, this does not appear to be a "single query" requirement.


You are right, it can not be single query icon_mad.gif ,

My issue will be fixed if this requirement met. (I am sorry I am unable to put sample data as system is building from the scratch, If this reply will not clear the requirement I will put some sample data).

Actually My client want to check how much time a transaction takes, he want to check from(or after) a particular date, i.e date after the system was run last time. Now this date is coming from a table T1, Integer format yyyymmdd(I will confirm the fields if required, not available right now).

It has to be compared with date fetched from the second table(from Timestamp IN or OUT, anyone ), Main operation is going to be on 2nd table but only for the data/records which mets the condition i.e Date of Table-2 > Date of Table-1 (after last transaction).

If condition is true for 1st transaction, then I have to calculate difference of times(IN and OUT) from Timestamp, same if true for 2nd transaction, 3rd , 4th and skip 5th if condition is not true and so on......

So, basically, in a query I want to extract dates from two tables, test the condition, if TRUE, calculate the Timestamp2 - Timestamp1(or vice versa), then the AVG , MAX and MIN of this Difference.

I have written nothing new to clear the question, just few words to clear my thought. Please help me, if need more input I will provide here.

I can try few steps but when I fetch date from 1st table, it is yyyymmdd and from second table yyyy-mm-dd, how to compare them or test them, Is a cobol W-S variable mandatory?
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Sat Jul 24, 2010 9:03 am    Post subject:
Reply with quote

Hi Naziashaffi,

This query will convert the ordinary date to integer(yyyymmdd) format
Code:

select int(replace(char(current date, ISO),'-',''))
from sysibm.sysdummy1;

I did this because, converting integer to date might require a few substrings, concatenation & date manipulations stuff. This was much easier with less functions usage.

You can use the query like this to compare the dates(or integers)
Code:
select t2.Timestamp_IN_col, t2.Timestamp_out_Col
from t1, t2
where int(replace(char(t2.date_col, ISO),'-','')) > t1.Date_col

Not Tested

Thanks,
Sushanth
Back to top
View user's profile Send private message
naziashaffi

New User


Joined: 27 May 2009
Posts: 22
Location: India

PostPosted: Mon Aug 02, 2010 10:38 pm    Post subject:
Reply with quote

Hi sushanth and Dick,

Thank your consistent reply.
You solution worked like Magic!

thank you very much.
I am proud to be a part of this forum, with such an experience and technical professionals.

This is the best forum I have ever seen.

Please Always give little part/time of your life to this forum.

I am new to the Mainframe. Just because the help of this forum I am able to get a good job On IBM Mainframes, in a good company. With lot of Developement opportunities.

Thanks a lot. I owe a lot to all you guys and this forum.
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: Wed Aug 04, 2010 4:08 am    Post subject: Reply to: Cursor usage and Temporary tables??
Reply with quote

Good ot hear it is working - thank you for letting us know and for the kind words icon_smile.gif

d
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 Identify top 30 big tables or indexes... ashek15 DB2 0 Fri Jun 16, 2017 10:01 am
No new posts )SEL and )ENDSEL usage cvnlynn TSO/ISPF 4 Thu Jun 15, 2017 1:15 am
No new posts Testing rerad cursor for status with ... John F Dutcher DB2 8 Fri May 19, 2017 9:35 pm
No new posts Need inputs on Space requirement and... ashek15 IMS DB/DC 0 Sat Apr 01, 2017 8:26 am
No new posts Data replication from multiple Db2 ta... kishpra DB2 9 Mon Mar 27, 2017 9:58 pm


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