>>obey TEST011(set_up);
>>----------------------------------------------------
>>-------------------- set up section ----------------
>>----------------------------------------------------
>>
>>control query default MVQR_REWRITE_LEVEL '3';

--- SQL operation complete.
>>control query default MVQR_REWRITE_ENABLED_OPTION 'ON';

--- SQL operation complete.
>>--control query default QUERY_CACHE '0';
>>control query default MVQR_PUBLISH_TO 'PRIVATE';

--- SQL operation complete.
>>
>>prepare checkPlan from
+>  select operator, tname
+>  from table(explain(NULL, 'QUERYSTMT'))
+>  where operator like '%_SCAN%'
+>  order by tname;

--- SQL command prepared.
>>
>>obey GetMvDesc;
>>--set pattern $$VERS_CURR_SCHEMA_VERSION$$ 2400;
>>set param ?mvDescSubId  -2;
>>set param ?mvName 'SUMBY_DAY';
>>set param ?mvSchemaName 'SUMBY';
>>
>>-- query the TEXT SMD table for the descriptor of an MV
>>-- Note: Cast to UTF8, but we don't want to exceed the 20000 byte line
>>--       length limit of the NT version of awk (file CropDescriptor.ksh).
>>prepare getMVDescFromTEXT from
+>  select cast(text0 || coalesce(text1, '') || coalesce(text2, '') || coalesce(text3, '')
+>                    || coalesce(text4, '') || coalesce(text5, '') || coalesce(text6, '')
+>                    || coalesce(text7, '') || coalesce(text8, '') as char(15000 bytes) character set utf8)  as mv_descriptor_text
+>  from
+>		 (SELECT o.object_uid as uid0, text as text0
+>		         from  HP_DEFINITION_SCHEMA.TEXT t,
+>		               HP_DEFINITION_SCHEMA.objects o,
+>		               HP_SYSTEM_CATALOG.SYSTEM_SCHEMA.schemata s
+>		         where o.object_name = ?mvName
+>		           and o.OBJECT_TYPE = 'MV'
+>		           and o.object_uid = t.object_uid
+>		           and o.schema_uid = s.schema_uid
+>		           and s.schema_name = ?mvSchemaName
+>		           and t.object_sub_id = ?mvDescSubId
+>		           and sequence_num = 0 ) as row0
+>	left outer join
+>		 (SELECT o.object_uid as uid1, text as text1
+>		         from  HP_DEFINITION_SCHEMA.TEXT t,
+>		               HP_DEFINITION_SCHEMA.objects o,
+>		               HP_SYSTEM_CATALOG.SYSTEM_SCHEMA.schemata s
+>		         where o.object_name = ?mvName
+>		           and o.OBJECT_TYPE = 'MV'
+>		           and o.object_uid = t.object_uid
+>		           and o.schema_uid = s.schema_uid
+>		           and s.schema_name = ?mvSchemaName
+>		           and t.object_sub_id = ?mvDescSubId
+>		           and sequence_num = 1 ) as row1
+>		 on uid0 = uid1
+>	left outer join
+>		 (SELECT o.object_uid as uid2, text as text2
+>		         from  HP_DEFINITION_SCHEMA.TEXT t,
+>		               HP_DEFINITION_SCHEMA.objects o,
+>		               HP_SYSTEM_CATALOG.SYSTEM_SCHEMA.schemata s
+>		         where o.object_name = ?mvName
+>		           and o.OBJECT_TYPE = 'MV'
+>		           and o.object_uid = t.object_uid
+>		           and o.schema_uid = s.schema_uid
+>		           and s.schema_name = ?mvSchemaName
+>		           and t.object_sub_id = ?mvDescSubId
+>		           and sequence_num = 2 ) as row2
+>		 on uid0 = uid2
+>	left outer join
+>		 (SELECT o.object_uid as uid3, text as text3
+>		         from  HP_DEFINITION_SCHEMA.TEXT t,
+>		               HP_DEFINITION_SCHEMA.objects o,
+>		               HP_SYSTEM_CATALOG.SYSTEM_SCHEMA.schemata s
+>		         where o.object_name = ?mvName
+>		           and o.OBJECT_TYPE = 'MV'
+>		           and o.object_uid = t.object_uid
+>		           and o.schema_uid = s.schema_uid
+>		           and s.schema_name = ?mvSchemaName
+>		           and t.object_sub_id = ?mvDescSubId
+>		           and sequence_num = 3 ) as row3
+>		 on uid0 = uid3
+>	left outer join
+>		 (SELECT o.object_uid as uid4, text as text4
+>		         from  HP_DEFINITION_SCHEMA.TEXT t,
+>		               HP_DEFINITION_SCHEMA.objects o,
+>		               HP_SYSTEM_CATALOG.SYSTEM_SCHEMA.schemata s
+>		         where o.object_name = ?mvName
+>		           and o.OBJECT_TYPE = 'MV'
+>		           and o.object_uid = t.object_uid
+>		           and o.schema_uid = s.schema_uid
+>		           and s.schema_name = ?mvSchemaName
+>		           and t.object_sub_id = ?mvDescSubId
+>		           and sequence_num = 4 ) as row4
+>		 on uid0 = uid4
+>	left outer join
+>		 (SELECT o.object_uid as uid5, text as text5
+>		         from  HP_DEFINITION_SCHEMA.TEXT t,
+>		               HP_DEFINITION_SCHEMA.objects o,
+>		               HP_SYSTEM_CATALOG.SYSTEM_SCHEMA.schemata s
+>		         where o.object_name = ?mvName
+>		           and o.OBJECT_TYPE = 'MV'
+>		           and o.object_uid = t.object_uid
+>		           and o.schema_uid = s.schema_uid
+>		           and s.schema_name = ?mvSchemaName
+>		           and t.object_sub_id = ?mvDescSubId
+>		           and sequence_num = 5 ) as row5
+>		 on uid0 = uid5
+>	left outer join
+>		 (SELECT o.object_uid as uid6, text as text6
+>		         from  HP_DEFINITION_SCHEMA.TEXT t,
+>		               HP_DEFINITION_SCHEMA.objects o,
+>		               HP_SYSTEM_CATALOG.SYSTEM_SCHEMA.schemata s
+>		         where o.object_name = ?mvName
+>		           and o.OBJECT_TYPE = 'MV'
+>		           and o.object_uid = t.object_uid
+>		           and o.schema_uid = s.schema_uid
+>		           and s.schema_name = ?mvSchemaName
+>		           and t.object_sub_id = ?mvDescSubId
+>		           and sequence_num = 6 ) as row6
+>		 on uid0 = uid6
+>	left outer join
+>		 (SELECT o.object_uid as uid7, text as text7
+>		         from  HP_DEFINITION_SCHEMA.TEXT t,
+>		               HP_DEFINITION_SCHEMA.objects o,
+>		               HP_SYSTEM_CATALOG.SYSTEM_SCHEMA.schemata s
+>		         where o.object_name = ?mvName
+>		           and o.OBJECT_TYPE = 'MV'
+>		           and o.object_uid = t.object_uid
+>		           and o.schema_uid = s.schema_uid
+>		           and s.schema_name = ?mvSchemaName
+>		           and t.object_sub_id = ?mvDescSubId
+>		           and sequence_num = 7 ) as row7
+>		 on uid0 = uid7
+>	left outer join
+>		 (SELECT o.object_uid as uid8, text as text8
+>		         from  HP_DEFINITION_SCHEMA.TEXT t,
+>		               HP_DEFINITION_SCHEMA.objects o,
+>		               HP_SYSTEM_CATALOG.SYSTEM_SCHEMA.schemata s
+>		         where o.object_name = ?mvName
+>		           and o.OBJECT_TYPE = 'MV'
+>		           and o.object_uid = t.object_uid
+>		           and o.schema_uid = s.schema_uid
+>		           and s.schema_name = ?mvSchemaName
+>		           and t.object_sub_id = ?mvDescSubId
+>		           and sequence_num = 8 ) as row8
+>		 on uid0 = uid8;

