On Thu, Apr 19, 2012 at 5:23 PM, George, William@FTB <william.geo...@ftb.ca.gov> wrote: > Note: I did attempt to send this to the DB2 group but it bounced back at > me so I'll send it here to IBM-MAIN and attempt to figure out what went > wrong with my DB2 auth. > > DB2 internals type of question. > > ISSUE > > We have a process that returns table rows matching on a masked name. The > "LIKE" predict is used with a wildcard to return the matching names. > We, not surprisingly, are experiencing time outs in our CICS region when > the mask is rather open ended. For example: LIKE "%AND%. There are > several million names in the database. > > We are now limiting the cursor to stop after 50 names have been returned > but we still see some time outs. > > MAIN QUESTION > > Does the cursor, prior to returning any results, compile all the results > before hand? Meaning, even though we have limited to cursor loop to 50 > is it still compiling the thousands of results in the background? Would > at FETCH FIRST help in this case? Something else? > > ADDITIONAL QUESTION > > Is there a better method than the LIKE predict? The name column is part > of an index and is the lead column. > > We are contemplating removing the beginning wildcard. LIKE 'AND%'. > This we figure should help but doesn't give the user as much > flexibility. > > Any insights on main question would be much appreciated. > > Thanks > > Bill >
1. If the first letter is a wild card, it is going throught the whole DB until it finds the minimum number. Simply requiring the first letter be entered would split this into 26 searchs. 2. Do you need a true wildcard search, or would a soundex search (ignoring vowels and looking at consonants that sound similar) be a better idea? http://en.wikipedia.org/wiki/Soundex A. Move the name fields to work fields. Translate letters to numbers as listed, Compress out the duplicate numbers then the zeros. Keep initial and 3 numbers, Store in database as separate fields. B. Take inquiry names, process same way, search soundex fields. -- Mike A Schwab, Springfield IL USA Where do Forest Rangers go to get away from it all? ---------------------------------------------------------------------- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@bama.ua.edu with the message: INFO IBM-MAIN
