661

AAbbreviations, in database naming conventions,

78–79Absolute positioning, 329Access

authorized, 385concurrent, 416–418and DBMS, 624of distributed data, 509–510unauthorized, 402

Accessibility, of DBAs, 611Access path choices, 326–335

hashed access, 334indexed access, 328–334parallel access, 334–335table scans, 327

Access paths, 161, 199forcing, 341–343reviewing, 338–343

Accounts, 387, 389ACF2 (Computer Associates), 24, 386ACID (atomicity, consistency, isolation, and durability),

properties,171–173,176Acquire/release specification, 183–184

Action items, and design reviews, 201Active database logs, dual copies for, 424Active databases, 366Active logs, 420, 479

DBMS, 62, 63and point of consistency, 418–419

Active system catalog, 567ActiveX Data Objects, 24, 167

OLE DB and, 168Adabas (Software AG), 7, 50, 52, 627Adamski, Joseph J., 624Adaptive Ltd., 635Adaptive Server Anywhere, 45AD/Cycle (IBM), 573Add-on tools, database security, 586Ad hoc SQL, 169Adjectives, tracking those used by business experts,

104Adjust tables, in index record, 477ADLC. See Application development life cycleADO. See ActiveX Data ObjectsAdvanced Queueing feature (Oracle), 504Advanced reorganization tools, 238Advanced SQL class, 85

Index

Note: Italicized page locators refer to tables/figures.

index_661-703.qxd 5/22/02 2:34 PM Page 661

Advisory tools, 262Agents, 589Aggregate records, 530“Airline magazine syndrome,”222AIX (IBM), 74Algorithms, randomizing, 136Allen Systems Group, 632, 635Allied agents, and system performance, 269–270Allied agent software, 292Allocation units, 474ALLOW UPDATES parameter, 290ALL privilege, granting, 392ALTER statement, 154, 210, 213, 214, 215, 241, 582

and DDL script, 217and free space, 305limitations of, 211–213and relational database modification, 579and space management, 473

ALTER TABLE statement, 375, 376ALTER tools, functions of, 580American National Standards Institute,Web site

for, 644Analysis

and change management, 205, 206of environment, 610and performance management, 255of tasks, 609

Analysis tools, for DBMS, 53Analytical processing, transaction processing vs.,

516–518AND logical operator

and check conditions, 360and multi-index access, 332

Animation, with Java, 554ANSI. See American National Standards InstituteAnti-SQL, and UNDO recovery, 435APIs. See Application Programming InterfacesApplets, 554, 555Application-centric repository products, 570Application code design review

participants in, 199–200and SQL, 199–200

Application code generators, warning about, 348Application component metadata, 571Application data files, and Oracle tablespaces, 272Application DBAs

focus of, 28pros/cons on staffing of, 27–29reporting structure, 34

Application design, 159–186batch processing, 185–186database application development and SQL,160–171

defining transactions, 171–175locking, 176–185

Application developers, 320and database change requests, 218working with, 611

Application development life cycle, 7, 8, 9–10, 189cost of performance problems across, 256design reviews in, 194, 195

Application development personnel, in designreview, 192

Application-enforced relationships, 418Application failures, 408, 437Application integration, 503Application interfaces, and XML, 551Application migration and turnover procedures,

83–84Application performance, 319–349

additional optimization considerations, 335–338designing applications for relational access,

319–320relational optimization, 320–335reviewing access paths, 338–343SQL coding and tuning for efficiency, 343–349tools, 590–592

Application performance tuning, and indexes, 298Application problems, and availability problems,

233–234Application program, and SQL analysis, 592Application Programming Interfaces

SQL middleware and, 165–166and transaction processing systems, 173

Applications, 249and change, 209database administrators and type of, 32database administrator staffing and number of, 31disaster recovery plan and ranking of, 449–450

Application servers, 66, 175, 539Application test beds, maintaining, 500–501Application tuning, 261ARCH. See ArchiverArchitect, and metadata management, 573Architecture

DBMS, 56–57of Oracle, 272–273and recovery, 409

Archive logs, 232, 285amount of time on disk, 479DBMS, 62, 63file protection, 404

Archiver, 273ASCII, 499Assembler, 161, 165

662 Index

index_661-703.qxd 5/22/02 2:34 PM Page 662

Associative entities, 107, 108ATMs, and distributed data management model, 544Atomic data, 109Atomicity, 171–172, 623Attribute class words, sample, 99Attributes, 97–101, 117

attribute naming guidelines, 98–99domains, 98identifying, relating, and describing by, 97nulls and lack of value, 100–101transformation of, to columns, 122–123

Attribute values, 99Audit data files, protecting, 404Auditing, 401–403, 406, 583Auditing tools, 579, 583–584Audit records, selective creation of, 402Authentication, 386Authority, granting and revoking, 390–396Authorization, 585. See also Security

availability and problems with, 234database, 20dropping, 579granting to PUBLIC, 394groups, 398–399for recovery operations, 453, 456roles, 397and UNLOAD utility, 498

Authorized users, unauthorized access by, 402Automated rollback, restart, and recovery, 5Automation, 242. See also Technology

of backup and recovery procedures, 439and change management, 205, 206of code review process, 591and database reorganization, 315–316by DBAs, 608of routine maintenance tasks, 549of security, 386

Autonomy, distributed databases and, 505, 506Availability, 466

around-the-clock, 316, 408and change management, 206and clustering, 57, 59components of, 223and database administrators, 17, 19–20defining, 222–226downtime vs., 228ensuring, 239–245full-time, 225increased requirements for, 223–224and Internet-connected databases, 548–549and Java, 556need for, 221–222

problems, 246and recovery strategy, 438and redoing transactions, 436and service level, 258sufficiency of, 227–228

Availability requirements, for database administrators,31

Available pages, in data caches, 279AVG function, 530

BBachmann diagramming technique, 94Background mode, 588Background processes, Oracle, 273Backlogs, 168Backup, 538. See also Disaster recovery; Image copy

backups; Recoveryconsistency, 23, 418–419database, 240and database administration standards, 81and database administrators, 17, 21of database object definitions, 426–427and database server, 542for data warehouse, 533–534DBMS instance, 423–424documenting strategy for, 426files, 353hot and cold, 417, 44and log archiving, 419–420management, 242retention periods for, 409RMAN for, 417scheduling, 420, 422–423and solid state devices, 271storage management, 458–459tape, 456–458tools, 53, 592–593of transaction logs, 286

Backup and recovery alternatives, 443–445disk mirroring, 445replication, 444–445standby databases, 443–444

Backup-and-recovery DBAs, 29Backup copies

of database files and logs, 231and standby databases, 232storage management software for making, 425–426

BACKUP LOG command, 421BACKUP utility, 409, 593Backward log rolling, 433Bankruptcies, e-business, 9Batch jobs, 18

Index 663

index_661-703.qxd 5/22/02 2:34 PM Page 663

Batch processing, 185–186, 537and application performance, 320

Batch reporting, 589Batch scheduling software, 24Batch workload, and physical design review, 197BCNF. See Boyce Codd normal formBCP executions, 430Benchmarks

and DBMS products, 53TPC, 55

BETWEEN, 360Bitmap indexes, 129, 133–134, 527, 528Bitmaps, 474Blizzards, 448Blocks

and database locks, 176size of, 150, 309–310

BMC PATROL, 24BMC Software, 290, 548, 631Booch, Grady, 94Borland International, 627Boyce Codd normal form, 114Bridges, 24Broken blocks, recovering, 442Brokerages, and downtime, 226b-tree, 24B-tree indexes, 129

structure of, 132, 314B-tree structure, 352Budgets, 7. See also Costs

data warehousing, 523disk system evaluation, 485storage, 479

Buffer cache detail, and DBCC command, 355Buffer hit ratios, 257Buffer pools (or data caches), 63, 64, 276

efficiency of, 280hiperpools defined for, 281

Buffers, 19, 132Bugs, 68, 353, 437. See also Errors

and application outages, 234and availability, 246and corrupt data, 234and data loss, 236and DBMS software failure, 233and DBMS vendors, 72and declarative RI, 377and fallback, 75and locks, 179and operating system failure, 233and outages, 238in system software, 434

and UNLOAD data files, 500Bulk data movement, 502–504

ETL software, 502with messaging software, 503–504other methods of, 504with replication and propagation, 502–503

Bulk load operations, 286BULK LOAD utility, 380Bulk-logged recovery, 430Bundled query tools, 596Bursting pipes, 448Business, 207

and change management, 203–204and client/server computing, 538–539data at center of, 3and disaster recovery planning, 448and repositories, 571service interruptions, 449understanding, 610

Business-critical applications, 450Business descriptions, analyzing, 104Business intelligence, 515

tools, 595–597vendors, 636

Business logic, in client/server applications,540, 543

Business metadata, 566, 571Business Objects, 636Business process, and data models, 91Business rules, and check constraints, 360, 361, 365Bytecodes, Java, 556

CC, 24, 161, 165, 199, 269, 349, 597C++, 24, 165, 167, 261, 554CA-7, 405Cabling, 24Cache controllers, 251Caches, 19, 275, 278, 298CALL, and stored procedures, 366Callable routines, 166Call-level interface, 166Candidate keys, 97, 101, 106, 124, 130Candle Corporation, 290, 633Capacity, and data sharing, 245Capacity planning, 488–489, 578Capacity planning tools, 53, 262Capital letters, entity names with, 96Cardinality, 93, 103, 108, 134Cartesian products, avoiding, 346Cascading DELETEs, 368, 374, 377–378Cascading DROP effect, 210, 579

664 Index

index_661-703.qxd 5/22/02 2:34 PM Page 664

Cascading REVOKEs, 395eliminating, 586group-level security and, 399

Cascading UPDATEs, 366, 368, 373CASE tools, 165, 569, 573Catalog query and analysis tools, 579, 584–585Catalog visibility tools, 584CA Unicenter, 24Celko, Joe, 624Centralized administration, of privileges, 391Centralized data management model, 544Centralized processing, distributed processing

vs., 542Certification, DBA, 46–47Change management

and database administrators, 206–207, 219facets of, 203–204requirements, 204–206tools, 579–586

Change requests, standardized, 218–219Changes, 203, 204

effective migration of, 581examining impact of, 581proactive, 204repositories and control of, 569types of, 207–209

CHANGE scripts, 242Chargebacks, and data warehouses, 532–533CHECKALLOC option, 354Check conditions, 360Check constraints, 22, 78, 124, 127, 359–366

benefits with, 360–361for code and reference tables, 381and dropped objects, 442examples, 361–366and LOAD utility, 495and null, 363restrictions on creation of, 360RI vs., vs. program logic, 379–380

Checklists, checking, 218–219Checkpoint, background process, 273Checkpoint frequency, setting, 284Checkpoint log truncations, enabling, 421Checkpoint/restart tools, 598CHECK utility, DB2, 353Chen diagramming technique, 94Chicago Loop district, flood in, 448Chief Data Officer, data administrator as, 12Child tables, 365, 372, 381, 560CICS, 24, 173, 269CIFS, 487CKPT. See Checkpoint

Class words, attribute, 98, 99CLI. See Call-level interfaceClient computer, 540Client nodes, distributed data residing on, 506Client/server applications, software layers in, 540Client/server component layers, recommended

hardware for, 541Client/server computing, 537–547

and business issues, 538–539and database gateways, 546description of, 539–542historical look at, 537–538network traffic, 546–547types of applications with, 542–545

Client/server network configurations, 543Client/server processing, 541Clustered indexes, 129, 137, 302

accessing, 330and pointers, 477

Cluster failover, 230Clustering, 136–137, 243, 296, 302–305, 330

and database management systems, 57–59and database reorganization, 316and failover techniques, 20and interleaving data, 305and mirror tables, 143

Clustering technology, exploiting, 243–244Cluster ratios, 314, 315, 327Clusters, 77, 243

configuring to failover, 244database examples of, 244–245

COBOL, 24, 161, 165, 199, 261, 269, 349, 597CODASYL, 621

data models, 620, 621database models, 50

Codd, E. F., 108, 109Code generators, 165

warning about, 348Code review process, automation of, 591Code size, and DBCC command, 355Code tables, 126, 296Code walkthroughs, 592Coding, SQL, 343–349Cold backup, 417, 444ColdFusion, 269Collision resolution algorithm, 136Column-level check constraints, 362Columns, 77, 210, 212. See also Rows; Tables

adding to middle of table, 215–216and database locks, 176default values for, 359defined as integer, 22

Index 665

index_661-703.qxd 5/22/02 2:34 PM Page 665

Columns continuedand derivable data, 148functions, 40in horizontally split tables, 143and identity property, 125in indexes, 130lengths of, 476minimum number of, in SELECT list, 345nullable, 124, 363ordering, 125–126overstuffing, 560and redundant data, 146renaming, 214in reverse key indexes, 134and row size, 128rules defined for, 364for speed tables, 150splitting long text, 144–145from tables, 153transform attributes to, 122–123in vertically split tables, 143from views, 153

COM. See Component Object ModuleCombined tables, 145, 301COMMIT statement, 171, 174, 284

and batch programs, 185distributed two-phase, 510–511frequent issuance of, 348

COMMIT strategy, and application performance, 320COMMITTED READ isolation, 181, 182Communication

and database administrators, 2, 42and change management, 219and performance management, 264

Community, building, 609Company image, and downtime, 227Comparison tools, 217, 581–582Complexity, of DBMS environment, 71–72Component-based technologies, 24Component Object Module, 24, 167Compression, 128–129, 296, 306–307

data, 521–522and disaster recovery backups, 461tools, 262, 599–600trade-offs with, 307

Computer Associates, 52, 92, 570, 627, 629, 632, 634,635, 636

Computer-based training, 85Computer viruses, 448Conceptual data models, 14, 15, 17, 19, 92, 105, 106,

108Conceptual design review, 194, 195–196

Concurrency, 623and LOAD utility, 497and locking problems, 185and performance, 287problems, 176and UNLOAD utility, 498

Concurrent access, 416–418and lock granularity, 177

Conference on Data Systems Languages.See CODASYL

