Enabling Oracle Applications on DB2 an Early User Experience

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.

6. Competitive Landscape: Efficiency, Service, Automation

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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 – Sizing up the enablement to DB2

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

40. Interactive Deploy 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

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

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

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

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

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

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

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