DB2 Version 9.7 for Linux, UNIX, and Windows

Common SQL API supports the development of portable administrative applications

The common SQL API provides a collection of common-signature and signature-stable stored procedures that are portable across IBM® data servers. You can use these stored procedures to create applications that perform common administrative functions such as getting and setting the values of configuration parameters or getting system and error information.

IBM data servers have many ways to obtain data for administrative purposes and to issue administrative commands. Prior to the introduction of this feature, the variety of methods for performing administrative functions, their different syntaxes, and their security options resulted in tight coupling between tool and data server versions, high implementation complexity on the tools side, and slow integration.

The common SQL API addresses these problems by providing the following benefits:

A single access method: The stored procedures are accessed through SQL.
A simple security model: The stored procedures require only EXECUTE privilege, with no additional dependencies.
The ability to add additional stored procedures in fix packs: The set of stored procedures can be expanded in future fix packs to provide support for performing additional administrative functions.
Data server version independence: The stored procedures provide syntactically identical XML parameters and error handling across all data servers to ensure data server version independence. Signature stability and commonality are achieved by using simple XML documents (with a common DTD) as parameters. Version, platform, and technology differences are expressed through different key value pairs in hierarchical property lists.
The ability for clients to determine supported features: Clients can call the stored procedures to determine the highest supported versions.
Support for automation: You can use the stored procedures in automated scripts.

The common SQL API currently provides the following stored procedures.

Table 1. Common SQL API stored procedures
Procedure name	Description
CANCEL_WORK	Cancels either a specific activity (for example, an SQL statement) or all activities for a connected application
GET_CONFIG	Retrieves data server configuration data, including nodes.cfg file data, database manager configuration data, database configuration data, and DB2® registry settings from all database partitions
GET_MESSAGE	Retrieves the short message text, long message text, and SQLSTATE for an SQLCODE
GET_SYSTEM_INFO	Retrieves information about the data server, including information about the system, the current instance, installed DB2 products, environment variables, and available processors
SET_CONFIG	Updates the configuration parameters retrieved by the GET_CONFIG procedure