--- SQL command prepared.
>>
>>
>>create schema test011;

--- SQL operation complete.
>>set schema test011;

--- SQL operation complete.
>>set pattern $$MVSchemaName$$ TEST011;
>>
>>create table T011_FACT (
+>	g1   int,
+>	g2   int,
+>	dimkey int
+>	) no partition;

--- SQL operation complete.
>>
>>create table T011_DIM (
+>        pkey int not null primary key, 
+>        dimdata1 int,
+>        dimdata2 char(10)
+>        ) store by primary key no partition;

--- SQL operation complete.
>>
>>insert into T011_FACT values
+>  (2, 6, 5), (7, 6, 1), (4, 7, 9), (9, 5, 5), (5, 5, 3),  -- Random data
+>  (2, 1, 9), (5, 8, 8), (4, 4, 6), (3, 2, 5), (9, 1, 6),
+>  (2, 4, 7), (7, 1, 6), (6, 3, 3), (1, 9, 8), (5, 3, 1),
+>  (8, 8, 2), (1, 9, 8), (8, 9, 4), (8, 1, 7), (4, 6, 1),
+>  (1, 4, 5), (3, 7, 6), (6, 7, 2), (6, 1, 2), (9, 5, 3),
+>  (4, 5, 1), (8, 6, 5), (8, 8, 8), (8, 6, 9), (1, 6, 4),
+>  (7, 5, 4), (5, 6, 2), (8, 3, 8), (2, 1, 4), (2, 1, 5),
+>  (6, 2, 5), (2, 7, 9), (3, 9, 7), (3, 5, 1), (1, 8, 4),
+>  (3, 3, 4), (9, 1, 4), (2, 1, 9), (8, 6, 6), (9, 8, 1),
+>  (5, 1, 9), (2, 9, 3), (6, 3, 2), (9, 4, 9), (2, 5, 8),
+>  (1, 1, 1), (1, 1, 1), (1, 1, 1),                       -- Higher count data
+>  (1, 2, 1), (1, 2, 1), (1, 2, 1), (1, 2, 1), (1, 2, 1), 
+>  (1, 3, 1), (1, 3, 1), (1, 3, 1), (1, 3, 1), (1, 3, 1), (1, 3, 1), (1, 3, 1), 
+>  (1, 4, 1), (1, 4, 1), (1, 4, 1), (1, 4, 1), (1, 4, 1), (1, 4, 1), (1, 4, 1), (1, 4, 1), (1, 4, 1);

