>>obey TEST001(create_rewrite_publish_table);
>>----------------------------------------------------
>>---------- create rewrite publish table ------------
>>-- In the future this should be part of init sql  --
>>----------------------------------------------------
>>
>>log;
>>create schema MANAGEABILITY.MV_REWRITE;

--- SQL operation complete.
>>
>>CREATE TABLE MANAGEABILITY.MV_REWRITE.REWRITE_PUBLISH   (
+>    OPERATION_TIMESTAMP   LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
+>  , REDEF_TIME            LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
+>  , REFRESH_AT_TIME       LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
+>  , OBJECT_UID            LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
+>  , CATALOG_UID           LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
+>  , OBJECT_NAME        CHAR(499) CHARACTER SET UCS2 COLLATE DEFAULT 
+>NO DEFAULT NOT NULL NOT DROPPABLE
+>  , OBJECT_NEW_NAME    CHAR(499) CHARACTER SET UCS2 COLLATE DEFAULT 
+>DEFAULT NULL
+>  , DESCRIPTOR_INDEX      INT NO DEFAULT 
+>  , OPERATION_TYPE      CHAR(2) CHARACTER SET ISO88591 COLLATE DEFAULT 
+>NO DEFAULT NOT NULL NOT DROPPABLE
+>  , IGNORE_CHANGES_USED	CHAR(2) CHARACTER SET ISO88591 COLLATE DEFAULT 
+>DEFAULT NULL    
+>  ,PRIMARY KEY (OPERATION_TIMESTAMP, REDEF_TIME)) NO PARTITION;

--- SQL operation complete.
>>
>>obey TEST001(set_up);
>>----------------------------------------------------
>>-------------------- set up section ----------------
>>-- create and populate all tables needed by the test
>>----------------------------------------------------
>>
>>create catalog QRP_CAT;

--- SQL operation complete.
>>create schema QRP_CAT.SCH;

--- SQL operation complete.
>>set schema QRP_CAT.SCH;

--- SQL operation complete.
>>set pattern $$MVSchemaName$$ SCH;
>>
>>create table t1 (a int not null primary key,
+>                 b int,
+>                 c char(10)) no partition;

--- SQL operation complete.
>>
>>create table t2 (d int not null primary key,
+>                 e char (20),
+>                 f int) no partition;

--- SQL operation complete.
>>
>>create table t3 (g int not null primary key,
+>                 h int) no partition;

--- SQL operation complete.
>>
>>create view t1_v as select * from t1;

--- SQL operation complete.
>>
>>insert into t1 values (1,1,'a'),(2,2,'b'),(3,2,'d');

--- 3 row(s) inserted.
>>insert into t2 values (1,'a',1),(2,'b',2),(3,'c',3);

--- 3 row(s) inserted.
>>insert into t3 values (1,1),(2,2),(3,3);

--- 3 row(s) inserted.
>>
>>Update statistics for table t1 on every column;

--- SQL operation complete.
>>Update statistics for table t2 on every column;

--- SQL operation complete.
>>Update statistics for table t3 on every column;

--- SQL operation complete.
>>
>>set param ?mvDescSubId  -2;
>>
>>-- query the TEXT SMD table for the descriptor of an MV
>>prepare getMVDescFromTEXT  from
+>  SELECT sequence_num, substring(TEXT, 1, 2000) as mv_descriptor_text
+>         from  HP_DEFINITION_SCHEMA.TEXT t,
+>               HP_DEFINITION_SCHEMA.objects o
+>         where o.object_name = ?mvName and
+>               o.OBJECT_TYPE = 'MV' and
+>               o.object_uid = t.object_uid and
+>               t.object_sub_id = ?mvDescSubId
+>         order by sequence_num;

--- SQL command prepared.
>>
>>-- retrieve all rows from the REWRITE_PUBLISH table
>>prepare getMVRP  from
+>  SELECT substring(OBJECT_NAME,1,18) object_name,
+>         substring(OBJECT_NEW_NAME,1,18) object_new_name,
+>         DESCRIPTOR_INDEX D_I, OPERATION_TYPE O_T, IGNORE_CHANGES_USED I_C,
+>         case when REFRESH_AT_TIME = 0 then '0' ELSE 'NONZERO' end as refresh_at
+>  FROM manageability.mv_rewrite.rewrite_publish;

--- SQL command prepared.
>>
>>-- retrieve all rows related to a specific MV identified by its
>>-- full external name from the REWRITE_PUBLISH table
>>prepare getMVRP_PERMV  from
+>  SELECT substring(OBJECT_NAME,1,18) object_name,
+>         substring(OBJECT_NEW_NAME,1,18) object_new_name,
+>         DESCRIPTOR_INDEX D_I, OPERATION_TYPE O_T, IGNORE_CHANGES_USED I_C,
+>         case when REFRESH_AT_TIME = 0 then '0' ELSE 'NONZERO' end as refresh_at
+>  FROM   manageability.mv_rewrite.rewrite_publish
+>  WHERE  object_name = ?fullMVName;

--- SQL command prepared.
>>
>>
>>-- enable query rewrite
>>control query default MVQR_REWRITE_LEVEL '4';

--- SQL operation complete.
>>control query default MVQR_PUBLISH_TO  'public';

--- SQL operation complete.
>>
>>
>>execute getMVRP;

--- 0 row(s) selected.
>>
>>obey TEST001(rewrite_publish_ddl);
>>----------------------------------------------------
>>---------- check rewrite publish schema ------------
>>----------------------------------------------------
>>
>>showddl manageability.mv_rewrite.rewrite_publish;

CREATE TABLE MANAGEABILITY.MV_REWRITE.REWRITE_PUBLISH
  (
    OPERATION_TIMESTAMP              LARGEINT NO DEFAULT
      -- NOT NULL NOT DROPPABLE
  , REDEF_TIME                       LARGEINT NO DEFAULT
      -- NOT NULL NOT DROPPABLE
  , REFRESH_AT_TIME                  LARGEINT NO DEFAULT
      -- NOT NULL NOT DROPPABLE
  , OBJECT_UID                       LARGEINT NO DEFAULT
      -- NOT NULL NOT DROPPABLE
  , CATALOG_UID                      LARGEINT NO DEFAULT
      -- NOT NULL NOT DROPPABLE
  , OBJECT_NAME                      CHAR(499) CHARACTER SET UCS2 COLLATE
      DEFAULT NO DEFAULT -- NOT NULL NOT DROPPABLE
  , OBJECT_NEW_NAME                  CHAR(499) CHARACTER SET UCS2 COLLATE
      DEFAULT DEFAULT NULL
  , DESCRIPTOR_INDEX                 INT NO DEFAULT
  , OPERATION_TYPE                   CHAR(2) CHARACTER SET ISO88591 COLLATE
      DEFAULT NO DEFAULT -- NOT NULL NOT DROPPABLE
  , IGNORE_CHANGES_USED              CHAR(2) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL
  , CONSTRAINT MANAGEABILITY.MV_REWRITE.REWRITE_PUBLISH_858423112_7831 PRIMARY
      KEY (OPERATION_TIMESTAMP ASC, REDEF_TIME ASC) NOT DROPPABLE
  , CONSTRAINT MANAGEABILITY.MV_REWRITE.REWRITE_PUBLISH_658423112_7831 CHECK
      (MANAGEABILITY.MV_REWRITE.REWRITE_PUBLISH.OPERATION_TIMESTAMP IS NOT NULL
      AND MANAGEABILITY.MV_REWRITE.REWRITE_PUBLISH.REDEF_TIME IS NOT NULL AND
      MANAGEABILITY.MV_REWRITE.REWRITE_PUBLISH.REFRESH_AT_TIME IS NOT NULL AND
      MANAGEABILITY.MV_REWRITE.REWRITE_PUBLISH.OBJECT_UID IS NOT NULL AND
      MANAGEABILITY.MV_REWRITE.REWRITE_PUBLISH.CATALOG_UID IS NOT NULL AND
      MANAGEABILITY.MV_REWRITE.REWRITE_PUBLISH.OBJECT_NAME IS NOT NULL AND
      MANAGEABILITY.MV_REWRITE.REWRITE_PUBLISH.OPERATION_TYPE IS NOT NULL) NOT
      DROPPABLE
  )
  LOCATION \NSK.$DATA1.ZSDF87JP.MS8JPT00
  NAME NSK_DATA1_ZSDF87JP_MS8JPT00
  STORE BY (OPERATION_TIMESTAMP ASC, REDEF_TIME ASC)
  ;

--- SQL operation complete.
>>
>>obey TEST001(create_mv);
>>----------------------------------------------------
>>----------------- create mv section ----------------
>>-- test the creation of the different mv refresh
>>-- types: ON REQUEST, ON STATEMENT and RECOMPUTE
>>--        User Maintained MVs
>>---- The test will:
>>------- make sure that a row is inserted into the
>>--        REWRITE_PUBLISH PUBLISH table after the
>>--        creation of an MV if the MV is initialize
>>--        on refresh and 2 row if initialize on
>>--        create
>>--      make sure that a row is inserted into the 
>>--        TEXT SMD metadata table with the MV 
>>--        descriptor TEXT
>>--      all the created mvs are created with the
>>--      qeuery rewrite attribute enabled
>>----------------------------------------------------
>>
>>-- ON REQUEST INITIALIZE ON REREFSH MV
>>set param ?mvName 'MV1';
>>set param ?mvSchemaName $$MVSchemaName$$;
>>create mv mv1 refresh on request initialize on refresh 
+>  enable query rewrite
+>  as select b, c from t1,t2
+>  where  a < 20 and b = f;

*** WARNING[12112] A secondary index QRP_CAT.SCH.MV1_796923112_7831 was created for the materialized view.

--- SQL operation completed with warnings.
>>
>>-- check contents of REWRITE_PUBLISH table
>>execute getMVRP;

OBJECT_NAME                           OBJECT_NEW_NAME                       D_I          O_T  I_C  REFRESH_AT
------------------------------------  ------------------------------------  -----------  ---  ---  ----------

QRP_CAT.SCH.MV1                       ?                                              -2  CT   0    0         

--- 1 row(s) selected.
>>
>>-- check the mv descriptor text in TEXT SMD table
>>execute getMVDescFromTEXT;

SEQUENCE_NUM  MV_DESCRIPTOR_TEXT
------------  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

           0  <MV>
  <Version>
    1.0
  </Version>
  <Table>
    QRP_CAT.SCH.MV1
  </Table>
  <Misc isIncremental='1'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T2' TS='212222176832203190' isKeyCovered='1' numCols='3' rangeBits='00000008'>
          QRP_CAT.SCH.T1
        </Table>
        <Table id='T1' TS='212222176834226175' isKeyCovered='1' numCols='3'>
          QRP_CAT.SCH.T2
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J116'>
          <Column ref='C96'>
          </Column>
          <Column id='C104' tableId='T1' colIndex='2'>
            QRP_CAT.SCH.T2.F
          </Column>
        </JoinPred>
      </JoinPredList>
      <RangePredList>
        <Range id='R137' sqlType='INTEGER SIGNED NO NULLS'>
          <Column ref='C95'>
          </Column>
          <OpLE isNormalized='1'>
            <NumericVal scale='0'>
              19
            </NumericVal>
          </OpLE>
        </Range>
      </RangePredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O113' name='SYS_A1'>
        <Column id='C95' tableId='T2' colIndex='0' isNullable='0'>
          QRP_CAT.SCH.T1.A
        </Column>
      </Output>
      <Output id='O116' name='B'>
        <Column id='C96' tableId='T2' colIndex='1'>
          QRP_CAT.SCH.T1.B
        </Column>
      </Output>
      <Output id='O119' name='C'>
        <Column id='C97' tableId='T2' colIndex='2'>
          QRP_CAT.SCH.T1.C
        </Column>
      </Output>
      <Output id='O122' name='SYS_D2'>
        <Column id='C102' tableId='T1' colIndex='0' isNullable='0'>
          QRP_CAT.SCH.T2.D
        </Column>
      </Output>
    </OutputList>
  </JBB>
</MV>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          

--- 1 row(s) selected.
>>
>>-- ON REQUEST INITIALIZE ON CREATE MV
>>set param ?mvName 'MV2';
>>set param ?mvSchemaName $$MVSchemaName$$;
>>create mv mv2 refresh on request initialize on create 
+>  enable query rewrite
+>  as select b, count (*)  cnt from t1,t2
+>  where  a < 20 and c = e
+>  group by b;

*** WARNING[12112] A secondary index QRP_CAT.SCH.MV2_322133112_7831 was created for the materialized view.

--- SQL operation completed with warnings.
>>
>>-- check contents of REWRITE_PUBLISH table
>>execute getMVRP;

OBJECT_NAME                           OBJECT_NEW_NAME                       D_I          O_T  I_C  REFRESH_AT
------------------------------------  ------------------------------------  -----------  ---  ---  ----------

QRP_CAT.SCH.MV1                       ?                                              -2  CT   0    0         
QRP_CAT.SCH.MV2                       ?                                              -2  CR   0    NONZERO   

--- 2 row(s) selected.
>>
>>-- check the mv descriptor text in TEXT SMD table
>>execute getMVDescFromTEXT;

SEQUENCE_NUM  MV_DESCRIPTOR_TEXT
------------  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

           0  <MV>
  <Version>
    1.0
  </Version>
  <Table>
    QRP_CAT.SCH.MV2
  </Table>
  <Misc isIncremental='1'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T2' TS='212222176840249210' numCols='3' rangeBits='00000008'>
          QRP_CAT.SCH.T1
        </Table>
        <Table id='T1' TS='212222176840249210' numCols='3'>
          QRP_CAT.SCH.T2
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J102'>
          <Column id='C78' tableId='T2' colIndex='2'>
            QRP_CAT.SCH.T1.C
          </Column>
          <Column id='C84' tableId='T1' colIndex='1'>
            QRP_CAT.SCH.T2.E
          </Column>
        </JoinPred>
      </JoinPredList>
      <RangePredList>
        <Range id='R120' sqlType='INTEGER SIGNED NO NULLS'>
          <Column id='C76' tableId='T2' colIndex='0' isNullable='0'>
            QRP_CAT.SCH.T1.A
          </Column>
          <OpLE isNormalized='1'>
            <NumericVal scale='0'>
              19
            </NumericVal>
          </OpLE>
        </Range>
      </RangePredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O94' name='CNT'>
        <Expr id='X94'>
          <Function id='S94' op='count' aggregateFunc='1'>
            <NumericVal id='S93' scale='0'>
              1 
            </NumericVal>
          </Function>
        </Expr>
      </Output>
      <Output id='O99' name='B'>
        <Column ref='C77'>
        </Column>
      </Output>
    </OutputList>
    <GroupBy id='G3'>
      <Primary>
        <Column id='C77' tableId='T2' colIndex='1'>
          QRP_CAT.SCH.T1.B
        </Column>
      </Primary>
    </GroupBy>
  </JBB>
</MV>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               

--- 1 row(s) selected.
>>
>>
>>-- ON STATEMENT INITIALIZE ON REFRESH MV
>>set param ?mvName 'MV3';
>>set param ?mvSchemaName $$MVSchemaName$$;
>>create mv mv3 refresh on statement initialize on refresh 
+>  enable query rewrite
+>  as select b from t1,t2
+>  where  a < 20 and b = f;

*** WARNING[12112] A secondary index QRP_CAT.SCH.MV3_875133112_7831 was created for the materialized view.

