Configuring Oracle Tuxedo XA Connection to DB2 Using DB2 Connect

This chapter contains the following topics:

•	Prerequisite

•	DB2 Connect Configuration

•	Oracle Tuxedo Configuration

•

Summary

•	Trouble Shooting

Prerequisite

Before you start to configure Tuxedo XA connection to DB2 running on mainframe using DB2 Connect, you need to:

•	Install DB2 Connect for Universal System on the machine running Tuxedo.

•	Configure TCP/IP communication on both mainframe and the machine running Tuxedo.

DB2 Connect Configuration

This section describes the steps required to configure DB2 Connect on open system to connect to DB2 server running on mainframe. These steps must be performed by users who have the necessary system privileges and special expertise, such as your network or system administrator, or your DB2 administrator.

DB2 Instance Creation

This chapter describes how to use db2icrt to create DB2 instance which will be used to connect to database residing on mainframe. The db2icrt command creates DB2 instances in the instance owner's home directory.

Note:

The DB2 DB2ICRT command is not available for a non-root installation of DB2 database products on Linux and UNIX operating system.

On Linux or UNIX operating systems, this utility is located in the DB2DIR/instance directory, where DB2DIR represents the location where the DB2 Connect is installed. On Windows operating system, this utility is located under the DB2PATH\bin directory where DB2PATH is the location where the DB2 Connect is installed.

•	Command Syntax:

db2icrt -[ h, d, p, a, s, u ] Instname

Example:

$DB2DIR/instance/db2icrt -u db2art db2art

The db2icrt command takes the following parameters:

-h | -?

Displays the usage information.

-d

Turns debug mode on. Use this option only when instructed by DB2 database support.

-a AuthType

Specifies the authentication type (SERVER, CLIENT or SERVER_ENCRYPT) for the instance. The default is SERVER.

-p PortName

Specifies the port name or number used by the instance. This option does not apply to client instances.

-s InstType

Specifies the type of instance to create. Use the -s option only when you are creating an instance other than the default associated with the installed product from which you are running db2icrt. Valid values are: Client, standalone, ese or wse.

-u Fenced ID

Specifies the name of the user ID under which fenced user-defined functions and fenced stored procedures will run. The -u option is required if you are not creating a client instance.

InstName

Specifies the name of the instance which is also the name of an existing user in the operating system. This has to be the last argument of the db2icrt command.

DB2 Instance Configuration

This section describes how to use CATALOG command to setup the connection to DB2 server residing on mainframe. You can also use DB2 Client Configuration Assistant (CCA), a graphic user interface tool instead, but it is not covered in this section

DB2 CATALOG

DB2 maintains a set of tables that contain information about the data that DB2 controls. These tables are collectively known as the catalog.

The catalog tables contain information about DB2 objects such as tables, views, and indexes. When you create, alter, or drop an object, DB2 inserts, updates, or deletes rows of the catalog that describe the object.

The DB2 catalog consists of tables of data about everything defined to the DB2 system, including table spaces, indexes, tables, copies of table spaces and indexes, and storage groups. The system database DSNDB06 contains the DB2 catalog.

Before starting this step, you need to check:

•	Whether the DB2 server on mainframe is started, and TCP/IP communication is up or not.

•	Assign an unused TCP port for the DB2 instance listener and add it into your local configuration "/etc/services" like this:

db2c_db2art 60000/tcp

Next, use CATALOG TCP/IP NODE, CATALOG DCS DATABASE and CATALOG DATABASE step by step to finish the connection setup.

CATALOG TCP/IP NODE

The CATALOG TCP/IP NODE command syntax is as follows:

catalog [ ADMIN ] [ TCP/IP protocol ] node [ Node-name ] remote [ Hostname ] server [ Service-name ] with [ comment-string ]

Example:

db2 catalog tcpip node wasa-host remote wasa server 4001 with "catalog remote host wasa:4001 to local alias wasa-host"

CATALOG TCP/IP NODE takes the following parameters:

ADMIN

Specifies that a TCP/IP administration server node is to be cataloged. This parameter cannot be specified if the SECURITY SOCKS parameter is specified.

TCP/IP Protocol

Specifies TCP/IP protocol used, could be: TCPIP, TCPIP4, TCPIP6

Node-name

The nodename of the TCPIP, TCPIP4, or TCPIP6 node represents a local nickname you can set for the machine that contains the database you want to catalog. Only specify TCPIP4 when specifying an IPv4 IP address, and only specify TCPIP6 when specifying an IPv6 IP address.