--- 74 row(s) inserted.
>>insert into T011_FACT(g1)         values (0);

--- 1 row(s) inserted.
>>           -- Some null values
>>insert into T011_FACT(g1, g2)     values (0, 0);

--- 1 row(s) inserted.
>>insert into T011_FACT(g1, dimkey) values (0, 1);

--- 1 row(s) inserted.
>>insert into T011_FACT(g2)         values (0);

--- 1 row(s) inserted.
>>insert into T011_FACT(g2, dimkey) values (0, 1);

--- 1 row(s) inserted.
>>insert into T011_FACT(dimkey)     values (1);

--- 1 row(s) inserted.
>>
>>insert into T011_DIM(pkey, dimdata1, dimdata2) values
+>  (1, 1, 'abcdef'), (2, 2, 'abcdef'), (3, 3, 'abcdef'), 
+>  (4, 4, 'aNICEf'), (5, 5, 'aNICEf'), (6, 6, 'aNICEf'),
+>  (7, 7, 'aNIECE'), (8, 8, 'aNIECE'), (9, 9, 'aNIECE');

--- 9 row(s) inserted.
>>insert into T011_DIM(pkey) values (10);

--- 1 row(s) inserted.
>>
>>obey TEST011(create_mvs);
>>--===========================================
>>-- Create MVs
>>--===========================================
>>
>>-- MJV
>>create mv angb_mjv
+>	recompute
+>	initialized on create
+>	as  select *
+>	    from T011_FACT, T011_DIM
+>	    where dimkey=pkey;

--- SQL operation complete.
>>
>>set pattern $$MVName$$ ANGB_MJV;
>>obey TEST011(dump_MV);
>>--===========================================
>>--== Create the MV descriptor XML
>>--===========================================
>>
>>select * from $$MVName$$;

