 | Level: Introductory Rav Ahuja (rsahuja@ca.ibm.com), DB2 Program Manager, IBM
13 Jul 2006 New enhancements for developers in IBM® DB2® 9 for Linux®, UNIX®, and Windows® include a new Developer Workbench, deeper integration with .NET environments, rich support for XML and SOA environments, new drivers and adapters for PHP and Ruby on Rails, and new application samples. This article, the final article in a series introducing the features of DB2 9, provides an overview of these enhancements
Introduction
DB2 9 (formerly codenamed "Viper") provides numerous enhancements that simplify database application development, reduce development time, and improve developer productivity. In addition to providing a platform for robust enterprise applications, DB2 9 is also optimized for rapidly building a new breed of "Web 2.0" applications based on Web services, XML feeds, data syndication, and more.
New Developer Workbench
The Developer Workbench is a visual tool that aids in rapid development of DB2 business objects and is available at no charge. This newly designed tool is based on the Eclipse framework and replaces the DB2 Development Center (that was based on Swing architecture).
Developer Workbench makes it easy to:
- Create, view, and edit database objects (such as tables and schemas)
- Explore and edit data in tables and rows
- Visually build SQL and and XQuery statements
- Develop and deploy stored procedures, user defined functions (UDFs), routines, and scripts
- Debug SQL and Java™ stored procedures
- Develop SQLJ applications
- Develop queries and routines for XML data
- Perform data movement (such as load and extract)
- Collaborate and share projects with team members
- Migrate projects from DB2 Development Center
- and more...
| | Not all of the features of the Developer Workbench are supported for all platforms. Older DB2 versions may not be supported in the initial release of Developer Workbench. |
|
In addition to providing an interface for developing applications and objects for DB2 9 on Linux, UNIX, and Windows, Developer Workbench provides a common interface for working with DB2 on other platforms -- DB2 for iSeries™ and DB2 for z/OS®. You can also use Developer Workbench with IBM Cloudscape™ or Apache Derby.
Developer Workbench -- Installation
The Developer Workbench installation media is separate from the base DB2 9 product installation CDs, but can be found in the same media pack. Since Developer Workbench is available for use with a variety of platforms and IBM data servers, it is also available as a free download from the Web. The installation itself is fairly straightforward and can be completed with just a few clicks using the graphical installer. The installation footprint is about 400MB (can vary somewhat depending on the platform), and contains an independent Eclipse image to minimize versioning issues with other Eclipse based tools installed on the system. Developer Workbench help and tutorials are available in an information center that is installed with Developer Workbench.
Developer Workbench -- Layout
Developer Workbench has several views for developing DB2 business objects, such as:
-
Database Explorer view -- Shows what’s in the database (such as tables, stored procedures, UDFs, dependencies, remote servers) and allows you to perform actions on these objects (such as create, run, and browse properties).
-
Data Project Explorer view -- Lets you perform development actions for procedures, UDFs, and SQL statements, with easy operations such as drag and drop, copy and paste, wizards, context sensitive completion options.
-
Data Output view -- Lets you see reports of data development actions against a database.
Figure 1. DB2 Developer Workbench layout showing several views
Developer Workbench -- Develop and deploy routines
Developer Workbench allows you to develop SQL and Java routines (stored procedures and UDFs) with ease. These server-side database objects can help you encapsulate business logic and reduce network traffic. You can perform the following types of actions with Developer Workbench while working with routines:
- Display existing routines from a database server
- Create a new routine using a wizard
- Import a routine source file or copy an existing procedure into the development project
- Edit the procedure source and properties with the routine editor
- Package Java procedures within multiple JAR files
- Deploy procedures to unlike servers (for example, deploy a routine developed for a DB2 for Windows server to DB2 for System z™)
- Estimate costs of running statements on DB2 for z/OS
- Compare and edit routines side-by-side
- Export procedures for integrated development environment (IDE) independent batch deployment
- Run a routine (test call)
- Debug stored procedures by stepping through the code, setting breakpoints, and viewing variable content
- Drag and drop routines from a database connection to the project
Figure 2. Use Developer Workbench to develop stored procedures
Deeper .NET integration
DB2 was the first major data server to provide built-in support for the .NET framework from Microsoft®, ahead of even Microsoft’s own SQL Server. DB2 9 goes even further by providing richer and deeper .NET and Visual Studio integration to help you develop more powerful applications faster. DB2 enhancements in this area include:
- Enriched tooling and add-ins for Visual Studio 2005
- Data provider updated for .NET framework Version 2.0
Enriched add-ins for Visual Studio 2005
DB2 provides add-ins for Visual Studio to help you easily build .NET applications, Web sites, Web services, and database server-side business logic using little or no C# or Visual Basic code, and without leaving the Visual Studio environment. These add-ins in DB2 9 are greatly enhanced with a number of unique capabilities for which other data servers typically require the use of third party add-ons.
Features of this enriched DB2 tooling for Visual Basic include:
| | The IBM Server Explorer has been removed from the IBM database add-ins for Microsoft Visual Studio 2005 but still remains for IBM database add-ins for Microsoft Visual Studio 2003. |
|
- New IBM designers for easily working with database objects (such as tables, views, scripts, procedures, and result sets)
- Ability to generate and deploy DB2-based Web services without writing a single line of code
- Integration into Microsoft Server Explorer to perform database activity (such as manage connections and explore database objects)
- Ability to create and debug SQL procedures easily (including common language runtime [CLR] stored procedures)
- Ability to clone DB2 database objects
- Schema cache for rapid access to schema information
- Advanced object filtering capabilities
- Import and export capabilities from the data grid
- Integration with Microsoft Query Builder
- Parameter persistence support for rerun of routines
- Support for IBM family of data servers including Informix® Dynamic Server (IDS), DB2 for z/OS, and DB2 for iSeries in addition to DB2 for Linux, UNIX, and Windows
- Comprehensive support for new DB2 pureXML™ data type and features
Figure 3. DB2 add-ins in Visual Studio 2005 for developing stored procedures
For additional details on DB2 9 integration with Visual Studio, refer to the DB2 developerWorks zone for Visual Studio and .NET.
Updated native DB2 .NET data provider
DB2 comes with a native .NET data provider that has been updated for .NET framework Version 2.0, and is integrated into the Visual Studio Server Explorer. Enhancements in this updated .NET data provider include:
-
Support for System.Data.Common base classes -- This enables you to develop generic .NET database applications without referencing any data provider-specific classes. This is especially useful when you are writing application code to be reused with a variety of data servers (or providers). Where applicable, the DB2 .NET data provider classes are inherited from their corresponding classes in the System.Data.Common namespace.
DbProviderFactory myFactory =
DbProviderFactories.GetFactory("IBM.Data.DB2"); |
Alternatively, you can use the DB2Factory class to create DB2 specific .NET objects:
DB2Factory myFactory = DB2Factory.Instance; |
-
DB2 data type equivalency for .NET data types -- DB2Type namespace has an equivalent data type in .NET for all DB2 data types. This provides developers a way to preserve the entire fidelity of the data type. The DB2Types classes also provide a means to represent DB2 database column values as individual nullable objects. Instances of DB2Types classes are also useful as parameters for CLR stored procedures or user-defined functions.
-
Scrollable and updateable result sets -- New methods have been added to the existing DB2Command class to create a result set that is navigable. These enable your applications to update individual column values, or to scroll through result sets backward or forward.
-
Data paging capability -- The DB2Command class now has an ExecutePageReader method that enables applications to fetch a specific set of rows from the database. It allows applications to specify the starting row of the fetch and the number of rows to be fetched from the database. This feature is useful when looking for a particular set of rows, and is much faster and simpler than finding the desired rows by scrolling through the entire result set.
-
Bulk data copy -- A new DB2BulkCopyOperation class provides the ability to bulk copy data in DB2 tables. This makes it easy to insert records from an available source like a DataReader or even a non-DB2 source.
-
Batch updates for SQL statements -- The DB2DataAdapter property, UpdateBatchSize, enables applications to determine the number of SQL statements to collect before issuing them as a batch to the DB2 database server. This can provide a performance boost, as it results in fewer individual transmissions of data between the client application and the database server.
 |
