Introduction

IBM WebSphere Federation Server V9.1 (WFS) introduces several new features designed to make the product more usable and versatile. It also introduces some valuable performance enhancements which are described in detail in a companion paper, Performance enhancements in IBM WebSphere Federation Server V9.1, Part 1. This article examines the performance characteristics of four new features that add new functionality and potentially enable new types of applications. Where applicable, you'll learn about configuration choices that maximize performance when using the new functionality. We cover the following new capabilities:

• Two-phase commit for federated statements
• Access to Oracle and Sybase stored procedures through federation
• Ability to update LOBs in remote DB2 databases through federation
• Ability to run 32-bit wrappers in 64-bit instances of V9 Federation Server on UNIX platforms

1.0 Federated two-phase commit

Federated two phase commit (F2PC) adds desirable functionality to WebSphere Federation Server in that it enables applications to perform multisite updates with full transactional consistency. For a complete discussion on its use, implementation, and possible configurations, please consult "Handle distributed transactions with federated two-phase commit in WebSphere Federation Server Version 9.1" in the Resources section or Chapter 8 of the "Administration Guide for Federated Systems"(Resources). Our discussion here attempts to:

• Characterize performance impacts on existing federated transactions that do not require F2PC
• Point out configuration choices that materially affect performance
• Briefly compare performance of WebSphere Federation Server v9.1 with that of the IBM DB2 DataJoiner V2.1.1 product for multisite two-phase commit transactions, and give some sense of the performance capabilities of WFS F2PC in a typical environment

1.1 Federated two-phase commit performance impact, and influence of configuration

In order to be able to perform multi-site transactions with F2PC protection, each federated remote source that is involved in the transaction must be enabled for F2PC. A remote source is enabled for F2PC by setting the DB2_TWO_PHASE_COMMIT server option to 'Y'(its default setting is 'N'). The server option can be set in two different ways, depending on whether the capability is to be enabled only for a particular application or for all federated applications that access that source.

1. A particular application can enable one or more remote sources for F2PC directly after connecting to the federated server using the SET SERVER OPTION directive for each remote server. In this case, the specified remote sources are enabled for F2PC for transactions initiated from within this application for the duration of its connection to the federated server. It is not possible to enable or disable the F2PC capability for a given remote source "mid-stream"; that is, once F2PC is enabled for a given source from within an application, the capability remains in effect for that source until the application disconnects from the federated server.
2. The DBA can enable F2PC for a particular remote source through use of the ALTER SERVER command. This method, which can be thought of as "global" or "permanent" enablement, allows any present or future application to involve the specified source in a multi-site update with F2PC support.

A key point is that while enabling a remote source for F2PC by either method allows that source to participate in multi-site update transactions, it also incurs an overhead that affects all transactions (including read-only transactions), whether they are multi-site or not. Enabling F2PC causes the federated server to use the XA protocol to coordinate all transactions, and adds extra network round-trips and extra disk writes compared to the case in which F2PC is not enabled. Use of the XA protocol allows the federated server to implement two phase commit semantics with the data source, and the additional disk writes ensure that the federated server is able to track global transaction state and recover from any possible failure.

The following two figures show how enabling F2PC for a particular remote source affects the network traffic and disk writes for insert/update/delete and read-only transactions involving that source. Operations that take place if F2PC is not enabled are depicted in blue. Additional operations that take place if F2PC is enabled are shown in red. The first figure applies to a single-site insert or update:

The second figure applies to a single-site read-only transaction

The additional operations are needed to implement F2PC functionality for multi-site update transactions that require it, but represent an overhead for those single-site transactions that do not require F2PC. For single-source transactions, enabling F2PC for a given data source causes three additional network round-trips, and between three (for read-only) and four (for insert/update/delete) additional disk writes. This overhead is constant and not otherwise dependent on the work done by the transaction. Thus, the overhead will be most noticeable for transactions that are small to begin with, and less significant for long transactions.

