Solved

gateway connectivity from Oracle 10g to a different DB2 instance

Posted on 2011-02-18
4
1,234 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
Comment Utility
i'm not that familiar with the tool you are using but the two instances use different ports...
0
 

Author Comment

by:fairrington
Comment Utility
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
Comment Utility
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
Comment Utility
Once I found the port the Beta instance was on the Gateway connected without an error.

Thanks.....
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to recover a database from a user managed backup

771 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now