69

Optimizer Hints have been available since DB2 Version 6 in 1999. This old method was hard to use and prone to error as it relied on the QUERYNO for each statement to link the SQL to the hint. If your program changed causing the QUERYNO to be different then the hint would not operate correctly. To resolve these problems DB2 10 for z/OS introduced a completely new method of providing hints based on the SQL Text and a Repository built into the DB2 Catalog. They can be applied to an SQL statement wherever it appears in the DB2 subsystem. Other advantages the new method provides include the ability to override certain Bind Options at the Statement rather than at the Package level, such as controls over Star joins and Parallelism options. DB2 11 has built on the initial implementation by allowing you to provide Filter Factors for predicates where the Optimizer is not able to calculate them, such as where Host Variables, Parameter Markers or Expressions are being used. This allows the Optimizer to make a more informed decision on the correct access path to use based on your knowledge of the application. This session will describe how to use Statement Level Access path hints as introduced in DB2 10 for z/OS, along with the additional features that have been added with DB2 11 for z/OS. 

At this presentation the audience will learn the stored procedure and user-defined function concepts in DB2 for z/OS. The application developers will get familiar with the recent enhancements in this area and get educated on how to monitor and tune these procedures and functions, while the database administrators will learn how some of their common tasks can be covered with a set of administrative procedures available with DB2 out of the box.

IBM DB2 10 has been in the market for 5 years. Many customers have migrated to DB2 10, but have not yet taken advantage of the DB2 10 SQL enhancements. DB2 11 is the new kid on the block, and while many customers are thinking about their migration plans, they have not investigated the DB2 11 SQL functional enhancements. This session will provide information on the SQL enhancements contained in these two DB2 releases and how to take advantage of these enhancements.