DB2 Application Development

DB2 offers a rich application development environment that allows developers to build databases supporting requirements for e-business and business intelligence applications. Many of these tools are integrated with the database; the major tools, especially those that can be used with DB2 for z/OS, are reviewed next.

• DB2 Application Development Client is used for creating, testing, and debugging stored procedures on local and remote DB2 servers.

• DB2 Extenders enable the SQL API to access unstructured data types, including text, image, audio, video, and XML.

• DB2 Cube Views provide OLAP support in DB2 UDB, including features and functions that improve the relational database's ability for managing and deploying multidimensional data across the enterprise.

• DB2 OLAP Server provides the ability to build online analytical processing (OLAP) cubes using DB2 as the relational data store; a higher-end version of the DB2 OLAP Server is available to handle larger numbers of users.

• DB2 Data Warehouse Edition provides the ability to build data marts/warehouses by automating the processes involved in managing, refreshing, moving, and transforming data, including the ability to define the star schema model.

• QMF provides an integrated, powerful, and reliable query and reporting tool set for DB2 data. An optional QMF for Windows is available.

DB2 Application Development Client

The Application Development Client provides many tools for development, including business intelligence (Data Warehouse Center and Information Catalog Center), command line tools (Command Center, Command Line Processor, Command Window), Development Center, Control Center, Replication Center, Task Center, Information Center, and monitoring tools (Health Center, Memory Visualizer, Event Analyzer). DB2 Development Center is the successor to the DB2 version 7 Stored Procedure Builder. In a nutshell, the DB2 Development Center is a rapid iterative development environment for building stored procedures (SPs), user-defined functions (UDFs), structured data types, and much more. This tool was rewritten to allow for concurrent task execution, flexible docking, enhanced scalability, and higher productivity. This integrated development environment (IDE) can stand on its own or be embedded into WebSphere Studio Application Developer or any of the Microsoft Visual Studio development products. As a result, developers can build DB2 business logic without leaving their favorite IDE.

Some of the Development Center features are:

• Support for the entire family of DB2 server operating systems, including z/OS, OS/400, UNIX, and Windows

• Support for developing SQL and Java stored procedures, SQL scalar and table user-defined functions; MQSeries, OLE DB, and XML table functions; and structured data types for Enterprise JavaBeans (EJB) methods and properties

• Support for viewing live database tables, views, triggers, SPs, and UDFs, as well as the ability to test SPs and UDFs written in any language

• Enhanced round-trip debugging of SQL SPs with variable-value change support, using an integrated SQL debugger

The Development Center can work as a Microsoft Visual Studio plug-in or with Microsoft Visual Basic or IBM WebSphere Studio Application Developer or it can stand alone. The DB2 Development Center supports the entire DB2 family from a single development environment and has facilities for debugging and deploying stored procedures.

DB2 Extenders

The DB2 Extenders can take your database applications beyond traditional numeric and character data to images, XML, videos, voice, spatial objects, complex documents, and more. You can use the extenders to bring all these types of data into a database and work with them, using SQL.

• XML Extender provides new data types that let you store XML documents in DB2 databases and adds functions that help you work with these XML documents while in a database. You can store entire XML documents in DB2 or store them as external files managed by the database. This method is known is XML Columns. You can also decompose an XML document into relational tables and then recompose that information to XML on the way out of the database. Thus, your DB2 database can strip the XML out of a document and simply take the data, or the DB2 database can take data and create an XML document from it. This method is known as XML columns. In DB2 version 7, the XML Extender was a free, separately installable product. In DB2 version 8, it is built into the DB2 installation as a component.

• DB2 Net Search Extender helps businesses that need fast performance when searching for information in a database. This extender is likely to be used in Internet applications, which need excellent search performance on large indexes and scalability of concurrent queries. In DB2 version 8, the Text Information Extender has merged with the Net Search Extender.

• DB2 Spatial Extender allows you to store, manage, and analyze spatial data, such as the location of geographic features, in DB2, along with traditional data for text and numbers. The DB2 Spatial Extender extends the function of DB2 with a set of advanced spatial data types that represent geometries, such as points, lines, and polygons; it also includes many functions and features that interoperate with those new data types.

• Text, Audio, Image, and Video Extenders allow you to extend the relational database to use nontraditional forms of data, such as text, songs, pictures, and movies. With these extenders, you can work with data via SQL

DB2 Cube Views

