Tutorial: Extending CICS Applications to Use Database Connectors for Microsoft SQL Server





This tutorial tells you how to extend an existing CICS application to use Micro Focus's Database Connectors™ for Microsoft® SQL Server.

Introduction to the Data Conversion Tutorial


This section provides a brief introduction to the process of extending an existing CICS application to use Micro Focus's Database Connectors™ for Microsoft® SQL Server. This will allow your application to store and access information in a Microsoft SQL Server relational database management system (RDBMS).

This "Getting Started" tutorial is intended to provide a quick introduction to the most common tasks you will encounter while migrating your data. You should do the sections in the order in which they are presented in this tutorial; each section uses information or files from the previous section(s).

Overview


The data conversion tutorial presents an example of one way to convert your data from an existing CICS application running under Studio Enterprise Edition. For the purposes of this tutorial, you should already have your data files residing in the Micro Focus file system with the ASCII character set.

In this tutorial, you will:

  • Gather information from your existing application describing the COBOL record layout
  • Gather information on the key structure of the file
  • Create a simple COBOL application with a SELECT and FILE DESCRIPTOR for the file
  • Compile the COBOL application to generate an eXtended File Descriptor (XFD) that will be used to map your data from the record-oriented COBOL application to a relational database's column-oriented format
  • Use your newly created COBOL application to generate an empty database table in Microsoft SQL server
  • Verify the table format
  • Enhance the sample application to load the data from your Micro Focus data file into the newly created Microsoft SQL Server table

Demonstration Application


The CICS application that is used in this tutorial is the ACCT demonstration application, the standard example application that IBM supplies with CICS systems. The ACCT application files are supplied with Studio Enterprise Edition. This application is also used for the CICS tutorial (find the location of this tutorial in the next section, Preparation).

Preparation


To complete this tutorial, have the following software installed:

  • Studio Enterprise Edition 6.0
  • Enterprise Server
  • Mainframe Subsystem Support (MSS), an option within Enterprise Server

You should also have completed the tutorial Programming\Mainframe Support\Tutorials\CICS, in your Help.

We will be using the VSAM file ACCTFIL that is created during this tutorial.

Finally, you should be familiar with the Database Connectors User's Guide. We will be referring you to sections of this manual throughout this tutorial.

Gathering Information About Your COBOL Data File


This section walks you through the process of gathering information about your data file.

Overview


To prepare for building a table in the relational database, we will need to build a COBOL description for a VSAM file that matches the data used in your application. To accomplish this, we will need to locate the following information:

  • COBOL file descriptor (FD) for the internal layout of the data within the file
  • Description of the unique primary key used for accessing the data file
  • Descriptions of any alternate indexes that may be required for the file

Determining the File Descriptor


To determine the FD for our data, we will examine a COBOL application that accessed the data through CICS.

The files for the demonstration are in the the subdirectory Examples\Visual Studio Integration\Mainframe\CICS in your Studio Enterprise Edition installation folder. If you have completed the CICS tutorial in an alternate directory, use that directory.

Open the demonstration project in Studio Enterprise Edition:

  1. Click File > Open > Project/Solution, browse to your solution name (such cics.sln), and select it.

    The solution explorer window will show the COBOL files, mapsets, and copy files used for this project.

  2. Open the COBOL source file acct01.cbl.
  3. Locate a CICS operation that reads in a data record.

    For example, you can do a quick find for the phrase “EXEC CICS READNEXT”. In this example, we will see the statement:

    EXEC CICS READNEXT DATASET('ACCTIX') INTO(ACCTREC)
                   LENGTH(ACCT-LNG) RIDFLD(BRKEY) END-EXEC.
    
    
  4. Go to the definition of ACCTREC by placing your cursor on its name and either right-clicking it and clicking Go To Definition or by pressing your <f12> function key.

    The structure of this record is defined in the copybook ACCTREC. If you would like to examine this structure, you will want to open the file by placing your cursor on its name and right-clicking it and clicking Open “ACCTREC”.

Determining the Key Layout

If you ran the previously mentioned tutorial, you will have a COBOL VSAM file called ACCTFIL. If you do not know the location in which this file was created, you can determine it with the following:

  1. Start Enterprise Server Administration.
  2. In the row for CICSDEMO, which is the enterprise server to use, click Details.
  3. Click the Properties tab.
  4. Click the MSS tab.

    The field File Path: will contain the directory in which your file was created.