--- SQL operation completed with warnings.
>>
>>-- check contents of REWRITE_PUBLISH table
>>execute getMVRP;

OBJECT_NAME                           OBJECT_NEW_NAME                       D_I          O_T  I_C  REFRESH_AT
------------------------------------  ------------------------------------  -----------  ---  ---  ----------

QRP_CAT.SCH.MV1                       ?                                              -2  CT   0    0         
QRP_CAT.SCH.MV2                       ?                                              -2  CR   0    NONZERO   
QRP_CAT.SCH.MV3                       ?                                              -2  CT   0    0         

--- 3 row(s) selected.
>>
>>-- check the mv descriptor text in TEXT SMD table
>>execute getMVDescFromTEXT;

SEQUENCE_NUM  MV_DESCRIPTOR_TEXT
------------  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

           0  <MV>
  <Version>
    1.0
  </Version>
  <Table>
    QRP_CAT.SCH.MV3
  </Table>
  <Misc isIncremental='1' isImmediate='1'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T2' TS='212222176840249210' isKeyCovered='1' numCols='3' rangeBits='00000008'>
          QRP_CAT.SCH.T1
        </Table>
        <Table id='T1' TS='212222176844026469' isKeyCovered='1' numCols='3'>
          QRP_CAT.SCH.T2
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J113'>
          <Column ref='C93'>
          </Column>
          <Column id='C101' tableId='T1' colIndex='2'>
            QRP_CAT.SCH.T2.F
          </Column>
        </JoinPred>
      </JoinPredList>
      <RangePredList>
        <Range id='R134' sqlType='INTEGER SIGNED NO NULLS'>
          <Column ref='C92'>
          </Column>
          <OpLE isNormalized='1'>
            <NumericVal scale='0'>
              19
            </NumericVal>
          </OpLE>
        </Range>
      </RangePredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O110' name='SYS_A1'>
        <Column id='C92' tableId='T2' colIndex='0' isNullable='0'>
          QRP_CAT.SCH.T1.A
        </Column>
      </Output>
      <Output id='O113' name='B'>
        <Column id='C93' tableId='T2' colIndex='1'>
          QRP_CAT.SCH.T1.B
        </Column>
      </Output>
      <Output id='O119' name='SYS_D2'>
        <Column id='C99' tableId='T1' colIndex='0' isNullable='0'>
          QRP_CAT.SCH.T2.D
        </Column>
      </Output>
    </OutputList>
  </JBB>
</MV>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              

--- 1 row(s) selected.
>>
>>
>>-- ON STATEMENT INITIALIZE ON CREATE MV
>>set param ?mvName 'MV4';
>>set param ?mvSchemaName $$MVSchemaName$$;
>>create mv mv4 refresh on statement initialize on create
+>  enable query rewrite
+>  as select b from t1,t2
+>  where  a < 20 and b = f;

*** WARNING[12112] A secondary index QRP_CAT.SCH.MV4_228133112_7831 was created for the materialized view.

--- SQL operation completed with warnings.
>>
>>-- check the mv descriptor text in TEXT SMD table
>>execute getMVRP;

OBJECT_NAME                           OBJECT_NEW_NAME                       D_I          O_T  I_C  REFRESH_AT
------------------------------------  ------------------------------------  -----------  ---  ---  ----------

QRP_CAT.SCH.MV1                       ?                                              -2  CT   0    0         
QRP_CAT.SCH.MV2                       ?                                              -2  CR   0    NONZERO   
QRP_CAT.SCH.MV3                       ?                                              -2  CT   0    0         
QRP_CAT.SCH.MV4                       ?                                              -2  CR   0    NONZERO   

--- 4 row(s) selected.
>>
>>-- check contents of REWRITE_PUBLISH table
>>execute getMVDescFromTEXT;

SEQUENCE_NUM  MV_DESCRIPTOR_TEXT
------------  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

           0  <MV>
  <Version>
    1.0
  </Version>
  <Table>
    QRP_CAT.SCH.MV4
  </Table>
  <Misc isIncremental='1' isImmediate='1'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T2' TS='212222176840249210' isKeyCovered='1' numCols='3' rangeBits='00000008'>
          QRP_CAT.SCH.T1
        </Table>
        <Table id='T1' TS='212222176844026469' isKeyCovered='1' numCols='3'>
          QRP_CAT.SCH.T2
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J113'>
          <Column ref='C93'>
          </Column>
          <Column id='C101' tableId='T1' colIndex='2'>
            QRP_CAT.SCH.T2.F
          </Column>
        </JoinPred>
      </JoinPredList>
      <RangePredList>
        <Range id='R134' sqlType='INTEGER SIGNED NO NULLS'>
          <Column ref='C92'>
          </Column>
          <OpLE isNormalized='1'>
            <NumericVal scale='0'>
              19
            </NumericVal>
          </OpLE>
        </Range>
      </RangePredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O110' name='SYS_A1'>
        <Column id='C92' tableId='T2' colIndex='0' isNullable='0'>
          QRP_CAT.SCH.T1.A
        </Column>
      </Output>
      <Output id='O113' name='B'>
        <Column id='C93' tableId='T2' colIndex='1'>
          QRP_CAT.SCH.T1.B
        </Column>
      </Output>
      <Output id='O119' name='SYS_D2'>
        <Column id='C99' tableId='T1' colIndex='0' isNullable='0'>
          QRP_CAT.SCH.T2.D
        </Column>
      </Output>
    </OutputList>
  </JBB>
</MV>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              

--- 1 row(s) selected.
>>
>>-- RECOMPUTE INITIALIZE ON REFRESH MV
>>set param ?mvName 'MV5';
>>set param ?mvSchemaName $$MVSchemaName$$;
>>create mv mv5 recompute initialize on refresh
+>  enable query rewrite
+>  as select b, max(c) max_c from t1,t2
+>  where  a < 20 and b = f
+>  group by b;

--- SQL operation complete.
>>
>>-- check contents of REWRITE_PUBLISH table
>>execute getMVRP;

OBJECT_NAME                           OBJECT_NEW_NAME                       D_I          O_T  I_C  REFRESH_AT
------------------------------------  ------------------------------------  -----------  ---  ---  ----------

QRP_CAT.SCH.MV1                       ?                                              -2  CT   0    0         
QRP_CAT.SCH.MV2                       ?                                              -2  CR   0    NONZERO   
QRP_CAT.SCH.MV3                       ?                                              -2  CT   0    0         
QRP_CAT.SCH.MV4                       ?                                              -2  CR   0    NONZERO   
QRP_CAT.SCH.MV5                       ?                                              -2  CT   0    0         

--- 5 row(s) selected.
>>
>>-- check the mv descriptor text in TEXT SMD table
>>execute getMVDescFromTEXT;

SEQUENCE_NUM  MV_DESCRIPTOR_TEXT
------------  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

           0  <MV>
  <Version>
    1.0
  </Version>
  <Table>
    QRP_CAT.SCH.MV5
  </Table>
  <Misc isIncremental='0'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T2' TS='212222176849876481' numCols='3' rangeBits='00000008'>
          QRP_CAT.SCH.T1
        </Table>
        <Table id='T1' TS='212222176849876481' numCols='3'>
          QRP_CAT.SCH.T2
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J90'>
          <Column ref='C69'>
          </Column>
          <Column id='C77' tableId='T1' colIndex='2'>
            QRP_CAT.SCH.T2.F
          </Column>
        </JoinPred>
      </JoinPredList>
      <RangePredList>
        <Range id='R111' sqlType='INTEGER SIGNED NO NULLS'>
          <Column id='C68' tableId='T2' colIndex='0' isNullable='0'>
            QRP_CAT.SCH.T1.A
          </Column>
          <OpLE isNormalized='1'>
            <NumericVal scale='0'>
              19
            </NumericVal>
          </OpLE>
        </Range>
      </RangePredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O85' name='MAX_C'>
        <Expr id='X85'>
          <Function id='S85' op='max' aggregateFunc='5'>
            <Column id='C70' tableId='T2' colIndex='2'>
              QRP_CAT.SCH.T1.C
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O90' name='B'>
        <Column ref='C69'>
        </Column>
      </Output>
    </OutputList>
    <GroupBy id='G3'>
      <Primary>
        <Column id='C69' tableId='T2' colIndex='1'>
          QRP_CAT.SCH.T1.B
        </Column>
      </Primary>
    </GroupBy>
  </JBB>
</MV>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                

--- 1 row(s) selected.
>>
>>
>>-- RECOMPUTE INITIALIZE ON CREATE MV
>>set param ?mvName 'MV6';
>>set param ?mvSchemaName $$MVSchemaName$$;
>>create mv mv6 recompute initialize on create
+>  enable query rewrite
+>  as select b, max(c) max_c from t1_v,t2
+>  where  a < 20 and b = f
+>  group by b;

--- SQL operation complete.
>>
>>-- check contents of REWRITE_PUBLISH table
>>execute getMVRP;

OBJECT_NAME                           OBJECT_NEW_NAME                       D_I          O_T  I_C  REFRESH_AT
------------------------------------  ------------------------------------  -----------  ---  ---  ----------

QRP_CAT.SCH.MV1                       ?                                              -2  CT   0    0         
QRP_CAT.SCH.MV2                       ?                                              -2  CR   0    NONZERO   
QRP_CAT.SCH.MV3                       ?                                              -2  CT   0    0         
QRP_CAT.SCH.MV4                       ?                                              -2  CR   0    NONZERO   
QRP_CAT.SCH.MV5                       ?                                              -2  CT   0    0         
QRP_CAT.SCH.MV6                       ?                                              -2  CR   0    NONZERO   

--- 6 row(s) selected.
>>
>>-- check the mv descriptor text in TEXT SMD table
>>execute getMVDescFromTEXT;

SEQUENCE_NUM  MV_DESCRIPTOR_TEXT
------------  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

           0  <MV>
  <Version>
    1.0
  </Version>
  <Table>
    QRP_CAT.SCH.MV6
  </Table>
  <Misc isIncremental='0'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T2' TS='212222176849876481' numCols='3' rangeBits='00000008'>
          QRP_CAT.SCH.T1
        </Table>
        <Table id='T1' TS='212222176849876481' numCols='3'>
          QRP_CAT.SCH.T2
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J90'>
          <Column ref='C69'>
          </Column>
          <Column id='C77' tableId='T1' colIndex='2'>
            QRP_CAT.SCH.T2.F
          </Column>
        </JoinPred>
      </JoinPredList>
      <RangePredList>
        <Range id='R111' sqlType='INTEGER SIGNED NO NULLS'>
          <Column id='C68' tableId='T2' colIndex='0' isNullable='0'>
            QRP_CAT.SCH.T1.A
          </Column>
          <OpLE isNormalized='1'>
            <NumericVal scale='0'>
              19
            </NumericVal>
          </OpLE>
        </Range>
      </RangePredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O85' name='MAX_C'>
        <Expr id='X85'>
          <Function id='S85' op='max' aggregateFunc='5'>
            <Column id='C70' tableId='T2' colIndex='2'>
              QRP_CAT.SCH.T1.C
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O90' name='B'>
        <Column ref='C69'>
        </Column>
      </Output>
    </OutputList>
    <GroupBy id='G3'>
      <Primary>
        <Column id='C69' tableId='T2' colIndex='1'>
          QRP_CAT.SCH.T1.B
        </Column>
      </Primary>
    </GroupBy>
  </JBB>
</MV>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                

--- 1 row(s) selected.
>>
>>-- MV using another MV
>>set param ?mvName 'MV7';
>>set param ?mvSchemaName $$MVSchemaName$$;
>>create mv mv7 refresh on request initialize on refresh
+>  enable query rewrite
+>  as select b, count(c) cc from mv1
+>  group by b;

*** WARNING[12112] A secondary index QRP_CAT.SCH.MV7_561433112_7831 was created for the materialized view.

--- SQL operation completed with warnings.
>>
>>-- check the mv descriptor text in TEXT SMD table
>>execute getMVRP;

OBJECT_NAME                           OBJECT_NEW_NAME                       D_I          O_T  I_C  REFRESH_AT
------------------------------------  ------------------------------------  -----------  ---  ---  ----------

QRP_CAT.SCH.MV1                       ?                                              -2  CT   0    0         
QRP_CAT.SCH.MV2                       ?                                              -2  CR   0    NONZERO   
QRP_CAT.SCH.MV3                       ?                                              -2  CT   0    0         
QRP_CAT.SCH.MV4                       ?                                              -2  CR   0    NONZERO   
QRP_CAT.SCH.MV5                       ?                                              -2  CT   0    0         
QRP_CAT.SCH.MV6                       ?                                              -2  CR   0    NONZERO   
QRP_CAT.SCH.MV7                       ?                                              -2  CT   0    0         

--- 7 row(s) selected.
>>
>>-- check contents of REWRITE_PUBLISH table
>>execute getMVDescFromTEXT;

SEQUENCE_NUM  MV_DESCRIPTOR_TEXT
------------  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

           0  <MV>
  <Version>
    1.0
  </Version>
  <Table>
    QRP_CAT.SCH.MV7
  </Table>
  <Misc isIncremental='1'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T2' TS='212222176849876481' numCols='3' rangeBits='00000008'>
          QRP_CAT.SCH.T1
        </Table>
        <Table id='T1' TS='212222176849876481' numCols='3'>
          QRP_CAT.SCH.T2
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J85'>
          <Column ref='C63'>
          </Column>
          <Column id='C70' tableId='T1' colIndex='2'>
            QRP_CAT.SCH.T2.F
          </Column>
        </JoinPred>
      </JoinPredList>
      <RangePredList>
        <Range id='R106' sqlType='INTEGER SIGNED NO NULLS'>
          <Column id='C62' tableId='T2' colIndex='0' isNullable='0'>
            QRP_CAT.SCH.T1.A
          </Column>
          <OpLE isNormalized='1'>
            <NumericVal scale='0'>
              19
            </NumericVal>
          </OpLE>
        </Range>
      </RangePredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O78' name='CC'>
        <Expr id='X78'>
          <Function id='S78' op='count_nonull' aggregateFunc='2'>
            <Column id='C64' tableId='T2' colIndex='2'>
              QRP_CAT.SCH.T1.C
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O80' name='SYS_COUNTSTAR1'>
        <Expr id='X80'>
          <Function id='S80' op='count' aggregateFunc='1'>
            <NumericVal id='S79' scale='0'>
              1 
            </NumericVal>
          </Function>
        </Expr>
      </Output>
      <Output id='O85' name='B'>
        <Column ref='C63'>
        </Column>
      </Output>
    </OutputList>
    <GroupBy id='G3'>
      <Primary>
        <Column id='C63' tableId='T2' colIndex='1'>
          QRP_CAT.SCH.T1.B
        </Column>
      </Primary>
    </GroupBy>
  </JBB>
</MV>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            

--- 1 row(s) selected.
>>
>>-- USER MAINTAINED MV
>>set param ?mvName 'MV10';
>>set param ?mvSchemaName $$MVSchemaName$$;
>>create mv mv10 refresh by user initialize by user
+>  enable query rewrite
+>  as select b, max(c) max_c from t1_v,t2
+>  where  a < 20 and b = f
+>  group by b;

--- SQL operation complete.
>>
>>-- check contents of REWRITE_PUBLISH table
>>execute getMVRP;

OBJECT_NAME                           OBJECT_NEW_NAME                       D_I          O_T  I_C  REFRESH_AT
------------------------------------  ------------------------------------  -----------  ---  ---  ----------

QRP_CAT.SCH.MV1                       ?                                              -2  CT   0    0         
QRP_CAT.SCH.MV2                       ?                                              -2  CR   0    NONZERO   
QRP_CAT.SCH.MV3                       ?                                              -2  CT   0    0         
QRP_CAT.SCH.MV4                       ?                                              -2  CR   0    NONZERO   
QRP_CAT.SCH.MV5                       ?                                              -2  CT   0    0         
QRP_CAT.SCH.MV6                       ?                                              -2  CR   0    NONZERO   
QRP_CAT.SCH.MV7                       ?                                              -2  CT   0    0         
QRP_CAT.SCH.MV10                      ?                                              -2  CR   0    NONZERO   

--- 8 row(s) selected.
>>
>>-- check the mv descriptor text in TEXT SMD table
>>execute getMVDescFromTEXT;

SEQUENCE_NUM  MV_DESCRIPTOR_TEXT
------------  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

           0  <MV>
  <Version>
    1.0
  </Version>
  <Table>
    QRP_CAT.SCH.MV10
  </Table>
  <Misc isIncremental='0' isUMV='1'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T2' TS='212222176849876481' numCols='3' rangeBits='00000008'>
          QRP_CAT.SCH.T1
        </Table>
        <Table id='T1' TS='212222176849876481' numCols='3'>
          QRP_CAT.SCH.T2
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J90'>
          <Column ref='C69'>
          </Column>
          <Column id='C77' tableId='T1' colIndex='2'>
            QRP_CAT.SCH.T2.F
          </Column>
        </JoinPred>
      </JoinPredList>
      <RangePredList>
        <Range id='R111' sqlType='INTEGER SIGNED NO NULLS'>
          <Column id='C68' tableId='T2' colIndex='0' isNullable='0'>
            QRP_CAT.SCH.T1.A
          </Column>
          <OpLE isNormalized='1'>
            <NumericVal scale='0'>
              19
            </NumericVal>
          </OpLE>
        </Range>
      </RangePredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O85' name='MAX_C'>
        <Expr id='X85'>
          <Function id='S85' op='max' aggregateFunc='5'>
            <Column id='C70' tableId='T2' colIndex='2'>
              QRP_CAT.SCH.T1.C
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O90' name='B'>
        <Column ref='C69'>
        </Column>
      </Output>
    </OutputList>
    <GroupBy id='G3'>
      <Primary>
        <Column id='C69' tableId='T2' colIndex='1'>
          QRP_CAT.SCH.T1.B
        </Column>
      </Primary>
    </GroupBy>
  </JBB>
</MV>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     

--- 1 row(s) selected.
>>
>>-- clean it up
>>drop mv mv10;

--- SQL operation complete.
>>
>>-- Run QMP and clean up the PUBLISH table
>>obey TEST001(run_qmp);
>>----------------------------------------------------
>>----------------------------------------------------
>>
>>-- Run QMP
>>sh sh -c "$QMP -target FILE qmpLog.log";
>>log;
<Publish TS='212222176840249210'>
  <MVName>
    QRP_CAT.SCH.MV1
  </MVName>
    <MV>
  <Version>
    1.0
  </Version>
  <Table>
    QRP_CAT.SCH.MV1
  </Table>
  <Misc isIncremental='1'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T2' TS='212222176832203190' isKeyCovered='1' numCols='3' rangeBits='00000008'>
          QRP_CAT.SCH.T1
        </Table>
        <Table id='T1' TS='212222176834226175' isKeyCovered='1' numCols='3'>
          QRP_CAT.SCH.T2
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J116'>
          <Column ref='C96'>
          </Column>
          <Column id='C104' tableId='T1' colIndex='2'>
            QRP_CAT.SCH.T2.F
          </Column>
        </JoinPred>
      </JoinPredList>
      <RangePredList>
        <Range id='R137' sqlType='INTEGER SIGNED NO NULLS'>
          <Column ref='C95'>
          </Column>
          <OpLE isNormalized='1'>
            <NumericVal scale='0'>
              19
            </NumericVal>
          </OpLE>
        </Range>
      </RangePredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O113' name='SYS_A1'>
        <Column id='C95' tableId='T2' colIndex='0' isNullable='0'>
          QRP_CAT.SCH.T1.A
        </Column>
      </Output>
      <Output id='O116' name='B'>
        <Column id='C96' tableId='T2' colIndex='1'>
          QRP_CAT.SCH.T1.B
        </Column>
      </Output>
      <Output id='O119' name='C'>
        <Column id='C97' tableId='T2' colIndex='2'>
          QRP_CAT.SCH.T1.C
        </Column>
      </Output>
      <Output id='O122' name='SYS_D2'>
        <Column id='C102' tableId='T1' colIndex='0' isNullable='0'>
          QRP_CAT.SCH.T2.D
        </Column>
      </Output>
    </OutputList>
  </JBB>
</MV>
</Publish>
<Publish TS='212222176844026469'>
  <MVName>
    QRP_CAT.SCH.MV2
  </MVName>
  <Update op='Refresh' TS='212222176844026469'>
  </Update>
    <MV>
  <Version>
    1.0
  </Version>
  <Table>
    QRP_CAT.SCH.MV2
  </Table>
  <Misc isIncremental='1'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T2' TS='212222176840249210' numCols='3' rangeBits='00000008'>
          QRP_CAT.SCH.T1
        </Table>
        <Table id='T1' TS='212222176840249210' numCols='3'>
          QRP_CAT.SCH.T2
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J102'>
          <Column id='C78' tableId='T2' colIndex='2'>
            QRP_CAT.SCH.T1.C
          </Column>
          <Column id='C84' tableId='T1' colIndex='1'>
            QRP_CAT.SCH.T2.E
          </Column>
        </JoinPred>
      </JoinPredList>
      <RangePredList>
        <Range id='R120' sqlType='INTEGER SIGNED NO NULLS'>
          <Column id='C76' tableId='T2' colIndex='0' isNullable='0'>
            QRP_CAT.SCH.T1.A
          </Column>
          <OpLE isNormalized='1'>
            <NumericVal scale='0'>
              19
            </NumericVal>
          </OpLE>
        </Range>
      </RangePredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O94' name='CNT'>
        <Expr id='X94'>
          <Function id='S94' op='count' aggregateFunc='1'>
            <NumericVal id='S93' scale='0'>
              1 
            </NumericVal>
          </Function>
        </Expr>
      </Output>
      <Output id='O99' name='B'>
        <Column ref='C77'>
        </Column>
      </Output>
    </OutputList>
    <GroupBy id='G3'>
      <Primary>
        <Column id='C77' tableId='T2' colIndex='1'>
          QRP_CAT.SCH.T1.B
        </Column>
      </Primary>
    </GroupBy>
  </JBB>
</MV>
</Publish>
<Publish TS='212222176847956546'>
  <MVName>
    QRP_CAT.SCH.MV3
  </MVName>
    <MV>
  <Version>
    1.0
  </Version>
  <Table>
    QRP_CAT.SCH.MV3
  </Table>
  <Misc isIncremental='1' isImmediate='1'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T2' TS='212222176840249210' isKeyCovered='1' numCols='3' rangeBits='00000008'>
          QRP_CAT.SCH.T1
        </Table>
        <Table id='T1' TS='212222176844026469' isKeyCovered='1' numCols='3'>
          QRP_CAT.SCH.T2
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J113'>
          <Column ref='C93'>
          </Column>
          <Column id='C101' tableId='T1' colIndex='2'>
            QRP_CAT.SCH.T2.F
          </Column>
        </JoinPred>
      </JoinPredList>
      <RangePredList>
        <Range id='R134' sqlType='INTEGER SIGNED NO NULLS'>
          <Column ref='C92'>
          </Column>
          <OpLE isNormalized='1'>
            <NumericVal scale='0'>
              19
            </NumericVal>
          </OpLE>
        </Range>
      </RangePredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O110' name='SYS_A1'>
        <Column id='C92' tableId='T2' colIndex='0' isNullable='0'>
          QRP_CAT.SCH.T1.A
        </Column>
      </Output>
      <Output id='O113' name='B'>
        <Column id='C93' tableId='T2' colIndex='1'>
          QRP_CAT.SCH.T1.B
        </Column>
      </Output>
      <Output id='O119' name='SYS_D2'>
        <Column id='C99' tableId='T1' colIndex='0' isNullable='0'>
          QRP_CAT.SCH.T2.D
        </Column>
      </Output>
    </OutputList>
  </JBB>
</MV>
</Publish>
<Publish TS='212222176849876481'>
  <MVName>
    QRP_CAT.SCH.MV4
  </MVName>
  <Update op='Refresh' TS='212222176849876481'>
  </Update>
    <MV>
  <Version>
    1.0
  </Version>
  <Table>
    QRP_CAT.SCH.MV4
  </Table>
  <Misc isIncremental='1' isImmediate='1'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T2' TS='212222176840249210' isKeyCovered='1' numCols='3' rangeBits='00000008'>
          QRP_CAT.SCH.T1
        </Table>
        <Table id='T1' TS='212222176844026469' isKeyCovered='1' numCols='3'>
          QRP_CAT.SCH.T2
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J113'>
          <Column ref='C93'>
          </Column>
          <Column id='C101' tableId='T1' colIndex='2'>
            QRP_CAT.SCH.T2.F
          </Column>
        </JoinPred>
      </JoinPredList>
      <RangePredList>
        <Range id='R134' sqlType='INTEGER SIGNED NO NULLS'>
          <Column ref='C92'>
          </Column>
          <OpLE isNormalized='1'>
            <NumericVal scale='0'>
              19
            </NumericVal>
          </OpLE>
        </Range>
      </RangePredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O110' name='SYS_A1'>
        <Column id='C92' tableId='T2' colIndex='0' isNullable='0'>
          QRP_CAT.SCH.T1.A
        </Column>
      </Output>
      <Output id='O113' name='B'>
        <Column id='C93' tableId='T2' colIndex='1'>
          QRP_CAT.SCH.T1.B
        </Column>
      </Output>
      <Output id='O119' name='SYS_D2'>
        <Column id='C99' tableId='T1' colIndex='0' isNullable='0'>
          QRP_CAT.SCH.T2.D
        </Column>
      </Output>
    </OutputList>
  </JBB>
</MV>
</Publish>
<Publish TS='212222176852702577'>
  <MVName>
    QRP_CAT.SCH.MV5
  </MVName>
    <MV>
  <Version>
    1.0
  </Version>
  <Table>
    QRP_CAT.SCH.MV5
  </Table>
  <Misc isIncremental='0'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T2' TS='212222176849876481' numCols='3' rangeBits='00000008'>
          QRP_CAT.SCH.T1
        </Table>
        <Table id='T1' TS='212222176849876481' numCols='3'>
          QRP_CAT.SCH.T2
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J90'>
          <Column ref='C69'>
          </Column>
          <Column id='C77' tableId='T1' colIndex='2'>
            QRP_CAT.SCH.T2.F
          </Column>
        </JoinPred>
      </JoinPredList>
      <RangePredList>
        <Range id='R111' sqlType='INTEGER SIGNED NO NULLS'>
          <Column id='C68' tableId='T2' colIndex='0' isNullable='0'>
            QRP_CAT.SCH.T1.A
          </Column>
          <OpLE isNormalized='1'>
            <NumericVal scale='0'>
              19
            </NumericVal>
          </OpLE>
        </Range>
      </RangePredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O85' name='MAX_C'>
        <Expr id='X85'>
          <Function id='S85' op='max' aggregateFunc='5'>
            <Column id='C70' tableId='T2' colIndex='2'>
              QRP_CAT.SCH.T1.C
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O90' name='B'>
        <Column ref='C69'>
        </Column>
      </Output>
    </OutputList>
    <GroupBy id='G3'>
      <Primary>
        <Column id='C69' tableId='T2' colIndex='1'>
          QRP_CAT.SCH.T1.B
        </Column>
      </Primary>
    </GroupBy>
  </JBB>
</MV>
</Publish>
<Publish TS='212222176853715386'>
  <MVName>
    QRP_CAT.SCH.MV6
  </MVName>
  <Update op='Refresh' TS='212222176853715386'>
  </Update>
    <MV>
  <Version>
    1.0
  </Version>
  <Table>
    QRP_CAT.SCH.MV6
  </Table>
  <Misc isIncremental='0'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T2' TS='212222176849876481' numCols='3' rangeBits='00000008'>
          QRP_CAT.SCH.T1
        </Table>
        <Table id='T1' TS='212222176849876481' numCols='3'>
          QRP_CAT.SCH.T2
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J90'>
          <Column ref='C69'>
          </Column>
          <Column id='C77' tableId='T1' colIndex='2'>
            QRP_CAT.SCH.T2.F
          </Column>
        </JoinPred>
      </JoinPredList>
      <RangePredList>
        <Range id='R111' sqlType='INTEGER SIGNED NO NULLS'>
          <Column id='C68' tableId='T2' colIndex='0' isNullable='0'>
            QRP_CAT.SCH.T1.A
          </Column>
          <OpLE isNormalized='1'>
            <NumericVal scale='0'>
              19
            </NumericVal>
          </OpLE>
        </Range>
      </RangePredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O85' name='MAX_C'>
        <Expr id='X85'>
          <Function id='S85' op='max' aggregateFunc='5'>
            <Column id='C70' tableId='T2' colIndex='2'>
              QRP_CAT.SCH.T1.C
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O90' name='B'>
        <Column ref='C69'>
        </Column>
      </Output>
    </OutputList>
    <GroupBy id='G3'>
      <Primary>
        <Column id='C69' tableId='T2' colIndex='1'>
          QRP_CAT.SCH.T1.B
        </Column>
      </Primary>
    </GroupBy>
  </JBB>
</MV>
</Publish>
<Publish TS='212222176865524603'>
  <MVName>
    QRP_CAT.SCH.MV7
  </MVName>
    <MV>
  <Version>
    1.0
  </Version>
  <Table>
    QRP_CAT.SCH.MV7
  </Table>
  <Misc isIncremental='1'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T2' TS='212222176849876481' numCols='3' rangeBits='00000008'>
          QRP_CAT.SCH.T1
        </Table>
        <Table id='T1' TS='212222176849876481' numCols='3'>
          QRP_CAT.SCH.T2
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J85'>
          <Column ref='C63'>
          </Column>
          <Column id='C70' tableId='T1' colIndex='2'>
            QRP_CAT.SCH.T2.F
          </Column>
        </JoinPred>
      </JoinPredList>
      <RangePredList>
        <Range id='R106' sqlType='INTEGER SIGNED NO NULLS'>
          <Column id='C62' tableId='T2' colIndex='0' isNullable='0'>
            QRP_CAT.SCH.T1.A
          </Column>
          <OpLE isNormalized='1'>
            <NumericVal scale='0'>
              19
            </NumericVal>
          </OpLE>
        </Range>
      </RangePredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O78' name='CC'>
        <Expr id='X78'>
          <Function id='S78' op='count_nonull' aggregateFunc='2'>
            <Column id='C64' tableId='T2' colIndex='2'>
              QRP_CAT.SCH.T1.C
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O80' name='SYS_COUNTSTAR1'>
        <Expr id='X80'>
          <Function id='S80' op='count' aggregateFunc='1'>
            <NumericVal id='S79' scale='0'>
              1 
            </NumericVal>
          </Function>
        </Expr>
      </Output>
      <Output id='O85' name='B'>
        <Column ref='C63'>
        </Column>
      </Output>
    </OutputList>
    <GroupBy id='G3'>
      <Primary>
        <Column id='C63' tableId='T2' colIndex='1'>
          QRP_CAT.SCH.T1.B
        </Column>
      </Primary>
    </GroupBy>
  </JBB>
</MV>
</Publish>
<Publish TS='212222176868676810'>
  <MVName>
    QRP_CAT.SCH.MV10
  </MVName>
  <Update op='Refresh' TS='212222176868676810'>
  </Update>
    
</Publish>
<Publish TS='212222176868676811'>
  <MVName>
    QRP_CAT.SCH.MV10
  </MVName>
  <Update op='Drop'>
  </Update>
</Publish>
>>
>>-- clean up the PUBLISH table
>>delete from manageability.mv_rewrite.rewrite_publish;

--- 0 row(s) deleted.
>>
>>
>>obey TEST001(refresh_mv);
>>----------------------------------------------------
>>---------------- refresh mv section ----------------
>>-- test the refresh of the different mv refresh
>>-- types: ON REQUEST, ON STATEMENT and RECOMPUTE
>>---- The test will:
>>------- make sure that a row is inserted into the
>>--        REWRITE_PUBLISH PUBLISH table after
>>--        an MV refresh
>>--      make sure that the correct type of refresh
>>--      is indicated (incremental refresh versus
>>--      recompute refresh)
>>----------------------------------------------------
>>
>>-- initialize an ON REQUEST MV
>>refresh mv1 outfile REFRESH.LOG;

--- SQL operation complete.
>>log;

The materialized view QRP_CAT.SCH.MV1 is being refreshed (by recompute) in a single transaction...

The materialized view QRP_CAT.SCH.MV1 has been refreshed (by recompute) in a single transaction.

Starting the log cleanup of table QRP_CAT.SCH.T2...

Finished the log cleanup of table QRP_CAT.SCH.T2.

Starting the log cleanup of table QRP_CAT.SCH.T1...

Finished the log cleanup of table QRP_CAT.SCH.T1.


>>
>>-- check contents of REWRITE_PUBLISH table
>>execute getMVRP;

OBJECT_NAME                           OBJECT_NEW_NAME                       D_I          O_T  I_C  REFRESH_AT
------------------------------------  ------------------------------------  -----------  ---  ---  ----------

QRP_CAT.SCH.MV1                       ?                                               ?  RR   ?    NONZERO   

--- 1 row(s) selected.
>>
>>-- initialize an ON STATEMENT MV
>>refresh mv3 outfile REFRESH.LOG;

--- SQL operation complete.
>>log;

The materialized view QRP_CAT.SCH.MV3 is being refreshed (by recompute) in a single transaction...

The materialized view QRP_CAT.SCH.MV3 has been refreshed (by recompute) in a single transaction.


>>
>>-- check contents of REWRITE_PUBLISH table
>>execute getMVRP;

OBJECT_NAME                           OBJECT_NEW_NAME                       D_I          O_T  I_C  REFRESH_AT
------------------------------------  ------------------------------------  -----------  ---  ---  ----------

QRP_CAT.SCH.MV1                       ?                                               ?  RR   ?    NONZERO   
QRP_CAT.SCH.MV3                       ?                                               ?  RR   ?    NONZERO   

--- 2 row(s) selected.
>>
>>-- initialize a RECOMPUTE MV
>>refresh mv5 outfile REFRESH.LOG;

--- SQL operation complete.
>>log;

The materialized view QRP_CAT.SCH.MV5 is being refreshed (by recompute) in a single transaction...

The materialized view QRP_CAT.SCH.MV5 has been refreshed (by recompute) in a single transaction.


>>
>>-- check contents of REWRITE_PUBLISH table
>>execute getMVRP;

OBJECT_NAME                           OBJECT_NEW_NAME                       D_I          O_T  I_C  REFRESH_AT
------------------------------------  ------------------------------------  -----------  ---  ---  ----------

QRP_CAT.SCH.MV1                       ?                                               ?  RR   ?    NONZERO   
QRP_CAT.SCH.MV3                       ?                                               ?  RR   ?    NONZERO   
QRP_CAT.SCH.MV5                       ?                                               ?  RR   ?    NONZERO   

--- 3 row(s) selected.
>>
>>-- induce incremental refresh and
>>-- check contents of REWRITE_PUBLISH table
>>insert into t1 values (8,1,'k'),(9,2,'z');

--- 2 row(s) inserted.
>>refresh mv1 outfile REFRESH.LOG;

--- SQL operation complete.
>>log;

The materialized view QRP_CAT.SCH.MV1 is being refreshed in a single transaction...

The materialized view QRP_CAT.SCH.MV1 has been refreshed in a single transaction.

Starting the log cleanup of table QRP_CAT.SCH.T2...

Finished the log cleanup of table QRP_CAT.SCH.T2.

Starting the log cleanup of table QRP_CAT.SCH.T1...

Finished the log cleanup of table QRP_CAT.SCH.T1.


>>refresh mv2 outfile REFRESH.LOG;

--- SQL operation complete.
>>log;

The materialized view QRP_CAT.SCH.MV2 is being refreshed in a single transaction...

The materialized view QRP_CAT.SCH.MV2 has been refreshed in a single transaction.

Starting the log cleanup of table QRP_CAT.SCH.T2...

Finished the log cleanup of table QRP_CAT.SCH.T2.

Starting the log cleanup of table QRP_CAT.SCH.T1...

Finished the log cleanup of table QRP_CAT.SCH.T1.


>>execute getMVRP;

OBJECT_NAME                           OBJECT_NEW_NAME                       D_I          O_T  I_C  REFRESH_AT
------------------------------------  ------------------------------------  -----------  ---  ---  ----------

QRP_CAT.SCH.MV1                       ?                                               ?  RR   ?    NONZERO   
QRP_CAT.SCH.MV3                       ?                                               ?  RR   ?    NONZERO   
QRP_CAT.SCH.MV5                       ?                                               ?  RR   ?    NONZERO   
QRP_CAT.SCH.MV1                       ?                                               ?  RF   ?    NONZERO   
QRP_CAT.SCH.MV2                       ?                                               ?  RF   ?    NONZERO   

--- 5 row(s) selected.
>>
>>-- execute a refresh with recompute on an mv
>>refresh mv1 recompute outfile REFRESH.LOG;

--- SQL operation complete.
>>log;

The materialized view QRP_CAT.SCH.MV1 is being refreshed (by recompute) in a single transaction...

The materialized view QRP_CAT.SCH.MV1 has been refreshed (by recompute) in a single transaction.

Starting the log cleanup of table QRP_CAT.SCH.T2...

Finished the log cleanup of table QRP_CAT.SCH.T2.

Starting the log cleanup of table QRP_CAT.SCH.T1...

Finished the log cleanup of table QRP_CAT.SCH.T1.


>>
>>-- check contents of REWRITE_PUBLISH table
>>execute getMVRP;

OBJECT_NAME                           OBJECT_NEW_NAME                       D_I          O_T  I_C  REFRESH_AT
------------------------------------  ------------------------------------  -----------  ---  ---  ----------

QRP_CAT.SCH.MV1                       ?                                               ?  RR   ?    NONZERO   
QRP_CAT.SCH.MV3                       ?                                               ?  RR   ?    NONZERO   
QRP_CAT.SCH.MV5                       ?                                               ?  RR   ?    NONZERO   
QRP_CAT.SCH.MV1                       ?                                               ?  RF   ?    NONZERO   
QRP_CAT.SCH.MV2                       ?                                               ?  RF   ?    NONZERO   
QRP_CAT.SCH.MV1                       ?                                               ?  RR   ?    NONZERO   

--- 6 row(s) selected.
>>
>>-- Run QMP and clean up the PUBLISH table
>>obey TEST001(run_qmp);
>>----------------------------------------------------
>>----------------------------------------------------
>>
>>-- Run QMP
>>sh sh -c "$QMP -target FILE qmpLog.log";
>>log;
<Publish TS='212222176891410729'>
  <MVName>
    QRP_CAT.SCH.MV1
  </MVName>
  <Update op='Recompute' TS='212222176880868335'>
  </Update>
</Publish>
<Publish TS='212222176902165878'>
  <MVName>
    QRP_CAT.SCH.MV3
  </MVName>
  <Update op='Recompute' TS='212222176902349277'>
  </Update>
</Publish>
<Publish TS='212222176907398410'>
  <MVName>
    QRP_CAT.SCH.MV5
  </MVName>
  <Update op='Recompute' TS='212222176907551321'>
  </Update>
</Publish>
<Publish TS='212222176891410729'>
  <MVName>
    QRP_CAT.SCH.MV1
  </MVName>
  <Update op='Refresh' TS='212222176912906541'>
  </Update>
</Publish>
<Publish TS='212222176844026469'>
  <MVName>
    QRP_CAT.SCH.MV2
  </MVName>
  <Update op='Refresh' TS='212222176927747403'>
  </Update>
</Publish>
<Publish TS='212222176954940716'>
  <MVName>
    QRP_CAT.SCH.MV1
  </MVName>
  <Update op='Recompute' TS='212222176947338325'>
  </Update>
</Publish>
>>
>>-- clean up the PUBLISH table
>>delete from manageability.mv_rewrite.rewrite_publish;

--- 0 row(s) deleted.
>>
>>
>>obey TEST001(refresh_mvgroup);
>>----------------------------------------------------
>>------------- refresh mvgroup section --------------
>>-- test the refresh of an mvgroup. An mvgroup can 
>>-- only have ON REQUEST an RECOMPUTE mvs
>>---- The test will:
>>------- make sure that rows are inserted into the
>>--        REWRITE_PUBLISH PUBLISH table after
>>--        the mvgroup refresh as if the mvs were
>>--        refreshed seperatly
>>--      make sure that the correct type of refresh
>>--      is indicated (incremental refresh versus
>>--      recompute refresh)
>>----------------------------------------------------
>>
>>-- generate deltas
>>insert into t1 values (4,4,'e');

--- 1 row(s) inserted.
>>insert into t2 values (4,'d',2),(5,'e',3);

--- 2 row(s) inserted.
>>
>>-- create and mvgroup and add two on request 
>>-- mvs to it, then refresh it
>>create mvgroup mvr_g1;

--- SQL operation complete.
>>alter mvgroup mvr_g1 add mv1;

--- SQL operation complete.
>>--alter mvgroup mvr_g1 add mv2;
>>refresh mvgroup mvr_g1 outfile REFRESH.LOG;

--- SQL operation complete.
>>log;

The materialized view QRP_CAT.SCH.MV1 is being refreshed in a single transaction...

The materialized view QRP_CAT.SCH.MV1 has been refreshed from 2 deltas in a single transaction.

Starting the log cleanup of table QRP_CAT.SCH.T2...

Finished the log cleanup of table QRP_CAT.SCH.T2.

Starting the log cleanup of table QRP_CAT.SCH.T1...

Finished the log cleanup of table QRP_CAT.SCH.T1.


>>alter mvgroup mvr_g1 remove mv1;

--- SQL operation complete.
>>alter mvgroup mvr_g1 add mv2;

--- SQL operation complete.
>>
>>-- check contents of REWRITE_PUBLISH table
>>execute getMVRP;

OBJECT_NAME                           OBJECT_NEW_NAME                       D_I          O_T  I_C  REFRESH_AT
------------------------------------  ------------------------------------  -----------  ---  ---  ----------

QRP_CAT.SCH.MV1                       ?                                               ?  RF   ?    NONZERO   

--- 1 row(s) selected.
>>
>>
>>-- generate deltas
>>insert into t1 values (5,2,'f'),(6,3,'g');

--- 2 row(s) inserted.
>>insert into t2 values (6,'f',4);

--- 1 row(s) inserted.
>>
>>-- add a recompute mv to the mvgroup, then 
>>--refresh the mvgroup with recompute
>>--alter mvgroup mvr_g1 add mv6;
>>refresh mvgroup mvr_g1 recompute outfile REFRESH.LOG;

--- SQL operation complete.
>>log;

The materialized view QRP_CAT.SCH.MV2 is being refreshed (by recompute) in a single transaction...

The materialized view QRP_CAT.SCH.MV2 has been refreshed (by recompute) in a single transaction.

Starting the log cleanup of table QRP_CAT.SCH.T2...

Finished the log cleanup of table QRP_CAT.SCH.T2.

Starting the log cleanup of table QRP_CAT.SCH.T1...

Finished the log cleanup of table QRP_CAT.SCH.T1.


>>
>>-- check contents of REWRITE_PUBLISH table
>>execute getMVRP;

OBJECT_NAME                           OBJECT_NEW_NAME                       D_I          O_T  I_C  REFRESH_AT
------------------------------------  ------------------------------------  -----------  ---  ---  ----------

QRP_CAT.SCH.MV1                       ?                                               ?  RF   ?    NONZERO   
QRP_CAT.SCH.MV2                       ?                                               ?  RR   ?    NONZERO   

--- 2 row(s) selected.
>>
>>-- Run QMP and clean up the PUBLISH table
>>obey TEST001(run_qmp);
>>----------------------------------------------------
>>----------------------------------------------------
>>
>>-- Run QMP
>>sh sh -c "$QMP -target FILE qmpLog.log";
>>log;
<Publish TS='212222176954940716'>
  <MVName>
    QRP_CAT.SCH.MV1
  </MVName>
  <Update op='Refresh' TS='212222176971291316'>
  </Update>
</Publish>
<Publish TS='212222177010731569'>
  <MVName>
    QRP_CAT.SCH.MV2
  </MVName>
  <Update op='Recompute' TS='212222177001775639'>
  </Update>
</Publish>
>>
>>-- clean up the PUBLISH table
>>delete from manageability.mv_rewrite.rewrite_publish;

--- 0 row(s) deleted.
>>
>>
>>obey TEST001(mv_rename);
>>----------------------------------------------------
>>----------------- mv rename section ----------------
>>-- test renaming an MV
>>---- The test will:
>>------- make sure that a row is inserted into the
>>--        REWRITE_PUBLISH PUBLISH table after
>>--        an MV is renamed and make sure that
>>--        correct operation type is used. the row
>>--        should include the MV new and old names
>>----------------------------------------------------
>>
>>-- rename an MV
>>alter mv mv1 rename to mv1_r;

--- SQL operation complete.
>>
>>-- check contents of REWRITE_PUBLISH table
>>execute getMVRP;

OBJECT_NAME                           OBJECT_NEW_NAME                       D_I          O_T  I_C  REFRESH_AT
------------------------------------  ------------------------------------  -----------  ---  ---  ----------

QRP_CAT.SCH.MV7                       ?                                              -2  RP   0    0         
QRP_CAT.SCH.MV1                       QRP_CAT.SCH.MV1_R                               ?  MR   ?    NONZERO   

--- 2 row(s) selected.
>>
>>-- make sure that mv mv1_r descriptor has changed
>>set param ?mvName 'MV1_R';
>>set param ?mvSchemaName $$MVSchemaName$$;
>>execute getMVDescFromTEXT;

