DB2 Version 9.1 for z/OS

Administration Guide

SC18-9840-05

���

DB2 Version 9.1 for z/OS

Administration Guide

SC18-9840-05

���

NoteBefore using this information and the product it supports, be sure to read the general information under “Notices” at theend of this information.

Sixth edition (October 2009)

This edition applies to DB2 Version 9.1 for z/OS (DB2 V9.1 for z/OS), product number 5635-DB2, and to anysubsequent releases until otherwise indicated in new editions. Make sure you are using the correct edition for thelevel of the product.

Specific changes are indicated by a vertical bar to the left of a change. A vertical bar to the left of a figure captionindicates that the figure has changed. Editorial changes that have no technical significance are not noted.

© Copyright International Business Machines Corporation 1982, 2009.US Government Users Restricted Rights – Use, duplication or disclosure restricted by GSA ADP Schedule Contractwith IBM Corp.

Contents

About this information . . . . . . . . . . . . . . . . . . . . . . . . . . . . xviiWho should read this information . . . . . . . . . . . . . . . . . . . . . . . . . . . xviiDB2 Utilities Suite . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xviiTerminology and citations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xviiiAccessibility features for DB2 Version 9.1 for z/OS . . . . . . . . . . . . . . . . . . . . . xviiiHow to send your comments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xixHow to read syntax diagrams . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xix

Part 1. Designing a database . . . . . . . . . . . . . . . . . . . . . . . . . 1

Chapter 1. Database objects and relationships . . . . . . . . . . . . . . . . . . . 3Logical database design with the entity-relationship model . . . . . . . . . . . . . . . . . . . . 3

Modeling your data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3Recommendations for logical data modeling . . . . . . . . . . . . . . . . . . . . . . . . 5Practical examples of data modeling . . . . . . . . . . . . . . . . . . . . . . . . . . 5Entities for different types of relationships . . . . . . . . . . . . . . . . . . . . . . . . 5Entity attributes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7Entity normalization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9

Logical database design with Unified Modeling Language. . . . . . . . . . . . . . . . . . . . 13Physical database design . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15

Denormalization of tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15Views as a way to customize what data users see . . . . . . . . . . . . . . . . . . . . . 17Indexes on table columns. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18

Chapter 2. Implementing your database design . . . . . . . . . . . . . . . . . . 19Implementing DB2 databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19

Creating DB2 databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19Dropping DB2 databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20

Implementing DB2 storage groups. . . . . . . . . . . . . . . . . . . . . . . . . . . . 20Advantages of storage groups . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20Creating DB2 storage groups . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22Enabling SMS to control DB2 storage groups . . . . . . . . . . . . . . . . . . . . . . . 22Deferring allocation of DB2-managed data sets . . . . . . . . . . . . . . . . . . . . . . 23How DB2 extends data sets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23DB2 space allocation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24Managing DB2 data sets with DFSMShsm . . . . . . . . . . . . . . . . . . . . . . . . 28Managing your own data sets . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32Defining index space storage . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36Creating EA-enabled table spaces and index spaces . . . . . . . . . . . . . . . . . . . . . 36

Implementing DB2 table spaces. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37Creating a table space explicitly . . . . . . . . . . . . . . . . . . . . . . . . . . . 37Guidelines and recommendations for table spaces . . . . . . . . . . . . . . . . . . . . . 38Examples of table space definitions . . . . . . . . . . . . . . . . . . . . . . . . . . 42

Implementing DB2 tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45Creating base tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45Guidelines for table names . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45Creating temporary tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45Creating materialized query tables. . . . . . . . . . . . . . . . . . . . . . . . . . . 50Creating tables that use table-controlled partitioning. . . . . . . . . . . . . . . . . . . . . 51Creating tables that use index-controlled partitioning . . . . . . . . . . . . . . . . . . . . 54Creating a clone table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54

Implementing DB2 views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55Creating DB2 views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55Guidelines for view names . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57

© Copyright IBM Corp. 1982, 2009 iii

||

How DB2 inserts and updates data through views . . . . . . . . . . . . . . . . . . . . . 57Dropping DB2 views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58

Implementing DB2 indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58Creating DB2 indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59Guidelines for defining indexes. . . . . . . . . . . . . . . . . . . . . . . . . . . . 59How DB2 implicitly creates an index . . . . . . . . . . . . . . . . . . . . . . . . . . 60Recommendations for index page size . . . . . . . . . . . . . . . . . . . . . . . . . 61Index versions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62Compressing indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63

Implementing DB2 schemas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63Creating a schema by using the schema processor . . . . . . . . . . . . . . . . . . . . . 63Processing schema definitions . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64

Loading data into DB2 tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65Loading data with the LOAD utility . . . . . . . . . . . . . . . . . . . . . . . . . . 65Loading data by using the INSERT statement . . . . . . . . . . . . . . . . . . . . . . . 68Loading data from DL/I . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70

Implementing DB2 stored procedures. . . . . . . . . . . . . . . . . . . . . . . . . . . 71Creating stored procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72Dropping stored procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73

Implementing DB2 user-defined functions . . . . . . . . . . . . . . . . . . . . . . . . . 73Creating user-defined functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . 73Deleting user-defined functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . 74

Estimating disk storage for user data . . . . . . . . . . . . . . . . . . . . . . . . . . . 74General approach to estimating storage . . . . . . . . . . . . . . . . . . . . . . . . . 74Calculating the space required for a table . . . . . . . . . . . . . . . . . . . . . . . . 76Calculating the space required for an index. . . . . . . . . . . . . . . . . . . . . . . . 79

Saving space with data compression . . . . . . . . . . . . . . . . . . . . . . . . . . . 84Compressing data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84Calculating the space that is required for a dictionary . . . . . . . . . . . . . . . . . . . . 84

Chapter 3. Altering your database design . . . . . . . . . . . . . . . . . . . . . 87Altering DB2 databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87

ALTER DATABASE options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87Altering DB2 storage groups. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 88

Letting SMS manage your DB2 storage groups . . . . . . . . . . . . . . . . . . . . . . 88Adding or removing volumes from a DB2 storage group . . . . . . . . . . . . . . . . . . . 88

Altering table spaces . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89Changing the logging attribute . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90Changing the space allocation for user-managed data sets . . . . . . . . . . . . . . . . . . . 91Dropping, re-creating, or converting a table space . . . . . . . . . . . . . . . . . . . . . 92Rebalancing data in partitioned table spaces . . . . . . . . . . . . . . . . . . . . . . . 93Altering a page set to contain DB2-defined extents . . . . . . . . . . . . . . . . . . . . . 94

Altering DB2 tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94Adding a new column to a table . . . . . . . . . . . . . . . . . . . . . . . . . . . 95Specifying a default value when altering a column . . . . . . . . . . . . . . . . . . . . . 96Altering the data type of a column . . . . . . . . . . . . . . . . . . . . . . . . . . 97Altering a table for referential integrity . . . . . . . . . . . . . . . . . . . . . . . . . 102Adding or dropping table check constraints . . . . . . . . . . . . . . . . . . . . . . . 106Adding a partition . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 106Altering partitions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 108Adding XML columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 115Altering materialized query tables . . . . . . . . . . . . . . . . . . . . . . . . . . 115Altering the assignment of a validation routine . . . . . . . . . . . . . . . . . . . . . . 118Altering a table to capture changed data . . . . . . . . . . . . . . . . . . . . . . . . 118Changing an edit procedure or a field procedure . . . . . . . . . . . . . . . . . . . . . 119Altering the subtype of a string column . . . . . . . . . . . . . . . . . . . . . . . . 119Altering the attributes of an identity column . . . . . . . . . . . . . . . . . . . . . . . 120Changing data types by dropping and re-creating the table . . . . . . . . . . . . . . . . . . 120Moving a table to a table space of a different page size . . . . . . . . . . . . . . . . . . . 123

Altering DB2 views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 124Altering views by using the INSTEAD OF trigger . . . . . . . . . . . . . . . . . . . . . 124

iv Administration Guide

||||

||

||

||

||

||

Altering DB2 indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 125Adding a column to an index when you add the column to a table . . . . . . . . . . . . . . . 126Altering how varying-length index columns are stored . . . . . . . . . . . . . . . . . . . 127Altering the clustering of an index . . . . . . . . . . . . . . . . . . . . . . . . . . 127Dropping and redefining a DB2 index . . . . . . . . . . . . . . . . . . . . . . . . . 128Reorganizing indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 128Recycling index version numbers. . . . . . . . . . . . . . . . . . . . . . . . . . . 129

Altering stored procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 129Altering user-defined functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131Altering implicitly created XML objects. . . . . . . . . . . . . . . . . . . . . . . . . . 131Changing the high-level qualifier for DB2 data sets. . . . . . . . . . . . . . . . . . . . . . 132

Defining a new integrated catalog alias. . . . . . . . . . . . . . . . . . . . . . . . . 133Changing the qualifier for system data sets . . . . . . . . . . . . . . . . . . . . . . . 133Changing qualifiers for other databases and user data sets . . . . . . . . . . . . . . . . . . 136

Tools for moving DB2 data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 139Moving DB2 data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 141Moving a DB2 data set . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 142Scenario: Moving from index-controlled to table-controlled partitioning . . . . . . . . . . . . . . 143

