IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Need help in tuning a DB2 query


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
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
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
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

Global Moderator


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

PostPosted: Fri Jul 27, 2012 7:22 pm
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
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
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
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
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

Superior Member


Joined: 14 Mar 2007
Posts: 10833
Location: italy

PostPosted: Fri Jul 27, 2012 8:57 pm
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
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: 741
Location: Whitby, ON, Canada

PostPosted: Sat Jul 28, 2012 12:03 am
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
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
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

Superior Member


Joined: 14 Mar 2007
Posts: 10833
Location: italy

PostPosted: Mon Jul 30, 2012 7:54 pm
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

Superior Member


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

PostPosted: Tue Jul 31, 2012 2:57 pm
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

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Tue Jul 31, 2012 7:01 pm
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Issue with EXEC CICS QUERY SECURITY c... CICS 6
No new posts DB2, write report based on query outp... DB2 1
No new posts Using WITH UR in SELECT SUM query DB2 1
No new posts SQLCODE = -122 while using the scalar... DB2 4
No new posts DB2: Need helping below DB2 query DB2 13
Search our Forums:

Back to Top