Enabling oracle applications on db2 an early user experience l.jpg
This presentation is the property of its rightful owner.
Sponsored Links
1 / 58

Enabling Oracle Applications on DB2 an Early User Experience PowerPoint PPT Presentation


  • 230 Views
  • Uploaded on
  • Presentation posted in: General

Enabling Oracle Applications on DB2 an Early User Experience. Bernie O’Connor Anixter Inc. [email protected] Jason Spencer Anixter Inc. [email protected] Session Code: C03 Tuesday, May 11, 3:00 PM Platform: DB2 for Linux, UNIX, Windows. 5 Key Points.

Download Presentation

Enabling Oracle Applications on DB2 an Early User Experience

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Enabling oracle applications on db2 an early user experience l.jpg

Enabling Oracle Applications on DB2 an Early User Experience

Bernie O’Connor

Anixter Inc.

[email protected]

Jason Spencer

Anixter Inc.

[email protected]

Session Code: C03

Tuesday, May 11, 3:00 PM

Platform: DB2 for Linux, UNIX, Windows


5 key points l.jpg

5 Key Points

  • Datatype differences in migration to DB2 from Oracle

  • Syntax

    • Most of the migration worked without intervention.

  • Concurrency

    • No problems with the major differences between approaches.

  • Moving Data between Oracle and DB2

    • Major improvements from the early beta to now.

  • Participation in the DB2 9.7 (Cobra) beta

    • a great experience!


Agenda l.jpg

Agenda

  • Anixter’s Competitive Landscape

  • DB2 9.7 Business Driver: to hold down costs

  • Warehouse Management System

    • real parts, not a data warehouse

  • Product Content Management System

    • Data, Syntax, and DB2 pureXML

  • Value Added Tax

    • Unified Code Base and Testing Scripts?

  • Other advantages: one codebase for LUW and z/OS?

  • The DB2 9.7 beta


Anixter s business see www anixter com nyse axe l.jpg

Anixter’s BusinessSee www.anixter.com (NYSE: AXE)

  • Anixter is a leading global supplier of communications and security products, electrical and electronicwire and cable, fasteners and other small components.

  • We help our customers specify solutions and make informed purchasing decisions around technology, applications and relevant standards.

  • Throughout the world, we provide innovative supply chainmanagement services to reduce our customers' total cost of production and implementation.

  • Fiscal Year ended January 1, 2010, Anixter reported sales: $5 billion

  • To more than 100,000 customers in 52 countries, Anixter is more than a distributor: we are a business partner.


Slide6 l.jpg

Competitive Landscape: Efficiency, Service, Automation


Slide7 l.jpg

Competitive Landscape: IT as a Percentage of Revenue

source:

InformationWeek


Why db2 9 7 business driver to hold down costs l.jpg

Why DB2 9.7?Business Driver to hold down costs

  • Key business driver: hold down personnel costs

    • by maintaining skilled professionals for one enterprise software stack, instead of two or more.

    • we already have specialists in WebSphere, MQ Series and DB2

  • Difference between departmental and enterprise applications.

    • We can be “good enough” to support heterogeneous departmental applications

    • Enterprise Applications require Scalability, High Availability, Recovery

  • Personnel, even more than hardware and software, is a critical cost to be considered.


Three application migration experiences l.jpg

Three Application Migration Experiences

  • Warehouse Management

  • Product Content Management

  • Value Added Tax


Warehouse management nature of the opportunity to migrate l.jpg

Warehouse Management Nature of the Opportunity to Migrate

  • Warehouse Management System

    • Contracted to be done in WebSphere / DB2

    • Delivered in JBoss / Oracle

    • We still wanted WebSphere / DB2

  • Why the mismatch between spec and delivery?

    • Vendor skill-sets

      • Originally intended to hire FTEs or Contractors

      • Vendor was acquired and reacquired

      • Vendor team lost FTEs and budget for contractors

      • Remaining team had a JBoss / Oracle skill-set


Warehouse management application profile l.jpg

Warehouse Management Application Profile

  • Application Profile

    • RF Gun is the primary UI

    • Message-based solution internally

  • Lines of code

    • Java: 2501 files with 171,285 lines of code

    • JSP: 387 files with 19,101 lines of code

    • XML: 400 files with 153,897 lines

    • XSD:63 XSD files with 4,898 lines

    • JS (Javascript): 60 files with 21,350 lines

    • Tables: 223

    • Views: 194, many nested


Warehouse management ease of migration to db2 9 7 l.jpg

