DB2 Server for VSE. Operations Guide / Handbooks (2004-2007)

*..CREATE TABLE "GEORGE"

. "SYSCAT

0D2008

*ALOG" ("TNAME" VARCHAR( 18) , "CREA

0D2028

*TOR" CHAR( 8) , "TABLETYPE" CHAR ( *

0D2048

*1),"NCOLS" SMALLINT , "REMARKS" VA

0D2068

*RCHAR( 254) , "DBSPACENO" SMALLINT

0D2088

*,"DBSPACENAME" VARCHAR( 18) , "TAB

0D20A8

*ID" SMALLINT , "CLUSTERTYPE" CHAR(

0D20C8

* 1) , "CLUSTERROW" INTEGER , "AVGRO *

0D20E8

*WLEN" SMALLINT,"ROWCOUNT" INTEG

0D2108

*ER

, "NPAGES" INTEGER

, "PCTPAGES" *

0D2128

*SMALLINT,"NOVERFLOW" INTEGER , "L

0D2148

*FDTABID" SMALLINT,

LFDLINK" SMAL

0D2168

*LINT, "LFDDBSPACE" SMALLINT ,"TLAB

0D2188

*EL" VARCHAR ( 30) , "PARENTS" SMALL*

0D21A8

*INT,"DEPENDENTS" SMALLINT,"INACT

0D21C8

*IVE" SMALLINT ) IN "PUBLIC"."BAC

0D21E8

*KSTORE"

0D2208

Figure 242. Example of Table Content for a DB2 Server for VSE & VM Error on a Dynamic

Statement During the Reload Process

In any case, refer to the DB2 Server for VM Messages and Codes manual to check the

meaning of the SQLCODE, or contact your database administrator or system

administrator for assistance.

Note: The dump that is produced will help IBM technical support investigate the

problem, if you are not able to solve it yourself.

270

Data Restore Guide

Notices

IBM may not offer the products, services, or features discussed in this document in

all countries. Consult your local IBM representative for information on the

products and services currently available in your area. Any reference to an IBM

product, program, or service is not intended to state or imply that only that IBM

product, program, or service may be used. Any functionally equivalent product,

program, or service that does not infringe any IBM intellectual property right may

be used instead. However, it is the user’s responsibility to evaluate and verify the

operation of any non-IBM product, program, or service.

IBM may have patents or pending patent applications covering subject matter

described in this document. The furnishing of this document does not give you

any license to these patents. You can send license inquiries, in writing, to:

IBM Director of Licensing

IBM Corporation

North Castle Drive

Armonk, NY 10594-1785

U.S.A.

For license inquiries regarding double-byte (DBCS) information, contact the IBM

Intellectual Property Department in your country or send inquiries, in writing, to:

IBM World Trade Asia Corporation

Licensing

2-31 Roppongi 3-chome, Minato-ku

Tokyo 106, Japan

The following paragraph does not apply to the United Kingdom or any other

country where such provisions are inconsistent with local law:

INTERNATIONAL BUSINESS MACHINES CORPORATION PROVIDES THIS

PUBLICATION “AS IS” WITHOUT WARRANTY OF ANY KIND, EITHER

EXPRESS OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED

WARRANTIES OF NON-INFRINGEMENT, MERCHANTABILITY OR FITNESS

FOR A PARTICULAR PURPOSE. Some states do not allow disclaimer of express or

implied warranties in certain transactions, therefore, this statement may not apply

to you.

This information could include technical inaccuracies or typographical errors.

Changes are periodically made to the information herein; these changes will be

incorporated in new editions of the publication. IBM may make improvements

and/or changes in the product(s) and/or the program(s) described in this

publication at any time without notice.

Any references in this information to non-IBM Web sites are provided for

convenience only and do not in any manner serve as an endorsement of those Web

sites. The materials at those Web sites are not part of the materials for this IBM

product and use of those Web sites is at your own risk.

IBM may use or distribute any of the information you supply in any way it

believes appropriate without incurring any obligation to you.

271

Licensees of this program who wish to have information about it for the purpose

of enabling: (i) the exchange of information between independently created

programs and other programs (including this one) and (ii) the mutual use of the

information which has been exchanged, should contact:

IBM Corporation

Mail Station P300

522 South Road

Poughkeepsie, NY 12601-5400

U.S.A

Such information may be available, subject to appropriate terms and conditions,

including in some cases, payment of a fee.

The licensed program described in this information and all licensed material

available for it are provided by IBM under terms of the IBM Customer Agreement,

IBM International Program License Agreement, or any equivalent agreement

between us.

Any performance data contained herein was determined in a controlled

environment. Therefore, the results obtained in other operating environments may

vary significantly. Some measurements may have been made on development-level

systems and there is no guarantee that these measurements will be the same on

generally available systems. Furthermore, some measurement may have been

estimated through extrapolation. Actual results may vary. Users of this document

should verify the applicable data for their specific environment.

Information concerning non-IBM products was obtained from the suppliers of

those products, their published announcements, or other publicly available sources.

IBM has not tested those products and cannot confirm the accuracy of

performance, compatibility, or any other claims related to non-IBM products.

Questions on the capabilities of non-IBM products should be addressed to the

suppliers of those products.

All statements regarding IBM’s future direction or intent are subject to change or

withdrawal without notice, and represent goals and objectives only.

This information may contain examples of data and reports used in daily business

operations. To illustrate them as completely as possible, the examples include the

names of individuals, companies, brands, and products. All of these names are

fictitious and any similarity to the names and addresses used by an actual business

enterprise is entirely coincidental.

This information may contain sample application programs in source language,

which illustrates programming techniques on various operating platforms. You

may copy, modify, and distribute these sample programs in any form without

payment to IBM, for the purposes of developing, using, marketing, or distributing

application programs conforming to the application programming interface for the

operating platform for which the sample programs are written. These examples

have not been thoroughly tested under all conditions. IBM, therefore, cannot

guarantee or imply reliability, serviceability, or function of these programs.

272

Data Restore Guide

Contents

About This Manual

vii

Restrictions on the ACQUIRE DBSPACE

Some Terminology

vii

Statement

Components of the Relational Database

Creating Tables

Management System

vii

Controlling Who Creates Tables

Organization

How to Create Tables

Prerequisite IBM Publications

Naming Tables

Highlighting Conventions

Choosing Columns

Specifying Columns

Specifying Data Types

Syntax Notation Conventions

xiii

Specifying a PRIMARY KEY

Specifying a UNIQUE Constraint

SQL Reserved Words

xvii

Considerations for Referential Integrity when

Creating Tables

Summary of Changes

xix

Placing Tables in Dbspaces

Summary of Changes for DB2 Version 7 Release 5

xix

Creating Views

Enhancements, New Functions, and New

Reasons for Using Views

Capabilities

. xix

Creating a View on a Table

Creating a View from Several Tables

Chapter 1. Designing a Database

Things You Cannot Do with a View

Materializing a View

Sample Tables

Entities, Properties, and Occurrences

Creating Indexes

Step 1: Select the Data to Record in the Database . . 1

Index Key

UNIQUE Indexes

Step 2: Define Tables for Each Type of Relationship

The PCTFREE Clause

One-to-One Relationships

Clustering Rows of a Table on an Index

One-to-Many and Many-to-One Relationships . . 3

Some Things to Remember When Defining Keys

Many-to-Many Relationships

General Performance Considerations on the Use

Step 3: Provide Column Definitions for Tables . . . 4

of Indexes

Step 4: Identify One or More Columns as a Primary

Migration Considerations for Indexes

Key

Using the Catalog in Database Design

Step 5: Ensure that Equal Values Represent the Same

Retrieving Catalog Information about a Table .

Entity

Retrieving Catalog Information about Columns

Step 6: Plan for Referential Integrity

Retrieving Catalog Information about Indexes .

Elements of Referential Integrity

Retrieving Catalog Information about Views .

DELETE, INSERT, and UPDATE Considerations

Retrieving Catalog Information about

Step 7: Normalize Your Tables

Authorization

First Normal Form

The COMMENT ON Statement

Second Normal Form

Third Normal Form

Fourth Normal Form

Chapter 3. Maintaining Your Database

Step 8: Considerations for Distributed Data

Maintaining Tables

Definitions

Loading Data into Tables

Application Programming

Copying Tables

System Operations

Moving Tables from One Dbspace to Another .

Distributing Existing Data

Merging Data from Multiple Tables

Altering the Design of a Table

Chapter 2. Implementing Your Design

Altering Referential and Unique Constraints .

Enforcing Referential Constraints

Storage Concepts

Moving Data from One Application Server to

How Information is Stored in Dbspaces

Another

Database Generation

