Build a pureXML application in DB2 for z/OS, Part 3: Develop stored procedures with Rational Developer for System z

In this tutorial, the third installment in a series, learn how to use IBM® Rational® Developer for System z® to develop COBOL stored procedures that manipulate XML data. This tutorial illustrates the XML schema support offered, and provides step-by-step instructions for creating and testing stored procedures.

Kirsten A. Larsen (kila@dk.ibm.com), Senior IT specialist, IBM

Kirsten A. LarsenKirsten Ann Larsen is a senior IT specialist and technical lead with IT Delivery in IBM Nordics. She has more than 10 years of experience with DB2 for z/OS and has co-authored a Redbook on DB2 security. She has worked with XML since pureXML support was included with the release of DB2 9 in 2007.



Susan Malaika (malaika@us.ibm.com), Senior Technical Staff Member, IBM

Susan Malaika photoSusan Malaika works in IBM's Information Management Group. She specializes in XML and Web technologies, including Grid computing. She has published articles and co-edited a book on the Web. She is a member of the IBM Academy of Technology.



11 June 2009

Also available in Chinese

Before you start

About this series

With the popularity of SOA and Web-based technologies, there are increasing amounts of XML data coming in and out of computer systems. The IBM DB2® 9 for z/OS® support for pureXML® makes it much simpler to store and manipulate XML on System z® through SQL/XML interfaces. This tutorial is the third installment in a series on how to get started with XML in DB2 for z/OS. The series provides different approaches for creating simple applications that manipulate XML data. The first installment presents an example application based on COBOL stored procedures that can be downloaded. The second installment shows how to set up Web access through Universal Services.

About this tutorial

This tutorial describes how to use Rational Developer for System z to develop COBOL stored procedures that manipulate XML data. This tutorial shows the XML schema support offered, and provides step-by-step instructions for creating and testing stored procedures.

Objectives

The objective of this tutorial is to demonstrate the ease with which COBOL applications for handling pureXML in DB2 can be developed using Rational Developer for System z (RDz).

Prerequisites

This tutorial is written for COBOL developers whose skills and experience are at an introductory to intermediate level. You should have a general familiarity with XML and a working knowledge of DB2 for z/OS.

System requirements

You need to have the following software installed on z/OS:

  • DB2 9 for z/OS
  • IBM Enterprise COBOL for z/OS 4.1.0
  • Rational Developer for System z, Version 7.5 server

You need to have the following software installed on your laptop:

  • Rational Developer for System z, Version 7.5 client

This tutorial assumes that you have already configured RDz on both the server and client sides, that you have created a connection to z/OS, and that you have the authorizations required to perform the necessary tasks. see the Resources section for links to trial download and documentation.


What is Rational Developer for System z?

Rational Developer for System z (RDz) is an Eclipse-based integrated development environment that provides support for the development of a multitude of different applications for z/OS.

It consists of a server part that is installed on z/OS and a client part that is installed on your laptop and configured to communicate with z/OS.

This tutorial shows how to create a COBOL stored procedure that manipulates XML data in DB2 for z/OS. This is done through the Data Perspective, which can be seen in Figure 1. A perspective is a set of explorers and windows, each with a dedicated purpose, collected in one screen and aimed at performing a certain type of development task. Examples of perspectives are the Web Perspective, the Java Perspective, and the z/OS Projects Perspective.

Figure 1. The Data Perspective in RDz
Screenshot showing the Data Perspective in RDz

The Data Perspective consists of five explorers or windows:

  • The Data Project Explorer (1) shows the different projects and the artifacts you have created within these projects.
  • The Data Source Explorer (2) shows the databases you have defined and allows you to connect to them and work with objects in them.
  • The Remote Systems (3) shows the z/OS systems defined and allows you to connect and work with them.
  • The editor, in the middle of the page, (4) is used to edit whichever type of artifact you are working on (for example, a COBOL program or an SQL script).
  • The bottom window (5) is a status/error/task bar showing information about the current situation or last performed task.

Whenever appropriate in the following sections, figures may show only the relevant explorer or window, instead of the whole RDz screen.


Build the XML application

Let's take a look at the steps needed to create a stored procedure that inserts an XML document into a DB2 table. As part of the insert, the XML document is to be validated against an XML schema to make sure it has the correct format

