Solved

gateway connectivity from Oracle 10g to a different DB2 instance

Posted on 2011-02-18
4
1,246 Views
Last Modified: 2012-05-11
Hello,

I have a test database configured in a DB2 Instance called BETA. I also have a different test database in the DB2 instance called DB2. The DB2 instance is the default instance where our production database is also stored. Using the 11gR1 Gateway for DRDA, I can only connect to DB2 databases in the DB2 instance. When I try to connect to the TEST database in the BETA instance, I get the following error message:

DG4DRDA v11.1.0.5.0 grc=0, drc=-30020 (839C,0000), errp=GDJMRC

What Do I need in the initGATE81.ora to see the BETA instance? Running a TEST database in the production instance really slows down the system, because they are competing for the same resources.

Here is my current file:
############################################################################
# #
# SAMPLE init.ora file for DB2/UDB #
# #
############################################################################
# Trace Parameters
# Setting up the gateway in trace mode will have adverse effects on performance.
# TRACE_LEVEL: Default: 0
# 0 for no tracing, 255 for full tracing.
#TRACE_LEVEL=255

# LOG_DESTINATION: Default: $ORACLE_HOME/tg4drda/log/gatewaysid_pid.log
# $ORACLE_HOME/tg4drda/log. If this
# destination is correct, enter the trace file desired. If this destination is
# not correct, enter the entire path name and file name.
#LOG_DESTINATION=DB2UDB.log

# ORACLE_DRDA_TCTL: Default: none
# Default destination is $ORACLE_HOME/tg4drda/admin. If this
# destination is correct, enter the debug file name desired. If this
# destination is not correct, enter the entire path name and debug file name.
# Sample is located in $ORACLE_HOME/tg4drda/admin/debug.tctl
#ORACLE_DRDA_TCTL=$ORACLE_HOME/tg4drda/admin/debug.tctl

# ORACLE_DRDA_TRACE: Default: value specified by LOG_DESTINATION
# Parameter is used to specify a different log patch for DRDA internal tracing.
#ORACLE_DRDA_TRACE=/oracle/tg4drda/mylog/mydrda.log

# Hetergeneous Services Parameters
# Refer to Oracle9i Hetergeneous Connectivity Administrator's Guide for more
# detailed information

# HS_COMMIT_POINT_STRENGTH: Default: 0
# Parameter is important only if the non-Oracle system can participate in two-
# phase commit.
HS_COMMIT_POINT_STRENGTH=255

# HS_NLS_DATE_FORMAT=YYYY-MM-DD: Default: Determined by HS_LANGUAGE
HS_NLS_DATE_FORMAT=YYYY-MM-DD

# HS_LANGUAGE: Default: System Specific
HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1

# HS_NLS_NCHAR: Default: System Specific
#HS_NLS_NCHAR=WE8ISO8859P1

# HS_RPC_FETCH_REBLOCKING: Default: ON
# Controls whether HS attempts to optimize performance of data transfer.
HS_RPC_FETCH_REBLOCKING=off

# HS_RPC_FETCH_SIZE: Default: 4000
HS_RPC_FETCH_SIZE=32767

# HS_FDS_FETCH_ROWS: Default: 20
HS_FDS_FETCH_ROWS=20

# HS_DB_DOMAIN: Default: WORLD
# HS_DB_NAME: Default: HO
# HS_DB_DOMAIN is required if you use the Oracle Names Server. HS_DB_NAME and
# HS_DB_DOMAIN define the global name of the non-ORACLE system and combined
# they must form a unique address.
HS_DB_DOMAIN=mydomain.com
HS_DB_NAME=TEST

# The following parameters are specifically for the
# Transparent Gateway for IBM DRDA.
# DRDA gateway parameters to access DB2 (instance D51A)
# Note: Valid DB2/UDB userid/pw required in database link.

# FDS_CLASS : Default: TG4DRDA_DB2MVS
# This parameters specifies the agent capabilities of the remote DRDA server.
# The correct class must be specified to match the remote DRDA server.
# Valid Classes are:
# For DB2/OS390 server: TG4DRDA_DB2MVS
# For DB2/400 server: TG4DRDA_DB2400
# For DB2/UDB server: TG4DRDA_DB2UDB
# For DB2/VM server: TG4DRDA_DB2VM
FDS_CLASS=TG4DRDA_DB2UDB