DB2 Cube Views, the latest generation of OLAP support in DB2 UDB, includes features and functions that improve the relational database's ability for managing and deploying multidimensional data across the enterprise. With DB2 Cube Views, the database becomes multidimensionally aware by including metadata support for dimensions, hierarchies, attributes, and analytical functions; analyzing the dimensional model and recommending aggregatesMQTs, also known as summary tablesthat improve OLAP performance; adding OLAP metadata to the DB2 catalogs; providing an OLAP foundation that will speed deployment and improve performance; and simplifying the exploitation of advanced DB2 technologies, such as summary table management and analytical functions. DB2 Cube Views improves DB2 query performance by enabling the DB2 optimizer to rewrite incoming queries to take advantage of the MQTs that DB2 Cube Views recommends, loading cubes, performing drill-through queries and ad hoc analysis directly to the relational tables in DB2, and enhancing all queries that use aggregate data.

DB2 OLAP Server

DB2 OLAP Server can be used for developing analytic applications for fast, intuitive, multidimensional analysis, allowing users to ask questions in an intuitive business language. The server can process multidimensional requests that calculate, consolidate, and retrieve information from a multidimensional database, a relational database, or both. Create your own applications or have turnkey solutions built for you with the many applications, tools, and solutions from providers that support DB2 OLAP Server. Perform analysis such as "Display the profit of my highest- and lowest-performing products last quarter in my domestic sales regions."

DB2 OLAP includes both features of the two main types of OLAP: MOLAP (multidimensional OLAP) and ROLAP (relational OLAP). MOLAP is known for its performance and ability to deliver answers to users very quickly. ROLAP is known for its scalability and ability to give users access to very detailed drill-down data when required. Additionally, DB2 OLAP integrates the popular Hyperion Essbase OLAP products with the e-business-ready IBM DB2 UDB relational database and combines powerful multidimensional analysis with a comprehensive set of built-in financial, mathematical, and statistical functions.

DB2 Warehouse Edition

DB2 Warehouse Edition (DWE) has two versions: Enterprise and Standard. DB2 Data Warehouse Enterprise Edition includes and extends the powerful DB2 Enterprise Server Edition (ESE) and is designed for enterprise-wide data warehouses with unlimited scalability. The DB2 Data Warehouse Standard Edition includes and extends DB2 Workgroup Edition and is limited to four CPUs (DB2 UDB Workgroup Edition licensing).

DB2 has been steadily progressing toward supporting business intelligence functions inside the database. The latest release of DB2 DWE enables customers and partners to perform mining; online analytic processing; extract, transform, and load (ETL); and advanced analytics, with a single, unified engine operating on common data structures within a consolidated data warehouse. This release combines a carefully selected set of IBM products to provide the essential infrastructure needed to extend the enterprise data warehouse as a platform for business intelligence.

The analytics include SQL; advanced statistical functions, such as correlation, covariance, and standard deviation; a complete family of linear regression functions; and sliding-windows functions and smooth-moving average and equiheight histograms. Ad hoc capabilities include materialized query tables that cache prejoined aggregates for fast reuse by common queries, patented multidimensional clustering index and table structure that speeds OLAP queries, star join optimizer algorithms that enable faster star schema queries, and an aggregate-aware cost-based optimizer with adjustable optimization levels for query performance.

Query Management Facility (QMF)

QMF is a tightly integrated, powerful, and reliable query and reporting tool set for DB2 databases on distributed and host platforms. With QMF, you can execute queries, format reports, and build procedures to perform multiple activities. QMF stores the queries, forms, and procedures in its own database, so you can reuse them. QMF provides an environment that is easy for a novice to use but powerful enough for an application programmer.

In short, QMF also allows you to do the following:

• Easily build queries and reports via a quick-start interface

• Leverage a Java-based query capability to launch queries from your favorite Web browser

• Integrate query results with desktop tools, such as spreadsheets and personal databases

• Rapidly build data-access and update applications

• Fully exploit DB2 performance, SQL syntax, and advanced database performance techniques, such as static SQL

DB2 QMF has been enhanced with new data-visualization, solution-building, Web-enablement, and solution-sharing capabilities. This includes support for the DB2 Universal Database version 8 functionality, including IBM DB2 Cube Views, long names, Unicode, and enhancements to SQL. Also, visual data appliances, such as executive dashboards, offer visually rich interactive functionality and interfaces specific to virtually any type of information request.