Level: Introductory Susan Malaika, Senior Technical Staff Member , IBM
Dirk Hain, Computer Science Student, Humboldt University, Berlin (Germany), IBM
14 Aug 2003 Are you interested in experimenting with grid technology with DB2 UDB? This article contains instructions for setting up and accessing IBM DB2 Universal Database through the open source grid technologies OGSA-DAI and Globus Toolkit.
© 2003 International Business Machines Corporation.
All rights reserved. Introduction Grid computing makes it possible to access computing resources in a flexible way without awareness of certain physical aspects of those resources, such as location and platform [1] [2]. The word "grid" is defined as an infrastructure consisting of multiple computers connected via network technologies providing the impression of one computer system. The goal of grid computing is the secure and straightforward sharing of applications, data and computational power, providing easy service like access to the infrastructure no matter which platform is being used. A systems architecture is evolving called the Open Grid Service Architecture (OGSA) to identify the components needed in a grid system. OGSA defines a service-based structure for creating a grid computing environment. See [3] for a description of the mechanisms and interfaces defined in OGSA. Because the goal of Grid is the provision of computational power as a service like electricity, available for users whenever required, standards comparable to the voltage of electric power and switches must be defined. Standardizing the interfaces among components of a grid system takes place in the Global Grid Forum (GGF). Open source implementations, such as the Globus Toolkit®. One of the evolving standards in the area of data on the grid is called Data Access and Integration (DAIS). The goal of the DAIS specification is to describe how to access existing data resources in a grid environment. The Open Grid Service Architecture - Data Access and Integration (OGSA-DAI) is a framework that enables existing data resources, such as relational and XML databases, to be integrated into a grid environment. As Figure 1 illustrates, SOAP messages are used for the communication between an OGSA-DAI client and server. OGSA-DAI supports the DAIS specification as it evolves. As well as accessing and updating data in a database, OGSA-DAI offers an extensibility mechanism, making it possible to add further user defined activities to OGSA-DAI that can be executed in addition to activities already offered by OGSA-DAI, such as SQL query and update.
Figure 1. OGSA-DAI overview
This article provides a brief explanation of how to access an IBM® DB2® Universal DatabaseTM (DB2 UDB) database through OGSA-DAI on the Windows® platform. The description includes the software prerequisites needed, the OGSA-DAI configuration for DB2 UDB, the use of the OGSA-DAI end-to-end sample client to retrieve and update DB2 UDB data through OGSA-DAI perform requests. Several papers referenced in this document are available on the OGSA-DAI Web site, where you can find additional information on OGSA-DAI and on grid computing.
Overview of the configuration After following the instructions in this article, you will have the following components running on the server side of your system:
- OGSA-DAI running in a Web service container such as Apache Jakarta Tomcat. As illustrated in Figure 2, OGSA-DAI has three major interfaces:
- Grid Data Service Registry (GDSR). The registry enables applications to dynamically locate databases they are interested in. We won't use the registry explicitly in the examples in this article, although the end-to-end sample client will access the registry.
- Grid Data Service Factory (GDSF). The factory enables the creation of Grid Data Services to access and update databases. We will configure the factory in this article to include the name of the databases that will be accessed and the user ID and password that OGSA-DAI will use to access DB2 UDB databases.
- Grid Data Service (GDS). A data service enables databases to be accessed through the grid infrastructure.
- The Globus Toolkit. Grid middleware that OGSA-DAI relies on for various services, including Apache XML Axis handlers.
On the client side you'll have the OGSA-DAI sample client and the Globus Toolkit. You will use the sample client to test your installation and configuration, and to access DB2 UDB. The sample client sends the following XML documents to the OGSA-DAI server: - An OGSA-DAI create document to create a Grid Data Service for accessing the appropriate DB2 database via OGSA-DAI.
- An OGSA-DAI perform document to execute SQL queries and updates in DB2 via OGSA-DAI, and to receive responses back. Each SQL operation is called an activity. This article describes two types of SQL activities:
- SQLQueryStatementActivity
- SQLUpdateStatementActivity
Other kinds of activities are supported but they are beyond the scope of this article (although we do refer to DeliverFromURLActivity and DeliverToResponseActivity in the examples). A perform document can contain multiple SQL operations, but in this article we will just use one operation. You will write the create documents to identify the databases to be accessed through OGSA-DAI. Your perform documents contain the SQL you want to run, such as an SQL select. You don't need to write any database connect statements. OGSA-DAI executes the appropriate database connection for you behind the scenes when creating the Grid Data Service. The output from a SQL select request is formatted as XML in the WebRowSet format. You don't need to iterate through the results row by row because OGSA-DAI returns all the rows together. You will see some examples of perform document operations and results later in this article. All the SQL requests in one perform document execute in a separate transaction (unit of recovery). You can set up both the client and server software on one machine. The initial verification of the installation and configuration described in this article assumes a single system. Figure 2 illustrate the possibility of OGSA-DAI sending a response to a third party. We do not discuss that option further in this article.
Figure 2. The OGSA-DAI process
To gain a more detailed view of the actions taking place within OGSA-DAI, look at Figure 2 and Figure 3 together. The numbers in Figure 2 represent the sequence in which actions are executed: - Communicating with the registry (GDSR) and requesting the handle of a GridDataServiceFactory (GDSF).
- Calling the factory (GDSF), which creates the GridDataService (GDS) and returns the handle for it.
- Calling the GDS and submitting the request in a perform document. The GDS executes the query against the database and returns the result to the client.
Figure 3 shows the GridDataService using JDBC to connect to the database on behalf of the client. For more information about the whole OGSA-DAI process see [5].
Figure 3. Actions within OGSA-DAI
Setting up OGSA-DAI to access DB2 UDB This section describes the software prerequisites and the main configuration changes to run OGSA-DAI in conjunction with a DB2 database system: Install the prerequisite software Use the following software: The example code provided here was run on a Windows 2000 machine with GT3 beta. To install the GT3 beta and OGSA-DAI, follow the installation manual provided with OGSA-DAI 2.5 (INSTALL.TXT). To install DB2 UDB, follow the instructions provided with DB2 UDB. To run the examples in this article, install the sample database. You'll also need to install Apache Jakarta Tomcat. To access DB2 via OGSA-DAI, the DB2 JDBC driver must be present for OGSA-DAI. You can find the JDBC driver in the DB2 installation directory within <DB2_basedir>\SQLLIB\java. To provide the driver for OGSA-DAI, place the three files (db2jcc.jar, db2jcc_license_cisuz.jar, db2jcc_license_cu.jar) in the <Tomcat>\webapps\ogsa\WEB-INF\lib directory where all libraries are located that OGSA-DAI needs. (See OGSA-DAI INSTALL.TXT for the location of this directory.) After checking that Apache Tomcat, GT3, OGSA-DAI and DB2 are installed properly (see OGSA-DAI INSTALL.TXT for instructions), you should make the DB2-specific modifications in the OGSA-DAI configuration files according to the code provided in Appendix A. The following section explains the modifications. Configure OGSA-DAI to work with DB2 UDB The configuration directory mentioned in the following steps refers to the directory <TOMCAT>\webapps\ogsa\WEB-INF\etc. All files described in this section should be placed there. Step 1: Update gdsf_config.xml to tell OGSA-DAI about the DB2 database to be accessed
For each DB2 UDB database you want to access through OGSA-DAI, add a new dataResource entry in the OGSA-DAI gdsf_config.xml configuration file. If you want to access just the DB2 UDB sample database through OGSA-DAI, and if the DB2 UDB system is local to your Apache Tomcat system, you can take the file provided in gdsf_config.xml replacing the supplied gdsf_config.xml in the OGSA-DAI configuration directory. Note: Replace the "configuration" attribute of tag <roleMap> with the absolute path to your ExampleDatabaseRoles.xml file. If you want to access more than one DB2 UDB database, you will need several entries in gdsf_config.xml. You can add a dataResource entry for each database to the supplied gdsf_config.xml. If you change the DB2 version, or run the DB2 system on a separate machine from Apache Jakarta Tomcat, or if you want OGSA-DAI to use a special JDBC driver, you will need to modify some sections of this configuration file. For information about how to adapt gdsf_config.xml to your environment, see [5]. Copy the gdsf_config.xml file to the configuration directory. Step 2: Update dataResourceImplementationMap.xml to tell OGSA-DAI which class manages the DB2 access
The dataResource you just added to gdsf_config.xml must be reflected in dataResourceImplementationMap.xml. Important: The set of dataResources mentioned in gdsf_config.xml must be equal to that in dataResourceImplementationMap.xml; otherwise, OGSA-DAI will throw a NullPointerException where the reason is not easily diagnosed. The Appendix contains an example of a dataResourceImplementationMap.xml file that corresponds to gdsf_config.xml. Copy the dataResourceImplementationMap.xml file to the configuration directory. Be careful if you cut and paste this code as there may appear some hidden HTML content in your XML file, especially at line breaks that cause trouble not easy to retrieve. Step 3: Provide a userid and password to access DB2 UDB
Finally for configuring the system, you must supply the userid information for the GridDataService to use access the DB2 database on your behalf. You will need to update the ExampleDatabaseRoles.xml file. See the example shown in ExampleDatabaseRoles.xml where <validUser> and <validPassword> have to be replaced with concrete values for the DB2 database you want to access. Exactly one userid and one password are required for each database. Copy the ExampleDatabaseRoles.xml file to the configuration directory. Step 4: Provide a create document and a perform document
For testing your installation and configuration, you need a create document and a perform document: Verify the installation and configuration After following steps 1-4, your server system should be set up. Now you are ready to verify your installation. Copy files sentenv.bat and setcp.bat coming with your OGSA-DAI distribution (see your OGSA-DAI root folder) into <TOMCAT>\webapps\ogsa\WEB-INF and open a DOS shell in this directory. Run setenv.bat to set your classpath. Then try executing the simple SELECT example using the end-to-end sample client by typing the following command in the same DOS shell, which assumes the Apache Jakarta Tomcat server is running on port 8080. The first parameter refers to the OGSA-DAI registry, the second parameter refers to the create document, and the third parameter refers to the perform document. Listing 1. Executing a SELECT statement using the OGSA-DAI sample client
java uk.org.ogsadai.client.Client \\
http://localhost:8080/ogsa/services/ogsadai/DAIServiceGroupRegistry \\
C:/<Tomcat>/webapps/ogsa/WEB-INF/etc/gdsfCreateExample.xml \\
C:/<Tomcat>/webapps/ogsa/WEB-INF/etc/PerformSimpleSelect.xml |
If you see some results formatted in XML and containing the section visible in Figure 4 after executing the command above, then your installation and configuration succeeded.
Figure 4. DOS shell with data section of response document
Troubleshooting If OGSA-DAI cannot access DB2 UDB, check INSTALL.TXT for troubleshooting and recheck the specific configuration steps: - Modify
gdsf_config.xml (configure the dataResource used). - Modify
dataResourceImplementationMap.xml. (The set of dataResources in gdsf_config.xml and dataResourceImplementationMap.xml must be consistent.) - Modify
ExampleDatabaseRoles.xml to your user ID information. - Modify
gdsfCreateExample.xml to refer to the DB2 dataResource.xml (specified in gdsf_config.xml). - Copy
gdsf_config.xml, dataResourceImplementationMap.xml,
ExampleDatabaseRoles.xml, gdsfCreateExample.xml and
PerformSimpleSelect.xml into the configuration directory.
Configuring the OGSA-DAI client for DB2 UDB After you test your installation, you can continue testing by running more examples on the OGSA-DAI server, or you can set up a client system with the OGSA-DAI sample client and Globus Toolkit. You will also need create documents and perform documents in a client system directory. Create documents and perform documents can easily be adjusted to access another database. The next section shows some examples of using OGSA-DAI with DB2 UDB. All examples access the DB2 UDB sample database and assume that you are testing on the server system.
Examples The following sections provide examples of using OGSA-DAI with DB2 to help you understand how to use OGSA-DAI for accessing a DB2 database. These examples are tested on DB2 V8.1.2 with the DB2 JCC JDBC driver. Setup instructions and configuration files are in included with this article. Selecting data from DB2 For SQL query requests, OGSA-DAI provides an activity called SQLQueryStatementActivity. To run a query statement, provide a perform document that includes the query to execute within a SQLQueryStatement activity. For the invoking client to receive the results of the query, provide a deliverToResponse activity in the same perform document. The perform document can be invoked from a JavaTM program or by using the end-to-end client that comes with OGSA-DAI. See PerformSimpleSelect.xml for an example perform document to select data from the DB2 sample database. The query appears within the <expression> tags with the SQLQueryStatement activity. The <deliverToResponse> tag states that the query result is delivered by the corresponding OGSA-DAI response document. The end-to-end sample client then sends the query result to the DOS command line when the activity is finished. The stream mapping that pipes the query result from the SQLQueryStatement to the response document is expressed by having the same value of the 'name' attribute of the <webRowSetStream> tag within the SQLQueryStatement activity, and in the 'from' attribute of <fromLocal> tag within the deliverToResponse activity. The query can be executed with the end-to-end client by using the command shown in Listing 2 in a DOS shell. Using a tracing mechanism, you can execute the query stepwise to observe every step that OGSA-DAI runs. You can observe the steps by setting the -demo flag at the end of the uk.org.ogsadai.client.Client invocation as shown in Listing 2. For further details on how to use the end-to-end sample client, see INSTALL.TXT and [4]. Listing 2. Command to execute the OGSA-DAI sample client
java uk.org.ogsadai.client.Client -demo \\
http://localhost:8080/ogsa/services/ogsadai/DAIServiceGroupRegistry \\
C:/<Tomcat>/webapps/ogsa/WEB-INF/etc/gdsfCreateExample.xml \\
C:/<Tomcat>/webapps/ogsa/WEB-INF/etc/PerformSimpleSelect.xml
|
Figure 5 illustrates the process from the client side. The client just passes the query within a perform document and receives the answer from the framework.
Figure 5. Client-side view of select query process
The result of the request should contain a data section formatted as illustrated in Listing 3. The format corresponds to the WebRowset notation. If the sample database was manipulated, the content of output XML may differ from this output. Listing 3. Results in WebRowset notation
<data>
<row>
<col>000010</col>
<col>CHRISTINE</col>
<col>I</col>
<col>HAAS</col>
<col>A00</col>
<col>3978</col>
<col>-157737600000</col>
<col>PRES </col>
<col>18</col>
<col>F</col>
<col>-1147276800000</col>
<col>52750.0</col>
<col>1000.0</col>
<col>4220.0</col>
</row>
</data>
|
Updating data in DB2 UDB For updating data, OGSA-DAI provides another activity, the SQLUpdateStatement activity (see Figure 6). In our example, a row is inserted into the EMP_PHOTO table in the sample database. A binary image file not bigger than 100 KB is necessary for this request. This 100KB restriction comes from the maximum size of the image column in the EMP_PHOTO table. ExampleInsert.xml provides a perform document for this request. To run it you must complete the perform document with a valid URL pointing to the image file by replacing the following comment in the perform document with the binary file location.
<!--path to binary file < 100KB e.g., file:///C:/TMP/Sample.jpg-->
|
Then you can test the request using the end-to-end sample client again. The command to enter is the same as in the previous section except for the final parameter, which is the path of ExampleInsert.xml perform document. For inserting binary data into DB2, OGSA-DAI has to read the binary file. The deliverFromURL activity, an input activity, enables OGSA-DAI to locate and read the file as a byte array and provide it as input to other activities in the perform document, in this case sqlUpdateStatement.
Figure 6. Client-side view of update query process
Conclusion This article illustrates the integration of IBM DB2 UDB into a grid environment using the OGSA-DAI framework. The article provides a guide to setting up the OGSA-DAI system along with DB2, for relational data access and update through SQL requests in OGSA-DAI perform documents. Further articles or updates may be produced in support of later releases of the software. The notion of sharing computational power and providing it as a grid service is supplemented by the sharing of data such as data held in DB2, incorporating all advantages relational database systems in a grid environment.
Glossary
Activity
An operation that a Grid Data Service supports
Create Document
XML document specifying a data resource (which maps to a
database) for which a grid data service is to be created
DAIS
Data Access and Integration draft standards
Globus Toolkit
Includes reference implementations for parts of the OGSA
infrastructures
Grid Data Service Registry
Service that provides registry and search functionalities
for OGSA-DAI services
Grid Data Service Factory
Component that can create Grid Data Services
Grid Data Service
A service providing access to a specific data resource
OGSA
Open Grid Service Architecture, concept of Grid system
OGSA-DAI
Open Grid Service Architecture Data Access and Integration
OGSA-DAI Service
One of Grid Data Service Registry, Grid Data Service Factory or Grid Data Service
Perform Document
XML document specifying certain requests to a specific data
resource
Appendix A: Configuration examples This section includes the examples you can use for configuring OGSA-DAI with DB2 UDB. Place all of these files in the <TOMCAT>\webapps\ogsa\WEB-INF\etc directory. gdsf_config.xml
<?xml version="1.0" encoding="UTF-8"?>
<gridDataServiceFactoryConfig
xmlns = "http://ogsadai.org.uk/schemas/gdsf"
name="GridDataServiceFactory">
<!--scheme entry for DB2-->
<scheme name="DB2">
<driver name="jdbc" class="com.ibm.db2.jcc.DB2Driver">
<queryLanguage name="SQL" version="SQL92"/>
</driver>
</scheme>
<!--end scheme entry for DB2 -->
<!--location entry for DB2 -->
<location name="DB2V8">
<dataManager name="DB2" productType="DB2"/>
<physicalLocation>localhost:50000</physicalLocation>
<roleMap configuration=
"<TOMCAT>/webapps/ogsa/WEB-INF/etc/ExampleDatabaseRoles.xml"
class="uk.org.ogsadai.common.rolemap.SimpleFileRoleMapper"/>
<path name="SAMPLE">
<data name="SAMPLE"/>
</path>
<validSchemes>
<validScheme>DB2</validScheme>
</validSchemes>
</location>
<!--end location entry for DB2 -->
<!--dataResource entry for DB2 -->
<dataResource name="DB2DataResource">
<scheme-ref>DB2</scheme-ref>
<location-ref>DB2V8</location-ref>
<path-ref>SAMPLE</path-ref>
</dataResource>
<!--end dataResource entry for DB2 -->
</gridDataServiceFactoryConfig>
|
dataResourceImplementationMap.xml
<?xml version="1.0" encoding="UTF-8"?>
<gdsf:dataResourceImplementations
xmlns="http://ogsadai.org.uk/schemas/gdsf"
xmlns:gdsf="http://ogsadai.org.uk/schemas/gdsf"
xmlns:xsi= "http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://ogsadai.org.uk/schemas/gdsf
../../src/xml/xsd/data_resource_implementations_map.xsd">
<!--author Dirk Hain mapping for DB2 -->
<gdsf:dataResourceImplementation dataResourceName="DB2DataResource"
class= "uk.org.ogsadai.porttype.gds.dataresource.
SimpleJDBCDataResourceImplementation"/>
<!--author Dirk Hain mapping for DB2 -->
</gdsf:dataResourceImplementations>
|
gdsfCreateExample.xml
<?xml version="1.0" encoding="UTF-8"?>
<tns:gridDataServiceFactoryCreate
xmlns:gds="http://ogsadai.org.uk/schemas/gds"
xmlns:tns="http://ogsadai.org.uk/schemas/gdsf"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation=
"http://ogsadai.org.uk/schemas/gdsf ../../src/xml/types/
grid_data_service_factory_types.xsd
http://ogsadai.org.uk/schemas/gds grid_data_service_types.xsd ">
<tns:dataResourceName>DB2DataResource</tns:dataResourceName>
</tns:gridDataServiceFactoryCreate>
|
ExampleDatabaseRoles.xml
<?xml version="1.0" encoding="UTF-8"?>
<DatabaseRoles>
<!--author Dirk Hain mapping for DB2 -->
<Database name="SAMPLE">
<User dn="No Certificate Provided"
userid="<validUser>" password="<validPassword>"/>
</Database>
<!--author Dirk Hain mapping for DB2 -->
</DatabaseRoles>
|
Appendix B. Example queries This section contains the following perform documents that you can use with the DB2 UDB sample database: PerformSimpleSelect.xml
<?xml version="1.0" encoding="UTF-8"?>
<gridDataServicePerform
xmlns="http://ogsadai.org.uk/schemas/gds">
<request name="simpleSelect">
<documentation>
These are just some simple selects to test the access to db2.
</documentation>
<sqlQueryStatement name="select">
<expression>
select * from employee where salary>50000 fetch first 5 rows only
</expression>
<webRowSetStream name="dbanswer"/>
</sqlQueryStatement>
<deliverToResponse name="d1">
<fromLocal from="dbanswer"/>
</deliverToResponse>
</request>
<execute name="executeSimpleSelect" requestName="simpleSelect"></execute>
</gridDataServicePerform>
|
ExampleInsert.xml
<?xml version="1.0" encoding="UTF-8"?>
<gridDataServicePerform
xmlns= "http://ogsadai.org.uk/schemas/gds">
<request name="blobinsert">
<documentation>
This activity inserts a blob into db2 sample database emp_photo table.
</documentation>
<sqlUpdateStatement name="insert">
<sqlParameter position="1" from="blobinputstream"/>
<dataResource>DB2DataResource</dataResource>
<expression>INSERT INTO EMP_PHOTO VALUES ('000747',
'jpg' , ?)</expression>
<resultStream name="insertresult"/>
</sqlUpdateStatement>
<deliverFromURL name="d2">
<fromURL><!--path to binary file < 100KB
file:///C:/TMP/Sample.jpg -->
</fromURL>
<toLocal name="blobinputstream"/>
</deliverFromURL>
<deliverToResponse name="d1">
<fromLocal from="insertresult"/>
</deliverToResponse>
</request>
<execute name="executeBlobInsert" requestName="blobinsert"/>
</gridDataServicePerform>
|
Resources
- [1] The Grid: Computing Without Boundaries, Ian Foster, April 2003, Scientific American
- [2] IBM Grid Pages:
- [3] The Physiology of the Grid, Ian Foster, Carl Kesselman, Jeff Nick, Steve Tuecke
- [4] Grid Data Service, Amy Krause, Tom Sugden, Andrew Borley - OGSA-DAI-USER-UG-GDS.pdf at http://www.ogsadai.org/docs/docs.php
- [5] Grid Data Service Factory, Mike Jackson, Mario Antonioletti, Amy Krause
About the authors  | 
| | Susan Malaika a Senior Technical Staff Member in the DB2 Information Integrator team. She has been an IBM Academy of Technology member since 1995. She co-edited a book on the Web in 1996. She has worked in DB2 since 1998 and she specializes in XML and Web technologies, including Grid computing. |
| 
| | Dirk Hain is a Computer Science student at Humboldt University, Berlin (Germany). He is currently working as an intern in DB2 Information Integration technologies. His particular focus is grid and databases especially IBM DB2 UDB. You can reach Dirk at dhain at us.ibm.com. |
Rate this page
| 
