Connectivity Supplement

Setting Up the Application Server

The application server support in DB2 for MVS/ESA allows DB2 for MVS/ESA to act as a server for DRDA application requesters. The Application Requester connected to a DB2 for MVS/ESA Application Server can be:

A DB2 for MVS/ESA requester
A DB2 Universal Database requester, which can run on AIX, HP-UX, OS/2, Solaris, Windows 3.1, Windows 3.11 for Workgroups, Windows 95, or Windows NT.
A DDCS Version 2 requester, which can run on AIX, HP-UX, OS/2, Solaris, Windows 3.1, Windows 3.11 for Workgroups, Windows 95, or Windows NT, as well as SCO, SGI, or SINIX.
A DB2 Connect Version 5 requester, which can run on AIX, HP-UX, OS/2, Solaris, Windows 3.1, Windows 3.11 for Workgroups, Windows 95, or Windows NT.
An OS/400 requester
A DB2 for VM requester
Any other product that supports the DRDA Application Requester protocols

For any Application Requester connected to a DB2 for MVS/ESA Application Server, the DB2 for MVS/ESA Application Server supports database access as follows:

The Application Requester is permitted to access tables stored at the DB2 for MVS/ESA application server. The Application Requester must create a package at the DB2 for MVS/ESA Application Server before the application can be run. The DB2 for MVS/ESA Application Server uses the package to locate the application's SQL statements at execution time.
The Application Requester can inform the DB2 for MVS/ESA Application Server that access must be restricted to read-only activities if the DRDA requester-server connection does not support the two-phase commit process. For example, a DB2 for MVS/ESA V2R3 requester with a CICS front end would inform the DB2 for MVS/ESA application server that updates are not allowed.
The Application Requester can also be permitted to access tables stored at other DB2 for MVS/ESA systems in the network using system-directed access. System-directed access allows the application requester to establish connections to multiple database systems in a single unit of work.

Provide Network Information

For the DB2 for MVS/ESA Application Server to properly process distributed database requests, you must take the following steps:

Define the application server to the local Communications Manager.
Define each potential secondary server destination so the DB2 for MVS/ESA application server can reroute SQL requests to their final destination.
Provide the necessary security.
Provide for data representation.

Defining the Application Server

For the Application Server to receive distributed database requests, it must be defined to the local Communications Manager and have a unique RDB_NAME. You must take the following steps to properly define the Application Server:

Select the LU name and RDB_NAME to be used by the DB2 for MVS/ESA Application Server. The process to record these names in DB2 for MVS/ESA and VTAM is the same process described in "Defining the Local System". The RDB_NAME you choose for DB2 for MVS/ESA must be supplied to all end users and Application Requesters that require connectivity to the Application Server.
Register the NETID.LUNAME value for the DB2 for MVS/ESA Application Server with each Application Requester requiring access, so the Application Requester can route SNA requests to the DB2 for MVS/ESA server. This is true even in cases where the Application Requester is able to perform dynamic network routing, because the Application Requester must know the NETID.LUNAME before dynamic network routing can be used.
Provide the DRDA default TPN (X'07F6C4C2' ) to each Application Requester because DB2 for MVS/ESA automatically uses this value.
Create an entry in the VTAM mode table for each mode name that is requested by an Application Requester. These entries describe the RU sizes, pacing window size, and class of service for each mode name.
Define session limits for the Application Requesters that connect with the DB2 for MVS/ESA Application Server. The VTAM APPL statement defines default session limits for all partner systems. If you want to establish unique defaults for a particular partner, you can use the SYSIBM.SYSLUMODES table of the communications database (CDB).
See "Setting RU Sizes and Pacing" about how to review your VTAM network.

Create entries in the DB2 for MVS/ESA CDB to identify which Application Requesters are allowed to connect to the DB2 for MVS/ESA Application Server. Two basic approaches to define the CDB entries for the Application Requesters in the network are:

You can insert a row in SYSIBM.SYSLUNAMES that provides default values to use for any LU not specifically described in the CDB (the default row contains blanks in the LUNAME column). This approach allows you to define specific attributes for some of the LUs in your network, while establishing defaults for all other LUs.

