Relativity and SQL commands

Cliff, the error message says it all.  The ON clause of the JOIN specifies columns that match.  This is standard SQL, and not peculiar to ODBC or Relativity.

Use the WHERE clause to specify a column value matching a literal.

Now, with that said, the fact that you are using a literal to specify a column value in an index screams out that you should be defining this as a record type in the meta-data along with a table built on that record type, rather than forcing the SQL user to know the magic incantation of IXTYPE = 1.  Care taken in the design of the database, with an eye on how it is presented to the SQL coder, will pay dividends over the long term that overshadow the modest effort required to make the database easy to use.

Tom,

Thanks for the response. I follow you on setting up record types and using the where clause but the query that I have been able to run with a previous ODBC driver is so much faster when I could define the ON clause properly for the right key sequence. I am going to just have to add another key to this file so that I can get around the use of the literals. Might you happen to have a small example of multiple files being referenced in the same table? I say this because some of my Cobol data has redefines in the index area and I can make separate tables based on the record type info but for some vb projects that have been written in the past function better when you can see data from one query that contains information from the two separate files - like a customer cross reference file and a stock inventory file so that you can see the stock info along with the customer number on one record line for multiple items.

Cliff,

A properly defined record type in Relativity will obviate the need for specifying such literals in an ON clause, because the literal is held as part of the record type filter.

See topic Defining Record Type Filters In the Designer help file. 

A table should be defined on only one record type (though the filter may have multiple conditions that specify the record type).  For a record type where a literal defines the value of a field in the key, Relativity will automatically supply that literal value without the need to specify it in the ON clause.  My guess is that your other ODBC driver had no way to provide this information in the metadata, thus forcing this magic incantation out into the SQL.

The process is fairly straightforward and described in the topic Defining Record Types.  Determine what your record types are, and what rules define the record types.  Go into the file definition view and add the 88 item (conditionals) on the data items that will use literals in the record type filter; give these condition items reasonable names.  Then, create the record types, adding the record type filter as you go using the condition-items you defined earlier.  Then, define tables on each record type.

I would have to see the underlying COBOL file(s) to provide enough context to answer your question regarding an example.  If you provide the source files for the FD(s) involved (suitably reduced for the purpose of a simple example), I will try to find some time to give an example.  In general, you are describing something I would consider not difficult at all.

Tom,

Appreciate your comments.  The defining and using different record types is working just fine.

Briefly the issue I have where we used to use joins quite a bit is getting what I would call simple data from multiple files to come back with one row in a record set.  Not sure if I need to use item patterns or not but not getting the desired results.

Say I have a table called history that contains historical sales details and has a field labeled as HIST-ITEM [pic x(20)]. In the table inventory there is a corresponding field labeled  as INV-ITEM with the same picture clause. Also in the inventory table there is a field called INV-DESCRIPTION.  Can I modify the table history to also contain this description field?

Not item patterns, item sets.  See the topic, Defining Item Sets.  Then, follow the links.

It is quite typical in COBOL applications, especially those created before split keys, to have data replicated in more than one key.  In your case, the item's id (which you identify as INV-ITEM) may actually be in multiple keys, in order to provide better performance in reports and lookups.  In COBOL, the programmer would make the choice by which key is specified in the START statement.  (Split keys can alleviate this type of redundant data because a data item can be specified as a member of several keys - almost like item sets in reverse in the COBOL source.)

If Relativity knows that the same data appears in more than one key, it can do a much better job of optimizing the query.  It does so by choosing the key where,  in this case, the item identifier is in the leftmost position in the key.  

So, determine which sets of items contain redundant data.  In one i was looking at this weekend, there were three sets in one record layout, comprising a total of eight items (two sets of three, and one set of two).  Note also that you should define only one column in the table for each item set;. Pick whichever data item of the set is convenient (and in the record type).  Relativity will use the most appropriate COBOL data item out of the set for the join.

Without this hint (which, again, the COBOL programmer has in his brain and codes the START accordingly), Relativity will be forced to do a full table scan, which means absolutely rotten performance.

I don't think we are on the same page.  I am trying to get data from TWO different files and show it in one table, this is not a case of redundant fields in the same file.

Very scaled down version

FD INVHST-FILE

  RECORD CONTAINS 170 CHARACTERS

  LABEL RECORD IS STANDARD

  DATA RECORD IS INVHST-RECORD.