Warehouse Management Ease of Migration to DB2 9.7

  • It migrated successfully!

  • We ran the exact same tests as with JBoss / Oracle

  • With WebSphere / DB2 9.7:

    • Read Currently Committed worked

      • Concurrency Control was a very big deal!

      • Writers didn’t block readers for the application

      • Serge is the right person to tell you about the how this works

      • ...I can tell you this means we now can run more than 1 person

    • Equivalent Performance

      • Some normal tuning using indexes


Warehouse management some extra effort datatypes unique constraints with nullable columns l.jpg

Warehouse Management Some Extra Effort: Datatypes, Unique Constraints with Nullable Columns

  • Datatypes

    • Current Issues

      • Float(126) had to be changed to Float(51)

      • Number(38,0) had to be changed to Number(31,0)

      • Columns with the LONG data type caused syntax errors. This data type is now deprecated in both Oracle and DB2.

    • Issues encountered during beta program that were fixed

      • Raw(nnn)

      • /* .. */ comments caused syntax errors.

      • TO_CHAR(NULL)

  • Unique Constraints with Nullable Columns

    • Unique constraints containing nullable columns caused syntax errors.

      • Our workaround was to remove the constraint and create a unique index instead.


Warehouse management some non problems l.jpg

Warehouse Management Some Non-Problems

  • “Non-problem” differences upon review:

    • Timestamp Manipulation: Microseconds

    • Varchar2: No problem for our application


Warehouse management refactor writing for application challenges l.jpg

Warehouse Management “Refactor-writing” for Application Challenges

  • Did we all live happily ever after? Well...

  • DB2 can’t fix Application Design Challenges

  • Design problems impact performance in any software:

    • Workflow and History mixed with Transactional Data:

      • We need to separate these

    • Four touches for each message is being revised:

      • Synchronous calls internally

      • Asynchronous calls externally

    • Inconsistent approaches:

      • Need to be implemented consistently

  • In brief, Application Design is still critical to success

    • We are “refactor-writing” as we speak


Product content management nature of the opportunity to migrate l.jpg

Product Content Management Nature of the Opportunity to Migrate

  • Product Content Management (PCM)

    • Originally designed for Catalog Publishing

    • Now also a System of Record

      • Integration with Legacy Systems via MQ Series

    • An Unusual Topology

      • Microsoft Access Client

      • Oracle Database

  • The Problem Statement:

    Proprietary XML Publishing impedes upgrades

    • OS upgrade is stuck behind the DBMS upgrade

    • DBMS upgrade is successful except for XML


Product content management basic application port ok xml is the wrinkle l.jpg

Product Content Management Basic Application Port OK – XML is the wrinkle

  • Anixter’s Product Content Management (PCM)

    • The basic application migrates fairly easily

    • XML conversion is currently WIP

      • If we’re converting the application for XML

        • We can convert to a DBMS we can more easily support


Product content management three key steps to enablement l.jpg

Product Content Management Three key steps to enablement

  • Sizing up the enablement to DB2.

    • How much re-factoring will be necessary?

  • Laying the migration foundation.

    • What steps are needed to successfully migrate the objects/data from Oracle to DB2?

  • Migrating the objects/data.

    • What tools are available to help us migrate?


Product content management db2 9 7 sizing up the enablement to db2 l.jpg

Product Content Management DB2 9.7 – Sizing up the enablement to DB2


Product content management db2 9 7 sizing up the enablement to db220 l.jpg

Product Content Management DB2 9.7 – Sizing up the enablement to DB2

  • MEET DB2 Tool is now Public!

    • Download site https://www14.software.ibm.com/webapp/iwm/web/reg/pick.do?source=swg-meetdb2&S_TACT=meetdb2&lang=en_US


Product content management db2 9 7 sizing up the enablement to db221 l.jpg

Product Content Management DB2 9.7 – Sizing up the enablement to DB2


Product content management db2 9 7 sizing up the enablement to db222 l.jpg

Product Content Management DB2 9.7 – Sizing up the enablement to DB2


Product content management db2 9 7 sizing up the enablement to db223 l.jpg

Product Content Management DB2 9.7 – Sizing up the enablement to DB2


Product content management db2 9 7 sizing up the enablement to db224 l.jpg

Product Content Management DB2 9.7 – Sizing up the enablement to DB2


Product content management db2 9 7 laying the migration foundation l.jpg

