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
 
DB2 Performance improvement

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

New User


Joined: 14 Nov 2008
Posts: 6
Location: Bangalore

PostPosted: Wed Feb 11, 2009 11:41 am    Post subject: DB2 Performance improvement
Reply with quote

Hi,
I am trying to retrieve data from DB2 table for the last month (i.e 1st jan to 31 jan and this query can run any of the day in february)
I am using the follow query and its giving performance issue.
Its not using the column index because i am using SUBSTR command.

SELECT *
FROM EMPLOYEE
WHERE (SUBSTR(CHAR(EMP_JOIN_DT),1,4) ||
SUBSTR(CHAR(EMP_JOIN_DT),6,2) ||
SUBSTR(CHAR(EMP_JOIN_DT),9,2) BETWEEN
SUBSTR(CHAR(CURRENT DATE - 1 MONTH),1,4) ||
SUBSTR(CHAR(CURRENT DATE - 1 MONTH),6,2) ||
'01' AND
SUBSTR(CHAR(CURRENT DATE - 1 MONTH),1,4) ||
SUBSTR(CHAR(CURRENT DATE - 1 MONTH),6,2) ||
'31')

EMP_JOIN_DT - Timestamp

Any suggestions to improve the performance?

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

Terry Heinze

JCL Moderator


Joined: 14 Jul 2008
Posts: 1249
Location: Richfield, MN, USA

PostPosted: Wed Feb 11, 2009 12:28 pm    Post subject:
Reply with quote

DB2 DATE can be broken down into DAY (DATE) or MONTH (DATE) or YEAR (DATE). If TIMESTAMP can also be broken down into DAY, MONTH, or YEAR, you wouldn't need to use SUBSTR. I don't have access to a mainframe so I can't test this.
Back to top
View user's profile Send private message
arunvp2

New User


Joined: 14 Nov 2008
Posts: 6
Location: Bangalore

PostPosted: Wed Feb 11, 2009 12:43 pm    Post subject:
Reply with quote

Thanks Terry. Timestamp also can be broken to DAY (TIMESTAMP) or MONTH (TIMESTAMP) or YEAR (TIMESTAMP). But i didn't get how to implement this in the query.

Regards,
Arun
Back to top
View user's profile Send private message
Bharath Bhat

Active User


Joined: 20 Mar 2008
Posts: 283
Location: chennai

PostPosted: Wed Feb 11, 2009 12:52 pm    Post subject:
Reply with quote

Quote:
CURRENT DATE - 1 MONTH

This can be calculated before executing the query. This would save a lot of time since this is calculated many times.
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


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

PostPosted: Wed Feb 11, 2009 3:34 pm    Post subject:
Reply with quote

Hi,
Quote:
i didn't get how to implement this in the query.
You might check this similar link: http://www.ibmmainframes.com/viewtopic.php?t=35730&highlight=month+date
Back to top
View user's profile Send private message
arunvp2

New User


Joined: 14 Nov 2008
Posts: 6
Location: Bangalore

PostPosted: Wed Feb 11, 2009 7:28 pm    Post subject:
Reply with quote

Thanks to all.

I am using this query in QMF and its working fine.
SELECT TIMESTAMP(SUBSTR(CHAR(CURRENT DATE - 1 MONTH),1,7)
|| '-01-00.00.00.000000') ,
TIMESTAMP ((LAST_DAY(CURRENT DATE - 1 MONTH )),'24.00.00')
FROM SYSIBM.SYSDUMMY1

But in Embedded with cobol its giving -180.
EXEC SQL
SET :WS-FIRST-DAY =
TIMESTAMP(SUBSTR(CHAR(CURRENT DATE - 1 MONTH),1,7)
|| '-01-00.00.00.000000')
, :WS-LAST-DAY =
TIMESTAMP ((LAST_DAY(CURRENT DATE - 1 MONTH))
,'24.00.00')
END-EXEC
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Wed Feb 11, 2009 7:48 pm    Post subject:
Reply with quote

you are obviously not generating a valid timestamp with your concatenation.
Back to top
View user's profile Send private message
arunvp2

New User


Joined: 14 Nov 2008
Posts: 6
Location: Bangalore

PostPosted: Wed Feb 11, 2009 7:52 pm    Post subject:
Reply with quote

ok.But same query executed fine in QMF.
With application program i am facing this problem.

EXEC SQL
SET :WS-FIRST-DAY =
TIMESTAMP(SUBSTR(CHAR(CURRENT DATE - 1 MONTH),1,7)
|| '-01-00.00.00.000000')
END-EXEC
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Wed Feb 11, 2009 9:16 pm    Post subject:
Reply with quote

if the definition of ws-first-day is not PIC x(26) that is your problem.
Back to top
View user's profile Send private message
arunvp2

New User


Joined: 14 Nov 2008
Posts: 6
Location: Bangalore

PostPosted: Wed Feb 11, 2009 9:29 pm    Post subject:
Reply with quote

Thanks.

Query got executed by adding ISO format in date field.

EXEC SQL
SET :WS-FIRST-DAY =
TIMESTAMP(SUBSTR(CHAR(CURRENT DATE - 1 MONTH,ISO),1,7)
|| '-01-00.00.00.000000')
END-EXEC
Back to top
View user's profile Send private message
MCEVOY

New User


Joined: 21 Nov 2005
Posts: 18

PostPosted: Wed Feb 11, 2009 9:44 pm    Post subject:
Reply with quote

I think your original query could be recoded as

SELECT *
FROM EMPLOYEE
WHERE MONTH(EMP_JOIN_DT) = MONTH(CURRENT DATE - 1 MONTH)

but is that exactly what you want to select ... ?
This gives you all people who joined in the previous month of any year.

For people who joined LAST MONTH, I suggest you add to the above

AND EMP_JOIN_DT > (CURRENT TIMESTAMP - 2 MONTHS)
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 CONTIG for performance? JPVRoff JCL & VSAM 8 Fri Jun 09, 2017 8:39 am
No new posts BC, BCR, BRC, BRCL performance steve-myers PL/I & Assembler 0 Fri Dec 23, 2016 7:44 am
No new posts PL/I code tuning/Performance improvement Virendra Shambharkar PL/I & Assembler 4 Mon Dec 05, 2016 11:57 am
No new posts What are the way we can improve CPU p... Gunapala CN DB2 10 Mon Oct 24, 2016 2:16 pm
No new posts DISP=(SHR,PASS) performance opinion steve-myers JCL & VSAM 1 Wed Dec 02, 2015 11:53 pm

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