Appendix B - Multiple Owners In A Single Database

In the schematic referenced in the Introduction, you'll notice that there are two table owners in the system database. We refer to the first owner as "staging" and the second owner as "production".

The staging owner is linked to the tables into which you insert your pre-validated data. These tables have an owner ID of CISSTG.

The production owner is linked to the tables used by your production system. These tables have an owner ID of CISADM.

When the validation programs run against your staging data, they validate the staging data against the production control tables (and insert errors into the staging error table). This means that the SQL statements that access / update master and transaction data need to use the staging owner (CISSTG). Whereas the SQL statements that access control tables need to use the production owner (CISADM).

But notice that when these same programs run against production (Validate (b)), the SQL statements will never access the staging owner. Rather, they all point at the production owner.

This is accomplished as follows:

• A separate application server must exist for each owner. Each application server points at a specific Tuxedo server. The Tuxedo server references a specific database user ID.

• The database user ID associated with the staging database uses CISSTG as the owner for the master and transaction tables, but it uses CISADM as the owner of the production control tables.

• The database user ID associated with the production database uses CISADM as the owner for the master, transaction, and control tables.

You may wonder why we went to this trouble. There are several reasons:

• We wanted to re-use the validation logic that exists in the programs that validate your production data. In order to achieve this, these programs must sometimes point at the staging owner, and other times they must point at the production owner (and this must be transparent to the programs otherwise two sets of SQL would be necessary).

• We wanted to let you use the application to look at and correct staging data. This can be accomplished by creating an application server that points at your staging database with the ownership characteristics described above.

• We wanted the validation programs to be able to validate your production data (in addition to your staging data). Why would you want to validate production data if only clean data can be added to production? Consider the following scenarios:

• After an upgrade, you might want to validate your production data to ensure your pre-existing user-exit logic still works.

• You may want to conduct an experiment of the ramifications of changing your validation logic. To do this, you could make a temporary change to user exit logic (in production) and then run the validation jobs on a random sample basis.

• You forget to run a validation program before populating production and you want to see the damage. If you follow the instructions in this document, this should never happen. However, accidents happen. And if they do, at least there's a way to determine the ramifications.

While the redirection of owner ID's is a useful technique for the validation programs, it cannot be used by the key assignment and production insert programs? Why, because these programs have to access the same tables but with different owners. For example, the program that inserts rows into the person table must select rows from staging.Person and insert them into production.Person.

This is accomplished as follows:

• Views exist for each table that exists in both databases. These views have hard-coded the database owner CISADM (production). For example, there is a view called CX_PER that points at person table in production.

• The key assignment and insertion programs use these views whenever then need to access production data.