View previous topic :: View next topic
|
Author |
Message |
arunvp2
New User
Joined: 14 Nov 2008 Posts: 6 Location: Bangalore
|
|
|
|
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 |
|
|
Terry Heinze
JCL Moderator
Joined: 14 Jul 2008 Posts: 1249 Location: Richfield, MN, USA
|
|
|
|
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 |
|
|
arunvp2
New User
Joined: 14 Nov 2008 Posts: 6 Location: Bangalore
|
|
|
|
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 |
|
|
Bharath Bhat
Active User
Joined: 20 Mar 2008 Posts: 283 Location: chennai
|
|
|
|
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 |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006 Posts: 6250 Location: Mumbai, India
|
|
Back to top |
|
|
arunvp2
New User
Joined: 14 Nov 2008 Posts: 6 Location: Bangalore
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
you are obviously not generating a valid timestamp with your concatenation. |
|
Back to top |
|
|
arunvp2
New User
Joined: 14 Nov 2008 Posts: 6 Location: Bangalore
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
if the definition of ws-first-day is not PIC x(26) that is your problem. |
|
Back to top |
|
|
arunvp2
New User
Joined: 14 Nov 2008 Posts: 6 Location: Bangalore
|
|
|
|
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 |
|
|
MCEVOY
New User
Joined: 21 Nov 2005 Posts: 18
|
|
|
|
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 |
|
|
|