To determine the key structure, you can use the rebuild command:

  1. Change directory to the location pointed to by File Path.
  2. Use the rebuild command to determine the details of the file format. For example:
    rebuild /n acctfil
    Micro Focus Studio Enterprise Edition V6 File Management Utility
    Version 5.101.0057 Copyright ©) 1984-2009 Micro Focus (IP) Limited.
    
            File                             :   acctfil
            Organization                     :   Indexed
            Format                           :   IDX-8
            Recording Mode                   :   Variable
            Compression                      :   N
            Maximum Record Length            :               383
            Minimum Record Length            :                63
            Index Node Size                  :              1024
            Number of data records           :                 2
            Data  Created With Extfh Version :             14720
            Last  Updated With Extfh Version :             14720
    
            Key Description (where 'Start' is measured from offset 1):
              Key       Start     Length     Dupl     Key Comp   Sparse Char
                0           1          5        N
                1           6         12        Y
    /n acctfil
    

This same information can also be located from within Enterprise Server Administration:

  1. Start Enterprise Server Administration.
  2. In the row for CICSDEMO, which is the enterprise server to use, click Details.
  3. Click ES Monitor & Control…

    This shows summary information for the application that the server is running and its transactions.

  4. In Resources, which is about halfway down on the left, expand the drop-down list and click by Group and then Groups.

    Notice that the first group is DFH$ACCT for the ACCT demonstration program. If you click the Details button for this group, you can display information about the demo’s resources, and you can click Details again to see further details on each item.

  5. Click on Details beside “ACCTFIL FCT ACCT CICS primer base cluster”.

    This will provide information on the main file, such as:

    • Minimum record size = 63
    • Maximum record size = 383
    • Primary key offset = 1
    • Primary key length = 5
  6. Click your Back button, and then click Details beside “ACCTIX FCT ACCT CICS alternate index."

    This will provide information on the alternate index, such as:

    • Alternate key offset = 6
    • Alternate key length = 12
    • Allow Duplicate Keys  

Creating the COBOL Application


In this section, we will use the information gathered in the previous section to write a simple COBOL application that describes the data file. We will then use the checker to compile the COBOL application to generate an “eXtended File Descriptor” (XFD) that will be used to map your data from the record-oriented COBOL application to a relational database’s column-oriented format.

Preparation


Be sure you have installed and configured all of the prerequisite software listed in the subsection Preparation in the section Introduction to the Data Conversion Tutorial earlier in this appendix.

  1. In Studio Enterprise Edition, click File > New > Project > COBOL Projects > Native > Console Applications.
  2. Create a new project called db_acct in a directory of your choice.
  3. In the solution explorer window, right-click in the db_acct project name.
  4. Click Add > Existing Item.
  5. Browse to the Studio Enterprise Edition subdirectory Examples\Visual Studio Integration\Mainframe\CICS and add the copy book acctrec.cpy.
  6. In the solution explorer window, right-click in the db_acct project name. .
  7. Click Add > New Item > Native > COBOL Program to create a new COBOL program.
  8. Name the COBOL program dbacct.cbl.
  9. Add this program to your project.

Code Sample


To begin our process, we will write a small application to open and close the file. This will allow us to check to see if our COBOL description of the file is correct.

From the previous section, we know that the record layout of the file is located in the copy book ACCTREC. We also know that we have two keys on the file. There is a primary key that is located at offset 1 and has a length of 5 bytes. There is also an alternate key located at offset 6 that is 12 bytes in length and allows duplicates. We will fill in the initial COBOL structure using this information.

