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