Distributed Data | DB2 for z/OS Version 8 DBA Certification Guide

Distributed Relational Database Architecture

Communicating between platforms introduces several issues, ranging from character conversion to definition of the actions that you want executed on the other platform. Several years ago, with DB2 for MVS/ESA, Version 2, Release 3, IBM introduced a set of protocols known as Distributed Relational Database Architecture (DRDA). An open standard that can be licensed by other companies, DRDA provides for communication between a client, or application requester, and an application server. DRDA was developed to maximize functionality and reduce the overhead when communicating between like platforms.

The overhead for like platforms and the maximization of functionality are achieved by putting the work on the platform that receives the message. For example, the request to fetch from a cursor will be processed by the application server, and the rows that are returned will be translated by the application requester. The support of ASCII tables in DB2 for z/OS allows you to have both the application server and the application requester on the same data representation model (code page), thereby effectively eliminating any data conversion. The original specifications identified four levels of support:

Remote request, a single request to one remote DBMS, consisting of one SQL statement. This level does not allow you to define a unit of work. Every SQL statement is an independent unit of work.
Remote unit of work (RUW), a unit of work requesting services to a single remote DBMS. RUW provides integrity within a single DBMS. Several statements can be grouped together in a single unit of work, but these statements cannot span more than one DBMS. Programs that take advantage of RUW can only connect to a single database at a time, but can switch between databases at the end of a unit of work.
Distributed unit of work (DUW), a unit of work requesting services to two or more DBMSs. DUW provides integrity across multiple DBMSs. However, access is to only a single DBMS in an SQL statement. The changes at all DBMSs are backed out if the execution of one SQL statement is not successful. Two-phase commit protocols are used to maintain consistency.
Distributed request, a request to two or more remote DBMSs residing on the same or different locations. Distributed request provides integrity across multiple locations and access to more than one location in an SQL statement. This level of distributed processing is not directly supported in the DRDA specification.

No immediate plans exist to extend the DRDA architecture and include the distributed-request functionality. The implementation of a standard technique to solve the distributed-request problem is much more complicated, as it would require the addition of optimization-related concepts, such as network performance and estimation of the cost of the remote SQL component. With DB2, however, you can find a solution for the distributed request, which is based on logic that is not part of the DRDA architecture but that is part of DB2's unique design.

You need to differentiate the two potential target data locations of the distributed request. If you want to combine DB2 dataon any platformyour solution will be different if you want non-DB2 data. The first questionhow to perform a distributed requestis answered by any DB2 for UNIX, Windows, or Linux manual that is at least version 7. The section on nicknames explains the steps required to make this work. What you need to remember is that you have full read/write access to any DB2 DRDA-enabled data source. Furthermore, that version of DB2 will also, as will any DRDA server, provide the full flavor of SQL syntax available on that platform, including recursion, rollup, cube, and many other SQL functions that do not exist on the z/OS platform.

The second questionhow to incorporate non-DB2 data sourcesrequires a two-part answer. The full support for distributed-request functionality is available only when using DataJoiner, which uses a private solution to solve this problem and does not use the DRDA extensions. DB2 LUW version 7 implements this solution partially, by integrating the read-only (R/O) part of the DataJoiner code. Non-DB2 R/O access to Oracle is provided by the Relational Connect option, of which several versions are available, including Oracle, Sybase, SQL Server, Informix, and Life Sciences. The current DataJoiner/DB2 LUW version 7 solution has a performance model of the target database and uses a copy of the remote object statistics to help the optimizer decide on the optimal placement of the SQL parts. The information required to determine this best-access plan is based on information added by the system administrator, such as relative network speed and processor capacity, and information obtained by extracting the catalog of the remote system.

With DRDA, the recommended method, the application connects to a server at another location and executes packages that have been previously bound at that server. The application uses a CONNECT statement, a three-part name, or, if bound with DBPROTOCOL (DRDA), an alias to access the server. For more information on bind options, refer to Chapter 11.

Queries can originate from any system or application that issues SQL statements as an application requester in the formats required by DRDA. DRDA access supports the execution of dynamic SQL statements and SQL statements that satisfy all the following conditions.

