1 / 58

Enabling Oracle Applications on DB2 an Early User Experience

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

wing-carson
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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Enabling Oracle Applications on DB2 an Early User Experience Bernie O’Connor Anixter Inc. Bernie.OConnor@anixter.com Jason Spencer Anixter Inc. Jason.Spencer@anixter.com Session Code: C03 Tuesday, May 11, 3:00 PM Platform: DB2 for Linux, UNIX, Windows

  2. 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!

  3. 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

  4. 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.

  5. Competitive Landscape: Efficiency, Service, Automation

  6. Competitive Landscape: IT as a Percentage of Revenue source: InformationWeek

  7. 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.

  8. Three Application Migration Experiences • Warehouse Management • Product Content Management • Value Added Tax

  9. 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

  10. 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

  11. 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

  12. 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.

  13. Warehouse Management Some Non-Problems • “Non-problem” differences upon review: • Timestamp Manipulation: Microseconds • Varchar2: No problem for our application

  14. 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

  15. 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

  16. 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

  17. 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?

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

  19. 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

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

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

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

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

  24. 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.

  25. Product Content Management DB2 9.7 – Laying the Migration Foundation • Compatibility Features Example

  26. 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.

  27. 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.

  28. 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

  29. 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.

  30. Product Content Management DB2 9.7 – Migrating the Objects and Data • Launching IBM Data Movement Tool

  31. Product Content Management DB2 9.7 – Migrating the Objects and Data • Choose Options for DDL Extract

  32. 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.

  33. Product Content Management DB2 9.7 – Migrating the Objects and Data • Working Directory

  34. Product Content Management DB2 9.7 – Migrating the Objects and Data • Deploying Objects and Data

  35. 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

  36. Deploy DDL/DATA Product Content Management DB2 9.7 – Migrating the Objects and Data

  37. Product Content Management DB2 9.7 – Migrating the Objects and Data

  38. Product Content Management DB2 9.7 – Migrating the Objects and Data

  39. Interactive Deploy Product Content Management DB2 9.7 – Migrating the Objects and Data

  40. Product Content Management DB2 9.7 – Migrating the Objects and Data

  41. Product Content Management DB2 9.7 – Migrating the Objects and Data

  42. Product Content Management DB2 9.7 – Migrating the Objects and Data

  43. Product Content Management DB2 9.7 – Migrating the Objects and Data • Deploy using command line script db2gen.cmd

  44. Product Content Management DB2 9.7 – Migrating the Objects and Data • View db2gen_OUTPUT.txt • Verify Object Creation

  45. Product Content Management DB2 9.7 – Migrating the Objects and Data • View db2gen_OUTPUT.txt • Verify Data Load

  46. Product Content Management DB2 9.7 – Migrating the Objects and Data • View db2gen_OUTPUT.txt • Row Count

  47. Product Content Management DB2 9.7 – Migrating the Objects and Data • View db2gen_OUTPUT.txt • Table Status

  48. Product Content Management DB2 9.7 – Migrating the Objects and Data • PCM Migration Results • 93.3% of Objects Migrated Successfully!

  49. 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!

More Related