SEQUENCE_NUM  MV_DESCRIPTOR_TEXT
------------  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

           0  <MV>
  <Version>
    1.0
  </Version>
  <Table>
    QRP_CAT.SCH.MV1_R
  </Table>
  <Misc isIncremental='1'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T2' TS='212222176832203190' isKeyCovered='1' numCols='3' rangeBits='00000008'>
          QRP_CAT.SCH.T1
        </Table>
        <Table id='T1' TS='212222176834226175' isKeyCovered='1' numCols='3'>
          QRP_CAT.SCH.T2
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J116'>
          <Column ref='C96'>
          </Column>
          <Column id='C104' tableId='T1' colIndex='2'>
            QRP_CAT.SCH.T2.F
          </Column>
        </JoinPred>
      </JoinPredList>
      <RangePredList>
        <Range id='R137' sqlType='INTEGER SIGNED NO NULLS'>
          <Column ref='C95'>
          </Column>
          <OpLE isNormalized='1'>
            <NumericVal scale='0'>
              19
            </NumericVal>
          </OpLE>
        </Range>
      </RangePredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O113' name='SYS_A1'>
        <Column id='C95' tableId='T2' colIndex='0' isNullable='0'>
          QRP_CAT.SCH.T1.A
        </Column>
      </Output>
      <Output id='O116' name='B'>
        <Column id='C96' tableId='T2' colIndex='1'>
          QRP_CAT.SCH.T1.B
        </Column>
      </Output>
      <Output id='O119' name='C'>
        <Column id='C97' tableId='T2' colIndex='2'>
          QRP_CAT.SCH.T1.C
        </Column>
      </Output>
      <Output id='O122' name='SYS_D2'>
        <Column id='C102' tableId='T1' colIndex='0' isNullable='0'>
          QRP_CAT.SCH.T2.D
        </Column>
      </Output>
    </OutputList>
  </JBB>
</MV>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        

--- 1 row(s) selected.
>>
>>-- MV7 is defined on top of MV1 make sure 
>>-- mv7 descriptor text is changed
>>set param ?mvName 'MV7';
>>set param ?mvSchemaName $$MVSchemaName$$;
>>execute getMVDescFromTEXT;

SEQUENCE_NUM  MV_DESCRIPTOR_TEXT
------------  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

           0  <MV>
  <Version>
    1.0
  </Version>
  <Table>
    QRP_CAT.SCH.MV7
  </Table>
  <Misc isIncremental='1'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T2' TS='212222176849876481' numCols='3' rangeBits='00000008'>
          QRP_CAT.SCH.T1
        </Table>
        <Table id='T1' TS='212222176849876481' numCols='3'>
          QRP_CAT.SCH.T2
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J85'>
          <Column ref='C63'>
          </Column>
          <Column id='C70' tableId='T1' colIndex='2'>
            QRP_CAT.SCH.T2.F
          </Column>
        </JoinPred>
      </JoinPredList>
      <RangePredList>
        <Range id='R106' sqlType='INTEGER SIGNED NO NULLS'>
          <Column id='C62' tableId='T2' colIndex='0' isNullable='0'>
            QRP_CAT.SCH.T1.A
          </Column>
          <OpLE isNormalized='1'>
            <NumericVal scale='0'>
              19
            </NumericVal>
          </OpLE>
        </Range>
      </RangePredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O78' name='CC'>
        <Expr id='X78'>
          <Function id='S78' op='count_nonull' aggregateFunc='2'>
            <Column id='C64' tableId='T2' colIndex='2'>
              QRP_CAT.SCH.T1.C
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O80' name='SYS_COUNTSTAR1'>
        <Expr id='X80'>
          <Function id='S80' op='count' aggregateFunc='1'>
            <NumericVal id='S79' scale='0'>
              1 
            </NumericVal>
          </Function>
        </Expr>
      </Output>
      <Output id='O85' name='B'>
        <Column ref='C63'>
        </Column>
      </Output>
    </OutputList>
    <GroupBy id='G3'>
      <Primary>
        <Column id='C63' tableId='T2' colIndex='1'>
          QRP_CAT.SCH.T1.B
        </Column>
      </Primary>
    </GroupBy>
  </JBB>
</MV>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            

--- 1 row(s) selected.
>>
>>-- Run QMP and clean up the PUBLISH table
>>obey TEST001(run_qmp);
>>----------------------------------------------------
>>----------------------------------------------------
>>
>>-- Run QMP
>>sh sh -c "$QMP -target FILE qmpLog.log";
>>log;
<Publish TS='212222177018492892'>
  <MVName>
    QRP_CAT.SCH.MV7
  </MVName>
  <Update op='Alter' hasIgnoreChanges='0'>
  </Update>
  <Update op='Refresh' TS='0'>
  </Update>
    <MV>
  <Version>
    1.0
  </Version>
  <Table>
    QRP_CAT.SCH.MV7
  </Table>
  <Misc isIncremental='1'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T2' TS='212222176849876481' numCols='3' rangeBits='00000008'>
          QRP_CAT.SCH.T1
        </Table>
        <Table id='T1' TS='212222176849876481' numCols='3'>
          QRP_CAT.SCH.T2
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J85'>
          <Column ref='C63'>
          </Column>
          <Column id='C70' tableId='T1' colIndex='2'>
            QRP_CAT.SCH.T2.F
          </Column>
        </JoinPred>
      </JoinPredList>
      <RangePredList>
        <Range id='R106' sqlType='INTEGER SIGNED NO NULLS'>
          <Column id='C62' tableId='T2' colIndex='0' isNullable='0'>
            QRP_CAT.SCH.T1.A
          </Column>
          <OpLE isNormalized='1'>
            <NumericVal scale='0'>
              19
            </NumericVal>
          </OpLE>
        </Range>
      </RangePredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O78' name='CC'>
        <Expr id='X78'>
          <Function id='S78' op='count_nonull' aggregateFunc='2'>
            <Column id='C64' tableId='T2' colIndex='2'>
              QRP_CAT.SCH.T1.C
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O80' name='SYS_COUNTSTAR1'>
        <Expr id='X80'>
          <Function id='S80' op='count' aggregateFunc='1'>
            <NumericVal id='S79' scale='0'>
              1 
            </NumericVal>
          </Function>
        </Expr>
      </Output>
      <Output id='O85' name='B'>
        <Column ref='C63'>
        </Column>
      </Output>
    </OutputList>
    <GroupBy id='G3'>
      <Primary>
        <Column id='C63' tableId='T2' colIndex='1'>
          QRP_CAT.SCH.T1.B
        </Column>
      </Primary>
    </GroupBy>
  </JBB>
</MV>
</Publish>
<Publish TS='212222177018492892'>
  <MVName>
    QRP_CAT.SCH.MV1
  </MVName>
  <Update op='Rename'>
    QRP_CAT.SCH.MV1_R
  </Update>
</Publish>
>>
>>-- clean up the PUBLISH table
>>delete from manageability.mv_rewrite.rewrite_publish;

--- 0 row(s) deleted.
>>
>>
>>
>>obey TEST001(table_rename);
>>----------------------------------------------------
>>--------------- table rename section ---------------
>>-- test renaming an MV base table
>>---- The test will:
>>------- make sure that a row is inserted into the
>>--        REWRITE_PUBLISH PUBLISH table after
>>--        an table is renamed and make sure that 
>>--        correct operation type is used
>>--      make sure that the MV descriptor TEXT in the
>>--        SMD TEXT table is updated to use the new 
>>--        table name
>>----------------------------------------------------
>>
>>-- rename an mv base table
>>alter table t1 rename to t4;

--- SQL operation complete.
>>
>>-- check contents of REWRITE_PUBLISH table
>>-- each mv directly defined on the table should
>>-- have a row published
>>-- mvs that are not directly using the table should
>>-- not be affected (MV7 in this case)
>>-- mv MV6 should also change because views are
>>-- expanded to their base tables
>>execute getMVRP;

OBJECT_NAME                           OBJECT_NEW_NAME                       D_I          O_T  I_C  REFRESH_AT
------------------------------------  ------------------------------------  -----------  ---  ---  ----------

QRP_CAT.SCH.MV1_R                     ?                                              -2  RP   0    NONZERO   
QRP_CAT.SCH.MV2                       ?                                              -2  RP   0    NONZERO   
QRP_CAT.SCH.MV3                       ?                                              -2  RP   0    NONZERO   
QRP_CAT.SCH.MV4                       ?                                              -2  RP   0    NONZERO   
QRP_CAT.SCH.MV5                       ?                                              -2  RP   0    NONZERO   
QRP_CAT.SCH.MV6                       ?                                              -2  RP   0    NONZERO   

--- 6 row(s) selected.
>>
>>-- make sure that the mv descriptors of all mvs
>>-- directly defined on the table have been updated
>>
>>set param ?mvName 'MV1_R';
>>set param ?mvSchemaName $$MVSchemaName$$;
>>execute getMVDescFromTEXT;

SEQUENCE_NUM  MV_DESCRIPTOR_TEXT
------------  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

           0  <MV>
  <Version>
    1.0
  </Version>
  <Table>
    QRP_CAT.SCH.MV1_R
  </Table>
  <Misc isIncremental='1'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T2' TS='212222176832203190' isKeyCovered='1' numCols='3' rangeBits='00000008'>
          QRP_CAT.SCH.T4
        </Table>
        <Table id='T1' TS='212222176834226175' isKeyCovered='1' numCols='3'>
          QRP_CAT.SCH.T2
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J116'>
          <Column ref='C96'>
          </Column>
          <Column id='C104' tableId='T1' colIndex='2'>
            QRP_CAT.SCH.T2.F
          </Column>
        </JoinPred>
      </JoinPredList>
      <RangePredList>
        <Range id='R137' sqlType='INTEGER SIGNED NO NULLS'>
          <Column ref='C95'>
          </Column>
          <OpLE isNormalized='1'>
            <NumericVal scale='0'>
              19
            </NumericVal>
          </OpLE>
        </Range>
      </RangePredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O113' name='SYS_A1'>
        <Column id='C95' tableId='T2' colIndex='0' isNullable='0'>
          QRP_CAT.SCH.T4.A
        </Column>
      </Output>
      <Output id='O116' name='B'>
        <Column id='C96' tableId='T2' colIndex='1'>
          QRP_CAT.SCH.T4.B
        </Column>
      </Output>
      <Output id='O119' name='C'>
        <Column id='C97' tableId='T2' colIndex='2'>
          QRP_CAT.SCH.T4.C
        </Column>
      </Output>
      <Output id='O122' name='SYS_D2'>
        <Column id='C102' tableId='T1' colIndex='0' isNullable='0'>
          QRP_CAT.SCH.T2.D
        </Column>
      </Output>
    </OutputList>
  </JBB>
</MV>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        

--- 1 row(s) selected.
>>
>>set param ?mvName 'MV3';
>>set param ?mvSchemaName $$MVSchemaName$$;
>>execute getMVDescFromTEXT;

SEQUENCE_NUM  MV_DESCRIPTOR_TEXT
------------  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

           0  <MV>
  <Version>
    1.0
  </Version>
  <Table>
    QRP_CAT.SCH.MV3
  </Table>
  <Misc isIncremental='1' isImmediate='1'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T2' TS='212222176840249210' isKeyCovered='1' numCols='3' rangeBits='00000008'>
          QRP_CAT.SCH.T4
        </Table>
        <Table id='T1' TS='212222176844026469' isKeyCovered='1' numCols='3'>
          QRP_CAT.SCH.T2
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J113'>
          <Column ref='C93'>
          </Column>
          <Column id='C101' tableId='T1' colIndex='2'>
            QRP_CAT.SCH.T2.F
          </Column>
        </JoinPred>
      </JoinPredList>
      <RangePredList>
        <Range id='R134' sqlType='INTEGER SIGNED NO NULLS'>
          <Column ref='C92'>
          </Column>
          <OpLE isNormalized='1'>
            <NumericVal scale='0'>
              19
            </NumericVal>
          </OpLE>
        </Range>
      </RangePredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O110' name='SYS_A1'>
        <Column id='C92' tableId='T2' colIndex='0' isNullable='0'>
          QRP_CAT.SCH.T4.A
        </Column>
      </Output>
      <Output id='O113' name='B'>
        <Column id='C93' tableId='T2' colIndex='1'>
          QRP_CAT.SCH.T4.B
        </Column>
      </Output>
      <Output id='O119' name='SYS_D2'>
        <Column id='C99' tableId='T1' colIndex='0' isNullable='0'>
          QRP_CAT.SCH.T2.D
        </Column>
      </Output>
    </OutputList>
  </JBB>
</MV>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              

--- 1 row(s) selected.
>>
>>set param ?mvName 'MV5';
>>set param ?mvSchemaName $$MVSchemaName$$;
>>execute getMVDescFromTEXT;

SEQUENCE_NUM  MV_DESCRIPTOR_TEXT
------------  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

           0  <MV>
  <Version>
    1.0
  </Version>
  <Table>
    QRP_CAT.SCH.MV5
  </Table>
  <Misc isIncremental='0'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T2' TS='212222176849876481' numCols='3' rangeBits='00000008'>
          QRP_CAT.SCH.T4
        </Table>
        <Table id='T1' TS='212222176849876481' numCols='3'>
          QRP_CAT.SCH.T2
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J90'>
          <Column ref='C69'>
          </Column>
          <Column id='C77' tableId='T1' colIndex='2'>
            QRP_CAT.SCH.T2.F
          </Column>
        </JoinPred>
      </JoinPredList>
      <RangePredList>
        <Range id='R111' sqlType='INTEGER SIGNED NO NULLS'>
          <Column id='C68' tableId='T2' colIndex='0' isNullable='0'>
            QRP_CAT.SCH.T4.A
          </Column>
          <OpLE isNormalized='1'>
            <NumericVal scale='0'>
              19
            </NumericVal>
          </OpLE>
        </Range>
      </RangePredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O85' name='MAX_C'>
        <Expr id='X85'>
          <Function id='S85' op='max' aggregateFunc='5'>
            <Column id='C70' tableId='T2' colIndex='2'>
              QRP_CAT.SCH.T4.C
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O90' name='B'>
        <Column ref='C69'>
        </Column>
      </Output>
    </OutputList>
    <GroupBy id='G3'>
      <Primary>
        <Column id='C69' tableId='T2' colIndex='1'>
          QRP_CAT.SCH.T4.B
        </Column>
      </Primary>
    </GroupBy>
  </JBB>
</MV>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                

--- 1 row(s) selected.
>>
>>-- expected MV6 to change even though it is not
>>-- directly using the table. The mv is using a 
>>-- view on the table instead.
>>set param ?mvName 'MV5';
>>set param ?mvSchemaName $$MVSchemaName$$;
>>execute getMVDescFromTEXT;

