You have probably heard about the SQL dialects conversion tool called SAP Advanced SQL Migration developed by SAP, this tool helps you to migrate non-SAP SQL code to SAP SQL code and this is the first of a series of technical blogs that will describe internal details about how that SAP tool works and how the conversion for some special functionalities/aspects from the distinct supported source Database Management Systems are being implemented.
In this first blog I am going to explain and describe a very important concept in the migration tool called Run Time Component (RTC in short). An RTC is a stored procedure or a function provided by the Migration Tool to be used when there is not corresponding one for a source procedure or function in the target database or when it is needed to compensate some semantic differences in behavior between the non-SAP source Database and the SAP target Database.
What you would be ideally expecting when you are migrating from a Database system to a different one is that every Built-In Function and Stored Procedure has a corresponding one in the target Database, but real life is different and that does not happen in all cases for different reasons:
- The different Database vendors in the market are not currently following the ANSI standard in all the aspects
- Each Database vendor has its own and particular extensions to the standard adding non-standard functionalities
- Each vendor follows its name convention for naming functions/procedures and the parameters and behavior for what seems a similar function or procedure can vary from one to another
When the migration tool finds in the source code a function or procedure call that has a corresponding one in the target Database fully matching the behavior the converted code directly calls the corresponding function or proc, for example oracle SYSDATE Built-In Function is directly converted to CURRENT_TIMESTAMP in Hana and CURRENT_BIGDATETIME in ASE:
When a call to a function or procedure has no corresponding one or has a corresponding one but with different behavior the migration tool provides a function or procedure covering the behavior in the source Database (RTC) and the converted SQL code calls that RTC, for example Oracle NEXT_DAY function has 2 parameters and there is a similar function in Hana with same name but has only one parameter and cannot be used because of there are semantic differences, so the migration tool provides an RTC called sp_f_dbmtk_next_weekday behaving in the same way oracle does:
We can classify RTCs in 3 different kinds:
- Simple RTC: one simple RTC is created to implement a source built-in function or procedure, sp_f_dbmtk_next_weekday mentioned above fits into this kind.
- Complex RTC: the source función or stored procedure is complex and a set of RTCs (a main RTC + some auxiliar RTCs ) is created to mimic the behavior, one example can be Oracle to Hana RTC called sp_f_dbmtk_format_datetime_to_string created to mimic TO_CHAR Oracle function considering some cases that are not considered in Hana TO_VARCHAR conversion function.
- A set or RTCs created to implement a complex functionality covered by more than one source stored procedure and/or functions. An example is the the set of RTC created to implement Oracle DBMS_OUTPUT functionality.
More SAP Advanced SQL Migration tool topics coming soon …
Other related posts: