Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:

By submitting your information, you agree that databasejournal.com may send you databasejournal offers via email, phone and text message, as well as email offers about other products and services that databasejournal believes may be of interest to you. databasejournal will process your information in accordance with the Quinstreet Privacy Policy.

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Mar 19, 2008

DB2 9 for Linux, UNIX, and Windows: DBA Guide, Reference, and Exam Prep (6th Edition) - Page 4

By DatabaseJournal.com Staff

DB2 Connectivity

DB2 provides a variety of options for connecting to other DB2 and non-DB2 data servers:

  • DB2 Clients — DB2 client code is required on workstations for remote users to access a DB2 database.

  • DB2 Drivers — Some APIs that are part of a DB2 client are packaged on their own, outside of a client installation. DB2 drivers offer a more lightweight deployment option for specific API connectivity.

  • DB2 Connect — This add-on product provides support for applications executing on Linux, UNIX, and Windows to transparently access DB2 data servers on i5/OS, VM/VSE, and z/OS environments. DB2 Connect provides much more than just connectivity; more details of its capabilities are provided later in this chapter.

  • Note - DB2 Connect is not required to access DB2 for any of the LUW platforms.

  • DB2 Replication — This feature provides replication capabilities for DB2 data servers. There are two kinds of replication, SQL-based replication and Queue-based replication (also know as Q-replication). Both are sometimes referred to as Data Propagator (DPROPR).

  • SQL-based replication is included in all of the DB2 mainstream editions that run on Linux, UNIX, and Windows. It's made up of two components, CAPTURE and APPLY. It uses SQL to replay data on target servers.

    Q-Replication is built on the IBM WebSphere MQSeries technology and is generally thought to be a more available and powerful replication technology. You can add this capability to a DB2 environment through one of the WebSphere Information Integrator products.

  • WebSphere Information Server or WebSphere Federation Server — The WebSphere brand has a suite of products that help you publish, place, cleanse, transform, enrich, and access data across heterogeneous data sources.

  • WebSphere Application Server — A part of this application server is shipped with DB2. It allows developers to use Java as a platform in a transaction processing environment.

DB2 Clients

Once a DB2 application has been developed, you need to install connectivity software on each client workstation in a two-tier environment. If you are deploying a Web-based application in a three-tier environment, you need to, at a minimum, install the DB2 client connectivity software on the application server. In DB2 9 there are basically two types of clients, the DB2 Runtime Client and the DB2 Client.

A DB2 Runtime Client provides the minimum client footprint (about 20–30 MB) to support connectivity to DB2 9 data servers (the exception to this is if you choose to support communication via specific API drivers, covered later in this section). When you install a DB2 Runtime Client, you install a driver to support all the common programming methodologies, including ADO.NET, ADO, OLE DB, ODBC, JDBC, SQLJ, static SQL, and more.

Note - In this chapter, the use of the term client with a lowercase c refers the DB2 client software in general, while an uppercase C refers to a specific DB2 client package.

Using a DB2 Runtime Client you can perform basic functions, such as running any DB2 commands or SQL statements from a DB2 CLP or CLP session; however, for the most part, this client comes with no other capabilities than supporting data connectivity. For example, there are no graphical-based management tools, documentation, and so on. You can download a DB2 Runtime Client at http://www14.software.ibm.com/webapp/iwm/web/preLogin.do?lang=en_US&source=swg-dm-db2rtcl.

Note - The DB2 8 Runtime Client is very different from the DB2 9 Runtime Client. In DB2 8, there was a special client only available on Windows called the DB2 Runtime Client Lite. This client had a reduced footprint compared to the DB2 9 Runtime Client and didn't include tooling, documentation, and so on. The DB2 9 Runtime Client is analogous to the DB2 8 Runtime Client Lite, only is now available on all supported platforms.

A special DB2 Runtime Client is made available as Windows Merge Modules (.msi files), which makes the installation of this software within Windows applications more natural. You can download these files at http://www-304.ibm.com/jct03002c/software/data/db2/runtime.html.

In contrast, the DB2 Client includes all the functions found in the DB2 Runtime Client plus functions for client-server configuration, tools for database administration and application development, samples, and more. For example, this client includes the Configuration Assistant that provides graphically administration and connectivity tools, as well as a host of programming samples, and so on. The DB2 9 Client replaces the functions found in both the DB2 8 Application Development and DB2 8 Administration clients.

A DB2 Client's footprint is directly correlated to the components you select to install and can take between 200 MB and 800 MB depending on the options you select. The process to install a DB2 Client is very similar to a DB2 data server. You can download a DB2 Client at http://www-304.ibm.com/jct03002c/software/data/db2/runtime.html.

The choice of which DB2 client to install should be based on the requirements of the application on the client machine. For example, if you have a database application developed for Linux, UNIX, or Windows and you do not require the DB2 administration or application development tools from a Windows workstation, yet want to support applications written in multiple programming languages like .NET and Java, you should install the DB2 Runtime Client on that workstation.

