Front cover -- Contents -- Notices -- Trademarks -- Preface -- The team that wrote this redbook -- Become a published author -- Comments welcome -- Chapter 1. Introduction -- 1.1 Contents abstract -- 1.2 The project environment -- 1.3 DB2: A high-level overview -- Chapter 2. Architectures -- 2.1 Defining an instance -- 2.1.1 Informix Dynamic Server instance architecture -- 2.1.2 DB2 Universal Database instance architecture -- 2.2 Allocating memory to an instance -- 2.2.1 IDS memory allocation -- 2.2.2 DB2 memory allocation -- 2.3 Processes -- 2.3.1 Context switching -- 2.3.2 IDS processes and threads -- 2.3.3 DB2 processes -- 2.4 Allocating disk space -- 2.4.1 IDS disk allocation -- 2.4.2 DB2 disk allocation -- 2.4.3 Logging -- 2.5 Cost-based optimization -- 2.5.1 IDS query optimization -- 2.5.2 DB2 query optimization -- 2.6 Parallelism -- 2.7 High availability -- Chapter 3. Planning the transition -- 3.1 Tasks and activities -- 3.1.1 Readiness assessment and scope -- 3.1.2 Tool evaluation -- 3.1.3 Estimating project duration -- 3.2 Data conversion -- 3.2.1 Preparation overview -- 3.2.2 Data conversion process -- 3.2.3 Time planning -- 3.2.4 The database structure -- 3.2.5 Data movement approaches -- 3.2.6 DB2 Information Integrator -- 3.2.7 Modifying the application -- 3.2.8 Database objects and interfaces -- 3.3 After the transition -- Chapter 4. Configuration -- 4.1 IDS and DB2 configuration -- 4.1.1 Knobs, configuration, and tuning -- 4.1.2 Commands -- 4.1.3 Granularity -- 4.1.4 Database manager -- 4.1.5 Dynamic parameters -- 4.1.6 Cataloging -- 4.1.7 Client access to DB2 instances -- 4.2 Configuration methods -- 4.2.1 DB2 configuration methods -- 4.2.2 Configuration Advisor and AUTOCONFIGURE -- 4.3 Configuration files and objects overview -- 4.3.1 Environment variables and the profile registry -- 4.3.2 DB2 registry and environment variables.

4.3.3 DB2 configuration files and objects -- 4.3.4 Configuring DB2 clients and servers -- 4.3.5 Configuring a connection using the Configuration Assistant -- 4.3.6 Configuring the client using profiles -- 4.3.7 Automating the rollout of DB2 clients -- 4.4 Client-to-server communications protocols -- 4.4.1 Configuring TCP/IP on the client using the CLP -- 4.4.2 Configuring a client-to-server connection -- 4.4.3 Setting the DB2COMM registry variable -- 4.4.4 Cataloging the TCP/IP node on the client -- 4.4.5 Cataloging databases using the CLP -- 4.4.6 Testing the client to server connection using the CLP -- 4.5 Configuring the instance -- 4.5.1 Page size or sizes -- 4.5.2 Table spaces -- 4.5.3 Buffer pools -- 4.5.4 Logical logs -- 4.5.5 Databases -- Chapter 5. Instance and database operations -- 5.1 Instance operation modes -- 5.1.1 Online mode -- 5.1.2 Offline mode -- 5.1.3 Quiescent mode -- 5.1.4 Creating and dropping the instance -- 5.2 Modifying the configuration -- 5.2.1 Working with the DAS -- 5.2.2 Viewing and updating the configuration using Control Center -- 5.2.3 Managing buffer pools -- 5.3 Managing database storage -- 5.3.1 Table spaces and containers -- 5.3.2 Monitoring table space and container storage -- 5.3.3 Transactions and logs -- 5.4 Backup and recovery -- 5.4.1 Recovery types -- 5.4.2 Backup and restore methods -- 5.5 High availability -- 5.5.1 HADR implementation -- 5.5.2 Log mirroring -- 5.5.3 Replication -- 5.5.4 Online split mirror and suspended I/O support -- Chapter 6. SQL considerations -- 6.1 SELECT issues -- 6.2 ROWID -- 6.3 MATCHES predicate -- 6.4 Comments -- 6.5 Substring notation -- 6.6 SQLCODE and no rows found -- 6.7 SQLSTATE -- 6.8 Built-in functions -- 6.9 SQL access to system catalogs -- 6.10 Quotation marks and character strings -- 6.11 Concatenation behavior -- 6.12 Implicit casting.