01 INVHST-RECORD.

 05 IHREC1.

 10 IHKEY1.

   15 IHCUST PIC X(10).

 10 IHITEM PIC X(20).

................

FD INVMAS-FILE

  RECORD CONTAINS 1100 CHARACTERS

  LABEL RECORD IS STANDARD

  DATA RECORD IS INVMAS-RECORD.

01 INVMAS-RECORD.

 05 IMREC1.

 10 IMKEY1.

   15  IMITEM PIC X(20).

 10 IMDESC.

Can I take the Relativity table I have defined for INVHST file and add to it fields from the INVMAS file based on the relationship where INVHST:IHITEM = INVMAS:IMITEM ?

I thought there was a way to combine fields from 2 or more files into 1 table. If I am mislead that is not a big deal but if I can that reduces the JOINING that would need to be done outside of relativity.

"It is quite typical in COBOL applications............."

At the risk of being seen as pedantic.........this should probably read 'typical in RM/COBOL applications rather than 'typical in COBOL applications'.

Most of the COBOL world is on mainframe, using proper databases (Eg. DB2) rather than this awful prehistoric ISAM file system. As a 30 year veteran of COBOL (mainframe), with many companies in multiple countries, I am using RM/COBOL for the first time. It is in no way 'normal COBOL'. My head has exploded several times.....

RM/COBOL is a pretty strict implementation of the 1985 COBOL standard.  It made a lot of money for the Ryan McFarland company, back in the day when PC's and UNIX machines didn't have proper databases such as DB2.  Many of the RM/COBOL applications that are still running today where written between 1985 and 1990, and yes, they are using ISAM.  As usual, it was considered too risky to rewrite a working application to use a database.

So, sorry about the exploding head, but the product is what it is.

Cliff,

You say, "...but if I can that reduces the JOINING that would need to be done outside of relativity.."

Okay, sorry for the misdirect.  Guess I took off when I saw item patterns, never to look back.

Although not available in the Relativity Designer (unless it has been added and I am unaware), the Relativity database can store Views.  A View behaves for the user much like a Table, and is often used to create convenient and easier to use...views  (for lack of a better term)  of the data..  Views can be used to hide the join information from the user, for example, making for a SELECT statement that is much simpler..

I don't think you can use the SQL Window in the Designer to do a CREATE VIEW statement.  However, there are other ODBC clients around that can.  For example, you can use OdbcTest found here.  I haven't used this one, but it seems to be actively maintained.

The grammar of the CREATE VIEW statement is documented in the Relativity DBMS help file.  Search for "CREATE VIEW".

jcr, first, welcome to the Micro Focus Community.  There are a lot of folks here that can help you become familiar with the technology, and perhaps offer a few pointers along the way to make your job, if not more enjoyable, at least a bit less messy.    You might be surprised about how much an RM/COBOL (or other Micro Focus COBOL offering) application can be a full participant in today's web-enabled world.

"As a 30 year veteran of COBOL (mainframe), with many companies in multiple countries, I am using RM/COBOL for the first time."

To paraphrase Ronald Reagan's famous quip from his debate with Walter Mondale, I won't hold your youth and inexperience against you.  I have had my IBM "THINK" sign for almost 50 years. Click on my name above and read my bio; perhaps you can understand why I don't think ISAM is prehistoric, because I can remember when it did not exist. 