# DRDA_CONNECT_PARM: Default: DRDACON1:446 for TCP. Default: DRDACON1 for SNA
# System dependent must be customized
# TCP format: hostname.network:TCP Port Number
# TCP example: host.us.oracle.com:2019
# SNA format: sideinfo_name from Local SNA configuration
# SNA example: DB2LU
DRDA_CONNECT_PARM=192.168.0.4:50000

# DRDA_REMOTE_DB_NAME: Default: DB2V2R3, system must be customized
# Name of DB2 instance on UNIX.
DRDA_REMOTE_DB_NAME=BETA

# DRDA_PACKAGE_COLLID: Default: ORACLE, system must be customized
# Specifies the collection id defined on non-Oracle database system.
# On DB2: GRANT CREATE IN COLLECTION ORACLE TO id
DRDA_PACKAGE_COLLID=ORADRDA

# DRDA_PACKAGE_NAME: Default: G2DRSQL
# Parameter can be any 1-18 character distinctive string.
DRDA_PACKAGE_NAME=G2DRSQ2

#DRDA_PACKAGE_CONSTOKEN: Default: none. Do not change value.
DRDA_PACKAGE_CONSTOKEN=A929B170D366FB00

# DRDA_RECOVERY_USERID: Default: ORARECOV, must be a valid UNIX userid.
# The recovery user connects to the IBM database if a distributed transaction
# is in doubt.
DRDA_RECOVERY_USERID=ORADRDA

# DRDA_RECOVERY_PASSWORD: Default: none, must be a valid UNIX password.
# The recovery user connects to the IBM database if a distributed transaction
# is in doubt.
DRDA_RECOVERY_PASSWORD=xxxxxxxxx

# DRDA_ISOLATION_LEVEL: Default: CS
# Possible Values: CHG Change (Default for DB2/400)
# CS Cursor Stability (default for DB2/UDB, DB2/OS390 and
# DB2/VM
# RR Repeatable Read
# Be careful changing the DRDA_ISOLATION_LEVEL. It will effect ALL DB2 users !
DRDA_ISOLATION_LEVEL=NC

# DRDA_PACKAGE_OWNER: Default: none, must be a valid UNIX userid
# This parameter allows you to assign the package owner to be someone other
# than the userid connected when the BIND package program (gtw$_bind_pkg)
# is run. THIS USER MUST ALSO OWN THE ORACLE2PC table. See the Installation
# Guide for more information. It is currently commented out.
#
DRDA_PACKAGE_OWNER=ORADRDA

# DRDA_DISABLE_CALL: Default: True
# This parameter is used to disable Stored Procedure support in the gateway
# for those DRDA Servers which the gateway does not currently support calling
# Stored Procedures.
DRDA_DISABLE_CALL=FALSE

# DRDA_CAPABILITY: Default: none
# Parameter determines which Oracle mapped functions will be treated natively
# with no pre/post processing.
#DRDA_CAPABILITY={FUNCTION/{ON|OFF}},...

# DRDA_CODEPAGE_MAP: Default: $ORACLE_HOME/tg4drda/admin/codepage.map
# Parameter specifies the location of the codepage map file. The default search
# location is $ORACLE_HOME/tg4drda/admin.
#DRDA_CODEPAGE_MAP=codepage.map

# DRDA_CMSRC_CM_IMMEDIATE: Default: FALSE
# Parameter sets the SNA session allocation mode. A setting of FALSE will cause
# the gatewa to wait for a free session if no free sessions exist. A setting of
# TRUE will cause the gateway to fail the allocation immediately if no free
# sessions exist.
#DRDA_CMSRC_CM_IMMEDIATE=FALSE

# DRDA_DEFAULT_CCSID: Default: None
# Parameter specifies the default character set codepage for character set
# conversions when the DRDA server database indicates that a character string
# has a CCSID of 65535.
#DRDA_DEFAULT_CCSID=
# DRDA_FLUSH_CACHE: Default: SESSION
# Parameter specifies when the cursor cache is to be flushed. When parameter is
# set to COMMIT, the cursor cache is flushed whenever a transactio is committed.
# When parameter is set to SESSION, the cursor cache is not flushed until the
# session is terminated.
#DRDA_FLUSH_CACHE=SESSION