The steps for creating this stored procedure and the DB2 artifacts needed to run it can be divided into the following main tasks:

  • Create a data development project:
    A project provides you with a folder-like structure and is used for organizing your work by grouping together all the bits and pieces needed for the project. A data development project is associated with a particular DB2 subsystem. It is stored in the workspace of your RDz client, i.e. on your laptop.

    The steps involved in performing this task are shown in Figures 2 through 9.
  • Create a DB2 table to hold the XML data:
    A table in which to store the XML data is to be created in the DB2 subsystem associated with the data development project.

    The steps involved in performing this task are shown in Figures 10 through 13.
  • Register an XML schema:
    The XML document which is to be inserted using the stored procedure is to be validated against an XML schema. We need to register the schema in DB2 to enable this validation.

    The steps involved in this task are shown in Figures 14 through 19.
  • Create the COBOL stored procedure:
    A COBOL stored procedure is made up from a COBOL load module and definitions in DB2. We need to create the COBOL source code for the load module and the DDL that gives the definition in DB2. The source code is saved in a data set on z/OS, whereas the DDL is saved locally in the RDz workspace.

    The steps involved in this task are shown in Figures 20 through 32.
  • Deploy the stored procedure:
    The source code is to be transformed into a load module on z/OS and the DDL run against the DB2 subsystem associated with the data development project.

    The steps involved in this task are shown in Figures 33 through 34.

Create a data development project

The first step is to create a data development project that will hold all the artifacts needed for the project.

  1. Right-click in the white area of the Data Project Explorer, and choose New > Project, as shown in Figure 2:
    Figure 2. Create a new data development project, step 1
    Screenshot showing how to create a new data development project, step 1
  2. You now have to choose which wizard to start. Select Data Development Project, then click on Next, as shown in Figure 3:
    Figure 3. Create a new data development project, step 2
    Screenshot showing how to create a new data development project, step 2
  3. Give the project a name, such as XML with COBOL SP, then click on Next:
    Figure 4. Create a new data development project, step 3
    Screenshot showing how to create a new data development project, step 3
  4. You are now asked to choose a connection. If you already have defined access to the DB2 subsystem you are going to work on, either through DB2 Connect or previously using RDz, the name will appear in the list of connections to choose from. You can choose it by high-lighting it; otherwise you have to configure the connection. To start the wizard, click on New:
    Figure 5. Create a new data development project, step 4
    Screenshot showing how to create a new data development project, step 4
  5. To choose the connection profile type, click on DB2 for z/OS, give the connection a name (DSN9—the location name of the DB2 subsystem), and click on Next:
    Figure 6. Configure a connection to DB2 for z/OS, step 1
    Screenshot showing how to donfigure a connection to DB2 for z/OS, step 1
  6. You now need to supply connection information for the DB2 subsystem: the location name of the DB2 subsystem, the host name of the z/OS system, which can either be an IP address or a logical name, and the port number of your DB2 subsystem. These values can be obtained from the output of the DB2 command DIS DDF.

    You also need user name and password on z/OS that is to be used for the connection.

    As illustrated in Figure 7, the following values are supplied:
    • Location: DSN9
    • Host: 9.123.45.678
    • Port number: 446
    • User name: user48
    • Password: something

    Click on Test Connection to make sure the connection works, then click on Finish:
    Figure 7. Configure a connection to DB2 for z/OS, step 2
    Screenshot showing how to donfigure a connection to DB2 for z/OS, step 2
  7. The newly configured connection appears in the list. To select it, click on it, then click on Finish:
    Figure 8. Create a new data development project, step 5
    Screenshot showing how to create a new data development project, step 5

You can now see the new data development project in the Data Project Explorer, as shown in Figure 9:

Figure 9. Data development project created
Screenshot showing that the data development project is created

Create a DB2 table to hold the XML data

Before you can start creating your stored procedures, you need to create the objects the stored procedures are going to use in DB2.

Let's assume a very simple data model where all the data is stored in one table as XML. To create this table:

  1. Expand the newly created data development project by clicking on the plus sign (+) next to it.
  2. Right-click on SQL Scripts, and select SQL or XQuery Script:
    Figure 10. Create a DB2 table, step 1
    Screenshot showing how to create a DB2 table, step 1
  3. A window opens where you give a name to the DDL script and click on Finish:
    Figure 11. Create a DB2 table, step 2
    Screenshot showing how to create a DB2 table, step 2
  4. This results in an editor opening in the center area of the screen. Enter the DDL for your table in the editor, and save your changes by entering CTRL+S, then right-click on the script in the Data project Explorer and choose Run SQL:
    Figure 12. Create a DB2 table, step 3
    Screenshot showing how to create a DB2 table, step 3

