Configuring Oracle Gateway DRDA v11.2.0.3 (for IBM DB2 v10.1) :

Change directory to your $ORACLE_HOME/dg4db2/admin directory

For the example I published here, I’ve used the Linux (LUW) option:

From the installation done here, the below information is already captured within the initdg4db2.ora file:

[oracle@oel6u4-x64-11g admin]$ more initdg4db2.ora
# This is a customized agent init file that contains the HS parameters
# that are needed for the Database Gateway for DB2
#
# HS init parameters
#
HS_FDS_CONNECT_INFO=[10.0.2.10]:60123/DB2TEST,LUW
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER
HS_TRANSACTION_MODEL=COMMIT_CONFIRM
HS_FDS_TRANSACTION_ISOLATION=READ_COMMITTED
HS_FDS_SUPPORT_STATISTICS=TRUE
HS_FDS_RESULTSET_SUPPORT=FALSE
HS_FDS_PACKAGE_COLLID=ORACLEGTW
HS_IDLE_TIMEOUT=0
[oracle@oel6u4-x64-11g admin]$ The next step is to update the current listener, to include the DRDA config for connection to the DB2 database:
Add the following in red to the existing listener, and restart the listener:
Example:
(SID_DESC=
(SID_NAME=gateway_sid)
(ORACLE_HOME=oracle_home_directory)
(PROGRAM=g4drsrv)
)
Listener.ora:
[oracle@oel6u4-x64-11g admin]$ more $ORACLE_HOME/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = PROD11G)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = PROD11G)
)
(SID_DESC =
(SID_NAME = dg4db2)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(PROGRAM = dg4db2)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oel6u4-x64-11g.huis.co.za)(PORT = 1521))
)
ADR_BASE_LISTENER = /u01/app/oracle
[oracle@oel6u4-x64-11g admin]$ Restart the listener after this step
Configuring Two-Phase Commit:
Support for Two-Phase Commit requires running the ORACLE_HOME\dg4db2\admin\dg4db2_tx.sql script on the DB2 server. This script will create objects used by the gateway for Two-Phase Commit. Edit the script and replace the default recover account schema ("RECOVER") with the account name specified for the HS_FDS_RECOVERY_ACCOUNT initialization parameter.

Scp the file from the ORACLE_HOME to the server on which the db2 installation is running:

Connect to the db2 database, and run the sql script as shown below – important to note the following:
--    NOTES
--     For z/OS run via SPUFI
--     For AS/400 run via STRSQL
--     For LUW run via db2 -tvf

[db2inst@oel6u4-x64-db2 ~]$ db2 connect to db2test user wicus using mypasswd
Database Connection Information
Database server        = DB2/LINUXX8664 10.1.0
SQL authorization ID   = WICUS
Local database alias   = DB2TEST
[db2inst@oel6u4-x64-db2 ~]$ db2 -tvf /tmp/dg4db2_tx.sql
CREATE TABLE    RECOVER.HS_TRANSACTION_LOG( GLOBAL_TRAN_ID CHAR(64) NOT NULL, TRAN_COMMENT   CHAR(254) )
DB20000I  The SQL command completed successfully.
COMMIT
DB20000I  The SQL command completed successfully.
GRANT INSERT, UPDATE, DELETE, SELECT ON TABLE    RECOVER.HS_TRANSACTION_LOG TO PUBLIC
DB20000I  The SQL command completed successfully.
COMMIT
DB20000I  The SQL command completed successfully. [db2inst@oel6u4-x64-db2 ~]$


