Uploaded byiliqzrccg
11 views

Mysql To Db2 Conversion Guide Ibm Redbooks

Mysql To Db2 Conversion Guide Ibm Redbooks Mysql To Db2 Conversion Guide Ibm Redbooks Mysql To Db2 Conversion Guide Ibm Redbooks

Download to read offline
Mysql To Db2 Conversion Guide Ibm Redbooks download https://ebookbell.com/product/mysql-to-db2-conversion-guide-ibm- redbooks-1764638 Explore and download more ebooks at ebookbell.com
Here are some recommended products that we believe you will be interested in. You can click the link to download. An Instruction To Html Css Php And Mysql To Create Datadriven Web Sites Hutchinson https://ebookbell.com/product/an-instruction-to-html-css-php-and- mysql-to-create-datadriven-web-sites-hutchinson-232133692 Mysql Quick Guide Comprehensive Guide To Mysql Database Integration And Beyond Jackson https://ebookbell.com/product/mysql-quick-guide-comprehensive-guide- to-mysql-database-integration-and-beyond-jackson-55939374 Php Mysql Novice To Ninja Kevin Yank https://ebookbell.com/product/php-mysql-novice-to-ninja-kevin- yank-46096152 Php Mysql Novice To Ninja 7th Edition Tom Butler https://ebookbell.com/product/php-mysql-novice-to-ninja-7th-edition- tom-butler-42448612
Php Mysql Novice To Ninja 7th Edition Tom Butler https://ebookbell.com/product/php-mysql-novice-to-ninja-7th-edition- tom-butler-42542622 Php Mysql Novice To Ninja Get Up To Speed With Php The Easy Way 6th Edition Tom Butler https://ebookbell.com/product/php-mysql-novice-to-ninja-get-up-to- speed-with-php-the-easy-way-6th-edition-tom-butler-10380008 Php Mysql Novice To Ninja Kevin Yank https://ebookbell.com/product/php-mysql-novice-to-ninja-kevin- yank-43035208 Php Mysql Novice To Ninja 7th Edition 7th Edition Tom Butler https://ebookbell.com/product/php-mysql-novice-to-ninja-7th- edition-7th-edition-tom-butler-42451910 Php Mysql Novice To Ninja Kevin Yank https://ebookbell.com/product/php-mysql-novice-to-ninja-kevin- yank-42926306
ibm.com/redbooks MySQL to DB2 Conversion Guide Whei-Jen Chen Angela Carlson Guides you through a MySQL database and application conversion to DB2 Enriches applications through advanced DB2 features Converts an application with detailed examples Front cover
MySQL to DB2 Conversion Guide December 2009 International Technical Support Organization SG24-7093-01
© Copyright International Business Machines Corporation 2009. All rights reserved. Note to U.S. Government Users Restricted Rights -- Use, duplication or disclosure restricted by GSA ADP Schedule Contract with IBM Corp. Second Edition (December 2009) This edition applies to DB2 9.7 for Linux, UNIX, and Windows and MySQL 5.1. Note: Before using this information and the product it supports, read the information in “Notices” on page ix.
© Copyright IBM Corp. 2009. All rights reserved. iii Contents Notices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .ix Trademarks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . x Preface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xiii The team who wrote this book . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xiii Become a published author . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xiv Comments welcome. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xv Summary of changes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvii December 2009, Second Edition . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvii Executive summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xix Chapter 1. DB2 for Linux, UNIX, and Windows. . . . . . . . . . . . . . . . . . . . . . . 1 1.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2 1.2 Product overview. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 1.2.1 DB2 Data Server Editions for the production environment . . . . . . . . . 3 1.2.2 Products for accessing System z and System i host data . . . . . . . . . 7 1.2.3 DB2 for pervasive platforms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8 1.2.4 Additional DB2 data server features . . . . . . . . . . . . . . . . . . . . . . . . . . 8 1.3 DB2 for Linux, UNIX, and Windows architecture. . . . . . . . . . . . . . . . . . . . . 9 1.3.1 DB2 9.7 threaded architecture and process model. . . . . . . . . . . . . . . 9 1.3.2 DB2 database objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10 1.3.3 DB2 catalog. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18 1.4 DB2 utilities . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18 1.5 DB2 database access . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21 1.5.1 DB2 clients and drivers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21 1.5.2 Application access . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25 1.5.3 DB2 application programming interfaces . . . . . . . . . . . . . . . . . . . . . 26 Chapter 2. MySQL database. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35 2.1 MySQL licensing overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36 2.2 MySQL architecture overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36 2.2.1 Database client and non-client utilities . . . . . . . . . . . . . . . . . . . . . . . 37 2.2.2 Database server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38 2.3 MySQL design and SQL compliance . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41 2.3.1 MySQL directory structure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41 2.3.2 MySQL storage engines . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45 2.3.3 MySQL standard SQL compliance . . . . . . . . . . . . . . . . . . . . . . . . . . 48
iv MySQL to DB2 Conversion Guide 2.4 MySQL utilities . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49 2.4.1 Overview of the MySQL server-side programs and utilities . . . . . . . 50 2.4.2 Overview of the MySQL client-side programs and utilities . . . . . . . . 50 2.5 MySQL application programming interfaces . . . . . . . . . . . . . . . . . . . . . . . 51 Chapter 3. Planning the conversion from MySQL to DB2 . . . . . . . . . . . . . 55 3.1 Conversion project planning overview. . . . . . . . . . . . . . . . . . . . . . . . . . . . 56 3.1.1 Benefits of converting to DB2 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57 3.1.2 IBM conversion support . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60 3.1.3 Education . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61 3.2 Application assessment. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62 3.3 System planning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63 3.3.1 Software . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64 3.3.2 Hardware. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64 3.3.3 Conversion tools . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65 3.4 The conversion process . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66 3.4.1 Preparing for the installation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67 3.4.2 Porting the database structure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67 3.4.3 Data porting. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68 3.4.4 Application porting. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70 3.4.5 Basic administration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72 3.4.6 Testing and tuning. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72 Chapter 4. Conversion scenario . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75 4.1 Application structure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76 4.1.1 Application flow . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76 4.2 Database structure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84 4.3 System environment . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85 Chapter 5. Installation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87 5.1 DB2 Express-C 9.7 on Linux . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 88 5.1.1 System requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 88 5.1.2 Installation procedure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 92 5.1.3 Instance creation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102 5.1.4 Client setup on Linux. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103 5.2 Other software products . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 105 5.2.1 Apache2 installation with DB2 support . . . . . . . . . . . . . . . . . . . . . . 105 5.2.2 PHP installation with DB2 support . . . . . . . . . . . . . . . . . . . . . . . . . 107 5.3 IBM Data Movement Tool installation and usage . . . . . . . . . . . . . . . . . . 112 5.3.1 IBM Data Movement Tool prerequisites . . . . . . . . . . . . . . . . . . . . . 112 5.3.2 IBM Data Movement Tool installation . . . . . . . . . . . . . . . . . . . . . . . 113 Chapter 6. Database conversion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 115 6.1 Data type mapping . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 116
Contents v 6.2 Data definition language differences . . . . . . . . . . . . . . . . . . . . . . . . . . . . 122 6.2.1 Database manipulation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 123 6.2.2 Table manipulation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 128 6.2.3 Index manipulation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 136 6.2.4 Trigger manipulation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 137 6.2.5 Procedures and function manipulation . . . . . . . . . . . . . . . . . . . . . . 138 6.3 Other considerations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 138 6.4 Converting the database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 142 6.4.1 Automatic conversion using porting tools . . . . . . . . . . . . . . . . . . . . 143 6.4.2 Manual conversion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 144 6.4.3 Metadata transport . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 147 6.5 Sample database conversion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 148 6.5.1 Converting database objects with the IBM Data Movement Tool . . 148 6.5.2 Manual database object conversion and enhancements . . . . . . . . 158 Chapter 7. Data conversion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 167 7.1 Data porting considerations. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 168 7.1.1 Data porting commands and tools . . . . . . . . . . . . . . . . . . . . . . . . . 168 7.1.2 Differences in data formats . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 175 7.1.3 Differences in the user account management. . . . . . . . . . . . . . . . . 177 7.2 Sample project: Data porting. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 192 7.2.1 Export user data from MySQL. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 192 7.2.2 Map MySQL user data to DB2 user data . . . . . . . . . . . . . . . . . . . . 193 7.2.3 Create DB2 user . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 194 7.2.4 Export MySQL application data. . . . . . . . . . . . . . . . . . . . . . . . . . . . 195 7.2.5 Convert MySQL application data to DB2 format . . . . . . . . . . . . . . . 197 7.2.6 Import application data into DB2 . . . . . . . . . . . . . . . . . . . . . . . . . . . 197 7.2.7 Basic data checking . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 199 Chapter 8. Application conversion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 205 8.1 Data Manipulation Language differences and similarities . . . . . . . . . . . . 206 8.1.1 SELECT syntax. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 206 8.1.2 JOIN syntax. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 207 8.1.3 UNION syntax . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 208 8.1.4 Subquery syntax . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 209 8.1.5 Grouping, having, and ordering. . . . . . . . . . . . . . . . . . . . . . . . . . . . 209 8.1.6 Strings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 210 8.1.7 Implicit casting of data types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 213 8.1.8 String concatenation and NULL values. . . . . . . . . . . . . . . . . . . . . . 216 8.1.9 Record deletion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 218 8.1.10 Built-in functions and operators. . . . . . . . . . . . . . . . . . . . . . . . . . . 221 8.2 Application source conversion. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 222 8.2.1 Converting MySQL Perl applications to DB2 . . . . . . . . . . . . . . . . . 222
vi MySQL to DB2 Conversion Guide 8.2.2 Converting MySQL PHP applications to DB2 . . . . . . . . . . . . . . . . . 225 8.2.3 Converting MySQL Ruby on Rails applications to DB2 . . . . . . . . . 237 8.2.4 Converting MySQL Java applications to DB2 . . . . . . . . . . . . . . . . . 240 8.2.5 Converting MySQL C/C++ applications to DB2 . . . . . . . . . . . . . . . 247 8.2.6 Converting Connector/ODBC applications to DB2 . . . . . . . . . . . . . 256 8.2.7 Condition handling in DB2. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 259 8.2.8 Special conversions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 266 8.3 Additional application considerations . . . . . . . . . . . . . . . . . . . . . . . . . . . 271 8.3.1 The purpose of locking . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 271 8.3.2 Concurrency control and transaction isolation . . . . . . . . . . . . . . . . 272 8.3.3 DB2 isolation levels. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 272 8.3.4 Locking . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 274 8.3.5 Specifying the isolation level in DB2 . . . . . . . . . . . . . . . . . . . . . . . . 276 Chapter 9. Database administration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 279 9.1 Database configuration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 280 9.1.1 DB2 configuration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 280 9.2 Database recovery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 286 9.2.1 DB2 database recovery. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 286 9.3 Database replication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 296 9.4 Data movement. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 298 9.4.1 DB2 data movement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 298 9.5 High availability . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 304 9.6 Autonomics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 306 9.7 Workload management . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 311 9.8 Database management tools . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 312 9.8.1 DB2 Control Center. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 312 9.8.2 IBM Optim and Data Studio tool suite overview . . . . . . . . . . . . . . . 314 Chapter 10. Testing and tuning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 321 10.1 Test planning. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 322 10.1.1 Principles of software tests . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 322 10.1.2 Test documentation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 322 10.1.3 Test phases. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 322 10.1.4 Time planning and time exposure . . . . . . . . . . . . . . . . . . . . . . . . . 323 10.2 Data checking techniques . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 324 10.2.1 IMPORT/LOAD messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 324 10.2.2 Data checking . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 328 10.3 Code and application testing. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 330 10.3.1 Checking the application code . . . . . . . . . . . . . . . . . . . . . . . . . . . 331 10.3.2 Security testing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 331 10.4 Troubleshooting. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 332 10.4.1 Interpreting DB2 informational messages . . . . . . . . . . . . . . . . . . . 332
Contents vii 10.4.2 DB2 tools for troubleshooting . . . . . . . . . . . . . . . . . . . . . . . . . . . . 334 10.4.3 DB2 diagnostic logs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 335 10.4.4 DB2 support information . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 339 10.4.5 Monitoring tools. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 342 10.4.6 Visual Explain . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 354 10.5 Initial tuning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 356 10.5.1 Table space design . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 356 10.5.2 Physical placement of database objects . . . . . . . . . . . . . . . . . . . . 357 10.5.3 Buffer pools . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 360 10.5.4 Large transactions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 363 10.5.5 SQL execution plan. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 366 10.5.6 Configuration Advisor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 371 10.5.7 Design Advisor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 375 Chapter 11. Advanced DB2 features . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 381 11.1 DB2 pureXML . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 382 11.2 Data compression . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 386 11.3 Partitioning features . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 388 11.3.1 Database partitioning feature . . . . . . . . . . . . . . . . . . . . . . . . . . . . 388 11.3.2 Table partitioning. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 389 11.3.3 Multidimensional clustering . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 392 11.4 Materialized query tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 394 11.5 User-defined data types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 396 Appendix A. Mapping MySQL built-in functions and operators . . . . . . . 399 A.1 Grouping related functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 400 A.2 String functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 402 A.3 Numeric functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 408 A.4 Date and time functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 409 A.5 Comparing operators and other functions. . . . . . . . . . . . . . . . . . . . . . . . 410 Appendix B. Sample code for user-defined functions. . . . . . . . . . . . . . . 413 B.1 Sample code for BIT_AND . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 414 B.2 Sample code for FORMAT function . . . . . . . . . . . . . . . . . . . . . . . . . . . . 415 B.3 Sample code for RPAD and LPAD functions . . . . . . . . . . . . . . . . . . . . . 416 B.4 Sample code for GREATEST function . . . . . . . . . . . . . . . . . . . . . . . . . . 422 B.5 Sample code for LEAST . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 427 B.6 Sample code for BIT_COUNT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 431 B.7 Sample code for SUBSTRING_INDEX. . . . . . . . . . . . . . . . . . . . . . . . . . 432 B.8 Sample code for UNIX_TIMESTAMP . . . . . . . . . . . . . . . . . . . . . . . . . . . 433 Related publications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 435 IBM Redbooks publications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 435 Other publications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 435
viii MySQL to DB2 Conversion Guide Online resources . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 436 How to get IBM Redbooks publications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 439 Help from IBM . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 439 Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 441
© Copyright IBM Corp. 2009. All rights reserved. ix Notices This information was developed for products and services offered in the U.S.A. IBM may not offer the products, services, or features discussed in this document in other countries. Consult your local IBM representative for information on the products and services currently available in your area. Any reference to an IBM product, program, or service is not intended to state or imply that only that IBM product, program, or service may be used. Any functionally equivalent product, program, or service that does not infringe any IBM intellectual property right may be used instead. However, it is the user's responsibility to evaluate and verify the operation of any non-IBM product, program, or service. IBM may have patents or pending patent applications covering subject matter described in this document. The furnishing of this document does not give you any license to these patents. You can send license inquiries, in writing, to: IBM Director of Licensing, IBM Corporation, North Castle Drive, Armonk, NY 10504-1785 U.S.A. The following paragraph does not apply to the United Kingdom or any other country where such provisions are inconsistent with local law: INTERNATIONAL BUSINESS MACHINES CORPORATION PROVIDES THIS PUBLICATION "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESS OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF NON-INFRINGEMENT, MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE. Some states do not allow disclaimer of express or implied warranties in certain transactions, therefore, this statement may not apply to you. This information could include technical inaccuracies or typographical errors. Changes are periodically made to the information herein; these changes will be incorporated in new editions of the publication. IBM may make improvements and/or changes in the product(s) and/or the program(s) described in this publication at any time without notice. Any references in this information to non-IBM Web sites are provided for convenience only and do not in any manner serve as an endorsement of those Web sites. The materials at those Web sites are not part of the materials for this IBM product and use of those Web sites is at your own risk. IBM may use or distribute any of the information you supply in any way it believes appropriate without incurring any obligation to you. Information concerning non-IBM products was obtained from the suppliers of those products, their published announcements or other publicly available sources. IBM has not tested those products and cannot confirm the accuracy of performance, compatibility or any other claims related to non-IBM products. Questions on the capabilities of non-IBM products should be addressed to the suppliers of those products. This information contains examples of data and reports used in daily business operations. To illustrate them as completely as possible, the examples include the names of individuals, companies, brands, and products. All of these names are fictitious and any similarity to the names and addresses used by an actual business enterprise is entirely coincidental. COPYRIGHT LICENSE: This information contains sample application programs in source language, which illustrate programming techniques on various operating platforms. You may copy, modify, and distribute these sample programs in any form without payment to IBM, for the purposes of developing, using, marketing or distributing application programs conforming to the application programming interface for the operating platform for which the sample programs are written. These examples have not been thoroughly tested under all conditions. IBM, therefore, cannot guarantee or imply reliability, serviceability, or function of these programs.
x MySQL to DB2 Conversion Guide Trademarks IBM, the IBM logo, and ibm.com are trademarks or registered trademarks of International Business Machines Corporation in the United States, other countries, or both. These and other IBM trademarked terms are marked on their first occurrence in this information with the appropriate symbol (® or ™), indicating US registered or common law trademarks owned by IBM at the time this information was published. Such trademarks may also be registered or common law trademarks in other countries. A current list of IBM trademarks is available on the Web at http://www.ibm.com/legal/copytrade.shtml The following terms are trademarks of the International Business Machines Corporation in the United States, other countries, or both: 1-2-3® AIX® AS/400® ClearCase® DB2 Connect™ DB2 Universal Database™ DB2® developerWorks® Distributed Relational Database Architecture™ DRDA® eServer™ Everyplace® HACMP™ i5/OS® IBM® IMS™ Informix® InfoSphere™ iSeries® OpenPower® Optim™ OS/390® PartnerWorld® POWER® pSeries® pureXML® Rational Rose® Rational® Redbooks® Redbooks (logo) ® System i® System p® System z9® System z® Tivoli® UniData® WebSphere® z/OS® z9® zSeries® The following terms are trademarks of other companies: AMD, the AMD Arrow logo, and combinations thereof, are trademarks of Advanced Micro Devices, Inc. Snapshot, and the NetApp logo are trademarks or registered trademarks of NetApp, Inc. in the U.S. and other countries. SUSE, the Novell logo, and the N logo are registered trademarks of Novell, Inc. in the United States and other countries. Oracle, JD Edwards, PeopleSoft, Siebel, and TopLink are registered trademarks of Oracle Corporation and/or its affiliates. Red Hat, and the Shadowman logo are trademarks or registered trademarks of Red Hat, Inc. in the U.S. and other countries. VMware, the VMware "boxes" logo and design are registered trademarks or trademarks of VMware, Inc. in the United States and/or other jurisdictions. EJB, Enterprise JavaBeans, J2EE, Java, Java runtime environment, JavaBeans, JavaServer, JDBC, JSP, MySQL, Solaris, Sun, and all Java-based trademarks are trademarks of Sun Microsystems, Inc. in the United States, other countries, or both. Access, ActiveX, Expression, Microsoft, MS, SQL Server, Visual Basic, Visual Studio, Windows Mobile, Windows, and the Windows logo are trademarks of Microsoft Corporation in the United States, other countries, or both.
Notices xi Intel Pentium, Intel Xeon, Intel, Itanium-based, Itanium, Pentium, Intel logo, Intel Inside logo, and Intel Centrino logo are trademarks or registered trademarks of Intel Corporation or its subsidiaries in the United States, other countries, or both. UNIX is a registered trademark of The Open Group in the United States and other countries. Linux is a trademark of Linus Torvalds in the United States, other countries, or both. Other company, product, or service names may be trademarks or service marks of others.
xii MySQL to DB2 Conversion Guide
© Copyright IBM Corp. 2009. All rights reserved. xiii Preface Switching database vendors is often considered an exhausting challenge for database administrators and developers. Complexity, total cost, and the risk of downtime are often the reasons that restrain IT decision makers from starting the conversion project. The primary goal of this book is to show that, with the proper planning and guidance, converting from MySQL™ to IBM DB2® for Linux, UNIX, and Windows is not only feasible but straightforward. If you picked up this book, you are most likely considering converting to DB2 and are probably aware of several of the advantages of converting to DB2 data server. In this IBM® Redbooks® publication, we discuss in detail how you can take advantage of this industry leading database server. This book is an informative guide that describes how to convert the database system from MySQL 5.1 to DB2 9.7 on Linux®, and the steps involved in enabling the applications to use DB2 instead of MySQL. This MySQL to DB2 migration guide also presents the best practices in conversion strategy and planning, conversion tools, porting steps, and practical conversion examples. It is intended for technical staff involved in a MySQL to DB2 conversion project. The team who wrote this book This book was produced by a team of specialists from around the world working at the International Technical Support Organization, San Jose Center. Whei-Jen Chen is a Project Leader at the International Technical Support Organization, San Jose Center. She has extensive experience in application development, database design and modeling, and DB2 system administration. Whei-Jen is an IBM Certified Solutions Expert in Database Administration and Application Development, as well as an IBM Certified IT Specialist.
xiv MySQL to DB2 Conversion Guide Angela Carlson is a Software Engineer with IBM Canada. She has four years of technical experience working in the IT industry, focusing on relational database technology and application development. She has experience in developing relational database applications with PHP, Perl, and Java™. In Angela’s current position, she works closely with IBM Business Partners and their database needs to enable their solutions with DB2. She also researches and develops competitive material on DB2 and MySQL. Angela holds a Bachelor’s degree of Software Engineering Science from the University of Western Ontario. Thanks to the following people for their contributions to this project: Boris Bialek Program Director, Information Management Partner Technologies, IBM Canada Irina Delidjakova Information Management Emerging Partnerships and Technologies, IBM Canada Vlad Barshai Information Management Emerging Partnerships and Technologies, IBM Canada Martin Schlegel Information Management Partner Technologies, IBM Canada Daniel Krook Cloud Engineering and Experience, IBM U.S. Emma Jacob International Technical Support Organization, San Jose Center Thanks to the authors of the previous edition of this book: Authors of the first edition, MySQL to DB2 UDB Conversion Guide, SG24-7093, published in May 2004, were Whei-Jen Chen, Andreas Blank, Michael Hoeller, Rakesh Midha, and Klaus Subtil Become a published author Join us for a two- to six-week residency program. Help write a book dealing with specific products or solutions, while getting hands-on experience with
Preface xv leading-edge technologies. You will have the opportunity to team with IBM technical professionals, IBM Business Partners, and Clients. Your efforts will help increase product acceptance and customer satisfaction. As a bonus, you will develop a network of contacts in IBM development labs, and increase your productivity and marketability. Find out more about the residency program, browse the residency index, and apply online at: ibm.com/redbooks/residencies.html Comments welcome Your comments are important to us. We want our books to be as helpful as possible. Send us your comments about this book or other IBM Redbooks publications in one of the following ways: 򐂰 Use the online Contact us review IBM Redbooks publication form found at: ibm.com/redbooks 򐂰 Send your comments in an e-mail to: redbooks@us.ibm.com 򐂰 Mail your comments to: IBM Corporation, International Technical Support Organization Dept. HYTD Mail Station P099 2455 South Road Poughkeepsie, NY 12601-5400
xvi MySQL to DB2 Conversion Guide
© Copyright IBM Corp. 2009. All rights reserved. xvii Summary of changes This section describes the technical changes made in this edition of the book and in previous editions. This edition might also include minor corrections and editorial changes that are not identified. Summary of Changes for SG24-7093-01 for MySQL to DB2 Conversion Guide as created or updated on December 1, 2009. December 2009, Second Edition This revision reflects the addition, deletion, or modification of new and changed information described below. New information 򐂰 DB2 features and functions of DB2 for Linux, UNIX®, and Windows® Version 9, 9.5, and 9.7 򐂰 IBM Data Movement Tool 򐂰 MySQL 5.1 features Changed information 򐂰 DB2 and MySQL features and functions 򐂰 Conversion scenarios and examples
xviii MySQL to DB2 Conversion Guide
Executive summary xix Executive summary This book describes how to migrate MySQL 5.1 to DB2 Version 9.7 on Linux and enable your applications on DB2. To further ease your migration, this informative guide will cover best practices in migration strategy and planning, as well as the step-by-step directions, tools, and practical conversion. After completing this book, it will be clear to the technical reader that a MySQL to DB2 migration is easy and straightforward. Potential IBM clients seek migration information because DB2 offers performance and functional capabilities that the competition can't compare with. DB2 Express C, our lightweight community edition, is free to develop, deploy and redistribute, and is designed to give the IT community a powerful alternative to the open source or free databases currently available. DB2 Express C offers the same high quality, reliable, scalable features that you would expect from an IBM enterprise database at no charge. Fixed Term License support is available as well, at a lower price than the competition. The decision to migrate becomes simple when you consider that DB2 can be easily deployed in the development stack, while offering many additional features and ease of use. Enterprise class features aimed to lower the total cost of ownership can be found in every edition of DB2. DB2 has powerful autonomics which make installation, configuration, maintenance and administration virtually hands free. DB2 9.7's compression features help companies manage rising energy costs and reduce datacenter sprawl by reducing storage requirements and improving I/O efficiency. IBM is committed to providing products to our clients that are powerful and affordable. DB2 provides industry leading features, such as pureXML, Workload Management, and Granular Security. Using DB2 pureXML® makes XML data processing even faster, more flexible, and more reliable. Manage workloads with new threshold, priority and OS integration features in DB2 9.7. Keep data secure from internal and external threats using the unparalleled security control in DB2 9.7. Start taking advantage of these exciting new features and help your business manage costs and simplify application development. Migrate your database systems and applications today and discover why DB2 9.7 is a smarter product for a smarter planet. Arvind Krishna General Manager IBM Information Management
xx MySQL to DB2 Conversion Guide
© Copyright IBM Corp. 2009. All rights reserved. 1 Chapter 1. DB2 for Linux, UNIX, and Windows The goal of this chapter is to give an overview of the DB2 database server, its architecture, and the tools and utilities that are available with the server and application programming interfaces. In this chapter, we cover these topics: 򐂰 DB2 product overview 򐂰 DB2 for Linux, UNIX, and Windows architecture 򐂰 DB2 utilities 򐂰 DB2 database access 1
2 MySQL to DB2 Conversion Guide 1.1 Introduction IBM has an extremely strong history of database innovation and has developed a number of highly advanced data servers. It started in the 60s when IBM developed the Information Management System (IMS™), which is a hierarchical database management system. IMS was used to maintained inventory for the Saturn V moon rocket and the Apollo space vehicle. In the 70s, IBM invented the Relational Model and the Structured Query Language (SQL). In the 80s, IBM introduced DB2 for the mainframe (DB2 for z/OS®), which was the first database that used relational modeling and SQL. DB2 for distributed platforms (DB2 for Linux, UNIX, and Windows) was introduced in the 90s. Since then, IBM continues to develop on DB2 for both mainframe and distributed platforms. Although the relational data model has become more prevalent in the industry; IBM still realizes that the hierarchical data model is important. Therefore in July 2006, IBM launched the first hybrid (also known as multi-structured) data server. The release of DB2 for Linux, UNIX, and Windows Version 9 (DB2 9) data server brought the most exciting and innovative database features to the market; these features were further enhanced with the release of DB2 9.5 and 9.7. DB2 9 introduced many important features for both database administrators and application developers. These features included pureXML®, autonomics, table partitioning, data compression, and label-based access control. DB2 9.5 enhanced the manageability of the DB2 data server by introducing the threaded engine, easier integration with high availability disaster recovery (HADR), workload management, enhancements to autonomics, and more. The focus of the DB2 9.7 release is to provide unparalleled reliability and scalability for the changing needs of your business. Therefore, DB2 9.7 introduces enhancements to Version 9 and Version 9.5 features, such as enhancements to data compression, performance, workload management, security, and application development. When this book was written, DB2 9.7 had just been released on June 2009. DB2 9.7 is the database version that we use throughout the book. DB2 9.7 is a highly scalable and easy to install and manage hybrid data server. DB2 was developed to meet the demands of even the most critical database applications. This is managed through various autonomics capabilities, such as self-tuning memory management and automatic storage. DB2 provides a highly adaptable database environment while optimizing data storage through backups and deep data row compression. DB2 deep embedded capabilities allow for ubiquitous deployment in user directories and administrative installations for any size server. In a single database, DB2 provides native storage and processing of both transactional XML data in a pre-parsed tree format and relational data using pureXML technology.
Chapter 1. DB2 for Linux, UNIX, and Windows 3 1.2 Product overview DB2 for Linux, UNIX, and Windows spans the spectrum from products on handheld devices to large clusters and mainframes (see Figure 1-1). You can obtain more detailed information from the DB2 Web site: http://www.ibm.com/db2/ Figure 1-1 DB2 product overview 1.2.1 DB2 Data Server Editions for the production environment DB2 provides packages for users based on their business needs. In DB2 9.5, IBM introduced the common client concept for all IBM data management products that provides equal engine functionality across system boundaries. This functionality means that DB2 scales up and down according to your needs. Be it a desktop or a heavy duty enterprise server, DB2 has consistent behavior across any platform, providing an easy move regardless of the size limit. DB2 widens the boundaries even further, because all IBM data servers, including Informix®, now share a common API for SQL. Therefore, whether using Java Database Connectivity (JDBC™) on DB2 for z/OS, Informix 11 on an embedded system, or DB2 for Linux, UNIX, and Windows, the driver code remains the same. In fact, all database connections to any IBM data server can be made with the same common client. In addition, IBM Optim™ Data Studio provides a new cross-family tool for both application development and database management, underlining commitment across and beyond DB2.
4 MySQL to DB2 Conversion Guide There are several DB2 package formats: 򐂰 DB2 Personal Edition DB2 Personal Edition (PE) provides a single user database engine that is ideal for deployment to PC-based users. The PE includes the ability for remote management, the pureXML feature, and the SQL replication feature, making it the perfect choice for deployment in occasionally connected or remote office implementations that do not require multi-user capability, that is, point-of-sale systems. PE does not accept remote database requests; however, it contains DB2 client components and serves as a remote client to a DB2 Server. The DB2 Personal Edition can also be used for connecting and managing other DB2 data servers in the network. The Personal Edition includes most of the features included in DB2 Express Edition and runs in either 32-bit or 64-bit Intel® or AMD™ workstations for either Windows or Linux operating systems. 򐂰 DB2 Express-C DB2 Express-C is the no-charge community version of the DB2 data server. It is targeted towards developers and Independent Software Vendors (ISVs) to allow the development and deployment of applications, including the no charge distribution of DB2 Express-C itself. All applications developed with this version of DB2 can be moved to a higher edition of DB2 for Linux, UNIX and Windows and even DB2 for z/OS without any application changes if using the common SQL API set of the DB2 family. This version of DB2 is at no charge for download and is therefore perfectly suited for DB2 educators and students. DB2 Express-C does not restrict the database size and can be used in a 64-bit memory model. The code is optimized to use up to a maximum of 2 CPU cores and 2 GB of memory. No fix pack updates are available for this edition; however, new versions of Express-C are updated and freely available for download at any time. While this version does not include all the features of higher editions of DB2, such as storage optimization, replication services, or high availability, it comes with the award-winning pureXML technology to leverage both relational and XML data by being able to natively store XML data in a single database. Several of these features can be activated by purchasing the DB2 Express Fixed Term License (FTL). Obtaining the FTL provides 1 year of 24x7 support, plus the ability to use high availability and disaster recovery. At any point, users of DB2 Express-C can receive advice on the IBM DB2 Express Forum, which is monitored by IBM DB2 developers, by accessing the following link: http://www.ibm.com/developerworks/forums/forum.jspa?forumID=805
Chapter 1. DB2 for Linux, UNIX, and Windows 5 DB2 Express-C runs on Windows or Linux for both Intel and AMD on 32-bit or 64-bit architecture, as well as on Linux on Power (IBM System p® and System i®). You can download DB2 Express-C from this link: hhttp://www-01.ibm.com/software/data/db2/express/ 򐂰 DB2 Express + Fixed Term License The plus fixed term license (FTL) is a 1-year license subscription with 24x7 IBM support to DB2 Express, providing service-level assurances for problem resolution and offering fix pack updates tied to the usual DB2 fix pack schedule. FTL also raises the DB2 Express-C limitations and is optimized to run for 4 CPU cores and 4 GB of memory. Additional features with the DB2 Express FTL include the High Availability Disaster Recovery (HADR) feature and full SQL replication capability. Licensing is quite easy with DB2 Express-C + FTL, because it requires only purchasing one FTL subscription per data server no matter how it is used or as explained before, its size. This licensing explicitly applies also to an HADR setup, where no distinction is made between cold, warm, or hot standby usage. DB2 Express-C with FTL runs on Windows or Linux for both Intel or AMD on 32-bit or 64-bit architecture, as well as on Linux on Power (IBM System p and System i). More information about DB2 Express + Fixed Term License is available at this Web site: http://www-01.ibm.com/software/data/db2/express/support.html?S_TACT=105AGX2 8&S_CMP=DLMAIN 򐂰 DB2 Express DB2 Express edition is specifically tailored for small and medium businesses (SMBs). It is designed for independent software vendors who need an easy-to-install database integrated into their application software solution. It is a multi-user version that supports local and remote applications in stand-alone and local area network (LAN) environments. DB2 Express utilizes up to 4 GB of memory and can be installed on a server with up to 200 processor value units. For more information about processor value units, visit this Web site: http://www.ibm.com/developerworks/db2/library/techarticle/dm-0611zikopoulos 2/ Several key features and strengths of the Express Edition are simplified deployment, SQL replication, backup compression, autonomic management
6 MySQL to DB2 Conversion Guide features, such as the Self-Tuning Memory Manager, adaptive utility throttling, automatic storage management, configuration and design advisors, health and fault monitors, automated backup, pureXML, high availability, and more. DB2 Express runs on Linux, Solaris™ x86, or Windows platforms. 򐂰 DB2 Workgroup DB2 Workgroup Server Edition is used primarily in small to medium-sized business environments. It includes support for a per authorized user-based or a per processor-based licensing model that is designed to provide an attractive price point for smaller installations while still providing a fully functional data server on a wider range of platforms compared to lower function editions of DB2. Workgroup Server now includes the High Availability Disaster Recovery (HADR) feature, Tivoli System Automation for MultiPlatforms (TSA MP), and Online Table Reorganization. The following feature packs are available: – Query Optimization including Materialized Query Tables (cached tables) – Multidimensional Clustering and Query Parallelism – pureXML This DB2 edition can be deployed on systems with up to 400 processor value units and 16 GB of memory. While the DB2 Express edition only runs on Windows, Linux, or Solaris, the Workgroup Server edition adds support for AIX®, Hewlett-Packard UNIX (HP-UX) on Itanium64, and Solaris on x86 and Sparc. 򐂰 DB2 Enterprise DB2 Enterprise Server Edition meets the database server needs for any size business. This product is the ideal foundation for building data warehouses, transaction processing, or Web-based solutions, as well as for a back-end for packaged solutions, such as enterprise resource planning (ERP), customer relationship management (CRM), and supply chain management (SCM). In addition, the DB2 Enterprise Server Edition offers connectivity and integration for other enterprise DB2 and Informix data sources. The Enterprise Server Edition does not pose limits to the maximum memory or number of CPU cores. It can be licensed with either authorized user licenses or processor value unit licenses. In addition to the features offered in the Workgroup Server Edition, the following features are also available: Query Parallelism, Multidimensional Clustering, Materialized Query Tables, Table Partitioning, and Connection Concentration. With the DB2 feature pack, you can add the following features: Performance Optimization Feature, Advanced Access® Control Feature, Storage Optimization Feature, and Geodetic Data Management Feature.
Chapter 1. DB2 for Linux, UNIX, and Windows 7 DB2 Enterprise Server Edition runs on Windows (32-bit and 64-bit), Linux (Intel/ AMD 64-bit, System i, System p, System z®), AIX, Solaris (Sparc and x64) and HP-UX (ia64). 򐂰 InfoSphere™ Warehouse InfoSphere Warehouse (formerly known as DB2 Warehouse) is a powerful platform for building business intelligence (BI) solutions. InfoSphere Warehouse comes as single integrated software package, using DB2 Enterprise Server Edition as its base. It provides an ideal solution for companies that need to consolidate data marts, information silos, and business analytics. InfoSphere Warehouse contains database management tools and embedded data movement and transformation tools. In addition to the base feature set of DB2 Enterprise Server Edition, the DB2 feature packs containing Data Partitioning Feature (DPF) are standard in all editions of InfoSphere Warehouse. Workload Management and Deep Data Row Compression are optional features for InfoSphere Warehouse Enterprise Base Edition. The overall integrated software package for InfoSphere Warehouse has support for slice and dice analytics and provides direct support for optimized online analytical processing (OLAP) analytics against the dynamic warehouse. Embedded analytical features include capabilities for Data Mining, In-line Analytics, Cubing Services, and Unstructured Analytics. 򐂰 Other DB2 editions There are also DB2 versions for System i and System z available. See the following Web site for details: http://www.ibm.com/software/data/db2/ 1.2.2 Products for accessing System z and System i host data With the following DB2 products, you can extend enterprise systems to access the host data in a System z or System i system: 򐂰 DB2 Connect™ Personal Edition The DB2 Connect Personal Edition provides the application programming interface (API) drivers and connectivity infrastructure to enable direct connectivity from desktop applications to System z and System i data servers. This product is specifically designed and is licensed for enabling two-tier, client/server applications running on individual workstations, and as such, is not appropriate for use on servers. 򐂰 DB2 Connect Enterprise Edition The DB2 Connect Enterprise Edition addresses the needs of organizations that require robust connectivity from a variety of desktop systems to System i
8 MySQL to DB2 Conversion Guide and System z database servers. DB2 client software is deployed on desktop systems and provides drivers that connect client/server applications running on these desktop systems to a DB2 Connect server (gateway) that accesses host data. The licensing model for this product is user-based. 򐂰 DB2 Connect Application Server Edition Technically, the DB2 Connect Application Server Edition product is identical to the DB2 Connect Enterprise Server; however, its licensing terms and conditions are meant to address the specific needs of multi-tier, client/server applications, as well as applications that utilize Web technologies. DB2 Connect Application Server Edition license charges are based on the size and number of processors that are available to the application servers where the application is running. 򐂰 DB2 Connect Unlimited Edition The DB2 Connect Unlimited Edition product is ideal for organizations with extensive usage of DB2 Connect, especially where multiple applications are involved. This product provides program code of the DB2 Connect Personal Edition, as well as program code identical to the DB2 Connect Application Server Edition for unlimited deployment throughout an organization. 1.2.3 DB2 for pervasive platforms DB2 Everyplace® Edition supports a wide variety of handheld devices, such as Palm OS, Windows Mobile® for Pocket PC, Windows desktops, Symbian, QNX Neutrino, and various Linux distributions. Its strength is to synchronize with data from other systems, using security features, including table-level encryption and communication encryption, such as Secure Sockets Layer (SSL). DB2 Everyplace is also extremely flexible by being completely self-managed and by supporting many programmable API interfaces, such as Open Database Connectivity (ODBC), Java Database Connectivity (JDBC), .NET, DB2 call level interface (CLI), and so on. Its engine is highly flexible, which makes it easy to move DB2 Everyplace databases from one supported mobile device to another. 1.2.4 Additional DB2 data server features In addition to the product offerings for Linux, UNIX, Windows, and accessing System z and System i host data, DB2 also offers a great variety of features, which are included in various DB2 packages. Information about additional DB2 features and products can be found at this Web site: http://www.ibm.com/software/data/db2/9/features.html?S_CMP=wspace
Chapter 1. DB2 for Linux, UNIX, and Windows 9 1.3 DB2 for Linux, UNIX, and Windows architecture Data servers provide software services for the secure and efficient management of structured information. DB2 is a hybrid relational and XML data server. A data server refers to a computer where the DB2 database engine is installed. The DB2 engine is a full-function, robust database management system that includes optimized SQL support based on actual database usage and tools to help manage the data. In this section, we will discuss the DB2 engine architecture and the database objects that are used to maintain your database. 1.3.1 DB2 9.7 threaded architecture and process model Figure 1-2 shows the DB2 9.7 architecture overview. Starting with DB2 9.5, a threaded model architecture is used. There are many advantages to using a threaded model architecture. Firstly, a new thread requires less memory and fewer operating system resources than a process, because certain operating system resources can be shared among all threads within the same process. Moreover, on specific platforms, the context switch time for threads is less than that for processes, which can improve performance. Figure 1-2 DB2 threaded architecture
10 MySQL to DB2 Conversion Guide From a client/server perspective, the client code and the server code are separated into separate address spaces. The application code runs in the client process, while the server code runs in a separate process. The client process can run on either the same machine as the data server or another machine, accessing the data server through a programming interface. The memory units are allocated for database managers, databases, and applications. Because DB2 is running with a threaded architecture, all threads within the engine process share the same address space, meaning all threads can immediately see new memory allocations. This design creates a simplified memory model by allowing memory growth and shrinkage through control of a single memory parameter for an entire instance. This control is automatically performed by the Self-Tuning Memory Manager, which can also tune other memory parameters for best performance without DBA intervention. Its adaptive algorithm is able to react to unforeseen memory requirements in DB2 caused by workloads running against it. To enable access to a specific database, the DB2 instance process responsible for the database must be running on the DB2 server. When an instance process is started, several processes are created, which interact with one another to maintain connected applications and the database. There are several background processes in DB2 that are pre-started; other processes start on a need-only basis. There are several important background processes: 򐂰 The main process is the DB2 System Controller (db2sysc), which runs the entire DB2 engine infrastructure. The DB2 data server activities are performed by Engine Dispatchable Units (EDU), which are defined as threads running within a single operating system process. 򐂰 A second DB2 background process is started together with the system controller and is called the DB2 Watch Dog (db2wdog). Its responsibility is to watch and monitor the system controller and to react to error conditions. 򐂰 For autonomic tasks, another process is initialized when the database is activated, which can happen either manually or by being triggered by a client connection. This process is the Autonomic Computing Daemon (db2acd) and runs autonomic tasks, such as health-monitoring, auto-runstats, and the administration scheduler on the client side. 򐂰 There is also the db2fmp process, which, decoupled from the DB2 system controller process, serves thread-safe stored procedures and user-defined functions (UDFs). 1.3.2 DB2 database objects In this section, we introduce the DB2 objects and their relationships to each other. Figure 1-3 on page 11 shows the fundamental DB2 database objects.
Chapter 1. DB2 for Linux, UNIX, and Windows 11 Figure 1-3 DB2 object relationships These are a few major DB2 objects: 򐂰 Instances A DB2 instance represents the database management system. It controls how data is manipulated and manages system resources assigned to it. Each instance is a complete, fairly independent environment, containing all the database partitions defined for a given parallel database system. An instance can have its own set of databases (which other instances cannot access directly), and all database partitions share the same system directories. Each instance has separate security from other instances on the same machine (system), allowing for situations where both production and development environments are run on the same machine without interference. In order to connect to a database, any database client must first establish a network connection to the instance. 򐂰 Databases A database is a structured collection of data, which is stored within tables. Since DB2 9, data within tables can be stored as both relational data and XML documents natively in a pre-parsed tree format within a table column. Each database includes a set of system catalog tables that describes the
12 MySQL to DB2 Conversion Guide logical and physical structure of the object in the database, a configuration file containing the parameter values configured for the database, and a recovery log. Figure 1-4 shows the relationship between instances, databases, and tables. Figure 1-4 Relationship between instances, databases, and tables 򐂰 Database partition groups A database partition group is a set of one or more database partitions (Figure 1-5 on page 13). A database partition group must be created prior to the creation of the tables in a database. This database partition group is where the table spaces will be stored. After the database partition group is there, a table space can be created where tables will be stored. If a partition group is not specified, there is a default group where table spaces are allocated. In a non-partitioned environment, all the data resides in a single partition; therefore, it is unnecessary to worry about partition groups in simple setups.
Chapter 1. DB2 for Linux, UNIX, and Windows 13 Figure 1-5 Database partition groups in a database 򐂰 System catalog tables Each database includes a set of system catalog tables that describes the logical and physical structure of the data. DB2 creates and maintains an extensive set of system catalog tables for each database. These tables contain information regarding definitions of database objects, including user tables, views, indexes, and security information about the privileges that users have on these objects. Catalog tables are created when the database is created and are updated during normal operations. They cannot be explicitly created or dropped; however, they can be queried for their contents using the catalog views. 򐂰 Table spaces A database is organized into subdivided table spaces, which store data. When creating a table, you can decide to have certain objects, such as indexes and large object (LOB) data, kept separately from the rest of the table data. A table space is equally spread over one or more physical storage devices, which is called striping. When using a database, if no additional table spaces are created by the user, the default user table space is used. DB2 allows you as much control as needed. When table spaces are created, they reside in database partition groups. The table space definitions and attributes are maintained in the database system catalog. Each table space has at least one container assigned to it. A container is an allocation of physical storage, such as a file or a device. Table spaces come in two types: system managed space (SMS) or database managed space (DMS), as shown in Figure 1-6 on page 14. In an SMS table
14 MySQL to DB2 Conversion Guide space, each container is a directory in the file system of the operating system. This type of table space allows the operating system’s file manager to control the storage space. In a DMS table space, each container is either a re-sizable file or a pre-allocated physical device, such as a disk, which the database manager must control. Figure 1-6 DMS/SMS table spaces and containers When using SMS or DMS in combination with container files, you can choose how DB2 handles these files. For example, you can choose to enable various optimization features if supported by the operating systems, that is, Direct I/O (to bypass file system caching; always enabled with raw and block devices), Vector I/O (reading contiguous data pages from disk into contiguous portions of memory), and Async I/O (non-sequential processing of read and write requests across multiple disks to avoid delays from synchronous events). When using the Automatic Storage feature in DB2, you can simply specify folders where the database can automatically create and manage DMS table spaces. When more space is required, the database manager automatically allocates more space. Table spaces can be automatically resized using this feature. This feature provides a convenient and worry-free operation scenario. You can perform manual operations without having to specify container files. 򐂰 Containers A container is a physical storage device. It can be identified by a directory name, a device name, or a file name. A container is assigned to a table space. A single table space can span many containers, but each container can belong to only one table space.
Chapter 1. DB2 for Linux, UNIX, and Windows 15 򐂰 Buffer pools A buffer pool is the amount of memory allocated to cache table and index data pages. The purpose of the buffer pool is to improve system performance. Think of it as a database-controlled file system cache. Data can be accessed much faster from memory than from disk. Therefore, the fewer times the database manager needs to read from or write to a disk (I/O) synchronously, the better the performance of the application. The size of the buffer pool is the single most important performance tuning area to help reduce the delay caused by synchronous I/O. Buffer pool memory can be automatically tuned online in the same way as most other memory-related parameters. The feature responsible for automatic tuning is called the Self-Tuning Memory Manager, which allocates and releases new memory from the OS by shifting unused memory within DB2 to components. 򐂰 Schemas A schema is an identifier, by default, the user ID, which qualifies tables and other database objects. A schema can be owned by an individual, and the owner can control access to the data and the objects within it. A schema name is used as the first part of a two-part object name. For example, a schema named Smith might qualify a table named SMITH.PAYROLL. 򐂰 Tables A database presents data as a collection of tables. Data within a table is arranged in columns and rows. A table can contain XML documents that are natively stored as a parsed hierarchical format, as shown in Figure 1-7 on page 16. The data in the table is logically related, and relationships can be defined between tables. Table data is accessed by using Structured Query Language (SQL) or XQuery with XPath expressions. Both products are standardized query languages for defining and manipulating both relational and XML data in a database. A query is used in applications or by users to retrieve data from a database. A typical query for relational data uses SQL to create a statement in the form of: SELECT <column_name> FROM <table_name> A typical XQuery for the table that is shown in figure 1-7 looks like this query: xquery db2-fn:sqlquery("SELECT INFO FROM XMLEmployeeInfo WHERE EmpID=1001")/customerinfo/name This query allows us to iterate through all our table rows and return customer information from each XML document stored in each row for the example table that is outlined in Figure 1-7 on page 16. DB2 and the XQuery language also allow us to modify and update a subtree of an XML document in place without having to rewrite the whole document. This query is possible,
16 MySQL to DB2 Conversion Guide because we already store XML documents in a pre-parsed and hierarchical format: db2 create table dept (EmpID int,…, XMLEmployeeInfo xml) Figure 1-7 Relational and XML data in a single table When using the Deep Data Row Compression feature, DB2 is able to transparently compress and decompress table rows (for each table with compression turned on). This feature can effectively save 45-80% of the space on disk. Compressed rows in a table are compressed when pre-fetched to buffer pool memory and left in a compressed state until they are actually used. Although decompression of the data when it is fetched adds a slight overhead, I/O bound workloads will have a performance gain due to the reduced amount of data we actually need to read and write from or to disk, as well as saved memory. 򐂰 Views A view provides another way of looking at data from one or more tables; it is a named specification of a result table. The specification is a SELECT statement that runs whenever the view is referenced in an SQL statement. A view has columns and rows just like a base table. All views can be used just like base tables for data retrieval. Figure 1-8 on page 17 shows the relationship between tables and views.
Chapter 1. DB2 for Linux, UNIX, and Windows 17 Figure 1-8 Relationship between tables and views 򐂰 Indexes An index is a set of keys, each pointing to rows in a table. For example, table A has an index based on the first column in the table (Figure 1-9 on page 18). This key value provides a pointer to the rows in the table: value 19 points to record KMP. If searching for this particular record, a full table scan can be avoided, because we have an index defined. Except for changes in performance, users of this table are unaware that an index is being used. DB2 decides whether to use the index or not. DB2 also provides tools, such as the Design Advisor, that can help decide what indexes will be beneficial. An index allows efficient access when selecting a subset of rows in a table by creating a direct path to the data through pointers. The DB2 SQL Optimizer chooses the most efficient way to access data in tables. The optimizer takes indexes into consideration when determining the fastest access path. Indexes have both benefits and disadvantages. Be careful when defining indexes and take into consideration costs associated with update, delete, and insert operations and maintenance, such as reorganization and recovery.
18 MySQL to DB2 Conversion Guide Figure 1-9 Relationship between indexes and tables 1.3.3 DB2 catalog In DB2, the metadata is stored in a set of base tables and views called the catalog. The catalog contains information about the logical and physical structure of the database objects, object privileges, integrity information, and more. The catalog is automatically created with the database. The base tables are owned by the SYSIBM schema and stored in the SYSCATSPACE table space. On top of the base tables, the SYSCAT and SYSSTAT views are created. SYSCAT views are the read-only views that contain the object information and are found in the SYSCAT schema. SYSSTAT views are views, which you can update, that contain statistical information that is found in the SYSTAT schema. You can obtain the complete DB2 catalog views in DB2 SQL Reference Volume 1 and 2 available for download under the following link: http://www.ibm.com/support/docview.wss?rs=71&uid=swg27015148 1.4 DB2 utilities All DB2 system commands are installed in the sqllib/bin directory during installation. Several of the most important commands in DB2 are listed in Table 1-1 on page 19, Table 1-2 on page 19, Table 1-3 on page 20, Table 1-4 on page 20, and Table 1-5 on page 20. You can obtain more information in the DB2 manuals, especially the Command Reference that is available at this link: http://www.ibm.com/support/docview.wss?rs=71&uid=swg27015148
Chapter 1. DB2 for Linux, UNIX, and Windows 19 Table 1-1 DB2 instance commands Table 1-2 Handling general database tasks Command Description Example db2start Starts the default instance db2start db2stop Stops the current instance db2stop -f db2icrt Creates an instance db2icrt -u db2fenc1 db2inst1 db2idrop Drops an instance db2idrop -f db2inst1 db2ilist Lists all instance db2ilist db2imigr Converts an instance after upgrading DB2 db2imigr -u db2fenc1 db2inst1 db2iupdt Updates an instance after installation of a fix pack db2iupdt -u db2fenc1 db2inst1 Description Example Deactivates a database db2 deactivate db mydb Views database manager settings db2 get dbm cfg show detail Changes a database manager setting db2 update dbm cfg using health_mon off Views database settings db2 get db cfg show detail Changes a database setting db2 update db cfg using SELF_TUNING_MEM on Views registry values db2set Changes registry parameters db2set DB2AUTOSTART=yes Views cataloged databases db2 list db directory Views cataloged nodes db2 list node directory Lists all connected applications db2 list applications all Forces applications off db2 force application (41408, 55458) Lists utilities db2 list utilities Gets a database snapshot db2 get snapshot for database on mydb
20 MySQL to DB2 Conversion Guide Table 1-3 DB2 DAS instance commands Table 1-4 Informational commands Table 1-5 Graphical tools Command Description Example db2admin Starts and stops the DB2 Administration Server db2admin start dasauto Autostarts DB2 Administration Server dasauto -on dascrt Creates a DB2 Administration Server dascrt -u dasusr1 dasdrop Removes a DB2 Administration Server dasdrop dasmigr Converts a DB2 Administration Server dasmigr Command Description Example db2level Shows the current version and service level db2level db2look Extracts DDL statements db2look -d dep -a -e -o db2look.sql db2dart Database analysis and reporting tool db2dart dbaddr db2pd Troubleshooting tool db2pd -db sample -locks Tool Command Purpose IBM Data Studio Integrated development environment package, which includes all administrative capabilities, as well as an integrated Eclipse development environment for Java, XML, and Web services Optim Development Studio A purchasable integrated development environment for advanced development of DB2 databases Optim Database Administrator A purchasable integrated development environment for advanced administration of DB2 databases DB2 installer db2setup Installs DB2 and creates instances DB2 instance installer db2isetup Creates instances Control Center db2cc Administers instances, databases Replication Center db2rc Administers replication between servers Satellite Admin. Center db2cc Administers collections of DB2 servers
Chapter 1. DB2 for Linux, UNIX, and Windows 21 1.5 DB2 database access IBM introduced common application development and tooling support for DB2 for Linux, UNIX, and Windows (as of 9.5), DB2 for z/OS, and Informix Dynamic Server. This support generally lowers development costs through reuse and support of common components across IBM data servers. More information to application development, tools, clients, and drivers can be obtained at this link: http://www.ibm.com/software/data/db2/ad/ and http://www.ibm.com/developerworks/db2/library/techarticle/dm-0804zikopoulos 1.5.1 DB2 clients and drivers DB2 clients and drivers are used to access databases that reside on DB2 servers. A database cannot be created on a DB2 client. IBM data server clients DB2 Data Server 9.7 comes with the following clients: 򐂰 IBM Data Server Runtime Client 򐂰 IBM Data Server Client Command line processor (CLP) db2 or db2cmd Executes DB2 commands at the command line Health Center db2hc Views/resolves health monitor alerts Task Center db2cc –tc Schedules, runs tasks; notifies contacts Journal db2cc -j Monitors jobs, recovery history, and so on Configuration Assistant db2ca Configures instances and databases Note: The Control Center and its associated components have been deprecated in Version 9.7 and might be removed in a future release. We recommend that you use the new suite of GUI tools for managing DB2 data and data-centric applications. These new tools include the IBM Data Studio, the Optim Development Studio, and the Optim Database Administrator.
22 MySQL to DB2 Conversion Guide The IBM Data Server Runtime client offers the basic client functionality and includes drivers for ODBC, CLI, ADO.NET, Object Linking and Embedding (OLE) DB, PHP, Ruby, Perl-DB2, JDBC, and SQLJ. This client already includes the drivers and the capabilities to define data sources. Furthermore, the Lightweight Directory Access Protocol (LDAP) is available, as well. Additionally, the IBM Data Server Client provides vast amounts of sample code in various languages, header files for application development and graphical administration and development tools, such as the DB2 Control Center, the IBM Data Studio, the MS® Visual Studio® Tools, and more. Figure 1-10 illustrates how to connect to a DB2 data server using the IBM data server clients. Figure 1-10 DB2 9.7 client and drivers IBM Optim Data Studio is a comprehensive suite of integrated Eclipse tools geared toward both database developers and database administrators. It reduces the time to perform day-to-day administration tasks, create, deploy, and debug SQL and Java stored procedures, deploy data-centric Web services, and create queries for relational and XML data using SQL and XQuery. It supports multiple IBM data servers, including DB2 for Linux, UNIX, and Windows, DB2 for i5/OS® and z/OS, Apache Derby, and the Informix Dynamic Server. Because it is
Chapter 1. DB2 for Linux, UNIX, and Windows 23 built on the extensible Eclipse framework, this IDE includes a number of plug-ins to support programming languages, such as Java, C/C++, PHP, Ruby, Perl, and so on. Other plug-ins are available to maintain the written application sources in various source code repositories, for example, the Concurrent Versions System (CVS) or IBM Rational® ClearCase® from within IBM Optim Data Studio. If running mixed versions of DB2 servers and clients, it is good to know that DB2 Clients from DB2 UDB Version 8 and DB2 9.1 or 9.5 for Linux, UNIX, and Windows are still supported and able to connect to a DB2 9.7 data server. In the reverse direction, the newer IBM data server clients from Version 9.7 can also connect to the earlier DB2 9.1 and DB2 UDB Version 8 servers using the IBM Data Server Driver for ODBC, CLI, and .Net. In this case, however, new DB2 Version 9.7 functionality is not available. IBM data server drivers The IBM data server drivers include the products: 򐂰 IBM Data Server Driver for JDBC and SQLJ 򐂰 IBM Data Server Driver for ODBC, CLI, and .NET As of DB2 Version 9.5, both clients and drivers are decoupled from the server release schedule and can be downloaded separately. The IBM Data Server Driver for JDBC and SQLJ is already included in the IBM Data Server Runtime Client. It provides support for JDBC 3 and 4 compliant applications, as well as for Java applications using static SQL (SQLJ). Support is also provided for pureXML, SQL/XML, and XQuery. All of this support and other features, such as connection concentration, automatic client reroute, and more, are provided within in a single package called db2jcc4.jar. The IBM Data Server Driver for ODBC, CLI and .Net is a lightweight deployment solution for Windows applications to provide runtime support for applications without needing to install the Data Server client or the Data Server Runtime Client. On Windows, the driver comes as an installable image including merge modules to easily embed it in a Windows installer-based installation. On Linux and UNIX, there is another easy deployment solution called the IBM Data Server Driver for ODBC and CLI, which is available in tar format. Communication protocols DB2 primarily uses these protocols to communicate: 򐂰 TCP, IPv4, IPv6, and Named Pipes (Windows only) for remote connections 򐂰 Interprocess Communication (IPC) for local connections within a DB2 instance
24 MySQL to DB2 Conversion Guide For client/server communication, DB2 supports TCP/IP and Named Pipes for remote or local loopback connections and uses IPC for client connections, which are local to the DB2 server instance. Local and remote DB2 connections are illustrated in Figure 1-11. Figure 1-11 Client connection scenario Protocols are automatically detected and configured during an instance creation. The DB2COMM registry variable identifies the protocol detected in a server. To enable a specific protocol on the server, the db2set db2comm command must be executed. For TCP/IP, a unique port address has to be specified in the database manager configuration. This port is registered in the services file (usually /etc/services on UNIX and Linux). For example, to reserve port 50000 with the service name db2icdb2, the entry in the services file is: db2icdb2 50000/tcp From the command line, this information can be then updated in the database manager with the following DB2 command: db2 UPDATE DBM CFG USING SVCENAME db2icdb2 These tasks can also be performed using the DB2 Configuration Assistant utility. Client/server configuration methods The following methods are available for client/server configuration: 򐂰 Statically using command line processor (CLP) or Configuration Assistant (Discovery Service available) 򐂰 Dynamically using Lightweight Directory Access Protocol (LDAP) At the client side, the database information is configured using either the CATALOG command or using the Configuration Assistant. The databases are
Chapter 1. DB2 for Linux, UNIX, and Windows 25 configured under a node, which describes host information, such as protocol use, port number, and so on. To configure a remote TCP/IP node, use the following command: db2 CATALOG TCPIP NODE node-name REMOTE host-name SERVER service-name The service name registered in the server or the port number can be specified in the SERVER option. To catalog a database under this node, the command used is: db2 CATALOG DATABASE database-name AS alias-name AT NODE node-name When using the Configuration Assistant GUI tool to add a database connection, a database discovery can be started to find the desired database. For information about how to enable and configure DB2 in an LDAP environment, have a look at the Lightweight Directory Access Protocol (LDAP) section within the “Database fundamentals” chapter in the DB2 Information Center. The DB2 Information Center contains searchable and structured Web pages and can be installed locally. Additionally, the Information Center is also publicly available online at this Web site: http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp 1.5.2 Application access You can use various methods when deploying applications with DB2 Data Server 9.7: 򐂰 Single-tier In this configuration, the application and the database reside on the same system. In enterprise environments, it can be rare to see such a configuration, because remote access to a database server is typically required. Nonetheless, this method is quite common for developing applications that can later be deployed transparently in a multi-tier DB2 environment without any changes or batch applications. 򐂰 Client/Server or 2-tier The application and the database reside on separate systems. The machines where the application runs typically have a DB2 client installed, which communicates over the network to a database server. For the application, the physical location of the data is transparent. The application communicates with the DB2 client using a standard interface (for example, ODBC) and the Note: DB2 Discovery method is enabled at the instance level using the DISCOVER_INST parameter, and at database level using the DISCOVER_DB parameter.
26 MySQL to DB2 Conversion Guide DB2 client takes over the task of accessing the data over the network. In certain cases, such as browser-based access or Java-based access, it is not necessary to have the DB2 client running on the same machine where the application executes. DB2 provides exceptional flexibility for mixing and matching client and server platforms in a heterogeneous environment. DB2 client and server code is available for a wide variety of platforms. For example, the application can execute on a Windows-based machine with a DB2 client for Windows, which can then access a DB2 database on a Linux server. Likewise, the Linux machine can act as a client and access data from UNIX servers or mainframes. 򐂰 Multi-tier In a multi-tier configuration, the application, DB2 client, and the data source typically reside on separate systems. Table 1-6 provides examples of these configuration scenarios. Table 1-6 Multi-tier configuration examples IBM recognizes that in many cases there might be a need for accessing data from a variety of distributed data sources rather than one centralized database. The data sources can be from IBM, such as DB2 or Informix, from non-IBM databases, such as Oracle®, or even from non-relational data, such as files or spreadsheets. As illustrated in the last scenario in Table 1-6, IBM offers the most comprehensive business integration solution by allowing federated access to a variety of distributed data sources. 1.5.3 DB2 application programming interfaces In order to access or manage DB2 objects, several application programming interfaces (APIs) can be used, as shown in Figure 1-12 on page 27. Client Middle-tier Server Web-browser Web server DB2 database Application client Application server DB2 client DB2 database server 1 DB2 database server 2 Application DB2 Client DB2 Connect Gateway System z®, System i® Application DB2 Client DB2 server Secondary data sources (for example, Mainframe DB2, Non-DB2, and non-relational)
Chapter 1. DB2 for Linux, UNIX, and Windows 27 Figure 1-12 Application connections to DB2 DB2 administrative application programming interface DB2 provides numerous administrative APIs, which allow applications to perform database administration tasks available in DB2 Control Center, such as importing and exporting data, or creating, activating, backing up, or restoring a database. These calls can be included within embedded SQL and DB2 CLI applications. Examples of API programs can be found in the DB2 home directory sqllib/sample/ for various programming languages. For additional information, refer to DB2 Administrative API Reference, SC23-5824. Embedded SQL statements in applications Two types of SQL statements have to be distinguished: 򐂰 Static SQL statements With static SQL statements, the SQL statement type and the table and column names are known before compilation time. The specific data values for which the statement is searching are not known. These values can be represented in host language variables. Before compiling and linking the program, pre-compiling and binding of the embedded SQL statements must be performed. Pre-compiling converts
28 MySQL to DB2 Conversion Guide embedded SQL statements into DB2 runtime API calls that a host compiler can process to create a bind file. The bind command creates a package in the database. This package then contains the SQL operation and the access plan that DB2 will use to perform the operations. 򐂰 Dynamic SQL Dynamic SQL statements in an application are built and executed at run time. For a dynamically prepared SQL statement, the syntax has to be checked and an access plan has to be generated during the program execution. Examples of embedded static and dynamic SQL can be found in the DB2 home directory: sqllib/samples/. DB2 call level interface DB2 call level interface (DB2 CLI) is a programming interface that can be used from C and C++ applications to access DB2 data servers. DB2 CLI is based on the Microsoft® Open Database Connectivity (ODBC) specification and the International Organization for Standardization (ISO) CLI standard. The DB2 CLI library can be loaded as an ODBC driver by an ODBC driver manager. DB2 CLI includes support for many ODBC and ISO SQL/CLI functions, as well as DB2 specific functions. Figure 1-13 illustrates the ODBC driver manager environment and the DB2 CLI environment. Figure 1-13 ODBC/CLI driver connectivity
Random documents with unrelated content Scribd suggests to you:
it were a sword in his heart, but at her look and gesture—her frightened look, her imploring gesture—the sword was turned and still he was alive, alive to watch her die. In those moments his soul left time and space, and hung a tortured point, infinitely lonely, infinitely agonised, in some illimitable region of never-ending pain. There was no past, no future, only Eternity and his undying soul in anguish. The thousand years were as a day, and the day as a thousand years. There was no beginning and no end. O God, no end! He did not hear the crowd stir a little, and drift hither and thither as it was pressed upon from one side; he did not see the gendarmes press against the drift, only to be driven back again, hustled, surrounded so that their horses were too hampered to answer to the spur. Suddenly a woman went down screaming under the horses' feet, and on the instant the crowd flamed into fury before the agonised shriek had died away. In a moment all was a seething, shouting, cursing welter of struggling humanity. The noise of it reached even Dangeau's stunned brain, and he said within himself, "It is over. She is dead," and opened his eyes. The scaffold stood like an island in a sea grown suddenly wild with tempest, and even as he looked, the human waves of it broke in a fierce swirl which welled up and overflowed it on every side. Sanson, his hand on the machinery, was whirled aside, jostled, pushed, cursed. A fat woman, with bare, mottled arms, Heaven knows how she came on the platform, dealt him a resounding smack on the face, and shrieked voluble abuse, which was freely echoed. Dangeau was surrounded, embraced, cheered, lifted off his feet, the cord that bound his arms slashed through, and of a sudden
Goyot had him round the neck, and he found voice and clamoured Aline's name. The little surgeon, after one glance at his wild eyes, pushed with him through the surging press; they had to fight their way, and the place was slippery, but they were through at last, through and down on their knees by the woman who lay bound beneath the knife that Sanson's hand was freeing when the tumult caught him. A dozen hands snatched her back again now, the cords were cut, and Dangeau's shaking voice called in her ears, called loudly, and in vain. "Air, give her air and room," he cried, and some pushed forwards and others back. The fat woman took the girl's head upon her lap, whilst tears rained down her crimson cheeks. "Eh, the poor pretty one," she sobbed hysterically, and pulled off her own ample kerchief to cover Aline's thin bosom. Dangeau leaned over her calling, calling still, unaware of Goyot at his side, and of Goyot's voice saying insistently, "Tiens, my friend, that was a near shave, eh?" "My wife," he muttered, "my wife—my wife is dead," and with that he gazed round wildly, cried "No, no!" in a sharp voice, and fell to calling her again. Goyot knelt on the reeking boards, caught the frail wrist in that brown skilful hand of his, shifted his grasp once, twice, a third time, shook his head, and took another grip. "No, she 's alive," he said at last, and had to say it more than once, for Dangeau took no heed. "Aline! Aline! Aline!" he called in hoarse, trembling tones, and Goyot dropped the girl's wrist and took him harshly by the shoulder. "Rouse, man, rouse!" he cried. "She's alive. I tell you. I swear it. For the love of Heaven, wake up, and help me to get her away. It's
touch and go for all of us these next few hours. At any moment Henriot may have the upper hand, and half an hour would do our business, with this pretty toy so handy." He grimaced at the red axe above them, "Come, Dangeau, play the man!" Dangeau stared at him. "What am I to do?" he asked irritably. Goyot pressed his shoulder with a firm hand. "Lift your wife, and bring her along after me. Can you manage? She looks light enough." It was no easy matter to come through the excited crowd, but Dangeau's height told, and with Aline's head against his shoulder he pushed doggedly in the wake of Goyot, who made his way through the press with a wonderful agility. Down the steps now, and inch by inch forward through the jostling excited people. Up a by way at last, and then sharp to the left where a carriage waited, and with that Goyot gave a gasp of relief, and mopped a dripping brow. "Eh, mon Dieu!" he said; "get in, get in!" The carriage had mouldy straw on the floor, and the musty odour of it mounted in the hot air. Dangeau complained of it sharply. "A devil of a smell, this, Goyot!" and the little surgeon fixed him with keen, watchful eyes, as he nodded acquiescence. What house they came to, or how they came to it, Dangeau knew no more than his unconscious wife. She lay across his breast, white and still as the dead, and when he laid her down on the bed in the upper room they reached at last, she fell limply from his grasp, and he turned to Goyot with a groan.
A soft, white-haired woman, dark-eyed and placid,—afterwards he knew her for Goyot's housekeeper,—tried to turn him out of the room, but he would go no farther than the window, where he sat staring, staring at the houses across the way, watching them darken in the gathering dusk, and mechanically counting the lights that presently sprang into view. Behind him Marie Carlier came and went, at Goyot's shortly worded orders, until at last Dangeau's straining ears caught the sound of a faint, fluttering sigh. He turned then, the lights in the room dancing before his burning eyes. For a moment the room seemed full of the small tongues of flame, and then beyond them he saw his wife's eyes open again, whilst her hand moved in feeble protest against the draught which Goyot himself was holding to her lips. Dangeau got up, stood a moment gazing, and then stumbled from the room and broke into heavy sobbing. Presently Goyot brought him something in a glass, which he drank obediently. "Now you will sleep," said the little man in cheerful accents, and sleep he did, and never stirred until the high sun struck across his face and waked him to France's new day, and his. For in that night fell Robespierre, cast down by the Convention he had dominated so long. The dawn that found him shattered, praying for the death he had vainly sought, awakened Paris from the long nightmare which had been the marriage gift of her nuptials with this incubus. At four o'clock on the afternoon of the 10th Thermidor, Robespierre's head fell under the bloody axe of the Terror, and with
his last gasp the life went out of the greatest tyranny of modern times. When Goyot came home with the news, Dangeau's face flamed, and he put his hand before his eyes for a moment. Then he went up to Aline. She had lain in a deep sleep for many, many hours, but towards the afternoon she had wakened, taken food, and dressed herself, all in a strange, mechanical fashion. She was neither to be gainsaid nor persuaded, and Dangeau, reasonable once more, had left her to the kind and unexciting ministrations of Marie Carlier. Now he could keep away no longer; Goyot followed him and the housekeeper met them by the door. "She is strange, Monsieur," she whispered. "She has not roused at all?" inquired Goyot rather anxiously. Marie shook her head. "She just sits and stares at the sky. God knows what she sees there, poor lamb. If she would weep——" "Just so, just so," Goyot nodded once or twice. Then he turned a penetrating look on Dangeau. "Ha, you are all right again. A near thing, my friend, eh? Small wonder you were upset by it." "Oh, I!" said Dangeau, with an impatient gesture. "It is my wife we are speaking of." "Yes, yes, of course—a little patience, my dear Dangeau—yes, your wife. Marie here, without being scientific, is a sensible woman, and it's a wonderful thing how common-sense comes to the same conclusions as science. A fascinating subject that, but, as you are about to observe, this is not the time to pursue it. What I mean to say is, that your wife is suffering from severe shock; her brain is
overcharged, and Marie is quite right when she suggests that tears would relieve it. Now, my good Dangeau, do you think you can make your wife cry?" "I don't know—I must go to her." "Well, well, go. Don't excite her, but—dear me, Marie, how impatient people are. When one has saved a man's life, he might at least let one finish a sentence, instead of breaking away in the middle of it. Get me something to eat, for, parbleu, I 've earned it." Dangeau had closed the door, and stood looking at his wife. "Aline," he said, "have they told you? We are safe—Robespierre is dead." Then he threw back his head, took a long, deep breath, and cried: "It is new life—new life for France, new work for those who love her—new life for us—for us, Aline." Aline stood by the window, very still. At the sound of Dangeau's voice she turned her head. He saw that she was smiling, and his heart contracted as he looked at her. Death had come so close to her, so very close, that it seemed to him the shadow of it lay cold and still above that strange unchanging smile; and he called to her abruptly, with a rough tenderness. "Aline! Aline!" She looked up then, and he saw then the same smile lie deep within her eyes. Unfathomably peaceful they were, but not with the peace of the living. "Won't you come to me, my dear," he said gently, and with the simplicity he would have used to a child.
A little shiver just stirred the stillness of her form, and she came slowly, very slowly, across the room, and then stood waiting, and with a sudden passion Dangeau laid both hands upon her shoulders insistently, heavily. He wondered had she lost the memory of the last time he had touched and held her thus. Then he had fought with pride and been defeated. Now he must fight again, fight for her very soul and reason, and this time he must win, or the whole world would be lost. He paused, gathering all the forces of his soul, then looked at her with passionate uneasiness. If she would tremble, if she would even shrink from him— anything but that calm which was there, and shone serenely fixed, like the smile upon the faces of the dead. It hinted of the final secret known. "Mon Dieu! Aline, don't look like that!" he cried, and in strong protest his arms slipped lower, and drew her close to his heart that beat, and beat, as if it would supply the life hers lacked. She came passively at his touch, and stood in his embrace unresisting and unresponsive. Remembering how she had flushed at a look and quivered at a touch, his fears redoubled, and he caught her close, and closer, kissing her, at first gently, but in the end with all the force of a passion so long restrained. For now at last the dam was down, and they stood together in love's full flowing tide. When he drew back, the smile was gone, and the lips that it had left trembled piteously, as her colour came and went to each quickened breath.
"Aline," he said, very low, "Aline, my heart! It is new life—new life together." She pushed him back a pace then, and raised her eyes with a look he never forgot. The peace had left them now, and they were troubled to the depths, and brimmed with tears. Her lips quivered more and more, the breath came from them in a great sob, and suddenly she fell upon his breast in a passion of weeping.
THE END *** END OF THIS PROJECT GUTENBERG EBOOK A MARRIAGE UNDER THE TERROR ***
*** END OF THE PROJECT GUTENBERG EBOOK A MARRIAGE UNDER THE TERROR *** Updated editions will replace the previous one—the old editions will be renamed. Creating the works from print editions not protected by U.S. copyright law means that no one owns a United States copyright in these works, so the Foundation (and you!) can copy and distribute it in the United States without permission and without paying copyright royalties. Special rules, set forth in the General Terms of Use part of this license, apply to copying and distributing Project Gutenberg™ electronic works to protect the PROJECT GUTENBERG™ concept and trademark. Project Gutenberg is a registered trademark, and may not be used if you charge for an eBook, except by following the terms of the trademark license, including paying royalties for use of the Project Gutenberg trademark. If you do not charge anything for copies of this eBook, complying with the trademark license is very easy. You may use this eBook for nearly any purpose such as creation of derivative works, reports, performances and research. Project Gutenberg eBooks may be modified and printed and given away—you may do practically ANYTHING in the United States with eBooks not protected by U.S. copyright law. Redistribution is subject to the trademark license, especially commercial redistribution. START: FULL LICENSE
THE FULL PROJECT GUTENBERG LICENSE
PLEASE READ THIS BEFORE YOU DISTRIBUTE OR USE THIS WORK To protect the Project Gutenberg™ mission of promoting the free distribution of electronic works, by using or distributing this work (or any other work associated in any way with the phrase “Project Gutenberg”), you agree to comply with all the terms of the Full Project Gutenberg™ License available with this file or online at www.gutenberg.org/license. Section 1. General Terms of Use and Redistributing Project Gutenberg™ electronic works 1.A. By reading or using any part of this Project Gutenberg™ electronic work, you indicate that you have read, understand, agree to and accept all the terms of this license and intellectual property (trademark/copyright) agreement. If you do not agree to abide by all the terms of this agreement, you must cease using and return or destroy all copies of Project Gutenberg™ electronic works in your possession. If you paid a fee for obtaining a copy of or access to a Project Gutenberg™ electronic work and you do not agree to be bound by the terms of this agreement, you may obtain a refund from the person or entity to whom you paid the fee as set forth in paragraph 1.E.8. 1.B. “Project Gutenberg” is a registered trademark. It may only be used on or associated in any way with an electronic work by people who agree to be bound by the terms of this agreement. There are a few things that you can do with most Project Gutenberg™ electronic works even without complying with the full terms of this agreement. See paragraph 1.C below. There are a lot of things you can do with Project Gutenberg™ electronic works if you follow the terms of this agreement and help preserve free future access to Project Gutenberg™ electronic works. See paragraph 1.E below.
1.C. The Project Gutenberg Literary Archive Foundation (“the Foundation” or PGLAF), owns a compilation copyright in the collection of Project Gutenberg™ electronic works. Nearly all the individual works in the collection are in the public domain in the United States. If an individual work is unprotected by copyright law in the United States and you are located in the United States, we do not claim a right to prevent you from copying, distributing, performing, displaying or creating derivative works based on the work as long as all references to Project Gutenberg are removed. Of course, we hope that you will support the Project Gutenberg™ mission of promoting free access to electronic works by freely sharing Project Gutenberg™ works in compliance with the terms of this agreement for keeping the Project Gutenberg™ name associated with the work. You can easily comply with the terms of this agreement by keeping this work in the same format with its attached full Project Gutenberg™ License when you share it without charge with others. 1.D. The copyright laws of the place where you are located also govern what you can do with this work. Copyright laws in most countries are in a constant state of change. If you are outside the United States, check the laws of your country in addition to the terms of this agreement before downloading, copying, displaying, performing, distributing or creating derivative works based on this work or any other Project Gutenberg™ work. The Foundation makes no representations concerning the copyright status of any work in any country other than the United States. 1.E. Unless you have removed all references to Project Gutenberg: 1.E.1. The following sentence, with active links to, or other immediate access to, the full Project Gutenberg™ License must appear prominently whenever any copy of a Project Gutenberg™ work (any work on which the phrase “Project
Gutenberg” appears, or with which the phrase “Project Gutenberg” is associated) is accessed, displayed, performed, viewed, copied or distributed: This eBook is for the use of anyone anywhere in the United States and most other parts of the world at no cost and with almost no restrictions whatsoever. You may copy it, give it away or re- use it under the terms of the Project Gutenberg License included with this eBook or online at www.gutenberg.org. If you are not located in the United States, you will have to check the laws of the country where you are located before using this eBook. 1.E.2. If an individual Project Gutenberg™ electronic work is derived from texts not protected by U.S. copyright law (does not contain a notice indicating that it is posted with permission of the copyright holder), the work can be copied and distributed to anyone in the United States without paying any fees or charges. If you are redistributing or providing access to a work with the phrase “Project Gutenberg” associated with or appearing on the work, you must comply either with the requirements of paragraphs 1.E.1 through 1.E.7 or obtain permission for the use of the work and the Project Gutenberg™ trademark as set forth in paragraphs 1.E.8 or 1.E.9. 1.E.3. If an individual Project Gutenberg™ electronic work is posted with the permission of the copyright holder, your use and distribution must comply with both paragraphs 1.E.1 through 1.E.7 and any additional terms imposed by the copyright holder. Additional terms will be linked to the Project Gutenberg™ License for all works posted with the permission of the copyright holder found at the beginning of this work.
1.E.4. Do not unlink or detach or remove the full Project Gutenberg™ License terms from this work, or any files containing a part of this work or any other work associated with Project Gutenberg™. 1.E.5. Do not copy, display, perform, distribute or redistribute this electronic work, or any part of this electronic work, without prominently displaying the sentence set forth in paragraph 1.E.1 with active links or immediate access to the full terms of the Project Gutenberg™ License. 1.E.6. You may convert to and distribute this work in any binary, compressed, marked up, nonproprietary or proprietary form, including any word processing or hypertext form. However, if you provide access to or distribute copies of a Project Gutenberg™ work in a format other than “Plain Vanilla ASCII” or other format used in the official version posted on the official Project Gutenberg™ website (www.gutenberg.org), you must, at no additional cost, fee or expense to the user, provide a copy, a means of exporting a copy, or a means of obtaining a copy upon request, of the work in its original “Plain Vanilla ASCII” or other form. Any alternate format must include the full Project Gutenberg™ License as specified in paragraph 1.E.1. 1.E.7. Do not charge a fee for access to, viewing, displaying, performing, copying or distributing any Project Gutenberg™ works unless you comply with paragraph 1.E.8 or 1.E.9. 1.E.8. You may charge a reasonable fee for copies of or providing access to or distributing Project Gutenberg™ electronic works provided that: • You pay a royalty fee of 20% of the gross profits you derive from the use of Project Gutenberg™ works calculated using the method you already use to calculate your applicable taxes. The fee is owed to the owner of the Project Gutenberg™ trademark,
but he has agreed to donate royalties under this paragraph to the Project Gutenberg Literary Archive Foundation. Royalty payments must be paid within 60 days following each date on which you prepare (or are legally required to prepare) your periodic tax returns. Royalty payments should be clearly marked as such and sent to the Project Gutenberg Literary Archive Foundation at the address specified in Section 4, “Information about donations to the Project Gutenberg Literary Archive Foundation.” • You provide a full refund of any money paid by a user who notifies you in writing (or by e-mail) within 30 days of receipt that s/he does not agree to the terms of the full Project Gutenberg™ License. You must require such a user to return or destroy all copies of the works possessed in a physical medium and discontinue all use of and all access to other copies of Project Gutenberg™ works. • You provide, in accordance with paragraph 1.F.3, a full refund of any money paid for a work or a replacement copy, if a defect in the electronic work is discovered and reported to you within 90 days of receipt of the work. • You comply with all other terms of this agreement for free distribution of Project Gutenberg™ works. 1.E.9. If you wish to charge a fee or distribute a Project Gutenberg™ electronic work or group of works on different terms than are set forth in this agreement, you must obtain permission in writing from the Project Gutenberg Literary Archive Foundation, the manager of the Project Gutenberg™ trademark. Contact the Foundation as set forth in Section 3 below. 1.F.
1.F.1. Project Gutenberg volunteers and employees expend considerable effort to identify, do copyright research on, transcribe and proofread works not protected by U.S. copyright law in creating the Project Gutenberg™ collection. Despite these efforts, Project Gutenberg™ electronic works, and the medium on which they may be stored, may contain “Defects,” such as, but not limited to, incomplete, inaccurate or corrupt data, transcription errors, a copyright or other intellectual property infringement, a defective or damaged disk or other medium, a computer virus, or computer codes that damage or cannot be read by your equipment. 1.F.2. LIMITED WARRANTY, DISCLAIMER OF DAMAGES - Except for the “Right of Replacement or Refund” described in paragraph 1.F.3, the Project Gutenberg Literary Archive Foundation, the owner of the Project Gutenberg™ trademark, and any other party distributing a Project Gutenberg™ electronic work under this agreement, disclaim all liability to you for damages, costs and expenses, including legal fees. YOU AGREE THAT YOU HAVE NO REMEDIES FOR NEGLIGENCE, STRICT LIABILITY, BREACH OF WARRANTY OR BREACH OF CONTRACT EXCEPT THOSE PROVIDED IN PARAGRAPH 1.F.3. YOU AGREE THAT THE FOUNDATION, THE TRADEMARK OWNER, AND ANY DISTRIBUTOR UNDER THIS AGREEMENT WILL NOT BE LIABLE TO YOU FOR ACTUAL, DIRECT, INDIRECT, CONSEQUENTIAL, PUNITIVE OR INCIDENTAL DAMAGES EVEN IF YOU GIVE NOTICE OF THE POSSIBILITY OF SUCH DAMAGE. 1.F.3. LIMITED RIGHT OF REPLACEMENT OR REFUND - If you discover a defect in this electronic work within 90 days of receiving it, you can receive a refund of the money (if any) you paid for it by sending a written explanation to the person you received the work from. If you received the work on a physical medium, you must return the medium with your written explanation. The person or entity that provided you with the defective work may elect to provide a replacement copy in lieu
of a refund. If you received the work electronically, the person or entity providing it to you may choose to give you a second opportunity to receive the work electronically in lieu of a refund. If the second copy is also defective, you may demand a refund in writing without further opportunities to fix the problem. 1.F.4. Except for the limited right of replacement or refund set forth in paragraph 1.F.3, this work is provided to you ‘AS-IS’, WITH NO OTHER WARRANTIES OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO WARRANTIES OF MERCHANTABILITY OR FITNESS FOR ANY PURPOSE. 1.F.5. Some states do not allow disclaimers of certain implied warranties or the exclusion or limitation of certain types of damages. If any disclaimer or limitation set forth in this agreement violates the law of the state applicable to this agreement, the agreement shall be interpreted to make the maximum disclaimer or limitation permitted by the applicable state law. The invalidity or unenforceability of any provision of this agreement shall not void the remaining provisions. 1.F.6. INDEMNITY - You agree to indemnify and hold the Foundation, the trademark owner, any agent or employee of the Foundation, anyone providing copies of Project Gutenberg™ electronic works in accordance with this agreement, and any volunteers associated with the production, promotion and distribution of Project Gutenberg™ electronic works, harmless from all liability, costs and expenses, including legal fees, that arise directly or indirectly from any of the following which you do or cause to occur: (a) distribution of this or any Project Gutenberg™ work, (b) alteration, modification, or additions or deletions to any Project Gutenberg™ work, and (c) any Defect you cause.
Section 2. Information about the Mission of Project Gutenberg™ Project Gutenberg™ is synonymous with the free distribution of electronic works in formats readable by the widest variety of computers including obsolete, old, middle-aged and new computers. It exists because of the efforts of hundreds of volunteers and donations from people in all walks of life. Volunteers and financial support to provide volunteers with the assistance they need are critical to reaching Project Gutenberg™’s goals and ensuring that the Project Gutenberg™ collection will remain freely available for generations to come. In 2001, the Project Gutenberg Literary Archive Foundation was created to provide a secure and permanent future for Project Gutenberg™ and future generations. To learn more about the Project Gutenberg Literary Archive Foundation and how your efforts and donations can help, see Sections 3 and 4 and the Foundation information page at www.gutenberg.org. Section 3. Information about the Project Gutenberg Literary Archive Foundation The Project Gutenberg Literary Archive Foundation is a non- profit 501(c)(3) educational corporation organized under the laws of the state of Mississippi and granted tax exempt status by the Internal Revenue Service. The Foundation’s EIN or federal tax identification number is 64-6221541. Contributions to the Project Gutenberg Literary Archive Foundation are tax deductible to the full extent permitted by U.S. federal laws and your state’s laws. The Foundation’s business office is located at 809 North 1500 West, Salt Lake City, UT 84116, (801) 596-1887. Email contact
links and up to date contact information can be found at the Foundation’s website and official page at www.gutenberg.org/contact Section 4. Information about Donations to the Project Gutenberg Literary Archive Foundation Project Gutenberg™ depends upon and cannot survive without widespread public support and donations to carry out its mission of increasing the number of public domain and licensed works that can be freely distributed in machine-readable form accessible by the widest array of equipment including outdated equipment. Many small donations ($1 to $5,000) are particularly important to maintaining tax exempt status with the IRS. The Foundation is committed to complying with the laws regulating charities and charitable donations in all 50 states of the United States. Compliance requirements are not uniform and it takes a considerable effort, much paperwork and many fees to meet and keep up with these requirements. We do not solicit donations in locations where we have not received written confirmation of compliance. To SEND DONATIONS or determine the status of compliance for any particular state visit www.gutenberg.org/donate. While we cannot and do not solicit contributions from states where we have not met the solicitation requirements, we know of no prohibition against accepting unsolicited donations from donors in such states who approach us with offers to donate. International donations are gratefully accepted, but we cannot make any statements concerning tax treatment of donations received from outside the United States. U.S. laws alone swamp our small staff.
Please check the Project Gutenberg web pages for current donation methods and addresses. Donations are accepted in a number of other ways including checks, online payments and credit card donations. To donate, please visit: www.gutenberg.org/donate. Section 5. General Information About Project Gutenberg™ electronic works Professor Michael S. Hart was the originator of the Project Gutenberg™ concept of a library of electronic works that could be freely shared with anyone. For forty years, he produced and distributed Project Gutenberg™ eBooks with only a loose network of volunteer support. Project Gutenberg™ eBooks are often created from several printed editions, all of which are confirmed as not protected by copyright in the U.S. unless a copyright notice is included. Thus, we do not necessarily keep eBooks in compliance with any particular paper edition. Most people start at our website which has the main PG search facility: www.gutenberg.org. This website includes information about Project Gutenberg™, including how to make donations to the Project Gutenberg Literary Archive Foundation, how to help produce our new eBooks, and how to subscribe to our email newsletter to hear about new eBooks.
Welcome to our website – the perfect destination for book lovers and knowledge seekers. We believe that every book holds a new world, offering opportunities for learning, discovery, and personal growth. That’s why we are dedicated to bringing you a diverse collection of books, ranging from classic literature and specialized publications to self-development guides and children's books. More than just a book-buying platform, we strive to be a bridge connecting you with timeless cultural and intellectual values. With an elegant, user-friendly interface and a smart search system, you can quickly find the books that best suit your interests. Additionally, our special promotions and home delivery services help you save time and fully enjoy the joy of reading. Join us on a journey of knowledge exploration, passion nurturing, and personal growth every day! ebookbell.com

