Q

How to write a query to search a field with just two characters?

A search by a field named (last name) must be capable of supporting a wild card search using at least the first two characters of the last name. What kind of query needs to written to search a field by just supplying the first two characters in a wild card search?
You will need to use the LIKE operator to implement the wildcard approach that you outlined above. I assume that you will be providing this capability to an on-line transaction whereby the user will enter the first several character of the last name as input on a screen. If so, your program should take those characters and then append percent signs to the end of the variable filling it entirely. So, for a variable of twenty bytes where the end user enters JA, your program would change that to JA%%%%%%%%%%%%%%%%%%. This allows DB2 to find any name that starts with the characters "JA".

You should also BIND your program with the REOPT(VARS) parameter to allow DB2 to use an index on this predicate. As long as at least one leading character is supplied DB2 can use an index. If, however, the leading character is unknown - that is, it is a percent sign (%) or underscore (_) - then DB2 would use an expensive table space scan. If you do not specify REOPT(VARS), then DB2 will not know what the variable contains and will likely...

resort to the scan for the query at BIND time. Of course, you could also use dynamic SQL to resolve this problem.

This was first published in January 2003

Dig deeper on IBM DB2 management

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchAWS

SearchContentManagement

SearchOracle

SearchSAP

SearchSOA

SearchSQLServer

Close