Function Customization Example

Restriction: This topic applies to Windows environments only.

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.