Discovering Database Relationships
Most DB2 for i databases contain thousands of tables and NO explicit declaration of referential constraints. Implicit relationships are buried in the code and/or in the minds of the developers – a technique that opens your business to extra development work and the risk of losing valuable knowledge of your database structure.
By revealing and using database relationships with Xcase Relate-DB, organizations and developers on the IBM i gain several very valuable benefits:
- Better Documentation
The relationships between the database tables are essential to properly understanding the structure of the database. Document this information to speed up development, off-load key development personnel from routine tasks and ensure that this critical knowledge remains within your business.
- Better Support for Query Tools
Query tools such as Web Query will use the retrieved relationships to automate the definition of sophisticated queries. Make sure your database has the metadata necessary to implement affordable business intelligence.
- Better Integrity
Referential integrity for the discovered relationships can be implemented at the database level. By moving relationship logic into the database, you shorten development cycles, guarantee consistent use of integrity rules and simplify your programs.
- Better Performance
As an added benefit, the database engine uses the declared relationships within the database, yielding improved performance.
To achieve these benefits, we first need to discover all of the existing relationships and then, optionally, implement them within the database. Xcase Relate-DB offers a solution that greatly simplifies both steps.
Step One: The Discovery Process
Although certain members of your development team have in-depth knowledge of the implicit relationships within your database, making all details of those relationships explicit is an extremely time consuming and error prone task.By cleverly automating this task, Relate-DB minimizes the time and effort required from the development team, allowing them to focus exclusively on validating the automatically discovered relationships.Xcase uses a unique, data-centric approach that exploits all the information available from both the meta data and the physical data. The code itself is analyzed only to determine which files are potentially related.During the discovery process, Xcase analyzes each potential parent-child couple and uses all meta data available to qualify possible relationships. Xcase then physically verifies the candidate relationships. At the end of the process, the developer needs only to validate the small set of relationships that have been selected among the hundreds of thousands of possibilities.
Using the same methodology, Xcase can also identify date fields that have not been formally declared as date types.
Step Two: The Implementation Process
Once the relationships have been discovered and validated, it is desirable (but not mandatory) to implement them in a modernized SQL database in order to obtain all the benefits of database normalization. Even if you choose not to modernize your database to SQL or implement the relationships in the database, the discovered relationships are still very useful as documentation and as meta data needed by tools such as Web Query.
Once all relationships have been defined, implementing them manually can be a complex and tedious task for three reasons:
- Impact on Existing Applications – When a referential constraint is declared, the database engine will not tolerate orphan records. For example, your application creates an Order Line before creating the (parent) Order Header record that holds the order total. If a referential constraint is created between the two tables, the application needs to be modified so that the Order Header record is created before the Order Line records. As a rule, like in the modernization process, it is highly advisable that the implementation does not force the developer to analyze all the existing code in order to identify possible conflicts with the new referential integrity constraints.
- Non-SQL-able Relationships – If a discovered relationship is based on a filtered unique index, the relationship cannot be declared as is in an SQL database. For example, let’s say that a relationship exists between the customers flagged as “Active” and orders. This is materialized by an LF on Customer having a unique index on customer.custno and a selection clause on customer.active=1. This type of relationship is not supported by the SQL Engine because the declaration of referential constraints does not support a selection clause. You can link only columns of a unique key in the parent table to columns in the child table.
- Relationships based on the Surrogate Parent Key (PK) – It is a best practice to define PK Surrogates in the modernized tables. In this case, it is also highly advisable that the declared relationship references the Surrogate PK and not the original Parent Keys.
All of these issues are easily resolved by establishing a relationship using the PK Surrogate in the parent and a new column which we will call an FK Surrogate in the child. If the values of the FK Surrogate column are properly initialized and maintained, this relationship will be functionally equivalent to the discovered relationship by linking the exact same set of records in the parent and child tables. This task of creating this “Surrogate” Relationship is completely automated by Xcase and solves all the issues described above.
- Impact on Existing Applications – When an orphan is created inadvertently or temporarily as in the example above, the FK Surrogate can be set to Null, the referential constraint will not be violated, and the existing application can continue running as is. When a value is set for Order, the FK Surrogate can be automatically set to the value of the PK Surrogate of the parent, linking the Order Line to the Order.
- Non-SQL-able Relationships – As the PK Surrogate is by definition a Unique Key, the Surrogate Relationship is always SQLable, even when it represents a retrieved relationship based a filtered (selective) key.
- Relationships Based on the Surrogate PK – By design, the Surrogate Relationship references the PK Surrogate in the parent table, yielding consistent and efficient relationships.
Finally, Xcase automatically generates and activates carefully designed triggers to maintain proper values for the FK Surrogate for a self-initiating and self-maintaining system.
Request additional information or a guided demo with a DB2 for i modernization expert.