1

I have the following COBOL files: .DAT, .IDX and fd (file definition) file. We are using COBOL Net Express from MicroFocus.

  1. Now at first I would like to create MsSql table from this Cobol file definiton. The file defintion has this inside:

    FD PREGLA DATA RECORD IS FPG-REC.
    01 FPG-REC.
    02 FPG-STA PIC X(01).
    02 FPG-KEY.
    03 FPG-FRM PIC X(02).
    03 FPG-ODD PIC X(02).
    03 FPG-DOK PIC 9(08) BINARY.
    02 FPG-POZ PIC 9(06) BINARY.
    02 FPG-PRM.
    03 FPG-IND PIC 9(01) OCCURS 10 TIMES.
    .........and so on

Is it possible to import this file into Microsoft SQL server 2008? We are also using Sql server Managment studio. Now I tried SQL server import and export wizard, but it does not have import for this kind of file.

I have also looked in NET Express, but without any luck. Is it even possible to get SQL table definiton from COBOL fd?

Jernej Jerin
  • 3,179
  • 9
  • 37
  • 53

1 Answers1

2

Creating SQL table definitions from COBOL record layouts is not always a straight forward process (going the other way is pretty simple though).

The problem is that COBOL record layouts can be quite complex with various overlays (COBOL REDEFINES) and denormalizations (COBOL OCCURS). These pretty much defeat most attempts to automate the process of mapping a complex COBOL Record to an SQL table layout.

Data type mapping can also be a bit of a challenge. Net Express files may be created to target either ASCII or EBCDIC (IBM Mainframe) based environments. If your files are encoded in EBCDIC you will most likely have to write custom conversion software because your file contains mixed character/numeric data (there may be third party products that can automate, or partly automate, this type of conversion but I am not familar with them).

Try looking at one of the .DAT files with a simple text editor (e.g. notepad). If you can read the character data then it is ASCII based - and you have a fighting chance of loading the data without much additional conversion effort.

COBOL field definitions that are PIC X something contain character data and translate directly into SQL CHAR data of a similar length (i.e. PIC X(4) becomes CHAR(4)).

COBOL fields definitions defined as BINARY translate into SQL INTEGER. Whether the integer is long or short depends on the number of digits. For example PIC S9(8) BINARY specifies a signed binary integer of 8 digits - that would occupy 4 bytes. On the other hand, PIC S9(4) BINARY is only 4 digits so would occupy 2 bytes (short integer).

Another common COBOL field definition is PACKED-DECIMAL or COMP-3. These fields may translate into SQL DECIMAL data types.

SimoTime provides a very good overview for several COBOL field definitions. Working out the translation into the appropriate SQL data type should not be difficult.

Note 1: From the COBOL record layout fragment provided in your question I can see an OCCURS clause. Because of this, the resulting table will not even be in First Normal Form. These tables can be a real pain to manage in a database environment.

Note 2: The useable data will be found in the .DAT files. The record layout will correspond to the COBOL record definition. The .IDX files contain indexing data used by MicroFocus when reading/writing. You can ignore these.

NealB
  • 16,670
  • 2
  • 39
  • 60
  • Thanks for your answear. I made a mistake in op where I asked if it is possible to get SQL table definiton from COBOL fd, then I already knew that. The larger problem that I have not mentioned is, that we have aproximately 100 of these files to convert to MsSql rdb. I have found out that all the tools that exist for this kind of automated conversion are only for sale, no freeware exists. But thanks again for providing a link for overview of several COBOL field definitions, if we will have to do it on hand, these will be a good reference. – Jernej Jerin Aug 04 '11 at 17:34
  • continuing: Also you said that if it is not in First Normal form, it can be hard to manage. Do these comercial tools for auto conversion from COBOL to MsSql rdb require, that it is in 1,2,3 or 4 Normal Form? – Jernej Jerin Aug 04 '11 at 17:35
  • @Jernej Non-normalized tables can be created, queried and updated by most RDBMS. Normalization is not an actual *requirement* for the DBMS. A poorly normalized database will lead to long term grief for your application. Have a look at some of the answers to [this SO question](http://stackoverflow.com/questions/246701/what-is-normalisation-or-normalization-why-is-it-important) to see why normalization is a good idea. Conversion of data managed through a file system to a RDBMS is something that generally takes a lot of analysis and is not easily automated by "off the shelf" conversion tools. – NealB Aug 04 '11 at 20:18