More Related Content

PDF
Db2 deployment guide
bybupbechanhgmail
 
PDF
DB2 Integrated cluster enviorment deployement
byssuser6df8c1
 
PDF
Ibm Informix Integration Through Data Federation Ibm Redbooks
byzlimagabca
 
PDF
Integrating ibm db2 with the ibm system storage n series sg247329
byBanking at Ho Chi Minh city
 
PDF
Integrating ibm db2 with the ibm system storage n series sg247329
byBanking at Ho Chi Minh city
 
PDF
RDB Synchronization, Transcoding and LDAP Directory Services ...
byVideoguy
 
PDF
BOOK - IBM Z vse using db2 on linux for system z
bySatya Harish
 
PDF
Ibm tivoli monitoring for network performance v2.1 the mainframe network mana...
byBanking at Ho Chi Minh city
 
Db2 deployment guide
bybupbechanhgmail
 
DB2 Integrated cluster enviorment deployement
byssuser6df8c1
 
Ibm Informix Integration Through Data Federation Ibm Redbooks
byzlimagabca
 
Integrating ibm db2 with the ibm system storage n series sg247329
byBanking at Ho Chi Minh city
 
Integrating ibm db2 with the ibm system storage n series sg247329
byBanking at Ho Chi Minh city
 
RDB Synchronization, Transcoding and LDAP Directory Services ...
byVideoguy
 
