support: Customer Portal
Focused on delivering choice, investment protection and flexibility to organizations with valuable COBOL assets
 

Veryant Knowledge Base
Home > All Categories > Data Access > RDBMS > How to manage a large COBOL OCCURS on a Database with Database Bridge
Question Title 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.

Authored by: Veryant Support This question has been viewed 255 times so far.
Click Here to View all the questions in RDBMS category.
File Attachments File Attachments
There are no attachment file(s) related to this question.
How helpful was this article to you?
User Comments User Comments Add Comment
There are no user comments for this question. Be the first to post a comment. Click Here
Related Questions Related Questions
  1. How do I set up JDBC to communicate with Oracle database?
  2. Where is the Microsoft SQL Server 2005 JDBC Driver?
  3. Trying to work with MS SQL I get the error "JRE (Java Runtime Environment) version 1.7 is not supported by this driver. use classlibrary sqljdbc4.jar witch supports JDBC 4.0".
  4. Does Oracle Pro*COBOL work with isCOBOL?
  5. Where can I get products and services for Postgres (PostgreSQL)?
  6. Can isCOBOL access Oracle, MS SQL and other RDBMS?
Article Information Additional Information
Article Number: 329
Created: 2022-05-27 9:47 AM
Rating: No Rating
 
Article Options Article Options
Print Question Print this Question
Email Question Email Question to Friend
Export to Adobe PDF Export to PDF File
Export to MS Word Export to MS Word
Bookmark Article
Subscribe to Article Subscribe to Article
 
Search Knowledge Base Search Knowledge Base



 
 

© Veryant - All Rights Reserved
Veryant and isCOBOL are trademarks or registered marks of Veryant in the United States and other countries. All other marks are the property of their respective owners.