Part 2. Security and auditing . . . . . . . . . . . . . . . . . . . . . . . . 147

Chapter 4. Getting started with DB2 security . . . . . . . . . . . . . . . . . . . 149DB2 security solutions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 149What’s new in DB2 Version 9.1 security? . . . . . . . . . . . . . . . . . . . . . . . . . 149DB2 data access control . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 150

ID-based access control within DB2 . . . . . . . . . . . . . . . . . . . . . . . . . . 151Role-based access control within DB2 . . . . . . . . . . . . . . . . . . . . . . . . . 152Ownership-based access control within DB2 . . . . . . . . . . . . . . . . . . . . . . . 152Access control through multilevel security. . . . . . . . . . . . . . . . . . . . . . . . 152Access control through exit routines . . . . . . . . . . . . . . . . . . . . . . . . . . 152

DB2 subsystem access control . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 153Managing access requests from local applications . . . . . . . . . . . . . . . . . . . . . 153Managing access requests from remote applications . . . . . . . . . . . . . . . . . . . . 153

Data set protection . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 154RACF for data protection . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 154Data encryption . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 154

Scenario: Securing data access at Spiffy Computer . . . . . . . . . . . . . . . . . . . . . . 155Determining security objectives . . . . . . . . . . . . . . . . . . . . . . . . . . . 155Securing manager access to employee data . . . . . . . . . . . . . . . . . . . . . . . 155Securing access to payroll operations and management . . . . . . . . . . . . . . . . . . . 159Managing access privileges of other authorities . . . . . . . . . . . . . . . . . . . . . . 162

Chapter 5. Managing access through authorization IDs or roles. . . . . . . . . . . 165Authorization IDs and roles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 166

Authorization IDs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 166Roles in a trusted context . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 166

Privileges and authorities . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 167Explicit privileges . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 167Implicit privileges through object ownership . . . . . . . . . . . . . . . . . . . . . . . 173Administrative authorities . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 174Utility authorities for DB2 catalog and directory. . . . . . . . . . . . . . . . . . . . . . 179Privileges by authorization ID and authority . . . . . . . . . . . . . . . . . . . . . . . 180

Managing explicit privileges . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 185Granting privileges to a role . . . . . . . . . . . . . . . . . . . . . . . . . . . . 186Granting privileges to the PUBLIC ID . . . . . . . . . . . . . . . . . . . . . . . . . 186Granting privileges to remote users . . . . . . . . . . . . . . . . . . . . . . . . . . 187Granting privileges through views . . . . . . . . . . . . . . . . . . . . . . . . . . 188Granting privileges with the GRANT statement . . . . . . . . . . . . . . . . . . . . . . 188Revoking privileges with the REVOKE statement . . . . . . . . . . . . . . . . . . . . . 194

Managing implicit privileges . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 202

Contents v

||

||

||||

||

||

||

Managing implicit privileges through object ownership . . . . . . . . . . . . . . . . . . . 202Managing implicit privileges through plan or package ownership . . . . . . . . . . . . . . . . 205Managing implicit privileges through routines . . . . . . . . . . . . . . . . . . . . . . 212

Retrieving privilege records in the DB2 catalog . . . . . . . . . . . . . . . . . . . . . . . 225Catalog tables with privilege records . . . . . . . . . . . . . . . . . . . . . . . . . 225Retrieving all authorization IDs or roles with granted privileges . . . . . . . . . . . . . . . . 226Retrieving multiple grants of the same privilege. . . . . . . . . . . . . . . . . . . . . . 226Retrieving all authorization IDs or roles with the DBADM authority . . . . . . . . . . . . . . . 227Retrieving all IDs or roles with access to the same table . . . . . . . . . . . . . . . . . . . 227Retrieving all IDs or roles with access to the same routine . . . . . . . . . . . . . . . . . . 228Retrieving tables or views accessible by an ID . . . . . . . . . . . . . . . . . . . . . . 229Retrieving plans or packages with access to the same table . . . . . . . . . . . . . . . . . . 229Retrieving privilege information through views . . . . . . . . . . . . . . . . . . . . . . 230

Implementing multilevel security with DB2 . . . . . . . . . . . . . . . . . . . . . . . . 230Multilevel security. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 231Mandatory access checking . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 234Implementing multilevel security at the object level . . . . . . . . . . . . . . . . . . . . 236Implementing multilevel security with row-level granularity . . . . . . . . . . . . . . . . . 237Restricting access to the security label column . . . . . . . . . . . . . . . . . . . . . . 240Managing data in a multilevel-secure environment . . . . . . . . . . . . . . . . . . . . . 240Implementing multilevel security in a distributed environment . . . . . . . . . . . . . . . . . 248

Chapter 6. Managing access through RACF . . . . . . . . . . . . . . . . . . . 251Establishing RACF protection for DB2 . . . . . . . . . . . . . . . . . . . . . . . . . . 251

Defining DB2 resources to RACF . . . . . . . . . . . . . . . . . . . . . . . . . . . 252Permitting RACF access . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 254Managing authorization for stored procedures . . . . . . . . . . . . . . . . . . . . . . 261Protecting connection requests that use the TCP/IP protocol . . . . . . . . . . . . . . . . . 270Establishing Kerberos authentication through RACF . . . . . . . . . . . . . . . . . . . . 271

Implementing DB2 support for enterprise identity mapping . . . . . . . . . . . . . . . . . . . 272Configuring the z/OS LDAP server . . . . . . . . . . . . . . . . . . . . . . . . . . 272Setting up RACF for the z/OS LDAP server . . . . . . . . . . . . . . . . . . . . . . . 274Setting up the EIM domain controller . . . . . . . . . . . . . . . . . . . . . . . . . 274Adding the SAF user mapping plug-in data set to LNKLIST . . . . . . . . . . . . . . . . . 276

Managing connection requests from local applications . . . . . . . . . . . . . . . . . . . . . 276Processing of connection requests . . . . . . . . . . . . . . . . . . . . . . . . . . 277Using secondary IDs for connection requests . . . . . . . . . . . . . . . . . . . . . . . 278Processing of sign-on requests . . . . . . . . . . . . . . . . . . . . . . . . . . . . 279Using secondary IDs for sign-on requests . . . . . . . . . . . . . . . . . . . . . . . . 281Using sample connection and sign-on exit routines for CICS transactions . . . . . . . . . . . . . 281

Managing connection requests from remote applications . . . . . . . . . . . . . . . . . . . . 282Security mechanisms for DRDA and SNA . . . . . . . . . . . . . . . . . . . . . . . . 282Communications database for the server . . . . . . . . . . . . . . . . . . . . . . . . 283Enabling change of user passwords . . . . . . . . . . . . . . . . . . . . . . . . . . 285Authorization failure code . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 286Managing inbound SNA-based connection requests . . . . . . . . . . . . . . . . . . . . 286Managing inbound TCP/IP-based connection requests . . . . . . . . . . . . . . . . . . . 293Managing denial-of-service attacks . . . . . . . . . . . . . . . . . . . . . . . . . . 295Managing outbound connection requests . . . . . . . . . . . . . . . . . . . . . . . . 296Translating outbound IDs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 305Sending passwords . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 307

Chapter 7. Managing access through trusted contexts. . . . . . . . . . . . . . . 311Trusted contexts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 311Trusted connections . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 311Defining trusted contexts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 312Creating local trusted connections . . . . . . . . . . . . . . . . . . . . . . . . . . . 313Establishing remote trusted connections by DB2 for z/OS requesters . . . . . . . . . . . . . . . . 313Establishing remote trusted connections to DB2 for z/OS servers . . . . . . . . . . . . . . . . . 314Switching users of a trusted connection . . . . . . . . . . . . . . . . . . . . . . . . . 315

vi Administration Guide

||||||||||

||

||||||||||||||

Reusing a local trusted connection through the DSN command processor and DB2I . . . . . . . . . . 315Reusing a remote trusted connection by DB2 for z/OS requesters . . . . . . . . . . . . . . . . 316Reusing a remote trusted connection through DB2 for z/OS servers . . . . . . . . . . . . . . . 316Reusing a local trusted connection through RRSAF . . . . . . . . . . . . . . . . . . . . . 316Reusing a local trusted connection through the SQL CONNECT statement . . . . . . . . . . . . . 317

Defining external security profiles . . . . . . . . . . . . . . . . . . . . . . . . . . . 317Enabling users to perform actions on behalf of others . . . . . . . . . . . . . . . . . . . . . 318Performing tasks on objects for other users . . . . . . . . . . . . . . . . . . . . . . . . 318

Chapter 8. Managing access through data definition control . . . . . . . . . . . . 321Data definition statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 321Data definition control support . . . . . . . . . . . . . . . . . . . . . . . . . . . . 321Registration tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 322Installing data definition control support . . . . . . . . . . . . . . . . . . . . . . . . . 323Enabling data definition control . . . . . . . . . . . . . . . . . . . . . . . . . . . . 324

Controlling data definition by application name . . . . . . . . . . . . . . . . . . . . . . 324Controlling data definition by application name with exceptions . . . . . . . . . . . . . . . . 325Controlling data definition by object name . . . . . . . . . . . . . . . . . . . . . . . 327Controlling data definition by object name with exceptions . . . . . . . . . . . . . . . . . . 328