BOOK - IBM Z vse using db2 on linux for system z
bySatya Harish
 
Ibm tivoli monitoring for network performance v2.1 the mainframe network mana...
byBanking at Ho Chi Minh city
 

Similar to Mysql To Db2 Conversion Guide Ibm Redbooks

PDF
Db2 udb backup and recovery with ess copy services
bybupbechanhgmail
 
PDF
Expert oracle database architecture
byairy6548
 
PDF
Deployment guide series ibm total storage productivity center for data sg247140
byBanking at Ho Chi Minh city
 
PDF
Essbase database administrator's guide
byChanukya Mekala
 
PDF
Progress OpenEdge database administration guide and reference
byVinh Nguyen
 
PDF
Performance tuning for content manager sg246949
byBanking at Ho Chi Minh city
 
PDF
Deployment guide series ibm tivoli usage and accounting manager v7.1 sg247569
byBanking at Ho Chi Minh city
 
PDF
Developers Guide To Multiplayer Games Wordware Game Developers Library Andrew...
byeltnuyl6977
 
PDF
Ibm tivoli web access for information management sg246823
byBanking at Ho Chi Minh city
 
PDF
Jdbc
byMeenakshi Chandrasekaran
 
PDF
Administering maximo asset management
byKhaled Saleh
 
