Tutorial: Application Migration - Converting Labeled Duration Calculations

This tutorial takes you through the process of migrating a DB2 application to a SQL Server application, using HCOSS to convert labeled duration calculations and managing date formats.

SQL Server does not support labeled duration calculations directly. HCOSS handles the conversion to T-SQL to implement the DB2 syntax of an EXEC SQL statement using labeled durations.

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

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

Eclipse Project

The Eclipse project we provide for this tutorial contains the LBLDURATION project, which is a native COBOL project.

Phase 1: Start Enterprise Developer and HCO for SQL Server Tools

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 Eclipse, click Run > Tools > Data Tools > HCO for SQL Server.

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

Analyze the Native Application
  1. From the Eclipse IDE, click File > Import.
  2. Expand General, and select Existing Projects into Workspace; then click Next.
  3. Select Set root directory; then browse to the %PUBLIC%\Documents\Micro Focus\Enterprise Developer\Samples\Mainframe\SQL\hcoss\LBLDURATION directory, and click OK.
  4. On the Projects list, check LBLDURATION.
  5. Check Copy projects into workspace.
  6. Click Finish. Eclipse loads the project and builds it automatically.
  7. From the COBOL Explorer, expand LBLDURATION > COBOL Programs; then double-click the LBLDURATION.cbl file to view its contents. Pay particular attention to the following EXEC SQL statement:
               EXEC SQL 
                   DECLARE CSR69 CURSOR FOR SELECT 
                    A.PROJNO
                   ,A.PROJNAME
                   ,A.PRSTDATE
                   ,A.PRENDATE
                   ,(((DAYS(A.PRENDATE) - DAYS(A.PRSTDATE) ) / 7) + 1) * 40 
                   AS MANHOURS
                   FROM PROJ A
                   ORDER BY A.PROJNAME
               END-EXEC
    This contains your labeled duration calculation:
    ,(((DAYS(A.PRENDATE) - DAYS(A.PRSTDATE) ) / 7) + 1) * 40
    In this case, you are using the DAYS labeled duration in a calculation where you:
    • Start with the number of days between the start and end date for projects
    • Divide that number by 7 to get the number of weeks
    • Add 1 to account for truncation of a partial week
    • Multiply that result by 40 to get the number of hours needed to complete the project
  8. Close the code editor.
  9. Open the project properties for the LBLDURATION project.
  10. Expand Micro Focus > Project Settings > COBOL; then select SQL Settings. Several OpenESQL compiler directives have been set for you. The following table offers a brief description of each:
    SQL(DBMAN=ODBC) Uses an ODBC connection
    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(DATE=USA) Date output is in USA format
    SQL(INIT) Initiates the database connection
    SQL(QUALIFIER=TEST) Schema qualifier is TEST
    SQL(NOCHECK) No SQL compile-time checking performed
    SQL(BEHAVIOR=OPTIMIZED) Optimizes migration process
Define a Post-Build Event
  1. In the Properties window, expand Micro Focus > Build Configurations > Events.
  2. In the Post build event command line field, type the following command:
    DSN SYSTEM(HCODemo) @"LBLDURATION.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. Eclipse executes this event immediately after building the application, automatically binding the application at that time.

  3. Click Apply and Close. Eclipse builds the native application automatically.
View the Contents of the Bind Script File
  • From the COBOL Explorer, open and review the content of LBLDURATION.hcodsn. This bind script file contains one BIND PLAN command that binds the LBLDURATION member into a plan named LBLDURATION.
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 the stored procedure named PLN:LBLDURATION.LBLDURATIONconsistency-token$0, where consistency-token is the value of the generated consistency token.

    In this, you see the SQL from your application code. You also see that HCOSS has converted the original DB2 DAYS expression into T-SQL syntax that recreates the same functionality in SQL Server. This is due to having set the SQL(DIALECT=MAINFRAME) directive when you compiled.

Phase 3: Run the Native Application

  1. From the COBOL Explorer, open the LBLDURATION.cbl source file.
  2. Set a break point on the GO BACK line.
  3. Click Run > Debug to start debugging.
  4. If prompted with the Debug As dialog box, select COBOL Application; then click OK.
  5. When prompted to open the Debug perspective, click Yes.
  6. Click Resume to continue to your breakpoint.

You see from the output that your results show the hours required to complete each project. All output dates are in USA format.

Note: If the output window is not visible, minimize Eclipse to reveal it.

This completes the tutorial.