# DRDA_COMM_BUFLEN: Default: 32767, use for SNA communications only
# Parameter specifies the communications buffer length for SNA sends/receives.
#DRDA_COMM_BUFLEN=32767

# DRDA_DESCRIBE_TABLE: Default: TRUE
# Parameters directs the gateway to use the DRDA operation "Table Describe" to
# return the description of tables.
DRDA_DESCRIBE_TABLE=FALSE

# DRDA_CACHE_TABLE_DESC: Default: TRUE
# Parameters directs the gateway to cache table descriptions once instead
# of querying the remote server each time.
#DRDA_CACHE_TABLE_DESC=TRUE

# DRDA_LOCAL_NODE_NAME: Default: AIX_RS6K
# Parameter should not be changed. This name is used internally by the DRDA
# server to identify the local node.
DRDA_LOCAL_NODE_NAME=AIX_RS6K

# DRDA_OPTIMIZE_QUERY: Default: TRUE
# Parameters turns on or off the distributed query optimizer (DQO) capability.
# Parameter only valid on DB2/MVS and DB2/VM. All other servers must set the
# value to FALSE.
DRDA_OPTIMIZE_QUERY=FALSE

# DRDA_SECURITY_TYPE: Default: PROGRAM, use for SNA communications only
# Parameter specifies the type of SECURITY used for SNA communications.
#DRDA_SECURITY_TYPE=PROGRAM

# DRDA_PACKAGE_SECTIONS: Default: 100
# Parameter specifies the number of cursors declared at the remote database
# when the package is bound. This is the max number of open cursors allowed
# at any one time.
#DRDA_PACKAGE_SECTIONS=100

# DRDA_READ_ONLY: Default: FALSE
# Parameter specifies whether the gateway runs in a read-only transaction mode.
# In this mode, SQL statements that modify data are not allowed.
#DRDA_READ_ONLY=FALSE

# ORA_MAX_DATE: Default: 4712-12-31
# Parameter specifies the gateway maximun date value. Range of values is any
# valid date less than 4712-12-31
#ORA_MAX_DATE=4712-12-31

# DRDA_GRAPHIC_PAD_SIZE: Default: 0
# Parameter adds size padding to GRAPHIC column descriptions.
#DRDA_GRAPHIC_PAD_SIZE=0

# DRDA_GRAPHIC_LIT_CHECK: Default: FALSE
# Parameter controls gateway analysis and conversion of multi-byte data
# to double-byte format of string literals in SQL INSERT statements as
# applied to GRAPHIC columns on the remote server.
#DRDA_GRAPHIC_LIT_CHECK=FALSE

# DRDA_GRAPHIC_TO_MBCS: Default: FALSE
# Parameter controls gateway conversion of double-byte data to multi-byte
# format when retrieving GRAPHIC data from remote server.
#DRDA_GRAPHIC_TO_MBCS=FALSE

# DRDA_MBCS_TO_GRAPHIC: Default: FALSE
# Parameter controls gateway conversion of multi-byte data to double-byte
# format when inserted into GRAPHIC columns at the remote server.
#DRDA_MBCS_TO_GRAPHIC=FALSE
Any help would be greatly appreciated.

Thanks,
Steve
0
Comment
Question by:fairrington
  • 2
  • 2
4 Comments
 
LVL 37

Accepted Solution

by:
momi_sabag earned 500 total points
ID: 34930150
i'm not that familiar with the tool you are using but the two instances use different ports...
0
 

Author Comment

by:fairrington
ID: 34930174
That could be my problem! How do I find out the port it's on? I know my production instance is on 50000.

Thanks!
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 34930222
each db2 instance has a parameter called SVCNAME
you can see it by issuing
get dbm cfg
from the db2 clp
 
0
 

Author Closing Comment

by:fairrington
ID: 34930265
Once I found the port the Beta instance was on the Gateway connected without an error.

Thanks.....
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
case statement in where clause 5 48
Oracle and DateTime math 6 39
Oracle 12c database link between pdb not working 20 81
Oracle Listener Not Starting 11 44
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Via a live example, show how to take different types of Oracle backups using RMAN.

822 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question