Registering object sets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 329Disabling data definition control . . . . . . . . . . . . . . . . . . . . . . . . . . . . 330Managing registration tables and indexes . . . . . . . . . . . . . . . . . . . . . . . . . 331

Creating registration tables and indexes . . . . . . . . . . . . . . . . . . . . . . . . 331Naming registration tables and indexes. . . . . . . . . . . . . . . . . . . . . . . . . 332Dropping registration tables and indexes . . . . . . . . . . . . . . . . . . . . . . . . 332Creating table spaces for registration tables . . . . . . . . . . . . . . . . . . . . . . . 332Adding columns to registration tables . . . . . . . . . . . . . . . . . . . . . . . . . 332Updating registration tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . 333

Chapter 9. Protecting data through encryption and RACF . . . . . . . . . . . . . 335Encrypting your data through DB2 built-in functions . . . . . . . . . . . . . . . . . . . . . 335

Defining columns for encrypted data . . . . . . . . . . . . . . . . . . . . . . . . . 335Defining column-level encryption . . . . . . . . . . . . . . . . . . . . . . . . . . 336Defining value-level encryption . . . . . . . . . . . . . . . . . . . . . . . . . . . 337Using predicates for encrypted data . . . . . . . . . . . . . . . . . . . . . . . . . . 339Optimizing performance of encrypted data . . . . . . . . . . . . . . . . . . . . . . . 339

Encrypting your data with Secure Socket Layer support . . . . . . . . . . . . . . . . . . . . 341AT-TLS configuration. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 341Configuring the DB2 server for SSL . . . . . . . . . . . . . . . . . . . . . . . . . . 342Configuring the DB2 requester for SSL . . . . . . . . . . . . . . . . . . . . . . . . . 343

Protecting data sets through RACF . . . . . . . . . . . . . . . . . . . . . . . . . . . 344Adding groups to control DB2 data sets . . . . . . . . . . . . . . . . . . . . . . . . 344Creating generic profiles for data sets . . . . . . . . . . . . . . . . . . . . . . . . . 344Authorizing DB2 IDs to use data set profiles . . . . . . . . . . . . . . . . . . . . . . . 346Enabling DB2 IDs to create data sets . . . . . . . . . . . . . . . . . . . . . . . . . 346

Chapter 10. Auditing access to DB2. . . . . . . . . . . . . . . . . . . . . . . 347Determining active security measures . . . . . . . . . . . . . . . . . . . . . . . . . . 347DB2 audit trace. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 348

Authorization IDs traced by auditing . . . . . . . . . . . . . . . . . . . . . . . . . 348Audit classes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 349Audit trace reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 350Audit trace records . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 350Limitations of the audit trace . . . . . . . . . . . . . . . . . . . . . . . . . . . . 351Starting the audit trace . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 351Stopping the audit trace . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 352Collecting audit trace records . . . . . . . . . . . . . . . . . . . . . . . . . . . . 352Formatting audit trace records. . . . . . . . . . . . . . . . . . . . . . . . . . . . 353Auditing in a distributed data environment . . . . . . . . . . . . . . . . . . . . . . . 353

Additional sources of audit information . . . . . . . . . . . . . . . . . . . . . . . . . 353

Contents vii

||||||||||

||||

||||||||

Determining ID privileges and authorities . . . . . . . . . . . . . . . . . . . . . . . . . 354Auditing specific IDs or roles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 354Auditing specific tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 355Ensuring data accuracy and integrity . . . . . . . . . . . . . . . . . . . . . . . . . . 355

Ensuring data presence and uniqueness . . . . . . . . . . . . . . . . . . . . . . . . 356Protecting data integrity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 356Tracking data changes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 357Checking for lost and incomplete transactions . . . . . . . . . . . . . . . . . . . . . . 357

Ensuring data consistency . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 358Using referential integrity for data consistency . . . . . . . . . . . . . . . . . . . . . . 358Using locks for data consistency . . . . . . . . . . . . . . . . . . . . . . . . . . . 358Checking data consistency . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 359

Part 3. Operation and recovery . . . . . . . . . . . . . . . . . . . . . . . 363

Chapter 11. DB2 basic operational concepts . . . . . . . . . . . . . . . . . . . 365Recommendations for entering commands. . . . . . . . . . . . . . . . . . . . . . . . . 365DB2 operator commands . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 365Where DB2 commands are entered . . . . . . . . . . . . . . . . . . . . . . . . . . . 368Where command responses go . . . . . . . . . . . . . . . . . . . . . . . . . . . . 370Authorities for DB2 commands . . . . . . . . . . . . . . . . . . . . . . . . . . . . 371DB2 message identifiers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 372

Unsolicited DB2 messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 372Operational control options. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 373

Chapter 12. Starting and stopping DB2 . . . . . . . . . . . . . . . . . . . . . 375Starting DB2. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 375

Messages at start . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 375Options at start. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 376Restricting access to data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 376Ending the wait state at startup . . . . . . . . . . . . . . . . . . . . . . . . . . . 377Restart options after an abend . . . . . . . . . . . . . . . . . . . . . . . . . . . . 377

Stopping DB2 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 378

Chapter 13. Submitting work to DB2 . . . . . . . . . . . . . . . . . . . . . . 379Submitting work by using DB2I . . . . . . . . . . . . . . . . . . . . . . . . . . . . 379Running TSO application programs . . . . . . . . . . . . . . . . . . . . . . . . . . . 379

DSN subcommands for TSO environments . . . . . . . . . . . . . . . . . . . . . . . 380Sources that DB2 checks to find authorization access for an application program . . . . . . . . . . . 381

Running IMS application programs . . . . . . . . . . . . . . . . . . . . . . . . . . . 381Running CICS application programs. . . . . . . . . . . . . . . . . . . . . . . . . . . 382Running batch application programs . . . . . . . . . . . . . . . . . . . . . . . . . . 382Running application programs using CAF . . . . . . . . . . . . . . . . . . . . . . . . . 383Running application programs using RRSAF . . . . . . . . . . . . . . . . . . . . . . . . 384

Chapter 14. Scheduling administrative tasks . . . . . . . . . . . . . . . . . . . 385Interacting with the administrative task scheduler . . . . . . . . . . . . . . . . . . . . . . 385

Adding a task . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 385Listing scheduled tasks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 397Listing the last execution status of scheduled tasks . . . . . . . . . . . . . . . . . . . . . 402Removing a scheduled task. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 406Manually starting the administrative task scheduler . . . . . . . . . . . . . . . . . . . . 408Manually stopping the administrative task scheduler . . . . . . . . . . . . . . . . . . . . 409Synchronization between administrative task schedulers in a data sharing environment . . . . . . . . 409Troubleshooting the administrative task scheduler . . . . . . . . . . . . . . . . . . . . . 410

Architecture of the administrative task scheduler . . . . . . . . . . . . . . . . . . . . . . 413The lifecycle of the administrative task scheduler . . . . . . . . . . . . . . . . . . . . . 414Task lists of the administrative task scheduler . . . . . . . . . . . . . . . . . . . . . . 416Architecture of the administrative task scheduler in a data sharing environment . . . . . . . . . . . 416

viii Administration Guide

||

||

||||

||||||||||||||||||||||||||||

Security guidelines for the administrative task scheduler . . . . . . . . . . . . . . . . . . . . 417User roles in the administrative task scheduler . . . . . . . . . . . . . . . . . . . . . . 418Protection of the interface of the administrative task scheduler . . . . . . . . . . . . . . . . . 419Protection of the resources of the administrative task scheduler . . . . . . . . . . . . . . . . 419Secure execution of tasks in the administrative task scheduler . . . . . . . . . . . . . . . . . 420

Execution of scheduled tasks in the administrative task scheduler . . . . . . . . . . . . . . . . . 421Multi-threading in the administrative task scheduler . . . . . . . . . . . . . . . . . . . . 421Scheduling execution of a stored procedure . . . . . . . . . . . . . . . . . . . . . . . 422How the administrative task scheduler works with Unicode. . . . . . . . . . . . . . . . . . 424Scheduled execution of a JCL job . . . . . . . . . . . . . . . . . . . . . . . . . . . 424Execution of scheduled tasks in a data sharing environment. . . . . . . . . . . . . . . . . . 425

Chapter 15. Monitoring and controlling DB2 and its connections . . . . . . . . . . 427Controlling DB2 databases and buffer pools . . . . . . . . . . . . . . . . . . . . . . . . 427

Starting databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 427Monitoring databases. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 429Obtaining information about application programs . . . . . . . . . . . . . . . . . . . . . 431Obtaining information about and handling pages in error . . . . . . . . . . . . . . . . . . 433Using the STOP DATABASE command to make objects available . . . . . . . . . . . . . . . . 436Altering buffer pools . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 437Monitoring buffer pools . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 437

Controlling user-defined functions . . . . . . . . . . . . . . . . . . . . . . . . . . . 438Starting user-defined functions . . . . . . . . . . . . . . . . . . . . . . . . . . . 438Monitoring user-defined functions . . . . . . . . . . . . . . . . . . . . . . . . . . 439Stopping user-defined functions . . . . . . . . . . . . . . . . . . . . . . . . . . . 440

