We live in a heterogeneous data world. Sooner or later, we are going to have to integrate the data we have stored in DB2 with data stored in other database management systems.
DB2’s federated support allows data from many different databases to be accessed as if it were local tables within a DB2 database. Federated support to other DB2 family databases is included as standard in all versions of DB2. Support for other DBMS types is included with InfoSphere Warehouse, although with some limitations on the use cases, or by adding InfoSphere Federation Server to an existing DB2 installation.
In this article, we will explore the basic principles of DB2 federation and then describe the specific case of setting up federation to SQL Server.
Federation Basics
Federated support within DB2 is enabled at an instance level by setting a parameter:
db2 “UPDATE DBM CFG USING FEDERATED YES”
The DB2 instance will normally have to be restarted to bring this change into effect.
Within a database, a number of objects must then be defined:
- Wrapper: defines the target system type including details of the connectivity libraries to use
- Server: defines the remote connection to be made, providing information such as host names, port numbers and any DBMS-specific information such as versions and database names
- User Mapping: defines the mapping between a DB2 authentication ID and the authentication details for the remote connection. Mappings can be between individual IDs or under some circumstances, can be wildcarded.
- Nickname: defines a DB2 name (including schema) for a particular remote table.
Installing ODBC Drivers on UNIX Systems
Connectivity to SQL Server is established using an ODBC connection. On Windows servers, this is available as standard, and configuration is via standard Windows tools. However, on UNIX systems, ODBC is not available by default and must be installed separately. Since this is so fundamental to the SQL Server connectivity, we will describe how to set this up.
There are a number of IBM-supported ODBC connectivity on UNIX systems. For example, on AIX there are three different options:
- IBM badged drivers produced by DataDirect
- Standard DataDirect drivers
- Open source drivers from the OpenODBC project
In this case, we will use the IBM badged drivers. These are available from –
ftp://ftp.software.ibm.com/software/db2ii/downloads/odbc_driver/
The file required for AIX is currently isv95-or-above-odbc-aix64-fs-110531.tar.gz
The ODBC driver must be installed as root and uses a graphical interface. As a reminder, this means that if you are working remotely over SSH you will need to run a local X server (Xming is a good free one for Windows) and enable SSH agent and X11 forwarding. Also, when switching to root from a regular user, you will need to use simply “su” not “su –“ to preserve the X $DISPLAY environment variable.
To install the ODBC driver, untar the file into an empty directory. Then, as root, run –
./odbcsetup.bin
This will bring up a graphical installer. It is fine to take all the defaults, which will install the product into /opt/IBM/WSII/odbc : however, adjust according to your site standards, if required. If you expect to perform this install on multiple servers, create a response file for use by subsequent installs, thus avoiding the need for a graphical environment on all servers.
Configure ODBC Driver
Once the ODBC driver software has been installed, it must be configured.
First, set some additional environment variables for the DB2 instance. We assume here that ODBC has been installed in the default location and that our DB2 instance is db2inst1 with an instance home directory as /home/db2inst1. Edit /home/db2inst1/sqllib/userprofile and add the following extra lines:
export ODBCINI=/home/db2inst1/odbc.ini
export DJX_ODBC_LIBRARY_PATH=/opt/IBM/WSII/odbc/lib
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$DJX_ODBC_LIBRARY_PATH
export LIBPATH=$LIBPATH:$DJX_ODBC_LIBRARY_PATH
The first line sets the location of the ODBC configuration file. The second line sets up an environment variable pointing to the ODBC shared libraries, with the third and fourth lines adding this path to standard UNIX library paths.
While these environment variables are needed for DB2, another file is needed to make the ODBC driver available to federation. Create /home/db2inst1/sqllib/cfg/db2dj.ini if it does not already exist and ensure it has the following contents:
DJX_ODBC_LIBRARY_PATH=/opt/IBM/WSII/odbc/lib
ODBCINI=/home/db2inst1/odbc.ini
Finally, create the ODBC configuration file /home/db2inst1/odbc.ini with the following contents:
[ODBC Data Sources]
mssqlserver=Microsoft SQL Server
[ODBC]
Trace=0
TraceDll=/opt/IBM/WSII/odbc/lib/odbctrac.so
InstallDir=/opt/IBM/WSII/odbc
quotedID=yes
[mysqldb]
Driver=/opt/IBM/WSII/odbc/lib/VMmsss24.so
Description=Microsoft SQL Server Driver
Address=
Database=
QuotedId=No
The parameters in
You should then log off and on from db2inst1 (to refresh the DB2 environment), and then, stop and start the DB2 instance to bring all these changes into effect.
To test the driver works, use the example program supplied with the ODBC driver. Run:
/opt/IBM/WSII/odbc/example/example
You will be prompted for the data source name. In our case, this is mysqldb. You will be then asked for the user name and password. Note that all three are case sensitive.
If everything is successful, you should get the messages:
SQLSTATE = 01000
NATIVE ERROR = 5701
MSG = [IBM(DataDirect OEM)][ODBC SQL Server Driver][SQL Server]Changed database context to '
Enter SQL statements (Press ENTER to QUIT)
SQL>
A simple test SQL to issue, which will work on any SQL Server database, is:
SELECT * from sys.tables;
To exit the command line, simply press
Preparing DB2 for Federated Connections
If your DB2 version is not InfoSphere Warehouse, you will need to install InfoSphere Federation Server to obtain the relational wrappers needed to connect to SQL Server and other non-IBM databases.
Even if you are using InfoSphere Warehouse, it is recommended to apply the latest DB2 fix level from IBM Fix Central (http://www-933.ibm.com/support/fixcentral/).
One word of caution about fix levels:
It is documented on the IBM website that you can apply standard DB2 fixpacks to the DB2 ESE supplied with InfoSphere Warehouse. If, however, you apply these fixpacks in the widely recommended method by installing a server fixpack into a separate location and then migrating the instance to it (so that you can easily fallback if needed), be aware that the standard fixpacks do not contain the relational wrappers and other bundled federation features, so you will lose this functionality following the instance migration.
Instead, you should use the DB2 fixpack supplied as part of the Federation Server fixpacks. These may not appear at exactly the same time as the base DB2 fixpacks. They may also be at a slightly later fix level than the base fix level: we found that the Federation Server install would not work on top of a DB2 upgraded from the base DB2 fixpack because of this difference in fix level.
Once you have your DB2 updated with the fix level from within the Federation Server, it is time to install the relational wrappers and other Federation Server components. As root, run:
./iisetup
Again this is a graphical installer so the same advice about SSH forwarding applies as for the ODBC install. If you are rolling this out on multiple servers, you should create a response file the first time you perform this and use the response file for subsequent installs.
After a number of initial screens, you will be asked whether you wish to install a fresh copy of DB2 or apply the Federation Server components to an existing DB2. The latter option is what you want to do. The next screen should automatically identify the existing DB2 installation directory.
You will then be presented with a list of components to install: the default is to install all components not previously installed (the relational wrappers for DB2 and Informix are already installed in most cases).
You will then be asked if you want to configure the wrappers (first relational and then non-relational). Doing this from the graphical interface might save you a little bit of work later on, but be aware that you will need to already have installed all the drivers and client software you are going to use first if you want this to work correctly. In this case, we are going to not configure the wrappers graphically, but instead go through the steps later from a command line, both to demonstrate what is done, and also to ensure that they are configured exactly as we wish.
Do not be alarmed that the actual size of the software installed is very small (only about 45 MB for 9.7.6) despite the huge size of the fixpack download (nearly 3 GB). Most of the download is the two flavours of DB2 fixpack (server and universal) which are not applied by this install.
Once the install is finished, stop and start DB2 to bring all the changes into effect.
There is one further step required before the federation server is ready for use. One of the shared libraries (the “fenced wrapper library”) must be linked on your system to ensure that it is linked against the correct client connectivity version. To do this, run command djxlinkMssql as root, ensuring you source the DB2 profile first:
. /home/db2inst1/sqllib/db2profile
/opt/IBM/db2/V9.7.6/bin/djxlinkMssql
This produces file libdb2mssql3F.a (on AIX) in the DB2 install’s library location (in our case /opt/IBM/db2/V9.7.6/lib64).
Setting Up Federated Link
Now that all the required software is installed, we can now configure federation within a DB2 database. All commands are run after connecting to the DB2 database.
First, create a wrapper for the SQL Server connection (this describes the AIX library to use for connecting):
db2 “CREATE WRAPPER
The name
Then, define a server specific to the SQL Server database you want to connect to:
db2 “CREATE SERVER
WRAPPER
The wrapper (
Next, create a user mapping to map the DB2 user ID to a SQL Server user ID and password. In this case, we are mapping the DB2 instance owner db2inst1 through to a SQL Server user called BIXtester :
db2 “CREATE USER MAPPING FOR db2inst1 SERVER
This means that if you connect to DB2 as db2inst1, all connections to SQL Server will be made with “
Lastly, create a nickname for each of the tables you want to access. In this example, we created a nickname MYMSSQL.MYTAB01 for the SQL Server table, Table01, which is under the default dbo schema. Note that we must use quotes around the SQL Server schema and table name since the names are case sensitive. The name
CREATE NICKNAME MYMSSQL.MYTAB01 FOR
Because of the need for quotes, you should put this in a SQL file (perhaps nicks.sql) and run it using:
db2 –tvf nicks.sql
You can then access the SQL Server table from within the DB2 database by using the nickname as you would any other DB2 table:
SELECT * FROM MYMSSQL.MYTAB01 FETCH FIRST 10 ROWS ONLY;
Note that the SQL syntax / dialect you use is the DB2 syntax. You do not need to learn the SQL Server dialect. Furthermore, you can use all the functionality provided by the DB2 query engine even although that functionality might not be available within SQL Server. For example, you can use all the DB2 pureXML SQL/XML functionality and DB2 OLAP functions as you would if the nickname were a real table on the DB2 server itself.
The DB2/Federation Server optimizer will try to determine the most efficient way to obtain the data it needs from the SQL Server, using its knowledge of the SQL Server functionality (this is one of the reasons why setting the correct SQL Server version in the server definition is so important).
Another useful facility is the ability to use nicknames as the source SQL for a crossloader (LOAD FROM CURSOR) invocation. Thus, if you have a requirement to move data quickly from SQL Server to DB2, this gives you a means of doing this easily. You can execute the LOAD from the ADMIN_CMD system stored procedure, and schedule it regularly using DB2 ATS (Automated Task Scheduler). This is not only a very efficient way to move the data, but it also automatically takes care of many of the issues experienced when transferring data via files such as codepage translation issues.
Summary
DB2 federation is a very powerful way of bringing together data from disparate systems, whether they are from IBM or other vendors. We have used SQL Server as an example here, since it demonstrates some of the challenges involved in setting up a link (it is probably one of the more difficult connections to set up, especially if your DB2 server is on UNIX rather than on Windows). But, similar connectivity can be set up to other databases, and we have used it widely between different DB2 for LUW databases to other members of the DB2 family (DB2 for z/OS and DB2 for iSeries) as well as third-party databases.
Whether your aim is to consolidate all your data onto one platform (hopefully DB2) or to continue to operate in a heterogeneous environment, DB2 federation can make your path much easier and save you time and money again and again.
- Phil Nelson
teamdba@scotdb.com