6.13 Deferred constraint checking -- 6.14 Set operators: UNION, INTERSECT, and MINUS -- 6.15 Multi-database access -- 6.16 LOAD and UNLOAD statements -- 6.17 Temporary tables -- 6.18 Compound SQL -- 6.19 INSERT cursors -- 6.20 Isolation levels -- 6.21 Optimizer directives -- 6.22 Creating and altering tables -- 6.23 Synonyms -- 6.24 Primary key definitions -- 6.25 Constraint naming -- 6.26 Triggers -- 6.27 DDL usage -- Chapter 7. DB2 Migration Toolkit for Informix -- 7.1 The MTK for Informix -- 7.1.1 Features and functionality -- 7.1.2 Recommendations for use -- 7.2 Technical overview of the MTK -- 7.2.1 The graphical user interface -- 7.2.2 The migration process -- 7.3 How to install and execute the MTK -- 7.3.1 Using the MTK with manual deployment to DB2 UDB -- Chapter 8. An MTK tutorial -- Part I: Core database migration -- 8.1 Create a project -- 8.2 Work with the project -- 8.2.1 Specify Source tab -- 8.2.2 Convert tab -- 8.2.3 Refine tab -- 8.2.4 Other common migration considerations -- 8.2.5 Generating Data Transfer Scripts tab -- 8.2.6 Deploy to DB2 tab -- 8.3 Summary of best practices -- Part II: Database application object migration -- 8.4 Procedure, function, and trigger migration -- Chapter 9. Access methods -- 9.1 Indexing strategies -- 9.1.1 Basic index comparison -- 9.1.2 DB2 index expansions -- 9.1.3 Type I and type II indexes -- 9.1.4 Index reorganization -- 9.1.5 Functional indexes -- 9.2 Advanced access methods -- 9.2.1 Materialized query tables -- 9.2.2 Multidimensional cluster -- 9.3 Optimizer -- 9.3.1 Optimizer analyses -- 9.3.2 Optimizer directives -- Chapter 10. Data types -- 10.1 Object names -- 10.2 Data type mapping -- 10.3 Disk considerations -- 10.4 Character types -- 10.4.1 Truncation -- 10.4.2 NCHAR -- 10.4.3 VARCHAR -- 10.4.4 TEXT -- 10.5 Numerical types -- 10.5.1 Numerical limits -- 10.5.2 DECIMAL -- 10.5.3 MONEY.

