DB2 supports Oracle SQL and PL/SQL - IBM

DB2 supports Oracle SQL and PL/SQL Serge Rielau, STSM, SQL Architect [email protected]

© Copyright IBM 2010

Agenda • • • • •

Motivation What‟s changed in the new DB2®? How do I do it? Experience Outlook


Babylonian Confusion De-facto standards

NUMBER VARCHAR2

TINYINT

SELECT * FROM T

recursion, ..

DB2

ISO SQL Standard

• SQL dialects lock customers in – Vendor specific skill set – Vendor specific applications

• Loss of competition implies higher prices, less innovation. • But SQL dialect is not a competitive differentiator! © Copyright IBM 2010

What did DB2 do to bridge the gap? Traditionally 1. Implement SQL Standard features 2. Extend SQL standard with popular features 3. Translate SQL dialect using Migration Tool Kit (MTK)

Problem • • •

MTKs can only help with data base objects and DDL e.g. procedures, triggers, functions, tables ..but SQL is littered throughout the application code! Impedance mismatch will cause performance issues © Copyright IBM 2010

A new approach • Syntax toleration Where no semantic conflicts exist

• Add infrastructure Eliminate impedance mismatch

• Compatibility modes Overcome clashes between DB2 and other SQL dialects

• Out of the box compatibility for your app – Client side – Server side © Copyright IBM 2010

Experience • It Works – > 95% statement compatibility is the rule Average is over 98%

• Examples – Intercom Migration of HR application “Balanced Score” 850 Tables and Views, 65,000 Lines of PL/SQL 500 PL/SQL Packages, Stored Procedures and UDFs from 150 days project in DB2 9.5 to 7 days in DB2 9.7 – LSI 27 applications in 4 month elapsed time (small team) – European financial institution Using Realease moved 1000+ Oracle Forms in a week – France Telecom, Potash, ...


The new reality Concurrency Control



Native support

Oracle SQL dialect



Native support

PL/SQL



Native support

PL/SQL Packages



Native support

Built-in package library



Native support

Oracle JDBC extensions



Native support

OCI



Native support

Oracle Forms



Through realease

SQL*Plus Scripts



Native support

RAC



pureScale

Changes are the exception. Not the rule.

THIS IS WHY WE CALL IT ENABLEMENT AND NOT PORT ! © Copyright IBM 2010

Concurrency Control • Pre DB2 9.7 • DB2 9.7 • Architecture


Concurrency Control of old • Oracle default – Statement level snapshot No

No

No

Yes

• DB2 default – Cursor stability No

No*

Yes

Yes

* In default isolation DB2 keeps no rows locked while scanning © Copyright IBM 2010

Concurrency Control in DB2 9.7 • ―read currently committed‖ variation on DB2’s ―cursor stability‖ isolation – If uncommitted row-change found use currently committed version – Default behavior for DB2 moving forward

No

No

No

Yes


Concurrency Control in DB2 9.7 • Log based – No management overhead – No performance overhead – No wasted memory/storage (no undo tablespace) Memory Lookup

Scanner Table T1

User 1: update T1 set name = ‘Russo’ where country=‘Italy’

Name

X

User 2: select * from T1

Log Buffer Country

Rossi Russo

Italy

Bernard

France

Garcia

Spain

Pappas

Greece

Levi

Israel

Peeters

Belgium

RID 1=Rossi->Russo Log Files

Locks


Oracle SQL Dialect support • • • •

Data types Casting Function library SQL constructs


New types in DB2 9.7 NUMBER

Exploits P6 hardware accelerated DECFLOAT

VARCHAR2

„‟ is NULL, trailing blank sensitive collation

TIMESTAMP(n) 0 (date + time) 95% of SQL PL © Copyright IBM 2010

Optim Development Studio PL/SQL Debugger


Supported PL/SQL constructs All logic

IF, WHILE, :=, etc..

EXCEPTION

Try/catch handling

Constant variables

Variables that cannot be set

FOR

Step through numbers

over range

over SELECT

Step through result set of query

over cursor

Step through result set of cursor

User Defined Exceptions

Define conditions with or without SQLCODEs

%TYPE

Anchored scalar data types

%ROWTYPE

Anchored row types

#PRAGMA AUTONOMOUS

Private commit scope

FORALL/BULK COLLECT

New in FP1

Conditional compilation

New in FP1 © Copyright IBM 2010

PL/SQL in DB2 Anonymous block

Server side scripting New also in SQL PL dialect

Scalar function

Including OUT parameters (FP1) Including DEFAULT, and named parameters (FP2)

Procedure

Including DEFAULT, and named parameters

Package Trigger

Known as MODULE in SQL PL Row level, Before or After © Copyright IBM 2010

PL/SQL Packages in DB2 CREATE PACKAGE