Conferences, 611config.ora files, and Oracle database files, 272Configuration options, 289–290Configuration parameters, impacts of, 290Configured memory, and DBCC command, 355Connections number, SP_MONITOR, 291Connectivity problems, 237Consistency, 172

backup, 418–419and check constraints, 361checking, 354in naming entities, 96

Constraints, 77, 382applying to columns, 123check, 359–366contradictory, 365and LOAD utility, 495naming, 78primary key, 356self-referencing, 375types of, 22unique, 357violations, 360

Consultant Web sites, 642Consumer spending patterns, 224Contention

and database performance, 19, 251and locking, 287–288

Contingency planning, 447, 448and change management, 205Web sites, 462

Contingency Planning and Research, 226Continuing education, through user groups, 612Contracts, vendor, 604Contradictory constraints, 365Control files, in Oracle databases, 272Control-M, 405Convergence, of features and functionality in DBMS

software, 55Conversions, advice on, 534Cooperative processing, 542Copybooks, 569

666 Index

index_661-703.qxd 5/22/02 2:34 PM Page 666

COPY command, 288COPY utility, 409

DB2 for OS/390 use of, 415, 416CORBA, 24Corporate data, searching for patterns in, 518Corporate databases, seamless access to, 538Corporate data security unit, DBMS security handled

by, 82Corporate passwords, standards for, 389Correlated subqueries, and report tables, 142Corrupt data, 234–235Costs

of clusters, 244and compression algorithm, 600CPU and I/O, 322and data warehouse projects, 532disaster recovery plans and minimizing of, 449of downtime, 226–227of indexes, 131justification of, 603and optimization, 337, 338of performance problems across application

development life cycle, 256and RAID storage solutions, 485and recovery process, 438with reorganization, 315with solid state devices, 271of upgrades, 69

COUNT function, 530COURSE Entity

in first normal form, 111in second normal form, 111, 112

CPUand availability problems, 230, 231costs, 322DBMS requirements, 61parallelism, 335

cpu busy number, SP_MONITOR, 291Crashes, recovery from, 238, 622CREATE database objects, 585CREATE INDEX, 286CREATE statement, 154, 210, 358

for EMP table, 362, 363and free space, 305

CREATE TABLE DDL statement, 358CREATE TABLE statement, 144, 375, 376Critical applications, 450Critical data, backing up, 420, 422Criticality, defining, 449Cross-platform portability, with Java, 554Currency, data types for, 358, 359Current run number, SP_MONITOR, 291

Cursors, 164, 182Customers,Web, 221, 222

DDAs. See Data administratorsDAFS. See Direct access file systemDAFS Collaborative, 488DAMA. See Data Management AssociationData, 2. See also Metadata

accessing from views, 335–336application by application copying of, 458–459availability of, 19backup and recovery of, 21at center of applications, 3compressing/decompressing, 128, 129, 306–307corruption of, 234–235denormalized, 521derived, 116, 148dropping, 579from, to knowledge, 564–565in global locations, 512growth of, 467at heart of e-business, 560interleaving, 137–138, 296, 305–306loading and unloading, 493–501loss of, 236movement of, 493nature and types of, 420, 422and new technology, 38–39on PDAs, 45privacy policies and, 9purging, 532redundant, 146remote mirroring of, 459replication of, 444–445requests for unnecessary, 345residing at server, 507sample, 118storage and size terminology, 468tampering with, 402unclustered, 310–311usage metadata, 572usage of, 531–532volatility, 422, 423

Data abstraction levels, and DBMS, 622Data access, 133

control of, 5and DBMS, 624

Data acquisition, consistent, 523Data administration, 12–15Data administration group, 1Data Administration Newsletter, 643

Index 667

index_661-703.qxd 5/22/02 2:34 PM Page 667

Data administration representatives, in designreview, 192

Data administration standards, 79, 81Data administrators, 12, 27, 56

database administrators contrasted with, 12, 13,14, 15, 16

responsibilities of, 17Data aggregation, and indexes, 298Data availability, 221–246, 466

defining, 222–226ensuring, 239–245

Data availability problems, 228–239. See alsoAvailability

application problems, 233–234database objects loss, 235–236data center loss, 229data corruption, 234–235data loss, 236data replication and propagation failures, 236–237DBA mistakes, 238DBMS software failure, 233disk-related outages, 231–232downtime costs, 226–228network problems, 229–230operating system failure, 233outages, 238–239recovery issues, 237–238security and authorization problems, 234server hardware loss, 230–231severe performance problems, 237

Database administration, 12, 15, 228challenge of, 4–5management discipline of, 7–11necessity for, 1and performance requirements, 32practice of, 607reason for learning, 3

Database Administration class, 85Database administration representatives, in design

review, 192Database administration standards, 81Database administration system versions/releases

complexity of DBMS environment, 71–72and DBA staff skill set, 73DBMS upgrade strategy, 75–76fallback planning, 74–75features and complexity, 71migration verification, 75and organization style, 73platform support, 74reputation of DBMS vendor, 72supporting software, 74

support policies of DBMS, 72–73upgrading, 67–76

Database administration tools, 24, 53Database administrator certification, information

sources on, 47Database administrator groups, DBMS decision mak-

ing by, 50, 51Database administrators, 56, 90, 123, 164, 213, 347

and application development life cycle, 8–10and applications changes, 209and architecture of Oracle, 272automating functions of, 242and availability, 19–20, 228–239, 246backup and recovery planning by, 446certification of, 46–47and change management, 206–207, 219communication between DA team, technicians,

and application programming staff via, 15communication skills needed by, 2data administrators contrasted with, 12, 13, 14,

15, 16database backup and recovery planning by, 407and database change implementation, 215–216and database design denormalization, 140and database development support, 66database gateway installation/configuration done

by, 546database objects created by, 393database-related Usenet newsgroups of interest

to, 640database security managed by, 386, 388database structures compared by, 216and data modeling, 89, 90and data warehouse administration, 534day in life of, 10–11and DBMS, 398DBMS performance basics for, 263–264in design reviews, 189disaster recovery plan updating by, 452on disaster recovery teams, 462and distributed environments, 506–508e-DBAs, 43, 45and enterprise performance management, 250and external security, 404and filegroups, 128formula for calculating level of effort by, 33functions of, 3and hardware configuration changes, 208and increased availability requirements, 223and indexing, 129, 135, 298–301Java code understood by, 557–558job offer evaluations for, 11

668 Index

index_661-703.qxd 5/22/02 2:34 PM Page 668

lack of understanding about, 1–2as leaders of design reviews, 191and locking levels, 176mean salary for full-time, 4and metadata management, 573mistakes by, 238and multiplatform environment management,35–36need for, 1new technology and, 38–46optimal number of, on staff, 30–33page sizes chosen by, 309–310and performance problems, 19personal digital assistant, 45–46procedural, 41, 42reporting structures, 33, 34responsibilities of, 17rules of thumb for, 607–613salaries for, 4–5security reporting by, 396space management by, 472special skills and, 186and SQL tuning and coding, 343–349staff experience, 32and standardized change requests, 218storage management by, 465storage options understood by, 490success for, 613and system administrators, 16and system performance, 268, 292tables mapped to tablespaces by, 127and technicians, 54and test and production, 37–38unique vantage points of, 3and XML, 553

Database administrator tasks, 16–25availability, 17, 19–20backup and recovery, 17, 21database design, 17–18database security and authorization, 17, 20–21data integrity, 17, 22–23DBMS release migration, 17, 23jack-of-all-trades, 23–25performance monitoring and tuning, 17, 18–19

Database administrator types, 25–30application DBA, 27–29database analyst, 26–27database architect, 26data modeler, 27data warehouse administrator, 30system DBA, 25–26task-oriented DBA, 29

Database alteration tools, 579

Database analysts, 26–27Database applications

design review phases for, 194development standards, 82optimal hardware environment for, 270

Database architects, tasks of, 26Database auditing tools, aspects captured by, 583Database backup and recovery, 240, 407–446

alternatives to, 424–426, 443–445image copy backups, 408–427preparing for problems, 407–408recovery, 427–443

Database change management, 203–219impact of change on database structures,

209–219requirements for, 204–207types of changes, 207–209

Database change management tools, 579–586auditing tools, 583–584catalog query and analysis tools, 584–585change management tools, 579–581database comparison tools, 581–582database object migration tools, 582purchasing, 213referential integrity tools, 582–583security tools, 585–586

Database changesapplication changes synchronized with, 209examples of, 214–216requesting, 217–218scenarios, 213–216standardized forms for implementing, 218

Database checking, 354Database clusters, and new DBMS versions, 69Database comparison tools, 579, 581–582Database connectivity, 537–561

client/server computing, 537–547Internet, the Web and, 547–561

Database descriptor, 277Database design, 121–156, 558–561, 612

and database administrators, 17–18, 27database performance design, 129–138and Data Definition Language, 154and denormalization, 138–152from logical data model to physical database,

121–129process, 189views, 152–154

Database disorganization, reasons for, 310–311Database environment creation, 49–86

choosing DBMS, 51–54database standards and procedures, 76–84

Index 669

index_661-703.qxd 5/22/02 2:34 PM Page 669

Database environment creation continuedDBMS architectures, 56–57DBMS clustering, 57–59DBMS education, 84–85DBMS proliferation, 59–60defining organization’s DBMS strategy, 49–50hardware issues, 60installing DBMS, 60–67upgrading DBMS versions and releases, 67–76

Database exports, for creating logical backups,424–425

Database failover, 230Database files

fault-tolerant RAID levels for, 486in Oracle database, 272temporary, 472

Database gateways, 546Database IDs, 390Database implementations, future planning for, 488Database instance failure, recovering from, 439Database integration, and eXtensible Markup

Language, 553Database kernels, 251, 588, 590Database logs, 283–287, 419

caches, 277configuration considerations, 285–286and DBMS, 623and disasters, 457information recorded in, 284placement of, 308

Database maintenanceand availability, 241and DBMS, 398

Database management, integrating with Web, 221Database management software, convergence of

features and functionality in, 55Database management systems, 164

and access paths, 161advantages with use of, 621–624and allied agents, 269–270amount of memory needed by, 278architectures, 56–57audit facilities, 402–403and availability, 19benefits with, 624caches, 298and change, 206choosing and installing, 49, 50, 51–52and client/server systems, 540, 543and clustering, 57–59, 303complexity of, 71–72components of, 271

and compression, 128control over backup and recovery by, 415–416database administrators and variety of, 33database logs in, 283database resources controlled by, 385data distribution standards supported by, 508–509and data integrity, 22and data interleaving, 137–138defined, 5direct index lookup performed by, 328–329and distributed database usage guidelines, 508distributed environment vs., 505distribution capabilities of, 506domains transformed to data types, 123education, 84–85environment, 66–67factors in choosing, 52–54fundamentals of, 619–624and index updates, 131instance backup and recovery for, 423–424knowledge of, by DBAs, 15in larger environment, 268and lock types, 178memory consumption by, 277–278modern disk usage, 466and multiplatform DBA issues, 35multiple, within organizations, 50page splitting with, 304–305parallelism types supported by, 334–334performance basics, 263–264and physical database design, 121proliferation of, 59–60reasons for using, 620–624relationship of, to databases, 6release migration and DBAs, 17, 23situations/commands not logged in, 286–287space allocation by, 599special physical implementation needs for, 150statistics collected by, 323storage requirements, 62–63support vs. exploit with versions/releases, 75understanding backup capabilities of, 417upgrade strategies for, 70–76vendors, 611, 612

Database management systems installation/configuration, 60–67, 274–290

basics of, 61configuring DBMS, 65connecting DBMS to supporting infrastructure

software, 65–66database logs, 283–287data cache details, 279–282

670 Index

index_661-703.qxd 5/22/02 2:34 PM Page 670

DBMS environments, 66–67hardware requirements, 61–62locking and contention, 287–288memory requirements, 63–65memory usage, 274–279“open”database objects, 282other configuration options, 289–290storage requirements, 62–63system catalog, 288–289types of, 274verification of, 66

Database management system software, 2and change, 207and complexity, 225failure, 233

Database management system strategychoosing DBMS, 51–54DBMS architectures, 56–57DBMS clustering, 57–59DBMS proliferation, 59–60defining for organization, 49–50hardware issues, 60

Database Modeling and Database Design class, 85Database naming conventions, 76–77

standard abbreviations, 78–79Database object definitions, backing up, 426–427Database object hierarchy, 211Database object migration tools, 582Database object privileges, 391, 392–393Database object recovery, general steps for, 429–431Database objects

and backups, 414–415, 420concurrent access to, 416and Data Definition Language, 154DBMS support for, 77and disaster recovery, 449and free space, 214, 306“free-standing,”364full image copy backups of, 411loss of, 235–236quick migration of, 582and recovery, 430, 440and security, 387small, 413

Database operations, logging, 286–287Database optimizer, 241Database optimizing techniques, 295–310

clustering, 302–305compression, 306–307denormalization, 301file placement and allocation, 307–309free space, 305–306

indexing, 298–301interleaving data, 305page size, 309–310partitioning, 295, 296–297raw partition vs. file system, 297–298

Database outages, 229Database performance, 18, 223, 295–316

and database reorganization, 310–316defining, 250–251design, 129–138and I/O dependence, 466roadmap, 251–253techniques for optimizing databases, 295–310tools, 588, 590tuning tips, 344

Database performance monitors, 238evaluating, 588–589

Database performance tuning, and indexes, 298Database portals, 642–643Database programming, in Microsoft environment,

166–167Database Programming class, 85Database-related Usenet newsgroups, 639

of interest to DBAs, 640Database reorganization, 239, 310–316

automating, 315–316scheduling, 314–315, 316

Databases, 3, 77, 249active, 366availability of, 19, 223backup and recovery, 242building from data model, 91database administrator staffing and number of, 31database administrator staffing and size of, 31and database locks, 176defined, 5description of, 619–620development of, 201, 569, 570and disaster recovery, 448, 456–461distributed, 505–512free space within, 128fundamentals of, 5growth in, 224hostile, 559, 560, 561hosting, 547index creation for, 131Internet, the Web, and, 547–561Internet-connected, 548–549Oracle, 272parallel, 527relationship of DBMS to, 6restoring, 231