For example, you can allow the DALLAS system (another DB2 for MVS/ESA system) to send already-verified distributed database requests (LU 6.2 SECURITY=SAME), while requiring database manager systems to send passwords. Furthermore, you might not want to record an entry in the CDB for each database manager system, especially if there is a large number of these systems. Figure 10 shows how the CDB can be used to specify SECURITY=SAME for the DALLAS system, while enforcing SECURITY=PGM for all other requesters.

Figure 10. Establishing Defaults for Application Requester Connections

INSERT INTO SYSIBM.SYSLUNAMES (LUNAME, SYSMODENAME, USERSECURITY, ENCRYPTPSWDS, MODESELECT, USERNAMES) VALUES ('LUDALLAS', ' ', 'A', 'N', 'N', ' '); INSERT INTO SYSIBM.SYSLUNAMES (LUNAME, SYSMODENAME, USERSECURITY, ENCRYPTPSWDS, MODESELECT, USERNAMES) VALUES (' ', ' ', 'C', 'N', 'N', ' ');

You can use the CDB to individually authorize each Application Requester in the network, by setting the CDB in one of these ways:

Do not record a default row in SYSIBM.SYSLUNAMES. When the default row (the row containing a blank LU name) is not present, DB2 for MVS/ESA requires a row in SYSIBM.SYSLUNAMES containing the LU name for each application requester that attempts to connect. If the matching row is not found in the CDB, the Application Requester is denied access.
Record a default row in SYSIBM.SYSLUNAMES that specifies come-from checking is required (USERNAMES column set to 'I' or 'B'). This causes DB2 for MVS/ESA to limit access to Application Requesters and end users identified in the SYSIBM.SYSUSERNAMES table, as described in "Come-From Checking" . You might want to use this approach if your name translation rules require a row with a blank LU name in SYSIBM.SYSLUNAMES, but you do not want DB2 for MVS/ESA to use this row to allow unrestricted access to the DB2 for MVS/ESA Application Server.

In Figure 11, no row contains blanks in the LUNAME column, so DB2 for MVS/ESA denies access to any LU other than LUDALLAS or LUNYC.

Figure 11. Identifying Individual Application Requester Connections

INSERT INTO SYSIBM.SYSLUNAMES (LUNAME, SYSMODENAME, USERSECURITY, ENCRYPTPSWDS, MODESELECT, USERNAMES) VALUES ('LUDALLAS', ' ', 'A', 'N', 'N', ' '); INSERT INTO SYSIBM.SYSLUNAMES (LUNAME, SYSMODENAME, USERSECURITY, ENCRYPTPSWDS, MODESELECT, USERNAMES) VALUES ('LUNYC', ' ', 'A', 'N', 'N', ' ');

Defining Secondary Servers

DB2 for MVS/ESA does not implement a database server as defined in DRDA. Instead, DB2 for MVS/ESA provides secondary servers that provide access to multiple DB2 for MVS/ESA systems in a single unit of work using system-directed access.

SQL differences

The SQL supported by system-directed access differs significantly from DRDA remote unit of work:

The SQL CONNECT statement is not used to establish a connection to a secondary server. Instead, the server is accessed by specifying three-part SQL object names. For example, the following SQL statement is routed to the CHICAGO system-directed access server:
```
SELECT * FROM CHICAGO.USER.TABLE;
```
SQL DDL statements (for example, CREATE) are not allowed.
System-directed access does not support remote bind (for example, BIND PACKAGE), so you do not have to bind your application at the system-directed access server before attempting to execute the application.
The SQL statements sent to a secondary server can be static or dynamic, but all the statements are issued dynamically. This occurs because the secondary server does not have a plan or package containing the application's SQL statements, so it is not possible for the server to choose database access paths in advance.
A single SQL application can access multiple secondary servers simultaneously.
More than one DB2 for MVS/ESA system can be the target of SQL updates for any given commit scope.
An application can use multiple LU 6.2 conversations to a secondary server in a single commit scope. The DB2 for MVS/ESA Application Server usually creates one LU 6.2 conversation for each read-only SQL query. This allows the secondary server to anticipate the SQL application's FETCH requests, and send the answer set before it is actually requested by the application.

SQL object names

When the DB2 for MVS/ESA Application Server receives an SQL request, it examines the SQL object name to determine where the object resides in the network. DB2 for MVS/ESA accepts either one-, two-, or three-part SQL object names, where the name takes one of the following forms:

objectname specifies the name of a DB2 for MVS/ESA table, view, synonym, or alias.

authid.objectname specifies the owner of the object and the object name.

location.authid.objectname specifies the owning system, the owning user, and the name of the object.

If the location name (the first part of the three-part object name) matches the local DB2 for MVS/ESA system's RDB_NAME, the request identifies a local DB2 for MVS/ESA object.

If the location name does not match the local DB2 for MVS/ESA system's RDB_NAME, the DB2 for MVS/ESA Application Server reroutes the request to the system identified by the location name using system-directed access. The target system must be another DB2 for MVS/ESA system, because system-directed access is only supported between DB2 for MVS/ESA systems. System-directed access does not support any remote bind functions, so the application does not have to be bound at the server before executing the application. Figure 12 summarizes the process used by DB2 for MVS/ESA to resolve SQL object names.

Figure 12. DB2 for MVS/ESA SQL Object Name Resolution

Server definition

If the DB2 for MVS/ESA Application Server is to reroute SQL requests, you must define each secondary server in the CDB and VTAM. Most of the definition process is similar to the process described in "Defining the Remote Systems". To connect secondary servers, do the following:

Record the RDB_NAME and LU name values for each server in the CDB and VTAM. The TPN value used by system-directed access is different than the DRDA default value. However, this difference is not important because DB2 for MVS/ESA automatically chooses the correct value.
Define the security requirements in SYSIBM.SYSLUNAMES for each secondary server. This process is described in "Provide Security".
Define the mode name (or names) used between the DB2 for MVS/ESA Application Server and the secondary servers, and place these mode names in the VTAM mode table. The default mode name is IBMDB2LM.
Define the session limits for each secondary server. The process used to establish the session limits is the same as the process described in "Defining the Local System". However, system-directed access can establish multiple conversations for each SQL application. You might need to establish higher session limits for the system-directed access connections than you establish for DRDA connections. See "Connecting Distributed Database Systems" in the DB2 Administration Guide for specific details on how to calculate the number of LU 6.2 sessions required by system-directed access applications.

As the owner of the database resources, the secondary server controls database security for SQL objects residing at the server. However, this responsibility is shared with the DB2 for MVS/ESA Application Server making the request. The server controls access to SQL objects as follows:

The secondary server does not have a copy of the DB2 for MVS/ESA plan, so it depends on the requesting DB2 for MVS/ESA Application Server to verify that the end user is allowed to execute the package at the requesting system (the Application Server).
Static SQL statements are executed dynamically at the secondary server using the privileges granted to the person owning the DB2 for MVS/ESA package at the requesting DB2 for MVS/ESA Application Server.
Dynamic SQL statements are executed using the privileges granted to the end user at the Application Requester.

Provide Security

When an Application Requester routes a distributed database request to the DB2 for MVS/ESA Application Server, the following security considerations can be involved:

Come-from checking
Selection of end user names
Network security parameters
Database manager security
Security enforced by an external security subsystem

Come-From Checking

When the DB2 for MVS/ESA Application Server receives an end user name from the Application Requester, the Application Server can restrict the end user names received from a given Application Requester. This is accomplished through the use of come-from checking. Come-from checking allows the Application Server to specify that a given user ID is only allowed to be used by particular partners. For example, the Application Server can restrict JONES to "come from" DALLAS. If another Application Requester (other than DALLAS) attempts to send the name JONES to the Application Server, the Application Server can reject the request because the name did not come from the correct network location.

DB2 for MVS/ESA implements come-from checking as part of inbound end user name translation, which is described in the next section.

Selecting End User Names

The user ID passed by the Application Requester might not be unique throughout the entire SNA network. The DB2 for MVS/ESA Application Server might need to perform inbound name translation to create unique end user names throughout the SNA network. Similarly, the DB2 for MVS/ESA Application Server might need to perform outbound name translation to provide a unique end user name to the secondary servers involved in the application (see "Provide Security" for information concerning outbound end user name translation).

Inbound name translation is enabled by setting the USERNAMES column of the SYSIBM.SYSLUNAMES table to 'I' (inbound translation) or 'B' (both inbound and outbound translation). When inbound name translation is in effect, DB2 for MVS/ESA translates the user ID sent by the Application Requester and the DB2 for MVS/ESA plan owner's name (if the Application Requester is another DB2 for MVS/ESA system).

