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
 
DB2 Query Performance Issue

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

New User


Joined: 28 Aug 2007
Posts: 18
Location: Chennai

PostPosted: Mon Jul 21, 2008 2:42 pm    Post subject: DB2 Query Performance Issue
Reply with quote

Hi all,
I have used a query in my cobol program. The query is actually a cursor which uses joins for using two or three tables. I have got a very high DB2 scoring and have to reduce the same. I dont have admin rights to run the explain parameter. But, i have been asked to tune the query.
Do anyone know how can we tune a query without admin access?
Can anyone help me out please?
Back to top
View user's profile Send private message

Manuneedhi K

Active User


Joined: 07 May 2008
Posts: 115
Location: Chennai

PostPosted: Mon Jul 21, 2008 2:57 pm    Post subject:
Reply with quote

Please post the query here for someone to have a look and suggest a solution.
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Mon Jul 21, 2008 3:13 pm    Post subject:
Reply with quote

Vidhya,

First try changing the SQLID to your schema name or creator name in SPUFI ...

using SET CURRENT SQLID = 'creator';

mostly you may not have access to run EXPLAIN on user.Plan_table or the table may not exist ... so try changing the creator id and run the EXPLAIN or else if you can post the query with the exact details of indexes on the table, the keys etc we may be able to help ...

Before that please check with your seniors on how to run EXPLAIN ... they might be able to help ...
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Mon Jul 21, 2008 11:50 pm    Post subject:
Reply with quote

Shove the query to your DBAs and have them run the explains if they or your security people won't give you access. We have to do that at my shop.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Tue Jul 22, 2008 12:18 pm    Post subject:
Reply with quote

create your own plan table and populate it via spufi. check out the application programming guide.

In our shop the explains in the 'official' plan table are updated via compile/link/bind jcl from endevor.

to provide explains for other sql, (that which is not yet imbedded) we use our own plan tables.

never been in a shop where you can not create your own tables, etc... in at least one development environment.
Back to top
View user's profile Send private message
vini_srcna

Active User


Joined: 26 May 2005
Posts: 178
Location: Copenhagen, Denmark

PostPosted: Tue Jul 22, 2008 2:03 pm    Post subject:
Reply with quote

All you need to run an Explain is the access to run that query & your own plan table.

ex. If it is a select query, then you need have that select access. I beleive you can create your own plan table, atleast in DSNDB04.!!(not preferably)

Wait...you said its cobol program. BIND the program with EXPLAIN(YES). Check in the plan table of the OWNER mentioned in the BIND parameter. I'm sure there should be one plan table existing.!!!
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Tue Jul 22, 2008 6:37 pm    Post subject:
Reply with quote

dbzTHEdinosauer: be glad you don't work here then. All tables dev on up to prod have to go through the database people.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Tue Jul 22, 2008 7:44 pm    Post subject:
Reply with quote

I meant, that I can create tables, me being the creator. I do not have authority to create anything with the normal creator name, but with my user id i can.

not being able to create db2 objects under my userid/creator id would be cause for me to bring to the attention of management that the dba's are BSing.

but, this is one of my standard questions when interviewing for a consultants job. If the idiots want to make development to difficult, I will go elsewhere.
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 Can we limit length in concatenation ... balaji81_k DB2 7 Tue Aug 22, 2017 2:50 am
No new posts CONTIG for performance? JPVRoff JCL & VSAM 7 Fri Jun 09, 2017 8:39 am
No new posts Need DB2 query to fetch previous row ! Chandan1993 DB2 10 Sat Jun 03, 2017 10:43 am
No new posts Issue with NDM process to transmit ES... chetanambi All Other Mainframe Topics 6 Wed May 03, 2017 10:52 am
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm


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