This IBM Redbooks publication helps you design, install, manage, and tune stored procedures with DB2 for z/OS. Stored procedures can provide major benefits in the areas of application performance, code re-use, security, and integrity. DB2 has offered an ever improving support for developing and operating stored procedures. DB2’s enhancements are related to tooling, language support, system environment, and have opened new possibilities for secure, highly portable applications in line with the e-business strategy of today's organizations.

In this project we show how to develop stored procedures in several languages; we explore the new functions available for the z/OS platform deployment; and provide recommendations on setting up and tuning the appropriate stored procedure environment. The functions we have investigated include setting up the WLM environments, nesting stored procedure, invoking COBOL, C, REXX, SQL language programs, accounting, debugging options, special registers, and diagnostics. We have also set up, developed, and debugged Java stored procedures with the new Java universal driver in a DB2 for z/OS Version 8 environment. A chapter is devoted to DB2-supplied stored procedures. They can be used for almost all of a DBA’s tasks.

We start with the basic information, which is useful for the reader who is just beginning with stored procedures, but we also deal with more detailed and recent functionalities, which will be of interest for the more advanced users.

Part 1. Introduction

Chapter 1. Importance of stored procedures

Chapter 2. Stored procedures overview

Chapter 3. Our case study

Part 2. Operating environment

Chapter 4. Setting up and managing Workload Manager

Chapter 5. Language Environment set up

Chapter 6. RRSAF

Chapter 7. Security and authorization

Chapter 8. Operational issues

Part 3. Developing stored procedure

Chapter 9. Defining stored procedures

Chapter 10. COBOL programming

Chapter 11. C programming

Chapter 12. REXX programming

Chapter 13. Debugging

Chapter 14. SQL Procedures language

Chapter 15. Remote stored procedure calls

Chapter 16. Code level management

Part 4. Java stored procedures

Chapter 17. Building Java stored procedures

Chapter 18. Using the new Universal Driver

Part 5. Performance

Chapter 19. General performance considerations

Chapter 20. Server address space management

Chapter 21. I/O performance management

Part 6. Extending the functions

Chapter 22. Enhancements to stored procedures with DB2 V8

Chapter 23. Developing multi-threaded stored procedures in C language

Chapter 24. Accessing CICS and IMS

Chapter 25. DB2-supplied stored procedures

Chapter 26. Using LOBs

Chapter 27. Using triggers and UDFs

Part 7. Cool tools for an easier life

Chapter 28. Tools for debugging DB2 stored procedures

Chapter 29. The DB2 Development Center

Chapter 30. Using WSAD to debug Java stored procedures converted to Java applications

Part 8. Appendixes

Appendix A. Frequently asked questions

Appendix B. Samples for using DB2-supplied stored procedures

Appendix C. Additional material