Contents


Build a DB2 for z/OS mobile application using DB2 Adapter for z/OS Connect, Part 1

Expose DB2's functionality

Content series:

This content is part # of # in the series: Build a DB2 for z/OS mobile application using DB2 Adapter for z/OS Connect, Part 1

Stay tuned for additional content in this series.

This content is part of the series:Build a DB2 for z/OS mobile application using DB2 Adapter for z/OS Connect, Part 1

Stay tuned for additional content in this series.

Mobile is a pervasive tool that most people keep within reach almost constantly. To stay competitive in the current marketplace, your company needs to take advantage of mobile technology. In this series, learn how to build a DB2 for z/OS mobile application by using DB2 Adapter for z/OS Connect and IBM MobileFirst Developer Edition. In Part 1, discover how to expose DB2 for z/OS functionality by using DB2 Adapter for z/OS Connect.

About the products

DB2 for z/OS is commonly used to store critical transaction data. Large numbers of data transactions in banking, retail, insurance, and government occur on DB2 for z/OS because of its quality service, high availability, security, and performance.

IBM MobileFirst Platform provides an integrated development and testing environment for mobile applications. The development environment combines multiple set of tools, frameworks, and code bases into one single development environment, giving users one code base to develop and maintain.

z/OS Connect provides a common way to interact with all z/OS business and infrastructure assets using REST and JSON technology. It shields the front end from awareness of any data transformation that is required to access the back-end systems. At the same time, it also shields the back-end system from requiring awareness of RESTful URIs and JSON.

Figure 1 is a simplified architecture of the mobile solution we are going to implement. The mobile application accesses the HTTP Adapter in MobileFirst, which calls a REST API (exposed by DB2 Adapter for z/OS Connect) to do a SELECT in DB2 for z/OS.

Figure 1. Simplified architecture of a mobile solution
How data might move, transparently between storage tiers
How data might move, transparently between storage tiers

The scenario in this article retrieves a string from string ID from the SYSIBM.SYSXMLSTRINGS table using a regular SQL statement. SYSIBM.SYSXMLSTRINGS is a catalog table that contains mapping between the string and string ID that are used inside XML storage. Listing 1 is a sample output of SYSIBM.SYSXMLSTRINGS catalog table (you may see different values in your DB2 system).

Listing 1. Sample output of SYSXMLSTRINGS catalog table
SELECT STRINGID, SUBSTR(STRING,1, 35) AS STRING
FROM SYSIBM.SYSXMLSTRINGS
STRINGID       STRING 
1001           product 
1002           description 
1003           name 
1004           detail
1005           http://www.w3.org/2000/xmlns/ 
1006           space 
1007           http://posample.org 
1008           pid 
1009           details 
1010           price 
1011           weight

Introducing DB2 Adapter for z/OS Connect

Figure 2 illustrates the architecture of z/OS Connect and the workflow of a request to z/OS Connect (modified from a figure in IBM Knowledge Center).

Figure 2. z/OS Connect architecture
the architecture of z/OS Connect
the architecture of z/OS Connect

In short, z/OS Connect is a servlet inside WebSphere® Liberty Profile. It accepts RESTful URIs with JSON payload, transforms data for the back-end system (in this case, DB2 for z/OS), sends the request to DB2, and sends the result back to the client. It also provides two important services:

  • Interceptors service provides a way to pre-invoke work and post-invoke work, for example, System Authorization Facility (SAF) for access control and System Management Facility (SMF) for auditing.
  • Discovery service allows a user to query for a list of configured services and details on a given configured service.

DB2 Adapter for z/OS Connect provides the services between z/OS Connect and DB2 for z/OS. It allows developer to access DB2 for z/OS for invoking SQl statements and stored procedures.

Software requirements

To run this scenario, you need the following software:

  • DB2 10 for z/OS or later
  • DB2 Adapter for z/OS Connect (bundled in DB2 Accessories Suites V3.3). Learn more.
  • IBM Data Studio client 4.1.2 or later
  • REST client for your browser (optional, but recommended)

Verify the installation of DB2 Adapter for z/OS Connect

