IBM Support

How to use the federation capabilities of Db2 to query data in a remote Db2, Db2 iSeries (AS/400), or Db2 for z/OS data source?

Question & Answer


Question

How do you use the federation capabilities of Db2 for Linux, Unix, and Windows (LUW) to query data in a remote Db2 LUW, Db2 iSeries (AS/400),  or Db2 for z/OS data source？

Answer

There are three ways to access remote Db2 family data sources:
Method 1 - Creating a connection to the remote data source by using the HOST, PORT, and DBNAME parameters.
Method 2 - Cataloging the remote data source.
Method 3 - Configuring the IBM data server driver configuration file, db2dsdriver.cfg
NOTE: Method 3 is the only feasible way when configure workload balancing for Db2 for zOS data source.
For more information, see Configuring access to Db2 data sources.

Before You Begin

Ensure that your Db2 server is configured properly for federation:
1. To access a remote Db2 LUW data source, use Method 1 or Method 2:
Method 1 - Create a connection to the remote data source by using the HOST, PORT, and DBNAME parameters.
Refer to the following example:
update dbm cfg using federated YES;
db2stop force;
db2start;

connect to fed_db;

CREATE SERVER SERVER1 TYPE DB2/LUW VERSION '11.5' authorization "db2inst8" password "password1" OPTIONS (HOST 'test1.fyre.ibm.com', PORT '50008', DBNAME 'REMOTE_DB');

create user mapping for user server SERVER1 options (REMOTE_AUTHID 'db2inst8', REMOTE_PASSWORD 'password1');

set passthru SERVER1;

drop table alltype_test;
create table alltype_test(col1_int integer, col2_smallint smallint, col3_double double, col4_char char(30), col5_varchar varchar(30), col6_date date, col7_time time, col8_timestamp timestamp, col9_decimal numeric);

insert into alltype_test values(1, 2, 3.1, 'hello4', 'hello world5', '2020-09-16','10:44:27', '2020-09-17 10:44:28', 12.39);
insert into alltype_test values(21, 22, 23.1, 'hello24', 'hello world25', '2020-09-16','10:44:27', '2020-09-17 10:44:28', 12.39);

set passthru reset;

CREATE OR REPLACE NICKNAME nick_alltype_test FOR SERVER1.db2inst8.alltype_test;

select * from nick_alltype_test;
Method 2 - Catalog the remote data source.
Refer to the following example:
update dbm cfg using federated YES;

uncatalog node FEDNODE2;
uncatalog db DATASDB2;

catalog tcpip node FEDNODE2 remote test1.fyre.ibm.com server 50008;

catalog db remote_db as DATASDB2 at node FEDNODE2;

db2stop force;
db2start;

connect to local_db;

drop wrapper WRAPPER1;
drop server SERVER1;

CREATE wrapper WRAPPER1 LIBRARY 'libdb2drda.so' OPTIONS(DB2_FENCED 'Y');

CREATE server SERVER1 TYPE DB2/UDB VERSION '11.5' wrapper WRAPPER1 authorization "db2inst8" password "password1" OPTIONS (NODE 'FEDNODE2', DBNAME  'DATASDB2');

create user mapping for user server SERVER1 options (REMOTE_AUTHID 'db2inst8', REMOTE_PASSWORD 'password1');

set passthru SERVER1;

drop table alltype_test;