Rich XML development support
DB2 9 features pureXML technology that provides a unique set of capabilities for managing and serving XML data in a highly efficient manner. pureXML technology consists of a true XML data type (that stores XML in its hierarchical format rather than as a large object or stuffed into relational columns), XML indexing, XML text search support, SQL/XML and XQuery support, schema evolution flexibility, and numerous other capabilities that you can read about in "What’s new in DB2 Viper: XML to the Core" (developerWorks, February 2006).
As an integral part of pureXML technology, DB2 9 contains rich support for developing XML centric and hybrid applications to simplify coding, reduce development time, and improve application change agility. XML development support in DB2 9 includes:
- Support for various programming languages and application interfaces
- Support for querying data using XQuery and SQL/XML (or both together)
- Comprehensive XML capabilities in Developer Workbench (including XQuery builder)
- Tight pureXML integration with Visual Studio and .NET
- New XML development code samples and DB2 SAMPLE database enhancements
XML data type support for programming languages and interfaces
New language specific XML types and bindings have been added to the DB2 client interfaces to allow applications to work with the DB2 XML data type efficiently and seamlessly. As a result, the following popular programming languages and application interfaces are supported for the DB2 XML data type:
-
Languages -- C/C++, Java, C#, Visual Basic, Cobol, PHP
-
Interfaces -- JDBC, CLI / ODBC, .NET, Embedded SQL, SQLJ
Take, for instance, DB2 support for XML in JDBC. The new DB2 driver for JDBC has been enhanced for XML data. XML data for query results and input and output parameters can be bound using Java data types such as: strings, byte arrays, and streams. Since JDBC 3.0 currently does not define a native XML data type, DB2 provides an extension XML type known as com.ibm.db2.DB2Xml. The DB2Xml extension has a number of very useful methods that makes working with XML data easy. In the example below, a column is retrieved as a DB2Xml object. Then the getDB2String method returns the serialized representation of the XML value (without XML declaration) as a string object. The getDB2XMLBinaryStream("UTF-16") then returns a binary stream with the XML value encoded in UTF-16, including a matching XML declaration.
com.ibm.db2.jcc.DB2Xml xml1 =
(com.ibm.db2.jcc.DB2Xml) rs.getObject ("xml_stuff");
String s = xml1.getDB2String();
InputStream is = xml2.getDB2XMLBinaryStream("UTF-16");
|
For additional examples, refer to the article "Develop Java applications for DB2 XML data" (developerWorks, May 2006).
XML support in Developer Workbench
The Developer Workbench contains comprehensive support for XML including:
-
Support for the XML data type -- This data type is treated as a first class citizen in the in the Developer Workbench allowing you to work with the XML data type in queries and routines. You can also view, edit, and update contents of XML documents in the database.
-
Stored procedure support -- You can create and run stored procedures that contain XML data type (input or output) parameters or return XML data.
-
Data output view -- You can view XML data type columns on the results page, and visualize the content of XML columns as a tree or document text.
-
Support for XML schema -- You can load existing XML schemas and XML schema documents from the XML schema repository in the database and view properties such as target namespace or schema location. You can also register new XML schemas (and corresponding XML schema documents) or drop them.
-
XML document validation -- You can perform XML value validation for XML documents against a registered XML schema.
-
XQuery builder -- With the XQuery builder, you can create complete queries without needing to understand XQuery semantics. You can build an XML query visually by selecting sample resultant nodes from a tree representation of a schema or XML document and dragging the nodes onto a return grid. After a node is listed on the return grid, you can drill down into the query to add predicates and sorting preferences. You can drill down multiple levels in a query to specify nested predicates, clauses, and expressions. For example, you might select a node and then drill down to add a predicate. Within that predicate, you might drill down again to add another predicate. After you build your query, you can run it directly from Developer Workbench to test the query.
Figure 4. XQuery builder in Developer Workbench
pureXML support for Visual Studio
DB2 add-ins for Visual Studio contain full support for pureXML including the functionality to perform the following actions:
- Use an XML data type for columns and procedures
- Provide an XML index for an XML column
- Capability to visualize XML data
- Update, import, and export XML data
- Validate an XML database against a registered XML schema
- Register and unregister XML schemas
- Generate sample data based on an XML schema
- Create and register annotated XML schemas
- Execute and visualize XQuery and SQL/XML scripts
- Apply XSLT to XML data for customized visualization
Enhanced DB2 driver for PHP
PHP has become one of the most popular Web application development languages. This open source scripting language is easy to learn and comes with many powerful features that enable developers to easily interact with HTML. With support and tools from companies like Zend, PHP is also becoming popular in enterprise environments and is widely used with databases.
While PHP support for DB2 has been available for quite some time, an IBM contributed driver for PHP was introduced in 2005. Among other improvements, this (ibm_db2) PHP extension has now been enhanced with pureXML support in DB2 9. The latest driver can be downloaded at no-charge from the PECL Web site, the repository for PHP extensions, found in the Resources section of this article.
This DB2 driver for PHP is also included as part of the Zend Core for IBM -- a seamless, out-of-the-box, easy to install, and supported PHP development and production environment tailored for DB2, IBM Cloudscape, or Apache Derby data servers. For more information on the Zend Core for IBM, refer to the Resources section of this article.
DB2 enablement for Ruby on Rails
Ruby on Rails, an open source Web framework, is fast emerging as the leading technology for creating Web-based applications extremely quickly. By espousing a philosophy of "convention over configuration" and agile development principles, Rails can radically reduce development time and effort involved in creating Web applications.
IBM has enabled DB2 9 for Ruby on Rails and released a Ruby driver and a Rails adapter for DB2. A Starter Toolkit for DB2 on Rails that includes DB2 Express-C (a free to build, deploy, distribute data server), tutorials, examples, flash movies, and other learning materials is also available. For further details or downloads related to DB2 on Rails, refer to the alphaWorks Starter Toolkit found in the Resources section of this article.
Conclusion
In this article, you've looked at several of the application development enhancements in DB2 9 for powering a new breed of agile applications and improving developer productivity. Now it's time to test drive DB2 9 and experience the benefits for yourself.
Resources Learn
-
developerWorks Information Management zone: Learn more about DB2. Find technical documentation, how-to articles, education, downloads, product information, and more.
-
developerWorks resource page for DB2 for Linux, UNIX, and Windows: Read articles and tutorials and connect to other resources to expand your DB2 skills.
-
Learn about DB2 Express-C, the no-charge version of DB2 Express Edition for the community.
- "What's new in DB2 Viper:
XML to the core" (developerWorks, February 2006): Find details on the new XML capabilities in DB2 9.
- "Develop Java applications for DB2 XML data" (developerWorks, May 2006): Discover useful examples for developing Java applications using the new XML capabilities in DB2 9.
-
Visit the developerWorks Visual Studio .NET zone to get information about using .NET tooling to develop DB2 applications.
-
Stay current with developerWorks technical events and Webcasts.
Get products and technologies
Discuss
About the author | | |
Rav Ahuja is a worldwide DB2 program manager based at the IBM Toronto Lab. He has been working with DB2 for Linux, UNIX, and Windows since version 1 and has held various roles in DB2 development, technical support, marketing, and product strategy. He works with customers and partners around the globe helping them build and benefit from DB2 and services-based solutions. Rav is a frequent contributor to DB2 papers, articles and books. He holds a Computer Engineering degree from McGill University and MBA from University of Western Ontario. |
Rate this page
| |
