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

Page 3 of 7  Document options PDF - Fits A4 and Letter 1654 KB (40 pages) Get Adobe® Reader® Discuss My developerWorks needs you! Connect to your technical community Rate this tutorial Help us improve this content

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
   
   
   
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
   
   
   
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
   
   
   
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
   
   
   
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
   
   
   
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
   
   
   
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
   
   
   

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

Back to top

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
   
   
   
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
   
   
   
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
   
   
   

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:

Back to top

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
   
   
   
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
   
   
   
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
   
   
   
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
   
   
   
6. 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:

Back to top

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
   
   
   
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
   
   
   
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
   
   
   
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
   
   
   
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
   
   
   
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
   
   
   
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
   
   
   
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):
   
   

   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

   
   
   
10. Click on Next.
    
    Figure 27. 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
    
    
    
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
    
    
    
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
    
    
    
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
    
    
    

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:

Listing 1 shows the code listing for Figure 32.

            
      ***************************************************
      *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. 
    

Back to top

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.

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.

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.

Back to top

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
   
   
   
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
   
   
   
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
   
   
   
7. Browse to find the document needed, and click on Open:
   
   Figure 38. 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
   
   
   

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.

Back to top

	Page 3 of 7