Transcription
DB2 Universal Database for z/OS Version 8Administration GuideSC18-7413-07
DB2 Universal Database for z/OS Version 8Administration GuideSC18-7413-07
NoteBefore using this information and the product it supports, be sure to read the general information under “Notices” on page1351.Eighth Edition, Softcopy Only (December 2008)This edition applies to Version 8 of IBM DB2 Universal Database for z/OS (DB2 UDB for z/OS), product number5625-DB2, and to any subsequent releases until otherwise indicated in new editions. Make sure you are using thecorrect edition for the level of the product.This softcopy version is based on the printed edition of the book and includes the changes indicated in the printedversion by vertical bars. Additional changes made to this softcopy version of the book since the hardcopy book waspublished are indicated by the hash (#) symbol in the left-hand margin. Editorial changes that have no technicalsignificance are not noted.This and other books in the DB2 UDB for z/OS library are periodically updated with technical changes. Theseupdates are made available to licensees of the product on CD-ROM and on the Web (currently atwww.ibm.com/software/data/db2/zos/library.html). Check these resources to ensure that you are using the mostcurrent information. Copyright International Business Machines Corporation 1982, 2008.US Government Users Restricted Rights – Use, duplication or disclosure restricted by GSA ADP Schedule Contractwith IBM Corp.
ContentsAbout this book . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxvWho should read this book. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxvTerminology and citations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxviHow to read the syntax diagrams . . . . . . . . . . . . . . . . . . . . . . . . . . . xxviAccessibility . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxviiiHow to send your comments . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxviiiSummary of changes to this book . . . . . . . . . . . . . . . . . . . . . . . xxixPart 1. Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1Chapter 1. System planning concepts . . . . . . . . . . . . . . . . . . . . . . . 3The structure of DB2. . . . . . . . . . . . . .Data structures . . . . . . . . . . . . . .System structures . . . . . . . . . . . . . .More information about data structures . . . . . .Control and maintenance of DB2 . . . . . . . . .Commands . . . . . . . . . . . . . . .Utilities . . . . . . . . . . . . . . . . .High availability. . . . . . . . . . . . . .More information about control and maintenance of DB2The DB2 environment . . . . . . . . . . . . .Address spaces . . . . . . . . . . . . . .DB2 lock manager . . . . . . . . . . . . .DB2 attachment facilities . . . . . . . . . . .DB2 and distributed data . . . . . . . . . . .DB2 and z/OS . . . . . . . . . . . . . .DB2 and the Parallel Sysplex . . . . . . . . .DB2 and the Security Server for z/OS . . . . . .DB2 and DFSMS. . . . . . . . . . . . . .More information about the z/OS environment . . . 3. 3. 7. 10. 11. 11. 11. 12. 13. 13. 13. 14. 14. 18. 19. 19. 19. 20. 20Part 2. Designing a database: advanced topics . . . . . . . . . . . . . . . . 23Chapter 2. Introduction to designing a database: advanced topics . . . . . . . . . . 25Chapter 3. Creating storage groups and managing DB2 data sets . . . . . . . . . . 27 Managing data sets with DB2 storage groups . . . .Creating DB2 storage groups . . . . . . . .Using SMS to manage DB2 storage groups . . . .Deferring allocation of DB2-managed data sets . .Extending DB2-managed data sets . . . . . . .Managing DB2 data sets with DFSMShsm . . . . .Migrating to DFSMShsm . . . . . . . . . .Recalling archive logs . . . . . . . . . . .Using DFSMShsm with the RECOVER utility . . .Using DFSMShsm with the BACKUP SYSTEM utilityManaging your own data sets . . . . . . . . .Requirements for your own data sets . . . . . .Using the DEFINE CLUSTER command . . . . .Extending user-managed data sets . . . . . . .Defining index space storage . . . . . . . . .Creating EA-enabled table spaces and index spaces . . Copyright IBM Corp. 1982, 2008.27293030313535363637373840414142iii
Chapter 4. Implementing your database design . . . . . . . . . . . . . . . . . . 43 Implementing databases . . . . . . . . . . . . . . . . . . .Implementing table spaces . . . . . . . . . . . . . . . . . .Creating a table space explicitly . . . . . . . . . . . . . . .Creating a table space implicitly . . . . . . . . . . . . . . .Choosing a page size . . . . . . . . . . . . . . . . . . .Choosing a page size for LOBs . . . . . . . . . . . . . . . .Implementing tables . . . . . . . . . . . . . . . . . . . .Distinctions between DB2 base tables and temporary tables . . . . . .Implementing table-controlled partitioning . . . . . . . . . . . .Implementing indexes . . . . . . . . . . . . . . . . . . . .Types of indexes. . . . . . . . . . . . . . . . . . . . .Using the NOT PADDED clause for indexes with varying-length columns .Using indexes to avoid sorts. . . . . . . . . . . . . . . . .Using schemas . . . . . . . . . . . . . . . . . . . . . .Creating a schema . . . . . . . . . . . . . . . . . . . .Authorization to process schema definitions . . . . . . . . . . .Processing schema definitions . . . . . . . . . . . . . . . .4344444545464848515454575858585959Chapter 5. Loading data into DB2 tables . . . . . . . . . . . . . . . . . . . . . 61Loading tables with the LOAD utility . . . . . .Making corrections after LOAD . . . . . . . .Loading data using the SQL INSERT statement . . .Inserting a single row . . . . . . . . . . .Inserting multiple rows . . . . . . . . . .Special considerations when using INSERT statementLoading data from DL/I . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .to load tables. . . . .61636364646465Chapter 6. Altering your database design . . . . . . . . . . . . . . . . . . . . . 67 Altering DB2 storage groups. . . . . . . . . . . .Letting SMS manage your DB2 storage groups . . . .Adding or removing volumes from a DB2 storage group .Altering DB2 databases . . . . . . . . . . . . .Altering table spaces . . . . . . . . . . . . . .Changing the space allocation for user-managed data sets .Dropping, re-creating, or converting a table space . . .Altering tables . . . . . . . . . . . . . . . .Adding a new column to a table . . . . . . . . .Altering the data type of a column . . . . . . . .Altering a table for referential integrity . . . . . . .Adding or dropping table check constraints . . . . .Adding a partition . . . . . . . . . . . . . .Altering partitions . . . . . . . . . . . . . .Registering or changing materialized query tables . . .Altering the assignment of a validation routine . . . .Altering a table for capture of changed data . . . . .Changing an edit procedure or a field procedure . . . .Altering the subtype of a string column . . . . . . .Altering the attributes of an identity column . . . . .Changing data types by dropping and re-creating the tableMoving a table to a table space of a different page size . .Altering indexes . . . . . . . . . . . . . . . .Adding a new column to an index . . . . . . . .Altering how varying-length index columns are stored . .Altering the clustering index . . . . . . . . . .Rebalancing data in partitioned table spaces . . . . .Dropping and redefining an index. . . . . . . . .Index versions . . . . . . . . . . . . . . .Altering views . . . . . . . . . . . . . . . .Altering stored procedures . . . . . . . . . . . .ivAdministration 929293939394949696
Altering user-defined functions . . . . . . . . . . .Moving from index-controlled to table-controlled partitioningChanging the high-level qualifier for DB2 data sets . . . .Defining a new integrated catalog alias . . . . . . .Changing the qualifier for system data sets . . . . . .Changing qualifiers for other databases and user data setsMoving DB2 data . . . . . . . . . . . . . . .Tools for moving DB2 data . . . . . . . . . . .Moving a DB2 data set . . . . . . . . . . . .Copying a relational database . . . . . . . . . .Copying an entire DB2 subsystem . . . . . . . . 96. 97. 98. 99. 99. 102. 105. 106. 108. 109. 109Chapter 7. Estimating disk storage for user data . . . . . . . . . . . . . . . . . 111Factors that affect storage . . . . . . . . .Calculating the space required for a table . . . .Calculating record lengths and pages . . . .Saving space with data compression . . . . .Estimating storage for LOBs . . . . . . .Estimating storage when using the LOAD utilityCalculating the space required for a dictionary . .Disk requirements . . . . . . . . . . .Virtual storage requirements . . . . . . .Calculating the space required for an index . . .Levels of index pages. . . . . . . . . .Estimating storage from number of index pages .111112113114114114116116117117117118Part 3. Security and auditing . . . . . . . . . . . . . . . . . . . . . . . . 123Chapter 8. Introduction to security and auditing in DB2 . . . . . . . . . . . . . . 127Reading strategies for security. . . .If you are new to DB2 . . . . .If you have used DB2 before . . . Reading strategies for auditing . . .Controlling data access . . . . . .Access control within DB2 . . . .#Controlling access to a DB2 subsystemData set protection . . . . . .127127128128128129131132Chapter 9. Controlling access to DB2 objects . . . . . . . . . . . . . . . . . . 133Explicit privileges and authorities . . . . . . . . . .Authorization IDs . . . . . . . . . . . . . . .Granting explicit privileges . . . . . . . . . . . .Administrative authorities . . . . . . . . . . . .Field-level access control by views . . . . . . . . .Authority over the catalog and directory . . . . . . .Implicit privileges of ownership . . . . . . . . . . .Establishing ownership of objects with unqualified names .Establishing ownership of objects with qualified names . .Privileges by type of object . . . . . . . . . . . .Granting implicit privileges . . . . . . . . . . .Changing ownership . . . . . . . . . . . . . .Privileges exercised through a plan or a package . . . . .Establishing or changing ownership of a plan or a package .Qualifying unqualified names . . . . . . . . . . .Authorization to execute . . . . . . . . . . . .#Controls in the program . . . . . . . . . . . . .Privileges required for remote packages . . . . . . .Access control for user-defined functions and stored proceduresAdditional authorization for stored procedures . . . . .Controlling access to catalog tables for stored procedures. 53154155156156Contentsv
#### #Example of roles and authorizations for a routine . . . . .Which IDs can exercise which privileges . . . . . . . . .Authorization for dynamic SQL statements . . . . . . .Composite privileges . . . . . . . . . . . . . . .Multiple actions in one statement. . . . . . . . . . .Matching job titles with privileges . . . . . . . . . . .Examples of granting and revoking privileges . . . . . . .Examples using the GRANT statement . . . . . . . . .Examples with secondary IDs . . . . . . . . . . . .Examples using the REVOKE statement . . . . . . . .Finding catalog information about privileges . . . . . . . .Retrieving information in the catalog . . . . . . . . .Creating views of the DB2 catalog tables . . . . . . . .Multilevel security. . . . . . . . . . . . . . . . .Introduction to multilevel security . . . . . . . . . .Implementing multilevel security with DB2 . . . . . . .Working with data in a multilevel-secure environment . . .Implementing multilevel security in a distributed environment .Data encryption through built-in functions . . . . . . . .Defining columns for encrypted data . . . . . . . . .Defining encryption at the column level . . . . . . . .Defining encryption at the value level . . . . . . . . .Ensuring accurate predicate evaluation for encrypted data . .Encrypting non-character values . . . . . . . . . . .Performance recommendations for data encryption . . . . 99206207208208210211211212Chapter 10. Controlling access through a closed application . . . . . . . . . . . . 215Registration tables . . . . . . . . . . . . . . . . .Columns of the ART . . . . . . . . . . . . . . .Columns of the ORT . . . . . . . . . . . . . . .Controlling data definition . . . . . . . . . . . . . .Installing data definition control support . . . . . . . .Controlling data definition by application name . . . . . .Controlling data definition by application name with exceptionsControlling data definition by object name . . . . . . .Controlling data definition by object name with exceptions . .Registering sets of objects . . . . . . . . . . . . .Managing the registration tables and their indexes . . . . . .Creating the tables and indexes . . . . . . . . . . .Adding columns . . . . . . . . . . . . . . . .Updating the tables . . . . . . . . . . . . . . .Stopping data definition control . . . . . . . . . . .Chapter 11. Controlling access to a DB2 subsystemControlling local requests . . . . . . . . . . . . .Processing connections . . . . . . . . . . . . . .Steps in processing connections . . . . . . . . . .Supplying secondary IDs for connection requests . . . .Required CICS specifications . . . . . . . . . . .Processing sign-ons . . . . . . . . . . . . . . .Steps in processing sign-ons . . . . . . . . . . .Supplying secondary IDs for sign-on requests . . . . .Controlling requests from remote applications . . . . . .Overview of security mechanisms for DRDA and SNA . .The communications database for the server . . . . . .Controlling inbound connections that use SNA protocols . .Controlling inbound connections that use TCP/IP protocols .Planning to send remote requests. . . . . . . . . . .The communications database for the requester . . . . .What IDs you send . . . . . . . . . . . . . .viAdministration 8. . . . . . . . . . . . . . . 57
#####Translating outbound IDs . . . . . . . . .Sending passwords . . . . . . . . . . .Establishing RACF protection for DB2 . . . . . .Defining DB2 resources to RACF . . . . . . .Permitting RACF access . . . . . . . . . .Issuing DB2 commands . . . . . . . . . .Establishing RACF protection for stored proceduresEstablishing RACF protection for TCP/IP . . . .Establishing Kerberos authentication through RACF .Other methods of controlling access . . . . . . .Chapter 12. Protecting data sets through RACFAdding groups to control DB2 data sets . . . .Creating generic profiles for data sets . . . . .Permitting DB2 authorization IDs to use the profilesAllowing DB2 authorization IDs to create data sets .260262264265267274274278278279. . . . . . . . . . . . . . . . . 281.281281283283Chapter 13. Auditing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 285######Using the audit trace . . . . . . . . . . . . . .Starting the audit trace . . . . . . . . . . . .Stopping the audit trace . . . . . . . . . . . .Audit class descriptions . . . . . . . . . . . .Limitations of the audit trace . . . . . . . . . .Auditing in a distributed data environment . . . . .Auditing a specific table. . . . . . . . . . . .The role of authorization IDs in auditing . . . . . . .Auditing specific IDs . . . . . . . . . . . . .Determining which IDs hold privileges and authorities .Using audit records . . . . . . . . . . . . . .The contents of audit records . . . . . . . . . .Formatting audit records . . . . . . . . . . .Suggested audit trace reports . . . . . . . . . .Using other sources of audit information . . . . . . .Determining which security measures are enabled . . . .Ensuring data accuracy and consistency . . . . . . .Ensuring that the required data is present . . . . . .Ensuring that data is unique . . . . . . . . . .Ensuring that data fits a pattern or value range . . . .Ensuring that data is consistent . . . . . . . . .Ensuring that changes are tracked . . . . . . . .Ensuring that concurrent users access consistent data . .Checking for lost and incomplete transactions . . . .Determining whether data is consistent. . . . . . . .Automatically checking the consistency of data . . . .Submitting SQL queries to check data consistency . . .Checking data consistency with the CHECK utility . . .Checking data consistency with the DISPLAY DATABASEChecking data consistency with the REPORT utility . .Checking data consistency with the operation log . . .Using internal integrity reports to check data consistency. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .command . . . . . . . . . pter 14. A sample security plan for employee data . . . . . . . . . . . . . . . 301Securing manager access . . . . . . . . . .Granting the SELECT privilege to managers . . .Securing distributed access . . . . . . . . .Auditing manager use . . . . . . . . . .Securing payroll operations access . . . . . . .Securing compensation updates . . . . . . .Additional controls for compensation updates . .Granting privileges to payroll operations and payroll. . . . . . . . . . . . . . . . . . . . . . . . . . . . .management.Contents.301302303304305305306307vii
Auditing payroll operations and payroll management . . .Securing administrator, owner, and other access . . . . . .Securing access by IDs with database administrator authoritySecuring access by IDs with system administrator authority .Securing access by owners with implicit privileges on objectsSecuring access by other users. . . . . . . . . . .307308308308309310Part 4. Operation and recovery . . . . . . . . . . . . . . . . . . . . . . . 311Chapter 15. Basic operation . . . . . . . . . . . . . . . . . . . . . . . . . . 317Entering commands . . . . . . . . .DB2 operator commands . . . . . .Authorities for DB2 commands . . . .Starting and stopping DB2 . . . . . . .Starting DB2. . . . . . . . . . .Stopping DB2 . . . . . . . . . .Submitting work . . . . . . . . . .Using DB2I (DB2 Interactive) . . . . .Running TSO application programs . . .Running IMS application programs . . .Running CICS application programs. . .Running batch application programs . .Running application programs using CAF .Running application programs using RRSAFReceiving messages . . . . . . . . .Receiving unsolicited DB2 messages . . .Determining operational control . . . . 32Chapter 16. Scheduling administrative tasks . . . . . . . . . . . . . . . . . . . 335Interacting with the administrative task scheduler . . . . . . . . . . . . . .Adding a task . . . . . . . . . . . . . . . . . . . . . . . . .Listing scheduled tasks . . . . . . . . . . . . . . . . . . . . . .Listing the last execution status of scheduled tasks . . . . . . . . . . . . .Removing a scheduled task. . . . . . . . . . . . . . . . . . . . .Manually starting the administrative task scheduler . . . . . . . . . . . .Manually stopping the administrative task scheduler . . . . . . . . . . . .Synchronization between administrative task schedulers in a data sharing environmentTroubleshooting the administrative task scheduler . . . . . . . . . . . . .Architecture of the administrative task scheduler . . . . . . . . . . . . . .The lifecycle of the administrative task scheduler . . . . . . . . . . . . .Scheduler task lists . . . . . . . . . . . . . . . . . . . . . . .Architecture of the administrative task scheduler in a data sharing environment . . .Security guidelines for the administrative task scheduler . . . . . . . . . . . .User roles in the administrative task scheduler . . . . . . . . . . . . . .Protection of the interface of the administrative task scheduler . . . . . . . . .Protection of the resources of the administrative task scheduler . . . . . . . .Secure execution of tasks in the administrative task scheduler . . . . . . . . .Execution of scheduled tasks in the administrative task scheduler . . . . . . . . .Multi-threading in the administrative task scheduler . . . . . . . . . . . .Scheduled execution of a stored procedure . . . . . . . . . . . . . . .How the administrative task scheduler works with Unicode. . . . . . . . . .Scheduled execution of a JCL job . . . . . . . . . . . . . . . . . . .Execution of scheduled tasks in a data sharing environment. . . . . . . . . 60361362362363364364365Chapter 17. Monitoring and controlling DB2 and its connections . . . . . . . . . . 367Controlling DB2 databasesStarting databases . .Monitoring databases.Stopping databases .Altering buffer pools .viiiAdministration Guideand buffer pools. . . . . . . . . . . . . . . . . . . . .367368369375376
Monitoring buffer pools . . . . . . . .Controlling user-defined functions . . . . .Starting user-defined functions . . . . .Monitoring user-defined functions . . . .Stopping user-defined functions . . . . .Controlling DB2 utilities . . . . . . . . .Starting online utilities . . . . . . . .Monitoring online utilities . . . . . . .Stand-alone utilities . . . . . . . . .Controlling the IRLM. . . . . . . . . .Starting the IRLM . . . . . . . . . .Modifying the IRLM . . . . . . . . .Monitoring the IRLM connection . . . . .Stopping the IRLM . . . . . . . . .Monitoring threads . . . . . . . . . .Controlling TSO connections . . . . . . .Connecting to DB2 from TSO . . . . . .Monitoring TSO and CAF connections . . .Disconnecting from DB2 while under TSO. .Controlling CICS connections . . . . . . .Connecting from CICS . . . . . . . .Controlling CICS connections . . . . . .Disconnecting from CICS . . . . . . .Controlling IMS connections . . . . . . .Connecting to the IMS control region . . .Controlling IMS dependent region connectionsDisconnecting from IMS . . . . . . . .Controlling RRS connections . . . . . . .Connecting to RRS using RRSAF . . . . .Monitoring RRSAF connections . . . . .Controlling connections to remote systems . .Starting the DDF . . . . . . . . . .Suspending and resuming DDF server activityMonitoring connections to other systems . .Monitoring and controlling stored proceduresUsing NetView to monitor errors . . . . .Stopping the DDF . . . . . . . . . .Controlling traces . . . . . . . . . . .Controlling the DB2 trace . . . . . . .Diagnostic traces for attachment facilities . .Diagnostic trace for the IRLM . . . . . .Controlling the resource limit facility (governor).Changing subsystem parameter values . . . 406417420421422423424424425425Chapter 18. Managing the log and the bootstrap data set . . . . . . . . . . . . . 427 How database changes are made . . . . . . .Units of recovery . . . . . . . . . . .Rolling back work . . . . . . . . . . .Establishing the logging environment . . . . .Creation of log records . . . . . . . . .Retrieval of log records . . . . . . . . .Writing the active log. . . . . . . . . .Writing the archive log (offloading) . . . . .Controlling the log . . . . . . . . . . .Archiving the log . . . . . . . . . . .Dynamically changing the checkpoint frequency.Monitoring the system checkpoint . . . . .Setting limits for archive log tape units . . . .Displaying log information . . . . . . . .Resetting the log RBA . . . . . . . . . .Log RBA range . . . . . . . . . . . .
Note: These DB2 storage groups are not the same as storage groups that are defined by the DFSMS storage management subsystem (DFSMSsms). You have several options for managing DB2 data sets: v Let DB2 manage the data sets. This option means less work for DB2 database administrators. See "Managing data sets with DB2 storage groups" for more
