How to manage a large COBOL OCCURS on a Database with Database Bridge

In COBOL it is common that an FD contains one or more OCCURS clauses, a concept that doesn't exist in a relational data base.

When we migrate from an indexed file system to a relational data base, using isCOBOL Database Bridge; this could cause some problems when the OCCURS contains a large number of occurrences, because for each occurrence the data base creates a separate column.
This can cause the number of columns created in the database to exceed the number of columns permitted. Oracle's limit is 1,000 columns; SQL Server's limit ranges from 1,024 to 3,000 columns; DB2's limit is 750 columns, PostgreSQL allows 16,000 columns, and so on.

As an example, let's say that you have the following File Descriptor (FD):

   FD F-EXPR.
    01 REC-EXPR.
       02 COD-EXPR     PIC X(10).
       02 LEN-EXPR     PIC 9(5).
       02 ARR-EXPR.
          03 ARR-CHAR  PIC X OCCURS 50000.

This would create a table in the database with over 50,000 columns, which is something that no database supports.

One solution would be to define the above FD as follows:

   FD F-EXPR.
    01 REC-EXPR.
       02 COD-EXPR      PIC X(10).
       02 LEN-EXPR      PIC 9(5).
       02 F-CARACT-EXPR PIC X(50000).
       02 ARR-EXPR      REDEFINES F-CARACT-EXPR.
          03 ARR-CHAR   PIC X OCCURS 50000.

This change would create a single column; F-CHARACT-EXPR of type varchar(50000). The field ARREGL-EXPR is not created in the database, since redefines do not create columns.

In all modern popular databases, a varchar uses variable storage. That is to say that a row whose F-CHARACT-EXPR = "short expression" occupies only 15 bytes to store that varchar for example.

Another solution is to use the EFD Directive USE GROUP, which assigns a group of items to a single column in the table, like this:

   FD F-EXPR.
    01 REC-EXPR.
       02 COD-EXPR     PIC X(10).
       02 LEN-EXPR     PIC 9(5).
   $EFD USE GROUP
       02 ARR-EXPR.
          03 ARR-CHAR  PIC X OCCURS 50000.

By adding the USE GROUP directive, the data is stored in the database as a single alphanumeric field where the column name is ARREGL-EXPR

In each of these cases, you would have a single field in the database visible to whoever makes queries on that table.
But in your programs you would still have the ARR-CHAR array to continue using as usual.

Article ID: 329
Created: May 27, 2022
Last Updated: May 27, 2022
Author: Support KB Author

Online URL: https://support.veryant.com/phpkb/article.php?id=329