G1           G2           DIMKEY       PKEY         DIMDATA1     DIMDATA2
-----------  -----------  -----------  -----------  -----------  ----------

          7            6            1            1            1  abcdef    
          ?            ?            1            1            1  abcdef    
          ?            0            1            1            1  abcdef    
          0            ?            1            1            1  abcdef    
          1            4            1            1            1  abcdef    
          1            4            1            1            1  abcdef    
          1            4            1            1            1  abcdef    
          1            4            1            1            1  abcdef    
          1            4            1            1            1  abcdef    
          1            4            1            1            1  abcdef    
          1            4            1            1            1  abcdef    
          1            4            1            1            1  abcdef    
          1            4            1            1            1  abcdef    
          1            3            1            1            1  abcdef    
          1            3            1            1            1  abcdef    
          1            3            1            1            1  abcdef    
          1            3            1            1            1  abcdef    
          1            3            1            1            1  abcdef    
          1            3            1            1            1  abcdef    
          1            3            1            1            1  abcdef    
          1            2            1            1            1  abcdef    
          1            2            1            1            1  abcdef    
          1            2            1            1            1  abcdef    
          1            2            1            1            1  abcdef    
          1            2            1            1            1  abcdef    
          1            1            1            1            1  abcdef    
          1            1            1            1            1  abcdef    
          1            1            1            1            1  abcdef    
          9            8            1            1            1  abcdef    
          3            5            1            1            1  abcdef    
          4            5            1            1            1  abcdef    
          4            6            1            1            1  abcdef    
          5            3            1            1            1  abcdef    
          8            8            2            2            2  abcdef    
          6            3            2            2            2  abcdef    
          5            6            2            2            2  abcdef    
          6            1            2            2            2  abcdef    
          6            7            2            2            2  abcdef    
          5            5            3            3            3  abcdef    
          2            9            3            3            3  abcdef    
          9            5            3            3            3  abcdef    
          6            3            3            3            3  abcdef    
          8            9            4            4            4  aNICEf    
          9            1            4            4            4  aNICEf    
          3            3            4            4            4  aNICEf    
          1            8            4            4            4  aNICEf    
          2            1            4            4            4  aNICEf    
          7            5            4            4            4  aNICEf    
          1            6            4            4            4  aNICEf    
          2            6            5            5            5  aNICEf    
          6            2            5            5            5  aNICEf    
          2            1            5            5            5  aNICEf    
          8            6            5            5            5  aNICEf    
          1            4            5            5            5  aNICEf    
          3            2            5            5            5  aNICEf    
          9            5            5            5            5  aNICEf    
          4            4            6            6            6  aNICEf    
          8            6            6            6            6  aNICEf    
          3            7            6            6            6  aNICEf    
          7            1            6            6            6  aNICEf    
          9            1            6            6            6  aNICEf    
          2            4            7            7            7  aNIECE    
          3            9            7            7            7  aNIECE    
          8            1            7            7            7  aNIECE    
          5            8            8            8            8  aNIECE    
          2            5            8            8            8  aNIECE    
          8            3            8            8            8  aNIECE    
          8            8            8            8            8  aNIECE    
          1            9            8            8            8  aNIECE    
          1            9            8            8            8  aNIECE    
          4            7            9            9            9  aNIECE    
          9            4            9            9            9  aNIECE    
          5            1            9            9            9  aNIECE    
          2            1            9            9            9  aNIECE    
          2            7            9            9            9  aNIECE    
          8            6            9            9            9  aNIECE    
          2            1            9            9            9  aNIECE    

--- 77 row(s) selected.
>>
>>log $$MVName$$.tmp clear;
<Publish TS='0'>
<MV>
  <Version>
    1.0
  </Version>
  <Table>
    CAT.TEST011.ANGB_MJV
  </Table>
  <Misc isIncremental='0'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T1' TS='212223874883853161' isKeyCovered='1' numCols='3'>
          CAT.TEST011.T011_DIM
        </Table>
        <Table id='T2' TS='212223874883368922' numCols='4'>
          CAT.TEST011.T011_FACT
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J107'>
          <Column id='C89' tableId='T1' colIndex='0' isNullable='0'>
            CAT.TEST011.T011_DIM.PKEY
          </Column>
          <Column ref='C84'>
          </Column>
        </JoinPred>
      </JoinPredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O101' name='G1'>
        <Column id='C82' tableId='T2' colIndex='1'>
          CAT.TEST011.T011_FACT.G1
        </Column>
      </Output>
      <Output id='O104' name='G2'>
        <Column id='C83' tableId='T2' colIndex='2'>
          CAT.TEST011.T011_FACT.G2
        </Column>
      </Output>
      <Output id='O107' name='DIMKEY'>
        <Column id='C84' tableId='T2' colIndex='3'>
          CAT.TEST011.T011_FACT.DIMKEY
        </Column>
      </Output>
      <Output id='O113' name='DIMDATA1'>
        <Column id='C90' tableId='T1' colIndex='1'>
          CAT.TEST011.T011_DIM.DIMDATA1
        </Column>
      </Output>
      <Output id='O116' name='DIMDATA2'>
        <Column id='C91' tableId='T1' colIndex='2'>
          CAT.TEST011.T011_DIM.DIMDATA2
        </Column>
      </Output>
    </OutputList>
  </JBB>
</MV>
<Update op='Refresh' TS='1'> </Update>
</Publish>
>>
>>
>>
>>-- MAV 
>>create mv angb_mav1
+>	recompute
+>	initialized on create
+>	as  select g1, g2, count(dimdata1) cnt, max(dimdata2) mx
+>	    from T011_FACT, T011_DIM
+>	    where dimkey=pkey
+>	    group by g1, g2;

--- SQL operation complete.
>>
>>set pattern $$MVName$$ ANGB_MAV1;
>>obey TEST011(dump_MV);
>>--===========================================
>>--== Create the MV descriptor XML
>>--===========================================
>>
>>select * from $$MVName$$;

