Application performance can be improved with these enhancements to the stored procedure capabilities of DB2 Universal Database.
Stored Procedure Builder has been enhanced to simplify the process of building your stored procedures and also to make it easier to debug and deploy them in your enterprise:
For more information on the Stored Procedure Builder see the Online Help.
DB2 Version 7.2 provides a basic set of functions for integrating MQSeries messaging operations within SQL statements. These functions support a variety of messaging models, including simple, one-way messages, request/reply, and publish/subscribe. Using these basic functions, it is possible to support a wide range of applications, ranging from simple event notification to operational data store creation.
More information is available in the DB2 7.2 Release Notes and at www.software.ibm.com/software/data/integration/MQSeries. Additional support for semi-structured messages is provided by the MQSeries Assist wizard (see MQSeries Assist Wizard for more information). Additional support for XML messages is provided by new functionality in the DB2 XML Extender (see http://www.ibm.com/software/data/db2/extenders/xmlext/ ).
The new DB2 Version 7.2 Data Warehouse Center also provides MQSeries Integration. Further details on this new functionality may be found at MQSeries Message Queues.
A copy of MQSeries is now provided to support the use of these exciting new capabilities on Windows 2000, Windows NT, AIX, HP-UX, and the Solaris Operating Environment.
DB2 Version 7.2 provides a new MQSeries Assist wizard. This wizard creates a table function that reads from an MQSeries queue using the MQSeries user-defined functions, which are also new in Version 7.2.
The wizard can treat each MQSeries message as a delimited string or a fixed length column string depending on your specification. The created table function parses the string according to your specifications, and returns each MQSeries message as a row of the table function. The wizard also allows you to create a view on top of the table function and to preview an MQSeries message and the table function result.
DB2 Version 7.2 provides a new OLE DB Assist wizard. This wizard helps you to create a table function that reads data from another database provider that supports the Microsoft OLE DB standard. You can optionally create a DB2 table with the data that is read by the OLE DB table function, and you can create a view for the OLE DB table function.
For more information on the OLE DB Assist wizard, see the DB2 Release Notes.
DB2 now delivers nested stored procedure support that not only simplifies the migration of applications from other database vendors, but also allows application developers to use and exploit stored procedures in a more effective way. See the Application Development Guide for more information.
SQL Procedure Language support is now available for DB2 on Windows, OS/2, and UNIX, in addition to DB2 Universal Database for OS/390 and DB2 Universal Database for OS/400. It supports building DB2 stored procedures that employ SQL in a manner consistent with the Persistent Stored Module definition of the ANSI SQL99 standard. See the Application Development Guide and the Application Building Guide for more information.
In addition to Java and the stored procedure language, DB2 Universal Database now enables you to write stored procedures using Microsoft Visual Basic. Stored procedures, written using Microsoft Visual Basic, can be deployed on DB2 Universal Database servers running on Windows 32-bit operating systems. You can specify OLE as the language in which the stored procedure is implemented and call it from a client application written in any of the languages supported by DB2. See Native OLE DB Support for more information on DB2 OLE support.
Stored procedures can now be called from the command line processor (CLP), using the CALL command. See the SQL Reference, Volume 1 for more information.
An enhanced set of stored procedure samples is available with the Application Development Client. The samples are written using embedded SQL in C/C++, CLI in C, JDBC in Java, and SQL procedures. They demonstrate the capability of client applications in one language to call a stored procedure written in another language. Separate source files for CREATE PROCEDURE statements, client applications, stored procedures, and DROP PROCEDURE statements make it easy to copy and modify the sample code to create your own stored procedures.
With the Java class loader, DB2 Universal Database enables you to improve the performance of your Java stored procedures and increase the availability of your production servers. The Java class loader enables you to perform online replacement of your Java stored procedure classes. When you issue the CALL SQLJ.REFRESH_CLASSES() statement, DB2 continues to run, and the Java Virtual Machine (JVM) remains loaded in memory, ready to execute the refreshed stored procedure classes. Stored procedures can use the new classes without the need to stop the database manager. See the Application Development Guide for more information.