ChannelDB2

FREE Book- Getting Started with DB2 Express-C

by Raul Chong, Ian Hakes, Rav Ahuja [2nd Edition, Updated for DB2 V9.5]

Getting started with DB2 couldn't be easier. Read this book to:
* Find out what DB2 Express-C is all about
* Understand DB2 architecture, tools, security
* Learn how to administer DB2 databases
* Write SQL, XQuery, stored procedures
* Develop database applications for DB2
* Practice using hands-on exercises
* Prepare for the DB2 on Campus exam

This book is ideal for developers, consultants, ISVs, DBAs, students, or anyone who wants to get started with DB2. While this books is focused on DB2 Express-C, the no-charge edition of DB2, the concepts and content are also very applicable to other DB2 editions on Linux, UNIX, and Windows. The 2nd edition of the book has been updated for DB2 Version 9.5.

To download the book (pdf) and quicklabs (zip), right-click on the desired link below and Save...
English (US) [5.5MB]
Chinese (Simplified) [5MB]
German [8MB]
Italian [4.6MB]
Polish [6.6MB]
Portuguese (Brazilian) [4.9MB]
Spanish [4.8MB]
Vietnamese [10MB]
Quicklabs (Exercises) [841KB]




TABLE OF CONTENTS

About this book 8
Notices and Trademarks 8
Who should read this book? 9
How is this book structured? 9
A book for the community 9
Authors and Contributors 10
Acknowledgements 10
Foreword 11

PART I – OVERVIEW AND SETUP 12
Chapter 1 – What is DB2 Express-C? 13
1.1 Free to develop, deploy, and distribute…no limits! 13
1.2 User assistance and technical support 14
1.3 DB2 servers 14
1.4 DB2 clients and drivers 15
1.5 Application development freedom 16
1.6 DB2 versions versus DB2 editions 17
1.7 Moving up to another DB2 edition 17
1.8 Maintenance of DB2 Express-C 18
1.9 Related free software 18
1.9.1 IBM Data Studio 19
1.9.2 DB2 Net Search Extender 19
1.9.3 Starter Toolkit for DB2 on Rails 19
1.9.4 Web 2.0 Starter Toolkit for DB2 19
1.9.5 WebSphere Application Server – Community Edition 19

Chapter 2 – Related features and products 21
2.1 Features included with DB2 Express-C subscription 24
2.1.1 Fix packs 25
2.1.2 High Availability Disaster Recovery (HADR) 25
2.1.3 Data Replication 26
2.2 Features not available with DB2 Express-C 26
2.2.1 Database Partitioning 26
2.2.2 Connection Concentrator 27
2.2.3 Geodetic Extender 27
2.2.4 Workload Management (WLM) 27
2.3 Fee-based products that are related to DB2 27
2.3.1 DB2 Connect 27
2.3.2 WebSphere Federation Server 28
2.3.3 WebSphere Replication Server 29

Chapter 3 – DB2 installation 31
3.1 Installation prerequisites 31
3.2 Operating system installation authority 31
3.3 Installation wizard 32
3.4 Silent Install 36
QuickLab #1: Install DB2 Express-C & create SAMPLE database 38
Chapter 4 – DB2 Environment 41
Quicklab #2 – Create a New Database 50

4.1 DB2 configuration 51
4.1.1 Environment variables 52
4.1.2 Database manager configuration file (dbm cfg) 52
4.1.3 Database configuration file (db cfg) 54
4.1.4 DB2 profile registry 55
4.2 The DB2 Administration Server 56
Quicklab #3 – Working with instances, databases and configuration 57

Chapter 5 – DB2 Tools 59
5.1 Control Center 61
5.2 Command Editor 64
5.3 SQL Assist Wizard 66
5.4 Show SQL Button 67
Quicklab #4: Populating the EXPRESS Database using scripts 68
5.5 Scripting 70
5.5.1 SQL scripts 70
5.5.2 Operating system (shell) scripts 71
Quicklab #5: Create an installation script for EXPRESS Database 73
5.6 Task Center 76
5.6.1 The Tools Catalog database 76
5.7 Journal 77
5.8 Health Monitor 79
5.8.1 Health Center 80

PART II – LEARNING DB2: DATABASE ADMINISTRATION 83
Chapter 6 – DB2 Architecture 85
6.1 DB2 process model 85
6.2 DB2 memory model 87
6.3 DB2 storage model 88
6.3.1 Pages and Extents 88
6.3.2 Buffer pools 88
6.3.3 Table spaces 90

Chapter 7 – DB2 Client Connectivity 95
7.1 DB2 Directories 95
7.2 Configuration Assistant 96
7.2.1 Setup required at the server 96
7.2.2 Setup required at the client 99
7.2.3 Creating Client and Server Profiles 103
Quicklab #6: Using the Configuration Assistant 106

Chapter 8 – Working with Database Objects 109
8.1 Schema 109
8.2 Tables 110
8.2.1 Data Types 110
8.2.2 Identity Columns 113
8.2.3 SEQUENCE objects 113
8.2.4 System catalog tables 114
8.2.5 Declared temporary tables 114
Quicklab #7: Creating a new table 116
8.3 Views 119
8.4 Indexes 119
8.4.1 Design Advisor 119
8.5 Referential integrity 121