Much to the consternation of the IBM sales force, there has been an entire world of COBOL outside the IBM mainframe orbit for decades.  And much of that world uses indexed files (the proper term as defined in the COBOL standard, ISAM being one of IBM's implementation of indexed files).  Some of it uses non-relational database.  Do you remember IBM's IMS?  Been there, done that!  Other vendors offered many flavors of 'database' technologies.

So, welcome to the vast world of non-IBM COBOL.

Both you and Mike Schultz (who, frankly should know better! ) also use the term "proper database".  Your application, whatever it may be, has a database, and whether it is proper or not being much more a matter of design and not technology.  Another fact to consider: DB2 and other relational database offerings (notably the very popular MySQL) were, at least in their beginnings, a layer on top of indexed files (DB2 over ISAM and VSAM, MySQL over MyISAM).  The relational overlay was there to implement (and perhaps enforce) the design concept of relational database.  Relational database vendors, as they have sought to gain market advantage through the addition of proprietary features (and significant bloat) have moved away from those 'ISAM' origins, but you can still run MySQL on MyISAM.

"It is in no way 'normal COBOL'."

Well, the only reasonable definition of normal would be that of the ANSI/ISO standard for COBOL. (This is, I think, Mike's main point, although I would disagree with his word 'strict'.)   The COBOL standard, by the way, never mentions relational databases.  You are attributing normal to your experience, which most of us do.  That's a normal thing to do.  

Without having actually seen the COBOL programs in your application, I can only speculate that you are involved in a poorly written application, perhaps also with a poorly designed database.  The fact that the application's database is implemented with direct use of indexed files (instead of through a SQL layer) is not the cause of your head explosion; yours is a post hoc ergo propter hoc analysis.  I design and implement third normal form databases using indexed files all the time, and have for years.  First design, then choose the appropriate technology.

Hang in there!  There's a lot of folks here that have shared your pain, and can offer help

<<Both you and Mike Schultz (who, frankly should know better! ) also use the term "proper database". >>

I believe that, if you go back and look at my post, I included "proper database" in quotes because I was echoing jcr's wording.  So, yes, I know better.

Umm, not seeing them!  So, either it is my old eyes, or yours...   

Thanks Tom - yes, I agree pretty much with most of what you said. And yes, my 'head explosions' are not so much due to RM/COBOL itself, but this particular system itself. Specifically:

- the COBOL code is stuck somewhere in the 70s or early 80s, before 'structured COBOL' became the norm. ie. no structured hierarchical program design, a bad/incorrect mix of sections and paragraphs (and their labelling), GO TOs all over the place (not just to a section's exit para, which I would allow), full stops (aka 'periods', for the Americans) everywhere rather than the END delimiters introduced in 85, plus no proper titles/descriptions/comments, and NO documentation, etc etc

And it is bug ridden. All support/maintenance has been from a reactive-fix-the-results mentality, rather than a prevent-the-problem mentality.

Add to this the ISAM issues (some are specific to here):

   - no data integrity

   - no constraints, rules around foreign keys, allowable key values etc

   - massive denormalisation

   - no querying ability - only programmatic data extraction/presentation - I can't see the data!

   - no logical units of work (commit/rollback)

That's what I meant about 'proper database' rather than just a 'file system' - it protects data integrity, and is properly queryable. And more, of course. Yes, a database like DB2 has underlying VSAM file system, but it is much more than just the underlying file system. Also on a mainframe, eg using Z/OS, there are even tools like Fileaid which give excellent access to the raw data on VSAM and other file systems.

As for 'normal COBOL' - yes, of course based on my experience only. But in my 30 years, the vast majority of COBOL jobs advertised were not Unix based.

I know there are more modern tools (hence this forum topic) which deal with a lot of the above, but none present here. And I look forward to any advice on how to EASILY deal with a lot of that (I have no say in budget).

Many thanks to you all.

Thanks Tom - yes, I agree pretty much with most of what you said. And yes, my 'head explosions' are not so much due to RM/COBOL itself, but this particular system itself. Specifically:

- the COBOL code is stuck somewhere in the 70s or early 80s, before 'structured COBOL' became the norm. ie. no structured hierarchical program design, a bad/incorrect mix of sections and paragraphs (and their labelling), GO TOs all over the place (not just to a section's exit para, which I would allow), full stops (aka 'periods', for the Americans) everywhere rather than the END delimiters introduced in 85, plus no proper titles/descriptions/comments, and NO documentation, etc etc

And it is bug ridden. All support/maintenance has been from a reactive-fix-the-results mentality, rather than a prevent-the-problem mentality.

Add to this the ISAM issues (some are specific to here):

   - no data integrity

   - no constraints, rules around foreign keys, allowable key values etc

   - massive denormalisation

   - no querying ability - only programmatic data extraction/presentation - I can't see the data!

   - no logical units of work (commit/rollback)

That's what I meant about 'proper database' rather than just a 'file system' - it protects data integrity, and is properly queryable. And more, of course. Yes, a database like DB2 has underlying VSAM file system, but it is much more than just the underlying file system. Also on a mainframe, eg using Z/OS, there are even tools like Fileaid which give excellent access to the raw data on VSAM and other file systems.

As for 'normal COBOL' - yes, of course based on my experience only. But in my 30 years, the vast majority of COBOL jobs advertised were not Unix based.

I know there are more modern tools (hence this forum topic) which deal with a lot of the above, but none present here. And I look forward to any advice on how to EASILY deal with a lot of that (I have no say in budget).

Many thanks to you all.