create table alltype_test (COL_BINARY binary(10), DBCLB xml, COL_VARBINARY varbinary(10),DCF decfloat, BGI bigint, COL_FLOAT float, DAT date,TMP timestamp,CLB clob,BLB clob,CHA char(5),VCH varchar(20),GPH nchar(8),VGP nvarchar(10),DCM decimal,ITG int,SMI smallint,DOB float(2),REL float,CHA_NUMBER char(10),VCH_NUMBER varchar(10),SMI_SMALL smallint,ITG_SMALL int,DCM_SMALL decimal(5, 2),REL_SMALL float,ITG_DATE int,DOB_DATE float,CHA_ONECHAR char(1),VCH_ONECHAR varchar(25),DCM_DATETIME decimal(5, 2),CHA_DATETIME char(25),VCH_DATETIME varchar(25),TMPDAY date,TME_DATA_CHA char(25),CLB_NUMBER varchar(10),VCH_TRUNC varchar(25),CHA_TRUNC char(25),BGI_SMALL bigint, GPH_NUMBER varchar(5),BOOLN boolean,GPH_DATETIME graphic(25), TME time, VGP_DATETIME vargraphic(25), VGP_ONECHAR vargraphic(1), GPH_ONECHAR graphic(1));

insert into alltype_test values (bx'123abc','<catalog> <book>  <author> Gambardella Matthew</author>  <title>XML Developers Guide</title> <genre>Computer</genre> <price>44.95</price> <publish_date>2000-10-01</publish_date> <description>An in-depth look at creating application with XML</description></book></catalog>',bx'abcdef', 2.1, 1234, 1.1, '1970-11-23', '1970-11-23 12:12:12', 'ab', 'ab', 'abcd0', 'abcdefghijklmnopqrs0', 'abcdefg0', 'abcdefghi0', 0.99, 1, 1, 0.99, 0.99, '2', '4', 1, 1, 1.0, 1, 50, 50, 'r', 'u', 78.69, '1989-10-09 07:05:30', '1989-10-09 07:05:30', '1970-11-23', '1989-10-09 07:05:30', '6', 1, '1989-10-09 07:05:30', 123, '45', FALSE, '1970-11-23', '07:05:30', '1970-11-24', 'r', 'm');

set passthru reset;

CREATE NICKNAME nick_alltype_test FOR SERVER1.db2inst8.alltype_test;

select * from nick_alltype_test;
2. To access a remote Db2 iSeries data source, use Method 1 or Method 2:
Method 1 - Create a connection to the remote data source by using the HOST, PORT, and DBNAME parameters.
Refer to the following example:
update dbm cfg using federated YES;

db2stop force;
db2start;
connect to fed_db;

drop server SERVER1;

CREATE SERVER "SERVER1"  TYPE DB2/ISERIES  VERSION '7.3' authorization "J15USER1" password "J15USER1" OPTIONS (HOST  'FVTR73P2.RCH.STGLABS.IBM.COM',PORT '446',DBNAME  'FVTR73P2');

create user mapping for user server SERVER1 options (REMOTE_AUTHID 'J15USER1', REMOTE_PASSWORD 'J15USER1');

set passthru server1;
drop table alltype_test;

create table alltype_test(col1_int integer, col2_smallint smallint, col3_double double, col4_char char(30), col5_varchar varchar(30), col6_date date, col7_time time, col8_timestamp timestamp, col9_decimal numeric);

insert into alltype_test values(1, 2, 3.1, 'hello4', 'hello world5', '2020-09-16','10:44:27', '2020-09-17 10:44:28', 12.39);

set passthru reset;

CREATE OR REPLACE NICKNAME nick_alltype_test FOR SERVER1.J15USER1.alltype_test;

select * from nick_alltype_test;
Method 2 - Catalog the remote data source.
Refer to the following example:
update dbm cfg using federated YES;

uncatalog node FEDNODE2;
uncatalog db FVTR73P2;

catalog tcpip node FEDNODE2 remote TEST1.STGLABS.IBM.COM server 446;
catalog db FVTR73P2 as FVTR73P2 at node FEDNODE2;

db2stop force;
db2start;
connect to fed_db;

drop WRAPPER "DRDA";
drop server SERVER1;

CREATE WRAPPER "DRDA" LIBRARY 'libdb2drda.so' OPTIONS (DB2_FENCED  'Y');

CREATE SERVER "SERVER1"  TYPE DB2/ISERIES  VERSION '7.3'  WRAPPER "DRDA" authorization "J15USER1" password "J15USER1" OPTIONS (DBNAME  'FVTR73P2');

