Configuration Lab (ConfigLab)

In order to implement new business practices, users change configuration data.  The best way to confirm the system will work as desired is to test the changes using production data before the changes are promoted to production.  The Configuration Lab (or ConfigLab) tools exist to support this methodology.  Specifically, the ConfigLab tools allow you to:

·         Copy data from production to a "test" environment.

·         Change the configuration data as dictated by new / different business practices.

·         Confirm the changes are correct by using the system's online and batch transactions.

·         Approve or reject individual changes.

·         Apply the approved changes to production.

The ConfigLab tools have many uses in addition to testing configuration data and promoting the changes to production.  For example, the following points describe several ways users of Oracle Utilities Customer Care and Billing can use the ConfigLab tools:

·         Problematic customers and bills can be transferred to a "playpen" environment where the system's transactions are used to experiment with potential corrections without affecting production. 

·         Historical bills are transferred to a "revenue analysis" environment where the revenue team can perform what-if analysis to determine the impact of rate changes on future revenue.

·         Business process scripts developed in a "development" environment can be promoted to a "QA" environment where they can be tested thoroughly before being promoted to production.

In sum - the ConfigLab tools can be used to compare and promote data between any two databases.

The topics that follow describe how to use the ConfigLab.

Contents

The Big Picture of ConfigLab

Environment Management

Difference Query

Root Object

Root Object Exception

The topics in this section describe major concepts that will facilitate your use of the ConfigLab.

Contents

Same Word, Two Meanings

Pushing and Pulling Data Is Implemented Via Comparing

The Compare Process Is Controlled By Metadata

A Batch Control Must Exist

The Comparison Process Creates Root Objects

Applying Approved Changes To The Target Environment

How To Compare Objects In Two Databases

How To Copy Sample DB Processes From The Demonstration Database

Same Word, Two Meanings

We use the term Configuration Lab (or ConfigLab) to describe two different things:

·         A ConfigLab is a test environment where your organization can experiment with changes to configuration data.  For example, you can set up a ConfigLab environment to test changes to the algorithms used to calculate your customer's bills.

·         We also use the term ConfigLab to describe the tools used to compare and promote data between databases.  For example, you can use ConfigLab tools to promote changes to configuration data in a pre-production database to production.

Pushing and Pulling Data Is Implemented Via Comparing

The following points describe potential uses of the ConfigLab tools:

·         You might want to "push" problematic bills to a "playpen" environment where you can experiment with different options to correct them.

·         You might want to "push" existing rates from production into a "pre-production" environment where you can experiment with rate changes.

·         After you've tested new rates in a "pre-production" environment you can pull them into production.

While the above tasks are very different, they are all implemented using the same philosophy.  The following points describe this philosophy:

·         In order to transfer data between two environments, you execute a background process to compare the data in a "target" environment with that in a "source" environment.  For example, if you want to promote new / changed rates from a "pre-production" environment to production, the background process compares the rates in "pre-production" (the source) with the rates in "production" (the target).

It should be noted that the comparison is always performed in the "target" environment.  To continue with the previous example, you would execute the comparison process in production and you'd indicate you want to compare production with the "pre production" environment.

·         The ConfigLab categorizes the comparison results as follows:

·         Add.  If data exists in the source environment that is not in the target environment, the data is categorized as "add".

·         Change.  If data exists in the source environment with the same prime key as data in the target environment but with different column values, the data is categorized as "change".

·         Delete.  If data exists in the target environment that is not in the source environment, the data is categorized as "delete".

·         You use the Difference Query to view the results of the comparison.  This query also allows you to approve or reject the adds, changes and deletes.

·         After confirming the results of the comparison are as desired, you run the CL-APPCH background process (apply changes) to apply the approved changes to the target environment.  This background process is executed in the target environment (just as the comparison process was).

The Compare Process Is Controlled By Metadata

You do not write programs to compare data in two databases.  Rather, you set up meta-data to define the objects you want to compare.  After this set up is complete, you execute background processes to compare the desired object and, if desired, promote the differences. 

The following topics describe the meta-data that you set up to define what to compare.

Contents

Maintenance Objects Are Composed of Tables

Database Processes Define The Maintenance Objects To Compare

DB Process Instructions Limit Which Objects Are Compared

Maintenance Objects Are Composed of Tables

A maintenance object is meta-data that defines a group of tables that make up a logical entity.  Maintenance object meta-data exists for every object in the system.  For example, there is a maintenance object called USER.  This maintenance object's tables hold the information about your users. 