Controlling DB2 utilities . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 440Starting online utilities . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 440Monitoring and changing online utilities . . . . . . . . . . . . . . . . . . . . . . . . 440Controlling DB2 stand-alone utilities . . . . . . . . . . . . . . . . . . . . . . . . . 441

Controlling the IRLM. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 442z/OS commands that operate on IRLM. . . . . . . . . . . . . . . . . . . . . . . . . 443Starting the IRLM . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 444Stopping the IRLM . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 445

Monitoring threads . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 445Types of threads . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 446Output of the DISPLAY THREAD command . . . . . . . . . . . . . . . . . . . . . . . 446Displaying information about threads . . . . . . . . . . . . . . . . . . . . . . . . . 447Monitoring all DBMSs in a transaction . . . . . . . . . . . . . . . . . . . . . . . . . 451

Controlling connections . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 453Controlling TSO connections . . . . . . . . . . . . . . . . . . . . . . . . . . . . 453Controlling CICS connections . . . . . . . . . . . . . . . . . . . . . . . . . . . . 456Controlling IMS connections . . . . . . . . . . . . . . . . . . . . . . . . . . . . 461Controlling RRS connections . . . . . . . . . . . . . . . . . . . . . . . . . . . . 471Controlling connections to remote systems . . . . . . . . . . . . . . . . . . . . . . . 475

Controlling traces . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 493Types of DB2 traces . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 493Diagnostic traces for attachment facilities . . . . . . . . . . . . . . . . . . . . . . . . 494Controlling the DB2 trace . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 494Diagnostic trace for the IRLM . . . . . . . . . . . . . . . . . . . . . . . . . . . . 495

Controlling the resource limit facility (governor). . . . . . . . . . . . . . . . . . . . . . . 496Changing subsystem parameter values . . . . . . . . . . . . . . . . . . . . . . . . . . 496Setting the priority of stored procedures . . . . . . . . . . . . . . . . . . . . . . . . . 497

Chapter 16. Managing the log and the bootstrap data set . . . . . . . . . . . . . 499How database changes are made . . . . . . . . . . . . . . . . . . . . . . . . . . . . 499

Units of recovery and points of consistency . . . . . . . . . . . . . . . . . . . . . . . 499How DB2 rolls back work . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 500How the initial DB2 logging environment is established . . . . . . . . . . . . . . . . . . . 501How DB2 creates log records . . . . . . . . . . . . . . . . . . . . . . . . . . . . 501How DB2 writes the active log . . . . . . . . . . . . . . . . . . . . . . . . . . . 502

Contents ix

||||||||||||||||||||||

||||

||

||

How DB2 writes (offloads) the archive log . . . . . . . . . . . . . . . . . . . . . . . 503How DB2 retrieves log records . . . . . . . . . . . . . . . . . . . . . . . . . . . . 508Managing the log . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 508

Quiescing activity before offloading . . . . . . . . . . . . . . . . . . . . . . . . . . 509Archiving the log . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 510Dynamically changing the checkpoint frequency. . . . . . . . . . . . . . . . . . . . . . 511Setting limits for archive log tape units . . . . . . . . . . . . . . . . . . . . . . . . . 512Monitoring the system checkpoint . . . . . . . . . . . . . . . . . . . . . . . . . . 512Displaying log information . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 512

Resetting the log RBA . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 513Log RBA range . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 513Resetting the log RBA value in a data sharing environment . . . . . . . . . . . . . . . . . . 514Resetting the log RBA value in a non-data sharing environment . . . . . . . . . . . . . . . . 514

Canceling and restarting an offload . . . . . . . . . . . . . . . . . . . . . . . . . . . 515Displaying the status of an offload . . . . . . . . . . . . . . . . . . . . . . . . . . . 515Discarding archive log records. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 516Locating archive log data sets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 516Management of the bootstrap data set . . . . . . . . . . . . . . . . . . . . . . . . . . 519

Restoring dual-BSDS mode . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 520BSDS copies with archive log data sets . . . . . . . . . . . . . . . . . . . . . . . . . 520Recommendations for changing the BSDS log inventory . . . . . . . . . . . . . . . . . . . 520

Chapter 17. Restarting DB2 after termination . . . . . . . . . . . . . . . . . . . 523Methods of restarting. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 523

Types of termination . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 523Normal restart and recovery . . . . . . . . . . . . . . . . . . . . . . . . . . . . 524Automatic restart . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 529Restart in a data sharing environment . . . . . . . . . . . . . . . . . . . . . . . . . 529Restart implications for table spaces that are not logged . . . . . . . . . . . . . . . . . . . 529Conditional restart . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 530

Terminating DB2 normally . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 531Restarting automatically . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 531Deferring restart processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 532

Deferral of restart . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 533Performing conditional restart . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 533

Options for recovery operations after conditional restart . . . . . . . . . . . . . . . . . . . 534Conditional restart records . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 534

Resolving postponed units of recovery . . . . . . . . . . . . . . . . . . . . . . . . . . 534RECOVER POSTPONED command . . . . . . . . . . . . . . . . . . . . . . . . . . 535Recovering from an error during RECOVER POSTPONED processing . . . . . . . . . . . . . . 536

Chapter 18. Maintaining consistency across multiple systems . . . . . . . . . . . 537Multiple system consistency . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 537

Two-phase commit process . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 537Commit coordinator and multiple participants . . . . . . . . . . . . . . . . . . . . . . 539Illustration of multi-site update . . . . . . . . . . . . . . . . . . . . . . . . . . . 540Termination for multiple systems . . . . . . . . . . . . . . . . . . . . . . . . . . . 541Consistency after termination or failure. . . . . . . . . . . . . . . . . . . . . . . . . 542Normal restart and recovery for multiple systems . . . . . . . . . . . . . . . . . . . . . 543Multiple-system restart with conditions. . . . . . . . . . . . . . . . . . . . . . . . . 544Heuristic decisions about whether to commit or abort an indoubt thread . . . . . . . . . . . . . 544

Resolving indoubt units of recovery . . . . . . . . . . . . . . . . . . . . . . . . . . . 544Resolution of IMS indoubt units of recovery . . . . . . . . . . . . . . . . . . . . . . . 545Resolution of CICS indoubt units of recovery. . . . . . . . . . . . . . . . . . . . . . . 546Resolution of RRS indoubt units of recovery . . . . . . . . . . . . . . . . . . . . . . . 546Resolving WebSphere Application Server indoubt units of recovery . . . . . . . . . . . . . . . 547Resolving remote DBMS indoubt units of recovery . . . . . . . . . . . . . . . . . . . . . 549Determining the coordinator’s commit or abort decision . . . . . . . . . . . . . . . . . . . 550Recovering indoubt threads . . . . . . . . . . . . . . . . . . . . . . . . . . . . 550Resetting the status of an indoubt thread . . . . . . . . . . . . . . . . . . . . . . . . 551

x Administration Guide

||

Chapter 19. Backing up and recovering your data. . . . . . . . . . . . . . . . . 553Plans for backup and recovery . . . . . . . . . . . . . . . . . . . . . . . . . . . . 553

Plans for recovery of distributed data . . . . . . . . . . . . . . . . . . . . . . . . . 553Plans for extended recovery facility toleration . . . . . . . . . . . . . . . . . . . . . . 554Plans for recovery of indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . 555Preparation for recovery: a scenario . . . . . . . . . . . . . . . . . . . . . . . . . . 555Events that occur during recovery . . . . . . . . . . . . . . . . . . . . . . . . . . 556Tips for maximizing data availability during backup and recovery . . . . . . . . . . . . . . . 561Where to find recovery information . . . . . . . . . . . . . . . . . . . . . . . . . . 564How to report recovery information . . . . . . . . . . . . . . . . . . . . . . . . . . 565How to discard SYSCOPY and SYSLGRNX records. . . . . . . . . . . . . . . . . . . . . 566Preparations for disaster recovery . . . . . . . . . . . . . . . . . . . . . . . . . . 567Recommendations for more effective recovery from inconsistency . . . . . . . . . . . . . . . . 569How to recover multiple objects in parallel . . . . . . . . . . . . . . . . . . . . . . . 571Automatic fast log apply during RECOVER . . . . . . . . . . . . . . . . . . . . . . . 572Recovery of page sets and data sets . . . . . . . . . . . . . . . . . . . . . . . . . . 572Recovery of data to a prior point in time . . . . . . . . . . . . . . . . . . . . . . . . 578Preparing to recover an entire DB2 subsystem to a prior point in time using image copies or object-levelbackups . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 587Creating essential disaster recovery elements . . . . . . . . . . . . . . . . . . . . . . . 588Resolving problems with a user-defined work file data set . . . . . . . . . . . . . . . . . . 589Resolving problems with DB2-managed work file data sets . . . . . . . . . . . . . . . . . . 590Recovering error ranges for a work file table space . . . . . . . . . . . . . . . . . . . . . 590Recovering after a conditional restart of DB2 . . . . . . . . . . . . . . . . . . . . . . . 591Regenerating missing identity column values. . . . . . . . . . . . . . . . . . . . . . . 592Recovering a table space and all of its indexes . . . . . . . . . . . . . . . . . . . . . . 593Removing various pending states from LOB and XML table spaces . . . . . . . . . . . . . . . 597Restoring data by using DSN1COPY . . . . . . . . . . . . . . . . . . . . . . . . . 597Backing up and restoring data with non-DB2 dump and restore . . . . . . . . . . . . . . . . 598Recovering accidentally dropped objects . . . . . . . . . . . . . . . . . . . . . . . . 598Recovering your DB2 system to a given point in time by using the RESTORE SYSTEM utility . . . . . . 604Recovering by using DB2 restart recovery . . . . . . . . . . . . . . . . . . . . . . . . 614Recovering by using FlashCopy backups . . . . . . . . . . . . . . . . . . . . . . . . 614Making catalog definitions consistent with your data after recovery to a prior point in time . . . . . . . 615Performing remote site recovery from a disaster at a local site . . . . . . . . . . . . . . . . . 617Backup and recovery involving clone tables . . . . . . . . . . . . . . . . . . . . . . . 619Data restore of an entire system . . . . . . . . . . . . . . . . . . . . . . . . . . . 619

