Database Administration

Craig S. Mullins is president and principal consultant of Mullins Consulting, a leading data and database management consultancy. Mullins has more than thirty years of experience in all facets of database administration and development, in industries ranging from manufacturing and finance to education and research. He covered database administration as research director for Gartner Group and authored DB2 Developer’s Guide, Sixth Edition, the number-one guide to DB2 for z/OS. Mullins publishes The Database Site (thedatabasesite.com) and presents at industry events worldwide. IBM named him Information Management Champion for his work in the DB2 community.

How to Use This Book xxxiii Acknowledgments xxxv

About the Author xxxvii







 

Chapter 1: What Is a DBA? 1

Why Learn Database Administration? 3

A Unique Vantage Point 4

The Management Discipline of Database Administration 9

Evaluating a DBA Job Offer 14

Database, Data, and System Administration 15

DBA Tasks 20

DBMS Release Migration 29

The Types of DBAs 31

Staffing Considerations 37

Multiplatform DBA Issues 42

Production versus Test 44

The Impact of Newer Technology on DBA 46

DBA Certification 56

The Rest of the Book 58

Review 58

 

Chapter 2: Creating the Database Environment 61

Defining the Organization’s DBMS Strategy 61

Installing the DBMS 75

Upgrading DBMS Versions and Releases 82

Database Standards and Procedures 92

DBMS Education 103

Summary 104

Review 104

Suggested Reading 105

 

Chapter 3: Data Modeling and Normalization 107

Data Modeling Concepts 108

The Components of a Data Model 113

Discovering Entities, Attributes, and Relationships 124

Conceptual, Logical, and Physical Data Models 125

What Is Normalization? 128

The Normal Forms 128

Normalization in Practice 135

Additional Data Modeling Issues 135

Summary 136

Review 137

Suggested Reading 138

 

Chapter 4: Database Design 141

From Logical Model to Physical Database 141

Database Performance Design 150

Denormalization 160

Views 175

Data Definition Language 177

Temporal Data Support 177

Summary 180

Review 181

Suggested Reading 182

 

Chapter 5: Application Design 185

Database Application Development and SQL 186

Defining Transactions 205

Locking 210

Batch Processing 221

Summary 222

Review 222

Suggested Reading 223

 

Chapter 6: Design Reviews 227

What Is a Design Review? 227

Types of Design Reviews 232

Design Review Output 239

Additional Considerations 240

Summary 241

Review 241

Suggested Reading 242

 

Chapter 7: Database Change Management 243

Change Management Requirements 244

Types of Changes 247

Impact of Change on Database Structures 250

Summary 262

Review 263

Suggested Reading 263

 

Chapter 8: Data Availability 265

Defining Availability 267

Cost of Downtime 271

Availability Problems 274

Ensuring Availability 287

Summary 296

Review 297

Suggested Reading 298

 

Chapter 9: Performance Management 299

Defining Performance 299

Service-Level Management 308

Types of Performance Tuning 311

Performance Tuning Tools 313

DBMS Performance Basics 315

Summary 316

Review 316

Suggested Reading 317

 

Chapter 10: System Performance 319

The Larger Environment 320

DBMS Installation and Configuration Issues 327

System Monitoring 345

Summary 346

Review 346

Suggested Reading 347

 

Chapter 11: Database Performance 349

Techniques for Optimizing Databases 349

Database Reorganization 365

Summary 371

Review 371

Suggested Reading 372

 

Chapter 12: Application Performance 373

Designing Applications for Relational Access 373

Relational Optimization 374

Additional Optimization Considerations 391

Reviewing Access Paths 394

SQL Coding and Tuning for Efficiency 399

Summary 407

Review 407

Suggested Reading 408

 

Chapter 13: Data Integrity 409

Types of Integrity 409

Database Structure Integrity 410

Semantic Data Integrity 414

Temporal Database Systems 444

Summary 446

Review 447

Suggested Reading 448

 

Chapter 14: Database Security 449

Data Breaches 449

Database Security Basics 451

Granting and Revoking Authority 456

Authorization Roles and Groups 466

Other Database Security Mechanisms 468

Encryption 470