PDF
IBMRedbook
byNeelamegan Minor
 
PDF
DB2 10 for Linux on System z Using z/VM v6.2, Single System Image Clusters an...
byIBM India Smarter Computing
 
PDF
Tivoli business systems manager v2.1 end to-end business impact management sg...
byBanking at Ho Chi Minh city
 
PDF
Ibm tivoli usage accounting manager v7.1 handbook sg247404
byBanking at Ho Chi Minh city
 
PDF
Certification guide series ibm tivoli usage and accounting manager v7.1 imple...
byBanking at Ho Chi Minh city
 
PDF
Implementing tivoli data warehouse v 1.2 sg247100
byBanking at Ho Chi Minh city
 
PDF
Db2 v9 admin guide z os
byLeo Goicochea
 
PDF
Introducing tivoli personalized services manager 1.1 sg246031
byBanking at Ho Chi Minh city
 
PDF
BOOK - IBM Implementing ibm system directory 6.1
bySatya Harish
 
Db2 udb backup and recovery with ess copy services
bybupbechanhgmail
 
Expert oracle database architecture
byairy6548
 
Deployment guide series ibm total storage productivity center for data sg247140
byBanking at Ho Chi Minh city
 
Essbase database administrator's guide
byChanukya Mekala
 
Progress OpenEdge database administration guide and reference
byVinh Nguyen
 
Performance tuning for content manager sg246949
byBanking at Ho Chi Minh city
 
Deployment guide series ibm tivoli usage and accounting manager v7.1 sg247569
byBanking at Ho Chi Minh city
 
Developers Guide To Multiplayer Games Wordware Game Developers Library Andrew...
byeltnuyl6977
 
Ibm tivoli web access for information management sg246823
byBanking at Ho Chi Minh city
 
Jdbc
byMeenakshi Chandrasekaran
 
Administering maximo asset management
byKhaled Saleh
 
IBMRedbook
byNeelamegan Minor
 
DB2 10 for Linux on System z Using z/VM v6.2, Single System Image Clusters an...
byIBM India Smarter Computing
 
Tivoli business systems manager v2.1 end to-end business impact management sg...
byBanking at Ho Chi Minh city
 
Ibm tivoli usage accounting manager v7.1 handbook sg247404
byBanking at Ho Chi Minh city
 
Certification guide series ibm tivoli usage and accounting manager v7.1 imple...
byBanking at Ho Chi Minh city
 
Implementing tivoli data warehouse v 1.2 sg247100
byBanking at Ho Chi Minh city
 
Db2 v9 admin guide z os
byLeo Goicochea
 
Introducing tivoli personalized services manager 1.1 sg246031
byBanking at Ho Chi Minh city
 
BOOK - IBM Implementing ibm system directory 6.1
bySatya Harish
 

Recently uploaded

PDF
Selection in Genetics and Plant Breeding | Complete Concept Explained 🌾
bySatyam Sharma
 
PPTX
How to Create Event Booth in Odoo 18 Event
byCeline George
 
PPTX
GROWTH AND DEVELOPMENT..............pptx
byAneetaSharma15
 