The additional log disk writes, most of which take place at the federated server, represent the greatest performance impact on single-site transactions of enabling F2PC. This impact can be minimized by careful placement of the federated server logs on high-performance disks. Best performance is obtained using fast disks with write caching; i.e. those that can cache I/Os to memory in the disk subsystem and guarantee that they will be written to the disk media eventually. Using slow devices without write caching for the federated server logs (such as internal SCSI drives) increases the impact of enabling F2PC because the additional disk writes take longer.

The following chart shows scaled throughput of a representative order entry workload (more details in the following section) involving a single remote source with ten concurrent clients over a period of an hour. The workload contains a mixture of read-only and insert/update/delete transactions. Since only a single remote source is involved, enabling F2PC is not necessary -- there are no multi-site transactions. However, the intent is to simulate a situation where a remote site is enabled for F2PC because some transactions within an application require it, whereas others do not. We are interested in the effect of enabling F2PC on single-site transactions that do not, by themselves, require F2PC.

The chart shows the effect on scaled transaction throughput (units: transactions/time) in three situations, listed in descending order of performance:

• F2PC to the remote source is not enabled (blue)
• F2PC to the remote source is enabled, and the federated server's logs are on a disk with write caching (yellow)
• F2PC to the remote source is enabled, and the federated server's logs are on a disk without write caching (green)

For this single-source workload, enabling F2PC reduces throughput by around 7% if the federated server's logs are on disks with write caching, and about 35% if the logs are on disks without write caching. However, please note that the impact of F2PC on single-site transactions will vary depending on your workload. Transactions that are very short to begin with will be impacted to a greater extent. In all cases, careful placement of the logs on the federated server will minimize F2PC impact and increase transaction throughput.

1.2 Comparison with IBM DB2 DataJoiner V2.1.1

WebSphere Federation Server v9.1 is the first release to offer federated two-phase commit capability for multi-site transactions since IBM's predecessor federation product, DataJoiner (DJ). Those customers migrating from DataJoiner to WFS V9.1 can be confident that the new release offers equal and often better performance for federated transactions, whether they require F2PC or not. WFS V9.1 is slower than DJ only under one set of very specific circumstances - namely for single-site read-only transactions to a site for which F2PC is enabled, when the federated server's log disks do not have write caching. But if log disks with write caching are used on the federated server, then WFS V9.1 is faster than or comparable to DJ for transactions that involve reads, writes, or a mixture of both, for single-site transactions with and without F2PC enabled, and for multi-site transactions with F2PC enabled.

The following chart compares V9.1 Federation Server with DataJoiner for a representative multi-stream order entry workload. The workload has five different transaction types in defined proportions. The transactions involve one or two federated sources and comprise two multi-site update transactions (49%), two read only transactions (8%) and one single-site update (43%). F2PC is enabled for both sources. The most important transaction is called "new order". It is a read/update transaction involving both sources that accounts for 45% of the workload. The chart shows the scaled rate of NewOrder transactions over time for both products, using federated server log disks with and without write caching in each case. Two things are apparent from the chart :

• For a given type of log disk (with or without write caching), Websphere Federation Server achieves a 16% - 26% higher transaction throughput than DataJoiner.
• For a given product (DJ or WFS), transaction throughput can be increased significantly by choosing log disks with write caching on the federated server.

1.3 F2PC performance in a modest sample environment

What sorts of transaction rates can you expect to achieve with WebSphere Federation Server in an environment requiring federated two-phase commit? While the answer naturally depends on the characteristics of the transactions and the configuration of the systems involved, we thought it would be useful to provide the results of a simple test using the modest available equipment in our laboratory -- several older pSeries machines.

The hardware was configured in a three-tier environment using various pSeries AIX servers. Thirty concurrent clients ran on a smaller front-end server, and submitted transactions to a federated server on a separate machine. The federated server ran applications that referenced nicknames to the actual tables used in the transactions on both back-end DB2 database servers.

