© 2003 International Business Machines Corporation. All rights reserved.

Q: IBM has not been known in the past for its ease-of-use story. Yet it seems that recent releases have really focused on GUI tools for administration and for application development. Have you found that this focus is paying off for developers in terms of their productivity?

A: We have been getting good feedback from developers on our enhancements in the area of application development support. This work started with the V6 DB2 Stored Procedure Builder which provided simple workstation based GUI tool for developing Java(R) and SQL stored procedures for the DB2 family, including DB2® Universal Database^TM on the z/OS^TM platform. This same level of support was then integrated into IBM Visual Age® for Java to provide the DB2 development support in the context of a full J2EE development environment.

Over the last few years this support has been significantly enhanced with the introduction of source level debugging support for SQL Stored Procedures and the Version 8 DB2 Development Center, which significantly expands on the features of the Stored Procedure Builder. In addition, working jointly with the WebSphere® Studio teams, we are providing a rich DB2 development support in the WebSphere Studio family of products for both the Windows® and Linux platforms. This support includes the development of DB2 server-side objects, such as tables, views, stored procedures, user-defined functions (UDFs), and so on , as well as full support for the development of J2EE, Web and Web-service based applications which access DB2 data and logic. Most recently we have introduced similar DB2 development support deeply integrated into Microsoft® Visual Studio .NET.

However, we are not by any means done. This is an ongoing important area of focus for us, and we continue to roll out new features with an emphasis on improved performance and usability.

Q: Can you explain the IBM strategy for data management application development? Why, for example, is similar functionality available in DB2 Development Center, WebSphere Studio, and Microsoft Visual Studio?

A: In discussions with customers we have found that there are strong development preferences ranging from J2EE developers who want support in rich Java development environment, to .NET developers who want support in the Microsoft IDE, to the developers who want basic command line support so that they can work with their editor of choice. In addition, developers want to work on different operating systems platforms including Windows, Linux and other UNIX® operating systems. To address these different requirements, we have three primary integration points for providing application development tooling:

• Integration with the base DB2 "centers" in the form of the DB2 Development Center.
• Integration into WebSphere Studio.
• Integration into the Microsoft Visual Studio.

Each of these is targeted a slightly different development community:

• The DB2 Development Center, which is integrated with the other DB2 administration tools, such as the DB2 Control Center, is targeted primarily at DB2 server-side development in the form of stored procedures and UDFs. The primary audience is administrators and developers who focus on the core database parts of the applications. It is designed in a form that should be very natural to this type of user. This support is available on the Windows, Linux, AIX® and Solaris platforms.
• The integration with WebSphere Studio provides this same server-side development for stored procedures and UDFs but now integrates it with a full J2EE development environment, thus making it easy to develop the server-side functions and also to develop the J2EE applications and Web service applications within the same development environment. This environment is designed to be natural for Java and Web developers whose development platform of choice is Windows or Linux.
• The integration with the Microsoft Visual Studio family of products provides integration of the DB2 application and server-side development into a development environment suitable for building applications that use Microsoft support. This environment is designed to be natural to Microsoft developers.

While we focus primarily on these three integration points for providing our deepest level of integration, the fact that DB2 provides client access through all of the popular APIs -- JDBC, ODBC, OLDEDB, ADO, and ADO.NET -- make it possible to also use a number of other popular development tools, including basic editor and command-line to develop your DB2 applications.

Q: Well, this raises the question about Rational® products. How can developers use Rational products in developing DB2 applications?

A: Today's Rational products, such as Rational XDE,have significant support for DB2 development. For example, there is full support for both forward and reverse engineering of DB2 schemas, logical data modeling, and transformations to and from physical models and schema and as well as support for the design and development of DB2 applications. This tooling runs in both a WebSphere Studio and Microsoft Visual Studio .NET environment. In addition, the Rational source code management offering, ClearCase, fully integrates into development environments such as the WebSphere studio family and Microsoft Visual Studio.NET and can be used for the management of all the components of a DB2 application, including not only application source files but also stored procedures, user-defined functions, and any other SQL DDL or DML scripts. Going forward we are working on tighter integration between the Rational and DB2 tooling offerings which will provide even more functionality.

Q: Is there a tool that developers can use to make deployment of stored procedures to z/OS easier?

A: IBM does not provide tooling in support of deployment of DB2 z/OS stored procedure binaries, although there are other vendor tools which can help in this area. However, with DB2 Version 8.1.2 (and as a download for V7.2), we have introduced a way to build client scripts using the new functionality DB2BUILD which can be run on the workstation to create SQL stored procedures on DB2 z/OS. With this support user can now have build script and the source in files which they can manage in traditional source code control systems and be used to build the SQL stored procedures on multiple targets. DB2BUILD has support for dropping, creating and altering the source of SQL stored procedures.

Q: Developers seem to really like the ease of using SQL Procedural Language for building their stored procedures. However, they don't like the C compiler requirement. Is there any chance of getting that requirement removed soon?

A: We understand the concerns with the C compiler requirement for SQL procedures. On DB2 for Windows and UNIX we have introduced functionality, GET_ROUTINE_SAR and PUT_ROUTINE_SAR, which lets developers or DBAs extract and deploy SQL stored procedures to like DB2 server platforms without having a C compiler present on the target server. In addition, for DB2 on Windows and UNIX we have development work underway to remove the C compiler requirement altogether.

Q: IBM has been a strong proponent of SQLJ and the latest release of DB2 UDB 8.1 has really made SQLJ a viable option for non-mainframe DB2. But JDBC has the advantage of being integrated into many tools. What are you doing to make SQLJ just as accessible?

