New in SAP Advanced SQL Migration v3.4: Support for Netezza & Hana 2 SP04
Today we released version 3.4 of the SAP Advanced SQL Migration tool. If you are a registered user .of the migration tool, you will receive this new release in the coming days.
Version 3.4 has two big new features:
- Support for converting from Netezza to SAP Hana
- Support for conversion to Hana 2 SP04, utilizing many of the new features in SP04
First, about Netezza.
Following many requests for a tool to convert Netezza SQL to the Hana SQL dialect, we developed support for Netezza as a source DBMS.
Only Netezza DDL files are supported as input: we do not currently support a connection to the Netezza database, as we do with Oracle, MS SQL Server and DB2. This means the customer needs to reverse-engineer the Netezza database structure so that the generated DDL files can be used as input. SAP provides a script to perform this reverse-engineering.
It is quite common to see a Netezza application spread across multiple Netezza databases (I’ve seen as many as 130 in one application). When migrating to Hana, it is usually best to merge these Netezza databases into a single Hana tenant database, requiring some (fully automatic) adjustment of the Netezza schema names by the migration tool. As with many aspects of the SAP Advanced SQL Migration tool, this merging of databases is configurable.
We have recently won our first few customer opportunities for Netezza-to-Hana migration, and now that we are releasing support for Netezza to all registered users, let’s hope we will see more successes soon.
As for Hana 2 SP04:
In mid-2019, Hana 2 SP04 was released. While that may have sounded like just another Hana release, this was actually a pretty big deal from a SQL perspective as SP04 contains many new features. For the migration tool, this allowed us to implement conversions that were not possible until now (like preserving the sequential @@ERROR checking control flow from Microsoft SQL Server after migrating to Hana – a separate blog will cover this soon).
SP04 also allows us to implement a more direct and simple conversion for some cases where we had previously implemented a more complex workaround. In practice, this means implementing an additional conversion since we need to keep the previous conversion for those customers who have not yet deployed SP04.
Given the large number of new SQL features in SP04, this meant a lot of engineering work. These are some of the main SP04 features that are now utilized by the migration tool:
- Transactional savepoints
- Statement triggers on column tables
- WITH READ ONLY views
- Direct INSERT/UPDATE/DELETE on table variables
- WITH HOLD cursor declarations
- Support for recursive procedure/function calls
- [NOT] EXISTS in SQLScript IF/WHILE statements
- IN (…) in SQLScript IF/WHILE statements
- CONTINUE handlers
- Lateral joins
When select “Hana 2 SP04” as the target DBMS in the configuration dialog in the migration tool, the generated Hana SQL/SQLScript code will utilize the new SQL features as much as possible.