Chapter 20. Recovering from different DB2 for z/OS problems . . . . . . . . . . . 621Recovering from IRLM failure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 621Recovering from z/OS or power failure . . . . . . . . . . . . . . . . . . . . . . . . . 621Recovering from disk failure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 622Recovering from application errors . . . . . . . . . . . . . . . . . . . . . . . . . . . 624

Backing out incorrect application changes (with a quiesce point) . . . . . . . . . . . . . . . . 624Backing out incorrect application changes (without a quiesce point) . . . . . . . . . . . . . . . 625

Recovering from IMS-related failures . . . . . . . . . . . . . . . . . . . . . . . . . . 625Recovering from IMS control region failure . . . . . . . . . . . . . . . . . . . . . . . 626Recovering from IMS indoubt units of recovery . . . . . . . . . . . . . . . . . . . . . . 626Recovering from IMS application failure . . . . . . . . . . . . . . . . . . . . . . . . 629Recovering from a DB2 failure in an IMS environment . . . . . . . . . . . . . . . . . . . 629

Recovering from CICS-related failure . . . . . . . . . . . . . . . . . . . . . . . . . . 630Recovering from CICS application failures. . . . . . . . . . . . . . . . . . . . . . . . 630Recovering DB2 when CICS is not operational . . . . . . . . . . . . . . . . . . . . . . 631Recovering DB2 when the CICS attachment facility cannot connect to DB2 . . . . . . . . . . . . . 632Recovering CICS indoubt units of recovery . . . . . . . . . . . . . . . . . . . . . . . 632Recovering from CICS attachment facility failure . . . . . . . . . . . . . . . . . . . . . 635

Recovering from subsystem termination . . . . . . . . . . . . . . . . . . . . . . . . . 635Recovering from temporary resource failure . . . . . . . . . . . . . . . . . . . . . . . . 636Recovering from active log failures . . . . . . . . . . . . . . . . . . . . . . . . . . . 637

Recovering from being out of space in active logs . . . . . . . . . . . . . . . . . . . . . 637Recovering from a write I/O error on an active log data set . . . . . . . . . . . . . . . . . . 638

Contents xi

||||

Recovering from a loss of dual active logging . . . . . . . . . . . . . . . . . . . . . . 639Recovering from I/O errors while reading the active log . . . . . . . . . . . . . . . . . . . 640

Recovering from archive log failures. . . . . . . . . . . . . . . . . . . . . . . . . . . 642Recovering from allocation problems with the archive log . . . . . . . . . . . . . . . . . . 642Recovering from write I/O errors during archive log offload . . . . . . . . . . . . . . . . . 642Recovering from read I/O errors on an archive data set during recovery . . . . . . . . . . . . . 643Recovering from insufficient disk space for offload processing . . . . . . . . . . . . . . . . . 644

Recovering from BSDS failures . . . . . . . . . . . . . . . . . . . . . . . . . . . . 645Recovering from an I/O error on the BSDS . . . . . . . . . . . . . . . . . . . . . . . 645Recovering from an error that occurs while opening the BSDS . . . . . . . . . . . . . . . . . 646Recovering from unequal timestamps on BSDSs . . . . . . . . . . . . . . . . . . . . . . 646Recovering the BSDS from a backup copy . . . . . . . . . . . . . . . . . . . . . . . . 647

Recovering from BSDS or log failures during restart . . . . . . . . . . . . . . . . . . . . . 650Recovering from failure during log initialization or current status rebuild . . . . . . . . . . . . . 652Recovering from a failure during forward log recovery . . . . . . . . . . . . . . . . . . . 663Recovering from a failure during backward log recovery . . . . . . . . . . . . . . . . . . . 669Recovering from a failure during a log RBA read request . . . . . . . . . . . . . . . . . . . 671Recovering from unresolvable BSDS or log data set problem during restart. . . . . . . . . . . . . 672Recovering from a failure resulting from total or excessive loss of log data . . . . . . . . . . . . . 675Resolving inconsistencies resulting from a conditional restart . . . . . . . . . . . . . . . . . 679

Recovering from DB2 database failure . . . . . . . . . . . . . . . . . . . . . . . . . . 684Recovering a DB2 subsystem to a prior point in time . . . . . . . . . . . . . . . . . . . . . 685Recovering from a down-level page set problem. . . . . . . . . . . . . . . . . . . . . . . 686Recovering from a problem with invalid LOBs . . . . . . . . . . . . . . . . . . . . . . . 688Recovering from table space I/O errors. . . . . . . . . . . . . . . . . . . . . . . . . . 689Recovering from DB2 catalog or directory I/O errors . . . . . . . . . . . . . . . . . . . . . 690Recovering from integrated catalog facility failure . . . . . . . . . . . . . . . . . . . . . . 691

Recovering VSAM volume data sets that are out of space or destroyed . . . . . . . . . . . . . . 691Recovering from out-of-disk-space or extent limit problems . . . . . . . . . . . . . . . . . . 692

Recovering from referential constraint violation . . . . . . . . . . . . . . . . . . . . . . . 696Recovering from distributed data facility failure . . . . . . . . . . . . . . . . . . . . . . . 697

Recovering from conversation failure . . . . . . . . . . . . . . . . . . . . . . . . . 697Recovering from communications database failure . . . . . . . . . . . . . . . . . . . . . 698Recovering from database access thread failure . . . . . . . . . . . . . . . . . . . . . . 699Recovering from VTAM failure . . . . . . . . . . . . . . . . . . . . . . . . . . . 700Recovering from TCP/IP failure . . . . . . . . . . . . . . . . . . . . . . . . . . . 700Recovering from remote logical unit failure . . . . . . . . . . . . . . . . . . . . . . . 701Recovering from an indefinite wait condition. . . . . . . . . . . . . . . . . . . . . . . 701Recovering database access threads after security failure . . . . . . . . . . . . . . . . . . . 702

Performing remote-site disaster recovery . . . . . . . . . . . . . . . . . . . . . . . . . 703Recovering from a disaster by using system-level backups . . . . . . . . . . . . . . . . . . 703Restoring data from image copies and archive logs . . . . . . . . . . . . . . . . . . . . . 704Recovering from disasters by using a tracker site . . . . . . . . . . . . . . . . . . . . . 718Using data mirroring for disaster recovery . . . . . . . . . . . . . . . . . . . . . . . 727

Scenarios for resolving problems with indoubt threads . . . . . . . . . . . . . . . . . . . . 733Scenario: Recovering from communication failure . . . . . . . . . . . . . . . . . . . . . 735Scenario: Making a heuristic decision about whether to commit or abort an indoubt thread . . . . . . . 737Scenario: Recovering from an IMS outage that results in an IMS cold start . . . . . . . . . . . . . 739Scenario: Recovering from a DB2 outage at a requester that results in a DB2 cold start . . . . . . . . . 740Scenario: What happens when the wrong DB2 subsystem is cold started . . . . . . . . . . . . . 745Scenario: Correcting damage from an incorrect heuristic decision about an indoubt thread . . . . . . . 746

Chapter 21. Reading log records . . . . . . . . . . . . . . . . . . . . . . . . 749Contents of the log . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 749

Unit of recovery log records . . . . . . . . . . . . . . . . . . . . . . . . . . . . 749Checkpoint log records . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 754Database page set control records . . . . . . . . . . . . . . . . . . . . . . . . . . 754Other exception information . . . . . . . . . . . . . . . . . . . . . . . . . . . . 755

The physical structure of the log . . . . . . . . . . . . . . . . . . . . . . . . . . . . 755Physical and logical log records . . . . . . . . . . . . . . . . . . . . . . . . . . . 755The log record header . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 756

xii Administration Guide

||

The log control interval definition (LCID) . . . . . . . . . . . . . . . . . . . . . . . . 757Log record type codes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 759Log record subtype codes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 760Interpreting data change log records. . . . . . . . . . . . . . . . . . . . . . . . . . 761

Reading log records with IFI . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 762Gathering active log records into a buffer . . . . . . . . . . . . . . . . . . . . . . . . 762Reading specific log records (IFCID 0129) . . . . . . . . . . . . . . . . . . . . . . . . 763Reading complete log data (IFCID 0306) . . . . . . . . . . . . . . . . . . . . . . . . 764

Reading log records with OPEN, GET, and CLOSE . . . . . . . . . . . . . . . . . . . . . . 766JCL DD statements for DB2 stand-alone log services . . . . . . . . . . . . . . . . . . . . 767Data sharing members that participate in a read. . . . . . . . . . . . . . . . . . . . . . 769Registers and return codes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 770Stand-alone log OPEN request. . . . . . . . . . . . . . . . . . . . . . . . . . . . 771Stand-alone log GET request . . . . . . . . . . . . . . . . . . . . . . . . . . . . 772Stand-alone log CLOSE request . . . . . . . . . . . . . . . . . . . . . . . . . . . 774Sample application that uses stand-alone log services . . . . . . . . . . . . . . . . . . . . 775

Reading log records with the log capture exit routine . . . . . . . . . . . . . . . . . . . . . 776

Part 4. Appendixes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 779

Appendix A. Exit routines . . . . . . . . . . . . . . . . . . . . . . . . . . . 781Connection routines and sign-on routines . . . . . . . . . . . . . . . . . . . . . . . . . 781

Specifying connection and sign-on routines . . . . . . . . . . . . . . . . . . . . . . . 782Sample connection and sign-on routines . . . . . . . . . . . . . . . . . . . . . . . . 782When connection and sign-on routines are taken . . . . . . . . . . . . . . . . . . . . . 783Exit parameter list for connection and sign-on routines . . . . . . . . . . . . . . . . . . . 784Authorization ID parameter list for connection and sign-on routines . . . . . . . . . . . . . . . 785Input values for connection routines. . . . . . . . . . . . . . . . . . . . . . . . . . 786Input values for sign-on routines . . . . . . . . . . . . . . . . . . . . . . . . . . . 787Expected output for connection and sign-on routines . . . . . . . . . . . . . . . . . . . . 787Processing in sample connection and sign-on routines . . . . . . . . . . . . . . . . . . . . 788Performance considerations for connection and sign-on routines . . . . . . . . . . . . . . . . 789Debugging connection and sign-on routines . . . . . . . . . . . . . . . . . . . . . . . 790Session variables in connection and sign-on routines . . . . . . . . . . . . . . . . . . . . 791

Access control authorization exit routines . . . . . . . . . . . . . . . . . . . . . . . . . 792Specifying access control authorization routines . . . . . . . . . . . . . . . . . . . . . . 794The default access control authorization routine . . . . . . . . . . . . . . . . . . . . . . 794When access control authorization routines are taken . . . . . . . . . . . . . . . . . . . . 794Considerations for access control authorization routines . . . . . . . . . . . . . . . . . . . 795Parameter list for access control authorization routines . . . . . . . . . . . . . . . . . . . 799Expected output for access control authorization routines . . . . . . . . . . . . . . . . . . 808Debugging access control authorization routines. . . . . . . . . . . . . . . . . . . . . . 811Determining whether the access control authorization routine is active . . . . . . . . . . . . . . 811

Edit routines . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 812Specifying edit routines . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 812When edit routines are taken . . . . . . . . . . . . . . . . . . . . . . . . . . . . 813Parameter list for edit routines . . . . . . . . . . . . . . . . . . . . . . . . . . . 813Incomplete rows and edit routines . . . . . . . . . . . . . . . . . . . . . . . . . . 814Expected output for edit routines. . . . . . . . . . . . . . . . . . . . . . . . . . . 815

Validation routines . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 815Specifying validation routines . . . . . . . . . . . . . . . . . . . . . . . . . . . . 816When validation routines are taken . . . . . . . . . . . . . . . . . . . . . . . . . . 816Parameter list for validation routines . . . . . . . . . . . . . . . . . . . . . . . . . 816Incomplete rows and validation routines . . . . . . . . . . . . . . . . . . . . . . . . 818Expected output for validation routines . . . . . . . . . . . . . . . . . . . . . . . . 818

Date and time routines . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 819Specifying date and time routines . . . . . . . . . . . . . . . . . . . . . . . . . . 819When date and time routines are taken. . . . . . . . . . . . . . . . . . . . . . . . . 820Parameter list for date and time routines . . . . . . . . . . . . . . . . . . . . . . . . 820Expected output for date and time routines . . . . . . . . . . . . . . . . . . . . . . . 821

Contents xiii

Conversion procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 822Specifying conversion procedures . . . . . . . . . . . . . . . . . . . . . . . . . . 822When conversion procedures are taken . . . . . . . . . . . . . . . . . . . . . . . . . 823Parameter list for conversion procedures . . . . . . . . . . . . . . . . . . . . . . . . 823Expected output for conversion procedures . . . . . . . . . . . . . . . . . . . . . . . 824

Field procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 825Field definition for field procedures . . . . . . . . . . . . . . . . . . . . . . . . . . 826Specifying field procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 826When field procedures are taken . . . . . . . . . . . . . . . . . . . . . . . . . . . 827Control blocks for execution of field procedures . . . . . . . . . . . . . . . . . . . . . . 828Field-definition (function code 8) . . . . . . . . . . . . . . . . . . . . . . . . . . . 831Field-encoding (function code 0) . . . . . . . . . . . . . . . . . . . . . . . . . . . 834Field-decoding (function code 4) . . . . . . . . . . . . . . . . . . . . . . . . . . . 835

Log capture routines . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 837Specifying log capture routines . . . . . . . . . . . . . . . . . . . . . . . . . . . 838When log capture routines are taken . . . . . . . . . . . . . . . . . . . . . . . . . 838Parameter list for log capture routines . . . . . . . . . . . . . . . . . . . . . . . . . 838

Routines for dynamic plan selection in CICS . . . . . . . . . . . . . . . . . . . . . . . . 840Routine for the CICS transaction invocation stored procedure . . . . . . . . . . . . . . . . . . 840General guidelines for writing exit routines . . . . . . . . . . . . . . . . . . . . . . . . 840

Coding rules for exit routines . . . . . . . . . . . . . . . . . . . . . . . . . . . . 840Modifying exit routines . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 841Execution environment for exit routines . . . . . . . . . . . . . . . . . . . . . . . . 841Registers at invocation for exit routines. . . . . . . . . . . . . . . . . . . . . . . . . 841Parameter list for exit routines. . . . . . . . . . . . . . . . . . . . . . . . . . . . 842

Row formats for edit and validation routines . . . . . . . . . . . . . . . . . . . . . . . . 843Column boundaries for edit and validation routines . . . . . . . . . . . . . . . . . . . . 843Null values for edit procedures, field procedures, and validation routines . . . . . . . . . . . . . 844Fixed-length rows for edit and validation routines . . . . . . . . . . . . . . . . . . . . . 844Varying-length rows for edit and validation routines . . . . . . . . . . . . . . . . . . . . 844Varying-length rows with nulls for edit and validation routines . . . . . . . . . . . . . . . . 845EDITPROCs and VALIDPROCs for handling basic and reordered row formats . . . . . . . . . . . 845Converting basic row format table spaces with edit and validation routines to reordered row format . . . . 846Dates, times, and timestamps for edit and validation routines . . . . . . . . . . . . . . . . . 847Parameter list for row format descriptions. . . . . . . . . . . . . . . . . . . . . . . . 848DB2 codes for numeric data in edit and validation routines . . . . . . . . . . . . . . . . . . 849

RACF access control module . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 851

Appendix B. Stored procedures for administration . . . . . . . . . . . . . . . . 853DSNACICS stored procedure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 853

The DSNACICX user exit routine. . . . . . . . . . . . . . . . . . . . . . . . . . . 859DSNLEUSR stored procedure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 861DSNAIMS stored procedure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 864DSNAIMS2 stored procedure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 868ADMIN_COMMAND_DB2 stored procedure . . . . . . . . . . . . . . . . . . . . . . . . 873ADMIN_COMMAND_DSN stored procedure . . . . . . . . . . . . . . . . . . . . . . . 885ADMIN_COMMAND_UNIX stored procedure . . . . . . . . . . . . . . . . . . . . . . . 887ADMIN_DS_BROWSE stored procedure . . . . . . . . . . . . . . . . . . . . . . . . . 891ADMIN_DS_DELETE stored procedure. . . . . . . . . . . . . . . . . . . . . . . . . . 894ADMIN_DS_LIST stored procedure . . . . . . . . . . . . . . . . . . . . . . . . . . . 897ADMIN_DS_RENAME stored procedure . . . . . . . . . . . . . . . . . . . . . . . . . 902ADMIN_DS_SEARCH stored procedure . . . . . . . . . . . . . . . . . . . . . . . . . 905ADMIN_DS_WRITE stored procedure . . . . . . . . . . . . . . . . . . . . . . . . . . 908ADMIN_INFO_HOST stored procedure . . . . . . . . . . . . . . . . . . . . . . . . . 912ADMIN_INFO_SSID stored procedure . . . . . . . . . . . . . . . . . . . . . . . . . . 915ADMIN_INFO_SYSPARM stored procedure . . . . . . . . . . . . . . . . . . . . . . . . 917ADMIN_JOB_CANCEL stored procedure . . . . . . . . . . . . . . . . . . . . . . . . . 921ADMIN_JOB_FETCH stored procedure. . . . . . . . . . . . . . . . . . . . . . . . . . 923ADMIN_JOB_QUERY stored procedure . . . . . . . . . . . . . . . . . . . . . . . . . 926ADMIN_JOB_SUBMIT stored procedure . . . . . . . . . . . . . . . . . . . . . . . . . 930ADMIN_UTL_SCHEDULE stored procedure . . . . . . . . . . . . . . . . . . . . . . . . 933

