Configuration of a Federated Database System

This section explains how to configure a federated server to access data stored in DB2 family databases.

Enabling Federated Database Functionality

To take advantage of federated database functionality:

On UNIX Systems

During the installation of DB2 UDB, you must select the Distributed Join for DB2 Data Sources option and optionally create an instance to use with this option. If you choose to create an instance, the FEDERATED parameter will be set to YES by default. If you choose to create an instance at a later date, you must manually set the FEDERATED parameter to YES for that instance.

This is necessary only if you are creating an instance using db2icrt. If you go back to using db2setup to either create or set up an existing instance, the FEDERATED parameter will be set to YES again.

On Windows Systems

Federated database functionality is enabled by default as part of the DB2 installation.

Adding a DB2 Data Source to a Federated System

This section explains how to add a DB2 Data Source to a federated system.

Step 1

The first step is to configure network communications. Configuring your federated server to access DB2 family data sources is similar to configuring a client to communicate with a DB2 server. Refer to the Configuration Client-to-Server Communication Using the Client Configuration Assistance section in the IBM DB2 Quick Beginnings for DB2 Client.

db2 "connect to HDB1 user TST1TXP using xxxxxxxx"

Database Connection Information
Database server        = DB2 OS/390 6.1.1
SQL authorization ID   = TST1TXP
Local database alias   = HDB1

cd ~dsnow/sqllib/bnd

db2 "bind @ddcsmvs.lst blocking all sqlerror continue messages ddcsmvs.msg grant public"

Step 2

Catalog an entry in the federated server's node directory that points to the location of the DB2 data source. The federated server determines the access method to use, based on the type of node being cataloged and the type of DB2 family database being accessed.

db2 "catalog tcpip node HDB1NODE remote 210.100.100.210 server 1300"
db2 "catalog database HDB1 as HDB1 at node HDB1NODE authentication dcs"
db2 "catalog dcs database HDB1 as HDB1"

Check catalog:
db2 list node directory

Node Directory
Number of entries in the directory = 1

Node 1 entry:
Node name                            = HDB1NODE
Comment                              =
Protocol                             = TCPIP
Hostname                             = 210.100.100.210
Service name                         = 1300

db2 list db directory
System Database Directory
Number of entries in the directory   = 1

Database 1 entry:
Database alias                       = HDB1
Database name                        = HDB1
Node name                            = HDB1NODE
Database release level               = 9.00
Comment                              =
Directory entry type                 = Remote
Authentication                       = DCS
Catalog node number                  = -1

db2 list dcs directory
Database Connection Services (DCS) Directory
Number of entries in the directory   = 1

DCS 1 entry:
Local database name                  = HDB1
Target database name                 = HDB1
Application requestor name           =
DCS parameters                       =
Comment                              =
DCS directory release level          = 0x0100

Step 3

Use the CREATE WRAPPER statement to define the wrapper module that will be used to access DB2 data sources. Wrappers are the mechanism that federated servers use to communicate with and retrieve data from data sources.

db2sampl

db2 "connect to SAMPLE user dsnow using yyyyyyyy"
Database Connection Information

Database server          = DB2/SUN 8.1.0
SQL authorization ID     = DSNOW
Local database alias     = SAMPLE

db2 "create wrapper drda"
DB20000I  The SQL command completed successfully.

Check wrapper:
db2 "select substr(wrapname,1,8) as wrapname, wraptype,
    substr(library,1,18) as library
  from syscat.wrappers"

WRAPNAME                 WRAPTYPE                LIBRARY
--------                 --------                -------
DRDA                     R                       libdrda.so

1 record(s) selected.

Step 4

Set the DB2_DJ_COMM environment variable to include the wrapper library that corresponds to the wrapper module that you created in the previous step (optional).

db2set  DB2_DJ_COMM='libdb2drda.a'

Step 5

Use the CREATE SERVER statement to define each DB2 server to which communications are configured.

db2 "create server HOST390
  type DB2/390
  version 7.1 wrapper drda
  authorization 'TST1TXP'
  password 'xxxxxxxx'
  options(node 'HDB1NODE', dbname 'HDB1')"

Check Federated Server:
db2 "select substr(servername,1,8) as servername, substr(setting,1,18) as setting,
    substr(option,1,18) as option from syscat.serveroptions"

SERVERNAME              SETTING                  OPTION
----------              ---------                ------
HOST390                 HDB1NODE                 NODE
HOST390                 HDB1                     DBNAME

2 record(s) selected.

Step 6

If a user ID or password at the federated server is different from a user ID or password at a DB2 family data source, use the CREATE USER MAPPING statement to map the local user ID to the user ID and password defined at the DB2 family data source.