G1           G2           CNT                   MX        
-----------  -----------  --------------------  ----------

          3            5                     1  abcdef    
          4            5                     1  abcdef    
          8            9                     1  aNICEf    
          9            1                     2  aNICEf    
          1            8                     1  aNICEf    
          7            5                     1  aNICEf    
          1            6                     1  aNICEf    
          2            6                     1  aNICEf    
          3            2                     1  aNICEf    
          3            7                     1  aNICEf    
          5            8                     1  aNIECE    
          4            7                     1  aNIECE    
          9            4                     1  aNIECE    
          5            1                     1  aNIECE    
          2            7                     1  aNIECE    
          1            4                    10  abcdef    
          4            6                     1  abcdef    
          6            1                     1  abcdef    
          2            9                     1  abcdef    
          8            6                     3  aNIECE    
          4            4                     1  aNICEf    
          2            4                     1  aNIECE    
          2            5                     1  aNIECE    
          7            6                     1  abcdef    
          1            3                     7  abcdef    
          8            8                     2  abcdef    
          5            6                     1  abcdef    
          9            5                     2  abcdef    
          8            1                     1  aNIECE    
          8            3                     1  aNIECE    
          1            9                     2  aNIECE    
          ?            ?                     1  abcdef    
          0            ?                     1  abcdef    
          1            2                     5  abcdef    
          6            3                     2  abcdef    
          5            5                     1  abcdef    
          6            2                     1  aNICEf    
          3            9                     1  aNIECE    
          ?            0                     1  abcdef    
          1            1                     3  abcdef    
          9            8                     1  abcdef    
          5            3                     1  abcdef    
          6            7                     1  abcdef    
          3            3                     1  aNICEf    
          2            1                     4  aNIECE    
          7            1                     1  aNICEf    

--- 46 row(s) selected.
>>
>>log $$MVName$$.tmp clear;
<Publish TS='0'>
<MV>
  <Version>
    1.0
  </Version>
  <Table>
    CAT.TEST011.ANGB_MAV1
  </Table>
  <Misc isIncremental='0'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T1' TS='212223874883853161' numCols='3'>
          CAT.TEST011.T011_DIM
        </Table>
        <Table id='T2' TS='212223874883368922' numCols='4'>
          CAT.TEST011.T011_FACT
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J106'>
          <Column id='C86' tableId='T1' colIndex='0' isNullable='0'>
            CAT.TEST011.T011_DIM.PKEY
          </Column>
          <Column id='C81' tableId='T2' colIndex='3'>
            CAT.TEST011.T011_FACT.DIMKEY
          </Column>
        </JoinPred>
      </JoinPredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O94' name='CNT'>
        <Expr id='X94'>
          <Function id='S94' op='count_nonull' aggregateFunc='2'>
            <Column id='C87' tableId='T1' colIndex='1'>
              CAT.TEST011.T011_DIM.DIMDATA1
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O95' name='MX'>
        <Expr id='X95'>
          <Function id='S95' op='max' aggregateFunc='5'>
            <Column id='C88' tableId='T1' colIndex='2'>
              CAT.TEST011.T011_DIM.DIMDATA2
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O100' name='G1'>
        <Column ref='C79'>
        </Column>
      </Output>
      <Output id='O103' name='G2'>
        <Column ref='C80'>
        </Column>
      </Output>
    </OutputList>
    <GroupBy id='G3'>
      <Primary>
        <Column id='C79' tableId='T2' colIndex='1'>
          CAT.TEST011.T011_FACT.G1
        </Column>
        <Column id='C80' tableId='T2' colIndex='2'>
          CAT.TEST011.T011_FACT.G2
        </Column>
      </Primary>
    </GroupBy>
  </JBB>
</MV>
<Update op='Refresh' TS='1'> </Update>
</Publish>
>>
>>
>>
>>-- ANGB MV
>>create mv angb_mav2
+>	recompute
+>	initialized on create
+>	as  select count(dimdata1) cnt, max(dimdata2) mx
+>	    from T011_FACT, T011_DIM
+>	    where dimkey=pkey;

--- SQL operation complete.
>>
>>set pattern $$MVName$$ ANGB_MAV2;
>>obey TEST011(dump_MV);
>>--===========================================
>>--== Create the MV descriptor XML
>>--===========================================
>>
>>select * from $$MVName$$;

CNT                   MX        
--------------------  ----------

                  77  abcdef    