10.5.4 SERIAL and SERIAL8 -- 10.6 Date and time types -- 10.6.1 DATE -- 10.6.2 DATETIME, TIME, and TIMESTAMP -- 10.6.3 INTERVAL -- 10.7 LOB data types -- 10.8 BOOLEAN -- 10.9 Collections -- 10.10 SEQUENCE objects -- 10.11 NULL values -- 10.12 FLOAT -- 10.13 REAL or SMALLFLOAT -- 10.14 Limits -- Chapter 11. Extensibility -- 11.1 Introduction -- 11.2 Understanding extensibility -- 11.3 Feature mapping -- 11.4 Terminology -- 11.4.1 IDS -- 11.4.2 DB2 -- 11.5 Available IDS DataBlades and DB2 Extenders -- 11.6 Other resources -- 11.6.1 Tools -- 11.6.2 Guides -- 11.6.3 Training -- 11.6.4 Articles -- 11.6.5 Books -- Chapter 12. Application conversion considerations -- 12.1 Key considerations -- 12.2 Planning -- 12.3 Packages -- 12.3.1 Static and dynamic SQL -- 12.3.2 Bind -- 12.4 Transactions -- 12.4.1 Savepoints -- 12.5 Locks -- 12.5.1 Types of locks -- 12.5.2 Lock escalation -- 12.5.3 Deadlocks -- 12.6 Isolation levels -- 12.6.1 Repeatable Read (RR) -- 12.6.2 Read Stability (RS) -- 12.6.3 Cursor Stability (CS) -- 12.6.4 Uncommitted Read (UR) -- 12.7 Cursors -- 12.7.1 Non-Scroll Cursor -- 12.7.2 Scroll Cursor -- 12.7.3 Update Cursor -- 12.7.4 Insert Cursor -- 12.8 Stored procedures -- 12.8.1 Terminology -- 12.8.2 Languages and interfaces -- 12.8.3 Invocation -- 12.9 Programming language considerations -- 12.9.1 ESQL/C -- 12.9.2 JDBC -- 12.9.3 ODBC/CLI -- 12.9.4 C++ -- 12.9.5 Large objects -- 12.9.6 SQLCA -- 12.9.7 SQLDA -- 12.10 Development integration -- 12.10.1 IBM WebSphere Studio Application Developer -- 12.10.2 Microsoft .NET add-in -- Chapter 13. Security -- 13.1 Concepts -- 13.1.1 Authorities -- 13.1.2 Roles and groups -- 13.1.3 Security levels -- 13.2 Client/server security -- 13.3 Authentication methods -- 13.3.1 LDAP -- 13.3.2 PAM -- Chapter 14. Administrative operations -- 14.1 Performance tuning -- 14.1.1 Buffer pool tuning.

14.1.2 Process tuning -- 14.1.3 Quick-start tips for performance tuning -- 14.2 Tools and wizards included with DB2 -- 14.2.1 Control Center -- 14.2.2 Command Editor -- 14.2.3 Task Center -- 14.2.4 SQL Assist -- 14.2.5 Visual Explain -- 14.2.6 Development Center -- 14.2.7 Configuration Assistant -- 14.2.8 Journal -- 14.2.9 Health Center -- 14.2.10 Replication Center -- 14.2.11 License Center -- 14.2.12 Information Catalog Center -- 14.2.13 Data Warehouse Center -- 14.2.14 Web administration -- 14.2.15 Wizards, advisors, and launchpads -- 14.3 Optional tools -- 14.3.1 DB2 Performance Expert -- 14.3.2 DB2 Recovery Expert -- 14.3.3 DB2 High Performance Unload -- 14.3.4 DB2 Test Database Generator -- 14.3.5 DB2 Table Editor -- 14.3.6 DB2 Web Query Tool -- 14.4 Utilities -- 14.4.1 Exporting and importing data -- 14.4.2 Database reorganization -- 14.4.3 Database statistics -- 14.4.4 Schema extraction -- 14.4.5 Maintaining database integrity -- 14.4.6 Throttling utilities -- 14.4.7 Validating a backup -- 14.5 Other administrative operations -- 14.5.1 Configuring automatic maintenance -- 14.5.2 Working with databases -- 14.5.3 Working with tables -- 14.5.4 Command line processor -- 14.6 Monitoring tools and advisors -- 14.6.1 Health check tools -- 14.6.2 Memory Visualizer -- 14.6.3 Storage Manager -- 14.6.4 Event monitor -- 14.6.5 Snapshots -- 14.6.6 Activity Monitor -- 14.6.7 DB2 Performance Expert -- 14.6.8 The db2pd utility, an onstat equivalent -- 14.6.9 Diagnostic files -- 14.6.10 Error message and command help -- Appendix A. Configuration variables and parameters -- General registry variables -- System environment variables -- Database manager configuration file -- Appendix B. Informix source and object definitions -- Appendix C. Additional resources -- Pre-transition planning and estimating -- General transition questions.

Transition consulting services.