PPTX
Upselling and crosselling in Odoo 19 pos
byCeline George
 
PPTX
ANKYLOSING SPONDYLITIS OR BAMBOO SPINE.pptx
byDrNidhiAgarwal
 
PPTX
business economics unit 3 production dr kanchan (1).pptx
byDr. Kanchan Kumari
 
PDF
FA Term 2 Reiki Yoga All Student Workshop 22
byLDMMIA, Reiki Yoga
 
PPTX
Banking Company Accounts in Corporate Accounting .pptx
byDGayathiry
 
PPTX
Targeted Drug Delivery System – Liposomes, Nanoparticles, Niosomes & Monoclon...
byseemashinde5197
 
PDF
GIÁO ÁN KẾ HOẠCH BÀI DẠY TIẾNG ANH GLOBAL SUCCESS LỚP 12 (CẢ NĂM) THEO CÔNG V...
byNguyen Thanh Tu Collection
 
PDF
Plant Domestication | Complete Concept Explained BREEDING PLANT DOMESTICATION
bySatyam Sharma
 
PDF
BÀI GIẢNG POWERPOINT CHÍNH KHÓA PHIÊN BẢN AI TIẾNG ANH 9 CẢ NĂM, BÁM SÁT SÁCH...
byNguyen Thanh Tu Collection
 
PDF
Artificial Intelligence in Education: Transforming Teaching and Learning for ...
byNavid Nazhand
 
PPTX
Nasopulmonary drug delivery system Pradip Sontakke
byDrxpradipPatil
 
PDF
PHARMACOPOEIAS: PHARMACEUTICS: B PHARMACY: SEMESTER I : BY MR. SAKHARE R. S.
byMr. SAKHARE R. S.
 
PPTX
Becoming a Senior Student Week 4 Term 4 10CLA.pptx
bymansk2
 
PDF
Creating Motion Graphics and Videos with Generative AI
byShalin Hai-Jew
 
PDF
PTE Answer Short Questions_ 50+ Most Repeated Questions & Smart Tips to Score...
byGurully
 
PPTX
Presentation1jdhdhhhjjjdbbdnjenmmen.pptx
bypgiopration
 
PDF
Get to know your EEG and MEG data - artifacts, segmenting, preprocessing
byRobert Oostenveld
 
Selection in Genetics and Plant Breeding | Complete Concept Explained 🌾
bySatyam Sharma
 
How to Create Event Booth in Odoo 18 Event
byCeline George
 
GROWTH AND DEVELOPMENT..............pptx
byAneetaSharma15
 
Upselling and crosselling in Odoo 19 pos
byCeline George
 
ANKYLOSING SPONDYLITIS OR BAMBOO SPINE.pptx
byDrNidhiAgarwal
 
business economics unit 3 production dr kanchan (1).pptx
byDr. Kanchan Kumari
 
FA Term 2 Reiki Yoga All Student Workshop 22
byLDMMIA, Reiki Yoga
 
Banking Company Accounts in Corporate Accounting .pptx
byDGayathiry
 
Targeted Drug Delivery System – Liposomes, Nanoparticles, Niosomes & Monoclon...
byseemashinde5197
 
GIÁO ÁN KẾ HOẠCH BÀI DẠY TIẾNG ANH GLOBAL SUCCESS LỚP 12 (CẢ NĂM) THEO CÔNG V...
byNguyen Thanh Tu Collection
 
Plant Domestication | Complete Concept Explained BREEDING PLANT DOMESTICATION
bySatyam Sharma
 
BÀI GIẢNG POWERPOINT CHÍNH KHÓA PHIÊN BẢN AI TIẾNG ANH 9 CẢ NĂM, BÁM SÁT SÁCH...
byNguyen Thanh Tu Collection
 
Artificial Intelligence in Education: Transforming Teaching and Learning for ...
byNavid Nazhand
 
Nasopulmonary drug delivery system Pradip Sontakke
byDrxpradipPatil
 
PHARMACOPOEIAS: PHARMACEUTICS: B PHARMACY: SEMESTER I : BY MR. SAKHARE R. S.
byMr. SAKHARE R. S.
 
Becoming a Senior Student Week 4 Term 4 10CLA.pptx
bymansk2
 
Creating Motion Graphics and Videos with Generative AI
byShalin Hai-Jew
 
PTE Answer Short Questions_ 50+ Most Repeated Questions & Smart Tips to Score...
byGurully
 
Presentation1jdhdhhhjjjdbbdnjenmmen.pptx
bypgiopration
 
Get to know your EEG and MEG data - artifacts, segmenting, preprocessing
byRobert Oostenveld
 

