Transcript Oracle PL/SQL to NonStop SQL/MX Migration Case Study – April 2011

LIBERATE
your infrastructure
With HP NonStop SQL Solution for OLTP
and data warehouse database applications
Oracle PL/SQL to
NonStop SQL/MX Migration
Case Study
©2011 Hewlett-Packard Development Company, L.P.
©2010 Hewlett-Packard Development Company, L.P. The information contained herein is subject to change without notice
The information contained herein is subject to change without notice
Table of Content
Executive
Summary
Project
Objectives
Summing Up
Project
Methodology
Test Results
Test Cases
2
Test
Environment
Executive Summary
– Customer unhappy with Oracle database induced outages
– Evaluates NonStop for its superior availability
– Conducts a POC using representative workloads
– Ispirer, an HP Alliance Partner, modifies SQLWays to support
NonStop SQL dialect
– Successful migration from PL/SQL to SQL/MX
•
96.8% overall conversion rate, 100% conversion rate for simple SQL statements
– SQLWays reduces costs of migration with automation and
accelerates project timeline
3
Project Objectives
Project Objectives
– Execute PL/SQL to SQL/MX Migration POC with
representative workload
– Assess costs of migration
– Provide product and tool feedback
– Disclaimer:
•
This Case Study does not offer design recommendations based on Best
Practices deployed in NonStop SQL/MX environments
5
Project Methodology
Project Methodology
Baseline-Migrate-Verify
– Customer extracts Oracle PL/SQL statements, DDL, and
sample data from production
– HP baselines query results on an Oracle database instance
running on a Windows server
– Use Ispirer SQLWays to convert the DML and DDL
– Populate the NonStop SQL database with customer data
– Execute converted DML against the SQL/MX database
– Verify and document results
7
Test Environment
Test Environment
– Hardware
•
HP Integrity NonStop NB50000c
− Itanium2 1.6 GHz 8p/16c, 64GB Memory
•
HP Proliant DL380 G6 (Oracle)
− Xeon X5570 2.93GHz 2p/8c 48GB Memory
– Software
•
J06.10 + NonStop SQL/MX 3.0 + Beta code for
upcoming features
•
•
9
Ispirer SQLWays 4.0+ to support NonStop SQL
Oracle 10.2.0.3.0 executing on Windows 2003 SP2
Test Cases
Test Cases
– Convert and test 1352 simple, 49 complex SQL
statements
– Data Migration
•
Connect to Oracle database and generate
Create Table scripts
− Modify scripts to specify location and extents
11
•
Create NonStop schema using these scripts
•
Export data from Oracle database
•
Load data into NonStop SQL using Import tool
Test Results
DDL Conversion
– Ispirer SQLWays used to convert the Oracle DDL
•
Physical attributes like Location and Extents added
•
Next SQLWays Release supports Location and Extents
– 150 Tables created on NonStop
– 41 Tables populated using data exported from
Oracle database
13
Complex Query Conversion
– Convert 49 complex queries using SQLWays
– Compile converted queries on NonStop
– Execute converted queries
– Validate results
14
SQL Statements (1352) Conversion
– 800 statements migrate “as is”
– 57 statements need no conversion with SQL/MX Beta code
•
NVL, Coalesce, Decode, Bitand, Round, Sum Over, Max Over,
DATEADD, MERGE INTO
– 302 statements convert automatically using SQLWays
•
NVL2, LNNVL, Substr, Instr, Greatest, (+)=, Sysdate, Systimestamp,
To_Char, To_Date, To_Timestamp, Trunc, Rownum, dual table, Time
calculation, Minus
– 156 statements convert using SQLWays rules based conversion
•
Rowid, Merge Into, Rownum (Update), Stored Functions
– Remaining 43 statements (3%) require application change or Java code to
workaround
•
15
With Clause, Lag Over, First_Value Over, Connect BY, Delete Returning,
Stored Function
Upcoming SQL/MX Enhancements
– NVL
– COALESCE
– DECODE
– BITAND
– ROUND
– SUM OVER
– MAX OVER
– DATEADD
– MERGE INTO
16
SQLWays Conversion
N VL2 (x,a,b)
CASE W HEN x IS N ULL THEN a ELSE b EN D
LN N VL(x=y)
(N OT x=y) OR (1 = CASE W HEN (x IS N ULL OR y IS N ULL) THEN 1 ELSE 0 EN D))
SUBSTR(x, n, m)
SUBSTRIN G(x,n,m) or SUBSTRIN G(x FROM n FOR m)
IN STR(x,y)
POSITION (y IN x)
GREATEST(x,y,z)
CASE W HEN (x>y) AN D (x>z) THEN x W HEN (y>x) AN D (y>z) THEN y ELSE z EN D
A.colx = B.colx(+)
A LEFT JOIN B ON A.colx = B.colx
A.colx(+) = B.colx
A RIGHT JOIN B ON A.colx = B.colx
SYSDATE
CURREN T_DATE
SYSTIMESTAMP
CURREN T_TIMESTAMP
T)_CHAR(time 1, ' YYYY/ MM/ DD
REPLACE(SUBSTRIN G(CAST(time 1 AS VARCHAR(26)), 1, 19), ' -' , ' / ' )
HH24:mm:ss' )
TO_CHAR(date 1, ' YYYYMMDD' )
REPLACE(CAST(time 1 AS VARCHAR(10), ' -' , ' ' )
TO_TIMESTAMP(s, ' YYYY/ MM/ DD
CAST(REPLACE(s, ' / ' , ' -' ) AS TIMESTAMP)
HH24:mm:ss' )
TRUN C(num 1)
CAST(num 1 AS IN TEGER)
TRUN C(num 1, n)
CAST(num 1 AS DECIMAL(10,n))
TRUN C(time 1,' yyyymmdd' )
DATE_TRUN C(' DAY' , time1)
date 1 + 3/ 24
date 1 + IN TERVAL' 3' HOUR
time 1 + 10/ (24* 60* 60)
time 1 + IN TERVAL ' 10' SECON D
date 1 + num 1
date 1 + CAST(num 1 AS IN TERVAL DAY(2))
date 1 + num 1/ (24* 60)
date 1 + CAST(num 1 AS IN TERVAL MIN UTE(4))
ROW ID
SYSKEY
SELECT … W HERE ROW N UM < ' n' OR
FIRST ' n'
' ROW N UM = 1'
SELECT … W HERE ROW N UM <n
SELECT [FIRST n]…
MIN US
N OT EXISTS or N OT IN
17
In Summary,
– 100% conversion rate for DDL and simple SQL
statements
– 96.8% conversion rate for all SQL statements
– Ispirer SQLWays reduces costs of migration with
automated conversion
– Ispirer SQLWays accelerates migration project
timelines
18
Thank you.
19