Some enterprises prefer to deploy the DB2 Runtime Client code remotely on a dedicated server. In these environments, remote workstations need to access the DB2 Runtime Client code remotely from a code server before accessing to DB2. This type of configuration is known as a thin client. A thin-client configuration can provide many benefits, such as a central catalog directory of all database connections and a single footprint of code to manage. The trade-off is that clients must load the client .dlls from the code server before accessing the remote data server. It should be noted however that this performance hit is only "noticed" on the initial connection. Once the client code is loaded on the client workstation, subsequent calls to the code server are not needed.

When you want to deploy your DB2 application, you only need to ensure that a DB2 Runtime Client is installed on each workstation executing the application. Figure 1-10 shows the relationship between an application, a DB2 Runtime Client, and the DB2 data server. If the application and database are installed on the same server, connectivity is considered to come from a local client. If the application is installed on a system other than the DB2 data server, its connectivity is considered to come from a remote client.

Figure 1–10
Accessing a DB2 data server using the DB2 Runtime Client

DB2 client connectivity can be configured using various supported communication protocols. The supported protocols vary according to operating system:

  • TCP/IP — used in all environments

  • Named Pipe — used in Windows environments

DB2 Drivers

Some larger enterprises and independent software vendors (ISVs) want to deploy their applications written in single language without the overheard of installing and maintaining a DB2 client on each client workstation. Despite the much smaller footprint of the DB2 Runtime Client in DB2 9, IBM makes available two drivers that can be deployed for connectivity on their own, outside of a DB2 Runtime Client or DB2 Client installation.

The IBM Driver for JDBC and SQLJ can be deployed to support Java-based connectivity to a DB2 data server. This driver is about 2 MB in size and can easily be embedded within your application. In fact, it comes with a royalty-free distribution license for this very purpose.

You should note however that although this driver can connect to the entire DB2 family, if you're connecting to a DB2 for i5/OS, DB2 for VM/VSE, or DB2 for z/OS data server, you need to additionally license this connectivity with DB2 Connect (covered in the next section) to ensure you are compliant with respect to licensing.

It's important to note that the IBM Driver for JDBC and SQLJ solely supports Java applications. Aside from not including tooling and documentation, this driver doesn't support data server connectivity using other APIs. For example, you can't support a .NET application using this driver. You can download this driver at http://www14.software.ibm.com/webapp/download/preconfig.jsp?id=2004-09-20+10%3A09%3A21.003415R&cat=database&fam=&s=c&S_TACT=105AGX11&S_CMP=DB2.

DB2 9 includes a new standalone driver for CLI and ODBC applications called the IBM DB2 Driver for ODBC and CLI. This driver delivers the same benefits and restrictions as the IBM Driver for JDBC and SQLJ to your DB2 environment, except it solely supports CLI/ODBC connections. You can download this driver at http://www14.software.ibm.com/webapp/download/preconfig.jsp?id=2004-09-20+13%3A44%3A48.813589R&cat=database&fam=&s=c&S_TACT=105AGX11&S_CMP=SPLT.

The main benefits of the IBM DB2 Driver for ODBC and CLI are:

  • Light-weight deployment solution for ODBC/CLI applications

  • Much smaller footprint (25M installed) than DB2 RTCL and DB2 Client

  • No need to have DB2 RTCL or DB2 Client installed on the client machines

  • Same concept as the JDBC/SQLJ driver but for ODBC/CLI applications

DB2 Connect

DB2 Connect is a separate product family that licenses client connectivity from distributed platforms to the DB2 family running on i5/OS, VM/VSE, and z/OS operating systems (hereafter referred to as mainframe). Although communications between any members of the DB2 family use the Distributed Relational Database Architecture (DRDA), you have to explicitly license access from DB2 for Linux, UNIX, and Windows clients to these data servers.

A client accessing DB2 running on the mainframe is generally referred to as a DRDA Application Requester (DRDA AR) and the DB2 server that manages the client connection as a DRDA Application Server (DRDA AS). DB2 Connect only uses TCP/IP as the transport protocol between a DRDA AR and DRDA AS.

Note - If you are connecting to DB2 for Linux, UNIX, and Windows from a client running on a mainframe, you don't need DB2 Connect. DRDA AS functionality is built into DB2 data servers running on distributed platforms.

