View previous topic :: View next topic
|
Author |
Message |
Vidhya Karthi
New User
Joined: 28 Aug 2007 Posts: 18 Location: Chennai
|
|
|
|
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 |
|
|
Manuneedhi K
Active User
Joined: 07 May 2008 Posts: 115 Location: Chennai
|
|
|
|
Please post the query here for someone to have a look and suggest a solution. |
|
Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004 Posts: 551 Location: Bangalore
|
|
|
|
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 |
|
|
stodolas
Active Member
Joined: 13 Jun 2007 Posts: 632 Location: Wisconsin
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
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 |
|
|
vini_srcna
Active User
Joined: 26 May 2005 Posts: 178 Location: Copenhagen, Denmark
|
|
|
|
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 |
|
|
stodolas
Active Member
Joined: 13 Jun 2007 Posts: 632 Location: Wisconsin
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
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 |
|
|
|