Support for the "three-part name" in IBM Inforsphere Federation v10.1

Introduction to the three-part name of the federated database

The federated database uses nicknames to access and manage remote data sources, each of which corresponds to an object of a remote data source. However, in a large federated database environment, a large number of database objects (such as tables, attempts, etc.) are included on each remote data source server, and users need to create a nickname for each remote object. In such an environment, creating nicknames is a very tedious thing to do. In addition, the management of these nicknames also aggravates the user's management work.

To solve this problem, a new solution-"three-part name"-is introduced in the latest version of the federated database V10.1. To put it simply, you allow users to use the three-part name "Server.schema.table_name" or "Schem.table_name@server", based on the creation of wrappers, servers, and user mappings. Direct access to and management of data from remote data sources. This saves the user the tedious work of creating and managing nicknames.

In contrast to nicknames, when using three-part names, when the federated database first references a remote database object through SQL, the federated database obtains the metadata and state information (for example, statistics) of the remote database object, and saves the information in the cache. Users will no longer need to manage and maintain catalog tables in the federated database.

In addition, the use of "Schema.table_name@server" to refer to remote objects is actually compatible with Oracle's Query Gateway. With this three-part name, users can migrate applications on the Oracle Query Gateway directly to the federated database.

Use of the three-part name

In this section we will detail how to query and update using three-part names.

As with nicknames, you still need to create wrappers, servers, and user mappings before using the three-part name, as described in the following steps.

1 Create the wrapper (the statements in the example are for Linux and Solaris):

Listing 1. To create a wrapper command

Create wrapper ctlib
create wrapper drda;
Create wrapper ODBC OPTIONS (MODULE '/opt/lib/odbc.so ');

2) Create the server:

Listing 2. command to create a server

CREATE SERVER sybase_server1 TYPE Sybase VERSION 15.5 wrapper ctlib OPTIONS ( 
NODE ' Sybase_node ', dbname ' sybase_db '); C5/>create server Drda_server1 TYPE db2/cs VERSION wrapper drda OPTIONS ( 
node ' Udb_node ');
    
CREATE SERVER odbc_ Server1 TYPE ODBC VERSION 4.0 wrapper ODBC OPTIONS ( 
NODE ' Odbc_node ', dbname ' Venice ');

3 Create a mapping between DB2 user and data source users for the server:

Listing 3. Create a command for user mappings

CREATE user MAPPING for user SERVER sybase_server1 OPTIONS ( 
remote_authid ' sybase_usr ', enable_kerberos_connection ' Y ');
    
CREATE user MAPPING for user SERVER drda_server1 OPTIONS ( 
remote_authid ' drda_usr ', Remote_password ' Drda_ PW ');
    
CREATE user MAPPING for user SERVER odbc_server1 OPTIONS ( 
remote_authid ' mssql_usr ', Remote_password ' mssq L_usr ');

4 Create a remote object (which needs to be executed directly at the remote data source):

Listing 4. To create a command for a remote object

--Create a remote SYBASE object
--table SYB_TB1 record the employee's basic information: ID, NAME, age 
CREATE TABLE syb_tb1 (col_id int, 
                   Col_nema char (), 
  col_age  int
                   );
    
--Table SYB_TB2 Records employee's work information: ID,DEP, Job 
CREATE table SYB_TB2 (col_id int, 
                   COL_DEP Char ( 
                   col_job Char) 
                   ;-
                       
-Create a remote UDB object
--table DRDA_TB1 record employee Address information: id,address Create 
table drda_t B1 (col_id int, 
                   col_address  char 
                   );
    
--Table DRDA_TB2 record employee phone information: id,phone 
CREATE Table Drda_ TB2 (col_id int, 
                   col_phone int);-
    
-Create remote MSSQL object
--table MSQ_TB1 record employee's CV: id,res 
CREATE table MSQ_TB1 (col_id int, 
                   col_res CHAR) 
                   ;

Note that if the user needs to use the three-part name of the Schema.table_name@server form, the 18th bit of the Db2set registration variable db2_compatibility_vector should be set to 1:

Listing 5. Set Db2set registration variable

Db2set db2_compatibility_vector=10000;
db2stop
Db2start;
    
--or set it to ORA:
db2set db2_compatibility_ Vector=ora;
db2stop;
Db2start;

After completing the above steps, we can access the remote data using the three-part name! The following is an instance of using three-part names.

Update remote data instances with a three-part name query

The following is a list of examples of three-part names from which you can see the general usage of three-part names.