DB2 application development overview
Accessing data in a relational database (RDBMS) can be achieved through:
- End-user queries via Structured Query Language (SQL)
- Application interfaces, which hide the details of the SQL from the end user behind a graphical user interface (GUI).
The vast majority of DB2 applications today use GUIs to hide data access details from the end-user.
Application developers are required to learn a language dependent technique of accessing DB2 data. For example, Cobol programmers embed SQL statements in their applications and use preprocessors to convert the tagged SQL statements into native library calls that interact with the DB2 server. C programmers currently have a vast array of database access options available to them today, including embedded SQL, Call Level Interface (CLI), ODBC, ADO, OLE-DB, and ADO.Net.
Java(TM) programmers currently have JDBC and SQLj as a direct database interface for DB2. The JDBC(TM) interface details are defined within the language itself known as Java 2, Standard Edition, J2SE. Java 2, Enterprise Edition (J2EE) defines a set of language extensions for building distributed Java applications. You may have heard of many of these language extensions, including JavaServer Pages(TM), servlets, and Enterprise JavaBeans (EJBs), among others. These Java language extensions require some sort of application server environment and they use either JDBC or SQLj as the access interface for DB2 databases.
What is the promise of Web services?
Web services technology is essentially a new programming paradigm to aid in the development and deployment of loosely coupled applications within a company or across industries. In the past, developers tended to develop most of their applications "from the ground up." The term "code reuse" was used, but was very often not put into practice because developers tended to only trust the code that they developed. As software development has progressed as a discipline and as programming languages have also advanced, the ability to reuse application modules has greatly increased. For example, the Java language has many built-in class libraries that developers use frequently.
As applications grow, they need to be able to execute in a distributed environment. Distributed applications provide unlimited scalability and other benefits. Defining an interface for distributed applications has been a challenge over the years. Language-independent technologies such as CORBA (Common Object Request Broker Architecture) provide a complicated and powerful programming model. Other distributed technologies work well within a single language environment, such as Java RMI (Remote Method Invocation) and Microsoft's DCOM (Distributed Common Object Model).
In contrast, Web services provide a simple-to-understand interface between the provider and consumer of application resources using a Web Service Description Language (WSDL). Web services also enables the following technologies to help simplify the implementation of distributed applications:
- Application interface discovery using Universal Description, Discovery, and Integration (UDDI).
- Application interface description, again using UDDI.
- A standard message format using Simple Object Access Protocol (SOAP), which is being developed as a specification in W3C as XML Protocol.
- A standard transport protocol using HyperText Transport Protocol (HTTP).
- A standard network protocol using TCP/IP.
Web services - the architecture
The Web services architecture is currently defined in many layers, as seen in Figure 1. The fundamental underpinnings of any Web service include:
- WSDL, which provides a common service description
- SOAP, which provides the XML-based messaging protocol
Figure 1. Web services layered architecture
WSDL defines an XML format for describing network services as a set of endpoints operating on messages that contain either document-oriented or procedure-oriented information. SOAP messages can be created or consumed using any programming language. Even the transport/network layer is flexible. The vast majority of initial Web services are being deployed using HTTP protocol over TCP/IP networks, but other options are also used, including WebSphere(reg) MQ and others.
The Web services architecture takes into account that WSDL interfaces to an application component may change, and likely will change, over time. The Universal Description, Discovery and Integration (UDDI) specification helps distributed application developers and implementers solve these application evolution issues. There are public UDDI registries hosted on the Internet by companies such as IBM and Microsoft (and others). These registries can be used by developers to publicize their application interfaces (as specified by WSDL); alternatively, the registries are used to find other developers' application interfaces. When a WSDL interface has changed, the developers can republish their new interface in the public UDDI registry.
Tools are indispensable in many professions, such as carpentry and automobile mechanics. Software development is no different in this regard. With a simple understanding of the Web services architecture, software development tools can help you navigate the world of Web services.
IBM's WebSphere Studio is a set of development tools that help you create and maintain Java applications, including Web services, XML, and data-access applications for DB2 data. WebSphere Studio Site Developer Advanced and WebSphere Studio Application Developer both provide the environment to easily create DB2 Web Services. One of the key differences between WebSphere Studio Site Developer and WebSphere Studio Application Developer is full Enterprise JavaBean (EJB) development and test environment in WebSphere Studio Application Developer. Note that there are no dependencies on EJBs to develop Web Services applications.
WebSphere Studio is the replacement platform for IBM's VisualAge(reg) for Java products and it is based on an open development framework known as Eclipse (www.eclipse.org). WebSphere Studio provides tools for creating WSDL interfaces to Java applications and DB2 data. Web services can be published to a UDDI registry directly from WebSphere Studio. A UDDI browser is also provided within WebSphere Studio. With WebSphere Studio V5, you can create DB2 user-defined functions and stored procedures.
IBM's WebSphere Application Server is a J2EE™-compliant Java Web Application Server. It is an ideal platform for hosting DB2 Web Service provider applications. The Apache SOAP server (http://xml.apache.org/soap/) is included with WebSphere Application Server.
DB2 as a Web services provider and consumer
Figure 2 shows how DB2 fully participates in the Web services environment:
Figure 2. DB2 and the Web services environment
DB2 as a Web services provider
Now let's look at the left side of Figure 2. Access to a DB2 database can be provided to any Web services client application through a WSDL interface. You can create a WSDL interface to DB2 data using the capabilities of the Web services Object Runtime Framework (WORF), also known as Document Access Definition Extension (DADx). In simplified terms, access to DB2 data can be defined using an XML file. This XML file can contain a series of operations. Each of the operations can consist of DB2 stored procedure invocations, XML document storage or retrieval, or CREATE, SELECT, UPDATE, DELETE tasks using SQL statements. Listing 1 is an example of an SQL statement.
Listing 1. Employee Web service (sample.dadx) showing SQL SELECT operation
<?xml version="1.0" encoding="UTF-8"?>
<DADX xmlns=http://schemas.ibm.com/db2/dxx/dadx>
<operation name="showemployees">
<query>
<SQL_query>SELECT * FROM EMPLOYEE</SQL_query>
</query>
</operation>
</DADx>
|
In Listing 1 a single operation called showemployees is defined for retrieving all of the data from the employee table within a DB2 database. A DADx file may contain multiple operations. These operations are basically deployed as Web services within a single application module.
The DB2 Web services environment (WORF) provides the infrastructure to automatically create the WSDL interfaces for each of your defined operations. WORF generates a Web services test client as a Web application, using Java servlets and JSPs. The test client can use simple HTTP or SOAP bindings. An HTTP binding is useful for testing a DB2 Web service directly using a Web browser. The SOAP binding can be used by Web services clients to create distributed applications. The data is returned from the DB2 Web service as an XML document or as a Java object. XML parsers are readily available for various programming languages, including Java and C.
So I have defined operations for DB2 data
access via DADx, now what?
You must deploy the DADx file and its runtime environment (Apache
SOAP v2.2) to a supported Java Web application server environment
(Apache/Jakarta Tomcat 3.2.x+ or IBM's WebSphere Application Server
v4.x or later releases). After you have the DB2 Web service tested
and deployed, any Web services client can start using the DB2 Web
service. Using WebSphere Application Server to deploy DB2 Web
Services provides additional benefits such as pooled database
connections and centralized administration. WebSphere can also be
deployed using horizontal and vertical scaling techniques to
provide fault-tolerance and high-transaction rates required for a
popular DB2
Web service.
Operational characteristics of DB2 Web
services
A descriptor is created for each DADx environment to identify the
DB2 JDBC driver details to connect to the DB2 database, including
the user information. The operation is executed within the
authorization scope of the defined user within the DADx
descriptor.
DB2 Web services for DB2 UDB on
zSeries™
Accessing DB2 UDB for zSeries™ data using DADx requires a Java Web
Application Server, regardless of platform. If the WebSphere
environment is hosted on zSeries the native DB2 for zSeries JDBC
driver is required. Alternately, the WebSphere environment can be
hosted on UNIX®, Windows®, or Linux platforms and use the DB2 UDB
UNIX, Windows, or Linux JDBC driver via DB2 Connect.
DB2 as a Web services consumer
Now we are ready to move to the right side of Figure 2, in which DB2 optimizes access to other Web service providers as a consumer. DB2's extensible optimizer, known as Starburst, was introduced in 1995. By using SQL statements, it is easier to consume and integrate Web services data. Using SQL to access Web services data can save you effort because data can be manipulated within the context of an SQL statement before it is returned to the client application. Tools play an important role in accessing Web services data from SQL. Converting an existing WSDL interface into a DB2 function (table or scalar) is a task that can is simplified using a WebSphere Studio plug-in to convert WSDL to DB2 SQL functions. The tooling capability is also included in WebSphere Studio V5.
Accessing Web services data directly from DB2 SQL statements does not require a Java application server. These SQL extensions are created by a DB2 database administrator or database developers. During SQL statement execution, a connection with the Web service provider is established and the response document is returned as a relation table or a scalar value.
The right side of Figure 2 shows a DB2 Web service SQL extension called . This SQL function returns the current currency conversion rate between two countries. Since this data is constantly in flux, a Web service provider can supply the most current value for your application. In the example, we are using the power of SQL to calculate the average salary for each job family within the STAFF table in Canadian dollars. We are also ordering the result before returning it to the application. A non-DB2 Web service client application could achieve this same result without the use of DB2 Web service SQL extensions, but it would require more programming effort.
The DB2 Web service infrastructure is planned to be provided in DB2 UDB Version 8, including the tools for creating the SQL extensions. Because these Web services are consumed via SQL statements, it is simple to test the Web service access using tools such as DB2's Command Line Processor (CLP).
Recommendations for using DB2 Web services
When you expose DB2 data to Web services clients using DADx, consider embedding data access logic into DB2 stored procedures. Each DADx operation is currently limited to a single SQL statement and executes within a single unit of work. DB2 stored procedures provide a very powerful technique for creating an abstraction layer for DB2 data access. DB2 stored procedures can be created in various programming languages, including Java and the standard SQL procedure language.
Note that in DB2 UDB Version 8, the Java stored procedure infrastructure has been rearchitected to improve performance.
To make the development job easier, consider using the DB2 Development Center, which is provided in DB2 UDB Version 8 as the replacement for the DB2 Stored Procedure builder. The Development Center can be used to create and test stored procedures and to create other SQL extensions for DB2.
Integrating Web service provider data and DB2 data using SQL extensions is a powerful technique for building distributed applications. DB2 can execute SQL statements in parallel to integrate various data providers using a single SQL statement.
DB2 can be used as a Web service provider or consumer of data. Providing Web service data requires a Java application server, but no JDBC or SQLj coding is required because the DB2 Web service infrastructure generates and executes the required JDBC code to access DB2 data. DB2 can easily consume and integrate Web service data from other sources using SQL extensions. Tools are important for building, deploying, and testing DB2 Web services. These tools are either provided directly from DB2 UDB or by using WebSphere Studio.
DB2, DB2 Universal Database, Distributed Relational Database Architecture, DRDA, iSeries, OS/390, WebSphere, and z/OS are trademarks or registered trademarks of IBM Corporation in the United States, other countries, or both.
Windows is a registered trademark of Microsoft Corporation in the United States, other countries, or both.
Java and all Java-based trademarks and logos are trademarks or registered trademarks of Sun Microsystems, Inc. in the United States, other countries, or both.
UNIX is a registered trademark of The Open Group in the United States and other countries.
Other company, product, and service names may be trademarks or service marks of others.
IBM copyright and trademark information
- DB2 Version 8 information, at http://www.ibm.com/software/data/db2/v8/
- Video Central sample and tutorial at http://www.ibm.com/developerworks/db2/library/samples/videocentral/videocentral.html
- Video Central overview article, at http://www.ibm.com/developerworks/db2/library/techarticle/0205zhang/0205zhang.html
- Meet the Experts: Jim Kleewein, at
http://www.ibm.com/developerworks/db2/library/techarticle/0203kleewein/0203kleewein.html
- Cutlip, Rob. Web Services: The New Web Paradigm, Part 2,
at http://www.db2mag.com/db_area/archives/2002/q1/cutlip.shtml/
- Hutchison, Grant. Minding your Business, at http://www.db2mag.com/db_area/archives/2002/q2/hutchison.shtml
- Bloor Report on Web Service Gotchas, at
http://www.ibm.com/software/solutions/webservices/pdf/bloor_overview.pdf
(PDF)
