DB2 Replication
DB2 Replication allows for data to be propagated from one location to another. SQL-based replication, also know as Data Propagator (DPROPR) replication, is a free component of the DB2 for Linux, UNIX, and Windows data servers (it's a paid feature on the mainframe). You can also extend this replication capability to support a wide variety of data servers, including DB2, Oracle, Microsoft, Sybase, Informix, IMS, Lotus Notes, and flat files with WebSphere Replication Server.
Note - SQL replication is also the core technology behind the DB2e SyncServer that enables mobile users to keep their data synchronized with corporate data.
An example of SQL-based replication is shown in Figure
1–12. SQL-based replication has been around for over a decade and has proven itself to be extremely flexible, resilient, easy to set up, and scalable.
Figure 1–12
SQL-based replication extended with WebSphere Replication Server
In Figure 1–12 you can see the broad reach of the SQL-based replication technology when it is extended with the WebSphere Replication Server product. As you may recall, distributed versions of DB2 come with the DPROPR technology built in and can support replication subscriptions across the DB2 family. The core capabilities of a distributed data server with respect to replication are shown in the top-left and top-right portions of Figure
1–12.
When you buy WebSphere Replication Server, you also get Q-based replication (Figure
1–13). In Q-based replication, each message represents a transaction that is placed on a WebSphere MQ Series message queue. This replication is known for its highly parallel apply process and rich conflict detection and resolution services.
Figure 1–13
The power of Q-based replication
WebSphere Federation Server and WebSphere Information Server
WebSphere Information Server provides features and functions to place, publish, integrate, find, cleanse, and access data. The federation capabilities are worth mentioning as they relate to data access. WebSphere Federation Server contains a sub-set of the functionality found in WebSphere Information Server, namely the federation capabilities (and hence will be the focus of this section). You can learn more about WebSphere Federation Server and WebSphere Information Server at http://www.ibm.com/software/data/integration/.
A federated system is a distributed database environment that appears as a single virtual database to end users and applications. The WebSphere Federation Server technology (some of which is built into DB2) allows SQL statements to transparently access, join, or update data located across heterogeneous data sources. Federated support enables the DB2 data server to evaluate global statistics on all data sources in order to optimize requests for the remote data. For example, the global optimizer can use knowledge of source statistics, source indexes, source functions, server and network speeds, and so on.
The SQL support in a DB2 federated environment supports the same SQL as a non-federated environment, including advanced object-relational SQL operations across the federated data sources, such as recursive SQL, common table expressions (CTEs), and more. If the remote data source doesn't have this capability, DB2 will compensate for the missing functions.
Perhaps the greatest benefit to a federated system is that developers only have to learn a single dialect of SQL; namely, the DB2 ANSI-compliant SQL API. If an external data source has different data types or non-standard SQL, DB2 will transparently compensate for missing functions and convert data types. For example, SQL Server 2005 has a MONEY data type whereas DB2 uses a DECIMAL data type to support currency. Without the WebSphere Federation Server technology, a developer would have to know the differences between these data servers, or cast them to like data types using OLE DB. With WebSphere Federation Server, developers just code as if they were always accessing DB2 data, and the rest is taken care of.
An example of federated access is shown in Figure
1–14.
Figure 1–14
The power of federation — any data, one API
In Figure 1–15 you can see the technology components that make up a federated database. Nicknames are used to implement a low form of granularity with respect to the data sources you want to make available. Nicknames essentially are local aliases on remote tables, although they can be mapped to specific rows and columns. A collection of nicknames is located within a server that represents that actual data source. For example, in Figure
1–15 Oracle is one such data source, so the server component would represent a specific Oracle database. All the SQL eventually flows through a wrapper, which is a library of code that allows access to a particular class of data servers. Connections made to these servers use their native protocol; for example, the Oracle connection will use the Net8 client.
Figure 1–15
How federation works
You can see in Figure 1–14 that with WebSphere Federation Server you can pretty much extend the reach of DB2 to any data source in your environment. For example, in the life sciences industry, scientists need access to specialized data to support their research related to drug discovery. IBM offers a set of Life Sciences wrappers that can be used to build a federated system that can access data useful to scientists, including Excel spreadsheets, image data, flat files, and BLAST (Basic Local Alignment Search Tool).
If a wrapper is not available for purchase, there is a software developer's kit (SDK) that you can use to build your own.
Database Enterprise Developer's Edition
IBM offers a special discounted suite of Information Management products that you can purchase at a discount for development, evaluation, demonstration, and testing of your application programs; this edition is called Data Enterprise Developer Edition (DEDE).
Some of the products included in this comprehensive developer offering are:
DB2 Express 9
DB2 Workgroup 9
DB2 Enterprise 9
DB2 Runtime Client (including .msi merge modules on Windows)
DB2 Client
IBM Driver for ODBC and CLI
IBM Driver for JDBC and SQLJ
DB2 Developer Workbench
DB2 Embedded Application Server (components of WebSphere Application Server)
DB2 Information Center
DB2 Documentation CD
DB2e Enterprise Edition
WebSphere MQ (this is a restricted copy that can only be used in conjunction with the DB2 software)
Rational Web Developer (this is a restricted copy that can only be used in conjunction with the DB2 software)
Tivoli System Automation (TSA) for Linux and AIX
Informix IDS Enterprise Edition
IBM Cloudscape/Apache Derby
DB2 Connect Unlimited Edition for i5/OS and z/OS
All the DB2 Extenders™, namely DB2 Spatial Extender, DB2 Geodetic Extender, DB2 Net Search Extender, and the DB2 XML Extender
All of the DB2 add-on feature packs outlined earlier in this chapter
Most of the products within DEDE are available for all the platforms that DB2 Enterprise supports (unless of course a product doesn't exist on a specific platform). The data server where you install DEDE can be on a platform that is different from the one on which the application will eventually be deployed or tested because of the common code base used in the distributed DB2 environment. In fact, almost any applications developed using DEDE can be executed on any system that has DB2 client software (or specific drivers for which the application is written) installed.
The application development environment provided with DEDE allows application developers to write programs using today's most popular methodologies, including:
Embedded SQL
Call Level Interface (CLI)/Open Database Connectivity (ODBC)
.NET Framework 2.0
DB2 Application Programming Interfaces (APIs)
Web Services
Java Database Connectivity (JDBC) and SQLJ
Python™
PHP
Perl™
Ruby on Rails™
DEDE also includes the necessary programming libraries, header files, code samples, and pre-compilers for all of the supported programming languages.
DB2 Developer Workbench
The DB2 9 Developer Workbench (DB2 DWB) replaces the DB2 8 Development Center. The DB2 DWB is an Eclipse-based graphical environment that supports the rapid development of DB2 SQL and Java stored procedures, SQL scalar and table user defined functions (UDFs), SQL statements, XQuery statements, and Web Services. However, there's so much more to this list. For example, the DB2 DWB includes an SQL editor that's enriched with syntax colorization and code assistants, as well as teaming support, compare utilities, and more.
The DB2 DWB is a separate tool and is maintained separate from a DB2 data
server. You can download it from http://www-304.ibm.com/jct03001c/software/data/db2/ad/dwb.html.
The DB2 DWB is really meant for power DBAs that aren't coding experts but require rapid development assistance for building business logic for their data servers. Depending on your environment, you may elect to use another tool like Toad for DB2 or Visual Studio. Pure developers will likely choose to use the plug-ins provided with DB2 9 into their respective IDEs, although they are free to use the DB2 DWB. For the most part, you can perform the same tasks in any of the tools that IBM ships or the integration points in specific IDEs.
A snapshot of the Developer Workbench screen is shown in Figure
1–16.
Figure 1–16
The DB2 Developer Workbench
» 