Removing Tables

Defining Dbspaces

Maintaining Dbspaces

Identifying Dbspace Requirements

Altering the Design of a Dbspace

Adding Dbspaces to the Database

Reorganizing a Dbspace to Free Storage Pool

Acquiring Dbspaces

Pages

Retrieving Information about Dbspace

Releasing Empty Pages

Parameters

iii

Removing Dbspaces

Recovery from Application Failures

123

VSAM Restrictions

Application Program Recovery in VM

126

Reorganizing Indexes on the Catalog Tables . . . 77

Dropping the DB2 Server for VM Resource

Moving Your Database

Adapter Code

126

Batch and VSE/ICCF Application Recovery .

126

Chapter 4. Supporting Your Users . .

Online Application Recovery

127

ISQL Sessions

128

Adding a New User

DBS Utility Processing

128

Setting Up New ISQL Users

Preprocessor

129

Authorizing Access

Recovery from User Logic Errors

129

Specifying a Default Application Server in VM

Dynamic Recovery from User Errors

130

Loading Initial Tables

Selective Recovery from User Data Errors . .

133

Training New Users

Database Recovery from User Logic Errors .

135

Removing Users from an Application Server . . . 85

Example

Chapter 7. Customizing the HELP Text

Chapter 5. Providing Security

and Messages Text

139

Authorities

The SYSLANGUAGE Table

139

Types of Authorities

The SYSTEXT1 and SYSTEXT2 Tables

141

Granting Authorities

Adding Topics to HELP Text Tables

143

Revoking Authorities

Adding a HELP Topic to the HELP Text

Privileges

Supplied by IBM

143

Privileges of Ownership

Creating Your Own HELP Text Tables

144

Granting Privileges to Other Users

Making the HELPTEXT Dbspace Larger

145

Revoking Privileges

Moving the HELP Text to Another Dbspace . .

147

Monitoring Privileges

Printing the HELP Text Using the DBS Utility .

147

Privileges on Application Programs

Printing the HELP Text Using ISQL

148

Connecting to an Application Server in VM . . . 97

Establishing a Default Application Server . . . 97

Chapter 8. Application Design

Connecting to the Application Server Implicitly

Considerations

149

Connecting to the Application Server Explicitly

Application Implementation Capabilities

149

Connecting to an Application Server in VSE . .

100

Batch/Interactive Capabilities

149

Establishing a Default Application Server . .

101

Online (CICS) Transaction Processing

Connecting to the Application Server in

Capabilities

150

Different VSE Environments

101

Query Capabilities

151

User IDs for Remote CICS/VSE Transactions

104

Report Writing Capabilities

156

Connecting to an Application Server in Special

Programmed Application Capabilities

158

Circumstances

105

EXECs that Use DB2 Server for VM Facilities

158

Resolving Remote Server Name to Target Database

Application Development Capabilities

162

(CICS)

106

Application Database Considerations

165

Resolving Remote Server Name to Target Database

Database Support for Application Development

165

(VSE Batch)

107

Database Support for Query/Report Writing

166

Restricting Access Using Views

108

Application Implementation Considerations . . .

169

Example

108

VSE Batch/Interactive Application

Changing User Passwords

109

Considerations

169

Example

109

Online CICS/VSE Transaction Considerations

171

Securing the Database Catalog Tables

109

Application Development Considerations

172

Example 1

110

Loading Data into Test Dbspaces

172

Example 2

110

Use of Synonyms in Application Development

173

Example 3

110

Testing SQL Statements

173

Security Auditing

110

Checking Application Code

174

Auditing Security Using the Catalog Tables . . 111

Query/Report Writing Considerations

175

Auditing Security Using Tracing

111

User Identifiers (Userids) for Query Users . . .

175

Application Independence with CMS Work Units

175

Chapter 6. Recovering from Failures

121

Application Maintenance Considerations

176

Overview of Recovery Concepts

121

Data Administration Support

176

Logical Units of Work

121

Data Independence Support

176

CMS Work Units

122

Arithmetic Operations

179

Atomic Operations

122

Data Access Changes

188

Dynamic Application Backout

122

Hypothetical Change Support

191

Restart Processing

123

iv Database Administration

Chapter 9. DB2 Server for VM

Calculating Internal Dbspace Size Requirements

240

Calculating Total Internal Dbspace and DASD

Database Configurations

193

Needs

242

DB2 Server for VM Concepts

193

Operating Modes for the Database Machine .

194

Example Configurations

194

Appendix B. CMS EXECs

243

One Database Machine with One Database .

194

SQLINIT EXEC

243

One Database Machine with Two Databases .

195

Initializing a User Machine

243

Several Database Machines with Many

SQLGLOB EXEC

252

Databases

196

SQLCIREO EXEC

257

Multiple Database Machines on Different

SQLRELEP EXEC

259

Processors

197

SQLDBID EXEC

261

Accessing a Database from a Processor that

SQLRMEND EXEC

261

Does Not Have One

199

Example

263

Performance Considerations with Multiple

ARISDBHD EXEC

264

Databases

200

ARISDBLD EXEC

265

VSE Guest Sharing (On VM/ESA Systems Only)

201

SQLLEVEL EXEC

266

Chapter 10. Usage Environments in

Appendix C. Querying the Status of

VSE

203

an Application (VM Only)

267

Batch/Interactive Application Processing

203

Example

268

Online (CICS) Transaction Processing

204

Application Development

206

Appendix D. Maximums

271

Query/Report Writing

207

ISQL Maximums

271

Chapter 11. Stored Procedures

209

Appendix E. SQLGLOB Parameters

Stored Procedure Concepts

209

(VSE Only)

273

Stored Procedure Servers

209

Transactions for Updating SQLGLOB Parameters

275

The Stored Procedure Server

209

DSQG - Update global SQLGLOB Parm

The Stored Procedure Handler

210

Transaction

275

Stored Procedure Server Groups

210

DSQU - Update user SQLGLOB Parm

Setting up a Stored Procedure Server

210

Transaction

276

Managing Stored Procedure Servers

213

DSQQ - Query SQLGLOB Parm Transaction .

277

Stored Procedure Server Allocation

213

DSQD - Delete user SQLGLOB Parm

States of a Stored Procedure Server

216

Transaction

277

Altering or Dropping a Stored Procedure Server

Batch Program to Update/Query the SQLGLOB

Definition

217

File

278

Stored Procedures

217

Using Online and Batch Resource Adapter Tracing

279

Preparing a Stored Procedure to Run

217

Online Trace File JCL

280

Dropping or Altering a Stored Procedure . . . 218

Batch Trace File JCL

280

Setting Up Schema Stored Procedures for

Formatting the Online or Batch Trace File . .

280

CLI/ODBC/JDBC/OLE DB Client Applications . 218

Initialization Parameters Affecting Stored

Appendix F. Preparing the Schema

Procedure Execution

218

PTIMEOUT Parameter

218

Stored Procedures for

PROCMXAB Parameter

219

CLI/ODBC/JDBC/OLE DB Client

Summary of Environment Interactions

219

Applications

281

Setting up Schema Stored Procedures for

Appendix A. Estimating Your Dbspace

CLI/ODBC/JDBC/OLE DB Client Applications .

281

Requirements

223

Estimating Dbspace Size

223

Notices

285

General Guidelines

223

Trademarks

287

Estimating Storage for a Table

224

Estimating the Number of Header Pages . . . 226

Bibliography

289

Estimating the Number of Data Pages

227

Estimating the Number of Index Pages

235

Index

293

Estimating Internal Dbspace Size and DASD Needs

for Sort Operations

238

Contacting IBM

303

When Do We Sort?

239

Internal Dbspace Characteristics

239

Product information

303

Contents v

About This Manual

This book describes the tasks for planning and administering an application server

in the following environments:

1. Virtual Storage Extended (VSE/ESA), 2.3.1 or above.

2. Virtual Machine/Enterprise Systems Architecture (VM/ESA), 2.3.0 or above

3. VM/ESA with Virtual Storage Extended (VSE) running as a guest under VM

and accessing a VM application server.

The planning and administration of a DB2 Server for VSE & VM application server

consists of designing, implementing, securing, and maintaining a database. To

accomplish these tasks, you must know about:

v Database design

v Table design

v Index creation

v Structured Query Language (SQL)

v Relational concepts.

The first three areas are described in this book. For a description of the other

topics, refer to the DB2 Server for VSE & VM SQL Reference manual, SC09-2989, and

the DB2 Server for VSE & VM Database Services Utility manual, SC09-2983.

Note: The DB2 Server for VSE & VM Performance Tuning Handbook, GC09-2987,

contains information on database design techniques that you must know

before you start to design your database. This information was previously in