Suppose you have created a server called test under WLP_USER_DIR(=/tmp/usr) using the db2AdapterServer template. The /tmp directory is temporary and is deleted following an initial program load (IPL), but it's useful for development and unit testing purposes. If you want to create and configure a persistent WebSphere Liberty Profile (WLP) server, you may want to use something like: WLP_USER_DIR=/u/db2wlp/usr.

The following command creates a new Library Profile server-called test using the db2AdapterServer template.

server create test --template=db2zAdapterServer

In this scenario, we use

  • sampleBasicSSLUsingDefaultCert.xml as sslServerCertificate.xml
  • sampleBasicZOSConnectAuthentication.xml as zosConnectAuthentication.xml (updated with appropriate user name and password)
  • sampleBasicZOSConnectAuthorization.xml as zosConnectAuthorization.xml (updated with authorized user name)

For simplicity, we use a list of user IDs and passwords and a Liberty Profile-generated server certificate for SSL encryption for authentication. SAF is not used here.

Below is my DB2 info inside db2zAdapterDataSources.xml:

Listing 2. DB2 information inside db2zAdapterDataSources.xml
<db2zadapter_db2zAdapterDataSource
id="db2zAdapterDataSource"
databaseName="STLEC1"
serverName="dtec207.vmec.svl.ibm.com"
portNumber="446"
driverType="4" 
user="sysadm"
password="mypassword"
trustedContext="false"
maxPoolSize="40"
maxStatements="0"
accountingInterval="COMMIT"
/>

After you have successfully started your server in /tmp/usr/servers/test/logs/console.log you should see something similar to this:

[AUDIT   ] CWWKT0016I: Web application available (default_host): http://dtec207.vmec.svl.ibm.com:9180/

If you access http://dtec207.vmec.svl.ibm.com:9180/zosConnect/services using a browser, you may see something like the following. This is the default deployment service. Make a note about the deployment services HTTS port (in this case, 9443) as you will need this information to configure the web services using IBM Data Studio Client.

