Oracle to DB2 Conversion Guide: Compatibility Made Easy

Oracle to DB2 Conversion Guide: Compatibility Made Easy

Released September 2013
Publisher(s): IBM Redbooks
ISBN: 9780738438757

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

This IBM® Redbooks® publication describes IBM DB2® SQL compatibility features. The latest version of DB2 includes extensive native support for the PL/SQL procedural language, new data types, scalar functions, improved concurrency, built-in packages, OCI, SQL*Plus, and more. These features can help with developing applications that run on both DB2 and Oracle and can help simplify the process of moving from Oracle to DB2.

In addition, IBM now provides tools to simplify the enablement process, such as the highly scalable IBM Data Movement Tool for moving schema and data into DB2, and an Editor and Profiler for PL/SQL provided by the IBM Data Studio tool suite.

This Oracle to DB2 migration guide describes new technology, preferred practices for moving to DB2, and common scenarios that can help you as you move from Oracle to DB2. This book is intended for IT architects and developers who are converting from Oracle to DB2.

DB2 compatibility with Oracle is provided through native support. The new capabilities in DB2 that provide compatibility are implemented at the lowest and most intimate levels of the database kernel, as though they were originally engineered for DB2. means that the DB2 implementation is done without the aid of an emulation layer. This intimacy leads to the scalable implementation that DB2 offers, providing identical performance between DB2 compatibility features and DB2 other language elements. For example, DB2 runs SQL PL at the same performance as PL/SQL implementations of the same function.