Define prototypes and public objects

CREATE PACKAGE BODY

Define content and private objects

Replace package body

Replace body without loosing prototypes or public objects

PKG [BODY]

Public/private variables

VARIABLE

CURSOR

Public/private cursors

TYPE

Public/private types

EXCEPTION

User defined exceptions

FUNCTION PROCEDURE

SYNONYM ON PACKAGE

User defined exceptions

Public synonyms © Copyright IBM 2010

Built-in package library DBMS_OUTPUT

“print debugging” and simple reporting

UTL_FILE

Server side I/O API

DBMS_ALERT

Cross session semaphoring

DBMS_PIPE

Cross session data pipe

DBMS_JOB

Job scheduler

DBMS_LOB

Alternate API to DB2 native LOB functions

DBMS_SQL

Alternate API to PREPARE/EXECUTE

DBMS_UTILITY

Misc functions and procedures

UTL_MAIL

Server API to email

UTL_SMTP

Server API to SMTP


Using SQL*Plus scripts in DB2 CLPPlus Compatible commands

Compatible options

Variable substitution

Column formatting

Simple reporting

Control variables


DB2 pureScale • Unlimited Capacity –Start small –Grow easily, with your business

• Application Transparency –Avoid the risk and cost of tuning your applications to the database topology

• Continuous Availability –Maintain service across planned and unplanned events © Copyright IBM 2010

Ongoing focus - FP1 • OCI client support

– Allows OCI applications to be enabled natively – Provides OCI compatible DB2CI library • Misc. function refinements – SUBSTRB, SUBSTR, TO_CHAR .. • OUT and INOUT parameters for functions – Allows usage of functions more like procedures • Improved multi partition environment support (DPF) – Allow creation of PL/SQL function and usage “as procedures” • Conditional compilation – Oracle compatible #ifdef style compiler directives “CCFLAG” – Allows for “one source” applications across Oracle and DB2 • Improved BOOLEAN support – Allow terms such as “IF isManager THEN” • FORALL and BULK COLLECT © Copyright IBM 2010

Ongoing focus - FP2 • Obfuscation – Protect ISV procedure logic by scrambling the text – DBMS_DDL.WRAP() • Functions, Procedures • Triggers • Views • PL/SQL Packages • NCHAR, NVARCHAR2, NCLOB – Synonyms for GRAPHIC and friends in Unicode

– Includes TO_NCHAR(), ... functions • DEFAULT and named parameters in functions – Invoke functions without specifying all arguments • BULK COLLECT performance enhancements


Ongoing focus – FP3 • PL/SQL NUMBER performance improvement by up to 10x – Perform NUMBER arithmetic directly in the Procedure Virtual Machine (PVM) • RATIO_TO_REPORT Olap function – Same as / SUM() OVER(...) • Limited LOB Comparisons – Support comparisons as long as actual LOB value fits into VARCHAR/VARGRAPHIC – Allows LOB-ificiation (with INLINE LENGTH) for lazy VARCHAR2(4000) definitions overcoming 32k pagesize limit • RAISE_APPLICATION_ERROR improvements – Allow non constant arguments • Runtime “purity level” enforcement

– Eliminate need to classify functions as MODIFIES/READS – PL/SQL functions can do what actual context allows. © Copyright IBM 2010

What the future may hold • PL/SQL “spit and polish” – Trigger enhancements • Multi Action, Update in Before, ... – Local object definitions • Nested procedures • Local type declarations

• Compatibility with more client APIs – e.g. Pro*C, PHP OCI?

• More modules (seeking beta testers!) – DBMS_AQ – DBMS_STATS – DBMS_LOCK – DBMS_APPLICATION_INFO – Web application Toolkit © Copyright IBM 2010

What impacts migration time length Average effort across large enterprise clients and ISVs Degree of Complexity

EASY

MEDIUM

COMPLEX

Application ratio

50%

35%

15%

Historic effort in Person Days

< 120

120-1000

> 800

Effort DB2 9.7 and beyond

5-20

20-200

> 200

Determining factors 

quantities of issues listed below



team skills,



critical path



dependencies of code . . .

Easy • DBMS_STATS • Multi-action trigger • PHP/PERL • Java • OCI

Complex

Medium • Oracle Forms



• Updates in BEFORE trigger

• Replication

• Partition handling

3rd party dependencies

• Text search

• AQ

• Object-relational features

• Pro*C

• OCCI © Copyright IBM 2010

Steps of Migration 1. Identify and size-up candidate using the MEET DB2 Tool and Assessment Questionaire 2. Enable application using IBM Data Movement Tool 3. Test, Test, Test 4. Parallel production 5. Cut-Over


DB2 9.7 MEET Report Case Study

99.0% of statements immediately transferable to IBM DB2

