DB2 Query Performance Issue

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?

Manuneedhi K · Posted: Mon Jul 21, 2008 2:57 pm

Please post the query here for someone to have a look and suggest a solution.

ashimer · Posted: Mon Jul 21, 2008 3:13 pm

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 ...

stodolas · Posted: Mon Jul 21, 2008 11:50 pm

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.

dbzTHEdinosauer · Posted: Tue Jul 22, 2008 12:18 pm

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.

vini_srcna · Posted: Tue Jul 22, 2008 2:03 pm

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.!!!

stodolas · Posted: Tue Jul 22, 2008 6:37 pm

dbzTHEdinosauer: be glad you don't work here then. All tables dev on up to prod have to go through the database people.

dbzTHEdinosauer · Posted: Tue Jul 22, 2008 7:44 pm

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.