This book is a great introduction to DB2, whether you have used DB2 before or you are new to DB2. This book will save you time and effort because the topics are presented in a clear and concisemanner, and we use figures, examples, case studies, and review questions to reinforce the materialas it Understanding DB2@Second EditionIBM PressINFORMATION MANAGEMENTService-Oriented Architecture(SOA)CompassAn introduction to MStMBieberstein Bose Fiammante Jones and Shah ISBN 0131870025Meltz, Long, Harrington, Hain, and Nicholls ISBN 0131856715WEBSPHEREDB2 ExpressYip, Cheung, Gartner, Liu, and O'Connell- ISBN 0131463977Enterprise Java TM Programming with IBM@ WebSphere@Second editionDB2 for z/os@ Version 8 DBA Certification GuideBrown, Craig, Hester, Pitt, Stinehour, Weitzel, Amsden, Jakab, and BergLawson ISBN 0131491202SBN032118579XDB2 SOL PL, Second EditionEnterprise Messaging Using JMS and IBM WebSphereJanmohamed, Liu, Bradstock, Chong, Gao, McArthur, and yipYusuf isbn 0131468634SBN0131477005IBM@ WebSphereHigh Availability Guide for DB2GBarcia. Hines. alcott and botzum ISbN 0131468626Eaton and Cialinim 0131448307BM@ WebSphere@ Application Server for DistributedThe Official Introduction to DB2@ for z/0S@, Second EditionPlatforms and z/0S@Sloan■SBN0131477501Black, Everett, Draeger, Miller, lyer, McGuinnes, Patel, HerescuUnderstanding DB2@ 9 SecurityGissel, Betancourt, Casile, Tang, and Beaubien ISBN 0131855875Bond, See, Wong, and Chan- ISBN 0131345907IBM@ Web Sphere@ System AdministrationUnderstanding DB2@Williamson. Chan. Cundiff. Lauzon and mitchell ISBN 0131446045Chong, Liu, Qi, and Snow- ISBN 0131859161LOTUSRATIONAL AND SOFTWARE DEVELOPMENTIBM@ WebSphere@ and LotusIBM Rational@ ClearCase@. Ant, and cruise controlLamb, Laskey, and Indurkhya- ISBN 0131443305Lee ISBn 0321356993Lotus@ Notes@ Developer's ToolboxImplementing IBM@ Rational@ Clear QuesteElliott ISBN 0132214482Buckley. Pulsipher, and Scott ISBN 0321334868OPEN SOURCEImplementing the IBM Rational Unified Process@e andSolutions: A Guide to Improving Your Software DevelopmentApache Derby-Off to the RacesCapability and MaturityZikopoulos, Baklarz, and Scott ISBN 0131855255Barnes SBN 0321369459Building Applications with the Linux@ Standard BaseOutside-in Software DevelopmentLinux standard Base Team ISBN 0131456954Kessler and Sweitzer 0131575511Performance Tuning for linux ServersProject Management with the IBM Rational Unified Process@Johnson, Huizenga, and Pulavarty n ISBN 013144753XGbbs■SBN0321336399BUSINESS STRATEGY managementSoftware Configuration Management Strategies and IBMRational@ Clear Case@ Second editionCan Two Rights Make a Wrongellagic and Milligan ISBN 0321200195Moulton Reger n ISBN 0131732943Visual Modeling with IBM Rational@ Software ArchitectDeveloping Quality Technical Information, Second Editionand UMlTMHargis, Carey, Hernandez, Hughes, Longo, Rouiller, and WildeQuatrani and Palistrant ISBN 0321238087SBN0131477498Do It Wrong QuicklyCOMPUTINGMoran■0132255960Autonomic ComputingIrresistible! Markets Models and Meta-Value in ConsumerMurch ISBN 013144025XElectronicsBusiness Intelligence for the EnterpriseBailey and Wenzek ISBN 0131987585Biere ISBN 0131413031Mining the Talk: Unlocking the Business Value inGrid ComputingUnstructured InformatiJoseph and Fellenstein ISBN 0131456601Spangler and Kreulen ISBN 0132339536Inescapable Data: Harnessing the power of convergenceReaching the goalStakutis and Webster ISBN 0131852159Ricketts- 0132333120On Demand computingSearch Engine Marketing, IncFellenstein ISBN 0131440241Moran and hunt isbn 0131852922RFID SourcebookThe New language of Business: SOA and web 2.0Lahiri ISBN 0131851373Carter SbN 013195654XVisitwww.ibmpressbooks.comforacompletelistofIbmPressbooksP R E SSUnderstanding DB20Second EditionLearning Visually with ExamplesRaul F chongXiaomei WangMichael DangDwaine R snowBM PressPearson plcUpper Saddle River, NJ. Boston Indianapolis San Franciscolew York● Toronto Montreal o London. Munich● Paris· MadridCapetown Sydney. Tokyo Singapore Mexico Cityibmpressbooks.comThe authors and publisher have taken care in the preparation of this book, but make no expressed or impliedwarranty of any kind and assume no responsibility for errors or omissions. No liability is assumed forincidental or consequential damages in connection with or arising out of the use of the information orprograms contained hereino Copyright 2008 by International Business Machines Corporation. All rights reservedNote to U.S. Government Users: Documentation related to restricted right Use, duplication or disclosure issubject to restrictions set forth in GSa ADP Schedule Contract with IBM CorporationIBM Press program managers Tara Woodman Ellice uffeCover design IBM CorporationAssociate Publisher: Greg WiegandMarketing Manager: Kourtnaye sturgeonPublicist: heather foxAcquisitions Editor: Bernard GoodwinManaging editor: John FullerProject Editor: Lara WysonCopy Editor: Plan-it PublishingIndexer: Barbara paveyCompositor: codemantraProofreader: Sossity smithManufacturing Buyer: Anna PopickPublished by Pearson plcPublishing as ibm pressIBM 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 particular to your business, traininggoals, marketing focus, and branding interests. For more information, please contactU.S. Corporate and government Sales1-800-382-3419corpsales@pearsontechgroup.comFor sales outside the u.s., please contactInternational salesinternational@ pearsoned comThe following terms are trademarks or registered trademarks of International Business Machines Corporation inthe United States, other countries, or both: IBM, the IBM logo, IBM Press, 1-2-3, AlX, Alphablox, AS/400Clear Case. DB2. DB2 Connect DB2 Universal Database. DS6000 DS8000. 15/OS Informix iSeries LotusMVS, OmniFind, OS/390, OS/400, pureXML, Rational, Rational Unified Process, System i, System p, System x,Web Sphere, z/OS, ZSeries, z/VM, and Z/VSE. Java and all Java-based trademarks are trademarks of SunMicrosystems, Inc in the United States, other countries, or both. Microsoft, Windows, Windows NT, and theWindows logo are trademarks of Microsoft Corporation in the United States, other countries, or both. UNIX is aregistered trademark of The Open group in the United States and other countries. Linux is a registered trademarkof Linus Torvalds in the United States, other countries, or both. Other company, product, or service names maybe trademarks or service marks of othersLibrary of Congress Cataloging-in-Publication dataUnderstanding DB2: learning visually with examples /Raul F Chong.let al. ].--2nd edp cmIncludes bibliographical references and indexISBN-13: 978-0-13-158018-3(hardcover: alk. paper)1. Relational databases. 2. IBM Database 2.l. chong. raul fQA76.9D3U552007005.7565-dc222007044541All rights reserved. This publication is protected by copyright, and permission must be obtained from thepublisher prior to any prohibited reproduction, storage in a retrieval system, or transmission in any form or byany means, electronic, mechanical, photocopying, recording, or likewise. For information regardingpermissions, write toPearson education incRights and contracts department501 Boylston Street, Suite 900Boston Ma02116Fax(617)671-3447ISBN-13:978-0-13-158018-3ISBN-10:0-13-158018-3Text printed in the United States on recycled paper at RR Donnelley in Crawfordsville, IndianaFirst printing. December 2007Raul would like to thank his wife Jin for her understanding support, andlove. Her insightfulness, patience, and beautiful smile kept him going tocomplete this second edition of this book. raul would also like to thank hisparents, Elias and Olga Chong, for their constant support and love, andhis siblings, Alberto, David, Patricia, and Nancy for their supportXIlionel woulld like to thank her family-Peiqin, Nengchao, Kangning,Kaiwen, Weihong, Weihua, and Chong for their endless love and supportMost of all, Xiaomei wants to thank Chau for his care, love, and under-standing when she spent many late nights and weekends writing this bookMichael would like to thank his wonderful wife Sylvia for herconstantsupport, motivation, understanding, and most importantly, love, in hisquest to complete the second edition of this bookDwaine would like to thank his wife linda and daughter Alyssa for theirconstant love, support, and understanding. You are always there tobrighten my day, not only during the time spent writing this book, but nomatter where I am or what /am doing Dwaine would also like to thankhis parents, Gladys and robert, for always being thereAll the authors of this book would like to thank clara Liu, coauthor of thefirst edition, for helping with this second edition Clara was expecting asecond child at the time this book was being written, and decided not toparticipate for this reasonContentsForewordXXIIIPrefaceXXVAcknowledgmentsAbout the authorsXXXIIIChapterIntroduction to db2I Brief hiof db22 The role of db2 in the Information on-Demand World2. On-Demand Business1.2.2 Information On-Demand1. 2.3 Service-Oriented Architecture1.2.4 Web services1.2.5XML2.6 DB2 and the IBM Strategy3 DB2 Editions78912353. DB2 EVeryplace Edition13.2 DB2 Personal edition1.3.3 DB2 Express-C1.3.4 DB2 Express Edition1.3.5 DB2 Workgroup Server Edition81.3.6 DB2 Enterprise Server Edition84 DB2 Clients201.5 Try-and-Buy Versions226 Host Connectivity1. 7 Federation Stt1. 8 Repl49 IBM Web Sphere Federation Server and Web Sphere replication Server1. 0 Special Package Offerings25I.I DB2 Syntax Diagram Conventions26L I2 Case Study28L I 3 Summary1 4 ReQContentsChapter 2 DB2 at a glance: The Big Picture332.1 SQL Statements, XQuery Statements, and DB2 Commands2.1.I SQL Statements352.1.2 XQuery Statements2.1.3 DB2 System Commands2. 1.4 DB2 Command Line processor( cLp) commands372.2 DB2 Tools Overview382.2 Command-Line Tools382.2.2 General Administration tools2.2.3 Information Tools2.2.4 Monitoring Tools2.2.5 Setup Tools402.2.6○ ther tools2.3 The dB2 Environment2.3.1 An Instance422.3.2 The Database Administration server442.3.3 Configuration Files and the DB2 Profile Registries(2)442.3.4 Connectivity and DB2 Directories(2.3.5 Databases492.3.6 Table Spaces ( 5)502.3.7 Tables, Indexes, and Large Objects()2.3.9 Buffer pc52.3. 10 The Internal Implementation of the DB2 Environment52.4 Federation552.5 Case Study: The DB2 Environment562.6 Database Partitioning Feature5826. Database partitions82.6.2 The Node Configuration File2.6.3 An Instance in the dpF Environment642.6.4 Partitioning a Database2.6.5 Configuration Files in a DPF Environment672.6.6 Logs in a DPF Er2.6.7 The Catalog partition682.6.8 Partition Groups6826.9 Buffer pools in a dpf environment692.6.10 Table Spaces in a Partitioned database Environment702.6 The Coordinator partition702.6.2 Issuing Commands and SQL Statements in a DPF Environment702.6.3 The dB2NodE Environment variable722.6.14 Distribution Maps and Distribution Keys732.7 Case Study: DB2 with DPF Environment2. 8 BM Balanced Warehouse792.9 Summary2. 0 Review Questions8