It wouldn't be the first time we are asked about how to use SAP Advanced SQL Migration and what is the good way to proceed with it to tackle a non-SAP database migration for a custom application into any of the SAP databases supported by the migration tool (namely SAP Hana, SAP ASE, SAP SQL Anywhere and SAP IQ). In this blog I am giving some guidelines and explaining how to use the migration tool, but please take into consideration that this can be slightly modified depending on particular circunstancies.
The general scope for SAP Advanced SQL Migration in a database migration project and the steps to follow are clearly shown in the grey area in the following picture:
The first important thing to notice here is that SAP Advanced SQL Migration Tool is able to connect to the source database for some capabilities it provides, that can be donde *ONLY* for some supported sources namely Oracle, Microsoft SQL Server and DB2 LUW and it is done using jdbc through a gateway component provided with the migration tool .The main capabilities don't need the connection to the source database, but you get better results when doing it. One important remark regarding this is that when the tool is connected to the source database it performs read operations from the catalog tables only and no updates are done (no impact in overall performance is expected)
There are basically 4 high level steps for the migration tool, namely OVERVIEW/SCHEMA (0), EXTRACT (1), CONVERT/ANALYZE (2) and ITERATIVE CONVERT(3). Let's go into them with some details.
These options are not the only ones available in the migration tool, there are others that are out of the scope of this blog, for excample 'convert_file' (used to convert SQL code/queries stored in text files), 'symtab' (rebuild the internal symbol table if any object has been modified since the 'extract' or 'extrac_offline') …etc. Check SAP Advanced SQL Migration tool User Manuals for details about them.
Other related posts:
The general scope for SAP Advanced SQL Migration in a database migration project and the steps to follow are clearly shown in the grey area in the following picture:
The first important thing to notice here is that SAP Advanced SQL Migration Tool is able to connect to the source database for some capabilities it provides, that can be donde *ONLY* for some supported sources namely Oracle, Microsoft SQL Server and DB2 LUW and it is done using jdbc through a gateway component provided with the migration tool .The main capabilities don't need the connection to the source database, but you get better results when doing it. One important remark regarding this is that when the tool is connected to the source database it performs read operations from the catalog tables only and no updates are done (no impact in overall performance is expected)
There are basically 4 high level steps for the migration tool, namely OVERVIEW/SCHEMA (0), EXTRACT (1), CONVERT/ANALYZE (2) and ITERATIVE CONVERT(3). Let's go into them with some details.
0. Overview/Schema: if you are allowed to connect to source database the tool provides these 2 options to check the database and reports details about it, the commands are 'overview' and 'schema'. These commands don't do any exhaustive analysis in database objects at SQL level so they are not valid to help determining the final complexity and thus sometimes they are not used and you can directly go to the 'extract". 'Overview' and 'schema' are used to determine database properties (memory and cpu configured, character set, database size ...etc) and what kind of objects and their type appear (it returns a list of tables with their table type, triggers with trigger type, views, synonyms ...etc).
1. Extract: The migration tool needs to extract all the SQL objects and their SQL code from the source database (tables, views, constraints, synonyms, stored procedures, functions ...etc) and stored them in an internal metadata that is used later to perform the conversion. There are 2 different ways to obtain this source code depending on what is the source database and other restrictions:
- Directly connecting to the source database as explained above, 'extract' option is used in this case.. This method can be used only for Oracle, Microsoft SQL Server and DB2 (not valid for Teradata and Netezza). If connection to production is not allowed a Q&A or test environment with the same data model is also valid.
- Use text files provided by customer containing all the source objects to be injected into the migration tool, the option in this case is 'extact_offline'. The suggested way to get this source SQL code for every supported source is:
- For Oracle, use Oracle "SQL Developer".
- For Microsoft SQL Server, use "SQL Server Management Studio"
- For DB2, use "db2look" (using @ as delimiter)
- For Teradata, use BTEQ ‘SHOW’ commands for every object. SAP can provide a script to generate these ‘SHOW’ commands.
- For Netezza SAP you can use Netezza utilities like nz_ddl_table, nz_ddl_view ...etc. In this case SAP can also provide a unix shell script to do it.
- Use text files provided by customer containing all the source objects to be injected into the migration tool, the option in this case is 'extact_offline'. The suggested way to get this source SQL code for every supported source is:
2. Convert/analyze: the source SQL code has to be exhaustively analyzed to determine the complexity of the conversion. The migration tool provides 2 different options to do this, one is called 'analyze' and the other 'convert'. In both cases you get as output a great report indicating what is and is not converted automatically, when automatic conversion is not possible you get the reason and suggestions to try manual conversion and in general the areas where you should focus on. This report is called the "Complexity Assessment".The difference between 'analyze' and 'convert' is that the former is not generating the converted code (only performs the deep analysis to generate the assessment) while the latter does, so 'analyze' is faster.
NOTE: if the model is not a huge model 'convert' is suggested because you will also get the converted code where you can check further if needed.
3. Iterative convert: SAP Advanced SQL migration has many options impacting the converted code generation, in some cases for certain objects the converted code obtained is better configuring certain parameters to a certain values while in other cases the conversion is better when those same parameters are configured to different values. In these circunstancies more than one convert can be done using converted SQL code from different conversions if needed.
These options are not the only ones available in the migration tool, there are others that are out of the scope of this blog, for excample 'convert_file' (used to convert SQL code/queries stored in text files), 'symtab' (rebuild the internal symbol table if any object has been modified since the 'extract' or 'extrac_offline') …etc. Check SAP Advanced SQL Migration tool User Manuals for details about them.
Other related posts:
- SAP Managed Tags:
