Using CICS with DB2 pureXML, Part 1: Perform basic XML storage and retrieval through CICS Web services

Scenarios and use cases

This article provides an introduction to using DB2® pureXML® with CICS® applications written in Common Business Oriented Language (COBOL). XML is playing an increasingly important role in CICS applications. Therefore, the need to store and query XML in CICS applications is growing. This article describes two scenarios for using CICS with DB2 pureXML. The first scenario shows how to store inbound XML Web service messages in DB2 pureXML without first parsing the messages in CICS. The second article shows how a CICS application can retrieve XML data from DB2 and transmit it through a Web service. The article provides sample source code that you can download.

Share:

Scott Clee (Scott_Clee@uk.ibm.com), CICS Test Architect, IBM

Photo of Scott CleeScott Clee is the CICS Test Architect for IBM in the United Kingdom. He frantically architects the face of test by looking for new ways to push testing techniques, processes, and technology. His areas of expertise include CICS, Java, Common Business Oriented Language (COBOL), Linux, and testing. He has a degree in Mathematics and Computing from the University of Bath, UK.



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.



15 April 2010

Also available in Portuguese Spanish

Introducing CICS and pureXML

CICS Web services support became available in CICS TS V3.1. This feature makes it possible for CICS applications to be service requesters, service providers, or both, using either an HTTP transport or a WebSphere® MQ transport. Web services messages are typically in XML and often incorporate SOAP headers, so this article refers to them as SOAP messages. For more information on CICS Web services support, refer to the appropriate links in the Resources section at the end of this article.

DB2 9.1 for z/OS® added support, called pureXML, for an XML data type that enables XML data to be stored and indexed natively. Native XML support means that the XML is parsed once when it stored. XML indexes are maintained and provide speedy access to the stored XML through XPath and SQL/XML queries. Moreover, in DB2 10.1 (in beta at the time this article was published), the stored XML can also be updated through XPath. For more information on DB2 pureXML, refer to the appropriate links in the Resources section at the end of this article.

This article shows you how to use the CICS Web services support to store and retrieve SOAP messages using CICS and DB2 pureXML. The scenarios provide a basic description of the configuration and deployment required to connect end-to-end between CICS Web services and a DB2 pureXML back-end. Sample code is provided in the Download section. The sample source code is written in COBOL, but it would be straightforward to convert the code into either the PL/I or C programming languages.

Introducing the two scenarios

The two scenarios share the same CICS region, which is running CICS TS 4.1. The XML data is stored in a DB2 9.1 table that contains a single column of type XML. Listing 1 contains the SQL code to create this table.

Listing 1. Create table with a single column of type XML
CREATE TABLE REDBOOK.PUREXML (SOAP XML) CCSID EBCDIC IN DATABASE REDBOOK;

The sample code in the Download section contains the full SQL used to create the database STOGROUP, TABLESPACE, and TABLE.

The first scenario shows how a COBOL CICS program named WEBINST (a pipeline handler and service provider) inserts an XML message it has received from a Web service into a DB2 pureXML column. The second scenario shows how a COBOL CICS program named WEBSLCT (also a pipeline handler and service provider) retrieves XML from a DB2 pureXML column, and returns it as a response to a Web service invocation.

Scenario 1: Storing incoming data through CICS Web services

The steps in this scenario show you how to configure your CICS region to accept an inbound Web service request and store the SOAP payload in DB2. In order to simplify the example and keep the focus on the mechanics of setting up the Web service, the input message is returned as the output message of the Web service.

Instead of using the CICS supplied pipeline handler (DFHWS2LS0), which converts the SOAP data (the XML) into binary language structures, this scenario uses a custom pipeline handler program (named WEBINST) to receive the SOAP message and insert it directly into DB2. This improves performance by eliminating the need to have CICS parse the XML.

Step 1 — Create a CICS pipeline configuration file

Copy the pipeline configuration file shown in Listing 2 to UNIX® System Services (USS) as /u/user/config/insert_pipeline.xml. This configuration file notifies CICS that the custom pipeline handler program named WEBINST is to be used for handling Web service requests.