Table of contents

  1. Front cover
  2. Notices
    1. Trademarks
  3. Preface
    1. Authors
    2. Now you can become a published author, too!
    3. Comments welcome
    4. Stay connected to IBM Redbooks publications
  4. Summary of changes
    1. September 2013, Third Edition
  5. Chapter 1. Introduction
    1. 1.1 DB2 family of products
      1. 1.1.1 DB2 editions
      2. 1.1.2 IBM DB2 10.5 Enterprise Edition priced features
      3. 1.1.3 DB2 10 autonomic computing features
      4. 1.1.4 Introduction to PureData
    2. 1.2 DB2 Oracle database compatibility features overview
      1. 1.2.1 Concurrency control
      2. 1.2.2 Data types
      3. 1.2.3 Implicit casting
      4. 1.2.4 SQL Standard
      5. 1.2.5 PL/SQL
      6. 1.2.6 Built-in packages
      7. 1.2.7 Oracle specific JDBC extensions
      8. 1.2.8 SQL*Plus scripts
      9. 1.2.9 Oracle Call Interface and Pro*C
    3. 1.3 DB2 educational resources
      1. 1.3.1 IBM professional certification
      2. 1.3.2 Other resources
      3. 1.3.3 DB2 10 videos and topics
  6. Chapter 2. Language compatibility features
    1. 2.1 DB2 compatibility features references
      1. 2.1.1 SQL compatibility setup
      2. 2.1.2 PL/SQL record and collection types
      3. 2.1.3 Subtypes
      4. 2.1.4 Basic procedural statements
      5. 2.1.5 Control of flow statements
      6. 2.1.6 Condition (exceptions) handling
      7. 2.1.7 Cursor data type
      8. 2.1.8 Static and dynamic SQL support
      9. 2.1.9 Support for built-in scalar functions
      10. 2.1.10 Routines, procedures, and functions compatibility
      11. 2.1.11 PL/SQL packages
      12. 2.1.12 Triggers
      13. 2.1.13 SQL statements
    2. 2.2 Schema compatibility features
      1. 2.2.1 Extended data type support
      2. 2.2.2 Flexible schema changes in DB2
      3. 2.2.3 Sequences
      4. 2.2.4 Index enablement
      5. 2.2.5 Constraints enablement
      6. 2.2.6 Created global temporary tables
      7. 2.2.7 Synonyms
      8. 2.2.8 Views and Materialized Views
      9. 2.2.9 Object types
      10. 2.2.10 Partitioning and MDC
      11. 2.2.11 Oracle database links
      12. 2.2.12 Oracle Data Dictionary compatible views
    3. 2.3 DB2 command-line utilities
      1. 2.3.1 The command line processor plus user interface
      2. 2.3.2 Using the DB2 command line processor
  7. Chapter 3. Conversion process and enablement tools
    1. 3.1 The conversion process
    2. 3.2 Enablement tools
      1. 3.2.1 IBM Data Studio
      2. 3.2.2 IBM Database Conversion Workbench
    3. 3.3 Getting started with DCW
      1. 3.3.1 Creating a DCW project
      2. 3.3.2 DCW Task Launcher
    4. 3.4 DDL extraction using DCW
      1. 3.4.1 DDL extraction using a connection
      2. 3.4.2 DDL extraction using a custom script
      3. 3.4.3 Importing the DDL file
    5. 3.5 Assessment and conversion using DCW
      1. 3.5.1 Evaluating an Oracle DDL
      2. 3.5.2 Converting Oracle DDL to DB2 compatible syntax
      3. 3.5.3 The Split DDL function
    6. 3.6 Preparing your DB2 database for data movement
      1. 3.6.1 Creating the target DB2 database
      2. 3.6.2 Deploying the DDL objects that are required for data movement
    7. 3.7 Data movement using DCW
      1. 3.7.1 Data movement using flat files
      2. 3.7.2 Data movement using pipes
      3. 3.7.3 Data movement using IBM InfoSphere Federation Server
      4. 3.7.4 Data movement using IBM InfoSphere Change Data Capture
      5. 3.7.5 Manual data deployment
      6. 3.7.6 Selecting the appropriate data movement method
      7. 3.7.7 Verifying data movement
    8. 3.8 Deploying remaining objects on the target DB2 database
    9. 3.9 Conclusion
  8. Chapter 4. Enablement scenario
    1. 4.1 Installing DB2 and creating an instance
    2. 4.2 Enabling SQL compatibility
    3. 4.3 Creating and configuring the target DB2 database
    4. 4.4 Defining a new database user
    5. 4.5 Using IBM Database Conversion Workbench
      1. 4.5.1 Getting started
      2. 4.5.2 Extracting DDL and PL/SQL objects
      3. 4.5.3 Compatibility evaluation
      4. 4.5.4 Conversion
      5. 4.5.5 Splitting DDL
      6. 4.5.6 Deploying objects
      7. 4.5.7 Extracting and loading data from files
      8. 4.5.8 Deploying PL/SQL objects
      9. 4.5.9 Resolving incompatibilities with Interactive Deploy
    6. 4.6 Verifying enablement
    7. 4.7 Summary
  9. Chapter 5. Application conversion
    1. 5.1 DB2 application development introduction
      1. 5.1.1 Driver support
      2. 5.1.2 Embedded SQL
    2. 5.2 Application enablement planning
      1. 5.2.1 Checking software and hardware availability and compatibility
      2. 5.2.2 Educating developers and administrators
      3. 5.2.3 Analyzing application logic and source code
      4. 5.2.4 Setting up the target environment
      5. 5.2.5 Changing vendor-specific database API use
      6. 5.2.6 Application testing
      7. 5.2.7 Application tuning
      8. 5.2.8 Production rollout procedures
      9. 5.2.9 User education
    3. 5.3 Converting XML features
      1. 5.3.1 SQL/XML
      2. 5.3.2 XQuery
      3. 5.3.3 Modifying XML data
    4. 5.4 Converting Oracle Pro*C applications to DB2
      1. 5.4.1 Connecting to the database
      2. 5.4.2 Host variable declaration
      3. 5.4.3 Exception handling
      4. 5.4.4 Error messages and warnings
      5. 5.4.5 Passing data to a stored procedure from a C program
      6. 5.4.6 Building a C/C++ DB2 application
    5. 5.5 Converting Oracle Java applications to DB2
      1. 5.5.1 Java access methods to DB2
      2. 5.5.2 JDBC driver for DB2
      3. 5.5.3 JDBC driver declaration
      4. 5.5.4 New binary XML API
      5. 5.5.5 Stored procedure calls
    6. 5.6 Converting Oracle Call Interface applications
    7. 5.7 Converting Open Database Connectivity applications
      1. 5.7.1 Introduction to DB2 CLI
      2. 5.7.2 Setting up the DB2 CLI environment
    8. 5.8 Converting Perl applications
      1. 5.8.1 DB2 Connect method syntax
      2. 5.8.2 Syntax for calling a DB2 stored procedures
    9. 5.9 Converting PHP applications
      1. 5.9.1 Connecting to Oracle using PDO
      2. 5.9.2 Connecting to DB2 using PDO
      3. 5.9.3 Connecting to an Oracle database using PHP (OCI8)
      4. 5.9.4 Connecting PHP applications to a DB2 database
    10. 5.10 Converting .NET applications
      1. 5.10.1 Supported development software for .NET Framework applications (DB2 9.7)
      2. 5.10.2 Supported development software for .NET Framework applications (DB2 10.5)
      3. 5.10.3 Supported deployment software for .NET Framework applications (in general)
      4. 5.10.4 .NET Data Providers
      5. 5.10.5 Visual Basic .NET conversion example
  10. Appendix A. Terminology mapping
  11. Appendix B. Data types
    1. B.1 Supported SQL data types in C/C++
    2. B.2 Supported SQL data types in Java
    3. B.3 Data types available in PL/SQL
    4. B.4 Mapping Oracle data types to DB2 data types
  12. Appendix C. Built-in modules
    1. C.1 DBMS_ALERT
    2. C.2 DBMS_DDL
    3. C.3 DBMS_JOB
    4. C.4 DBMS_LOB
    5. C.5 DBMS_OUTPUT
    6. C.6 DBMS_PIPE
    7. C.7 DBMS_SQL
    8. C.8 DBMS_UTILITY
    9. C.9 UTL_DIR
    10. C.10 UTL_MAIL
    11. C.11 UTL_SMTP
  13. Appendix D. DB2CI sample program
  14. Appendix E. Code samples
    1. E.1 Code disclaimer
    2. E.2 Oracle DDL statements
    3. E.3 DB2 DDL statements
    4. E.4 Deep nested objects sample
  15. Appendix F. Additional material
    1. Code disclaimer
    2. Locating the web material
    3. Using the web material
  16. Related publications
    1. IBM Redbooks publications
    2. Other publications
    3. Online resources
    4. Help from IBM
  17. Back cover

Product information

  • Title: Oracle to DB2 Conversion Guide: Compatibility Made Easy
  • Author(s):
  • Release date: September 2013
  • Publisher(s): IBM Redbooks
  • ISBN: 9780738438757