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.
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).
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.
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
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.
- 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
- 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
- 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
- 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
- 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
- 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
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: 126.96.36.1998
- 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
- 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
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
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:
- Expand the newly created data development project by clicking on the plus sign (+) next to it.
- Right-click on SQL Scripts, and select SQL or XQuery
Figure 10. Create a DB2 table, step 1
- A window opens where you give a name to the DDL script and click
Figure 11. Create a DB2 table, step 2
- 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
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
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:
- Start by expanding the relevant subsystem in the Data Source Explorer.
- Right-click on XML Schemas, and select Register an XML
Figure 14. Register XML schema in DB2 for z/OS, step 1
- 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
- Click on Add to search for the schema in the local file
Figure 16. Register XML schema in DB2 for z/OS, step 3
- 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
- Click on Finish to perform the schema registration in DB2:
Figure 18. 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
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.
- 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
- Enter a name for the stored procedure, and chose the language
COBOL from the drop-down menu, then click on
Figure 21. Create a stored procedure, step 2
- 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
Figure 22. Create a stored procedure, step 3
- 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
- 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
XMLPARSEfunction 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
- 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
- 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.
- 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
- 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):
Mode Name SQL Type COBOL Type In COM VARCHAR(1000) 49 VAR-LEN PIC S9(4) USAGE BINARY.
49 VAR-TEXT PIC X9(1000) USAGE DISPLAY
In DOC CLOB(1M) USAGE IS SQL TYPE IS CLOB(1M) In SCHEMA VARCHAR(100) 49 VAR-LEN PIC S9(4) USAGE BINARY.
49 VAR-TEXT PIC X9(100) USAGE DISPLAY
Out SQLSTATEOUT CHAR(5) PIC X(5) Out SQLCODEOUT INTEGER PIC S9(9) COMP-5
- Click on Next.
Figure 27. Create a stored procedure, step 8
- 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
- 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
Figure 29. Create a stored procedure, step 10
- 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
- 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
Figure 31. 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
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
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
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.
- Find the stored procedure in the Data Source Explorer view in the left bottom corner of the Data Perspective, as shown in Figure 24.
- Expand Databases, then expand the subsystem you are using (in this case, DSN9).
- Expand Schemas and your schema (in this case, DK18523), then expand Stored Procedures.
- Right-click on the stored procedure and choose Run in the
Figure 35. Test a stored procedure, step 1
- 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
- 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
- Browse to find the document needed, and click on Open:
Figure 38. Test a stored procedure, step 4
- 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
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
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.
Many thanks to Marcia Abercrombie, Mel Fowles, Lawrence Legard, and Larry England for their help with setting up RDz and DB2.
- Build a pureXML application in DB2 for z/OS
- "Build a pureXML application in DB2 for z/OS, Part 1: Data Storage and Manipulation through COBOL stored procedures" (developerWorks, May 2009): Follow an example application based on COBOL stored procedures that can be downloaded.
- "Build a pureXML application in DB2 for z/OS, Part 2: Web enablement through Universal Services" (developerWorks, June 2009): Learn how to set up Web access through Universal Services.
- Rational Developer for System z: Learn more about Rational Developer for System z.
- developerWorks Information Management zone: Learn more about Information Management. Find technical documentation, how-to articles, education, downloads, product information, and more.
- Stay current with developerWorks technical events and webcasts.
Get products and technologies
- Rational Developer for System z: Download a trial version of Rational Developer for System z.
- Industry Formats and Services with pureXML: Download Industry Formats and Services with pureXML.
- Build your next development project with IBM trial software, available for download directly from developerWorks.
- Participate in the discussion forum.
- Participate in developerWorks blogs and get involved in the developerWorks community.
Dig deeper into Information management on developerWorks
Get samples, articles, product docs, and community resources to help build, deploy, and manage your cloud apps.
Experiment with new directions in software development.
Software development in the cloud. Register today to create a project.
Evaluate IBM software and solutions, and transform challenges into opportunities.