Standards and Procedures

To implement DB2 effectively, you must have a set of standards and procedures that are the blueprint for DB2 development in your organization. Standards are common practices that provide an environment that is consistent, efficient, or understandable (for example, a naming standard for DB2 objects). Procedures are scripts that outline the way a proscribed event should be handled, such as a disaster recovery plan.

DB2 standards and procedures are usually developed in conjunction with one another and stored in a common place. Standards and procedures are usually part of a corporate-wide (or IT-wide) standards and procedures document. They can be stored in printed format as well as being made available online for easy access. Most organizations publish their standards and procedures on their internal, corporate intranet site on the Web.

Several vendors offer "canned" standards and procedures (both hard copy and online). One such example is Quest Software's DB2 Knowledge Xpert, which is a Windows -based technical resource designed to help developers and DBAs research and solve DB2 problems.

This section describes the items that should be addressed by DB2 standards and procedures.

Roles and Responsibilities

Running DB2 requires a large degree of administrative overhead. Not only must the DB2 subsystem be installed and then maintained , but the functionality of DB2 must also be administered. This work constitutes the bulk of the administrative burden .

A matrix of DB2 functions and who will support them is necessary. The matrix can be at the department level or at the job description level. Table 40.1 shows a sample matrix you can use as a template for your organization.

Table 40.1. DB2 Roles and Responsibilities

Role		DA	DBA	PGM	ANL	TS	DSD	SEC	MGT	EU	OPR
Budgeting for DB2			X			X			X	X	X
DB2 Installation			X			X	X	X		X
DB2 System Support						X
DB2 System Security						X		X
System-wide Performance Monitoring			X			X
System-wide Tuning							X
DB2 System Backup and Recovery Procedures		X	X	X	X	X	X	X	X	X	X
Hardware Planning						X	X		X
Capacity Planning			X		X	X				X
Utility Development			X			X
Data Analysis		X	X
DB2 Object Creation			X
DB2 Database Performance Monitoring											X
DB2 Database Performance Tuning		X	X		X		X
DB2 Application Design		X	X		X						X
DB2 Program Coding				X	X
DB2 Program Testing				X	X
Stored Procedure Coding			X	X	X
Stored Procedure Testing			X	X	X
Stored Procedure Support			X	X	X						X
Trigger Coding			X	X	X
Trigger Testing			X	X	X
Trigger Support			X	X	X						X
User -defined Function Coding		X	X	X
User-defined Function Testing		X	X	X
User-defined Function Support		X	X	X						X
DB2 Application Security			X					X
DB2 Application Turnover			X	X	X					X
DB2 Application Performance Monitoring			X		X	X
DB2 Application Database Backup and Recovery			X		X					X	X
DB2 Job Scheduling				X	X					X
DB2 Design Reviews		X	X	X	X	X	X	X	X	X	X
DB2 Tool Selections		X	X	X	X	X	X	X	X
Implementing DDF			X			X		X
Distributing DB2 Data		X	X	X	X	X	X	X
DB2 Data Sharing			X			X	X	X	X		X
QMF Installation						X
QMF Administration			X			X
QMF Tuning			X		X	X				X
DA	Data administrator
DBA	Database administrator
PGM	Programmer
ANL	Analyst
TS	Technical support
DSD	DASD support
SEC	Data security
MGT	Management
EU	End user
OPR	Operations

The matrix in Table 40.1 represents a sampling of roles and responsibilities for the DB2 environment. Each block of the matrix represents a portion of the total responsibility for the given role.

Your organization might have different roles responsible for different areas. Additionally, you might have more categories or a further breakdown of the categories (for example, dividing the Utilities Development line into a single line for each utility).

Each position on the matrix should be accompanied by in-depth text as follows :

• A description of the resources encompassing this combination of role and responsibility.

• A definition of the role in terms of what needs to be performed. This information should include a detailed list of tasks and a reference to the supporting organizational procedures that must be followed to carry out these tasks .