xiv Administration Guide

||

||

||||

||||

ADMIN_UTL_SORT stored procedure . . . . . . . . . . . . . . . . . . . . . . . . . . 942Common SQL API stored procedures . . . . . . . . . . . . . . . . . . . . . . . . . . 949

Versioning of XML documents. . . . . . . . . . . . . . . . . . . . . . . . . . . . 950XML input documents . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 951XML output documents . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 952XML message documents . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 953GET_CONFIG stored procedure . . . . . . . . . . . . . . . . . . . . . . . . . . . 954GET_MESSAGE stored procedure . . . . . . . . . . . . . . . . . . . . . . . . . . 973GET_SYSTEM_INFO stored procedure . . . . . . . . . . . . . . . . . . . . . . . . . 982

Troubleshooting DB2 stored procedures . . . . . . . . . . . . . . . . . . . . . . . . . 996

Information resources for DB2 for z/OS and related products . . . . . . . . . . . 999

How to obtain DB2 information . . . . . . . . . . . . . . . . . . . . . . . . 1005

How to use the DB2 library . . . . . . . . . . . . . . . . . . . . . . . . . . 1009

Notices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1013Programming Interface Information . . . . . . . . . . . . . . . . . . . . . . . . . . 1015

General-use Programming Interface and Associated Guidance Information . . . . . . . . . . . . 1015Product-sensitive Programming Interface and Associated Guidance Information. . . . . . . . . . . 1015

Trademarks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1015

Glossary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1017

Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1061

Contents xv

||||||||||||||||||

xvi Administration Guide

About this information

This information provides guidance information that you can use to perform avariety of administrative tasks with DB2® for z/OS® (DB2).

Information about DB2 concepts, which was present in previous editions of thisbook, is now provided in Introduction to DB2 for z/OS.

This information assumes that your DB2 subsystem is running in Version 9.1new-function mode. Generally, new functions that are described, including changesto existing functions, statements, and limits, are available only in new-functionmode. Two exceptions to this general statement are new and changed utilities andoptimization enhancements, which are also available in conversion mode unlessstated otherwise.

Who should read this informationThis information is primarily intended for system and database administrators. Itassumes that the user is familiar with:v The basic concepts and facilities of DB2v Time Sharing Option (TSO) and Interactive System Productivity Facility (ISPF)v The basic concepts of Structured Query Language (SQL)v The basic concepts of Customer Information Control System (CICS®)v The basic concepts of Information Management System (IMS™)v How to define and allocate z/OS data sets using job control language (JCL).

Certain tasks require additional skills, such as knowledge of Transmission ControlProtocol/Internet Protocol (TCP/IP) or Virtual Telecommunications Access Method(VTAM®) to set up communication between DB2 subsystems, or knowledge of theIBM® System Modification Program (SMP/E) to install IBM licensed programs.

DB2 Utilities Suite

Important: In this version of DB2 for z/OS, the DB2 Utilities Suite is available asan optional product. You must separately order and purchase a license to suchutilities, and discussion of those utility functions in this publication is not intendedto otherwise imply that you have a license to them.

The DB2 Utilities Suite is designed to work with the DFSORT™ program, whichyou are licensed to use in support of the DB2 utilities even if you do not otherwiselicense DFSORT for general use. If your primary sort product is not DFSORT,consider the following informational APARs mandatory reading:v II14047/II14213: USE OF DFSORT BY DB2 UTILITIESv II13495: HOW DFSORT TAKES ADVANTAGE OF 64-BIT REAL

ARCHITECTURE

These informational APARs are periodically updated.Related information

DB2 utilities packaging (Utility Guide)

© Copyright IBM Corp. 1982, 2009 xvii

http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db29.doc.ugref/db2z_utlpackaging.htm

Terminology and citationsIn this information, DB2 Version 9.1 for z/OS is referred to as ″DB2 for z/OS.″ Incases where the context makes the meaning clear, DB2 for z/OS is referred to as″DB2.″ When this information refers to titles of DB2 for z/OS books, a short title isused. (For example, ″See DB2 SQL Reference″ is a citation to IBM DB2 Version 9.1 forz/OS SQL Reference.)

When referring to a DB2 product other than DB2 for z/OS, this information usesthe product’s full name to avoid ambiguity.

The following terms are used as indicated:

DB2 Represents either the DB2 licensed program or a particular DB2 subsystem.

OMEGAMON®

Refers to any of the following products:v IBM Tivoli® OMEGAMON XE for DB2 Performance Expert on z/OSv IBM Tivoli OMEGAMON XE for DB2 Performance Monitor on z/OSv IBM DB2 Performance Expert for Multiplatforms and Workgroupsv IBM DB2 Buffer Pool Analyzer for z/OS

C, C++, and C languageRepresent the C or C++ programming language.

CICS Represents CICS Transaction Server for z/OS.

IMS Represents the IMS Database Manager or IMS Transaction Manager.

MVS™ Represents the MVS element of the z/OS operating system, which isequivalent to the Base Control Program (BCP) component of the z/OSoperating system.

RACF®

Represents the functions that are provided by the RACF component of thez/OS Security Server.

Accessibility features for DB2 Version 9.1 for z/OSAccessibility features help a user who has a physical disability, such as restrictedmobility or limited vision, to use information technology products successfully.

Accessibility features

The following list includes the major accessibility features in z/OS products,including DB2 Version 9.1 for z/OS. These features support:v Keyboard-only operation.v Interfaces that are commonly used by screen readers and screen magnifiers.v Customization of display attributes such as color, contrast, and font size

Tip: The Information Management Software for z/OS Solutions InformationCenter (which includes information for DB2 Version 9.1 for z/OS) and its relatedpublications are accessibility-enabled for the IBM Home Page Reader. You canoperate all features using the keyboard instead of the mouse.

Keyboard navigation

You can access DB2 Version 9.1 for z/OS ISPF panel functions by using a keyboardor keyboard shortcut keys.

xviii Administration Guide

|

For information about navigating the DB2 Version 9.1 for z/OS ISPF panels usingTSO/E or ISPF, refer to the z/OS TSO/E Primer, the z/OS TSO/E User’s Guide, andthe z/OS ISPF User’s Guide. These guides describe how to navigate each interface,including the use of keyboard shortcuts or function keys (PF keys). Each guideincludes the default settings for the PF keys and explains how to modify theirfunctions.

Related accessibility information

Online documentation for DB2 Version 9.1 for z/OS is available in the InformationManagement Software for z/OS Solutions Information Center, which is available atthe following Web site: http://publib.boulder.ibm.com/infocenter/dzichelp

IBM and accessibility

See the IBM Accessibility Center at http://www.ibm.com/able for more informationabout the commitment that IBM has to accessibility.

How to send your commentsYour feedback helps IBM to provide quality information. Please send anycomments that you have about this book or other DB2 for z/OS documentation.You can use the following methods to provide comments:v Send your comments by e-mail to db2zinfo@us.ibm.com and include the name

of the product, the version number of the product, and the number of the book.If you are commenting on specific text, please list the location of the text (forexample, a chapter and section title or a help topic title).

v You can send comments from the Web. Visit the DB2 for z/OS - TechnicalResources Web site at:

http://www.ibm.com/support/docview.wss?&uid=swg27011656

This Web site has an online reader comment form that you can use to sendcomments.

v You can also send comments by using the feedback link at the footer of eachpage in the Information Management Software for z/OS Solutions InformationCenter at http://publib.boulder.ibm.com/infocenter/db2zhelp.

How to read syntax diagramsCertain conventions apply to the syntax diagrams that are used in IBMdocumentation.

Apply the following rules when reading the syntax diagrams that are used in DB2for z/OS documentation:v Read the syntax diagrams from left to right, from top to bottom, following the

path of the line.The ��─── symbol indicates the beginning of a statement.The ───� symbol indicates that the statement syntax is continued on the nextline.The �─── symbol indicates that a statement is continued from the previous line.The ───�� symbol indicates the end of a statement.

v Required items appear on the horizontal line (the main path).

About this information xix

�� required_item ��

v Optional items appear below the main path.

�� required_itemoptional_item

��

If an optional item appears above the main path, that item has no effect on theexecution of the statement and is used only for readability.

��optional_item

required_item ��

v If you can choose from two or more items, they appear vertically, in a stack.If you must choose one of the items, one item of the stack appears on the mainpath.

�� required_item required_choice1required_choice2

��

If choosing one of the items is optional, the entire stack appears below the mainpath.

�� required_itemoptional_choice1optional_choice2

��

If one of the items is the default, it appears above the main path and theremaining choices are shown below.

�� required_itemdefault_choice

optional_choiceoptional_choice

��

v An arrow returning to the left, above the main line, indicates an item that can berepeated.

�� required_item � repeatable_item ��

If the repeat arrow contains a comma, you must separate repeated items with acomma.

