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

How to replace the below query?


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
pkmurali
Warnings : 1

Active User


Joined: 15 Dec 2005
Posts: 271

PostPosted: Tue Feb 27, 2018 9:51 pm
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: 2455
Location: Hampshire, UK

PostPosted: Tue Feb 27, 2018 10:40 pm
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: 271

PostPosted: Tue Feb 27, 2018 11:31 pm
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: 2455
Location: Hampshire, UK

PostPosted: Wed Feb 28, 2018 2:14 am
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: 8696
Location: Dubuque, Iowa, USA

PostPosted: Wed Feb 28, 2018 3:14 am
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: 1353
Location: Israel

PostPosted: Wed Feb 28, 2018 9:28 pm
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

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Wed Feb 28, 2018 10:06 pm
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: 271

PostPosted: Fri Mar 02, 2018 1:34 pm
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

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Fri Mar 02, 2018 8:58 pm
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: 271

PostPosted: Sun Mar 04, 2018 11:48 am
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

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Mon Mar 05, 2018 9:18 pm
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: 271

PostPosted: Tue Mar 06, 2018 1:18 pm
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

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Tue Mar 06, 2018 8:25 pm
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Replace Multiple Field values to Othe... DFSORT/ICETOOL 12
No new posts RC query -Time column CA Products 3
No new posts Dynamically pass table name to a sele... DB2 2
No new posts Query on edit primary command CLIST & REXX 5
No new posts Query on edit primary command CLIST & REXX 1
Search our Forums:

Back to Top