Listing 2. CICS pipeline configuration file
<?xml version="1.0" encoding="EBCDIC-CP-US"?>
<provider_pipeline xmlns="http://www.ibm.com/software/htp/cics/pipeline">
  <service>
    <terminal_handler>
      <handler>
        <program>WEBINST</program>
        <handler_parameter_list/>
      </handler>
    </terminal_handler>
  </service>
</provider_pipeline>

Step 2 — Create and install CICS resources

Create and install the following CICS resources in your region:

  • PIPELINE(WEBINST) CONFIGFILE(/u/user/config/insert_pipeline.xml) SHELF(/u/user/shelf)
  • URIMAP(WEBINST) USAGE(PIPELINE) PIPELINE(WEBINST) HOST( * ) PATH(/pureXML/insert)
  • PROGRAM(WEBINST) DATALOCATION(ANY)
  • TCPIPSERVICE(PUREXML) PORT(xxxxx) PROTOCOL(HTTP)

The following resources are required by DB2 and can also be shared by the WEBSLCT program described in Scenario 2 of this article.

  • DB2ENTRY(PUREXML) ACCOUNTREC(TXid) AUTHTYPE(Userid) DRollback(Yes) PLAN(PUREXML)
  • DB2TRAN(PUREXML) ENTRY(PUREXML) TRANSID(CPIH)

Copy the COBOL program shown in Listing 3 to z/OS and compile it with the integrated DB2/CICS/COBOL compiler. The sample code in the Download section contains a sample compile job.

Listing 3. COBOL program
******************************************************************
 IDENTIFICATION DIVISION.
 PROGRAM-ID. WEBINST.
 ENVIRONMENT DIVISION.
 CONFIGURATION SECTION.
*
 DATA DIVISION.
*
 WORKING-STORAGE SECTION.
*
 01  WSRESP    PIC S9(8)   COMP VALUE 0.
 01  WSRESP2   PIC S9(8)   COMP VALUE 0.
 01  WSFLENGTH PIC S9(8)   COMP VALUE 1024.
 01  XMLDATA   PIC X(1024) VALUE SPACES.

     EXEC SQL INCLUDE SQLCA END-EXEC.

 LINKAGE SECTION.

 PROCEDURE DIVISION.

 MAINLINE SECTION.

* Get the SOAP data

     EXEC CICS GET CONTAINER('DFHREQUEST')
         INTO(XMLDATA)
         FLENGTH(WSFLENGTH)
         RESP(WSRESP)
         RESP2(WSRESP2)
         END-EXEC.

     EXEC SQL
         INSERT INTO REDBOOK.PUREXML
                   ( SOAP )
            VALUES ( :XMLDATA )
     END-EXEC

* Respond with the original XML data by deleting DFHREQUEST
* container and returning DFHRESPONSE container

     EXEC CICS DELETE CONTAINER('DFHREQUEST') END-EXEC.

     EXEC CICS PUT CONTAINER('DFHRESPONSE')
         FROM(XMLDATA)
         RESP(WSRESP)
         RESP2(WSRESP2)
         END-EXEC.

* Exit program

     EXEC CICS RETURN END-EXEC.

 MAINLINE-EXIT.
     EXIT.
*----------------------------------------------------------------*

Step 3 — Test the Web service from Rational Developer for System z

Create a project in an Eclipse based IDE (for example, Rational® Developer for System z®) and import the SWITCH.wsdl file from the sample code into your project.

Right-click the sample WSDL file and select Web Services > Test with Web Services Explorer as shown in Figure 1.

Figure 1. Test with Web Services Explorer
Screenshot with right-click on the WSDL and selection path of Web Services > Test with Web Services Explorer.

From the Endpoints dialog (Figure 2), update the URI to point to your z/OS image using the TCP/IP service specified in the CICS TCPIPSERVICE resource, and click Go.

Figure 2. Add new Endpoint
Screenshot of Endpoints dialog with updated URI definition.

Enter test data into the fields of the Invoke a WSDL Operation form (Figure 3), and click Go.

Figure 3. Enter test data to generate a SOAP message
Input Message Details

Eclipse generates a SOAP message based on the data you enter into the form and sends it to CICS. CICS passes the SOAP to the WEBINST custom pipeline handler you created in Step 1. WEBINST inserts the data into the XML column of the DB2 table named REDBOOK.PUREXML. The WEBINST application also returns the original SOAP message as a response, as shown in the Web Services Explorer screenshot in Figure 4.

