Function Customization Example

In this example, we use the scenario that our migrated DB2 database uses a built-in function for math, atanh, that our SQL Server database does not support. We use Microsoft SQL Server Management Studio to create the function in the default dbo schema of our SQL Server database, and we use HCOSS to map the DB2 function to the newly created SQL Server user-defined function.

1. In SQL Server Management Studio, create a user-defined function in your SQL Server database to serve as an equivalent for the DB2 atanh function by executing the following Transact-SQL.

   create function atanh (@in float) returns float
   begin
   	return log((1 + @in) / (1 - @in)) / 2
   end
   go
   

2. Qualify the function in SQL Server Management Studio using the dbo schema by executing the following:

   declare @x float = 0.75
   select dbo.atanh(@x)

3. Map the newly defined dbo.atanh SQL Server function to the DB2 atanh.
   1. From the HCOSS interface, click Customize SQL Name Mappings and add the following to the blank row at the bottom of the page:
      • In DB2 Function/Special Register column type atanh.
      • In SQL Server Equivalent column type dbo.atanh.
      • Check the Is Function checkbox.
   2. Click Save.
4. Set the DIALECT directive to MAINFRAME and build the application.

      $set sql(targetdb=mssqlserver db=HCODemo dialect=mainframe, init) 
          identification division.
          program-id. Program1.
   
          environment division.
          configuration section.
   
          data division.
          working-storage section.
          
          exec sql include sqlca end-exec.
          
          01  mfsqlmessagetext    pic x(200).
          
          01  x           comp-2.
          
          01  atanh       comp-2.
          01  digits      comp-2
   
          procedure division.
          
              move 0.25 to x
              exec sql
                  select atanh(:x)
                   into :atanh
              end-exec
              
              display atanh 
    
              goback.