Miscellaneous Distributed Topics

The following assortment of tips might prove to be helpful as you develop your distributed DB2 applications.

Workstation DB2

In addition to DB2 for z/OS, IBM also provides versions of DB2 for Linux, Unix and Windows (LUW). Of course, these DB2 implementations are not 100-percent compatible with DB2 for z/OS. Also, each DB2 uses SQL, but different SQL features are provided by each. For example, DB2 for LUW supports the EXCEPT clause for performing relational division and the INTERSECT clause for performing relational intersection. DB2 for z/OS does not.

The workstation DB2 products do not internally support DRDA. An additional product, DB2 Connect, which is covered in Chapter 43, provides DRDA support.

DDF Suspend and Resume

If you ever tried to make changes to your database structures in a distributed environment prior to DB2 V7, you probably had some trouble. It can be difficult to maintain your distributed databases, because the DDF DRDA server holds locks preventing you from running DDL. DB2 V7 provides relief with modifications to the STOP DDF and START DDF commands.

A simple STOP DDF command causes locks to be released, but it also causes all remote connections to be lost. DB2 V7 adds a new parameter to the STOP DDF command called MODE(SUSPEND) . Issuing a stop with this parameter suspends all DDF activity and enables your DDL to be run. When the maintenance is complete, the START DDF command will return DDF processing to normal.

DB2 Version 8 Distribution Improvements

IBM delivered a few nice improvements to distributed data support in DB2 for z/OS as of Version 8.

Database Aliases

One of these new features is the ability to specify a database alias for distributed connections. This is important because there is a basic difference in the way that distributed data is accessed by DB2 for z/OS and DB2 LUW. When you connect to DB2 for z/OS you address the entire DB2 subsystem by using its location name. With DB2 LUW however, access is by its database name. So the database name of the DB2 LUW system you want to connect to must be specified in the LOCATION column of the SYSIBM.LOCATIONS CDB catalog table (when the requester is DB2 for z/OS). Prior to DB2 V8, a one-to-one mapping was enforced between location name and database name. And there was no way to access multiple DB2 LUW databases that have the same name ”even if they reside on different machines.

With DB2 V8, the DBALIAS column is added to SYSIBM.LOCATIONS to fix this problem. You can use the DBALIAS column to point your SELECT statement to the real database name on DB2 LUW that you want to connect to. You continue to specify the LOCATION name as the first qualifier of your three-part table name in your SELECT statement. The mapped LINKNAME links you to the corresponding entry in SYSIBM.IPNAMES , which provides the correct TCP/IP address for the workstation you want to access.

Server Location Aliases

DB2 V8 also allows you to define up to eight alias names in addition to the location name for a DB2 data-sharing group . Each location alias is an alternative name that a requester can use to access a DB2 subsystem. Location alias names are defined using the change log inventory ( DSNJU003 ) utility. All that is required is adding the location alias names to your BSDS data sets on each member of the data sharing group. The syntax is as follows :

 DDF ALIAS =  aliasname  

After running DSNJU003 , the BSDS will store the location alias names that have been specified for the DB2 subsystem.

TCP/IP Network Member Routing

Finally, prior to DB2 V8 remote TCP/IP connections were typically set up to automatically balance connections across all members of a data sharing group. This is not always the desired setup, though. DB2 V8 enables you to setup member routing in a TCP/IP network. To route requests from certain DB2 for z/OS requesters to specific members of a data sharing group, combine the server location alias feature with the new CDB table SYSIBM.IPLIST .

Combining DRDA and Private Protocol Requests

By combining CONNECT statements and SQL statements that access three-part tables names, you can issue application-directed and system-directed requests from within a single unit of work. However, having a system-directed and an application-directed request to the same location is not possible. The requests must be to different locations.

Consider the following piece of code:

 CONNECT TO JACKSONVILLE;     .     .     . SELECT COL7 INTO   :HV7 FROM   DEPT;     .     .     . SELECT COL1, COL2 INTO   :HV1, :HV2 FROM   CHICAGO.OWNER.EMPLOYEE;     .     .     . COMMIT; 

The application connects to Jacksonville using application-directed access ( CONNECT ). At the Jacksonville location, the DEPT table is accessed. Within the same unit of work, a request is made for Chicago data using system-directed access (three-part table name).

DB2 Versions and Distribution

Distributed connectivity can be set up among multiple DB2 subsystems all running different versions if you so desire . Furthermore, you can access different release levels of DB2 within a single unit of work.

Developing Client/Server Applications

Client/server processing is popular development methodology for accessing remote data. DB2 is an ideal candidate for functioning as the server in the client/server framework. It can accept requests from multiple IBM and non-IBM RDBMS products.

ASCII Server Support

IBM mainframes use a different encoding scheme for alphanumeric characters than most other computers. The IBM encoding scheme is known as EBCDIC. When non-IBM computers communicate with IBM computers it is necessary to translate the EBCDIC encoding scheme to ASCII, the standard encoding scheme used by these other devices.

DB2 enables an entire subsystem, a database, a table space, or a table to be defined to use ASCII instead of EBCDIC. You can enhance performance by creating ASCII objects for distributed applications because characters will not need to be converted to EBCDIC when communicating with other ASCII servers.

Before creating ASCII objects, consider the following caveats:

• You can specify a different encoding scheme for DB2 objects using the CCSID parameter of the CREATE DATABASE , CREATE TABLESPACE , CREATE GLOBAL TEMPORARY TABLE , or CREATE TABLE statement.

• The encoding scheme of an object cannot be altered after the object is created.

• z/OS applications that display ASCII encoded data actually receive the data as EBCDIC, but sort the data using the ASCII collating sequence.

Another alternative, Unicode, is becoming increasingly popular. DB2 for z/OS is being used as the server in more and more large, client/server applications. Unicode makes it easier for multi-national organizations to manage data in a global environment where different languages require different code sets. DB2 V8 is engineered to use and take advantage of Unicode. As of V8, DB2 can convert any SQL statement to Unicode before parsing and as a result, all characters parse correctly. Additionally, the V8 DB2 Catalog has been re-engineering in Unicode.

Native TCP/IP Support

DB2 provides native TCP/IP support for distributed connections. Previous versions of DB2 supported TCP/IP requesters, but only with additional software and configuration. TCP/IP enables direct connections to DB2 from client applications without the overhead and expense of the additional software.

You can choose to use SNA, TCP/IP, or mixed networks for distributed DB2 applications.