Step 2. Federate the data
In the real world it is very common for an enterprise to have data residing in heterogeneous data sources. The IBM InfoSphere Federation Server enables enterprises to access and integrate diverse data and content as if they were a single resource.
To simulate the real world scenario, the InfoSphere Federation Server is used to access data from the LAWENF and ARREST databases. Table 2 summarizes the steps for creating a federated object for an IBM DB2 for Linux ®, UNIX® and Windows ® database table:
Table 2. Steps to create a federated object for a DB2 database table.
|1||Catalog the remote database. Skip this step if the database is local to the Federation Server.|
|2||Create DB2 wrapper.|
|3||Create server definition.|
|4||Create user mapping.|
To access the remote LAWENFC, BOOKINGS and CHARGES tables via the Federation Server, you must create a nickname object for each of the remote database tables. Below are the steps for creating the BOOKINGS and CHARGES nickname objects. These steps assume that a Federation Database has already been created. In this tutorial, the Federation Database is called FEDSRVER.
- Catalog the ARREST database:
- From the Start menu, select Programs -> IBM DB2 -> DB2COPY1 (Default) -> Set-up Tools -> Configuration Assistant.
- Select Selected -> Add Database Using Wizard.
- Follow the prompts from the Add Database Wizard to catalog the ARREST database.
- Create the DB2 wrapper:
- From the Start menu, select Programs IBM DB2 -> DB2COPY1 (Default) -> General Administration Tools -> Control Center.
- Select FEDSRVER -> Federation Database Object.
- Right-click on Federation Database Object and select Create Wrapper.
- In the Create Wrapper window, enter the following parameters:
- Data source: DB2
- Wrapper name: drda2
- Click OK.
Figure 3 shows the steps to create a DB2 wrapper:
Figure 3. Creating the DB2 wrapper
- Create a server definition for the ARREST database.
- Expand the DRDA wrapper object.
- Right-click on Server Definitions and select Create.
- On the Create Server Definitions window, click on Discover. The system returns a list of databases in the Server Definitions list box.
- From the Server Definitions list box, select the Uncheck All button. Check the box next to the ARREST database to select only the ARREST database.
- Click on Properties. Enter the following parameters on the Server Definition Properties:
- Name: ARREST
- Type: DB2
- Version: (select the appropriate version)
- Enter the appropriate User ID and Password.
- Click OK.
Figure 4 shows the steps to create a server definition for the ARREST database:
Figure 4. Creating a server definition for the ARREST database
- Next, you should create user mapping. This step requires a local
database userid be mapped to the remote database userid. The
remote database userid is the userid that has access rights to the
- Expand on the ARREST server definition.
- Right-click on User Mappings and select Create.
- Select the local user ID(s) to be mapped, as Figure 5 shows:
Figure 5. Create user mapping for local user ID(s)
- Select the Settings tab.
- Next to the REMOTE_AUTHID, enter the remote userid that has access right to the remote database, as in Figure 6.
- Next to the REMOTE_PASSWORD, enter the password for the remote userid.
Figure 6. Enter remote database user ID and password
- Create a nickname for the BOOKINGS and CHARGES tables.
- Expand on the ARREST server definition.
- Right-click on Nicknames and select Create.
- From the Create Nicknames window, click on Discover.
- From the Discover window, enter a value for the Remote schema to narrow down the search criteria. If no filter is provided, the system returns all the tables it discovers for the ARREST database. Figure 7 shows this step:
Figure 7. Enter parameters to discover remote database tables
- From the Nicknames window, click on the check boxes next to the BOOKINGS and CHARGES nicknames. If you are satisfied with the value of the nicknames, click OK to finish creating the nicknames. Otherwise, change the nickname value by double-clicking the nickname value. That puts you into edit mode. Change the value. When done, click OK to finish creating the nickname objects for the BOOKINGS and CHARGES tables. Figure 8 illustrates this step:
Figure 8. Select the remote database tables to create the nickname objects
Follow the same steps listed above to create a nickname for the LAWENFC table in the LAWENF database.
Once a nickname is defined for the remote database table, you can reference the remote table by its nickname as if the table is defined in your local database. To facilitate the DataStage job in accessing the data in the LAWENFC, BOOKINGS and CHARGES tables, a federated view, IncReport, is created. The IncReport view combines selected columns from these three tables into one single view. Listing 4 shows the DDL statement that creates the IncReport federated view:
Listing 4. DDL for creating the IncReport federated view.
-- -- DDL Statements for creating a federated IncReport view -- create view IncReport as SELECT L.INCIDENTNUMBER, L.INCIDENTLOCATION, L.INCIDENTDESCRIPTION, L.PERSONNAME, L.RESIDENCE1, L.PHONENUMBER1, L.DOB, L.INCIDENTTYPE, L.DRIVERSLICENSE, L.XSSN, L.HAIRCOLOR, L.EYECOLOR, L.HEIGHT, L.WEIGHT, L.RACE, L.SEX, B.BOOKINGID, B.FACILITYNAME, C.CHARGEID, C.SUBJECTNAME, C.DESCRIPTION, C.APPLICABILITY, C.CATAGORYCODE, C.STATUTECODE, C.STATUTELEVEL FROM LAWENFC AS L, BOOKINGS AS B, CHARGES AS C WHERE (L.INCIDENTNUMBER = B.INCIDENTNUMBER) AND (L.INCIDENTNUMBER = C.INCIDENTNUMBER);