|
View previous topic :: View next topic
|
| Author |
Message |
Prajesh_v_p
Active User
Joined: 24 May 2006
Posts: 133
Location: India
|
|
|
|
Hi All,
While fine tuning a query, It has been obeserved that many of the CPU is going against objects SYSIBM.SYSCOLUMNS and SYSIBM.DSNTTX0 and other SYSIBM tables.
Do anyone know why a query against application table fetching this many pages from SYSIBM tables?
The table has 578 columns and the query is indexed with 2 columns match.
Thanks,
Prajesh V P |
|
| Back to top |
|
 |
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
Sounds like both a database design issue as well as inefficient queries. . .
| Quote: |
| the query is indexed with 2 columns match |
Does this mean there is an index of these 2 columns or that these 2 columns are part of some index(es)?
There really are 578 columns that are related to the primary index ? |
|
| Back to top |
|
|
Prajesh_v_p
Active User
Joined: 24 May 2006
Posts: 133
Location: India
|
|
|
|
Hi Dick,
The database has 578 columns and 2 column match is for an index which has 5 columns in it.
But why do you think an inefficiancy in the sql query can cause pages retrieved from catalogues tables?
Thanks,Prajesh V P |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
| The database has 578 columns and 2 column match is for an index which has 5 columns in it. |
Are these 2 columns the first 2 columns named in the index?
| Quote: |
| But why do you think an inefficiancy in the sql query can cause pages retrieved from catalogues tables? |
Rather an inefficient query can cause a high amout of temporary table usage. DB2 does use the db2 catalog for some "under the covers" work, but someone much more knowledgable than i am would have to provide details.
How many rows are in this table? How many rows are returned from a "high usage" query?
Have you or the dba run an explain on some of the problem queries to see what might be learned? |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
is this static or dynamic sql?
In static sql there is no reason why db2 would need to access SYSIBM.SYSCOLUMNS unless you have some exotic bind parameters. |
|
| Back to top |
|
|
|
|
