CICS Web services support enables you to access CICS applications through web services. CICS applications can also invoke web services. CICS supplies various handlers, including pipeline and application handlers, to manipulate the incoming and outgoing web services messages. You can write your own handlers, too. This article assumes that you are familiar with CICS Web services.
DB2 pureXML enables the storage of XML in XML collections (columns). The individual XML elements and attributes in the stored XML can be indexed and manipulated in application programs such as Java, COBOL, or C, through XPath and SQL/XML. You can also view the stored XML in relational form.
The benefits of storing and retrieving XML directly include the following:
- The system is simpler to design: No (or few) data normalizations required.
- The system is speedier to develop: Fewer mappings between the incoming and outgoing data and the stored data.
- The system is faster to execute: Fewer relational joins and less XML shredding, which consumes CPU.
- The system is quicker to evolve: Fewer database changes needed, because as long as the XML is well-formed, it can be stored in the database (and optionally validated)
- The system is easier to understand: The data structures are consistent across the boundaries of the system and the database.
- The system is straightforward to prototype: Less customization is required, and the database design is simple
- The system integrates smoothly with web technologies: Web technologies are XML-based.
DB2 pureXML stores XML data in parsed form. Thus, in many cases you don't need to repeatedly parse the same XML in the middle tier, which is CPU intensive. Instead you can use declarative languages, such as XPath or SQL/XML, to manipulate the already parsed XML, as shown in Figure 1.
Figure 1. Parse once—access many with DB2 pureXML
This article describes the following:
- A review of the scenarios in the first article with an explanation of how the scenarios are extended in this article
- A summary of the scenarios in both articles in Table 1
- A description of the CICS service configuration and an outline of the CICS handlers used in the scenarios
- Scenario 1A, which stores incoming XML in DB2 pureXML by extracting the business data from the SOAP message
- A variation on Scenario 1A, which shows how the stored XML can be accessed as though it is in relational form through a view
- Scenario 1B, which stores incoming XML in relational form
- Scenarios 2A and 2B outlined to show how data in a database can be published as an XML web service response
- How to run the CICS Web services in conjunction with DB2 pureXML
- A summary of other scenarios
Reviewing the scenarios
The first article in this series, "Using CICS with DB2 pureXML, Part 1: Perform basic XML storage and retrieval through CICS Web services" introduced two scenarios. Scenario 1 in that article stored an incoming web service XML message, including the SOAP header, into a DB2 XML column, as shown in Figure 2.
Figure 2. Service request: Using DB2 pureXML to log inbound messages
This article develops Scenario 1 further by describing the following scenarios:
- Scenario 1A
- Extracts the business data from the SOAP message using SQL/XML, and then stores the XML
- Scenario 1B
- Converts (shreds) the XML into relational form using SQL/XML, and stores it
Scenario 2 in the first article retrieved XML data stored in DB2, which included the SOAP header, and returned it in a response to a web service request, as shown in Figure 3.
Figure 3. Service response: Using DB2 pureXML to create outbound messages
This article develops Scenario 2 further by describing the following scenarios:
- Scenario 2A
- Updates XML data through a capability of DB2 10 for z/OS
- Scenario 2B
- Uses SQL/XML to publish relational data as XML and to return the resulting XML in a response to a web service request.
Introducing the new scenarios
Following are the new scenarios in this article.
- CICS service request
- The service request scenarios, Scenarios 1A and 1B, give examples of ways in which the data in an inbound web service request message can be accessed and manipulated using pureXML. The SQL is provided that extracts the business data from the body of a SOAP message and then stores the business data in DB2. In Scenario 1A, the business data is stored as XML. A variation is also discussed that enables the data stored as XML to be accessed as relational. Scenario 1B offers an option in which the XML is shredded to enable the data to be stored as relational.
- CICS service response
- Service response Scenario 2A describes how XML data can be updated through a capability of DB2 10 for z/OS. Service response Scenario 2B illustrates how relational data can be returned as XML through a capability of DB2 8 z/OS. This article's example uses this to construct a web service response message to be returned from the web service.
Figure 4 shows how each of the new scenarios flows:
- Scenario 1A processes the service request and stores the XML intact into an XML storage table on the DB2 server.
- Scenario 2A retrieves the stored XML, modifies it intact, and outputs a service response.
- Scenario 1B processes the service request and stores it as relational data on the DB2 server.
- Scenario 2B retrieves the stored relational data and outputs a service response in XML format.
Figure 4. Service request and response flow
Table 1 shows a scenario summary for the scenarios in both articles. Article 1 refers to the first article in the series. Article 2 refers to this article. DB2 z/OS version indicates the version of DB2 that provides the capability to support the scenario.
Table 1. List of CICS Web services and DB2 pureXML scenarios
|Article (Part 1 or Part 2)||DB2 z/OS version||Scenario name||Description|
|1||9||Scenario 1: Request - XML with SOAP header||Store incoming XML in DB2 pureXML with SOAP header|
|2||9||Scenario 1A: Request - Modified XML (Business data extract without SOAP header)||Store incoming XML in DB2 pureXML by extracting the business data using SQL/XML to remove the SOAP header. Also, access the stored XML as relational through SQL/XML views|
|2||9||Scenario 1B: Request - Relational||Store incoming XML as relational in DB2 using SQL/XML to shred the XML|
|1||9||Scenario 2: Response - XML with SOAP header||Retrieve the stored XML in DB2 pureXML and return it as a response|
|2||10||Scenario 2A: Response - Modified XML||Retrieve and modify the stored XML in DB2 pureXML and return it as a response using DB2 10 for z/OS capability|
|2||8||Scenario 2B: Response - Relational||Retrieve the stored relational data and return it as a response using SQL/XML to publish the relational data as XML|
The benefits of using DB2 capabilities to manipulate XML include the following:
- Manipulating complex XML
- If the inbound XML data is complicated, with multi-dimensional nested structures, then accessing the data using XPath queries might be simpler than by using the CHANNEL and CONTAINERs interface that the CICS supplied application handler provides.
- Parse-once approach
- If the data needs to be persisted in DB2, then less CPU is likely to be used if all XML manipulations are performed in DB2.
- Log of incoming messages
- Using some of the techniques described here, a query-able log of the incoming messages is maintained, making it simpler to track what is happening in the system.
Understanding the CICS Web service configuration
This section of the article explains the configuration of the CICS Web service and where the pureXML capabilities are introduced. CICS Web services use a number of CICS resources such as a URIMAP, a PIPELINE containing one or more handlers, a WEBSERVICE resource, and so on. If you are unfamiliar with CICS Web services, it is recommended that you read introductory materials on the subject before continuing with this article. In particular, it is assumed that you are familiar with the CICS PIPELINE and pipeline handlers. See the Resources section for suitable documents.
When a web service message (a SOAP message) is passed through a CICS pipeline, zero or more handlers can access the message and make decisions based on its contents. CICS provides some of these handlers for security, distributed Units of Work (UOWs), and so on, as shown in Figure 5.
Figure 5. CICS Web services handlers
After these handlers have run, the message is passed to a terminal handler program. The terminal handler is responsible for processing the SOAP message.
You can write your own handlers, such as the general purpose terminal handler WEBINST described in the first article. However, CICS provides a SOAP handler that performs a number of checks on the inbound SOAP message to ensure that it is compliant with the SOAP specifications and returns the correct SOAP faults in error situations. For this article, use the supplied SOAP handler and gain the benefits it provides, including the capability to modify the CICS user ID and trans ID under which the business logic executes.
After the CICS-supplied SOAP handler has run, control is passed to an application handler. The application handler typically converts the body of the SOAP message into data structures suitable for use in a standard CICS program, and then links to that program. CICS supplies an application handler called DFHPITP that does the conversion. Rather than using DFHPITP to convert the XML into a COMMAREA structure or a CHANNEL with CONTAINERS, create an application handler named PUREXML(A or B) to insert the data into DB2. The application handler can then link to business logic, which can perform queries and updates on the data through SQL commands.
Listing 1 shows an example of the pipeline configuration file.
Listing 1. CICS pipeline configuration file example
<?xml version="1.0" encoding="EBCDIC-CP-US"?> <provider_pipeline xmlns="http://www.ibm.com/software/htp/cics/pipeline"> <service> <terminal_handler> <cics_soap_1.1_handler/> </terminal_handler> </service> <apphandler>PUREXMLA</apphandler> </provider_pipeline>
Scenario 1A: Store business data from incoming XML in DB2 pureXML
The DB2 table to store the XML is defined as shown in Listing 2.
Listing 2. DB2 table definition for storing XML
CREATE TABLE REDBOOK.SOAPBODY ( ID ROWID NOT NULL GENERATED ALWAYS, BODY XML) CCSID EBCDIC IN DATABASE REDBOOK;
The SOAP body contents that are included in the inbound message, such as the business data, are stored in XML format in the DB2 pureXML column called BODY. An ID field of type ROWID is also included. When the XML data is stored, it can be useful to have a key to the data, so that if the business logic needs to access the inbound XML later, it has an easy way to find it. The ROWID enables this capability.
When the request arrives into the CICS application handler, the entire SOAP envelope is read from the DFHREQUEST container into a variable called XMLDATA. Once the SOAP message is in memory, SQL is used to extract the contents of the body of the message (the business data) and store it into the table REDBOOK.SOAPBODY in the BODY column,, as shown in Listing 3.
Listing 3. SQL to store business data from incoming XML message as XML (without the SOAP header)
EXEC SQL SELECT ID INTO :WS-ROWID FROM FINAL TABLE( INSERT INTO REDBOOK.SOAPBODY ( BODY ) SELECT XMLDOCUMENT ( X.BODY ) FROM XMLTABLE( XMLNAMESPACES ('http://www.SWITCHI.com/schemas/SWITCHIInterface' AS "datans", 'http://schemas.xmlsoap.org/soap/envelope/' AS "soapns"), '$i/soapns:Envelope/soapns:Body' PASSING XMLPARSE(DOCUMENT :XMLDATA STRIP WHITESPACE) AS "i" COLUMNS "BODY" XML PATH 'datans:SWITCHOperation' ) AS X) END-EXEC.
DB2 generates the ROWID column content when the SOAP body is inserted into the table. The SQL statement selects the ROWID and stores it in a variable called WS-ROWID. This variable can then be passed to any business logic that needs to access the stored SOAP body. You might need to apply the fix to APAR PM25203 to use the ROWID in this way.
Note: The example in this article works with the entire message in the DFHREQUEST container. Although CICS has extracted the body of the message and provided it in a container called DFHWS-BODY, DB2 does not understand the data in the container on its own as valid XML. This is because CICS passes through any namespaces declared in the SOAP envelope of the inbound request in another container called DFHWS-NAMESPACES. If the data in DFHWS-BODY refers to these namespaces, an XML parser is likely to assume they are missing. In the case of DB2, an SQL error is produced, and the data cannot be parsed.
Scenario 1A variation: Access the stored XML as relational
While the data stored in Scenario 1A can be accessed directly as XML, you can also create relational views of the data. To do this, create a VIEW, and map the columns in the VIEW to the components of the XML message that are required. In the example, the inbound SOAP request message contains data associated with a customer account. Three pieces of information are taken from the SOAP message and made available through a VIEW called ACCVIEW: the account number, the account type, and the account balance. The DB2 administrator creates the VIEW while setting up the CICS Web service. Listing 4 shows how to create the view.
Listing 4. DB2 view definition for relational data (derived from the incoming XML message)
CREATE VIEW ACCVIEW(ACCOUNTNUM, ACCOUNTTYPE, BALANCE) AS SELECT X.* FROM REDBOOK.SOAPBODY RED, XMLTABLE( XMLNAMESPACES('http://www.SWITCHI.com/schemas/SWITCHIInterface' AS "datans"), '$acc/datans:SWITCHOperation/datans:account_details' PASSING RED.BODY as "acc" COLUMNS "ACCOUNTNUM" CHAR(8) PATH 'datans:account_number', "ACCOUNTTYPE" CHAR(1) PATH 'datans:account_type', "BALANCE" CHAR(8) PATH 'datans:balance') AS X;
At runtime, provide an application handler that is driven from the CICS-supplied SOAP handler. The application handler inserts the SOAP message into DB2. The data can then be accessed through the VIEW using a typical SQL query, as shown in Listing 5.
Listing 5. SQL to access XML data as relational through the view
EXEC SQL SELECT BALANCE FROM ACCVIEW WHERE ACCOUNTNUM = '12345678' END-EXEC.
Scenario 1B: Store incoming XML as relational in DB2 pureXML
The DB2 table used in Scenario 1B is shown in Listing 6.
Listing 6. DB2 table definition for relational data (derived from the incoming XML message)
CREATE TABLE REDBOOK.ACCOUNT ( ID ROWID NOT NULL GENERATED ALWAYS, ACCOUNTNUM CHAR(8), ACCOUNTTYPE CHAR(1), BALANCE CHAR(8)) IN DATABASE REDBOOK;
DB2 parses the inbound request message, and certain components of the message are extracted and stored in columns within the table. As in Scenario 1A, when the request arrives into the CICS application handler, the entire SOAP envelope is read from the DFHREQUEST container into a variable called XMLDATA.
Next, the SQL call extracts specific fields from the inbound message and stores them in the REDBOOK.ACCOUNT table, as shown in Listing 7.
Listing 7. SQL to store incoming XML message as relational
EXEC SQL SELECT ID INTO :WS-ROWID FROM FINAL TABLE( INSERT INTO REDBOOK.ACCOUNT (ACCOUNTNUM, ACCOUNTTYPE, BALANCE) SELECT X.ACCOUNTNUM, X.ACCOUNTTYPE, X.BALANCE FROM XMLTABLE( XMLNAMESPACES ('http://www.SWITCHI.com/schemas/SWITCHIInterface' AS "D", 'http://schemas.xmlsoap.org/soap/envelope/' AS "S"), '$i/S:Envelope/S:Body/D:SWITCHOperation/D:account_details' PASSING XMLPARSE(DOCUMENT :XMLDATA STRIP WHITESPACE) AS "i" COLUMNS "ACCOUNTNUM" CHAR(8) PATH 'D:account_number', "ACCOUNTTYPE" CHAR(1) PATH 'D:account_type', "BALANCE" CHAR(8) PATH 'D:balance') AS X) END-EXEC.
As part of the same SQL call, the ROWID value of the inserted ROW is returned into a variable called WS-ROWID. Once the SQL call is executed, the ROWID value stored in variable WS-ROWID is placed in a CICS container, and an EXEC CICS LINK call is made to the program containing the business logic. The business logic program can access the newly inserted data from DB2 by selecting it using the passed ROWID value, as shown in Listing 8.
Listing 8. SQL to select stored relational data
EXEC SQL SELECT ACCOUNTNUM, ACCOUNTTYPE, BALANCE INTO :WS-ACCNUM, :WS-ACCTYPE, :WS-BALANCE FROM REDBOOK.ACCOUNT WHERE ID = :WS-ROWID END-EXEC.
Scenario 2A: Retrieve and modify stored XML
In DB2 10 for z/OS, you can modify XML directly using SQL. You can add, modify, or delete XML nodes directly by specifying the appropriate XPath in the XMLMODIFY keyword. Listing 9 shows how to modify the value of the account_balance.
Listing 9. SQL to modify stored XML data
EXEC SQL UPDATE REDBOOK.ACCOUNT set info = XMLMODIFY(' REPLACE VALUE OF NODE /account_details/account_balance WITH "1000000.00"') WHERE ID = :WS-ROWID END-EXEC
With the XMLMODIFY keyword, you can make changes to stored XML to re-format the stored data to suit the required output message structure.
Scenario 2B: Retrieve and publish stored relational data
Scenario 2B shows how to create XML that includes relational data. For the purposes of the example, create the same web service response message used in Scenario 1B. The WSDL that describes the web service is the same WSDL used in the first article on CICS and DB2 pureXML. The WSDL is very simple. The response message contains the same elements as the request message.
In the example for Scenario 1B, the request message was shredded into relational data and stored in DB2 before a business logic program was called. The business logic accessed the stored relational data from the request message by way of a ROWID value that it received. The business logic modified the balance of the account.
Back in the application handler, create XML that includes the updated balance alongside the other values required for the response message. Use the ROWID again to find the data to return. When writing an application handler, CICS expects that the container DFHWS-BODY will contain the body of the SOAP response message when the application handler completes its processing. Therefore, you need to write an SQL call that creates a SOAP body element, and within that element, you need to create the business data to be returned.
Listing 10 shows the SQL to create the example SOAP body. The created XML is placed in a variable called XMLREPLY.
Listing 10. SQL to publish relational data as XML
EXEC SQL SELECT XMLELEMENT( NAME "soapenv:Body", XMLNAMESPACES ('http://www.SWITCHI.com/schemas/SWITCHIInterface' AS "p0", 'http://schemas.xmlsoap.org/soap/envelope/' AS "soapenv"), XMLELEMENT( NAME "p0:SWITCHOperationResponse", XMLELEMENT( NAME "p0:account_details", XMLELEMENT( NAME "p0:account_number", ACC.ACCOUNTNUM), XMLELEMENT( NAME "p0:account_type", ACC.ACCOUNTTYPE), XMLELEMENT( NAME "p0:balance", ACC.BALANCE) ) ) ) INTO :XMLREPLY FROM REDBOOK.ACCOUNT ACC WHERE ID = :WS-ROWID END-EXEC.
Note: When testing this scenario, you might find that the XML that
DB2 creates begins with an encoding statement like this:
<?xml version="1.0" encoding="IBM285"?>.
CICS does not expect an encoding statement in the DFHWS-BODY container.
Therefore, when the EXEC CICS PUT CONTAINER call is made in the COBOL
program PUREXMLB, to put the SOAP body of the reply into the DFHWS-BODY
container, the code puts the data after the encoding statement.
Testing the new scenarios
The first article provides clear instructions to test a CICS Web service. Where appropriate, refer to that article for information on testing the web services created in this article. Otherwise, complete the following steps to test the new scenarios.
Step 1 — Create the CICS pipeline configuration files
To create the CICS pipeline configuration files, complete the following steps:
- Copy the pipeline configuration files shown in Listing 11 to UNIX System Services (USS) as /u/user/config/AdvancedScenario1A_pipeline.xml.
Listing 11. CICS pipeline configuration file for Scenario 1A
<?xml version="1.0" encoding="EBCDIC-CP-US"?> <provider_pipeline xmlns="http://www.ibm.com/software/htp/cics/pipeline"> <service> <terminal_handler> <cics_soap_1.1_handler/> </terminal_handler> </service> <apphandler>PUREXMLA</apphandler> </provider_pipeline>
- Copy the pipeline configuration files shown in Listing 12 to UNIX System Services (USS) as /u/user/config/AdvancedScenario1B_pipeline.xml.
Listing 12. CICS pipeline configuration file for Scenario 1B and Scenario 2B
<?xml version="1.0" encoding="EBCDIC-CP-US"?> <provider_pipeline xmlns="http://www.ibm.com/software/htp/cics/pipeline"> <service> <terminal_handler> <cics_soap_1.1_handler/> </terminal_handler> </service> <apphandler>PUREXMLB</apphandler> </provider_pipeline>
Step 2 — Create and install CICS resources
The zip file provided in the Download section contains the COBOL source code for the application handlers used in this article. The zip file contains the handlers PUREXMLA and PUREXMLB. Both handlers link to COBOL business logic programs called PUREBUSA and PUREBUSB respectively. Complete the following steps to create and install the CICS resources:
- Compile and link all four programs using the integrated DB2/CICS/COBOL compiler. The JCL to compile the programs is included in the zip file. Tip: Examine the compiler and pre-compiler options specified in the supplied JCL to ensure that the supplied options are compatible with the options you usually use.
- Bind the DB2 plans by creating and installing the following CICS
resources in your region:
- PIPELINE(PUREXMLA) CONFIGFILE(/u/user/config/AdvancedScenario1A_pipeline.xml) SHELF(/u/user/shelf)
- URIMAP(PUREXMLA) USAGE(PIPELINE) PIPELINE(PUREXMLA) HOST( * ) PATH(/pureXML/AdvancedScenario1A)
- PROGRAM(PUREXMLA) DATALOCATION(ANY)
- PROGRAM(PUREBUSA) DATALOCATION(ANY)
The following CICS resources are required for Scenario 1B and Scenario 2B:
- PIPELINE(PUREXMLB) CONFIGFILE(/u/user/config/AdvancedScenario1B_pipeline.xml) SHELF(/u/user/shelf)
- URIMAP(PUREXMLB) USAGE(PIPELINE) PIPELINE(PUREXMLB) HOST( * ) PATH(/pureXML/AdvancedScenario1B)
- PROGRAM(PUREXMLB) DATALOCATION(ANY)
- PROGRAM(PUREBUSB) DATALOCATION(ANY)
The CICS resource TCPIPSERVICE(PUREXML) PORT(xxxxx) PROTOCOL(HTTP) is required for all the new scenarios.
DB2 requires the following CICS resources for all new scenarios:
- DB2ENTRY(PUREXML) ACCOUNTREC(TXid) AUTHTYPE(Userid) DRollback(Yes) PLAN(PUREXML)
- DB2TRAN(PUREXML) ENTRY(PUREXML) TRANSID(CPIH)
Step 3 — Test the web services
Sample WSDL for the web services is provided in the zip file.
- AdvancedScenario1A.wsdl is for Scenario 1A.
- AdvancedScenario1B.wsdl is for Scenario 1B and Scenario 2B.
Refer to the section entitled "Step 3 -- Test the Web service from Rational Developer for System z" in the first article for an example of using the WSDL to test the web service, but use the following endpoints:
http://<CICS hostname>:<CICS port>/pureXML/AdvancedScenario1Afor Scenario 1A
http://<CICS hostname>:<CICS port>/pureXML/AdvancedScenario1Bfor Scenario 1B and Scenario 2B
Exploring other scenarios
You can store incoming XML messages in hybrid formats, such as XML and relational together. You can also construct outgoing XML messages from hybrid data. In other words, all four of the new scenarios in this article can be mixed and matched. For example, you can show how forms can be used with CICS Web services and DB2 pureXML. See Resources to find out more about the use of Lotus® Forms and pureXML.
In addition, you can use the DB2 pureXML feature along with CICS applications outside the context of CICS Web services. For example, you can construct XML or you can transform XML using SQL/XML. You can also place XML in CICS COMMAREAs or containers.
This article shows how you can store incoming XML data using a CICS Web Service directly in DB2. This capability is available in DB2 9 for z/OS. Using customized CICS handlers, you can parse the XML using SQL to store the XML (the business data) without the SOAP header, either as XML or in relational form. You can also store the data in hybrid format, both as XML and as relational data.
The article also showed how you can publish stored relational data as XML and return it as a response to a CICS Web service. This capability is available in DB2 8 for z/OS. You can publish hybrid data (XML and relational) as XML. This capability is available in DB2 9 for z/OS. The article also explains how you can modify stored XML data directly (this capability is available in DB2 10 for z/OS) and return it as a response to a CICS Web service.
The benefits of DB2 performing the XML manipulations include improved performance (parse-once model) and broad XML manipulation capabilities through XPath and SQL/XML. The creation of a query-able log of inbound messages is a useful bonus.
Samples in a companion zip file, which you can download, configure, and execute, are provided to illustrate and re-create the scenarios described.
We'd like to thank Mengchu Cai, Arndt Eade, Ian J Mitchell, and Marcus Paradies for their support and contributions.
- Review Using CICS with DB2 pureXML, Part 1: Perform basic XML storage and retrieval through CICS Web services to see the first article in the series.
- Refer to the IBM Redbooks® "Application Development for CICS Web Services" and "Implementing CICS Web Services." for more information on CICS Web services support.
- See the IBM Redbook "Securing CICS Web Services" for information about securing CICS Web services.
- Go to on of the following resources for more information about DB2 pureXML:
- Check out the DB2 z/OS library website for information on DB2 10 for z/OS.
- Learn more about Information Management at the developerWorks Information Management zone. Find technical documentation, how-to articles, education, downloads, product information, and more.
- Stay current with developerWorks technical events and webcasts.
- Follow developerWorks on Twitter.
Get products and technologies
- Build your next development project with IBM trial software, available for download directly from developerWorks.
- Participate in the discussion forum.
- Check out the 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.
Keep up with the best and latest technical info to help you tackle your development challenges.
Software development in the cloud. Register today to create a project.
Evaluate IBM software and solutions, and transform challenges into opportunities.