To get the key information, we will scan down the record description and locate COBOL fields at the correct offset.

  1. Enter the following to the newly created dbacct.cbl program:
    IDENTIFICATION DIVISION.
           PROGRAM-ID. DBACCT.
    
           ENVIRONMENT DIVISION.
    
           INPUT-OUTPUT SECTION.
           FILE-CONTROL.
    
           SELECT ACCT-IN
                   ASSIGN TO DISK "ACCTFIL"
                   ORGANIZATION IS INDEXED
                   ACCESS MODE IS DYNAMIC
                   RECORD KEY IS
                      ACCTDO OF ACCT-IN-REC
                   ALTERNATE RECORD KEY IS
                      SNAMEDO OF ACCT-IN-REC WITH DUPLICATES
                   FILE STATUS IS ACCT-FILE-STATUS.
    
           DATA DIVISION.
           FILE SECTION.
    
           FD ACCT-IN.
           01 ACCT-IN-REC.
           COPY "acctrec.cpy".
    
           WORKING-STORAGE SECTION.
           01 ACCT-FILE-STATUS.
              05 ACCT-STATUS-KEY-1                   PIC X(1).
              05 ACCT-STATUS-KEY-2                   PIC X(1).
              05 ACCT-STATUS-KEY-2-BINARY REDEFINES ACCT-STATUS-KEY-2
                                                     PIC 9(2) COMP-X.
           01 USER-RETURN                            PIC X(10).
    
           PROCEDURE DIVISION.
           DECLARATIVES.
           FILE-ERR-HANDLING SECTION.
              USE AFTER STANDARD ERROR PROCEDURE ON ACCT-IN.
           FILE-ERR.
              DISPLAY "File Error: ", ACCT-FILE-STATUS.
              DISPLAY "Press <return> to exit".
              ACCEPT USER-RETURN.
              STOP RUN.
           END DECLARATIVES.
    
           LEVEL-1 SECTION.
           MAIN-LOGIC.
                PERFORM OPEN-IN-FILE.
                STOP RUN.
    
           OPEN-IN-FILE.
                OPEN INPUT ACCT-IN.
                CLOSE ACCT-IN.
  2. After you have entered the program above, save the file and then build the project.

You can now run the project in the Debugger. However, when your application attempts to open the file, you will receive the error message:

File Error: 35
Press <return> to exit

We have not told the program where the file is located. To do this in Studio Enterprise Edition, follow the steps below:

  1. Close your solution and exit Studio Enterprise Edition.
  2. Open a Visual Studio 2008 command prompt and set a variable:
    VariableValue
    DD_ACCTFIL    <location of file>\ACCTFIL
  3. Start Studio Enterprise Edition from the command prompt using the devenv command.

This time when we run the file, we will receive a different error message:

File Error: 39
Press <return> to exit

This error indicates that there is a mismatch between the file description in our COBOL program and the actual file on disk. Reviewing the information from the previous section, we see that the first alternate key for the file should be only 12 bytes long. The field we selected for the alternate record key from the copy book, however, is 18 bytes long. We will need to break this field up:

  1. Make a copy of acctrec.cpy called acctrec2.cpy and modify it as follows:
    02  ACCTDO               PIC X(5).
               02  SNAMEDO.
                   05  SNAMEDO1         PIC X(12).
                   05  SNAMEDO2         PIC X(6).
               02  FNAMEDO              PIC X(12).
    
    
  2. Once you have modified the new file and saved it, you should add it to your project.
  3. Change the copy statement in the COBOL application to copy in the new file acctrec2.cpy.
  4. Change the select statement to read:
    ALTERNATE RECORD KEY IS
    SNAMEDO1 OF ACCT-IN-REC WITH DUPLICATES
  5. Change the FD statement to read:
    FD ACCT-IN
        RECORD IS VARYING FROM 63 TO 383.
    01 ACCT-IN-REC.
    COPY “acctrec2.cpy”.
    
  6. Rebuild your project and run the application.

You should now have a COBOL application that successfully opens the file for processing.

Creating XFD Files


Relational databases work with data in a column-oriented format, while COBOL works with data in a record-oriented format. In order to move our data between these two domains, we will need some form of mapping. To accomplish this, we will use the checker to compile the COBOL application to generate an “eXtended File Descriptor” (XFD). This XFD will be used to map your data from the record-oriented COBOL application to a relational database’s column-oriented format. For detailed information on XFDs, see the chapter XFDs of your Database Connectors User's Guide.

To instruct the checker to generate XFD files, we will use a directive:

  • At the top of your COBOL file, add the following directive:
    $SET CREATEXFD
     IDENTIFICATION DIVISION.
     PROGRAM-ID. DBACCT.
  • If you wish for the XFD files to be placed in a different directory than the current one, you can use the following format:

    $SET CREATEXFD(XFD-DIRECTORY=c:\myproject\xfd)
    
  • This directive can also be set using the project’s properties. To do this, follow the steps below:
    1. Double-click on Properties.
    2. Click on the COBOL tab.
    3. In the Additional Directives entry field, type CREATEXFD and save the changes.

In the next section, we will expand the COBOL application to create a database table in Microsoft SQL Server using the XFD file generated by the checker.

Creating the Database Table


In this section, we will use the COBOL application from the previous section to create a new Microsoft SQL Server table in which to store our COBOL information.

In this section, you will:

  • Modify the COBOL application to add a new file description
  • Add COBOL directives to allow your application to work with Database Connectors
  • Set up configuration variables and files to direct the operations of Database Connectors
  • Run the sample program to create a new database table
  • Check the resulting Microsoft SQL Server table for columns and indexes
  • Modify field names for improved readability

Code Sample


Database Connectors is designed to simplify the process of working with relational databases as much as possible for the COBOL application developer. With this in mind, we allow you to use the traditional COBOL “OPEN OUTPUT” verb to create a new database table. We will modify the application from the previous example to have a new file description matching the existing file format, but intended to be our new table in the database.

$SET CREATEXFD
      IDENTIFICATION DIVISION.
      PROGRAM-ID. DBACCT.

      ENVIRONMENT DIVISION.

      INPUT-OUTPUT SECTION.
      FILE-CONTROL.

      SELECT ACCT-IN
              ASSIGN TO DISK "ACCTFIL"
              ORGANIZATION IS INDEXED
              ACCESS MODE IS DYNAMIC
              RECORD KEY IS
                 ACCTDO OF ACCT-IN-REC
              ALTERNATE RECORD KEY IS
                 SNAMEDO1 OF ACCT-IN-REC WITH DUPLICATES
              FILE STATUS IS ACCT-FILE-STATUS.

      SELECT ACCT-OUT
              ASSIGN TO DISK "DBACCTFIL"
              ORGANIZATION IS INDEXED
              ACCESS MODE IS DYNAMIC
              RECORD KEY IS
                 ACCTDO OF ACCT-OUT-REC
              ALTERNATE RECORD KEY IS
                 SNAMEDO1 OF ACCT-OUT-REC WITH DUPLICATES
              FILE STATUS IS ACCT-FILE-STATUS.

      DATA DIVISION.
      FILE SECTION.

      FD ACCT-IN
         RECORD IS VARYING FROM 63 to 383.
      01 ACCT-IN-REC.
      COPY "acctrec2.cpy".

      FD ACCT-OUT
         RECORD IS VARYING FROM 63 to 383.
      01 ACCT-OUT-REC.
      COPY "acctrec2.cpy".

      WORKING-STORAGE SECTION.
      01 ACCT-FILE-STATUS.
         05 ACCT-STATUS-KEY-1                   PIC X(1).
         05 ACCT-STATUS-KEY-2                   PIC X(1).
         05 ACCT-STATUS-KEY-2-BINARY REDEFINES ACCT-STATUS-KEY-2
                                                 PIC 9(2) COMP-X.
      01 USER-RETURN                            PIC X(10).

      PROCEDURE DIVISION.
      DECLARATIVES.
      FILE-ERR-HANDLING SECTION.
         USE AFTER STANDARD ERROR PROCEDURE ON ACCT-IN, ACCT-OUT.
      FILE-ERR.
         DISPLAY "File Error: ", ACCT-FILE-STATUS.
         DISPLAY "Press <return> to exit".
         ACCEPT USER-RETURN.
         STOP RUN.
      END DECLARATIVES.

      LEVEL-1 SECTION.
      MAIN-LOGIC.
     *     PERFORM OPEN-IN-FILE.
           PERFORM CREATE-OUT-FILE.
           STOP RUN.

      OPEN-IN-FILE.
           OPEN INPUT ACCT-IN.
           CLOSE ACCT-IN.

      CREATE-OUT-FILE.
           OPEN OUTPUT ACCT-OUT.
           CLOSE ACCT-OUT.

In the code sample above, we have:

  • Added a new SELECT and FD for our output file
  • Modified the declaratives to handle the new file
  • Added new code to perform an OPEN OUTPUT on the file

Setting Up the Configuration File


