DDL File Mapping

A DDL file map is used to map the OBID and DBID for an existing table to a DDL file with a different table definition. DDL file maps are used to perform the following tasks:
caladb2
A DDL file map is used to map the OBID and DBID for an existing table to a DDL file with a different table definition. DDL file maps are used to perform the following tasks:
  • Map a dropped table to a recreated table (when recovering a dropped object).
  • Propagate changes from one table to another.
When
CA Log Analyzer™ for DB2 for z/OS
formats column level data, it typically uses the table definitions in the DB2 catalog. The DB2 catalog tracks these definitions by using the DBIDs (database identifiers) and OBIDs (object identifiers) that are assigned to the tables upon creation. The DBIDs and OBIDs are also recorded in the DB2 log. When a table has the same identifiers in the log and the DB2 catalog,
CA Log Analyzer™ for DB2 for z/OS
can find and format the log data for that table.
However, when a table has two different sets of identifiers (one set in the log and another in the DB2 catalog), processing errors occur. This mismatch can happen when a table is dropped and recreated with a new DBID and OBID. The DB2 catalog contains the new identifiers, but the historical log data still references the old identifiers. You can resolve this mismatch by creating a DDL file map that links the dropped table DBID and OBID to a DDL file that defines the recreated table.
DDL maps also help you propagate changes to another table. You can make the same changes to a target table that have already been made to a source table. The propagation process involves defining a DDL map. This map associates the DBID and OBID of the source table with the name of the target table. You then specify the DDL map while generating REDO SQL or Log Apply files to apply to the mirror table.
Contents
File Maps and Versioned Tables
The DB2 online schema feature permits tables with multiple versions, each having a different definition. You can create a file map that points to versioned tables by inserting the following statement in the DDL file:
SET TABLE <creator>.<name> TO VERSION <version number>;
The SET TABLE statement is used only within the file map for
CA Log Analyzer™ for DB2 for z/OS
version control. If you want to submit the DDL file for execution, remove this statement. Leaving the statement in causes the execution to fail.
The SET TABLE statement tells
CA Log Analyzer™ for DB2 for z/OS
that any subsequent CREATE and ALTER TABLE statements in the DDL file represent the specified version. If a CREATE statement has no preceding SET TABLE statement, it is interpreted as version zero. If multiple CREATE statements follow a SET TABLE statement, only the last CREATE statement is used as the version definition. If multiple ALTER statements follow a SET TABLE statement, they are all applied to the version definition.
If you insert a SET TABLE statement, you can use the POPULATE line command to insert the version numbers into the DDL Maps portion of the DDL Map Editor panel.
File Maps and Clone Tables
The clone table function creates pairs of tables that reside in parallel data sets for the same tablespace. A clone shares identifiers and column structures with its base table. However, the clone has a different table name and a different instance number (1 or 2). The EXCHANGE command switches the instance numbers of the base table and its associated clone table.
The DDL file mapping facility supports the following clone statements:
ALTER TABLE <creator>.<name> ADD CLONE <creator>.<name>
ALTER TABLE <creator>.<name> DROP CLONE
EXCHANGE DATA BETWEEN TABLE <creator>.<name> AND <creator>.<name>
The previous DDL statements control the instance numbers of the tables. A standard CREATE TABLE statement creates a base table that is listed as instance 1. An ALTER TABLE ADD CLONE statement clones the base table and lists the clone as instance 2. An EXCHANGE statement switches the instance number of the two tables. In other words, the base table becomes instance 2, and the clone table becomes instance 1.
You can use the POPULATE line command to insert the instance numbers into the DDL Maps portion of the DDL Map Editor panel. You can also manually enter the instance numbers. For a base table, match the instance value to the instance column in SYSIBM.SYSTABLESPACE for the space the table resides in. For a clone table, match the instance value to the alternate value of the instance column. In other words, if SYSIBM.SYSTABLESPACE.INSTANCE = 1, enter 2 for the clone instance, and if SYSIBM.SYSTABLESPACE.INSTANCE = 2, enter 1 for the clone instance.
File Maps and Materialized Query Tables
A materialized query table (MQT) uses a query to summarize data from other tables. DB2 optimizes queries by using the data in the MQT instead of reexecuting the queries to gather the data again.
You cannot use DDL file maps with MQTs because the tables contain incomplete table and column definitions. If you build a DDL file map from a file containing a CREATE MQT statement, the file map is ignored during batch execution.
File Maps and Tables With LOB Columns
You can use DDL file maps with tables that contain LOB columns, with certain restrictions. DDL file mapping supports only LOBs that are fully inline. If a DDL file map points to a table with LOB columns, only the columns that are fully inline are processed. LOB columns that are partially inline are skipped, and the row data is dumped.
Also, when mapping to a table with LOB columns, explicitly define a ROWID column in the DDL file. If you do not,
CA Log Analyzer™ for DB2 for z/OS
cannot format the LOB data correctly.
See the following example:
CREATE TABLE USER01.LOBTABLE
          (CLOB CLOB (1M)
          , DB2_GENERATED_ROWID_FOR_LOBS ROWID NOT NULL
          )
     IN DATABASE TESTDB
;
DDL File Requirements
A DDL file contains table definitions that are provided in the form of CREATE TABLE or ALTER TABLE statements. The DDL file that you include in your map must meet the following criteria:
  • The DDL file is in a format suitable for execution through SPUFI. For example, it must have an LRECL of 80, and the SQL statements must appear in columns 1 through 72 and must terminate with a semicolon.
  • The table creator is explicitly specified on the CREATE TABLE statement. If the creator is not specified, it defaults to the AUTHID that is executing the job. If the AUTHID and the table creator are not the same, then the table can be incorrectly identified or cannot be found.