Articles & Content


Native SQL Procedures in DB2 9 for z/OS (A08)

Topic: DB2 for z/OS

Subtopic: 2007

DATE: 2007-11-6 (16:45 - 17:45)
SPEAKERS: Peggy Zagelow (IBM Silicon Valley Lab)

A new variation of SQL procedures, native SQL procedures, are introduced in DB2 9 for z/OS. Richer support of the SQL Procedural Language, and attention to the application development lifecycle, offer improved usability and portability. These extensions (including versioning and deployment capabilities) based on new technology can be a favorable alternative to the existing compilation of SQL Procedural Language procedures into an external C language implementation. Since native SQL procedures are managed by DB2 and run within the DB2 engine, there are also significant benefits in terms of improved performance, security, and reduced cost of ownership.

EXP. LEVEL: Beginner


Native SQL procedures have enhanced support for the SQL Procedural Language, including the nested compound statements and more new data types. You can write complex SQL procedures with ease and the SQL procedures are more portable.

Native SQL procedures have been designed with the view of the application development lifecycle in mind. You can create a version of an SQL procedure, debug it, replace it or add a new version of the procedure, and finally deploy it into production.

DB2 manages the various aspects of the application development lifecycle in a consistent and integrated manner providing enhanced security, including the source code for the native SQL procedures.

Native SQL procedures will be executed entirely in the DB2 engine, whereas external SQL procedures are executed in the WLM environment. The native SQL procedures are expected to outperform typical external SQL procedures.

You will not need a C or C++ compiler to create native SQL procedures. The multiple steps of setup and level of complexity in the build process that are required by an external SQL procedure, has been simplified for a native SQL procedure.

Click Here to Download

NOTE: These are only open to members of IDUG. If you are not a member, please CLICK HERE for more information.