If the Application Requester sends both a user ID and a password on the APPC ALLOCATE verb, the user ID and password are validated before the user ID is translated. The PASSWORD column in SYSIBM.SYSUSERNAMES is not used for password validation. Instead, the user ID and password are presented to the external security system (RACF or a RACF-equivalent product) for validation.

When the incoming user ID on the ALLOCATE verb is verified, DB2 for MVS/ESA has authorization exits you can use to provide a list of secondary AUTHIDs and perform additional security checks. See the DB2 Administration Guide, for details.

The inbound name translation process searches for a row in the SYSIBM.SYSUSERNAMES table, which must fit one of the patterns shown in the following precedence list (TYPE.AUTHID.LUNAME):

I.AUTHID.LUNAME--A specific end user from a specific Application Requester
I.AUTHID.blank--A specific end user from any Application Requester
I.blank.LUNAME--Any end user from a specific Application Requester

If no row is found, remote access is denied. If a row is found, remote access is allowed and the end user's name is changed to the value provided in the NEWAUTHID column, with a blank NEWAUTHID value indicating that the name is unchanged. Any DB2 for MVS/ESA resource authorization checks (for example, SQL table privileges) made by DB2 for MVS/ESA are performed on the translated end user names, rather than on the original user names.

When the DB2 for MVS/ESA Application Server receives an end user name from the Application Requester, several objectives can be accomplished by using the DB2 for MVS/ESA inbound name translation capability:

You can change an end user's name to make it unique. For example, the following SQL statements translate the end user name JONES from the NEWYORK application requester (LUNAME LUNYC) to a different name (NYJONES).

INSERT INTO SYSIBM.SYSLUNAMES
     (LUNAME, SYSMODENAME, USERSECURITY, ENCRYPTPSWDS,
              MODESELECT, USERNAMES)
  VALUES ('LUNYC', ' ', 'A', 'N', 'N', 'I');
INSERT INTO SYSIBM.SYSUSERNAMES
     (TYPE, AUTHID, LUNAME, NEWAUTHID, PASSWORD)
  VALUES ('I', 'JONES', 'LUNYC', 'NYJONES', ' ');

You can change the end user's name so that a group of end users are all represented by a single name. For example, you might want to represent all users from the NEWYORK Application Requester (LUNAME LUNYC) with the user name NYUSER. This allows you to grant SQL privileges to the name NYUSER and to control the SQL access given to users from NEWYORK.
```
INSERT INTO SYSIBM.SYSLUNAMES
     (LUNAME, SYSMODENAME, USERSECURITY, ENCRYPTPSWDS,
              MODESELECT, USERNAMES)
  VALUES ('LUNYC', ' ', 'A', 'N', 'N', 'I');
INSERT INTO SYSIBM.SYSUSERNAMES
     (TYPE, AUTHID, LUNAME, NEWAUTHID, PASSWORD)
  VALUES ('I', ' ', 'LUNYC', 'NYUSER', ' ');
```

You can restrict the end user names transmitted by a particular Application Requester. This use of end user name translation accomplishes the come-from check described in "Come-From Checking". For example, the SQL statements that follow allow only SMITH and JONES as end user names from the NEWYORK Application Requester. Any other name is denied access, because it is not listed in the SYSIBM.SYSUSERNAMES table.

INSERT INTO SYSIBM.SYSLUNAMES
     (LUNAME, SYSMODENAME, USERSECURITY, ENCRYPTPSWDS,
              MODESELECT, USERNAMES)
  VALUES ('LUNYC', ' ', 'A', 'N', 'N', 'I');
INSERT INTO SYSIBM.SYSUSERNAMES
     (TYPE, AUTHID, LUNAME, NEWAUTHID, PASSWORD)
  VALUES ('I', 'SMITH', 'LUNYC', ' ', ' ');
INSERT INTO SYSIBM.SYSUSERNAMES
     (TYPE, AUTHID, LUNAME, NEWAUTHID, PASSWORD)
  VALUES ('I', 'JONES', 'LUNYC', ' ', ' ');

