Managing individual catalog objects

The DDL that is used to create tables, indexes, views, and stored procedures should be stored in a version control system. As with source code, version control systems can be used to manage different versions of a catalog object or collection of objects. For example, you might have test, user acceptance, and production versions of catalog objects. This approach might be required because the underlying data source information might have different naming conventions in each environment.

The DDL source for each object can be retrieved from the metadata catalog by using the Generate DDL options in the CDA Data Source Explorer, or by using the metadata utility GENERATE DDL command. GENERATE DDL statement for the metadata utility documents the options for running the GENERATE DDL command.

When using the CDA Data Source Explorer you must first connect to the data source that provides access to the metadata catalog objects. The Data Source Explorer provides the following options to generate DDL:

Schema level generate DDL
This option allows you to generate DDL for all tables, indexes, views, and stored procedures that are owned by one or more schemas at one time. One file is created with all of the objects that are owned by the selected schemas. This feature can be useful when a schema represents a specific application. All objects for the application can then be versioned as one file and migrated from one environment to another by using one DDL file.

Follow these steps:

  1. In the Data Source Explorer, expand the Schemas folder.
  2. Select one or more schema names.
  3. Right-click one of the selected schemas and select Generate DDL.
  4. Follow the pages of the Generate DDL wizard to choose what object types to generate.
  5. After reviewing your settings, click Finish.
Object level generate DDL
This option allows you to generate DDL for one or more objects of any type at one time. One file is created with all of the objects that are selected. For example, it can be useful to select a table and all views that reference the table. This method ensures that any time that the table is dropped and recreated its views are also recreated.

Follow these steps:

  1. In the Data Source Explorer, expand the Schemas folder.
  2. Expand each schema that has objects for which you want to generate the DDL.
  3. Under each schema, you can expand the Tables, Views and Stored Procedures folders to get a list of the objects for each type.
  4. Select each object that you want to generate.
  5. Right-click one of the selected objects and select Generate DDL.
  6. Follow the pages of the Generate DDL wizard to choose what object types to generate.
  7. After reviewing your settings, click Finish.

View considerations

Having the DDL statements for view objects is very important. When the tables that are referenced by the views need to be updated, the tables must be dropped and recreated to make the changes. When a table is dropped, all views that reference the table are also dropped. After a referenced table is dropped and recreated, all associated views need to be recreated. Having the DDL for the view available is critical to recreate the views that were deleted by table changes. Generating the DDL for tables and their associated views at the same time is a useful method to ensure that all related tables and views are managed together.

You can execute the following query to get a list of all tables that have one or more views that reference them:

SELECT BCREATOR AS "TABLE_CREATOR",
       BNAME AS "TABLE_NAME",
       DCREATOR AS "VIEW_CREATOR",
       DNAME AS "VIEW_NAME"
    FROM SYSIBM.SYSVIEWDEP
    ORDER BY 1,2;

The query can be run from CDA or using the USERSAMP(CACCLNT) sample job.

Example output:

Example output from the USERSAMP(CACCLNT) sample job

In this example output:

  • The USER1.EMPLVSAM table is referenced by two separate views, USER1.V1_EMPLVSAM and USER1.V2_EMPLVSAM.
  • The USER1.IMSDB1 table is reference by two separate views, USER1.V_IMSDB1 and USER1.V_IMSDB_1_AND_2.
  • The USER1.IMSDB2 table is referenced by one view, USER1.V_IMSDB_1_AND_2.
  • The USER1.V_IMSDB_1_AND_2 view references both the USER1.IMSDB1 and USER1.IMSDB2 tables.