�� required_item �

,

repeatable_item ��

A repeat arrow above a stack indicates that you can repeat the items in thestack.

v Sometimes a diagram must be split into fragments. The syntax fragment isshown separately from the main syntax diagram, but the contents of thefragment should be read as if they are on the main path of the diagram.

xx Administration Guide

|||

�� required_item fragment-name ��

fragment-name:

required_itemoptional_name

v With the exception of XPath keywords, keywords appear in uppercase (forexample, FROM). Keywords must be spelled exactly as shown. XPath keywordsare defined as lowercase names, and must be spelled exactly as shown. Variablesappear in all lowercase letters (for example, column-name). They representuser-supplied names or values.

v If punctuation marks, parentheses, arithmetic operators, or other such symbolsare shown, you must enter them as part of the syntax.

About this information xxi

||||||||||||||

|

||||||||||||||||

|

|||

xxii Administration Guide

Part 1. Designing a database

© Copyright IBM Corp. 1982, 2009 1

2 Administration Guide

Chapter 1. Database objects and relationships

The general tasks that are necessary to design a database are logical data modelingand physical data modeling.

In logical data modeling, you design a model of the data without paying attentionto specific functions and capabilities of the DBMS that will store the data. In fact,you could even build a logical data model without knowing which DBMS you willuse.

Physical data modeling begins when you move closer to a physicalimplementation. The primary purpose of the physical design stage is to optimizeperformance while ensuring the integrity of the data.

Logical database design with the entity-relationship modelBefore you implement a database, you should plan or design the database so thatit satisfies all requirements.

Designing and implementing a successful database, one that satisfies the needs ofan organization, requires a logical data model. Logical data modeling is the processof documenting the comprehensive business information requirements in anaccurate and consistent format. Analysts who do data modeling define the dataitems and the business rules that affect those data items. The process of datamodeling acknowledges that business data is a vital asset that the organizationneeds to understand and carefully manage. This section contains information thatwas adapted from Handbook of Relational Database Design.

Consider the following business facts that a manufacturing company needs torepresent in its data model:v Customers purchase productsv Products consist of partsv Suppliers manufacture partsv Warehouses store partsv Transportation vehicles move the parts from suppliers to warehouses and then

to manufacturers

These are all business facts that a manufacturing company’s logical data modelneeds to include. Many people inside and outside the company rely oninformation that is based on these facts. Many reports include data about thesefacts.

Any business, not just manufacturing companies, can benefit from the task of datamodeling. Database systems that supply information to decision makers,customers, suppliers, and others are more successful if their foundation is a sounddata model.

Modeling your dataData analysts can perform the task of data modeling in a variety of ways.

Many data analysts follow these steps:

© Copyright IBM Corp. 1982, 2009 3

1. Build critical user views.a. Carefully examining a single business activity or function.b. Develop a user view, which is the model or representation of critical

information that the business activity requires.This initial stage of the data modeling process is highly interactive. Becausedata analysts cannot fully understand all areas of the business that they aremodeling, they work closely with the actual users. Working together,analysts and users define the major entities (significant objects of interest)and determine the general relationships between these entities.In a later stage, the analyst combines each individual user view with all theother user views into a consolidated logical data model.

2. Add keys to user viewsKey business rules affect insert, update, and delete operations on the data. Forexample, a business rule might require that each customer entity have at leastone unique identifier. Any attempt to insert or update a customer identifier thatmatches another customer identifier is not valid. In a data model, a uniqueidentifier is called a primary key.

3. Add detail to user views and validate them.a. Add other descriptive details that are less vital.b. Associate these descriptive details, called attributes, to the entities.

For example, a customer entity probably has an associated phone number.The phone number is a non-key attribute of the customer entity.

c. Validate all the user viewsTo validate the views, analysts use the normalization process and processmodels. Process models document the details of how the business will usethe data.

4. Determine additional business rules that affect attributes.a. Clarify the data-driven business rules.

Data-driven business rules are constraints on particular data values. Theseconstraints need to be true, regardless of any particular processingrequirements.The advantage to defining data-driven business rules during the datadesign stage, rather than during application design is that programmers ofmany applications don’t need to write code to enforce these business rules.For example, Assume that a business rule requires that a customer entityhave a phone number, an address, or both. If this rule doesn’t apply to thedata itself, programmers must develop, test, and maintain applications thatverify the existence of one of these attributes. Data-driven businessrequirements have a direct relationship with the data, thereby relievingprogrammers from extra work.

5. Integrate user views.a. Combine into a consolidated logical data model the newly created different

user views.b. Integrate other data models that already exist in the organization with the

new consolidated logical data model.At this stage, analysts also strive to make their data model flexible so that itcan support the current business environment and possible future changes. Forexample, assume that a retail company operates in a single country and thatbusiness plans include expansion to other countries. Armed with knowledge ofthese plans, analysts can build the model so that it is flexible enough tosupport expansion into other countries.

4 Administration Guide

Recommendations for logical data modelingTo build sound data models, analysts follow a well-planned methodology.

Follow these recommendation for building quality data models:v Work interactively with the users as much as possible.v Use diagrams to represent as much of the logical data model as possible.v Build a data dictionary to supplement the logical data model diagrams.

A data dictionary is a repository of information about an organization’sapplication programs, databases, logical data models, users, and authorizations.A data dictionary can be manual or automated.

Practical examples of data modelingTo better understand the key activities that are necessary for creating valid datamodels, investigate one or more real-life data modeling scenarios.

You begin by defining your entities, the significant objects of interest. Entities arethe things about which you want to store information. For example, you mightwant to define an entity, called EMPLOYEE, for employees because you need tostore information about everyone who works for your organization. You might alsodefine an entity, called DEPARTMENT, for departments.

Next, you define primary keys for your entities. A primary key is a uniqueidentifier for an entity. In the case of the EMPLOYEE entity, you probably need tostore a large amount of information. However, most of this information (such asgender, birth date, address, and hire date) would not be a good choice for theprimary key. In this case, you could choose a unique employee ID or number(EMPLOYEE_NUMBER) as the primary key. In the case of the DEPARTMENTentity, you could use a unique department number (DEPARTMENT_NUMBER) asthe primary key.

After you have decided on the entities and their primary keys, you can define therelationships that exist between the entities. The relationships are based on theprimary keys. If you have an entity for EMPLOYEE and another entity forDEPARTMENT, the relationship that exists is that employees are assigned todepartments. You can read more about this topic in the next section.

After defining the entities, their primary keys, and their relationships, you candefine additional attributes for the entities. In the case of the EMPLOYEE entity,you might define the following additional attributes:v Birth datev Hire datev Home addressv Office phone numberv Genderv Resume

Lastly, you normalize the data.

Entities for different types of relationshipsIn a relational database, you can express several types of relationships.

Chapter 1. Database objects and relationships 5

Consider the possible relationships between employees and departments. If a givenemployee can work in only one department, this relationship is one-to-one foremployees. One department usually has many employees; this relationship isone-to-many for departments. Relationships can be one-to-many, many-to-one,one-to-one, or many-to- many.

Subsections:v “One-to-one relationships”v “One-to-many and many-to-one relationships”v “Many-to-many relationships” on page 7v “Business rules for relationships” on page 7

The type of a given relationship can vary, depending on the specific environment.If employees of a company belong to several departments, the relationshipbetween employees and departments is many-to-many.

You need to define separate entities for different types of relationships. Whenmodeling relationships, you can use diagram conventions to depict relationshipsby using different styles of lines to connect the entities.

One-to-one relationships

When you are doing logical database design, one-to-one relationships arebidirectional relationships, which means that they are single-valued in bothdirections. For example, an employee has a single resume; each resume belongs toonly one person. The previous figure illustrates that a one-to-one relationship existsbetween the two entities. In this case, the relationship reflects the rules that anemployee can have only one resume and that a resume can belong to only oneemployee.

One-to-many and many-to-one relationships

A one-to-many relationship occurs when one entity has a multivalued relationshipwith another entity. In the following figure, you see that a one-to-manyrelationship exists between the two entities—employee and department. This figurereinforces the business rules that a department can have many employees, but thateach individual employee can work for only one department.

Employee ResumeA resume is owned

by an employee

An employeehas a resume

Figure 1. Assigning one-to-one facts to an entity

Employee DepartmentOne department can

have many employees

Many employees workfor one department

Figure 2. Assigning many-to-one facts to an entity

6 Administration Guide

Many-to-many relationships

A many-to-many relationship is a relationship that is multivalued in bothdirections. The following figure illustrates this kind of relationship. An employeecan work on more than one project, and a project can have more than oneemployee assigned.

Business rules for relationships

Whether a given relationship is one-to-one, one-to-many, many-to-one, ormany-to-many, your relationships need to make good business sense. Therefore,database designers and data analysts can be more effective when they have a goodunderstanding of the business. If they understand the data, the applications, andthe business rules, they can succeed in building a sound database design.

When you define relationships, you have a big influence on how smoothly yourbusiness runs. If you don’t do a good job at this task, your database and associatedapplications are likely to have many problems, some of which may not manifestthemselves for years.

Entity attributesWhen you define attributes for the entities, you generally work with the dataadministrator to decide on names, data types, and appropriate values for