SQL Injection 473

Auditing 477

External Security 478

DBMS Fixpacks and Maintenance 480

Summary 481

Review 481

Suggested Reading 482

 

Chapter 15: Regulatory Compliance and Database Administration 483

A Collaborative Approach to Compliance 486

Metadata Management, Data Quality, and Data Governance 488

Database Auditing and Data Access Tracking 490

Data Masking and Obfuscation 496

Database Archiving for Long-Term Data Retention 498

Closer Tracking of Traditional DBA Tasks 507

Summary 511

Review 511

Suggested Reading 512

 

Chapter 16: Database Backup and Recovery 515

The Importance of Backup and Recovery 515

Preparing for Problems 516

Backup 517

Recovery 537

Alternatives to Backup and Recovery 554

Summary 557

Review 557

Suggested Reading 558

 

Chapter 17: Disaster Planning 559

The Need for Planning 559

General Disaster Recovery Guidelines 563

Backing Up the Database for Disaster Recovery 569

Disaster Prevention 575

Summary 576

Review 576

Suggested Reading 577

 

Chapter 18: Data and Storage Management 579

Storage Management Basics 579

Files and Data Sets 583

Space Management 587

Fragmentation and Storage 595

Storage Options 596

Planning for the Future 608

Summary 609

Review 609

Suggested Reading 610

 

Chapter 19: Data Movement and Distribution 613

Loading and Unloading Data 614

EXPORT and IMPORT 622

Bulk Data Movement 623

Distributed Databases 626

Summary 633

Review 634

Suggested Reading 635

 

Chapter 20: Data Warehouse Administration 637

What Is a Data Warehouse? 637

Administering the Data Warehouse 640

Summary 658

Review 658

Suggested Reading 659

 

Chapter 21: Database Connectivity 661

Multitier, Distributed Computing 661

Network Traffic 670

Databases, the Internet, and the Web 675

Summary 681

Review 682

Suggested Reading 682

 

Chapter 22: Metadata Management 685

What Is Metadata? 685

Types of Metadata 689

Repositories and Data Dictionaries 691

Summary 696

Review 696

Suggested Reading 697

 

Chapter 23: DBA Tools 699

Types and Benefits of DBA Tools 699

Examine Native DBA Tools 728

Evaluating DBA Tool Vendors 729

Summary 733

Review 733

 

Chapter 24: DBA Rules of Thumb 735

Write Down Everything 735

Keep Everything 736

Automate! 737

Share Your Knowledge 739

Analyze, Simplify, and Focus 741

Don’t Panic! 742

Measure Twice, Cut Once 743

Understand the Business, Not Just the Technology 743

Don’t Become a Hermit 745

Use All of the Resources at Your Disposal 745

Keep Up-to-Date 746

Invest in Yourself 747

Summary 748

Final Exam 748

 

Appendix A: Database Fundamentals 753

What Is a Database? 753

Why Use a DBMS? 754

Summary 759

 

Appendix B: The DBMS Vendors 761

The Big Three 762

The Second Tier 763

Other Significant Players 763

Open-Source DBMS Offerings 764

Nonrelational DBMS Vendors 765

NoSQL DBMS Vendors 765

Object-Oriented DBMS Vendors 766

PC-Based DBMS Vendors 766

 

Appendix C: DBA Tool Vendors 769

The Major Vendors 769

Other DBA Tool Vendors 770

Data Modeling Tool Vendors 771

Repository Vendors 772

Data Movement and Business Intelligence Vendors 773

 

Appendix D: DBA Web Resources 775

Usenet Newsgroups 775

Mailing Lists 776

Web Sites, Blogs, and Portals 778

 

Appendix E: Sample DBA Job Posting 785

Job Posting 785

 

Bibliography 793

Database Management and Database Systems 793

Data Administration, Data Modeling, and Database Design 799

Database Security, Protection, and Compliance 802

Data Warehousing 804

SQL 805

Object Orientation and Database Management 807

Operating Systems 807

Related Topics 808

DB2 812

IMS 813

MySQL 813

Oracle 814

SQL Server 815

Sybase 816

Other Database Systems 817



 

Glossary 819



Index 853