Employ the DB2 for z/OS common SQL API in your tooling applications

Use Java technology to interface with the DB2 common SQL API stored procedures

Get the details on how to use the common SQL API (CSA), a set of stored procedures that exist across all IBM® data servers. Learn to employ the common SQL API and integrate it in an application. Explore a small J2EE Web application, based on the common SQL API, that compares the subsystem parameters of two IBM DB2® for z/OS® subsystems by employing the GET_CONFIG CSA stored procedure.

Knut Stolze, Ph.D. (stolze@de.ibm.com), Data Warehousing for DB2 z/OS, IBM

Knut Stolze photoKnut Stolze started his work with DB2 when he joined IBM as a visiting scientist at the Silicon Valley Lab, USA, in 1999 where he worked on the DB2 Image Extender. He moved on to the DB2 Spatial Extender development team and was responsible for several enhancements to improve usability, performance, and standard-conformance of the Extender. From 2002 through 2006 he was a PhD student and teaching assistant at the University of Jena, Germany. At the same time he continued his work for IBM in the area of the Information Integrator development. Today, Knut Stolze is a member of the development team responsible for DB2 z/OS Utilities and Data Warehousing on DB2 for z/OS. He can be reached through the newsgroups comp.databases.ibm-db2 and ibm.software.db2.udb.spatial or at stolze@de.ibm.com.

Marcel Kutsch (kutschm@de.ibm.com), DB2 for z/OS Development, IBM Germany Research and Development

Marcel Kutsch photoMarcel Kutsch is a software engineer at the IBM Boeblingen Laboratory in Germany. He is currently working on the design and development of stored procedures and the administrative task scheduler component of DB2 for z/OS. Prior to joining IBM, Marcel worked for two internships at the IBM Almaden Research Center, focusing on DB2's query optimization for DB2 for Linux, UNIX, and Windows. Marcel's first assignment at IBM, after graduating from the university in 2005, was on DB2 autonomic features.

31 July 2008

Also available in Russian


IBM database management systems such as DB2 for z/OS, IBM DB2 for Linux®, UNIX®, and Windows®, and IBM Informix® Dynamic Server (IDS) come with a vast number and variety of administrative functions and routines. While these routines make administrative data server information easily accessible for remote database applications and tools, at the same time, they tightly couple the application with the connected data server. This is mainly because of differences in syntax, security options, as well as access methods for the different data servers, and sometimes even between different data server versions. As a result, applications and tools suffer from higher implementation complexity, slow integration, and reuse characteristics. This article presents multiple techniques to work with the common SQL API procedures, a set of stored procedures that exist across all IBM data servers and tackle the above-mentioned challenges.

Introduction to the common SQL API

The common SQL API is a set of routines, each featuring the same signature across all IBM data servers. By employing an XML document as input and output parameters, these routines are furthermore signature-stable across different versions. Since the same invocation method and security considerations can be employed for all IBM data servers and even for several data server versions, application code invoking these routines does not have to be aware of the specific data server version. Instead, code complexity is reduced and applications can be integrated much easier.

On DB2 for z/OS, Versions 8 and 9, the common SQL API currently comprises of three stored procedures that basically collect data server configuration information as well as data for resource exploitation.

Table 1. Common SQL API stored procedures
Stored procedure nameFunctionality
SYSPROC.GET_MESSAGEReturns the short message text associated with a provided SQLCODE
SYSPROC.GET_SYSTEM_INFOReturns the following system configuration information:
  • Operating system information
    • Name and release
    • CPU model, number of online processors, processor identifier, and the serial number of the online processors
    • Real storage size
  • Product information
    • Primary JES name, release, node name, and held output class
    • Security software name and FMID
    • DFSMS release
    • TSO release
    • VTAM release
  • DB2 MEPL
  • Apply status of a SYSMOD
  • WLM classification rules
