Beginning DB2: From Novice to Professional

✍ Scribed by Grant Allen

• Publisher: Apress
• Year: 2008
• Tongue: English
• Leaves: 543
• Series: Expert's Voice
• Edition: 1
• Category: Library

✦ Synopsis

IBM’s DB2 Express Edition is one of the most capable of the free database platforms available in today’s marketplace. In Beginning DB2, author Grant Allen gets you started using DB2 Express Edition for web sites, desktop applications, and more. The author covers the basics of DB2 for developers and database administrators, shows you how to manage data in both XML and relational form, and includes numerous code examples so that you are never in doubt as to how things work. In this book, you’ll find

• A friendly introduction to DB2 Express Edition, an industrial–strength, relational database from IBM
• Dozens of examples so that you are never in doubt as to how things work
• Coverage of important language interfaces, such as from PHP, Ruby, C#, Python, and more
• Aimed at developers who want a robust database to back their applications

What you’ll learn

Install and manage DB2 Express Edition on Linux and Windows.

Manage IML data seamlessly using IBM’s pureXML storage engine.

Connect DB2 databases to web and desktop applications via languages such as PHP, Ruby, C#, Python, and more.

Protect your data with a sound backup and restore strategy.

Design and manage database schemas and security within the database.

Know when and why to upgrade to more powerful editions of DB2.

Who is this book for?

Application developers, including web application developers, looking for a robust, zero–cost database to back their applications. Database administrators looking to get up to speed quickly with DB2.

About the Apress Beginning Series

The Beginning series from Apress is the right choice to get the information you need to land that crucial entry–level job. These books will teach you a standard and important technology from the ground up because they are explicitly designed to take you from “novice to professional.” You’ll start your journey by seeing what you need to know—but without needless theory and filler. You’ll build your skill set by learning how to put together real–world projects step by step. So whether your goal is your next career challenge or a new learning opportunity, the Beginning series from Apress will take you there—it is your trusted guide through unfamiliar territory!

✦ Table of Contents

