Tutorial: Application Migration - Adding Function Emulation

This tutorial takes you through the process of migrating a DB2 application to a SQL Server application, using HCOSS to add function emulation.
This tutorial demonstrates adding function emulation by taking you through the process of:
  • Adding math functions to SQL Server that represent standard math functions in DB2.
  • Mapping the DB2 functions to the new SQL Server functions using the Customize SQL Name Mapping tool.

This tutorial also demonstrates binding the application using a post-build event in Visual Studio.

Requirements

Before attempting this tutorial, you must first complete the following tutorials to ensure you have an established SQL Server database named HCO_Test containing the required PROD and TEST schemas and a connection to the HCO_Test database:
  • Tutorial: Create a SQL Server Database
  • Tutorial: Create a Database Connection
  • Tutorial: DB2 Database Migration or Tutorial: Setup for Application Migration Tutorials

Visual Studio Solution

The Visual Studio solution we provide for this tutorial contains two HCOSS projects. The FunctionEmulation project is a native COBOL project. The MFunctionEmulation project is a managed COBOL project. However, the COBOL code is the same in both projects.

Phase 1: Create User-Defined Functions

  1. Launch SQL Server Management Studio, and select the HCO_Test database.
  2. Navigate to and open the FunctionEmulation.sql file, located in the %PUBLIC%\Documents\Micro Focus\Enterprise Developer\Samples\Mainframe\SQL\hcoss\FunctionEmulation directory.

    This file contains a script that creates some user defined math functions needed by the example code.

  3. Click Execute.
  4. From the Object Explorer expand and select HCO_Test > Programmability > Functions > Scalar-Valued Functions and Refresh this folder. You should now see the newly created functions.

Phase 2: Start Enterprise Developer and HCO for SQL Server

If Enterprise Developer and HCOSS for SQL Server tools are already running, skip this phase.

  1. Start Enterprise Developer as an administrator. This procedure varies depending on your Windows version. If you need instructions, see To start Enterprise Developer as an administrator.
  2. In Visual Studio, click View > Micro Focus SQL Tools > HCO for SQL Server Tools.

Phase 3: Map the Functions for HCOSS

  1. From the HCO for SQL Server interface, click Customize SQL Name Mappings.
  2. Scroll to the bottom of the grid and double-click in the first empty cell to activate it for editing. Type the following information in the appropriate rows and columns, and check the corresponding Is Function box for each:
    DB2 Function/Special Register SQL Server Equivalent
    atanh dbo.atanh
    cosh dbo.cosh
    sinh dbo.sinh
    tanh dbo.tanh
  3. Click Save to save the mappings.

Phase 4: Analyze, Build, and Bind the Native Application

Analyze the Native Application
  1. From the Visual Studio IDE, open the solution named FunctionEmulation, which is located in the %PUBLIC%\Documents\Micro Focus\Enterprise Developer\Samples\Mainframe\SQL\hcoss\FunctionEmulation directory.
  2. From the Solution Explorer, open the FunctionEmulation.cbl file to view its contents.Pay particular attention to the EXEC SQL statement that references the user-defined functions.
  3. Close the code editor.
  4. Open the project properties for the FunctionEmulation project.
  5. Notice that the Output type on the Application tab is Console Application.
  6. Switch to the COBOL tab, and set the following:
    Configuration Debug
    Platform target x86 (default)
  7. Switch to the SQL tab. Several OpenESQL compiler directives have been set for you. The following table offers a brief description of each:
    SQL(TARGETDB=MSSQLSERVER) Target database is SQL Server
    SQL(DB=HCODemo) SQL Server connection name is HCODemo
    SQL(DIALECT=MAINFRAME) HCOSS database syntax conversion is enabled
    SQL(DBRMLIB) EXEC-SQL commands are extracted and placed in database request module (DBRM)
    SQL(INIT) Initiates the database connection
    SQL(NOCHECK) No SQL compile-time checking performed
    SQL(BEHAVIOR=OPTIMIZED) Optimizes migration process
Define a Post-Build Event
  1. In the Properties window, click the COBOL tab; then click Build Events.
  2. In the Post build event command line field, type the following command:
    DSN SYSTEM(HCODemo) @"$(ProjectDir)FunctionEmulation.hcodsn"

    This command calls the DSN Bind utility, specifies the SQL database connection to use, and states the location and name of a bind script file. Visual Studio executes this event immediately after building the application, automatically binding the application at that time.

  3. Click OK to close the Build Events dialog box.
View the Contents of the Bind Script File
  • From the Solution Explorer, open and review the content of FunctionEmulation.hcodsn. This bind script file contains one BIND PLAN command that binds the FunctionEmulation member into a plan named FunctionEmulation.
Build and Bind the Applcation
  1. From the Solution Explorer, rebuild the FunctionEmulation project, thereby generating a DBRM for the application and automatically binding it to the DBRM using the post-build event.
  2. Verify that the project built successfully.
Verify the Results
  1. Using Microsoft SQL Server Management Studio, connect to your SQL Server instance.
  2. On the Object Explorer, expand Databases > HCO_Test > Programmability > Stored Procedures to see the stored procedures HCOSS created when you executed your packages and plan.
  3. Open one of the stored procedures that contains PLN as part of its name.

    In this, you see your application code making the calls to the user-defined functions.

Phase 5: Run the Native Application

  1. From the Solution Explorer, open the FunctionEmulation.cbl source file.
  2. Set a break point on the line containing if sqlcode = 0.
  3. Press F5 to start debugging.
  4. Use the tools available from the Debug toolbar or menu to continue to your breakpoint.

You should see from the output that the results are calculations performed by your user-defined functions.

Phase 6: Analyze, Build, Bind, and Run the Managed Application

Analyze the Managed Application
  1. From the Solution Explorer, switch the starting project: Right-click the MFunctionEmulation project and select Set as StartUp Project.
  2. Open the project properties for the MFunctionEmulation project.
  3. Click the SQL tab.

    Notice the directive settings for the OpenESQL ESQL Preprocessor include SQL(DBMAN=ADO). This means that we're using the ADO.NET database connection rather than the ODBC database connection.

Define a Post-Build Event
  1. From the Properties window add a post-build event to execute the following command (same command you used for the native project):
    DSN SYSTEM(HCODemo) @"$(ProjectDir)FunctionEmulation.hcodsn"
  2. Close the Properties window.
Build and Bind the Managed Application
  • From the Solution Explorer, right-click the MFunctionEmulation project and select Rebuild.
Run the Managed Application
  1. From the Solution Explorer in Visual Studio, open the FunctionEmulation.cbl source file.
  2. Set a break point on the line containing if sqlcode = 0.
  3. Run the application.

This completes the tutorial.