reusing oracle pl/sql code under ibm db2 luw - act local oracle plsql code under ib… · reusing...
TRANSCRIPT
©2011 Quest Software, Inc. All rights reserved.
Udo Brede
Senior Solution Architect Databases
21st June 2012
Reusing Oracle PL/SQL Code under
IBM DB2 LUW
1
©2011 Quest Software, Inc. All rights reserved.
Agenda
Oracle and DB2
SQL and PL/SQL
PL/SQL under Oracle
• Package
• Procedure
• Debug
Oracle Compatibility Features in DB2 LUW
PL/SQL now under DB2 LUW
• Package
• Procedure
Conclusion
2
©2011 Quest Software, Inc. All rights reserved.
Oracle
First release 1977/1979
Relational database
3
©2011 Quest Software, Inc. All rights reserved.
DB2 = Database 2 (IBM)
First release 1983
Relational database
Different “flavors”:
• DB2 z/OS (MVS, OS/390)
• DB2 VSE/VM
• DB2 on z/Linux
• DB2/2
• DB2/400 (AS400, I-Series)
• DB2 LUW (Linux, Unix, Windows)
4
©2011 Quest Software, Inc. All rights reserved.
Overview of the database components Oracle
Instance
File
Database
Tablespace
Schema
Table
Index
View
Trigger
Rollback Segment
Stored Procedures
Packages
SQL Plus
SQL Developer
DB2 LUW
Instance
Container
Database
Tablespace
Schema
Table
Index
View
Trigger
NA
Stored Procedures
Modules
DB2 CLP
Data Studio
5
©2011 Quest Software, Inc. All rights reserved.
Data Dictionary Catalog
Base tables
• SYS.xxxxx$
System views
• SYS.GV_$ or GV$
• SYS.V_$ or V$
• ALL_
• DBA_
• USER_
Base Tables
• SYSIBM.xxxx
System Views
• SYSCAT
• Read-only views defined for catalog base tables
• SYSSTAT
• Updateable set of views
• Primarily used for access path manipulation
• Administrative Views
Oracle DB2
6
©2011 Quest Software, Inc. All rights reserved.
Common Terms - Different Meanings
Oracle
Stored Procedure
• PL/SQL
Package
• grouping of PL/SQL blocks
• Nothing to do with optimization
Segment
• Set of Oracle Extents
DB2
Stored Procedure
• External - C,Java,Cobol
• Internal, SQL/PL
Package
• Pre-compiled Static SQL
Segment
• Block of pages in TS
7
©2011 Quest Software, Inc. All rights reserved.
Different Terms - Similar Meaning
Oracle
Package
Data Block
Dictionary
Alert Log
Redo Log
Archive log
Segments
Statement Cache
System Global Area (SGA)
DB2
Module (9.7)
Data Page
Catalog
Diag log
Log Files
Archival Logging
Space Consuming Objects
Package Cache
Instance shared memory/DB shared memory
8
©2011 Quest Software, Inc. All rights reserved.
Product Options
Express Edition • 1 CPU/1GB RAM
• 4 GB max DB size
• Nur für Windows/Linux
Standard Edition One • 2 Sockets
• Unlimited scalability
Standard Edition • 4 Sockets
• Unlimited scalability
• RAC
Enterprise Edition • Unlimited scalability
• Table Partitioning
• RAC
Personal Edition
Express-C • 2 CPU/2GB RAM
• Windows/Linux/Solaris/Mac
Express Edition • 4 CPU/ 4GB RAM max
Workgroup Edition • 16 CPUs/ 64 GB RAM
• Purescale
Enterprise Server Edition/Advance ESE • Unlimited scalability
• Purescale
InfoSphere Warehouse Editions • Data Partitioning (DPF)
Oracle DB2
9
©2011 Quest Software, Inc. All rights reserved.
SQL and PL/SQL - 1 The procedural SQL extension was developed by the American National Standards Institute (ANSI) as an extension to SQL. It was first adopted in 1996, and it provides procedural programmability in addition to the querying commands of SQL as SQL/PSM (Structured Query Language/Persistent Stored Modules).
PL/SQL (Procedural Language/SQL) is a proprietary programming language from Oracle that is a procedural extension of the relational programming language SQL (Structured Query Language) and a lot of Oracle development tools are included. PL/SQL is designed for working with databases. PL/SQL program units (essentially code contain-ers) can be compiled into the Oracle database. Therefore, programmers can embed PL/SQL units of functionality directly into the database. They also can write scripts containing PL/SQL program units that can be read into the database using the Oracle SQL*Plus tool.
While programmers can readily embed Data Manipulation Language (DML) statements directly into their PL/SQL code using straightforward SQL statements, Data Definition Language (DDL) requires more complex "Dynamic SQL" statements to be written in PL/SQL code. DML statements underpin the majority of PL/SQL code in typical software applications.
In the case of PL/SQL dynamic SQL, early versions of the Oracle database required the use of a complicated Oracle DBMS_SQL package library. However, more recent versions have introduced simpler "Native Dynamic SQL," along with associated EXECUTE IMMEDIATE syntax.
10
©2011 Quest Software, Inc. All rights reserved.
SQL and PL/SQL - 2 This procedural extension of the SQL language is now implemented by a lot of different database vendors, like Microsoft SQL Server Transact SQL (TSQL) and IBM DB2 SQL Procedural Language (SQL PL).
11
©2011 Quest Software, Inc. All rights reserved.
PL/SQL under Oracle: Package
12
©2011 Quest Software, Inc. All rights reserved.
PL/SQL under Oracle: Procedure
13
©2011 Quest Software, Inc. All rights reserved.
PL/SQL under Oracle: Debug Session
14
©2011 Quest Software, Inc. All rights reserved.
ORACLE Compatibility Features under DB2 LUW
15
©2011 Quest Software, Inc. All rights reserved.
Enabling Oracle Compatibility Features DB2 Compatibility Vector
• New registry value
• Enables Oracle compatibility
• Controls which compatibility features to turn on
• HEX values allowed, recommendation for Oracle is “ORA”
• With DB2 Command: db2set DB2_COMPATIBILITY_VECTOR=ORA
• Activates all features.
16
©2011 Quest Software, Inc. All rights reserved.
Creating a new database using the
new registry variable
• db2set command
• db2stop/db2start
• Create database
17
©2011 Quest Software, Inc. All rights reserved.
How to check, that my database has the
Oracle Compatibility Features switch on ? • db2 get database cfg Command
• 3rd party vendor tools
18
©2011 Quest Software, Inc. All rights reserved.
Compatibility Features
Oracle Data Dictionary Views
under schema “SYSIBMADM”
• ALL_*
19
©2011 Quest Software, Inc. All rights reserved.
Compatibility Features
Oracle Data Dictionary Views
under schema “SYSIBMADM”
• DBA_*
20
©2011 Quest Software, Inc. All rights reserved.
Compatibility Features
Oracle Data Dictionary Views
under schema “SYSIBMADM”
• USER_*
21
©2011 Quest Software, Inc. All rights reserved.
Modules - 1
New DB2 Object • Groups object definitions together for an application
• Functions
• Procedures
• TYPE
• VARIABLE
22
©2011 Quest Software, Inc. All rights reserved.
Modules - 2
Same as Oracle PL/SQL Packages
23
©2011 Quest Software, Inc. All rights reserved.
New DB2 Data Types Oracle
BFILE
BLOB
CHAR(n)
CLOB
DATE
LONG
LONG RAW
NCHAR(n)
NCLOB
NUMBER(p,s)
NVARCHAR2(n)
RAW(n)
ROWID
VARCHAR2(n)
DB2
BOOLEAN
INDEX BY
NUMBER
Oracle DATE
Ref Cursor type
ROW TYPE
TIMESTAMP(n)
VARCHAR2
VARRAY
NCHAR, NVARCHAR2, NCLOB
24
©2011 Quest Software, Inc. All rights reserved.
Compatibility Features
CLPPlus
• Like the Command Line Processor “SQL*Plus” from Oracle
• Provides a familiar environment for Oracle DBAs to interact with DB2
• Compatible interface: run Oracle SQL*PLUS scripts with no modification
25
©2011 Quest Software, Inc. All rights reserved.
New Concurrency Control under DB2 LUW
Isolation level „Cursor Stability“ is enhanced with “Currently Committed“
• Under DB2 LUW version 9.7 is “Currently Committed” the default setting for new databases (up to 9.5 it was “Cursor Stability”)
• Currently Committed is “disabled” during upgrade from existing database
• Perhaps older existing application syntax has to change!
• No additional memory, no mangement overhead, no performance loss
26
©2011 Quest Software, Inc. All rights reserved.
Application Enablement
Native PL/SQL support • No SQL translation required
• PL/SQL compiler
• PL/SQL debugger
• PL/SQL Object support
• After each row triggers
• Anonymous Blocks
• Before each row triggers
• PL/SQL packages
• Procedures
27
©2011 Quest Software, Inc. All rights reserved.
PL/SQL under DB2 LUW: PL/SQL Package
28
©2011 Quest Software, Inc. All rights reserved.
PL/SQL under DB2 LUW: Procedure
29
©2011 Quest Software, Inc. All rights reserved.
PL/SQL under DB2 LUW: Procedure Dependencies
30
©2011 Quest Software, Inc. All rights reserved.
PL/SQL Procedure under both DBMS
31
©2011 Quest Software, Inc. All rights reserved.
Conclusion IBM’s release of DB2 LUW 9.7 gives you Oracle compatibility features and supports PL/SQL, making migration from Oracle to DB2 possible. With IBM Purescale technology for DB2 LUW, IBM also has an answer to Oracle RAC. IBM helps you by providing additional analysis of your Oracle application to find the best migration path for your environment. Also IBM continues to deliver more features in this area with fixpacks of DB2 LUW 9.7 and also with the new 10 release.
But there are more capabilities beyond migration: You can develop Oracle PL/SQL on DB2 LUW. I recommend that you take a look at the different database editions each vendor offers and compare pricing and development costs. Don’t forget to take into account additional Oracle license costs for things like the OEM diagnostics and tuning management packs.
DBAs and application developers should evaluate the possibilities, but there are still many differences between Oracle and DB2 LUW. Tools that work and look similar on both databases can help minimize your learning curve and save your organization money.
32
©2011 Quest Software, Inc. All rights reserved.
Information & Resources
• IBM: Run Oracle applications on DB2 10 for Linux, UNIX, and Windows by Serge Rielau: http://www.ibm.com/developerworks/data/library/techarticle/dm-0907oracleappsondb2/
• Quest Software: Expert blogs (Steve Feuerstein, John Pocknell) and free videos: http://www.ToadWorld.com
©2011 Quest Software, Inc. All rights reserved.