The table is then created at the DB2 subsystem associated with the project, and a receipt (or error if anything is wrong with the DDL) is shown in the bottom of the screen, as shown in Figure 13:

Figure 13. Create a DB2 table, step 4
Screenshot showing how to create a DB2 table, step 4

Register an XML schema in DB2

The next step is to register an XML schema in DB2. This is not strictly necessary to work with XML data in DB2, but you want to validate any data inserted into DB2 against the XML schema to make sure it adheres to a certain format. To this end, the schema must be known to DB2.

Let's assume that the XML schema is resident on the local file system of your laptop. This may either be an XML schema you have developed yourself to suit the needs of your application, or it may be a schema representing a common standard, like UBL 2.0, for example, which is a format for electronic commerce developed by OASIS.

In fact, if you want to have a look at some of the industry formats used, like UBL 2.0, UNIFI, which is a financial format, or CDISC, which is a format used for clinical data, a wide variety of these are available for download (see Resources).

To register the XML schema to DB2:

  1. Start by expanding the relevant subsystem in the Data Source Explorer.
  2. Right-click on XML Schemas, and select Register an XML schema:
    Figure 14. Register XML schema in DB2 for z/OS, step 1
    Screenshot showing how to register XML schema in DB2 for z/OS, step 1
  3. Enter the name you want to use for the XML schema and an optional comment, then click on Next. (The name can be anything you choose, as it is not directly related to the actual contents of the schema document(s). It is the name you will use when validating data against the schema.)

    This tutorial uses the name of UBL_INVOICE_2 and no comment, as shown in Figure 15:
    Figure 15. Register XML schema in DB2 for z/OS, step 2
    Screenshot showing how to register XML schema in DB2 for z/OS, step 2
  4. Click on Add to search for the schema in the local file system:
    Figure 16. Register XML schema in DB2 for z/OS, step 3
    Screenshot showing how to register XML schema in DB2 for z/OS, step 3
  5. Browse the file system and select the document you wish to register. In this case, the schema is made up of several documents. Choose the main document and the dependants will automatically be included by RDz, then click on Open:
    Figure 17. Register XML schema in DB2 for z/OS, step 4
    Screenshot showing how to register XML schema in DB2 for z/OS, step 4
  6. Click on Finish to perform the schema registration in DB2:
    Figure 18. Register XML schema in DB2 for z/OS, step 5
    Screenshot showing how to register XML schema in DB2 for z/OS, step 5

The schema is now registered in DB2, and you get the receipt in the bottom of the screen, as shown in Figure 19:

Figure 19. Register XML schema in DB2 for z/OS, step 6
Screenshot showing how to register XML schema in DB2 for z/OS, step 6

Create a COBOL stored procedure

At this point you have created the necessary objects in DB2 and are ready to start development of the COBOL stored procedures.

