Skip to main content

Making Tables Readable and Writeable

  • July 7, 2016
  • 4 replies
  • 1 view
A
  • Anonymous

I have a file definition that has group elements and redefines. That has a section that looks like this.

10 IXKEY1           in positions 1 to 51

  15 IXTYPE PIC 9(01)  in position 1

      88 IS-CUSTOMER VALUE 1,6

      88 IS-BLANK VALEU 3 THRU 5,7

      88 IS-VENDOR VALUE 2

  15 IXITEM PIC X(20) in position 2 thru 21

  15 IXCOVN PIC X(10)                                                      in positions from 22 to 31
  15 IXCNBR REDEFINES IXCOVN
     20 IXCUST PIC X(07)
     20 IXCSEQ PIC 9(03)

  15 IXSUBN PIC X(20)      in position 32 to 51 

I have all these fields defined in my table and have run the Null Tool Utility across the file except for key fields. Unfortunately this area is part of an index key and I am hesitant to include these based on the warning message I get from Relativity. So when I try to read the entire file I get the aborted message of "Numeric item contains nonnumeric characters (column IXCSEQ) -- because IXCSEQ will only be numeric for 3 out of the 7 conditions for the file. Any design suggestions out there to work around this so that I can do a select on the entire file, or do I need to put in record types any break out into 3 different tables?

Also if I use the same file and skip the group level items when I make my table, I still get the message that it is not writeable because of
 
IXCOVN, IXCUST, IXCSEQ overlap message.  Any suggestions are appreciated. Thanks.


#RELATIVITYGROUPS
#REDEFINES
#OVERLAP
#WRITEABLE

4 replies

Thomas Morrison
  • Thomas Morrison
  • Participating Frequently
  • July 11, 2016

I have a file definition that has group elements and redefines. That has a section that looks like this.

10 IXKEY1           in positions 1 to 51

  15 IXTYPE PIC 9(01)  in position 1

      88 IS-CUSTOMER VALUE 1,6

      88 IS-BLANK VALEU 3 THRU 5,7

      88 IS-VENDOR VALUE 2

  15 IXITEM PIC X(20) in position 2 thru 21

  15 IXCOVN PIC X(10)                                                      in positions from 22 to 31
  15 IXCNBR REDEFINES IXCOVN
     20 IXCUST PIC X(07)
     20 IXCSEQ PIC 9(03)

  15 IXSUBN PIC X(20)      in position 32 to 51 

I have all these fields defined in my table and have run the Null Tool Utility across the file except for key fields. Unfortunately this area is part of an index key and I am hesitant to include these based on the warning message I get from Relativity. So when I try to read the entire file I get the aborted message of "Numeric item contains nonnumeric characters (column IXCSEQ) -- because IXCSEQ will only be numeric for 3 out of the 7 conditions for the file. Any design suggestions out there to work around this so that I can do a select on the entire file, or do I need to put in record types any break out into 3 different tables?

Also if I use the same file and skip the group level items when I make my table, I still get the message that it is not writeable because of
 
IXCOVN, IXCUST, IXCSEQ overlap message.  Any suggestions are appreciated. Thanks.


#RELATIVITYGROUPS
#REDEFINES
#OVERLAP
#WRITEABLE

Cliff,

My initial response is, this needs to be in two tables, one for vendor and one for customer.  Unless there is a valid business reason for some other formulation, that is what should be done.  There may be a similarity of the data stored for customers and vendors in this file (I am making that assumption), and the COBOL programmers took advantage of that similarity for reasons of efficiency (fewer files, smaller memory footprint - when memory footprint meant something, ease of use for the coder, etc).  However, to a SQL developer/data consumer, your effort to fit all this into one table may not provide a similar ease of use.

Turning to the issue of making this/these table(s) writable, the Relativity Designer help file provides detailed rules that must be followed.  In particular, for an INSERTable table, you might want to refer to the topic Building a New Record which details the technical aspects of building a COBOL record from the values supplied on an INSERT SQL statement, including how record types come into play, as well as null value representations.

There are also special rules for UPDATE, especially when it pertains to UPDATE-ing portions of a COBOL record that are subordinate to an OCCURS.

And the overlap rule is one of those topics.  Perhaps you can use the separate fields and then use the CONCAT() scalar function to put them together for most users.  (Relativity supports stored views, so you would fashion a query with the CONCAT() in a stored view.)  The users that are INSERTing or UPDATE-ing would necessarily have to use the separate fields.

In all there are 18 topics contained in the section Building Writable Tables.  

My next suggestion presumes that you want to enhance the COBOL application with new features and functionality using relational database tools, rather than COBOL.  By making tables writable, you are placing trusted business rules in the new, SQL-based code.  This is not something to be undertaken without consideration of the business rules, along with years of testing, that are baked into your COBOL user interface.

Something to consider, especially if you have the option of modifying the COBOL side of your application, is to use Relativity to do a CREATE TABLE.  This transfers the issues of dealing with the friction between the relational database concepts of data representation and the COBOL concept of data representation to the COBOL side.  After a CREATE TABLE, you can use the Relativity Designer to create a COBOL FD for the underlying data file.  Fair warning: this will be unlike any record layout your COBOL programmers will have seen before, but it will be quite usable.  

In this scenario, your COBOL side pulls data from the indexed file that is used by the created table, runs the edits (business rules), updates the COBOL files that the SQL side is using in read-only mode, and deletes the record from the created table file.  This is most like using a transaction file to update a master, and is has similar benefits and drawbacks.

Having said this, most folks that need an INSERTable or UPDATEable table usually deal with the issues detailed in the help files.

At bottom, there are definite friction points between COBOL records and relational tables.  Given that the data models are separated by a generation, this is not too unexpected.  Solving the problems involved requires novel concepts (as evidence, Relativity is covered by a patent).  These friction points are most exposed for writable tables, where the facts of the underlying data structure can impose constraints (mostly seen in the UPDATE constraints).

Tom Morrison, Consultant, South Carolina, USA
A
  • Anonymous
  • July 11, 2016

I have a file definition that has group elements and redefines. That has a section that looks like this.

10 IXKEY1           in positions 1 to 51

  15 IXTYPE PIC 9(01)  in position 1

      88 IS-CUSTOMER VALUE 1,6

      88 IS-BLANK VALEU 3 THRU 5,7

      88 IS-VENDOR VALUE 2

  15 IXITEM PIC X(20) in position 2 thru 21

  15 IXCOVN PIC X(10)                                                      in positions from 22 to 31
  15 IXCNBR REDEFINES IXCOVN
     20 IXCUST PIC X(07)
     20 IXCSEQ PIC 9(03)

  15 IXSUBN PIC X(20)      in position 32 to 51 

I have all these fields defined in my table and have run the Null Tool Utility across the file except for key fields. Unfortunately this area is part of an index key and I am hesitant to include these based on the warning message I get from Relativity. So when I try to read the entire file I get the aborted message of "Numeric item contains nonnumeric characters (column IXCSEQ) -- because IXCSEQ will only be numeric for 3 out of the 7 conditions for the file. Any design suggestions out there to work around this so that I can do a select on the entire file, or do I need to put in record types any break out into 3 different tables?

Also if I use the same file and skip the group level items when I make my table, I still get the message that it is not writeable because of
 
IXCOVN, IXCUST, IXCSEQ overlap message.  Any suggestions are appreciated. Thanks.


#RELATIVITYGROUPS
#REDEFINES
#OVERLAP
#WRITEABLE

Tom, Thanks for the details. I will have to think more about the way that Cobol files in the past have been shared as Relativity is enforcing better rules for data type. Need to think more about normalization. You are definitely correct that when file structures like this were created the idea of using the same data area to save memory/storage was huge.

Thomas Morrison
  • Thomas Morrison
  • Participating Frequently
  • July 11, 2016

