Book Excerpt: DB2 SQL Procedural Language for Linux, UNIX, and Windows

DB2 SQL Procedural Language is an easy-to-learn language that can be used to write stored procedures, triggers, and user-defined functions in DB2. This comprehensive guide, written by experts who have worked with the language from the beginning, is filled with examples and tips to help you get started as quickly as possible.

Drew Bradstock (drewkb@ca.ibm.com), DB2 Consultant, IBM Toronto Lab

Drew Bradstock is a database consultant at the IBM Toronto Lab.



Hana Curtis (hcurtis@ca.ibm.com), Database Consultnat, EMC

Hana Curtis is a database consultant at the IBM Toronto Lab and works with IBM Business Partners to enable their applications to DB2. Prior to 1997, she was a member of the DB2 development team responsible for the data manager component. She holds the following certifications: IBM Certified Solutions Expert - DB2 UDB V7.1 Database Administration for UNIX, Windows, and OS/2; IBM Certified Solutions Expert - DB2 UDB V7.1 Family Application Development; and IBM Certified Specialist - DB2 V7.1 User



Michael Gao (xmgao@ca.ibm.com), DB2 UDB Consultant, IBM Toronto Lab

Michael Gao is a DB2 Consultant from the IBM Toronto Lab where DB2 for distributed platforms is developed. He currently primarily works with IBM business partners in performance tuning, application development, porting and customized education. He has presented at several international conferences and co-authored the book DB2 SQL Procedural Language for Windows, UNIX and Linux. You can reach Michael at xmgao at ca.ibm.com.



Zamil Janmohamed (zamil@ca.ibm.com), DB2 Consultant, IBM Toronto Lab

Zamil Janmohamed is a database consultant at the IBM Toronto Lab.



Clara Liu (claraliu@ca.ibm.com), Database Consultant, IBM Toronto

Clara Liu works as a DB2 UDB consultant at the IBM Toronto Laboratory. As a member of the Data Management Channel Development Team, she works closely with IBM business partners. Clara specializes in database application development and integration of new technologies with DB2. She teaches DB2 UDB certification courses to IBM business partners and at conferences. She co-authored several books which can be found at your favorite online bookstore.



Fraser McArthur (fgmcarth@ca.ibm.com), DB2 Consultant, IBM Toronto

Fraser McArthur is a DB2 Technical Consultant with the Information Management Partner Enablement organization at the IBM Toronto Lab, where he has worked for the last five years. He focuses on assisting IBM Business Partners with performing application migrations and performance tuning.



Paul Yip (ypaul@ca.ibm.com), DB2 Consultant, IBM Toronto Software Lab

Paul Yip is a consultant from the IBM Toronto Labs where DB2 for Linux, UNIX®, and Windows® was developed. His primary work involves helping developers and IBM business partners migrate their existing applications from Oracle and Microsoft® SQL Server to DB2, performance tuning, and providing customized DB2 education. He is a DB2 Certified Solutions Expert for DB2 Administration and Application Development, a Red Hat Linux Certified Engineer (RHCE), and a Certified Java 2 Programmer. Paul is a Linux advocate and enjoys a good round of golf.



11 July 2005 (First published 01 September 2002)

DB2 SQL Procedural Language is an easy-to-learn language that can be used to write stored procedures, triggers, and user-defined functions in DB2. This comprehensive guide, written by experts who have worked with the language from the beginning, is filled with examples and tips to help you get started as quickly as possible.

About this book

SQL Procedural Language book cover

DB2 SQL Procedural Language for Linux, UNIX, and Windows fills the need for a comprehensive guide on writing database applications using SQL Procedural Language (SQL PL). SQL PL is an easy-to-learn language that can be used to write stored procedures, triggers, and user-defined functions in DB2. Written by experts from the IBM Toronto Lab (where DB2 for Linux, UNIX, and Windows is developed) who have worked with the language from the beginning, this book is filled with examples and expert tips to help you get started as quickly as possible.

Table of contents