Index 671

index_661-703.qxd 5/22/02 2:34 PM Page 671

Databases continuedsecurity, 585space usage by, 472storage of, in DBMS, 39“zapping,”355

Database security, 385–405auditing, 401–403authorization roles and groups, 397–399basics of, 386–390and database administrators, 17, 20–21external, 404–405granting and revoking authority, 390–397standards, 82–83stored procedures used for, 400–401threats to, 402views used for, 399–400

Database servers, 539amount of memory for, 279application logic coupled to, 39distributed data residing on, 506release upgrade strategy and location of, 71using, 174

Database standards and procedures, 76–84application migration and turnover procedures,

83–84data administration standards, 79, 81database administration standards, 81database application development standards, 82database naming conventions, 76–79database security standards, 82–83design review guidelines, 84operational support standards, 84roles and responsibilities, 79standard abbreviations, 78–79system administration standards, 82

Database statistics, and relational optimization,322–323

Database storage and data files, 469Database structure integrity, 351, 352–355Database structures

changing, 204comparing, 216–217impact of change on, 209–219mapping files to, 127

Database support roles and responsibilities, 80Database tables

bulk inserts of data into, 494mapping options for, 296

Database technology, 5–7Database tools, and database performance, 261Database tool vendors, selecting, 601–604Database transactions, logging of, 283

Database Trends and Applications,Web site for, 642

Database tuning, 260–261Database users, 389–390Database utilities, 53, 593–595Database views, 152–154Database writer, 273Data caches, 63, 275, 276, 279–281, 589

and free space, 306monitoring and tuning, 279–281read efficiency of, 280–281utilization statistics, 589

Data center, loss of, 229Data cleansing, 523–526, 528

data quality issues, 525operational problems, 526

Datacom, 50Data compression, 521–522, 527Data content, 531Data Control Language, 390Data Definition Language, 122, 154, 210, 217, 390

column-level constraints defined in, 362CREATE statements, 582recoveries and changes to, 409

Data definitions, data dictionaries and managementof, 573

Data degradation, 408Data dictionaries, 211, 215, 216–217, 573

query tools, 584tables, 570

Data distribution standards, 508–509Data elements, and repositories, 571Data encoding scheme, and UNLOAD utility, 499Data encryption software, 404Data feeds, failure of, 235Data files, Oracle database files, 272Data freshness, 530–531Data independence, and DBMS, 622Data integrity, 5, 351–382, 563, 620

and audit trails, 402and DA function, 15and database administrators, 17, 22–23database structure integrity, 352–355and data model, 91and DBMS, 623–624and index recovery, 440and locking, 176, 184and logical design review, 197maintaining, 544protecting, 524and recovery strategy, 437semantic data integrity, 355–382

672 Index

index_661-703.qxd 5/22/02 2:34 PM Page 672

and storage media, 466types of, 351–352

DataJoiner (IBM), 510Data Junction, 633, 636Data latency, and disaster recovery, 460Data lengths, 357, 358, 551Data loss

and error messages, 462minimizing, 451preventing, 468risks associated with, 449

Data management, 527Data marts, 225, 515

and bitmap indexes, 133defined, 516

Data mining, 517, 518Data mirroring, 481Data model components, 96–104

attributes, 97–101entities, 96–97keys, 101–102relationships, 102–104

Data modelers, 27Data modeling, 89, 104

advanced issues, 115–116concepts, 90–94and design tools, 578and homonyms/synonyms, 99, 100logical, 105metadata, 568, 571purpose of, 90tool vendors, 634–635

Data modeling terms, relationship of, 106Data models, 14, 15

conceptual, 105, 106, 108DBMS, 620enterprise, 91fragment, 104goal of, 91logical, 106, 108, 117, 155, 156multiple experts and development of, 105normalized, 108, 109, 114, 115physical, 108

Data movement, 522–523bulk, 502–504consistent data acquisition, 523and logical backups, 425vendors, 636

Data normalization, 90Data objects, changes to, 241Data ownership, 81Data ownership metadata, 568

Data page layouts, 473–476allocation pages, 474calculating table size, 476data record layouts, 474–476sample, 473

Data placement, in distributed systems, 507Data prefetch, 327Data quality

and DA function, 15issues, 525

Data recovery, auditing for, 403Data redundancy, and normalization, 108Data replication failures, 236–237Data resource management, 33

recommended reporting structure for, 35Data rows, in data page layout, 473Data scrubbing, 518, 523, 528Data security, 5, 621, 623Data semantics, defining, 551Data sets, 465

protecting, 404storage of, 468–471

Data sharing, and DB2 for z/OS, 245Data spaces, 127Data stewards, 566Data stewardship policy, 566Data synchronization technology, 46Data transmissions, failure of, 235Data types, 22, 357–359, 551

domains transformed to, 123–124user-defined, 77, 358–359

Data warehouse administration, 515–534backup and recovery, 533–534data cleansing, 523–526data content, 531data freshness, 530–531data movement, 522–523data usage, 531–532data warehouse design, 519–522data warehouse performance, 527–530data warehouse scalability, 526–527financial chargeback, 532–533

Data warehouse administratorsexperience of, 30and metadata management, 573

Data warehouses/warehousing, 52, 183, 224, 225,240, 643

and availability, 224beyond technology, 519and bitmap indexes, 133and change, 534complexity of, 518

Index 673

index_661-703.qxd 5/22/02 2:34 PM Page 673

Data warehouses/warehousing continuedand DBMS, 61as decision support vehicles, 528denormalizing design of, 521description of, 515–518design of, 519–522and ETL metadata, 568and mirror tables, 143and performance, 527–530purpose of, 522 and repositories, 571scalability of, 526–527technologies for, 30tools for, 30, 53

Data warehousing queries, and dirty reads, 183Date, C. J., 624DBADMs. See Database administratorsDBAs. See Database administratorsdBase, 629DBA tools, 74, 85, 238, 577–605

add-on, 632application tools, 590–592auditing tools, 583–584backup and recovery, 592–593business intelligence tools, 595catalog query and analysis tools, 584–585checkpoint/restart tools, 598for comparing database components, 216compression tools, 599–600database change management, 579–581database comparison tools, 581–582database object migration tools, 582database performance tools, 590database utility tools, 593–594data modeling and design, 578data warehousing tools, 595and DBA staffing, 33debugging tools, 598ETL tools, 595–596evaluating, 605homegrown, 604–605and multiplatform environment issues, 36online standards manuals, 600performance management, 587–588programming and development tools, 597–598query and reporting tools, 596–597referential integrity tools, 582–583security tools, 585–586space management tools, 599system performance tools, 588–590table editors, 587testing tools, 598

types and benefits of, 577–578utility management tools, 595various and new, 600–601

DBA tool vendors, 631–636evaluating, 601–604Web sites, 641

DBA Web resources, 637–645mailing lists, 639–640Usenet newsgroups, 637–638Web sites and portals, 641–645

DBAzine.com, 643DBCC CHECKALLOC(database_name), 354DBCC CHECKCATALOG(database_name), 354DBCC CHECKDB(database_name), 354DBCC CHECKTABLE(table_name), with DBCC

utility, 354DBCC command, and memory usage, 354DBCC MEMUSAGE, 354DBCC REINDEX(table_name), with DBCC utility, 354DBCC utility, 353

care in running of, 354consistency checking with, 354

DBD. See Database descriptorDBE Software, 633DBMAINT. See Database maintenanceDBMS Overview class, 84DBMSs. See Database management systemsDBMS vendors

Big Three, 625, 626nonrelational, 625, 629object-oriented, 625, 629open-source offerings, 625, 628–629PC-based, 625, 629–630reputation of, 72second-tier providers, 625, 626–627significant players, 625, 627–628support policies of, 72–73tier-1, 51tier-2, 52

DB2, 6, 8, 33, 50, 51, 52, 108, 280, 341, 502, 619,620, 643

archiving by transaction log by, 286certification program for, 46CHECK and REPAIR utilities, 353database modifications logged by, 125and data types, 358db2ckbkp operation, 411and DSN1COPY, 441, 442and EDM Pool, 64n.1, 277, 282hiperpools, 281links and news, 643LOB columns, 352, 476

674 Index

index_661-703.qxd 5/22/02 2:34 PM Page 674

and lock escalation, 184parameters provided by, 290and relational data model, 621Web site for, 641

DB2 Developer’s Guide (Mullins), 447DB2 Everyplace, 45, 57DB2 Extended Enterprise Edition, and shared-

nothing clustering, 59DB2 for OS/390, 289

COPY utility used by, 415, 416Data Sharing and Parallel Sysplex, 59and page sizes, 310STOGROUPS with, 470

DB2 for z/OS, 245DB2 magazine,Web site for, 642DB2 REORG utility, logging disabled with, 287DB2 UDB, DB2LOOK utility, 4426DBWR. See Database writerDCL. See Data Control LanguageDDL. See Data Definition LanguageDeadlines, for database change implementation, 218Deadlocks, 179, 180, 184, 185

detecting, 287, 288minimizing, 288

“Deadly embrace,”180Deallocation, 474Debugging tools, 582, 598Decentralized administration, of privileges, 391Decentralized user presentation model, 544Decimal data type, 123, 358Decision Support System, 517, 522Declarative referential constraints, 375, 376, 377Dedicated hardware, for database server, 547–548Default configuration options, avoiding, 290Default databases, 387Default data files, and Oracle tablespaces, 272Default language, 387Defaults, 77Default values

and DBMS software, 274standardizing, 524

Defragmentation, 224Degree, of relationship, 103DELETED table, 369DELETE privilege, granting, 392DELETE RI constraint, 365DELETE rule, 373–374DELETE statement, 66, 162, 185, 283, 371, 587, 593

and data modification, 310log records for, 419and transition tables, 370and triggers, 40, 366, 368

DELETE trigger, for referential constraints, 376Delivery, and change management, 206Demographics, 224Denormalization, 122, 138–152, 296

and database design, 560and database performance, 301documenting decisions for, 139–140, 151guidelines for, 139of hierarchies, 150need for, 138and performance, 152and repeating groups, 146and splitting long text columns, 144–145types of, 151, 521

Denormalized logical data models, avoiding, 141Denormalized tables, synchronizing with normalized

tables, 139Density, 323, 324Departmental DBMS, 56, 57Dependent tables, 379DEPT_NO primary key, 375Derived data, 116, 148, 301Design

database, 558–561data warehouse, 519–522

Design reviews, 189–201areas addressed by, 190conceptual, 194, 195–196description of, 189guidelines for, 84logical, 194, 196–197organizational, 194, 198–199output, 201participants in, 191–194physical, 194, 197–198post-implementation, 194, 200pre-implementation, 194, 200rules of engagement, 190–191SQL and application code, 194, 199–200types of, 194–200

Design review team, knowledge and skills requiredby, 193–194

Desktops, 249Determinants, defined, 115Development life cycle, 83Development tools, 597–598Devices, and database memory consumption, 277DFSMS backups, IBM, 425Dialup connections, 547DICE.com, 4Differential backup, 412Dimension, 517

Index 675

index_661-703.qxd 5/22/02 2:34 PM Page 675

Direct access file system, 488Direct index lookup, 328–329, 334Dirty reads, 181, 183, 529Disaster planning, 229, 447–462

adhering to written plan, 459–460need for, 447–451and order of recovery, 460and personnel, 455–456recovery guidelines for, 451–456and remote site, 451–452testing of, 454–455Web sites, 462written procedures and policies for, 452–454

Disaster recovery, 437backing up database for, 456–461business aspect of, 456and data latency, 460and data warehouses, 533–534replication for, 445service providers, 451standby databases for, 444

Disaster recovery plans, 76, 81as living document, 452–454sections in written plan, 453testing, 460usable, 462

Disastersand availability restoration, 229database log and, 457defined, 447preventing, 461–462recovery from, 437

Discussion forums, 613Disk allocation, 309Disk devices, for storage, 480Disk drives, 466

failure of, 231variety of, 480

Disk mirroring, 445Disk-related outages, and availability, 231–232Disks, file placement on, 308, 469–471Disk space, for index storage, 131Disk storage

and data warehouses, 532DBMS, 62devices, 251and I/O, 270–271

Disk striping, 481Disk subsystems, and availability problems, 230Disk systems, evaluating, 485–486DISTINCT clause

and queries, 131and sorting, 333

Distributed data, accessing, 509–510Distributed database implementation, sample, 507Distributed databases, 290, 505–512

levels of support for, 510standards for, 508–509and two-phase COMMIT, 510–511

Distributed data management model, 544Distributed data placement, 308–309Distributed environment, setting up, 506–508Distributed performance problems, 511–512Distributed processing, centralized processing

vs., 542Distributed Relational Database Architecture,

508Distributed request, 510Distributed unit of work, 510Distributed user presentation model, 543DML, 390Documents

backup strategies, 426by DBAs, 607–608, 610disaster recovery plans, 452–454for initialized devices, 309remote site recovery, 458

Document type declaration, for XML document,550, 551

Document Type Definitions, 550Domains, 22, 98, 116

enforcing, 380transforming to data types, 123–124

Dotcoms, and downtime, 227Downsizing, 538

and complexity, 225–226Downtime, 224

availability vs., 228cost of, 226database administrators and impact of, 31and database reorganization, 313and DBMS upgrades, 69e-business, 222and errors, 238and e-vailability, 548, 549minimizing, 206, 438, 446, 451and outages, 238and reorganizations, 315for system maintenance, 224

Drag-and-drop technology, 597DRDA. See Distributed Relational Database

ArchitectureDrivers, ODBC, 166DRM. See Data resource management“Drop analysis,”on SQL DROP statements, 585DROP database objects, 585

676 Index

index_661-703.qxd 5/22/02 2:34 PM Page 676

Dropdown menus, 538Dropped objects

recovering, 441–442statistics on, 314

DROP statement, 154, 210DSN1COPY, and DB2 for OS/390, 441, 442DSS. See Decision Support SystemDTDs. See Document Type DefinitionsDual active logging, 479Dual logs, 285Dumb terminals, and centralized processing, 542DUMP utility, 409, 594Duplicate tables, 142Durability, 172, 623Dynamic alerting, 291Dynamic parameters, 274Dynamic performance tables, Oracle, 314, 315Dynamic SQL, 170, 557