We again used the order entry workload described briefly above. The nine tables used in the workload are split across the two back end DB2 databases. From the federated server, both are enabled for F2PC. There are five transaction types submitted by the clients in defined proportions as explained above. The most important of these is a multi-site read/update called "new order" that makes up about 45% of all transactions. The workload involves other transactions as well, but these are considered a background load. The transaction rate was allowed to stabilize and was run for an hour.

We measured an aggregate transaction rate of about 157 transactions per second, of which 67 were multisource New Order transactions. The federated server's CPU had a utilization of about 73%, and an I/O rate of about 1.4 MB/sec to the log. Please remember that these numbers are simply one data point, obtained for a given transaction mix in a very modest environment.

2.0 Federated stored procedures

WebSphere Federation Server V9.1 introduces the capability to call stored procedures defined on remote sources. This capability is described in detail in "Use federated procedures in WebSphere Federation Server", see Resources. Initially, this capability is available for remote Oracle and Sybase databases, with support for others (including MS SQL Server and DB2) to follow. A federated stored procedure can be thought of as a nickname for a remote stored procedure. A calling application connected to the federated server cannot distinguish the federated stored procedure from a local stored procedure. Federated stored procedures may have input parameters and can return up to one result set.

Naturally, we are interested in understanding what overhead is added to a remote stored procedure call when it is issued through the federated server. Calling a remote stored procedure with no result set means that the overhead added by federation is limited to name and type mapping between local and remote names and input parameters. In the case where a result set is returned, result set type mapping and data movement back to the federated server are added to the overhead. Accordingly, we present measurement results that distinguish between two different cases:

1. Calling a federated stored procedure with no result set
2. Calling a federated stored procedure with one result set

2.1 Overhead of federated stored procedures with no result set

Our performance tests were conducted with a supported remote source. For both this and the following measurements, we used a simple JDBC client that connected either directly to the remote source back end via its native "thin" JDBC driver or to Federation Server via the DB2 JDBC driver. The JDBC client called the remote stored procedure either directly or using a corresponding federated stored procedure. To minimize variability due to network transfers, the federation server and the remote server were located on the same physical system. Thus, the results obtained here don't include network latency.

For the case in which the stored procedure returned no result set, we expected that the overhead of federation would be most noticeable for a very short stored procedure and less noticeable for a longer one. Accordingly, we experimented with a stored procedure that contained just one SQL statement (an update or an insert) and then added additional SQL statements to its body to extend its execution time. The following chart compares elapsed time of the remote native stored procedure with that of its corresponding federated stored procedure (red and blue columns, respectively). The elapsed time overhead, expressed as a percentage, is shown by the yellow line. As expected, the overhead is highest for the shortest stored procedure with just one statement, and drops proportionately for longer procedures with more statements.

2.2 Overhead of federated stored procedures with a result set

Our experimental setup was the same as for the last tests, except that the remote stored procedure was written to return a result set whose size depended on an input parameter. Result sets of between 10,000 and 600,000 rows were tested. The JDBC application returned these as a ResultSet object from either the federated stored procedure or the remote native stored procedure. Elapsed time measured in both cases was quite close, with the elapsed time per row returned by the federated stored procedure actually somewhat less than that of the native stored procedure.

This result demonstrates an unavoidable shortcoming of the test- namely, that in addition to comparing federated and native stored procedure execution, the test implicitly compares the remote source and DB2 JDBC drivers. It seems likely that for return of a result set, the overhead of federation is more than compensated for by an apparent performance difference between the JDBC drivers. We considered using a different test setup; for example, comparing stored procedure calls from the remote source's command-line interface with federated stored procedure calls via the DB2 CLP, but this setup also implicitly compares the remote source's application interface with the DB2 CLI interface. We conclude that for large result sets, the overhead of federation in a Java™ application environment is not significant and is comparable to other factors (such as choice of or configuration of JDBC driver) within the environment.

3.0 Insert/update of LOB columns in DB2 nicknames

While earlier releases of Federation Server have supported the ability to insert or update from Oracle nicknames containing LOB columns, V9.1 adds this ability for DRDA nicknames (DB2 LUW, DB2/z, and DB2 for iSeries sources).

