DB2 10 for z/OS Database Administration: Certification Study Guide provides a comprehensive review of the topics covered on IBM's Certification Exam 612: DB2 10 for z/OS Database Administration. The book also covers important new features found in DB2 10 for z/OS. Chapter 16, "Locking and Concurrency," discusses topics such as lock attributes, avoiding locks, claims and drains, escalation and promotion, and designing for concurrency.
About authors
Susan Lawson — Susan Lawson is an internationally recognized consultant and lecturer with a background in system and database administration. She currently works with several large clients to help develop, implement, and tune some of the world's largest and most complex DB2 databases and applications. Susan also conducts performance and availability audits to help clients reduce costs through proper performance tuning and ensure availability. She also writes articles and white papers, presents at user group meetings, and teaches a variety of DB2 courses. Susan is an IBM GOLD Consultant for DB2 and System z and an IBM Champion.
Daniel Luksetich — Daniel Luksetich is a DB2 database professional with more than 26 years of experience in high-performance design and tuning of very large databases and highly available, high-volume database applications. He also teaches DB2 courses and has authored books, guides, whitepapers, and magazine articles. Working as a DB2 application architect, Dan has been involved in several very large DB2 application designs. An IBM Champion, Dan specializes in DB2 for z/OS but has worked with DB2 on a variety of platforms. His specialties include performance audits, SQL education, backup and recovery, stored procedures, UDFs, triggers, application design, high-availability design, and DB2 high performance.
Contents
Contents
Introduction
Chapter 1: DB2 Product Fundamentals DB2 and the On Demand Business The DB2 Product Family DB2 Middleware and Connectivity InfoSphere Optim Data Lifecycle Management for DB2 Additional DB2 Application Development and Administration Summary and Resources Practice Questions and Answers
Chapter 2: Environment z/OS DB2 for z/OS DB2 and Security Parallel Sysplex Support Storage Management Subsystem DB2 Interfaces Installation, Migration, and Conversion DSNZPARMs Commands DB2 Utilities Catalog and Directory Distributed Data Memory Management Summary and Resources Practice Questions and Answers
Chapter 3: Access and Security DB2 Subsystem Access Data Set Protection Encryption DB2 Object Access Multilevel Security Auditing Summary and Resources Practice Questions and Answers
Chapter 4: Database Objects Understanding Data Structures Managing Database Objects Data Types Tables Sequence Objects Table Spaces Views Materialized Query Tables Clone Tables Temporal Tables Hash Tables (Tables with Hash Access Enabled) Indexes Databases Storage Groups Database Design and Implementation Sample Implementation Summary and Resources Practice Questions and Answers
Chapter 5: Retrieving and Manipulating Database Objects Data Retrieval Data Modification Summary and Resources Practice Questions and Answers
Chapter 6: Advanced SQL Coding Subqueries Unions Excepts Intersects Nested Table Expressions Common Table Expressions Data Change Tables Joins ORDER BY and FETCH FIRST in Subselects and Fullselects CASE Expressions Row Expressions PureXML Usage OLAP Specification Predicates and Filtering Summary and Resources Practice Questions and Answers
Chapter 7: Maintaining Data Data Movement Data Maintenance Rebuilding Indexes Data Statistics Real-Time Statistics Data Maintenance Process Diagnose Utility Standalone Utilities Displaying Utilities Resolving Restrictive and Advisory States Summary and Resources Practice Questions and Answers
Chapter 8: Recovery and Restart Database Recovery Concepts Logging Image Copies Establishing a Point of Consistency Recovery Concepts System-Level Backup and Recovery Backup and Recovery of the DB2 Catalog and Directory Disaster Recovery LOB and XML Recovery Tracker Site Recovery DB2 Restart Data Sharing Recovery Summary and Resources Practice Questions and Answers
Chapter 9: Data Sharing Data Sharing Benefits Data Sharing Components Shared Data Maintaining Data Integrity Performance Processing Costs Movement to Data Sharing Workload Management and Affinity Processing Submitting Work Communicating with a Data Sharing Group Sysplex Query Parallelism Recovery Considerations Summary and Resources Practice Questions and Answers
Chapter 10: Using SQL in an Application Program Delimiting SQL in a Program Declaring Table and View Definitions SQL Execution Validation Using Cursors Executing SQL Statements Using Dynamic SQL ODBC JDBC, SQLJ, and pureQuery Summary and Resources Practice Questions and Answers
Chapter 11: Binding an Application Program Precompile and Bind Plan or Package Ownership Plan Execution Authorization Summary and Resources Practice Questions and Answers
Chapter 12: Application Program Features Commit, Rollback, and Savepoint Savepoints Global Transactions Global Temporary Tables Fetch for Limited Rows Multi-Row Operations Identity Columns Sequence Objects Optimistic Locking Temporal Data Management Summary and Resources Practice Questions and Answers
Chapter 13: Stored Procedures Stored Procedures Benefits Writing Stored Procedures Native SQL Procedures Defining Stored Procedures Removing Stored Procedures Schema Qualification Execution Environments SQL Procedure Language SQL and Java Procedure Development DB2-Supplied Stored Procedures Summary and Resources Practice Questions and Answers
Chapter 14: Accessing Distributed Data Distributed Data Communications Protocols Communications Database Communicating with a Data Sharing Group Coding Methods for Distributed Data Programming Considerations Application Design Options Remote Query Performance Summary and Resources Practice Questions and Answers
Chapter 15: Advanced Functionality Triggers Object-Relational Extensions User-Defined Functions Large Objects XML Summary and Resources Practice Questions and Answers
Chapter 16: Locking and Concurrency Locking Data Avoiding Locks System Parameters Claims and Drains Locking Issues and Problems Lock Promotion and Escalation Database and Application Design for Concurrency Lock Monitoring Summary and Resources Practice Questions and Answers
Chapter 17: Performance Monitoring and Tuning Access Paths and Optimization Access Path Evaluation Guidelines for Using Explain Output Access Path Optimization Hints Catalog Statistics Predicate Types Designing Indexes and SQL for Performance Dynamic SQL Query Parallelism Database Monitoring Tracing Problems in DB2 Using DISPLAY Commands Buffer Pools Logging Opening and Closing Data Sets Summary and Resources Practice Questions and Answers