Mysql To Db2 Conversion Guide Ibm Redbooks

  • 1.
    Mysql To Db2Conversion Guide Ibm Redbooks download https://ebookbell.com/product/mysql-to-db2-conversion-guide-ibm- redbooks-1764638 Explore and download more ebooks at ebookbell.com
  • 2.
    Here are somerecommended products that we believe you will be interested in. You can click the link to download. An Instruction To Html Css Php And Mysql To Create Datadriven Web Sites Hutchinson https://ebookbell.com/product/an-instruction-to-html-css-php-and- mysql-to-create-datadriven-web-sites-hutchinson-232133692 Mysql Quick Guide Comprehensive Guide To Mysql Database Integration And Beyond Jackson https://ebookbell.com/product/mysql-quick-guide-comprehensive-guide- to-mysql-database-integration-and-beyond-jackson-55939374 Php Mysql Novice To Ninja Kevin Yank https://ebookbell.com/product/php-mysql-novice-to-ninja-kevin- yank-46096152 Php Mysql Novice To Ninja 7th Edition Tom Butler https://ebookbell.com/product/php-mysql-novice-to-ninja-7th-edition- tom-butler-42448612
  • 3.
    Php Mysql NoviceTo Ninja 7th Edition Tom Butler https://ebookbell.com/product/php-mysql-novice-to-ninja-7th-edition- tom-butler-42542622 Php Mysql Novice To Ninja Get Up To Speed With Php The Easy Way 6th Edition Tom Butler https://ebookbell.com/product/php-mysql-novice-to-ninja-get-up-to- speed-with-php-the-easy-way-6th-edition-tom-butler-10380008 Php Mysql Novice To Ninja Kevin Yank https://ebookbell.com/product/php-mysql-novice-to-ninja-kevin- yank-43035208 Php Mysql Novice To Ninja 7th Edition 7th Edition Tom Butler https://ebookbell.com/product/php-mysql-novice-to-ninja-7th- edition-7th-edition-tom-butler-42451910 Php Mysql Novice To Ninja Kevin Yank https://ebookbell.com/product/php-mysql-novice-to-ninja-kevin- yank-42926306
  • 5.
    ibm.com/redbooks MySQL to DB2 ConversionGuide Whei-Jen Chen Angela Carlson Guides you through a MySQL database and application conversion to DB2 Enriches applications through advanced DB2 features Converts an application with detailed examples Front cover
  • 7.
    MySQL to DB2Conversion Guide December 2009 International Technical Support Organization SG24-7093-01
  • 8.
    © Copyright InternationalBusiness Machines Corporation 2009. All rights reserved. Note to U.S. Government Users Restricted Rights -- Use, duplication or disclosure restricted by GSA ADP Schedule Contract with IBM Corp. Second Edition (December 2009) This edition applies to DB2 9.7 for Linux, UNIX, and Windows and MySQL 5.1. Note: Before using this information and the product it supports, read the information in “Notices” on page ix.
  • 9.
    © Copyright IBMCorp. 2009. All rights reserved. iii Contents Notices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .ix Trademarks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . x Preface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xiii The team who wrote this book . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xiii Become a published author . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xiv Comments welcome. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xv Summary of changes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvii December 2009, Second Edition . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvii Executive summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xix Chapter 1. DB2 for Linux, UNIX, and Windows. . . . . . . . . . . . . . . . . . . . . . . 1 1.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2 1.2 Product overview. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 1.2.1 DB2 Data Server Editions for the production environment . . . . . . . . . 3 1.2.2 Products for accessing System z and System i host data . . . . . . . . . 7 1.2.3 DB2 for pervasive platforms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8 1.2.4 Additional DB2 data server features . . . . . . . . . . . . . . . . . . . . . . . . . . 8 1.3 DB2 for Linux, UNIX, and Windows architecture. . . . . . . . . . . . . . . . . . . . . 9 1.3.1 DB2 9.7 threaded architecture and process model. . . . . . . . . . . . . . . 9 1.3.2 DB2 database objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10 1.3.3 DB2 catalog. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18 1.4 DB2 utilities . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18 1.5 DB2 database access . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21 1.5.1 DB2 clients and drivers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21 1.5.2 Application access . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25 1.5.3 DB2 application programming interfaces . . . . . . . . . . . . . . . . . . . . . 26 Chapter 2. MySQL database. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35 2.1 MySQL licensing overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36 2.2 MySQL architecture overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36 2.2.1 Database client and non-client utilities . . . . . . . . . . . . . . . . . . . . . . . 37 2.2.2 Database server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38 2.3 MySQL design and SQL compliance . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41 2.3.1 MySQL directory structure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41 2.3.2 MySQL storage engines . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45 2.3.3 MySQL standard SQL compliance . . . . . . . . . . . . . . . . . . . . . . . . . . 48
  • 10.
    iv MySQL toDB2 Conversion Guide 2.4 MySQL utilities . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49 2.4.1 Overview of the MySQL server-side programs and utilities . . . . . . . 50 2.4.2 Overview of the MySQL client-side programs and utilities . . . . . . . . 50 2.5 MySQL application programming interfaces . . . . . . . . . . . . . . . . . . . . . . . 51 Chapter 3. Planning the conversion from MySQL to DB2 . . . . . . . . . . . . . 55 3.1 Conversion project planning overview. . . . . . . . . . . . . . . . . . . . . . . . . . . . 56 3.1.1 Benefits of converting to DB2 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57 3.1.2 IBM conversion support . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60 3.1.3 Education . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61 3.2 Application assessment. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62 3.3 System planning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63 3.3.1 Software . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64 3.3.2 Hardware. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64 3.3.3 Conversion tools . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65 3.4 The conversion process . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66 3.4.1 Preparing for the installation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67 3.4.2 Porting the database structure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67 3.4.3 Data porting. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68 3.4.4 Application porting. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70 3.4.5 Basic administration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72 3.4.6 Testing and tuning. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72 Chapter 4. Conversion scenario . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75 4.1 Application structure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76 4.1.1 Application flow . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76 4.2 Database structure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84 4.3 System environment . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85 Chapter 5. Installation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87 5.1 DB2 Express-C 9.7 on Linux . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 88 5.1.1 System requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 88 5.1.2 Installation procedure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 92 5.1.3 Instance creation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102 5.1.4 Client setup on Linux. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103 5.2 Other software products . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 105 5.2.1 Apache2 installation with DB2 support . . . . . . . . . . . . . . . . . . . . . . 105 5.2.2 PHP installation with DB2 support . . . . . . . . . . . . . . . . . . . . . . . . . 107 5.3 IBM Data Movement Tool installation and usage . . . . . . . . . . . . . . . . . . 112 5.3.1 IBM Data Movement Tool prerequisites . . . . . . . . . . . . . . . . . . . . . 112 5.3.2 IBM Data Movement Tool installation . . . . . . . . . . . . . . . . . . . . . . . 113 Chapter 6. Database conversion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 115 6.1 Data type mapping . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 116
  • 11.
    Contents v 6.2 Datadefinition language differences . . . . . . . . . . . . . . . . . . . . . . . . . . . . 122 6.2.1 Database manipulation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 123 6.2.2 Table manipulation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 128 6.2.3 Index manipulation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 136 6.2.4 Trigger manipulation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 137 6.2.5 Procedures and function manipulation . . . . . . . . . . . . . . . . . . . . . . 138 6.3 Other considerations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 138 6.4 Converting the database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 142 6.4.1 Automatic conversion using porting tools . . . . . . . . . . . . . . . . . . . . 143 6.4.2 Manual conversion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 144 6.4.3 Metadata transport . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 147 6.5 Sample database conversion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 148 6.5.1 Converting database objects with the IBM Data Movement Tool . . 148 6.5.2 Manual database object conversion and enhancements . . . . . . . . 158 Chapter 7. Data conversion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 167 7.1 Data porting considerations. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 168 7.1.1 Data porting commands and tools . . . . . . . . . . . . . . . . . . . . . . . . . 168 7.1.2 Differences in data formats . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 175 7.1.3 Differences in the user account management. . . . . . . . . . . . . . . . . 177 7.2 Sample project: Data porting. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 192 7.2.1 Export user data from MySQL. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 192 7.2.2 Map MySQL user data to DB2 user data . . . . . . . . . . . . . . . . . . . . 193 7.2.3 Create DB2 user . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 194 7.2.4 Export MySQL application data. . . . . . . . . . . . . . . . . . . . . . . . . . . . 195 7.2.5 Convert MySQL application data to DB2 format . . . . . . . . . . . . . . . 197 7.2.6 Import application data into DB2 . . . . . . . . . . . . . . . . . . . . . . . . . . . 197 7.2.7 Basic data checking . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 199 Chapter 8. Application conversion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 205 8.1 Data Manipulation Language differences and similarities . . . . . . . . . . . . 206 8.1.1 SELECT syntax. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 206 8.1.2 JOIN syntax. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 207 8.1.3 UNION syntax . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 208 8.1.4 Subquery syntax . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 209 8.1.5 Grouping, having, and ordering. . . . . . . . . . . . . . . . . . . . . . . . . . . . 209 8.1.6 Strings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 210 8.1.7 Implicit casting of data types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 213 8.1.8 String concatenation and NULL values. . . . . . . . . . . . . . . . . . . . . . 216 8.1.9 Record deletion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 218 8.1.10 Built-in functions and operators. . . . . . . . . . . . . . . . . . . . . . . . . . . 221 8.2 Application source conversion. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 222 8.2.1 Converting MySQL Perl applications to DB2 . . . . . . . . . . . . . . . . . 222
  • 12.
    vi MySQL toDB2 Conversion Guide 8.2.2 Converting MySQL PHP applications to DB2 . . . . . . . . . . . . . . . . . 225 8.2.3 Converting MySQL Ruby on Rails applications to DB2 . . . . . . . . . 237 8.2.4 Converting MySQL Java applications to DB2 . . . . . . . . . . . . . . . . . 240 8.2.5 Converting MySQL C/C++ applications to DB2 . . . . . . . . . . . . . . . 247 8.2.6 Converting Connector/ODBC applications to DB2 . . . . . . . . . . . . . 256 8.2.7 Condition handling in DB2. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 259 8.2.8 Special conversions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 266 8.3 Additional application considerations . . . . . . . . . . . . . . . . . . . . . . . . . . . 271 8.3.1 The purpose of locking . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 271 8.3.2 Concurrency control and transaction isolation . . . . . . . . . . . . . . . . 272 8.3.3 DB2 isolation levels. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 272 8.3.4 Locking . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 274 8.3.5 Specifying the isolation level in DB2 . . . . . . . . . . . . . . . . . . . . . . . . 276 Chapter 9. Database administration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 279 9.1 Database configuration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 280 9.1.1 DB2 configuration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 280 9.2 Database recovery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 286 9.2.1 DB2 database recovery. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 286 9.3 Database replication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 296 9.4 Data movement. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 298 9.4.1 DB2 data movement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 298 9.5 High availability . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 304 9.6 Autonomics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 306 9.7 Workload management . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 311 9.8 Database management tools . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 312 9.8.1 DB2 Control Center. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 312 9.8.2 IBM Optim and Data Studio tool suite overview . . . . . . . . . . . . . . . 314 Chapter 10. Testing and tuning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 321 10.1 Test planning. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 322 10.1.1 Principles of software tests . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 322 10.1.2 Test documentation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 322 10.1.3 Test phases. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 322 10.1.4 Time planning and time exposure . . . . . . . . . . . . . . . . . . . . . . . . . 323 10.2 Data checking techniques . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 324 10.2.1 IMPORT/LOAD messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 324 10.2.2 Data checking . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 328 10.3 Code and application testing. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 330 10.3.1 Checking the application code . . . . . . . . . . . . . . . . . . . . . . . . . . . 331 10.3.2 Security testing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 331 10.4 Troubleshooting. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 332 10.4.1 Interpreting DB2 informational messages . . . . . . . . . . . . . . . . . . . 332
  • 13.
    Contents vii 10.4.2 DB2tools for troubleshooting . . . . . . . . . . . . . . . . . . . . . . . . . . . . 334 10.4.3 DB2 diagnostic logs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 335 10.4.4 DB2 support information . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 339 10.4.5 Monitoring tools. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 342 10.4.6 Visual Explain . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 354 10.5 Initial tuning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 356 10.5.1 Table space design . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 356 10.5.2 Physical placement of database objects . . . . . . . . . . . . . . . . . . . . 357 10.5.3 Buffer pools . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 360 10.5.4 Large transactions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 363 10.5.5 SQL execution plan. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 366 10.5.6 Configuration Advisor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 371 10.5.7 Design Advisor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 375 Chapter 11. Advanced DB2 features . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 381 11.1 DB2 pureXML . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 382 11.2 Data compression . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 386 11.3 Partitioning features . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 388 11.3.1 Database partitioning feature . . . . . . . . . . . . . . . . . . . . . . . . . . . . 388 11.3.2 Table partitioning. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 389 11.3.3 Multidimensional clustering . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 392 11.4 Materialized query tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 394 11.5 User-defined data types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 396 Appendix A. Mapping MySQL built-in functions and operators . . . . . . . 399 A.1 Grouping related functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 400 A.2 String functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 402 A.3 Numeric functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 408 A.4 Date and time functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 409 A.5 Comparing operators and other functions. . . . . . . . . . . . . . . . . . . . . . . . 410 Appendix B. Sample code for user-defined functions. . . . . . . . . . . . . . . 413 B.1 Sample code for BIT_AND . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 414 B.2 Sample code for FORMAT function . . . . . . . . . . . . . . . . . . . . . . . . . . . . 415 B.3 Sample code for RPAD and LPAD functions . . . . . . . . . . . . . . . . . . . . . 416 B.4 Sample code for GREATEST function . . . . . . . . . . . . . . . . . . . . . . . . . . 422 B.5 Sample code for LEAST . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 427 B.6 Sample code for BIT_COUNT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 431 B.7 Sample code for SUBSTRING_INDEX. . . . . . . . . . . . . . . . . . . . . . . . . . 432 B.8 Sample code for UNIX_TIMESTAMP . . . . . . . . . . . . . . . . . . . . . . . . . . . 433 Related publications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 435 IBM Redbooks publications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 435 Other publications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 435
  • 14.
    viii MySQL toDB2 Conversion Guide Online resources . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 436 How to get IBM Redbooks publications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 439 Help from IBM . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 439 Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 441
  • 15.
    © Copyright IBMCorp. 2009. All rights reserved. ix Notices This information was developed for products and services offered in the U.S.A. IBM may not offer the products, services, or features discussed in this document in other countries. Consult your local IBM representative for information on the products and services currently available in your area. Any reference to an IBM product, program, or service is not intended to state or imply that only that IBM product, program, or service may be used. Any functionally equivalent product, program, or service that does not infringe any IBM intellectual property right may be used instead. However, it is the user's responsibility to evaluate and verify the operation of any non-IBM product, program, or service. IBM may have patents or pending patent applications covering subject matter described in this document. The furnishing of this document does not give you any license to these patents. You can send license inquiries, in writing, to: IBM Director of Licensing, IBM Corporation, North Castle Drive, Armonk, NY 10504-1785 U.S.A. The following paragraph does not apply to the United Kingdom or any other country where such provisions are inconsistent with local law: INTERNATIONAL BUSINESS MACHINES CORPORATION PROVIDES THIS PUBLICATION "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESS OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF NON-INFRINGEMENT, MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE. Some states do not allow disclaimer of express or implied warranties in certain transactions, therefore, this statement may not apply to you. This information could include technical inaccuracies or typographical errors. Changes are periodically made to the information herein; these changes will be incorporated in new editions of the publication. IBM may make improvements and/or changes in the product(s) and/or the program(s) described in this publication at any time without notice. Any references in this information to non-IBM Web sites are provided for convenience only and do not in any manner serve as an endorsement of those Web sites. The materials at those Web sites are not part of the materials for this IBM product and use of those Web sites is at your own risk. IBM may use or distribute any of the information you supply in any way it believes appropriate without incurring any obligation to you. Information concerning non-IBM products was obtained from the suppliers of those products, their published announcements or other publicly available sources. IBM has not tested those products and cannot confirm the accuracy of performance, compatibility or any other claims related to non-IBM products. Questions on the capabilities of non-IBM products should be addressed to the suppliers of those products. This information contains examples of data and reports used in daily business operations. To illustrate them as completely as possible, the examples include the names of individuals, companies, brands, and products. All of these names are fictitious and any similarity to the names and addresses used by an actual business enterprise is entirely coincidental. COPYRIGHT LICENSE: This information contains sample application programs in source language, which illustrate programming techniques on various operating platforms. You may copy, modify, and distribute these sample programs in any form without payment to IBM, for the purposes of developing, using, marketing or distributing application programs conforming to the application programming interface for the operating platform for which the sample programs are written. These examples have not been thoroughly tested under all conditions. IBM, therefore, cannot guarantee or imply reliability, serviceability, or function of these programs.
  • 16.
    x MySQL toDB2 Conversion Guide Trademarks IBM, the IBM logo, and ibm.com are trademarks or registered trademarks of International Business Machines Corporation in the United States, other countries, or both. These and other IBM trademarked terms are marked on their first occurrence in this information with the appropriate symbol (® or ™), indicating US registered or common law trademarks owned by IBM at the time this information was published. Such trademarks may also be registered or common law trademarks in other countries. A current list of IBM trademarks is available on the Web at http://www.ibm.com/legal/copytrade.shtml The following terms are trademarks of the International Business Machines Corporation in the United States, other countries, or both: 1-2-3® AIX® AS/400® ClearCase® DB2 Connect™ DB2 Universal Database™ DB2® developerWorks® Distributed Relational Database Architecture™ DRDA® eServer™ Everyplace® HACMP™ i5/OS® IBM® IMS™ Informix® InfoSphere™ iSeries® OpenPower® Optim™ OS/390® PartnerWorld® POWER® pSeries® pureXML® Rational Rose® Rational® Redbooks® Redbooks (logo) ® System i® System p® System z9® System z® Tivoli® UniData® WebSphere® z/OS® z9® zSeries® The following terms are trademarks of other companies: AMD, the AMD Arrow logo, and combinations thereof, are trademarks of Advanced Micro Devices, Inc. Snapshot, and the NetApp logo are trademarks or registered trademarks of NetApp, Inc. in the U.S. and other countries. SUSE, the Novell logo, and the N logo are registered trademarks of Novell, Inc. in the United States and other countries. Oracle, JD Edwards, PeopleSoft, Siebel, and TopLink are registered trademarks of Oracle Corporation and/or its affiliates. Red Hat, and the Shadowman logo are trademarks or registered trademarks of Red Hat, Inc. in the U.S. and other countries. VMware, the VMware "boxes" logo and design are registered trademarks or trademarks of VMware, Inc. in the United States and/or other jurisdictions. EJB, Enterprise JavaBeans, J2EE, Java, Java runtime environment, JavaBeans, JavaServer, JDBC, JSP, MySQL, Solaris, Sun, and all Java-based trademarks are trademarks of Sun Microsystems, Inc. in the United States, other countries, or both. Access, ActiveX, Expression, Microsoft, MS, SQL Server, Visual Basic, Visual Studio, Windows Mobile, Windows, and the Windows logo are trademarks of Microsoft Corporation in the United States, other countries, or both.
  • 17.
    Notices xi Intel Pentium,Intel Xeon, Intel, Itanium-based, Itanium, Pentium, Intel logo, Intel Inside logo, and Intel Centrino logo are trademarks or registered trademarks of Intel Corporation or its subsidiaries in the United States, other countries, or both. UNIX is a registered trademark of The Open Group in the United States and other countries. Linux is a trademark of Linus Torvalds in the United States, other countries, or both. Other company, product, or service names may be trademarks or service marks of others.
  • 18.
    xii MySQL toDB2 Conversion Guide
  • 19.
    © Copyright IBMCorp. 2009. All rights reserved. xiii Preface Switching database vendors is often considered an exhausting challenge for database administrators and developers. Complexity, total cost, and the risk of downtime are often the reasons that restrain IT decision makers from starting the conversion project. The primary goal of this book is to show that, with the proper planning and guidance, converting from MySQL™ to IBM DB2® for Linux, UNIX, and Windows is not only feasible but straightforward. If you picked up this book, you are most likely considering converting to DB2 and are probably aware of several of the advantages of converting to DB2 data server. In this IBM® Redbooks® publication, we discuss in detail how you can take advantage of this industry leading database server. This book is an informative guide that describes how to convert the database system from MySQL 5.1 to DB2 9.7 on Linux®, and the steps involved in enabling the applications to use DB2 instead of MySQL. This MySQL to DB2 migration guide also presents the best practices in conversion strategy and planning, conversion tools, porting steps, and practical conversion examples. It is intended for technical staff involved in a MySQL to DB2 conversion project. The team who wrote this book This book was produced by a team of specialists from around the world working at the International Technical Support Organization, San Jose Center. Whei-Jen Chen is a Project Leader at the International Technical Support Organization, San Jose Center. She has extensive experience in application development, database design and modeling, and DB2 system administration. Whei-Jen is an IBM Certified Solutions Expert in Database Administration and Application Development, as well as an IBM Certified IT Specialist.
  • 20.
    xiv MySQL toDB2 Conversion Guide Angela Carlson is a Software Engineer with IBM Canada. She has four years of technical experience working in the IT industry, focusing on relational database technology and application development. She has experience in developing relational database applications with PHP, Perl, and Java™. In Angela’s current position, she works closely with IBM Business Partners and their database needs to enable their solutions with DB2. She also researches and develops competitive material on DB2 and MySQL. Angela holds a Bachelor’s degree of Software Engineering Science from the University of Western Ontario. Thanks to the following people for their contributions to this project: Boris Bialek Program Director, Information Management Partner Technologies, IBM Canada Irina Delidjakova Information Management Emerging Partnerships and Technologies, IBM Canada Vlad Barshai Information Management Emerging Partnerships and Technologies, IBM Canada Martin Schlegel Information Management Partner Technologies, IBM Canada Daniel Krook Cloud Engineering and Experience, IBM U.S. Emma Jacob International Technical Support Organization, San Jose Center Thanks to the authors of the previous edition of this book: Authors of the first edition, MySQL to DB2 UDB Conversion Guide, SG24-7093, published in May 2004, were Whei-Jen Chen, Andreas Blank, Michael Hoeller, Rakesh Midha, and Klaus Subtil Become a published author Join us for a two- to six-week residency program. Help write a book dealing with specific products or solutions, while getting hands-on experience with
  • 21.
    Preface xv leading-edge technologies.You will have the opportunity to team with IBM technical professionals, IBM Business Partners, and Clients. Your efforts will help increase product acceptance and customer satisfaction. As a bonus, you will develop a network of contacts in IBM development labs, and increase your productivity and marketability. Find out more about the residency program, browse the residency index, and apply online at: ibm.com/redbooks/residencies.html Comments welcome Your comments are important to us. We want our books to be as helpful as possible. Send us your comments about this book or other IBM Redbooks publications in one of the following ways: 򐂰 Use the online Contact us review IBM Redbooks publication form found at: ibm.com/redbooks 򐂰 Send your comments in an e-mail to: redbooks@us.ibm.com 򐂰 Mail your comments to: IBM Corporation, International Technical Support Organization Dept. HYTD Mail Station P099 2455 South Road Poughkeepsie, NY 12601-5400
  • 22.
    xvi MySQL toDB2 Conversion Guide
  • 23.
    © Copyright IBMCorp. 2009. All rights reserved. xvii Summary of changes This section describes the technical changes made in this edition of the book and in previous editions. This edition might also include minor corrections and editorial changes that are not identified. Summary of Changes for SG24-7093-01 for MySQL to DB2 Conversion Guide as created or updated on December 1, 2009. December 2009, Second Edition This revision reflects the addition, deletion, or modification of new and changed information described below. New information 򐂰 DB2 features and functions of DB2 for Linux, UNIX®, and Windows® Version 9, 9.5, and 9.7 򐂰 IBM Data Movement Tool 򐂰 MySQL 5.1 features Changed information 򐂰 DB2 and MySQL features and functions 򐂰 Conversion scenarios and examples
  • 24.
    xviii MySQL toDB2 Conversion Guide
  • 25.
    Executive summary xix Executivesummary This book describes how to migrate MySQL 5.1 to DB2 Version 9.7 on Linux and enable your applications on DB2. To further ease your migration, this informative guide will cover best practices in migration strategy and planning, as well as the step-by-step directions, tools, and practical conversion. After completing this book, it will be clear to the technical reader that a MySQL to DB2 migration is easy and straightforward. Potential IBM clients seek migration information because DB2 offers performance and functional capabilities that the competition can't compare with. DB2 Express C, our lightweight community edition, is free to develop, deploy and redistribute, and is designed to give the IT community a powerful alternative to the open source or free databases currently available. DB2 Express C offers the same high quality, reliable, scalable features that you would expect from an IBM enterprise database at no charge. Fixed Term License support is available as well, at a lower price than the competition. The decision to migrate becomes simple when you consider that DB2 can be easily deployed in the development stack, while offering many additional features and ease of use. Enterprise class features aimed to lower the total cost of ownership can be found in every edition of DB2. DB2 has powerful autonomics which make installation, configuration, maintenance and administration virtually hands free. DB2 9.7's compression features help companies manage rising energy costs and reduce datacenter sprawl by reducing storage requirements and improving I/O efficiency. IBM is committed to providing products to our clients that are powerful and affordable. DB2 provides industry leading features, such as pureXML, Workload Management, and Granular Security. Using DB2 pureXML® makes XML data processing even faster, more flexible, and more reliable. Manage workloads with new threshold, priority and OS integration features in DB2 9.7. Keep data secure from internal and external threats using the unparalleled security control in DB2 9.7. Start taking advantage of these exciting new features and help your business manage costs and simplify application development. Migrate your database systems and applications today and discover why DB2 9.7 is a smarter product for a smarter planet. Arvind Krishna General Manager IBM Information Management
  • 26.
    xx MySQL toDB2 Conversion Guide
  • 27.
    © Copyright IBMCorp. 2009. All rights reserved. 1 Chapter 1. DB2 for Linux, UNIX, and Windows The goal of this chapter is to give an overview of the DB2 database server, its architecture, and the tools and utilities that are available with the server and application programming interfaces. In this chapter, we cover these topics: 򐂰 DB2 product overview 򐂰 DB2 for Linux, UNIX, and Windows architecture 򐂰 DB2 utilities 򐂰 DB2 database access 1
  • 28.
    2 MySQL toDB2 Conversion Guide 1.1 Introduction IBM has an extremely strong history of database innovation and has developed a number of highly advanced data servers. It started in the 60s when IBM developed the Information Management System (IMS™), which is a hierarchical database management system. IMS was used to maintained inventory for the Saturn V moon rocket and the Apollo space vehicle. In the 70s, IBM invented the Relational Model and the Structured Query Language (SQL). In the 80s, IBM introduced DB2 for the mainframe (DB2 for z/OS®), which was the first database that used relational modeling and SQL. DB2 for distributed platforms (DB2 for Linux, UNIX, and Windows) was introduced in the 90s. Since then, IBM continues to develop on DB2 for both mainframe and distributed platforms. Although the relational data model has become more prevalent in the industry; IBM still realizes that the hierarchical data model is important. Therefore in July 2006, IBM launched the first hybrid (also known as multi-structured) data server. The release of DB2 for Linux, UNIX, and Windows Version 9 (DB2 9) data server brought the most exciting and innovative database features to the market; these features were further enhanced with the release of DB2 9.5 and 9.7. DB2 9 introduced many important features for both database administrators and application developers. These features included pureXML®, autonomics, table partitioning, data compression, and label-based access control. DB2 9.5 enhanced the manageability of the DB2 data server by introducing the threaded engine, easier integration with high availability disaster recovery (HADR), workload management, enhancements to autonomics, and more. The focus of the DB2 9.7 release is to provide unparalleled reliability and scalability for the changing needs of your business. Therefore, DB2 9.7 introduces enhancements to Version 9 and Version 9.5 features, such as enhancements to data compression, performance, workload management, security, and application development. When this book was written, DB2 9.7 had just been released on June 2009. DB2 9.7 is the database version that we use throughout the book. DB2 9.7 is a highly scalable and easy to install and manage hybrid data server. DB2 was developed to meet the demands of even the most critical database applications. This is managed through various autonomics capabilities, such as self-tuning memory management and automatic storage. DB2 provides a highly adaptable database environment while optimizing data storage through backups and deep data row compression. DB2 deep embedded capabilities allow for ubiquitous deployment in user directories and administrative installations for any size server. In a single database, DB2 provides native storage and processing of both transactional XML data in a pre-parsed tree format and relational data using pureXML technology.
  • 29.
    Chapter 1. DB2for Linux, UNIX, and Windows 3 1.2 Product overview DB2 for Linux, UNIX, and Windows spans the spectrum from products on handheld devices to large clusters and mainframes (see Figure 1-1). You can obtain more detailed information from the DB2 Web site: http://www.ibm.com/db2/ Figure 1-1 DB2 product overview 1.2.1 DB2 Data Server Editions for the production environment DB2 provides packages for users based on their business needs. In DB2 9.5, IBM introduced the common client concept for all IBM data management products that provides equal engine functionality across system boundaries. This functionality means that DB2 scales up and down according to your needs. Be it a desktop or a heavy duty enterprise server, DB2 has consistent behavior across any platform, providing an easy move regardless of the size limit. DB2 widens the boundaries even further, because all IBM data servers, including Informix®, now share a common API for SQL. Therefore, whether using Java Database Connectivity (JDBC™) on DB2 for z/OS, Informix 11 on an embedded system, or DB2 for Linux, UNIX, and Windows, the driver code remains the same. In fact, all database connections to any IBM data server can be made with the same common client. In addition, IBM Optim™ Data Studio provides a new cross-family tool for both application development and database management, underlining commitment across and beyond DB2.
  • 30.
    4 MySQL toDB2 Conversion Guide There are several DB2 package formats: 򐂰 DB2 Personal Edition DB2 Personal Edition (PE) provides a single user database engine that is ideal for deployment to PC-based users. The PE includes the ability for remote management, the pureXML feature, and the SQL replication feature, making it the perfect choice for deployment in occasionally connected or remote office implementations that do not require multi-user capability, that is, point-of-sale systems. PE does not accept remote database requests; however, it contains DB2 client components and serves as a remote client to a DB2 Server. The DB2 Personal Edition can also be used for connecting and managing other DB2 data servers in the network. The Personal Edition includes most of the features included in DB2 Express Edition and runs in either 32-bit or 64-bit Intel® or AMD™ workstations for either Windows or Linux operating systems. 򐂰 DB2 Express-C DB2 Express-C is the no-charge community version of the DB2 data server. It is targeted towards developers and Independent Software Vendors (ISVs) to allow the development and deployment of applications, including the no charge distribution of DB2 Express-C itself. All applications developed with this version of DB2 can be moved to a higher edition of DB2 for Linux, UNIX and Windows and even DB2 for z/OS without any application changes if using the common SQL API set of the DB2 family. This version of DB2 is at no charge for download and is therefore perfectly suited for DB2 educators and students. DB2 Express-C does not restrict the database size and can be used in a 64-bit memory model. The code is optimized to use up to a maximum of 2 CPU cores and 2 GB of memory. No fix pack updates are available for this edition; however, new versions of Express-C are updated and freely available for download at any time. While this version does not include all the features of higher editions of DB2, such as storage optimization, replication services, or high availability, it comes with the award-winning pureXML technology to leverage both relational and XML data by being able to natively store XML data in a single database. Several of these features can be activated by purchasing the DB2 Express Fixed Term License (FTL). Obtaining the FTL provides 1 year of 24x7 support, plus the ability to use high availability and disaster recovery. At any point, users of DB2 Express-C can receive advice on the IBM DB2 Express Forum, which is monitored by IBM DB2 developers, by accessing the following link: http://www.ibm.com/developerworks/forums/forum.jspa?forumID=805
  • 31.
    Chapter 1. DB2for Linux, UNIX, and Windows 5 DB2 Express-C runs on Windows or Linux for both Intel and AMD on 32-bit or 64-bit architecture, as well as on Linux on Power (IBM System p® and System i®). You can download DB2 Express-C from this link: hhttp://www-01.ibm.com/software/data/db2/express/ 򐂰 DB2 Express + Fixed Term License The plus fixed term license (FTL) is a 1-year license subscription with 24x7 IBM support to DB2 Express, providing service-level assurances for problem resolution and offering fix pack updates tied to the usual DB2 fix pack schedule. FTL also raises the DB2 Express-C limitations and is optimized to run for 4 CPU cores and 4 GB of memory. Additional features with the DB2 Express FTL include the High Availability Disaster Recovery (HADR) feature and full SQL replication capability. Licensing is quite easy with DB2 Express-C + FTL, because it requires only purchasing one FTL subscription per data server no matter how it is used or as explained before, its size. This licensing explicitly applies also to an HADR setup, where no distinction is made between cold, warm, or hot standby usage. DB2 Express-C with FTL runs on Windows or Linux for both Intel or AMD on 32-bit or 64-bit architecture, as well as on Linux on Power (IBM System p and System i). More information about DB2 Express + Fixed Term License is available at this Web site: http://www-01.ibm.com/software/data/db2/express/support.html?S_TACT=105AGX2 8&S_CMP=DLMAIN 򐂰 DB2 Express DB2 Express edition is specifically tailored for small and medium businesses (SMBs). It is designed for independent software vendors who need an easy-to-install database integrated into their application software solution. It is a multi-user version that supports local and remote applications in stand-alone and local area network (LAN) environments. DB2 Express utilizes up to 4 GB of memory and can be installed on a server with up to 200 processor value units. For more information about processor value units, visit this Web site: http://www.ibm.com/developerworks/db2/library/techarticle/dm-0611zikopoulos 2/ Several key features and strengths of the Express Edition are simplified deployment, SQL replication, backup compression, autonomic management
  • 32.
    6 MySQL toDB2 Conversion Guide features, such as the Self-Tuning Memory Manager, adaptive utility throttling, automatic storage management, configuration and design advisors, health and fault monitors, automated backup, pureXML, high availability, and more. DB2 Express runs on Linux, Solaris™ x86, or Windows platforms. 򐂰 DB2 Workgroup DB2 Workgroup Server Edition is used primarily in small to medium-sized business environments. It includes support for a per authorized user-based or a per processor-based licensing model that is designed to provide an attractive price point for smaller installations while still providing a fully functional data server on a wider range of platforms compared to lower function editions of DB2. Workgroup Server now includes the High Availability Disaster Recovery (HADR) feature, Tivoli System Automation for MultiPlatforms (TSA MP), and Online Table Reorganization. The following feature packs are available: – Query Optimization including Materialized Query Tables (cached tables) – Multidimensional Clustering and Query Parallelism – pureXML This DB2 edition can be deployed on systems with up to 400 processor value units and 16 GB of memory. While the DB2 Express edition only runs on Windows, Linux, or Solaris, the Workgroup Server edition adds support for AIX®, Hewlett-Packard UNIX (HP-UX) on Itanium64, and Solaris on x86 and Sparc. 򐂰 DB2 Enterprise DB2 Enterprise Server Edition meets the database server needs for any size business. This product is the ideal foundation for building data warehouses, transaction processing, or Web-based solutions, as well as for a back-end for packaged solutions, such as enterprise resource planning (ERP), customer relationship management (CRM), and supply chain management (SCM). In addition, the DB2 Enterprise Server Edition offers connectivity and integration for other enterprise DB2 and Informix data sources. The Enterprise Server Edition does not pose limits to the maximum memory or number of CPU cores. It can be licensed with either authorized user licenses or processor value unit licenses. In addition to the features offered in the Workgroup Server Edition, the following features are also available: Query Parallelism, Multidimensional Clustering, Materialized Query Tables, Table Partitioning, and Connection Concentration. With the DB2 feature pack, you can add the following features: Performance Optimization Feature, Advanced Access® Control Feature, Storage Optimization Feature, and Geodetic Data Management Feature.
  • 33.
    Chapter 1. DB2for Linux, UNIX, and Windows 7 DB2 Enterprise Server Edition runs on Windows (32-bit and 64-bit), Linux (Intel/ AMD 64-bit, System i, System p, System z®), AIX, Solaris (Sparc and x64) and HP-UX (ia64). 򐂰 InfoSphere™ Warehouse InfoSphere Warehouse (formerly known as DB2 Warehouse) is a powerful platform for building business intelligence (BI) solutions. InfoSphere Warehouse comes as single integrated software package, using DB2 Enterprise Server Edition as its base. It provides an ideal solution for companies that need to consolidate data marts, information silos, and business analytics. InfoSphere Warehouse contains database management tools and embedded data movement and transformation tools. In addition to the base feature set of DB2 Enterprise Server Edition, the DB2 feature packs containing Data Partitioning Feature (DPF) are standard in all editions of InfoSphere Warehouse. Workload Management and Deep Data Row Compression are optional features for InfoSphere Warehouse Enterprise Base Edition. The overall integrated software package for InfoSphere Warehouse has support for slice and dice analytics and provides direct support for optimized online analytical processing (OLAP) analytics against the dynamic warehouse. Embedded analytical features include capabilities for Data Mining, In-line Analytics, Cubing Services, and Unstructured Analytics. 򐂰 Other DB2 editions There are also DB2 versions for System i and System z available. See the following Web site for details: http://www.ibm.com/software/data/db2/ 1.2.2 Products for accessing System z and System i host data With the following DB2 products, you can extend enterprise systems to access the host data in a System z or System i system: 򐂰 DB2 Connect™ Personal Edition The DB2 Connect Personal Edition provides the application programming interface (API) drivers and connectivity infrastructure to enable direct connectivity from desktop applications to System z and System i data servers. This product is specifically designed and is licensed for enabling two-tier, client/server applications running on individual workstations, and as such, is not appropriate for use on servers. 򐂰 DB2 Connect Enterprise Edition The DB2 Connect Enterprise Edition addresses the needs of organizations that require robust connectivity from a variety of desktop systems to System i
  • 34.
    8 MySQL toDB2 Conversion Guide and System z database servers. DB2 client software is deployed on desktop systems and provides drivers that connect client/server applications running on these desktop systems to a DB2 Connect server (gateway) that accesses host data. The licensing model for this product is user-based. 򐂰 DB2 Connect Application Server Edition Technically, the DB2 Connect Application Server Edition product is identical to the DB2 Connect Enterprise Server; however, its licensing terms and conditions are meant to address the specific needs of multi-tier, client/server applications, as well as applications that utilize Web technologies. DB2 Connect Application Server Edition license charges are based on the size and number of processors that are available to the application servers where the application is running. 򐂰 DB2 Connect Unlimited Edition The DB2 Connect Unlimited Edition product is ideal for organizations with extensive usage of DB2 Connect, especially where multiple applications are involved. This product provides program code of the DB2 Connect Personal Edition, as well as program code identical to the DB2 Connect Application Server Edition for unlimited deployment throughout an organization. 1.2.3 DB2 for pervasive platforms DB2 Everyplace® Edition supports a wide variety of handheld devices, such as Palm OS, Windows Mobile® for Pocket PC, Windows desktops, Symbian, QNX Neutrino, and various Linux distributions. Its strength is to synchronize with data from other systems, using security features, including table-level encryption and communication encryption, such as Secure Sockets Layer (SSL). DB2 Everyplace is also extremely flexible by being completely self-managed and by supporting many programmable API interfaces, such as Open Database Connectivity (ODBC), Java Database Connectivity (JDBC), .NET, DB2 call level interface (CLI), and so on. Its engine is highly flexible, which makes it easy to move DB2 Everyplace databases from one supported mobile device to another. 1.2.4 Additional DB2 data server features In addition to the product offerings for Linux, UNIX, Windows, and accessing System z and System i host data, DB2 also offers a great variety of features, which are included in various DB2 packages. Information about additional DB2 features and products can be found at this Web site: http://www.ibm.com/software/data/db2/9/features.html?S_CMP=wspace
  • 35.
    Chapter 1. DB2for Linux, UNIX, and Windows 9 1.3 DB2 for Linux, UNIX, and Windows architecture Data servers provide software services for the secure and efficient management of structured information. DB2 is a hybrid relational and XML data server. A data server refers to a computer where the DB2 database engine is installed. The DB2 engine is a full-function, robust database management system that includes optimized SQL support based on actual database usage and tools to help manage the data. In this section, we will discuss the DB2 engine architecture and the database objects that are used to maintain your database. 1.3.1 DB2 9.7 threaded architecture and process model Figure 1-2 shows the DB2 9.7 architecture overview. Starting with DB2 9.5, a threaded model architecture is used. There are many advantages to using a threaded model architecture. Firstly, a new thread requires less memory and fewer operating system resources than a process, because certain operating system resources can be shared among all threads within the same process. Moreover, on specific platforms, the context switch time for threads is less than that for processes, which can improve performance. Figure 1-2 DB2 threaded architecture
  • 36.
    10 MySQL toDB2 Conversion Guide From a client/server perspective, the client code and the server code are separated into separate address spaces. The application code runs in the client process, while the server code runs in a separate process. The client process can run on either the same machine as the data server or another machine, accessing the data server through a programming interface. The memory units are allocated for database managers, databases, and applications. Because DB2 is running with a threaded architecture, all threads within the engine process share the same address space, meaning all threads can immediately see new memory allocations. This design creates a simplified memory model by allowing memory growth and shrinkage through control of a single memory parameter for an entire instance. This control is automatically performed by the Self-Tuning Memory Manager, which can also tune other memory parameters for best performance without DBA intervention. Its adaptive algorithm is able to react to unforeseen memory requirements in DB2 caused by workloads running against it. To enable access to a specific database, the DB2 instance process responsible for the database must be running on the DB2 server. When an instance process is started, several processes are created, which interact with one another to maintain connected applications and the database. There are several background processes in DB2 that are pre-started; other processes start on a need-only basis. There are several important background processes: 򐂰 The main process is the DB2 System Controller (db2sysc), which runs the entire DB2 engine infrastructure. The DB2 data server activities are performed by Engine Dispatchable Units (EDU), which are defined as threads running within a single operating system process. 򐂰 A second DB2 background process is started together with the system controller and is called the DB2 Watch Dog (db2wdog). Its responsibility is to watch and monitor the system controller and to react to error conditions. 򐂰 For autonomic tasks, another process is initialized when the database is activated, which can happen either manually or by being triggered by a client connection. This process is the Autonomic Computing Daemon (db2acd) and runs autonomic tasks, such as health-monitoring, auto-runstats, and the administration scheduler on the client side. 򐂰 There is also the db2fmp process, which, decoupled from the DB2 system controller process, serves thread-safe stored procedures and user-defined functions (UDFs). 1.3.2 DB2 database objects In this section, we introduce the DB2 objects and their relationships to each other. Figure 1-3 on page 11 shows the fundamental DB2 database objects.
  • 37.
    Chapter 1. DB2for Linux, UNIX, and Windows 11 Figure 1-3 DB2 object relationships These are a few major DB2 objects: 򐂰 Instances A DB2 instance represents the database management system. It controls how data is manipulated and manages system resources assigned to it. Each instance is a complete, fairly independent environment, containing all the database partitions defined for a given parallel database system. An instance can have its own set of databases (which other instances cannot access directly), and all database partitions share the same system directories. Each instance has separate security from other instances on the same machine (system), allowing for situations where both production and development environments are run on the same machine without interference. In order to connect to a database, any database client must first establish a network connection to the instance. 򐂰 Databases A database is a structured collection of data, which is stored within tables. Since DB2 9, data within tables can be stored as both relational data and XML documents natively in a pre-parsed tree format within a table column. Each database includes a set of system catalog tables that describes the
  • 38.
    12 MySQL toDB2 Conversion Guide logical and physical structure of the object in the database, a configuration file containing the parameter values configured for the database, and a recovery log. Figure 1-4 shows the relationship between instances, databases, and tables. Figure 1-4 Relationship between instances, databases, and tables 򐂰 Database partition groups A database partition group is a set of one or more database partitions (Figure 1-5 on page 13). A database partition group must be created prior to the creation of the tables in a database. This database partition group is where the table spaces will be stored. After the database partition group is there, a table space can be created where tables will be stored. If a partition group is not specified, there is a default group where table spaces are allocated. In a non-partitioned environment, all the data resides in a single partition; therefore, it is unnecessary to worry about partition groups in simple setups.
  • 39.
    Chapter 1. DB2for Linux, UNIX, and Windows 13 Figure 1-5 Database partition groups in a database 򐂰 System catalog tables Each database includes a set of system catalog tables that describes the logical and physical structure of the data. DB2 creates and maintains an extensive set of system catalog tables for each database. These tables contain information regarding definitions of database objects, including user tables, views, indexes, and security information about the privileges that users have on these objects. Catalog tables are created when the database is created and are updated during normal operations. They cannot be explicitly created or dropped; however, they can be queried for their contents using the catalog views. 򐂰 Table spaces A database is organized into subdivided table spaces, which store data. When creating a table, you can decide to have certain objects, such as indexes and large object (LOB) data, kept separately from the rest of the table data. A table space is equally spread over one or more physical storage devices, which is called striping. When using a database, if no additional table spaces are created by the user, the default user table space is used. DB2 allows you as much control as needed. When table spaces are created, they reside in database partition groups. The table space definitions and attributes are maintained in the database system catalog. Each table space has at least one container assigned to it. A container is an allocation of physical storage, such as a file or a device. Table spaces come in two types: system managed space (SMS) or database managed space (DMS), as shown in Figure 1-6 on page 14. In an SMS table
  • 40.
    14 MySQL toDB2 Conversion Guide space, each container is a directory in the file system of the operating system. This type of table space allows the operating system’s file manager to control the storage space. In a DMS table space, each container is either a re-sizable file or a pre-allocated physical device, such as a disk, which the database manager must control. Figure 1-6 DMS/SMS table spaces and containers When using SMS or DMS in combination with container files, you can choose how DB2 handles these files. For example, you can choose to enable various optimization features if supported by the operating systems, that is, Direct I/O (to bypass file system caching; always enabled with raw and block devices), Vector I/O (reading contiguous data pages from disk into contiguous portions of memory), and Async I/O (non-sequential processing of read and write requests across multiple disks to avoid delays from synchronous events). When using the Automatic Storage feature in DB2, you can simply specify folders where the database can automatically create and manage DMS table spaces. When more space is required, the database manager automatically allocates more space. Table spaces can be automatically resized using this feature. This feature provides a convenient and worry-free operation scenario. You can perform manual operations without having to specify container files. 򐂰 Containers A container is a physical storage device. It can be identified by a directory name, a device name, or a file name. A container is assigned to a table space. A single table space can span many containers, but each container can belong to only one table space.
  • 41.
    Chapter 1. DB2for Linux, UNIX, and Windows 15 򐂰 Buffer pools A buffer pool is the amount of memory allocated to cache table and index data pages. The purpose of the buffer pool is to improve system performance. Think of it as a database-controlled file system cache. Data can be accessed much faster from memory than from disk. Therefore, the fewer times the database manager needs to read from or write to a disk (I/O) synchronously, the better the performance of the application. The size of the buffer pool is the single most important performance tuning area to help reduce the delay caused by synchronous I/O. Buffer pool memory can be automatically tuned online in the same way as most other memory-related parameters. The feature responsible for automatic tuning is called the Self-Tuning Memory Manager, which allocates and releases new memory from the OS by shifting unused memory within DB2 to components. 򐂰 Schemas A schema is an identifier, by default, the user ID, which qualifies tables and other database objects. A schema can be owned by an individual, and the owner can control access to the data and the objects within it. A schema name is used as the first part of a two-part object name. For example, a schema named Smith might qualify a table named SMITH.PAYROLL. 򐂰 Tables A database presents data as a collection of tables. Data within a table is arranged in columns and rows. A table can contain XML documents that are natively stored as a parsed hierarchical format, as shown in Figure 1-7 on page 16. The data in the table is logically related, and relationships can be defined between tables. Table data is accessed by using Structured Query Language (SQL) or XQuery with XPath expressions. Both products are standardized query languages for defining and manipulating both relational and XML data in a database. A query is used in applications or by users to retrieve data from a database. A typical query for relational data uses SQL to create a statement in the form of: SELECT <column_name> FROM <table_name> A typical XQuery for the table that is shown in figure 1-7 looks like this query: xquery db2-fn:sqlquery("SELECT INFO FROM XMLEmployeeInfo WHERE EmpID=1001")/customerinfo/name This query allows us to iterate through all our table rows and return customer information from each XML document stored in each row for the example table that is outlined in Figure 1-7 on page 16. DB2 and the XQuery language also allow us to modify and update a subtree of an XML document in place without having to rewrite the whole document. This query is possible,
  • 42.
    16 MySQL toDB2 Conversion Guide because we already store XML documents in a pre-parsed and hierarchical format: db2 create table dept (EmpID int,…, XMLEmployeeInfo xml) Figure 1-7 Relational and XML data in a single table When using the Deep Data Row Compression feature, DB2 is able to transparently compress and decompress table rows (for each table with compression turned on). This feature can effectively save 45-80% of the space on disk. Compressed rows in a table are compressed when pre-fetched to buffer pool memory and left in a compressed state until they are actually used. Although decompression of the data when it is fetched adds a slight overhead, I/O bound workloads will have a performance gain due to the reduced amount of data we actually need to read and write from or to disk, as well as saved memory. 򐂰 Views A view provides another way of looking at data from one or more tables; it is a named specification of a result table. The specification is a SELECT statement that runs whenever the view is referenced in an SQL statement. A view has columns and rows just like a base table. All views can be used just like base tables for data retrieval. Figure 1-8 on page 17 shows the relationship between tables and views.
  • 43.
    Chapter 1. DB2for Linux, UNIX, and Windows 17 Figure 1-8 Relationship between tables and views 򐂰 Indexes An index is a set of keys, each pointing to rows in a table. For example, table A has an index based on the first column in the table (Figure 1-9 on page 18). This key value provides a pointer to the rows in the table: value 19 points to record KMP. If searching for this particular record, a full table scan can be avoided, because we have an index defined. Except for changes in performance, users of this table are unaware that an index is being used. DB2 decides whether to use the index or not. DB2 also provides tools, such as the Design Advisor, that can help decide what indexes will be beneficial. An index allows efficient access when selecting a subset of rows in a table by creating a direct path to the data through pointers. The DB2 SQL Optimizer chooses the most efficient way to access data in tables. The optimizer takes indexes into consideration when determining the fastest access path. Indexes have both benefits and disadvantages. Be careful when defining indexes and take into consideration costs associated with update, delete, and insert operations and maintenance, such as reorganization and recovery.
  • 44.
    18 MySQL toDB2 Conversion Guide Figure 1-9 Relationship between indexes and tables 1.3.3 DB2 catalog In DB2, the metadata is stored in a set of base tables and views called the catalog. The catalog contains information about the logical and physical structure of the database objects, object privileges, integrity information, and more. The catalog is automatically created with the database. The base tables are owned by the SYSIBM schema and stored in the SYSCATSPACE table space. On top of the base tables, the SYSCAT and SYSSTAT views are created. SYSCAT views are the read-only views that contain the object information and are found in the SYSCAT schema. SYSSTAT views are views, which you can update, that contain statistical information that is found in the SYSTAT schema. You can obtain the complete DB2 catalog views in DB2 SQL Reference Volume 1 and 2 available for download under the following link: http://www.ibm.com/support/docview.wss?rs=71&uid=swg27015148 1.4 DB2 utilities All DB2 system commands are installed in the sqllib/bin directory during installation. Several of the most important commands in DB2 are listed in Table 1-1 on page 19, Table 1-2 on page 19, Table 1-3 on page 20, Table 1-4 on page 20, and Table 1-5 on page 20. You can obtain more information in the DB2 manuals, especially the Command Reference that is available at this link: http://www.ibm.com/support/docview.wss?rs=71&uid=swg27015148
  • 45.
    Chapter 1. DB2for Linux, UNIX, and Windows 19 Table 1-1 DB2 instance commands Table 1-2 Handling general database tasks Command Description Example db2start Starts the default instance db2start db2stop Stops the current instance db2stop -f db2icrt Creates an instance db2icrt -u db2fenc1 db2inst1 db2idrop Drops an instance db2idrop -f db2inst1 db2ilist Lists all instance db2ilist db2imigr Converts an instance after upgrading DB2 db2imigr -u db2fenc1 db2inst1 db2iupdt Updates an instance after installation of a fix pack db2iupdt -u db2fenc1 db2inst1 Description Example Deactivates a database db2 deactivate db mydb Views database manager settings db2 get dbm cfg show detail Changes a database manager setting db2 update dbm cfg using health_mon off Views database settings db2 get db cfg show detail Changes a database setting db2 update db cfg using SELF_TUNING_MEM on Views registry values db2set Changes registry parameters db2set DB2AUTOSTART=yes Views cataloged databases db2 list db directory Views cataloged nodes db2 list node directory Lists all connected applications db2 list applications all Forces applications off db2 force application (41408, 55458) Lists utilities db2 list utilities Gets a database snapshot db2 get snapshot for database on mydb
  • 46.
    20 MySQL toDB2 Conversion Guide Table 1-3 DB2 DAS instance commands Table 1-4 Informational commands Table 1-5 Graphical tools Command Description Example db2admin Starts and stops the DB2 Administration Server db2admin start dasauto Autostarts DB2 Administration Server dasauto -on dascrt Creates a DB2 Administration Server dascrt -u dasusr1 dasdrop Removes a DB2 Administration Server dasdrop dasmigr Converts a DB2 Administration Server dasmigr Command Description Example db2level Shows the current version and service level db2level db2look Extracts DDL statements db2look -d dep -a -e -o db2look.sql db2dart Database analysis and reporting tool db2dart dbaddr db2pd Troubleshooting tool db2pd -db sample -locks Tool Command Purpose IBM Data Studio Integrated development environment package, which includes all administrative capabilities, as well as an integrated Eclipse development environment for Java, XML, and Web services Optim Development Studio A purchasable integrated development environment for advanced development of DB2 databases Optim Database Administrator A purchasable integrated development environment for advanced administration of DB2 databases DB2 installer db2setup Installs DB2 and creates instances DB2 instance installer db2isetup Creates instances Control Center db2cc Administers instances, databases Replication Center db2rc Administers replication between servers Satellite Admin. Center db2cc Administers collections of DB2 servers
  • 47.
    Chapter 1. DB2for Linux, UNIX, and Windows 21 1.5 DB2 database access IBM introduced common application development and tooling support for DB2 for Linux, UNIX, and Windows (as of 9.5), DB2 for z/OS, and Informix Dynamic Server. This support generally lowers development costs through reuse and support of common components across IBM data servers. More information to application development, tools, clients, and drivers can be obtained at this link: http://www.ibm.com/software/data/db2/ad/ and http://www.ibm.com/developerworks/db2/library/techarticle/dm-0804zikopoulos 1.5.1 DB2 clients and drivers DB2 clients and drivers are used to access databases that reside on DB2 servers. A database cannot be created on a DB2 client. IBM data server clients DB2 Data Server 9.7 comes with the following clients: 򐂰 IBM Data Server Runtime Client 򐂰 IBM Data Server Client Command line processor (CLP) db2 or db2cmd Executes DB2 commands at the command line Health Center db2hc Views/resolves health monitor alerts Task Center db2cc –tc Schedules, runs tasks; notifies contacts Journal db2cc -j Monitors jobs, recovery history, and so on Configuration Assistant db2ca Configures instances and databases Note: The Control Center and its associated components have been deprecated in Version 9.7 and might be removed in a future release. We recommend that you use the new suite of GUI tools for managing DB2 data and data-centric applications. These new tools include the IBM Data Studio, the Optim Development Studio, and the Optim Database Administrator.
  • 48.
    22 MySQL toDB2 Conversion Guide The IBM Data Server Runtime client offers the basic client functionality and includes drivers for ODBC, CLI, ADO.NET, Object Linking and Embedding (OLE) DB, PHP, Ruby, Perl-DB2, JDBC, and SQLJ. This client already includes the drivers and the capabilities to define data sources. Furthermore, the Lightweight Directory Access Protocol (LDAP) is available, as well. Additionally, the IBM Data Server Client provides vast amounts of sample code in various languages, header files for application development and graphical administration and development tools, such as the DB2 Control Center, the IBM Data Studio, the MS® Visual Studio® Tools, and more. Figure 1-10 illustrates how to connect to a DB2 data server using the IBM data server clients. Figure 1-10 DB2 9.7 client and drivers IBM Optim Data Studio is a comprehensive suite of integrated Eclipse tools geared toward both database developers and database administrators. It reduces the time to perform day-to-day administration tasks, create, deploy, and debug SQL and Java stored procedures, deploy data-centric Web services, and create queries for relational and XML data using SQL and XQuery. It supports multiple IBM data servers, including DB2 for Linux, UNIX, and Windows, DB2 for i5/OS® and z/OS, Apache Derby, and the Informix Dynamic Server. Because it is
  • 49.
    Chapter 1. DB2for Linux, UNIX, and Windows 23 built on the extensible Eclipse framework, this IDE includes a number of plug-ins to support programming languages, such as Java, C/C++, PHP, Ruby, Perl, and so on. Other plug-ins are available to maintain the written application sources in various source code repositories, for example, the Concurrent Versions System (CVS) or IBM Rational® ClearCase® from within IBM Optim Data Studio. If running mixed versions of DB2 servers and clients, it is good to know that DB2 Clients from DB2 UDB Version 8 and DB2 9.1 or 9.5 for Linux, UNIX, and Windows are still supported and able to connect to a DB2 9.7 data server. In the reverse direction, the newer IBM data server clients from Version 9.7 can also connect to the earlier DB2 9.1 and DB2 UDB Version 8 servers using the IBM Data Server Driver for ODBC, CLI, and .Net. In this case, however, new DB2 Version 9.7 functionality is not available. IBM data server drivers The IBM data server drivers include the products: 򐂰 IBM Data Server Driver for JDBC and SQLJ 򐂰 IBM Data Server Driver for ODBC, CLI, and .NET As of DB2 Version 9.5, both clients and drivers are decoupled from the server release schedule and can be downloaded separately. The IBM Data Server Driver for JDBC and SQLJ is already included in the IBM Data Server Runtime Client. It provides support for JDBC 3 and 4 compliant applications, as well as for Java applications using static SQL (SQLJ). Support is also provided for pureXML, SQL/XML, and XQuery. All of this support and other features, such as connection concentration, automatic client reroute, and more, are provided within in a single package called db2jcc4.jar. The IBM Data Server Driver for ODBC, CLI and .Net is a lightweight deployment solution for Windows applications to provide runtime support for applications without needing to install the Data Server client or the Data Server Runtime Client. On Windows, the driver comes as an installable image including merge modules to easily embed it in a Windows installer-based installation. On Linux and UNIX, there is another easy deployment solution called the IBM Data Server Driver for ODBC and CLI, which is available in tar format. Communication protocols DB2 primarily uses these protocols to communicate: 򐂰 TCP, IPv4, IPv6, and Named Pipes (Windows only) for remote connections 򐂰 Interprocess Communication (IPC) for local connections within a DB2 instance
  • 50.
    24 MySQL toDB2 Conversion Guide For client/server communication, DB2 supports TCP/IP and Named Pipes for remote or local loopback connections and uses IPC for client connections, which are local to the DB2 server instance. Local and remote DB2 connections are illustrated in Figure 1-11. Figure 1-11 Client connection scenario Protocols are automatically detected and configured during an instance creation. The DB2COMM registry variable identifies the protocol detected in a server. To enable a specific protocol on the server, the db2set db2comm command must be executed. For TCP/IP, a unique port address has to be specified in the database manager configuration. This port is registered in the services file (usually /etc/services on UNIX and Linux). For example, to reserve port 50000 with the service name db2icdb2, the entry in the services file is: db2icdb2 50000/tcp From the command line, this information can be then updated in the database manager with the following DB2 command: db2 UPDATE DBM CFG USING SVCENAME db2icdb2 These tasks can also be performed using the DB2 Configuration Assistant utility. Client/server configuration methods The following methods are available for client/server configuration: 򐂰 Statically using command line processor (CLP) or Configuration Assistant (Discovery Service available) 򐂰 Dynamically using Lightweight Directory Access Protocol (LDAP) At the client side, the database information is configured using either the CATALOG command or using the Configuration Assistant. The databases are
  • 51.
    Chapter 1. DB2for Linux, UNIX, and Windows 25 configured under a node, which describes host information, such as protocol use, port number, and so on. To configure a remote TCP/IP node, use the following command: db2 CATALOG TCPIP NODE node-name REMOTE host-name SERVER service-name The service name registered in the server or the port number can be specified in the SERVER option. To catalog a database under this node, the command used is: db2 CATALOG DATABASE database-name AS alias-name AT NODE node-name When using the Configuration Assistant GUI tool to add a database connection, a database discovery can be started to find the desired database. For information about how to enable and configure DB2 in an LDAP environment, have a look at the Lightweight Directory Access Protocol (LDAP) section within the “Database fundamentals” chapter in the DB2 Information Center. The DB2 Information Center contains searchable and structured Web pages and can be installed locally. Additionally, the Information Center is also publicly available online at this Web site: http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp 1.5.2 Application access You can use various methods when deploying applications with DB2 Data Server 9.7: 򐂰 Single-tier In this configuration, the application and the database reside on the same system. In enterprise environments, it can be rare to see such a configuration, because remote access to a database server is typically required. Nonetheless, this method is quite common for developing applications that can later be deployed transparently in a multi-tier DB2 environment without any changes or batch applications. 򐂰 Client/Server or 2-tier The application and the database reside on separate systems. The machines where the application runs typically have a DB2 client installed, which communicates over the network to a database server. For the application, the physical location of the data is transparent. The application communicates with the DB2 client using a standard interface (for example, ODBC) and the Note: DB2 Discovery method is enabled at the instance level using the DISCOVER_INST parameter, and at database level using the DISCOVER_DB parameter.
  • 52.
    26 MySQL toDB2 Conversion Guide DB2 client takes over the task of accessing the data over the network. In certain cases, such as browser-based access or Java-based access, it is not necessary to have the DB2 client running on the same machine where the application executes. DB2 provides exceptional flexibility for mixing and matching client and server platforms in a heterogeneous environment. DB2 client and server code is available for a wide variety of platforms. For example, the application can execute on a Windows-based machine with a DB2 client for Windows, which can then access a DB2 database on a Linux server. Likewise, the Linux machine can act as a client and access data from UNIX servers or mainframes. 򐂰 Multi-tier In a multi-tier configuration, the application, DB2 client, and the data source typically reside on separate systems. Table 1-6 provides examples of these configuration scenarios. Table 1-6 Multi-tier configuration examples IBM recognizes that in many cases there might be a need for accessing data from a variety of distributed data sources rather than one centralized database. The data sources can be from IBM, such as DB2 or Informix, from non-IBM databases, such as Oracle®, or even from non-relational data, such as files or spreadsheets. As illustrated in the last scenario in Table 1-6, IBM offers the most comprehensive business integration solution by allowing federated access to a variety of distributed data sources. 1.5.3 DB2 application programming interfaces In order to access or manage DB2 objects, several application programming interfaces (APIs) can be used, as shown in Figure 1-12 on page 27. Client Middle-tier Server Web-browser Web server DB2 database Application client Application server DB2 client DB2 database server 1 DB2 database server 2 Application DB2 Client DB2 Connect Gateway System z®, System i® Application DB2 Client DB2 server Secondary data sources (for example, Mainframe DB2, Non-DB2, and non-relational)
  • 53.
    Chapter 1. DB2for Linux, UNIX, and Windows 27 Figure 1-12 Application connections to DB2 DB2 administrative application programming interface DB2 provides numerous administrative APIs, which allow applications to perform database administration tasks available in DB2 Control Center, such as importing and exporting data, or creating, activating, backing up, or restoring a database. These calls can be included within embedded SQL and DB2 CLI applications. Examples of API programs can be found in the DB2 home directory sqllib/sample/ for various programming languages. For additional information, refer to DB2 Administrative API Reference, SC23-5824. Embedded SQL statements in applications Two types of SQL statements have to be distinguished: 򐂰 Static SQL statements With static SQL statements, the SQL statement type and the table and column names are known before compilation time. The specific data values for which the statement is searching are not known. These values can be represented in host language variables. Before compiling and linking the program, pre-compiling and binding of the embedded SQL statements must be performed. Pre-compiling converts
  • 54.
    28 MySQL toDB2 Conversion Guide embedded SQL statements into DB2 runtime API calls that a host compiler can process to create a bind file. The bind command creates a package in the database. This package then contains the SQL operation and the access plan that DB2 will use to perform the operations. 򐂰 Dynamic SQL Dynamic SQL statements in an application are built and executed at run time. For a dynamically prepared SQL statement, the syntax has to be checked and an access plan has to be generated during the program execution. Examples of embedded static and dynamic SQL can be found in the DB2 home directory: sqllib/samples/. DB2 call level interface DB2 call level interface (DB2 CLI) is a programming interface that can be used from C and C++ applications to access DB2 data servers. DB2 CLI is based on the Microsoft® Open Database Connectivity (ODBC) specification and the International Organization for Standardization (ISO) CLI standard. The DB2 CLI library can be loaded as an ODBC driver by an ODBC driver manager. DB2 CLI includes support for many ODBC and ISO SQL/CLI functions, as well as DB2 specific functions. Figure 1-13 illustrates the ODBC driver manager environment and the DB2 CLI environment. Figure 1-13 ODBC/CLI driver connectivity
  • 55.
    Random documents withunrelated content Scribd suggests to you:
  • 56.
    it were asword in his heart, but at her look and gesture—her frightened look, her imploring gesture—the sword was turned and still he was alive, alive to watch her die. In those moments his soul left time and space, and hung a tortured point, infinitely lonely, infinitely agonised, in some illimitable region of never-ending pain. There was no past, no future, only Eternity and his undying soul in anguish. The thousand years were as a day, and the day as a thousand years. There was no beginning and no end. O God, no end! He did not hear the crowd stir a little, and drift hither and thither as it was pressed upon from one side; he did not see the gendarmes press against the drift, only to be driven back again, hustled, surrounded so that their horses were too hampered to answer to the spur. Suddenly a woman went down screaming under the horses' feet, and on the instant the crowd flamed into fury before the agonised shriek had died away. In a moment all was a seething, shouting, cursing welter of struggling humanity. The noise of it reached even Dangeau's stunned brain, and he said within himself, "It is over. She is dead," and opened his eyes. The scaffold stood like an island in a sea grown suddenly wild with tempest, and even as he looked, the human waves of it broke in a fierce swirl which welled up and overflowed it on every side. Sanson, his hand on the machinery, was whirled aside, jostled, pushed, cursed. A fat woman, with bare, mottled arms, Heaven knows how she came on the platform, dealt him a resounding smack on the face, and shrieked voluble abuse, which was freely echoed. Dangeau was surrounded, embraced, cheered, lifted off his feet, the cord that bound his arms slashed through, and of a sudden
  • 57.
    Goyot had himround the neck, and he found voice and clamoured Aline's name. The little surgeon, after one glance at his wild eyes, pushed with him through the surging press; they had to fight their way, and the place was slippery, but they were through at last, through and down on their knees by the woman who lay bound beneath the knife that Sanson's hand was freeing when the tumult caught him. A dozen hands snatched her back again now, the cords were cut, and Dangeau's shaking voice called in her ears, called loudly, and in vain. "Air, give her air and room," he cried, and some pushed forwards and others back. The fat woman took the girl's head upon her lap, whilst tears rained down her crimson cheeks. "Eh, the poor pretty one," she sobbed hysterically, and pulled off her own ample kerchief to cover Aline's thin bosom. Dangeau leaned over her calling, calling still, unaware of Goyot at his side, and of Goyot's voice saying insistently, "Tiens, my friend, that was a near shave, eh?" "My wife," he muttered, "my wife—my wife is dead," and with that he gazed round wildly, cried "No, no!" in a sharp voice, and fell to calling her again. Goyot knelt on the reeking boards, caught the frail wrist in that brown skilful hand of his, shifted his grasp once, twice, a third time, shook his head, and took another grip. "No, she 's alive," he said at last, and had to say it more than once, for Dangeau took no heed. "Aline! Aline! Aline!" he called in hoarse, trembling tones, and Goyot dropped the girl's wrist and took him harshly by the shoulder. "Rouse, man, rouse!" he cried. "She's alive. I tell you. I swear it. For the love of Heaven, wake up, and help me to get her away. It's
  • 58.
    touch and gofor all of us these next few hours. At any moment Henriot may have the upper hand, and half an hour would do our business, with this pretty toy so handy." He grimaced at the red axe above them, "Come, Dangeau, play the man!" Dangeau stared at him. "What am I to do?" he asked irritably. Goyot pressed his shoulder with a firm hand. "Lift your wife, and bring her along after me. Can you manage? She looks light enough." It was no easy matter to come through the excited crowd, but Dangeau's height told, and with Aline's head against his shoulder he pushed doggedly in the wake of Goyot, who made his way through the press with a wonderful agility. Down the steps now, and inch by inch forward through the jostling excited people. Up a by way at last, and then sharp to the left where a carriage waited, and with that Goyot gave a gasp of relief, and mopped a dripping brow. "Eh, mon Dieu!" he said; "get in, get in!" The carriage had mouldy straw on the floor, and the musty odour of it mounted in the hot air. Dangeau complained of it sharply. "A devil of a smell, this, Goyot!" and the little surgeon fixed him with keen, watchful eyes, as he nodded acquiescence. What house they came to, or how they came to it, Dangeau knew no more than his unconscious wife. She lay across his breast, white and still as the dead, and when he laid her down on the bed in the upper room they reached at last, she fell limply from his grasp, and he turned to Goyot with a groan.
  • 59.
    A soft, white-hairedwoman, dark-eyed and placid,—afterwards he knew her for Goyot's housekeeper,—tried to turn him out of the room, but he would go no farther than the window, where he sat staring, staring at the houses across the way, watching them darken in the gathering dusk, and mechanically counting the lights that presently sprang into view. Behind him Marie Carlier came and went, at Goyot's shortly worded orders, until at last Dangeau's straining ears caught the sound of a faint, fluttering sigh. He turned then, the lights in the room dancing before his burning eyes. For a moment the room seemed full of the small tongues of flame, and then beyond them he saw his wife's eyes open again, whilst her hand moved in feeble protest against the draught which Goyot himself was holding to her lips. Dangeau got up, stood a moment gazing, and then stumbled from the room and broke into heavy sobbing. Presently Goyot brought him something in a glass, which he drank obediently. "Now you will sleep," said the little man in cheerful accents, and sleep he did, and never stirred until the high sun struck across his face and waked him to France's new day, and his. For in that night fell Robespierre, cast down by the Convention he had dominated so long. The dawn that found him shattered, praying for the death he had vainly sought, awakened Paris from the long nightmare which had been the marriage gift of her nuptials with this incubus. At four o'clock on the afternoon of the 10th Thermidor, Robespierre's head fell under the bloody axe of the Terror, and with
  • 60.
    his last gaspthe life went out of the greatest tyranny of modern times. When Goyot came home with the news, Dangeau's face flamed, and he put his hand before his eyes for a moment. Then he went up to Aline. She had lain in a deep sleep for many, many hours, but towards the afternoon she had wakened, taken food, and dressed herself, all in a strange, mechanical fashion. She was neither to be gainsaid nor persuaded, and Dangeau, reasonable once more, had left her to the kind and unexciting ministrations of Marie Carlier. Now he could keep away no longer; Goyot followed him and the housekeeper met them by the door. "She is strange, Monsieur," she whispered. "She has not roused at all?" inquired Goyot rather anxiously. Marie shook her head. "She just sits and stares at the sky. God knows what she sees there, poor lamb. If she would weep——" "Just so, just so," Goyot nodded once or twice. Then he turned a penetrating look on Dangeau. "Ha, you are all right again. A near thing, my friend, eh? Small wonder you were upset by it." "Oh, I!" said Dangeau, with an impatient gesture. "It is my wife we are speaking of." "Yes, yes, of course—a little patience, my dear Dangeau—yes, your wife. Marie here, without being scientific, is a sensible woman, and it's a wonderful thing how common-sense comes to the same conclusions as science. A fascinating subject that, but, as you are about to observe, this is not the time to pursue it. What I mean to say is, that your wife is suffering from severe shock; her brain is
  • 61.
    overcharged, and Marieis quite right when she suggests that tears would relieve it. Now, my good Dangeau, do you think you can make your wife cry?" "I don't know—I must go to her." "Well, well, go. Don't excite her, but—dear me, Marie, how impatient people are. When one has saved a man's life, he might at least let one finish a sentence, instead of breaking away in the middle of it. Get me something to eat, for, parbleu, I 've earned it." Dangeau had closed the door, and stood looking at his wife. "Aline," he said, "have they told you? We are safe—Robespierre is dead." Then he threw back his head, took a long, deep breath, and cried: "It is new life—new life for France, new work for those who love her—new life for us—for us, Aline." Aline stood by the window, very still. At the sound of Dangeau's voice she turned her head. He saw that she was smiling, and his heart contracted as he looked at her. Death had come so close to her, so very close, that it seemed to him the shadow of it lay cold and still above that strange unchanging smile; and he called to her abruptly, with a rough tenderness. "Aline! Aline!" She looked up then, and he saw then the same smile lie deep within her eyes. Unfathomably peaceful they were, but not with the peace of the living. "Won't you come to me, my dear," he said gently, and with the simplicity he would have used to a child.
  • 62.
    A little shiverjust stirred the stillness of her form, and she came slowly, very slowly, across the room, and then stood waiting, and with a sudden passion Dangeau laid both hands upon her shoulders insistently, heavily. He wondered had she lost the memory of the last time he had touched and held her thus. Then he had fought with pride and been defeated. Now he must fight again, fight for her very soul and reason, and this time he must win, or the whole world would be lost. He paused, gathering all the forces of his soul, then looked at her with passionate uneasiness. If she would tremble, if she would even shrink from him— anything but that calm which was there, and shone serenely fixed, like the smile upon the faces of the dead. It hinted of the final secret known. "Mon Dieu! Aline, don't look like that!" he cried, and in strong protest his arms slipped lower, and drew her close to his heart that beat, and beat, as if it would supply the life hers lacked. She came passively at his touch, and stood in his embrace unresisting and unresponsive. Remembering how she had flushed at a look and quivered at a touch, his fears redoubled, and he caught her close, and closer, kissing her, at first gently, but in the end with all the force of a passion so long restrained. For now at last the dam was down, and they stood together in love's full flowing tide. When he drew back, the smile was gone, and the lips that it had left trembled piteously, as her colour came and went to each quickened breath.
  • 63.
    "Aline," he said,very low, "Aline, my heart! It is new life—new life together." She pushed him back a pace then, and raised her eyes with a look he never forgot. The peace had left them now, and they were troubled to the depths, and brimmed with tears. Her lips quivered more and more, the breath came from them in a great sob, and suddenly she fell upon his breast in a passion of weeping.
  • 64.
    THE END *** ENDOF THIS PROJECT GUTENBERG EBOOK A MARRIAGE UNDER THE TERROR ***
  • 66.
    *** END OFTHE PROJECT GUTENBERG EBOOK A MARRIAGE UNDER THE TERROR *** Updated editions will replace the previous one—the old editions will be renamed. Creating the works from print editions not protected by U.S. copyright law means that no one owns a United States copyright in these works, so the Foundation (and you!) can copy and distribute it in the United States without permission and without paying copyright royalties. Special rules, set forth in the General Terms of Use part of this license, apply to copying and distributing Project Gutenberg™ electronic works to protect the PROJECT GUTENBERG™ concept and trademark. Project Gutenberg is a registered trademark, and may not be used if you charge for an eBook, except by following the terms of the trademark license, including paying royalties for use of the Project Gutenberg trademark. If you do not charge anything for copies of this eBook, complying with the trademark license is very easy. You may use this eBook for nearly any purpose such as creation of derivative works, reports, performances and research. Project Gutenberg eBooks may be modified and printed and given away—you may do practically ANYTHING in the United States with eBooks not protected by U.S. copyright law. Redistribution is subject to the trademark license, especially commercial redistribution. START: FULL LICENSE
  • 67.
    THE FULL PROJECTGUTENBERG LICENSE
  • 68.
    PLEASE READ THISBEFORE YOU DISTRIBUTE OR USE THIS WORK To protect the Project Gutenberg™ mission of promoting the free distribution of electronic works, by using or distributing this work (or any other work associated in any way with the phrase “Project Gutenberg”), you agree to comply with all the terms of the Full Project Gutenberg™ License available with this file or online at www.gutenberg.org/license. Section 1. General Terms of Use and Redistributing Project Gutenberg™ electronic works 1.A. By reading or using any part of this Project Gutenberg™ electronic work, you indicate that you have read, understand, agree to and accept all the terms of this license and intellectual property (trademark/copyright) agreement. If you do not agree to abide by all the terms of this agreement, you must cease using and return or destroy all copies of Project Gutenberg™ electronic works in your possession. If you paid a fee for obtaining a copy of or access to a Project Gutenberg™ electronic work and you do not agree to be bound by the terms of this agreement, you may obtain a refund from the person or entity to whom you paid the fee as set forth in paragraph 1.E.8. 1.B. “Project Gutenberg” is a registered trademark. It may only be used on or associated in any way with an electronic work by people who agree to be bound by the terms of this agreement. There are a few things that you can do with most Project Gutenberg™ electronic works even without complying with the full terms of this agreement. See paragraph 1.C below. There are a lot of things you can do with Project Gutenberg™ electronic works if you follow the terms of this agreement and help preserve free future access to Project Gutenberg™ electronic works. See paragraph 1.E below.
  • 69.
    1.C. The ProjectGutenberg Literary Archive Foundation (“the Foundation” or PGLAF), owns a compilation copyright in the collection of Project Gutenberg™ electronic works. Nearly all the individual works in the collection are in the public domain in the United States. If an individual work is unprotected by copyright law in the United States and you are located in the United States, we do not claim a right to prevent you from copying, distributing, performing, displaying or creating derivative works based on the work as long as all references to Project Gutenberg are removed. Of course, we hope that you will support the Project Gutenberg™ mission of promoting free access to electronic works by freely sharing Project Gutenberg™ works in compliance with the terms of this agreement for keeping the Project Gutenberg™ name associated with the work. You can easily comply with the terms of this agreement by keeping this work in the same format with its attached full Project Gutenberg™ License when you share it without charge with others. 1.D. The copyright laws of the place where you are located also govern what you can do with this work. Copyright laws in most countries are in a constant state of change. If you are outside the United States, check the laws of your country in addition to the terms of this agreement before downloading, copying, displaying, performing, distributing or creating derivative works based on this work or any other Project Gutenberg™ work. The Foundation makes no representations concerning the copyright status of any work in any country other than the United States. 1.E. Unless you have removed all references to Project Gutenberg: 1.E.1. The following sentence, with active links to, or other immediate access to, the full Project Gutenberg™ License must appear prominently whenever any copy of a Project Gutenberg™ work (any work on which the phrase “Project
  • 70.
    Gutenberg” appears, orwith which the phrase “Project Gutenberg” is associated) is accessed, displayed, performed, viewed, copied or distributed: This eBook is for the use of anyone anywhere in the United States and most other parts of the world at no cost and with almost no restrictions whatsoever. You may copy it, give it away or re- use it under the terms of the Project Gutenberg License included with this eBook or online at www.gutenberg.org. If you are not located in the United States, you will have to check the laws of the country where you are located before using this eBook. 1.E.2. If an individual Project Gutenberg™ electronic work is derived from texts not protected by U.S. copyright law (does not contain a notice indicating that it is posted with permission of the copyright holder), the work can be copied and distributed to anyone in the United States without paying any fees or charges. If you are redistributing or providing access to a work with the phrase “Project Gutenberg” associated with or appearing on the work, you must comply either with the requirements of paragraphs 1.E.1 through 1.E.7 or obtain permission for the use of the work and the Project Gutenberg™ trademark as set forth in paragraphs 1.E.8 or 1.E.9. 1.E.3. If an individual Project Gutenberg™ electronic work is posted with the permission of the copyright holder, your use and distribution must comply with both paragraphs 1.E.1 through 1.E.7 and any additional terms imposed by the copyright holder. Additional terms will be linked to the Project Gutenberg™ License for all works posted with the permission of the copyright holder found at the beginning of this work.
  • 71.
    1.E.4. Do notunlink or detach or remove the full Project Gutenberg™ License terms from this work, or any files containing a part of this work or any other work associated with Project Gutenberg™. 1.E.5. Do not copy, display, perform, distribute or redistribute this electronic work, or any part of this electronic work, without prominently displaying the sentence set forth in paragraph 1.E.1 with active links or immediate access to the full terms of the Project Gutenberg™ License. 1.E.6. You may convert to and distribute this work in any binary, compressed, marked up, nonproprietary or proprietary form, including any word processing or hypertext form. However, if you provide access to or distribute copies of a Project Gutenberg™ work in a format other than “Plain Vanilla ASCII” or other format used in the official version posted on the official Project Gutenberg™ website (www.gutenberg.org), you must, at no additional cost, fee or expense to the user, provide a copy, a means of exporting a copy, or a means of obtaining a copy upon request, of the work in its original “Plain Vanilla ASCII” or other form. Any alternate format must include the full Project Gutenberg™ License as specified in paragraph 1.E.1. 1.E.7. Do not charge a fee for access to, viewing, displaying, performing, copying or distributing any Project Gutenberg™ works unless you comply with paragraph 1.E.8 or 1.E.9. 1.E.8. You may charge a reasonable fee for copies of or providing access to or distributing Project Gutenberg™ electronic works provided that: • You pay a royalty fee of 20% of the gross profits you derive from the use of Project Gutenberg™ works calculated using the method you already use to calculate your applicable taxes. The fee is owed to the owner of the Project Gutenberg™ trademark,
  • 72.
    but he hasagreed to donate royalties under this paragraph to the Project Gutenberg Literary Archive Foundation. Royalty payments must be paid within 60 days following each date on which you prepare (or are legally required to prepare) your periodic tax returns. Royalty payments should be clearly marked as such and sent to the Project Gutenberg Literary Archive Foundation at the address specified in Section 4, “Information about donations to the Project Gutenberg Literary Archive Foundation.” • You provide a full refund of any money paid by a user who notifies you in writing (or by e-mail) within 30 days of receipt that s/he does not agree to the terms of the full Project Gutenberg™ License. You must require such a user to return or destroy all copies of the works possessed in a physical medium and discontinue all use of and all access to other copies of Project Gutenberg™ works. • You provide, in accordance with paragraph 1.F.3, a full refund of any money paid for a work or a replacement copy, if a defect in the electronic work is discovered and reported to you within 90 days of receipt of the work. • You comply with all other terms of this agreement for free distribution of Project Gutenberg™ works. 1.E.9. If you wish to charge a fee or distribute a Project Gutenberg™ electronic work or group of works on different terms than are set forth in this agreement, you must obtain permission in writing from the Project Gutenberg Literary Archive Foundation, the manager of the Project Gutenberg™ trademark. Contact the Foundation as set forth in Section 3 below. 1.F.
  • 73.
    1.F.1. Project Gutenbergvolunteers and employees expend considerable effort to identify, do copyright research on, transcribe and proofread works not protected by U.S. copyright law in creating the Project Gutenberg™ collection. Despite these efforts, Project Gutenberg™ electronic works, and the medium on which they may be stored, may contain “Defects,” such as, but not limited to, incomplete, inaccurate or corrupt data, transcription errors, a copyright or other intellectual property infringement, a defective or damaged disk or other medium, a computer virus, or computer codes that damage or cannot be read by your equipment. 1.F.2. LIMITED WARRANTY, DISCLAIMER OF DAMAGES - Except for the “Right of Replacement or Refund” described in paragraph 1.F.3, the Project Gutenberg Literary Archive Foundation, the owner of the Project Gutenberg™ trademark, and any other party distributing a Project Gutenberg™ electronic work under this agreement, disclaim all liability to you for damages, costs and expenses, including legal fees. YOU AGREE THAT YOU HAVE NO REMEDIES FOR NEGLIGENCE, STRICT LIABILITY, BREACH OF WARRANTY OR BREACH OF CONTRACT EXCEPT THOSE PROVIDED IN PARAGRAPH 1.F.3. YOU AGREE THAT THE FOUNDATION, THE TRADEMARK OWNER, AND ANY DISTRIBUTOR UNDER THIS AGREEMENT WILL NOT BE LIABLE TO YOU FOR ACTUAL, DIRECT, INDIRECT, CONSEQUENTIAL, PUNITIVE OR INCIDENTAL DAMAGES EVEN IF YOU GIVE NOTICE OF THE POSSIBILITY OF SUCH DAMAGE. 1.F.3. LIMITED RIGHT OF REPLACEMENT OR REFUND - If you discover a defect in this electronic work within 90 days of receiving it, you can receive a refund of the money (if any) you paid for it by sending a written explanation to the person you received the work from. If you received the work on a physical medium, you must return the medium with your written explanation. The person or entity that provided you with the defective work may elect to provide a replacement copy in lieu
  • 74.
    of a refund.If you received the work electronically, the person or entity providing it to you may choose to give you a second opportunity to receive the work electronically in lieu of a refund. If the second copy is also defective, you may demand a refund in writing without further opportunities to fix the problem. 1.F.4. Except for the limited right of replacement or refund set forth in paragraph 1.F.3, this work is provided to you ‘AS-IS’, WITH NO OTHER WARRANTIES OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO WARRANTIES OF MERCHANTABILITY OR FITNESS FOR ANY PURPOSE. 1.F.5. Some states do not allow disclaimers of certain implied warranties or the exclusion or limitation of certain types of damages. If any disclaimer or limitation set forth in this agreement violates the law of the state applicable to this agreement, the agreement shall be interpreted to make the maximum disclaimer or limitation permitted by the applicable state law. The invalidity or unenforceability of any provision of this agreement shall not void the remaining provisions. 1.F.6. INDEMNITY - You agree to indemnify and hold the Foundation, the trademark owner, any agent or employee of the Foundation, anyone providing copies of Project Gutenberg™ electronic works in accordance with this agreement, and any volunteers associated with the production, promotion and distribution of Project Gutenberg™ electronic works, harmless from all liability, costs and expenses, including legal fees, that arise directly or indirectly from any of the following which you do or cause to occur: (a) distribution of this or any Project Gutenberg™ work, (b) alteration, modification, or additions or deletions to any Project Gutenberg™ work, and (c) any Defect you cause.
  • 75.
    Section 2. Informationabout the Mission of Project Gutenberg™ Project Gutenberg™ is synonymous with the free distribution of electronic works in formats readable by the widest variety of computers including obsolete, old, middle-aged and new computers. It exists because of the efforts of hundreds of volunteers and donations from people in all walks of life. Volunteers and financial support to provide volunteers with the assistance they need are critical to reaching Project Gutenberg™’s goals and ensuring that the Project Gutenberg™ collection will remain freely available for generations to come. In 2001, the Project Gutenberg Literary Archive Foundation was created to provide a secure and permanent future for Project Gutenberg™ and future generations. To learn more about the Project Gutenberg Literary Archive Foundation and how your efforts and donations can help, see Sections 3 and 4 and the Foundation information page at www.gutenberg.org. Section 3. Information about the Project Gutenberg Literary Archive Foundation The Project Gutenberg Literary Archive Foundation is a non- profit 501(c)(3) educational corporation organized under the laws of the state of Mississippi and granted tax exempt status by the Internal Revenue Service. The Foundation’s EIN or federal tax identification number is 64-6221541. Contributions to the Project Gutenberg Literary Archive Foundation are tax deductible to the full extent permitted by U.S. federal laws and your state’s laws. The Foundation’s business office is located at 809 North 1500 West, Salt Lake City, UT 84116, (801) 596-1887. Email contact
  • 76.
    links and upto date contact information can be found at the Foundation’s website and official page at www.gutenberg.org/contact Section 4. Information about Donations to the Project Gutenberg Literary Archive Foundation Project Gutenberg™ depends upon and cannot survive without widespread public support and donations to carry out its mission of increasing the number of public domain and licensed works that can be freely distributed in machine-readable form accessible by the widest array of equipment including outdated equipment. Many small donations ($1 to $5,000) are particularly important to maintaining tax exempt status with the IRS. The Foundation is committed to complying with the laws regulating charities and charitable donations in all 50 states of the United States. Compliance requirements are not uniform and it takes a considerable effort, much paperwork and many fees to meet and keep up with these requirements. We do not solicit donations in locations where we have not received written confirmation of compliance. To SEND DONATIONS or determine the status of compliance for any particular state visit www.gutenberg.org/donate. While we cannot and do not solicit contributions from states where we have not met the solicitation requirements, we know of no prohibition against accepting unsolicited donations from donors in such states who approach us with offers to donate. International donations are gratefully accepted, but we cannot make any statements concerning tax treatment of donations received from outside the United States. U.S. laws alone swamp our small staff.
  • 77.
    Please check theProject Gutenberg web pages for current donation methods and addresses. Donations are accepted in a number of other ways including checks, online payments and credit card donations. To donate, please visit: www.gutenberg.org/donate. Section 5. General Information About Project Gutenberg™ electronic works Professor Michael S. Hart was the originator of the Project Gutenberg™ concept of a library of electronic works that could be freely shared with anyone. For forty years, he produced and distributed Project Gutenberg™ eBooks with only a loose network of volunteer support. Project Gutenberg™ eBooks are often created from several printed editions, all of which are confirmed as not protected by copyright in the U.S. unless a copyright notice is included. Thus, we do not necessarily keep eBooks in compliance with any particular paper edition. Most people start at our website which has the main PG search facility: www.gutenberg.org. This website includes information about Project Gutenberg™, including how to make donations to the Project Gutenberg Literary Archive Foundation, how to help produce our new eBooks, and how to subscribe to our email newsletter to hear about new eBooks.
  • 78.
    Welcome to ourwebsite – the perfect destination for book lovers and knowledge seekers. We believe that every book holds a new world, offering opportunities for learning, discovery, and personal growth. That’s why we are dedicated to bringing you a diverse collection of books, ranging from classic literature and specialized publications to self-development guides and children's books. More than just a book-buying platform, we strive to be a bridge connecting you with timeless cultural and intellectual values. With an elegant, user-friendly interface and a smart search system, you can quickly find the books that best suit your interests. Additionally, our special promotions and home delivery services help you save time and fully enjoy the joy of reading. Join us on a journey of knowledge exploration, passion nurturing, and personal growth every day! ebookbell.com