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
 
How to replace the below query?

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
pkmurali
Warnings : 1

Active User


Joined: 15 Dec 2005
Posts: 256

PostPosted: Tue Feb 27, 2018 9:51 pm    Post subject: How to replace the below query?
Reply with quote

Hi All,

I have a query as shown below,

Code:


Select c1 from tab

where c2 <> spaces;


C2 CHAR(8) NOT NULL

I need to select the row from tab where C2 greater than space and less than high value. Is that below query is fine or is there any efficient methods available to query.



Code:

Select c1 from tab
where  c2 between hex('40') and hex('FF');
[/b]
Back to top
View user's profile Send private message

Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2110
Location: UK

PostPosted: Tue Feb 27, 2018 10:40 pm    Post subject:
Reply with quote

You say "greater than" and "less than" but BETWEEN is inclusive so you need hex('41') and hex('fe').

BETWEEN is, apparently, more efficient than using "a >= ' ' AND a <= x'FF'"
Back to top
View user's profile Send private message
pkmurali
Warnings : 1

Active User


Joined: 15 Dec 2005
Posts: 256

PostPosted: Tue Feb 27, 2018 11:31 pm    Post subject:
Reply with quote

Thanks Nick. Is it enough to compare 1 byte hex'41' and hex'fe' or it should be 8 bytes ?
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2110
Location: UK

PostPosted: Wed Feb 28, 2018 2:14 am    Post subject:
Reply with quote

It's Nic.

Try it.
Back to top
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 8405
Location: Dubuque, Iowa, USA

PostPosted: Wed Feb 28, 2018 3:14 am    Post subject:
Reply with quote

Quote:
Is it enough to compare 1 byte hex'41' and hex'fe' or it should be 8 bytes ?
This really depends upon your data and the requirement. In most cases, checking the first character is enough since X'4100000000000000' is going to be greater than X'40FFFFFFFFFFFFFF'.
Back to top
View user's profile Send private message
Marso

REXX Moderator


Joined: 13 Mar 2006
Posts: 1303
Location: Israel

PostPosted: Wed Feb 28, 2018 9:28 pm    Post subject:
Reply with quote

pkmurali wrote:
Is it enough to compare 1 byte hex'41' and hex'fe' or it should be 8 bytes ?
It is not:
The lowest key you will fetch is x'41000000...' and the highest is x'FEFFFFFF...'
What about x'4041404040...' or x'FF000000..' for example ?
The first one is higher than all spaces but lower than your lowest key, the second is higher than your highest key but lower than high-values.
And none of them will be fetched.
Back to top
View user's profile Send private message
enrico-sorichetti

Senior Member


Joined: 14 Mar 2007
Posts: 10535
Location: italy

PostPosted: Wed Feb 28, 2018 10:06 pm    Post subject: Reply to: How to replace the below query?
Reply with quote

why would You care about specifying the HIGH_VALUE
should' nt be enough to specify

YOUR_8_BYTES_COLUMN > x'4040404040404040'
Back to top
View user's profile Send private message
pkmurali
Warnings : 1

Active User


Joined: 15 Dec 2005
Posts: 256

PostPosted: Fri Mar 02, 2018 1:34 pm    Post subject:
Reply with quote

Thanks to everyone addressed for this issue. This query cam when one of my colleague used <> which is not recommended. In my project I don't have any db2 tools to measure the query will go under sequential scan. Can I use only EXPLAIN provided if I have access to validate? I have to show how the.comparison of > spaces query with <> . Will both will impose sequential scan ?
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 2038
Location: NY,USA

PostPosted: Fri Mar 02, 2018 8:58 pm    Post subject:
Reply with quote

Quote:
This query cam when one of my colleague used <> which is not recommended.
Don't go too fancy. How much time are you trying to save here? How many rows are in context? What is explain results? have you run anything yet in QA with real data volume? Is the column part of the index?
Keep it simple and easy maintainability for others in future when they see the code.
Back to top
View user's profile Send private message
pkmurali
Warnings : 1

Active User


Joined: 15 Dec 2005
Posts: 256

PostPosted: Sun Mar 04, 2018 11:48 am    Post subject:
Reply with quote

I thought of writhing a optimized query. Since <> is stage 1 predicate I believe > spaces would be indexable. There 5 million rows in e table. The column is not a part of index. In my previoous post I have mentioned that the query is not used with EXPLAIN. Please suggest if a non indexable column is used both <> and > will have the same performance ?
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 2038
Location: NY,USA

PostPosted: Mon Mar 05, 2018 9:18 pm    Post subject:
Reply with quote

You still did not answer , have you run the query yet and measured the performance? All you are talking is in air.
if you want something > spaces then code that way or if you want something <> SPACES then code that way. If you get any performance issues, then get DBA advice. Also what harms you to add index on this column?
Back to top
View user's profile Send private message
pkmurali
Warnings : 1

Active User


Joined: 15 Dec 2005
Posts: 256

PostPosted: Tue Mar 06, 2018 1:18 pm    Post subject:
Reply with quote

Hi Rohit

I dont have access to execute Explain query. I have executed both queries in db2 spufi . So i am not able to measure the performance.

Thanks
Murali
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 2038
Location: NY,USA

PostPosted: Tue Mar 06, 2018 8:25 pm    Post subject:
Reply with quote

Quote:
I dont have access to execute Explain query. I have executed both queries in db2 spufi . So i am not able to measure the performance
Do you have DBA's? If not then forum cannot help, otherwise ask them to give you the results of explain, they should be able to give you. However, by having proper cardinality and indexing this is non issue also, there is no time out when you ran in SPUFI, so why do you consider it as an issue ?
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 secondary index Suja.Sai IMS DB/DC 0 Wed Oct 31, 2018 9:47 pm
No new posts Silly question maybe - REPLACE member... Cloink TSO/ISPF 15 Thu Oct 25, 2018 6:38 pm
No new posts Query regarding STOP REGION XX ABDU... ashek15 IMS DB/DC 11 Fri Oct 19, 2018 10:13 am
No new posts Facing issue while executing multi ro... aagarwal88 DB2 6 Tue Oct 02, 2018 8:11 am
No new posts DB2 SQL Query to fetch all instances ... MallikarjunSM DB2 2 Thu Sep 27, 2018 6:46 pm

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