Question ID : 329
Created on 2022-05-27 at 9:47 AM
Author : Veryant Support [support@veryant.com]
Online URL : http://support.veryant.com/support/phpkb/question.php?ID=329
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.