DB2® SQL PL: Essential Guide for DB2® UDB on Linux™, UNIX®, Windows®, i5/OS™, and z/OS®, Second Edition

DB2® SQL PL: Essential Guide for DB2® UDB on Linux™, UNIX®, Windows®, i5/OS™, and z/OS®, Second Edition

by Zamil Janmohamed, Clara Liu, Drew Bradstock, Raul Chong, Michael Gao, Fraser McArthur, Paul Yip
Released November 2004
Publisher(s): IBM Press
ISBN: 0131477005

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

  • IBM's definitive guide to writing DB2 SQL PL stored procedures, triggers, UDFs, and dynamic compound SQL

  • Fully updated to reflect the entire DB2 UDB product family, and new SQL PL improvements for Windows, UNIX, Linux, iSeries, and zSeries

  • Presents expert tips and best practices drawn from extensive experience in real customer environments

  • Covers everything from basic program structure through advanced techniques

  • Includes up-to-the-minute coverage of optimization and troubleshooting

    • Using the IBM DB2 SQL Procedural Language (SQL PL), programmers can drive major improvements in database performance, manageability, and time-to-market. Now, IBM's own experts present the definitive guide to DB2 SQL PL development on any platform: Windows, UNIX, Linux, iSeries, or zSeries.

    DB2 SQL PL, Second Edition shows developers how to take advantage of every facet of the SQL PL language and development environment. The authors offer up-to-the-minute coverage, best practices, and tips for building basic SQL procedures, writing flow-of-control statements, creating cursors, handling conditions, and much more. Along the way, they illuminate advanced features ranging from stored procedures and triggers to user-defined functions.

    The only book to combine practical SQL PL tutorials and a detailed syntax reference, DB2 SQL PL, Second Edition draws on the authors' unparalleled expertise with SQL PL in real business environments. Coverage includes

  • Using SQL PL to improve manageability and performance, while clearly separating DBA and development roles

  • Writing more efficient stored procedures, triggers, user-defined functions (UDFs), and dynamic compound SQL

  • Identifying SQL PL performance bottlenecks--and resolving them

  • Leveraging new language enhancements for Windows, UNIX, and Linux: improved table function support, session based locking, nested save points, new prepare options, and more

  • Using new features for iSeries V5R3: built-in string and date/time manipulation functions, SEQUENCE objects, and more

  • Utilizing zSeries Version 8's integrated stored procedures debugging and improved SQL Conditions support

  • Mastering DB2 Development Center, the unified development environment for creating DB2 stored procedures

    • Whether you're developing new SQL PL applications, migrating or tuning existing applications, or administering DB2, you'll find this book indispensable.

    Table of contents

    1. Copyright
    2. IBM Press
      1. On Demand Computing Books
      2. DB2 Books
      3. More Books from IBM Press
    3. Contributors
    4. Acknowledgments
    5. About the Authors
    6. Foreword
    7. 1. Introduction
      1. Installing DB2
      2. History of Stored Procedures
      3. A Brief Introduction to Stored Procedures, Triggers, and Functions
      4. What’s New in the Second Edition
        1. DB2’s SQL Procedural Language (SQL PL)
        2. Other Stored Procedure Languages
        3. SQL PL Development Tools–DB2 Development Center
      5. Book Conventions
        1. Syntax Description
        2. Meanings of Style
        3. Code
        4. Book Structure
        5. Contacting the Authors
    8. 2. Basic SQL Procedure Structure
      1. The CREATE PROCEDURE Statement
        1. Procedure Name
        2. Parameters
        3. Specific Name
        4. DYNAMIC RESULT SETS
        5. CONTAINS SQL, READS SQL DATA, MODIFIES SQL DATA
        6. DETERMINISTIC or NOT DETERMINISTIC
        7. CALLED ON NULL INPUT
        8. INHERIT SPECIAL REGISTERS
        9. OLD SAVEPOINT LEVEL, NEW SAVEPOINT LEVEL
        10. LANGUAGE SQL
        11. EXTERNAL ACTION or NO EXTERNAL ACTION
        12. PARAMETER CCSID
        13. SQL Procedure Body
      2. The SQL Procedure Body Structure
        1. Comments
        2. Variables
        3. Setting Variables
        4. DB2 Special Registers
      3. Bringing It All Together Example
      4. DB2 UDB for iSeries Considerations
        1. FENCED and NOT FENCED
        2. COMMIT ON RETURN
        3. SET OPTION Statement
      5. DB2 UDB for zSeries Considerations
        1. FENCED
        2. NO DBINFO
        3. NO COLLID or Collid collection-id
        4. WLM ENVIRONMENT
        5. ASUTIME
        6. STAY RESIDENT
        7. PROGRAM TYPE
        8. SECURITY
        9. RUN OPTIONS
        10. COMMIT ON RETURN
        11. STOP AFTER SYSTEM DEFAULT FAILURES or CONTINUE AFTER FAILURE
      6. Summary
    9. 3. Overview of SQL PL Language Elements
      1. DB2 Data Types
        1. Valid DB2 Built-In Data Types and Their Value Ranges
      2. Large Objects
      3. Choosing Proper Data Types
      4. Working with User-Defined Distinct Types
      5. Data Manipulation
        1. Working with Dates and Times
        2. Working with Strings
      6. Working with Generated Columns
      7. Working with Identity Columns and Sequence Objects
        1. Identity Column
        2. Generate Value Retrieval
        3. Change of Identity Column Characteristics
        4. Sequence Object
        5. Change of Sequence Object Characteristics
        6. Privileges Required for Using Sequence Objects
        7. Generated Value Retrieval
      8. Platform Portability Considerations
      9. Summary
    10. 4. Using Flow of Control Statements
      1. Compound Statements
        1. NOT ATOMIC Compound Statement
        2. ATOMIC Compound Statement
        3. Using Labels
      2. Conditional Statements
        1. The IF Statement
        2. The CASE Statement
      3. Looping Statements
        1. FOR Loop
        2. WHILE Loop
        3. REPEAT Loop
        4. LOOP
      4. Transfer of Control Statements
        1. GOTO
        2. LEAVE
        3. ITERATE
        4. RETURN
        5. COMMIT
        6. ROLLBACK
      5. Summary
    11. 5. Understanding and Using Cursors and Result Sets
      1. Using Cursors in SQL Procedures
      2. Positioned Delete
      3. Positioned Update
      4. Selecting Data from UPDATE, INSERT, or DELETE Statements
      5. Cursor Behavior with COMMIT/ROLLBACK
        1. Save Points Within Procedures
      6. Using Cursors to Return Result Sets
      7. Returning Multiple Result Sets
      8. Cursors and Locking
        1. Lock Modes
        2. Isolation Levels
        3. Uncommitted Read (UR)
        4. No Commit (NC)—iSeries Only
        5. Cursor Stability (CS)
        6. Read Stability (RS)
        7. Repeatable Read (RR)
        8. Controlling Locking in DB2 LUW
        9. No Wait and Lock Wait
        10. Controlling Locking in DB2 UDB for iSeries
        11. Controlling Locking in DB2 UDB for zSeries
        12. Evaluate Uncommitted and Avoiding Locks
        13. Lock Timeout and Deadlock Detection
      9. DB2 UDB for iSeries Considerations
        1. DECLARE CURSOR Statement in DB2 UDB for iSeries
      10. Summary
    12. 6. Condition Handling
      1. Basic Error Checking: SQLCODE and SQLSTATE
      2. Condition Handlers
      3. Custom Errors and Error Messages
        1. Using SIGNAL to Force the Invocation of a Handler
        2. Using RESIGNAL to Force the Invocation of a Handler
        3. Scope of Handlers
        4. RAISE_ERROR Function
        5. GET DIAGNOSTICS
      4. Processing Results from Called Procedures
      5. Bringing It All Together
      6. DB2 UDB for iSeries Considerations
        1. SIGNAL and RESIGNAL Statements
        2. RAISE_ERROR Function
        3. GET DIAGNOSTICS
      7. DB2 UDB for zSeries Considerations
        1. Condition Handlers
        2. GET DIAGNOSTICS Statement
        3. RETURN Statement
      8. Summary
    13. 7. Working with Dynamic SQL
      1. PREPARE and EXECUTE: The Two Phases of Any SQL Statement
      2. Dynamic SQL Versus Static SQL
      3. Restrictions and Considerations
      4. Using Dynamic SQL with EXECUTE IMMEDIATE
      5. Escaping Single Quotes (’)
      6. Reusing Dynamic SQL Statements with PREPARE and EXECUTE
      7. Using Dynamic SQL in Cursors
      8. Dynamic CALL Statements
      9. Authorization Consideration
      10. Summary
    14. 8. Nested SQL Procedures
      1. Basic Nested SQL Procedures
      2. Passing Parameters Between Nested SQL Procedures
      3. Returning Values from Nested SQL Procedures
      4. Returning Result Sets from Nested SQL Procedures
        1. Returning Result Sets to the Client
        2. Returning Result Sets to the Caller
        3. Receiving Result Sets as a Caller
        4. Receiving Multiple Result Sets as a Caller
      5. Receiving Results from a Procedure in a Trigger
      6. Levels of Nesting
        1. Recursion
      7. Security
      8. Summary
    15. 9. User-Defined Functions and Triggers
      1. The CREATE FUNCTION Statement
        1. Function Name and Parameters
        2. Returns Function Output
        3. Specific Name
        4. Language SQL
        5. DETERMINISTIC or NOT DETERMINISTIC
        6. EXTERNAL ACTION or NO EXTERNAL ACTION
        7. CONTAINS SQL, READS SQL DATA, MODIFIES SQL DATA
        8. STATIC DISPATCH
        9. CALLED ON NULL INPUT
        10. INHERIT SPECIAL REGISTERS (LUW and iSeries Only)
        11. PREDICATES (LUW only)
        12. SQL Function Body
      2. User-Defined Functions by Examples
        1. A Simple Scalar UDF Example
        2. A Complex Scalar UDF Example
        3. A Table UDF Example (LUW and iSeries)
        4. Invoking SQL Procedures in UDFs (LUW and iSeries Only)
      3. The CREATE TRIGGER Statement
        1. Trigger Name
        2. BEFORE, AFTER, or INSTEAD OF
        3. INSERT, DELETE, or UPDATE
        4. REFERENCING Clauses
        5. FOR EACH ROW or FOR EACH STATEMENT
        6. MODE DB2SQL
        7. Triggered Action
      4. Triggers by Examples
        1. A BEFORE Trigger Example
        2. An AFTER Trigger Example
        3. A Complex AFTER Trigger Example
        4. An INSTEAD OF Trigger Example (for LUW Only)
        5. A Comparison of View Triggers and Table Triggers (LUW only)
        6. Invoking UDFs and SQL Procedures from Triggers
      5. Considerations for Invoking SQL Procedures from UDFs and Triggers
        1. Data Access Restrictions
        2. Transaction Control in SQL Procedures
        3. Table Read or Write Conflict in SQL Procedures on LUW
      6. DB2 for iSeries Considerations
      7. DB2 for zSeries Considerations
        1. The CREATE FUNCTION Statement
        2. Trigger Considerations
        3. Invoking UDFs and SQL Procedures from Triggers
        4. Nesting SQL Statements
      8. Summary
    16. 10. Leveraging DB2 Application Development Features
      1. Leveraging Advanced SQL
        1. Combining SELECT with INSERT, UPDATE, and DELETE
        2. NEW TABLE and OLD TABLE
        3. INCLUDE
        4. FINAL TABLE
        5. Deleting a Few Rows at a Time
        6. MERGE Statement
      2. Declared Global Temporary Tables
        1. Introduction to Temporary Tables
        2. Creating the Environment for Temporary Tables
        3. Declaring Global Temporary Tables
        4. Using Temporary Tables in SQL Procedures
        5. Sharing Temporary Tables Between Multiple Procedures
        6. Considerations
        7. Created Global Temporary Tables
      3. Working with Save Points
        1. Introduction to Application Save Points
        2. Using Save Points in SQL Procedures
      4. Sequence Objects
      5. Summary
    17. 11. Deploying SQL Procedures, Functions, and Triggers
      1. Deploying on Linux, UNIX, and Windows
        1. Deploying SQL Procedures Using DDL
        2. Deploying Functions
        3. Deploying Functions Using the Development Center
        4. Deploying Functions Using Scripts
        5. Deploying Triggers
        6. Deploying Using GET ROUTINE and PUT ROUTINE
        7. Deploying Using Older Releases of DB2
        8. Deploying from Pre-V8.2 to Post-V8.2
        9. Deploying from Pre-V8.2 to V8.2
        10. Deploying from V8.2 to Pre-V8.2
        11. Code Page Considerations
        12. Backup and Recovery Considerations
      2. Deployment Considerations for DB2 for zSeries
        1. Deploying SQL Procedures
        2. Deploying Functions and Triggers
        3. Deployment Considerations Based on Your C Compiler Requirements
      3. Common Deployment Considerations for LUW and zSeries
        1. Deploying from One Database to Another
      4. Deploying in DB2 UDB for iSeries
        1. Using the Development Center
        2. Using iSeries Navigator and CL Commands in iSeries
      5. Summary
    18. 12. Performance Tuning
      1. Performance Considerations for LUW
        1. The Configuration Advisor
        2. Monitoring SQL Performance
        3. Using Explain to Analyze Access Plans
        4. The Design Advisor
        5. Large Object (LOB) Considerations
        6. Temporary Tables Considerations
        7. Improving Performance
        8. Creating Indexes
        9. Ongoing Maintenance to Keep Performance at Its Peak
      2. DB2 for iSeries Considerations
        1. Monitoring Memory Usage
        2. Performance Monitors and Commands
        3. Query Optimizer and Access Plans
        4. Variable Length Columns and Large Objects
        5. Database Statistics
      3. DB2 for zSeries Considerations
        1. The EDM Statement Cache
        2. The Performance Expert Tool
        3. Improving Response Time and Throughput
        4. Buffer Pool Size
        5. RID Pool Size
        6. RUNSTATS
        7. PCTFREE and FREEPAGE
        8. Distribute Your Datasets Efficiently
        9. Create Additional Work File Table Spaces
        10. Space Formatting
        11. Avoid Excessive Extents
        12. Maximum Number of Open Datasets
        13. Resolving Excessive Wait Time or Timeouts for UDFs and Stored Procedures
        14. Tuning Your Queries
        15. Giving Optimization Hints to DB2
        16. Limit Resources for a Stored Procedure
        17. Considerations for Stored Procedures in a Distributed Environment
      4. Summary
    19. 13. Best Practices
      1. Table and Index Best Practices
        1. Table Considerations
        2. Temporary Table Considerations
        3. Index Considerations
      2. Best Practices for Easier Code Maintenance
        1. Code Readability
        2. Code Size
        3. Grouping SQL Procedures by Schema
        4. Naming Conventions
        5. Return Values Versus Out Parameters
        6. Exception Handling
        7. Commit and Rollback
      3. Best Practices for Performance
        1. Exceptions
        2. Repetitive SQL
        3. Table Truncation
        4. Reducing Locking
        5. On LUW
        6. On iSeries
        7. On zSeries
        8. Dynamic SQL for DDL
        9. Using a Single Insert to Replace Multiple Insert Statements
        10. Value Assignment
        11. Deterministic Versus Not Deterministic
        12. Case Statement
        13. Functions
      4. Working with Result Sets
        1. Cursor Considerations
        2. Limiting Result Sets
        3. Optimizing Result Set Returns
        4. Minimize Result Set Columns
      5. Precompile Options on iSeries
      6. Summary
    20. A. Getting Started with DB2
      1. Getting Started with DB2 UDB for LUW
        1. Launching the DB2 Command Window
        2. Launching the DB2 Command Line Processor
        3. Overview of DB2 Architecture
        4. DB2 Instances
        5. Creating Databases
        6. Executing SQL in the DB2 Command Window
        7. Executing SQL in DB2 Interactive Mode
        8. Configuring the DB2 Environment
        9. DB2 Profile Registries
        10. Understanding Buffer Pools
        11. Working with Table Spaces
        12. Working with Tables
        13. Introducing the System Catalog Tables
      2. Getting Started with DB2 UDB for iSeries
        1. Launching the Interface
        2. Overview of the DB2 UDB for iSeries Architecture
        3. Executing SQL in DB2 UDB for iSeries
        4. Working with Tables
        5. Catalog
      3. Getting Started with DB2 UDB for zSeries
        1. Overview of the DB2 UDB for zSeries Architecture
        2. Catalog
        3. Interacting with DB2 for zSeries
        4. Using DB2I
        5. Using DB2 LUW Client Tools
      4. Summary
    21. B. Inline SQL PL for DB2 UDB for Linux, UNIX, and Windows
      1. Dynamic Compound Statement
      2. Stand-Alone Code
      3. Choosing Between Dynamic Compound Statements and SQL Procedures
      4. Summary
    22. C. Building from the Command Line
      1. Configuring the Build Environment
      2. Building SQL Procedures
        1. Creating a DB2 Command-Line Processor Script
      3. Building SQL Procedures in DB2 UDB for iSeries
      4. Building SQL Procedures in DB2 for zSeries
        1. The Build Process
        2. Methods to Build a DB2 for zSeries SQL Procedure
      5. Summary
    23. D. Using the DB2 Development Center
      1. Installing the DB2 Development Center
      2. DB2 for Windows
      3. DB2 for Linux and UNIX
      4. Using the Development Center for the First Time
      5. Development Center Views
      6. Customizing the Development Center
      7. Running Procedures
      8. Stored Procedure Run Settings
      9. Debugging Stored Procedures
      10. Debugging Nested Stored Procedures
      11. Working with Projects
      12. Importing Stored Procedures
      13. DB2 for iSeries Considerations
      14. DB2 for zSeries Considerations
      15. Environment Settings
      16. Developing SQL Stored Procedures from the Development Center
      17. The Project and Server Views
      18. Debugging DB2 for zSeries SQL Stored Procedures
      19. Summary
    24. E. Security Considerations in SQL Procedures
      1. Privileges
        1. Privileges Required by Developers
        2. Privileges Required by LUW Stored Procedure Developers
        3. Privileges Required by iSeries Stored Procedure Developers
        4. Privileges Required by zSeries Stored Procedure Developers
        5. Privileges Required by Users
        6. Privileges Required by LUW Users
        7. Privileges Required by iSeries Users
        8. Privileges Required by zSeries Users
      2. Using Stored Procedures in a Production Environment
      3. DB2 for zSeries Considerations
        1. DYNAMICRULES Bind/Rebind Option Value
        2. The Package’s Runtime Environment
      4. The DEFINE Behavior
      5. The INVOKE Behavior
      6. Common Attribute Values for the DEFINE and INVOKE Behaviors
      7. Simplifying Authorization
      8. Summary
    25. F. DDL
      1. Creating the SAMPLE Database on LUW
      2. Creating the SAMPLE Database on iSeries
      3. Creating the SAMPLE Database on zSeries
      4. SAMPLE Database DDL: Script 1 sampleDDL_1.db2
      5. DDL for Additional Database Objects: Script #2 sampleDDL_2.db2
    26. G. Additional Resources
      1. IBM Redbooks
        1. LUW
        2. iSeries
        3. zSeries
        4. Cross-Platform
      2. Certification
        1. Certification Guides
        2. Tutorials
      3. Education
        1. IBM Learning Services
        2. Application Programming
        3. Database Administration
        4. Performance Tuning
        5. Free Self-Study Computer-Based Training Courses
        6. IBM Solution Partnership Centers (SPCs)
        7. Tutorials
      4. Books
      5. Additional Websites
        1. DB2 FixPaks and Downloads
      6. Email Services and Periodicals
      7. User Groups and Conferences
    27. H. Sample Application Code
      1. Receiving Result Sets in Java
        1. Receiving Result Sets in a Java Application
        2. Receiving Result Sets in a Java Stored Procedure
      2. Receiving Result Sets in a C or C++ Application
      3. Receiving Multiple Result Sets in Java
        1. Receiving Multiple Result Sets in a Java Application
        2. Receiving Multiple Result Sets in a Java Stored Procedure
      4. Receiving Multiple Result Sets in a C or C++ Application
    28. CD-ROM Warranty

    Product information

    • Title: DB2® SQL PL: Essential Guide for DB2® UDB on Linux™, UNIX®, Windows®, i5/OS™, and z/OS®, Second Edition
    • Author(s): Zamil Janmohamed, Clara Liu, Drew Bradstock, Raul Chong, Michael Gao, Fraser McArthur, Paul Yip
    • Release date: November 2004
    • Publisher(s): IBM Press
    • ISBN: 0131477005