The static statements appear in a package bound to an accessible server.
The statements are executed using that package.
The objects involved in the execution of the statements are at the server where the package is bound. If the server is a DB2 subsystem, three-part names and aliases can be used to refer to another DB2 server.

DRDA communication conventions are invisible to DB2 applications and allow DB2 to bind and rebind packages at other servers and to execute the statements in those packages. For two-phase commit using SNA connections, DB2 supports both presumed-abort and presumed-nothing protocols that are defined by DRDA. If you are using TCP/IP, DB2 uses the sync-point manager defined in the documentation for DRDA level 3.

NOTE

DRDA protocols are documented by the Open Group Technical Standard in DRDA Volume 1: Distributed Relational Database Architecture (DRDA).

DB2 Private Protocol

An application program running under DB2 can refer to a table or view at another DB2. This is done with private protocol. With private protocol, the application must use an alias or three-part name to direct the SQL statement to a given location. Private protocol works only between application requesters and application servers that are both DB2 for z/OS subsystems.

A statement is executed using DB2 private-protocol access if it refers to objects that are not at the current server and is implicitly or explicitly bound with DBPROTOCOL(PRIVATE).The current server is the DBMS to which an application is actively connected. DB2 private-protocol access uses DB2 private connections. The statements that can be executed are SQL INSERT, UPDATE, DELETE, and SELECT statements with their associated SQL OPEN, FETCH, and CLOSE statements.

The location name identifies the other DB2 to the DB2 application server. A three-part name consists of a location, an authorization ID, and an object name. For example, the name NYSERVER.DB2USER1.TEST refers to a table named DB2USER1.TEST at the server with location name NYSERVER. Alias names have the same allowable forms as table or view names. The name can refer to a table or a view at the current server or to a table or a view elsewhere.

NOTE

Private protocol does not support many distributed functions, such as TCP/IP or stored procedures. The newer data types, such as LOB or user-defined types, are also not supported by private protocol. It is not the recommended method to use and is no longer being enhanced.

Communications Protocols

The DDF uses TCP/IP or SNA to communicate with other systems. SNA is the description of the logical structure, formats, protocols, and operational sequences for transmitting information through and controlling the configuration and operation of the networks. It is one of the two main network architectures used for network communications to the enterprise servers. On z/OS, SNA communication is implemented by VTAM.

Transmission Control Protocol/Internet Protocol (TCP/IP) is a standard communication protocol for network communications. Current levels of DB2 suppport full DRDA functionality over TCP/IP.

The security aspects of TCP/IP are different from those of SNA. With SNA, it is possible to tighten security; with TCP/IP, it is much easier to modify the address you are coming from. As a result, not all features available with the SNA protocol have been implemented for TCP/IP. DDF can be configured in such a way that it does inbound user ID translation. If a specific user ID enters the system, replace it with a new value.

Several systems use this to map a user ID to a functional user ID, which is very often the owner of the objects used in the application. IBM has decided not to implement inbound user ID translation for TCP/IP clients. If you need this functionality and are sure that your network is sufficiently secured so that you don't have any issues, you have to use the DB2 exits to perform the inbound user ID translation. You have to make sure that you modify the proper exit, as TCP/IP clients do not use the sign-on exit. The module that should be adapted is the DSN3@ATH exit.

DB2 can use virtual terminal access method (VTAM) for communicating with remote databases. This is done by assigning two names for the local DB2 subsystem: a location name and a logical unit name. A location name distinguishes a specific DBMS in a network, so applications use this name to direct requests to the local DB2 subsystem. Other systems use different terms for a location name. For example, DB2 Connect calls this the target database name. DB2 uses the DRDA term RDBNAM to refer to non-DB2 relational database names.

Communications Database

The DB2 catalog includes the communications database (CDB), which contains several tables that hold information about connections with remote systems. These tables are

SYSIBM.LOCATIONS
SYSIBM.LUNAMES
SYSIBM.IPNAMES
SYSIBM.IPLIST
SYSIBM.MODESELECT
SYSIBM.USERNAMES
SYSIBM.LULIST
SYSIBM.LUMODES

