Patterns: Information Aggregation and Data Integration with DB2 Information Integrator

200 Patterns: Information Aggregation and Data Integration with DB2 Information Integrator

Additional server options for ODBC are listed in Table B-3.

Table B-3 ODBC additional server options

Create the ODBC user mappings

As mentioned earlier, user mapping is used by the federated database server

whenever it connects to the ODBC data source server on behalf of the calling

federated database user. An association must be created for each user that

would be using the federated system.

In our case, we define a single user mapping for our NALUR1 user since that is

the only user ID used in our DFS portal.

Select and expand REWARDS, right-click the User Mappings folder, and click

on Create, similar to what was done for CHKSVG from Figure B-6 on page 175.

This action displays the screen shown in Figure B-36 on page 201.

Attention: Not all the ODBC server options are available through the DB2

Control Center; they must be set through the command line.

Server options Description

DB2_GROUP_BY GROUP BY is supported.

DB2_ORDER_BY ORDER BY is supported.

DB2_BASIC_PRED It allows ‘=’, ‘<‘, ‘>’ predicates.

DB2_COLFUNC It allows column functions.

DB2_SELECT_DISTINCT SELECT DISTINCT is supported.

Important: These server options’ settings override the default settings for

attributes within the DB2 Information Integrator ODBC wrapper. The wrapper

needs to work with

any ODBC data sources (even those with little SQL

functionality) to avoid the occurrences of errors that arise from pushing down

SQL operations and functions not supported by an ODBC data source.

Multiple ODBC server definitions can be defined for a single wrapper with

each server definition having different options.

The settings for these attributes in the ODBC wrapper are conservative.

Appendix B. Configuring data sources in DB2 Information Integrator 201

Figure B-36 ODBC data source - Create User Mapping dialog

Figure B-36 lists all the user IDs available on the federated system. Select the

user that sends the federated requests to the ODBC data source. We selected

the NALUR1 user, and switched to the Settings menu, as shown in Figure B-37

on page 202, to complete the user mapping.