Problem solve Get help with specific problems with your technologies, process and projects.

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.

Dig Deeper on IBM DB2 management

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.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.