--- 1 row(s) selected.
>>
>>log $$MVName$$.tmp clear;
<Publish TS='0'>
<MV>
  <Version>
    1.0
  </Version>
  <Table>
    CAT.TEST011.ANGB_MAV2
  </Table>
  <Misc isIncremental='0'>
  </Misc>
  <JBB id='B0'>
    <Hub>
      <JBBCList>
        <Table id='T1' TS='212223874883853161' numCols='3'>
          CAT.TEST011.T011_DIM
        </Table>
        <Table id='T2' TS='212223874883368922' numCols='4'>
          CAT.TEST011.T011_FACT
        </Table>
      </JBBCList>
      <JoinPredList>
        <JoinPred id='J100'>
          <Column id='C80' tableId='T1' colIndex='0' isNullable='0'>
            CAT.TEST011.T011_DIM.PKEY
          </Column>
          <Column id='C75' tableId='T2' colIndex='3'>
            CAT.TEST011.T011_FACT.DIMKEY
          </Column>
        </JoinPred>
      </JoinPredList>
    </Hub>
    <ExtraHub>
    </ExtraHub>
    <OutputList>
      <Output id='O88' name='CNT'>
        <Expr id='X88'>
          <Function id='S88' op='count_nonull' aggregateFunc='2'>
            <Column id='C81' tableId='T1' colIndex='1'>
              CAT.TEST011.T011_DIM.DIMDATA1
            </Column>
          </Function>
        </Expr>
      </Output>
      <Output id='O89' name='MX'>
        <Expr id='X89'>
          <Function id='S89' op='max' aggregateFunc='5'>
            <Column id='C82' tableId='T1' colIndex='2'>
              CAT.TEST011.T011_DIM.DIMDATA2
            </Column>
          </Function>
        </Expr>
      </Output>
    </OutputList>
    <GroupBy id='G3'>
    </GroupBy>
  </JBB>
</MV>
<Update op='Refresh' TS='1'> </Update>
</Publish>
>>
>>
>>
>>obey TEST011(match_angb);
>>--===========================================
>>-- Query 1
>>-- Should match all 3 MVs
>>--===========================================
>>set pattern $$QueryName$$ ANGB_QUERY;
>>
>>control query default MVQR_LOG_QUERY_DESCRIPTORS 'DUMP';

--- SQL operation complete.
>>
>>obey TEST011($$QueryName$$);
>>prepare QueryStmt from
+>  select count(dimdata1) cnt, max(dimdata2) mx
+>  from T011_FACT, T011_DIM
+>  where dimkey=pkey
+>  order by 1;

--- SQL command prepared.
>>
>>
>>obey TEST011(dump_Query);
>>--===========================================
>>--== Create the query descriptor XML
>>--===========================================
>>
>>log $$QueryName$$.tmp clear;
>>sh sh ./CropDescriptor.ksh -q -i $$QueryName$$.TMP -o $$QueryName$$.xml;
>>-- and once for the test log file
>>execute QueryStmt;

Query Descriptor                                                     
---------------------------------------------------------------------

<Query>                                                              
  <Version>                                                          
    1.0                                                              
  </Version>                                                         
  <Misc rewriteLevel='3'>                                            
  </Misc>                                                            
  <JBB id='B0'>                                                      
    <Hub>                                                            
      <JBBCList>                                                     
        <Table id='T1' TS='212223874883853161' numCols='3'>          
          CAT.TEST011.T011_DIM                                       
          <Key>                                                      
            <Column id='C9' tableId='T1' colIndex='0' isNullable='0'>
              CAT.TEST011.T011_DIM.PKEY                              
            </Column>                                                
          </Key>                                                     
        </Table>                                                     
        <Table id='T2' TS='212223874883368922' numCols='4'>          
          CAT.TEST011.T011_FACT                                      
          <Key>                                                      
            <Column id='C1' tableId='T2' colIndex='0' isNullable='0'>
              CAT.TEST011.T011_FACT.SYSKEY                           
            </Column>                                                
          </Key>                                                     
        </Table>                                                     
      </JBBCList>                                                    
      <JoinPredList>                                                 
        <JoinPred id='J29'>                                          
          <Column ref='C9'>                                          
          </Column>                                                  
          <Column id='C4' tableId='T2' colIndex='3'>                 
            CAT.TEST011.T011_FACT.DIMKEY                             
          </Column>                                                  
        </JoinPred>                                                  
      </JoinPredList>                                                
    </Hub>                                                           
    <ExtraHub>                                                       
    </ExtraHub>                                                      
    <OutputList>                                                     
      <Output id='O17'>                                              
        <Expr id='X17'>                                              
          <Function id='S17' op='count_nonull' aggregateFunc='2'>    
            <Column id='C10' tableId='T1' colIndex='1'>              
              CAT.TEST011.T011_DIM.DIMDATA1                          
            </Column>                                                
          </Function>                                                
        </Expr>                                                      
      </Output>                                                      
      <Output id='O18'>                                              
        <Expr id='X18'>                                              
          <Function id='S18' op='max' aggregateFunc='5'>             
            <Column id='C11' tableId='T1' colIndex='2'>              
              CAT.TEST011.T011_DIM.DIMDATA2                          
            </Column>                                                
          </Function>                                                
        </Expr>                                                      
      </Output>                                                      
    </OutputList>                                                    
    <GroupBy id='G3'>                                                
    </GroupBy>                                                       
  </JBB>                                                             
