Common SQL API stored procedures

Common SQL API stored procedures implement a cross-database and cross-operating system SQL API that is portable across IBM® data servers, including Db2 for Linux®, UNIX, and Windows.

The Common SQL API is a solution-level API that supports common tooling across IBM data servers. This Common SQL API ensures that tooling does not break when a data server is upgraded, and it notifies the caller when an upgrade to tooling is available to capitalize on new data server functionality. Applications that support more than one IBM data server will benefit from using the Common SQL API, as it lowers the complexity of implementation. Such applications typically perform a variety of common administrative functions. For example, you can use these stored procedures to retrieve data server configuration information, return system information about the data server, and return the short message text for an SQLCODE.

The Common SQL API includes the following stored procedures that are supplied with Db2:

These stored procedures use version-stable XML documents as parameters. These XML parameter documents adhere to a single, common document type definition (DTD). This DTD is flexible enough to represent hierarchical structures and binary data. The XML parameter documents can be parsed by using the Apache Commons Configuration component.

Each of the three XML parameter documents has a name and a version, and is typically associated with one stored procedure. The three types of XML parameter documents are:
  • XML input documents
  • XML output documents
  • XML message documents
The XML input document is passed as input to the stored procedure. The XML output document is returned as output, and the XML message document returns messages. If the structure, attributes, or types in an XML parameter document change, the version of the XML parameter document changes. The version of all three of these documents remains in sync when you call a stored procedure. For example, if you call the GET_SYSTEM_INFO stored procedure and specify the major_version parameter as 1 and the minor_version parameter as 1, the XML input, XML output, and XML message documents will be Version 1.1 documents.