You can restrict the Application Requesters allowed to connect to the DB2 for MVS/ESA Application Server. This is yet another feature of come-from checking. The following example accepts any end user name sent by the NEWYORK Application Requester (LUNYC) or the CHICAGO Application Requester (LUCHI). Other Application Requesters are denied access, because the default SYSIBM.SYSLUNAMES row specifies inbound name translation for all inbound requests.
```
INSERT INTO SYSIBM.SYSLUNAMES
     (LUNAME, SYSMODENAME, USERSECURITY, ENCRYPTPSWDS,
              MODESELECT, USERNAMES)
  VALUES (' ', ' ', 'A', 'N', 'N', 'I');
INSERT INTO SYSIBM.SYSUSERNAMES
     (TYPE, AUTHID, LUNAME, NEWAUTHID, PASSWORD)
  VALUES ('I', ' ', 'LUNYC', ' ', ' ');
INSERT INTO SYSIBM.SYSUSERNAMES
     (TYPE, AUTHID, LUNAME, NEWAUTHID, PASSWORD)
  VALUES ('I', ' ', 'LUCHI', ' ', ' ');
```

Provide Network Security

LU 6.2 provides three major network security features:

Session-level security
Conversation-level security
Encryption

"Network Security" discusses how to specify session-level security and encryption with DB2 for MVS/ESA. The DB2 for MVS/ESA Application Server uses session-level security and encryption in exactly the same manner as the DB2 for MVS/ESA Application Requester.

The only remaining network security consideration is SNA conversation-level security. Some aspects of conversation-level security are unique for a DB2 for MVS/ESA Application Server. The DB2 for MVS/ESA Application Server plays two distinct roles in network security:

As a requester to secondary servers, the DB2 for MVS/ESA Application Server is responsible for issuing APPC requests that contain the SNA conversation-level security parameters required by the secondary servers. The DB2 for MVS/ESA Application Server uses the USERNAMES column of the SYSIBM.SYSLUNAMES table and the SYSIBM.SYSUSERNAMES table to define the SNA conversation level security requirements for each secondary server. The details of these definitions are identical to those in "Network Security".
As the server for the Application Requester, the DB2 for MVS/ESA Application Server dictates the SNA conversation level security requirements for the Application Requester. DB2 for MVS/ESA uses the USERSECURITY column of the SYSIBM.SYSLUNAMES table to determine the conversation security required from each Application Requester in the network. The following values are used in the USERSECURITY column:

C
This indicates that DB2 for MVS/ESA requires the Application Requester to send a user ID and password (LU 6.2 SECURITY=PGM) with each distributed database request. If the ENCRYPTPSWDS column in SYSIBM.SYSLUNAMES contains 'Y', DB2 for MVS/ESA assumes the password is already in RACF encrypted format (this is only possible for a DB2 for MVS/ESA Application Requester). If the ENCRYPTPSWDS column does not contain 'Y', DB2 for MVS/ESA expects the password in the standard LU 6.2 format (EBCDIC character representation). In either case, DB2 for MVS/ESA passes the user ID and password values to the security subsystem for validation. You must have a security subsystem that provides APPC user ID and password verification; for example, RACF has the capability to verify APPC user IDs and passwords. If the security subsystem rejects the user ID-password pair, distributed database access is denied.
Any other value
This indicates the Application Requester is allowed to send either an already-verified user ID (LU 6.2 SECURITY=SAME) or a user ID and password (LU 6.2 SECURITY=PGM). If a user ID and password are sent, DB2 for MVS/ESA processes them as described for 'C' above. If the request contains only a user ID, the security subsystem is called to authenticate the user unless the sysusernames table is used to manage inbound user IDs.

If a security violation is discovered, LU 6.2 requires the DB2 for MVS/ESA Application Server to return the SNA security failure sense code ('080F6051'X) to the Application Requester. Because this sense code does not describe the cause of the failure, DB2 for MVS/ESA provides two methods for recording the cause of distributed security violations:

A DSNL030I message is produced, which provides the requester's LUWID and a DB2 reason code describing the failure. DSNL030I also includes the AUTHID, if known, that was sent from the application request that was rejected.
An alert is recorded in the NETVIEW hardware monitor database, which contains the same information provided in the DSNL030I message.

Database Manager Security