SEQUENCE_NUM  MV_DESCRIPTOR_TEXT
------------  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

           0  <MV>
  <Version>
    1.0
  </Version>
  <Table>
    QRP_CAT.SCH.MV5
  </Table>
  <Misc isIncremental='0'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T2' TS='212222176849876481' numCols='3' rangeBits='00000008'>
          QRP_CAT.SCH.T4
        </Table>
        <Table id='T1' TS='212222176849876481' numCols='3'>
          QRP_CAT.SCH.T2
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J90'>
          <Column ref='C69'>
          </Column>
          <Column id='C77' tableId='T1' colIndex='2'>
            QRP_CAT.SCH.T2.F
          </Column>
        </JoinPred>
      </JoinPredList>
      <RangePredList>
        <Range id='R111' sqlType='INTEGER SIGNED NO NULLS'>
          <Column id='C68' tableId='T2' colIndex='0' isNullable='0'>
            QRP_CAT.SCH.T4.A
          </Column>
          <OpLE isNormalized='1'>
            <NumericVal scale='0'>
              19
            </NumericVal>
          </OpLE>
        </Range>
      </RangePredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O85' name='MAX_C'>
        <Expr id='X85'>
          <Function id='S85' op='max' aggregateFunc='5'>
            <Column id='C70' tableId='T2' colIndex='2'>
              QRP_CAT.SCH.T4.C
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O90' name='B'>
        <Column ref='C69'>
        </Column>
      </Output>
    </OutputList>
    <GroupBy id='G3'>
      <Primary>
        <Column id='C69' tableId='T2' colIndex='1'>
          QRP_CAT.SCH.T4.B
        </Column>
      </Primary>
    </GroupBy>
  </JBB>
</MV>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                

--- 1 row(s) selected.
>>
>>-- the mv descriptor for MV7 should not
>>-- change since it is not directly defined  on t1
>>set param ?mvName 'MV7';
>>set param ?mvSchemaName $$MVSchemaName$$;
>>execute getMVDescFromTEXT;

SEQUENCE_NUM  MV_DESCRIPTOR_TEXT
------------  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

           0  <MV>
  <Version>
    1.0
  </Version>
  <Table>
    QRP_CAT.SCH.MV7
  </Table>
  <Misc isIncremental='1'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T2' TS='212222176849876481' numCols='3' rangeBits='00000008'>
          QRP_CAT.SCH.T1
        </Table>
        <Table id='T1' TS='212222176849876481' numCols='3'>
          QRP_CAT.SCH.T2
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J85'>
          <Column ref='C63'>
          </Column>
          <Column id='C70' tableId='T1' colIndex='2'>
            QRP_CAT.SCH.T2.F
          </Column>
        </JoinPred>
      </JoinPredList>
      <RangePredList>
        <Range id='R106' sqlType='INTEGER SIGNED NO NULLS'>
          <Column id='C62' tableId='T2' colIndex='0' isNullable='0'>
            QRP_CAT.SCH.T1.A
          </Column>
          <OpLE isNormalized='1'>
            <NumericVal scale='0'>
              19
            </NumericVal>
          </OpLE>
        </Range>
      </RangePredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O78' name='CC'>
        <Expr id='X78'>
          <Function id='S78' op='count_nonull' aggregateFunc='2'>
            <Column id='C64' tableId='T2' colIndex='2'>
              QRP_CAT.SCH.T1.C
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O80' name='SYS_COUNTSTAR1'>
        <Expr id='X80'>
          <Function id='S80' op='count' aggregateFunc='1'>
            <NumericVal id='S79' scale='0'>
              1 
            </NumericVal>
          </Function>
        </Expr>
      </Output>
      <Output id='O85' name='B'>
        <Column ref='C63'>
        </Column>
      </Output>
    </OutputList>
    <GroupBy id='G3'>
      <Primary>
        <Column id='C63' tableId='T2' colIndex='1'>
          QRP_CAT.SCH.T1.B
        </Column>
      </Primary>
    </GroupBy>
  </JBB>
</MV>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            

--- 1 row(s) selected.
>>
>>
>>-- test view name change on the MV, for now
>>-- we can only have views as base tables for
>>-- RECOMPUTE Mvs
>>alter view t1_v rename to t4_v;

--- SQL operation complete.
>>
>>-- check contents of REWRITE_PUBLISH table
>>-- no rows should be added
>>execute getMVRP;

OBJECT_NAME                           OBJECT_NEW_NAME                       D_I          O_T  I_C  REFRESH_AT
------------------------------------  ------------------------------------  -----------  ---  ---  ----------

QRP_CAT.SCH.MV1_R                     ?                                              -2  RP   0    NONZERO   
QRP_CAT.SCH.MV2                       ?                                              -2  RP   0    NONZERO   
QRP_CAT.SCH.MV3                       ?                                              -2  RP   0    NONZERO   
QRP_CAT.SCH.MV4                       ?                                              -2  RP   0    NONZERO   
QRP_CAT.SCH.MV5                       ?                                              -2  RP   0    NONZERO   
QRP_CAT.SCH.MV6                       ?                                              -2  RP   0    NONZERO   

--- 6 row(s) selected.
>>
>>set param ?mvName 'MV6';
>>set param ?mvSchemaName $$MVSchemaName$$;
>>-- check the MV descriptor text
>>-- expected no change to the descriptor
>>-- because of the view rename
>>execute getMVDescFromTEXT;

SEQUENCE_NUM  MV_DESCRIPTOR_TEXT
------------  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

           0  <MV>
  <Version>
    1.0
  </Version>
  <Table>
    QRP_CAT.SCH.MV6
  </Table>
  <Misc isIncremental='0'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T2' TS='212222176849876481' numCols='3' rangeBits='00000008'>
          QRP_CAT.SCH.T4
        </Table>
        <Table id='T1' TS='212222176849876481' numCols='3'>
          QRP_CAT.SCH.T2
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J90'>
          <Column ref='C69'>
          </Column>
          <Column id='C77' tableId='T1' colIndex='2'>
            QRP_CAT.SCH.T2.F
          </Column>
        </JoinPred>
      </JoinPredList>
      <RangePredList>
        <Range id='R111' sqlType='INTEGER SIGNED NO NULLS'>
          <Column id='C68' tableId='T2' colIndex='0' isNullable='0'>
            QRP_CAT.SCH.T4.A
          </Column>
          <OpLE isNormalized='1'>
            <NumericVal scale='0'>
              19
            </NumericVal>
          </OpLE>
        </Range>
      </RangePredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O85' name='MAX_C'>
        <Expr id='X85'>
          <Function id='S85' op='max' aggregateFunc='5'>
            <Column id='C70' tableId='T2' colIndex='2'>
              QRP_CAT.SCH.T4.C
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O90' name='B'>
        <Column ref='C69'>
        </Column>
      </Output>
    </OutputList>
    <GroupBy id='G3'>
      <Primary>
        <Column id='C69' tableId='T2' colIndex='1'>
          QRP_CAT.SCH.T4.B
        </Column>
      </Primary>
    </GroupBy>
  </JBB>
</MV>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                

--- 1 row(s) selected.
>>
>>-- Run QMP and clean up the PUBLISH table
>>obey TEST001(run_qmp);
>>----------------------------------------------------
>>----------------------------------------------------
>>
>>-- Run QMP
>>sh sh -c "$QMP -target FILE qmpLog.log";
>>log;
<Publish TS='212222177021125915'>
  <MVName>
    QRP_CAT.SCH.MV1_R
  </MVName>
  <Update op='Alter' hasIgnoreChanges='0'>
  </Update>
  <Update op='Refresh' TS='212222176971291316'>
  </Update>
    <MV>
  <Version>
    1.0
  </Version>
  <Table>
    QRP_CAT.SCH.MV1_R
  </Table>
  <Misc isIncremental='1'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T2' TS='212222176832203190' isKeyCovered='1' numCols='3' rangeBits='00000008'>
          QRP_CAT.SCH.T4
        </Table>
        <Table id='T1' TS='212222176834226175' isKeyCovered='1' numCols='3'>
          QRP_CAT.SCH.T2
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J116'>
          <Column ref='C96'>
          </Column>
          <Column id='C104' tableId='T1' colIndex='2'>
            QRP_CAT.SCH.T2.F
          </Column>
        </JoinPred>
      </JoinPredList>
      <RangePredList>
        <Range id='R137' sqlType='INTEGER SIGNED NO NULLS'>
          <Column ref='C95'>
          </Column>
          <OpLE isNormalized='1'>
            <NumericVal scale='0'>
              19
            </NumericVal>
          </OpLE>
        </Range>
      </RangePredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O113' name='SYS_A1'>
        <Column id='C95' tableId='T2' colIndex='0' isNullable='0'>
          QRP_CAT.SCH.T4.A
        </Column>
      </Output>
      <Output id='O116' name='B'>
        <Column id='C96' tableId='T2' colIndex='1'>
          QRP_CAT.SCH.T4.B
        </Column>
      </Output>
      <Output id='O119' name='C'>
        <Column id='C97' tableId='T2' colIndex='2'>
          QRP_CAT.SCH.T4.C
        </Column>
      </Output>
      <Output id='O122' name='SYS_D2'>
        <Column id='C102' tableId='T1' colIndex='0' isNullable='0'>
          QRP_CAT.SCH.T2.D
        </Column>
      </Output>
    </OutputList>
  </JBB>
</MV>
</Publish>
<Publish TS='212222177021125915'>
  <MVName>
    QRP_CAT.SCH.MV2
  </MVName>
  <Update op='Alter' hasIgnoreChanges='0'>
  </Update>
  <Update op='Refresh' TS='212222177001775639'>
  </Update>
    <MV>
  <Version>
    1.0
  </Version>
  <Table>
    QRP_CAT.SCH.MV2
  </Table>
  <Misc isIncremental='1'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T2' TS='212222176840249210' numCols='3' rangeBits='00000008'>
          QRP_CAT.SCH.T4
        </Table>
        <Table id='T1' TS='212222176840249210' numCols='3'>
          QRP_CAT.SCH.T2
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J102'>
          <Column id='C78' tableId='T2' colIndex='2'>
            QRP_CAT.SCH.T4.C
          </Column>
          <Column id='C84' tableId='T1' colIndex='1'>
            QRP_CAT.SCH.T2.E
          </Column>
        </JoinPred>
      </JoinPredList>
      <RangePredList>
        <Range id='R120' sqlType='INTEGER SIGNED NO NULLS'>
          <Column id='C76' tableId='T2' colIndex='0' isNullable='0'>
            QRP_CAT.SCH.T4.A
          </Column>
          <OpLE isNormalized='1'>
            <NumericVal scale='0'>
              19
            </NumericVal>
          </OpLE>
        </Range>
      </RangePredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O94' name='CNT'>
        <Expr id='X94'>
          <Function id='S94' op='count' aggregateFunc='1'>
            <NumericVal id='S93' scale='0'>
              1 
            </NumericVal>
          </Function>
        </Expr>
      </Output>
      <Output id='O99' name='B'>
        <Column ref='C77'>
        </Column>
      </Output>
    </OutputList>
    <GroupBy id='G3'>
      <Primary>
        <Column id='C77' tableId='T2' colIndex='1'>
          QRP_CAT.SCH.T4.B
        </Column>
      </Primary>
    </GroupBy>
  </JBB>
</MV>
</Publish>
<Publish TS='212222177021125915'>
  <MVName>
    QRP_CAT.SCH.MV3
  </MVName>
  <Update op='Alter' hasIgnoreChanges='0'>
  </Update>
  <Update op='Refresh' TS='212222176902349277'>
  </Update>
    <MV>
  <Version>
    1.0
  </Version>
  <Table>
    QRP_CAT.SCH.MV3
  </Table>
  <Misc isIncremental='1' isImmediate='1'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T2' TS='212222176840249210' isKeyCovered='1' numCols='3' rangeBits='00000008'>
          QRP_CAT.SCH.T4
        </Table>
        <Table id='T1' TS='212222176844026469' isKeyCovered='1' numCols='3'>
          QRP_CAT.SCH.T2
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J113'>
          <Column ref='C93'>
          </Column>
          <Column id='C101' tableId='T1' colIndex='2'>
            QRP_CAT.SCH.T2.F
          </Column>
        </JoinPred>
      </JoinPredList>
      <RangePredList>
        <Range id='R134' sqlType='INTEGER SIGNED NO NULLS'>
          <Column ref='C92'>
          </Column>
          <OpLE isNormalized='1'>
            <NumericVal scale='0'>
              19
            </NumericVal>
          </OpLE>
        </Range>
      </RangePredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O110' name='SYS_A1'>
        <Column id='C92' tableId='T2' colIndex='0' isNullable='0'>
          QRP_CAT.SCH.T4.A
        </Column>
      </Output>
      <Output id='O113' name='B'>
        <Column id='C93' tableId='T2' colIndex='1'>
          QRP_CAT.SCH.T4.B
        </Column>
      </Output>
      <Output id='O119' name='SYS_D2'>
        <Column id='C99' tableId='T1' colIndex='0' isNullable='0'>
          QRP_CAT.SCH.T2.D
        </Column>
      </Output>
    </OutputList>
  </JBB>
</MV>
</Publish>
<Publish TS='212222177021125915'>
  <MVName>
    QRP_CAT.SCH.MV4
  </MVName>
  <Update op='Alter' hasIgnoreChanges='0'>
  </Update>
  <Update op='Refresh' TS='212222176849876481'>
  </Update>
    <MV>
  <Version>
    1.0
  </Version>
  <Table>
    QRP_CAT.SCH.MV4
  </Table>
  <Misc isIncremental='1' isImmediate='1'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T2' TS='212222176840249210' isKeyCovered='1' numCols='3' rangeBits='00000008'>
          QRP_CAT.SCH.T4
        </Table>
        <Table id='T1' TS='212222176844026469' isKeyCovered='1' numCols='3'>
          QRP_CAT.SCH.T2
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J113'>
          <Column ref='C93'>
          </Column>
          <Column id='C101' tableId='T1' colIndex='2'>
            QRP_CAT.SCH.T2.F
          </Column>
        </JoinPred>
      </JoinPredList>
      <RangePredList>
        <Range id='R134' sqlType='INTEGER SIGNED NO NULLS'>
          <Column ref='C92'>
          </Column>
          <OpLE isNormalized='1'>
            <NumericVal scale='0'>
              19
            </NumericVal>
          </OpLE>
        </Range>
      </RangePredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O110' name='SYS_A1'>
        <Column id='C92' tableId='T2' colIndex='0' isNullable='0'>
          QRP_CAT.SCH.T4.A
        </Column>
      </Output>
      <Output id='O113' name='B'>
        <Column id='C93' tableId='T2' colIndex='1'>
          QRP_CAT.SCH.T4.B
        </Column>
      </Output>
      <Output id='O119' name='SYS_D2'>
        <Column id='C99' tableId='T1' colIndex='0' isNullable='0'>
          QRP_CAT.SCH.T2.D
        </Column>
      </Output>
    </OutputList>
  </JBB>
</MV>
</Publish>
<Publish TS='212222177021125915'>
  <MVName>
    QRP_CAT.SCH.MV5
  </MVName>
  <Update op='Alter' hasIgnoreChanges='0'>
  </Update>
  <Update op='Refresh' TS='212222176907551321'>
  </Update>
    <MV>
  <Version>
    1.0
  </Version>
  <Table>
    QRP_CAT.SCH.MV5
  </Table>
  <Misc isIncremental='0'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T2' TS='212222176849876481' numCols='3' rangeBits='00000008'>
          QRP_CAT.SCH.T4
        </Table>
        <Table id='T1' TS='212222176849876481' numCols='3'>
          QRP_CAT.SCH.T2
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J90'>
          <Column ref='C69'>
          </Column>
          <Column id='C77' tableId='T1' colIndex='2'>
            QRP_CAT.SCH.T2.F
          </Column>
        </JoinPred>
      </JoinPredList>
      <RangePredList>
        <Range id='R111' sqlType='INTEGER SIGNED NO NULLS'>
          <Column id='C68' tableId='T2' colIndex='0' isNullable='0'>
            QRP_CAT.SCH.T4.A
          </Column>
          <OpLE isNormalized='1'>
            <NumericVal scale='0'>
              19
            </NumericVal>
          </OpLE>
        </Range>
      </RangePredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O85' name='MAX_C'>
        <Expr id='X85'>
          <Function id='S85' op='max' aggregateFunc='5'>
            <Column id='C70' tableId='T2' colIndex='2'>
              QRP_CAT.SCH.T4.C
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O90' name='B'>
        <Column ref='C69'>
        </Column>
      </Output>
    </OutputList>
    <GroupBy id='G3'>
      <Primary>
        <Column id='C69' tableId='T2' colIndex='1'>
          QRP_CAT.SCH.T4.B
        </Column>
      </Primary>
    </GroupBy>
  </JBB>
