Build a pureXML and JSON application, Part 2: Create Universal Services for pureXML that expose JSON

Configure, deploy, and test JSON Universal Services on a JSONx store

The pureXML® Universal Services for JSON (abbreviated to JSON Universal Services in this article) are a set of database operations, including insert, update, delete, and query, exposed as Web services. These services enable an application to persist JSON in pureXML and to query it easily through HTTP with WebSphere® Application Server. Get started with configuring and testing JSON Universal Services in this article.

Faton (Tony) Avdiu, CoOp: DB2 Technical Enablement Specialist, IBM  

Photo of Faton (Tony) AvdiuFaton (Tony) Avdiu is a DB2 pureXML enablement (Co-Op), graduated in Computer Engineering. Tony is pursuing his masters degree in Information Systems at Pace University. He is currently working for IBM on technologies for storing XML in DB2.



Susan Malaika, Senior Technical Staff Member, IBM

Photo of Susan MalaikaSusan Malaika is a Senior Technical Staff Member in the IBM Information Management Group (part of IBM Software Group). Her specialties include XML, the Web, and databases. She has developed standards that support data for grid environments at the Global Grid Forum. In addition to working as an IBM product software developer, she has also worked as an Internet specialist, a data analyst, and an application designer and developer. She has also co-authored a book on the Web and published articles on transaction processing and XML. She is a member of the IBM Academy of Technology.



Michael Schenker, IBM DB2 for z/OS Optimizer Software Engineer, IBM

Author Photo: Michael SchenkerMichael Schenker has a masters degree in computer science acquired at the University of Applied Science in Leipzig/Germany. He has 7 years of work experience with IBM. During his career Michael worked on several database-related products starting as an intern to work on DB2 Information Integration with focus on non-relational wrappers. He joined IBM as full-time employee at the end of 2003. In 2006 Michael joined the database tools organization and worked on the DB2 Web service provider functionality in IBM Data Studio Developer. He gained a lot of expertise in SOA and Web technologies during that time . Recently Michael joined the DB2 for z/OS optimizer team but is still interested in SOA and Web technologies in and around DB2.



27 April 2010 (First published 27 October 2009)

Also available in Chinese Russian Japanese Vietnamese Portuguese Spanish

03 November 2009 - Authors added the More Information about the JSON Universal Services Operations section with Table 3 to describe the parameters, encodings, and the MIME types associated with the operations in the JSON Universal Services.

27 April 2010 - Authors updated and replaced the JSONUniversalServices.zip download file (see Download) which included changes to:

  • Prevent an OutOfMemory exception in the xml2json() function when multi-byte characters occur in the document
  • Prevent the xml2json() function cutting off of trailing characters in the resulting CLOB in case the document contains multi-byte characters
  • Support an optional properties file that defines the output format of the xml2json() function

Introduction

This article is the second in a series of three articles that illustrates how to build a three-tiered pureXML application that uses JavaScript or OpenSocial gadgets. By following the steps in this article, you will expose the JSON data described in the first article in the series, through JSON Universal Services.

JSONx is a canonical non-lossy XML representation for JSON which enables devices and software that support XML to operate with JSON. In the first article in the series, we provided an introduction to JSONx and illustrated how to store JSONx in a pureXML database. pureXML enables storing, indexing, and querying XML through languages such as SQL/XML, XQuery, and XPath.

Universal Services for pureXML are a simple but fixed set of database operations that allow the querying and modification of XML, stored in a pureXML column of a DB2® database. These database operations, which include insert, update, delete, and query, are exposed as Web Services, returning the data to the client as XML. The Universal Services are made available through straightforward configuration and deployment steps. For more information on Universal Services see the developerWorks article, "Universal Services for pureXML using Data Web Services" (see Resources for the link).

Frequently used terms

  • HTML: HyperText Markup Language
  • HTTP: Hypertext Transfer Protocol
  • JSON: JavaScript Object Notation
  • SOA: Service Oriented Architecture
  • URL: Uniform Resource Locator
  • WAR: Web ARchive files
  • XML: Extensible Markup Language