Our brief tests compared access to DB2 LUW tables containing one or more LOB columns through both normal client and federated connections. In one case, a CLI test application accessed DB2 base tables, and in the other case, the same application accessed nicknames pointing to the same tables. The application and federated server were co-located on the same machine; the database with the actual tables was on a different, LAN-connected machine. The experimental results showed that federation increases the elapsed time of I/U operations on DB2 nickname LOBs by about 25% in the environment described.

Federated select operations involving LOB columns are also about 25% longer against DB2 nicknames than against the underlying DB2 base tables if the SQLGetData API is used. If the SQLFetch API is used, federated selects of LOB columns are much slower, as elapsed time doubles with respect to the same selects run against the underlying base tables using a regular client connection. The performance difference between the SQLGetData and SQLFetch APIs merits further investigation.

Unlike regular data types, remote DB2 LOB columns which are materialized within the federated server do not use the bufferpool of the federated server; rather, they are staged in the temporary tablespace. For this reason, a prime performance recommendation for federated applications that handle LOBs is to ensure that the temporary tablespace is large enough and is placed on fast disks (preferably with write cache).

4.0 Bridging code for 32-bit wrappers in 64-bit instances

WFS V9.1 is based on DB2 9, which is available on UNIX platforms only in a 64-bit version. In contrast to earlier DB2 releases, there is no 32-bit version of WFS V9.1 on UNIX. In order to accommodate Federation server users with 32-bit wrappers for sources with no available 64-bit wrapper, V9.1 has the capability to accommodate 32-bit wrappers in its otherwise 64-bit environment.

Normally, wrappers may be run trusted (inside the db2agent process) or fenced (in a separate process called the db2fmp). In order to use 32-bit wrappers on a 64-bit version of WS Federation Server V9.1, the wrapper must be run fenced. The so-called bridging code runs within the db2fmp process and deals with the mismatch between the wrapper's "bitness" and that of the surrounding process environment.

Experiments show that the bridging code has virtually no performance impact by itself. That is, SELECTs run with a 32-bit wrapper and the bridging code in a 64-bit environment were only 1% slower than the same queries run with a fenced 64-bit wrapper for the same source. This means that users running 32-bit wrappers in fenced mode in earlier releases who upgrade to V9 and continue to use their 32-bit wrappers with the bridging code will notice almost no degradation.

However, users running 32-bit wrappers in trusted mode in prior releases will need to run them in fenced mode in V9, and this change alone can have considerable performance impact. Moving from a trusted to a fenced wrapper can impact simple selects against one nickname by between 5% and 15%, and more complex queries, such as multi-source nested joins, can degrade by substantially larger margins. The separate effects of moving from trusted to fenced wrappers and also using the bridging code are shown in the following figure:

Thus, users migrating to WFS V9.1 from an earlier release who must continue to use a 32-bit wrapper will experience virtually no performance degradation if they are already running the wrapper in fenced mode. 32-bit wrapper users who are accustomed to trusted mode may experience some workload-dependent performance degradation when they migrate to the fenced wrapper that is a prerequisite for the 32-64-bit bridging code.

5.0 Conclusion

We've looked at the performance characteristics of several new features in IBM Websphere Federation Server V9.1. Federated two phase commit performs well compared to its predecessor implementation in IBM DataJoiner® V 2.1.1. It has some performance impact on transactions that do not require F2PC, but the impact can be managed with careful attention to log disk configuration. The capability to update and insert rows of DB2 nicknames with LOB columns is associated with a reasonable overhead compared to the same operations on base tables, and the performance of federated stored procedures compares well with calling the underlying stored procedures through native interfaces. Finally, aside from the impact of running a fenced wrapper, there appears to be no disadvantage to running a 32-bit wrapper in a 64-bit environment.

IBM Websphere V9.1 Federation Server offers attractive new functionality with favorable performance characteristics. These enhancements represent a continuing effort to increase the usability and value of Federation Server for its customers with minimal performance impact.

Footnotes

