Skip to main content

PartnerWorld > Products > Systems, servers, and storage > Technical >

Improving SQL procedure performance

by Kent Milligan, Jarek Miszczyk, Gene Cobb

Last updated: 2009-12-18

PDF iconDownload the white paper (519 KB)
Links to external URLGet Adobe® Reader®

 
Rate this article
  Feedback

© Copyright IBM Corporation, 2008. All Rights Reserved.
All trademarks or registered trademarks mentioned herein are the property of their respective holders.

Abstract
This white paper introduces you to Expression Evaluator, which is a new component of the IBM DB2 for i database that is designed to enhance the performance of SQL stored procedures, functions and triggers. This paper also covers the latest IBM i 6.1 enhancements.
Introduction
IBM® DB2 for i was the first member of the IBM DB2® family that implemented SQL procedural language (SQL PL). The support for SQL stored procedures was first shipped in IBM OS/400® V4R2. Since then, every new release has delivered a number of enhancements and improved functionality; so over the years, the DB2 for i implementation of SQL PL has matured and become a robust programming-language alternative for SQL and IBM i developers.

The SQL PL, which is based on the ISO/ANSI/IEC SQL Persistent Stored Modules (SQL/PSM) specification, allows developers to write routines (user-defined functions, stored procedures and triggers) that combine SQL access with flow-control structures that are typical for a procedural language. This type of procedural-SQL scripting language has proved to be very popular among database programmers. In fact, all major database vendors offer a version of a scripting language with functionality similar to that of DB2 SQL PL. For example, Oracle supports PL/SQL and Microsoft® and Sybase use Transact-SQL. However, DB2 is the only database that implements a standard compliant-scripting language. Other vendors use proprietary dialects, primarily because they supported SQL procedural languages before the SQL/PSM standard was published.

The DB2 for i support for SQL PL has recently been instrumental in several large porting projects in which hundreds of SQL stored procedures, functions and triggers were successfully ported from other database platforms to DB2 for i. For more information on this topic, refer to the DB2 for i porting Web site (ibm.com/servers/enable/site/db2/porting.html). To facilitate these porting efforts, the IBM Rochester development laboratory has shipped a number of significant enhancements that are aimed at improving SQL PL functionality and performance. In this paper, the latest V5R4 and V6R1 enhancements are highlighted and nontrivial programming techniques are covered - to help increase your SQL PL expertise and improve performance of your SQL procedural objects.