Intelligent Enterprises of the 21st Century

What's New in the Second Edition

If you are familiar with IBM Press books, your first question may have appropriately been, "Where was the first edition?" DB2 SQL PL, Second Edition: Essential Guide for DB2 UDB on Linux, UNIX, Windows, i5/OS, and z/OS is actually based on the book DB2 SQL Procedural Language for Linux, UNIX, and Windows (©2002). The change of book title was motivated by the need to create greater awareness of the DB2 SQL Procedural Language by using the term DB2 SQL PL, and deliver value to a broader audience by increasing the scope of the content to cover SQL PL for the entire DB2 familynot just Linux, UNIX, and Windows. Hence, the second edition builds on content from DB2 SQL Procedural Language for Linux, UNIX, and Windows.

Since the first edition, significant enhancements have been delivered in DB2 UDB version 8.2 for LUW (or DB2 UDB version 8.1 with FixPak 7) and have warranted an updated book. Following is a summary of the new features on DB2 UDB 8.2 for LUW:

  • Native support for SQL procedures on Linux, UNIX, and Windows (the C compiler is no longer required)

  • Enhanced SQL for greater application efficiency and performance

  • Enhanced SQL Table function support to allow SQL PL logic as well as UPDATE, INSERT, DELETE, and MERGE statements

  • Ability to call stored procedures from inline SQL PL blocks, functions, and triggers

  • Support for nested save points

  • Session-based locking (SET LOCK WAIT and SET LOCK NO WAIT)

  • A new SQL procedure REOPT option, which causes a procedure to re-optimize with the latest available statistics at runtime

  • Increased length of SQL procedure statements from 64KB to 2MB

  • Ability to change procedure building prepare options using utility procedures SET_ROUTINE_OPTS and GET_ROUTINE_OPTS

  • Enhanced GET ROUTINE and PUT ROUTINE commands for easier deployment

New versions of other DB2 products have also been released. Our previous edition of this book did not discuss these platforms.

Note

This book covers the latest features available in DB2 for iSeries V5R3 and DB2 for zSeries version 8.

New features in DB2 for iSeries V5R3 (as compared to V5R2) include

  • Support for SEQUENCE objects

  • Several new built-in functions for encryption, date/time manipulation, and string manipulation

  • Enhancements to the GET DIAGNOSTICS statement

  • Enhancements to the DECLARE CURSOR statement

New features in DB2 for zSeries version 8 (as compared to version 7):

  • The length of SQL procedure statements have increased from 32KB to 2MB

  • Handling of SQL conditions has been enhanced (for example, the RESIGNAL statement is now supported)

  • The DB2 Development Center Integrated SQL Debugger can be used to debug DB2 for zSeries stored procedures

We updated all the chapters and added new ones to teach you how to leverage these new features of SQL PL.

DB2's SQL Procedural Language (SQL PL)

SQL PL is a subset of the SQL Persistent Stored Modules (SQL/PSM) language standard. The specification of the current SQL/PSM standard can be found in ANSI/ISO/IEC 9075-4:1999 Information Technology, Database Language SQL, Part 4: Persistent Stored Modules (SQL/PSM).

This standard is the basis for the structured programming languages used with SQL to write stored procedures and functions. The standard combines the ease of data access of SQL with the flow control structures of a simple programming language. It gives developers the ability to create compound SQL statements and procedures that only need to be coded once for multiple platforms.

Other Stored Procedure Languages

All the major database vendors support their own versions of an SQL procedural language. Each language supports the same core SQL commands, but each has its own unique implementation. Microsoft's SQL Server and Sybase's procedural languages are quite similar and are called T-SQL (for Transact-SQL). Informix uses Informix SPL (a stored procedure language), which is a 3GL similar to DB2 SQL PL. Oracle's procedural language is called PL/SQL.

A number of references on the Web can guide you in converting your stored procedures from other languages to stored procedures for DB2. A list of these references can be found in Appendix G, "Additional Resources."

SQL PL Development ToolsDB2 Development Center

The DB2 Development Center (see Figure 1-1) is a unified development environment for creating stored procedures using SQL PL for the entire DB2 family. SQL functions can also be written using the Development Center for Linux, UNIX, and Windows platforms.

Figure 1.1. The Development Center.

You can find a tutorial on how to use the Development Center in Appendix D, "Using the DB2 Development Center."

The iSeries Navigator for Windows is a common development tool for the iSeries platform. Appendix A, "Getting Started with DB2," provides a brief introduction to this tool.

Other development tools can also be used with DB2, but are beyond the scope of this book. We still encourage you to explore these other tools:

  • DB2 Universal Database Add-in for Microsoft Visual Studio.NET

  • DB2 Universal Database development plug-in for Eclipse and IBM WebSphere Studio Application Developer (WSAD)

    Related

    Категории