Figure 4. Request and Response SOAP Messages
Screenshot of Status window showing the SOAP Request and Response messages, which are the same

Step 4 — Show that the data was really stored in the database

This final step proves that the data was inserted into the DB2 table. From a tool such as DB2 SPUFI, execute the SQL statement shown in Listing 4.

Listing 4. SQL statement to show the data stored in the database
SELECT * FROM REDBOOK.PUREXML

Listing 5 shows the DB2 data returned by the above SQL statement.

Listing 5. Data returned from SQL statement
---------+---------+---------+---------+---------+---------+---------+---------+
          SELECT * FROM REDBOOK.PUREXML;                                00010000
---------+---------+---------+---------+---------+---------+---------+---------+
....
---------+---------+---------+---------+---------+---------+---------+---------+
<?xml version="1.0" encoding="IBM285"?><soapenv:Envelope xmlns:soapenv="http://s
DSNT400I SQLCODE = 000,  SUCCESSFUL EXECUTION
DSNT418I SQLSTATE   = 01004 SQLSTATE RETURN CODE
DSNT415I SQLERRP    = DSN SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD    = 0 0  0  -1  0  0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD    = X'00000000'  X'00000000'  X'00000000'  X'FFFFFFFF'
         X'00000000'  X'00000000' SQL DIAGNOSTIC INFORMATION
DSNT417I SQLWARN0-5 = W,W,,,, SQL WARNINGS
DSNT417I SQLWARN6-A = ,,,,   SQL WARNINGS
DSNE610I NUMBER OF ROWS DISPLAYED IS 1
DSNE612I DATA FOR COLUMN HEADER .... COLUMN NUMBER 1 WAS TRUNCATED
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
---------+---------+---------+---------+---------+---------+---------+---------+
---------+---------+---------+---------+---------+---------+---------+---------+
DSNE617I COMMIT PERFORMED, SQLCODE IS 0
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+---------+---------+
DSNE601I SQL STATEMENTS ASSUMED TO BE BETWEEN COLUMNS 1 AND 72
DSNE620I NUMBER OF SQL STATEMENTS PROCESSED IS 1
DSNE621I NUMBER OF INPUT RECORDS READ IS 1
DSNE622I NUMBER OF OUTPUT RECORDS WRITTEN IS 26
********************************************************** Bottom of Data ******

Note the XML declaration at the start of the data. It states that the XML is of encoding type IBM285 (UK EBCDIC). The significance of this is described in more detail in Scenario 2.

Scenario 2: Querying pureXML data and returning CICS Web services

The steps in this scenario show you how to create a COBOL CICS program that acts as a pipeline handler and service provider to retrieve XML from a DB2 pureXML column, and return it as a response to a Web service invocation.

Step 1 — Create a CICS pipeline configuration file

Copy the CICS pipeline configuration file shown in Listing 6 to Unix System Services (USS) as /u/user/config/select_pipeline.xml. This configuration file notifies CICS that the custom pipeline handler program named WEBSLCT is to be used for handling Web service requests.

Listing 6. CICS pipeline configuration file
<?xml version="1.0" encoding="EBCDIC-CP-US"?>
<provider_pipeline xmlns="http://www.ibm.com/software/htp/cics/pipeline">
  <service>
    <terminal_handler>
      <handler>
        <program>WEBSLCT</program>
        <handler_parameter_list/>
      </handler>
    </terminal_handler>
  </service>
</provider_pipeline>

Step 2 — Create and install CICS resources

Create and install the following resources in your CICS region:

  • PIPELINE(WEBSLCT) CONFIGFILE(/u/user/config/select_pipeline.xml) SHELF(/u/user/shelf)
  • URIMAP(WEBSLCT) USAGE(PIPELINE) PIPELINE(WEBSLCT) HOST(*) PATH(/pureXML/select)
  • PROGRAM(WEBSLCT) DATALOCATION(ANY)

For this scenario, you can reuse the TCPIPSERVICE, DB2ENTRY, and DB2TRAN resources you defined for WEBINST in Scenario 1.

Copy the COBOL program shown in Listing 7 to z/OS and compile it with the integrated DB2/CICS/COBOL compiler. The sample code in the Download section contains a sample compile job.