Some of these tables must be populated before data can be requested from remote systems. If this DB2 system services only data requests, the CDB does not have to be populated; the default values can be used.

When sending a request, DB2 uses the LINKNAME column of the SYSIBM.LOCATIONS catalog table to determine which protocol to use.

To receive VTAM requests, an LU name must be selected in installation panel DSNTIPR.
To receive TCP/IP requests, a DRDA port and a resynchronization port must be selected in installation panel DSNTIP5. TCP/IP uses the server's port number to pass network requests to the correct DB2 subsystem. If the value in the LINKNAME column is found in the SYSIBM.IPNAMES table, TCP/IP is used for DRDA connections. If the value is found in the SYSIBM.LUNAMES table, SNA is used.
If the same name is in both SYSIBM.LUNAMES and SYSIBM.IPNAMES, then TCP/IP is used to connect to the location.

NOTE

A requester cannot connect to a given location name using both SNA and TCP/IP. For example, if SYSIBM.LOCATIONS specifies a LINKNAME of LU1, and if LU1 is defined in both the SYSIBM.IPNAMES and SYSIBM.LUNAMES tables, TCP/IP is the only protocol used to connect to LU1 from this requester for DRDA connections. For private-protocol connections, the SNA protocols are used. If private-protocol connections are being used, the SYSIBM.LUNAMES table must be defined for the remote location's LU name.

Gathering Configuration Information

How do you obtain the information to fill in the CDB and set up the connection with the remote system? DB2 UDB for Linux, UNIX, and Windows has a piece of software called the Client Configuration Assistant, which communicates with a server component called the DB2 Database Administration Server (DAS), which provides the client with all the information required to define the target database. Today, DB2 for z/OS version 8 provides this DAS in the optional DB2 Management Client Package for z/OS component. Alternatively, the user can find the required information manually. The DB2 command -DIS DDF lists all the information required to set up the connection to the DB2:

 DSNL080I  -DBT5 DSNLTDDF DISPLAY DDF REPORT FOLLOWS: DSNL081I STATUS=STARTD DSNL082I LOCATION           LUNAME            GENERICLU DSNL083I DBT5               TU0.BSYSDBT5      -NONE DSNL084I IPADDR          TCPPORT RESPORT DSNL085I 10.35.4.16      2590    2591 DSNL086I SQL    DOMAIN=t390.rpc.com DSNL086I RESYNC DOMAIN=t390.rpc.com DSNL090I DT=A  CONDBAT=     64 MDBAT=   64 DSNL092I ADBAT=    0 QUEDBAT=      0 IN1DBAT=      0 CONQUED=      0 DSNL093I DSCDBAT=      0 IN2CONS=      0              DSNL099I DSNLTDDF DISPLAY DDF REPORT COMPLETE

Without this command, you would have to print the content of the bootstrap data set (BSDS), which contains other sensitive information you may not want exposed.

When setting up clients that will access DB2 for z/OS using TCP/IP, it is a good idea to use a host name that corresponds to the DB2 subsystem rather than the host name of the z/OS system where DB2 is currently running. This will make it easier to move the DB2 subsystem to a different z/OS system without having to reconfigure all of the client systems.

Communicating with a Data Sharing Group

All the members of the DB2 data sharing group have the same location name, but each individual member has its own specific communication parameters. Each member can also be designated with one or more alias location names. The mapping to the group or an individual member should be defined at the level of the configuration of the client. On z/OS, that would be the definitions in the CDB; with DB2 Connect, the target is defined in the node directory.

In fact, one of the purposes of the data sharing implementation is to make the entire group virtually one in order to increase the availability of the overall data sharing group. Every new release of DB2 Connect makes the routing to the data sharing members more transparent and efficient.

At the networking level, solutions allow you to "virtualize" the DB2 server. These solutions include the use of SNA-APPC (Advanced Program-to-Program Communication) generic resources and such techniques as TCP/IP VIPA (Virtual IP Address). Both require a considerable investment in configuration on the networking side. Another alternative is the definition of dynamic APPC application definitions, using the concept of a model. The application used by DB2 is no longer defined in a static fashion but refers to a model that is active on all systems in the data sharing group.