DB2 For i Scalar Function Performance Considerations
March 5, 2014 Michael Sansoterra
I love using user-defined functions (UDFs) in DB2 for i. They encapsulate business logic, provide for code modularity and improve code reusability. But they can also flush performance down the drain. So in this tip, I’d like to use a sample UDF to analyze how it can affect the performance of an SQL statement.
For this illustration, I’ve exported the Microsoft SQL Server Adventure Works sample data to DB2 for i. There are three main tables to consider in this query; SalesOrderHeader (31465 rows), SalesOrderDetail (121317 rows) and ProductCostHistory (22680 rows; I dumped in some extra randomized data into this