Reusing table mappings using view overrides

Many applications deploy multiple versions, or copies, of a file or database that uses the same COBOL copybooks, or PL/I includes to describe its contents. Often these are based on geographical location or for older applications, on file or database size limitations.

Note: You also see this as an application moves from initial development through its testing phases and then into production. When migrating between different development environments the recommendation is that each environment should have its own Classic CDC deployment that consists of a separate source server, metadata catalogs, and subscription definitions. For these environments it is not uncommon that some mapping changes are required because of variations in application maintenance levels where each environment references a file or database with a slightly different COBOL copybook or PL/I include.

A table mapping references a specific database or file, so in these kinds of scenarios multiple table mappings must be created for each unique database or file that needs to be referenced. Often, the data contains redefines that requires a unique mapping for each variant and a separate view that specifies the qualification logic for that mapping.

The relevant tables and views are then referenced in one or more CDC Replication subscriptions.

Customers typically pick a representative development deployment and then go through the process of mapping the data, validating the mapping, and creating the subscriptions that are necessary to meet the business requirement to capture and replication changes for the files or databases that exist for that deployment.

This initial mapping is done using Classic Data Architect and results in the creation of one or more tables and views that have been altered for data capture as well as any necessary grant statements that allow the tables or views to be referenced by a subscription. During this phase of a project, typically Management Console is used to define the subscriptions although this could also be done manually using CHCCLP scripting.

In cases where there are multiple versions of a file or database whose changes need to be captured in the same environment, most customers prefer to use some method of cloning the original table and view mappings to reference the “next” version of a file or database as opposed to using the CDA mapping wizard to create a new set of tables and views.

The cloning process requires creating new versions of each table with a different name that references a different file or database. When views exist, new versions of these are created under a different name and the table reference is updated to reference the new table name. Finally, the new table/views definitions need to be referenced in new or existing subscriptions.

The cloning process might be manual, automated, or some combination of the two. When an existing mapping needs to be changed when the cloning process is complete, a decision needs to be made whether to simply re-clone or attempt to merge the changes into each cloned version.

You can also use an existing table mapping that is referenced by a view that includes information that overrides the information in the table that is referenced by the view. This allows the contents of a different file or database to be read or captured using the column descriptions of the table that is referenced by the view.

This is done by providing a COMMENT ON statement for the view that identifies the file or database whose changes are to be captured or contents are to be read for refresh or data sampling purposes.

For a VSAM file that is accessed directly for refresh or mapping validation purposes, the syntax is:

COMMENT ON TABLE view-name IS ‘DSN: data-set-name’

Where the literal value starts with the DSN: keyword with one space between the keyword and the data set name. The data set name is the 1-44-character name of the VSAM file base cluster whose changes are to be captured and whose contents are to be accessed when a refresh or data sampling is performed.

For a VSAM file, this is accessed via CICS® for refresh or mapping validation purposes. The syntax is:

COMMENT ON TABLE view-name IS ‘DSN: data-set-name FCT: FCT-entry-name’

Where the literal value starts with the DSN: keyword with one space between the keyword and the data set name. The data set name is the 1-44-character name of the VSAM file base cluster whose changes are to be captured.

There is one space after the data set name and the FCT: keyword and one space before the FCT entry name. The FCT entry name identifies the 1-8-character name of the CICS file control entry that is to be used for refresh or sampling purposes when the file is under CICS control.

For an IMS database the syntax is:

COMMENT ON TABLE view-name IS ‘DBD: DBD-name PSB: PSB-name’

Where the literal value starts with the DBD: keyword with one space between the keyword and the DBD name. The DBD name identifies the 1-8-character DBD name of the IMS database for which changes will be captured for the segments that are referenced in the table mapping.

There is one space after the DBD name and the PSB: keyword and one space before the PSB name. The PSB name identifies the 1-8-character name of the PSB that is scheduled to access the DBD for refresh or sampling purposes.

You do not have to manually issue the COMMENT ON statements. In Classic Data Architect, if you click the view and navigate to the Properties view, click the Documentation tab and the entry field that is displayed can be used to provide the information that is described above.

Important: When using this approach, always remember when you are generating the view DDL to select the COMMENT ON statements checkbox in the Options page.