this manual under the chapter describing advanced database design and

performance techniques.

Some Terminology

Throughout this book, the Customer Information Control System (CICS) refers to

CICS/VSE Version 2 Release 3 or CICS Transaction Server Version 1 Release 1 or

later for online support and for ISQL. DB2 Server for VSE & VM refers to

DATABASE 2 Server for IBM VSE & VM Systems Version 7 Release 5, unless

otherwise noted.

Components of the Relational Database Management System

Figure 1 on page viii depicts a typical configuration with one database and two

users.

Figure 2 on page ix depicts a typical configuration with one database, one batch

partition user, and a CICS^® partition with several interactive users.

vii

Online Resource Adapter

Application Requester

ent

Interactive SQL

ent

CICS Application

Dbextent

Storage

Applications

Pool

CICS Partition

Batch Resource Adapter

Application

Program

Directory

Application Requester

Log

VSE Batch

Partition

Database

Data System Control

VSAM

Relational Data System

Database Storage

DB2

Subsystem

Database

for VSE

Database Manager

Partition

Library

VSE

Application Server

Figure 2. Basic Components of the RDBMS in VSE/ESA

The database is composed of :

v A collection of data contained in one or more storage pools, each of which in turn

is composed of one or more database extents (dbextents). A dbextent is a VM

minidisk or a VSE VSAM cluster.

v A directory that identifies data locations in the storage pools. There is only one

directory per database.

v A log that contains a record of operations performed on the database. A database

can have either one or two logs.

The database manager is the program that provides access to the data in the

database. In VM it is loaded into the database virtual machine from the production

disk. In VSE it is loaded into the database partition from the DB2 Server for VSE

library.

The application server is the facility that responds to requests for information from

and updates to the database. It is composed of the database and the database

manager.

The application requester is the facility that transforms a request from an

application into a form suitable for communication with an application server.

Note: General references to the database management system are assumed to

apply to the database under discussion, any unique or specific references to

other database systems will be explicitly made.

About This Manual ix

Organization

Summary of Changes.

This section summarizes the technical and library changes made to the DB2 Server

for VSE & VM product for Version 7 Release 5.

Chapter 1, “Designing a Database.”

To store information in a database, you must first convert it into tables while

maintaining any relationships that exist within it. This chapter outlines the steps

for effective design of a database.

Chapter 2, “Implementing Your Design.”

This chapter describes how to estimate your storage requirements, use SQL

commands to create objects (dbspaces, tables, views, and indexes) that support

your design, and query the catalog tables.

Chapter 3, “Maintaining Your Database.”

After a database is implemented, it must be maintained. This chapter describes

how to load data into tables, alter tables, and alter the design of dbspaces.

Chapter 4, “Supporting Your Users.”

This chapter describes activities that database administrators must consider to

support users. The tasks described include adding, deleting, authorizing, and

training users.

Chapter 5, “Providing Security.”

This chapter describes several security mechanisms that can help you protect your

data from unauthorized access.

Chapter 6, “Recovering from Failures.”

This chapter describes facilities you can use to recover from failures and maintain

the integrity of your data.

Chapter 7, “Customizing the HELP Text and Messages Text.”

This chapter discusses national languages used with the database manager.

Chapter 8, “Application Design Considerations.”

This chapter provides an overview of the ways that your data can be accessed, and

discusses topics that you should consider when developing your applications.

Chapter 9, “DB2 Server for VM Database Configurations.”

Information can be stored in one or more DB2 Server for VM application server,

and these application servers may be on one CPU or distributed among many.

Furthermore, users can access an application server on the VM/ESA system from a

VSE guest (this is called VSE Guest Sharing). This chapter describes these various

types of configurations.

Database Administration

Chapter 10, “Usage Environments in VSE.”

This chapter provides an overview of five possible usage environments for which

you can set up your DB2 Server for VSE system.

Chapter 11, “Stored Procedures.”

This chapter provides an overview of what stored procedures are, and how to use

them.

Appendix A, “Estimating Your Dbspace Requirements.”

Dbspaces, which hold tables, must have sufficient storage capacities to meet the

storage requirements of their tables. This appendix describes how to estimate the

amount of storage the tables require, so that you acquire dbspaces with sufficient

capacity.

Appendix B, “CMS EXECs.”

This appendix describes the EXECs provided for use in user VM/ESA virtual

machines.

Appendix C, “Querying the Status of an Application (VM Only).”

This appendix describes the CMS SQLQRY command available in your VM/ESA

system.

Appendix D, “Maximums.”

This appendix describes the logical data and ISQL maximums.

Appendix E, “SQLGLOB Parameters (VSE Only).”

This appendix describes the SQLGLOB VSAM file available in your VSE/ESA

system.

Prerequisite IBM Publications

All readers of this book should be familiar with the content of the following

manuals:

v DB2 Server for VSE & VM Overview, GC09-2995

v DB2 Server for VSE & VM SQL Reference, SC09-2989

v DB2 Server for VSE & VM Performance Tuning Handbook, GC09-2987.

Highlighting Conventions

This manual uses the following text highlighting conventions:

Italics Italic type is used for command variables, parameter values and their

symbolic equivalents, titles of standalone manuals, strings of characters to

be used exactly as they appear, and important terms that are being

defined.

Boldface

Bold type is used for emphasis.

About This Manual xi

Syntax Notation Conventions

Throughout this manual, syntax is described using the structure defined below.

v Read the syntax diagrams from left to right and from top to bottom, following

the path of the line.

The ►►─── symbol indicates the beginning of a statement or command.

The ───► symbol indicates that the statement syntax is continued on the next

line.

The ►─── symbol indicates that a statement is continued from the previous line.

The ───►◄ symbol indicates the end of a statement.

Diagrams of syntactical units that are not complete statements start with the

►─── symbol and end with the ───► symbol.

v Some SQL statements, Interactive SQL (ISQL) commands, or database services

utility (DBS Utility) commands can stand alone. For example:

►► SAVE

►◄

Others must be followed by one or more keywords or variables. For example:

►► SET AUTOCOMMIT OFF

►◄

v Keywords may have parameters associated with them which represent

user-supplied names or values. These names or values can be specified as either

constants or as user-defined variables called host_variables (host_variables can only

be used in programs).

►► DROP SYNONYM synonym

►◄

v Keywords appear in either uppercase (for example, SAVE) or mixed case (for

example, CHARacter). All uppercase characters in keywords must be present;

you can omit those in lowercase.

v Parameters appear in lowercase and in italics (for example, synonym).

v If such symbols as punctuation marks, parentheses, or arithmetic operators are

shown, you must use them as indicated by the syntax diagram.

v All items (parameters and keywords) must be separated by one or more blanks.

v Required items appear on the same horizontal line (the main path). For example,

the parameter integer is a required item in the following command:

xiii

v The repeat symbol indicates that an item can be repeated. For example:

►► ERASE

▼

name

►◄

This statement could appear as:

ERASE NAME1

ERASE NAME1 NAME2

A repeat symbol above a stack indicates that you can make more than one

choice from the stacked items, or repeat a choice. For example:

▼

►► VALUES

(

constant

)

►◄

host_variable_list

NULL

special_register

v If an item is above the main line, it represents a default, which means that it will

be used if no other item is specified. In the following example, the ASC keyword

appears above the line in a stack with DESC. If neither of these values is

specified, the command would be processed with option ASC.

ASC

►►

►◄

DESC

v When an optional keyword is followed on the same path by an optional default

parameter, the default parameter is assumed if the keyword is not entered.

However, if this keyword is entered, one of its associated optional parameters

must also be specified.

In the following example, if you enter the optional keyword PCTFREE =, you

also have to specify one of its associated optional parameters. If you do not

enter PCTFREE =, the database manager will set it to the default value of 10.

PCTFREE = 10

►►

►◄

PCTFREE = integer

v Words that are only used for readability and have no effect on the execution of

the statement are shown as a single uppercase default. For example:

Syntax Notation Conventions xv

SQL Reserved Words

The following words are reserved in the SQL language. They cannot be used in

SQL statements except for their defined meaning in the SQL syntax or as host

variables, preceded by a colon.

In particular, they cannot be used as names for tables, indexes, columns, views, or