Product Content Management DB2 9.7 – Laying the Migration Foundation

  • DB2_COMPATIBILITY_VECTOR

    • Used to enable one or more DB2 compatibility features introduced since DB2 9.5.

    • To enable all the supported compatibility features, set the registry variable to the value ORA. This is the recommended setting.


Product content management db2 9 7 laying the migration foundation26 l.jpg

Product Content Management DB2 9.7 – Laying the Migration Foundation

  • Compatibility Features Example


Product content management db2 9 7 laying the migration foundation27 l.jpg

Product Content Management DB2 9.7 – Laying the Migration Foundation

  • DB2_DEFERRED_PREPARE_SEMANTICS

    • When set to YES, dynamic SQL statements will not be evaluated at the PREPARE step, but rather on OPEN or EXECUTE calls.

    • Changes to the registry variables will not take effect until after the instance is stopped and then restarted.


Product content management db2 9 7 laying the migration foundation28 l.jpg

Product Content Management DB2 9.7 – Laying the Migration Foundation

  • Create Database

  • DECFLT_ROUNDING

    • It is also recommended to adjust the rounding behavior to match that of Oracle.

  • AUTO_REVAL

    • Changing this value to deferred_force will allow you to deploy objects out of dependency order.


Product content management db2 9 7 laying the migration foundation29 l.jpg

Product Content Management DB2 9.7 – Laying the Migration Foundation

  • Additional Information

    • Redbook – Oracle to DB2 Conversion Guide: Compatibility Made Easy (Chapter 2. Language Compatibility Features)

      http://publib-b.boulder.ibm.com/abstracts/sg247736.html?Open


Product content management db2 9 7 migrating the objects and data l.jpg

Product Content Management DB2 9.7 – Migrating the Objects and Data

  • IBM Data Movement Tool

    • Prerequisites

      • DB2 V9.7 should be installed on your target server if you are enabling an Oracle application to be run on DB2 LUW.

      • Java version 1.5 or higher must be installed on your target server.

      • Location of JDBC drivers for your source database and DB2.


Product content management db2 9 7 migrating the objects and data31 l.jpg

Product Content Management DB2 9.7 – Migrating the Objects and Data

  • Launching IBM Data Movement Tool


Product content management db2 9 7 migrating the objects and data32 l.jpg

Product Content Management DB2 9.7 – Migrating the Objects and Data

  • Choose Options for DDL Extract


Product content management db2 9 7 migrating the objects and data33 l.jpg

Product Content Management DB2 9.7 – Migrating the Objects and Data

  • Monitor progress of extract through console window or look through the IBMDataMovementTool.log located in the output directory.


Product content management db2 9 7 migrating the objects and data34 l.jpg

Product Content Management DB2 9.7 – Migrating the Objects and Data

  • Working Directory


Product content management db2 9 7 migrating the objects and data35 l.jpg

Product Content Management DB2 9.7 – Migrating the Objects and Data

  • Deploying Objects and Data


Product content management db2 9 7 migrating the objects and data36 l.jpg

Product Content Management DB2 9.7 – Migrating the Objects and Data

  • Three Different Deploy Options

    • Deploy DDL/DATA

    • Interactive Deploy

    • Deploy using command line script db2gen.cmd


Product content management db2 9 7 migrating the objects and data37 l.jpg

Deploy DDL/DATA

Product Content Management DB2 9.7 – Migrating the Objects and Data


Product content management db2 9 7 migrating the objects and data38 l.jpg

Product Content Management DB2 9.7 – Migrating the Objects and Data


Product content management db2 9 7 migrating the objects and data39 l.jpg

Product Content Management DB2 9.7 – Migrating the Objects and Data


Product content management db2 9 7 migrating the objects and data40 l.jpg

Interactive Deploy

Product Content Management DB2 9.7 – Migrating the Objects and Data


Product content management db2 9 7 migrating the objects and data41 l.jpg

Product Content Management DB2 9.7 – Migrating the Objects and Data


Product content management db2 9 7 migrating the objects and data42 l.jpg

Product Content Management DB2 9.7 – Migrating the Objects and Data


Product content management db2 9 7 migrating the objects and data43 l.jpg

Product Content Management DB2 9.7 – Migrating the Objects and Data


Product content management db2 9 7 migrating the objects and data44 l.jpg

Product Content Management DB2 9.7 – Migrating the Objects and Data

  • Deploy using command line script db2gen.cmd


Product content management db2 9 7 migrating the objects and data45 l.jpg

