5PPER3ADDLE2IVER.*s"OSTONs)NDIANAPOLISs3AN&RANCISCO
.EW9ORKs4ORONTOs-ONTREALs,ONDONs-UNICHs0ARISs-ADRID
#APETOWNs3YDNEYs4OKYOs3INGAPOREs-EXICO#ITY
DB2
®
9 for Linux
®
, UNIX
®
,
and Windows
®
DBA Guide, Reference, and Exam Prep
Sixth Edition
George Baklarz and Paul C. Zikopoulos
The authors and publisher have taken care in the preparation of this book, but make no
expressed or implied warranty of any kind and assume no responsibility for errors or omis-
sions. No liability is assumed for incidental or consequential damages in connection with or
arising out of the use of the information or programs contained herein.
© Copyright 2008 by International Business Machines Corporation. All rights reserved.
Note to U.S. Government Users: Documentation related to restricted right. Use, duplication, or
disclosure is subject to restrictions set forth in GSA ADP Schedule Contract with IBM Corporation.
IBM Press Program Managers: Tara Woodman, Ellice Uffer
Cover design: IBM Corporation
Associate Publisher: Greg Wiegand
Marketing Manager: Kourtnaye Sturgeon
Publicist: Heather Fox
Acquisitions Editor: Bernard Goodwin
Managing Editor: John Fuller
Cover Designer: Alan Clements
Project Editor: Elizabeth Ryan
Proofreader: Diane Freed
Manufacturing Buyer: Anna Popick
Published by Pearson plc
Publishing as IBM Press
IBM Press offers excellent discounts on this book when ordered in quantity for bulk purchases
or special sales, which may include electronic versions and/or custom covers and content par-
ticular to your business, training goals, marketing focus, and branding interests. For more
information, please contact: U.S. Corporate and Government Sales, 1-800-382-3419,
corpsales@pearsontechgroup.com.
For sales outside the United States, please contact: International Sales,
international@pearsoned.com.
The following terms are trademarks or registered trademarks of International Business
Machines Corporation in the United States, other countries, or both: IBM, the IBM logo, IBM
Press, Alphablox, Passport Advantage, DB2, DB2 Connect, DB2 Extenders, Data Propagator,
Cloudscape, IMS, Informix, Rational, ClearCase, Lotus, 1-2-3, Sametime, WebSphere,
MQSeries, Tivoli, pureXML, AIX, OS/400, OS/390, S/390, AS/400, z/OS, i5/OS, VSE/VM,
System i, System z, System x, System p, iSeries, DS6000 and DS8000.
Java and all Java-based trademarks are trademarks of Sun Microsystems, Inc. in the United States,
other countries, or both. Microsoft and Windows are trademarks of the Microsoft Corporation
in the United States, other countries, or both. Linux is a registered trademark of Linus Torvalds
in the United States, other countries, or both. UNIX is a registered trademark of The Open
Group in the United States and other countries. Intel and Itanium are trademarks or registered
trademarks of Intel Corporation or its subsidiaries in the United States and other countries.
Other company, product, or service names mentioned may be trademarks or service marks of
others.
Baklarz, Geor
ge.
DB2 9 for Linux, Unix, and Windows : DBA guide, reference and exam prep /
George Baklarz, Paul C. Zikopoulos. — 6th ed.
p. cm.
Revised ed. of: DB2 Universal Database v8.1 for Linux, UNIX, and Windows database
administration certification guide. 5th ed. c2003.
Includes index.
ISBN 0-13-185514-X (hbk. : alk. paper)
1. IBM Database 2. 2. Relational databases. I. Zikopoulos, Paul. II. Baklarz, George. DB2
Universal Database v8.1 for Linux, UNIX, and Windows database administration certification
guide. III. Title.
QA76.9.D3C6723 2007
005.75'65—dc22 2007039601
All rights reserved. This publication is protected by copyright, and permission must be
obtained from the publisher prior to any prohibited reproduction, storage in a retrieval system,
or transmission in any form or by any means, electronic, mechanical, photocopying, recording,
or likewise. For information regarding permissions, write to:
Pearson Education, Inc
Rights and Contracts Department
501 Boylston Street, Suite 900
Boston, MA 02116
Fax (617) 671-3447
ISBN-13: 978- 0-13-185514-4
ISBN-10: 0-13-185514-X
Text printed in the United States on recycled paper at Courier Westford in Westford, Massachusetts.
First printing, November 2007
Contents
Foreword ....................................................................................xxi
Preface .....................................................................................xxiii
Part ONE Introduction to DB2................................1
Chapter 1 Product Overview .....................................................3
Information as a Service ............................................................................ 4
The DB2 for Linux, UNIX, and Windows Data Server ............................ 9
DB2 Everyplace Edition ................................................................ 10
Apache Derby/IBM Cloudscape.................................................... 12
DB2 Personal Edition .................................................................... 13
The DB2 Express and DB2 Workgroup Editions.......................... 13
DB2 Enterprise Edition.................................................................. 20
DB2 Connectivity .................................................................................... 28
DB2 Clients.................................................................................... 29
DB2 Drivers................................................................................... 31
DB2 Connect ................................................................................. 32
DB2 Replication ............................................................................ 35
WebSphere Federation Server & WebSphere Information Server 37
Database Enterprise Developer’s Edition ..................................... 39
DB2 Developer Workbench .......................................................... 40
DB2 Extenders............................................................................... 41
DB2 Administration................................................................................. 45
Control Center................................................................................ 45
DB2 Replication Center................................................................. 47
Other Tools Available from the Control Center ............................ 48
DB2 Health Center......................................................................... 48
DB2 Configuration Assistant......................................................... 50
Advisors and Wizards.................................................................... 51
The DB2 Command Line Processor .............................................. 54
Visual Explain............................................................................... 55
DB2 Query Patroller ...................................................................... 55
Database Monitoring Tools ........................................................... 57
The DB2 Storage Management Tool............................................. 58
The DB2 Aftermarket Tools.......................................................... 59
Summary.................................................................................................. 60
x
Contents
Chapter 2 Getting Started .......................................................63
Product Installation...................................................................................64
Windows Installation......................................................................64
Linux and UNIX Installation .........................................................81
Multi-Copy Installations.................................................................83
Response File Installations.............................................................88
Using the DB2 Command Line Processor .....................................94
The DB2 Environment .............................................................................98
DB2 Profile Registry......................................................................98
Environment Variables.................................................................100
The DB2 Instance.........................................................................103
DB2 Administration Server (DAS)..............................................113
Summary ................................................................................................115
Chapter 3 Getting Connected ............................................... 117
DB2 Client Overview ............................................................................118
Installing a DB2 Client ................................................................120
Roadmap to Distributed Communications .............................................124
Automated Configuration Using DB2 Discovery ........................125
Configuring DB2 Discovery ........................................................126
Using the Automated Configuration Method...............................130
Automated Configuration Using Access Profiles ........................138
Manual Configuration Using the Configuration Assistant...........147
Manual Configuration Using the Command Line Processor .......151
Summary of Configuring Connections.........................................160
Binding Utilities ...........................................................................160
Binding Utilities Using the Configuration Assistant....................162
Summary ................................................................................................164
Chapter 4 Controlling Data Access ...................................... 167
Overview of Security..............................................................................168
Authentication ..............................................................................170
Authorization ...............................................................................184
Privileges......................................................................................194
Label-Based Access Control ........................................................213
Auditing..................................................................................................216
Summary ................................................................................................221