When you submit a background process to compare the tables in two databases, you don't tell the process about the individual tables.  Rather, you define the maintenance objects that you want compared and the system uses the maintenance object meta-data to determine the physical tables to compare (note, this is not 100% true, please see Database Processes Define The MOs To Compare for the complete story).

Please notice that the USER maintenance object has a single Primary table and multiple Child tables.  This is true of virtually every maintenance object in the system (i.e., all MO's have a single Primary table and 0 to many Child tables).  Each instance of a given maintenance object has a single row on the Primary table, and zero or more rows on each Child table.

The base package is shipped with all maintenance object and table meta-data populated.  You will not have to change this meta-data unless your implementation has introduced new tables. 

Database Processes Define The Maintenance Objects To Compare

A database process (DB process) is meta-data that defines the group of maintenance objects to compare.  To be exact, a DB process defines the maintenance objects whose tables are compared by a comparison background process.

A DB process has 1 or more maintenance objects; the number depends on what you want compared.  For example:

·         If you want to promote user information that was set up in a pre-production database to production, the related DB process requires a single maintenance object in it (i.e., USER).

·         If you want to copy the contents of every object that holds configuration data from pre-production to production, the related DB process would have many maintenance objects (i.e., one for every object that holds configuration data). 

When you link a maintenance object to a DB process, the system assumes that you want to compare EVERY row in every table defined on the maintenance object.  If this is what you want to do, then you are finished with the DB process configuration tasks.  However, if you want to compare a subset of the rows on these tables, the next section describes additional configuration tasks that must be performed.

DB Process Instructions Limit Which Objects Are Compared

A maintenance object specified on a DB process is referred to as DB process instruction (because it contains "instructions" governing the tables AND rows to be compared).

When you create an instruction (i.e., when you link a maintenance object to a DB process), you must define the type of instruction:

·         A Primary instruction defines a maintenance object that is independent of any other maintenance object on the DB process.  A DB process can contain an unlimited number of Primary instructions.

·         A Child instruction defines a maintenance object that is related to a Primary maintenance object in some way.  A Primary maintenance object can reference an unlimited number of Child maintenance objects.

An example will help explain the difference.  The following DB process is used to compare business process scripts.  This DB process has a single Primary instruction (SCRIPT) and many Child instructions (one for each foreign key referenced on the script maintenance object). 

A DB Process With 6 Instructions

Notice that each Child is linked to a Primary instruction.  This means that when the system compares a script, it will also compare the child objects referenced on each script.   

If you did not indicate these were Child instructions (i.e., they were marked as Primary instructions), the system would compare every Display Icon, Algorithm, Navigation Option and Function in the system; even those not referenced on a script.  In other words, Child instructions limit the child object that are compared to those related to its Primary maintenance object.

The following topics describe advanced DB process instruction concepts.

Contents

Table Rules Define Which Objects Will Be Compared

Criteria Algorithms Also Define Which Objects To Compare

Processing Algorithms Perform Extra Processing

Table Rules Define Which Objects Will Be Compared

If a DB process instruction has no table rules, every instance of the object will be compared when the compare process runs (and this might be exactly what you want to do).  However, assume you only want to compare business process scripts whose prime key starts with CI_.  To do this, you need a "table rule" on the DB process instruction associated with the SCRIPT maintenance object.  Table rules contain SQL snippets that are incorporated into the WHERE clause into the SQL statement used to select objects.  For example, the following table rule will limit the selection of scripts to that that start with CI_.

DB Process Instruction Table Rule

Table rules may be specified on any DB process instruction for any table within the related maintenance object.  However, most of the time, they are only specified on a Primary DB process instruction’s Primary table. 

Criteria Algorithms Also Define Which Objects To Compare

When the background process selects objects to compare, you can optionally have it call a plug-in to further restrict which objects are compared (table rules are the first level of restriction).  We refer to these algorithms as "criteria algorithms". 

Criteria algorithms are specified on DB process instructions (just as the table rules are).  You'd only need a criteria algorithm if the criteria could not be composed using an SQL statement (if you could compose the criteria using an SQL statement, you'd put this SQL in a table rule).

The comparison process passes criteria algorithms the primary key value of the selected maintenance object.  These algorithms simply return a Yes or No value depending on program logic that determines whether the object should be compared.  These algorithms are usually defined on a Primary DB process instruction, but there is no restriction.   

Processing Algorithms Perform Extra Processing