EEarthquakes, 448, 461EBCDIC, 499e-business, 206, 221, 222

and bankruptcies, 9and database design, 560and downtime, 227and e-DBAs, 43, 45and Java, 556, 558and storage management backups, 458

e-DBAs, 43, 45, 548–549, 558Edge Rock Alliance Ltd., 226Edit-checking, 559EDM Pool, 64n.1, 277, 282Education, 83

continuing, 612and database changes, 219DBMS, 84–85

Efficiency, SQL coding and tuning for, 343–349Efficient delivery, and change management,

20680/20 rule (Pareto Principle), 252EJB, 24Electrical failure, 448e-mail, and mailing lists, 640Embarcadero Technologies, 632, 634Embedded SQL, 169Employability, and DBA certification, 46–47Employee information, and database security, 399EMP table, CREATE statement for, 362, 363emp_type, 357, 362, 364, 365Encryption, 404Encryption key, 404End user management, in design review, 193

End users and DBAs, 32in design review, 193

Enhancement requests, 604ENROLLMENT Entity, in second normal form, 111,

112Enterprise data models, 91Enterprise DBMS, 56, 57Enterprise performance management, 250Enterprise storage servers, 24Entities, 96–97

of data model, 92discovering, 104naming guidelines, 96–97occurrences, 97transformation of, to tables, 122

Entity instance, 97Entity-relationship (ER) diagrams, 92–95

clockwise reading of relationships in, 103methods, 94sample, 93

Environmental descriptor manager pool.See EDM Pool

Environmental hazards, 448Error processing files, DBMS, 62Errors, 217, 542. See also Bugs

application, 209and availability, 246and conversions, 534and database object alteration, 579and data integrity, 525and data warehouse, 524DBA, 234DBA tools and reduction of, 577DBMS and recovery from, 622design review and correction of, 189and downtime, 238and dropping database objects, 236and formalized review processes, 219and load process, 494“log full,”421messages, 461–462and outages, 238and panic, 609–610scheduling, 237and server failure, 231

ERwin (Computer Associates), 24, 92ESCON, 487Ethernet, 487ETL (Extract,Transform, and Load)

metadata, 571software, 502, 525tools, 570, 595–596

Index 677

index_661-703.qxd 5/22/02 2:34 PM Page 677

e-vailability, 548Excelerator, 573Exclusive locks, 178Executable libraries, backing up, 423EXECUTE privilege, granting, 393Expand Beyond, 633EXPLAIN command, 130, 199, 338, 339, 591, 592EXPLAIN process, 338EXPORT utility, 409, 426, 495, 501, 512eXtensible Markup Language, 45, 549–553

benefits and drawbacks with, 552–553and database integration, 553DBAs and understanding of, 612

eXtensible Stylesheet Language, 551Extents, file, 311, 312External security, 404–405Extraction function, in XML Extender, 552Extract performance, 527Extract tools, 596

FFacts, 517Failover, 59, 244Failover technology, 408Failures

categories of, 408and database administrators, 40–41of DBMS instance or subsystem, 423–424identifying, 429–430matching type of, to type of recovery, 439reacting to, 407

Fallback planning, with new releases/versions, 74–75Fat clients, 545Fat server/thin client, 24Fault tolerance

with fiber channel, 486and RAID, 480and storage options, 479

FDDI, 487Federated multi-database setup, 506Fiber Channel technology, 486Fifth normal form (5NF), 115File extents, and database disorganization, 311, 312Filegroups, 77, 128File I/O, 257Filemaker, 50, 629Filenames, database objects mapped to, 77File placement and allocation, 296, 307–309

database log placement, 308disk allocation, 309distributed data placement, 308–309

File processing, and batch database programdevelopment, 815

Filesmapping to database structures, 127multiple storage devices for, 469raw, 128storage of, 468–472

File servers, 539File structures, in Oracle, 272File system caching, raw partitions used for avoiding,

297File systems, raw partitions vs., 296,297–298,471–472Fill factor, 305Filter factor, 322Finance, and 24/24 availability, 225Financial chargebacks, and data warehouses, 532–533Financial loss, 449Fires, 448Firing triggers, 367–368First normal form (1NF), 109, 115

COURSE entity in, 111STUDENT entity in, 110

Five nines, and availability, 227Fixed-length columns, 124Fixed length index keys, 477Fixed-length rows, size of, 475Floating-point data

loading into tables, 496unloading, 499

Floating point data types, 123, 358Floods, 448, 461Focus, maintaining, 609FORCEPLAN option, with Microsoft SQL Server, 341Foreground mode, 588Foreign key columns, 371, 380

and clustered indexes, 303and denormalization, 140

Foreign key perspective, 373Foreign keys, 97, 102, 106, 212, 371, 582. See also

Primary keysand clustering tables, 137and combined tables, 145in dependent tables, 375dropping, 579and index creation, 130and referential constraints, 126

Formalized review processes, 219FORTRAN, 165Forward log rolling, 433Fourth-generation languages (4GL), 161, 597Fourth normal form (4NF), 115

678 Index

index_661-703.qxd 5/22/02 2:34 PM Page 678

Fractured silos, 259Fragmentation

and database disorganization, 311, 312and distributed data placement, 309statistics on, 314

Frame relay, 547FREEPAGE parameter, 305Free space, 296, 305–306, 311, 312, 472, 476

disadvantages with, 306and page size, 309, 310percentage changes, 214–215

“Free-standing”database objects, 364FROM clause, 163, 338Full image copy backup, incremental image copy

backup vs., 412Full recovery, 430Functionality, and new releases, 71Functions, user-defined, 77, 552Future, planning for, 488–489

GGartner Group, 3, 73Gateways, 509Generators, backup, 461Gerstner, Lou, 467Giga Research Group, 467Global economy, 225, 449Grant authorization, 585GRANT statement, 390, 391, 394, 396, 585Granularity, trigger, 371Graphical SHOWPLAN example, 340Graphical user interfaces, 154, 540

and catalog tools, 584–585with query and reporting tools, 596

Graphic data types, 123GROUP BY

and clustering tables, 137and queries, 131and sorting, 333

GROUP BY SQL, 262Group-level security, and cascading REVOKEs, 399Groups, authorization of, 398–399Growth. See also Changes

planning for, 488–489in storage capacity, 467

GUEST users, 390GUIs. See Graphical user interfaces

HHackers, 404Hackett Benchmarking & Research, 76

Handheld devices, 51and DBMS, 61and mobile DBMS, 57

Hardware. See also Softwarefor client/server component layers, 541and DBMS selection criteria, 60failures, 21, 238

Hardware configurationand change, 208and system performance, 270

Hash, 334Hashed access, 334Hashing, 136Hashing algorithms, data retrieval based on, 334Hashing indexes, 129Hashing structures, 334Hash keys, 212Header, 353Header information, in index record, 477Health hazards, 448Heating failures, 448Heterogeneous database migration, and logical

backups, 425Hierarchical data models, 620Hierarchical database models, 50Hierarchies, 149–150Hierarchy tables, for departmental organization,

149High-availability, exploiting, 243High-performance Java, 556High-speed utility, backup and recovery with,

593Hints, in SQL queries, 342Hiperpools, DB2, 281Historical performance indicators, maintaining,

257HOLAP, 30Homegrown DBA tools, 604–605Homonyms, 99, 100Horizontally split table, 143Horizontal restriction, 400Hostile databases, 559, 560, 561Hot backup, 417Hot-swappable drives, 481How-to documents, 613HPJ. See High-performance JavaHP-UX (Hewlett-Packard), 74HTML, 549, 550, 551, 555Hubs, 24Hurricane Andrew, 448Hurricanes, 448

Index 679

index_661-703.qxd 5/22/02 2:34 PM Page 679

IIBM, 52, 467, 626, 629

AD/Cycle, 573AIX, 74DataJoiner, 510DB2 Everyplace, 45mainframes, 244MQSeries, 504RACF, 386Sysplex, 244, 245vendor partnerships with, 603

IBM DFHSM, 24IBM-Link, 611Icons, 538Identity property, 124, 125Identity values, 289Idle number, SP_MONITOR, 291IDMS, 33, 50, 621IDs, 387, 623IEBGENER, 60IF-THEN-ELSE statements, in SQL WHERE clauses,

345Image, and downtime, 227Image copy backups, 408, 413, 436, 593

alternate approaches to database backup,424–426

concurrent access issues, 416–418consistency with, 418–419database object definition backups, 426–427database objects and, 414–415and DBMS control, 415–416and DBMS environment, 424DBMS instance backup, 423–423documenting, 426full vs. incremental, 412–414guidelines on, 409invalid, 439log archiving and, 419–420post-recovery, 461restoring, 431scheduling, 420, 422–423unloading from, 498–499

Impact analysis, and change management, 205Impedance mismatches, 164IMPORT utility, 501, 512, 587IMS, 33, 50, 502, 620, 643IMS/TM, 173IN, 346, 360Incremental copies, merging, 414Incremental image copy backup, full image copy

backup vs., 412Independent software vendors, 593, 631

Index consistency, and internal DBMS integrity, 23Index covering, 331Index design, 129–136

bitmap indexes, 133–134b-tree indexes, 132–133index summary, 135–136ordered indexes, 135partitioned indexes, 135reverse key indexes, 134–135

Indexed access, 328–334Indexes, 19, 77, 128, 137, 212, 216, 349

for avoiding sorts, 332–333bitmap, 132calculating size of, 477–478clustered and unclustered, 302copying, 414–415corruption of, 352costs of, 131creating, 130–131damaged, 237and database tuning, 261data warehouse, 528defined, 129dropping, 235, 579and efficient loading, 496on foreign keys, 375improperly defined, 237locking, 177ordered, 132partitioned, 132and physical design review, 198and poorly performing SQL, 252and query analysis, 325reasons for avoiding use of, 333–334recovery of, 440, 457reorganizing, 314–315reverse key, 132screening, 331on segments, 470space for, 468and system catalog, 288

Indexing, 296, 298–301importance of, 135and mirror tables, 143and overloading, 300–301and performance enhancement, 298, 299times to avoid, 300

Index key density, 323, 324Index key values, in index record, 477Index-only access, 130–131, 331, 332Index page layouts, and calculating index size,

477–478

680 Index

index_661-703.qxd 5/22/02 2:34 PM Page 680

Index pagesnumber of records on, 478single index rows stored on, 477

Index recordscalculating size of, 478items in, 477

Index scans, types of, 329Index structures, size of, 128Indicator byte, and nulls, 495Industry standards, current awareness of, 612InfiniBand, 488Information, 564Information centers, 517, 518Information Engineering (Martin diagramming

technique), 94Information technology, 1

and certification, 46–47and change management, 203and database administration, 4growing complexity of, 225–226

Information technology infrastructuredatabase administrator interactions with, 24–25and system administrator, 16tools in, 24

Information technology organizationsdata policies of, 12and object-oriented programming, 168

Information technology silos, in fracturedenvironment, 259

Informix, 6, 50, 51, 52, 626TBCHECK utility, 353Web site for, 641

InfoShark, 633Ingres (Computer Associates’), 7, 50, 52, 627, 628Initialization, disk, 309Inmon,William H., 515Inner table, 325, 326Input buffers, 285Input file, describing, 495–496INSERTED table, 369INSERT privilege, granting, 392INSERT processing, 360INSERT rule, 372–373Inserts, and free space, 306INSERT statement, 66, 162, 185, 587, 593

and data modification, 310and transition tables, 370and triggers, 40, 366, 368

INSERT trigger, for referential constraints, 377Installation. See also Database management systems

installation/configuration database gateway, 546

DBMS, 60–67of system catalog, 288

Instance, Oracle, 273Instances

failures, 408Oracle, 272

INTEGER, 123Integer data types, 358Integrated data, and data warehouse, 516Integration

of system catalog, 567–568testing, 38, 83

Integrity. See also Data integrity; Referential integritychecking, 241, 593

types of, 351–352violations, 403

Intelligence, and change management, 204, 205, 206Intent locks, 178–179, 183, 184Interactive SQL, 233Interbase (Borland), 627Interfaces, and SQL, 165Interleaving data, 137–138, 296, 305Interleaving table data, 138Internal integrity, of database, 22, 23Internal security threats, 402Internal structure caches, 277International finance, and 24/24 availability, 225Internet, 547, 637. See also Web

coupling businesses to, 221from DBA to e-DBA, 43, 45and keeping up-to-date, 612mailing lists, 640

Internet age, never-ending uptime during, 227Internet-connected databases, 548–549Internet-enabled databases/applications, 24Internet service providers, 547Internet support, in database management systems/

applications, 243“Internet time,”222, 558–559Intranets, and Java applications, 555Intrusion detection, 402In-use pages, in data caches, 279Invalid image copy backups, 439Invasiveness, and recovery strategy, 438Invasive system performance tools, evaluating,

589I/O

costs, 322disk storage and, 270–271efficiency, 253parallelism, 334–335

io busy number, SP_MONITOR, 291

Index 681

index_661-703.qxd 5/22/02 2:34 PM Page 681

I/O operationsand database performance, 466and disk devices, 470

ISDN, 547ISO. See International Standards OrganizationIsolation, 172

distributed databases and, 505, 506levels, 181–183, 185

ISPs. See Internet service providersISVs. See Independent software vendorsIT. See Information technology

JJacobson, Ivar, 94Jasmine, 621, 629Java, 24, 45, 165, 261, 269, 349, 549, 554–558

applets, 554, 555applications, 554program types, 554qualities of, 555servlets, 554, 555and SQLJ, 166

Java Database Connectivity, 166, 556–557Java Virtual Machine, 556JBOD, 486–488

direct access file system, 488network-attached storage, 487–488storage area networks, 486–487

JDBC. See Java Database ConnectivityJob control software, and DBMS, 66Job-entry subsystems, 24Job offers, evaluating, 11Job schedulers, 405Job scheduling, and security, 404–405Join columns, and clustered indexes, 303Join criteria, and data interleaving, 137Joining, 325Join methods, and poorly performing SQL, 253Join order, 326JOINs

