One of the element of any migration is built-in functions conversion. Some of them are the same, some are similar but different and some does not have any direct equivalent and requires additional developing. Freely available http://www.redbooks.ibm.com/redbooks/pdfs/sg246672.pdf ("Function mapping" chapter) contains a good number of examples how to convert SQL Server built-in functions to DB2.
Conversion is quite easy but what to do if we want to get SQL statements migrated to DB2 and keep backward compatibility at the same time.
It is more complicated but also possible. I created a simple project with examples - it is available here.
- SQL Server functions
- SQL Server tests
- DB2 functions (equivalence of SQL Server)
- DB2 tests
Solution for DateAdd, DateDiff and DatePart
SELECT DATEADD(month, 1, '2006-08-30');Unfortunately, this statement cannot be migrated directly to DB2 because DB2 does not support enumeration type. So it is necessary to modify also SQL Server statement to achieve backward compatibility.
Instead of using DATEADD(month ...) function replace it with DATEADD_MM function.
CREATE FUNCTION DATEADD_MM(@mins INT, @da DATETIME) RETURNS DATETIME AS BEGIN RETURN DATEADD(mm,@mins,@da) ENDand modify the statement (all not built-in UDF in SQL Server should be qualified with schema name).
SELECT dbo.DATEADD_MM(1, '2006-08-30');DB2 equivalent
CREATE OR REPLACE FUNCTION DBO.DATEADD_MM(IN NOD INT, IN DAT TIMESTAMP) RETURNS TIMESTAMP RETURN DAT + NOD MONTHS @and DB2 SQL stamement
SELECT dbo.DATEADD_MM(1, '2006-08-30') FROM SYSIBM.DUAL;After this transformation function dbo.DATEADD_MM can be used any way in the SQL statements executable in SQL Server and DB2 without any modification.
In the files: DB2 and SQL Server are more examples for DATEADD, DATEDIFF and DATEPART function.
Solution for other built-in scalar functions
Other functions are easy to migrate, just create its DB2 equivalent.
Example:
SELECT SUBSTRING('ABCDEF',2,2)
DB2 equivalence
CREATE OR REPLACE FUNCTION SUBSTRING (IN STR VARCHAR(32672),IN STA INT, IN LEN INT) RETURNS VARCHAR(32672) RETURN SUBSTRING(STR,STA,LEN,CODEUNITS16) @More examples: DB2 and SQL Server
Performance issue
A question can be raised if the performance will not suffer. The question is worth considering because instead of replacing SQL Server function by DB2 equivalent we are adding additional wrapper function to keep backward compatibility. Using additional UDF (particularly in WHERE clause ) can impact performance. But it is not the case because in most case it is "inline" function or even "source" DB2 function which does not involve any additional function calling.
Conclusion
In most cases it is not a problem to migrate SQL Server build-in scalar function to DB2. Adding some consideration also migrating and keeping backward compatibility is possible.