create user mapping for user server SERVER1 options (REMOTE_AUTHID 'J15USER1', REMOTE_PASSWORD 'J15USER1');

set passthru server1;
drop table alltype_test;

create table alltype_test(col1_int integer, col2_smallint smallint, col3_double double, col4_char char(30), col5_varchar varchar(30), col6_date date, col7_time time, col8_timestamp timestamp, col9_decimal numeric);

insert into alltype_test values(1, 2, 3.1, 'hello4', 'hello world5', '2020-09-16','10:44:27', '2020-09-17 10:44:28', 12.39);

set passthru reset;

CREATE OR REPLACE NICKNAME nick_alltype_test FOR SERVER1.J15USER1.alltype_test;

select * from nick_alltype_test;
3. To access a remote Db2 z/OS data source, use Methods 1, 2, or 3:
Method 1 -Create a connection to the remote data source by using the HOST, PORT, and DBNAME parameters.
Refer to the following example:
update dbm cfg using federated YES;

db2stop force;
db2start;

connect to fed_db;

CREATE WRAPPER WRAPPER1 LIBRARY 'libdb2drda.so' OPTIONS(DB2_FENCED 'Y');

drop server SERVER1;

create server SERVER1 type db2/mvs version 11 WRAPPER WRAPPER1 authorization "SYSADM" password "password1" options (HOST 'test1.ibm.com', PORT '446', DBNAME 'STLEC1');

create user mapping for user server SERVER1 options ( REMOTE_AUTHID 'SYSADM', REMOTE_PASSWORD 'password1' );

set passthru server1;

create table alltype_test(col1_int integer, col2_smallint smallint, col3_double double, col4_char char(30), col5_varchar varchar(30), col6_date date, col7_time time, col8_timestamp timestamp, col9_decimal numeric);

insert into alltype_test values(1, 2, 3.1, 'hello4', 'hello world5', '2020-09-16','10:44:27', '2020-09-17 10:44:28', 12.39);

set passthru reset;

CREATE NICKNAME nick_alltype_test FOR SERVER1."SYSADM".alltype_test;

select * from nick_alltype_test;
Method 2 - Catalog the remote data source.
Refer to the following example:
update dbm cfg using federated YES;

uncatalog node FEDNODE2;
uncatalog db STLEC1;
uncatalog dcs db STLEC1;

catalog tcpip node FEDNODE2 remote test1.ibm.com server 446;
catalog db STLEC1 as STLEC1 at node FEDNODE2 authentication dcs;
catalog dcs db STLEC1 as STLEC1;

db2stop force;
db2start;

connect to testdbu;

drop WRAPPER "WRAPPER1";

CREATE WRAPPER "WRAPPER1" LIBRARY 'libdb2drda.so' OPTIONS(DB2_FENCED 'Y');

drop server SERVER1;

create server SERVER1 type db2/mvs version 11 wrapper "WRAPPER1" authorization "SYSADM" password "password1" options (node 'FEDNODE2', dbname 'STLEC1');

create user mapping for user server SERVER1 options ( REMOTE_AUTHID 'SYSADM', REMOTE_PASSWORD 'password1' );

set passthru server1;

drop table alltype_test;

create table alltype_test(col1_int integer, col2_smallint smallint, col3_double double, col4_char char(30), col5_varchar varchar(30), col6_date date, col7_time time, col8_timestamp timestamp, col9_decimal numeric);

insert into alltype_test values(1, 2, 3.1, 'hello4', 'hello world5', '2020-09-16','10:44:27', '2020-09-17 10:44:28', 12.39);
insert into alltype_test values(21, 22, 23.1, 'hello24', 'hello world25', '2020-09-16','10:44:27', '2020-09-17 10:44:28', 12.39);

set passthru reset;