First, make sure that all the necessary data sets for development on z/OS have been created. You need data sets for COBOL source code, for compiler errors, for DBRMs, for listing output, for object decks, and possibly for copy books. You also need a load library that you can write to, and the names of the DB2 and LE link libraries.

  1. Start the stored procedures wizard by right-clicking on the Stored Procedures folder in the Data Project Explorer, and select New > Stored Procedure:
    Figure 20. Create a stored procedure, step 1
    Screenshot showing how to create a stored procedure, step 1
  2. Enter a name for the stored procedure, and chose the language COBOL from the drop-down menu, then click on Next:
    Figure 21. Create a stored procedure, step 2
    Screenshot showing how to create a stored procedure, step 2
  3. Next, enter the name of the COBOL module, then click on Next. For convenience, this tutorial uses the same name as the name of the stored procedure, but this does not have to be the case.
    Figure 22. Create a stored procedure, step 3
    Screenshot showing how to create a stored procedure, step 3
  4. Click on Browse to choose the z/OS system on which you want to create the stored procedure, and click on Browse to choose the data set you want to use for the COBOL source code, then click on Next:
    Figure 23. Create a stored procedure, step 4
    Screenshot showing how to create a stored procedure, step 4
  5. You now get a window where you can enter one or more SQL statements to be used in your stored procedure. In this case, you want a simple SQL statement that inserts a row into your table consisting of a comment and an XML document. The XML document is to be validated against a schema, so use the function SYSPROC.DSN_XMLVALIDATE. This function, in turn, must be used with the XMLPARSE function that parses the document. The result are shown in Figure 24.

    You can also specify how many result sets the stored procedure returns by selecting the appropriate number from the drop-down menu. In this case, choose None, as the stored procedure only inserts data, then click on Next:
    Figure 24. Create a stored procedure, step 5
    Screenshot showing how to create a stored procedure, step 5
  6. The ext step is error handling and parameters. Choose SQLSTATE and SQLCODE from the drop-down menu, then click on Add to add the parameters:
    Figure 25. Create a stored procedure, step 6
    Screenshot showing how to create a stored procedure, step 6
  7. For each parameter, specify whether it is an In, Out, or InOut parameter. Give the parameter a name and an SQL type. Depending on the type, you may also need to specify length and other attributes.
  8. As shown in Figure 26, we create a parameter named COM with SQL type VARCHAR(1000). Click on OK if this is the last parameter you want to create, or click on Apply if you want to create more parameters. Continue until all the parameters have been defined.
    Figure 26. Create a stored procedure, step 7
    Screenshot showing how to create a stored procedure, step 7
  9. When all the parameters have been defined and you have clicked on OK, you return to the previous window. Note that parameters cannot be changed later, so take care to define them correctly from the start. The following table lists all of the parameters (as you can see in Figure 27):
    ModeNameSQL TypeCOBOL Type
    InCOMVARCHAR(1000) 49 VAR-LEN PIC S9(4) USAGE BINARY.
    49 VAR-TEXT PIC X9(1000) USAGE DISPLAY
    InDOCCLOB(1M)USAGE IS SQL TYPE IS CLOB(1M)
    InSCHEMAVARCHAR(100) 49 VAR-LEN PIC S9(4) USAGE BINARY.
    49 VAR-TEXT PIC X9(100) USAGE DISPLAY
    OutSQLSTATEOUTCHAR(5)PIC X(5)
    OutSQLCODEOUTINTEGERPIC S9(9) COMP-5
  10. Click on Next.
    Figure 27. Create a stored procedure, step 8
    Screenshot showing how to create a stored procedure, step 8
  11. Now you have to specify the collection ID you want to use for the stored procedure package, then click on Advanced. (We choose the value of DB2ADMIN.)
    Figure 28. Create a stored procedure, step 9
    Screenshot showing how to create a stored procedure, step 9
  12. Fill in the name of the WLM environment that is to be used for the stored procedure. (We use the value of DSN9WL4K.) Click on OK to return to the previous window, then click on Next:
    Figure 29. Create a stored procedure, step 10
    Screenshot showing how to create a stored procedure, step 10
  13. In the next window you have the option to specify previously developed fragments to be included in the stored procedure, as shown in Figure 31. Click on Next.
    Figure 30. Create a stored procedure, step 11
    Screenshot showing how to create a stored procedure, step 11
  14. Now you are through the stored procedure wizard and you get a summary page where you can check that everything has been defined as you intended (see Figure 31). If not, you still have the option of going back and making changes. Click on Finish.
    Figure 31. Create a stored procedure, step 12
    Screenshot showing how to create a stored procedure, step 12

When the stored procedure wizard has completed, the editor in the center of the page is opened with the COBOL source code generated. You can now modify the code with any changes or additions needed.

We add two statements that move the SQLCODE and SQLSTATE to the output parameters SQLCODEOUT and SQLSTATEOUT so that the stored procedure returns the result of the insert statement given in the SQLCA. Press CTRL+S to save any changes made.

Other than that, the COBOL source is ready to be compiled. See Figure 32 for the full source code:

Figure 32. Create a stored procedure, step 13
Screenshot showing how to create a stored procedure, step 13

Listing 1 shows the code listing for Figure 32.