Chapter 1 - Introduction
Chapter 2 - Basic SQL Procedure Structure
Chapter 3 - Using Flow-of-Control Statements
Chapter 4 - Understanding and Using Cursors
Chapter 5 - Condition Handling
Chapter 6 - Working with Dynamic SQL
Chapter 7 - Working with Nested SQL Procedures
Chapter 8 - Leveraging DB2 Advanced Features
Chapter 9 - Deploying Stored Procedures
Chapter 10 - Working with Triggers and User-Defined Functions
Appendix A - Getting Started with DB2
Appendix B - Setting Up the Build Environment
Appendix C - Using the Development Center
Appendix D - Security Considerations in SQL Stored Procedures
Appendix E - Built-in Function Reference
Appendix F - DDL
Appendix G - Additional Resources
Appendix H - Sample Application Code for Receiving Cursor Result Sets
Appendix I - SQL PL Cross Platform Compatibility

How to order

You can order the book using the following ISBN: 0-13-100772-6

Sample download: Chapter 5. Exception Handling

In this chapter, you will learn:

  • What SQLCODE and SQLSTATE are, and the differences between them.
  • What a condition handler is, when to use it, and how to work with multiple handlers.
  • How to force an application or user-defined error, and how to set the appropriate message tag to go along with it.
  • Various ways of returning errors, and how to check the success and or failure of a stored procedure call.

Related information

Errata

  • p. 61 (bottom) requires clarification:
    For a dynamic compound SQL (and hence, function/triggers as well) use:
        SET v_c1 = (SELECT c1 FROM t1 FETCH FIRST 1 ROW ONLY);

    For SQL Procedures, use

        SELECT c1 INTO v_c1 FROM t1 FETCH FIRST 1 ROW ONLY;
  • p. 77, Figure 5.1:
    IF SUBSTR(SQLSTATE,2) NOT IN ('00','01','02') THEN  -- 2

    should read

    IF SUBSTR(SQLSTATE,1,2) NOT IN ('00','01','02') THEN  -- 2
  • p. 80, Figure 5.3:
    IF SUBSTR(SQLSTATE,2) NOT IN ('00','01','02') THEN  -- 5

    should read

    IF SUBSTR(v_sqlstate_test,1,2) NOT IN ('00','01','02') THEN  -- 5
  • p. 80:
    "The other change made was the test condition at (5), to test for the variable set in the handler as opposed to the actual SQLCODE."

    should read:

    "The other change made was the test condition at (5), to test for the variable set in the handler as opposed to the actual SQLSTATE."

  • p. 101 (middle) format change:
    7. The SIGNAL statement is used to invoke the handler if child rows in the PROJECT table are found.

    Should read:

    7. The SIGNAL statement is used to invoke the handler if child rows in the project table are found.

  • p. 154
    Figure 8.18 Syntax of Declaring a Global Temporary Table
    >>-DECLARE GLOBAL TEMPORARY TABLE--table-name------------------->
     
            .-,---------------------.
            V                       |
    >--+-(----| column-definition |-+--)-----------------------------+-->
       +-LIKE--+-table-name1-+--+------------------+-----------------+
       |       '-view-name---'  '-| copy-options |-'                 |
       '-AS--(--fullselect--)--DEFINITION ONLY--+------------------+-'
                                                '-| copy-options |-'
     
          .-ON COMMIT DELETE ROWS---.
    >--*--+-------------------------+--*---------------------------->
          '-ON COMMIT PRESERVE ROWS-'
     
    >--+-----------------------------------------+------------------>
       |             .-ON ROLLBACK DELETE ROWS-. |
       '-NOT LOGGED--+-------------------------+-'
     
    >--*--+--------------+--*--+---------------------+-------------->
          '-WITH REPLACE-'     '-IN--tablespace-name-'
  • p. 155:
    NOT LOGGED is now an optional clause in DB2 V8. If it is not specified, the global temporary table will be logged.
  • p. 183:
    Section 9.2.2 makes reference to the KEEPDARI configuration parameter. This parameter has been renamed to KEEPFENCED in DB2 V8, and hence should be KEEPFENCED.
  • p. 191:
    SELET * FROM cl_sched should be SELECT * FROM cl_sched
  • p. 203:
    The CREATE TABLE statement should use VARCHAR(12) instead of VARCHAR(20).
  • p. 204:
    "SQL that modifies table data is not supported in SQL PL for triggers and UDFs" should be, "SQL that modifies table data is not supported in SQL PL for UDFs."

Download

DescriptionNameSize
Chapter in PDF formatch05.pdf  ( HTTP | FTP )646 KB
static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=219247
SummaryTitle=Book Excerpt: DB2 SQL Procedural Language for Linux, UNIX, and Windows
publish-date=07112005