JSON Universal Services are similar to Universal Services, except they operate on JSONx data stored in a pureXML column. As mentioned earlier, the database operations include insert, update, delete, and query, however the data is returned to the client as JSON, instead of XML.

The JSON Universal Services ensure that JavaScript (and other) client applications are not aware that an XML store is used to persist JSON. The DB2 native XML capabilities can be leveraged with the persisted JSON objects, making it possible to view, query, and manipulate the data with declarative languages such as XPath, dramatically speeding up development time.

In this article, we cover the configuration, deployment, and testing of the JSON Universal Services on a JSONx store based on the DB2 sample database described in the first article in this series, "Build a pureXML and JSON application, Part 1: Store and query JSON with DB2 pureXML" (see Resources for the link).

If you need more details on these steps, please refer to the README.pdf document of the JSON Universal Services download package for fully illustrated step-by-step instructions. (After you unzip package, you will find README.pdf in the JSONUniversalServices directory.)

Prerequisites

Before installing the JSON Universal Services, you need to complete the installations described in the first article in this series. Run the JSONx bundle, which will create the database, and register the necessary Java™ user-defined functions (UDFs) in DB2. These UDFs enable converting JSON into JSONx and vice-versa. You also need to have DB2 9.5 or 9.7, Java Runtime Environment 1.5.0, and a Web server (such as WebSphere Application Server V6.x or Apache Tomcat V6.x), installed on your system. Note that the Java Runtime is part of the DB2 installation. If you have not installed these products, read the first article. For a link to the first article, see Resources.

In the first article in this series, you created a table, CUSTOMER, which you will use to install and test your JSON Universal Services. Table 1 shows an overview of the CUSTOMER table:

Table 1. Overview of the JSONXMLADMIN.CUSTOMER Table
Column name Data type Description
CID INTEGER Customer ID
INFOXML A record containing personal information about the customer
COMMENTVARCHAR(256)A small textual identifier regarding the customer

Now that you have the prerequisites installed and your table is created, you can configure and install the JSON Universal Services for a specific pureXML column.


Install the JSON Universal Services

This section assumes that you downloaded and extracted the JSON Universal Services package into a local, temporary, working directory. The directory for JSON Universal Services used throughout this article is C:\temp\JSONUniversalServices. You should see directories and files as shown in Figure 1.

Figure 1. Contents of the JSON Universal Services download package
Screen capture of the directories and files in the extracted JSON Universal Services package

The directories include classes, services, and war. The files are configure.bat, configure.sh, and README.pdf.

Once the necessary files and scripts are available on your system, you can continue with the installation of the JSON Universal Services, which basically consists of three steps:

  • Configure the JSON Universal Services to fit your local system setup.
  • Deploy the JSON Universal Services on your application server.
  • Test the JSON Universal Services.

Configure the JSON Universal Services

The configuration of the Web services is achieved through a script provided with the download called configure.bat (Linux® users: configure.sh). This script is modified to work with the JSONx bundle. Therefore, if you modified the JSONx bundle, or if you decide to use a different database, you need to modify the configure.bat script before executing it. You might need to adjust some parameters to fit your local system setup. Table 1 shows the parameters used in this configuration script.

Table 2. Overview of the parameters used in the configuration script
Parameter nameDescriptionValue (according to the sample database)
DB2INSTALLDIRPath to the directory of the DB2 installation. C:\Program Files\IBM\SQLLIB
DBSCHEMAName of the database schema that the table containing the XML column belongs to. JSONXMLADMIN
DBNAME Name of the database that the Universal Services are used with. JSONSMPL
DBTABLEName of the table that contains the XML column. CUSTOMER
DBPRIMARY Name of the primary column of the table specified in DBTABLE. This column can either be of type INTEGER or VARCHAR. CID
DBCOMMENT Name of the comment column of the table specified in DBTABLE. COMMENT
DBXML Name of the XML column of the table specified in DBTABLE. This is the column that contains XML data. INFO

Note that the values of the three parameters in Table 2 DBPRIMARY, DBCOMMENT, and DBXML, are the same as the column names in Table 1.