Hostname

The hostname or the IP address of the node where the target database resides. IP address can be an IPv4 or IPv6 address. The hostname is the name of the node that is known to the TCP/IP network. The maximum length of the hostname is 255 characters.

Service-name

Specifies the service name or the port number of the server database manager instance. The maximum length is 14 characters. This parameter is case sensitive.

If a service name is specified, the services file on the client is used to map the service name to a port number. A service name is specified in the server's database manager configuration file, and the services file on the server is used to map this service name to a port number. The port number on the client and the server must match.

A port number, instead of a service name, can be specified in the database manager configuration file on the server, but this is not recommended. If a port number is specified, no service name needs to be specified in the local services file.

CATALOG DCS DATABASE

This command stores information about remote host (z/OS or OS/390) or iSeries (OS/400) databases in the Database Connection Services (DCS) directory. These databases are accessed through an Application Requester (AR), such as DB2 Connect. Having a DCS directory entry with a database name matching a database name in the system database directory invokes the specified AR to forward SQL requests to the remote server where the database resides.

Note:

If you just need access the data residing on universal system, such as UNIX, Linux, you could use CATALOG DATABASE only.

The CATALOG DCS DATABASE command syntax is as follows:

catalog dcs database [ Database-name ] as [ Target-database-name ] with [comment-string ]

Example:

db2 catalog dcs database db2wasa as qwa1 with "catalog the remote host

database qwa1 to local db2wasa"

CATALOG DCS DATABASE takes the following parameters:

Database-name

Specifies the alias of the target database to catalog. This name should match the name of an entry in the database directory that is associated with the remote node.

Target-database-name

Specifies the name of the target host or iSeries database to catalog.

DB2 CATALOG DATABASE

This command stores database location information in the system database directory. The database can be located either on the local workstation or on a remote database partition server.

Note:

If you need access the data residing on local or remote universal system, such as UNIX, Linux, you could use it.

The DB2 CATALOG DATABASE command syntax is as follows:

catalog database [ Database-name ] as [ Alias ] at node [ Node-name ] authentication [ Authentication-type ] with [ comment-string ]

Example:

db2 catalog database db2wasa at node wasa-host authentication dcs with "catalog the local db2wasa with dcs authentication type"

DB2 CATALOG DATABASE takes the following parameters:

Database-name

Specifies the name of the database to catalog.

Alias

Specifies an alias as an alternate name for the database being cataloged. If an alias is not specified, the database manager uses database-name as the alias.

Node-name

Specifies the name of the database partition server where the database being cataloged resides. This name should match the name of an entry in the node directory. If the node name specified does not exist in the node directory, a warning is returned, but the database is cataloged in the system database directory. The node name should be cataloged in the node directory if a connection to the cataloged database is desired.

Authentication-type

The authentication value is stored for remote databases (it appears in the output from the LIST DATABASE DIRECTORY command) but it is not stored for local databases

DB2 START UP

After all the steps of CATALOG, you need to update some database manager configuration and start DB2 Connect.

•	Update the SVCENAME of your DB2 instance to the listener added into "/etc/services" before.

db2 update dbm cfg using SVCENAME db2c_db2art

•	Set the communication protocol to TCP/IP.

db2set DB2COMM=tcpip

•	Configure the auto start and start DB2.

db2set DB2AUTOSTART=yes
db2start

Oracle Tuxedo Configuration

What follows is the description of the process to configure Oracle Tuxedo for accessing the database residing on mainframe with DB2 Connect. There are some differences based on whether Tuxedo is working with a 64-bit instance of DB2 Database or a 32-bit instance of DB2.

•	Set the DB2INSTANCE environment variable to reference the instance that contains the databases that you want Tuxedo to use. Set the PATH variable to include the DB2 Connect directories. Confirm the User ID and Password that can connect to the DB2 databases, you could find some example below:

export DB2INSTANCE=db2art
export DB2DIR=/opt/ibm/db2_connect/V9.1

•	Update some database manager configuration parameters accordingly.

•	Update the tp_mon_name database manager configuration parameter with the value TUXEDO. This parameter identifies the name of the transaction processing (TP) monitor product being used.

Valid value includes CICS, MQ, CB, SF, TUXDEO, TOPEND, WAS, blank or some other value.

db2 update dbm cfg using tp_mon_name TUXEDO