Listing 7. COBOL program
******************************************************************
 IDENTIFICATION DIVISION.
 PROGRAM-ID. WEBSLCT.
 ENVIRONMENT DIVISION.
 CONFIGURATION SECTION.
*
 DATA DIVISION.
*
 WORKING-STORAGE SECTION.
*
 01  WSRESP    PIC S9(8)   COMP VALUE 0.
 01  WSRESP2   PIC S9(8)   COMP VALUE 0.
 01  WSFLENGTH PIC S9(8)   COMP VALUE 1024.
 01  XMLDATA   PIC X(1024) VALUE SPACES.

     EXEC SQL INCLUDE SQLCA END-EXEC.

 LINKAGE SECTION.

 PROCEDURE DIVISION.

 MAINLINE SECTION.

* Get DB2 data

     EXEC SQL
         SELECT XMLSERIALIZE(SOAP AS CLOB)
         INTO :XMLDATA
         FROM REDBOOK.PUREXML
     END-EXEC.

* Respond with the XML data by deleting DFHREQUEST
* container and returning DFHRESPONSE container

     EXEC CICS DELETE CONTAINER('DFHREQUEST') END-EXEC.

     EXEC CICS PUT CONTAINER('DFHRESPONSE')
         FROM(XMLDATA)
         FLENGTH(WSFLENGTH)
         RESP(WSRESP)
         RESP2(WSRESP2)
     END-EXEC.

* Exit program

     EXEC CICS RETURN END-EXEC.

 MAINLINE-EXIT.
     EXIT.
*----------------------------------------------------------------*

Note that the XMLSERIALIZE function is used in the SQL statement for SELECT. This is so that the XML declaration is not added to the beginning of the returned XML payload. If you were to use SELECT * or SELECT SOAP without XMLSERIALIZE, then the returned XML would have an XML declaration at the start. The declaration would claim the XML is encoded in the local codepage of the CICS system. This local codepage would typically be one of the EBCDIC codepages. In some cases this may cause the application receiving the XML to reject it due to it not supporting the EBCDIC codepage.

Step 3 — Test the Web service from your browser

Point your browser at the URL for the WEBINST Web service. For example, in the sample case used to build this scenario, the URL would be: http://winmvsa1.hursley.ibm.com:12345/pureXML/select.

The SOAP message inserted by the WEBINST program in Scenario 1 is retrieved and displayed in your browser as shown in Figure 5.

Figure 5. SOAP message inserted by the WEBINST program
Screenshot of a Firefox browser displaying the XML SOAP message that was inserted by the WEBINST program

Other scenarios

There are many possible variations on the examples illustrated by the two scenarios described in this article. Examples include:

  • Modifying stored XML in DB2, using SQL XML update, in response to a request; for example, a change of address
  • Removing a portion of the XML, using SQL XMLTABLE, before storing it; for example, for security reasons
  • Converting the XML into relational columns, using SQL XMLTABLE, in addition to storing the data as XML, or as an alternative

These scenarios can support a variety of applications and services, such as application logging or forms based (for example, XHTML, XFORMS, or Lotus Forms) applications.

It is also possible to secure CICS Web services. The Resources section at the end of this article provides a link to details on this topic.

Conclusions and outlook

This article has demonstrated the basic steps involved in inserting and retrieving XML content into DB2 pureXML from a CICS COBOL application that is invoked through CICS Web services. The insertion and retrieval is done without having to parse or construct the XML data in the CICS application. The input XML message is stored directly in DB2. The output XML message is created from querying XML data in DB2. A CICS Web service that uses DB2 pureXML for its storage can easily become a part of a larger distributed application, for example, an XML forms application or a messaging application. The pureXML database can also form a queryable audit or application log for inbound and outbound CICS Web Services XML messages.

Acknowledgements

Thanks to Maj-Britt Risager (and colleagues) and Lars Andersen Hylleberg (and colleagues) for reviewing this article and providing feedback.


Download

DescriptionNameSize
Sample codeMVSFiles.zip11KB

Resources

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, SOA and web services
ArticleID=482002
ArticleTitle=Using CICS with DB2 pureXML, Part 1: Perform basic XML storage and retrieval through CICS Web services
publish-date=04152010