DB2 universal database certification guide / Calene Janacek, Dwaine Snow.

CONTENIDO
1. Product Overview 1
DB2 Universal Database - The Scalable Database 2
DB2 Products 2
DB2 Universal Database (UDB) 4
DB2 UDB Personal Edition 4
DB2 Workgroup Edition 5
DB2 Enterprise Edition 7
DB2 Universal Database Enterprise-Extended Edition 8
DB2 Connect 9
DB2 Connect Personal Edition 10
DB2 Connect Enterprise Edition 11
DB2 Developer's Edition 12
Licensing Scenarios 15
DB2 Client/Server Environment 20
Supported Communication Protocols 20
DB2 Resource Usage 21
Features and Functions 21
DB2 Universal Database Administrator's Tools 24
The Control Center 25
The SmartGuides 27
The Command Line Processor (CLP) 31
Summary 33
Questions 34
Answers 36
2. Getting Started and Connected 37
Product Installation 38
Intel Installation 38
UNIX Installation 47
DB2 Client Installation 56
The DB2 Environment 61
DB2 Profile Registry 61
Environment Variables 63
Using the Command Line Processor 64
DAS and DB2 Instances 66
DB2 Administration Server (DAS) Instance 66
DB2 Instances 68
Creating the Sample Database 72
Summary 77
Questions 78
Answers 79
Exercises 80
3. Controlling Data Access 85
Security 86
Overview of Security 86
Authentication 87
Authorization Levels 91
Security Considerations 95
DB2 Privileges (Data Control Language) 97
Database Object Privileges 97
Establishing a DB2 Client/Server Connection 107
Configuring a DB2 UDB Server 108
DB2 Directories 116
DB2 Discovery 120
Generating a Server Access Profile 124
Configuring DB2 Clients 124
Using the Client Configuration Assistant 126
Configuring a Connection to a DB2 Database 133
Instance Administration 148
Summary 153
Questions 154
Answers 157
Exercises 158
4. Using SQL 165
Understanding Database Objects 166
Data Types 166
DB2-Supplied Data Types 167
Large Objects (LOBs) 167
String Data Types 170
Numeric Data Types 171
Datetime Data Types 173
User-Defined Data Types 175
Code Page Considerations 179
Selecting the Correct Data Type 181
DB2 Certification Database Design and Implementation 182
Tables 191
Using SQL Data Definition Language (DDL) 200
Creating a DB2 Database 202
Views 203
Indexes 208
Data Manipulation Language (DML) 212
Data Retrieval (SELECT) 212
Selecting Columns from Multiple Tables 218
DB2 Functions 226
Data Modification (INSERT/UPDATE/DELETE) 246
Removing Data 250
View Classification 254
Business Rules 258
User-Defined Defaults 258
Check Constraints 259
Triggers 261
Advanced SQL 265
Recursive SQL 265
Outer Join 268
OLAP Features 272
Advanced Case Expressions 277
Summary 280
Questions 281
Answers 283
Exercises 284
5. Database Concurrency 295
Concurrency 296
Concurrency Considerations 296
Lost Update Problem 297
Uncommitted Read 301
Non-Repeatable Read 301
Phantom Read Problem 303
Isolation Level 304
Uncommitted Read 305
Cursor Stability 305
Read Stability 305
Repeatable Read 306
Choosing an Isolation Level 306
Locking 306
Lock Attributes 307
Lock Conversion 309
Lock Escalation 310
Lock Wait Behavior 311
Summary 313
Questions 314
Answers 314
Exercises 315
6. Data Placement 321
Containers 322
Table Spaces 322
Extents 324
System Managed Storage (SMS) Table Spaces 325
Database Managed Storage (DMS) Table Spaces 327
Planning a Table Space Environment 330
Creating Table Spaces 336
Performance Considerations 345
Buffer Pool 345
Extent Size 346
NUM_IOSERVERS 347
I/O Prefetch 347
Page Cleaners 348
System Catalogs in DB2 349
Managing Table Spaces 353
States of Table Spaces 361
Summary 363
Questions 364
Answers 366
Exercises 367
7. Data Management 379
Populating Tables 380
The LOAD Utility 380
Using The LOAD Utility 383
Syntax of the LOAD Command 386
The IMPORT/EXPORT Utility 402
The EXPORT Utility 403
The IMPORT Utility 410
Data Maintenance 414
Analyzing Data's Physical Organization: REORGCHK 415
Table Reorganization: REORG 420
RUNSTATS 425
The REBIND Utility 430
Data Maintenance Process 431
Modelling a Production Environment 432
User Maintenance 434
Fenced and Not Fenced Procedures and Functions 434
Database Recovery 438
Use of Log Files 439
Types of Logging 443
Backup and Restore 447
Restore Database SmartGuide 462
Summary of Backup/Restore Consideration 473
The QUIESCE Command 475
The Recovery History File 476
Administration Tools for Data Maintenance 479
The Script Center 480
The Journal 483
Data Replication 487
Summary 490
Answers 495
Exercises 496
8. Database Monitoring and Tuning 503
Monitoring Methods 504
Obtaining Database Access Information 504
Explain Facilities 504
Query Compilation 505
Gathering Explain Data 508
Examining EXPLAIN Data 513
Database Monitoring 521
Event Monitoring 531
Visual Performance Monitors 538
Performance Monitor 542
DB2 Governor 550
Performance Issues 551
Configuring Database Resources 553
DB2 Sorting Methods 558
DB2 Server Resources 561
Query Parallelism 562
Performance Tuning Scenario 568
Diagnostics and Problem Determination 571
Error Messages and SQL Codes 571
Tracing Problems in DB2 Universal Database 577
Database Analysis and Repair Tool 581
Summary 585
Questions 586
Answers 591
Exercises 593
9. DB2 Application Programming 597
DB2 Application Development Environment 598
Client/Server Platform 598
Choosing a DB2 Programming Method 601
Static Embedded SQL 601
Dynamic Embedded SQL 602
Call Level Interface (CLI) 603
DB2 Application Programming Interfaces (APIs) 604
Accessing Data from an Application 606
Using Schemas 606
Using an Alias 607
Creating Packages 608
Binding Applications 611
Binding Utilities 615
Parts of a DB2 Embedded Static SQL Application 617
Host Variables 617
Indicator Variables 626
Using Cursors 631
Handling Application Termination 638
Interrupt Handling 638
Large Objects 640
Direct LOB Reference 640
Reference by LOB Locator 641
LOB File Reference Variables 644
Unique Keys and Referential Integrity 646
Online Analytical Processing 648
STAR Schemas 648
OLAP Indexes 650
Dynamic Bitmap Index ANDing 650
STAR Joins 651
OLAP SQL 653
Summary 654
Questions 655
Answers 668
Exercises 671
10. DB2 Advanced Application Programming 673
Writing Dynamic Programs 674
First Look at Dynamic SQL 675
Dynamic SQL Phases 677
Types of Dynamic SQL Statements 680
Dynamic SQL - Callable SQL Interfaces 685
ODBC vs CLI 686
Embedded Dynamic vs Call Level Interface 687
Setting Up a CLI Development Environment 687
Stored Procedures 691
User Defined Functions 692
Support For Java Programming 696
Compound SQL 697
Summary 699
Questions 700
Answers 708
Exercises 711
Appendix A - Test Objectives
DB2 UDB V5 Fundamentals (503) 713
Appendix B - Test Objectives
DB2 UDB V5 Database Administration (504) 717
Appendix C - Test Objectives
DB2 UDB V5 Application Development (505) 721
Appendix D - DB2 Tools and Utilities 725
Appendix E - DB2 Example Source Code 727
Index 757
CD-ROM Installation