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
 

 

Need help in tuning a DB2 query

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

New User


Joined: 12 Jun 2012
Posts: 4
Location: India

PostPosted: Fri Jul 27, 2012 5:18 pm    Post subject: Need help in tuning a DB2 query
Reply with quote

I have a query where a condition like STATUS != 0 is being checked.
I need to tune this query as it is not performing efficiently.
STATUS can take a value from 0 to 6.
I wanted to ask which one of the below(if any) can be more efficient
1. Status > 0
2. Status in (0,1,2,3,4,5,6)
Please let me know if there is something better than above.
Back to top
View user's profile Send private message

gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Fri Jul 27, 2012 5:21 pm    Post subject:
Reply with quote

These two are better than checking for !=0

1. Status > 0
2. Status in (0,1,2,3,4,5,6)
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Fri Jul 27, 2012 7:22 pm    Post subject: Re: Need help in tuning a DB2 query
Reply with quote

Quote:

STATUS can take a value from 0 to 6.


You need only these values from 0 to 6 where table can contain values greater than 6 too ??

Quote:

1. Status > 0


I atleast feel this condition shoud be Status >=0 and Status < 7

and if it is going to be only 0 to 6 always and you wanted to select that has status anything from 0 to 6 why need a where clause?
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Fri Jul 27, 2012 7:48 pm    Post subject:
Reply with quote

Quote:
STATUS can take a value from 0 to 6.


Code:
WHERE STATUS BETWEEN 0 AND 6


using the BETWEEN keyword is much easier to read

using EXPLAIN output for different sql
would be a much better way to determine 'betterness'
as well as increase your skills.
Back to top
View user's profile Send private message
Md Immrann

New User


Joined: 12 Jun 2012
Posts: 4
Location: India

PostPosted: Fri Jul 27, 2012 8:24 pm    Post subject: Reply to: Need help in tuning a DB2 query
Reply with quote

Status can have values only 0,1,2,3,4,5,6
My query should select values 1,2,3,4,5,6
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Fri Jul 27, 2012 8:43 pm    Post subject: Re: Reply to: Need help in tuning a DB2 query
Reply with quote

Md Immrann wrote:
Status can have values only 0,1,2,3,4,5,6
My query should select values 1,2,3,4,5,6


So?
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Fri Jul 27, 2012 8:51 pm    Post subject: Re: Reply to: Need help in tuning a DB2 query
Reply with quote

dbzTHEdinosauer wrote:
Md Immrann wrote:
Status can have values only 0,1,2,3,4,5,6
My query should select values 1,2,3,4,5,6


So?


Changing from this
Quote:
WHERE STATUS BETWEEN 0 AND 6
to what he wants would be a difficult challenge!
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Fri Jul 27, 2012 8:57 pm    Post subject: Reply to: Need help in tuning a DB2 query
Reply with quote

what bothers me is that more and more often people asking belong to consulting and IT service providing organizations

as in this case
Quote:
Employer: ITC infotech


and even if I am rude to them I cannot really blame them,
is part of the overall strategy of such idiots ( the organization and their management )
not to provide proper training and the appropriate in house tutoring to less experienced people.

my rudeness is a <useless> attempt to wake them up
and make them start asking/pretending from their organization the basic in house support that once upon a time was customary to receive

end of rant
icon_exclaim.gif

PS.
I remember a topic where the TS confessed that he was advised to ask on the forum by his manager !
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Fri Jul 27, 2012 10:31 pm    Post subject:
Reply with quote

back in the 70's when i joined my first 'consultant' firm,
i asked my manager:
"why do you not provide us with training,
i have to spend my off-hours researching and learning things".

my manager replied:
"those of you who self-train/learn, become valuable assets for the company,
those of you who do not or are incapable, are allowed to change employer and work as an employee of our clients.
since we know that these new employees will constantly be creating problems,
we have more consulting work.
so by not training our employees, they become valuable assets to the company."
true story
Back to top
View user's profile Send private message
don.leahy

Active Member


Joined: 06 Jul 2010
Posts: 641
Location: Whitby, ON, Canada

PostPosted: Sat Jul 28, 2012 12:03 am    Post subject:
Reply with quote

dbzTHEdinosauer wrote:
back in the 70's when i joined my first 'consultant' firm,
i asked my manager:
"why do you not provide us with training,
i have to spend my off-hours researching and learning things".

