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
 

 

Syscolumns being access

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

Active User


Joined: 24 May 2006
Posts: 133
Location: India

PostPosted: Fri Mar 11, 2011 5:18 am    Post subject: Syscolumns being access
Reply with quote

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
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Mar 11, 2011 9:55 am    Post subject:
Reply with quote

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
View user's profile Send private message
Prajesh_v_p

Active User


Joined: 24 May 2006
Posts: 133
Location: India

PostPosted: Fri Mar 11, 2011 10:05 am    Post subject: Reply to: Syscolumns being access
Reply with quote

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
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Mar 11, 2011 10:34 am    Post subject:
Reply with quote

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
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Mon Mar 14, 2011 8:04 pm    Post subject:
Reply with quote

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
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 find RACF group for access to spooled... jzhardy JCL & VSAM 1 Mon May 08, 2017 11:46 am
No new posts IMS DC screen Access sivasaras IMS DB/DC 3 Fri Nov 18, 2016 6:38 pm
No new posts Access SQLCA fields inside a stored p... gangapd DB2 6 Thu Oct 27, 2016 10:20 am
No new posts RACF profile access vasanthz All Other Mainframe Topics 11 Fri Sep 23, 2016 5:51 am
No new posts How to perform Digest Access Authenti... vasanthz All Other Mainframe Topics 0 Tue Mar 15, 2016 4:47 pm


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