Creating shadow tables

Creating a shadow table involves several steps to ensure that the table is in a normal state and has a primary key defined. Shadow tables are materialized query tables (MQTs) that are column-organized tables and maintained by replication.

To create a shadow table:

1. Ensure that the source table has a primary key or unique constraint defined. The following example shows the SQL statements that were used to create the TRADE source table and its primary key:

   CREATE TABLE DTW.TRADE  (
             "T_ID" BIGINT NOT NULL ,
             "T_DTS" TIMESTAMP NOT NULL ,
             "T_ST_ID" CHAR(4 OCTETS) NOT NULL ,
             "T_TT_ID" CHAR(3 OCTETS) NOT NULL ,
             "T_IS_CASH" SMALLINT NOT NULL ,
             "T_S_SYMB" VARCHAR(15 OCTETS) NOT NULL ,
             "T_QTY" INTEGER NOT NULL ,
             "T_BID_PRICE" DECIMAL(8,2) NOT NULL ,
             "T_CA_ID" BIGINT NOT NULL ,
             "T_EXEC_NAME" CHAR(49 OCTETS) NOT NULL ,
             "T_TRADE_PRICE" DECIMAL(8,2) ,
             "T_CHRG" DECIMAL(10,2) ,
             "T_COMM" DECIMAL(10,2) NOT NULL ,
             "T_TAX" DECIMAL(10,2) NOT NULL ,
             "T_LIFO" SMALLINT NOT NULL )
            ORGANIZE BY ROW;
   
   ALTER TABLE DTW.TRADE
        ADD CONSTRAINT "T_PK" PRIMARY KEY ("T_ID");

2. Create the shadow table by issuing the CREATE TABLE statement with the MAINTAINED BY REPLICATION clause. This clause identifies this table as a shadow table. The primary key of the source table must be included in the select list of the CREATE TABLE statement for the shadow table. For example:

   CREATE TABLE DTW.TRADE_SHADOW AS 
     (SELECT * from DTW.TRADE) DATA INITIALLY DEFERRED REFRESH DEFERRED
     ENABLE QUERY OPTIMIZATION MAINTAINED BY REPLICATION ORGANIZE BY COLUMN;
   

   After you issue the CREATE TABLE statement, the new shadow table is put in set integrity-pending state.
3. Bring the shadow table out of set integrity-pending state into normal state by issuing the SET INTEGRITY statement with the IMMEDIATE UNCHECKED clause. For example:

   SET INTEGRITY FOR DTW.TRADE_SHADOW ALL IMMEDIATE UNCHECKED;

4. Define a primary key on the shadow table by issuing the ALTER TABLE statement. For example:

   ALTER TABLE DTW.TRADE_SHADOW
       ADD CONSTRAINT T_SHADOW_IX PRIMARY KEY (T_ID);

   The primary key on the shadow table is required to provide a one-to-one mapping for each row in the source table to the corresponding row in the shadow table. The primary key on the shadow table must match the enforced primary key constraint or a unique constraint on the source table in both column list and key sequence. This primary key also facilitates maintenance of the shadow table.
5. To verify that the shadow table was successfully created, issue the following query to list shadow tables that exists in the database.

   SELECT
       SUBSTR(TABNAME, 1, 24) AS TABNAME,
       SUBSTR(TABSCHEMA, 1, 24) AS TABSCHEMA
     FROM SYSCAT.TABLES
     WHERE SUBSTR(PROPERTY,23,1) = 'Y';