The next step is to copy all the files – luw/zos/as400 – to the db2 server for the specific installation you did (in my scenario, LUW)
[oracle@oel6u4-x64-11g admin]$ ls -l *luw*
-rw-r--r--. 1 oracle dba  2411 Mar  3  2009 dg4db2_luw_drop.sql
-rw-r--r--. 1 oracle dba   251 Mar  3  2009 dg4db2_luw_pkglist.lst
-rw-r--r--. 1 oracle dba 13009 Mar  3  2009 dg4db2_luw_tab.sql
-rw-r--r--. 1 oracle dba 31355 Mar  3  2009 dg4db2_luw_vw7.sql
-rw-r--r--. 1 oracle dba 44674 Mar  3  2009 dg4db2_luw_vw8.sql
[oracle@oel6u4-x64-11g admin]$
[oracle@oel6u4-x64-11g admin]$ scp *luw*.sql wicus@10.0.2.10:/tmp
wicus@10.0.2.10's password:
dg4db2_luw_drop.sql                                 100% 2411     2.4KB/s   00:00   
dg4db2_luw_tab.sql                                  100%   13KB  12.7KB/s   00:00   
dg4db2_luw_vw7.sql                                  100%   31KB  30.6KB/s   00:00   
dg4db2_luw_vw8.sql                                  100%   44KB  43.6KB/s   00:00   
[oracle@oel6u4-x64-11g admin]$ Make sure you run the correct scripts as mentioned in this document – upgrade/new install etc etc
http://docs.oracle.com/cd/E11882_01/gateways.112/e12061/cfg_gtwy.htm#OTGIW1065
ORACLE_HOME\dg4db2\admin\dg4db2_luw_tab.sql
ORACLE_HOME\dg4db2\admin\dg4db2_luw_vw7.sql
ORACLE_HOME\dg4db2\admin\dg4db2_luw_vw8.sql
[db2inst@oel6u4-x64-db2 ~]$ db2 -tvf /tmp/dg4db2_luw_tab.sql
[db2inst@oel6u4-x64-db2 ~]$ db2 -tvf /tmp/dg4db2_luw_vw7.sql
[db2inst@oel6u4-x64-db2 ~]$ db2 -tvf /tmp/dg4db2_luw_vw8.sql All sql should complete with the following:   
COMMIT
DB20000I  The SQL command completed successfully.
Configuring the Oracle database for access to the DB2 database via DRDA:
Add an entry to your tnsnames.ora file in $ORACLE_HOME/network/admin/tnsnames.ora using the below structure:

connect_descriptor=
   (DESCRIPTION=
      (ADDRESS=
         (PROTOCOL=TCP)
         (HOST=host_name)
         (PORT=port_number)
      )
      (CONNECT_DATA=
         (SID=gateway_sid))
     (HS=OK))
)

My example looks like this (This is the hostname/port number of the ORACLE listener on the ORACLE Database):

DB2=
   (DESCRIPTION=
      (ADDRESS=
         (PROTOCOL=TCP)
         (HOST=10.0.1.10)
         (PORT=1521)
      )
      (CONNECT_DATA=
         (SID=dg4db2))
      (HS=OK))

For a test, create a database link from within oracle to select the DUAL view from the DB2 database:
(make sure the listener port in the initdg4db2.ora file is not block on the DB2 server)
First test a local connection to DB2 to verify you have the correct username/password to test the dblink from oracle with:

SQL> !more tnsnames.ora
DB2=
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=10.0.1.10)
(PORT=1521)
)
(CONNECT_DATA=
(SID=dg4db2))
(HS=OK))
SQL> create public database link TODBTWO
2  connect to wicus identified by "diFFiculT1"
3  using 'DB2';
Database link created.
SQL> select * from DUAL@TODBTWO;
D
-
X SQL>



Another TEST using insert/update/delete:


SQL> select count(*) from WICUS.TESTING@TODBTWO;
  COUNT(*)
----------
0
SQL> select * from WICUS.TESTING@TODBTWO;
no rows selected
SQL> desc WICUS.TESTING@TODBTWO;
Name                                          Null?      Type
-----------------------------------------     --------   ----------------------------
NAAM                                          NOT NULL CHAR(30)
NAAM_ID                                             CHAR(20)
SQL> insert into WICUS.TESTING@TODBTWO values ('Lodewicus','001');
1 row created.
SQL> insert into WICUS.TESTING@TODBTWO values ('Lodewicus','002');
1 row created.
SQL> commit;
Commit complete.
SQL> select count(*) from WICUS.TESTING@TODBTWO;
  COUNT(*)
----------
2
SQL> select * from WICUS.TESTING@TODBTWO;
NAAM                            NAAM_ID
------------------------------ --------------------
Lodewicus                       001
Lodewicus                       002
SQL>