</MV>
</Publish>
<Publish TS='212222177021125915'>
  <MVName>
    QRP_CAT.SCH.MV6
  </MVName>
  <Update op='Alter' hasIgnoreChanges='0'>
  </Update>
  <Update op='Refresh' TS='212222176853715386'>
  </Update>
    <MV>
  <Version>
    1.0
  </Version>
  <Table>
    QRP_CAT.SCH.MV6
  </Table>
  <Misc isIncremental='0'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T2' TS='212222176849876481' numCols='3' rangeBits='00000008'>
          QRP_CAT.SCH.T4
        </Table>
        <Table id='T1' TS='212222176849876481' numCols='3'>
          QRP_CAT.SCH.T2
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J90'>
          <Column ref='C69'>
          </Column>
          <Column id='C77' tableId='T1' colIndex='2'>
            QRP_CAT.SCH.T2.F
          </Column>
        </JoinPred>
      </JoinPredList>
      <RangePredList>
        <Range id='R111' sqlType='INTEGER SIGNED NO NULLS'>
          <Column id='C68' tableId='T2' colIndex='0' isNullable='0'>
            QRP_CAT.SCH.T4.A
          </Column>
          <OpLE isNormalized='1'>
            <NumericVal scale='0'>
              19
            </NumericVal>
          </OpLE>
        </Range>
      </RangePredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O85' name='MAX_C'>
        <Expr id='X85'>
          <Function id='S85' op='max' aggregateFunc='5'>
            <Column id='C70' tableId='T2' colIndex='2'>
              QRP_CAT.SCH.T4.C
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O90' name='B'>
        <Column ref='C69'>
        </Column>
      </Output>
    </OutputList>
    <GroupBy id='G3'>
      <Primary>
        <Column id='C69' tableId='T2' colIndex='1'>
          QRP_CAT.SCH.T4.B
        </Column>
      </Primary>
    </GroupBy>
  </JBB>
</MV>
</Publish>
>>
>>-- clean up the PUBLISH table
>>delete from manageability.mv_rewrite.rewrite_publish;

--- 0 row(s) deleted.
>>
>>
>>obey TEST001(ignore_changes);
>>----------------------------------------------------
>>------------- ignore changes section ---------------
>>-- test altering the ignore changes clause of an MV
>>---- The test will:
>>------- make sure that a row is inserted into the
>>--        REWRITE_PUBLISH PUBLISH table after
>>--        an MV ignore changes clause is altered
>>--      make sure that the the correct state is 
>>--        included in the (empty ignore changes
>>--        list or not)
>>----------------------------------------------------
>>
>>
>>-- add a base table to the ignore changes list of
>>-- an mv
>>alter mv mv2 add ignore changes on t2;

--- SQL operation complete.
>>
>>-- change the table in the ignore changes list and
>>-- refresh the mv
>>delete from t2 where f = 2;

--- 2 row(s) deleted.
>>refresh mv2;

--- SQL operation complete.
>>
>>-- check contents of REWRITE_PUBLISH table
>>execute getMVRP;

OBJECT_NAME                           OBJECT_NEW_NAME                       D_I          O_T  I_C  REFRESH_AT
------------------------------------  ------------------------------------  -----------  ---  ---  ----------

QRP_CAT.SCH.MV2                       ?                                               ?  AI   1    NONZERO   
QRP_CAT.SCH.MV2                       ?                                               ?  RF   ?    NONZERO   

--- 2 row(s) selected.
>>
>>-- mv with a non-empty ignore changes list
>>create mv mv9 refresh on request 
+>  ignore changes on t2,t3
+>  initialize on create
+>  enable query rewrite
+>  as select b from t4,t2,t3
+>  where  a < 20 and b = f and f = h;

*** WARNING[12112] A secondary index QRP_CAT.SCH.MV9_933163112_7831 was created for the materialized view.

*** WARNING[12112] A secondary index QRP_CAT.SCH.MV9_644163112_7831 was created for the materialized view.

--- SQL operation completed with warnings.
>>
>>-- remove a table from ignore changes list
>>-- but the IG list still not empty
>>alter mv mv9 remove ignore changes on t2;

--- SQL operation complete.
>>
>>-- remove a table from ignore changes list
>>-- list becomes empty
>>alter mv mv9 remove ignore changes on t3;

--- SQL operation complete.
>>
>>
>>-- now add one table to the ignore changes list
>>-- so the IG list is not empty
>>alter mv mv9 add ignore changes on t2;

--- SQL operation complete.
>>
>>-- check contents of REWRITE_PUBLISH table
>>execute getMVRP;

OBJECT_NAME                           OBJECT_NEW_NAME                       D_I          O_T  I_C  REFRESH_AT
------------------------------------  ------------------------------------  -----------  ---  ---  ----------

QRP_CAT.SCH.MV2                       ?                                               ?  AI   1    NONZERO   
QRP_CAT.SCH.MV2                       ?                                               ?  RF   ?    NONZERO   
QRP_CAT.SCH.MV9                       ?                                              -2  CR   1    NONZERO   
QRP_CAT.SCH.MV9                       ?                                               ?  AI   1    NONZERO   
QRP_CAT.SCH.MV9                       ?                                               ?  AI   0    NONZERO   
QRP_CAT.SCH.MV9                       ?                                               ?  AI   1    NONZERO   

--- 6 row(s) selected.
>>
>>-- Run QMP and clean up the PUBLISH table
>>obey TEST001(run_qmp);
>>----------------------------------------------------
>>----------------------------------------------------
>>
>>-- Run QMP
>>sh sh -c "$QMP -target FILE qmpLog.log";
>>log;
<Publish TS='212222177025816041'>
  <MVName>
    QRP_CAT.SCH.MV2
  </MVName>
  <Update op='Alter' hasIgnoreChanges='1'>
  </Update>
</Publish>
<Publish TS='212222177025816041'>
  <MVName>
    QRP_CAT.SCH.MV2
  </MVName>
  <Update op='Refresh' TS='212222177031346847'>
  </Update>
</Publish>
<Publish TS='212222177039460127'>
  <MVName>
    QRP_CAT.SCH.MV9
  </MVName>
  <Update op='Refresh' TS='212222177039460127'>
  </Update>
  <Update op='Alter' hasIgnoreChanges='1'>
  </Update>
    <MV>
  <Version>
    1.0
  </Version>
  <Table>
    QRP_CAT.SCH.MV9
  </Table>
  <Misc isIncremental='1'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T2' TS='212222177025816041' isKeyCovered='1' numCols='3'>
          QRP_CAT.SCH.T2
        </Table>
        <Table id='T1' TS='212222176834944180' isKeyCovered='1' numCols='2'>
          QRP_CAT.SCH.T3
        </Table>
        <Table id='T3' TS='212222177023763987' isKeyCovered='1' numCols='3' rangeBits='00000008'>
          QRP_CAT.SCH.T4
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J141'>
          <Column id='C123' tableId='T2' colIndex='2'>
            QRP_CAT.SCH.T2.F
          </Column>
          <Column id='C129' tableId='T1' colIndex='1'>
            QRP_CAT.SCH.T3.H
          </Column>
          <Column ref='C115'>
          </Column>
        </JoinPred>
      </JoinPredList>
      <RangePredList>
        <Range id='R171' sqlType='INTEGER SIGNED NO NULLS'>
          <Column ref='C114'>
          </Column>
          <OpLE isNormalized='1'>
            <NumericVal scale='0'>
              19
            </NumericVal>
          </OpLE>
        </Range>
      </RangePredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O138' name='SYS_A1'>
        <Column id='C114' tableId='T3' colIndex='0' isNullable='0'>
          QRP_CAT.SCH.T4.A
        </Column>
      </Output>
      <Output id='O141' name='B'>
        <Column id='C115' tableId='T3' colIndex='1'>
          QRP_CAT.SCH.T4.B
        </Column>
      </Output>
      <Output id='O147' name='SYS_D2'>
        <Column id='C121' tableId='T2' colIndex='0' isNullable='0'>
          QRP_CAT.SCH.T2.D
        </Column>
      </Output>
      <Output id='O156' name='SYS_G3'>
        <Column id='C128' tableId='T1' colIndex='0' isNullable='0'>
          QRP_CAT.SCH.T3.G
        </Column>
      </Output>
    </OutputList>
  </JBB>
</MV>
</Publish>
<Publish TS='212222177046462456'>
  <MVName>
    QRP_CAT.SCH.MV9
  </MVName>
  <Update op='Alter' hasIgnoreChanges='1'>
  </Update>
</Publish>
<Publish TS='212222177047372468'>
  <MVName>
    QRP_CAT.SCH.MV9
  </MVName>
  <Update op='Alter' hasIgnoreChanges='0'>
  </Update>
</Publish>
<Publish TS='212222177048557516'>
  <MVName>
    QRP_CAT.SCH.MV9
  </MVName>
  <Update op='Alter' hasIgnoreChanges='1'>
  </Update>
</Publish>
>>
>>-- clean up the PUBLISH table
>>delete from manageability.mv_rewrite.rewrite_publish;

--- 0 row(s) deleted.
>>
>>
>>obey TEST001(commit_refresh_each);
>>----------------------------------------------------
>>---------- commit refresh each section -------------
>>-- test altering the commit refresh each clause 
>>-- of an MV
>>---- The test will:
>>------- make sure that a row is inserted into the
>>--        REWRITE_PUBLISH PUBLISH table after
>>--        an MV commit refresh each clause is 
>>--        altered
>>----------------------------------------------------
>>
>>-- mv2 is a self-maintainable mv so it can be 
>>-- refreshed in multiple transactions
>>
>>alter mv mv2 mvattributes commit refresh each 2;

--- SQL operation complete.
>>
>>refresh mv2 outfile REFRESH.LOG;

--- SQL operation complete.
>>log;

The materialized view QRP_CAT.SCH.MV2 is being refreshed in multiple transactions...

The materialized view QRP_CAT.SCH.MV2 was found up to date
Starting the log cleanup of table QRP_CAT.SCH.T4...

Finished the log cleanup of table QRP_CAT.SCH.T4.


>>
>>-- check contents of REWRITE_PUBLISH table
>>execute getMVRP;

OBJECT_NAME                           OBJECT_NEW_NAME                       D_I          O_T  I_C  REFRESH_AT
------------------------------------  ------------------------------------  -----------  ---  ---  ----------

QRP_CAT.SCH.MV2                       ?                                               ?  MT   ?    NONZERO   
QRP_CAT.SCH.MV2                       ?                                               ?  RF   ?    NONZERO   

--- 2 row(s) selected.
>>
>>-- Run QMP and clean up the PUBLISH table
>>obey TEST001(run_qmp);
>>----------------------------------------------------
>>----------------------------------------------------
>>
>>-- Run QMP
>>sh sh -c "$QMP -target FILE qmpLog.log";
>>log;
<Publish TS='212222177050960417'>
  <MVName>
    QRP_CAT.SCH.MV2
  </MVName>
</Publish>
<Publish TS='212222177050960417'>
  <MVName>
    QRP_CAT.SCH.MV2
  </MVName>
  <Update op='Refresh' TS='212222177055195519'>
  </Update>
</Publish>
>>
>>-- clean up the PUBLISH table
>>delete from manageability.mv_rewrite.rewrite_publish;

--- 0 row(s) deleted.
>>
>>
>>obey TEST001(rewrite_enabled);
>>----------------------------------------------------
>>------------- rewrite enabled section --------------
>>-- test altering the rewrite enabled mv attribute
>>---- The test will:
>>------- make sure that a row is inserted into the
>>--        REWRITE_PUBLISH PUBLISH table after
>>--        an MV query rewrite attribute is 
>>--        changed
>>--      make sure that publishing to the PUBLISH
>>--        is turned OFF when the query rewrite 
>>--        attribute is disabled
>>----------------------------------------------------
>>
>>-- create an mv with query rewrite enabled
>>-- all ddl and refresh operations on the mv
>>-- will be published
>>set param ?fullMVName 'QRP_CAT.SCH.MV8';
>>create mv mv8 refresh on request initialize on refresh
+>  enable query rewrite
+>  as select b, max(c) max_c from t4,t2
+>  where  a < 20 and b = f
+>  group by b;

*** WARNING[12112] A secondary index QRP_CAT.SCH.MV8_685363112_7831 was created for the materialized view.

--- SQL operation completed with warnings.
>>
>>alter mv mv8 add ignore changes on t2;

--- SQL operation complete.
>>alter mv mv8 remove ignore changes on t2;

--- SQL operation complete.
>>
>>-- check the contents of the published table 
>>execute getMVRP_PERMV;

OBJECT_NAME                           OBJECT_NEW_NAME                       D_I          O_T  I_C  REFRESH_AT
------------------------------------  ------------------------------------  -----------  ---  ---  ----------

QRP_CAT.SCH.MV8                       ?                                              -2  CT   0    0         
QRP_CAT.SCH.MV8                       ?                                               ?  AI   1    0         
QRP_CAT.SCH.MV8                       ?                                               ?  AI   0    0         

--- 3 row(s) selected.
>>
>>-- disable the mv query rewrite attribute
>>-- should publish a row then turn off publishing
>>alter mv mv8 disable query rewrite;

--- SQL operation complete.
>>
>>-- check the contents of the published table 
>>execute getMVRP_PERMV;

OBJECT_NAME                           OBJECT_NEW_NAME                       D_I          O_T  I_C  REFRESH_AT
------------------------------------  ------------------------------------  -----------  ---  ---  ----------

QRP_CAT.SCH.MV8                       ?                                              -2  CT   0    0         
QRP_CAT.SCH.MV8                       ?                                               ?  AI   1    0         
QRP_CAT.SCH.MV8                       ?                                               ?  AI   0    0         
QRP_CAT.SCH.MV8                       ?                                               ?  DP   ?    0         

--- 4 row(s) selected.
>>
>>-- these two events should not be published
>>alter mv mv8 add ignore changes on t2;

--- SQL operation complete.
>>alter mv mv8 remove ignore changes on t2;

--- SQL operation complete.
>>
>>-- check the contents of the published table 
>>execute getMVRP_PERMV;

OBJECT_NAME                           OBJECT_NEW_NAME                       D_I          O_T  I_C  REFRESH_AT
------------------------------------  ------------------------------------  -----------  ---  ---  ----------

QRP_CAT.SCH.MV8                       ?                                              -2  CT   0    0         
QRP_CAT.SCH.MV8                       ?                                               ?  AI   1    0         
QRP_CAT.SCH.MV8                       ?                                               ?  AI   0    0         
QRP_CAT.SCH.MV8                       ?                                               ?  DP   ?    0         