SYSPROC.GET_CONFIGReturns the following data server configuration information:
  • Data sharing group information
    • Group name, level, mode, protocol level, attach name
    • DB2 member, subsystem ID, command prefix, status, level
    • z/OS system name, IRLM subsystem, IRLM procedure
    • SCA structure size, status, utilization
    • LOCK1 structure size, number of entries
    • Number of list entries, utilization
  • DB2 subsystem parameters
    • Control block name
    • Install panel name
    • Install panel field name
    • Location on install panel
    • Subsystem parameter name
    • Subsystem parameter value
  • Data distribution facility information
    • DDF status
    • Location name, Lu-name, Generic Lu-name
    • IP-address, TCP/IP Port, Resynchronization port
    • SQL domain, Resynchronization domain
  • Connected DB2 subsystem
  • Active resource limit facility table names
  • Active log data set names and volumes
  • Timestamp of last DB2 restart

Each of the above-listed stored procedures collects the associated information, wraps it into an XML document, and returns it as a BLOB output parameter to the requesting application or tool. The XML document adheres to a common parameter list document type definition (DTD), which originates in Apple's Mac OS X (see Resources). By using such a hierarchical XML property list, platform, technology, as well as version differences are expressed by the means of XML key value pairs and do not have to be expressed by the stored procedure signature. For detailed information on the common SQL API signature, functionality, and the XML property list structure, please refer to chapter 24 of the IBM Redbooks publication "DB2 9 for z/OS Stored Procedures: Through the CALL and Beyond" (see Resources).

Online DB2 z/OS subsystem parameter compare tool

The remainder of this article discusses a J2EE Web application that basically compares and illustrates the DB2 subsystem parameters of two DB2 for z/OS subsystems (not running in data sharing mode). The Web application employs the SYSPROC.GET_CONFIG common SQL API stored procedure, parses the returned XML output document for the subsystem parameter section in a Java servlet, and outputs the name and the value of the subsystem parameter in a JSP. Figure 1 illustrates the main panel of the Web application. The subsystem parameter name is contained in the first column, whereas the actual values for the subsystems V81A and V81B are listed in columns two and three, respectively. The rows are highlighted in different colors to indicate matching parameter values for both subsystems (green rows), different parameter values (red rows), as well as parameter values that cannot be compared since the parameter is not set on one of the subsystems (grey rows).

Figure 1. Subsystem parameter compare tool output main panel
Subsystem parameter compare tool output main panel

The subsystem parameter values on the main panel of the Web application are hyperlinks. You can click on the values, and the application will drill down on the respective subsystem parameter and display more detailed information on a new page. Such a subsystem parameter drill-down is illustrated in Figure 2:

Figure 2. Drill down on single subsystem parameters
Drill down on single subsystem parameters

After having introduced the major functionality of the online DB2 z/OS subsystem parameter compare tool, let's now take a look at the actual implementation and focus on the processing of the XML output document returned by the GET_CONFIG stored procedure. The complete source code of the comparison tool is available in the Download section. It's about 1000 lines of code in total, and it also includes further details on items like the JSPs that are not fully described subsequently.


The Web application is based on J2EE technology and employs a classical Model-View-Controller (MVC) design pattern. The initial HTTP GET request http://localhost:8080/ZpCompServlet?action=GETSUBSYSTEM is processed by a Java servlet, which serves as the Controller part, as it controls the application program flow based on the provided action parameter. The servlet then instantiates an object of the Model, which is basically a collection of all subsystem parameters on a single DB2 subsystem, and inserts the object reference into an HTTP session object. Once the Model is set up and initialized, the Controller redirects the HTTP request to the View component that accesses the Model object and displays the subsystem parameters in a Java Server Page (JSP).

Listing 1 shows an excerpt of the Controller servlet. As it is common practice, we override the init() method, as well as the doGet method to react upon HTTP GET requests. The servlet then tries to obtain an HTTP session object, which is used later to store the instance of the Model part and make it accessible to the View component.

Listing 1. Controller servlet — Part 1
public class ZpCompServlet extends HttpServlet {

  private final String userID = "USERID";
  private final String password = "PASSWORD";

  /* Override init() method */
  public void init(ServletConfig conf) throws ServletException

