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
 

 

Performance problem in selecting data from one table

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

New User


Joined: 29 Aug 2007
Posts: 24
Location: chennai

PostPosted: Mon Oct 18, 2010 1:28 pm    Post subject: Performance problem in selecting data from one table
Reply with quote

Hi Experts,

We are facing some performance issues on the table GWXX276. We gave a simple query but still it dosent pick up the index. The below are the details.

The table is GWXX276, and the table definitions are as follows:

Code:
Code:
TABLE INDEXES                                                                 
 Index Name       Clustering PgSize UniqueRule   KeyColumn       Col# KSeq Ord
 GXX.GWIX2761          Y 4096 PRIMARY KEY                                 
                                                 MN_KEY             1  1   A
                                                 KN_KEY             3  2   A
                                                 BB_KEY             2  3   A 


The table is divided into partitions, its definition is
TABLESPACE PARTITIONS
Code:
Prt PriQty SecQty Stogrp VCatNm FrePg PctFr ChkFl Compr GBPCa TrkMd
1 -1 -1 GWSG001 GW 0 0 Y
2 -1 -1 GWSG001 GW 0 0 Y
3 -1 -1 GWSG001 GW 0 0 Y
..
--
until
Code:
60 -1 -1 GWSG001 GW 0 0 Y
and the partition limit keys are as follows:-

Prt LimitKey
1 319
2 320
..
--
52 370
53 371
54 372
55 373
56 374
57 375
58 376
59 377
60 378


We gave an explain on a simple query on this table,

Code:
SELECT                                               
    T276.MN_KEY                                     
   ,T276.MSR_KEY                                     
   ,T276.KN_KEY                        AS SLD_KN_KEY
                                                     
  FROM                                               
    GWXX276 T276                                     
                                                     
 WHERE T276.MN_KEY = 370                           




The explain results are as follows:-


Code:
PROGNAME  PLANNO  METHOD  CREATOR   TNAME                TABNO  ACCESS
------+---------+---------+---------+---------+---------+---------+---
DSNESM68       1       0  GXX   GWXX276                  1  R     
 
MATCHCOLS
+---------
        0
 



According to me the query should be able to pick up the index, but it dosent do it. because of this we have performance problem. There are around 80 million records in the table and for each partition we have around 8 million rows.

Other information are as follows:-

Cluster ratio of the index GWIX2761 is 100


Please help us why the query is not picking up the index?

Thank you very much,

Regards,
Anand
Back to top
View user's profile Send private message

GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Mon Oct 18, 2010 3:18 pm    Post subject:
Reply with quote

Code:
select page_range from plan_table
if it says 'Y' then it's ok.

Why would DB2 need to go to the index ? By going to the tablepart the where clause is resolved.
Because no extra criteria are given and the query is not index-only, it is better to do a table(part)scan.


Is the index Partitioned ?
Back to top
View user's profile Send private message
Anand Kumar

New User


Joined: 29 Aug 2007
Posts: 24
Location: chennai

PostPosted: Tue Oct 19, 2010 2:18 am    Post subject: Reply to: Performance problem in selecting data from one tab
Reply with quote

Hi GuyC,

The index is partitioned
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 Replace values in the input data Vikas Maharnawar DFSORT/ICETOOL 10 Thu May 11, 2017 2:18 pm
No new posts Group Data based on a key Arun Raj DFSORT/ICETOOL 7 Thu Apr 27, 2017 11:29 pm
No new posts unload data from table with lob columns farhad_evan DB2 0 Sat Apr 22, 2017 1:32 pm
No new posts SORT JSON type of data maxsubrat DFSORT/ICETOOL 8 Wed Apr 19, 2017 6:01 pm
No new posts Strings with double quotes having pro... raja Arumugam All Other Mainframe Topics 11 Thu Mar 30, 2017 10:34 am


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