• A definition of the responsibility in terms of who should do the tasks. In addition to primary and secondary contacts for the people performing the task, this description should provide a management contact for the department in charge of the responsibility.

Remember, Table 40.1 is only an example. It is not uncommon for DB2 administrative tasks to be assigned to departments or jobs different from the ones shown in the table. Likewise, your organization might identify additional roles that need to be documented. Each shop should have a document appropriately modified to reflect the needs and organization of the company.

This document will eliminate confusion when DB2 development is initiated. Analysts, programmers, and management will have an organized and agreed-on delineation of tasks and responsibilities before the development and implementation of DB2 applications.

Based on the roles and responsibilities matrix in use at your shop, you might need to augment or change the following procedures. Certain functions may move to a different area, but all the necessary standards are covered.

Data Administration

A thorough treatment of data administration is beyond the scope of this book, but this section lists some basic guidelines. All DB2 applications must be built using the techniques of logical database design. This design involves the creation of a normalized, logical data model that establishes the foundation for any subsequent development. It documents the data requirements for the organization. Each piece of business data is defined and incorporated into the logical data model. All physical DB2 tables should be traceable to the logical data model.

The data administration standards should outline the following:

• Corporate policies dictating that information is to be managed as a vital business resource

• Who is responsible for creating the logical data model

• How the logical data model will be created, stored, and maintained

• Who is responsible for maintaining and administering the logical data model

• The integration of application data models with an enterprise data model

• Data sharing issues (this does not refer to DB2 data sharing but refers to the sharing of data in general)

• How physical databases will be created from the logical data model

• How denormalization decisions will be documented

• The tools used by the data administrator (modeling tools, data dictionaries, repositories, and so on)

• Rules for data creation, data ownership, and data stewardship

• Metadata management policy

• The communication needed between data administration and database administration to ensure the implementation of an effective DB2 application

Database Administration Guide

A database administration guide is essential to ensure the ongoing success of the DBA function. The guide serves as a cookbook of approaches to be used in the following circumstances:

• Converting a logical model to a physical implementation

• Choosing physical DB2 parameters when creating (or generating) DDL

• DB2 utility implementation procedures and techniques

• DB2 backup and recovery techniques and guidelines ”including DB2 disaster recovery procedures

• DB2 application monitoring schedules

• DB2 application and database performance tuning guidelines

This document, although geared primarily for DBA staff, is useful for the programming staff as well. If the program developers understand the role of the DBA and the tasks that must be performed, more effective communication can be established between DBA and application development, thereby increasing the chances of achieving an effective and efficient DB2 application system.

System Administration Guide

The DB2 system administrator is considered to be at a higher level than the database administrator. It is not unusual, though, for a DBA to be the system administrator also. A system administrator guide is needed for many of the same reasons that a DBA guide is required. It should consist of the following items:

• DB2 installation and testing procedures

• Procedures to follow for applying fixes to DB2 (APARs)

• A checklist of departments to notify for impending changes

• Interface considerations (CICS, IMS/TM, TSO, CAF, RRSAF, DDF, and other installation-specific interfaces)

• A DB2 system monitoring schedule

• DB2 system tuning guidelines

• DB2 data sharing policy and implementation

• Procedures for using Workload Manager with DB2

• Procedures for working with z/OS, CICS, WebSphere, and IMS system administrators for issues that span DB2 and other, related system software

• System DASD considerations

Application Development Guide

The development of DB2 applications differs from typical program development. Providing an application development guide specifically for DB2 programmers is therefore essential. It can operate as an adjunct to the standard application development procedures for your organization. This guide should include the following topics:

• An introduction to DB2 programming techniques

• Shop SQL coding standards

• SQL tips and techniques

• DB2 program preparation procedures

• Interpretations of SQLCODE s, SQLSTATE s and DB2 error codes

