DB2 does not necessarily materialize the whole result set in every situation. Since the DB2 optimizer knows nothing about the loop in your program it can't incorporate your intentions. Try using FETCH FIRST 50 ROWS ONLY clause or OPTIMIZE FOR 50 ROWS clause in your cursor. Wild card at the start of a like predicate tends to be problematic more often than not, avoid it if you can. Try limiting your query by other criteria if there are any. Generally speaking the answer to "give me anything / everything" is not computationally easy. HTH Mohammad
*********** original post ***************** 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 ---------------------------------------------------------------------- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@bama.ua.edu with the message: INFO IBM-MAIN