Product Content Management DB2 9.7 – Migrating the Objects and Data

  • View db2gen_OUTPUT.txt

    • Verify Object Creation


Product content management db2 9 7 migrating the objects and data46 l.jpg

Product Content Management DB2 9.7 – Migrating the Objects and Data

  • View db2gen_OUTPUT.txt

    • Verify Data Load


Product content management db2 9 7 migrating the objects and data47 l.jpg

Product Content Management DB2 9.7 – Migrating the Objects and Data

  • View db2gen_OUTPUT.txt

    • Row Count


Product content management db2 9 7 migrating the objects and data48 l.jpg

Product Content Management DB2 9.7 – Migrating the Objects and Data

  • View db2gen_OUTPUT.txt

    • Table Status


Product content management db2 9 7 migrating the objects and data49 l.jpg

Product Content Management DB2 9.7 – Migrating the Objects and Data

  • PCM Migration Results

    • 93.3% of Objects Migrated Successfully!


Product content management db2 9 7 migrating the objects and data50 l.jpg

Product Content Management DB2 9.7 – Migrating the Objects and Data

  • XML Conversion – Work In Progress

    • The XML piece of the current Oracle solution has over 33,000 lines of code.

    • Using DB2 v9.7, Anixter plans on eliminating all the DOM processing and replacing it with shorter SQL/XML statements (ex. XMLTABLE, XMLGROUP..etc).

    • We also plan on trying to convert as much of the XSLT transformation code as possible.

    • Completing both objectives mentioned above should help us achieve a maximum performance gain and maximum reduction in memory consumption.

Shredding of an XML document is no longer needed with the use of the native XML data type!


Product content management db2 9 7 xml conversion l.jpg

Product Content Management DB2 9.7 – XML Conversion

Return 10 rows. Data randomly distributed among 106,015 XML Documents.


Product content management db2 9 7 migrating the objects and data52 l.jpg

Product Content Management DB2 9.7 – Migrating the Objects and Data

  • Enablement Tools

    • IBM Data Movement Tool

      http://www.ibm.com/developerworks/data/library/techarticle/dm-0906datamovement/

    • IBM Optim Development Studio

      http://publib.boulder.ibm.com/infocenter/idm/v2r2/index.jsp?topic=/com.ibm.datatools.dwb.nav.doc/topics/cprodover_dwb.html

    • Additional DB29.7 Information

      http://www.ibm.com/developerworks/views/data/libraryview.jsp?search_by=db2%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%209.7%20content


Value added tax nature of the opportunity to migrate l.jpg

Value Added Tax Nature of the Opportunity to Migrate

  • Value Added Tax Application

    • Provides correct Tax calculations

    • Maintains tax districts

    • Manages Certificates

      • such as Tax Exempt status

  • There is a DB2 version of VAT...

    • ...but the Vendor performs more rigorous testing on the primary development platform


Value added tax works well 9 7 can simplify development test l.jpg

Value Added TaxWorks well – 9.7 can simplify development, test

  • Value Added Tax already works

  • ...but the Opportunity for the ISV is:

    • One code base

    • One set of test scripts

    • More rapid delivery of new releases


Db2 9 7 beta program was a great experience thanks to ibm idug gold consultants l.jpg

DB2 9.7 Beta Program was a great experience!Thanks to IBM, IDUG, Gold Consultants

  • Beta program experience was very good!

  • Thanks to IBM, the DB2 Community, and Consultants:

    • IBM

      • Maria Schwenger (IBM – DB2 enablement advocate)

      • Eric Christy (IBM – Enterprise Data Management Sales)

    • IDUG

      • Chris Eaton (IBM – Toronto Lab – DB2 Technical Evangelist)

      • Martin Hubel (Gold Consultant and Information Champion)

        • Martin Hubel Consulting

    • IBM DB2 Gold Consultants and Information Champions

      • Dave Beulke (Gold Consultant and Information Champion)

        • Pragmatic Solutions (PSI)

      • Ingo Muller (former Gold Consultant)


Interested in participating in an ibm beta program l.jpg

Interested in participating in an IBM beta program?

  • To check out beta availability, send an email to: [email protected]

  • Special thanks to the IBM DB2 Emerging Technology team!

    • Twitter Account

      • Username: DB2EmergingTech

      • Profile: http://twitter.com/DB2EmergingTech


Questions l.jpg

Questions?

?


Slide58 l.jpg

Bernie O’ConnorAnixter [email protected] SpencerAnixter [email protected]

Thank You!Session Code: C03


  • Login