and clustering tables, 137and queries, 131

Joins, 276avoiding, 345and indexes, 298and relational optimization, 325–326subqueries vs., 163of tables, 325

Journals, writing for, 609JScript, 167Junior DBAs, 26Just a bunch of disks. See JBOD

Just In Time (JIT) compiler, 556JVM. See Java Virtual Machine

KKerberos, 24Kernel, and DBCC command, 355Keys, 101–102

candidate, 101design of and database usability, 560dropping, 579foreign, 102primary, 101–102

Key values, and hashing, 136KISS principle, 344Knowledge

from data to, 564–565definition of, 565sharing, 608–609

LLarge objects, 75Last run number, SP_MONITOR, 291Late-binding languages, 556Latency, and database change tracking, 214Lawsuits, and downtime, 226Leader, of design review, 191Leaf distance, 315

and index reorganization, 314–315Leaf pages, 328, 330

in b-tree indexes, 132, 133in indexes, 352

Lefkovits, Henry C., 573Legal responsibilities, 449, 450Legibility, of data models, 103Levels, and index reorganization, 314LGWR. See Log writerLicense cost, of DBMS, 54LIKE logical operator, 347, 360LIMIT parameter, 500Linux, 24, 56, 74, 628Listserv, 639List servers, 639Load balancing, and clustering, 59Loading, efficient, 496–497LOAD parameters, generation of, 499LOAD utility, 20, 131, 142, 241, 502, 512, 523, 549,

587, 593, 594bulk inserts of data into database tables with, 494and data movement, 493, 494–495and efficient loading, 496–497and nulls, 495and running of other utilities, 497

682 Index

index_661-703.qxd 5/22/02 2:34 PM Page 682

test beds of data maintained with, 500used for program testing, 501

LOB columns, DB2, 352LOBs. See Large objectsLock duration, 181–184

acquire/release specification, 183–184isolation level, 181–183

Locking, 176–185, 320and contention, 287–288granularity, 176, 180, 185logins, 387, 388memory required for, 278and transactions, 173

Locking problems, 237deadlocks, 179–180duration of, 181–183escalation of, 184lock timeouts, 179programming techniques for minimizing,

184–185Locking strategy, and application performance, 320Lock manager settings, 287Locks

and DBMS memory consumption, 278granularity and concurrent access, 177types of, 178–179

Lock suspensions, minimizing, 288LOCK TABLE command, 177Lock timeouts, 179

deadlocks, 180and lock duration, 181

Lock timeout values, 179Log archival process, 419, 420Log archiving, and backup, 419–420Log-based recovery tools, 593Log files, 62, 479“Log full” error, 421Logging

dual, 285LOAD utility and turning off, 497options, 253

Logical backups, 424–425Logical data independence, 622Logical data models, 14, 15, 81, 92, 105, 106, 108,

117, 155, 156and design reviews, 190from, to physical database, 121–129and logical design review, 196normalized, 139, 141and nullability of attributes, 124and primary keys, 124synchronizing with physical database, 208

Logical design, and change, 208Logical design review, 194, 196–197Logical transactions, 171Logic-oriented security, 401Logins, 386, 389

administrating, 388attempts, 403DBMS and database, 390information about, 387

Logoff attempts, 403Log offloading, 63, 285, 419Log read caches, 277Log records, 277, 409Log shipping, 460Log switches, 257Log write caches, 277Log writer, 273Long text columns, splitting, 144–145Lookup tables, 183, 296Loss

of data, 236of database objects, 235–236of data center, 229of server hardware, 230–231

MMagazines, writing for, 609Magazine Web sites, 641–642Mailing lists, 639–640Mainframe computers, 58, 538, 541, 542Main-memory database management systems.

See MMDBMSMaintenance

charges, 604and clustering, 244

Majordomo, 639MAJOR Entity, in third normal form, 113Malicious attacks, and data loss, 236Manageability, and availability, 223MANAGED_BY_DEPT column, 375Management discipline, of database administration,

7–11MANAGER role, 397Man-made disasters, 448, 462Manual database maintenance, reducing risks and

errors with, 316Manual reorganization, steps for, 313Manuals, 613

retaining, 608Many-to-many associative entity, 107Many-to-many relationships, 108

E/R diagramming method, 94

Index 683

index_661-703.qxd 5/22/02 2:34 PM Page 683

Mappingentity-to-table, 122files to database structures, 127options for tables, 296

Market Compensation Survey (1999), 3Martin diagramming technique, 94, 103Mass deletes, 236, 497Matching index scan, 329, 330, 331Materialized views, 530Matrices, roles and responsibilities, 79, 80MAX function, 530MAX values, and ordered indexes, 135Mean time between failure, 227Media failures, 353, 408

and disk mirroring, 445recovering from, 439

Mediator, role of in design review, 192Memory

amounts of, 278and availability problems, 230database performance and allocation of, 253and hardware configuration, 270and “open”database objects, 282and operating system, 268value of caching resources in, 275

Memory consumption, areas of, 277Memory structures, in Oracle, 272Memory-to-memory transfer, 488MEMUSAGE keyword, 354MERANT, 636MERGE COPY utility, 413Mergers and acquisitions, and complexity, 226Merge-scan join, 326Message queueing software, 16, 269, 503Message queues, and DBMS, 66, 69Messaging software, data movement with, 503–504Metadata, 13–14, 211, 524

data warehousing and, 530, 566description of, 563–564management, 81, 563–573strategy, 565–566types of, 566–568

META Group, 5, 33Metalanguages, 550, 551Microcode, 251Microsoft, 52, 626, 634

database programming, 166–167Knowledge Base, 611ODBC interface invented by, 166vendor partnerships with, 603

Microsoft Access, 50, 57, 630Microsoft Repository, 570

Microsoft SMS, 24Microsoft SQL Server, 6, 50, 51, 53, 108, 127, 274, 280,

429, 502, 619, 620, 626, 643Analysis services, 597backing up transaction logs, 421backup and recovery for, 415certification program for, 46constraints in, 363and data types, 358DBCC utility program, 353filegroups, 128, 470FORCEPLAN option with, 341links and news, 643and lock escalation, 184log options, 287memory requirements for, 278recovery models, 430and relational data model, 621and rules, 364and SHOWPLAN command, 338SQL cache in, 64system parameters in, 65text columns in, 476and transaction log file backups, 286transition tables, 369, 370Web site for, 641

Microsoft Transaction Server, 173, 269Microsoft Visual Studio, 24Middleware, 30Midrange computers, 538, 541Migration tools, 582Migration utilities, 289MIN function, 530MIN values, and ordered indexes, 135Mirrored disks, 408Mirroring, 459, 481Mirror tables, 142–143, 301MMDBMS technology, 480Mnemonic devices, and passwords, 388Mobile DBMS, 56, 61–62Mobile workers, and PDAs, 45, 46Modeling and design tools, choosing, 577MODIFY utility, DB2, 416MOLAP, 30Monitoring, management vs., 254–255Monotonic page splits, 304, 305, 311MQSeries (IBM), 269, 504MSMQ (Microsoft), 269, 504MTBF. See Mean time between failurezMulticolumn indexes, 129Multidimensional views, of detail, summary, and

aggregate data, 517

684 Index

index_661-703.qxd 5/22/02 2:34 PM Page 684

Multi-index access, types of, 332Multiplatform DBA issues, 35–36Multiple database environments, establishing, 38Multiple database management systems, guidelines

and support for, 36Multiple-DBMS environment, 596Multiple distributed database, 506Multiple joins, and report tables, 142Multiple release upgrades, 70Multiple tables mapped to single files, 296Multitier client/server implementation, 544, 545Multitier techniques, 24MVS, 24MySQL, 52, 628, 629

NNames

constraint, 362, 375for initialized devices, 309minimizing changes in, 77newsgroup, 637problems with, 559user, 387, 390

Naming conventions/guidelinesattribute, 98–99database, 76–79entity, 98–99for physical columns, 123for remote site backup files, 453and repositories, 569

NAS. See Network-attached storageNASDAQ, 449Natural disasters, 448Nested-loop joins, 326, 342Nested trigger calls, 289Nested triggers, 368–369, 377Network-attached storage, 24, 467

storage area network vs., 487–488Network cards, 229Network data models, 620–621Network File System, 487Networking, 611Network protocols

database gateways and, 546system administrators and configuration of, 16

Networks, 249and DBMS, 66fast connections to, 547, 548monitoring, 291problems with, 229–230tasks distributed across, 543

Network specialists, assistance from, 611

Network traffic, 546–547and distributed performance, 511reducing, 546–547

Neutralizing DELETE, 374Neutralizing UPDATE, 373NEW, 369, 370Newsgroups, Usenet, 637–639Newsletters, vendor, 602Newsreader software, 638NFS. See Network File SystemNiche tools, 599NIST. See National Institute of Standards and

TechnologyNodes

in b-tree indexes, 133and clustering, 244

Nonatomic data, 109Nonclustered indexes, 129, 477Noncritical applications, 450Non-DBMS DBA security, 405Nondisruptive utility, 240Nondynamic parameters, 274Nonleaf pages, 328Nonmatching index-only scan, 332Nonmatching index scan, 329, 330, 331Nonrelational DBMS vendors, 629Nonstandard abbreviations, 78Nonsubvertibility, of system catalog, 568Nonunique indexes, 129Nonvolatile data, and data warehouse, 516Nonvolatile storage, 465Normal forms

Boyce Codd, 114fifth, 115first, 109fourth, 115second, 111–112third, 112–113

Normalization, 90, 116, 138, 301and database design, 560definition of, 108in practice, 115and repeating groups, 146

Normalized data models, 90, 108, 114Normalized tables, synchronizing with denormalized

tables, 139Normal page splits, 304, 305, 311Notes, retaining, 608Noun phrases, tracking, 104Nouns

entities as, 96tracking, 104

Index 685

index_661-703.qxd 5/22/02 2:34 PM Page 685

Novell, 24NULL, 360Nullable columns, 124, 363nulls, 100–101, 363, 495NYSE, 449

OOBIDXLAT option, in DSN1COPY, 442ObjectDesign, 52Object Linking and Embedding Database, 166, 167

and ADO, 168Object migration tools, 582Object orientation, and SQL, 167–169Object-oriented database management systems,

629Object-oriented data models, 621Object-oriented DBMS vendors, 629Object oriented (OO) programming, 167–168,

554Object-oriented technologies, 24Object recovery, and logical backups, 424Object/relational databases, 169Object Store, 621, 629Occurrence, entity, 97ODBC. See Open Database ConnectivityODBMS. See Object-oriented database management

systemsOffset tables

in data page layout, 474in index record, 477in table record, 475

Off-site disaster recovery, 437written plan for, 452–453

OLAP. See Online analytical processingOLAP Council,The,Web site for, 645OLD, 369, 370OLE, 24OLE DB. See Object Linking and Embedding

DatabaseOLTP, 570

applications, 32and data freshness, 530OLAP vs., 517systems, 528

Omegamon (Candle Corporation), 290One-to-many relationships, 102, 107

and combined tables, 145E/R diagramming method, 94and hierarchies, 149

One-to-one relationships, 116and combined tables, 145E/R diagramming method, 94

Online analytical processing, 30, 517applications, 32activities, 597and data mining, 518OLTP vs., 517

Online change request system, 218–219Online database reorganization, 241Online event monitoring, 589Online forums, 609Online processing, 320, 537Online standards manuals, 600Online technical support, 602Ontos, 629Open Database Connectivity, 166, 557“Open”database objects, and memory consumption,

278, 282Open-source DBMS offerings, 628–629Open-source software, 628“Open-source software”movement, 52OPER. See Operations controlOperating system experts, 56Operating system parameters, system administrators

and configuration of, 16Operating systems

changes in, 534and DBMS releases, 69failures of, 233monitoring, 291support for DBMS, 52and system performance, 268–269

Operational metadata, 568, 572Operational support representatives, in design

review, 193Operational support standards, 84Operations control, and DBMS, 398Optical disk, 480Optical storage, 465Optimization

and database performance, 18–19, 251and performance management, 255query rewrite, 336–337relational, 320–335rule-based, 337–338view access, 335–336

Optimizers, 320, 321, 322, 349and data scans, 327indexes not chosen by, 333–334and query rewrite, 336–337and restrictive predicate placement, 344rule-based, 338and summary tables, 530

Opting in, to mailing lists, 639

686 Index

index_661-703.qxd 5/22/02 2:34 PM Page 686

Optionality, 103–104Oracle, 5, 8, 33, 50, 51, 52, 108, 184, 341, 502, 557,

619, 626, 634, 643Advanced Queueing feature, 504architecture of, 272–273backup and recovery for, 415certification program for, 46column alterations in, 212COMMIT issued in, 348databases, 272data interleaving supported by, 137, 138and data types, 358dynamic performance tables, 314, 315Export utility, 426instances, 272links and news, 643memory structures, 273MetaLink database, 611optimization-mode parameter provided by, 290optimization types supported by, 338Oracle8i Lite, 45parameter files, 272PGA and shared pool in SGA, 64n.1and relational data model, 621sort area, 273standby databases, 232, 244, 443system parameters set in, 65n.2transition tables, 370transportable tablespaces, 504vendor partnerships with, 603Web site for, 641

Oracle data, RMAN for backup and recovery of,417

Oracle DBAs, and impact of COMMIT on rollbacksegments, 348

Oracle Enterprise Manager, 263SQL Worksheet in, 66n.3

Oracle8i Lite, 45Oracle9i, 6, 620

Real Application Clusters, 59Virtual Private Database, 401

Oracle processes, 273ORDER BY, 262

and clustering tables, 137and queries, 131and sorting, 333

Ordered indexes, 135Organizational design review, 194, 198–199Organizations, types A, B, and C, 73 Organization style, and DBMS releases, 73Organization type, and DBMS choice, 53Organized tablespace, 312

OR logical operatorand check conditions, 360judicious use of, 346–347and multi-index access, 332

OR 0=1 component, 343OS. See Operating systemOS/2, 628OS/390, 24

DB2 for, 59, 282, 289, 310, 415, 416, 470images, 245