• References to other useful programming materials for teleprocessing monitors (CICS and IMS/TM), programming languages (such as COBOL, Java, and PL/I), interfaces (ODBC), and general shop coding standards

• The procedure for filling out DB2 forms (if any) for database design, database implementation, program review, database migration, and production application turnover

DB2 Security Guide

The DBA unit often applies and administers DB2 security. However, at some shops , the corporate data security unit handles DB2 security. You must provide a resource outlining the necessary standards and procedures for administering DB2 security. It should consist of the following:

• A checklist of the proper authorization to grant for specific situations. For example, if a plan is being migrated to production, it should list the security that must be granted before the plan can be executed.

• A procedure for implementing site-specific security. It must define which tools or interfaces (for example, secondary authorization IDs) are being used and how they are supported.

• An authoritative signature list of who can approve authorization requests .

• Procedures for any DB2 security request forms.

• Procedures for notifying the requester that security has been granted.

• Procedures for removing security from retiring , relocating, and terminated employees .

SQL Performance Guide

The SQL performance guide can be a component of the application development guide, but it should also exist independently. This document should contain tips and tricks for efficient SQL coding. It is useful not only for application programmers but also for all users of DB2 who regularly code SQL.

QMF Guide

If QMF (or another query tool) is in use at your site, a QMF guide must be available. It should contain information from the simple to the complex so that all levels of QMF users will find it useful. This guide should cover the following topics, in increasing order of complexity:

• What QMF is

• Who is permitted to use QMF

• When QMF can be used (such as hours of operation and production windows)

• How to request QMF use

• How to call up a QMF session

• A basic how-to guide for QMF features

• QMF limitations

• References to further documentation (for example, CBT and IBM manuals)

Vendor Tools Guide(s)

It is a good idea to have user guides available for each vendor tool in use at your company. Many of the vendors supply a user's guide that can be used "as is," or augmented with site-specific details such as who is authorized to use the tool, when it can be used, and how it was installed.

Naming Conventions

All DB2 objects should follow a strict naming convention. You learned some basic guidelines for DB2 naming conventions in Chapter 5, "Data Definition Guidelines." This section details the rules to follow in naming a DB2 object.

Make names as English-like as possible. In other words, do not encode DB2 object names , and avoid abbreviations unless the name would be too long otherwise .

Do not needlessly restrict DB2 object names to a limited subset of characters or a smaller size than DB2 provides. For example, do not forbid an underscore in table names, and do not restrict DB2 table names to eight characters or fewer (DB2 allows as many as 128 characters).

Another rule in naming objects is to standardize abbreviations. Use the abbreviations only when the English text is too long.

In most cases, provide a way to differentiate types of DB2 objects. For example, start indexes with I , tablespaces with S , and databases with D . In two cases, however, this approach is inappropriate. You should not constrain tables in this manner; you need to provide as descriptive a name as possible. The second exception is that views, aliases, and synonyms should follow the same naming convention as tables. In this way, DB2 objects that operate like tables can be defined similarly. The type of object can always be determined by querying the DB2 Catalog using the queries presented in Chapter 26, "DB2 Object Monitoring Using the DB2 Catalog and RTS."

Provide naming conventions for the following items:

Databases	STOGROUP s
Table Spaces	Check constraints
Tables	Referential constraints
Global Temporary Tables	Plans
Auxiliary Tables	Packages
Indexes	Collections
Views	Versions
Aliases	DBRMs
Synonyms	DBRM Libraries
DCLGEN Members	Transactions
DCLGEN Libraries	Programs
DB2 COPYLIB Members	DB2 Load Libraries
DB2 Subsystems	DB2 Address Spaces
Application DB2 data sets	RCTs
System DB2 data sets	Data sets for DB2 Tools
Locations	Creators
Utility ID	DB2 data sets (tools ”general for DB2 subsystem; specific for each tool)
DSNZPARM	RACF groups
DB2 group name	IRLM group name
Location name	Group attach name
DB2 member name	Workfile DB name
User-defined functions	User-defined distinct types
Command prefixes	Triggers