User name:         DSNOW

  db2 "create user mapping for DSNOW server HOST390
    options(remote_authid 'TST1TXP', remote_password 'xxxxxxxx')"

User name:         TPHAN
  db2 "create user mapping for TPHAN server HOST390
    options(remote_authid 'TST1TXP', remote_password 'xxxxxxxx')"
  Check user mapping:
  db2 "select substr(authid,1,8) as authid,
    substr(servername,1,8) as servername,
    substr(option,1,18) as option,
    substr(setting,1,18) as setting
    from syscat.useroptions"

AUTHID             SERVERNAME         OPTION             SETTING
----------------   ----------------   ----------------   ----------------
DSNOW              HOST390            REMOTE_AUTHID      TST1TXP
DSNOW              HOST390            REMOTE_PASSWORD    n…Ã|'Q•_xÈAM~_ø
TPHAN              HOST390            REMOTE_AUTHID      TST1TXP
TPHAN              HOST390            REMOTE_PASSWORD    _¶{Z_»_¥_K_àß«>À

4 record(s) selected.

Step 7

Use the CREATE NICKNAME statement to assign a nickname to a view or table located at your DB2 family data source. You will use this nickname when you query the DB2 family data source.

[View full width]
db2 "drop nickname DNTNCK.ACCOUNT_XREF"

DB21034E  The command was processed as an SQL statement because it was not a valid Command
 Line Processor command. During SQL processing it returned:
SQL0204N  "DNTNCK.ACCOUNT_XREF" is an undefined name. SQLSTATE=42704

db2 "create nickname DNTNCK.ACCOUNT_XREF for
  HOST390.DB2TTEST.ACCOUNT_XREF"
DB20000I  The SQL command completed successfully.

Check nicknames:
db2 "select substr(tabname,1,18) as tabname,
    substr(tabschema,1,18) as tabschema,
    substr(tbspace,1,18) as tbspace, type
    from syscat.tables where type='N'"

TABNAME             TABSCHEMA           TBSPACE             TYPE
-----------------   -----------------   -----------------   ----
ACCOUNT_XREF        DNTNCK              -                   N

1 record(s) selected.

db2 "select substr(d.tabschema,1,8) as schema_name,
    substr(d.tabname,1,30) as table_name,
    substr(a.setting,1,8)||'.'||substr(b.setting,1,8)||'.'||
    substr(c.setting,1,30) as three_parts_nickname
  from syscat.taboptions a, syscat.taboptions b,
    syscat.taboptions c, syscat.tables d
  where d.type='N' and d.tabname=a.tabname and
    d.tabname=b.tabname and d.tabname=c.tabname and
    a.option='SERVER' and b.option='REMOTE_SCHEMA' and
    c.option='REMOTE_TABLE'
  union select substr(d.tabschema,1,8) as schema_name,
    substr(d.tabname,1,30) as table_name,
    substr(a.setting,1,8)||'.'||substr(b.setting,1,8)||'.'||
    substr(c.setting,1,30) as three_parts_nickname
  from syscat.taboptions a, syscat.taboptions b,
    syscat.taboptions c, syscat.tables d
  where d.type='N' and d.tabname=a.tabname and
    d.tabname=c.tabname and
    d.tabname=b.tabname and a.option='SERVER' and
    b.option='REMOTE_SCHEMA' and
    c.option='REMOTE_TABLE'
  order by 1"

SCHEMA_NAME         TABLE_NAME          THREE_PARTS_NICKNAME
-----------         -----------         --------------------
DNTNCK              ACCOUNT_XREF        HOST390 .DB2TTEST .ACCOUNT_XREF

1 record(s) selected.

Step 8

Repeat the previous step for all database objects for which you want to create nicknames.

Step 9

Use the CREATE ALIAS statement to define an alias for a nickname (optional).

db2 "create alias dnt.account_xref for DNTNCK.ACCOUNT_XREF"

db2 "select substr(tabname,1,18) as tabname,
  substr(tabschema,1,18) as tabschema,
  type, substr(base_tabname,1,18) as basename,
  substr(base_tabschema,1,8) as baseschema
  from syscat.tables where type='A'"

TABNAME        TABSCHEMA      TYPE           BASENAME       BASESCHEMA
-------------  -------------  -------------  -------------  -------------
ACCOUNT_XREF   DNT            A              ACCOUNT_XREF   DNTNCK

1 record(s) selected.

Step 10

Grant permission:

db2 "grant all on DNTNCK.ACCOUNT_XREF to user DSNOW"
DB20000I  The SQL command completed successfully.

db2 "grant all on DNTNCK.ACCOUNT_XREF to user TPHAN"
DB20000I  The SQL command completed successfully.

Verifying Connections to a DB2 Data Source

This section explains how to verify that you have correctly configured your federated system to access a DB2 Data Source.

db2 "connect to SAMPLE user tphan using zzzzzzzz"
Database Connection Information

Database server                      = DB2/SUN 8.1.0
SQL authorization ID                 = TPHAN
Local database alias                 = SAMPLE

db2 "select * from dntnck.account_xref"

ACCT_NUM                             ACCT_OFFICE
------------------                   -----------
000000000000001001                   111
000000000000001011                   111
000000000000001101                   111
000000000000001111                   111
000000000000002001                   222
000000000000002011                   222
000000000000002101                   222
000000000000002111                   222

8 record(s) selected.

SQL Statements Summary

Table 7.5 presents descriptions for SQL statements.

Federated Database Considerations

The following section describes additional considerations in a federated database system. It also provides recommendations for improving federated database query performance.

Federated Database Object Naming Rules

• Nicknames, mappings, index specifications, servers, and wrapper names cannot exceed 128 bytes.

• Server and nickname options and option settings are limited to 255 bytes.

• Names for federated database objects can also include:

  • Valid accented letters

  • Multi-byte characters, except multi-byte spaces (for multi-byte environments)

Pushdown Analysis

Pushdown analysis tells the DB2 optimizer whether an operation can be performed at a remote data source. An operation can be a function, such as relational operator, system, or user functions, or an SQL operator (GROUP BY, ORDER BY, and so on).

Functions that cannot be pushed down can significantly impact query performance. Consider the effect of forcing a selective predicate to be evaluated locally instead of at the data source. This approach could require DB2 to retrieve the entire table from the remote data source, then filter it locally against the predicate. If your network is constrained and the table is large, query performance could suffer.

Connection to the Data Source

CONNECTSTRING specifies additional connection-relevant information for certain types of data sources. CONNECTSTRING server options are:

• IFILE? full path to the interfaces or sql.ini file that contains the connection details used by Sybase Open Client to access the Sybase server. This is particularly useful if you don't want to copy the interfaces or sql.ini file into the DB2 instance owner's \SQLLIB subdirectory but would rather use the interfaces or sql.ini file within the Sybase Open Client directory. For instance, on Windows systems, the sql.ini file is usually in the C:\Sybase\ini subdirectory.

• TIMEOUT? specifies the time, in seconds, that Federated Server should wait for results from Sybase server. If there are no problems on Sybase server or the user is willing to wait indefinitely for Sybase to provide results to SQL that is sent to it from the federated server, this CONNECTSTRING parameter does not need to be specified. But if there are problems, Sybase Open Client will wait indefinitely, and so will Federated Server. If you were in a DB2 CLP window, you need to issue Ctrl+C to stop the wait. Any other application would just hang.

• LOGIN_TIMEOUT? specifies the time, in seconds, that Federated Server should wait for Sybase server to respond to connections. If the Sybase server is running and there are no problems connecting to it, this parameter does not need to be specified. Otherwise, Sybase Open Client and DB2 Federated Server will wait indefinitely.

• PACKET_SIZE? size of data packets between Federated Server and Sybase. Large results sets from Sybase will perform better with a larger PACKET_SIZE.

Here is an example of specifying CONNECTSTRING on the initial create server statement to Sybase:

CREATE SERVER SYBS1000 TYPE SYBASE
  VERSION 11.9 WRAPPER CTLIB
  OPTIONS (NODE 'sybn1000', DBNAME 'testdb');

Here is an example of setting server option CONNECTSTRING with values for IFILE, LOGIN_TIMEOUT, and TIME_OUT:

ALTER SERVER SYBS1000
  OPTIONS (ADD CONNECTSTRING 'IFILE="c:\Sybase\ini\sql.ini",
    TIMEOUT=20, LOGIN_TIMEOUT=5');

The ability of a data source to process the SQL sent to it by the federated server will have a major impact on the time it takes for Federated Server to provide results to the users of the federated database. The SQL statement that will be sent to a data source can be determined by using Federated Server's explain facilities (dynexpln). Input to the Federated Server explain facility is the SQL statement that the user or application will give to the federated database. The output of the explain facility will include the whole plan that Federated Server will use to execute the statement, including showing the SQL statement(s) to be sent to each of the data sources.

If the plan to execute the SQL involves the creation of temporary tables in the federated database, availability of buffers to hold the pages of the temporary table will improve performance by avoiding the disk I/O involved if the pages of the temporary table have to be written out to disk. Also, if the plan involves a sort to be done in the federated database, for instance, if there is an ORDER BY clause that is not pushed down to a data source, the availability of sort resources in the federated database will affect performance.

For a sample, view the following SQL statement:

select count(*) from DNTNCK.ACCOUNT_XREF
    WHERE ACCT_NUM>'000000000000001001';

• Does the data source have adequate CPU capacity to process the statement?

• Is this index accessible?

• Does the data source support Plan_Hints?

• Is there enough sort space if sort is needed?

• How large is the temporary space?

Additional DB2 Federated Server options that could make the federated server send more efficient SQL to the data source, particularly for a specific data sources, are contained in the Plan_Hints server option. It is supported by only certain data sources and, even there, they need to be enabled. So the default setting for server option Plan_Hints is N. But if the data source does support Plan_Hints and the support is enabled, Federated Server can send the data source plan hints if the server option Plan_Hints is specified with a setting of Y.

ALTER SERVER ORASERV OPTIONS (ADD PLAN_HINTS 'Y');

Temporary Tables

Though the temporary table will be created in the federated database's table space for temporary tables, there will no disk I/O involved if all the pages of this temporary table will fit into available page buffers for this table space.

If the default temporary table space TEMPSPACE was created when the federated database was created, the pages of the table space will be kept in memory in the IBMDEFAULTBP buffer pool, and the database configuration parameter BUFFPAGE of the federated database determines the maximum number of pages that can be kept in memory.

Sort Allocation

If the execution plan indicates that a temporary table is created and used, and that ORDER BY has not been pushed down to a data source, it very likely that the ORDER BY will be executed in the federated database on the temporary table. This is particularly likely to happen if the federated database and the data source have different collating sequence, as indicated by the server option COLLATING_SEQUENCE whose default setting, if not explicitly specified, is N.

If a sort will be done in the federated database, the database configuration parameter SORTHEAP for the federated database and the Database Manager Configuration parameter SHEAPTHRES for the DB2 instance containing the federated database could have an impact.

Database configuration parameter SORTHEAP indicates the maximum memory allocated for a sort. The parameter is specified in number of pages. If a sort cannot fit into this allocation, it overflows into temporary table space tables. As discussion earlier, if the temporary table can't fit into the available memory buffers for the table space, there will be disk I/O. It is better, if possible, if the sort can be done using the memory pages allocated for sorts, so a larger value for SORTHEAP can improve performance.

Database Manager Configuration parameter SHEAPTHRES indicates the maximum memory allocated for sorts by users of all databases within the DB2 instance containing the federated database. Logically, it should be a multiple of the largest SORTHEAP value in the database configurations of the databases within the DB2 instance.

For example, we set the SORTHEAP for the federated database to 50,000 pages. So SHEAPTHRES in the Database Manager Configuration for the instance containing this federated database should be a multiple of 50,000.

Network Considerations

The network speed and number of hops between the federated server and a data source can have a big impact on how long it takes to give the federated user the result from an SQL statement when it:

• Returns a large result set from the data source

• Processes a large number of SQL statements at the data source

It is generally recommended that:

• The federated server and data source be collocated on the same system if possible

• As minimal a number of network hops as possible are used between the federated server and the data source

The DB2 Federated Server's Database Manager Configuration parameter RQRIOBLK can have an impact on performance if large blocks of data are transferred between the data source and the federated server. RQRIOBLK determines the maximum size of blocks of records that can be transferred at once between a DB2 server and its network clients; it also determines the size of the blocks that the federated server can receive from data sources. The default value is 32,767 bytes, which is already quite large. But a value as high as 65,535 bytes can be specified.

So if RQRIOBLK = 32,767 (the default value) and the result from the data source is 10,000 records with row length of 100, then ((10,000 records * 100 bytes) / 32,767 bytes per block) = 31 is the number of blocks needed to send the result from the data source to the federated server.

If the update of the Database Manager Configuration RQRIOBLK = 65,535, the result of 10,000 100-byte records would require ((10,000 records * 100 bytes) / 65,535 bytes per block) = 16 as the number of blocks needed to send the result from the data source to the federated server.

To find out the current value of RQRIOBLK, look at the Database Manager Configuration of the DB2 instance that has the federated database:

GET DATABASE MANAGER CONFIGURATION | grep ?i rqrioblk

To set RQRIOBLK to the maximum value:

UPDATE DBM CFG USING RQRIOBLK 65535

The parameter RQRIOBLK size might improve performance of queries with large results from data sources, but it might not resolve the real issue with network performance if the data rate of the network between the federated server and the data source is slow. If there are lots of routers to be traversed between the data source and the federated server, performance on selects from nicknames will be slow.