GUIDELINE
08/15/2002
Review Date
08/2002
1.0 SUBJECT: DB2 Online Production Environment
2.0 DISTRIBUTION: All Current Users
3.0 FROM: Bruce Roberts, Director of DISC
4.0 PURPOSE: To provide direction in the establishment of the DB2 online, production environment.
5.0 BACKGROUND: There is an upper limit to the number of transactions that a processor can manage under optimum conditions. This upper limit is known as the maximum transaction arrival rate (MTAR). If less than 100% of the processor is available for DB2 transactions, then the MTAR will be reduced a proportional amount. The MTAR for DB2 will drop substantially if the online, production environment described in this guideline is not created and maintained.
5.1 If an application's estimated peak period arrival rate for DB2 transactions approaches the available resource (percent of MTAR available), then the decision to utilize DB2 should be reconsidered.
6.0 GUIDELINE: In order to achieve acceptable response time in a DB2 online production environment, it is advisable to separate short-running, normal online transactions from longer-running ad hoc queries, relational joins, and utilities. In the same manner, activities with predictable resource consumption need to be separated from those that are unpredictable. This can be accomplished by maintaining specific processing times, or windows, for each type of activity.
6.1 DB2 data access should be allowed only through standard TP monitors.
6.2 All SQL commands used for online, production processing should be static, not dynamic.
6.3 Online transactions should be designed so that large application functions are broken into small subsets. The resource intensive commands, such as SORT, SUM, and AVG, should not be executed online.
6.4 Views of data for online, production processing should be limited to physical subsets of a table. (Refer to Guideline 3612 for information concerning views.)
6.5 Modifications to databases, such as adding a column or an index, should not be allowed during the peak period for transaction processing.
6.6 Neither DB2 utilities nor DXT processing should be run during the peak transaction processing period.
7.0 HISTORY: This Guideline was originally issued as 3611.00, 2/10/1992. Re-issued 3611.01, 8/15/2002
8.0 CONTACT: Deputy Director, Bureau of Information Systems