A DB Process instruction can have from 0 to many Apply Changes Processing algorithms.  These algorithms perform extra processing when the approved differences are applied to the target database (by running the Apply Changes (CL-APPCH) background process).  For example, if you were transferring Batch Control objects to production from a test environment, you could use an Apply Changes Processing algorithm to set each batch control's run number to the next available value in production (rather than use the last run number in the test environment).  When the Apply Changes executes, it simply passes the primary key of each changed object to the algorithm(s).  The algorithm can then update the object's data as desired and these changes are committed when the maintenance object is committed to the target environment. 

A Batch Control Must Exist

A DB process must reference a batch control.  This batch job is submitted when you want to compare the maintenance objects defined in the DB process. 

By convention, we recommend that you name the batch control the same as the DB process.  For example, a DB Process CI_COPSC (Copy Scripts) references a batch control CI_COPSC.  This naming convention is not required; it's recommended to simplify the administration of the batch controls.

To create a new batch control, you must duplicate the batch control CL-COPDB provided in the base product, because aside from the batch code and description, all the settings for the new batch control must match the base one.

The Comparison Process Creates Root Objects

When you submit the batch control defined on the DB process, "root objects" are created.  A root object is a record that a difference exists between a maintenance object in the source environment and the target environment.

This diagram shows the state transition of root objects:

·         Both the Initial and Pending states are transitory and should not be visible to end-users.

·         The comparison background process puts root objects into either the Approved or Rejected state.  You control the value by populate the parameters on the background process accordingly.

·         You can manually change a Rejected root object to Approved (and vice versa).

·         When you are ready to commit the changes to the target environment, run the apply changes (CL-APPCH) background process.  This process only processes Approved root object.  When a root object is processed, its status is changed to Complete if no problems are encountered (i.e., the change is committed on the target environment).  The status of the root object is changed to Error if problems are encountered (e.g., validation fails).

·         You can transition an Error object back to the Approved state if you want to reattempt applying the change to the target environment (you must resubmit the Apply Changes (CL-APPCH) background process to do this).

·         You can transition an Error object to the Rejected state if you want to acknowledge that the change should not be applied to the target environment.

Applying Approved Changes To The Target Environment

As described above, you submit a background process to compare the maintenance objects in two environments.  This background process creates a "root object" record for every difference between the two environments.  You can approve or reject any root object; rejecting a root object means the related difference will not be applied to the target environment. 

When the root objects are in the desired state, you submit the Apply Changes (CL-APPCH) background process.  This process applies all approved root objects to the target environment.  If you've defined processing algorithms on the DB process's instructions, it will execute these as the changes are applied to the target environment.

How To Compare Objects In Two Databases

The above sections provided background information about comparing objects in two environments; the following section reiterates this information in step-by-step instructions:

·         Make sure that an environment reference exists in the "target" database pointing at the source database.  This environment reference must be defined as either a Compare Source or a ConfigLab.