--- 4 row(s) selected.
>>
>>-- reset the mv query rewrite enable attribute
>>-- should publish a row and enable publishing
>>-- the first row should include the mv 
>>-- descriptor index
>>alter mv mv8 enable query rewrite;

--- SQL operation complete.
>>
>>alter mv mv8 add ignore changes on t2;

--- SQL operation complete.
>>alter mv mv8 remove ignore changes on t2;

--- SQL operation complete.
>>
>>drop mv mv8;

--- SQL operation complete.
>>
>>-- check the contents of the published table 
>>execute getMVRP_PERMV;

OBJECT_NAME                           OBJECT_NEW_NAME                       D_I          O_T  I_C  REFRESH_AT
------------------------------------  ------------------------------------  -----------  ---  ---  ----------

QRP_CAT.SCH.MV8                       ?                                              -2  CT   0    0         
QRP_CAT.SCH.MV8                       ?                                               ?  AI   1    0         
QRP_CAT.SCH.MV8                       ?                                               ?  AI   0    0         
QRP_CAT.SCH.MV8                       ?                                               ?  DP   ?    0         
QRP_CAT.SCH.MV8                       ?                                              -2  RP   0    0         
QRP_CAT.SCH.MV8                       ?                                               ?  AI   1    0         
QRP_CAT.SCH.MV8                       ?                                               ?  AI   0    0         
QRP_CAT.SCH.MV8                       ?                                               ?  DP   ?    0         

--- 8 row(s) selected.
>>
>>-- Run QMP and clean up the PUBLISH table
>>obey TEST001(run_qmp);
>>----------------------------------------------------
>>----------------------------------------------------
>>
>>-- Run QMP
>>sh sh -c "$QMP -target FILE qmpLog.log";
>>log;
<Publish TS='212222177059731735'>
  <MVName>
    QRP_CAT.SCH.MV8
  </MVName>
    
</Publish>
<Publish TS='212222177061672713'>
  <MVName>
    QRP_CAT.SCH.MV8
  </MVName>
  <Update op='Alter' hasIgnoreChanges='1'>
  </Update>
</Publish>
<Publish TS='212222177062599629'>
  <MVName>
    QRP_CAT.SCH.MV8
  </MVName>
  <Update op='Alter' hasIgnoreChanges='0'>
  </Update>
</Publish>
<Publish TS='212222177063238566'>
  <MVName>
    QRP_CAT.SCH.MV8
  </MVName>
  <Update op='Drop'>
  </Update>
</Publish>
<Publish TS='212222177064402622'>
  <MVName>
    QRP_CAT.SCH.MV8
  </MVName>
  <Update op='Alter' hasIgnoreChanges='0'>
  </Update>
  <Update op='Refresh' TS='0'>
  </Update>
    
</Publish>
<Publish TS='212222177064796786'>
  <MVName>
    QRP_CAT.SCH.MV8
  </MVName>
  <Update op='Alter' hasIgnoreChanges='1'>
  </Update>
</Publish>
<Publish TS='212222177065497698'>
  <MVName>
    QRP_CAT.SCH.MV8
  </MVName>
  <Update op='Alter' hasIgnoreChanges='0'>
  </Update>
</Publish>
<Publish TS='212222177066126730'>
  <MVName>
    QRP_CAT.SCH.MV8
  </MVName>
  <Update op='Drop'>
  </Update>
</Publish>
>>
>>-- clean up the PUBLISH table
>>delete from manageability.mv_rewrite.rewrite_publish;

--- 0 row(s) deleted.
>>
>>
>>obey TEST001(drop_mv);
>>----------------------------------------------------
>>------------------ drop mv section -----------------
>>-- test the dropping of the different mv refresh
>>-- types: ON REQUEST, ON STATEMENT and RECOMPUTE
>>---- The test will:
>>------- make sure that a row is inserted into the
>>--        REWRITE_PUBLISH PUBLISH table after
>>--        dropping an MV
>>----------------------------------------------------
>>
>>-- drop mvs and make sure the event is published in the
>>-- PUBLISH table
>>drop mv mv7;

--- SQL operation complete.
>>drop mv mv1_r;

--- SQL operation complete.
>>
>>-- check contents of REWRITE_PUBLISH table
>>execute getMVRP;

OBJECT_NAME                           OBJECT_NEW_NAME                       D_I          O_T  I_C  REFRESH_AT
------------------------------------  ------------------------------------  -----------  ---  ---  ----------

QRP_CAT.SCH.MV7                       ?                                               ?  DP   ?    0         
QRP_CAT.SCH.MV1_R                     ?                                               ?  DP   ?    NONZERO   

--- 2 row(s) selected.
>>
>>drop mv mv2;

--- SQL operation complete.
>>
>>-- check contents of REWRITE_PUBLISH table
>>execute getMVRP;

OBJECT_NAME                           OBJECT_NEW_NAME                       D_I          O_T  I_C  REFRESH_AT
------------------------------------  ------------------------------------  -----------  ---  ---  ----------

QRP_CAT.SCH.MV7                       ?                                               ?  DP   ?    0         
QRP_CAT.SCH.MV1_R                     ?                                               ?  DP   ?    NONZERO   
QRP_CAT.SCH.MV2                       ?                                               ?  DP   ?    NONZERO   

--- 3 row(s) selected.
>>
>>drop mv mv3;

--- SQL operation complete.
>>drop mv mv4;

--- SQL operation complete.
>>drop mv mv5;

--- SQL operation complete.
>>drop mv mv6;

--- SQL operation complete.
>>drop mv mv7;

*** ERROR[1004] Object QRP_CAT.SCH.MV7 does not exist or object type is invalid for the current operation.

*** ERROR[1031] Object QRP_CAT.SCH.MV7 could not be dropped.

--- SQL operation failed with errors.
>>
>>-- check contents of REWRITE_PUBLISH table
>>execute getMVRP;

OBJECT_NAME                           OBJECT_NEW_NAME                       D_I          O_T  I_C  REFRESH_AT
------------------------------------  ------------------------------------  -----------  ---  ---  ----------

QRP_CAT.SCH.MV7                       ?                                               ?  DP   ?    0         
QRP_CAT.SCH.MV1_R                     ?                                               ?  DP   ?    NONZERO   
QRP_CAT.SCH.MV2                       ?                                               ?  DP   ?    NONZERO   
QRP_CAT.SCH.MV3                       ?                                               ?  DP   ?    NONZERO   
QRP_CAT.SCH.MV4                       ?                                               ?  DP   ?    NONZERO   
QRP_CAT.SCH.MV5                       ?                                               ?  DP   ?    NONZERO   
QRP_CAT.SCH.MV6                       ?                                               ?  DP   ?    NONZERO   

--- 7 row(s) selected.
>>
>>-- Run QMP and clean up the PUBLISH table
>>obey TEST001(run_qmp);
>>----------------------------------------------------
>>----------------------------------------------------
>>
>>-- Run QMP
>>sh sh -c "$QMP -target FILE qmpLog.log";
>>log;
<Publish TS='212222177068833701'>
  <MVName>
    QRP_CAT.SCH.MV7
  </MVName>
  <Update op='Drop'>
  </Update>
</Publish>
<Publish TS='212222177069611798'>
  <MVName>
    QRP_CAT.SCH.MV1_R
  </MVName>
  <Update op='Drop'>
  </Update>
</Publish>
<Publish TS='212222177070601768'>
  <MVName>
    QRP_CAT.SCH.MV2
  </MVName>
  <Update op='Drop'>
  </Update>
</Publish>
<Publish TS='212222177071655750'>
  <MVName>
    QRP_CAT.SCH.MV3
  </MVName>
  <Update op='Drop'>
  </Update>
</Publish>
<Publish TS='212222177074416102'>
  <MVName>
    QRP_CAT.SCH.MV4
  </MVName>
  <Update op='Drop'>
  </Update>
</Publish>
<Publish TS='212222177021125916'>
  <MVName>
    QRP_CAT.SCH.MV5
  </MVName>
  <Update op='Drop'>
  </Update>
</Publish>
<Publish TS='212222177021125916'>
  <MVName>
    QRP_CAT.SCH.MV6
  </MVName>
  <Update op='Drop'>
  </Update>
</Publish>
>>
>>-- clean up the PUBLISH table
>>delete from manageability.mv_rewrite.rewrite_publish;

--- 0 row(s) deleted.
>>
>>
>>obey TEST001(redescribe_mv);
>>----------------------------------------------------
>>---------- redescribe section ----------------------
>>-- test the redescribe tool, repeating match operations
>>-- before and after. Without a descriptor version change,
>>-- this does not really exercise the redescribe tool
>>-- completely.
>>-- In addition, mv renaming and ignore changes operations
>>-- are carried out to round out code coverage for these
>>-- features in QMS.
>>----------------------------------------------------
>>
>>control query default MVQR_PUBLISH_TO 'PRIVATE';

--- SQL operation complete.
>>
>>create table mvqr_001_t1 (
+>  a int not null
+>, b int not null
+>, c char(10)
+>, primary key(a)
+>);

--- SQL operation complete.
>>create table mvqr_001_t2 like mvqr_001_t1;

--- SQL operation complete.
>>insert into mvqr_001_t1 values(1,10,'abc'), (2,20,'def'), (3,20,'ghi');

--- 3 row(s) inserted.
>>insert into mvqr_001_t2 values(1,19,'abc'), (2,11,'def');

--- 2 row(s) inserted.
>>
>>cqd MVQR_REWRITE_LEVEL '4';

--- SQL operation complete.
>>
>>create mv mvqr_001_mv1 refresh on request initialized on create ENABLE QUERY REWRITE
+>as select count(*) c1 from mvqr_001_t1 group by b;

--- SQL operation complete.
>>
>>create mv mvqr_001_mv2 refresh on request initialized on create ENABLE QUERY REWRITE
+>as select t1.c c1, t2.c c2 from mvqr_001_t1 t1, mvqr_001_t2 t2 where t1.a=t2.a;

--- SQL operation complete.
>>
>>-- redescribe single mv
>>sh sh -c "$QMS redescribe QRP_CAT.SCH.mvqr_001_MV1";
>>-- redescribe nonexistent mv
>>sh sh -c "$QMS redescribe QRP_CAT.SCH.mvqr_001_MVXYZ";
>>-- redescribe all mvs, with publish option; also test handling of empty catalog
>>create catalog mvqr_t001_emptycat;

--- SQL operation complete.
>>sh sh -c "$QMS redescribe all publish";
>>drop catalog mvqr_t001_emptycat;

--- SQL operation complete.
>>
>>cqd mvqr_rewrite_candidates 'QRP_CAT.SCH.MVQR_001_MV1 :,QRP_CAT.SCH.MVQR_001_MV2';

--- SQL operation complete.
>>
>>prepare s from select count(*) c1 from mvqr_001_t1 group by b;

--- SQL command prepared.
>>select 'Success: MV used' from table(explain(null,'S')) where tname like '%MVQR_001_MV1%';

(EXPR)          
----------------

Success: MV used

--- 1 row(s) selected.
>>execute s;

C1                  
--------------------

                   1
                   2

--- 2 row(s) selected.
>>
>>prepare s from select t1.c, t2.c from mvqr_001_t1 t1, mvqr_001_t2 t2 where t1.a=t2.a and t1.b>10;

--- SQL command prepared.
>>select 'Success: MV used' from table(explain(null,'S')) where tname like '%MVQR_001_MV2%';

(EXPR)          
----------------

Success: MV used

--- 1 row(s) selected.
>>execute s;

C           C         
----------  ----------

def         def       

--- 1 row(s) selected.
>>
>>-- Test use of renamed MV.
>>alter mv mvqr_001_mv1 rename to mvqr_001_mv1_x;

--- SQL operation complete.
>>cqd mvqr_rewrite_candidates 'QRP_CAT.SCH.MVQR_001_MV1_X :,QRP_CAT.SCH.MVQR_001_MV2';

--- SQL operation complete.
>>prepare s from select count(*) c1 from mvqr_001_t1 group by b;

--- SQL command prepared.
>>select 'Success: MV used' from table(explain(null,'S')) where tname like '%MVQR_001_MV1_X%';

(EXPR)          
----------------

Success: MV used

--- 1 row(s) selected.
>>execute s;

C1                  
--------------------

                   1
                   2

--- 2 row(s) selected.
>>
>>-- Ignore changes on 1 component table of mvqr_001_mv2. Query should still use it
>>-- since mvqr_rewrite_level is 1. Design calls for it to not be used if rewrite level
>>-- is 3, but that is not implemented yet.
>>alter mv mvqr_001_mv2 add ignore changes on mvqr_001_t2;

--- SQL operation complete.
>>prepare s from select t1.c, t2.c from mvqr_001_t1 t1, mvqr_001_t2 t2 where t1.a=t2.a and t1.b>10;

--- SQL command prepared.
>>select 'Success: MV used' from table(explain(null,'S')) where tname like '%MVQR_001_MV2%';

(EXPR)          
----------------

Success: MV used

--- 1 row(s) selected.
>>execute s;

C           C         
----------  ----------

def         def       

--- 1 row(s) selected.
>>
>>-- At rewrite level 3, mv that has a non-empty Ignore Changes list should not be used.
>>cqd mvqr_rewrite_level '4';

--- SQL operation complete.
>>prepare s from select t1.c, t2.c from mvqr_001_t1 t1, mvqr_001_t2 t2 where t1.a=t2.a and t1.b>10;

--- SQL command prepared.
>>select 'Success: MV used' from table(explain(null,'S')) where tname like '%MVQR_001_MV2%';

(EXPR)          
----------------

Success: MV used

--- 1 row(s) selected.
>>execute s;

C           C         
----------  ----------

def         def       

--- 1 row(s) selected.
>>
>>-- Now remove the sole entry from the Ignore Changes list and try again.
>>alter mv mvqr_001_mv2 remove ignore changes on mvqr_001_t2;

--- SQL operation complete.
>>prepare s from select t1.c, t2.c from mvqr_001_t1 t1, mvqr_001_t2 t2 where t1.a=t2.a and t1.b>10;

--- SQL command prepared.
>>select 'Success: MV used' from table(explain(null,'S')) where tname like '%MVQR_001_MV2%';

(EXPR)          
----------------

Success: MV used

--- 1 row(s) selected.
>>execute s;

C           C         
----------  ----------

def         def       

--- 1 row(s) selected.
>>
>>control query default MVQR_PUBLISH_TO RESET;

--- SQL operation complete.
>>
>>obey TEST001(clean_up);
>>----------------------------------------------------
>>------------------ clean up section ----------------
>>-- drop all mvs and tables used by the test
>>----------------------------------------------------
>>
>>set schema QRP_CAT.SCH;

--- SQL operation complete.
>>drop schema QRP_CAT.SCH cascade;

--- SQL operation complete.
>>drop catalog QRP_CAT;

--- SQL operation complete.
>>delete from manageability.mv_rewrite.rewrite_publish;

--- 4 row(s) deleted.
>>
>>obey TEST001(clean_up_rewrite_publish_table);
>>----------------------------------------------------
>>------ publish rewrite table clean up section ------
>>-- this section should be removed when creation   --
>>-- of the publish rewrite table is part of init   --
>>-- sql script                                     -- 
>>----------------------------------------------------
>>
>>drop schema MANAGEABILITY.MV_REWRITE cascade;

--- SQL operation complete.
>>
>>
>>exit;

End of MXCI Session