Figure 2 shows the configuration file, after it was edited according to the values provided in Table 2. (View a text version of Figure 2.)

Figure 2. Edit the configuration file configure.bat
Screen capture of edited configure.bat file

After the configure.bat (Linux users: configure.sh) is modified with the proper parameters, it is now possible to execute the script that performs the configuration of the JSON Universal Services. The script creates a Web application archive (WAR) file, that contains all the necessary files for the JSON Universal Services Web application, and which can be deployed to your application server. Remember that the script must be executed on the DB2 command line processor; otherwise, the execution fails.

Deploy JSON Universal Services

As described in the previous section, editing and executing the script configure.bat created a WAR file that you can now deploy to your application server. Prior to the deployment of the WAR file to the application server, it is necessary to configure a data source on the application server, which enables the Universal Services to access your DB2 database.

To set up the data source, follow the standard configuration process of your application server. The only requirement to consider during the setup is the name of the data source, which must meet the following pattern: jdbc/database name. The data source name for the example used in this article is jdbc/jsonsmpl. If you need further information about how to set up a data source, refer to the documentation for your application server or read the README.pdf document included in the JSON Universal Services download package (in the JSONUniversalServices directory).

The second step is to deploy the actual WAR file onto the application server. However, due to differences in the Web service artifacts required by WebSphere Application Server V6.x and Apache Tomcat V6.x, the configuration script creates two separate WAR files, one for each of the application servers.

The WAR files can be found in the subdirectory war, located in the JSON Universal Services working directory C:\temp\JSONUniversalServices. In particular, the directory contains two subdirectories: tomcat and was. The directory tomcat contains the WAR file to be installed on Apache Tomcat V6.x, whereas the directory was contains the WAR file to be installed on WebSphere Application Server V6.x. In order to install the WAR file on your application server, follow the standard procedure to do so. If more information is required, refer to the documentation of your application server or read the README.pdf document included in the JSON Universal Services download package.

It is now possible to test your set of Web services with the JSON Universal Services test page, which is already available through the deployment of the WAR file.


Test the JSON Universal Services

To ensure that the setup and configuration of the JSON Universal Services on your local system was successful, you need to test these services. The easiest way to test them is to use a simple HTML page, already available on your application server, as part of the deployment of the JSON Universal Services.

To access the JSON Universal Services test page, redirect your browser to the URL http://localhost:8080/JSONUniversalServices/testServices.html. Notice that the port 8080 is used if you installed it on Apache Tomcat. If you use WebSphere Application Server you must use the port 9080 to access the page. Note that, depending on your local system, you might need to adapt the hostname or port. You should see the page in Figure 3.

Figure 3. JSON Universal Services test page
Screen capture of the JSON Universal Services Test Page

This simple test page allows you to insert, update, delete, and query JSON data, stored as JSONx in your database. The Web services are invoked through the buttons provided on the test page. Figure 4 shows the Web service response, after we invoked the operation getPrimaryKeys through the button on the test page.

Figure 4. Response after Web service operation getPrimaryKeys is invoked
Screen capture of the JSON Universal Services Test Page

JSON documents were converted and inserted into the column as XML documents. You can retrieve these documents in both JSON and XML format.

For instance, by invoking the getJSONDocumentByKey method you will get the JSON representation of the document:

Listing 1. Customer information for Kathy Smith in JSON
{
  "customerinfo" : {
    "cid" : 1000 ,
    "name" : "Kathy Smith" ,
    "addr" : {
      "country"    : "Canada" ,
      "street"     : "5 Rosewood" ,
      "city"       : "Toronto" ,
      "prov-state" : "Ontario" ,
      "pcode-zip"  : "M6W 1E6"
    } ,
    "phone" : {
      "work" : "416-555-1358"
    }
  }
}

Alternatively, if you invoke the getXMLDocumentByKey method you will get the XML document (JSONx format) as it is stored on your DB2 pureXML database:

Listing 2. Customer information for Kathy Smith in JSONx
<json:object xmlns:json="http://www.ibm.com/xmlns/prod/2009/jsonx">
  <json:object name="customerinfo">
	<json:number name="cid">1000</json:number>
	<json:string name="name">Kathy Smith</json:string>
	<json:object name="addr">
	  <json:string name="country">Canada</json:string>
	  <json:string name="street">5 Rosewood</json:string>
	  <json:string name="city">Toronto</json:string>
	  <json:string name="prov-state">Ontario</json:string>
	  <json:string name="pcode-zip">M6W 1E6</json:string>
	</json:object>
	<json:object name="phone">
	  <json:string name="work">416-555-1358</json:string>
	</json:object>
  </json:object>
</json:object>

Furthermore, this page provides other services such as insert, update, delete, and XML query. Please read the following section and the instructions on the test page for more information.

More Information about the JSON Universal Services operations

In this section, we include Table 3 that describes the parameters, their encodings, and the MIME types associated with the operations in the JSON Universal Services. The operations are available through HTTP GET, HTTP POST with URL-encoded and plain text mime types.

  • For operations getDocumentByKey and getXMLDocumentByKey you can specify an id and retrieve the corresponding stored data in JSON or in XML respectively.
  • For operations insert and update you can specify an id for a JSON document to be inserted or updated, together with the new document. If the request is successful you will receive an update count of 1. Otherwise, you will receive an update count of 0.
  • For operation delete you can specify an id for a document to be deleted from the pureXML store. If the delete request is successful you will receive an update count of 1. Otherwise, you will receive an update count of 0.
  • For operation runXMLQuery, you can supply an XQuery or input to an XMLQUERY (part of SQL/XML) to retrieve XML content that can span across many stored documents. See the test page for some examples.
Table 3. Overview of the JSON Universal Services operations
OperationInput ParameterHTTP GET HTTP POST MIME_TYPE application/x-www-form-urlencoded HTTP POST MIME-TYPE text/plain, application/jsonResponse
getDocumentByKeyidid: parameter/value URL-encoded in query stringid: parameter/value URL-encoded in messageid: parameter/value URL-encoded in query string JSON document (text/plain)
getXMLDocumentByKeyidid: parameter/value URL-encoded in query stringid: parameter/value URL-encoded in messageid: parameter/value URL-encoded in query string JSONx representation of the document (text/xml)
insertid (unique)
doc (JSON Document)
id: parameter/value URL-encoded in query string
doc: parameter/value URL-encoded in query string
id: parameter/value URL-encoded in message
doc: parameter/value URL-encoded in message
id: parameter/value URL-encoded in query string
doc: POST message content
Update count (text/plain)
updateid
doc (JSON Document)
id: parameter/value URL-encoded in query string
doc: parameter/value URL-encoded in query string
id: parameter/value URL-encoded in message
doc: parameter/value URL-encoded in message
id: parameter/value URL-encoded in query string
doc: POST message content
Update count (text/plain)
deleteidid: parameter/value URL-encoded in query stringid: parameter/value URL-encoded in messageid: parameter/value URL-encoded in query string Update count (text/plain)
runXMLQueryquery
(XQuery or input to XMLQUERY)
query: parameter/value URL-encoded in query stringquery: parameter/value URL-encoded in messagequery: POST message content XML document with results (text/xml)

Conclusion

This article gave you an introduction of JSON Universal services, which covered the configuration, deployment, and testing of the JSON Universal Services on a JSONx store based on the DB2 sample database.

The first article in this series focused on the introduction of the new format JSONx, enabling the storage of JSON as XML in the DB2 pureXML database. It also discussed a use case for JSON and outlined the benefits of using pureXML to store JSON.

The next article in this series will focus on the creation of the presentation layer with Open-Social Gadgets that rely on the JSONx Universal Services as a back-end.


Download

DescriptionNameSize
JSON Universal Services packageJSONUniversal Services.zip1927KB

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 XML on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=XML, Information Management, SOA and web services
ArticleID=439049
ArticleTitle=Build a pureXML and JSON application, Part 2: Create Universal Services for pureXML that expose JSON
publish-date=04272010