Outages, 226, 231, 241, 461avoiding, 480and CPU failures, 230, 231database, 63disk-related, 231–232and e-vailability, 548, 549and media failures, 408and network problems, 229–230and operating system failure, 233planned and unplanned, 238–239, 239and severe performance problems, 257and stock exchanges, 449warnings about, 19

Outer joins, and report tables, 142Outer table, 325, 326“Out of space”conditions, 238, 286Output, and database design, 559Output buffers, 285Overhead, and index costs, 131Overloading, indexes, 300–301Overstuffing, 560Ownership

costs, 54data, 81

PPacket errors number, SP_MONITOR, 291Packets received number, SP_MONITOR, 291Packets sent number, SP_MONITOR, 291Page cache, and DBCC command, 355Page header

corruption of, 352in data page layout, 473

Page locking, 177Page pointer, in index record, 477Pages

of b-tree indexes, 133and database locks, 176in data caches, 279and hiperpools, 281recovering, 442

Page size, 296, 309–310

Index 687

index_661-703.qxd 5/22/02 2:34 PM Page 687

Page splits, 304and database disorganization, 311and free space, 306normal, 304statistics on, 314

Palm Pilots, 24, 45Panic, avoiding, 609–610Paradox, 50, 630Parallel access, 334–335Parallel databases, 527Parallelism, 297, 334, 342, 522Parallel processing, 61, 244, 245Parallel processors, 527Parallel Sysplex, 245Parallel technology, 243Parameter files, Oracle, 272Parent tables, 372, 379Pareto Principle (80/20 rule), 252Parity bits, 481Partial recovery, 432Partitioned indexes, 135Partitioned tablespaces, 210, 211, 212Partitioning, 295, 296–297, 308Partition scans, 327Pascal, Fabian, 624

Web site for, 642Passwords, 405, 623

guidelines for, 387, 388for login IDs, 386, 387profile parameters for, 389

Patches, 233PATROL (BMC Software), 290PC-based DBMS vendors, 629–630PCTFREE parameter, 305PDA database administrators, 45–46PDAs. See Personal digital assistantsPeers, knowledge shared with, 608–609Pendant DELETE, 366, 374, 377, 583PeopleSoft, 545Performance

application, 319–349and change, 206and code generation tools, 165and column ordering, 126and concurrency, 287for data access requests, 133database, 223, 295–316data warehouse, 527–530defining, 249–253degradation, 252, 402, 512and denormalization, 140, 152as design issue, 159

with direct access file system, 488distributed, 511–512estimation tools, 262and fast network connections, 548and hashing, 136and index locking, 177and Java, 556and locking, 184monitoring, 529monitoring vs. management of, 254–255and network traffic, 546and RAID, 480RAID levels and, 485and redundant data, 146and repeating groups, 146severe problems with, 237and simplicity, 344SQL coding for, 170–171and storage options, 479system, 267–292and upgrades, 69

Performance analysts, 29Performance benchmarks, DBMS, 53Performance management, 249–264

and database server, 542DBMS performance basics, 263–264monitoring vs. management, 254–257performance tuning tools, 261–263reactive vs. proactive, 255–256service-level management, 257–260types of performance tuning, 260–261

Performance management components, 254analysis, 255monitoring, 254optimization, 255

Performance management tools, 264, 587–592application performance tools, 590–592database performance tools, 590system performance tools, 588–590

Performance monitoring, 264and database administrators, 17, 18–19and data usage, 532tools for DBMS, 53

Performance monitors, 36, 261, 280, 588Performance optimization tools, 262Performance requirements, and database

administrators, 32Performance trace files, protecting, 404Performance tuning, 264Performance tuning tools, 261–263Performance tuning types, 260–261

application tuning, 261

688 Index

index_661-703.qxd 5/22/02 2:34 PM Page 688

database tuning, 260–261system tuning, 260

Persistence, and solid state devices, 271Personal computers, 537–538Personal DBMS, 56Personal digital assistants, 24, 45Personal Oracle, 57Personnel

and disaster recovery, 453, 455–456on recovery team, 453written disaster recovery plans for, 452

Pervasive computing, and DBMS, 61PGA. See Program global areaPGQ, in Oracle, 64n.1Phantoms, and isolation levels, 182Physical databases, 155

change and structure of, 209logical data models synchronized with, 208from logical models to, 121–129and views, 152–154

Physical data independence, 321, 622Physical data models, 108, 121, 190Physical data structures, building, 127–129Physical denormalization, 151, 301Physical design, and change, 208Physical design review, 194, 197–198Physical integrity, of database, 22Physical pages, and allocation pages, 474PIT recovery. See Point-in-time recoveryPlanned outages, 238, 239Planned SQL, 169Planning analysis, and change management, 205PLAN_TABLE, 339, 340, 341, 591PLATINUM Repository, 570PL/SQL, 598PMON. See Process monitorPocketPCs, 24, 45Poet, 629Point-and-click technology, 597Pointer consistency, and internal DBMS integrity, 23Pointer management, 554Pointers, 352Point-in-time recovery, 21, 411, 432, 433, 434–435,

439Point of consistency, 416, 418–419Point releases, 67Policies and procedures, for change requests/

implementation, 217–218Portals, database, 642–643Postal codes, 560PostgreSQL, 52, 628Post-implementation design review, 194

Power losses/outages, 231, 232Powersoft (Open Tools Division of Sybase), 634Power spikes, 231Power surges, 461Pratt, Philip J., 624Predicates

and check conditions, 360and indexed access, 328

Predicate transitive closure, 336, 337Predictability, and change management, 205, 206Pre-implementation design review, 194Prejoined tables, 141

and denormalization, 301Prepositional phrases, 104Preproduction performance estimation, 256–257Presentation logic, in client/server applications,

540, 543Primary database, 232PRIMARY filegroup, 128Primary key columns, 371, 380Primary key constraints, 357

example, 356Primary key perspective, 373Primary keys, 101–102, 106, 117, 212, 371, 582.

See also Foreign keysand clustering tables, 137and denormalization, 140design of and database usability, 560dropping, 579and first normal form, 109and indexes, 130, 300in parent tables, 375and referential constraints, 126and second normal form, 111specification of, 124for speed tables, 150and split keys, 144

Primary key values, 592Print servers, 539Privacy policies, and data, 9Privileges, 386, 394–395Privileges types, 391–394

database object, 391, 392–393program and procedure, 391, 393–394system, 391, 393table, 391, 392

Proactive database administrators, 7Proactive performance management, reactive per-

formance management vs., 255–256Proactive performance plans, 255Proactivity, and change management, 204, 206Problems, preparing for, 407–408

Index 689

index_661-703.qxd 5/22/02 2:34 PM Page 689

Problem solvingby database administrators, 3, 19resources for, 611–612by vendors, 603

Procedural database objects, 40Procedural DBAs

administering stored procedures, triggers, andUDFs, 40–43

duties of, 41, 42involvement by object, 42managing database logic, 39–43offloading coding-related tasks to, 43stored procedures, 39triggers, 40user-defined functions, 40

Procedure buffers/headers, and DBCC command,355

Procedure cache detail, and DBCC command, 355Procedure caches, 276, 281–282Procedure privileges, 391

granting, 393Procedures, database, 76Proceedings and presentations, retaining, 608Processes, in Oracle, 272Process monitor, Oracle, 273Production database backup, populating test

environment with, 443Production environment, and quality database

implementation, 37Productivity, loss in, 6Product vendors, nature and stability of, 601Program cache, 64Program files, protecting, 404Program global area, for Oracle instance, 273Program logic, RI vs. check constraints vs., 379–380Programmers, education classes for, 84Programming

object-oriented, 167, 168tools, 597–598

Programming experts, 56Programming languages

and application performance, 320and SQL, 165

Programming staff experience, and database adminis-trators, 32

Program privileges, 391granting, 393

Programs, 77batch database, 185retaining, 608SQL embedded in, 165

Program source libraries, backing up, 423

Program testing, LOAD AND UNLOAD used for, 501PROJECT_STATUS codes, 382Propagation, 503Propagation failures, 236–237Propagation tools, 596ProTech, 85PUBLIC, granting authorization to, 394Publications,Web-based, 609PUBLIC privileges, revoking, 395Publishing databases, problems with, 237Punched cards, 537

QQualifying row, 326Quality assurance, 83, 234Quality assurance environment, and multiple data-

base environments, 38Queries

and bitmap indexes, 134and clustering tables, 137and indexing, 298, 299and prejoined tables, 141and relational optimization, 321

Query analysis, and relational optimization,323–325

Query parallelism, 334Query performance, 300, 344, 527Query processing, and indexes, 129, 130Query rewrite, 336–337Query tools, 53, 233, 596–597Quest Software, 632QUIESCE utility, 416, 418, 419, 433

RRACF (IBM), 24, 386RAD. See Rapid application developmentRAID, 408, 466, 470, 480–486

devices, 271disk systems, 231evaluating, 485–486levels and performance, 485levels of, 481–486RAID-0, 481, 485RAID-0+1, 484, 485, 486RAID-1, 481, 482, 485, 486RAID-2, 481, 482RAID-3, 482RAID-4, 482, 483RAID-5, 482–483, 486RAID-6, 483RAID-10, 483, 484RAID-53, 484

690 Index

index_661-703.qxd 5/22/02 2:34 PM Page 690

storage, 232storage vendors, 484

Random accessmemory chips, 251and unclustered indexes, 303

Randomizer, 136Range access, and clustered indexes, 303Range columns, and clustered indexes, 303Rapid application development, 559RAS. See Reliability, availability, and scalabilityRational Rose, 24Rational Software, 634, 635Raw disk usage, 269Raw files, 128Raw partitions, file systems vs., 296,297–298,471–472RDA. See Remote Database AccessRDBMS. See Relational database management systemReactive database administrators, 7Reactive performance management, proactive per-

formance management vs., 255–256Read efficiency

of data caches, 280–281of procedure caches, 282

Read locks, 178Rebuilding, recovery vs., 415RECO. See Recover background processRECONFIGURE command, 274Record length, for tables, 475Records, 474

aggregate, 530index, 477on single index page, 478

Recover background process, 273Recoverability, and availability, 223RECOVER command, 288RECOVER function, of DBMS, 236RECOVER operation, 277, 283, 285Recover to current recovery, 21, 431–432, 434RECOVER utility, 20, 416, 429, 443, 456, 549, 593Recovery, 427–443. See also Backup; Disaster

recoveryand availability, 237–238of broken blocks and pages, 442complexity of, 427and database administration standards, 81and database administrators, 17, 21and database server, 542and data warehouse, 533–534designing DBMS environment for, 424from disk-related outages, 231of dropped database objects, 441–442duration of, 409, 438–439

failure type matched to type of, 439index, 440management, 242off-site disaster, 437optimum strategy for, 437–439options, 428–429order of, 460partial, 432point-in-time, 418, 432populating test databases, 443recover to current, 431–432, 434and risk, 449–451RMAN for, 417and solid state devices, 271SQL Server models, 430steps in, 429–431testing plan for, 440–441tools, 592–593transaction, 433–434and transaction logs, 285types of, 431–439

Recovery managers, 238Recovery point, selecting, 433Recursive triggers, 377Redo log files, in Oracle, 272Redo logs, 232, 272REDO recovery, 435, 436Redundancy, 22–23, 169

and denormalization, 138reducing, 90

Redundant arrays of inexpensive disks. See RAIDRedundant constraints, 365Redundant data, 146, 301Redundant network switches, 229Redundant power supplies, 231Reference manuals, retaining, 608Referential constraints, 22, 78, 143, 215, 369, 372, 418

building for relationships, 126–127and dropped objects, 442and loss of database objects, 235

Referential integrity, 126–127, 130, 143, 241,371–382, 582

bypassing, 380check constraints vs. program logic vs., 379–380DBMS support for, 376declarative RI constraints, 375–376DELETE rule, 373–374INSERT rule, 372–373pendant DELETE, 374relationship set-up, 375rules, 374rules of thumb, 380–381

Index 691

index_661-703.qxd 5/22/02 2:34 PM Page 691

Referential integrity continuedtools, 579, 582–583and triggers, 376–379triggers used for implementing, 369UPDATE rule, 373user-managed vs. system-managed, 379

Referentially related tables, backing up/recoveringtogether, 381

Relational access, applications designed for, 319–320Relational closure, 164

and set-at-a-time processing, 162–165Relational data, 127

fourth-generation languages for accessing, 597updating, 587

Relational database access, and SQL, 160Relational database management systems, 17, 38, 480

and data modification, 310identity property supported by, 125relational optimizers in, 321triggers fired by, 40

Relational databases, 77, 210, 264amount of memory needed by, 278changes made to, 549denormalizing, 301and hierarchies, 149and locking process, 288and memory usage, 274and object-oriented programming, 169and optimization, 251rows retrieved from, 262security in, 586

Relational database technology, 5Relational data models, 621Relational design, and performance, 18Relational optimization, 320, 321

access path choices, 326–335CPU and I/O costs, 322database statistics, 322–323joins, 325–326query analysis, 323–325

Relational optimizers, 320, 321, 322, 347, 349access paths chosen by, 342and indexed access, 328and index use for avoiding sorts, 333

Relational performance, poor, 558Relational queries, and optimization, 18Relational theory, 17Relations, 621Relationships, 102–104

and cardinality, 103of data model, 92and optionality, 103–104

referential constraints built for, 126–127and referential integrity, 371, 372, 373and referentially related tables, 381setting up, 375

Relative positioning, 330Release migration, DBMS, 23Release migration strategy

and platform support, 74and support policies of DBMS, 72

Releases, 289DBMS, 16, 243DBMS upgrades, 68–70knowing about, 612retired, 72–73skipping, 70upgrades, 424versions versus, 67

Release schedules, and DBMS, 54Reliability

and availability, 223and change management, 205, 206and clustering, 243and mean time between failure, 227

Reliability, availability, and scalability, 60Remote Database Access, 508, 509Remote request, 509Remote sites. See also Data warehouses/

warehousing additional data/files for backup at, 460–461database log backups shipped to, 457and disaster recovery, 451–452and storage management backups, 458WANs used for backup deliveries to, 459