•	Update the spm_name database manager configuration parameter with the hostname of the machine has DB2 Connect installed. This parameter identifies the name of the sync point manager (SPM) instance to the database manager.

Valid value applies to:

•	Database server with local and remote clients

•	Database server with local clients

•	Partitioned database server with local and remote clients

db2 update dbm cfg using spm_name bjaix ("bjaix" is the hostname of the machine which installs and configures DB2 Connect)

•

Update the max_connections to be greater than max_coordagents to activate the XA Concentrator. DB2 Connect's connection concentrator technology allows DB2 Connect Enterprise Edition servers to provide support to thousands of users simultaneously executing business transactions, while drastically reducing resources required on the S/390 host or AS/400 database servers.

Note:

If the application is accessing data residing on DB2 for z/OS and OS/390(R), DB2 for iSeries, or DB2 for VM&VSE, the DB2 Connect XA concentrator should be required.

You can activate the concentrator feature by setting the value of MAX_CONNECTIONS to any number greater than the default. The default value for MAX_CONNECTIONS is equivalent to the value of MAX_COORDAGENTS. Because each application will have one logical agent, MAX_CONNECTIONS actually controls the number of applications that can be connected to the database instance, while MAX_COORDAGENTS controls the number of inbound connections that can be active at any time. MAX_CONNECTIONS will take a numeric range from MAX_COORDAGENTS up to 64,000. The default number of logical agents is equal to MAX_COORDAGENTS.

db2 update dbm cfg using max_connections 500
db2 update dbm cfg using max_coordagents 200

•	Add a definition for DB2 Connect to the Tuxedo resource manager definition file ($TUXDIR/udataobj/RM). In the examples that follow, UDB_XA is the locally-defined Tuxedo resource manager name for DB2 Connect, and db2xa_switch_std is the DB2-defined name for a structure of type xa_switch_t.

# DB2 UDB
UDB_XA:db2xa_switch_std:-L${DB2DIR}/lib -ldb2

•	Build the Tuxedo transaction monitor server (TMS) for DB2:

${TUXDIR}/bin/buildtms -r UDB_XA -o ${TUXDIR}/bin/TMS_UDB

•

Write your own application servers which accesses the DB2 database residing on mainframe, and you can follow the examples below. The -r option specifies the resource manager name, the -f option specifies the files that contain the application business logic, the -s option specifies the application service names for this server, and the -o option specifies the output server file name.

${TUXDIR}/bin/buildserver -r UDB_XA -f svcfile.o -s SVC1,SVC2 -o UDBserver

•	Set up the Tuxedo configuration file to reference the DB2 server. In the *GROUPS section of the UBBCONFIG file, add an entry similar to:

UDB_GRP LMID=simp GRPNO=3
TMSNAME=TMS_UDB TMSCOUNT=2
OPENINFO="UDB_XA:db=sample,uid=username,pwd=password,tpm=tuxedo"

Where the TMSNAME parameter specifies the transaction monitor server that you built previously, and the OPENINFO parameter specifies the resource manager name. This is followed by the database name, and the DB2 database user ID and password, which are used for authentication.

The application servers that you built previously are referenced in the *SERVERS section of the Tuxedo configuration file.

•	Start Tuxedo application.

tmboot -y

Summary

This chapter summarizes all the steps needed and provides methods to check the connection and error detection.

1.	Firstly, let's recap the step-by-step procedures:

db2icrt -a server -u db2art db2art

db2 catalog tcpip node wasa-host remote wasa server 4001

db2 catalog dcs database db2wasa as qwa1

db2 catalog database db2wasa at node wasa-host authentication dcs

db2 update dbm cfg using SVCNAME db2c_db2art

db2set DB2COMM=tcpip

db2set DB2AUTOSTART=yes

db2stop & db2start

2.	And then, let's check whether the connection is reachable:

export DB2INSTANCE=db2art

export DB2BASE=db2wasa

db2 connect to $DB2BASE user user-name using password

3.

And check the output, if the screen shows some database connection information as below, it means you are able to access the database residing on mainframe, and you can create table or insert data as you required. If screen shows some errors with error code, maybe there are some failures there. Please find the most common errors and its correction in the APPENDIX.

Database Connection Information

Database server = DB2 OS/390 9.1.5

SQL authorization ID = BEAUSR1

Local database alias = DB2QWA1

4.	Finally, let's configure the Tuxedo application and start it up.

export TUXDIR=/home/db2art/tuxedo11gR1

export DB2INSTANCE=db2art