1.Some database systems, including DB2, actually store the second highest and second lowest values in a column , instead of maximum and minimum values. The motivation is to avoid "flag" values such as -1 and 99999 which will distort the true range of values. Depending on the remote source, values for HIGH2KEY and LOW2KEY stored in the federated server's catalog for nickname columns may be either actual maxima and minima or second highest/lowest values, respectively.

2.Some nickname-only queries in DPF (partitioned) systems may be executed using access plans that make use of computational partition groups. These plans achieve asynchrony among remote sources as a side effect

Resources

• Handle distributed transactions with federated two-phase commit in WebSphere Federation Server Version 9.1 (developerWorks, November 2006): The article by A. Chang, S. Harris and F. Sun describes the new federated 2PC capability and includes detailed information on configuration and use.
  
  
• Use federated procedures in WebSphere Federation Server (developerWorks, May 2006): The article by D. Bhatia, Q. Hu and H. Zeller describes the ability to define and call remote procedures through Federation Server from an application developer's point of view.
  
  
• Using data federation technology in IBM WebSphere Information Integrator: Data federation design and configuration (developerWorks, June 2005): The article by A. Betawadkar-Norwood, E. Lin and I. Ursu introduces you to federated data concepts and discusses design and configuration considerations for WebSphere Information Integrator (former name of Federation Server).
  
  
• Using data federation technology in IBM WebSphere Information Integrator: Data federation usage examples and performance tuning (developerWorks, June 2005): The article by A. Betawadkar-Norwood, E. Lin and I. Ursu discusses federated query optimization, demonstrates usage examples, and discusses performance tuning considerations for WebSphere Information Integrator (former name of Federation Server).
  
  
• IBM WebSphere Information Integration Administration Guide for Federated Systems: The book is the basic product documentation for WebSphere Federation Server.
  
  
• The DB2 for Linux, UNIX, and Windows SQL Reference, Volume 1 and The DB2 for Linux, UNIX, and Windows SQL Reference, Volume 2: These books are your fundamental references for SQL use with DB2 for Linux®, UNIX, and Windows®.
  
  
• IBM DB2 for Linux, UNIX, and Windows Command Reference: The book is the DB2 product documentation that provides descriptions of DB2 commands.
  
  
• http://www.ibm.com/software/data/integration/federation_server: Visit this site to learn more about WebSphere Federation Server.
  
  

About the authors

Susanne Englert is a member of the Websphere Federation Server performance team and has been working with the product since late 2001. She has worked extensively in the area of database performance and is interested in query optimization, parallel query processing, federated queries, and customer use cases. Susanne is a graduate of the University of Bonn and past chair of the TPC's Decision Support benchmark development subcommittee.

Simon Harris is a Performance Engineer within the WebSphere Federation Server development team in the Silicon Valley Laboratory. Simon has been working with federated database technology since its inception in IBM in 1995, supporting many customers in both a pre- and post-sales capacity throughout Europe, the Middle-East, and Africa.

Holger Kache is an advisory software engineer at the IBM Silicon Valley Laboratory. He is a member of the Information Integration Solutions organization with expertise in data integration and performance. Holger obtained his masters degree in Computer Science from the Brandenburg Technical University Cottbus, Germany before joining IBM in 2001.

Rate this article

Comments

Back to top

Table of contents

• Introduction
• 1.0 Federated two-phase commit
• 2.0 Federated stored procedures
• 3.0 Insert/update of LOB columns in DB2 nicknames
• 4.0 Bridging code for 32-bit wrappers in 64-bit instances
• 5.0 Conclusion
• Footnotes
• Resources
• About the authors
• Comments

Local resources

• IBM Innovation Center - Austin, TX
• IBM Innovation Center - Chicago, IL
• IBM Innovation Center - Dallas, TX
• IBM Innovation Center - San Mateo, CA
• IBM Innovation Center - Waltham, MA
• Technical Briefings in North America

Dig deeper into Information Management on developerWorks

Try IBM PureSystems. No charge.

Experience today!

---

Get started with the cloud-based trial and pattern development kit.

Special offers