Remote unit of work, 509Remote workers, and PDAs, 45Removable storage, 465RENAME statement, 211REORG, scheduling after loading data, 497Reorganization, 296

database, 310–316and image copy backups, 411manual, 313tools, 238, 262

REORG command, 288REORG process, online, 241REORG tool, 594REORG utility, 20, 131, 312, 313, 549, 593REPAIR utility, DB2, 353REPEATABLE READ isolation, 181, 182Repeating groups, 146–147, 301Replication, 444–445, 460, 503

data moved through, 502–503

692 Index

index_661-703.qxd 5/22/02 2:34 PM Page 692

and distributed data placement, 309Replication tools, choosing, 596Reporting tools, 596–597Reports

at recovery site, 453retaining, 608

Report tables, 141–142, 301Repositories, 569–573

benefits with, 570–571capabilities of, 569centralized, 572challenges with, 571–572defined, 569populating, 571, 572

Repository Manager (IBM), 573Required applications, 450Resources

and auditing, 403caching in memory, 275and database performance, 251of system, 18

Response time, and performance in distributedenvironment, 511

Responsive Systems, 633Restarts, and recovery, 411RESTORE command, RMAN, 417RESTORE function, of DBMS, 236RESTORE operation, 286Restricted DELETE, 373Restricted-INSERT RI rule, 378Restricted UPDATE, 373Retention of materials, by DBAs, 608Retrieval function, in XML Extender, 552Return on investment, 60, 167, 213Reusability, and repositories, 571Reverse key indexes, 134–135“Reverse”RI, 377Review processes, 219REVOKEs

cascading, 395–396chronology and, 396

REVOKE statement, 20, 390, 391, 394, 585analyzing effects of, 586timing of, 396

RI. See Referential integrityRightsizing, 538, 539Risk

categories of, 449and recovery, 449–451

Risk analysis, and change management, 205RMAN (Recovery Manager), 415, 416, 417ROI. See Return on investment

ROLAP, 30Roles, authorization, 397Roles and responsibilities, database support, 79, 80Rollback files, and Oracle tablespaces, 272ROLLBACK operation, 277, 283, 284, 285Roll backs, transaction, 171Rollback segments

DBMS, 62in Oracle, 272, 348

Root authority, 405Root pages, in b-tree indexes, 132, 133Ross diagramming technique, 94Routers, 24Routine maintenance, 240–241Row chaining

and database disorganization, 311, 312and free space, 306statistics on, 314

Row data, in table record, 475Row header, in table record, 475ROWID data types, 124Row length, in index record, 477Row-level locking, 179Row-level triggers, 371Row locking, 177Row migration

and database disorganization, 311and free space, 306statistics on, 314

Row recovery, and logical backups, 424Rows. See also Columns; Tables

and Cartesian products, 346and clustering, 136and cluster ratio, 314data, 473and database locks, 176and density, 324in first normal form, 109formulas for calculating physical size of, 475and free space, 306in horizontally split tables, 143and indexes, 130, 298and LOAD utility, 494minimizing number of to be returned, 345and page sizes, 309and repeating groups, 146, 147, 301retrieving from relational databases, 262in second normal form, 111size, 128in speed tables, 150from tables, 153in third normal form, 113

Index 693

index_661-703.qxd 5/22/02 2:34 PM Page 693

Rows continuedunloading, 500in vertically split tables, 143and views, 153, 400

Rule-based optimization, 337–338Rule-based optimizers, 338Rules, 77, 364, 372–374Rumbaugh, James, 94Rumbaugh diagramming technique, 94RUNSTATS command, 322–323

SSA. See System administratorSabotage, 448Salaries

DBA, 4–5, 11mean salary for full-time DBAs, 4

SAMPLE parameter, 500SANs. See Storage area networksSAP, 545SAS Institute, 636SA users, limiting number of, 398–399Scalability

and clustering, 57of data warehouses, 526–527and DBMS, 53and shared-nothing clustering, 58

Scalar functions, 40Scans, 298

and free space, 306indexed, 329matching index, 329nonmatching index, 331partition, 327table, 327tablespace, 327

Scheduling errors, 237Scribe, role of in design review, 192, 201Script libraries, 613Scripts, 212, 549, 611

and database statistics, 323DDL, 217files of, 404and loss of database objects, 235recovery procedures and, 453retaining, 608

SCSI. See Small computer system interfacesearchdatabase.com, 4, 643Search engines, 643Secondary databases, 232, 236Second normal form (2NF), 111–112

COURSE Entity in, 112ENROLLMENT Entity in, 112

Seconds number, SP_MONITOR, 291Second-tier DBMS providers, 626–627Security, 402. See also Database security

and add-on tools, 586and auditing tools, 584availability and problems with, 234and change, 207data, 5, 623database, 20–21, 82–83, 385–405and Java servlets, 554options, 289preventing breaches of, 20replacement products, 586reporting, 396–397tools, 585–586threats to, 402and UNLOAD utility, 498

Security administratorsand DBMS, 398and security reporting, 396

Security authorizations, 215, 216and dropped objects, 442for recovery operations, 453, 456tools, 579

Segmented tablespaces, 210Seibel Technologies, 545SELECT DISTINCT, and clustering tables, 137SELECT INTO/BULKCOPY database option, in SQL

Server, 286SELECT INTO statement, 286, 430SELECT list

specifying minimum number of columns in, 345of SQL statements, 332

SELECT privilege, 399, 400granting, 392

SELECT statements, 66, 162, 163, 171, 212, 338prefixing with EXPLAIN command, 339and read locks, 178and report tables, 142and views, 153

Self-description, 550, 552Self-referencing constraints, 375Semantic data integrity, 351, 352, 355–382

check constraints, 359–366data types, 357–359default values, 359entity integrity, 356–357referential integrity, 371–382triggers, 366–371unique constraints, 357

Semantic database integrity, 22–23Sequences, 77Sequential scans, and data caches, 281

694 Index

index_661-703.qxd 5/22/02 2:34 PM Page 694

SERIALIZABLE isolation, 181, 182Server hardware, loss of, 230–231Server performance, 527Server processes, Oracle, 273Servers, 249, 539

clustering and reliability of, 243data residing at, 507deployment of, in client/server implementation,

545and throughput, 511timely response from, 541and Web access, 547

Server user IDs (SUIDs), 389Serviceability, and availability, 223Service disruptions, reacting to, 407Service expectations, unrealistic, 219Service-level agreements, 31, 258, 299Service-level management, 257–260

Web site for, 644Service levels, 258Servlets, 555Set-at-a-time processing, 162–165, 186SET SYSPARM command, 243Set theory, 164

normalization and, 108and relational data model, 621

SET TRANSACTION ISOLATION LEVEL statement,181

SGA. See System global areaSGML. See Standard Generalized Markup LanguageShadow copies, 241Shadow objects, 466Shared-disk architecture, 59

for clustering, 57shared-nothing architecture compared to, 59

Shared locks, 178Shared-nothing architecture, 58

for clustering, 57, 58shared-disk architecture compared to, 59

SHOW PLAN command, 130, 199, 338, 591, 592SHOWPLAN example

graphical, 340textual, 339

Signed applets, 554Sign-offs, and standardized change requests, 218Silos, 24

IT, in fractured environment, 259Simple recovery, 430Simplifying tasks, 609Single column indexes, 129Single distributed databases, 506Single tables mapped to multiple files, 296Single tables mapped to single files, 296

Singleton SELECT, 592Size

of data caches, 279–280and data warehouse scalability, 527of pages, 296of procedure caches, 282of system catalogs, 289

SLAs. See Service-level agreementsSLM. See Service-level managementSmall computer system interface, 486, 487Small database objects, full image copies of, 413SMALLINT, 123SMON. See System monitorSMS. See System-managed storageSNA, 24, 269Snapshot replication, 444, 445Snapshot tables, and distributed data placement,

309Snowflake schema, for data warehouse, 520, 521Social security numbers, 560Softbase Systems Inc., 633Softlab Corporation, 635Software, 537

allied agent, 269, 292auditing, 583backup and recovery, 242database gateway, 546database management system, 2data encryption, 404DBMS, 54, 55, 207, 225, 612, 620ETL, 502, 525failures, 21, 237message queuing, 269messaging, 503newsreader, 638new versions/releases of, 67open source, 628personal database, 57storage management, 425, 436supporting, 74tape-compression, 461

Software AG, 52, 552, 628Software fixes, vendor, 603Software tools, and DBMS, 53Solaris (Sun Microsystems), 74Solid state devices, for optimizing disk access, 271Solid state disk, 465Solid state storage, 24Sort area, for Oracle instance, 273Sort caches, 276Sorting, 298

avoiding, 349and index creation, 131

Index 695

index_661-703.qxd 5/22/02 2:34 PM Page 695

Sorting continuedindexes used for avoiding, 332–333tools, 262, 601

Source database, 232Space

for databases, 468for index storage, 477, 478for tables, 476

Space calculators, 600Space management, 472–479

data page layouts, 473–476index page layouts, 476–478transaction logs, 479tools, 579, 599

Space map page, 474Space usage, 474SP_BINDRULE system procedure, 364SP_DBOPTION, in SQL Server, 287Speed, and recovery strategy, 438Speed tables, 150, 301Spikes, disk usage, 466Split tables, 143–145, 301SP_MONITOR (Sybase), 291SPUFI, 66n.3SP_UNBIND system procedure, 364SQL. See Structured Query Language SQL Central (Sybase), 263SQL data definition language, physical database

objects created with, 154SQLJ, 166, 556, 557SQL*Loader, 504SQL*Plus, 66n.3SQL Server. See Microsoft SQL Server SQL statements, 161–162, 165, 214, 253, 587

ALTER, 211and application performance tools, 590–591columns grouped/sorted in, 303COMMIT, 320, 348CREATE, 427DELETE, 497DROP, 585GRANT, 20and indexing, 135–136, 298INSERT, 283, 419and parallelism, 297prefixing with EXPLAIN command, 338and query analysis, 323–325reviewing, 199SELECT, 300, 345, 498SELECT list of, 332and triggers, 40and Virtual Private Database, 401

SQLSTATEs, 82SQL tuning

additional tips, 348–349and DBA, 343

SSOs. See Security administratorsStaffing considerations, 30–35

DBA reporting structures, 33–35optimal number of DBAs, 30–33

Stand-alone SQL, 170Standard abbreviations, 78–79Standard Generalized Markup Language, 549, 550Standardization, and change management, 205, 206Standardized change requests, 218–219Standards

data administration, 79, 81database, 76, 81database application development, 82database security, 82–83system administration, 82

Standby databases, 232, 236, 443–444, 459Standby instances, 232Standby systems, 230Standby tables, 232Star schema, for data warehouse, 519, 520, 521START command, 528STATEMENT_ID clause, 340Statement-level triggers, 371Static data, backing up, 422Static SQL, 170Statistics, database, 322–323Statistics gathering utilities, 241STATUS_CODE column, in PROJECT_STATUS table,

381, 382STATUS column, 495, 496Stewardship metadata, 568Stock values, and downtime, 227STOGROUP-defined tablespaces, 578Storage

of backup materials, 452and compression tools, 600and data interleaving, 137with DBMS, 621of derived data, 148growing field of, 490index, 131multiple devices for, 469network-attached, 467, 487options, 479–480planning for future, 488–489RAID, 232requirements, 62–63short-term persistent, 472

696 Index

index_661-703.qxd 5/22/02 2:34 PM Page 696

and size terminology, 468system-managed, 471for tables, 128, 475

Storage area networks, 24, 232, 467, 486–487benefits with, 487network-attached storage vs., 487–488

Storage consumption, views on, 489Storage function, in XML Extender, 552Storage management, 465–468

backups, 458–459software, 425, 426, 436

Storage media, data sets in, 465Storage systems, building, 468Storage technologies, evaluating, 465–466Stored data, and DTD, 551Stored procedure privileges, 391Stored procedures, 39, 77, 348, 366

administering, 40, 41, 42for security, 400–401

Striping, 481Structural data integrity, 241Structural problem management, 353–355

consistency checking, 354database checking, 354memory usage, 354–355other DBCC options, 355

Structural problems, types of, 352–353Structured Query Language, 31, 194, 624

analysis and application, 592analysis tools, 262, 341, 591and application code design review, 199–200application performance and types of, 319classes in, 85coding and/or tuning, 82, 170–171, 343–349and database administrators, 32database application development and, 160–171debugging tools, 598design review, 199embedding in program, 165flexibility of, 162, 163, 347functions and summary tables, 530interactive, 233Introduction class, 85middleware, 165–166monitors, 253, 349object orientation and, 167–169and performance problems, 252performance tips and techniques, 82queries, 342rules of thumb, 344–348standard, 612standard Web site, 644

tweaking, 342types of, 169–170usage considerations, 162, 170and view referencing, 336WHERE clauses, 130, 300, 360

STUDENT data, unnormalized, 110STUDENT data model, 114STUDENT Entity

in first normal form, 110in third normal form, 113

Subject-oriented data, and data warehouse, 516Subqueries, joins vs., 163Subscribers, and replicated/propagated data, 237Subsystem failure, recovering from, 439Subtypes, 116SUM function, 530Summary tables, automated, 529–530Sungard Recovery Services, 447Sun Microsystems, 74, 554Support charges, 612Support numbers, for vendors, 601Surge protectors, 461Swap area, 268, 269Sybase, 45, 50, 51, 338, 356, 626, 627

Adaptive Server Anywhere, 45ALLOW UPDATES parameter provided by, 290BCP utility, 411constraints in, 363and data types, 358DBCC utility program, 353DUMP utility, 594and rules, 364SP_MONITOR, 291SQL Central, 263transition tables, 369, 370triggers supported by, 377vendor partnerships with, 603Web site for, 641

Sybase segments, 470Symmetric replication, 444Synchronization of data, from PDAs, 46Synchronous writes, and data caches, 279Synonyms, 99, 100Syntax

check constraint, 360and DBA certification tests, 47SQL, 161

SYSADM authority, and job scheduler, 405SYSADMs. See System administratorsSYSOPR. See Operations controlSysplex (IBM), 244, 245System administration, 15–16