</Query>                                                             

--- 62 row(s) selected.
>>
>>
>>control query default MVQR_LOG_QUERY_DESCRIPTORS 'OFF';

--- SQL operation complete.
>>
>>sh echo INITIALIZE > angb1.in;
>>sh echo MATCH ANGB_QUERY.xml >> angb1.in;
>>
>>-- Run QMS as a command line tool
>>sh sh -c "$QMS angb1.in angb.out";
>>
>>log;
<Result>
  <Version>
    1.0
  </Version>
  <JbbResult ref='B0'>
    <JbbSubset hasGroupby='0'>
      <TableList>
        <Table ref='T1'>
          CAT.TEST011.T011_DIM
        </Table>
        <Table ref='T2'>
          CAT.TEST011.T011_FACT
        </Table>
      </TableList>
      <CandidateList>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='212223874885680771'>
            CAT.TEST011.ANGB_MJV
          </MVName>
          <OutputList>
            <Output ref='C10' result='Provided'>
              <MVColumn ref='C10'>
                DIMDATA1
              </MVColumn>
            </Output>
            <Output ref='C11' result='Provided'>
              <MVColumn ref='C11'>
                DIMDATA2
              </MVColumn>
            </Output>
          </OutputList>
        </Candidate>
      </CandidateList>
    </JbbSubset>
    <JbbSubset ref='G3' hasGroupby='1'>
      <TableList>
        <Table ref='T1'>
          CAT.TEST011.T011_DIM
        </Table>
        <Table ref='T2'>
          CAT.TEST011.T011_FACT
        </Table>
      </TableList>
      <CandidateList>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='212223874896694412'>
            CAT.TEST011.ANGB_MAV1
          </MVName>
          <GroupBy ref='G3' result='NotProvided'>
          </GroupBy>
          <OutputList>
            <Output ref='O18' result='NotProvided'>
              <Expr ref='X18'>
                <Function op='max' aggregateFunc='5'>
                  <MVColumn ref='S18'>
                    MX
                  </MVColumn>
                </Function>
              </Expr>
            </Output>
            <Output ref='O17' result='NotProvided'>
              <Expr ref='X17'>
                <Function op='sum' aggregateFunc='3'>
                  <MVColumn ref='S17'>
                    CNT
                  </MVColumn>
                </Function>
              </Expr>
            </Output>
          </OutputList>
        </Candidate>
        <Candidate isPreferredMatch='0' statsOnly='0'>
          <MVName TS='212223874899897004'>
            CAT.TEST011.ANGB_MAV2
          </MVName>
          <GroupBy ref='G3' result='Provided'>
          </GroupBy>
          <OutputList>
            <Output ref='O17' result='Provided'>
              <MVColumn ref='X17'>
                CNT
              </MVColumn>
            </Output>
            <Output ref='O18' result='Provided'>
              <MVColumn ref='X18'>
                MX
              </MVColumn>
            </Output>
          </OutputList>
        </Candidate>
      </CandidateList>
    </JbbSubset>
  </JbbResult>
</Result>
>>
>>-- Verify MJV is picked by the optimizer.
>>control query default MVQR_REWRITE_CANDIDATES 'CAT.TEST011.ANGB_MJV';

--- SQL operation complete.
>>obey TEST011(compare);
>>
>>obey TEST011($$QueryName$$);
>>prepare QueryStmt from
+>  select count(dimdata1) cnt, max(dimdata2) mx
+>  from T011_FACT, T011_DIM
+>  where dimkey=pkey
+>  order by 1;

--- SQL command prepared.
>>
>>
>>execute checkPlan;

OPERATOR                        TNAME
------------------------------  ------------------------------------------------------------

FILE_SCAN                       CAT.TEST011.ANGB_MJV                                        

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

CNT                   MX        
--------------------  ----------

                  77  abcdef    

--- 1 row(s) selected.
>>
>>log T011_C1.txt clear;
>>
>>-- Verify the data is correct
>>control query default MVQR_REWRITE_LEVEL '0';

--- SQL operation complete.
>>
>>obey TEST011($$QueryName$$);
>>prepare QueryStmt from
+>  select count(dimdata1) cnt, max(dimdata2) mx
+>  from T011_FACT, T011_DIM
+>  where dimkey=pkey
+>  order by 1;

--- SQL command prepared.
>>
>>
>>execute checkPlan;

OPERATOR                        TNAME
------------------------------  ------------------------------------------------------------