Listing 1. Code to create a stored procedure
      ***************************************************
      *COBOL Stored Procedure INSVXML
      *System Long Name:  STPLEX4A.SVL.IBM.COM
      *System Short Name:  stplex4a.svl.ibm.com
      *Data Set:  DK18523.SP.COBOL(INSVXML)
      * @param COM
      * @param DOC
      * @param SCHEMANAME
      * @param SQLSTATEOUT
      * @param SQLCODEOUT
      ***************************************************
       IDENTIFICATION DIVISION.
       PROGRAM-ID. INSVXML.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.
            EXEC SQL INCLUDE SQLCA END-EXEC.
       LINKAGE SECTION.
       01 COM.
            49 VAR-LEN PIC S9(4) USAGE BINARY.
            49 VAR-TEXT PIC X9(1000) USAGE DISPLAY.
       01 DOC USAGE IS SQL TYPE IS CLOB(1M).
       01 SCHEMANAME.
            49 VAR-LEN PIC S9(4) USAGE BINARY.
            49 VAR-TEXT PIC X9(100) USAGE DISPLAY.
       01 SQLSTATEOUT PIC X(5).
       01 SQLCODEOUT PIC S9(9) COMP-5.
       PROCEDURE DIVISION USING
            COM
            DOC
            SCHEMANAME
            SQLSTATEOUT
            SQLCODEOUT.
            EXEC SQL
              INSERT INTO DB2ADMIN.XMLTABLE (COMMENT, DOCUMENT)
                VALUES(:COM, XMLPARSE (DOCUMENT
                SYSFUN.DSN_XMLVALIDATE(.DOC, :SCHEMANAME)))
            END-EXEC.
            MOVE SQLSTATE TO SQLSTATEOUT.
            MOVE SQLCODE TO SQLCODEOUT.
            GOBACK.

Deploy the stored procedure

On the bottom of the editor area, a number of tabs give access to different information regarding the stored procedure. Click on the Data sets for Deploy tab and fill in values for each data set listed. Make sure that all these data sets exist. Use CTRL+S to save your changes. You can also go through the other tabs and perform any changes you need there.

Figure 33. Data sets for deployment
Screenshot showing the data sets for deployment

Finally, you are ready to build and deploy the stored procedure. In the Data Project Explorer, right-click on the stored procedure and choose Deploy, as shown in Figure 34.

Figure 34. Deployment of stored procedure to DB2 for z/OS
Screenshot showing the deployment of stored procedure to DB2 for z/OS

Rational Developer for System z will then spawn a job on z/OS that performs all the tasks necessary to deploy the stored procedure, including pre-compilation, compilation, link-editing, and bind, as well as refresh for the WLM environment used for the stored procedure. As usual, a receipt (or any compiler or other errors) will be given in the bottom of the screen when the deployment has completed.

Test the stored procedure

Having deployed the stored procedure to the DB2 subsystem associated with your data development project, you are now ready to test it.

  1. Find the stored procedure in the Data Source Explorer view in the left bottom corner of the Data Perspective, as shown in Figure 24.
  2. Expand Databases, then expand the subsystem you are using (in this case, DSN9).
  3. Expand Schemas and your schema (in this case, DK18523), then expand Stored Procedures.
  4. Right-click on the stored procedure and choose Run in the pull-down menu:
    Figure 35. Test a stored procedure, step 1
    Screenshot showing how to test a stored procedure, step 1
  5. Enter simple values directly in the window that appears. To enter the XML value, click on the dots buttons to the right:
    Figure 36. Test a stored procedure, step 2
    Screenshot showing how to test a stored procedure, step 2
  6. You now get a small editor to help you input the value, and there is even an XML tab to assist in producing well-formed XML. In this case, you have sample data for test in the local file system. Click on Browse.
    Figure 37. Test a stored procedure, step 3
    Screenshot showing how to test a stored procedure, step 3
  7. Browse to find the document needed, and click on Open:
    Figure 38. Test a stored procedure, step 4
    Screenshot showing how to test a stored procedure, step 4
  8. Click on OK to return to the previous screen, as shown in Figure 39, then click on OK again to run the stored procedure.
    Figure 39. Test a stored procedure, step 5
    Screenshot showing how to test a stored procedure, step 5

In the left side of the status window (see Figure 40), you can see that the stored procedure has run successfully. In the right-hand side, you can see the values of each of the parameters, including the out parameters SQLCODEOUT and SQLSTATEOUT, which are both 0, indicating that the insert was successful.

Figure 40. Test a stored procedure, step 6
Screenshot showing how to test a stored procedure, step 6

Summary

This tutorial showed you how you can build, test, and deploy COBOL stored procedures that manipulate XML through Rational Developer for System z. This tutorial also showed how you can register an XML schema to DB2 and validate XML against that schema from COBOL.

Acknowledgements

Many thanks to Marcia Abercrombie, Mel Fowles, Lawrence Legard, and Larry England for their help with setting up RDz and DB2.

Resources

Learn

Get products and technologies

Discuss

Comments

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


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management, XML, Rational, DevOps
ArticleID=396062
ArticleTitle=Build a pureXML application in DB2 for z/OS, Part 3: Develop stored procedures with Rational Developer for System z
publish-date=06112009