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
 
Help needed in Query Tuning

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

New User


Joined: 27 Mar 2010
Posts: 19
Location: folirida

PostPosted: Fri Oct 22, 2010 12:15 pm    Post subject: Help needed in Query Tuning
Reply with quote

Hi all,

I declared a cursor as given below.

EXEC SQL
DECLARE SAMPLE CURSOR FOR
SELECT POLY-NO,POLY-ST,AMOUNT,POLY-NO-DATE
FROM SAEMPLOYEE
WHERE POLY-NO LIKE :POLY-NO-DATE
AND EFFECT_DT =:WS-CURR-DT
END-EXEC.


Say for example my table having 5 million records.

I need to fetch below records from the table.

POLY-NO POLY-NO-DATE AMOUNT STATE
******* ************ ******** *****
F02256 F02256-2006 $1132.00 FL
F02256 F02256-2007 $1132.00 FL
F02256 F02256-2008 $1132.00 FL
F02256 F02256-2009 $1132.00 FL
F02256 F02256-2010 $1132.00 FL


My problem is for getting this five rows from the table, it takes lot of time.

please help me to tune this query to reduce the run time.

Thanks in Advance.

Paul

edited to get rid of the annoying coloring!
Back to top
View user's profile Send private message

sampaul4u

New User


Joined: 27 Mar 2010
Posts: 19
Location: folirida

PostPosted: Fri Oct 22, 2010 12:23 pm    Post subject: updated query with changes
Reply with quote

updated query with changes

EXEC SQL
DECLARE SAMPLE CURSOR FOR
SELECT POLY_NO,POLY_ST,AMOUNT,POLYNO_DATE
FROM SAEMPLOYEE
WHERE POLY_NO LIKE :POLYNO_DATE
AND EFFECT_DT =:WS-CURR-DT
END-EXEC.

result needed:
POLY_NO POLYNO_DATE AMOUNT POLY_ST
******* ************ ******** *******
F02256 F02256-2006 $1132.00 FL
F02256 F02256-2007 $1132.00 FL
F02256 F02256-2008 $1132.00 FL
F02256 F02256-2009 $1132.00 FL
F02256 F02256-2010 $1132.00 FL
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Fri Oct 22, 2010 12:58 pm    Post subject:
Reply with quote

What is likely to be filled in in :POLYNO_DATE ? F02256 or something like F022% or %
how many rows have EFFECT_DT = :WS-CURR-DT ?
How often does EFFECT_DT change for a specific row in the lifetime of that row ?

the query is fairly simple.
Only an index beginning with EFFECT_DT, POLYNO will make it faster.
The feasibility of such an index depends on much more than just one query.
Back to top
View user's profile Send private message
sampaul4u

New User


Joined: 27 Mar 2010
Posts: 19
Location: folirida

PostPosted: Fri Oct 22, 2010 1:19 pm    Post subject:
Reply with quote

Hi Guyc,

Thanks for your reply.

I updated the query as below.

EXEC SQL
DECLARE SAMPLE CURSOR FOR
SELECT POLY_NO,POLY_ST,AMOUNT,POLYNO_DATE
FROM SAEMPLOYEE
WHERE POLY_NO LIKE :POLYNO_DATE
END-EXEC.

result needed:
POLY_NO POLYNO_DATE AMOUNT POLY_ST
******* ************ ******** *******
F02256 F022562006 $1132.00 FL
F02256 F022562007 $1132.00 FL
F02256 F022562008 $1132.00 FL
F02256 F022562009 $1132.00 FL
F02256 F022562010 $1132.00 FL

my problem arrives in "WHERE POLY_NO LIKE :POLYNO_DATE"

POLYNO_DATE date like F02256%

ex:F022562006,F022562007

It should pickup all the years of POLYNO.

Is there any better way to change this query to reduce the timing.

Paul
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Fri Oct 22, 2010 2:07 pm    Post subject:
Reply with quote

column POLYNO = F02256
column POLYNO_DATE = F022562006 F02256207

Host Variable :POLYNO_DATE = F02256%

In your query you test column POLYNO like, but you explain the requirement as if you test column POLYNO_DATE like ?

maybe in your query you could write where POLYNO_DATE like :POLYNO_DATE
of
if you had a hostvariable containing F02256 , you could write where POLYNO = :POLYNO

This is ofcourse because the table is not normalized properly. Who in their right mind would repeat the polyno in the column polynodate ? the table should look something like this :
POLYNO, Yr
F02256, 2006
F02256, 2007
Back to top
View user's profile Send private message
steve6

New User


Joined: 10 Jul 2007
Posts: 7
Location: Chennai

PostPosted: Fri Oct 22, 2010 4:41 pm    Post subject:
Reply with quote

Hi,

Just another approach here.

Quote:
my problem arrives in "WHERE POLY_NO LIKE :POLYNO_DATE"


instead of "LIKE" - try the "SUBSTR". As you want to match POLY_NO & POLYNO_DATE and the first 6 chars are expected to be equal... you can try
"WHERE POLY_NO = SUBSTR(:POLYNO_DATE,1,6)"

I am not sure if this will reduce the time - as I couldn't recreate your scenario.
Can you please try this and let us know?
Back to top
View user's profile Send private message
Kjeld

Active User


Joined: 15 Dec 2009
Posts: 365
Location: Denmark

PostPosted: Fri Oct 22, 2010 5:15 pm    Post subject:
Reply with quote

I think (couldn't verify it) that the use of the intrinsic function SUBSTR is likely to cause data page scans instead of index usage.

I have never heard that it should improve performance over LIKE constructs.

I will recommend going for normalising the keys POLY_NO and POLY_NO_YR, like GuyC suggested, and establish a unique index on those columns, possibly adding other columns to make the key unique.
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 HEX value search in a DB2 query maxsubrat DB2 2 Wed Oct 04, 2017 3:04 pm
No new posts Create procedure issues -628 when add... chandraBE DB2 1 Mon Sep 18, 2017 12:16 pm
No new posts Can we limit length in concatenation ... balaji81_k DB2 7 Tue Aug 22, 2017 2:50 am
No new posts Need DB2 query to fetch previous row ! Chandan1993 DB2 10 Sat Jun 03, 2017 10:43 am
This topic is locked: you cannot edit posts or make replies. SORT trick needed bshkris SYNCSORT 6 Tue May 02, 2017 4:35 am

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