We will need to set up a configuration file that will contain instructions for Database Connectors on how to operate. In this example, we will use a project directory of C:\DataConnectors\Test and a configuration file of mssql.cfg.

  1. Create a new textfile called mssql.cfg in your project directory.
  2. Add the following entries to the file:
    ##################################################
    # mssql.cfg
    #
    # Configuration file for settings to be used in
    # conjunction with Database Connectors for MSSQL
    #
    ##################################################
    
    ##################################################
    # The following is a list of individual assignments
    # of files to a filesystem other than the default
    ##################################################
    
    DBACCTFIL-HOST mssql
    XFD-DIRECTORY c:\DataConnectors\Test
    
    ##################################################
    # Connection information to be used when connecting
    # to the target database in Microsoft SQL Server
    ##################################################
    
    A-MSSQL-DEFAULT-CONNECTION MYSERVER
    A-MSSQL-DATABASE testdb
    A-MSSQL-LOCK-DB testlock
    A-MSSQL-LOGIN testuser
    A-MSSQL-PASSWD testpasswd
    
    ######################################
    # File tracing
    ######################################
    
    FILE_TRACE 9
    FILE_TRACE_FLUSH TRUE
    FILE_TRACE_TIMESTAMP TRUE
    
    ##################################################
    # End of mssql.cfg
    #
    ##################################################

The following table details the options that can be set in the configuration file:

VariableValueDescription
DEFAULT-HOST mssqlDefault File System
A-MSSQL-DEFAULT-CONNECTION <servername> Name of the MSSQL server
A-MSSQL-DATABASE<testdb> Name of the specific database to be accessed
A-MSSQL-LOCK-DB <testlock> Name of the database that holds the lock table
A-MSSQL-LOGIN <testuser> Name under which you want to connect to the database system
A-MSSQL-PASSWD <testpasswd>Password assigned to the user account
XFD-DIRECTORYc:\DataConnectors\Test Directory containing your XFD files if different from current directory
FILE-TRACE <any number greater than “0”>Level of file tracing to perform

Setting the Run-time Environment Variable


After you have saved this file, Studio Enterprise Edition will need to know where to find this configuration file, so we will set an environment variable. To do this, follow the steps below:

  1. Close your solution and exit Studio Enterprise Edition.
  2. Open a Visual Studio 2008 command prompt and set a variable:
    VariableValue
    A_CONFIG     c:\DataConnectors\Test\mssql.cfg

Enabling Dynamic Redirection to a Custom File Handler


To enable the dynamic redirection of the file handler you need to set the following environment variables:

VariableValue
DYNREDIR    DYNCONFIG:EXPAND
FHREDIR<fhredir_config>

If you are using Enterprise Server, you also need to set the following environment variable:

VariableValue
TXFILEP    <VSAM_dir>

If you are using Enterprise Server with only a database, TXFILEP needs to point to the directory where Micro Focus VSAM files would normally be found, and the XFD files need to go into the same directory specified by TXFILEP.

fhredir_config contains the redirection information, for example:

/f$TXFILEP\*     /r ACUFH!ACUFH
/f<VSAM_dir>\*   /r ACUFH!ACUFH

where $TXFILEP\* is needed only if you are using Enterprise Server, and <VSAM_dir>\* means that any COBOL file that has an XFD in the directory specified by VSAM_dir will be redirected to the database.

If you are not using Enterprise Server, $TXFILEP is not needed.

You can replace the <VSAM_dir>\* with the full path, including the filename, for example:

/f<VSAM_dir>\ACCTFIL /r ACUFH!ACUFH

The DYNREDIR=DYNCONFIG:EXPAND causes all filenames to be fully expanded into their pathnames. For example, if you have an entry:

/f<VSAM_dir>\*   /r ACUFH!ACUFH

and a COBOL file called ACCTFIL, it will be expanded to:

<VSAM_dir>\ACCTFIL

If you are not using Enterprise Server, and you are using VSAM and database files in the same COBOL program, you cannot have the VSAM files in the same directory as the XFD files if you are using wildcards like <VSAM_dir>\*. This is because the wildcard tells the run-time to redirect all COBOL files in the specified directory to the database. For example, if your VSAM file is ACCTFIL and your XFD file is DBACCTFIL.xfd and they are in a directory specified by <VSAM_dir>, you cannot use:

/f <VSAM_dir>\*   /r ACUFH!ACUFH

This is because the DYNREDIR logic will try to redirect any file in that directory to the database, but we don't want to do this for ACCTFIL because it is a VSAM file. You have to either specify the full path for the XFD like this:

/f <VSAM_dir>\DBACCTFIL    /r ACUFH!ACUFH

or put the XFD in a different directory and specify the directory with the wildcard, for example:

/f <XFD_dir>\*   /r ACUFH!ACUFH

where XFD_dir is a directory where your XFD files reside and is different from the directory where your VSAM files reside. If you have a separate directory for your XFDs like this, you need to modify your COBOL program ASSIGN statement to specify the XFD_dir, for example:

ASSIGN TO DISK "<XFD_dir>\DBACCTFIL"

In addition, you will need to modify your ACUFH configuration file and set XFD_DIRECTORY to XFD_dir so that the file handler can find your XFD files, for example:

XFD_DIRECTORY <XFD_dir>

Turning on Tracing


To aid in resolving any difficulties that we may encounter in the initial configuration of Database Connectors, we will also set some variables that will allow us to perform tracing. After successful configuration of Database Connectors, you will wish to turn tracing off, because it can result in a significant performance penalty.

Set a new variable:

VariableValue
MFTRACE_CONFIG    c:\DataConnectors\Test\config.ctf

In addition ,you should create a configuration file for the tracing configuration. The following is a potential example of the file c:\DataConnectors\Test\config.ctf :

mftrace.emitter.textfile#Format = $(TIME) $(THREAD) $(COMPONENT) $(EVENT) $(LEVEL) :$(DATA)

mftrace.level.mf.rts               = debug
mftrace.level                      = info
mftrace.comp.mf.rts#pgm            = true
mftrace.comp.mf.rts#pgmload        = true
mftrace.comp.mf.rts#syspgm         = true
mftrace.comp.mf.rts#eprintf        = 1
mftrace.emitter.TEXTFILE#location	 = C:\DataConnectors\Test\logs

You will also need to create the directory C:\DataConnectors\Test\logs to receive your log files.

Running the Sample Program


When you have completed the above steps for creating your configuration file and preparing your environment, you are ready to create the new database table. Start Studio Enterprise Edition from the Visual Studio 2008 command prompt using the devenv command.

Run your application in Studio Enterprise Edition and step through the code. Upon successful creation, a new table will have been created in Studio Enterprise Edition.

Errors You May Encounter

When you come to the line “OPEN OUTPUT ACCT-OUT”, you may receive a "File Error:9k". If you check the log files that were created in the directory pointed to by mftrace.emitter.TEXTFILE#location = C:\DataConnectors\Test\logs, you will see the following:

init_connection
Trying to open a connection to server MyBadServer
report_status called from 1176
[1] 08001 17 – [DBNETLIB]SQL Server does not exist or access denied.
[2] 01000 53 – [DBNETLIB]ConnectionOpen (Connect()).

where MyBadServer is the value set in your configuration file for A-MSSQL-DEFAULT-CONNECTION.

Checking the Resulting Microsoft SQL Server Table


Once the COBOL application has finished, you will want to review the resulting database table to ensure that it has the desired format and column names.

  1. To review the structure of the table, bring up Microsoft’s SQL Server Management Studio.
  2. Connect to the data source you used in your configuration file. The entries map as follows:
    Server Management Studio     Database Connectors
    Server nameA-MSSQL-DEFAULT-CONNECTION
    LoginA-MSSQL-LOGIN
    PasswordA-MSSQL-PASSWD
  3. Once you are connected, perform the following steps:
    1. Expand the “Databases” folder.
    2. Expand the folder referred to in the A-MSSQL-DATABASE setting from your configuration file.
    3. Expand the “Tables” folder.
    4. Locate and expand the folder containing the name "DBACCTFIL."

Once you have expanded this folder you will see that it contains a list of subfolders that describe the columns that comprise the table, as well as the indexes on the table. (Note that there is also a folder titled “Keys.” This should not be confused with the “Indexes,” which is where the equivalent of the COBOL keys will be created.)

Indexes names will take the form I<tablename><key>.

You will want to review the objects in the “Columns” folder for readability and to ensure that the COBOL fields you wished to be included were indeed created. You can effect field names with the use of the NAME directive placed in your COBOL FD. You can modify the table structure with the USE GROUP, WHEN, and other directives placed within your COBOL FD. Please refer to the Micro Focus Database Connectors User's Guide for further details on these directives.

Modifying Field Names for Improved Readability


Once you have examined the resulting database table, there may be changes you would like to make to improve readability. One of the most common modifications you may want to make is to change the name of one of the resulting columns in the database table to make it easier to work with from SQL. Database Connectors provides a simple method for handling this situation.