Listing 3. Default deployment information
{"zosConnectServices":[{
"ServiceName":"DB2zAdapterDeploymentService",
"ServiceDescription":"Deploy a new DB2 Adapter service or delete an existing DB2 Adapter
service",
"ServiceProvider":"db2zadapter-1.0",
"ServiceURL":
"https://dtec207.vmec.svl.ibm.com:9443/zosConnect/services/
DB2zAdapterDeploymentService"}]

Set up DB2 Adapter services using IBM Data Studio Client

IBM Data Studio client provides development tools to create, deploy, or undeploy a DB2 Adapter service. Data Studio 4.1.2 (and later) supports DB2 Adapter for z/OS Connect. I am using Data Studio client 4.1.2 in this article.

If you want to start or test a DB2 Adapter service using your browser, you need to install a REST client for your browser.

Create a connection to z/OS Connect Server

1. Launch Data Studio client and open the IBM SQL and Routine Development perspective by clicking Open Perspective in the top right corner.

Figure 3. Open perspective
open perspective
open perspective

2. Click IBM SQL and Routine Development in the Open Perspective GUI.

Figure 4. Open perspective GUI
open perspective
open perspective

The perspective opens with the Data Project Explorer (DPE) view in the upper left of the GUI and the Data Source Explorer (DSE) view in the lower left of the GUI.

3. In DSE, right-click Database Connections and select New.

4. In the New Connection GUI, complete the following steps:

  1. Under Connection identification, check Use default naming convention.
  2. Under the Local tab, select DB2 for z/OS.
  3. For JDBC driver, select IBM Data Server Driver for JDBC and SQLJ (JDBC 4.0) Default.
  4. Under Properties > General tab, fill out the database information that match those specified in db2zAdapterDataSources.xml.
Figure 5. New connection GUI
new connection gui
new connection gui

5. Click the Test Connection button. You should see a pop-up that says "Connection succeeded."

6. Check z/OS Connect Server (located at the bottom part of the New Connection GUI), then click the Next button.

7. In the z/OS Connect Server Parameters GUI shown in figure 6, complete these steps:

  1. Check z/OS Connect Server.
  2. Fill out the HTTP port number of Liberty Profile server, user name, and password.
  3. Click the Finish button.
Figure 6. z/OS Connect Server parameters
z/OS Connect Server        Parameters
z/OS Connect Server Parameters

8. Under Data Source Explorer, you should see that a new database connection (in this case, STLEC1) is created.

Figure 7. New database connection
new database connection
new database connection

Creating a web service

1. In Data Project Explorer, select Click to create a new data development project, as shown below.

Figure 8. Create new data development project
Create new data development project
Create new data development project

2. In the New Data Development Project, specify a Project name (Project1) and then click the Next button.

Figure 9. New data development project
New data development project
New data development project

3. Under Select Connection Profile, specify the connection you just created (that is, STLEC1). Then click Finish button.

Figure 10. Select connection profile
Select the connection profile
Select the connection profile

A new project called Project1 should be created under the Data Project Explorer.

4. Expand Project1 by right-clicking Web Services and selecting New Web Service.

5. In the New Web Service GUI, select a Project (Project1), and specify a web service name (GetXMLString). Then click Finish.

Figure 11. Define a new web service
Define a new web service
Define a new web service

A new web service called GetXMLString should be created under the Web Services folder.

6. Under Project1, right-click SQL Scripts and then select New > SQL or XQuery

7. In New SQL or XQuery Script complete the following steps:

  1. Enter a script name(GetXMLString).
  2. Select SQL Query builder.
  3. Click Finish button.
Figure 12. New SQL or XQuery tool
new SQL or XQuery tool
new SQL or XQuery tool

8. The editor should be open. Enter the following SQL statement:

SELECT SUBSTR(STRING, 1, 60) as STRING
FROM SYSIBM.SYSXMLSTRINGS WHERE STRINGID= ?

9. To run this SQL statement, right-click GetXMLSting.sql > Run SQL.

10. Suppose you want to find the XML string for string ID equals 1006, in Specify Host Variable Values, enter 1006. Click Finish.

Figure 13. Specify host VariableValues
specify host variable values
specify host variable values

You should see the result in the lower right side, under the SQL results tag. In the following screenshot, the string for string ID 1006 is "space". Note, depending on the mapping in SYSIBM.SYSXMLSTRINGS table, you may see a different result.

Figure 14. SQL results
How data might move, transparently between storage tiers
How data might move, transparently between storage tiers

11. To associate a SQL script with the web service, drag and drop the new SQL script (GetXMLString.sql) onto GetXMLString* (under Web Services). After this action, you should see GetXMLString under Web Services>GetXMLString*, as in Figure 15.

Figure 15. Creating web services using SQL script
The GetXMLString should be uner GetXMLString
The GetXMLString should be uner GetXMLString

Deploy a web service

To deploy the new service, right-click Web Services>GetXMLString* and then select Deploy on z/OS Connect Server.

For a successful deployment, you should see a pop up: The service 'GetXMLString' is deployed successfully.

Also, you may find the following entries in /tmp/usr/servers/test directory.

Listing 4. Entries in the /tmp/usr/servers/test directory
-rw-r----- 1 SYSADM OMVSGRP  438 Dec 20 10:49 db2zAdapterUserDefinedServices.xml   
drwxr-x--- 2 SYSADM OMVSGRP 8192 Dec 20 10:49 db2zAdapterServicesBackup
-rw-r--r-- 1 SYSADM OMVSGRP 1349 Dec 20 10:49 GetXMLStringConfig.xml
  • GetXMLStringConfig.xml is the configuration file for the new service. db2zAdapterUserDefinedServices.xml is updated to contain the new web service information.
  • db2zAdapterServicesBackup is a directory which contains a backup version of db2zAdapterUserDefinedServices.xml.
  • If there is an existing service with the same name, the existing configuration will be backed up to db2zAdapterServicesBackup directory as well.

Test and query a DB2 Adapter service

DB2 Adapter uses z/OS Connect to authorize and authenticate users to perform specific tasks. If you test and query a DB2 Adapter service using a browser or REST client, you will be prompted to provide a user name and password. You may also need to accept the SSL certificate from the DB2 Adapter Service.

1. In a browser, access https://dtec207.vmec.svl.ibm.com:9443/zosConnect/services. You will see information about GetXMLString service (highlighted below) that we just created and deployed. You may also see a pop-up for a unrecognized SSL certificate from DB2 Adapter service.

Listing 5. Information about GetXMLString service
{
"zosConnectServices": [{
"ServiceName": "DB2zAdapterDeploymentService",
"ServiceDescription": "Deploy a new DB2 Adapter service or delete an existing DB2 Adapter service",
"ServiceProvider": "db2zadapter-1.0",
"ServiceURL": "https://dtec207.vmec.svl.ibm.com:9443/zosConnect/services/DB2zAdapterDeploymentService"
}, {
"ServiceName": "GetXMLString",
"ServiceDescription": "Get XML string from string id",
"ServiceProvider": "db2zadapter-1.0",
"ServiceURL": "https://dtec207.vmec.svl.ibm.com:9443/zosConnect/services/GetXMLString"
}]
}

2. To retrieve details about the GetXMLString service, access the following URL using a browser: https://dtec207.vmec.svl.ibm.com:9443/zosConnect/services/GetXMLString.

You may see the following output (partial reformat for readability).

  • Under RequestSchema, you should see the schema for the input parameter, which is called "1", either null or an integer.
  • Under ResponseSchema, you should find the schema for the output. The output parameter is an object called Result Output of array type.
Listing 6. Output for GetXML String service
{"zosConnect":{
"serviceName":"GetXMLString",
"serviceDescription":"Get XML string from string id",
"serviceProvider":"db2zadapter-1.0",
"serviceURL":"https://dtec207.vmec.svl.ibm.com:9443/zosConnect/services/GetXMLString",
"serviceInvokeURL":"https://dtec207.vmec.svl.ibm.com:9443/zosConnect/services/
GetXMLString?action=invoke",
"dataXformProvider":"DATA_UNAVAILABLE"},
"GetXMLString":{
"DB2zAdapterServiceName":"GetXMLString",
"DB2zAdapterServiceStatus":"Stopped",
"DB2zAdapterService":{
"RequestSchema":{
"$schema":"http://json-schema.org/draft-04/schema#",
"type":"object",
"properties":{
"1":{
"type":["null","integer"],
"multipleOf":1,
"minimum":-2147483648,
"maximum":2147483647,
"description":"Nullable INTEGER"
}
},
"required":["1"],
"description":"Service GetXMLString invocation HTTP request body"
},
"ResponseSchema":{
"$schema":"http://json-schema.org/draft-04/schema#",
"type":"object",
"properties":{
"ResultSet Output":{
"type":"array",
"items":{
"type":"object",
"properties":{
"STRING":{
"type":"string",
"description":"CHARACTER"
}
},
"required":["STRING"],
"description":"ResultSet Row"
}
},
"StatusDescription":{
"type":"string",
"description":"Service invocation status description"
},
"StatusCode":{
"type":"integer",
"multipleOf":1,
"minimum":100,
"maximum":600,
"description":"Service invocation HTTP status code"
},
"DiagnosticsCodes":{
"type":"array",
"items":{
"type":"integer",
"multipleOf":1,
"minimum":-99999,
"maximum":99999,
"uniqueItems":true,
"description":"Service invocation diagnostic code"
}
}
},
"required":["ResultSet Output","StatusDescription","StatusCode"],
"description":"Service GetXMLString invocation HTTP response body"
}
}
}
}

3. To query the status of a GetXMLString service, access the following url using a browser: https://dtec207.vmec.svl.ibm.com:9443/zosConnect/services/GetXMLString?action=status

You may see the following output:

Listing 7. Query the status of a GetXMLString service
{
"zosConnect": {
"serviceName": "GetXMLString",
"serviceDescription": "Get XML string from string id",
"serviceProvider": "db2zadapter-1.0",
"serviceURL": "https://dtec207.vmec.svl.ibm.com:9443/zosConnect/services/GetXMLString",
"serviceInvokeURL": "https://dtec207.vmec.svl.ibm.com:9443/zosConnect/services/
GetXMLString?action=invoke",
"dataXformProvider": "DATA_UNAVAILABLE",
"serviceStatus": "Stopped"
}
}

4. To start a GetXMLString service, you need to send a POST request through a REST client.

POST https://dtec207.vmec.svl.ibm.com:9443/zosConnect/services/GetXMLString?action=start

(Make sure you use https and secure port 9443, http with port 9180 cannot start a DB2 Adapter service.)

You may see the following output:

Listing 8. Start a GetXMLString service
{
"zosConnect": {
"serviceName": "GetXMLString",
"serviceDescription": "Get XML string from string id",
"serviceProvider": "db2zadapter-1.0",
"serviceURL": "https://dtec207.vmec.svl.ibm.com:9443/zosConnect/services/GetXMLString",
"serviceInvokeURL": "https://dtec207.vmec.svl.ibm.com:9443/zosConnect/services/
GetXMLString?action=invoke",
"dataXformProvider": "DATA_UNAVAILABLE",
"serviceStatus": "Started"
}
}

5. To invoke a GetXMLString service, you need to send a POST request through a REST client, as Figure 16 shows.

POST https://dtec207.vmec.svl.ibm.com:9443/zosConnect/services/GetXMLString?action=invoke

Specify the following HTTPS header fields:

  • For the Accept field, type application/json.
  • For the Content-Type field, type application/json.
  • For the Accept-Charset field, type UTF-8.

To find the XML string for "string ID equals 1006", specify that in the HTTPS request body, as figure 16 indicates.

Figure 16. Invoke web service using browser REST client
Invoke web service using browser REST client
Invoke web service using browser REST client

You may see the following output (reformatted for readability). From the following result, the XML string for string ID 1006 is "space".

Listing 9. Output for string ID request
{"ResultSet Output":
[{"STRING":"space"}],
"StatusDescription":"Execution Successful",
"StatusCode":200
}

Generating your own SSL Certificate

If the CN name of your SSL certificate is localhost, you may want to generate a SSL certificate with actual hostname of your DB2 server. You can do so using the securityUtility command.

  1. Stop your server (in my case: server stop test).
  2. Rename /tmp/usr/servers/test/resources/security/key.jks (if it is already exists).
  3. Issue the securityUtility command with following format:
Listing 10. Generate a SSL certificate with the hostname of your DB2 server
securityUtility createSSLCertificate --server=<server_name>
--password=<password> --validity=365
--subject=CN=<hostname>,O=<company>,C=<country>

In my case, the command looks like:

Listing 11. Sample example of SSL certificate generation
securityUtility createSSLCertificate --server=test
--password=mypassword --validity=365
--subject=CN=dtec207.vmec.svl.ibm.com,O=IBM,C=US

You may see the following output:

  • The certificate will be created with alias default.
  • The key algorithm is RSA and signature algorithm is SHA1withRSA.
  • Created SSL certificate for server test.
  • The certificate is created with CN=dtec207.vmec.svl.ibm.com,O=IBM,C=US as the SubjectDN.

To enable SSL, add the following lines to the server.xml.

Listing 12. Enable SSL
 <featureManager> 
  <feature>ssl-1.0</feature> 
 </featureManager> 
<keyStore id="defaultKeyStore" password="{xor}PG87Oiw3MC8=" />

4. Update/tmp/usr/servers/test/sslServerCertificate.xml with new password (in this example, {xor}PG87Oiw3MC8=)

Summary

This article shows you how to create, deploy, query, start, and test a DB2 Adapter service. Hopefully you were able to follow along. In Part 2, we will show how to invoke this REST API from a mobile application.

Acknowledgments

Thanks to Tom Toomire and Xavier Yuen for their comments and assistance with this paper.


Downloadable resources


Related topics

static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=1027695
ArticleTitle=Build a DB2 for z/OS mobile application using DB2 Adapter for z/OS Connect, Part 1: Expose DB2's functionality
publish-date=02022016