MEET Report • Rapid assessment of application • Early confirmation of compatibility • Lists details and source code line number for exceptions © Copyright IBM 2010

DB2 Early Access Program Study “The compatibility level that DB2 9.7 achieved is amazing.” —Masato Kudo, Works Applications

Code from 18 EAP Participants analyzed – Range of industries, solutions, countries – Range of sizes: 2,000 – 185,000 SQL statements – Over 750,000 lines tested – 90-99% of lines immediately transferable to DB2

100%

80%

% of SQL statements

90-99% of statements

60%

immediately transferable to IBM DB2 40%

20%

0% A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

Companies

Supported Statements

Unsupported Statements


R

IBM Data Movement Tool – Setup


Data Movement Tool – Drag, Drop, Patch


DB2 vs ―DB2 in Compatibility Mode‖ • Nothing is slower because of “compatibility mode” • Vast majority of features are true extensions – Difference to “regular” enhancement is the motivation – Function library, currently committed, system defined modules, compiled triggers, functions, anonymous blocks, PL/SQL

• Most non default features can coexist with DB2 apps – ROWNUM, CONNECT BY, (+) Join syntax

• Three features are incompatible – “DATE” • Differences in storage, some date arithmetic

– NUMBER • Differences in type resolution

– VARCHAR2 • '' IS NULL, trailing blank sensitive collation © Copyright IBM 2010

Continued development • Maximize Oracle Skills with DB2 app dev – Turn on all compat bits except VARCHAR2, DATE, NUMBER – Or, turn on ORA and develop new apps in Oracle mode

• PL/SQL is first class language in DB2 – Same as SQL PL, Java, C, CLR

• CLPPlus is a strategic investment – Capabilities exceed those of CLP

• DB2 Development is committed – To further enhance compatibility and adjust to customer demand – Priorities are set by customers, not Oracle – Oracle cannot change semantics and break customers and DB2


Conclusion • DB2 breaks with traditional approach to application migration – Re-use existing skills – PL/SQL and SQL PL have same performance characterists • Remain in your dialect of choice

– Easily port custom applications to DB2 – Maintain one source across DB2 and Oracle for packaged application


Resources • Wiki with technical and non technical resources www.tinyurl.com/Oracle2DB2Wiki • DB analysis tool (free for download and usage) www.tinyurl.com/MeetDB2 • DB movement tool (free for download and usage) www.tinyurl.com/Move2DB2 • Buzz www.tinyurl.com/ServersForTruth-1 www.tinyurl.com/ServersForTruth-2 www.tinyurl.com/reTHINKmigration-1 » Contact: [email protected] © Copyright IBM 2010

Disclaimer © Copyright IBM Corporation 2010. All rights reserved. U.S. Government Users Restricted Rights - Use, duplication or disclosure restricted by GSA ADP Schedule Contract with IBM Corp. THE INFORMATION CONTAINED IN THIS PRESENTATION IS PROVIDED FOR INFORMATIONAL PURPOSES ONLY. WHILE EFFORTS WERE MADE TO VERIFY THE COMPLETENESS AND ACCURACY OF THE INFORMATION CONTAINED IN THIS PRESENTATION, IT IS PROVIDED “AS IS” WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED. IN ADDITION, THIS INFORMATION IS BASED ON IBM’S CURRENT PRODUCT PLANS AND STRATEGY, WHICH ARE SUBJECT TO CHANGE BY IBM WITHOUT NOTICE. IBM SHALL NOT BE RESPONSIBLE FOR ANY DAMAGES ARISING OUT OF THE USE OF, OR OTHERWISE RELATED TO, THIS PRESENTATION OR ANY OTHER DOCUMENTATION. NOTHING CONTAINED IN THIS PRESENTATION IS INTENDED TO, NOR SHALL HAVE THE EFFECT OF, CREATING ANY WARRANTIES OR REPRESENTATIONS FROM IBM (OR ITS SUPPLIERS OR LICENSORS), OR ALTERING THE TERMS AND CONDITIONS OF ANY AGREEMENT OR LICENSE GOVERNING THE USE OF IBM PRODUCTS AND/OR SOFTWARE.

IBM, the IBM logo, ibm.com, and DB2 are trademarks or registered trademarks of International Business Machines Corporation in the United States, other countries, or both. If these and other IBM trademarked terms are marked on their first occurrence in this information with a trademark symbol (® or ™), these symbols indicate U.S. registered or common law trademarks owned by IBM at the time this information was published. Such trademarks may also be registered or common law trademarks in other countries. A current list of IBM trademarks is available on the Web at “Copyright and trademark information” at www.ibm.com/legal/copytrade.shtml Other company, product, or service names may be trademarks or service marks of others.


Serge Rielau [email protected]
