The purpose of this blog is to explain in detail the process you must follow to migrate an SAP ASE database to SAP Hana Cloud. The data model and SQL objects conversion is performed with SAP Advanced SQL Migration tool (SAPAdvanced SQL Migration). The source database is running on the SAP BTP Neo environment but the same process is valid as well, with minor adjustments, if the database is running on SAP ASE on-prem.
Notice that this blog may be longer that other usual blogs, much of it is due to the details it contains and also due 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 database to SAP Hana on-prem, but the process is similar no matter what the source and target databases are:
Convert Oracle-to-SAP HANA with Advanced SQL Migration Tool
NOTE: SAP Advanced SQL Migration tool version required is 3.5.3 because this is the first version including SAP ASE as source database support.
--------------------------------------------
BLOG CONTENT:
1. Prerequisites
2. Create the data model in the ASE Neo database
3. Configure a new SAP Advanced SQL Migration tool project
4. Extract the data model using SAP Advanced SQL Migration tool Gateway
4.1 Start the SAP BTP Neo database tunnel
4.2 Start the SAP Advanced SQL Migration tool Gateway
4.3 Extract the data model
4.4 Check data model has been extracted properly
4.5 Premigration complexity assessment
5. Data model conversion to SAP Hana Cloud
5.1 Verify that data model has been extracted properly
6. Run converted SQL scripts to create the data model in Hana Cloud
7. Verify that data model & SQL objects have been created in Hana Cloud
8. Data migration
8.1 Configure SAP ASE client to access ASE database on Neo
8.2 Exporting/importing using CSV files
8.3 Jupyter notebook to move data from ASE to Hana Cloud
9. Comments
--------------------------------------------
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: SAP Advanced SQL Migration 3.5.3
- The BTP Neo environment must be installed so that SAP Advanced SQL Migration tool is able to establish a connection to the ASE Neo database using the SAP BTP Neo db tunnel and extract the data model:
Setting up the Neo environment
NOTE: for simplicity both SAP Advanced SQL Migration tool and the Neo environment can be installed in the same windows host.
- A BTP Neo subaccount is required with an ASE database system (asetest in this case) and a user database (pubs2):
NOTE 1: the database name you specify when creating the database (pubs2) is a logical name, the real database name and the ASE system name (usually called ASE server name) are automatically generated when provisioning the system and database following an internal naming convention. In this example the real database name is NEO_4DZTQSZ0AJLQVS01KLISM9HOH and the ASE server name is HA00CY9:
NOTE 2: These names will be used later when configuring the SQL conversion project and extracting the data model.
2.- CREATE THE DATA MODEL IN THE ASE NEO DATABASE
The demo “pubs2” data model shipped with SAP ASE can be used for testing purposes, use this SQL script to create the data model in the SAP BTP ASE Neo database pubs2:
SAP ASE Neo pubs2 database SQL script
The data model consists of some tables, 1 view, some procedures … etc.:
3.- 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:/exodus_tests/pubs2_ASE_neo
In the first popup window select SAP ASE as source database and SAP Hana Cloud as target database and click “Save & Continue”:
In the next window fill in the project parameters:
The needed and suggested parameters to modify are:
- Param ID #110 > IP address f/ hostname where the migration tool Gateway runs.
- Param ID #111 > port where the gateway will listen (e.g., 12345).
- Param ID #112 > set to 600.
- Param ID #114 > ASE is on BTP Neo so select ‘NEO’.
- Param ID #121 > specify the real database name in the ASE Neo system.
- 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 (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 SYSTEM 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)
4.- EXTRACT THE DATA MODEL USING SAP ADVANCED SQL MIGRATION TOOL GATEWAY
4.1.- Start the SAP BTP Neo database tunnel
This is required to connect to the ASE BTP Neo database, the neo command used is “open-db-tunnel”:
As indicated in the output the connection to the database can be established using localhost as host and port number 30015, these properties must be used when executing the migration tool “extract” command later.
4.2.- 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 #111 (details for this in the migration tool user guide or the tutorial indicate above):
4.3.- 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 <proj_folder> <server_name> <server_host> <port> <db_user> <db_passwd>
In this case the final command is:
c:/sapdbmtk 128# sapdbmtk extract c:/exodus_tests/pubs2_ASE_neo HA00CY9 localhost 30015 javier XXXXXXXX
Initializing...
Logfile for this session: c:/exodus_tests/pubs2_ASE_neo/reports/HA00CY9.SQL-Extraction.ASE-HANA.2023Oct26.123925.log
SAP Advanced SQL Migration v.3.5.3, Sept 2023
================================================================================
>>> The installation/operation guidelines as described in the <<<
>>> SAP Advanced SQL Migration documentation must be strictly adhered to. <<<
>>> Copyright (c) 2023 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 Advanced SQL Migration community edition, 31-May-2020, 64791
================================================================================
Confirmation record found: Application Intellectual Property rights compliance verified.
Confirmation record found: SAP ASE / ASE BTP-Neo source DBMS server licensing compliance verified.
Creating directory c:/exodus_tests/pubs2_ASE_neo/extracted ...
Creating directory c:/exodus_tests/pubs2_ASE_neo/extracted/catalog ...
Connecting to ASE DBMS (via gateway at 192.168.1.42:12345)...
Socket connection established to 192.168.1.42:12345 (host:port)
Gateway timeout: 600 seconds
Successfully connected to connectivity gateway (version 3) at 192.168.1.42:12345 (host:port)
Successfully connected (via gateway) to ASE DBMS server at localhost:30015 (host:port)
All SQL queries executed against this ASE DBMS are logged in c:/exodus_tests/pubs2_ASE_neo/reports/HA00CY9.SQL-Extraction.ASE-HANA.2023Oct26.123925.sql.log
…
…
…
(10/12) Scanning c:/exodus_tests/pubs2_ASE_neo/extracted/NEO_4DZTQSZ0AJLQVS01KLISM9HOH.titleview.VIEW.tsql (8 lines, 1 second)
(11/12) Scanning c:/exodus_tests/pubs2_ASE_neo/extracted/NEO_4DZTQSZ0AJLQVS01KLISM9HOH.title_proc.PROCEDURE.tsql (8 lines, 1 second)
(12/12) Scanning c:/exodus_tests/pubs2_ASE_neo/extracted/NEO_4DZTQSZ0AJLQVS01KLISM9HOH.totalsales_trig.TRIGGER.tsql (27 lines, 1 second)
Disconnected (via gateway) from DBMS server.
Warning: The gateway is still running!
Do not forget to shut it down!
********************************************************************************
Ready!
***** Run Summary - SAP Advanced SQL Migration v.3.5.3 *************************
Warning: The gateway is still running!
Do not forget to shut it down!
Run start : 26-Oct-2023 12:39:25
Run end : 26-Oct-2023 12:39:37
Run duration : 12 seconds
Action performed : Extract SQL/DDL/schema from file from DBMS server
Source DBMS : SAP ASE / ASE BTP-Neo
Project directory : c:/exodus_tests/pubs2_ASE_neo
Generated files:
Extracted SQL objects : c:/exodus_tests/pubs2_ASE_neo/extracted (12 files, *.tsql; 135 lines)
Session log file/report : c:/exodus_tests/pubs2_ASE_neo/reports/HA00CY9.SQL-Extraction.ASE-HANA.2023Oct26.123925.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 129#
4.4.- 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.5.- Premigration complexity assessment
SAP Advanced SQL Migration can analyze the extracted SQL data model and SQL objects and provide details about the SQL migration complexity. This is done using the migration tool command “analyze” once the data model has been extracted. The syntax for this command is:
sapdbmtk analyze <project_folder>
In this case the command is:
sapdbmtk analyze c:/exodus_tests/pubs2_ASE_neo
The report generated has a section informing about the SQL migration complexity:
- This migration project is classified as low-to-medium complexity with an automatic conversion ratio of 90%.
- The number of issues with “more impact” is zero.
- The number of issues reported with “less impact” is 4, they are due to a compatible data type conversion, so they are not expected to be a real problem.
We are in front of a really good case where all objects are expected to be converted well.
NOTE: the migration tool command "convert" (instead of "analyze") also provides the sample complexity assessment but it takes longer because it goes through the real conversion process.
5.- DATA MODEL CONVERSION TO SAP HANA CLOUD
Once the data model is extracted, we 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:/exodus_tests/pubs2_ASE_neo
Check the end of the output in the command line to check that no errors happen:
c:/sapdbmtk 139# sapdbmtk convert c:/exodus_tests/pubs2_ASE_neo
Initializing...
Logfile for this session: c:/exodus_tests/pubs2_ASE_neo/reports/SQL-Conversion.ASE-HANA.2023Oct26.142101.log
SAP Advanced SQL Migration v.3.5.3, Sept 2023
================================================================================
>>> The installation/operation guidelines as described in the <<<
>>> SAP Advanced SQL Migration documentation must be strictly adhered to. <<<
>>> Copyright (c) 2023 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 Advanced SQL Migration community edition, 31-May-2020, 64791
================================================================================
Confirmation record found: Application Intellectual Property rights compliance verified.
Creating directory c:/exodus_tests/pubs2_ASE_neo/converted ...
Creating directory c:/exodus_tests/pubs2_ASE_neo/scripts ...
===== SAP Advanced SQL Migration v.3.5.3 =======================================
Source DBMS selected : SAP ASE / ASE BTP-Neo
Target DBMS selected : SAP Hana Cloud >= QRC4-22 (SQLScript)
Command line : sapdbmtk convert c:/exodus_tests/pubs2_ASE_neo
Action performed : SQL conversion (+migration effort estimate)
Project directory : c:/exodus_tests/pubs2_ASE_neo
Source DBMS version : SAP ASE / ASE BTP-Neo v.160 (Adaptive Server Enterprise/16.0 SP03 PL13/EBF 30517 SMP/P/x86_64/SLES 11.1/ase160sp03pl13x/4184/64-bit/FBO/Sun Nov 27 06:18:49 2022)
Source DBMS servername : HA00CY9
Session logfile : c:/exodus_tests/pubs2_ASE_neo/reports/SQL-Conversion.ASE-HANA.2023Oct26.142101.log
Run starting at : 26-Oct-2023 14:21:01
OS Process ID : 8015
================================================================================
Reading metadata........
***** SQL Conversion Section ***************************************************
12 SAP ASE / ASE BTP-Neo SQL files found in c:/exodus_tests/pubs2_ASE_neo/extracted
(1/12) Converting c:/exodus_tests/pubs2_ASE_neo/extracted/NEO_4DZTQSZ0AJLQVS01KLISM9HOH.byroyalty.PROCEDURE.tsql (5 lines)
Writing c:/exodus_tests/pubs2_ASE_neo/converted/NEO_4DZTQSZ0AJLQVS01KLISM9HOH.byroyalty.PROCEDURE.sqlscript (30 lines)
5 lines processed in 1 second (5 lines/sec)
Conversion coverage: 99%
SQL items to be resolved: 0 (in 0 categories)
Effort estimate for this file(functional migration): 0
Conversion statistics for this file: Effort breakdown(1 day=8 hrs)
Procedure declaration : 1 0 Fully converted
SELECT statement : 1 0 Fully converted
…
…
…
***** Created objects(in generated scripts) ************************************
user group : 1
user (object owner) : 2
user (user, non-owner) : 10
table : 11
view : 1
table column : 60
view column : 6
index(non-unique,suppressed) : 7
unique constraint : 5
procedure : 9
trigger : 2
********************************************************************************
Ready!
***** Run Summary - SAP Advanced SQL Migration v.3.5.3 *************************
Run start : 26-Oct-2023 14:21:01
Run end : 26-Oct-2023 14:21:07
Run duration : 6 seconds
Action performed : SQL conversion (+migration effort estimate)
Source DBMS : SAP ASE / ASE BTP-Neo
Target DBMS : SAP Hana Cloud >= QRC4-22 (SQLScript)
Project directory : c:/exodus_tests/pubs2_ASE_neo
Conversion coverage: 89%; 135 lines of SQL
Total estimated effort(functional migration): 2 days (1 day=8 hrs)
Complexity assessment summary: Low-to-Medium complexity, Resolve items: redesign:0 rewrite:4 adjust/verify:47
Generated files:
Converted SQL objects : c:/exodus_tests/pubs2_ASE_neo/scripts/converted (12 files, *.sqlscript; 496 lines)
Conversion log files(1/SQL object) : c:/exodus_tests/pubs2_ASE_neo/scripts/converted (12 files, *.convertlog)
Ready-to-run shell scripts : c:/exodus_tests/pubs2_ASE_neo/scripts (2 files, run_*.sh)
SQL DDL scripts : c:/exodus_tests/pubs2_ASE_neo/scripts (9 files, *.sqlscript)
Schema aspects to be resolved : c:/exodus_tests/pubs2_ASE_neo/scripts/_ASE-HANA.schema.convertlog (42 items reported)
Session log file/report : c:/exodus_tests/pubs2_ASE_neo/reports/SQL-Conversion.ASE-HANA.2023Oct26.142101.log
***** Next Steps ***************************************************************
Suggested next steps:
- Execute the generated shell scripts (run_ddl_scripts.sh and run_sql_scripts.sh),
located in <project-dir>/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 140#
5.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 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, FKs … etc.
- run_sql_scripts.sh – shell script to create SQL objects containing SQL code (stored procedures, functions and triggers)
6.- RUN CONVERTED SQL SCRIPTS TO CREATE THE DATA MODEL IN 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:/exodus_tests/pubs2_ASE_neo/scripts 121# run_ddl_scripts.sh
Using hdbsql: /cygdrive/c/Program Files/SAP/hdbclient/hdbsql
HDBSQL version 2.13.13.1650313242, the SAP HANA Database interactive terminal.
Copyright 2000-2022 by SAP SE.
Time on client system: Thu Dec 28 14:56:13 RDT 2023
-----------------------------------------------------------------------------
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.1698146098
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
…
…
…
Batch starting at line #115:
1) CALL sapdbmtk.sp_dbmtk_object_install_custom( 'view', 'NEO_4DZTQSZ0AJLQVS01KLISM9HOH', 'titleview') 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 view 'NEO_4DZTQSZ0AJLQVS01KLISM9HOH.titleview': ready!
Batch starting at line #118:
1) --------------------------------------------------------------------------------
2)
3)
4) /*
5) * Generated DDL for 1 views
6) *
7) * End of 'dbmtk_create_views.sqlscript'
8) */
Messages while executing 'dbmtk_create_views.sqlscript': 2 --> (0 errors; 2 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_ASE.sqlscript': 108 --> (0 errors; 108 warnings)
Messages while executing 'dbmtk_HANAC_runtime_custom.sqlscript': 1 --> (0 errors; 1 warnings)
Messages while executing 'dbmtk_create_users.sqlscript': 36 --> (0 errors; 36 warnings)
Messages while executing 'dbmtk_create_tables.sqlscript': 12 --> (0 errors; 12 warnings)
Messages while executing 'dbmtk_create_proc_headers.sqlscript': 10 --> (0 errors; 10 warnings)
Messages while executing 'dbmtk_create_views.sqlscript': 2 --> (0 errors; 2 warnings)
Total server messages: 251
Total errros : 0
Total warnings : 251
Total files : 8
Files w/zero messages: 1
===== end of script: run_ddl_scripts.sh =====
Session log: run_ddl_scripts.out
c:/exodus_tests/pubs2_ASE_neo/scripts 122#
Then run_sql_scripts.sh should be executed:
c:/exodus_tests/pubs2_ASE_neo/scripts 122# run_sql_scripts.sh
Using hdbsql: /cygdrive/c/Program Files/SAP/hdbclient/hdbsql
HDBSQL version 2.13.13.1650313242, the SAP HANA Database interactive terminal.
Copyright 2000-2022 by SAP SE.
Time on client system: Thu Dec 28 15:04:49 RDT 2023
-----------------------------------------------------------------------------
=== (1/12) ./converted/NEO_4DZTQSZ0AJLQVS01KLISM9HOH.deltitle.TRIGGER.sqlscript ==========
Batch starting at line #1:
1) /*
2) * This file : NEO_4DZTQSZ0AJLQVS01KLISM9HOH.deltitle.TRIGGER.sqlscript
3) * Converted from : NEO_4DZTQSZ0AJLQVS01KLISM9HOH.deltitle.TRIGGER.tsql
4) *
5) * Source DBMS : SAP ASE / ASE BTP-Neo v.160 (Adaptive Server Enterprise/16.0 SP03 PL13/EBF 30517 SMP/P/x86_64/SLES 11.1/ase160sp03pl13x/4184/64-bit/FBO/Sun Nov 27 06:18:49 2022)
6) * Source DB servername : HA00CY9
7) * Source DB name : NEO_4DZTQSZ0AJLQVS01KLISM9HOH
8) * Target DBMS : SAP Hana Cloud >= QRC4-22 (SQLScript)
9) *
10) *
11) * User config(id=101) : Include default configuration settings in header of generated files: Setting=yes
12) * User config(id=423) : Report identifier upper/lowercase analysis: Setting=no
13) * User config(id=330) : Case mapping for usernames: Setting=nomap
14) * User config(id=331) : Case mapping for names of schema objects (tables,views,columns,indexes,sequences,etc.): Setting=nomap
15) * User config(id=332) : Case mapping for names of SQL objects (procedures, functions; not for variables/parameters): Setting=nomap
16) * User config(id=335) : Apply case mappings also when source DBMS is case-sensitive for identifiers: Setting=no
17) * User config(id=390) : User/schema name to map the 'dbo' user to (blank=unchanged): Setting=
18) * User config(id=350) : Conversion of explicit source database name in object names: Setting=keep
19) * User config(id=351) : Conversion of remote object name: Setting=remote
20) * User config(id=340) : Default interpretation of double-quoted strings: Setting=string
21) * User config(id=211) : Include an 'ORIGSQL' comment with the original SQL in the converted SQL: Setting=yes
22) * User config(id=212) : Max. number of characters of original SQL in 'ORIGSQL' comment: Setting=80
23) * User config(id=215) : Append 'NOT CONVERTED' comment to unconverted statements in the converted SQL: Setting=yes
24) * User config(id=217) : Report additional details about some conversion categories, either for all files or only the totals: Setting=all
25) * User config(id=218) : Report details ordered by functionality or by impact: Setting=functionality
26) * User config(id=230) : Length to convert an ASE variable/parameter declared with (MAX) length: Setting=MAX
27) * User config(id=231) : Length to convert a CHAR/BINARY datatype exceeding the target DBMS length: Setting=MAX
28) * User config(id=232) : Convert BIT data type as BOOLEAN: Setting=no
29) * User config(id=275) : Conversion of MS SQL Server COMMIT sequence: Setting=all
30) * User config(id=276) : Conversion of MS SQL Server error handling control flow: Setting=errorcheck
31) * User config(id=292) : Conversion of PRINT-style statements: Setting=sqlscript_print
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=warning
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:/exodus_tests/pubs2_ASE_neo
46) *
47) *-----------------------------------------------------------------------------
48) * Generated by SAP Advanced SQL Migration v.3.5.3 at 26-Oct-2023 14:21:04
49) * (generated with: sapdbmtk convert, 64791)
50) * SAP Advanced SQL Migration is property of SAP SE.
51) *-----------------------------------------------------------------------------
52) */
Batch starting at line #54:
1) CALL sapdbmtk.sp_dbmtk_report_connect('NEO_4DZTQSZ0AJLQVS01KLISM9HOH.deltitle.TRIGGER.sqlscript') WITH HINT(IGNORE_PLAN_CACHE)
Executing script 'NEO_4DZTQSZ0AJLQVS01KLISM9HOH.deltitle.TRIGGER.sqlscript'...
Connected to SAP HANA server named: H00, database: H00, version: 4.00.000.00.1698146098
Connected as user 'DBADMIN'
…
…
…
Batch starting at line #99:
1)
2)
3) ---------- Locking user TESTUSER ----------
4) ALTER USER TESTUSER DEACTIVATE USER NOW;
Batch starting at line #104:
1)
2)
3)
4) /*
5) * Generated DDL for locking 12 users
6) * End of 'dbmtk_lock_users.sqlscript'
7) */
Messages while executing './dbmtk_lock_users.sqlscript': 0 --> (0 errors; 0 warnings)
Summary:
========
Messages while executing './converted/NEO_4DZTQSZ0AJLQVS01KLISM9HOH.deltitle.TRIGGER.sqlscript': 1 --> (0 errors; 1 warnings)
Messages while executing './converted/NEO_4DZTQSZ0AJLQVS01KLISM9HOH.totalsales_trig.TRIGGER.sqlscript': 1 --> (0 errors; 1 warnings)
Messages while executing './converted/NEO_4DZTQSZ0AJLQVS01KLISM9HOH.byroyalty.PROCEDURE.sqlscript': 2 --> (0 errors; 2 warnings)
Messages while executing './converted/NEO_4DZTQSZ0AJLQVS01KLISM9HOH.discount_proc.PROCEDURE.sqlscript': 2 --> (0 errors; 2 warnings)
Messages while executing './converted/NEO_4DZTQSZ0AJLQVS01KLISM9HOH.history_proc.PROCEDURE.sqlscript': 2 --> (0 errors; 2 warnings)
Messages while executing './converted/NEO_4DZTQSZ0AJLQVS01KLISM9HOH.insert_salesdetail_proc.PROCEDURE.sqlscript': 2 --> (0 errors; 2 warnings)
Messages while executing './converted/NEO_4DZTQSZ0AJLQVS01KLISM9HOH.insert_sales_proc.PROCEDURE.sqlscript': 2 --> (0 errors; 2 warnings)
Messages while executing './converted/NEO_4DZTQSZ0AJLQVS01KLISM9HOH.storeid_proc.PROCEDURE.sqlscript': 2 --> (0 errors; 2 warnings)
Messages while executing './converted/NEO_4DZTQSZ0AJLQVS01KLISM9HOH.storename_proc.PROCEDURE.sqlscript': 2 --> (0 errors; 2 warnings)
Messages while executing './converted/NEO_4DZTQSZ0AJLQVS01KLISM9HOH.titleid_proc.PROCEDURE.sqlscript': 2 --> (0 errors; 2 warnings)
Messages while executing './converted/NEO_4DZTQSZ0AJLQVS01KLISM9HOH.title_proc.PROCEDURE.sqlscript': 2 --> (0 errors; 2 warnings)
Messages while executing './dbmtk_lock_users.sqlscript': 0 --> (0 errors; 0 warnings)
Total server messages: 20
Total errros : 0
Total warnings : 20
Total files : 12
Files w/zero messages: 1
===== end of script: run_sql_scripts.sh =====
Session log: run_sql_scripts.out
c:/exodus_tests/pubs2_ASE_neo/scripts 123#
7.- VERIFY THAT DATA MODEL & 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 called “NEO_4DZTQSZ0AJLQVS01KLISM9HOH”, this was the original database name in the source BTP ASE Neo database system:
8.- 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. One of the typical approaches is data extraction to text files and then data import from text files that can be located in any different supported file storages.
Here are described a couple of the simple possibilities more but you can choose any other way you prefer.
8.1- Configure SAP ASE client to access ASE database on Neo.
- Star the SAP BTP Neo db tunnel (check section 4.1 above).
- Run the following openssl command with showcerts option to get the client certificate:
c:/sapdbmtk 102# openssl s_client -connect localhost:30015 -showcerts
CONNECTED(00000004)
Can't use SSL_get_servername
depth=0 CN = HA00CY9
verify error:num=20:unable to get local issuer certificate
verify return:1
depth=0 CN = HA00CY9
verify error:num=21:unable to verify the first certificate
verify return:1
depth=0 CN = HA00CY9
verify return:1
---
Certificate chain
0 s:CN = HA00CY9
i:CN = CA
-----BEGIN CERTIFICATE-----
MIIC8DCCAdigAwIBAgIELjYJCDANBgkqhkiG9w0BAQsFADANMQswCQYDVQQDEwJD
QTAeFw0yMzEwMjEwNTE5NTZaFw0zMzEwMTgwNTE5NTZaMBIxEDAOBgNVBAMTB0hB
MDBDWTkwggEiMA0GCSqGSIb3DQEBAQUAA4IBDwAwggEKAoIBAQCeN8abL9I9dKK+
Srvmd8B+sqay0wsW+VmHMieGAqCEmLzDi9K63IPYMvoxcNtZln0rRyNmdnddcyVH
XzFZst+beE70Nu5OQy+fkzBYIu6ZfOArdfgMijfyWbkPrBXPPZACje8jCzF3OoOL
J8gXD0MUEsM16SzepyroNVDc4J5jR3gDH0JrTtAWsiIATGGEsTnStSEWpawhgVbO
Pyq4zMuO6263j2EXcJ+8w6L69usSEBvW65ZesTjA5UkTAuXgpSnfio+L7HvIInva
m9dWJv+UqtiS7aqkq6VUysaQagT8ddRCUtYeS/KDJhUUqhZAbqNhVxTtlti7/xGz
RqWlheN7AgMBAAGjUzBRMB0GA1UdDgQWBBTV4VSCkcYs/zlWbhsKQ8bWTWm7ZzAP
BgNVHRMBAf8EBTADAQH/MB8GA1UdIwQYMBaAFBsDJc7f0u4PvRjz+lGPcOlSF1R5
MA0GCSqGSIb3DQEBCwUAA4IBAQAxIEk1DZ/ZzHxyIVhvb2vxY00ux/jOPrraXrfV
mUTOqd3UAs4ZtQlXWxBNM00zBLu0FKYtq9mLJrkidm6DfKKmlwO6stvVN7uP1hzx
QGLQ0YfjgfJRsvdODIfur2J09dD2Dnx8Fy4M1FwMOXf1oWWZ6QKqp4739WcVnRQi
01Thtz6uWBgohqrogbndPiWpqRFlnIp5Ay2KlzvjSTMZUzvJKdw20BJMdFGjf0/5
8OYMpWFaTom0pdEEkdz7a2nYANiwTEHLRSdCzLclt6W1pYkcJ5PeQ7pd4RQE2M9j
VWPxHaOqU/W2LPbr2gBGNKLbVSQK8LvZiomILjgr0ujIwMe4
-----END CERTIFICATE-----
---
Server certificate
subject=CN = HA00CY9
issuer=CN = CA
---
No client certificate CA names sent
---
SSL handshake has read 917 bytes and written 621 bytes
Verification error: unable to verify the first certificate
---
New, TLSv1.2, Cipher is AES128-GCM-SHA256
Server public key is 2048 bit
Secure Renegotiation IS supported
Compression: NONE
Expansion: NONE
No ALPN negotiated
SSL-Session:
Protocol : TLSv1.2
Cipher : AES128-GCM-SHA256
Session-ID: A2EC33F6490E786097B404C835D1EE9FEBED919CBC9413C15D7BDB5DAA8466A1
Session-ID-ctx:
Master-Key: 5EC067E43CF22DD1EF0C1EACA200D9B7772541EF6715EC0E47866BA24A636D6725838E959F020501296AD95A88F24958
PSK identity: None
PSK identity hint: None
SRP username: None
Start Time: 1699882169
Timeout : 7200 (sec)
Verify return code: 21 (unable to verify the first certificate)
Extended master secret: no
---
The —–BEGIN CERTIFICATE—– ……..—–END CERTIFICATE—– section will be used later and the CN (CN = HA00CY9) will be used too.
- Add the certificate returned by the ‘openssl … -showcerts‘ command to the trust.cert file under the SAP/Sybase folder (“ini” folder in windows and “config” in Unix/Linux), this is what you need to add:
—–BEGIN CERTIFICATE—–
MIIC8DCCAdigAwIBAgIELjYJCDANBgkqhkiG9w0BAQsFADANMQswCQYDVQQDEwJD
QTAeFw0yMzEwMjEwNTE5NTZaFw0zMzEwMTgwNTE5NTZaMBIxEDAOBgNVBAMTB0hB
MDBDWTkwggEiMA0GCSqGSIb3DQEBAQUAA4IBDwAwggEKAoIBAQCeN8abL9I9dKK+
Srvmd8B+sqay0wsW+VmHMieGAqCEmLzDi9K63IPYMvoxcNtZln0rRyNmdnddcyVH
XzFZst+beE70Nu5OQy+fkzBYIu6ZfOArdfgMijfyWbkPrBXPPZACje8jCzF3OoOL
J8gXD0MUEsM16SzepyroNVDc4J5jR3gDH0JrTtAWsiIATGGEsTnStSEWpawhgVbO
Pyq4zMuO6263j2EXcJ+8w6L69usSEBvW65ZesTjA5UkTAuXgpSnfio+L7HvIInva
m9dWJv+UqtiS7aqkq6VUysaQagT8ddRCUtYeS/KDJhUUqhZAbqNhVxTtlti7/xGz
RqWlheN7AgMBAAGjUzBRMB0GA1UdDgQWBBTV4VSCkcYs/zlWbhsKQ8bWTWm7ZzAP
BgNVHRMBAf8EBTADAQH/MB8GA1UdIwQYMBaAFBsDJc7f0u4PvRjz+lGPcOlSF1R5
MA0GCSqGSIb3DQEBCwUAA4IBAQAxIEk1DZ/ZzHxyIVhvb2vxY00ux/jOPrraXrfV
mUTOqd3UAs4ZtQlXWxBNM00zBLu0FKYtq9mLJrkidm6DfKKmlwO6stvVN7uP1hzx
QGLQ0YfjgfJRsvdODIfur2J09dD2Dnx8Fy4M1FwMOXf1oWWZ6QKqp4739WcVnRQi
01Thtz6uWBgohqrogbndPiWpqRFlnIp5Ay2KlzvjSTMZUzvJKdw20BJMdFGjf0/5
8OYMpWFaTom0pdEEkdz7a2nYANiwTEHLRSdCzLclt6W1pYkcJ5PeQ7pd4RQE2M9j
VWPxHaOqU/W2LPbr2gBGNKLbVSQK8LvZiomILjgr0ujIwMe4
—–END CERTIFICATE—–
- Modify the interfaces file (sql.ini in Windows systems or interfaces in Unix) adding and entry for the ASE server, specifying ssl with the CN returned by the ‘openssl … -showcerts’ command:
[NEO_ASE16]
query=TCP,localhost,30015,ssl=”CN=’HA00CY9′”
- Verify the connection with isql using a valid user and the corresponding password:
C:\Users\I063260>isql -Ujavier -PXXXXXXX -SNEO_ASE16
1> select db_name()
2> go
——————————
NEO_4DZTQSZ0AJLQVS01KLISM9HOH
(1 row affected)
8.2.- Exporting/importing using CSV files.
SAP ASE “bcp” tool can be used to export data from tables into files. Create the corresponding “bcp out” commands to export the data:
bcp NEO_4DZTQSZ0AJLQVS01KLISM9HOH.authors out authors.csv -Ujavier -Ppwd -S NEO_ASE16 -c
bcp NEO_4DZTQSZ0AJLQVS01KLISM9HOH.publishers out publishers.csv -Ujavier -Ppwd -S NEO_ASE16 -c
bcp NEO_4DZTQSZ0AJLQVS01KLISM9HOH.roysched out roysched.csv -Ujavier -Ppwd -S NEO_ASE16 -c
bcp NEO_4DZTQSZ0AJLQVS01KLISM9HOH.sales out sales.csv -Ujavier -Ppwd -S NEO_ASE16 -c
bcp NEO_4DZTQSZ0AJLQVS01KLISM9HOH.salesdetail out salesdetail.csv -Ujavier -Ppwd -S NEO_ASE16 -c
bcp NEO_4DZTQSZ0AJLQVS01KLISM9HOH.titleauthor out titleauthor.csv -Ujavier -Ppwd -S NEO_ASE16 -c
bcp NEO_4DZTQSZ0AJLQVS01KLISM9HOH.titles out titles.csv -Ujavier -Ppwd -S NEO_ASE16 -c
bcp NEO_4DZTQSZ0AJLQVS01KLISM9HOH.stores out stores.csv -Ujavier -Ppwd -S NEO_ASE16 -c
bcp NEO_4DZTQSZ0AJLQVS01KLISM9HOH.discounts out discounts.csv -Ujavier -Ppwd -S NEO_ASE16 -c
bcp NEO_4DZTQSZ0AJLQVS01KLISM9HOH.au_pix out au_pix.csv -Ujavier -Ppwd -S NEO_ASE16 -c
bcp NEO_4DZTQSZ0AJLQVS01KLISM9HOH.blurbs out blurbs.csv -Ujavier -Ppwd -S NEO_ASE16 -c
With the SAP BTP Neo db tunnel started run these “bcp out” commands and all data from the tables will be exported to files, one file per table will be generated, for example:
C:\Tmp\pubs2_data>bcp NEO_4DZTQSZ0AJLQVS01KLISM9HOH.authors out authors.csv -Ujavier -Ppwd -S NEO_ASE16 -c
Starting copy...
23 rows copied.
Clock Time (ms.): total = 94 Avg = 4 (244.68 rows per sec.)
Now import data using the Hana Cloud database explorer “Import Data” option right clicking on the table and selecting the CSV file where the data is located, this is the example for table AUTHORS:
Repeat the same process for the other tables.
8.3.- Jupyter notebook to move data from ASE to Hana Cloud
The Jupyter notebook attached below is able to connect to the source ASE Neo Database, connect to the SAP Hana Cloud database as well and move the data from source tables in ASE to target tables in Hana Cloud:
Jupyter notebook to move data from SAP ASE to SAP HANA Cloud
NOTES:
- The SAP BTP Neo db tunnel must be started to access the SAP ASE NEO (section 4.1).
- The SAP ASE client must be configured too as indicated in section 8.1
- You need to configure the Hana Cloud parameters accordingly (instance ID, data center, user and password).
This is the output obtained when running the notebook:
9.- Comments
- SAP Advanced SQL Migration is not analyzing the SQL code and improving it taking into consideration the features provided in the target database, it basically converts 1:1 every SQL structure and SQL object.
- Getting 100% of the data model automatically converted and working properly is desired but almost impossible to get in real life, the experience shows that some manual adaptations/modifications are typically needed after the conversion process.
- SAP Advanced SQL Migration tool doesn’t guarantee that the converted code is working well from both functional and performance point of views so functional and performance tests will be required to detect problems and adjust the converted data model as required in each case.
- Data migration is out of the scope of SAP Advanced SQL migration tool, so it is not deeply considered in this document. The document explains a couple of ways to load the data from ASE into Hana Cloud that may be valid for some cases with low data volume but will not be the best approach in all cases.
Enjoy this blog and contact Advanced_SQL_Migration@sap.com if you need further help or have further questions/clarifications.
Community: SAP Advanced SQL Migration
Contact: Advanced_SQL_Migration@sap.com
