September, 2022
Recently I had a question about representing many-to-many relationships between records using VSAM. The best implementation that I have had experience in implementing that was a purchasing system my group created in the late 1980s. We used a single VSAM cluster, I don't remember whether the base cluster was KSDS or ESDS, but either will work equally well. For each original record in a VSAM object we needed to relate to one or more other records, we would construct a key and write to this 'master' VSAM cluster. There were Alternate Indexes over the 'master' cluster, so that we could retrieve the 'master' key record, which would then point us to the various 'linked' records in the other VSAM clusters.
For an illustration of building multiple Alternate Indexes over a cluster, I created an ESDS cluster and populated it with some test data (created by my test data generator on my Linux desktop machine). Then I used IDCAMS to define and build four indexes for the ESDS cluster. I defined the indexes as UPGRADE, so that once they are initially created, any updates to the ESDS cluster will automatically add the index records to each Alternate Index. In a volatile system, which was being accessed in real time (through, for example, CICS) you would want the indexes updated automatically. If you were building a system that would not be updated in real time, you might define the indexes as NOUPGRADE to improve performance, and then delete and rebuild the indexes (and paths) whenever you did updates of the subordinate (linked) clusters.
The single jobstream to run the demonstration may be downloaded from: esds_via_alternate_indexes.tar.gz (6kB) [MD5: 83606ccb8197cca94baed3bbb247fff3]. You will need to tailor the JCL to correctly create the VSAM objects on your system; search for the high level qualifier of JAY01 and volume serial PUB000. My VSAM object definitions specify UNIQUE, so you do not need to point to a volume on which VSAM Dataspace already exists.
There are four steps to the job:
Step one deletes (if it already exists) the ESDS cluster, then defines it;
Step two reads the test data (in 80 column format, two records for each target ESDS record) and writes the concatenated record to a temporary sequential dataset;
Step three loads the test data in to the ESDS, then defines and builds the four Alternate Indexes, and defines the Paths relating the indexes to the base cluster;
Step four is a fairly simple COBOL program, using my VSAMIO routine, to read the contents of the ESDS cluster four times, once using each of the Alternate Indexes.
The output from this job on my system may be viewed at: esdsaixd.pdf.
I hope that you have found my instructions useful. If you have questions that I can answer to help expand upon my explanations and examples shown here, please don't hesitate to send them to me: