IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

DB2 Performance improvement


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
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
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
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
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
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

Superior Member


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

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

Hi,
Quote:
i didn't get how to implement this in the query.
You might check this similar link: 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
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: 6966
Location: porcelain throne

PostPosted: Wed Feb 11, 2009 7:48 pm
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
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: 6966
Location: porcelain throne

PostPosted: Wed Feb 11, 2009 9:16 pm
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
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
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts exploiting Z16 performance PL/I & Assembler 2
No new posts JOIN STATEMENT PERFORMANCE. DFSORT/ICETOOL 12
No new posts Which SORT utility can improve the Pe... DFSORT/ICETOOL 16
No new posts COBOL Performance Tuning COBOL Programming 6
No new posts CICS Performance statistics CICS 3
Search our Forums:

Back to Top