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
 

 

Quering Performance tuning

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

New User


Joined: 26 Sep 2007
Posts: 24
Location: Bangalore

PostPosted: Fri Oct 05, 2007 9:16 pm    Post subject: Quering Performance tuning
Reply with quote

Hi,
How to avoid the DB2 Optimizer perfroming the Table scan
as the table data is very large. The table query will be
accessed online.

Select A.C1,A.C2,A.C3, A.C4
From tbl1 A,tbl2 b ,tbl3 C
Where C.REGION= :DECLGEN-region and
B.date <> '00000000'
and( (B.date < :DCLGEN-VAR1)
oR
(B.date < :DCLGEN-VAR1
and A.IND = 'Y')


In the above table columns C1 C2 C3 and C4 are Indexed
The Primary key to all the table is C1 and C2
There will be any performance improvements if we index the coulmns
REGION,IND and DATE to perform fetch faster.

But IND wil have only two values 'y' or 'n'
Date can greater than Current date by 2 months
and less than Current Date by 6 months


In the table tbl1 there 1,38000 records and 69,000 pages to perform the table scan

And the Cardinality for the tbl2 is 209685.


Can you help in performance tuning.
Other way is to get the coulmns statistic by runing the RUNSTATS
Can body give any hints on how will this improve the performance
Back to top
View user's profile Send private message

stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Sat Oct 06, 2007 4:49 am    Post subject:
Reply with quote

That is a tiny table.... our production tables run 2,000,000 plus on the row counts.

But anyway, indexing your date files should help your performance somewhat.
Back to top
View user's profile Send private message
praveenbl22
Currently Banned

New User


Joined: 26 Sep 2007
Posts: 24
Location: Bangalore

PostPosted: Mon Oct 08, 2007 4:49 pm    Post subject:
Reply with quote

Hi stodolas,

Do Indexing the Columns like B.DATE, A.IND and C.REGION is a Better option. The A.IDN columns contains only two values 'Y' or 'N"
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Mon Oct 08, 2007 5:38 pm    Post subject:
Reply with quote

I saw that B.Date was the used twice. B.Date must not be a date format in DB2, because '00000000' couldn't possibly be stored in a date field there.

Indexes should improve the performance slightly, but I can't say how much. Indexes should be build on columns that are used in the WHERE of an SQL.
Back to top
View user's profile Send private message
mahsug

New User


Joined: 01 Jul 2003
Posts: 15
Location: Phoenix , AZ

PostPosted: Mon Oct 08, 2007 6:43 pm    Post subject: Re: Quering Performance tuning
Reply with quote

Hi Praveen,

I have a little bit difficulty in understanding the scenario , but with the limited understanding , here it goes.

The below query is not exhibiting any join conditions , I could see functional logics , but not the join conditions. If there is no join conditions , then the query wud result in cartition product.

I would recommend you to look at the access path of your query.

Regards,
Mahesh
Back to top
View user's profile Send private message
praveenbl22
Currently Banned

New User


Joined: 26 Sep 2007
Posts: 24
Location: Bangalore

PostPosted: Wed Oct 10, 2007 10:06 am    Post subject:
Reply with quote

Hi mahsug,

Can you see the changed query below with join predicate
Plan uses tablespace scan which may take long time.
The Columns C.REGION, A.IND and B.DATE are not indexed
Do indexing this coulmns will have performance imporvement.
Plan also does Sequential prefetch has the coulmns are not indexed.
Do prefetch actualy executes the query before the programs run and keeps the result in the buffer pool?
How DB2 evaluates the cost when Host variables are used in the query?
What is REPOPT?

Code:

SELECT A.C1,A.C2,A.C3, A.C4
FFROM  TBL1 A, TBL2 b ,TBL3 C
WHERE
-- Join Predicates the coulmns C1 and C2 are the Primary keys of  the
-- three tables and TBL1 is indexed by C1 , C2 , C3 and C4
           (A.C1 = B.C1 AND
            A.C2  = B.C2 AND
           A.C1  = C.C1 AND
            A.C2  = C.C2 AND   )
          C.REGION= :DECLGEN-REGION
AND    B.DATE <> '00000000'
AND (( B.DATE < :DCLGEN-VAR1)
           OR
         (B.DATE < :DCLGEN-VAR1
          AND A.IND = 'Y')
 
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 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
No new posts Performance tuning of Online system bipinpeter All Other Mainframe Topics 2 Thu Nov 26, 2015 2:29 pm


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