Some of the major capabilities provided by DB2 Connect include:

  • Support for programming APIs such as ADO, ODBC, OLE DB, CLI, JDBC, SQLJ, ADO.NET (and more) to DB2 running on the mainframe.

  • Federation across the DB2 and Informix data servers. You can extend the reach of this federation capability to heterogeneous sources by adding either WebSphere Federation Server or WebSphere Information Server.

  • Connection pooling and the more powerful connection concentration services that provide minimal resource consumption on the mainframe.

  • System z Sysplex exploitation for failover and load balancing. For example, DB2 Connect includes high-availability features such as z/OS Workload Manager Integration (WLM) and automatic re-direct to a data-sharing group.

  • Integration into mainstream development environments for mainframe data access. For example, DB2 Connect makes data artifacts on the mainframe (including nonrelational-related services like CICS and VSAM) transparent to the world's most popular IDEs; for example, a .NET developer can use Microsoft Visual Studio 2005 and build stored procedures without knowing where the data actually resides.

DB2 Connect is often used to enable mainframe resources for Web access, better integrate legacy and heritage systems with new deployments on distributed platforms, modernize the application development experience for mainframe development (for example, moving from COBOL to Java), and off-loading development cycles to lower-cost operational environments.

Depending on the DB2 Connect edition you purchase, you can create a connectivity architecture for single- or multi-tier environments.

Figure 1–11
Using DB2 Connect is a single-tier or multi-tier environment

You can see in Figure 1–11 that there are many ways to implement a DB2 Connect product. In a multi-tier environment, you use DB2 client software to connect to the DB2 Connect gateway (in a client/server model) passing the connection context to to the mainframe resource. In a Web-based deployment, you may have DB2 Connect on its own or collocated with the application server. Depending on your configuration, you may or may not need to install a DB2 client.

DB2 Connect products can be added on to an existing DB2 data server installation, or act as a standalone gateway. Either way, it's purchased separately from DB2. If you are using one of drivers discussed earlier in this chapter, technically you can make the connection to a mainframe DB2 resource, but you still need to purchase a valid DB2 Connect license. There are a number of DB2 Connect editions available and each is suited for a particular implementation of the DB2 Connect software. With the exception of DB2 Connect PE, all of the DB2 Connect editions offer the same function and features; they are merely differentiated by capability. All editions of DB2 Connect PE are considered gateways since they provide client/server access to workstations. To connect to a DB2 Connect gateway, you simply use a DB2 client.

DB2 Connect Personal Edition (DB2 Connect PE) is similar to DB2 Personal Edition in that it is used for single workstations and doesn't provide its users with any server capabilities. Using DB2 Connect PE, you can make direct connections to DB2 running on the mainframe. In Figure 1–11, the desktop users likely have DB2 Connect PE installed in their workstations. DB2 Connect PE can also act as a regular DB2 client and connect to distributed versions of the data server as well. Some environments deploy DB2 Connect with both direct and gateway connectivity. For example, a developer may make direct connections using the DB2 Connect PE software for certain stages of the development cycle. But once the application is in production, they may use the DB2 Connect PE software to connect to a DB2 Connect gateway to take advantage of the features it provides, such as load balancing, connection optimization, high-availability, and more. DB2 Connect PE is licensed on a per-workstation basis.

DB2 Connect Enterprise Edition (DB2 Connect EE) can be licensed using two different user-based options: the number of Authorized Users or the number of Concurrent Users. DB2 Connect EE provides the ability to implement a three-tier connectivity architecture that allows you to isolate connectivity to mainframe resources to a specific tier in your architecture. Again, DB2 clients (or drivers) are used to connect to the gateways, and then the DB2 Connect software manages the connection context to the mainframe.

DB2 Connect Application Server Edition (DB2 Connect ASE) is licensed by the total VU rating of all the applications servers that connect to the mainframe. The VUs that you purchase have nothing to do with how many DB2 Connect servers you set up, or what their rating is. For example, if your Web server farm was rated at 1,000 VUs and you set up a DB2 Connect gateway farm rated at 5,000 VUs, you would have to buy 1,000 VUs of DB2 Connect ASE. DB2 Connect ASE is well suited for environments where you expect to see more growth of mainframe resource than your Web tier because its costs are directly linked to the VU rating of the Web serving tier.

DB2 Connect Unlimited Edition (DB2 Connect UE) is available for i5/OS and z/OS. DB2 Connect UE for i5/OS is licensed by the number of managed processors attached to the i5/OS partition while DB2 Connect UE for z/OS is licensed with a base server license plus a per-unit charge for the MSU rating of the System z server you are connecting to. DB2 Connect UE is well suited for environments that have flat mainframe cycle growth and heavy Web tier growth as the costs of DB2 Connect UE are directly correlated with the capacity of the mainframe resource. DB2 Connect UE comes with a free copy of Mobility on Demand to move mainframe data to occasionally connected devices, as well as DB2 Connect PE. Essentially, this edition of DB2 Connect, as its name would imply, allows for unlimited deployments.

There's so much more to the DB2 Connect product than what's detailed in this section. In fact, the name DB2 Connect is misleading because connectivity is just a small part of what this product can do; however, these details are outside the scope of this book.

DB2 Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.