dbspaces unless they are enclosed in double quotation marks (").

ACQUIRE

GRANT

RESOURCE

ADD

GRAPHIC

REVOKE

ALL

GROUP

ROLLBACK

ALTER

ROW

AND

HAVING

RUN

ANY

IDENTIFIED

SCHEDULE

ASC

SELECT

AVG

INDEX

SET

INSERT

BETWEEN

INTO

SOME

STATISTICS

STORPOOL

CALL

SUM

CHAR

LOCK

SYNONYM

CHARACTER

LONG

COLUMN

TABLE

COMMENT

MAX

COMMIT

MIN

CONCAT

MODE

UNION

CONNECT

UNIQUE

COUNT

NAMED

UPDATE

CREATE

NHEADER

USER

CURRENT

NOT

NULL

VALUES

DBA

VIEW

DBSPACE

DELETE

WHERE

DESC

OPTION

WITH

DISTINCT

WORK

DOUBLE

ORDER

DROP

PACKAGE

EXCLUSIVE

PAGE

EXECUTE

PAGES

EXISTS

PCTFREE

EXPLAIN

PCTINDEX

PRIVATE

FIELDPROC

PRIVILEGES

FOR

PROGRAM

FROM

PUBLIC

xvii

Summary of Changes

This is a summary of the technical changes to the DB2 Server for VSE & VM

database management system for this edition of the book. Several manuals are

affected by some or all of the changes discussed here. For your convenience, the

changes made in this edition are identified in the text by a vertical bar (|) in the

left margin. This edition may also include minor corrections and editorial changes

that are not identified.

This summary does not list incompatibilities between releases of the DB2 Server

for VSE & VM product; see either the DB2 Server for VSE & VM SQL Reference, DB2

Server for VM System Administration, or the DB2 Server for VSE System

Administration manuals for a discussion of incompatibilities.

Summary of Changes for DB2 Version 7 Release 5

Version 7 Release 5 of the DB2 Server for VSE & VM database management

system is intended to run on the Z/VM Version 5 Release 2 or later environment

and on the Z/VSE(^®) Version 3 Release 1 or later environment.

Enhancements, New Functions, and New Capabilities

The following have been added to DB2 Version 7 Release 5:

Explain Option on DBSU REBIND PACKAGE Command

This new functionality allows the EXPLAIN(YES/NO) option on REBIND

PACKAGE command. If EXPLAIN(YES) is issued, then all four update tables

(structure, plan, cost, reference) will be updated. If EXPLAIN(NO) is issued, then

none of the four update tables will be updated.

For more information, see the following DB2 Server for VSE & VM documentation:

v DB2 Server for VSE & VM Database Services Utility

v DB2 Server for VSE & VM Performance Tuning Handbook

v DB2 Server for VSE & VM Quick Reference

v DB2 Server for VSE & VM SQL Reference

For Fetch only

This new functionality accepts the ″FOR FETCH ONLY″ clause after a cursor select

statement. It causes a cursor to become read-only (no UPDATEs or DELETEs are

permitted using this cursor). If a read-only cursor is referenced in an UPDATE or

DELETE statement, SQLCODE -510 will be issued and the statement is not

processed. In addition, under the SBLOCK preprocessor option, ″FOR FETCH

ONLY″ forces blocking to be used on the read-only cursor regardless of whether

there is a COMMIT. If there is no ″FOR FETCH ONLY″ clause, under SBLOCK,

blocking would only be done if a COMMIT was absent.

For more information, see the following DB2 Server for VSE & VM documentation:

v DB2 Server for VM Messages and Codes

v DB2 Server for VSE & VM Application Programming

v DB2 Server for VSE & VM Performance Tuning Handbook

v DB2 Server for VSE & VM Quick Reference

xix

v DB2 Server for VSE & VM SQL Reference

Application Message Formatter

This functionality provides an Application Programming Interface (API) that

retrieves the descriptive text for an SQLCODE, given an SQLCA input parameter.

The API will be available for Assembly, COBOL, C, PL/I and FORTRAN.

In DB2 for VM and DB2 for VSE Online, the user may specify the language of the

returned text. The languages supported by DB2 for VSE/VM are American English

(AMENG), uppercase English (UCENG), German (GER), French (FRANC) and

Japanese (KANJI). VSE Batch does not support switching to another language.

Therefore the default will be used regardless of the user’s specification. The values

of SQLCODE, SQLSTATE, SQLERRD1 and SQLERRD2 will be automatically

appended to the returned text. The user may also specify to have the entire

SQLCA included. If the SQLCODE could not be found in the repository, the entire

SQLCA will be returned in the buffer.

If the SQLCA was set by another product (such as DB2 UBD), the descriptive text

is retrieved if the SQLCODE exists in the DB2 for VM/VSE repositories. However,

the token substitutions may not be correct.

For more information, see DB2 Server for VSE & VM Application Programming.

Convert buffer read/write to compiler macro

The DRDA code has over 100 small modules. Each call to an external module has a

certain amount of overhead associated with it. Certain modules are called very

frequently and this can add up to a significant amount of time. This functionality

improves the performance by converting few modules to macros or internal

procedures, to reduce this overhead.

Modify Build Tree Creation

This functionality modifies Build Tree creation used by DRDA parsing and

generation. It is built in such a way that every code point that is used to search

through the tree must be converted to a different format before the search can be

done. If modified build tree was created with the converted point, then the code

point would not have to be converted every time the tree must be searched. This

improves the performance of the DRDA code path length with the minimal search.

Split code point search routines

When parsing a data stream within each parser action routine, a binary search is

done to find the specific code point. Some action specific routines are quite large,

so the binary search can be long. Splitting and spreading the code point evenly

among other modules would reduce the overheads and improves the performance

of the DRDA code path length.

DRDA Multi-Row Insert

Multi Row insert is a means of caching homogenous insert statements and sending

them as a block to the server for processing. This reduces the overhead of sending

a large number of singular inserts and receiving as many responses.

Buffering of homogenous inserts eliminates the need to send an SQL statement to

the DB2 server every time an insert is made, thereby improving performance over

DRDA.

For more information, see the following DB2 Server for VSE & VM documentation:

v DB2 Server for VSE & VM Application Programming

Database Administration

v DB2 Server for VSE & VM Database Administration

v DB2 Server for VM System Administration

v DB2 Server for VSE & VM Performance Tuning Handbook

v DB2 Server for VSE & VM Quick Reference

v DB2 Server for VSE & VM SQL Reference

Connection Pooling for DRDA TCP/IP in Online Resource

Adapter

Connection pooling is a technique that allows multiple users to share a cached set

of pre-established connections that provide access to a database. Establishing a

connection between a user and a server takes a sizeable time. Users who have

validated their entry to a database once need not establish a connection every time

a request is submitted. Instead, they can use a pre-established connection from a

pool of such connections and get their results much faster.

From the user’s point of view, there is a considerable improvement in response

time after this line item is implemented.

For more information, see the following documentation on DB2 Server for VSE &

VM:

v DB2 Server for VSE System Administration

v DB2 Server for VSE & VM Application Programming

v DB2 Server for VSE & VM Operation

v DB2 Server for VSE & VM Performance Tuning Handbook

IBM DB2 Server for VSE, Client Edition

This feature allows the customer the flexibility to install and use only the client

(run-time support) component of DB2 Server for VSE without the requirement to

buy and install the server component during the installation process of DB2 server

for VSE product. The client-only installation enables customers to reduce the total

cost of ownership when they have their databases residing on a non-local platform

(like VM, z/OS, LUW) and have a large number of their DB2 applications on VSE

(like ISQL on CICS, DBSU on VSE, other online/batch applications on VSE).

For more information, see the following DB2 Server for VSE & VM documentation:

v DB2 Server for VSE System Administration

v DB2 Server for VSE Program Directory

IBM DB2 Server for VM, Client Edition

This feature allows the customer the flexibility to install and use only the client

(run-time support) component of DB2 Server for VM without the requirement to

buy and install the server component during the installation process of DB2 server

for VM product. The client-only installation enables our customers to reduce the

total cost of ownership when they have their databases residing on a non-local

platform (like VM, z/OS, LUW) and have a large number of their DB2 applications

on VM (like ISQL, DBSU, other user applications on VM).

For more information, see the following DB2 Server for VSE & VM documentation:

v DB2 Server for VM System Administration

v DB2 Server for VM Program Directory

Summary of Changes xxi

Handling Commit Responses from DB2 UDB Stored Procedures

This feature will allow DB2 Resource Manager on VSE/VM to accept and process

results of a stored procedure running in a UDB server with a COMMIT statement

in the stored procedure.

Currently, DB2 for VM/VSE client does not handle responses from ’COMMIT’

statements coded in DB2 UDB stored procedures. Implementation of this feature

will enable handling responses of COMMIT statements in DB2 UDB stored

procedures and thus allow users to have COMMIT statements in their stored

procedures, while using DB2 for VM/VSE client.

COMMIT statements, however, are not allowed in stored procedures on the DB2

Server for VM/VSE.

For more information, see DB2 Server for VSE & VM Application Programming.

Make on-line programs AMODE 31 RMODE ANY

This feature converts DB2 server for VSE online program which presently operate

under 24 bit addressing mode from AMODE 24, to AMODE 31 RMODE ANY.

Presently, all the online programs are loaded below 16M line. Implementation of

this line item ensures that all the online program will be loaded above the 16M

line, which results in more virtual storage below the line, which can be utilized by

other applications.

For more information, see the following DB2 Server for VSE & VM documentation:

v DB2 Server for VSE System Administration

v DB2 Server for VSE Program Directory

Provide BIND File Support in VM and in VSE Batch Environments

This feature provides the facility of binding packages across servers. The process of

binding is achieved by dividing the program preparation method into two steps.

The first step does the precompilation of the embedded SQL programs with the

prep parameter ’BIND’. Invocation of VSE/VM preprocessor creates a ’bindfile’.

The bindfile can be bound against any DB2 server using VSE/VM binder. During

this process, the access path is generated, SQL statements are verified,

authorization checks are performed, and package on the target server is created.

This line item eliminates the need of re-prepping the source code or porting of

packages across DB2 servers.

For more information, see the following DB2 Server for VSE & VM documentation:

v DB2 REXX SQL for VM/ESA Installation and Reference

v DB2 Server for VM Messages and Codes

v DB2 Server for VSE & VM Application Programming

v DB2 Server for VSE & VM Database Administration

v DB2 Server for VM Program Directory

v DB2 Server for VSE Program Directory

Convert TCP/IP LE/C interface to EZASMI API

The feature of converting TCP/IP LE/C interface to EZASMI API intends to

replace the current LE/C interface and implement the EZA Assembler Interface

(EZASMI)to enhance performance in DB2 Client/Server for VSE over DRDA.

Currently, either LE/C interface or CSI Assembler Interface is used for TCP/IP

functions. The EZASMI interface makes the code all Assembler.

xxii Database Administration

Chapter 1. Designing a Database

This chapter describes the conceptual process of database design. The

implementation of the design, that is, the actual creation of a set of objects, is

discussed in Chapter 2, “Implementing Your Design,” on page 17.

Sample Tables

The DB2 Server for VSE & VM database contains sample tables that are referenced

throughout this book and are used to demonstrate various concepts and

procedures.

Entities, Properties, and Occurrences

Some basic terms for database design are defined below. There is no universally

accepted terminology for database design; these terms may be used differently

elsewhere.

v An entity is anything about which information can be stored. In the sample

database, some of the entities are employees, departments, and projects.

v Properties are types of information categories associated with an entity. In the

sample table EMPLOYEE, the entity employee has properties, such as, employee

number, job held, birth date, and salary amount, which appear as columns

EMPNO, JOB, BIRTHDATE, and SALARY.

v The occurrence of an entity consists of the values in all the columns for that

entity. In the sample table EMPLOYEE, each employee has a unique employee

number; therefore, each value in the EMPNO column is unique and can be used

to identify a particular occurrence.

Entities and properties are represented as columns, and occurrences are

represented as values in the columns, as shown in Table 1.

Table 1. Occurrences and Properties of an Entity

ENTITY

PROPERTIES

Employee

EMPNO

JOB

BIRTHDATE

SALARY

Sally Kwan

000030

Manager

1941-05-11

38250

William Jones

000210

Designer

1953-02-23

18270

Step 1: Select the Data to Record in the Database

To be effective, your database must be designed specifically to meet the data

storage and retrieval needs of your organization.

The first step in designing an effective database is to identify the collection of

information that it will contain. You must then organize this information into

tables, with each column of a row related in some way to all other columns of that

row. This approach will enable you to identify the relationships that exist between

the different entities.

For example, the following data relationships are expressed in the sample tables:

v Employees are assigned to departments, for example:

Dolores Quintana is assigned to Department C01.

Heather Nicholls is assigned to Department C01.

v Employees earn money, for example:

Dolores earns $23,800 per year.

Heather earns $28,420 per year.

v Departments report to other departments, for example:

Department C01 reports to Department A00.

Department D01 reports to Department A00.

v Employees work on projects, for example:

Dolores works on project IF1000.

Heather works on projects IF1000 and IF2000.

v Employees manage departments, for example:

Sally Kwan manages Department C01.

Before you design your tables, you must understand entities and their

relationships. Table 2 shows an example.

Table 2. Relationships in the Sample Database

ENTITY

RELATIONSHIP

ENTITY

Employees

are assigned to

departments

Employees

earn

money

Departments

report to

departments

Employees

work on

projects

Employees

manage

departments

The relationship between the columns in a table is the same in each row of the

table. For example, in Table 1 on page 1, the relationship between each entry in the

Employee column and its corresponding entry in the Salary column is the same,

because the Salary column describes the amount the employee earns.

Step

2: Define Tables for Each Type of Relationship

In a relational database, you can express several types of entity relationships.

Consider the relationship between employees and departments. A given employee

can work in only one department, so this relationship is single-valued for

employees. On the other hand, one department can have many employees, so this

relationship is multivalued for departments. Accordingly, this constitutes a

one-to-many relationship. Relationships can be:

v One-to-one

v One-to-many

v Many-to-one

v Many-to-many.

If each employee can belong to several departments, the employees/departments

relationship would be many-to-many.

You must define separate tables for different types of relationships.

One-to-One Relationships

One-to-one relationships are single-valued in both directions. A manager manages

one department; a department has only one manager. The questions “Who is the

manager of Department C01?” and “What department does Sally Kwan manage?”

Database Administration

both have single answers. The relationship could be assigned to either the

department table or the employee table. Because all departments have managers,

but not all employees are managers, it would be logical to add the manager to the

department table, as shown in Figure 3.

DEPARTMENTTable

Employee

manages

department

one

DEPTNO

ADMRDEPT

MGRNO

Figure 3. Assigning One-to-One Facts to a Table

One-to-Many and Many-to-One Relationships

To define tables for each one-to-many and many-to-one relationship, you must:

v Group all the relationships for which the “many” side of the relationship is the

same entity.

v Define a separate table for each group.

In Table 3, the “many” side of the first and second relationships is “employees”, so

we defined an employee table (EMPLOYEE). In Figure 4, “departments” is the

“many” side, so we defined a department table (DEPARTMENT).

Table 3. Many-to-One Relationships

ENTITY

RELATIONSHIP

ENTITY

1. Employees

are assigned to

departments

2. Employees

earn

money

3. Departments

report to

(administrative) departments

Employees

assigned to

departments

DEPARTMENT Table

many

one

EMPNO

WORKDEPT

SALARY

Employees

earn

money

many

one

DEPARTMENT Table

Departments

report

departments

DEPTNO

ADMRDEPT

many

one

Figure 4. Assigning Many-to-One Facts to Tables

Many-to-Many Relationships

A relationship that is multivalued in both directions is many-to-many. An

employee might work on more than one project, and a project might have more

than one employee assigned to it. The questions “What does Dolores Quintana

work on?” and “Who works on project IF1000?” both yield multiple answers. A

many-to-many relationship can be expressed in a table with a column for each

entity (“employees” and “projects”), as shown in Figure 5 on page 4.

Chapter 1. Designing a Database

EMP_ACT Table

Employees

work on

projects

many

EMPNO

PROJNO

Figure 5. Assigning Many-to-Many Facts to a Table

Step

3: Provide Column Definitions for Tables

Defining a column in a table consists of:

v Choosing a name for the column

Each column in a table must have a name that is unique within the table. For

detailed information, see “Column Names” on page 31.

v Specifying the data type that is valid for the column

The data type of a column indicates the length of the values in the column

and the kind of data that is valid for it. For detailed information, see

“Specifying Data Types” on page 32.

v Specifying the columns that can contain null values

Some columns cannot contain meaningful values in all rows because some

values may not be known at a particular time. For example, you may know a

new employee’s name but not his or her birth date. For detailed information,

see “Specifying Data Types” on page 32.

Step

4: Identify One or More Columns as a Primary Key

If every row in a table represents relationships for a unique entity, the table should

have a primary key: one column (or a set of columns) that provides a unique

identifier for the rows of the table. A unique index of the columns of the primary

key is created when the primary key is created. You can create the primary key

when you create the table using the CREATE TABLE statement (see “Creating

Tables” on page 27) or, if the table already exists, by using the ALTER TABLE

statement (see “Altering the Design of a Table” on page 64). A primary key must

not contain a nullable column or a long field.

Note: Long fields include the following data types: VARCHAR(n) with n>254,

VARGRAPHIC(n) with n>127, LONG VARCHAR, or LONG VARGRAPHIC.

The primary keys of some of the sample tables are:

Table

Key Column

EMPLOYEE table

EMPNO

DEPARTMENT table

DEPTNO

PROJECT table

PROJNO

Figure 6 shows part of the PROJECT table with the primary key column indicated.

Database Administration

PRIMARY KEY COLUMN

PROJECT Table

PROJNO PROJNAME

DEPTNO

MA2100

WELD LINE AUTOMATION

D01

MA2110

W L PROGRAMMING

D11

Figure 6. A Primary Key on a Table

Figure 7 shows a primary key consisting of more than one column; it is a

multicolumn key.

PRIMARY KEY COLUMNS

PROJ_ACT Table

PROJNO

ACTNO

ACSTAFF

ACSTDATE

MA2100

0.5

82-01-01

MA2100

1.0

82-01-01

MA2110

1.0

82-01-01

Figure 7. A Multicolumn Primary Key. The three columns PROJNO, ACTNO, and ACSTDATE are all parts of the

primary key.

If you have more than one candidate for a primary key, you can define a UNIQUE

constraint on the column (or set of columns) that you do not select as the primary

key. A column with a UNIQUE constraint is similar to a primary key in that a

unique index on the column is created. It differs in that you can create more than

one UNIQUE constraint on a table, and no foreign keys can reference a UNIQUE

constraint (see “Foreign Key” on page 7).

Step 5: Ensure that Equal Values Represent the Same Entity

You can have more than one table describing properties of the same set of entities.

For example, one table could give employees’ job and salary information, as in the

EMPLOYEE table, and another each employee’s home address. To retrieve both

sets of properties at once, you can join the tables on any set of matching columns,

as shown in Figure 8 on page 6. If there are two employees named Sally Kwan, a

join on employee name may not match the correct rows. Similarly, if one person has

more than one authorization ID, a join on ID may not produce the correct match.

Thus, for the purpose of retrieving information about an entity from more than one

table, an equal value in each of those tables should represent that entity. This type

of join is an equijoin.

Figure 8 shows a join between the DEPARTMENT and EMPLOYEE tables on

columns of department numbers.

Chapter 1. Designing a Database

DEPARTMENT Table

DEPTNO DEPTNAME

MGRNO

ADMRDEPT

E21

SOFTWARE SUPPORT

000100

E01

(join path)

EMPNO FIRSTNME

LASTNAME

WORKDEPT SALARY

000090

EILEEN

HENDERSON E11

29750.00

EMPLOYEE Table

Figure 8. A Join Path between Two Tables

The connecting columns must be of the same data type. They can have different

names (such as WORKDEPT and DEPTNO in Figure 8), or the same name (such as

the two columns called DEPTNO in the DEPARTMENT and PROJECT tables). The

latter case is illustrated in Figure 9.

PROJECT Table

DEPARTMENT Table

PROJNO PROJNAME

DEPTNO

DEPTNO DEPTNAME

MA2100 WELD LINE AUTOMATION D01

D11

MANUFACTURING SYSTEMS

MA2110 W L PROGRAMMING

D11

D21

ADMINISTRATION SYSTEMS

MA2111 W L PROGRAM DESIGN D11

E21

SOFTWARE SUPPORT

(join path)

Figure 9. A Join Path on Columns with the Same Name

Step 6: Plan for Referential Integrity

A table can serve as a complete list of all occurrences of a single entity. In the

sample database, the EMPLOYEE table serves that purpose for employees: only the

numbers that appear in this table are valid employee numbers. Similarly, the

DEPARTMENT table provides a master list of all valid department numbers, and

the PROJECT table provides a master list of valid projects. When a table refers to

an entity for which there is a master list, it should identify an occurrence of the

entity that appears in the master list; otherwise, either the reference is incorrect or

the master list is incomplete.

When all references from one table to another are valid, this condition is called

referential integrity. Having referential integrity does not necessarily mean the data

is correct. That the EMPLOYEE table shows every employee assigned to a valid

department number is one thing; whether it shows every employee in the correct

department is quite another.

Elements of Referential Integrity

You must consider many different elements to ensure referential integrity. The

concepts of a primary key and a unique constraint were described in “Step 4: Identify

One or More Columns as a Primary Key” on page 4. Other elements to consider

when dealing with referential integrity are described in the following sections.

Database Administration

Foreign Key

A column or set of columns that refers to the primary key of another table is a

foreign key. For example, the column Work Department (WORKDEPT) of the

EMPLOYEE table is a foreign key; it refers to DEPTNO, the primary key of the

DEPARTMENT table. The combination of the project number (PROJNO), activity

number (ACTNO), and activity starting date (EMSTDATE) columns in the

EMP_ACT table is a foreign key; it refers to the primary key of the PROJ_ACT

table.

Referential Constraint

A referential constraint is a relationship between a primary key and a foreign key

with certain deletion and update rules that define how the relationship is

maintained. Refer to “DELETE, INSERT, and UPDATE Considerations” for

information on deletion and update rules.

Parent and Dependent Tables

Establishing a referential constraint defines a relationship between two tables. The

table containing the primary key is the parent table, and the one containing the

foreign key is the dependent table. In a multilevel, hierarchical chain of dependent

tables, a descendent table is any table below the top level. Such a table is a

descendent of all the tables above it in the hierarchy.

A referential cycle is a set of referential constraints in which each table in the set is a

descendent of itself. A table can be a parent of many tables, and it can also be a

dependent or descendent of many parents.

Self-Referencing Table

A self-referencing table is one that contains both the primary key and the foreign

key of a referential constraint. Conceptually, a self-referencing table is both the

parent and the dependent table in a relationship. DB2 Server for VSE & VM does

not support self-referencing.

DELETE, INSERT, and UPDATE Considerations

DELETE Rules

For Parent Tables: When an employee retires, you remove that person’s

EMPLOYEE record. The deletion affects the information in the PROJECT,

DEPARTMENT, and EMP_ACT tables. For any particular relationship, one of the

following deletion rules is enforced:

v RESTRICT

You cannot delete any rows of the parent table that have dependent rows. In the

DEPARTMENT-PROJECT relationship, using RESTRICT means that you cannot

remove a department if any of its employees are assigned to a project.

v SET NULL

When you delete a row of the parent table, the corresponding values of the

foreign key in any dependent rows are set to NULL. This rule is used in the

DEPARTMENT-EMPLOYEE relationship: when you delete a department record,

the WORKDEPT column of dependent rows in the employee table is set to

NULL, indicating that the employee is not assigned to a department.

v CASCADE

When you delete a row of the parent table, any dependent rows in the

dependent table are also deleted. This rule is useful when a row in the

Chapter 1. Designing a Database

dependent table is useless without a row in the parent table. For example, if you

delete an employee there is no reason to maintain the associated EMP_ACT

record.

Multiple levels of CASCADE are supported; that is, a delete operation on a

parent table deletes all dependent rows in its dependent tables if the dependent

tables are enforced by the CASCADE delete rule of referential constraint. If any

of these dependent tables are also parent tables, the delete rule of referential

constraint in turn applies between them and their dependent tables. All

applicable delete rules are used to determine the result of a delete operation. A

delete operation is subject to rollback, if the parent row has a dependent row in a

referential constraint with a delete rule of RESTRICT, or if the deletion cascades

to any descendent that has a dependent row in a referential constraint with a

delete rule of RESTRICT.

For Dependent Tables: You may, at any time, delete rows from a dependent table

without taking any action on the parent table. For example, you may no longer

need EMP_ACT records after the project is completed. You can delete the record

without affecting the EMPLOYEE or PROJ_ACT tables.

Restrictions When Using the DELETE Statement: To ensure referential integrity,

the table specified in the subquery must not be affected by the delete on the object

table of the DELETE statement.

For example, if B is the object table of a DELETE statement, and A is a table that is

referenced in the FROM clause of a subquery of that statement, then the following

rules apply:

v Table A cannot also be an object table of the deletion.

v Table A cannot be a dependent of table B in a relationship with a delete rule of

CASCADE or SET NULL.

v Table A cannot be a dependent of any other table (for example, table C) in a

relationship with a delete rule of CASCADE or SET NULL, if deletions from

table B cascade to table C.

For more information on delete-connected tables, refer to “Restrictions on Keys and

Referential Constraints:” on page 40.

INSERT Rules

For Parent Tables: You can insert a row at any time into a parent table without

taking any action in the dependent table. For example, you can create a new

department in the DEPARTMENT table without making any change to the

EMPLOYEE table. For the insertion to be successful, the new primary key or

unique key values must be unique.

For Dependent Tables: You cannot insert a row into a dependent table unless a

row in the parent table contains a primary key value equal to the foreign key value

you want to insert. If a foreign key has a null value, it can be inserted into a

dependent table, but no logical connection exists.

UPDATE Rules

For Parent Tables: You cannot change a value in a primary key column if the

associated row has a dependent row. For example, if a department number

changes, the DEPTNO value in the DEPARTMENT table cannot be changed if

there are employees in the EMPLOYEE table who are members of that department.

Database Administration

For Dependent Tables: You cannot change a value in a foreign key column of a

dependent table unless the new foreign key value already exists in the primary key

of the parent table. For example, when an employee transfers from one department

to another, the department number must change. The new value must be the

number of an existing department, or null.

Step

7: Normalize Your Tables

Normalization is the method of reducing data stored in tables so that the tables

contain unique keys, each identifying a single entity. Each of these keys has an

associated row of values that describes each entity. Complete normalization is not

required for using the database manager.

The topic of normalizing tables draws much attention in database design. This

section briefly reviews the rules for first, second, third, and fourth normal forms of

tables, and describes some reasons why they should or should not be followed.

First Normal Form

Any relational table satisfies the requirement of first normal form: at each

row-and-column position in the table, there exists only one value, never a set of

values.

Second Normal Form

A table is in second normal form if each column not in the key provides a fact that

depends on the entire key.

Second normal form is violated when a non-key column is a fact about a subset of

a composite key, as in Figure 10. An inventory table records quantities of specific

parts stored at particular warehouses; its columns are shown below.

KEY

PART WAREHOUSE QUANTITY WAREHOUSE-ADDRESS

Figure 10. Key Violates Second Normal Form

The key here consists of the PART and the WAREHOUSE columns together.

Because the column WAREHOUSE-ADDRESS depends only on the value of

WAREHOUSE, the table violates the rule for second normal form. The problems

with this design are:

v The warehouse address is repeated in every record for a part stored in that

warehouse.

v If the address of the warehouse changes, every row referring to a part stored in

that warehouse must be updated.

v Because of the redundancy, the data could become inconsistent, with different

records showing different addresses for the same warehouse.

v If at some time there are no parts stored in the warehouse, there may be no row

in which to record the warehouse address.

Chapter 1. Designing a Database

To satisfy second normal form, the information shown in Figure 10 on page 9 must

be in two tables, as in Figure 11.

KEY

PART WAREHOUSE QUANTITY WAREHOUSE WAREHOUSE-ADDRESS

Figure 11. Two Tables Satisfy Second Normal Form

There is a performance disadvantage in having the two tables in second normal

form, because programs that produce reports on the location of parts have to join

both tables to retrieve the relevant information.

For further information on performance considerations, refer to “Considerations for

Normalization” on page 29.

Third Normal Form

A table is in third normal form if each non-key column provides a fact that

depends only on the key.

Third normal form is violated when a non-key column is a fact about another

non-key column. For example, the first table in Figure 12 contains the columns

EMPNO and WORKDEPT. Suppose a column DEPTNAME is added. The new

column depends on WORKDEPT, whereas the primary key is the column EMPNO;

thus, the table now violates third normal form.

Changing DEPTNAME for a single employee, John Parker, does not change the

department name for other employees in that department. The inconsistency that

results is shown in the updated version of the table in Figure 12.

EMPLOYEE-DEPARTMENT Table (EMPDEPT) Before Update

EMPNO

FIRSTNME

LASTNAME

WORKDEPT

DEPTNAME

000290

JOHN

PARKER

E11

OPERATIONS

000320

RAMLAL

MEHTA

E21

SOFTWARE SERVICES

000310

MAUDE

SETRIGHT

E11

OPERATIONS

EMPLOYEE-DEPARTMENT Table (EMPDEPT) After Update

EMPNO

FIRSTNME

LASTNAME

WORKDEPT

DEPTNAME

000290

JOHN

PARKER

E11

INSTALLATION MGMT

000320

RAMLAL

MEHTA

E21

SOFTWARE SERVICES

000310

MAUDE

SETRIGHT

E11

OPERATIONS

Figure 12. Update of an Unnormalized Table. Information in the table has become inconsistent.

Database Administration

The table can be normalized by providing a new table, with columns for

WORKDEPT and DEPTNAME. In that situation, updating a department name is

much easier as it only has to be made to the new table. But an SQL query that

shows the department name with the employee name is more complex to write: it

requires joining the two tables. It also takes longer to run than the query of a

single table. As well, the entire arrangement takes more storage space, because the

WORKDEPT column must appear in both tables.

Fourth Normal Form

A table is in fourth normal form if no row contains two or more independent

multivalued facts about an entity.

Consider facts about employees, skills, and languages, where an employee may

have several skills and know several languages. There are two relationships, one

between employees and skills, and one between employees and languages. A table

is not in fourth normal form if it represents both relationships, as in Figure 13.

KEY

EMPLOYEE SKILL

LANGUAGE

Figure 13. A Table That Violates Fourth Normal Form

Instead, the relationships should be represented in two tables, as in Figure 14.

KEY

EMPLOYEE

SKILL

EMPLOYEE LANGUAGE

Figure 14. Tables in Fourth Normal Form

If, however, the facts are interdependent (that is, the employee applies certain

languages only to certain skills), then the table should not be split.

Any data can be put into fourth normal form. A good rule when designing a

database is to arrange all data in tables in fourth normal form, and then decide

whether the result will give you an acceptable level of performance. If it will not,

you are at liberty to undo the normalization of your design.

Step

8: Considerations for Distributed Data

Two types of access to DB2 Server for VSE & VM data are available. They are

remote unit of work and distributed unit of work.

Remote unit of work, implemented in SQL/DS V3.3, for VM, and SQL/DS V3.4,

for VSE, lets a user or application program on a Distributed Relational Database

Architecture (DRDA) application requester to read or update data stored in a DB2

Server for VSE & VM DRDA application server. With remote unit of work, a user

Chapter 1. Designing a Database

or application program can have many SQL statements within a unit of work;

accessing one database management system with each SQL statement; and

accessing one database management system within a unit of work.

Distributed unit of work, implemented in DB2 Server for VSE & VM Version 5

Release 1 lets a user or application program on a Distributed Relational Database

Architecture (DRDA) application requester to read or update data stored in

multiple locations, where the DB2 Server for VSE & VM DRDA application server

is one of the multiple sites where data is read or updated within a single unit of

work. With distributed unit of work, a user or application program can have many

SQL statements within a unit of work; accessing one database management system

with each SQL statement; and accessing many database management systems

within a unit of work. Commit and rollback are coordinated at all locations so that

if a failure occurs anywhere in the system, data integrity is preserved. This type of

coordinated approach is called two phase commit processing and is done by a

Sync Point Manager. In phase one, the coordinating RDBMS (generally the

requesting RDBMS) polls each participating RDBMS to vote to commit or rollback

the transaction. In phase two, the coordinator directs the RDBMSs to commit or

rollback based on the preceding vote.

Access to DB2 Server for VSE & VM DRDA application servers by DRDA

application requesters is possible only if the DRDA facility is installed on the DB2

Server for VSE & VM application server.

DB2 Server for VM implements the application server and application requester

support for DRDA remote unit of work, and the application server support for

DRDA distributed unit of work. VM application requesters can participate in

remote unit of work activity but cannot participate in distributed unit of work

activity.

Access to non-DB2 Server for VM application servers by DB2 Server for VM

application requester is possible only if the DRDA facility has been installed on the

DB2 Server for VM application requester and if the non-DB2 Server for VM

application servers support IBM’s implementation of the DRDA protocol.

DB2 Server for VSE implements the application requester support for DRDA

remote and distributed unit of work for CICS/VSE online applications. VSE online

application requesters can participate in remote and distributed unit of work

activity. With distributed unit of work, a CICS/VSE online application is limited to

accessing a single DRDA application server within one LUW. However, it can

update another CICS resource, in addition to the remote DRDA application server

it is accessing, within one LUW, provided both the DRDA application server and

the CICS resource participates in two-phase commits.

DB2 Server for VSE implements the application requester support for DRDA

remote unit of work for Batch applications. VSE batch application requesters can

participate in remote unit of work activity, but cannot participate in distributed

unit of work activity.

Access to remote application servers by a DB2 Server for VSE application requester

is possible only if the DRDA facility has been installed on the DB2 Server for VSE

application requester and if the remote application server supports IBM’s

implementation of the DRDA protocol.

Database Administration

Designing a distributed database management system involves making decisions

about where to put the data, how to manage security and accounting, and how to

handle problems, backup, recovery, and change control.

For general guidance on making these decisions, refer to the following manuals:

v Planning for Distributed Relational Database,

v DB2 Connectivity Supplement,

v Connect Enterprise Edition Quick Beginnings,

v DB2 UDB Quick Beginnings, and

v DB2 Server for VM System Administration or DB2 Server for VSE System

Administration.

The decision to access distributed data has implications for many activities:

application programming, data recovery, and authorization. This section introduces

some of these considerations. Refer to the appropriate manual for information on

particular tasks.

Definitions

The application requester is the component that accepts a request from an application

and passes it to an application server. The application server is the component that

receives and processes requests issued by the application requester.

In VSE an application server is local if it resides in the same VSE machine as the

DB2 Server for VSE application requester. This can also be a DB2 Server for VM

application server accessed through VSE guest sharing. This DB2 Server for VM

server can be either on the same VM machine as the VSE guest, or on another VM

machine accessed remotely through AVS or TSAF. A remote application server can

be a DB2 Server for VSE application server not residing in the same VSE machine

as the application program connecting to it, or a non-DB2 Server for VSE

application server.

In VM, a system is local if the application requester and the application server

reside on the same processor, and is remote if they reside on different processors.

Remote does not necessarily mean at a distance; the application server and

application requester may be at the same user site.

Two relational database systems are like if both the application requester and the

application server are the same product (for example, both are DB2 Server for VSE

or both are DB2 Server for VM). They are unlike if different products are involved

(for example, a DB2 Server for VM application requester and a DB2 Server for VSE

application server).

A DB2 Server for VM application requester can communicate with a like system,

either local or remote, through the SQLDS protocol or the DRDA protocol. It can

communicate with an unlike system through the DRDA protocol, if the Relational

Database Management System (RDBMS) of the unlike system supports the

protocol.

A DB2 Server for VSE application requester can communicate with a local DB2

Server for VSE application server through the SQLDS protocol or a DB2 Server for

VM application server which is accessed using Guest Sharing through the SQLDS

protocol. A DB2 Server for VSE application requester can communicate with a

Chapter 1. Designing a Database

remote application server through the DRDA protocol, if the Relational Database

Management System (RDMS) of the remote application server supports the

protocol.

Application Programming

Several categories of application programming considerations are:

Character conversion

Data and statements are converted if the connected systems are using different

coded character set identifiers (CCSIDs). For example, an SQL statement

originating in an ASCII environment that is sent to an EBCDIC environment

must be converted for the DB2 Server for VSE & VM application server to

process it. This conversion ensures that the application server correctly interprets

the statement and the data, and displays the results using the appropriate

character sets. For more information on character conversion, refer to either the

DB2 Server for VM System Administration or the DB2 Server for VSE System

Administration manual.

It is important that the application server and application requester have the

same CCSID value, unless there is a specific reason for them to be different.

When the application server and application requester have different CCSID

values, character conversion cannot be avoided. This conversion has an

associated performance overhead, and causes performance degradation. For

more information on performance, see the DB2 Server for VSE & VM Performance

Tuning Handbook manual.

Access limitations

The limitations that exist for local multiple database applications apply to

remote database applications with remote unit of work support. You cannot:

- Access more than one application server in a single logical unit of work

(LUW).

- Join tables from multiple application servers.

- Define referential constraints across application servers.

These limitations also apply to remote database applications with distributed

unit of work support. One exception though, is that with DUOW you can access

more than one application server in a single logical unit of work (LUW).

For the DRDA protocol restrictions, see the DB2 Server for VSE & VM SQL

Reference manual.

Performance considerations

An obvious consideration for an SQL query that is transmitted to a remote

application server is that the query and its reply must both be transmitted over

an SNA or TCP/IP network, in VSE, or in VM, over a TSAF collection, VTAM

network or TCP/IP network, conceivably as far as halfway around the world.

This can increase the amount of processing and degrade the performance of the

application in comparison with the same query run on your local application

server. If the DRDA protocol is used, the DB2 Server for VSE & VM application

requester has the option of increasing the block size used to return data. This

can improve the performance of some applications. For more information in VM,

see “SQLINIT EXEC” on page 243, in VSE, see Appendix E, “SQLGLOB

Parameters (VSE Only),” on page 273.

If the connected systems use different CCSIDs, performance can also be

adversely affected, because additional processing is required to convert the data

and statements.

Cross-system differences.

Database Administration

Different relational database management systems use the SQL language, and

strive to provide a consistent interface for applications. There are, however, some

inconsistencies between systems. For example, the database manager does not

support self-referencing constraints (a referential constraint in which both the

primary key and the foreign key of the constraint are in the same table). On the

other hand, it provides an EXPLAIN function, useful in tuning SQL statement

performance, which is not provided by some RDBMS. These differences affect

the portability of database designs and applications from system to system.

System Operations

Several commands for monitoring the operations of the DB2 Server for VSE & VM

application server provide detailed information to the database administrator about

users and their systems. For more information on these commands, see the DB2

Server for VSE & VM Operation manual.

You cannot effectively administer a remote application server from your local

system, and sometimes must coordinate operations by means external to your local

system. Both the application requester and application server must be defined in

an SNA or TCP/IP network.

In VSE using SNA networks, Transaction Program Names (TPNs) can be used by

remote application requesters to identify local DB2 Server for VSE application

servers to which they want to connect on the local VSE system. These TPNs must

be identified in the local DBNAME Directory and mapped to the appropriate

server APPLID. Likewise, Remote Transaction Program Names (REMTPNs) can be

used by the local system to identify the remote DRDA application server to which

the local DB2 Server for VSE online (CICS) application requester wants to connect

(Batch applications must use TCP/IP). These REMTPNs must be identified in the

local VSE DBNAME Directory and mapped to the appropriate remote server SNA

System ID (SYSID).

In VSE using TCP/IP networks, remote DRDA application requesters must know

the local VSE TCP/IP Server’s IP Address (or Host Name) and the local DB2

application server’s Listener Port Number to access the local DB2 Server for VSE

DRDA application server. Likewise, local VSE DRDA application requesters must

know the remote DRDA application server’s IP address (or Host Name) and

Listener Port Number, which are identified in the local VSE DBNAME Directory.

For additional information on the VSE DBNAME Directory, refer to the DB2 Server

for VSE System Administration manual.

In VM, all access to remote application servers through VTAM or TCP/IP require a

CMS Communication Directory for the application requester. You must plan for

creating and maintaining this directory on each VM system where the application

requester resides. See the VM/ESA: Connectivity Planning, Administration, and

Operation manual.

Similar considerations apply to users accessing other (non-DB2 Server for VSE &

VM) application servers. Because each application server controls access to its own

data, you must arrange to have valid user IDs on the other systems. As well, you

must arrange for users to have proper authority and privileges on those

application servers. Traces (used for problem determination) must also be

coordinated with administrators at other sites, because traces must come from the

system on which the data resides.

Chapter 1. Designing a Database

Chapter

2. Implementing Your Design

After determining the design of your database, you can create objects to implement

your design. These objects include dbspaces, tables, views, and indexes.

This chapter discusses the following topics:

Database Storage Concepts

This section provides an overview of the physical database and explains the

relationships between objects, dbspaces, and storage pools.

Database Generation

When you create a database, its potential storage capacity is defined. You must

do some planning to ensure that the database satisfies your data storage

requirements.

Defining Dbspaces

The task of defining dbspaces, which contain tables, views, and indexes,

involves reserving logical space in the database, assigning the dbspace to a

storage pool, and setting usage parameters. You must understand what these

parameters are and how to select them so that the dbspace will best

accommodate the data to be stored in it.

Creating Tables

Information is stored in a database by placing it in tables. You must know how

to create tables and how to define referential constraints.

Creating Views

After you create tables, you can create views. A view is a logical, or virtual,

table that is derived from one or more tables or other views. Using views can

be advantageous in applications that have specific requirements for data tables.

Creating Indexes

Indexes are optional: they improve the speed with which table rows are

accessed.

Using the Catalog in Database Design

The catalog tables contain information about the existing structure of the

database, which can be helpful in database design.

Storage Concepts

A DB2 Server for VSE & VM database is a collection of user data objects (tables

and indexes) and supporting information maintained by the database manager for

that data. The supporting information includes control information (such as how

each data table is formatted and where each is located), and data recovery

information (restoring data to an earlier state). The database is composed of:

v A Directory: In VM this is a minidisk that contains database control information.

In VSE it is a VSAM data set. It includes mappings of the dbspaces to their

addresses on the DASD (that is, it relates the logical database image to the

physical storage used).

v One, two, or four Logs: In VM, these are minidisks and in VSE, these are VSAM

data sets. These contain information about the changes made to the data. If any

changes must be “undone” or “redone”, logs can be used to restore the data to

its proper state.

DB2 Server for VSE. Operations Guide / Handbooks (2004-2007) - page 47