You can embed COBOL directives into you program that will modify how the table is created. One of the more convenient directives is the NAME directive, which instructs Database Connectors to name the resulting database column by another name. This directive does not in any way modify how the COBOL program operates. It is strictly a comment from the perspective of your application. For further information about the NAME directive, see the help topic in your Database Connectors User's Guide reference section.

Code Sample


For our example here, we may want to modify the database table such that instead of the COBOL fields ACCTDO, SNAMEDO1, and FNAMEDO, the database table will have the more readable names of ACCOUNT_NUMBER, SURNAME, and FIRST_NAME. To do this we place directives into our COBOL program as follows.

$XFD name=account_number
           02  ACCTDO               PIC X(5).
           02  SNAMEDO.
      $XFD name=surname
               05  SNAMEDO1         PIC X(12).
               05  SNAMEDO2         PIC X(6).
      $XFD name=first_name
           02  FNAMEDO              PIC X(12).
           02  MIDO                 PIC X.
           02  TTLDO                PIC X(4).
           02  TELDO                PIC X(10).
           02  ADDR1DO              PIC X(24).

When you have saved your file, rebuild your project, and a new XFD file will be generated. When your application performs an OPEN OUTPUT, a new table with the new names will be generated. From the perspective of your existing COBOL code, there has been no change. You won’t need to make any changes to your procedure division logic.

Migrating Your Data


In the previous section, we saw how by setting up a sequence of configuration variables, we were able to use a standard COBOL program to create a new database table in Microsoft SQL Server. In this section, we will use the COBOL application from the previous section to move the data from the previously existing Micro Focus VSAM file into the newly created database table. We will use a configuration variable to determine which file system is to host the file.

Code Sample


For this exercise, we will add a new paragraph to our COBOL application that will read all of the records from the source file and write them into the newly created database table.

  1. Modify the procedure division of your COBOL application as follows:
    PROCEDURE DIVISION.
           DECLARATIVES.
           FILE-ERR-HANDLING SECTION.
              USE AFTER STANDARD ERROR PROCEDURE ON ACCT-IN, ACCT-OUT.
           FILE-ERR.
              DISPLAY "File Error: ", ACCT-FILE-STATUS.
              DISPLAY "Press <return> to exit".
              ACCEPT USER-RETURN.
              STOP RUN.
           END DECLARATIVES.
    
           LEVEL-1 SECTION.
           MAIN-LOGIC.
          *     PERFORM OPEN-IN-FILE.
          *     PERFORM CREATE-OUT-FILE.
                PERFORM COPY-FILE.
                STOP RUN.
    
           OPEN-IN-FILE.
                OPEN INPUT ACCT-IN.
                CLOSE ACCT-IN.
    
    
           CREATE-OUT-FILE.
                OPEN OUTPUT ACCT-OUT.
                CLOSE ACCT-OUT.
    
           COPY-FILE.
               OPEN INPUT ACCT-IN.
               OPEN OUTPUT ACCT-OUT.
               READ ACCT-IN NEXT RECORD AT END CONTINUE.
               PERFORM UNTIL ACCT-FILE-STATUS = "10"
                   MOVE CORRESPONDING ACCT-IN-REC TO ACCT-OUT-REC
                   WRITE ACCT-OUT-REC
                   READ ACCT-IN NEXT RECORD AT END CONTINUE
               END-PERFORM.
               CLOSE ACCT-IN.
               CLOSE ACCT-OUT.
               DISPLAY "Conversion Complete.".
    
  2. Open your configuration file mssql.cfg and verify that it contains the following entry:
    
    ##################################################
    # The following is a list of individual assignments
    # of files to a filesystem other than the default
    ##################################################
    
    DBACCTFIL-HOST mssql
    
  3. Rebuild and run your application.

If the run was successful, all of your records should now be stored in the database table. You can use a tool such as Microsoft SQL Server Management Studio to verify your data. For example, you can do a quick check with the SQL command:

select  acctdo, snamedo1, snamedo2 from dbo.DBACCTFIL

which will return all of the account numbers and surnames from you newly created table. Another quick spot check you can use is the SQL command:

SELECT COUNT (*) FROM dbo.DBACCTFIL

This will return the total number of rows in your table. You can compare this result to the number of records returned by the rebuild /n acctfil command.

Troubleshooting


In this section, we will examine some of the possible problems you may encounter while modifying your applications to work with a relational database.

Question

I cannot locate the Micro Focus VSAM file ACCTFIL.

Answer

This file should have been created when you ran the CICS tutorial. (For information on this tutorial, see the subsection Preparation in the section Introduction to the Data Conversion Tutorial.

Question

File error 35 on OPEN INPUT ACCT-IN

Answer
  • Verify that the file was created, as in the previous question.
  • To determine the location of the data file, refer to the subsection Determining the Key Layout in the section Gathering Information About Your COBOL Data File.
  • Verify that the Studio Enterprise Edition environment is set up correctly. See the discussion of this in the subsection Code Sample in the section Creating the COBOL Application.
Question

File error 39 on OPEN INPUT ACCT-IN

Answer

The actual key definition of ACCTFIL does not directly match up to existing fields in the copy book acctrec.cpy. You will need to modify the record description. See the discussion of this in the subsection Code Sample in the section Creating the COBOL Application.

Question

File error 9k or 9,107 on OPEN OUTPUT ACCT-OUT

Answer

A Database Connectors-specific error has occurred. You will want to rerun your application using an emitter in order to determine the exact nature of the error. See the subsection Turning on Tracing in the section Creating the Database Table for details on how to turn on tracing and configure an emitter for use with Database Connectors.

Once you have a trace file, search for the error “9k” to determine the source of the issue. There will be a block of information providing details on the error, for example:

MF.RTS 51 1 : "  f_errno: 19"
MF.RTS 51 1 : "  f_int_errno: 3"
MF.RTS 51 1 : "  f_int2_errno: 2"
MF.RTS 51 1 : "  f_errmsg: Unable to open XFD file for 'DBACCTFIL',
                 error = 2"
MF.RTS 51 1 : "  9k or 9,107"

This error indicates the run-time system was unable to locate the data dictionary (XFD) for the file. To resolve this, ensure that you compiled with the $SET CREATEXFD directive (see the subsection Creating XFD Files in the section Creating the COBOL Application). If your XFD files are not in the current directory, you will need to have set the XFD-DIRECTORY variable in your Database Connectors configuration file to the directory in which your XFD files are stored (see the subsection Setting Up the Configuration File in the section Creating the Database Table).

MF.RTS 51 1 : "  f_errno: 19"
MF.RTS 51 1 : "  f_int_errno: 18456"
MF.RTS 51 1 : "  f_int2_errno: 0"
MF.RTS 51 1 : "  f_errmsg: Login failed for user 'testuser'."
MF.RTS 51 1 : "  9k or 9,107"

This error indicates that Database Connectors was unable to log into the Microsoft SQL Server database. To resolve this, ensure that you have provided the correct configuration information for access to your MSSQL database (see the subsection Setting Up the Configuration File in the section Creating the Database Table). The variables in question are:

  • A-MSSQL_DEFAULT-CONNECTION
  • A-MSSQL-DATABASE
  • A-MSSQL-LOGIN
  • A-MSSQL-PASSWD
Question

The file DBACCTFIL is being created as a Micro Focus VSAM file, not an MSSQL table.

Answer

Verify the following:

  • You are running on Studio Enterprise Edition Version 6.0
  • You have created an environment variable, A_CONFIG, pointing to your Database Connectors configuration file (see the subsection Setting the Studio Enterprise Edition Environment Variable in the section Creating the Database Table).
  • Your configuration file contains the following entry:
    DBACCTFIL-HOST mssql

For a more complete discussion of troubleshooting your Studio Enterprise Edition application using Micro Focus Database Connectors, refer to the chapter Performance and Troubleshooting of your Database Connectors User’s Guide.

Question

I receive an error 9k when I try to create a table in Microsoft SQL Server.

Answer

You might be using the wrong server name in the A-MSSQL-DEFAULT-CONNECTION variable. One way you can verify the server name is by following the steps below. This method is useful if you don't have the client tools for SQL Server installed.

  1. Create a new ODBC Datasource for SQL Server.
  2. When prompted for the server name, select your local server from the drop-down list of available servers.
  3. Connect to this server using ODBC Administrator.

    This will provide you with the name of the server you need to use in the A-MSSQL-DEFAULT-CONNECTION variable.

Your Session will expire in 90 minutes
Notification will be shown in:
600 seconds