·         Create a batch control for the DB process (if one doesn't already exist). 

·         Create a DB process to define the maintenance objects to be compared.  Make sure to reference the batch control created in the previous step.  Also inspect the DB process instructions to make sure they are consistent with what you want to compare.

·         Submit the DB process's batch control.

·         After the comparison batch process completes, transfer to the Difference Query to view the results of the comparison.

·         Approve all changes using the Difference Query. 

·         Reject all changes that you don't want applied.

·         After the changes are marked as desired, submit the apply changes background process (CL-APPCH).  After this process completes, return to the Difference Query to check that all of your approved changes have become complete (this means that the target database has been changed).

How To Copy Sample DB Processes From The Demonstration Database

Your product's demonstration database contains sample database processes.  These database processes reference logical groups of maintenance objects that many customers compare.  For example, the "control table" database process references every maintenance object that holds configuration data. 

You may find that these sample database processes closely match your needs.  If this proves true, you should copy these from the demonstration database.  This will save you time as you won't have to set up the each database process.  The topics in this section describe how to copy database processes from the demonstration database.

Contents

If You Work In A Non-English Language

Setup A DB Process To Perform The Copy

Run CL-COPDB and CL-APPCH

If You Work In A Non-English Language

The demonstration database is installed in English only.  If you work in a non-English language, you must execute the NEWLANG background process on the demonstration database before using it as a Compare Source supporting environment.  If you work in a supported language, you should apply the language package to the demonstration database as well.

If you don’t execute NEWLANG on the demonstration database, any objects copied from the demonstration database will not have language rows for the language in which you work and therefore you won’t be able to see the information in the target environment.

Setup A DB Process To Perform The Copy

The base package provides a DB process called CL-COPDB.  This DB process copies the sample DB processes.  This is confusing because you are configuring a DB process in one environment that copies DB processes from another.

This DB process has an instruction that references the database process maintenance object (MO).  This instruction has a table rule with an override condition that selects the all database processes that are prefixed with CI_ from the source database (there are numerous sample DB processes in the demonstration database and this process copies them all).  If you only want to copy a single DB process, update the table rule to only copy the desired DB process. 

Note that the DB Process includes additional instructions to copy any algorithms used by the DB process instructions and the associated background processes for each DB process.

Run CL-COPDB and CL-APPCH

After configuring the table rule on the DB processes instruction, submit the CL-COPDB background process in your target database.  When you submit this process, you must supply it with an environment reference that points to the demonstration database.  If you don’t have an environment reference configured in your target database that references the demonstration database, you must have your technical staff execute a registration script that sets up this environment reference.  Refer to Registering ConfigLab Environments for more information.

When the CL-COPDB process runs, it highlights differences between the DB process in the demonstration database and your target database.  You can use the Difference Query to review these root objects and approve or reject them. 

After you’ve approved the root object(s), submit the CL-APPCH batch process to change your target database.  You must supply the CL-APPCH process with two parameters:

·         The DB Process used to create the root objects (CL-COPDB)

·         The environment reference that identifies the source database (i.e., the demonstration database) 

When you want to compare maintenance objects in two environments, you submit a background process in the "target" environment.  When you submit this job, you must define the "source" environment.  This environment's data will be compared against the target environment's data.  You identify the source environment by supplying an "environment reference code" to the background process. 

You must run a utility to create an environment reference as this utility sets up many database synonyms (the comparison process uses these synonyms to access the data in this environment).  The topics in this section describe how environment references are created and managed.

Contents

Two Types Of Environments

Registering Environments

Deregistering ConfigLab Environments

Reregistering ConfigLab Environments

Database Users

Database Relationships

How To Register a ConfigLab Environment

Two Types Of Environments

When you run the utility that creates an environment reference, you must define the type of environment:

·         If you want the prime-key values of "random number" objects to be unique across both environments, indicate the environment is a ConfigLab. 

·         If you don't care about the uniqueness of the prime-keys in the two environments, indicate the environment is a Comparison Source.  If you choose this option and you add entities with system-assigned prime keys, there's a slight chance that your newly added rows will be rejected as duplicates if you promote the additions to the target environment.

Registering Environments

You must run a utility to create an environment reference.  Your implementation's database administrator should execute this utility because it performs functions that require administrative access.  The following points summarize what this utility does:

·         Adds an environment reference object.  This object defines the environment's:

·         Role.

·         Universal environment ID

·         The prefix character used for creating synonyms described below.

·         Creates remote table synonyms in the current environment that reference tables in the environment being referenced.  Refer to Database Users and Database Relationships.  The names of these synonyms are the same as the physical table names except the first character is replaced with the environment reference's prefix character.  For example, assuming the environment reference's prefix is Z, the CI_SA_TYPE table in the target environment database is referenced via the ZI_SA_TYPE synonym in the current environment. 

Environment Reference Example

Deregistering ConfigLab Environments

You should deregister environments that you no longer plan to compare.  When you deregister an environment, the remote table synonyms are removed and the environment reference status is changed to inactive.

Reregistering ConfigLab Environments

If you deregistered an environment and you need to compare maintenance objects in that environment again, you must reregister it.  You must also reregister an environment if you apply a single fix or upgrade that makes a database change. 

When you reregister an environment, the environment reference status is changed to active and the remote table synonyms are updated accordingly.

Database Users

When you install any Framework product, you define a user as the owner of the database schema objects.  We'll call this database user SPLADM.  To use ConfigLab tools, you need to define two additional database users in each environment's database:

·         A user with read/write access to the database schema.  We'll call this database user CISUSER. 

·         Note that this database user is installed automatically when your product is installed in an Oracle database.  For DB2, this database user must be added manually following installation and prior to environment registration. For MS SQL Server this database user is not necessary.

·         The application server(s) accessing the environment should be configured to access its database as the CISUSER database user. 

·         A user with read-only access to the database schema.  We'll call this database userCISREAD. 

·         Note that this database user is installed automatically when the system is installed in an Oracle database.  For DB2 and MS SQL Server, this database user is not necessary. 

Database Relationships

If an environment will be accessed by the ConfigLab tools (either as a source or a target), you must define database relationships between the two environments.

·         In Oracle, a database link defines a database relationship.  A database administrator must add database links in the "target" and "source" databases as described in the sections below.  These database links must be added prior to executing the environment registration utility.

·         In DB2, a database relationship is not a database object like it is in Oracle, however the same principles apply.  A database administrator must ensure that security is configured to allow the database users in both environments to access remote host schemas as described in the sections below. 

·         In MS SQL Server, a database relationship is defined with the linked servers.  A database administrator must add linked server and SQL Server data source (ODBC) for target and source database server prior to executing registration utility.

The DB2 version of the registration utility grants the privileges to individual remote host schema objects, the Oracle registration utility relies on the privileges associated with database links, whereas the MS SQL Server registration utility doesn’t grant any privileges, rather uses fully qualified object name.

The topics in this section describe these database relationships.

Contents

ConfigLab Database Relationships

Compare Target Database Relationships

ConfigLab Database Relationships

Prior to registering a ConfigLab environment, the following database relationships must be configured by your implementation's database administrator:

The following topics describe these database links.

Contents

From The Target To The ConfigLab

From The ConfigLab To The Target

From The Target To The ConfigLab

The diagram above shows how a database relationship allows the CISUSER database user defined in the target environment to access database objects in the ConfigLab environment’s database. 

The diagram below shows how the meter read table in a ConfigLab supporting environment’s database is accessed by the target environment using a remote table synonym, prefixed with "Z" in this case: 

ConfigLab Environment Tables Accessed Via Remote Table Synonyms

From The ConfigLab To The Target

Remember that when you register an environment as a ConfigLab, there is a database relationship from the CISUSER schema in the ConfigLab environment database that points to the CISUSER schema in the target environment.  This database relationship allows a subset of tables (i.e., the system-generated key tables) to access the target environment’s CISUSER schema when generating a key value for a new record. 

When a system-generated key value is assigned, the key value is also kept in the table's corresponding "key table" (see Defining Table Options).  Key tables store the environment identifier along with the key value. 

The key table in the target environment holds the key values of rows in its own environment plus the key values of rows in the ConfigLab environment.  This ensures that system-generated keys added in the ConfigLab are not used in the target environment.

The diagram below shows how the meter read key table is shared between a target environment and its ConfigLab environment:

ConfigLab and Target Environment Share Key Tables

Compare Target Database Relationships

Prior to registering a Compare Source environment, the following database relationships must be configured by your implementation's database administrator to match the diagram below:

Notice that there is no database relationship originating from the Compare Source schema back to the target environment’s schema.  This is because random keys added in a Compare Source are not kept in the target environment's key table (because the system does not guarantee unique keys between a Compare Source and a target environment).

The diagram below shows how the script table in a Compare Source environment’s database is accessed by the target environment using a remote table synonym, prefixed with "R" in this case: 

 

Compare Source Environment Tables Accessed Via Remote Table Synonyms

How To Register a ConfigLab Environment

A database administrator must execute the environment registration utility, as administrative access is required.  This utility is also used to deregister and reregister environments.

Contents

Oracle (EnvSetup)

DB2 (EnvSetup)

MS SQL Server (EnvSetup)

Oracle (EnvSetup)

The registration utility may be executed from any workstation configured to connect to both the supported environment database and the ConfigLab environment database. 

In this example, we describe how to register a ConfigLab (the same principles apply for Compare Source and Sync Target).  We'll call the supported environment database "CDXPROD" and the ConfigLab environment database "CDXCLAB". 

A sample file oracle-compare-source.bat is provided with an example for a Compare Source registration. 

You may specify the following parameters on the command line.  If parameters are not supplied, the registration utility prompts for them:

·         Information about the supporting environment database:

·         Name of the database

·         Application Schema owner

·         Application Schema user password

·         Database user with read-write privileges to the application schema

·         Database user with read-only privileges to the application schema

-s CDXCLAB,CISADM,{application schema owner password},CISUSER,CISREAD

·         Information about the supported environment database:

·         Name of the database

·         Application Schema owner

·         Application Schema user password

·         Database user with read-write privileges to the application schema

·         Database user with read-only privileges to the application schema

-r CDXPROD,CISADM,{application schema owner password},CISUSER,CISREAD

·         Action:

·         I-Install (register), U-Reconfigure (reregister), D-Uninstall (deregister)

-a I

·         Environment type of the supporting environment:

·         CMPS-Compare Source, SYNT-Sync Target, CLAB-ConfigLab, ARCH-Archive

-t CLAB

·         Environment reference code:

·         The environment reference used to track the ConfigLab environment.

-e PROD-CONFIGLAB

·         Name prefix:

·         The prefix character used to reference database tables in the ConfigLab environment.  Note that this character must not be C, S, or a name prefix used by an existing supporting environment.

-n L

·         Environment description:

·         Description of the environment reference.

-d Production ConfigLab

·         Source database link name:

·         Name of the database link from the production database to the ConfigLab environment database.

-x CDXPRODCISUSER-CDXCLABCISUSER

·         Target database link name:

·         Name of the database link from the ConfigLab environment database to the production database.  Not specified for Compare Source or Sync Target environments.

-y CDXCLABCISUSER-CDXPRODCISUSER

·         Oracle character set:

·         The Oracle character set specified for the production database.

-c {Oracle database character set}

·         Apply changes to the databases:

·         Specify this parameter to apply the changes directly instead of writing them to a log file.

-u

·         Owner Flag:

·         Specify the Owner Flag for the application.

-o owner flag value

·         Log file name:

·         Specify the name of the log file if the parameter above was not specified.

-l {log file name}

DB2 (EnvSetup)

The registration utility may be executed from any workstation configured to connect to both the production environment database and the ConfigLab environment database. 

In this example, we describe how to register a ConfigLab environment.  We'll call the supported environment database "CDXPROD" and the ConfigLab environment database "CDXCLAB". 

A sample file DB2-compare-source.bat is provided with an example for a Compare Source registration. 

You may specify the following parameters on the command line.  If parameters are not supplied, the registration utility prompts for them:

·         Information about the supporting environment database:

·         Location of the database

·         Database user with SYSADM privileges

·         Password of database user with SYSADM privileges

·         Application Schema owner database user

·         Database user with read-write privileges to the application schema

-s CDXCLAB,{sysadm user},{sysadm user password},CISADM,CISUSER

·         Information about the supported environment database:

·         Location of the database

·         Database user with SYSADM privileges

·         Password of database user with SYSADM privileges

·         Application Schema owner database user

·         Database user with read-write privileges to the application schema

-r CDXPROD,{sysadm user},{sysadm user password},CISADM,CISUSER

·         Action:

·         I-Install (register), U-Reconfigure (reregister), D-Uninstall (deregister)

-a I

·         Environment type of the supporting environment:

·         CMPS-Compare Source, SYNT-Sync Target, CLAB-ConfigLab, ARCH-Archive

-t CLAB

·         Environment reference code:

·         The environment reference used to track the ConfigLab environment.

-e PROD-CONFIGLAB

·         Name prefix:

·         The prefix character used to reference database tables in the ConfigLab environment.  Note that this character must not be C, S, or a name prefix used by an existing supporting environment.

-n A

·         Environment description:

·         Description of the environment reference.

-d Production ConfigLab

·         Apply changes to the databases:

·         Specify this parameter to apply the changes directly instead of writing them to a log file.

-u

·         Owner Flag:

·         Specify the Owner Flag for the application.

-o owner flag value

·         Log file name:

·         Specify the name of the log file if the parameter above was not specified.

-l {log file name}

MS SQL Server (EnvSetup)

The registration utility may be executed from any workstation configured to connect to both the production environment database and the ConfigLab environment database. 

In this example, we describe how to register a ConfigLab environment.  We'll call the supported environment database "CDXPROD" and the ConfigLab environment database "CDXCLAB". 

A sample file MS SQL Server-compare-source.bat is provided with an example for a Compare Source registration. 

Database security will need to be setup for the utility to register.

Example:

CDXPROD – The database already has a user-id. CDXPRODUSER defined and security has been generated for this user.

CDXCLAB – The database already has a user-id. CDXCLABUSER defined and security has been generated for this user.

For the utility to register database CDXCLAB in database CDXPROD, additional security needs to run.

In database CDXCLAB add the user CDXPRODUSER.

exec sp_adduser ' CDXPRODUSER ',' CDXPRODUSER ', 'public'

Generate security for the user CDXPRODUSER in database CDXCLAB.

Add ODBC connection on the database server, called CDXCLAB with login id sa connecting to CDXCLAB.

Add ODBC connection on the database server, called CDXPROD with login id sa connecting to CDXPROD.

You may specify the following parameters on the command line.  If parameters are not supplied, the registration utility prompts for them:

·         Information about the supporting environment database:

·         Name of SQL Server data source (ODBC) for the database

·         Password for “sa” account

·         Name of the Linked server in supporting environment

·         Name of the database

·         Application schema owner database user

-s CDXCLAB,{sa user password},SF-PDNT-032,CDXCLAB,dbo

·         Information about the supported environment database:

·         Name of SQL Server data source (ODBC) for the database

·         Password for “sa” account

·         Name of the Linked server in supported environment

·         Name of the database

·         Application schema owner database user

-r CDXPROD,{sa user password},SF-PDNT-022,CDXPROD,dbo

·         Action:

·         I-Install (register), U-Reconfigure (reregister), D-Uninstall (deregister)

-a I

·         Environment type of the supporting environment:

·         CMPS-Compare Source, SYNT-Sync Target, CLAB-ConfigLab, ARCH-Archive

-t CLAB

·         Environment reference code:

·         The environment reference used to track the ConfigLab environment.

-e PROD-CONFIGLAB

·         Name prefix:

·         The prefix character used to reference database tables in the ConfigLab environment.  Note that this character must not be C, S, or a name prefix used by an existing supporting environment.

-n A

·         Environment description:

·         Description of the environment reference.

-d Production ConfigLab

·         Apply changes to the databases:

·         Specify this parameter to apply the changes directly instead of writing them to a log file.

-u

·         Owner Flag:

·         Specify the Owner Flag for the application.

-o owner flag value

·         Log file name:

·         Specify the name of the log file if the parameter above was not specified.

-l {log file name}

·         Click Start, point to All Programs, point to Administrative Tools, and then click Component Services.

·         In the Component Services Wizard, expand Component Services, and then double-click Computers.

·         Right-click My Computer, and then click Properties.

·         Click the MS DTC tab, and then click Security Configuration.

·         In the Security Configuration dialog box, click to select the Network DTC Access check box.

·         Under Network DTC Access, click Network Transactions.

·         Make sure that DTC Logon Account is set to NT Authority\NetworkService.

·         Click OK.

·         In the message box, click Yes to continue.

·         In the DTC Console Message dialog box, click OK.

·         In the System Properties dialog box, click OK.

·         Reboot the computer for these changes to take effect.

When you submit the batch job associated with a comparison DB process, the process saves the differences on the database.  We refer to each difference as a "root object".  The topics in this section describe the query used to view these differences.

Contents

Difference Query - Main

Difference Query - Difference Query

Difference Query - Main

Use Admin Menu, Difference Query to view a summary of the differences between the maintenance objects in two environments.

Description of Page

Batch Control, Batch Number, and Batch Business Date is the batch run that compared the maintenance objects.

DB Process is the DB process that defines the maintenance objects that were compared.

Environment Reference is the name of the environment whose data was compared against the data in the current environment.

The grid contains a summary of the comparison results:

·         Add.  If a maintenance object exists in the source environment that is not in the current environment, the maintenance object is categorized as "add".

·         Change.  If a maintenance object exists in the source environment with the same prime key as data in the current environment but with different column values, the maintenance object is categorized as "change".

·         Delete.  If a maintenance objects exists in the current environment that is not in the source environment, the data is categorized as "delete".

The summary is further categorized based on the promotion status of the difference.  Distinct summary lines are shown for each status value - All, Approved, Complete, Error, Initial, Pending, Rejected.

The area above the grid allows you to filter the items that appear in the grid:

·         Use Action to restrict the summary information based on the comparison category (see above for a list of the categories).

·         Use Status to restrict the summary information based on the status of the information.  Refer to The Comparison Process Creates Root Objects for a description of each state.

Don’t forget to click the search button after changing the filters.

The following points describe each column in the grid:

·         Click the adjacent go to button to transfer to the Differences Query tab where the associated root objects can be viewed. 

·         The Maintenance Object column defines the type of maintenance object. 

·         The Description column describes the maintenance object. 

·         The Action column indicates if the maintenance objects are to be Added, Changed or Deleted (note, these actions are only performed if the execute the apply changes background process CL-APPCH).

·         The Status column indicates the status of the maintenance objects.

·         The Total Root Objects column displays the number of maintenance objects with this Action and Status for the Batch Control and Batch Number.

Click Approve All to set the status of all root objects associated with the Batch Control and Batch Number to Approved.

Click Reject All to set the status of all root objects associated with the Batch Control and Batch Number to Rejected.

Difference Query - Difference Query

The Main tab provides a summary of the differences in the maintenance objects.  This tab shows the details of every maintenance object.  Use Admin Menu, Difference Query and navigate to the 2^nd tab to view this information.

Description of Page

Please see the Main tab for a description of the fields in the first section.  The following points describe the remaining fields:

·         Use Maintenance Object to restrict the root objects in the grid to those related to a specific maintenance object.

·         Use Action to restrict the root objects that appear in the grid to those marked with the specified action (Add, Change, Delete).

·         Use Status to restrict the root objects that appear in the grid to those in a specific state.

·         You may also specify unique identifier of the maintenance object to further filter the root objects that appear in the grid.  The prompts for the unique identifier differ depending on the selected Maintenance Object.

Don’t forget to click the search button after changing the filters.

Click Select All to select all root objects currently displayed in the grid.

Click Clear All to unselect all root objects currently displayed in the grid.

The grid displays the root objects that correspond to the criteria specified in the filter.  The following points describe each column:

·         Click the Select checkbox to select or unselect a specific root object.

·         Click the adjacent go to button to transfer to the Root Object - Root Object Tree page where the root object can be viewed. 

·         The Maintenance Object column displays the maintenance object associated with the root object. 

·         The Description column describes the maintenance object associated with the root object. 

·         The PK Info column displays primary key of the maintenance object. 

·         The Action column shows the action associated with the root object.

·         The Status column shows the status associated with the root object.

·         The Primary Root column displays information about the root object's Primary root object.

·         The Other Roots column displays whether the root object is associated with more than one maintenance object.

Click Approve to set the status of selected root objects to Approved.

Click Reject to set the status of selected root objects to Rejected.

When you submit the batch job associated with a comparison DB process, the process saves the differences on the database.  We refer to each difference as a "root object".  The topics in this section describe the transaction that shows the details of a root object.

Contents

Root Object - Main

Root Object - Data Differences

Root Object - Root Object Tree

Root Object - Main

Use Admin Menu, Root Object to view the prime key root objects.

Description of Page

Root Object displays the description of the maintenance object related to the root object including:

·         the value of the Primary key for the record stored on the Primary table associated with the root object's maintenance object,

·         the environment reference used as the source of the Compare DB process,

·         the action assigned to the root object, and

·         the root object's status.

Root Object is the unique identifier of the root object.

Maintenance Object displays the code and description of the root object's maintenance object.

Environment Reference displays the code and description of the environment that was the source environment of the comparison.

Action displays the action assigned to the root object by the comparison process. 

Status displays the status assigned to the root object.  This field is updateable for root objects in approved, rejected, and error status for the most recent batch run related to the Compare DB process.

Batch Control and Batch Number describe the batch run of the Compare DB process used to create the root object.

The first grid at the bottom of the page describes the components that make up the value of the Primary key of the maintenance object:

Field                                        The name of a given field that is part of the Primary key constraint for the record's table.

Description                              The description of the field.

PK Value                                 Displays the PK field's value.

Constraint Id                            Displays the Primary key constraint associated with the record's table.

Sequence                                Displays the order in which the record's field is displayed as part of the Primary key constraint.

The second grid describes the DB process instructions that are associated with the root object (as a difference could be highlighted by multiple DB process instructions). 

DB process                               The name of the Compare DB process used to create the root object.

Process Sequence                   Along with DB Process, this represents the reference to the DB process instruction that specifies the root object's maintenance object.

Primary Root                           The identifier for the root object that groups this root object together with other root objects for processing when changes are applied.

Maintenance Object Description         

                                                This is the description of the maintenance object associated with the Primary Root object.

Level Nbr                                 An internal level number assigned to the root instruction during the compare. 

Root Instruction                       The unique identifier of the root instruction.

Root Object - Data Differences

The Main tab provides a summary of the root object.  This tab shows the details of tables that will be changed if the root object is approved (and the apply changes background process is run).  Use Admin Menu, Root Object and navigate to the Data Differences tab to view this information.

Description of Page

Table and Description              The identity of the table that will be changed

PK Value                                 The row's primary key.

Statement Type                       May be Insert, Update, or Delete.  This defines the type of SQL statement to be used to change this environment.

SQL                                         The actual SQL statement to be used to apply changes to the data in the current environment.

Suppress                                  Check this box if the SQL statement should not be executed when the Apply Changes process runs.

Root Object - Root Object Tree

This tab provides an alternate view of the information shown on the Data Differences tab.  Use Admin Menu, Root Object and navigate to the Root Object Tree tab to view this information.

Description of Page

This page displays a tree that shows the details of tables that will be changed if the root object is approved (and the apply changes background process is run).

After executing the background process to apply changes to data based on root objects created by a Compare DB process, any errors that occur as part of applying the changes are written to the root object exception table.

To view the messages associated with the exception records, schedule the TD-CLERR background process.  This process generates a To Do entry for every record in the root object exception table.

After correcting the cause of the error, navigate to Root Object and change the status to approved or rejected, and run CL-APPCH again.