DDS to DDL Modernization - Adding Constraints

So another example in updating my sample legacy application is to add some integrity rules into my existing database.

If you have not seen the post on Surrogate DDS to DDL Modernization check Surrogate DDS to DDL Modernization - Example.

I have converted my Item Master File and my Item Class files to SQL tables and checked out and modified the relevant logical files as before. Now I have created a new object of type SQLCST (SQL Constraint) on my work list. This will contain the code to build the constraint.

My coded constraint looks like this, of course I could build this directly into the table, but I wanted to be able to back this constraint out in case it causes any issues with my application down the road.

When I promote the SQLCST file through to test, the following command is run to execute the SQL in place. This then builds the constraint over my database in the relevant environment.

Basically this constraint is stopping the user from deleting any class codes that are used on the item master file. In a legacy application this is normally coded in the RPG and could exist many times in different programs. Now when I refactor or rewrite my application, this code can be removed from the RPG, if indeed it was there in the first place. Now the code should only exist as a rule in my database.  

I will now modify any RPG programs that delete records from the reference file (ITMCLASSQ) to trap the Constraint Violation, here is a great article which demonstrates this in Free format RPG. There are also links to the old version.

My tactic here is to make many small changes as quickly as possible without destabilizing the whole application. My change management software makes sure that all my changes are documented and audited as I go. More importantly I can deliver changes to the business quickly and effectively in small projects.

Gotchas...

1. Any program that deletes records from the Reference file must be updated to trap the Constraint Violation. 

2. If your database was defined in a library and not a schema you will need to implement Journaling on the relevant files in order to use constraints. The relevant commands are CRTJRNRCV,CRTJRN and STRJRNPF.

This is what the SQL Manual states in small print.

A schema can also be created using the CRTLIB CL command, however, the catalog views and journal and journal receiver
created by using the CREATE SCHEMA statement will not be created with CRTLIB.