Oracle Database 12c Enables Quad Graphics to Quickly Migrate from Sybase to Oracle Exadata

Oracle Database 12c Enables Quad

Graphics to Quickly Migrate from

Sybase to Oracle Exadata

Prakash Nauduri Technical Director

Platform Migrations Group, Database Product Management Sep 30, 2014

Kevin Bott

Quad/Graphics Oracle DBA Lead

Safe Harbor Statement

The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a

commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle.

Program Agenda

Database migration drivers and challenges

Oracle offerings for migrations

First hand account of Migrating to Oracle Database 12c by

Quad Graphics

Q&A

Program Agenda

Database migration drivers and challenges

Oracle offerings for migrations

First hand account of migrating to Oracle Database 12c by

Quad Graphics

Q&A

Consolidate & Reduce

Server Sprawl Simplify Management & Reduce Overhead

Database migration drivers

Lower IT costs AND improve Quality of Service

Improve OLTP & DW Performance

Database migration challenges

• Database Migrations have an impact on applications

– SQL statements changes , error handling, database API changes

• Application changes due to database platform migration is time-consuming

– Database centric code may be spread across many applications on many systems

– Use of dynamic SQL statements makes it difficult to identify and modify SQL statements – Testing application changes is also difficult

• Differences in database functionality introduces learning curve for DBAs/Developers

– Re-training of administrators/Developers is a must

• Managing many instances of Oracle database is a challenge when migrating from other databases/platforms

Program Agenda

Database migration drivers and challenges

Oracle offerings for migrations

First hand account of migrating to Oracle Database 12c by

Quad Graphics

Q&A

Oracle offerings for migrations - Strategy

• Focusing on all aspects of migrations

– Schema, data, application migration, testing, deployment tasks

– Increase automation of migration tasks

• Enable IT staff in performing migrations

– Migrations cannot be automated 100% with tools/software – Provide subject matter expertise to customers and partners

if needed

– Provide turn key solution approach for migrations

• Reduce learning curve for DBAs and developers

– Introduce features/functionality commonly used by users on other platforms Scoping/Analysis Migration Testing User Acceptance Deployment

Migration Lifecycle

Oracle products enabling migrations

• Analysis/Scoping

• Database Migration

• Application Migration

• Bulk data migration, production rollout (0 downtime), Data Integration

• Testing

Migration Tasks

• Oracle SQL Developer, Application

Migration Assistant

• Oracle SQL Developer

• SQL Developer( Application Migration

Assistant), SQL Translation Framework

• Oracle SQL Developer/database

Utilities, Golden Gate/Oracle Data Integrator/Database Gateways

• SQL Developer Unit Tests, SQL

Translation Framework, Testing Suite Oracle Products

Oracle Database 12c

–

enabling faster migrations

• Reduce application migration effort

• Reduce database migration effort and learning curve for

DBAs and developers

• Consolidation of Databases/Instances

• SQL Translation Framework

– Can translate non-Oracle SQL statements to Oracle SQL statements and execute them

• Database Provider for DRDA

– Pre-compiled (COBOL) Applications accessing DB2 on zSeries , iSeries or LUW systems can be connect to Oracle

• Oracle Database 12c features – IDENTITY column support

– Support for implicit result sets from stored procedures/functions

– Top ‘N’ SQL Support

– VARCHAR2 Datatype length = 32K – Support for LEFT OUTER JOIN clause • Pluggable Databases (Oracle Multi-tenant)

– Migrated “Databases” are treated just as they were in the source database and avoid namespace collision

SQL Translation Framework

•

SQL Translation Framework

– SQL Translation Profile – collection of translated statements

• Each application can have its own profile

– SQL Translator – java engine that translates the SQL

• SQL Developer can install Sybase & SQL Server translators

•

SQL Translation Framework Benefits

– Test and Verify application code before production

– Helps define Task List of modifying the application code

– Convert Applications To Oracle Quicker

– Temporary measure before migrating application fully or retiring it

Oracle 12c Migration Features

SQL Translation Framework: 1

_time

SQL Translation Framework Sybase Translator template fingerprint Select top Select * Source Target Error Code Dictionary SQL Translation Dictionary Generate Fingerprint Translate “Select Top 2 * From T1” Sybase Application • Framework receives

– “Sybase Top 3 * From T1”

• Performs static lookup of a conversion in the SQL Translation Dictionary

– Not Available: Generate the Fingerprint

“ Select Top <ora:literal type=integer order=1> * From T1”

• Lookup fingerprint in the SQL Translation Dictionary

– Available: Gets the Fingerprint

“Select * From T1 FETCH FIRST

<ora:literal type=integer order=1> ROWS ONLY“

• Processes the Template with values acquired

“Select * From T1 FETCH FIRST 3 ROWS ONLY“

• Returns the translated SQL to the Framework

