Using Federation Server with Optim
30 May 2014
Updated 2 February 2014
For a number of data sources including DB2 for iSeries you need to configure IBM InfoSphere Federation server to enable Optim to access the data. Federation Server is included with the Optim enterprise editions. If you are unfamiliar with Federation Server, this can seem challenging, but it is actually pretty straight forward if you follow the steps in this article. Further, using Federation Server has some advantages over connecting directly to the data source such as the ability to create views and indexes on the data that can help Optim without adding the objects to the data source.
IBM Infosphere Federation Server is a DB2 on Linux, UNIX and Windows (DB2/LUW) database with the ability to connect to other databases and non-relational data sources and present objects from those sources as if they were tables in the DB2/LUW database. So an application like Optim connecting to that DB2/LUW database thinks that it is just using tables in that database, with DB2 reading and writing that data in the real source behind the scenes. For more information on how this works please see my Virtual Databases article. While you may think that using Federation Server is a drag on performance, it actually uses the DB2 optimizer to intelligently issue queries to the sources for great performance. It also allows you to use functions that may not be available in the source, create indexes, views, procedures and other objects on the external data to help performance without altering the data sources at all.
You can get a federated database instance by installing Federation Server, or by applying the Federation Server licenses to an existing DB2/LUW or DB2 Connect Server install. If you already own DB2 Connect it has Federation Server built-in for DB2/i or DB2/z sources (depending you your license). You can have the DB2/LUW federated database on the server with your Optim server or on another server. Once you have a DB2/LUW instance with federation enabled and have picked an existing database to use in the instance or have created a new one, you will need to take a few steps to enable and configure Federation Server. I will outline those steps below and you can get more information from these manuals:
The first thing you need to do after determining which instance to use for federation is to enable federation in the instance by updating the instance (or DBM) configuration. This can be done by logging into a CLP window in the Federation Server Instance and issuing the following command:
UPDATE DBM CFG USING FEDERATED YES
For DB2/iSeries sources you plan to federate into the DB2 database you need to create a catalog entry. This creates a name you can reference to connect directly to the external DB2 source. Here is an example of the commands for an iSeries server named ISERDBX. From what I can tell about iSeries there is only one database per server and the database has the same name as the server so in this example the database name and database server names are both ISERDBX.
catalog tcpip node ISERDBX remote 192.168.0.1 server 446;
catalog database ISERDBX at node ISERDBX authentication dcs;
catalog dcs database ISERDBX as ISERDBX;
Please note that all database names in an instance must be unique whether they are physical databases in the instance or remote databases that just have a catalog entry. I actually created a new database to hold my federated objects for ISERDBX. Since I couldn’t call it ISERDBX because that name was already cataloged, I gave my federated database the name ISERDBF. When I created the alias in Optim for my iSeries database I called it ISERDBX. However, the Optim ISERDBX alias references ISERDBF, because Optim can’t talk directly to an iSeries database, but I wanted my Optim alias to have the same name as the physical database.
After you have cataloged the Series database in DB2/LUW, it is a good idea to bind the DB2 Connect packages to the database. Under the covers Federation Server uses DB2 Connect to talk to the iSeries database and will expect some of the objects created by the bind process to be in the source database. For instructions on how to perform the bind commands please see the “Binding” section in my DB2 Connect article.
By and large, you will need to create a new database to hold your federated objects for iSeries due to code page considerations. Code page conversion can be problematic and you should verify the Coded Character Set ID (CCSID) of the iSeries database. Talk to the iSeries administrator to get this. Most iSeries databases will use EBCDIC encoding and the default CCSID will appear as 65535 or EBCDIC or 37. However, it is possible to have UNICODE databases. You should create a non-UNICODE federated database for non-UNICODE iSeries databases and a UNICODE federated database for UNICODE DB2/i databases. To do this use the CREATE DATABASE example below if your federated database is running on Windows. You will know if you encounter this issue if you get the SQL0322N error while attempting an operation on your federated database like telling Data Studio to “Discover and Create Nicknames”. In general when your iSeries DB2 database is not UNICODE, then I recommend creating the federated database with the code page of the operating system where you are creating the database. The Supported Code Page Knowledge center page, shows the code pages for each operating system for each territory. The example below is for creating a database on a Windows server in the US since we are suing the 1252 code set.
CREATE DATABASE ISERDBF
USING CODESET 1252 TERRITORY US
If you use a DB2/LUW database for your Optim Directory, then the code set of that dataset needs to be non-UNICODE if your federated database is non-UNICODE. If your federated database is UNICODE then your Optim Directory needs to be UNICODE. This is because the DB2 client can not talk to UNICODE and non-UNICODE databases at the same time. Taking some literary license, I recommend that “UNICODEness” of all of your databases need to be the same. So if your iSeries DB2 database is EBCDIC or some other non-UNICODE code set and your databases are installed on Windows, then I would recommend creating your federated database and Optim Directory database using the code set in the example above.
With federation, DB2 allows you to create objects called nicknames. A nickname is like a view on a table, except the nickname is really a view on a table outside of the database containing the nickname. So once you have created a database to host your nicknames, you have to configure it to talk to the external data sources. The first step in configuring your database to connect to the external database is to create an object called a wrapper. It tells DB2 how to translate things like column data types from the source to the federated database. You can read more about wrappers in the Federation Server links above. While you can create a wrapper using Data Studio, I prefer to just run the command to create it. Here is the command for any iSeries or zSeries DB2 database:
CREATE WRAPPER "DRDA"
OPTIONS (DB2_FENCED 'Y');
Once you have created the wrapper, you tell the federated database about the actual server to which you want to connect. The command used to create the server for my iSeries connection is below. You will need to create one server object for each physical iSeries Server. The user id and password needed to connect to DB2 on iSeries are IUSERID and IPASSWD respectively. These are the credentials defined on the iSeries system to allow access to the database.
CREATE SERVER "ISERDBX"
The next step is to map the user ids connecting to the federation server database to the user ids defined on the iSeries server. You do this with an object called a user mapping. This allows you to give different users who connect to the federated database different rights in the iSeries database. When someone connects to the federated database and queries a nickname, the iSeries user id defined for that id via the user mapping is used to connect to the iSeries database. In my case I was creating a demonstration and was not sure which users would be connecting to the federation server, so I just mapped all of the users in my Windows server to my high level iSeries id. That would be a really bad idea in a production environment though. In this example, I mapped the DB2ADMIN user id that is used to administer the DB2 Federation Server database to the IUSERID that has rights in the iSeries database. In this example, when DB2ADMIN selects from a nickname in the federated database, Federation Server actually connects to the iSeries database using the IUSERID user. So whichever rights IUSERID has in iSeries DB2ADMIN has as well.
CREATE USER MAPPING FOR DB2ADMIN
In your DB2/LUW federated database ISERDBF, it is a good idea to create the same schema names as the tables you want to access in the iSeries database. It can save a lot of confusion when your SCHEMA.NICKNAME in ISERDBF looks identical to the matching SCHEMA.TABLE_NAME in the iSeries Database. By creating your schema’s upfront it is easier to have IBM Data Studio or other tool create the appropriate nicknames in the schemas you want. You create schemas in DB2/LUW using the CREATE SCHEMA command.
As I noted earlier, nicknames are the objects in the federated database that Optim and other applications can query to get access to the data in the source tables in an iSeries database or other database. Data Studio and other DB2 GUI tools allow you to create nicknames individually or to just point at a group of tables and have them generated for you. In Data Studio you do this in the Administration Explorer in the Database Administration perspective. In Figure 1 the federated database pointing to the iSeries database is called ISERIES. If I was being consistent with the previous examples, I would be connecting to ISERDBF instead. Just expand that database, expand Federated Database Objects, right click Nicknames and choose Discover and Create Nicknames. Then follow the prompts to pick the DB2/LUW database schema into which to place the nicknames and then the iSeries tables on which you wish to create nicknames. For more information about connecting to databases and using perspectives in Data Studio please see my Data Studio Update Part 2 article. As of the writing of this article, I could not get Data Studio to actually generate the nicknames for me, so I used an old copy of the Control Center to do it.
Figure 1. Generate Several Nicknames at Once
Unfortunately, Optim can’t see through the federated database to get the foreign keys in the iSeries database. However, there is a relatively painless way for Optim to get these relationships. You can just reverse engineer the foreign keys from iSeries, change the syntax slightly and create NOT ENFORCED foreign keys on the nicknames in the federated database. Optim will view these just like foreign keys on a table! I used IBM Data Studio to reverse engineer the foreign keys in my iSeries database. To do that I got into the Data perspective, connected to my iSeries database in the Data Source Explorer view and selected “Generate DDL…” This walked me through a number of panels. In the one that had the list of all of the objects in the database, I unchecked all but “Foreign Key Constraints”. This process has to do a ton of work, so be patient. It took over a half an hour to generate my foreign keys and there are not that many objects in my database. Please note that iSeries data sources do not show up in the Administration Explorer view. For more information about connecting to databases and using perspectives in Data Studio please see my Data Studio Update Part 2 article. Figure 2 shows how I initiated the reverse engineering process on an iSeries database. Here is an example of one of the foreign key DDL scripts I created with this process and executed through a connection to my DB2/LUW federated database.
ALTER NICKNAME "IDEMOPRD"."OPTIM_CUSTOMERS"
ADD CONSTRAINT "RSC" FOREIGN KEY
NOT ENFORCED ;
Figure 2. Reverse Engineer iSeries Foreign Keys
Once you have completed these steps you are ready to create your Optim alias for the iSeries database by pointing it to the federated database, ISERDBF. While these steps will create a functioning federated connection it is not optimized for performance. There are a number of things you can do to get the best performance for the Optim queries processed by the federated database. You can read more about what you can do to optimize performance in the Administration Guide for Federated Systems. A few of the important ones include
When you create the nickname, the DB2 federated database gathers statistics from the source database about the base tables such as row counts and indexes available. However, if the source statistics were out of date on the source database or the amount and nature of data changes over time, you will want to gather the statistics on some periodic basis. Read more about Global catalog statistics in the Information Center.
You can actually create indexes on nicknames in the federated database. This is useful if an index does not exist in the source database that would improve performance of Optim queries such as Start Table Criteria. This is covered in the Administration Guide and is useful when you are not allowed to create indexes in the source.
There are a number of settings in Federation Server that can improve performance including a number of optional parameters on the server and wrapper objects described above. Read more about the things you can do in the Administration Guide.
Allan Martin, a good friend of mine and an excellent Optim consultant, recently told me about a situation where his customer was having performance issues on an Optim extract from an iSeries DB2 database and determined that the problem was that the indexes on DB2/i were not being used. To fix this issue they did two things:
If you need to get data into Optim from iSeries or zSeries source that is not in a table or view there may be something that you can do. I have not tried this myself, but I’m pretty sure it would work. Functions and stored procedures on those platforms allow you to access many types of data that are not in the tables. In federation server you can write a table oriented function that return data in row format. You could write one of those functions that calls the function or stored procedure in the source database. Since the federated database function is a table function you can build a view on that function. Optim can access views, so it can access the non table data from the source.
One interesting thing about many DB2 tables in iSeries is that they are built on files that are not fully described, meaning that most or all of the fields are just concatenated into one long column when you select them using SQL. A federated database provides a convenient way to allow Optim to see the fields as individual columns. In the federated database you can create a view on the nickname of the not fully described table that uses substring and other functions to break out data into columns. Optim can then use the view instead of the base nickname. For Optim Test Data Management, only individual columns need to be obfuscated need to be broken out. Of course you could build the view in the iSeries database too, but that would require you to change the production database. Here is an example of a view that could be built on a nickname where the iSeries table has two columns, KEY and RREC. Since the only sensitive data in the table is the telephone number that we wish to mask, we leave everything else in longer substrings.
CREATE VIEW myschema.view_described1
(key, leadingRec, teleno, endingRec)
Added 2 February 2, 2015
Please keep in mind when creating an insert request for Test Data Management or a restore request with Archiving that if the source table is journaled and the target table is not journaled then you will get an error like this, “SQL1822N Unexpected error code "-7008" received when trying to insert data into an iSeries database”. Further you may get other complaints about the target needing to be journaled. To avoid this error, you can make the journaling on the source and target the same or you can make some Federation Server Configuration changes as noted at this link: http://www-01.ibm.com/support/docview.wss?uid=swg21577791. Thank you Allan Martin for sharing this information.
Then next time you need to connect your Optim Server to an iSeries database or other data source that needs Federation Server, I hope that you find this article useful. Please add any thoughts that that you on this topic to the Facebook Page or the db2Dean and Friends Community and help the extended community.