I have a file definition that has group elements and redefines. That has a section that looks like this.

10 IXKEY1           in positions 1 to 51

  15 IXTYPE PIC 9(01)  in position 1

      88 IS-CUSTOMER VALUE 1,6

      88 IS-BLANK VALEU 3 THRU 5,7

      88 IS-VENDOR VALUE 2

  15 IXITEM PIC X(20) in position 2 thru 21

  15 IXCOVN PIC X(10)                                                      in positions from 22 to 31
  15 IXCNBR REDEFINES IXCOVN
     20 IXCUST PIC X(07)
     20 IXCSEQ PIC 9(03)

  15 IXSUBN PIC X(20)      in position 32 to 51 

I have all these fields defined in my table and have run the Null Tool Utility across the file except for key fields. Unfortunately this area is part of an index key and I am hesitant to include these based on the warning message I get from Relativity. So when I try to read the entire file I get the aborted message of "Numeric item contains nonnumeric characters (column IXCSEQ) -- because IXCSEQ will only be numeric for 3 out of the 7 conditions for the file. Any design suggestions out there to work around this so that I can do a select on the entire file, or do I need to put in record types any break out into 3 different tables?

Also if I use the same file and skip the group level items when I make my table, I still get the message that it is not writeable because of
 
IXCOVN, IXCUST, IXCSEQ overlap message.  Any suggestions are appreciated. Thanks.


#RELATIVITYGROUPS
#REDEFINES
#OVERLAP
#WRITEABLE

Please don't ignore my initial response: It is probably better to use two tables.

I think that doing so will probably eliminate the overlapping operands (assuming Customers uses the CUST and SEQ fields, and vendors use the group item).  Remember that it is the *table* that is writable, so there is no need to have a single table 'covering' all the underlying data items.  If you have a Customer table and a Vendor table, they can both be writable.

If the SQL user needs the two tables combined on occasion, the SELECT ...  UNION might be useful.  But my guess is that the SQL side really doesn't want a combination of customers and vendors - they seem pretty far removed from each other in most applications (realizing yours might not follow that general rule).

Tom Morrison, Consultant, South Carolina, USA
A
  • Anonymous
  • July 13, 2016

I have a file definition that has group elements and redefines. That has a section that looks like this.

10 IXKEY1           in positions 1 to 51

  15 IXTYPE PIC 9(01)  in position 1

      88 IS-CUSTOMER VALUE 1,6

      88 IS-BLANK VALEU 3 THRU 5,7

      88 IS-VENDOR VALUE 2

  15 IXITEM PIC X(20) in position 2 thru 21

  15 IXCOVN PIC X(10)                                                      in positions from 22 to 31
  15 IXCNBR REDEFINES IXCOVN
     20 IXCUST PIC X(07)
     20 IXCSEQ PIC 9(03)

  15 IXSUBN PIC X(20)      in position 32 to 51 

I have all these fields defined in my table and have run the Null Tool Utility across the file except for key fields. Unfortunately this area is part of an index key and I am hesitant to include these based on the warning message I get from Relativity. So when I try to read the entire file I get the aborted message of "Numeric item contains nonnumeric characters (column IXCSEQ) -- because IXCSEQ will only be numeric for 3 out of the 7 conditions for the file. Any design suggestions out there to work around this so that I can do a select on the entire file, or do I need to put in record types any break out into 3 different tables?

Also if I use the same file and skip the group level items when I make my table, I still get the message that it is not writeable because of
 
IXCOVN, IXCUST, IXCSEQ overlap message.  Any suggestions are appreciated. Thanks.


#RELATIVITYGROUPS
#REDEFINES
#OVERLAP
#WRITEABLE

Tom, Your assumption is correct and I have broken off into two different tables for right now. For this particular file there would not be a reason to see both customer and vendor but there are other files that do and so I will think about doing a SQL union.

Recent badge winners

Explore all badges
Terms & ConditionsAccessibility statement