第1页 / 共300页
第2页 / 共300页
第3页 / 共300页
第4页 / 共300页
第5页 / 共300页
第6页 / 共300页
第7页 / 共300页
第8页 / 共300页
db2数据库入门教程（官方中文版DB2_Express_v9.7）.pdf
About this book
Notices and trademarks
Who should read this book?
How is this book structured?
A book for the community
Authors and Contributors
Acknowledgements
Foreword
PART I – OVERVIEW AND SETUP
Chapter 1 – What is DB2 Express-C?
1.1 Free to develop, deploy, and distribute…no limits!
1.2 Downloading DB2 Express-C
1.3 User assistance and technical support
1.4 DB2 servers
1.5 DB2 clients and drivers
1.6 Application development freedom
1.7 DB2 versions versus DB2 editions
1.8 Moving up to another DB2 edition
1.9 Maintenance and updates for DB2 Express-C
1.10 Related free software and DB2 components
1.10.1 IBM Data Studio
1.10.4 DB2 Text Search
1.10.5 WebSphere Application Server – Community Edition
1.11 Summary
Chapter 2 – Related features and products
2.1 Features included with DB2 Express subscription (FTL)
2.1.1 Fix packs
2.1.2 High Availability Disaster Recovery (HADR)
2.1.3 Data Replication
2.2 Features not available with DB2 Express-C
2.2.1 Database Partitioning
2.2.2 Connection Concentrator
2.2.3 Geodetic Extender
2.2.4 Label-based Access Control (LBAC)
2.2.5 Workload Manager (WLM)
2.2.6 Deep compression
2.2.7 SQL Compatibility
2.3 Fee-based products that are related to DB2
2.3.1 DB2 Connect
2.3.2 InfoSphere Federation Server
2.3.3 InfoSphere Replication Server
2.3.4 Optim Development Studio (ODS)
2.3.5 Optim Database Administrator (ODA)
2.4 DB2 Offerings on Amazon Elastic Compute Cloud
2.5 Summary
Chapter 3 – DB2 installation
3.1 Installation prerequisites
3.2 Operating system installation authority
3.3 Installation wizard
3.4 Validating your installation
3.5 Silent Install
3.6 Summary
3.7 Exercises
Chapter 4 – DB2 Environment
4.1 DB2 configuration
4.1.1 Environment variables
4.1.2 Database manager configuration file (dbm cfg)
4.1.3 Database configuration file (db cfg)
4.1.4 DB2 profile registry
4.2 The DB2 Administration Server (deprecated)
4.3 Summary
Chapter 5 – DB2 Tools
5.1 IBM Data Studio
5.2 Control Center (deprecated)
5.2.1 Launching the Control Center
5.3 Command Editor (deprecated)
5.3.1 Launching the Command Editor
5.3.2 Adding a database connection
5.4 SQL Assist Wizard (deprecated)
5.5 Show SQL Button (deprecated)
5.6 Task Center (deprecated)
5.6.1 The Tools Catalog database (deprecated)
5.7 Journal (deprecated)
5.7.1 Launching the Journal
5.8 Health Monitor (deprecated)
5.8.1 Health Center (deprecated)
5.9 Self-tuning memory manager
5.10 Scripting
5.10.1 SQL scripts
5.10.2 Operating system (shell) scripts
5.11 Windows Vista considerations
5.12 Summary
5.13 Exercises
PART II – LEARNING DB2: DATABASE ADMINISTRATION
Chapter 6 – DB2 Architecture
6.1 DB2 process model
6.2 DB2 memory model
6.3 DB2 storage model
6.3.1 Pages and Extents
6.3.2 Buffer pools
6.3.3 Table spaces
6.4 Summary
6.5 Exercises
Chapter 7 – DB2 Client Connectivity
7.1 DB2 Directories
7.1.1 System database directory
7.1.2 Local database directory
7.1.3 Node directory
7.1.4 DCS directory
7.2 Configuration Assistant (deprecated)
7.2.1 Setup required at the server
7.2.2 Setup required at the client
7.2.3 Creating Client and Server Profiles
7.3 Summary
7.4 Exercises
Chapter 8 – Working with Database Objects
8.1 Schemas
8.2 Public synonyms (or aliases)
8.3 Tables
8.3.1 Data Types
8.3.2 Identity Columns
8.3.3 Sequence objects
8.3.4 System catalog tables
8.3.5 Declared global temporary tables (DGTTs)
8.3.6 Create Global Temporary Tables (CGTTs)
8.4 Views
8.5 Indexes
8.5.1 Design Advisor
8.6 Referential integrity
8.7 Schema Evolution
8.8 Summary
8.9 Exercises
Chapter 9 – Data Movement Utilities
9.1 EXPORT utility
9.2 IMPORT utility
9.3 LOAD utility
9.4 The db2move utility
9.5 The db2look utility
9.6 Summary
9.7 Exercises
Chapter 10 – Database Security
10.1 Authentication
10.2 Authorization
10.2.1 Privileges
10.2.2 Authorities
10.2.3 Roles
10.3 Group privilege considerations
10.4 The PUBLIC group
10.5 The GRANT and REVOKE statements
10.6 Authorization and privilege checking
10.7 Extended Security on Windows
10.8 Summary
10.9 Exercises
Chapter 11 – Backup and Recovery
11.1 Database Logging
11.2 Types of logs
11.3 Types of logging
11.3.1 Circular logging
11.3.2 Archive logging
11.4 Database logging from the Control Center
11.5 Logging parameters
11.6 Database backup
11.7 Database recovery
11.7.1 Recovery types
11.7.2 Database restore
11.8 Other operations with BACKUP and RESTORE
11.9 Summary
11.10 Exercises
Chapter 12 – Maintenance Tasks
12.1 REORG, RUNSTATS, REBIND
12.1.1 The REORG command
12.1.2 The RUNSTATS command
12.1.3 BIND / REBIND
12.1.4 Maintenance tasks from the Control Center
12.2 Maintenance Choices
12.3 Summary
12.4 Exercises
Chapter 13 – Concurrency and Locking
13.1 Transactions
13.2 Concurrency
13.3 Problems without concurrency control
13.3.1 Lost update
13.3.2 Uncommitted read
13.3.3 Non-repeatable read
13.3.4 Phantom read
13.4 Isolation Levels
13.4.1 Uncommitted read
13.4.2 Cursor stability
13.4.3 Read stability
13.4.4 Repeatable read
13.4.5 Comparing isolation levels
13.4.6 Setting the isolation level
13.5 Lock escalation
13.6 Lock monitoring
13.7 Lock wait
13.8 Deadlock causes and detection
13.9 Concurrency and locking best practices
13.10 Summary
13.11 Exercises
PART III – LEARNING DB2: APPLICATION DEVELOPMENT
Chapter 14 – Introduction to DB2 Application Development
14.2.1 Stored Procedures
14.2.2 User-defined functions
14.2.3 Triggers
14.3.1 Embedded SQL
14.3.2 Static SQL vs. Dynamic SQL
14.3.3 CLI and ODBC
14.3.4 JDBC, SQLJ and pureQuery
14.3.5 OLE DB
14.3.6 ADO.NET
14.3.7 PHP
14.3.8 Ruby on Rails
14.3.9 Perl
14.3.10 Python
14.7.1 Working with Microsoft Access and Microsoft Excel
14.9 Sample programs
14.10 Summary
Chapter 15 – DB2 pureXML
15.1 Using XML with databases
15.2 XML databases
15.2.1 XML-enabled databases
15.2.2 Native XML databases
15.3 XML in DB2
15.3.1 pureXML technology advantages
15.3.2 XPath basics
15.3.3 XQuery basics
15.3.4 Inserting XML documents
15.3.5 Querying XML data
15.3.6 Joins with SQL/XML
15.3.7 Joins with XQuery
15.3.8 Update and delete operations
15.3.9 XML indexing
15.4 Working with XML Schemas
15.4.1 Registering your XML Schemas
15.4.2 XML Schema validation
15.4.3 Other XML support
15.6 Summary
15.7 Exercises
Appendix A – Troubleshooting
A.1 Finding more information about error codes
A.2 SQLCODE and SQLSTATE
A.3 DB2 Administration Notification Log
A.4 db2diag.log
A.5 CLI traces
A.6 DB2 Defects and Fixes
Appendix B – References and Resources
B.1 References
B.2 Web sites:
B.3 Books
B.4 Contact emails
GETTING STARTED WITH
DB2 Express-C
A book for the community by the community
RAUL CHONG, IAN HAKES, RAV AHUJA
FOREWORD BY DR. ARVIND KRISHNA
THIRD EDITION
4 Getting Started with DB2 Express-C
Third Edition (June 2009)
Third printing (October 2010)
This edition has been updated for IBM® DB2® Express-C Version 9.7.2 for Linux®,
UNIX® and Windows®.
© Copyright IBM Corporation, 2007, 2010. All rights reserved.
Contents
About this book ............................................................................................................... 11
Notices and trademarks ............................................................................................... 11
Who should read this book? ........................................................................................ 12
How is this book structured? ........................................................................................ 12
A book for the community ............................................................................................ 13
Authors and Contributors ............................................................................................. 14
Acknowledgements ...................................................................................................... 14
Foreword ...................................................................................................................... 15
PART I – OVERVIEW AND SETUP ................................................................................. 17
Chapter 1 – What is DB2 Express-C? ........................................................................... 19
1.1 Free to develop, deploy, and distribute…no limits! ................................................ 20
1.2 Downloading DB2 Express-C ................................................................................ 20
1.3 User assistance and technical support .................................................................. 21
1.4 DB2 servers ........................................................................................................... 21
1.5 DB2 clients and drivers .......................................................................................... 22
1.6 Application development freedom ......................................................................... 23
1.7 DB2 versions versus DB2 editions ........................................................................ 24
1.8 Moving up to another DB2 edition ......................................................................... 25
1.9 Maintenance and updates for DB2 Express-C ...................................................... 25
1.10 Related free software and DB2 components ....................................................... 26
1.10.1 IBM Data Studio ............................................................................................ 26
1.10.4 DB2 Text Search .......................................................................................... 27
1.10.5 WebSphere Application Server – Community Edition .................................. 27
1.11 Summary .............................................................................................................. 27
Chapter 2 – Related features and products ................................................................. 29
2.1 Features included with DB2 Express subscription (FTL) ....................................... 32
2.1.1 Fix packs ......................................................................................................... 32
2.1.2 High Availability Disaster Recovery (HADR) .................................................. 33
2.1.3 Data Replication ............................................................................................. 33
2.2 Features not available with DB2 Express-C .......................................................... 34
2.2.1 Database Partitioning ..................................................................................... 34
2.2.2 Connection Concentrator ................................................................................ 35
2.2.3 Geodetic Extender .......................................................................................... 35
2.2.4 Label-based Access Control (LBAC) .............................................................. 35
2.2.5 Workload Manager (WLM) .............................................................................. 36
2.2.6 Deep compression .......................................................................................... 37
2.2.7 SQL Compatibility ........................................................................................... 38
2.3 Fee-based products that are related to DB2 ......................................................... 39
2.3.1 DB2 Connect .................................................................................................. 39
2.3.2 InfoSphere Federation Server ........................................................................ 40
2.3.3 InfoSphere Replication Server ........................................................................ 41
2.3.4 Optim Development Studio (ODS) ................................................................. 41
2.3.5 Optim Database Administrator (ODA) ............................................................ 42
6 Getting Started with DB2 Express-C
2.4 DB2 Offerings on Amazon Elastic Compute Cloud ............................................... 42
2.5 Summary ................................................................................................................ 42
Chapter 3 – DB2 installation .......................................................................................... 43
3.1 Installation prerequisites ........................................................................................ 43
3.2 Operating system installation authority .................................................................. 43
3.3 Installation wizard .................................................................................................. 44
3.4 Validating your installation ..................................................................................... 51
3.5 Silent Install ............................................................................................................ 53
3.6 Summary ................................................................................................................ 54
3.7 Exercises ............................................................................................................... 54
Chapter 4 – DB2 Environment ....................................................................................... 59
4.1 DB2 configuration .................................................................................................. 68
4.1.1 Environment variables .................................................................................... 69
4.1.2 Database manager configuration file (dbm cfg) ............................................. 69
4.1.3 Database configuration file (db cfg) ................................................................ 72
4.1.4 DB2 profile registry ......................................................................................... 73
4.2 The DB2 Administration Server (deprecated) ........................................................ 74
4.3 Summary ................................................................................................................ 75
4.4 Exercises ............................................................................................................... 75
Chapter 5 – DB2 Tools .................................................................................................... 81
5.1 IBM Data Studio ..................................................................................................... 83
5.2 Control Center (deprecated) .................................................................................. 84
5.2.1 Launching the Control Center ......................................................................... 87
5.3 Command Editor (deprecated) .............................................................................. 88
5.3.1 Launching the Command Editor ..................................................................... 88
5.3.2 Adding a database connection ....................................................................... 89
5.4 SQL Assist Wizard (deprecated) ........................................................................... 90
5.5 Show SQL Button (deprecated) ............................................................................. 91
5.6 Task Center (deprecated) ...................................................................................... 92
5.6.1 The Tools Catalog database (deprecated) ..................................................... 93
5.7 Journal (deprecated) .............................................................................................. 94
5.7.1 Launching the Journal .................................................................................... 96
5.8 Health Monitor (deprecated) .................................................................................. 96
5.8.1 Health Center (deprecated) ............................................................................ 97
5.9 Self-tuning memory manager ................................................................................ 99
5.10 Scripting ............................................................................................................... 99
5.10.1 SQL scripts ................................................................................................... 99
5.10.2 Operating system (shell) scripts ................................................................. 101
5.11 Windows Vista considerations ........................................................................... 102
5.12 Summary ............................................................................................................ 102
5.13 Exercises ........................................................................................................... 102
PART II – LEARNING DB2: DATABASE ADMINISTRATION ..................................... 107
Chapter 6 – DB2 Architecture ...................................................................................... 109
6.1 DB2 process model ............................................................................................. 109
6.2 DB2 memory model ............................................................................................. 111
Contents 7
6.3 DB2 storage model .............................................................................................. 112
6.3.1 Pages and Extents ........................................................................................ 113
6.3.2 Buffer pools ................................................................................................... 113
6.3.3 Table spaces ................................................................................................ 115
6.4 Summary .............................................................................................................. 120
6.5 Exercises ............................................................................................................. 120
Chapter 7 – DB2 Client Connectivity ........................................................................... 125
7.1 DB2 Directories .................................................................................................... 125
7.1.1 System database directory ........................................................................... 125
7.1.2 Local database directory ............................................................................... 126
7.1.3 Node directory .............................................................................................. 126
7.1.4 DCS directory ............................................................................................... 126
7.2 Configuration Assistant (deprecated) .................................................................. 126
7.2.1 Setup required at the server ......................................................................... 127
7.2.2 Setup required at the client ........................................................................... 130
7.2.3 Creating Client and Server Profiles .............................................................. 133
7.3 Summary .............................................................................................................. 137
7.4 Exercises ............................................................................................................. 137
Chapter 8 – Working with Database Objects ............................................................. 141
8.1 Schemas .............................................................................................................. 141
8.2 Public synonyms (or aliases) ............................................................................... 142
8.3 Tables .................................................................................................................. 143
8.3.1 Data Types.................................................................................................... 143
8.3.2 Identity Columns ........................................................................................... 148
8.3.3 Sequence objects ......................................................................................... 148
8.3.4 System catalog tables ................................................................................... 149
8.3.5 Declared global temporary tables (DGTTs) .................................................. 150
8.3.6 Create Global Temporary Tables (CGTTs) .................................................. 152
8.4 Views ................................................................................................................... 152
8.5 Indexes ................................................................................................................ 153
8.5.1 Design Advisor .............................................................................................. 153
8.6 Referential integrity .............................................................................................. 155
8.7 Schema Evolution ................................................................................................ 156
8.8 Summary .............................................................................................................. 157
8.9 Exercises ............................................................................................................. 157
Chapter 9 – Data Movement Utilities ........................................................................... 161
9.1 EXPORT utility ..................................................................................................... 162
9.2 IMPORT utility ...................................................................................................... 163
9.3 LOAD utility .......................................................................................................... 164
9.4 The db2move utility .............................................................................................. 166
9.5 The db2look utility ................................................................................................ 166
9.6 Summary .............................................................................................................. 169
9.7 Exercises ............................................................................................................. 169
Chapter 10 – Database Security .................................................................................. 173
10.1 Authentication .................................................................................................... 174
8 Getting Started with DB2 Express-C
10.2 Authorization ...................................................................................................... 175
10.2.1 Privileges .................................................................................................... 175
10.2.2 Authorities ................................................................................................... 176
10.2.3 Roles ........................................................................................................... 181
10.3 Group privilege considerations .......................................................................... 182
10.4 The PUBLIC group ............................................................................................ 182
10.5 The GRANT and REVOKE statements ............................................................. 182
10.6 Authorization and privilege checking ................................................................. 183
10.7 Extended Security on Windows ......................................................................... 184
10.8 Summary ............................................................................................................ 185
10.9 Exercises ........................................................................................................... 185
Chapter 11 – Backup and Recovery ............................................................................ 191
11.1 Database Logging .............................................................................................. 191
11.2 Types of logs ...................................................................................................... 192
11.3 Types of logging ................................................................................................ 193
11.3.1 Circular logging ........................................................................................... 193
11.3.2 Archive logging ........................................................................................... 194
11.4 Database logging from the Control Center ........................................................ 195
11.5 Logging parameters ........................................................................................... 196
11.6 Database backup ............................................................................................... 197
11.7 Database recovery ............................................................................................. 199
11.7.1 Recovery types ........................................................................................... 199
11.7.2 Database restore ........................................................................................ 200
11.8 Other operations with BACKUP and RESTORE ............................................... 200
11.9 Summary ............................................................................................................ 200
11.10 Exercises ......................................................................................................... 201
Chapter 12 – Maintenance Tasks ................................................................................ 205
12.1 REORG, RUNSTATS, REBIND ......................................................................... 205
12.1.1 The REORG command ............................................................................... 206
12.1.2 The RUNSTATS command ........................................................................ 206
12.1.3 BIND / REBIND ........................................................................................... 207
12.1.4 Maintenance tasks from the Control Center ............................................... 208
12.2 Maintenance Choices ........................................................................................ 209
12.3 Summary ............................................................................................................ 211
12.4 Exercises ........................................................................................................... 211
Chapter 13 – Concurrency and Locking ..................................................................... 215
13.1 Transactions ...................................................................................................... 215
13.2 Concurrency ....................................................................................................... 216
13.3 Problems without concurrency control ............................................................... 217
13.3.1 Lost update ................................................................................................. 217
13.3.2 Uncommitted read ...................................................................................... 218
13.3.3 Non-repeatable read ................................................................................... 219
13.3.4 Phantom read ............................................................................................. 219
13.4 Isolation Levels .................................................................................................. 220
13.4.1 Uncommitted read ...................................................................................... 220
相关推荐
2023年江西萍乡中考道德与法治真题及答案.doc
2012年重庆南川中考生物真题及答案.doc
2013年江西师范大学地理学综合及文艺理论基础考研真题.doc
2020年四川甘孜小升初语文真题及答案I卷.doc
2020年注册岩土工程师专业基础考试真题及答案.doc
2023-2024学年福建省厦门市九年级上学期数学月考试题及答案.doc
2021-2022学年辽宁省沈阳市大东区九年级上学期语文期末试题及答案.doc
2022-2023学年北京东城区初三第一学期物理期末试卷及答案.doc
2018上半年江西教师资格初中地理学科知识与教学能力真题及答案.doc
2012年河北国家公务员申论考试真题及答案-省级.doc
2020-2021学年江苏省扬州市江都区邵樊片九年级上学期数学第一次质量检测试题及答案.doc
2022下半年黑龙江教师资格证中学综合素质真题及答案.doc
