Solved

gateway connectivity from Oracle 10g to a different DB2 instance

Posted on 2011-02-18
4
1,260 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Can't Access My Database 57 86
ER Diagram 3 42
oracle spooling query into csv eliminating new line character 9 40
Excess Redo 3 32
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…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to recover a database from a user managed backup

733 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