Beginning DB2: From Novice to Professional......Page 1
Contents at a Glance......Page 6
Contents......Page 9
About the Author......Page 21
About the Technical Reviewer......Page 23
Acknowledgments......Page 25
How This Book Is Structured......Page 27
Contacting the Author......Page 28
Who Should Read This Book?......Page 31
DB2 Will Be Even Bigger in the Future......Page 32
Choosing the Right DB2 Edition For You......Page 33
Obtaining DB2: A World of Choice......Page 34
Summary......Page 37
Installing on Linux......Page 39
Using the DB2 9 Discovery Kit DVD......Page 40
Disk......Page 41
Users and Groups Under Linux......Page 42
Graphical Installation Under Windows and Linux......Page 43
Command-Line Installation Under Linux......Page 60
Uninstalling DB2......Page 62
Summary......Page 63
DB2 First Steps......Page 67
Launching DB2 First Steps Under Windows......Page 68
Launching DB2 First Steps Under Linux......Page 69
Working with DB2 First Steps......Page 71
Creating the SAMPLE Database......Page 73
Starting the DB2 Control Center from First Steps......Page 75
Starting the DB2 Control Center in Other Ways......Page 76
Selecting Your Preferred DB2 Control Center View......Page 77
Browsing the DB2 Control Center Object Views......Page 78
Invoking the Create Table Control Center Wizard......Page 80
Using the DB2 Command Editor......Page 83
Powering Up the Command Line Processor......Page 86
Learning About the Commands......Page 87
Connecting and Disconnecting......Page 88
Summary......Page 89
Getting Started as the Right User......Page 91
From the Control Center......Page 92
From the Command Line......Page 94
Starting and Stopping the DAS Instance......Page 95
Starting and Stopping Database Instances......Page 97
DB2 Instances and the DAS Instance......Page 98
The DB2 Management Service......Page 99
The DB2 Fault Monitor Coordinator......Page 100
Configuring and Changing Your DB2 Instances and Databases......Page 101
Two Different Levels of Configuration......Page 102
Configuring Your DB2 Instances from the Control Center......Page 103
Configuring a DB2 Database from the Control Center......Page 106
Attaching to a DB2 Instance......Page 107
Issuing the Commands to Change Instance Parameters......Page 108
Issuing the Commands to Change Database Parameters......Page 109
The Version Information Utility: DB2LEVEL......Page 110
The Instance Listing Utilities: DB2ILIST and DASLIST......Page 111
The DB2 Problem Determination Tool: DB2PD......Page 112
Summary......Page 113
IBM Data Studio......Page 117
Sourcing the Data Studio......Page 118
Multiple Installers for Data Studio......Page 119
The Power of Eclipse-Based IDE Installations......Page 120
Running Data Studio......Page 122
Starting a New Data Project......Page 123
New Project Connection Management......Page 125
New SQL Statement Wizard......Page 128
New Stored Procedure Wizard......Page 132
Summary......Page 137
Manipulating Data with SQL......Page 139
Using Select Statements......Page 140
Using DB2 Registers......Page 148
Using DB2 Functions and Procedures......Page 151
Using Insert Statements......Page 152
Using Update Statements......Page 153
Summary......Page 154
Data Types in DB2......Page 155
The Strings......Page 156
The Date and Time Data Types......Page 157
Your First Table......Page 158
Naming DB2 Tables......Page 159
Working with Null Data......Page 160
Primary Key Constraints......Page 161
Referential Constraints......Page 162
Check Constraints......Page 164
Disabling Constraints and Constraint Deferral......Page 165
Automatic Value Generation for Columns......Page 166
Specifying Table Storage Characteristics......Page 168
Other Table Characteristics......Page 169
Summary......Page 170
Creating Stored Procedures in DB2......Page 171
Dropping Procedures......Page 180
Managing Procedures......Page 182
Further Reading on DB2 Stored Procedures......Page 183
Types of User-Defined Functions in DB2......Page 184
Creating User-Defined Functions in DB2......Page 185
Calling User-Defined Functions in DB2......Page 187
Summary......Page 189
Controlling Access to DB2......Page 191
Configuring Where and When DB2 Authenticates Users......Page 192
CLIENT Authentication Type......Page 193
KRB_SERVER_ENCRYPT Authentication Type......Page 194
Choosing Your Authentication Option......Page 195
DB2 Instance-Level Privileged Groups......Page 196
Changing Your DB2 Authentication Parameters......Page 197
Using Control Center to Manage Authentication Parameters......Page 198
Using DB2 CLP to Manage Authentication Parameters......Page 200
Configuring DB2 to Use Local or Global Groups......Page 201
Security Context of the DB2 Server......Page 202
Summary......Page 203
Database-Level Authorities......Page 205
Changing Database Authorities......Page 207
Group Lookup in DB2 on Windows......Page 210
The PUBLIC Group......Page 211
The Benefits of Roles over Groups......Page 212
Working with Privileges on DB2 Objects......Page 213
Granting Object Privileges to Users......Page 215
Building Blocks of LBAC......Page 218
Defining an LBAC Policy......Page 219
LBAC in Action......Page 220
Summary......Page 222
Exploring XML in the Sample Database......Page 223
Querying Your XML Data......Page 226
Using XQuery for XML......Page 227
Using XPath Queries for XML......Page 232
Using XPath Predicates......Page 233
Using XPath Wildcards......Page 234
More pureXML Features for Querying Data......Page 235
Inserting XML Data......Page 236
XML Schema Registration in DB2......Page 238
Updating XML Data......Page 240
Deleting XML Data......Page 241
Summary......Page 242
Creating Indexes......Page 243
Enforcing Unique Values......Page 245
The Important Case of Foreign Keys......Page 246
Reverse Scans......Page 247
Included Columns......Page 248
Using Design Advisor......Page 249
Index Wrap Up......Page 255
Creating Sequences......Page 256
Altering Sequences......Page 258
Using Sequences......Page 259
Defining Views......Page 261
Further View Options......Page 263
Summary......Page 265
PHP and Zend Core for DB2......Page 269
Under Windows......Page 270
Under Linux......Page 271
Continuing the Installation......Page 272
Post-Installation Checks......Page 275
Connecting to a Database......Page 276
Working with Ad Hoc SQL Statements......Page 280
Working with Prepared Statements and Stored Procedures......Page 283
Other IBM_DB2 PHP Functions......Page 285
Configuring the Zend Framework......Page 286
Managing Connections with the Zend Framework......Page 287
Executing Statements Using the Zend Core Framework......Page 288
Summary......Page 290
Installing Ruby and Rails......Page 291
Creating a Project with Rails......Page 295
Connecting a Rails Project to DB2......Page 296
Designing DB2 Tables in Rails......Page 297
Executing a Migration in Ruby for DB2......Page 299
Evolving Your DB2 Schema with Ruby on Rails......Page 300
The Schema_Info Table for Ruby on Rails......Page 301
Ruby on Rails Scaffolding for DB2......Page 302
Ruby on Rails Scaffolding in Action with DB2......Page 303
Summary......Page 305
Installing IBM Database Add-Ins......Page 307
Registering the IBM Data Server Provider for .NET......Page 309
Testing DB2 .NET Connectivity......Page 310
Testing the Database Add-Ins......Page 311
Managing DB2 Objects......Page 314
Creating a New DB2 View Object......Page 315
Building the DB2 Database Project......Page 317
Building .NET Windows Applications with DB2......Page 318
Binding DB2 Objects to Windows Controls......Page 319
Specifying a DB2 Connection for a Windows C# Project......Page 320
Building and Running the Windows Project......Page 322
Building .NET Web Applications with DB2......Page 323
Binding DB2 Objects to Web Controls......Page 324
Building and Running the Web Project......Page 326
Summary......Page 327
Developing Java Applications with DB2......Page 329
JDBC Driver Types......Page 330
Writing the First Java DB2 Application......Page 331
JDBC Connection URL......Page 332
Refactoring the Connection Code......Page 333
Working with Data......Page 335
Working with DB2 JDBC Features......Page 337
Writing the Stored Procedure Java Class......Page 339
Deploying the Java Stored Procedure Class......Page 340
Writing the Stored Procedure......Page 341
Testing the Java DB2 Stored Procedure......Page 342
Creating a pureQuery-Enabled Project......Page 343
Autogenerating pureQuery Java Mapping Classes......Page 346
Examining pureQuery Results......Page 349
Summary......Page 350
Perl......Page 351
Using Automated Installation......Page 352
Compiling from Source......Page 354
Getting Connected......Page 355
Executing SQL Statements......Page 356
Installing the Python DB2 Driver......Page 358
Building and Installing from Source......Page 359
Going Further with Python and DB2......Page 360
Rexx......Page 361
Summary......Page 362
Building Blocks of DB2 Databases......Page 365
Creating DB2 Databases......Page 366
Specifying an Alias......Page 367
Handling Languages and Text in DB2 Databases......Page 368
Understanding Tablespace Use in DB2......Page 370
Choosing the Right Tablespace Storage Management Type......Page 371
Using System Managed Storage Tablespaces......Page 372
Using Database Managed Storage Tablespaces......Page 373
Tuning Initial Tablespace Parameters......Page 374
Introducing Buffer Pools......Page 375
Moving Beyond Database, Tablespace, and Buffer Pool Basics......Page 376
Altering Databases......Page 377
Working with Tablespaces and Buffer Pools......Page 378
Dropping Databases......Page 384
Summary......Page 385
Permissions for Backup and Recovery......Page 387
General Logging Principles......Page 388
Circular Logging......Page 389
Archive Logging......Page 390
Infinite Logging......Page 393
DB2 Backups......Page 394
Anatomy of a Backup......Page 395
Taking Backups from the Control Center......Page 396
Advanced Backup Options from the Command Line......Page 400
Incremental and Delta Backups......Page 401
Restoring a Database from Backup......Page 403
Roll Forward Recovery from a Backup......Page 404
Database Recovery Using the Control Center......Page 405
Advanced Recovery Options from the Command Line......Page 409
Individual Tablespace Restore with Database Online......Page 410
Summary......Page 411
DB2 Clients on Linux and Windows......Page 413
Using the DB2 Express-C Edition Installer......Page 414
Using the Dedicated DB2 Client Installers......Page 415
Client Support......Page 418
Administration Tools......Page 419
Server Support (Windows Only)......Page 420
Deciding Which Components to Install......Page 421
The DB2 Client Configuration Assistant......Page 422
Examining Your Current Configuration......Page 428
Using the Catalog Command......Page 430
Discoverability Parameters......Page 432
Third-Party Connectivity Options......Page 433
Summary......Page 434
Delimited Format......Page 435
Exporting Data......Page 436
Exporting LOBs......Page 437
Exporting XML Objects......Page 439
Exporting via the Control Center......Page 440
Performing a Simple Import......Page 442
Dealing with Import Warnings and Errors......Page 444
Importing a Mix of New and Updated Data......Page 446
Performing More-Complex Imports......Page 447
Graphical Import Using the Control Center......Page 449
Loading Data with the Load Utility......Page 451
Summary......Page 454
Working with Design......Page 455
Database Design Overview......Page 456
Statement of Requirements......Page 457
Identification of Users and Use Cases......Page 458
Analysis of Use Cases and Data Requirements......Page 460
Finding Relationships in the Data......Page 461
Building the Logical Data Model......Page 463
Building the Physical Model in DB2......Page 464
Summary......Page 468
Using db2look......Page 469
Using db2move......Page 471
Using db2look from the Control Center......Page 473
Preparing for Replication......Page 474
Launching the Replication Center......Page 475
Configuring the Replication Center......Page 476
Creating the Capture Control Tables......Page 478
Creating the Apply Control Tables......Page 479
Testing Replication in Action......Page 480
Data Row Compression......Page 482
Data Row Compression Design......Page 483
Estimating Compression Savings......Page 484
Compressing a Table......Page 485
Summary......Page 486
Starting with a Sensible Approach to Tuning......Page 487
Proactive Tuning with the Configuration Advisor......Page 488
Understanding Locking in DB2......Page 492
Continuing with the Configuration Advisor......Page 494
Traditional DB2 Memory Management Model......Page 496
Activating STMM Mode......Page 497
Self-Tuning Memory Management Mechanics......Page 498
Using the Memory Visualizer......Page 499
Monitoring and Tuning Database Activity......Page 500
Using DB2 Event Monitors......Page 501
Using DB2 Snapshot Monitors......Page 506
Automated RUNSTATS in DB2......Page 509
Manually Running the RUNSTATS Command......Page 512
Working with REORGCHK......Page 513
Working with REORG......Page 515
Summary......Page 517
Index......Page 519