export DB2DIR=/opt/ibm/db2_connect/V9.1

db2 update dbm cfg using tp_mon_name TUXEDO

db2 update dbm cfg using spm_name bjaix

db2 update dbm cfg using max_connections 500

db2 update dbm cfg using max_coordagents 200

Add "UDB_XA:db2xa_switch_std:-L${DB2DIR}/lib -ldb2" into $TUXDIR/udataobj/RM

${TUXDIR}/bin/buildtms -r UDB_XA -o ${TUXDIR}/bin/TMS_UDB

${TUXDIR}/bin/buildserver -r UDB_XA -f svcfile.o -s SVC1,SVC2 -o UDBserver

Configure OPENINFO in ubbconfig: OPENINFO="UDB_XA:db=sample,uid=username,pwd=password,tpm=tuxedo"

tmboot -y

Trouble Shooting

This section lists the most common symptoms of connection problems encountered when using DB2 Connect. In each case, you are provided with:

•	A combination of a message number and a return code (or protocol specific return code) associated with that message. Each message and return code combination has a separate heading, and the headings are ordered by message number, and then by return code.

•	A symptom is provided, usually in the form of a sample message listing.

•	A suggested solution is provided, indicating the probable cause of the error. In some cases more than one suggested solution may be provided.

SQL1403N

Symptom: SQL1403N The username and/or password supplied is incorrect.

Solution: User fails to authenticate at the DB2 Connect workstation. Determine whether the user is supposed to be authenticated at the DB2 Connect workstation.

If yes, make sure that the correct password is provided on the CONNECT statement if necessary.

If no, the system database directory entry must have been incorrectly cataloged using AUTHENTICATION SERVER (this is the default if AUTHENTICATION is not specified explicitly). If this is the case, then re-catalog the entry using AUTHENTICATION DCS or CLIENT.

Password is not available to send to the target server database. If the system database directory entry is cataloged using AUTHENTICATION DCS, then a password has to be flowed from the DB2 Client to the target server database. On certain platforms, for example AIX, the password can only be obtained if it is provided on the CONNECT statement.

SQL5043N

Symptom: Support for one or more communications protocols failed to start successfully. However, core database manager functionality started successfully.

Perhaps the TCP/IP protocol is not started on the DB2 Connect gateway. There may have been a successful client connection previously

Solution: This warning is a symptom which signals that DB2 Connect, acting as a gateway for remote clients, is having trouble handling one or more client communication protocols. These protocols can be TCP/IP, APPC and others, and usually the message indicates that one of the communications protocols defined to DB2 Connect is not configured properly.

Often the cause may be that the DB2COMM profile variable is not defined, or is defined incorrectly. Generally, the problem is the result of a mismatch between the DB2COMM variable and names defined in the database manager configuration (for example, svcname or tpname).

One possible scenario is having a previously successful connection, then getting the SQL5043 error message, while none of the configuration has changed. This could occur using the TCP/IP protocol, when the remote system abnormally terminates the connection for some reason. When this happens, a connection may still appear to exist on the client, and it may become possible to restore the connection without further intervention by issuing the commands shown below.

Most likely, one of the clients connecting to the gateway still has a handle on the TCP/IP port. On each client machine that is connected to the gateway.

SQL30061

Symptom: Connecting to the wrong host or AS/400 database server location - no target database can be found.

Solution: The wrong server database name may be specified in the DCS directory entry. When this occurs, SQLCODE -30061 is returned to the application.

Check the DB2 node, database, and DCS directory entries. The target database name field in the DCS directory entry must correspond to the name of the database based on the platform.

SQL30081 with Return Code 79

Symptom: SQL30081N A communication error has been detected.

Communication protocol being used: "TCP/IP". Communication API being used: "SOCKETS".

Location where the error was detected: "". Communication function detecting the error:

"connect". Protocol specific error code(s): "79", "*", "*". SQLSTATE=08001

Solution: This error can occur in the case of a remote client failing to connect to a DB2 Connect gateway. It can also occur when connecting from the DB2 Connect gateway to a host.

The DB2COMM profile variable may set incorrectly on the DB2 Connect gateway. Check this. For example, the command db2set db2comm=tcpip should appear in sqllib/db2profile when running DB2 Extended Enterprise Edition on AIX.

There may be a mismatch between the TCP/IP service name and/or port number specifications at the DB2 client and the DB2 Connect gateway. Verify the entries in the TCP/IP services files on both machines.