my manager replied:
"those of you who self-train/learn, become valuable assets for the company,
those of you who do not or are incapable, are allowed to change employer and work as an employee of our clients.
since we know that these new employees will constantly be creating problems,
we have more consulting work.
so by not training our employees, they become valuable assets to the company."
true story

The consulting company wins either way: it keeps the self-learners on staff to do the work, and the others end up working for clients, screwing things up and therefore generating more work for the consulting company.

Impressively evil. icon_evil.gif

The only flaw I can see is that the best of the self-learners strike out on their own and form their own consulting companies.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Sat Jul 28, 2012 1:11 am    Post subject:
Reply with quote

Quote:
The only flaw I can see is that the best of the self-learners strike out on their own and form their own consulting companies.


you would be surprised at the number of people,
although competent, capable technicians,
are too afraid to try it on their own.

my guess (based on what I have seen) 1 in 10 try it.
the other 9 remain employees of someone.

tax laws in the states are daunting for independent consultants,
as well as companies always want a deep pocket fall guy
(can't get blood out of a stone!).

I like to work on a project 7,8,10,18 months and then take 6 months off,
then find another contract.
hard to sell that to most employers - employers want the individual afraid of tomorrow,
thus people will sit out their lives working for firms - for the security
Here in germany it is easier. I contract to a consultant firm
(there are 3 with which I have good relations)
as an independent consultant.
that means I make more hourly during the contract,
but not being an employee, the firm does not have to pay me when there is no work.
Back to top
View user's profile Send private message
Md Immrann

New User


Joined: 12 Jun 2012
Posts: 4
Location: India

PostPosted: Mon Jul 30, 2012 7:38 pm    Post subject:
Reply with quote

Thanks for all your valuable posts. Mr Enrico .. may I ask you what ignited you so much that you have to post my employer's name here and use words like 'idiots' for an organisation . I joined this forum to learn things from experienced people like you all, not to beg any one to do the work for me. I also did my homework before posting the query. Its just a mistake that I made by asking for your opinions.
I suppose I will be banned now for being rude.
Thank you all.
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Mon Jul 30, 2012 7:54 pm    Post subject: Reply to: Need help in tuning a DB2 query
Reply with quote

Quote:
I joined this forum to learn things from experienced people like you all, not to beg any one to do the work for me. I also did my homework before posting the query. Its just a mistake that I made by asking for your opinions.


looks like You did not read and understand what I wrote !

nobody implied that You begged for us to do Your work, or that You did not do Your homework

Your employer did not do it's homework!

Your organization ... ITC ... is in consulting
THEY should take care of tutoring and first aid help... for their employees
and it is clear from Your questions that did not care to do anything for You!

when/where I worked it was as part of my job the task of tutoring and first aid help for Younger and less experienced people.
( and the effectiveness of my tutoring was one of the evaluation items )

i used the word idiots because of the way they manage their people skill and tutoring.

but then relying on free help from more knowledgeable outside people might be a well thought plan to save on internal resources

when we reply on the forums we do it
ON OUR OWN TIME
FREE OF CHARGE


ant that gives us the right to comment on the posters organization's ways

after all our time and knowledge sharing result in a saving for ITC
icon_evil.gif

PS ...
most probably idiots is the wrong term, they are smarter than we are
the right term is <strongly derogatory adjective/noun for people/organizations who sell for money what they get for free >
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Tue Jul 31, 2012 2:57 pm    Post subject:
Reply with quote

Since the time discussion drifted, most of the things said in this thread sounds true - and I won't quote a particular sentence from someone -- but what's the solution, if any?
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: Tue Jul 31, 2012 7:01 pm    Post subject: Reply to: Need help in tuning a DB2 query
Reply with quote

Hi Anuj,

Quote:
I need to tune this query as it is not performing efficiently.

I've not replied as i was waiting for TS to provide some more info. . . Foolish of me, eh?

What is the indication that this STATUS check causes a problem? Is STATUS an index to the table? What are the other predicates - it would help if the entire query was posted. What does an EXPLAIN show?

d
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 PL/I code tuning/Performance improvement Virendra Shambharkar PL/I & Assembler 4 Mon Dec 05, 2016 11:57 am
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm
No new posts SQL Query optimization. arunsoods DB2 12 Sun Sep 04, 2016 6:27 am


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