- SAP Managed Tags:
The purpose for this blog is to describe the End-to-End process you can follow to migrate an Oracle on-prem data model to SAP HANA Cloud including the data model conversion using SAP Advanced SQL Migration tool and the data migration using SAP HANA Smart Data Integration.
Notice that this blog may be longer that other usual blogs, this is due to the details it contains and also to the outputs attached for detail purposes.
If you are not familiarized with SAP Advanced Migration tool and the commands used during the process the following tutorial may help you, notice that the tutorial converts an Oracle on-prem database to SAP HANA on-prem, but the tutorial is not including the data migration part:
TUTORIAL | Convert Oracle-to-SAP HANA with Advanced SQL Migration Tool
--------------------------------------------
BLOG CONTENT:
1. Prerequisites.
2. Configure a new SAP Advanced SQL Migration tool project.
3. Extract the data model using SAP Advanced SQL Migration tool Gateway.
3.1 Start the SAP Advanced SQL Migration tool Gateway.
3.2 Extract the data model.
3.3 Check data model has been extracted properly.
4. Data model conversion to SAP HANA Cloud.
4.1 Verify that data model has been converted properly.
5. Run converted SQL scripts to create the data model in SAP HANA Cloud.
6. Verify that data model & SQL objects have been created in SAP HANA Cloud.
7. Data migration.
8. Real time data replication.
--------------------------------------------
1.- PREREQUISITES
- Download SAP Advanced SQL Migration tool from the following link and install it. Check installation guide inside the “docs” folder shipped with the zip installation file or in the official documentation:
Download SAP Advanced SQL Migration tool
SAP Advanced SQL Migration tool Installation Guide
- The Oracle data model should be created. The following PS/SQL script is provided with all the DDLs to create the tables, sequences, functions, stored procedures, and packages into the Oracle system. The script also populates the tables with some data so that data migration can be tested in a later step:
To create the data model download the script and follow this process:
- Create an Oracle user called “appdemo3” and give the permissions to create SQL objects.
- Download and execute APPDEMO2.sql script, this can be done with “sqlplus” client:
2.- CONFIGURE A NEW SAP ADVANCED SQL MIGRATION TOOL PROJECT
From the SAP Advanced SQL Migration Tool command line run sapdbmtk with “config” option specifying the project folder as parameter:
c://sapdbmtk 101# sapdbmtk config C:/APPDEMO3/HanaCloud
Initializing...
In the first popup window select Oracle as source database and SAP HANA Cloud as target database and click “Save & Continue”:
In the following window fill in the project parameters:
The needed and suggested parameters to modify are:
Param ID #110 > IP address for the host (or hostname) where the migration tool Gateway runs.
(The gateway can run on the same host as the migration tool is running).
Param ID #111 > port where the gateway will listen (e.g., 12345).
Param ID #112 > set to 600.
Param ID #112 > set to “no”.
Param ID #120 > specify filter selection to extract objects: “in (‘APPDEMO3’)” should be set.
Param ID #200 > set to “no”.
Param ID #201 > set to the password used for every user created by the migration tool.
Param ID #220> set to “INT”.
Param ID #221 > set to 300.
Param ID #222 > set to “INT”.
Param ID #223 > set to “INT”.
Param ID #300 > set to “direct_select”
Param ID #330 > set to “nomap”.
Param ID #331 > set to “nomap”.
Param ID #332 > set to “nomap”.
Param ID #700 > set to “yes”.
Param ID #740 > tenant DB name in SAP HANA (in Hana Cloud this is always H00).
Param ID #751 > host where SAP HANA is running (this is the HC SQL endpoint without the port).
Param ID #752 > port where SAP HANA is running (this is always 443 for HC).
Param ID #753 > SAP HANA username, use DBADMIN to avoid having missing permissions.
Param ID #754 > SAP HANA user password for user specified in previous parameter.
Param ID #757 > set to ‘no’ (do not lock users created by the migration tool.
3.- EXTRACT THE DATA MODEL USING SAP ADVANCED SQL MIGRATION TOOL GATEWAY
3.1.- Start the SAP Advanced SQL Migration tool Gateway
Configure and start the migration tool Gateway using the host and port indicated in the project parameters #110 and #11. Details to perform the configuration can be found in the migration tool user guide or the tutorial indicated at the beginning of this blog:
3.2.- Extract the data model
The migration tool “extract” command is used to connect to the source database and extract the data model, the usage for this command is:
sapdbmtk extract <project_folder> <server_name> <server_host> <port> <db_user> <db_user_passwd>
In this case the command is:
sapdbmtk extract C:/APPDEMO3/HanaCloud XE 192.168.1.179 1521 system XXXXXXX
NOTE: two compliance popups may appear, please click on “I confirm” an execute the same "extract" command again.
Check the end of the output in the command line to see that no errors happen:
c://sapdbmtk 106# sapdbmtk extract C:/APPDEMO3/HanaCloud XE 192.168.1.179 1521 system XXXXXXX Initializing... Logfile for this session: C:/APPDEMO3/HanaCloud/reports/XE.SQL-Extraction.Oracle-HANA.2024Oct16.163632.log SAP Advanced SQL Migration v.3.5.4, September 2024 ================================================================================ >>> The installation/operation guidelines as described in the <<< >>> SAP Advanced SQL Migration documentation must be strictly adhered to. <<< >>> Copyright (c) 2024 SAP SE. All rights reserved. <<< >>> Use, disclosure, reproduction or distribution of this program is <<< >>> prohibited except as expressly allowed by the governing license <<< >>> agreement or written permission of SAP SE. <<< >>> Modification, reverse engineering and de-obfuscation are not permitted. <<< ================================================================================ This program is registered to: SAP-internal development, 10-Sep-2013, 59664 ================================================================================ Confirmation record found: Application Intellectual Property rights compliance verified. No DBMS licensing compliance confirmation record found. Confirmation of sufficient licensing rights in the source database is required to continue. Confirmation recorded in c:/appdemo3/hanacloud/config/confirmed_dbms_compliance.txt at 16-Oct-2024 16:37:40. Please rerun your 'sapdbmtk extract' command. c://sapdbmtk 107# c://sapdbmtk 107# sapdbmtk extract C:/APPDEMO3/HanaCloud XE 192.168.1.179 1521 system Go2Sybase Initializing... Logfile for this session: C:/APPDEMO3/HanaCloud/reports/XE.SQL-Extraction.Oracle-HANA.2024Oct16.163852.log SAP Advanced SQL Migration v.3.5.4, September 2024 … … … (29/44) Scanning C:/APPDEMO3/HanaCloud/extracted/APPDEMO3.INSERT_STORE.PROCEDURE.plsql (94 lines, 1 second) (30/44) Scanning C:/APPDEMO3/HanaCloud/extracted/APPDEMO3.ISBN.FUNCTION.plsql (11 lines, 1 second) (31/44) Scanning C:/APPDEMO3/HanaCloud/extracted/APPDEMO3.IS_NUMBER.FUNCTION.plsql (46 lines, 1 second) (32/44) Scanning C:/APPDEMO3/HanaCloud/extracted/APPDEMO3.IS_VALID_PHONE.FUNCTION.plsql (24 lines, 1 second) (33/44) Scanning C:/APPDEMO3/HanaCloud/extracted/APPDEMO3.IS_VALID_POSTALCODE.FUNCTION.plsql (16 lines, 1 second) (34/44) Scanning C:/APPDEMO3/HanaCloud/extracted/APPDEMO3.PUBLISHER_NAME.FUNCTION.plsql (11 lines, 1 second) (35/44) Scanning C:/APPDEMO3/HanaCloud/extracted/APPDEMO3.STATISTICS_PKG.PACKAGE-BODY.plsql (170 lines, 1 second) (36/44) Scanning C:/APPDEMO3/HanaCloud/extracted/APPDEMO3.STATISTICS_PKG.PACKAGE.plsql (24 lines, 1 second) (37/44) Scanning C:/APPDEMO3/HanaCloud/extracted/APPDEMO3.STORE_NAME.FUNCTION.plsql (11 lines, 1 second) (38/44) Scanning C:/APPDEMO3/HanaCloud/extracted/APPDEMO3.TITLES_PKG.PACKAGE-BODY.plsql (570 lines, 1 second) (39/44) Scanning C:/APPDEMO3/HanaCloud/extracted/APPDEMO3.TITLES_PKG.PACKAGE.plsql (84 lines, 1 second) (40/44) Scanning C:/APPDEMO3/HanaCloud/extracted/APPDEMO3.UPDATE_AUTHOR.PROCEDURE.plsql (106 lines, 1 second) (41/44) Scanning C:/APPDEMO3/HanaCloud/extracted/APPDEMO3.UPDATE_AUTHOR_PICTURE.PROCEDURE.plsql (17 lines, 1 second) (42/44) Scanning C:/APPDEMO3/HanaCloud/extracted/APPDEMO3.UPDATE_PUBLISHER.PROCEDURE.plsql (57 lines, 1 second) (43/44) Scanning C:/APPDEMO3/HanaCloud/extracted/APPDEMO3.UPDATE_STORE.PROCEDURE.plsql (88 lines, 1 second) (44/44) Scanning C:/APPDEMO3/HanaCloud/extracted/APPDEMO3.VALIDATE_ORDER_DETAIL.PROCEDURE.plsql (37 lines, 1 second) Gateway shut down. ******************************************************************************** Ready! ***** Run Summary - SAP Advanced SQL Migration v.3.5.4 ************************* Run start : 16-Oct-2024 16:38:52 Run end : 16-Oct-2024 16:39:08 Run duration : 16 seconds Action performed : Extract SQL/DDL/schema from file from DBMS server Source DBMS : Oracle Project directory : C:/APPDEMO3/HanaCloud Generated files: Extracted SQL objects : C:/APPDEMO3/HanaCloud/extracted (44 files, *.plsql; 2219 lines) Session log file/report : C:/APPDEMO3/HanaCloud/reports/XE.SQL-Extraction.Oracle-HANA.2024Oct16.163852.log ***** Next Steps *************************************************************** Suggested next steps: - Run 'sapdbmtk analyze' or 'sapdbmtk convert' See the User Guide for full details on all commands. Run 'sapdbmtk' (without arguments) for commands and options. ===== End ====================================================================== c://sapdbmtk 108#
3.3.- Check data model has been extracted properly
A new subfolder called “extracted” should appear in the project folder, you should see there a file for every extracted stored procedure, function, and trigger and one subfolder called “catalog” with the internal metadata about the extracted data model (columns, indexes …etc.):
4.- DATA MODEL CONVERSION TO SAP HANA CLOUD
Once the data model is extracted, you need to convert to SAP Hana Cloud, this is done with the “convert” migration tool command:
sapdbmtk convert <project_folder>
In this case the command is:
sapdbmtk convert C:/APPDEMO3/HanaCloud
Check the end of the output in the command line to check that no errors happen:
c://sapdbmtk 108# sapdbmtk convert C:/APPDEMO3/HanaCloud
Initializing...
Logfile for this session: C:/APPDEMO3/HanaCloud/reports/SQL-Conversion.Oracle-HANA.2024Oct16.165755.log
SAP Advanced SQL Migration v.3.5.4, September 2024
================================================================================
>>> The installation/operation guidelines as described in the <<<
>>> SAP Advanced SQL Migration documentation must be strictly adhered to. <<<
>>> Copyright (c) 2024 SAP SE. All rights reserved. <<<
>>> Use, disclosure, reproduction or distribution of this program is <<<
>>> prohibited except as expressly allowed by the governing license <<<
>>> agreement or written permission of SAP SE. <<<
>>> Modification, reverse engineering and de-obfuscation are not permitted. <<<
================================================================================
This program is registered to: SAP-internal development, 10-Sep-2013, 59664
================================================================================
Confirmation record found: Application Intellectual Property rights compliance verified.
Creating directory C:/APPDEMO3/HanaCloud/converted ...
Creating directory C:/APPDEMO3/HanaCloud/scripts ...
===== SAP Advanced SQL Migration v.3.5.4 =======================================
Source DBMS selected : Oracle
Target DBMS selected : SAP Hana Cloud >= QRC1-24 (SQLScript)
Command line : sapdbmtk convert C:/APPDEMO3/HanaCloud
Action performed : SQL conversion
Project directory : C:/APPDEMO3/HanaCloud
Source DBMS version : Oracle v.11 (Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production)
Source DBMS servername : XE
Session logfile : C:/APPDEMO3/HanaCloud/reports/SQL-Conversion.Oracle-HANA.2024Oct16.165755.log
Run starting at : 16-Oct-2024 16:57:55
OS Process ID : 908
================================================================================
Reading metadata..........
***** SQL Conversion Section ***************************************************
44 Oracle SQL files found in C:/APPDEMO3/HanaCloud/extracted
(1/44) Converting C:/APPDEMO3/HanaCloud/extracted/APPDEMO3.AUTHOR_NAME.FUNCTION.plsql (11 lines)
Writing C:/APPDEMO3/HanaCloud/converted/APPDEMO3.AUTHOR_NAME.FUNCTION.sqlscript (37 lines)
11 lines processed in 1 second (11 lines/sec)
Conversion coverage: 88%
SQL items to be resolved: 1 (in 1 categories)
Effort estimate for this file(functional migration): -to be determined-
Conversion statistics for this file: Effort breakdown(1 day=8 hrs)
Manual edits required : 1 -to be determined-
Manually adjust/verify
NVARCHAR function value(no length): config'd length=300 : 1
Variable declaration : 1 0 Fully converted
Function declaration : 1 0 Fully converted
RETURN statement : 1 0 Fully converted
SELECT-INTO statement : 1 0 Fully converted
…
…
…
Top-20 files, by complexity(high to low):
1 APPDEMO3.TITLES_PKG.PACKAGE-BODY.sqlscript
2 APPDEMO3.UPDATE_AUTHOR.PROCEDURE.sqlscript
3 APPDEMO3.INSERT_AUTHOR.PROCEDURE.sqlscript
4 APPDEMO3.IS_NUMBER.FUNCTION.sqlscript
5 APPDEMO3.UPDATE_STORE.PROCEDURE.sqlscript
6 APPDEMO3.INSERT_STORE.PROCEDURE.sqlscript
7 APPDEMO3.IS_VALID_POSTALCODE.FUNCTION.sqlscript
8 APPDEMO3.IS_VALID_PHONE.FUNCTION.sqlscript
9 APPDEMO3.VALIDATE_ORDER_DETAIL.PROCEDURE.sqlscript
10 APPDEMO3.UPDATE_PUBLISHER.PROCEDURE.sqlscript
11 APPDEMO3.INSERT_PUBLISHER.PROCEDURE.sqlscript
12 APPDEMO3.INSERT_ORDER_DETAIL.PROCEDURE.sqlscript
13 APPDEMO3.GET_ORDER_DETAILS.PROCEDURE.sqlscript
14 APPDEMO3.GET_AUTHORS.PROCEDURE.sqlscript
15 APPDEMO3.GET_AUTHORS_LOOKUP.PROCEDURE.sqlscript
16 APPDEMO3.GET_STORES_LOOKUP.PROCEDURE.sqlscript
17 APPDEMO3.GET_STORES.PROCEDURE.sqlscript
18 APPDEMO3.GET_PUBLISHERS_LOOKUP.PROCEDURE.sqlscript
19 APPDEMO3.GET_PUBLISHERS.PROCEDURE.sqlscript
20 APPDEMO3.GET_ORDERS.PROCEDURE.sqlscript
***** Migration Complexity Assessment Summary **********************************
Based on analysis by SAP Advanced SQL Migration v.3.5.4 at 16-Oct-2024 16:57:55,
the Oracle-to-HANACloud migration at C:/APPDEMO3/HanaCloud is (preliminary) classified as:
Low-to-Medium complexity
(8 tables; 0 views; 0 triggers; 42 SQL functions/procedures/packages;
2219 lines of SQL; 93% automatically converted)
with specific migration items identified:
(more impact) Potential (partial) redesign required : 0
(less impact) Statement/Query rewrite required : 31 (see below)
(least impact) Manually adjust/verify : 133 (see above)
Details: Statement/Query rewrite required items(31):
Datatype partly supported : 31
Additional manual conversion required, Statement/Query rewrite required
Fixed-length CHAR datatype not supported; changed to NVARCHAR for function value('APPDEMO3.TITLES_PKG__calculate_isbn_check_digit') : 1
Fixed-length CHAR datatype not supported; changed to NVARCHAR for function value('APPDEMO3.TITLES_PKG__is_valid_isbn') : 1
Fixed-length CHAR datatype not supported; changed to NVARCHAR for function value('APPDEMO3.is_number') : 1
Fixed-length CHAR datatype not supported; changed to NVARCHAR for function value('APPDEMO3.is_valid_phone') : 1
Fixed-length CHAR datatype not supported; changed to NVARCHAR for function value('APPDEMO3.is_valid_postalcode') : 1
Fixed-length CHAR datatype not supported; changed to NVARCHAR for parameter('p_isbn') : 4
Fixed-length CHAR datatype not supported; changed to NVARCHAR for parameter('p_phone') : 2
Fixed-length CHAR datatype not supported; changed to NVARCHAR for parameter('p_postalcode') : 4
Fixed-length CHAR datatype not supported; changed to NVARCHAR for parameter('p_state') : 6
Fixed-length CHAR datatype not supported; changed to NVARCHAR for parameter('p_type') : 2
Fixed-length CHAR datatype not supported; changed to NVARCHAR for variable('c') : 1
Fixed-length CHAR datatype not supported; changed to NVARCHAR for variable('check_digit') : 1
Fixed-length CHAR datatype not supported; changed to NVARCHAR for variable('isbn_without_check_digit') : 1
Fixed-length CHAR datatype not supported; changed to NVARCHAR for variable('ret') : 5
***** Created objects(in generated scripts) ************************************
user group : 1
user (object owner) : 1
user (user, non-owner) : 4
table : 8
table (global temporary) : 1
table column : 58
index(non-unique,suppressed) : 2
index(unique) : 3
primary key constraint : 8
references(pk-fk) constraint : 4
unique constraint : 2
sequence : 5
synonym : 2
function : 20
procedure : 43
********************************************************************************
Ready!
***** Run Summary - SAP Advanced SQL Migration v.3.5.4 *************************
Run start : 16-Oct-2024 16:57:55
Run end : 16-Oct-2024 16:58:24
Run duration : 29 seconds
Action performed : SQL conversion
Source DBMS : Oracle
Target DBMS : SAP Hana Cloud >= QRC1-24 (SQLScript)
Project directory : C:/APPDEMO3/HanaCloud
Conversion coverage: 93%; 2219 lines of SQL
Total estimated effort(functional migration): -to be determined-
Complexity assessment summary: Low-to-Medium complexity, Resolve items: redesign:0 rewrite:31 adjust/verify:133
Generated files:
Converted SQL objects : C:/APPDEMO3/HanaCloud/scripts/converted (42 files, *.sqlscript; 5228 lines)
Conversion log files(1/SQL object) : C:/APPDEMO3/HanaCloud/scripts/converted (42 files, *.convertlog)
Ready-to-run shell scripts : C:/APPDEMO3/HanaCloud/scripts (2 files, run_*.sh)
SQL DDL scripts : C:/APPDEMO3/HanaCloud/scripts (9 files, *.sqlscript)
Schema aspects to be resolved : C:/APPDEMO3/HanaCloud/scripts/_Oracle-HANA.schema.convertlog (10 items reported)
Session log file/report : C:/APPDEMO3/HanaCloud/reports/SQL-Conversion.Oracle-HANA.2024Oct16.165755.log
***** Next Steps ***************************************************************
Suggested next steps:
- Execute the generated shell scripts (run_ddl_scripts.sh and run_sql_scripts.sh),
located in /scripts; then manually correct/complete the generated SQL code as required,
See the User Guide for full details on all commands.
Run 'sapdbmtk' (without arguments) for commands and options.
===== End ======================================================================
c://sapdbmtk 109#4.1.- Verify that data model has been converted properly
A new subfolder called “scripts” should appear in the project folder, a set of SQL scripts plus shell scripts are generated and they should be executed to create the data model in SAP HANA Cloud:
SQL scripts:
dbmtk_create_users.sqlscript > SQL script to create the database users.
dbmtk_create_tables.sqlscript > SQL script to create the converted tables.
dbmtk_create_views.sqlscript > SQL script to create the converted view.
… etc.
Shell scripts:
run_ddl_scripts.sh > shell script to create the data model (users, tables, views, PKs, ...etc.)
run_sql_scripts.sh > shell script to create SQL objects containing SQL code (stored procedures,functions and triggers)
run_sql_scripts_constraints.sh > shell script to constraints and foreign keys, typically executed after the data migration
5.- RUN CONVERTED SQL SCRIPTS TO CREATE THE DATA MODEL IN SAP HANA CLOUD
Script run_ddl_scripts.sh should be executed first, it is a shell script so a Unix command line is required, you can use a migration tool command line (the migration tool runs on top of Cygwin Unix env for Windows):
c://appdemo3/HanaCloud/scripts 102# run_ddl_scripts.sh
Using hdbsql: /cygdrive/c/Program Files/sap/hdbclient/hdbsql
HDBSQL version 2.22.27.1723745450, the SAP HANA Database interactive terminal.
Copyright 2000-2024 by SAP SE.
Time on client system: Wed Oct 16 17:52:01 WEDT 2024
-----------------------------------------------------------------------------
dbmtk_HANAC_tenant_config.sqlscript: Setting/checking HANA tenant database options for SAP Advanced SQL Migration...
Connected to SAP HANA server named: H00, database: H00, version: 4.00.000.00.1728375763
Connected as user 'DBADMIN'
Checking tenant option 'carry_nested_implicit_result'...
>> This option is suggested to be set to 'true', if not behavior
>> may not be the expected or manual adjustments my be required
Checking tenant option 'enable_select_into_scalar_udf'...
>> This option is suggested to be set to 'true', if not behavior
>> may not be the expected or manual adjustments my be required
Checking tenant option 'dynamic_sql_ddl_error_level'...
>> This option is suggested to be set to 'silent', if not behavior
>> may not be the expected or manual adjustments my be required
Checking tenant option 'sudf_support_level_select_into'...
>> This option is suggested to be set to 'silent', if not behavior
>> may not be the expected or manual adjustments my be required
Checking tenant option 'support_level_set_session_in_read_only'...
>> This option is suggested to be set to 'silent', if not behavior
>> may not be the expected or manual adjustments my be required
Checking tenant option 'sudf_support_level_proc_call'...
>> This option is suggested to be set to 'silent', if not behavior
>> may not be the expected or manual adjustments my be required
Checking tenant option 'sudf_support_level_cursor'...
>> This option is suggested to be set to 'silent', if not behavior
>> may not be the expected or manual adjustments my be required
…
…
…
(64/65) Creating placeholder procedure 'APPDEMO3.statistics_pkg__dbmtk_init_session_global'...
Batch starting at line #1435:
1) CALL sapdbmtk.sp_dbmtk_object_drop('APPDEMO3.statistics_pkg__dbmtk_init_session_global', 'procedure') WITH HINT(IGNORE_PLAN_CACHE)
Batch starting at line #1437:
1) CREATE PROCEDURE APPDEMO3.statistics_pkg__dbmtk_init_session_global
2) READS SQL DATA
3) AS
4) BEGIN
5) /* dummy body, for placeholder declaration only */
6) RETURN;
7) END
Batch starting at line #1445:
1) CALL sapdbmtk.sp_dbmtk_object_install_custom( 'procedure', 'APPDEMO3', 'statistics_pkg__dbmtk_init_session_global') WITH HINT(IGNORE_PLAN_CACHE)
Warning: * 1347: Not recommended feature: DDL statement is used in Dynamic SQL (current dynamic_sql_ddl_error_level = 1) SQLSTATE: HY000
sp_dbmtk_object_install_custom: Post-processing installation of procedure 'APPDEMO3.statistics_pkg__dbmtk_init_session_global': ready!
Batch starting at line #1447:
1) SET SCHEMA APPDEMO3
Batch starting at line #1449:
1) --********** Creating placeholder procedure 'APPDEMO3.titles_pkg__dbmtk_init_session_global' ********************
Batch starting at line #1451:
1) CALL SQLSCRIPT_PRINT:PRINT_LINE( '(65/65) Creating placeholder procedure ''APPDEMO3.titles_pkg__dbmtk_init_session_global''...' );
(65/65) Creating placeholder procedure 'APPDEMO3.titles_pkg__dbmtk_init_session_global'...
Batch starting at line #1453:
1) CALL sapdbmtk.sp_dbmtk_object_drop('APPDEMO3.titles_pkg__dbmtk_init_session_global', 'procedure') WITH HINT(IGNORE_PLAN_CACHE)
Batch starting at line #1455:
1) CREATE PROCEDURE APPDEMO3.titles_pkg__dbmtk_init_session_global
2) READS SQL DATA
3) AS
4) BEGIN
5) /* dummy body, for placeholder declaration only */
6) RETURN;
7) END
Batch starting at line #1463:
1) CALL sapdbmtk.sp_dbmtk_object_install_custom( 'procedure', 'APPDEMO3', 'titles_pkg__dbmtk_init_session_global') WITH HINT(IGNORE_PLAN_CACHE)
Warning: * 1347: Not recommended feature: DDL statement is used in Dynamic SQL (current dynamic_sql_ddl_error_level = 1) SQLSTATE: HY000
sp_dbmtk_object_install_custom: Post-processing installation of procedure 'APPDEMO3.titles_pkg__dbmtk_init_session_global': ready!
Batch starting at line #1466:
1) /*
2) * End of 'dbmtk_create_proc_headers.sqlscript'
3) */
Messages while executing 'dbmtk_create_proc_headers.sqlscript': 66 --> (0 errors; 66 warnings)
Summary:
========
Messages while executing 'dbmtk_HANAC_tenant_config.sqlscript': 0 --> (0 errors; 0 warnings)
Messages while executing 'dbmtk_HANAC_runtime.sqlscript': 82 --> (0 errors; 82 warnings)
Messages while executing 'dbmtk_HANAC_runtime_ORA.sqlscript': 123 --> (0 errors; 123 warnings)
Messages while executing 'dbmtk_HANAC_runtime_custom.sqlscript': 1 --> (0 errors; 1 warnings)
Messages while executing 'dbmtk_create_users.sqlscript': 16 --> (0 errors; 16 warnings)
Messages while executing 'dbmtk_create_sequences.sqlscript': 6 --> (0 errors; 6 warnings)
Messages while executing 'dbmtk_create_tables.sqlscript': 14 --> (0 errors; 14 warnings)
Messages while executing 'dbmtk_create_proc_headers.sqlscript': 66 --> (0 errors; 66 warnings)
Total server messages: 308
Total errors : 0
Total warnings : 308
Total files : 8
Files w/zero messages: 1
===== end of script: run_ddl_scripts.sh =====
Session log: run_ddl_scripts.out
c://appdemo3/HanaCloud/scripts 103#
Then run_sql_scripts.sh should be executed:
c://appdemo3/HanaCloud/scripts 103# run_sql_scripts.sh
Using hdbsql: /cygdrive/c/Program Files/sap/hdbclient/hdbsql
HDBSQL version 2.22.27.1723745450, the SAP HANA Database interactive terminal.
Copyright 2000-2024 by SAP SE.
Time on client system: Wed Oct 16 17:56:10 WEDT 2024
-----------------------------------------------------------------------------
=== (1/42) ./converted/APPDEMO3.AUTHOR_NAME.FUNCTION.sqlscript ==========
Batch starting at line #1:
1) /*
2) * This file : APPDEMO3.AUTHOR_NAME.FUNCTION.sqlscript
3) * Converted from : APPDEMO3.AUTHOR_NAME.FUNCTION.plsql
4) *
5) * Source DBMS : Oracle v.11 (Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production)
6) * Source DB servername : XE
7) * Target DBMS : SAP Hana Cloud >= QRC1-24 (SQLScript)
8) *
9) *
10) * User config(id=101) : Include default configuration settings in header of generated files: Setting=yes
11) * User config(id=423) : Report identifier upper/lowercase analysis: Setting=no
12) * User config(id=330) : Case mapping for usernames: Setting=nomap
13) * User config(id=331) : Case mapping for names of schema objects (tables,views,columns,indexes,sequences,etc.): Setting=nomap
14) * User config(id=332) : Case mapping for names of SQL objects (procedures, functions; not for variables/parameters): Setting=nomap
15) * User config(id=335) : Apply case mappings also when source DBMS is case-sensitive for identifiers: Setting=no
16) * User config(id=211) : Include an 'ORIGSQL' comment with the original SQL in the converted SQL: Setting=yes
17) * User config(id=212) : Max. number of characters of original SQL in 'ORIGSQL' comment: Setting=80
18) * User config(id=215) : Append 'NOT CONVERTED' comment to unconverted statements in the converted SQL: Setting=yes
19) * User config(id=217) : Report additional details about some conversion categories, either for all files or only the totals: Setting=all
20) * User config(id=218) : Report details ordered by functionality or by impact: Setting=functionality
21) * User config(id=220)*: Datatype to convert Oracle INT, INTEGER or SMALLINT (which have 38-digit precision): Setting=INT
22) * User config(id=221)*: Length to convert Oracle (VAR)CHAR(2) declared without length: Setting=300
23) * User config(id=222)*: For variables/parameters, datatype to convert Oracle NUMBER declared without precision: Setting=INT
24) * User config(id=223)*: For table/view columns, datatype to convert Oracle NUMBER declared without precision: Setting=INT
25) * User config(id=224) : For variables/parameters, datatype to convert Oracle NUMBER declared with precision but without scale(see User Guide): Setting=optimized_integer
26) * User config(id=225) : For table/view columns, datatype to convert Oracle NUMBER declared with precision but without scale(see User Guide): Setting=optimized_integer
27) * User config(id=231) : Length to convert a CHAR/BINARY datatype exceeding the target DBMS length: Setting=MAX
28) * User config(id=265) : Convert Oracle packages to Hana User-Defined Libraries: Setting=no
29) * User config(id=290) : Default conversion of DBMS_OUTPUT.PUT_LINE/GET_LINE: Setting=print
30) * User config(id=292) : Conversion of PRINT-style statements: Setting=sqlscript_print
31) * User config(id=300)*: Conversion of REF CURSOR output parameters in procedures called from the client app: Setting=direct_select
32) * User config(id=310) : Generate a SQL SECURITY/EXECUTE AS clause for procedures/functions: Setting=yes
33) * User config(id=320) : Ignore or keep the leading 'N' in Unicode string notation N'...': Setting=ignore
34) * User config(id=441) : Generate identity columns as BY DEFAULT or ALWAYS: Setting=by_default
35) * User config(id=380) : How aggressively to convert identifiers in SQL objects (higher=slower): Setting=medium
36) * User config(id=382) : How to rename identifiers that are reserved words: delimited, or with a suffix: Setting=delimited
37) * User config(id=383) : How to handle identifiers that are non-recommended words (e.g. ANSI-reserved): Setting=ignore
38) * User config(id=385) : When to substitute a column alias with the expression, when referenced in the query(settings are cumulative): Setting=simple_expression
39) * User config(id=600) : Schema for run-time components: Setting=sapdbmtk
40) * User config(id=704) : Show warning counts in Hana when running run_ddl/run_sql generated shell scripts: Setting=yes
41) * User config(id=720) : Include the original SQL code at the end of the converted file: Setting=no
42) * User config(id=781) : Generate call to customizable procedure after each object creation: Setting=yes
43) * NB: Settings marked '*' are set to non-defaults (use config #101=no for condensed overview))
44) *
45) * Project directory : C:/APPDEMO3/HanaCloud
46) *
47) *-----------------------------------------------------------------------------
48) * Generated by SAP Advanced SQL Migration v.3.5.4 at 16-Oct-2024 17:50:00
49) * (generated with: sapdbmtk convert, 59664)
50) * SAP Advanced SQL Migration is property of SAP SE.
51) *-----------------------------------------------------------------------------
52) */
Batch starting at line #54:
1) CALL sapdbmtk.sp_dbmtk_report_connect('APPDEMO3.AUTHOR_NAME.FUNCTION.sqlscript') WITH HINT(IGNORE_PLAN_CACHE)
Executing script 'APPDEMO3.AUTHOR_NAME.FUNCTION.sqlscript'...
Connected to SAP HANA server named: H00, database: H00, version: 4.00.000.00.1728375763
Connected as user 'DBADMIN'
Batch starting at line #57:
…
…
…
65) p_warning_msg = 'Title can''t be found in DB';
66) END IF;
67)
68) -- Check DISCOUNT
69)
70) IF (:p_discount > 100.0)
71) THEN
72) /* ORIGSQL: p_warning_msg := */
73) p_warning_msg = 'Discount can''t be above 100.0';
74) END IF;
75) END;
Batch starting at line #172:
1) CALL sapdbmtk.sp_dbmtk_object_install_custom('procedure', 'APPDEMO3', 'validate_order_detail') WITH HINT(IGNORE_PLAN_CACHE);
Warning: * 1347: Not recommended feature: DDL statement is used in Dynamic SQL (current dynamic_sql_ddl_error_level = 1) SQLSTATE: HY000
sp_dbmtk_object_install_custom: Post-processing installation of procedure 'APPDEMO3.validate_order_detail': ready!
Batch starting at line #175:
1) /*
2) * end of 'APPDEMO3.VALIDATE_ORDER_DETAIL.PROCEDURE.sqlscript'
3) */
Messages while executing './converted/APPDEMO3.VALIDATE_ORDER_DETAIL.PROCEDURE.sqlscript': 2 --> (0 errors; 2 warnings)
Summary:
========
Messages while executing './converted/APPDEMO3.AUTHOR_NAME.FUNCTION.sqlscript': 3 --> (0 errors; 3 warnings)
Messages while executing './converted/APPDEMO3.CALCULATE_SALES_DETAILS.FUNCTION.sqlscript': 3 --> (0 errors; 3 warnings)
Messages while executing './converted/APPDEMO3.DELETE_AUTHOR.PROCEDURE.sqlscript': 2 --> (0 errors; 2 warnings)
Messages while executing './converted/APPDEMO3.DELETE_ORDER.PROCEDURE.sqlscript': 2 --> (0 errors; 2 warnings)
Messages while executing './converted/APPDEMO3.DELETE_ORDER_DETAILS.PROCEDURE.sqlscript': 2 --> (0 errors; 2 warnings)
Messages while executing './converted/APPDEMO3.DELETE_PUBLISHER.PROCEDURE.sqlscript': 2 --> (0 errors; 2 warnings)
Messages while executing './converted/APPDEMO3.DELETE_STORE.PROCEDURE.sqlscript': 2 --> (0 errors; 2 warnings)
Messages while executing './converted/APPDEMO3.GET_AUTHORS.PROCEDURE.sqlscript': 2 --> (0 errors; 2 warnings)
Messages while executing './converted/APPDEMO3.GET_AUTHORS_COUNT.PROCEDURE.sqlscript': 2 --> (0 errors; 2 warnings)
Messages while executing './converted/APPDEMO3.GET_AUTHORS_LOOKUP.PROCEDURE.sqlscript': 2 --> (0 errors; 2 warnings)
Messages while executing './converted/APPDEMO3.GET_AUTHOR_BY_ID.PROCEDURE.sqlscript': 2 --> (0 errors; 2 warnings)
Messages while executing './converted/APPDEMO3.GET_AUTHOR_PICTURE.PROCEDURE.sqlscript': 2 --> (0 errors; 2 warnings)
Messages while executing './converted/APPDEMO3.GET_DEMOAPP_PROPERTY.PROCEDURE.sqlscript': 2 --> (0 errors; 2 warnings)
Messages while executing './converted/APPDEMO3.GET_DEMOAPP_USAGE_STATISTICS.PROCEDURE.sqlscript': 2 --> (0 errors; 2 warnings)
Messages while executing './converted/APPDEMO3.GET_ORDERS.PROCEDURE.sqlscript': 2 --> (0 errors; 2 warnings)
Messages while executing './converted/APPDEMO3.GET_ORDERS_COUNT.PROCEDURE.sqlscript': 2 --> (0 errors; 2 warnings)
Messages while executing './converted/APPDEMO3.GET_ORDER_DETAILS.PROCEDURE.sqlscript': 2 --> (0 errors; 2 warnings)
Messages while executing './converted/APPDEMO3.GET_PUBLISHERS.PROCEDURE.sqlscript': 2 --> (0 errors; 2 warnings)
Messages while executing './converted/APPDEMO3.GET_PUBLISHERS_COUNT.PROCEDURE.sqlscript': 2 --> (0 errors; 2 warnings)
Messages while executing './converted/APPDEMO3.GET_PUBLISHERS_LOOKUP.PROCEDURE.sqlscript': 2 --> (0 errors; 2 warnings)
Messages while executing './converted/APPDEMO3.GET_PUBLISHER_BY_ID.PROCEDURE.sqlscript': 2 --> (0 errors; 2 warnings)
Messages while executing './converted/APPDEMO3.GET_STORES.PROCEDURE.sqlscript': 2 --> (0 errors; 2 warnings)
Messages while executing './converted/APPDEMO3.GET_STORES_COUNT.PROCEDURE.sqlscript': 2 --> (0 errors; 2 warnings)
Messages while executing './converted/APPDEMO3.GET_STORES_LOOKUP.PROCEDURE.sqlscript': 2 --> (0 errors; 2 warnings)
Messages while executing './converted/APPDEMO3.INSERT_AUTHOR.PROCEDURE.sqlscript': 2 --> (0 errors; 2 warnings)
Messages while executing './converted/APPDEMO3.INSERT_ORDER.PROCEDURE.sqlscript': 2 --> (0 errors; 2 warnings)
Messages while executing './converted/APPDEMO3.INSERT_ORDER_DETAIL.PROCEDURE.sqlscript': 2 --> (0 errors; 2 warnings)
Messages while executing './converted/APPDEMO3.INSERT_PUBLISHER.PROCEDURE.sqlscript': 2 --> (0 errors; 2 warnings)
Messages while executing './converted/APPDEMO3.INSERT_STORE.PROCEDURE.sqlscript': 2 --> (0 errors; 2 warnings)
Messages while executing './converted/APPDEMO3.ISBN.FUNCTION.sqlscript': 3 --> (0 errors; 3 warnings)
Messages while executing './converted/APPDEMO3.IS_NUMBER.FUNCTION.sqlscript': 2 --> (0 errors; 2 warnings)
Messages while executing './converted/APPDEMO3.IS_VALID_PHONE.FUNCTION.sqlscript': 2 --> (0 errors; 2 warnings)
Messages while executing './converted/APPDEMO3.IS_VALID_POSTALCODE.FUNCTION.sqlscript': 2 --> (0 errors; 2 warnings)
Messages while executing './converted/APPDEMO3.PUBLISHER_NAME.FUNCTION.sqlscript': 3 --> (0 errors; 3 warnings)
Messages while executing './converted/APPDEMO3.STATISTICS_PKG.PACKAGE-BODY.sqlscript': 21 --> (0 errors; 21 warnings)
Messages while executing './converted/APPDEMO3.STORE_NAME.FUNCTION.sqlscript': 3 --> (0 errors; 3 warnings)
Messages while executing './converted/APPDEMO3.TITLES_PKG.PACKAGE-BODY.sqlscript': 14 --> (0 errors; 14 warnings)
Messages while executing './converted/APPDEMO3.UPDATE_AUTHOR.PROCEDURE.sqlscript': 2 --> (0 errors; 2 warnings)
Messages while executing './converted/APPDEMO3.UPDATE_AUTHOR_PICTURE.PROCEDURE.sqlscript': 2 --> (0 errors; 2 warnings)
Messages while executing './converted/APPDEMO3.UPDATE_PUBLISHER.PROCEDURE.sqlscript': 2 --> (0 errors; 2 warnings)
Messages while executing './converted/APPDEMO3.UPDATE_STORE.PROCEDURE.sqlscript': 2 --> (0 errors; 2 warnings)
Messages while executing './converted/APPDEMO3.VALIDATE_ORDER_DETAIL.PROCEDURE.sqlscript': 2 --> (0 errors; 2 warnings)
Total server messages: 120
Total errors : 0
Total warnings : 120
Total files : 42
Files w/zero messages: 0
===== end of script: run_sql_scripts.sh =====
Session log: run_sql_scripts.out
c://appdemo3/HanaCloud/scripts 103#
6.- VERIFY THAT DATA MODEL AND SQL OBJECTS HAVE BEEN CREATED IN SAP HANA CLOUD
Open the BTP Cockpit - Database Explorer and check that the tables and other SQL objects are created under a schema APPDEMO3:
7.- DATA MIGRATION
The data migration can be performed in very different ways depending on each case, any ETL tool can be used, for example SAP Data Services. Export/import to/from files in a file storage is another possibility. In this case the data migration is going to be done using SAP HANA Smart Data Integration, using this you also have the possibility to activate real time data replication as it will be shown in bullet #8.
An SAP HANA Cloud remote source pointing for the Oracle database is required both for the data migration and the real time replication. To make this work properly some configuration is required in the SAP HANA Cloud instance and also in an on-prem host with access to the Oracle database instance. In the on-prem host SAP HANA Data Provisioning Agent needs to be installed and configured, in the SAP HANA instance the remote source has to be created once the SAP HANA Data Provisioning Agent is configured.
DP Agent configuration and SAP HANA Cloud instance configuration is out of the scope of this blog, so I am not entering into the deep details, check these links and sub-links carefully and follow the process to configure them properly:
SDI Configuration Guide for SAP HANA Cloud | SAP Help Portal
The SDI adapter I am using for this configuration is the Oracle log reader using “LogMiner” as capture mode, this adapter requires some specific configuration in the Oracle side as well, for example for real time data replication supplemental logging must be enabled in the Oracle Server. Details about how to configure the adapter can be found here:
SDI Oracle Log Reader | SAP Help Portal
Once everything is configured you should be able to create a remote source pointing to the Oracle on-prem database, this is how it looks like in my demo environment (the name of the remote source is ORA11):
This remote source is accessing the real physical tables in the Oracle on-prem system:
Now you can create virtual tables pointing the Oracle systems and populate the SAP HANA Cloud tables selecting from those virtual tables, this is done with “insert … select …” statements. This is what you need to execute to get this done:
This is the output executing these commands:
Statement: grant create virtual table, create remote subscription on remote source ORA11 to appdemo3 Client elapsed time: 6.000 ms Statement prepare time: 1.145 ms elapsed time, 0.8860 ms CPU time Statement execute time: 4.283 ms elapsed time, 1.931 ms CPU time Peak memory consumed: 66.49 KB Statement: connect appdemo3 password Go2Sybase Rows affected: 0 Client elapsed time: 51.00 ms Statement prepare time: 0.6810 ms elapsed time, 0.4800 ms CPU time Statement execute time: 50.83 ms elapsed time, 13.28 ms CPU time Peak memory consumed: 59.80 KB Statement: create virtual table appdemo3.vt_demoapp_properties at ... Client elapsed time: 688.0 ms Statement prepare time: 0.8850 ms elapsed time, 0.7550 ms CPU time Statement execute time: 686.0 ms elapsed time, 12.14 ms CPU time Peak memory consumed: 202.2 KB Statement: create virtual table appdemo3.vt_authors at ORA11."null".appdemo3.authors Client elapsed time: 204.0 ms Statement prepare time: 1.006 ms elapsed time, 0.7530 ms CPU time Statement execute time: 202.6 ms elapsed time, 4.047 ms CPU time Peak memory consumed: 70.23 KB Statement: create virtual table appdemo3.vt_titles at ORA11."null".appdemo3.titles Client elapsed time: 105.0 ms Statement prepare time: 0.9630 ms elapsed time, 0.7510 ms CPU time Statement execute time: 103.1 ms elapsed time, 4.195 ms CPU time Peak memory consumed: 70.18 KB Statement: create virtual table appdemo3.vt_publishers at ORA11."null".appdemo3.publishers Client elapsed time: 194.0 ms Statement prepare time: 0.9130 ms elapsed time, 0.7100 ms CPU time Statement execute time: 192.3 ms elapsed time, 3.753 ms CPU time Peak memory consumed: 70.23 KB Statement: create virtual table appdemo3.vt_orders at ORA11."null".appdemo3.orders Client elapsed time: 199.0 ms Statement prepare time: 0.8430 ms elapsed time, 0.6590 ms CPU time Statement execute time: 197.7 ms elapsed time, 3.606 ms CPU time Peak memory consumed: 70.18 KB Statement: create virtual table appdemo3.vt_orderdetails at ORA11."null".appdemo3.orderdetails Client elapsed time: 231.0 ms Statement prepare time: 0.8950 ms elapsed time, 0.6930 ms CPU time Statement execute time: 229.3 ms elapsed time, 3.867 ms CPU time Peak memory consumed: 70.23 KB Statement: create virtual table appdemo3.vt_stores at ORA11."null".appdemo3.stores Client elapsed time: 196.0 ms Statement prepare time: 1.147 ms elapsed time, 0.8430 ms CPU time Statement execute time: 194.6 ms elapsed time, 3.697 ms CPU time Peak memory consumed: 70.18 KB Statement: insert into appdemo3.demoapp_properties select * from appdemo3.vt_demoapp_properties Rows affected: 0 Client elapsed time: 141.0 ms Statement prepare time: 5.934 ms elapsed time, 5.684 ms CPU time Statement execute time: 139.5 ms elapsed time, 2.292 ms CPU time Peak memory consumed: 101.0 KB Statement: insert into authors select * from vt_authors Rows affected: 25 Client elapsed time: 214.0 ms Statement prepare time: 8.437 ms elapsed time, 8.186 ms CPU time Statement execute time: 212.1 ms elapsed time, 14.47 ms CPU time Peak memory consumed: 11.11 MB Statement: insert into appdemo3.titles select * from appdemo3.vt_titles Rows affected: 19 Client elapsed time: 216.0 ms Statement prepare time: 8.990 ms elapsed time, 8.737 ms CPU time Statement execute time: 214.0 ms elapsed time, 12.98 ms CPU time Peak memory consumed: 16.61 MB Statement: insert into appdemo3.publishers select * from appdemo3.vt_publishers Rows affected: 6 Client elapsed time: 213.0 ms Statement prepare time: 6.009 ms elapsed time, 5.667 ms CPU time Statement execute time: 211.2 ms elapsed time, 5.235 ms CPU time Peak memory consumed: 101.1 KB Statement: insert into appdemo3.orders select * from appdemo3.vt_orders Rows affected: 31 Client elapsed time: 209.0 ms Statement prepare time: 5.745 ms elapsed time, 5.494 ms CPU time Statement execute time: 207.8 ms elapsed time, 4.294 ms CPU time Peak memory consumed: 99.95 KB Statement: insert into appdemo3.orderdetails select * from appdemo3.vt_orderdetails Rows affected: 113 Client elapsed time: 204.0 ms Statement prepare time: 6.112 ms elapsed time, 5.866 ms CPU time Statement execute time: 203.6 ms elapsed time, 4.619 ms CPU time Peak memory consumed: 100.8 KB Statement: insert into appdemo3.stores select * from appdemo3.vt_stores Rows affected: 8 Client elapsed time: 209.0 ms Statement prepare time: 6.416 ms elapsed time, 6.180 ms CPU time Statement execute time: 207.7 ms elapsed time, 4.374 ms CPU time Peak memory consumed: 104.2 KB
And you can check that the data has been migrated to your SAP HANA Cloud database:
8.- REAL TIME DATA REPLICATION
For demo purposes real time replication is going to be activated only for the AUTHORS table, these are the commands you need to run for that and the output you obtain:
Now you can insert a new author in the Oracle on-prem database, you can do it with the stored procedure the application is using for that purpose (appdemo3.insert_author):
call appdemo3.insert_author ('Javier', 'Martin', '12345', 'address1', 'Madrid', 'MA', 'Spain', '11111', null);
Oracle sqlplus client can be used to execute the procedure call and to check the new author:
And finally, you can check that the new author is replicated into the SAP HANA Cloud database:
A demo video with more visual aspects about this migration can be seen here:
DEMO - E2E Oracle to SAP HANA Cloud migration with SAP Advanced SQL Migration & SAP HANA SDI
I hope you have enjoyed this blog and good luck with your database migration to SAP HANA Cloud.
Community: SAP Advanced SQL Migration
Contact: Advanced_SQL_Migration@sap.com
2 Comments