CREATE NICKNAME nick_alltype_test FOR SERVER1."SYSADM".alltype_test;

select * from nick_alltype_test;
Method 3 - Configuring the IBM data server driver configuration file, db2dsdriver.cfg.
Refer to the following example:
(db2inst1@snore1) /home/db2inst1/sqllib/cfg

$ cd /home/db2inst1/sqllib/cfg

$ cat db2dsdriver.cfg
<configuration>
  <dsncollection>
      <dsn alias="STLEC1" name="STLEC1" host="test1.ibm.com" port="446">
      <parameter name="Authentication" value="SERVER"/>
      <parameter name="commprotocol" value="TCPIP"/>
      </dsn>
  </dsncollection>
  <databases>
    <database name="STLEC1" host="test1.svl.ibm.com" port="446">
    <parameter name="CurrentSchema" value="SYSADM"/>
      <wlb>
        <parameter name="enableWLB" value="true"/>
      </wlb>
    </database>
  </databases>
</configuration>
When you finish editing the file, save it to the .../sqllib/cfg folder of your Db2 instance. Refer to the following example.
update dbm cfg using federated yes;

db2stop force;
db2start;

connect to testdbu;

drop WRAPPER "WRAPPER1";
drop server server1;

CREATE WRAPPER "WRAPPER1" LIBRARY 'libdb2drda.so' OPTIONS(DB2_FENCED 'Y');

create server SERVER1 type DB2/ZOS VERSION 12.1 WRAPPER "WRAPPER1" AUTHORIZATION "SYSADM" password "password1" options (DBNAME 'STLEC1');

create user mapping for user server SERVER1 options ( REMOTE_AUTHID 'SYSADM', REMOTE_PASSWORD 'password1' );

set passthru SERVER1;

drop table alltype_test;

CREATE TABLE alltype_test( BOL VARCHAR(10) , SMI SMALLINT, ITG INTEGER , BGI BIGINT , DCM DECIMAL(16, 10)   , LVC VARCHAR(128)   , VGP VARCHAR(30)   , REL REAL , DOB DOUBLE , CHA CHAR(63), VCH VARCHAR(128)   , CLO CLOB , BLO CLOB , DAT DATE , TME TIME , TMP TIMESTAMP   , DCM_DATETIME DECIMAL(26, 12) , CHA_DATETIME CHAR(63)  , VCH_DATETIME VARCHAR(128)  , CHA_NUMBER CHAR(63)  , VCH_NUMBER VARCHAR(128)  , SMI_SMALL SMALLINT  , ITG_SMALL INTEGER  , BGI_SMALL BIGINT   , DCM_SMALL DECIMAL(16, 10) , REL_SMALL REAL   , ITG_DATE  INTEGER  , BGI_DATE  BIGINT   , DOB_DATE  DOUBLE   , CHA_ONECHAR CHAR(1)  , VCH_ONECHAR VARCHAR(128) );

INSERT INTO alltype_test VALUES ( 'FALSE', 1, 6067, 298000, 3.1415, 'NVCAHR FOR TEST', 'ALPHANUM FOR TEST', 2.71828, 6.6725, 'CAHR FOR TEST', 'VARCHAR FOR TEST', 'CLOB FOR TEST', 'BLOB FOR TEST', '1989-10-09', '18:45:00', '1989-10-09 07:05:01' , 3.141, 'Monday', 'Monday', 'Monday', '1', 1, 1, 1, 724, 3.453, 72, 72, 3.1415, 'x', '7' );

set passthru reset;

CREATE NICKNAME nick_alltype_test FOR SERVER1."SYSADM".alltype_test;

select * from nick_alltype_test;

[{"Type":"MASTER","Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSFHEG","label":"DB2 Enterprise Server Edition"},"ARM Category":[{"code":"a8m500000008PkvAAE","label":"Federation"}],"ARM Case Number":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Versions"}]

Document Information

Modified date:
16 November 2023

UID

ibm16557382

Page Feedback