Tips: Designing distributed relational database applications

 

Distributed relational database applications have different requirements from applications developed solely for use on a local database.

To properly plan for these differences, design your applications with the following considerations in mind:

• Take advantage of the distributed unit of work (DUW) function where appropriate.

• Code programs using common interfaces.

• Consider dividing a complex application into smaller parts and placing each piece of the application in the location best suited to process it. One good way to distribute processing in an application is to make use of the SQL CALL statement to run a stored procedure at a remote location where the data to be processed resides. The stored procedure is not limited to SQL operations when it runs on a DB2 Universal Database™ for iSeries™ application server; it can use integrated database input/output or perform other types of processing.

• Investigate how the initial database applications will be prepared, tested, and used.

• Take advantage, when possible, of SQL set-processing capabilities. This will minimize communication with the application servers. For example, update multiple rows with one SQL statement whenever you can.

• Be aware that database updates within a unit of work must be done at a single site if the remote unit of work (RUW) connection method is used when the programs are prepared, or if the other nodes in the distributed application do not support DUW.

• Keep in mind that the DUW connection method restricts you from directing a single statement to more than one relational database.

• Performance is affected by the choice of connection management methods. Use of the RUW connection management method might be preferable if you do not have the need to switch back and forth among different remote relational databases. This is because more overhead is associated with the two-phase commit protocols used with DUW connection management.

  However, if you have to switch frequently among multiple remote database management systems, use DUW connection management. When running with DUW connection management, communication conversations to one database management system do not have to be ended when you switch the connection to another database management system. In the like environment, this is not as big a factor as in the unlike environment, since conversations in the like environment can be kept active by use of the default DDMCNV(*KEEP) job definition attribute. Even in the like environment, however, a performance advantage can be gained by using DUW to avoid the cost of closing cursors and sending the communication flow to establish a new connection.

• The connection management method determines the semantics of the CONNECT statement. With the RUW connection management method, the CONNECT statement ends any existing connections before establishing a new connection to the relational database. With the DUW connection management method, the CONNECT statement does not end existing connections.

 

Parent topic:

Designing the application, network, and data for a distributed relational database