Copying Changes from one DB to Another

[ron]

Linux RHEL -- OE 11.7.17

Hi, I have a set-up that is pretty common -- TEST, UAT and PROD servers. We have an application that has both a character UI and via AppServers also a GUI UI.

When a change is implemented, it often involves a rather large number of menu changes in the character version. The changes have to be applied three times -- in TEST initially -- then UAT -- and then PROD. These changes can involve a lot of work and there are obviously opportunities for typing mistakes.

The changes will sometimes create new table records and sometimes changes to existing records.

I would like to be able to capture all the changes in a way that the changes could be applied to UAT and PROD -- eliminating the need to enter them all again manually.

The databases involved would usually not be identical -- they would each be at a different point in the system life cycle.

Can anyone offer any suggestion as to how this might be achieved?

Ron.

 

[TomBascom]

Much like with schema changes, you need a method to capture the deltas in development and then manage the promotion (and potential un-promotion or "demotion") process.

One way to do it would be some 4gl code that modifies the appropriate data in a manner that is appropriate to whatever release is being tested/promoted and then make that "data fix" program part of the release process.

 

[ron]

Thank you, Tom.

Yes, I appreciate that a 4GL-code solution is possible, but that would require quite a lot of R&D to discover exactly what is required.

I was hoping that there might be a way to capture details of what happens when all the menu changes are made to the TEST system such that they could be applied to UAT and PROD. If that could be done it would save a lot of effort and, most importantly, eliminate the chance of typing mistakes when implementing into PROD.

 

[TomBascom]

A big part of the problem is that every application does it differently. There isn’t a uniform data format for these things.

Maybe I am wrong but, as I recall, your application is a bespoke one is it not? If so then you would not have the option of following any “best practices” or tooling provided by the vendor.

One of the reasons that I mentioned using a coding approach is that it would tend to make it easy to integrate with an existing code-focused SCM tool (if you have one). Packaging up your changes as code might be less of a challenge if you already have something that handles that.

 

[Rob Fitzpatrick]

@ron I am trying to understand what you are talking about. Is it application changes or database schema changes? The thread title is about database changes. Can you please clarify?

When a change is implemented, it often involves a rather large number of menu changes in the character version.

What menu(s)? Are you referring to the Data Dictionary menus for editing the schema?

These changes can involve a lot of work and there are obviously opportunities for typing mistakes.

What exactly is all this typing in UAT and prod? Do you re-type the schema changes manually in each environment?

I was hoping that there might be a way to capture details of what happens when all the menu changes are made to the TEST system such that they could be applied to UAT and PROD.

If the changes you are referring to are specifically schema changes then yes, there is a way to propagate changes from one database to another. Though I think I would need to understand more about how you currently perform schema change management. You mentioned test, UAT, and prod environments. Those are deployment environments. Surely there are other environments as well, e.g. dev. Where and how do you manage your master schema for each version of your application?

 

[ron]

Sorry for not explaining things, clearly, Rob.

The application we use is large and complex (it's a banking system). Major changes (which happen rather often) almost always involve schema changes -- and we can handle that without a problem.

The "problem" is that the CHUI part of the system has an extensive menu system which is wholly contained within the application -- and maintained within the application. In other words we can add/change menus and bottom-of-screen slide menus. It also allows the maintenance of system parameters. These changes can sometimes take over an hour to do -- and there is obviously an opportunity for mistakes to be made.

All of the required changes are documented in detail by one of the developers when the changes are initially applied to our "test" environment -- and used later when the changes are applied to the "UAT" environment -- and finally when the changes are applied to "PROD".

Note, of course, that all of these changes will be stored in the database.

Applying the changes in "PROD" is our biggest headache because if a mistake is made the consequences are considerable since it can take valuable time which could take longer than the planned outage time-window.

So -- if the initial changes could be encapsulated such that they could be applied to the next environment "automatically" it would save time and significantly reduce the risk of mistakes.

This *could* be achieved using replication triggers -- but I think it would need some "clever" ABL code to interpret what the triggers capture such that the same actions could be applied to a further environment.

Ron.

 

[Rob Fitzpatrick]

If the CHUI menu system data is stored in application tables then I don't see why it wouldn't be possible for the developer to write an upgrade utility that makes and validates the necessary changes without requiring manual data entry.

Also, you may want to consider your promotion path. In addition to dev, test, UAT, etc., many FIs also have an environment called "staging" or "pre-prod" that is kept in sync with prod, in terms of code and schema and any necessary configuration data. Before changes are promoted to prod, they are first promoted to and tested in staging, to ensure that the upgrade process itself is working, and to ensure that any issues are caught by BAs or techs before promotion to prod. Because staging is not production, it can be updated during the business day without requiring a time-limited outage. So once you do take your prod outage, you are running a process your have already tested on a functionally-identical system, so you can have high confidence that the upgrade will succeed. Food for thought.

 

[ron]

Thank you Rob,

I didn't want to over-complicate the situation I was describing... we do have many environments, including Staging.

I personally don't delve into the application code very much but from what I see of how the menu systems works I think it would be a big stretch for our developers to write an ABL program to update the menus. That's why I was thinking that it could be done by capturing the changes with Replication triggers. (We have done a lot of work using Replication Triggers, so we're very familiar with them).

 

[TomBascom]

Why not just dump the tables that describe the menus?

 

[tamhas]

Or use the difference tool...

 

[ron]

Tom -- I am embarrassed that I didn't think of dumping/loading the tables that control the menu system!

That sounds like the best solution.

By the way ... what is the "difference tool"?

 

[dimitri.p]

Tom -- I am embarrassed that I didn't think of dumping/loading the tables that control the menu system!

That sounds like the best solution.

By the way ... what is the "difference tool"?

As much fun as it is to be dumping and loading data in pursuit of application functionality, it should be within the capabilities - but perhaps "below the pay-grade" - of a developer dealing with a "complex banking system" to write a exportmenusettings.p and importmenusettings.p
For some reason the last person to touch the database is usually blamed for whatever happens after , "since you touched the database". Including but not limited to power outage, leaky roof, visit from mother-in-law etc etc