Chapter 9 – Data Movement Utilities 123
9.1 EXPORT utility 124
9.2 IMPORT utility 125
9.3 LOAD 126
9.4 The db2move utility 128
9.5 The db2look utility 128
Quicklab #8 – Extracting DDL for the EXPRESS database 130

Chapter 10 – Database Security 133
10.1 Authentication 134
10.2 Authorization 135
10.3 DBADM authority 138
10.4 The PUBLIC group 138
10.5 The GRANT and REVOKE statements 139
10.6 Authorization and privilege checking 139
10.7 Group privilege considerations 141
Quicklab #9 – Granting and revoking user permissions 142

Chapter 11 – Backup and Recovery 145
11.1 Database Logging 145
11.2 Types of logs 146
11.3 Types of logging 147
11.3.1 Circular logging 147
11.3.2 Archival logging or log retain 147
11.4 Database logging from the Control Center 148
11.5 Logging parameters 149
11.6 Database backup 150
Quicklab #10 – Scheduling a backup 152
11.7 Database recovery 155
11.7.1 Recovery types 155
11.7.2 Database restore 155
11.8 Other operations with BACKUP and RESTORE 156

Chapter 12 – Maintenance Tasks 157
12.1 REORG, RUNSTATS, REBIND 157
12.1.1 The REORG command 158
12.1.2 The RUNSTATS command 158
12.1.3 BIND / REBIND 159
12.1.4 Maintenance tasks from the Control Center 160
12.2 Maintenance Choices 161
Quicklab #11 – Configuring automated maintenance 164

Chapter 13 – Concurrency and Locking 167
13.1 Transactions 167
13.2 Concurrency 168
13.3 Problems without concurrency control 169
13.3.1 Lost update 169
13.3.2 Uncommitted read 170
13.3.3 Non-repeatable read 170
13.3.4 Phantom read 171
13.4 Isolation Levels 172
13.4.1 Uncommitted read 172
13.4.2 Cursor stability 172
13.4.3 Read stability 173
13.4.4 Repeatable read 173
13.4.5 Comparing isolation levels 173
13.4.6 Setting the isolation level 174
13.5 Lock escalation 175
13.6 Lock monitoring 176 13.7 Lock wait 177
13.8 Deadlock causes and detection 178 13.9 Concurrency and locking best practices 179

PART III – LEARNING DB2: APPLICATION DEVELOPMENT 181
Chapter 14 – SQL PL Stored Procedures 183
14.1 The IBM Data Studio 184
14.1.2 Create a stored procedure in Data Studio 185
14.2 SQL PL stored procedures basics 188
14.2.1 Stored procedure structure 188
14.2.2 Optional stored procedure attributes 189
14.2.3 Parameters 189
14.2.4 Comments in an SQL PL stored procedure 190
14.2.5 Compound statements 190
14.2.6 Variable declaration 191
14.2.7 Assignment statements 191
14.3 Cursors 191
14.4 Flow control 192
14.5 Calling stored procedures 192
14.6 Errors and condition handlers 194
14.7 Dynamic SQL 196

Chapter 15 – Inline SQL PL, Triggers, and UDFs 197
15.1 Inline SQL PL 197
15.2 Triggers 198
15.2.1 Types of triggers 199
Quicklab #12 – Creating a trigger in the Control Center 202 15.3 User-defined functions (UDFs) 205
15.3.1 Scalar functions 205
15.3.2 Table functions 206
Quicklab #13 – Creating a UDF using the IBM Data Studio 207

Chapter 16 – DB2 pureXML 209
16.1 Using XML with databases 210
16.2 XML databases 210
16.2.1 XML-enabled databases 210
16.2.2 Native XML databases 211
16.3 XML in DB2 211
16.3.1 pureXML technology advantages 212
16.3.2 XPath basics 214
16.3.3 XQuery defined 217
16.3.4 Inserting XML documents 219
16.3.5 Querying XML data 222
16.3.6 Joins with SQL/XML 227
16.3.7 Joins with XQuery 228
16.3.8 Update and delete operations 228
16.3.9 XML indexing 230
QuickLab #14 - SQL/XML and XQuery 232

Chapter 17 –Developing with Java, PHP, and Ruby 233
17.1 Application development in Java 233
17.1.1 JDBC Type 2 driver 233
17.1.2 JDBC Type 4 driver 234
17.2 Application development in PHP 235
17.2.1 DB2 connection options for PHP 235
17.2.2 Zend Core for IBM 237
17.3 Application development in Ruby on Rails 239
17.3.1 Startup Toolkit for DB2 on Rails 239

Appendix A – Troubleshooting 241
A.1 Finding more information about error codes 242
A.2 SQLCODE and SQLSTATE 242
A.3 DB2 Administration Notification Log 243
A.4 db2diag.log 243
A.5 CLI traces 244
A.6 DB2 Defects and Fixes 244
Resources 245
Web sites: 245
Books 246


Try BLU Acceleration on Cloud

© 2014   Created by channeldb2.

Badges  |  Report an Issue  |  Terms of Service