We have 3 databases: the first for development, the second for testing by our users and finally the third for production. At this moment all our project leaders have the GRANT rights to create and alter tables, triggers, views, FK-constraints, ... on all three databases.

There are multiple development teams, all working on different parts of the database, all with their own development / testing / production cycle.
Normally we replace the development and testing databases with a restore of the production database after 14 days. So development and testing can happen with realistic data.

Due to a new regulation we have to restrict the access of the project leaders to the production environment. That means we will have to do their job of creating and altering tables, views, ... We have to make sure that the scripts that were used to alter the test database (and that were tested and approved) are exactly those that will run on the production database.

What we came up with is this:

• We allow them full access to the development database, as they can today
• They give us the necessary (SQL?) scripts to go from a given version to the next version of that part of the database they are working on (we call that delta-scripts)
• We run their delta-scripts on the development and test database after each restore, and finally on the production database.

But we see a lot of practical problems with this. For starters:

• Views, triggers, SP's, indexes, FK's , ... can or can not be affected by any of these delta scripts.
• This process should be fully automated, as it will have to be invoked after each restore.
• The delta scripts can be altered during development en testing, yet the code that finally will run on production must be exactly the same as the one that was tested on the test database.

Any better ideas than what we came up with? Where can I find a good guide of how to manage this ?
What tools are available that can help us with the delta-scripts?

I'm surprised that you managed to have project leaders handle the db2 changes on production without any problems.

Basically, you seem to be moving in the right direction .. Well, if you want a spohisticated and automated change maangement system, then you should have tools like IBM change manager.

IMHO, the scope of your question is too wide to be able to answer in a discussion forum ... ( I have even had an assignment for a client exclusively to address this issue).

Sathyaram,

Thank you for your reply.

Can you direct me toward publications, books, on-line resources,... that can help me with this?