Migration Oracle → DB 2 DEMO Juliano Marcos Martins - julianom@br. ibm. com http: //jmmwrite. wordpress. com
Agenda Introduction Migration problems Oracle enablement The tools The scenario Demo Q&A
Introduction Juliano Marcos Martins IBM Lab employee DB 2 QA This is a DEMO presentation. We are not comparing products or giving technical explanation/directions.
Migration problems
Typical “Oracle to DB 2” Migration Steps Before DB 2 9. 7 Fill out long questionnaire and get IBM Migration Team estimate Map schema and data types Move data using script developed by your DBA or command line “IBM Extract” tool Translate objects using DB 2 Migration Toolkit (semi-automated) – Triggers, procedures, and functions Manually translate SQL in application code
Why So Afraid of Migrating ? ? Impedance mismatch between source and target database features Data types, concurrency model, weak typing, packages, locking, Proprietary built-in packages and functions, … Workarounds >> Poor performance THIS IS ALL HISTORY NOW! Lack of skills in development team. LOOK INTO THE FUTURE: DB 2 9. 7 © 2010 IBM Corporation 6
Oracle enablement
DB 2 9. 7 Oracle DB Compatibility Features Registry variable: DB 2_COMPATIBILITY_VECTOR Accepts hexadecimal values (NULL or 00 to FFF) Optional: SET SQLCOMPAT PLSQL For supporting the use of “/” as a termination character when executing PL/SQL scripts in CLP – Not necessary for CLPPlus. DB 2 Server © 2010 IBM Corporation 8
“Oracle to DB 2 9. 7” Enablement Steps Rapid and accurate estimates based on your source code Move DDL and data using IBM Data Movement tool Deploy PL/SQL triggers, packages, procedures, and functions – Using IBM Data Movement tool – In some cases, manually fix a few exceptions
DB 2 9. 7 Beta Program Results Started in April 2008 – Longest beta program in DB 2 LUW history 430 worldwide customers and partners Specialized beta enablement team – Evaluated millions of lines of PL/SQL code – Built DB 2 9. 7 enablement expertise within IBM
DB 2 9. 7: Percentage of Supported PL/SQL Statements Variety of participants –Different industries –Different solutions –Different app sizes –Different countries PL/SQL supported –>750, 000 lines of code –Average of 98%
DB 2 9. 7: Differences are the exception, not the rule In DB 2 9. 7 PL/SQL applications Native support Oracle SQL dialect Native support Concurrency (locking) model Native support Data types Native support Built-in functions Native support Built-in packages Native support SQLPlus scripts Native support Oracle JDBC methods Native support Online schema changes Native support NAT I V E S UP P O RT Compatibility features
What Customers Worldwide Say About DB 2 9. 7 "To move our application to DB 2 9. 5 would have taken an estimated two-year effort. We were thrilled to see it took only one week to move it to DB 2 9. 7. This represents a terrific opportunity to expand our international community of users, partners and developers, and we’re very excited to partner with IBM to make new deployment options available. ” Paolo Juvara, CTO of Openbravo (Spain) "With IBM DB 2 9. 7 and the new IBM Optim Development Studio, we completed our recent data migration project from Oracle to DB 2 in 80 percent less time than we originally estimated, saving about two and a half months. IBM's Data Management solutions help us improve healthcare performance and the quality of care. ” Gene Ostrovsky, VP Research and Development, Exact. Cost
Tools
Migration Enablement Evaluation Tool for DB 2 • MEET DB 2 Identifies code that uses features not supported in DB 2 9. 7 No Database software required HTML report • Provides summary statistics • Lists details and source code line number 99. 0% of statements © 2010 immediately transferable to IBM Corporation DB 2 15
MEET DB 2 - Reports © 2010 IBM Corporation 16
Tools – IBM Data Movement tool IBM Data Movement Tool – Simple and intuitive GUI interface – Moves tables, views, sequences, and other objects from Oracle to DB 2 • Applies transformation as necessary – Highly scalable parallel processing engine for data movement – Can assist with deployment of PL/SQL objects • Packages, procedures, and functions
The scenario
Scenario Virtual Machine PHP application running with Oracle Windows XP The app manage inventory Oracle XE database (invent database) Have triggers, functions, etc. . DB 2 ESE We will move the database from Oracle to
It's Show time!!!
Demo steps Extract DDL Understand the Oracle Database with MEET DB 2 Create the DB 2 Database Prepare the DB 2 Database db 2 set DB 2_COMPATIBILITY_VECTOR=ORA Migrate structures with IBM Data Movement Tools Maybe some structure will need changes
Passos Enabling Oracle 1 - Iniciar a máquina virtual e logar como user 1 2 - Iniciar um prompt do DOS e rodar: db 2 set DB 2_COMPATIBILITY_VECTOR=ORA db 2 stop force db 2 start auto reval = If you want to deploy objects out of dependency order decflt_round = Adjust rounding behavior to match that of Oracle 3 - Iniciar um comando (start command line
Passos 4 - Mostrar a aplicação em PHP (No Oracle vai funcionar, no DB 2 não vai) 5 - Já foi gerado a DDL do Oracle para salvarmos tempo 5. 1 - Iniciar o MEET e abrir o arquivo sqlscript. db 2 dentro de scripts no Desktop Estudar o relatório -> 1 Tipo e 1 trigger requerem atenção (neste ponto ja deve ter
Passos – Migrando a estrutura 6 - Abrir o IBM data movement tool e testar a conexão com o oracle e DB 2 7 - Marcar DDL e clicar em Extract. . . No diretorio foram criados N objetos SQL: contain DDL statements created using DB 2 syntax. This includes the tables DDL and related objects (indexes, constraints, etc), and the LOAD commands for importing the data.
Passos – Migrando a estrutura 8 - Carregando os objetos indo em interactive deploy. 8. 1 - Selecionamos a pasta onde foi gerado o output Podemos ver todos os objetos a serem migrados. Nesta ferramenta preparamos a migração.
Passos – Migrando a estrutura 9 - Mandar fazer o deploy de tudo clicando em Deploy All objects, duas falhas vão ocorrer: A DDL do Tipo Trigger ins. Srv. R 10 - Vamos arrumar elas, comecando pelo MODEL dentro de Type/Invent, no DB 2 não utilizamos a palabra OBJECT, removemos ela, deixando como a seguir e rodamos ela:
Passos – Migrando a estrutura 11 - Arrumamos a trigger agora, midando de BEFORE para AFTER (BEFORE triggers can not update the database - They need to be changed to AFTER triggers)
Passos – Dar permissão para o usuário 12 - Temos que dar permissão para o usuário. No DB 2, os usuário são criados no Sistema Operacional. Ja criamos (o user 1) para salvar tempo e criamos um script para dar permissão para o mesmo, executamos o mesmo comando abaixo num prompt de comando: clpplus user 01/password@localhost: 50000/invent @grantall. sql
Passos – Deploy dos dados 13 - Voltamos para o IDMT e mandamos fazer o deploy dos dados, marcando DATA e desmarcando DDL. No diretório serão criados os artefatos necessarios, e as pastas: DATA: contains the text files with data extracted from each table of the source database. DUMP: it will be used during the data
Passos – Deploy dos dados 14 - Clicar em Deploy Data - não mandar fazer replace das estruturas. Apos terminar, ver o output. 15 - Checar o DUMP vazio (opcional). 16 - Executar o Row. Count. cmd dentro de mgrdata, este script vai gerar o arquivo XE. tables. rowcount com os dados das tabelas do Oracle e do DB 2, para simples
Passos – Testando a aplicação 17 - Abrimos o Firefox e testamos a aplicação no DB 2 (usuario superuser - senha password) 18 - Checar o código do view. Inventory. php dentro de DB 2 Inventory para mostrar a conexao com DB 2. Em Welcome. php:
Passos – Checando uma Trigger 19 - Mostrar a Trigger que coloca a data do fechamento da compra, no prompt do DOS, rodar: clpplus invent/password@localhost: 50000/invent @verify. Trigers 2. sql Ele chama a seguinte trigger:
Passos – Checando uma Função 20 - Vamos no CMDPlus e executamos: connect invent/password@localhost: 50000/invent select invent. serv. Owneravg. Days. Open(2) from dual; /* CREATE Function avg. Days (média de dias aberta) */ CREATE OR REPLACE FUNCTION serv. Owneravg. Days. Open(o. ID IN NUMBER)
Passos – Ultimos testes 21. Mostrando um relatório: set serverout on execute invent. user. Invent. Info(501); * utiliza o dbms_output. put_line 22. Mostrando a data do sistema:
Links Meu blog http: //jmmwrite. wordpress. com julianom@br. ibm. com MEET DB 2 Download: http: //www. ibm. com/services/forms/pre. Login. do? lang=en_US&source=swg-meetdb 2 IBM Data Movement Tool
BACKUP SLIDES Oracle to DB 2® 9. 7 Migration Demo © 2010 IBM Corporation 36
DB 2_COMPATIBILITY_VECTOR (Registry Variable) Hexadecimal value (NULL or 00 to FFF) Recommended value for Oracle migration is ORA (same as FFF) * Must be set BEFORE database creation - It can not be changed later © 2010 IBM Corporation 37
DB 2_COMPATIBILITY_VECTOR (Continued) © - It can not be changed * Must be set BEFORE database creation 2010 IBM Corporation later 38
Configuring Compatibility Mode SET SQLCOMPAT PLSQL Sets “/” as a valid statement terminator character for PL/SQL statements until the current CLP session ends SET SQLCOMPAT PLSQL; SET – SQLCOMPAT DB 2 – – – – CREATE OR REPLACE FUNCTION get_browser_version ( p_name IN varchar, p_version IN varchar) RETURN browsers%TYPE IS BEGIN … END; / – © 2010 DB 2; SET SQLCOMPAT IBM Corporation Sets back to the default – 39
Packages PL/SQL packages A package is a named collection of functions, procedures, variables, cursors, UDTs and records. Specification and body In DB 2 9. 7, –the counterpart. MODULE are CREATE OR REPLACE MODULES. @ specification bod y – – – inventory ALTER MODULE inventory PUBLISH PROCEDURE proc(IN c 1 INT) LANGUAGE SQL @ ALTER MODULE inventory ADD PROCEDURE proc(IN c 1 INT) BEGIN … END @ CALL my. Sch. inventory. proc(100) @ © 2010 IBM Corporation 40
System-Defined Packages DB 2 implements the following Oracle system-defined packages: DBMS_OUTPUT DBMS_PIPE Set of routines for sending and receiving messages through a pipe, which allows two or more sessions in the same DB 2 instance to exchange information. DBMS_ALERT Procedures for putting and getting messages from a message buffer. Useful during application debugging when you need to standard output. Procedures for registering, sending and receiving alerts in the current session. UTL_FILE Routines for reading from and writing files on the file system. DBMS_SQL, DBMS_LOB, DBMS_JOB, DBMS_UTILITY, UTIL_DIR, UTIL_MAIL, UTIL_SMTP. SQL PL system-defined Modules Same as above © 2010 IBM Corporation 41
Data Types VARCHAR 2 Implicitly mapped to VARCHAR (max. length 32672) NULL = ‘’, trailing blank sensitive collation NUMBER Exploits P 6 hardware accelerated DECFLOAT Implicit mapping: NUMBER -> DECFLOAT(16) © 2010 IBM NUMBER(p) Corporation -> DECIMAL(p) 42
Data Types TIMESTAMP(n) 0 (date + time) <= N <= 12 (date + time + picoseconds) DATE Year to seconds Implicitly mapped to TIMESTAMP(0) Functions using DATE will use TIMESTAMP(0) instead © 2010 IBM Corporation 43
Data Types – Collections & Record Oracle's PL/SQL VArray – – – – Associative Array – – TYPE emp. Type IS TABLE OF INTEGER INDEX BY VARCHAR 2(50); r_emp emp. Type; – – Array (since DB 2 9. 5) – TYPE genres IS VARRAY(4)( OF VARCHAR 2(30); DB 2's SQL PL CREATE TYPE genres AS VARCHAR(30) ARRAY[4]; Associative Array – – CREATE TYPE emp. Type AS INTEGER ARRAY[VARCHAR(50)]; CREATE TYPE DECLARE r_emp emp_type AS ROW ( emp. Type; ename TYPE emp_type IS – RECORD ( ROW type VARCHAR(50), ename – age INTEGER); VARCHAR 2(50), – DECLARE r_emp age INTEGER); © 2010 44 emp_type; r_emp emp_type; IBM Corporation RECORD
Freedom of Development - PL/SQL or SQL PL All Oracle Compatibility features in the next slides are natively supported by the DB 2 engine. For Oracle developers No emulation, no workarounds when migrating their applications to DB 2 They can continue to use the PL/SQL syntax to develop their applications on DB 2 © 2010 IBM Corporation For DB 2 developers 45
Basics (1/2) Basic Statements NULL (No-op, e. g. BEGIN NULL; END; ) Assignments (e. g. todays_date : = SYSDATE; ) SQL statements INSERT, SELECT INTO, DELETE, UPDATE, EXECUTE IMMEDIATE Statement Attributes SQL%FOUND (returns TRUE if >0 rows is affected by I/U/D or SELECT INTO retrieves >0 rows) SQL%NOTFOUND (opposite of SQL%NOTFOUND) SQL%ROWCOUNT (# of rows affected by I/U/D command) RETURNING INTO clause Control Statements IF, CASE, EXIT, LOOP, WHILE, GOTO, FOR, Exception handling Error Support RAISE (explicitly raise a previously defined condition) RAISE_APPLICATION_ERROR (makes user-defined code & error msg available to © 2010 IBMfor identifying exception) program Corporation 46
Basics (2/2) Anonymous blocks Variables Unnamed block of code that can be executed but are not stored in database. Useful for ad-hoc coding. Parameters: IN, INOUT, OUT more later Functions (create/replace/drop) Procedures (create/replace/drop) Triggers (create/replace/drop) Collections VArrays (store elements of same type in 47 © 2010 IBM Corporation order in which they are added. Must know #
%TYPE %ROWTYPE Oracle PL/SQL – – -- links to a column’s data type v_name staff. name%TYPE; -- links to a table row definition v_emp. Row employee%ROWTYPE; Data type anchoring Define types based on another SQL object Row/column in a table/view, cursor, variable DB 2's SQL PL -- links to a column’s data type DECLARE v_name ANCHOR staff. name; – – -- links to a table row definition DECLARE v_emp. Row ANCHOR ROW OF employee; Advantages: No need to know about variable’s data type © 2010 IBM Corporation If definition of a column changes, the data type of a variable changes accordingly 48
Implicit Casting (aka SQL standard by using strong typing DB 2 9. 5 follows ANSI Weak Typing) Required matching data types, meaning that one or both data types had to be explicitly converted to a common data type before a comparison or an assignment could be made. DB 2 9. 7 allows now for weak typing Implicit conversion between string and numeric data, and between string and date/timestamp data on: • • Assignment -> SET salary : = '52000' Comparison -> WHERE salary > '52000' CONCAT -> 'salary: ' || 52000 Arithmetic and UNION operations Substitutability between DATE and TIMESTAMP Untyped expressions (NULL or parameter marker) © 2010 IBM Corporation Select lists, functions calls, predicates, arithmetic 49
Scalar Functions Introduced/Enhanced in DB 2 9. 7 INITCAP, TO_CLOB, RPAD, LPAD, TO_NUMBER (synonym to DECFLOAT_FORMAT), INST (synonym to LOCATE_IN_STRING), ADD_MONTHS, NVL, TO_TIMESTAMP, LEAST, GREATEST, MONTHS_BETWEEN TO_DATE (enhanced) More format options + Locale information TO_CHAR (enhanced) Date, Timestamp or String More format options + Locale information © 2010 IBM Corporation 50
Triggers Support for row-level and statement level triggers Restrictions Triggers with combined actions (“INSERT or UPDATE or DELETE”) Disable/Enable triggers Slight modification There is no concept for disabling triggers in DB 2 BEFORE triggers can not update the database They need to be changed to AFTER triggers © 2010 IBM Corporation 51
SQL*PLUS CLPPLUS – Command Line Processor Plus Complements the functionality provided by CLP Buffer to store scripts, SQL PL or PL/SQL statements Commands to retrieve information about DBs and its objects Ability to store buffers or buffer output to a file Multiple options formatting the output of scripts and queries … others Greatly helps ISVs who rely on SQL*PLUS scripts to deploy their applications Corporation © 2010 IBM 52
Скачать презентацию Migration Oracle DB 2 DEMO Juliano Marcos
c1e88faac583638454079b937ecb0ae5.ppt