DB2-Optimize for N rows

DB2-Optimize for N rows - Programmers Only . One of the...

Info iconThis preview shows pages 1–2. Sign up to view the full content.

View Full Document Right Arrow Icon
Programmers Only . One of the most common questions I am asked is what the "optimize for n rows" clause does. And, while the answer seems easy enough on the surface, the real answer involves a pretty thorough understanding of how DB2 works in situations where more than one row qualifies for our SQL statement. Sometimes we code an SQL cursor that has a logical result set of hundreds of thousands or even millions of rows. And sometimes we OPEN that cursor and FETCH again and again, until DB2 makes a plea that enough is enough - there just aren't any more rows out there that meet our criteria - and gives us a +100 SQLCODE. But sometimes we do not want to read all of the qualified rows. Sometimes we OPEN the cursor and FETCH just once, or just five times, or just 10 times before we decide programmatically that no more rows are needed to fill our screen or to supply our program logic requirements. So what happens to the rest of the rows when we stop short of completion, before we get that +100 SQLCODE? And is there anything that we can do to improve performance when we read less than the full answer set of rows? The access path that DB2 chooses for your cursor is based on the DB2 Optimizer's estimation of all possible realistic paths. DB2 assesses each feasible, reasonable path and chooses the one with the least cost overhead (in terms of CPU, I/O, and SORT) for retrieving all of the rows that DB2 thinks will meet your search criteria, your WHERE clause predicates. DB2 determines how many rows will qualify for your query by evaluating your predicates and by looking at Catalog statistical information (put there by the RUNSTATS utility). Then DB2 determines the least cost path for returning that estimated number of rows back to your program. A key component of the calculation is whether or not your SQL contains SORT syntax. If you care what order your rows are returned in, the calculation becomes a bit more sophisticated. DB2 has to determine the least-cost access path for returning all of the qualified rows in the specified order. The critical piece of information in the process I just described is the fact that DB2 assumes that you want all of the qualified rows. If you have no sort syntax in your SQL, then DB2 evaluates each possible path, looks at each of the indexes on the table, and determines which index (if any) will net in the fewest index reads and the fewest table reads to get all of your rows back to you one at a time. That's right - one at a time. Each time you fetch, you are telling DB2 to find the next qualified row. And each fetch causes DB2 to go looking for the next qualified row. It is a common misconception that the OPEN CURSOR statement connects to DB2 and performs a process that causes the retrieval of all of the qualified rows. The myth is that DB2 uses all of the qualified rows to create a "result set" and puts that result set somewhere (In memory? On disk?) to be accessed each time you FETCH. The truth is that IBM probably would not have sold a single license for DB2 if that were true. Can you
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Image of page 2
This is the end of the preview. Sign up to access the rest of the document.

This note was uploaded on 11/24/2011 for the course FINANCE 123 taught by Professor Hbr during the Spring '11 term at Lethbridge College.

Page1 / 5

DB2-Optimize for N rows - Programmers Only . One of the...

This preview shows document pages 1 - 2. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online