Top tips to improve the Performance of Dynamic SQL

In the early days of DB2 for z/OS the use of dynamic SQL was minimized at most DB2 installations due to fears over unreliable or unpredictable performance. That approach has been almost completely turned on its head now and it is common to find sites where the vast majority of application SQL is Dynamic rather than Static in nature. There are many reasons for this change, including the increasing use of languages such as Java rather than traditional COBOL and PL/I as well as Dynamic based Packaged Applications such as SAP. However, in order to support this gradual change over the years DB2 has also added many features to allow better Performance as well as Management of Dynamic SQL. Many don’t require application changes to allow you to exploit them, but others do and relatively minor changes in coding practice can produce significant benefits. This presentation will describe a variety of methods to improve the performance of Dynamic SQL and provide useful tips on how to best exploit them. Topics covered include using the Local and Global Statement Cache as well as the new Dynamic Plan Stability feature added in DB2 12 for z/OS and more.