In this chapter, you will learn how to
At this point, you should know almost everything about writing DB2 SQL procedures. You have learned about SQL PL structure, flow control, cursor usage, condition handling, dynamic SQL, and using nested procedure calls. The focus of this chapter is on DB2 application development features that are not part of SQL PL per se but are commonly employed in SQL procedures, functions, and triggers, because they offer significant benefits in the areas of functionality, performance, and manageability.
Using more efficient SQL is arguably the best way to improve performance of stored procedures. If SQL is inefficient, database tuning can only get you so far. Increasing memory or adding more disks to the system may be a way to make DB2 run larger, but rewriting SQL for more efficient execution allows DB2 to run smarter.
Declared global temporary tables can be used when you need to have a table exist only for a particular user and only for the duration of his or her session. They are also used for staging data for manipulation using SQL, and have the option of disabling transaction logging to improve performance of data-intensive operations.
Using save points and nested save points can give you greater control over transactions. Save points allow you to start a transaction and keep portions of a transaction while rolling back other portions, without committing the overall unit of work.
Sometimes it may be useful to use stored procedures as wrappers for sequence objects for increased portability. This will appeal mostly to application developers who wish to create applications to run on multiple database platforms.