A: With the upcoming release of WebSphere Studio (Application Developer), we are providing integrated SQLJ support. This include full integrated support for edit, compile (including both SQLJ translation and compile), customization and SQLJ source level debugging. In addition you can also develop Java stored procedures which use SQLJ.

Q: How do Java stored procedures fit into the generic J2EE framework? Why should developers consider using Java stored procedures?

A: Java stored procedures, or stored procedures in general, have a number of benefits, including the ability consolidate database-intensive logic at the server, which can result in improved performance and reduced locking issues by removing network costs from the processing time. The heavy processing is done at the server and only the result is returned to the caller. In addition, the logic is only written once, with a single copy installed at the database server. Thus it can be easily reused in a number of application running on the same or different machines.

Stored procedures merge well with the J2EE framework. A stored procedure maps naturally to an EBJ session bean method, and we provide tooling in the Application Developer configuration of WebSphere Studio to easily generate the session bean method that invokes the stored procedures. In the future, CMP methods will also be able to be mapped to stored procedures. In addition JSPs can easily invoke stored procedures. Basically, from a J2EE programming model when it makes sense to push logic closer to the database in the form of stored procedures, you can do so.

Q: Is there any special tooling being developed for making development easier for diverse federated backend data sources, such as when I need to combine XML and SQL data together?

A: With the DB2 Information Integrator^TM product we provide addition tooling in the DB2 Control Center to configure the federated data sources and map the data into nicknames. Along with support for a number of relational database vendors, Life Sciences data sources,general sources such as flat files and Excel, XML data can also be accessed through this federated layer. Once the nicknames exist, application developers can use then in the same manner as other relational tables and can build federated application both from within the IBM WebSphere Studio and Microsoft Visual Studio development environments.

At a different level, DB2 also includes a set of UDFs, called SQL/XML functions, which let you compose XML results from your DB2 data, including the federated data. With this support you can easily compose a single SQL statement that accesses data from a mixture of backend data sources and return an XML result. We will be adding the support for building these queries into our tooling.

Moving forward, we will be integrating XML support, including XML query support more tightly with the database. We are currently working on the on the XQJ specification, JSR 225, with other industry vendors. XQJ is the Java API for querying XML data. We will also be enhancing our tooling support to support this new API and features.

Q: Is there any more work being done on tooling to support DB2 UDB and WebSphere MQ integration?

A: We provide a base set of UDFs that let developers easily combine messaging operations with their SQL processing. For example, a developer can write a single SQL statement which reads messages off a WebSphere MQ message queue and inserts them into DB2 table or select data from a table and send it as an MQ message. These functions can be easily used from within development environments such as WebSphere Studio or Microsoft Visual Studio to build applications. We also provide wizards which step through the mapping of some common message formats, such as positional or character delimited and which generate table functions or and views which use the base DB2 MQ UDFs to read the message, decompose it and return it as the result from the table function.

Most recently, in V8.1.2, a new integration point was added with the MQ listener. With this functional a user can set up their DB2 workstation system so that when messages arrive on a queue the message content is automatically dispatched to a user-supplied stored procedure to process the content.

Q: Web services are becoming increasingly important in application architectures. Is there work being done in support of developing Web services which access DB2 data?

A: DB2 provides support for combining database and web services. You can easily develop Web services which access DB2 data and stored procedures. The application developer configuration of WebSphere Studio provides integrated support for developing, testing and deploying these Web services. In addition DB2 provides support for invoking Web services as part of the SQL statement processes by using web service user-defined functions. WebSphere Studio includes a wizard that allows you to easily create these UDFs from an existing Web service. In the future we will be integrating the support for DB2 Web service development into other IDEs.

Q: Where can I find more information?

A: In addition to the product documentation, a wide variety of articles on development topics have been published on the developerWorks DB2 product domain, and we will continue to publish more articles highlight new features as they become available. Here is some useful content:

Java:

• Focus area on DB2 and Java
• Overview of Java Development in DB2 UDB V8
• Tutorial: Building database applications using DB2 and WebSphere
• Considering SQLJ for Your DB2 V8 Java Applications
• Optimize DB2 performance with JDBC and SQLJ

.NET:

• Focus area on DB2 and Visual Studio .NET
• Overview of DB2 Development Add-ins for Visual Studio .NET

Development Center:

• Tutorial: Creating Stored Procedures with DB2

Web Services:

• Focus area on DB2 and Web services
• Tutorial:Access DB2 with Web services
• Tutorial: Invoking Web services from DB2

WebSphere MQ:

• Using MQSeries from DB2 Applications
• DB2 MQ XML Functions: Using MQSeries and XML Extender from DB2 Applications
• Tutorial: DB2 and WebSphere MQ Integration (Part 1)
• Tutorial: DB2 and WebSphere MQ Integration (Part 2)

Notices

All statements regarding IBM's future direction or intent are subject to change without notice, and represent goals and objectives only.

About the author

Connie Nelin is a Distinguished Engineer in the IBM Database Advanced Technology area. She has worked for IBM since 1987 with a focus on database application development support and tooling. Connie currently has overall responsibility for application development tooling strategy, architecture and development for the DB2 family. This includes DB2 Development Center, DB2 SQL Assistant as well as integration of DB2-specific features into the WebSphere Studio, Microsoft Visual Studio and Microsoft Office Products.

Comments (Undergoing maintenance)

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• About the author
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: DB2 serving ATOM feed
• Webcast: A smarter way to successful SOA - Information as a trusted strategic asset
• Briefing: Information on Demand
• Buy: DB2 Enterprise

My developerWorks community

Dig deeper into Information Management on developerWorks

DB2 pureScale

See how true transparent scaling architecture can be achieved on distributed platforms

Special offers