FILE_SCAN                       CAT.TEST011.T011_DIM                                        
FILE_SCAN                       CAT.TEST011.T011_FACT                                       

--- 2 row(s) selected.
>>
>>execute QueryStmt;

CNT                   MX        
--------------------  ----------

                  77  abcdef    

--- 1 row(s) selected.
>>
>>control query default MVQR_REWRITE_LEVEL '3';

--- SQL operation complete.
>>
>>log T011_C2.txt clear;
>>
>>-- Diff query results
>>log;
>>-- Diff is done.
>>
>>-- Verify MAV1 is picked by the optimizer.
>>control query default MVQR_REWRITE_CANDIDATES 'CAT.TEST011.ANGB_MAV1';

--- SQL operation complete.
>>obey TEST011(compare);
>>
>>obey TEST011($$QueryName$$);
>>prepare QueryStmt from
+>  select count(dimdata1) cnt, max(dimdata2) mx
+>  from T011_FACT, T011_DIM
+>  where dimkey=pkey
+>  order by 1;

--- SQL command prepared.
>>
>>
>>execute checkPlan;

OPERATOR                        TNAME
------------------------------  ------------------------------------------------------------

FILE_SCAN                       CAT.TEST011.ANGB_MAV1                                       

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

CNT                   MX        
--------------------  ----------

                  77  abcdef    

--- 1 row(s) selected.
>>
>>log T011_C1.txt clear;
>>
>>-- Verify the data is correct
>>control query default MVQR_REWRITE_LEVEL '0';

--- SQL operation complete.
>>
>>obey TEST011($$QueryName$$);
>>prepare QueryStmt from
+>  select count(dimdata1) cnt, max(dimdata2) mx
+>  from T011_FACT, T011_DIM
+>  where dimkey=pkey
+>  order by 1;

--- SQL command prepared.
>>
>>
>>execute checkPlan;

OPERATOR                        TNAME
------------------------------  ------------------------------------------------------------

FILE_SCAN                       CAT.TEST011.T011_DIM                                        
FILE_SCAN                       CAT.TEST011.T011_FACT                                       

--- 2 row(s) selected.
>>
>>execute QueryStmt;

CNT                   MX        
--------------------  ----------

                  77  abcdef    

--- 1 row(s) selected.
>>
>>control query default MVQR_REWRITE_LEVEL '3';

--- SQL operation complete.
>>
>>log T011_C2.txt clear;
>>
>>-- Diff query results
>>log;
>>-- Diff is done.
>>
>>-- Verify MAV2 is picked by the optimizer.
>>control query default MVQR_REWRITE_CANDIDATES 'CAT.TEST011.ANGB_MAV2';

--- SQL operation complete.
>>obey TEST011(compare);
>>
>>obey TEST011($$QueryName$$);
>>prepare QueryStmt from
+>  select count(dimdata1) cnt, max(dimdata2) mx
+>  from T011_FACT, T011_DIM
+>  where dimkey=pkey
+>  order by 1;

--- SQL command prepared.
>>
>>
>>execute checkPlan;

OPERATOR                        TNAME
------------------------------  ------------------------------------------------------------

FILE_SCAN                       CAT.TEST011.ANGB_MAV2                                       

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

CNT                   MX        
--------------------  ----------

                  77  abcdef    

--- 1 row(s) selected.
>>
>>log T011_C1.txt clear;
>>
>>-- Verify the data is correct
>>control query default MVQR_REWRITE_LEVEL '0';

--- SQL operation complete.
>>
>>obey TEST011($$QueryName$$);
>>prepare QueryStmt from
+>  select count(dimdata1) cnt, max(dimdata2) mx
+>  from T011_FACT, T011_DIM
+>  where dimkey=pkey
+>  order by 1;

--- SQL command prepared.
>>
>>
>>execute checkPlan;

OPERATOR                        TNAME
------------------------------  ------------------------------------------------------------

FILE_SCAN                       CAT.TEST011.T011_DIM                                        
FILE_SCAN                       CAT.TEST011.T011_FACT                                       

--- 2 row(s) selected.
>>
>>execute QueryStmt;

CNT                   MX        
--------------------  ----------

                  77  abcdef    

--- 1 row(s) selected.
>>
>>control query default MVQR_REWRITE_LEVEL '3';

--- SQL operation complete.
>>
>>log T011_C2.txt clear;
>>
>>-- Diff query results
>>log;
>>-- Diff is done.
>>
>>obey TEST011(clean_up);
>>----------------------------------------------------
>>------------------ clean up section ----------------
>>----------------------------------------------------
>>drop schema test011 cascade;

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

End of MXCI Session