As the owner of database resources, the DB2 for MVS/ESA Application Server controls the database security functions for SQL objects residing at the DB2 for MVS/ESA Application Server. Access to DB2 for MVS/ESA-managed objects is controlled by privileges, which are granted to users by the DB2 for MVS/ESA administrator or the owners of individual objects. The two basic classes of objects that the DB2 for MVS/ESA Application Server controls are:

Packages-- Individual end users are authorized to create, replace, and run packages with the DB2 for MVS/ESA GRANT statement. When an end user owns a package, that user can automatically run or replace the package. Other end users must be specifically authorized to run a package at the DB2 for MVS/ESA Application Server with the GRANT statement. USE can be granted to individual end users or to PUBLIC, which allows all end users to run the package.
When an application is bound to DB2 for MVS/ESA, the package contains the SQL statements contained in the application program. These SQL statements are classified as:

Static SQL
Static SQL means that the SQL statement and the SQL objects referenced by the statement are known at the time the application is bound to DB2 for MVS/ESA. The person creating the package must have authority to execute each of the static SQL statements contained in the package.
When end users are granted authority to execute a package, they automatically have authority to execute each of the static SQL statements contained in the package. Thus, end users do not need any DB2 for MVS/ESA table privileges if the package they execute contains only static SQL statements.
Dynamic SQL
Dynamic SQL describes an SQL statement that is not known until the program executes. In other words, the SQL statement is built by the program and dynamically bound to DB2 for MVS/ESA with the SQL PREPARE statement. When an end user executes a dynamic SQL statement, the user must have the table privileges required to execute the SQL statement. Because the SQL statement is not known at the time the plan or package is created, the end user is not automatically given the required authority by the package owner.
SQL objects-- These are tables, views, synonyms, or aliases. DB2 for MVS/ESA users can be granted various levels of authority to create, delete, change, or read individual SQL objects. This authority is required to bind static SQL statements or to execute dynamic SQL statements.

When you create a package, the DISABLE/ENABLE option allows you to control which DB2 for MVS/ESA connection types can run the package. You can use RACF and DB2 for MVS/ESA security exit routines to selectively allow end users to use DDF. You can use RLF to specify limits on processor time for remote binds and dynamic SQL executions.

Consider a DB2 for MVS/ESA package named MYPKG, which is owned by JOE. JOE can allow SAL to execute the package by issuing the DB2 for MVS/ESA GRANT USE statement. When SAL executes the package, the following occurs:

DB2 for MVS/ESA verifies that SAL was given USE authority for the package.
SAL can issue every static SQL statement in the package because JOE had the required SQL object privileges to create the package.
If the package has dynamic SQL statements, SAL must have SQL table privileges of her own. For example, SAL cannot issue SELECT * FROM JOE.TABLE5 unless she is granted read access to JOE.TABLE5.

Security Subsystem

The DB2 for MVS/ESA Application Server use of the security subsystem (RACF or a RACF-equivalent product) is dependent on how you define the inbound name translation function in the SYSIBM.SYSLUNAMES table:

If you specify 'I' or 'B' for the USERNAMES column, inbound name translation is active, and DB2 for MVS/ESA assumes that the DB2 for MVS/ESA administrator is using inbound name translation to perform part of the system security enforcement. The external security subsystem is called only if the Application Requester sends a request containing both user ID and password (SECURITY=PGM). You must have a security subsystem that provides APPC user ID and password verification; for example, RACF has the capability to verify APPC user IDs and passwords.
If the request from the Application Requester contains only a user ID (SECURITY=SAME), the external security system is not called at all, because the inbound name translation rules define which users are allowed to connect to the DB2 for MVS/ESA Application Server.
If you specify something other than 'I' or 'B' for the USERNAMES column, the following security subsystem checks are performed:
- When a distributed database request is received from the Application Requester, DB2 for MVS/ESA calls the external security system to validate the end user's user ID (and password if it is provided).
- The external security system is called to verify that the end user is authorized to connect to the DB2 for MVS/ESA subsystem.
In either case, an authorization exit is driven to provide a list of secondary authorization IDs. For more information, see the DB2 Administration Guide.

Represent Data

You must ensure that your DB2 for MVS/ESA subsystem has the ability to convert from each application requester's CCSID to your DB2 for MVS/ESA subsystem's installation CCSID. Refer to "Represent Data" for more information.

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]