Migration and Turnover Procedures

The minimum number of environments for supporting DB2 applications is two: test and production. Most shops, however, have multiple environments. For example, a shop could have the following DB2 environments to support different phases of the development life cycle:

Unit testing

Integration testing

User acceptance testing

Quality assurance

Education

Having multiple environments requires a strict procedure for migrating DB2 objects and moving DB2 programs and plans from environment to environment. Each shop must have guidelines specific to its environment because all sites do not implement these different environments in the same way. For example, both test and production DB2 could be supported using either a single DB2 subsystem or two DB2 subsystems. (Two are recommended to increase efficiency and turnaround time, but having two is a luxury some smaller shops cannot afford.)

Dual versions of these procedures should exist to describe what is entailed from the point of view of both the requester and the person implementing the request. For the requester, the procedures should include what will be migrated, why and when it will be migrated, who is requesting the migration, and the authorization for the migration. For the person implementing the request, the procedures should include who is responsible for which portions of the migration and a description of the methods used to migrate.

Design Review Guidelines

All DB2 applications, regardless of their size, should participate in a design review both before and after they are implemented. Design reviews are critical for ensuring that an application is properly designed to achieve its purpose.

Design reviews can take many forms. Some of the areas that can be addressed by a design review include the following:

• A validation of the purpose of the application

• An assessment of the logical and physical data models

• A review and analysis of DB2 physical parameters

• A prediction of SQL performance

Before discussing the different types of DB2 design reviews, I must first outline who must participate to ensure a successful review of all elements of the application. The following personnel should engage in the design review process:

AA	Representatives from other applications affected by the application being reviewed (because of the need to interface with the new application, shared data requirements, scheduling needs, and so on)
AD	Application development personnel assigned to this development effort
DA	Data administration representatives
DBA	Database administration representatives
EU	End-user representatives
EUM	End-user management
IC	Information center representatives
MM	MIS management for the new application and all affected applications
OLS	Online support representatives (CICS or IMS/TM unit, or Web support if the application is for the Internet)
OS	Operational support management
TS	Technical support and systems programming representatives

Each of these participants does not need to take part in every facet of the design review. Holding more than one design review is best, with each one focusing on an aspect of the design. The scope of each design review should be determined before the review is scheduled so that only the appropriate participants are invited.

You can break down the design review into seven distinct phases, which are described in the following sections.

Phase 1

The first phase of the design review process is the Conceptual Design Review (CDR). This review validates the concept of the application. This review involves a presentation of the statement of purpose as well as an overview of the desired functionality.

A CDR should be conducted as early as possible to determine the feasibility of a project. Failure to conduct a CDR can result in projects that provide duplicate or inadequate functionality ”projects that are canceled because of lack of funds, staffing, planning, user participation, or management interest; or projects over budget.

Participants should include AA, AD, DA, DBA, EU, EUM, and MM.

Phase 2

Phase 2 of the design review process is the Logical Design Review (LDR). This phase should be conducted when the first cut of the logical data model has been completed. A thorough review of all data elements, descriptions, and relationships should occur during the LDR. The LDR should scrutinize the following areas:

• Is the model in (at least) third normal form?

• Are all data elements (entities and attributes) required for this application identified?

• Are the data elements documented accurately?

• Are all relationships defined properly?

Failure to hold an LDR can result in a failure to identify all required pieces of data, a lack of documentation, and a database that is poorly designed and difficult to maintain. This failure results in the development of an application that is difficult to maintain. If further data modeling occurs after the logical design review is held, further LDRs can be scheduled as the project progresses.

Participants should include AA, AD, DA, DBA, EU, EUM, and IC.

Phase 3