  /* Controller servlet */
  public void doGet(HttpServletRequest req, HttpServletResponse res)
                    throws ServletException, IOException {

  /* Obtain a new session */
  HttpSession session = req.getSession(true);
  PrintWriter out = res.getWriter();
  String action = req.getParameter("action");
  if (action.equals("GETSUBSYSTEM")) {
    /* create 2 worker subthreads */
    Thread[] threadpool = new Thread[2];
    SPWrapper[] spw = new SPWrapper[2];
    for (int i = 0; i < 2; i++) {
      spw[i] = new SPWrapper();
      /* Establish a connection */
      if (i % 2 == 0) {
         spw[i].establishCon("hostname:port/location-name ", userID, password);
         spw[i].establishCon("hostname:port/location-name", userID, password);

      /* Init the parameters for a CSA call */
      spw[i].set_major(1);         // MAJOR_VERSION
      spw[i].set_minor(0);         // MINOR_VERSION
      spw[i].set_local("en_US");   // REQUESTED_LOCAL
      spw[i].set_xml_input(null);  // XML_INPUT
      spw[i].set_xml_filter(null); // XML_FILTER

      threadpool[i] = new Thread(spw[i]);

Calling the stored procedure

The Web application calls the common SQL API stored procedure SYSPROC.GET_CONFIG on two DB2 subsystems. The application issues these two calls in parallel (in other words, you create two threads, each of which connect to one of the DB2 subsystems). Each thread then initializes the GET_CONFIG input parameters. We intend to call GET_CONFIG in Version 1.0, where 1 is currently the highest major and 0 the highest minor version supported. Furthermore, request a locale of en_US. In Version 1.0, GET_CONFIG does not support an XML input document. Therefore, set this input parameter to null. As we do not want to filter for a single key value, we also do not provide an XML filter input parameter. By using these settings, the stored procedure will construct a complete XML output document that contains a section listing all DB2 subsystem parameters. Please note that we use a setter method to specify the stored procedure name (in other words, GET_CONFIG) since the same setting of parameters can be used to call the other common SQL API stored procedures (in other words, SYSPROC.GET_SYSTEM_INFO).

The class SPWrapper implements the runnable interface of the thread object. Here, you create a JDBC connection to a DB2 z/OS data server. In detail, the establishCon function uses the JCC driver, com.ibm.db2.jcc.DB2Driver, to establish a JDBC Type 4 connection to the data server URL jdbc:db2://hostname:port/location-name, as illustrated in Listing 2:

Listing 2. Establish JDBC connection (SPWrapper)
public void establishCon(String url, String userID, String password) {
  try {
    con = DriverManager.getConnection("jdbc:db2://" + url, userID, password);
  catch (ClassNotFoundException cnfe) {
  catch (SQLException sqle) {
    System.out.println("Error during CALL " + " SQLSTATE = " + sqle.getSQLState() +
            " SQLCODE = "  + sqle.getErrorCode() + " : " + sqle.getMessage());

The threads created in the controller servlet eventually call the stored procedure. In the function callCSASP, you first prepare the stored procedure CALL statement and then bind the input and output parameters to the respective parameter markers. All XML parameters, for example, XML_INPUT and XML_OUTPUT, are of type BLOB. Therefore, you cannot simply bind-in string representations on the XML_FILTER parameter marker, for example. First, you have to write it into a byte array. When dealing with the BLOB parameter, there is no need for the calling application to do any codepage conversion (for example, EBCDIC - UTF8, or UTF8 - EBCDIC for input parameters). The stored procedure expects the XML input documents in the BLOB input parameters to be in UTF-8 and also creates the XML output documents in UTF-8. As mentioned above, the same routine can be employed to call any other common SQL API stored procedure.Listing 3 shows how you call for the common SQL API stored procedure. The evaluation of the returned parameters is abbreviated, and you should refer to the source code available in the Download section for the complete specifics.

Listing 3. Setup for the procedure call (SPWrapper)
public void callCSASP() {
  try {
    // Prepare stored procedure CALL
    cstmt = con.prepareCall("CALL SYSPROC." + spName + "(?,?,?,?,?,?,?)");

    // Register input parameter
    cstmt.setInt(1, major_version);       // Major Version
    cstmt.setInt(2, minor_version);       // Minor Version
    cstmt.setString(3, requested_local);  // Requested Locale
    cstmt.setObject(4, xml_input, Types.BLOB);
    cstmt.setObject(5, xml_filter, Types.BLOB);

    // Output Parms
    cstmt.registerOutParameter(1, Types.INTEGER); // Major Version
    cstmt.registerOutParameter(2, Types.INTEGER); // Minor Version
    cstmt.registerOutParameter(6, Types.BLOB);    // XML_OUTPUT
    cstmt.registerOutParameter(7, Types.BLOB);    // XML_MESSAGE

    // Execute statement and commit

    ctstmt_warning = cstmt.getWarnings();
    if (ctstmt_warning != null) {
      System.out.println("SQL Warning: " + ctstmt_warning.getMessage());
    System.out.println("Major Version returned " + cstmt.getInt(1) );
    System.out.println("Minor Version returned " + cstmt.getInt(2) );


The XML_OUTPUT and XML_MESSAGE output parameters are mutually exclusive. This implies that in case the stored procedure processing was successful, the XML output document contains all the data server configuration information, and no XML message document is returned from the procedure. Here, you retrieve the returned XML_OUTPUT BLOB and continue processing on this object. In case the procedure encountered an internal error, the XML message document provides details on the error scenario. However, the XML_OUTPUT parameter is then not returned. When such an error case occurs, you simply materialize the XML_MESSAGE BLOB in a file on the application server.

Parsing the XML output document

After you retrieve the XML output documents for the two DB2 subsystems, you instantiate and populate the Model part of the MVC Web application. The Model is represented by the class SubsystemConfig, which essentially encapsulates the configuration of a single DB2 subsystem (in other words, it contains the associated subsystem identifier as well as the collection of subsystem parameters). Usually Java Beans are employed to represent the Model; for simplicity reasons, we just created an ordinary Java class. As soon as the subsystem configurations of the two DB2 systems is stored in two instances of the Model, the Controller servlet stores these two instances in the HTTP session, such that the View can access them later on.

To parse the XML documents for the subsystem parameter section, we employ the Java XPath extension implemented in the packages javax.xml.xpath. This requires the creation of a DOM from the XML document, upon which a compiled XPath can be applied. Listing 4 shows you how to create a DOM object from the BLOB InputStream object:

Listing 4. Creating a DOM from the XML_OUTPUT BLOB (SPWrapper)

  InputStream instream = (InputStream)b_out.getBinaryStream();


public void processXMLOutput(InputStream inStream) {
  DocumentBuilderFactory domFactory = DocumentBuilderFactory.newInstance();
  DocumentBuilder domBuilder;
  try {
    domBuilder = domFactory.newDocumentBuilder();
    Document document = domBuilder.parse(inStream);


Listing 5 abstracts the XML output document produced by Version 1.0 of the GET_CONFIG stored procedure to provide a better understanding of the employed XPaths. The elements <dict>...</dict> usually contain further information, and are just displayed in a folded manner to provide a more condensed overview about the XML structure.

Listing 5. XML output structure (GET_CONFIG)
<plist version="1.0">
    <key>Document Type Name</key><string>Data Server Configuration Output</string>
    <key>Document Type Major Version</key><integer>1</integer>
    <key>Document Type Minor Version</key><integer>0</integer>
    <key>Data Server Product Name</key><string>DSN</string>
    <key>Data Server Product Version</key><string>8.1.5</string>
    <key>Data Server Major Version</key><integer>8</integer>
    <key>Data Server Minor Version</key><integer>1</integer>
    <key>Data Server Platform</key><string>z/OS</string>
    <key>Document Locale</key><string>en_US</string>
    <key>Common Data Sharing Group Information</key>
    <key>DB2 Subsystem Specific Information</key>
        <key>Display Name</key><string>DB2 Subsystem Specific Information</string>
          <key>Display Name</key><string>V81A</string>
          <key>DB2 Subsystem Status Information</key>
          <key>DB2 Subsystem Parameters</key>
          <key>DB2 Distributed Access Information</key>
          <key>Active Log Data Set Information</key>
          <key>Time of Last DB2 Restart</key>
          <key>Resource Limit Facility Information</key>
          <key>Connected DB2 Subsystem</key>

The first step seeks to extract the DB2 subsystem ID from the XML output document. The XPath employed in Listing 6 returns a NodeSet from the XML document, where each node contains a DB2 subsystem ID (for example, V81A). Create a Java XPath object by making use of an XPathFactory. Then compile the XPath and apply it on your XML-DOM object. For every subsystem ID node returned by the XPath, instantiate a new Model SubsystemConfig object, as can be seen in Listing 6:

Listing 6. Apply subsystemID XPath (GET_CONFIG)
  XPathFactory xpathFactory = XPathFactory.newInstance();
  XPath xpath = xpathFactory.newXPath();
  XPathExpression subsystemParameter;

  // Apply XPath on the DOM object
  subsystemParameter = xpath.compile("//string[text() =
          'DB2 Subsystem Specific Information']/following-sibling::key[1]");
  Object result = subsystemParameter.evaluate(document, XPathConstants.NODESET);

  NodeList nodes = (NodeList) result;
  for (int i = 0; i < nodes.getLength(); i++) {
    String subsystemID = (String)nodes.item(i).getTextContent();
    SubsystemConfig subsystem = new SubsystemConfig(subsystemID, nodes.item(i));

With the extracted DB2 subsystem ID, you start populating the Model object and use the subsystem ID node as a new context node for the following XPaths that extract the subsystem parameters information. The XPaths to access the specific information sections of the subsystem parameters (Name or Value, for example) are stored in the Model itself (the SubsystemConfig class, in other words). The first SubsystemConfig XPath that you apply returns a NodeSet pointing to all subsystem parameters names. You then iterate over this set where the current subsystem parameter name node serves as the new context node to apply additional relative XPaths in order to extract further information. Listing 7 shows the XML fragment being processed. The markers on the right side are referenced in the first column in Table 2, which provides an overview of the subsystem parameter section in the GET_CONFIG XML output document. The second column in the table contains XPaths that are relative to a respective context node. The context node hierarchy is indicated by the numbering -- in other words, (1.1) is a relative XPath for the current context node (1), and so on.

Listing 7. XML fragment returned by GET_CONFIG
<key>V81A</key>                                        -- (1)
  <key>Display Name</key><string>V81A</string>
  <key>DB2 Subsystem Status Information</key>
  <key>DB2 Subsystem Parameters</key>
    <key>Display Name</key>
    <string>DB2 Subsystem Parameters</string>
    <key>DSNHDECP</key>                                -- (1.1.1)
      <key>Display Name</key><
      <key>AGCCSIB</key>                               -- (1.1)
        <key>Display Name</key><
        <key>Installation Panel Name</key><
          <key>Display Name</key><
          <string>Installation Panel Name</string>
          <string>DSNTIPF</string>                     -- (1.1.3)
        <key>Installation Panel Field Name</key>
          <key>Display Name</key><
          <string>Installation Panel Field Name</string>
          <string>ASCII CCSID</string>                 -- (1.1.4)
        <key>Location on Installation Panel</key><
          <key>Display Name</key><
          <string>Location on Installation Panel</string>
          <string>8</string>                           -- (1.1.5)
        <key>Subsystem Parameter Value</key><
          <key>Display Name</key><
          <string>Subsystem Parameter Value</string>
          <string>65534</string>                       -- (1.1.2)
    ... Further subsystem Parameters in the same control block
    ... further subsystem parameter control blocks
Table 2. Subsystem parameter section (GET_CONFIG)
XML structureXPaths
(1)Initial context node
(1.1.1)Control block name for current subsystem parameter: ../preceding-sibling::string[1]
(1.1)New context node, NodeSet for all subsystem parameter names: ./following-sibling::dict[1]//string[text() = 'DB2 Subsystem Parameters']/following-sibling::dict/dict/string[1]
(1.1.3)Install panel name: ./following-sibling::key[text() = 'Installation Panel Name']/following-sibling::dict[1]/key[text() = 'Value']/following-sibling::string[1]
(1.1.4)Location on install panel: ./following-sibling::key[text() = 'Location on Installation Panel']/following-sibling::dict[1]/key[text() = 'Value']/following-sibling::string[1]
(1.1.5)Install panel field name: ./following-sibling::key[text() = 'Installation Panel Field Name']/following-sibling::dict[1]/key[text() = 'Value']/following-sibling::string[1]
(1.1.2)Subsystem parameter value: ./following-sibling::key[text() = 'Subsystem Parameter Value']/following-sibling::dict[1]/key[text() = 'Value']/following-sibling::string[1]

Similar to the DB2 subsystem ID, the SubsystemConfig class obtains an XPath object from an XPathFactory and compiles the respective XPaths. As mentioned above, the first XPath applied returns a NodeSet over all subsystem parameter names. The class then iterates over all objects, uses them as a new context node, and applies the remaining relative XPaths. The respective Java code is illustrated in Listing 8. As soon as all information related to a single subsystem parameter are extracted, a new object of class ZParm is instantiated, which contains these values as properties. The class object is stored in a sorted TreeMap object zparms where the subsystem parameter name is chosen as the lookup and sort key. You now can easily get a sorted list of subsystem parameter names, and, furthermore, you can conveniently access additional parameter information by simply doing a lookup on the mapped ZParm object.

Listing 8. Extracting subsystem parameter information (SubsystemConfig class)
  private String                  subsystemID = null;
  private TreeMap<String, ZParm>  zparms      = null;
  private Node                    contextNode = null;

  /* XPath to subsystem parameter name */
  private final String xpathZPName = "./following-sibling::dict[1]//string[text() = " +
          "'DB2 Subsystem Parameters']/following-sibling::dict/dict/string[1]";
  /* XPath to control block name */
  private final String xpathCBN = "../preceding-sibling::string[1]";
  /* XPath to subsystem parameter value */
  private final String xpathZPValue = "./following-sibling::key[text() = " +
          "'Subsystem Parameter Value']/following-sibling::dict[1]/" +
          "key[text() = 'Value']/following-sibling::string[1]";


  XPathFactory xpathFactory = XPathFactory.newInstance();
  XPath xpath = xpathFactory.newXPath();
  XPathExpression subsystemParameter = xpath.compile(xpathZPName);
  NodeList zParmNodes = (NodeList)subsystemParameter.evaluate(
          contextNode, XPathConstants.NODESET);
  for (int i = 0; i < zParmNodes.getLength(); i++) {
    currentzParmNode = zParmNodes.item(i);
    zParmName = (String)currentzParmNode.getTextContent();

    controlBlockName = extractInformation(currentzParmNode, xpathCBN).getTextContent();
    zParmValue = extractInformation(currentzParmNode, xpathZPValue).getTextContent();

    /* Create zParm object */
    zparms.put(zParmName, new ZParm(zParmName, controlBlockName, installPanelName,
            locationOnInstallPanel, installPanelFieldName, zParmValue));


private Node extractInformation(Node contextNode, String xpathString) {
  Node node = null;
  try {
    XPathFactory xpathFactory = XPathFactory.newInstance();
    XPath xpath = xpathFactory.newXPath();
    XPathExpression subsystemParameter = xpath.compile(xpathString);
    node = (Node)subsystemParameter.evaluate(contextNode, XPathConstants.NODE);
  } catch (XPathExpressionException e) {
  return node;

As soon as the servlet threads finish the subsystem parameter information gathering work, the two Model objects are stored in the session object with a fixed lookup name: zParmComp.DSGroup1, zParmComp.DSGroup2. The servlet then redirects the original HTTP request to a Java Server Page (JSP), which serves as the View part of the Web application.

Listing 9. Redirecting to View (ZpCompServlet)
  /* Get the Data Sharing group ArrayList object */
  ArrayList dsGroup1 = spw[0].get_ds_group();
  ArrayList dsGroup2 = spw[1].get_ds_group();

  /* Forward object to View JSP */
  session.setAttribute("zParmComp.DSGroup1", dsGroup1);
  session.setAttribute("zParmComp.DSGroup2", dsGroup2);
  String jspURL="/DisplayConfig.jsp";
  ServletContext sc = getServletContext();
  RequestDispatcher rd = sc.getRequestDispatcher(jspURL);
  rd.forward(req, res);

After the servlet redirects the HTTP request to the View, the DisplayConfig JSP is processed. This JSP first accesses the Model objects in the HTTP session by performing a lookup with the help of the shared, constant string values: zParmComp.DSGroup1, zParmComp.DSGroup2. The JSP then accesses the two TreeMap zparms objects and obtains two keySets. It basically iterates over these keySets, and in case both keys match (the subsystem parameter names, in other words), the JSP compares the parameter values and output them. Otherwise the smaller key name/value combination is displayed, and the next element in the sorted list is retrieved. We use some color encoding to visualize possible inconsistencies in the two subsystems.

The subsystem parameter values are displayed as hyperlinks. Once a user clicks on a parameter value, the link issues an HTTP request to the ZpCompServlet with an action parameter of GETZPARM and two additional attributes that determine the subsystemID as well as the parameter name. For example:


The servlet then checks the two session objects to see if they represent the requested DB2 subsystemID and extracts the respective ZParm object from the TreeMap. This object is then stored in the HTTP session.

Finally, the servlet again redirects the original HTTP request, to another JSP View, called DisplayZParm. This JSP extracts the ZParm object from the session and creates an HTTP response containing the objects information.

This article does not illustrate the details on the JSP implementation because they are just a means to demonstrate the capabilities of the information provided by the GET_CONFIG stored procedure. The full source code implementing the described functionality can be found in the Download section.

Jakarta Commons Configuration

Instead of employing the XPath approach, as described in the section for the Web application, there exists a set of classes that make the XML PList document returned by GET_CONFIG very easily accessible from your application. This PList API is provided by the Jakarta Commons Configuration (see Resources section). It contains an XMLPropertyListConfiguration implementation that allows for extracting certain key values from the XML document, as well as constructing new and changing existing XML PList documents. The following Listing 10 shows how to instantiate an XMLPropertyListConfiguration from a GET_CONFIG XML output document materialized in a file. The code then extracts the value of the key Document Type Name (in other words, Data Server Configuration Output) along with the name and version of the data server. You require the following Jakarta Commons Configuration JARs to run the code:

  • commons-lang
  • commons-logging
  • commons-collections
  • commons-digester
  • commons-beanutils
  • commons-codec
  • commons-configuration
Listing 10. Using Jakarta Commons Configuration
  XMLPropertyListConfiguration config = 
            new XMLPropertyListConfiguration("xml_output_gc.xml");
  System.out.println(config.getProperty("Document Type Name"));
  System.out.println(config.getProperty("Data Server Product Name"));
  System.out.println(config.getProperty("Data Server Product Version"));

The constructor of the XMLPropertyListConfiguration tries to validate the provided XML document against a PList DTD. In the current implementation Version 1.5, you cannot explicitly tell the constructor to ignore the validation. The Common SQL API stored procedures, do not include a <!DOCTYPE> tag that would specify the DTD against the validation should occur. Therefore, please be aware that the constructor will currently throw a configuration exception; however, you can continue to use the API to process the document.

For a comprehensive example that employs the Jakarta Commons Configuration, please refer to Appendix A in the IBM Redbooks publication "DB2 9 for z/OS Stored Procedures: Through the CALL and Beyond" (see Resources).


This article provided an introduction to the Common SQL API, which is available for all major IBM data servers like DB2 for z/OS or DB2 LUW. The GET_CONFIG stored procedure — as part of the API — was exploited in a simple zParm comparison tool for different DB2 subsystems. The provided sample code can be used as a baseline to build more complex and flexible applications. For example, you can parameterize the application to select the desired DB2 subsystems dynamically at runtime, or you can incorporate the GET_SYSTEM_INFO procedure to extend the functionality to operating system-specific information.


Source code for the sample applicationCSA-sample.zip13KB



Get products and technologies

  • Build your next development project with IBM trial software, available for download directly from developerWorks.



developerWorks: Sign in

Required fields are indicated with an asterisk (*).

Need an IBM ID?
Forgot your IBM ID?

Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name

The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.


All information submitted is secure.

Dig deeper into Information management on developerWorks

Zone=Information Management, Java technology, Web development, XML
ArticleTitle=Employ the DB2 for z/OS common SQL API in your tooling applications