Using SQL Developer to Move to Oracle DB

•

Support for Migrating Applications to Oracle

– Can scan applications for SQL statements and produce reports

– Perform in-place SQL translations (works with static SQL only)

•

GUI interface to manage SQL Translation Framework environment

•

Comprehensive reports for analyzing source databases and determine

migration tasks status, complexity

•

Supports Oracle Database 12c features, ad-hoc SQL translation

•

Single tool for migrating non-Oracle databases as well as on-going Oracle

Database development activities

•

Provides flexible data migration approaches

Oracle Database 12c – New migration features

Identity Columns

CREATE TABLE table1 (

Id NUMBER(12) GENERATED BY DEFAULT ON NULL AS IDENTITY , name VARCHAR2(100 CHAR) NOT NULL );

Pre 12C Migration

Source TSQL 12C Migration

CREATE TABLE table1 (

Id NUMBER(12) , name VARCHAR2(100 CHAR) NOT NULL );

CREATE SEQUENCE t1_id_SEQ MINVALUE 1 MAXVALUE 999999999999 INCREMENT BY 1 NOCYCLE ;

CREATE OR REPLACE TRIGGER t1_id_TRG BEFORE INSERT ON t1 FOR EACH ROW

DECLARE

v_newVal NUMBER(12) := 0; v_incval NUMBER(12) := 0; BEGIN

IF INSERTING AND :new.id IS NULL THEN

SELECT t1_id_SEQ.NEXTVAL INTO v_newVal FROM DUAL;

-- If this is the first time this table have been inserted into (sequence == 1) IF v_newVal = 1 THEN

--get the max indentity value from the table SELECT NVL(max(col1),0) INTO v_newVal FROM t1; v_newVal := v_newVal + 1;

--set the sequence to that value LOOP

EXIT WHEN v_incval>=v_newVal;

SELECT t1_id_SEQ.nextval INTO v_incval FROM dual; END LOOP;

END IF;

-- save this to emulate @@identity utils.identity := v_newVal;

-- assign the value from the sequence to emulate the identity column :new.col1 := v_newVal;

create table tabel1 (id int identity,

Implicit Cursors

OPEN v_cursor FOR SELECT *

FROM table1

FETCH FIRST 10 ROWS ONLY ;

DBMS_SQL.RETURN_RESULT(v_cursor) ; OPEN v_cursor FOR

SELECT * FROM table2 FETCH FIRST 10 ROWS ONLY ;

DBMS_SQL.RETURN_RESULT(v_cursor) ; END; Pre 12C Migration Source TSQL 12C Migration CREATE OR REPLACE PROCEDURE testproc1 ( cv_1 OUT SYS_REFCURSOR, cv_2 OUT SYS_REFCURSOR) AS BEGIN

OPEN cv_1 FOR SELECT * FROM table1 WHERE ROWNUM <= 10 ;

OPEN cv_2 FOR SELECT * FROM table2 WHERE ROWNUM <= 10 ;

END; create procedure

testproc1 as begin

select top 10 * from table1 select top 10 * from table2 end;

Oracle Database 12c – New migration features

Fetch First Rows

SELECT * FROM T1

FETCH FIRST 10 PERCENT ROWS ONLY ;

Pre 12c Migration

Source T-SQL 12c Migration

WITH query AS ( SELECT * FROM T1 )

SELECT * FROM query

WHERE ROWNUM <= CEIL( 10 / 100 * ( SELECT COUNT(*) FROM query ) ) ; SELECT TOP 10 PERCENT * FROM T1

Oracle Database 12c – New migration features

VARCHAR2(32000)

CREATE TABLE table1 ( Id NUMBER ,

address VARCHAR2(6000) NOT NULL);

Pre 12c Migration

Source T-SQL 12c Migration

CREATE TABLE table1 ( Id NUMBER ,

address CLOB NOT NULL); create table tabel1

(id int ,

address varchar(6000) not null)

NOTE: Oracle Database kernel has to be re-linked with parameters associated with this feature.

Oracle Database 12c – New migration features

•

Improved Global Temporary Performance

•

Enhanced Oracle Native LEFT OUTER JOIN Syntax

– Allows multiple tables on the left of an outer join

– Previously had to convert to ANSI Joins

Outer Joins & Temporary tables

SELECT * FROM T1 ,T2 ,T3 WHERE T1.ID(+) =T2.ID AND T1.ID(+) =T3.ID;

Pre 12c Migration

Source T-SQL 12c Migration

FAILS – Multi table outer joins on old syntax was not supported SELECT * FROM T1

LEFT OUTER JOIN

T2 ON T1.ID = T2.ID LEFT OUTER JOIN

T3 ON T1.ID = T3.ID SELECT * FROM T1 , T2 , T3

WHERE T1.ID =* T2.ID AND T1.ID =* T3.ID

Migration Assistance From Oracle

•

Customer Led – Platform Migration Group can provide

• Migration Training

• Scoping/estimating, Project Planning

• Prototyping/Proof of Concept, Knowledge Transfer

• Technical issue resolution

• Best Practices

•

Oracle Led – Oracle can provide a turn key solution through Oracle

Benefits

People:

Dedicated team of migration experts Flexible onshore/offshore model

Process:

Proven methodology and re-usable tools built from 25 years of hands-on migratihands-on experience

Technology:

Real-time management & visibility into projects Management tools to ensure quality delivery

Technology

People Process The right mix of people, process, and technology to maximize performance and

value with the lowest cost and risk

Program Agenda

Database migration drivers and challenges

Oracle offerings for migrations

First hand account of migrating to Oracle Database 12c by

Quad Graphics

Q&A

| 23

Quad/Graphics

• Global provider of print and related multichannel

solutions to Fortune 500 client base

• $4.8 billion in annual sales

• 25,000+ full-time employees

• 2nd _{largest printer in Western}

Hemisphere

| 24

Global Platform

North America • 59 plants • 22,000 employees • 90% of revenue Latin America • 10 plants • 2,850 employees • 6% of revenue Europe • 2 plants • 1,450 employees • 4% of revenue Asia • Strategic Partnership with India’s ManipalTech

| 25

Business Challenges and Strategy

• Transforming Industry

> Traditional long run print on the decline

> Postage rates on the rise

> Continued downward pricing pressures

• One Common Platform

> Both Manufacturing Platform & IT Tools

> Allows work to be shifted between plants

• Adopted M&A Strategy for Growth

> Nine Acquisitions in Five Years

| 26

IT Challenges

Infrastructure

• Maintaining system performance

with increasing print volumes and shorter turn around time

• Scale Up Strategy

> Hitting the Ceiling

> Contention increasing with CPU count.

• Acquisitions bring more disparate

systems

Development

• Continuing requests for features

and new development

• Each acquisition brings new

integration challenges > One size does not fit all

| 27

IT Strategy

Infrastructure

• Migrate to Oracle Database

> Benefits from Concurrency Model

• Oracle Exadata

> Allows us to scale out as applications migrate

Development

• Oracle Database 12c

• SQL Translation Framework

| 28

Environment Overview

• Four main Sybase Environments

running on IBM Power7 / AIX

> Front Office, Back Office, Transportation, Warehousing, etc.

• Approximately 1000 applications,

organized into 160 systems > 1900 application users and roles

| 29

Project History

• 2010

> Quad/Graphics acquired WorldColor, doubling in size overnight

> Saw the need for long term strategy

• 2011

> Oracle Insight Program

• 2012

> Sybase to Oracle POC completed, partnered with Neos LLC

• 2013

> Migrated first application from Sybase to Oracle, partnered with Neos LLC

> Exadata POC, 11gR2 > Oracle 12c Released

• SQL Translation Framework POC

• 2014

> Running four migration projects for different business areas

> Development work on first translated application

| 30

SQL Translation Framework –

The Impact on Our Migration

• Enables an intermediate migration

step for applications

• Application testing can start after

driver and connection string change

• It helps minimize the investment on

low value applications

• Time and effort saved is redirected

> More Testing

> More Migration Work > Other Strategic Work

• It helps reduce pressures of normal

project management constraints > Cost, Scope and Schedule

• The SQL Translation Framework is

a key enabler for Quad/Graphics to complete our Migration Program on schedule

| 31

Lessons Learned

• Don’t forget your internal processes

> How will developers review translations? > Are translation changes “Code Changes”? > Will developers be able to edit their own

translations?

• User and Profile Management

> Use a login trigger to enable translations > Store relationship in a table

| 32

Lessons Learned

> At which level should the profile be created?

> Trade-offs of manageability and redundant translations > Be flexible in your design

Dev Team System App Login Login App Login System App Login

| 33

Lessons Learned

• Some code will need to change

> Not 100% will translate

• Dynamic SQL

> Real time translation will add to query execution time

> Apply /*+ NO_SQL_TRANSLATION */ hint

if possible

• Not good for Ad-Hoc TSQL Scripts

> Convert to PL/SQL or Stored Procedure

• Upfront analysis

> Longevity of application

> Good candidate for translation?

> Feature compatibility with ‘old’ software

• Reserved word translations

> Profile Owner should also own the Migration Repository

| 34

Keep Current on Versions

• Oracle 12.1.0.2

> DBA_TRANSLATIONS view has new

columns for auditing and tracking creation of translations

• TSQL Translator

> Install and retest problem translations

• UTILS Package

> Compare with previous version for changes > If UTILS package was customized, merge in

Program Agenda

Database migration drivers and challenges

Oracle offerings for migrations

First hand account of migrating to Oracle Database 12c by

Quad Graphics

Q&A