The view might or might not contain a WHERE clause. If the file or segment that is being referenced contains redefines and the mapping references redefined data, then the WHERE clause contains the logic to select the mapping based on control variables in the data. When no selection logic is required, the view does not include a WHERE clause.

Using this approach, you create an initial set of mappings that reference one instance of the files or IMS databases that need to be replicated. You go through the normal process of defining the required tables and views, altering these for data capture, and setting up the necessary subscriptions.

Because the build-out process requires a view for each mapping that needs to be replicated, the recommendation during this initial design and development process is to define views for all tables. While these initial mappings reference a representative file or database, these views should be updated to include the override information (data set name for VSAM or DBD and PSB name for IMS). This approach provides consistency and validates that the basic override syntax and information provided is valid.

During this initial development effort, you want to verify that each mapping is complete and correct and verify that end-to-end replication is working properly.

When this process is complete, you can clone the views that were created to reference the other copies of the file or database – as many as are required. For each view, you give it a new name and then update the COMMENT ON override information to reference the desired file or database. When these have been defined in the metadata catalogs, you can complete the replication setup by referencing these new views in existing or new subscriptions.

One simple way to manage the build-out process is to use a unique schema name for all the views. If this approach is taken, then in CDA, you can select the schema name, right click, and select Generate DDL.

Make sure that all of the checkboxes on the Options window are selected and then advance to the Objects window, where you only want to select Views and then select Next.

Depending on the number of views, it might take awhile to generate all the CREATE, ATLER, COMMENT ON, and GRANT statements for all of the views that exist for that schema name. When the Save and Run DDL window is displayed, you have several choices on how you want to proceed. These include:

  • Saving the generated DDL and externally updating the DDL to provide new view names and update the contents of the COMMENT ON statements.
  • Editing the DDL in CDA and manually updating the view names and COMMENT ON statements, or using the CDA find and replace functions.

When you finish updating the generated DDL you will want to run it to create the new set of views. You continue this process until you complete the build-out process.

Alternately, you can take the same approach by using the metadata utility with the following control card input:

GENERATE DDL FOR VIEW LIKE Schema-Name.% WITH ALL;

Remember that if you need to update tables that are referenced by a view, this requires dropping the table, which results in all views that reference the table being deleted. To prevent loss of these override views and the associated COMMENT ON information, remember to export and save all these definitions when complete so that they are not lost. This can be done in CDA or using the metadata utility.

Using CDA or the metadata utility to generate DDL for views that include COMMENT ON statements produce DLL where the COMMENT ON statement is executed after the CREATE VIEW statement and before any ALTER or GRANT statements. The COMMENT ON statement executes successfully provided the view exists in the metadata catalog.

The COMMENT ON statement updates the REMARKS column in the SYSIBM.SYSTABLES row for the view. When there is REMARKS data for a view its contents are parsed to determine if override information has been provided. For a view that references a VSAM table, if the REMARKS start with the keyword DSN: or for an IMS table DBD:. The assumption is that the REMARKS column contains override information and is parsed.

If a syntax error is detected, an error is reported and the operation fails. This can affect the following operations:

  • Execution of an ALTER VIEW statement
  • An attempt to sample data for the VIEW from CDA
  • Adding the view to a subscription
  • Loading a view for a subscription during source server start-up
  • When a refresh operation is performed for the view
  • When replication is started for a subscription that contains the view

For the first two kinds of failures, you can fix the information in the COMMENT ON statement, execute the statement, and then retry the operation.

When no syntax errors are found, the information from the COMMENT ON statement overrides the information in the table that is referenced by the view. This override is only done to an in-memory copy of the table, which allows the same table to be referenced by multiple views.

If errors are encountered while you are attempting to access or validate the information for the table, the overridden names are displayed in the error messages that report the problem.

When a subscription contains a view with one of these kinds of COMMENT ON specifications, one of the following event messages is issued when the subscription is loaded in memory or when a view is added to a subscription:

  • CECD0816I IMS DBD DBD-name is using table mapping base-table-name for view view-name for subscription subscription-name. Access is performed using PSB PSB-name.
  • CECD1033I VSAM file data-set-name is using table mapping base-table-name for view view-name for subscription subscription-name.
  • CECD1034I VSAM file data-set-name is using table mapping base-table-name for view view-name for subscription subscription-name. Access is performed using FCT entry FCT-name.