CA IDMS SQL doesn't see a junction record that contains only a FILLER

Products

• IDMS
• IDMS - Database
• IDMS - ADS

Issue/Introduction

Junction records are used in a network database design to connect two occurrences of the same record type. Some sites also use this term to refer to a record which serves only to connect two record types, but contains no actual data itself. Junction records typically contain no actual data.

How can CA IDMS SQL be used to follow a path that contains a junction record, when that record contains only a FILLER and no actual data? 

Environment

This challenge can arise in any CA IDMS environment that uses SQl to retrieve network database records, where the database structure contains junction records. It can also occur in web-based searches against these records.

Resolution

The problem is the lack of named data elements in the junction record. The way the IDMS SQL support works is that it "translates" network structures into SQL structures whenever possible, when a query is executed. There are a few things that can't be translated, and FILLERs are one of those. There's no such thing as a filler in an SQL database. So there's no way to make a record with no named fields into something that SQL can understand. 

There are three options in this situation: 

 
1- Create a new data element in the dictionary called FILLERX or something similar, with the same picture as the existing FILLER field in the current junction record DPR-SPECJN. Update the definition of the DPR-SPECJN record to drop the FILLER field and include instead the FILLERX field. 
a- Because this is now a named element instead of FILLER, the SQL optimizer will recognize it and the record containing it. 
b- Because you have kept the new field with the same PIC as the old FILLER, the record size hasn't changed so you don't really need to recompile anything. 
c- Recompile the schema to include the new definition of the DPR-SPECJN record, and use that schema as the basis for an SQL schema. 
d- With that definition, you should be able to specify the set names in the WHERE clause & the DPR-SPECJN record name in the FROM clause, and the data retrieval should work fine. 

2- Similar approach to above, but perhaps slightly simpler: you could create a Record Synonym and give FILLER a valid Column Name in the record synonym. Then modify one of the schemas to use the synonym and have the SQL schema definition reference that network schema with the synonym. 

3- You could instead create a table procedure to walk the existing sets & junction record, and invoke the table procedure to retrieve the data. BUT I think you'd have to write the Table Procedure from scratch because QuickBridge (which many folks use to generate table procedures) won't be able to access the record with only the filler in it either.

 

Additional Information

Additional information about using CA IDMS SQL to access network database records can be found here: 

https://docops.ca.com/ca-idms-ref/19/en/sql-reference/accessing-network-defined-databases