The third phase of the design review process is the Physical Design Review (PDR). Most DB2 developers associate this component with the design review process. In this phase, the database is reviewed in detail to ensure that all the proper design choices were made. In addition, the DA and DBA should ensure that the logical model was translated properly to the physical model, with all denormalization decisions documented.

In addition, the overall operating environment for the application should be described and verified . The choice of a teleprocessing monitor and a description of the online environment and any batch processes should be provided. Data sharing and distributed data requirements should be addressed during this phase.

At this stage, the SQL that will be used for this application might be unavailable. General descriptions of the processes, however, should be available. From the process descriptions, a first-cut denormalization effort (if required) should be attempted or verified.

Because the PDR phase requires much in-depth attention, it can be further divided. The PDR, or pieces of it, can be repeated before implementation if significant changes occur to the physical design of the database or application.

Participants should include AA, AD, DA, DBA, EU, EUM, IC, MM, OLS, OS, and TS.

Phase 4

Phase 4 is the Organization Design Review (ODR). It is smaller in scope ”but no less critical ”than the Physical Design Review. This review addresses the enterprise-wide concerns of the organization with respect to the application being reviewed. Some common review points follow:

• How does this system interact with other systems in the organization?

• Has the logical data model for this application been integrated with the enterprise data model (if one exists)?

• To what extent can this application share the data of other applications? To what extent can other applications share this application's data?

• Does the planned application conform to shop guidelines and restrictions for the environment in which it will run? For example, will a new batch program fit within the required batch window? Or, will a new Web program be designed to conform to the organization's design requirements?

• How will this application integrate with the current production environment in terms of DB2 resources required, the batch window, the online response time, and availability?

Participants should include AA, AD, DA, DBA, EU, EUM, IC, MM, OLS, OS, and TS.

Phase 5

Phase 5, the SQL Design Review (SDR), must occur for each SQL statement before production turnover. This phase should consist of the following analyses.

An EXPLAIN should be run for each SQL statement using production statistics. The PLAN_TABLE s should then be analyzed to determine whether the most efficient access paths have been chosen , whether the runtime estimates are within the agreed service level, and to verify function resolution when UDFs are used. If a plan analysis tool is available, the output from it should be analyzed as well.

Every DB2 program should be reviewed to ensure that inefficient host language constructs were not used. In addition, efficient SQL implemented inefficiently in loops should be analyzed for its appropriateness. To accomplish this, you will need knowledge of the application language being used, whether it is COBOL, Java, or some other language.

All dynamic SQL should be reviewed whether it is embedded in an application program or earmarked for QMF. The review should include multiple EXPLAIN s for various combinations of host variables . Be sure to EXPLAIN combinations of host variable values so that you test both values that are not one of the 10 most frequently occurring values and values that are one of the 10 most frequently occurring values. These values can be determined by running the column occurrence query as presented in Chapter 26.

Different access paths can be chosen for the same query based on differing column value distributions. This needs to be taken into account to determine how best to implement RUNSTATS for tables accessed dynamically.

Suggestions for performance improvements should be made and tested before implementation to determine their effect. If better performance is achieved, the SQL should be modified.

Participants should include AD, DBA, EU, and IC.

Phase 6

Phase 6 is the Pre-Implementation Design Review (Pre-IDR). This phase is simply a review of the system components before implementation. Loose ends from the preceding five phases should be taken care of, and a final, quick review of each application component should be performed.

Participants should include AA, AD, DA, DBA, EU, EUM, IC, MM, OLS, OS, and TS.

Phase 7

The last design review phase is phase 7, the Post-Implementation Design Review (Post-IDR). This phase is necessary to determine whether the application is meeting its performance objectives and functionality objectives. If any objective is not being met, a plan for addressing the deficiency must be proposed and acted on. Multiple Post-IDR phases can occur.

Participants should include AA, AD, DA, DBA, EU, EUM, IC, MM, OLS, OS, and TS.