Index 697

index_661-703.qxd 5/22/02 2:34 PM Page 697

System administration standards, 82System administrators, 56

assistance from, 611and corrupt data, 235and database administrators, 16and DBMS, 398education classes for, 84responsibilities of, 15–16, 17and security, 405and security reporting, 396

System capacity, measuring against requirements,489

System catalog, 215, 216backing up, 409, 423and DB2 COPY utility, 416file protection for, 404as metadata source, 567placement of, 288–289and query tools, 584recovering at remote site, 458security of, 396

System catalog tables, and auditing, 403System checkpoint, 284System data files, and Oracle tablespaces, 272System DBAs, tasks of, 25–26System dump files, DBMS, 62System global area, for Oracle instance, 273System libraries, backing up, 423System maintenance, shrinking window on, 224System-managed referential integrity, user-managed

referential integrity vs., 379System-managed storage, 471System monitor, Oracle, 273System monitoring, 290–291System parallelism, 335System parameters of DBMS, configuring, 65System performance, 267–292

larger environment, 268–271tools, 588–590

System privileges, 391granting, 393

Systems analysis and tuning tools, 262Systems management software, and DBMS, 66Systems management tools, 24Systems programming representatives, in design

review, 193System tuning, 260

TTable access, and index design, 131Table data, interleaving, 138Table editing tools, data movement with, 504

Table editors, 587Table-level check constraints, 362–363Table names, encoding, 78Table privileges, 391

granting, 392Table records, elements in, 475Tables, 77, 210, 212

adding/dropping declarative RI with, 376after denormalization, 147clustered, 137, 302, 303columns added to end of, 214columns added to middle of, 215–216columns from, 153combined, 145creating correct indexes on, in database,

298and database locks, 176and data interleaving, 137denormalized, 141, 301disorganized, 311, 312dropping, 235, 579dynamic performance, 314, 315entities transformed to, 122floating-point data loaded into, 496hierarchy, 149inner, 325, 326joining, 325limiting size of, 470loading with nullable columns, 496LOAD utility for populating, 495mirror, 142–143number of indexes for, 299outer, 325, 326and page sizes, 309and poorly performing SQL, 252prejoined, 141primary keys identified for, 124–125recovery of, 414redundant data stored in, 301report, 141–142rows from, 153sequential access of, and clustering, 303speed, 150split, 143–145storage for, 128tracker, 257transition, 369transition variables and, 369–370unclustered, 311

Table scans, 130, 133, 134, 327, 330, 332and data caches, 281and poorly performing SQL, 252

698 Index

index_661-703.qxd 5/22/02 2:34 PM Page 698

Table size, calculating, 476Tablespaces, 77, 127, 289, 593

and database locks, 176disorganized, 311, 312dropping, 235free space in, 215, 305organized, 312and page sizes, 309partitioned, 296recovery of, 414, 442reorganizing, 314transportable, 504

Tablespace scans, 332, 327, 330Tags, 549, 550, 551Tamino (Software AG), 552, 627Tampering with data, 402Tape

backups, 456–458DBMS, 63degradation, 353, 408devices, 465storage, 480

Tape-compression software, 461Tape machines, 24Tape management libraries, backing up, 423Target database, 232Task-oriented DBAs, 29TBCHECK utility (Informix), 353TCP/IP, 24, 269Teams, in DBMS environment, 56Technical education classes, 84–85Technical support, 538

and DBAs, 611, 612in design review, 193online, 602

Technical support numbers, response of, 602, 603Technicians, for DBMS, 54Technology

and business, 610–611and database administrators, 38–46data synchronization, 46Java, 554–558keeping up-to-date on, 612–613metadata, 566new, 549–558XML, 549–553

Temporary files, and Oracle tablespaces,272

Teradata (NCR), 52Terrorist attacks, 448Test beds, maintaining, 500–501Test databases, populating, 443

Testing, 37and indexing, 299of invasive system performance tools, 590procedures, 610program, 234recovery plans, 440–441, 460tools, 594, 598utility tools, 594

Textbooks, retaining, 608Textual SHOWPLAN example, 339Themis, 85Thin clients, 545Thin server/fat client, 24Third-generation languages, 161Third normal form (3NF), 112–113, 196

MAJOR Entity in, 113STUDENT Entity in, 113

Third-party software, and transaction recovery,433–434

Third-party tools, 238, 261, 349, 601for automating reorganizations, 315and dropped database objects, 236OLAP, 597recovery, 456

Third-party utilities, requirements for, 594Third-party vendors

and database utilities, 594DBA tools from, 577gateway software through, 546import and export products through, 502

Throughput, 408, 512and contention, 19and database performance, 18, 251and performance in distributed environment, 511

Tier-1 DBMS vendors, 51, 52Tier-2 DBMS vendors, 52Time dimension, 517Timeouts, 185

and deadlocks, 180lock, 179minimizing, 288

TimesTen Performance Systems, 480Time-variant data, and data warehouse, 516Time zones, and 24/24 availability, 225TINYINT, 123T1/T3 lines, 547Tool vendors

data modeling, 634–635DBA, 631–636

Top Secret (Computer Associates), 386Total errors number, SP_MONITOR, 291Total read number, SP_MONITOR, 291

Index 699

index_661-703.qxd 5/22/02 2:34 PM Page 699

Total write number, SP_MONITOR, 291Toysmart.com, 9TPC. See Transaction Processing Performance

CouncilTPC-C benchmark, 55TPC-H benchmark, 55TPC-R benchmark, 55TPC-W benchmark, 55Trace information, 588Tracker tables, 257Training. See also Education

for DBAs, 11, 238specialists, 85vendor, 602

Transaction design/processing, and applicationperformance, 320

Transaction failures, 408, 439Transaction identification, and recovery strategy, 437Transaction logs, 283, 284, 419, 479

backing up, 286, 420, 421, 470and data loss, 236raw partition for, 471and recovery scenarios, 285sizes of, 479space for, 468–469

Transaction processing, analytical processing vs.,516–518

Transaction processing monitors, 66, 173Transaction Processing Performance Council, 53, 55Transaction processing (TP) systems, 24, 173–175Transaction processors, 269

and DBMS releases, 69physical design review and choice of, 197system administrators and configuration of, 16

Transaction recovery, 21, 433–434, 437Transactions

and ACID properties, 172–173defining, 171–173guidelines, 173

Transaction servers, 173, 174monitoring, 291using, 175

Transact-SQL, 598Transition tables, 369, 377Transition variables, and tables, 369–370Translators, 557Transparency, distributed databases and, 505, 506Transportable tablespaces, Oracle, 504Trigger-based RI, declarative RI favored over, 377Triggers, 39, 77, 78, 139, 212, 215, 216, 382, 418

administering, 40, 41, 42and declarative RI, 376, 377

dropping, 579firing times, 367–368granularity, 371“hidden” impact of, 349for implementing referential integrity, 369and LOAD utility, 494nested, 368–369referential integrity using, 376–379sample, 370scenarios supported by, 367statement-level, 371synopsis, 371transition variables and tables, 369–370

TRUNC LOG ON CHKPT database option, 287, 421Tuning, SQL, 343–349Tuning boxes, 267Tuxedo, 24, 173Tweaking SQL, 34224/7 availability, 221, 224, 225, 31624/7 uptime, 24624/24 availability, 225Two-phase COMMIT, 511

UUDFs. See User-defined functionsUDTs. See User-defined data typesUML. See Unified Modeling LanguageUnclustered data, and data disorganization, 310Unclustered index access, 330Unclustered indexes, 302UNCOMMITTED READ isolation, 181, 182, 183, 529Understanding Relational Databases (Pascal), 624UNDO recovery, 435, 436Unfederated multidatabase setup, 506UNICODE, 499Unified Modeling Language, 94

sample class diagram, 95UNION operation, 276

and clustering tables, 137and queries, 131and sorting, 333

UniQSL, 629Unique constraints, 22, 78, 495Unique indexes, 129Unit of work, 173, 177Unit testing, 38, 83University of California, Berkeley, 627UNIX, 24, 53, 51, 56, 628

buffers, 471environment and root authority, 405machines based on, 541platforms, 6, 74

700 Index

index_661-703.qxd 5/22/02 2:34 PM Page 700

raw partition and file system in, 297servers, 50

UNLOAD data, limiting, 499–500Unloads, 424UNLOAD utility, 142, 241, 443, 495, 497, 502, 512,

523, 593concurrency, 498data encoding scheme, 499and data movement, 493, 494floating-point value, 499generation of LOAD parameters, 499limiting UNLOAD data, 499–500purpose of, 498test beds of data maintained with, 500unloading from image copy backups, 498–499unloading from views, 500used for program testing, 501

Unnamed constraints, 362Unplanned outages, 238, 239Unplanned SQL, 169UOW. See Unit of workUpdated pages, in data caches, 279Update function, in XML Extender, 552Update locks, 178UPDATE privilege, granting, 392UPDATE processing, 360UPDATE rule, 373UPDATE statement, 66, 162, 185, 283, 587

and data modification, 310log records for, 419and transition tables, 370and triggers, 40, 366, 368

UPDATE STATISTICS command, 322UPDATETEXT statement, 286UPDATE trigger, for referential constraints, 376,

377Upgrades

benefits vs. risks with, 68–70and change, 207fees with, 604multiple release, 70

Upsizing, 538–539UPS systems, 231, 408Uptime, 222, 227. See also DowntimeUsage patterns, and repositories, 570–571Usenet newsgroups, 637–639USE_NL hint, 342User acceptance testing, 83User connections, and memory consumption, 277User-defined data types, 123, 358–359, 364, 552User-defined functions, 39, 40, 41, 42, 552User errors, and database recovery, 437

User groups, 611, 644attending, 612and mailing lists, 640

User IDs, 387, 405User-level transactions, examples of, 434User-managed referential integrity, system-managed

referential integrity vs., 379User names, 390User processes, Oracle, 273User references, and DBMS vendors, 54Users

database administrator staffing and number of, 31and error messages, 461–462and security, 389–390talking to, 610

Utility management tools, 595Utility manager, 595Utility tools, testing, 594

VValue, nulls and lack of, 100–101Values

attribute, 99and direct index lookup, 328identity, 289lock timeout, 179

Vandalism, 448Variable-length columns, 124, 128, 300Variable-length index keys, 477Variable-length rows

and free space, 306size of, 475

VBScript, 167VCAT. See Volume catalogVendors

data modeling tools, 92, 634–635data movement and business intelligence, 636DBA tools, 601–604, 631–636DBMS, 49, 60, 68, 85, 625–630and mailing lists, 640relational optimizers of, 321–322and release schedules for DBMS, 54repository, 635reputation of DBMS, 72support for RAID levels by, 481technical support by, 611, 612tier-1 DBMS, 51tier-2 DBMS, 52and versions/releases, 67Web sites for, 641and XML-based DBMSs, 552

Verbs, tracking, 104

Index 701

index_661-703.qxd 5/22/02 2:34 PM Page 701

Versant, 52Versions

DBMS, 16, 67–68releases versus, 67and repositories, 569

Vertically split tables, 143Vertical restriction, 400Very critical applications, 449–450Very large databases, 52Very large objects, 352VI, 488Views, 77, 152–154, 216

access to, 335–336columns from, 153database, 152–154dropping, 236, 579materialization of, 336merging, 336rows from, 153for security, 399–400unloading from, 500uses of, 154

Virtual Private Database, Oracle9i, 401Viruses, 448Visible Systems, 635Visual Basic, 165, 167, 199, 261, 597Visual dBase, 57Visual Explain tool, DB2, 341Visual Foxpro, 630Visual Studio, 570VLDBs. See Very large databasesVLDB Report (Winter Corporation), 467Volatility

of data, 422, 423and database administrators, 32

Volume catalog, 578VPD. See Virtual Private DatabaseVSAM, 24, 502V$ tables, 315VTAM, 24

WWalkthroughs, 592WANs. See Wide-area networksWar, 448Warehousing, 18. See also Data warehouses/

warehousingWeb, 637. See also Internet

database connection to, 547database management integrated with,

221

keeping up-to-date with, 612XML and future of, 552

Web-based applications, and eDBAs, 43Web-based training, 85Web browsers, Java-enabled, 554Web-enabled databases/applications, 24“Web month,”558Web pages, and Java capabilities, 554Web portals, 609, 640Web servers

and DBMS, 66Java applets on, 554

Web sites, 548, 609, 611, 643–645consultant, 642DAFS Collaborative, 488disaster and contingency planning, 462magazine, 641–642vendor, 641

WebSphere (IBM), 175Webster’s New Collegiate Dictionary, 565Web support personnel, in design review, 193Web-to-database capabilities, complex infrastructure

enabling, 44WHEN clause, 500WHERE clause, 171, 310, 328, 334, 592

clustered indexes and predicates in, 303mass deletes and, 497and row returns, 345and Virtual Private Database, 401

White papers, retaining, 608Wide-area networks, 459Wild-card character (%), 347Windows NT servers, 50, 244Windows NT systems, CIFS on, 487Windows NT/2000, 56Windows operating system, 24Windows 2000, 6–7Winter Corporation, 467Wisdom, 565WITH CHECK OPTION, 400WITH GRANT OPTION, 390, 391, 396, 399Work files, DBMS, 62Workgroup DBMS, 56Workload, and database performance, 18, 250–251World Trade Center bombings (1993, 2001), 448World Wide Web. See WebWORM (write once, read many) technologies, 480Wrappers, 551Write-ahead logs, 284Write locks, 178WRITETEXT statement, 286

702 Index

index_661-703.qxd 5/22/02 2:34 PM Page 702

XXML. See eXtensible Markup LanguageXMLCLOB, UDT defined for, 552XML Extender, 552XMLFILE, UDT defined for, 552XML portal Web site, 645XML Schema, 551XMLVARCHAR, UDT defined for, 552XSL. See eXtensible Stylesheet language

YYevich, Lawson & Associates,Web site for, 642

Z“Zapping”database, 355Zip codes, 560z/OS, data sharing and DB2 for, 245

Index 703

index_661-703.qxd 5/22/02 2:34 PM Page 703