Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist 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: 6970
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: 6970
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 Query on IEFBR14 with GDG Ashishpanpaliya JCL & VSAM 3 Tue Nov 07, 2017 8:34 pm
No new posts Query on XMITIP abdulrafi All Other Mainframe Topics 1 Wed Oct 25, 2017 6:54 pm
No new posts SIGNAL ON HALT issue packerm CLIST & REXX 1 Fri Oct 20, 2017 6:56 pm
No new posts HEX value search in a DB2 query maxsubrat DB2 2 Wed Oct 04, 2017 3:04 pm
No new posts Create procedure issues -628 when add... chandraBE DB2 1 Mon Sep 18, 2017 12:16 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us