| Using the distributed data facility (DDF) of DB2 allows for access to data held by other data management systems or makes your DB2 data accessible to other systems. A DB2 application program can use SQL to access data controlled by database management systems (DBMSs) other than the DB2 at which the application's plan is bound. This DB2 is known as the local DB 2. The local DB2 and the other DBMSs are called application servers (AS). Any application server other than the local DB2 is considered a remote server (RS), and access to its data is a distributed operation. DB2 provides two methods of accessing data at remote application servers: DRDA and DB2 private protocol access. For application servers that support the two-phase commit process, both methods allow for updating data at several remote locations within the same unit of work. The location name of the DB2 subsystem is defined during DB2 installation. The communications database (CDB) records the location name and the network address of a remote DBMS. The tables in the CDB are part of the DB2 catalog. DRDA: 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). It was an open standard that could be licensed by other companies and provided for communication between a client (application requester) and a server (application server). The basic ideas of the protocol were to maximize the functionality and to reduce the overhead when communicating between like platforms. The overhead for like platforms and the maximization of the 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 OS/390 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 different levels of support: Remote request: A single request to one remote DBMS. The remote request consists 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. 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. Distributed request: 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. There are no immediate plans 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 which 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 data (on any platform), you can use a different solution than if you want non-DB2 data. The first question, how to perform a distributed request, is answered by any DB2 for UNIX, Windows, or OS/2 (DB2 UWO) manual that is at least version 7. The section in 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. This includes recursion, rollup, cube, and many other SQL functions that do not exist on the OS/390. The second question, how to incorporate non-DB2 data sources, requires a dual answer. The full support for distributed request functionality is currently available only when using DataJoiner. DataJoiner uses a private solution to solve this problem and does not use the DRDA extensions. DB2 UWO 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 and DB2 UWO 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 obtain 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 an alias (if bound with DBPROTOCOL [DRDA]) to access the server. For more information on bind options, refer to Chapter 6, "Binding an Application Program." 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 of 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 a DB2 to bind and rebind packages at other servers and to execute the statements in those packages. For two-phase commit using System Network Architecture (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 only works between application requesters and application servers that are both DB2 for OS/390 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, and 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 whose location name is NYSERVER. Alias names have the same allowable forms as table or view names. The name can refer to a table or view at the current server or to a table or 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. Also, private protocol support is going away with v7. |
Communications Protocols The DDF uses TCP/IP or SNA to communicate with other systems. Figure 10-1 shows the connectivity options you have with DB2's DDF. Figure 10-1. Connectivity options.  Setting up a network for use by DBMSs requires knowledge of both database management and communications. Thus, you must put together a team of people with those skills to plan and implement the network. SNA 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. VTAM DB2 also uses 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 (LU 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 nam e. DB2 uses the DRDA term RDBNAM to refer to non-DB2 relational database names. TCP/IP Transmission Control Protocol/Internet Protocol (TCP/IP) is a standard communication protocol for network communications. Previous versions of DB2 supported TCP/IP requesters, although additional software and configuration was required. Native TCP/IP eliminates these requirements, allowing gateway-less connectivity to DB2 for systems running UNIX System Services. The security aspects of TCP/IP are different from SNA. Unlike SNA, where it is possible to tighten security, TCP/IP is much easier to mimic the address you are coming from. As a result, not all features available to 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, then 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 really need this functionality and you are sure your network is sufficiently secured so that you don't have any issues, then you would have to use the DB2 exits to perform the inbound user ID translation. You have to make sure 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. CDB The DB2 catalog includes the communications database (CDB), which contains several tables that hold information about connections with remote systems. These tables include -
SYSIBM.LOCATIONS -
SYSIBM.LUNAMES -
SYSIBM.IPNAMES -
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 SYSIBM.LUNAMES table, SNA is used. -
If the same name is in both SYSIBM.LUNAMES and SYSIBM.IPNAMES,TCP/IP is used to connect to the location. NOTE | | A requester cannot connect to a given location using both SNA and TCP/IP protocols. For example, if the 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 UWO has a piece of software called the client configuration assistant, which communicates with a component on the server called the DB2 Database Administrative Server. This DB2DAS provides the client with all the information required to define the target database. Today DB2 for OS/390 and Z/OS does not provide this DB2DAS, and the user has to find the required information manually. The DB2 command -DIS DDF lists all the information required to set up the connection to the DB2 (see for the following sample output). Without this command, you would have to print the content of the bootstrap dataset (BSDS), which contains other sensitive information you may not want exposed. 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 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. There is therefore no way to define at the application level which member of the data sharing group should be addressed. The mapping to the group or an individual member should be defined at the level of the configuration of the client. On OS/390 that would be the definitions in the CDB; when using DB2 Connect the target is defined in the node directory. In fact, the purpose 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. With every new release of DB2 Connect, we see further improvements to make the routing to the data sharing members more transparent and efficient. At the networking level, we find solutions that allow you to " virtualize " the DB2 server. These include the use of SNA/APPC (Advanced Program to Program Communication) generic resources and techniques such as TCP/IP VIPA (Virtual IP Address). Both require a considerable investment from the networking side. Another alternative is the definition of dynamic APPC application definitions. In this case we make use of 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. | 
