-- @@@ START COPYRIGHT @@@
--
-- Licensed to the Apache Software Foundation (ASF) under one
-- or more contributor license agreements.  See the NOTICE file
-- distributed with this work for additional information
-- regarding copyright ownership.  The ASF licenses this file
-- to you under the Apache License, Version 2.0 (the
-- "License"); you may not use this file except in compliance
-- with the License.  You may obtain a copy of the License at
--
--   http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing,
-- software distributed under the License is distributed on an
-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
-- KIND, either express or implied.  See the License for the
-- specific language governing permissions and limitations
-- under the License.
--
-- @@@ END COPYRIGHT @@@
-- @@@ START COPYRIGHT @@@
--
>>
>>obey TESTMV215M(SET_UP);
>>-------------------------------------------------------
>>control query default insert_vsbb 'USER';

--- SQL operation complete.
>>set PARSERFLAGS 3;
>>set schema cat.tpcd;

--- SQL operation complete.
>>
>>obey ./TPCD/CREATEALL.OBEY(SET_UP);
>>
>>create catalog cat;

*** ERROR[1035] Catalog CAT already exists.

--- SQL operation completed with errors.
>>create schema cat.tpcd;

*** ERROR[1022] Schema CAT.TPCD already exists.

--- SQL operation completed with errors.
>>cd ./tpcd;
>>
>>set schema CAT.TPCD;

--- SQL operation complete.
>>set envvar DSS_CONFIG . ;
>>-- change all of these lines together if needed
>>set envvar DSS_TEMP ./tpcdtemp;
>>sh mkdir %DSS_TEMP% ;
>>sh copy %DSS_CONFIG%/dbgen.exe %DSS_TEMP% ;
>>sh copy %DSS_CONFIG%/Dists.dss %DSS_TEMP% ;
>>sh copy %DSS_CONFIG%/*.fmt %DSS_TEMP% ;
>>sh copy %DSS_CONFIG%/generateTpcd.ksh %DSS_TEMP% ;
>>sh copy %DSS_CONFIG%/*.awk %DSS_TEMP% ;
>>sh copy %DSS_CONFIG%/runGen.pl %DSS_TEMP% ;
>>
>>cd tpcdtemp;
>>
>>log SQLCI.LOG clear;
>>
>>
>>
>>-- insert only selected data
>>
>>delete from lineitem;

--- 5864 row(s) deleted.
>>delete from supplier;

--- 79 row(s) deleted.
>>delete from orders;

--- 1618 row(s) deleted.
>>delete from part;

--- 1499 row(s) deleted.
>>delete from partsupp;

--- 4736 row(s) deleted.
>>delete from customer;

--- 799 row(s) deleted.
>>delete from nation;

--- 25 row(s) deleted.
>>delete from region;

--- 5 row(s) deleted.
>>
>>
>>cd tpcd/tpcdtemp;
>>sh import cat.tpcd.nation -I nation.tbl -U nation.fmt;
>>sh import cat.tpcd.region -I region.tbl -U region.fmt;
>>sh import cat.tpcd.supplier -I supplier.tbl -U supplier.fmt -r -C 30;
>>sh import cat.tpcd.lineitem -I lineitem.tbl -U lineitem.fmt -r -C 17000;
>>sh import cat.tpcd.orders -I orders.tbl -U orders.fmt -r -C 4500;
>>sh import cat.tpcd.part -I part.tbl -U part.fmt -r -C 600;
>>sh import cat.tpcd.partsupp -I partsupp.tbl -U partsupp.fmt -r -C 2400;
>>sh import cat.tpcd.customer -I customer.tbl -U customer.fmt -r -C 450;
>>cd ../..;
>>
>>obey PREPARE_DDL_SELECTS;
>>SET PARAM ?mv_name 'g1';
>>SET PARAM ?gr_name 'g1';
>>SET PARAM ?table_name 'g1';
>>
>>PREPARE select_mvs_table FROM
+>    SELECT	
+>			FIRST_ADDED_COL_NUM, 
+>			COMMIT_REFRESH_EACH,
+>			MV_TYPE, 
+>			REWRITE_ENABLED, 
+>			LEFT_LINEAR, 
+>			INCREMENTALY_MAINTAINABLE,
+>			CREATION_REFRESH_TYPE,
+>			CONTAINS_MVS, 
+>			MVSTATUS,
+>			IS_MINMAX,
+>			MV_AUDIT
+>	FROM DEFINITION_SCHEMA_VERSION_$$VERS_CURR_SCHEMA_VERSION$$.mvs
+>	WHERE mv_uid = (SELECT object_uid 
+>	FROM DEFINITION_SCHEMA_VERSION_$$VERS_CURR_SCHEMA_VERSION$$.objects 
+>			WHERE object_name = ?mv_name);

*** ERROR[4001] Column FIRST_ADDED_COL_NUM is not found.  Tables in scope: CAT.DEFINITION_SCHEMA_VERSION_$$VERS_CURR_SCHEMA_VERSION$$.MVS.  Default schema: CAT.TPCD.

*** ERROR[8822] Unable to prepare the statement.

>>
>>PREPARE select_mvs_used FROM
+>    SELECT SUBSTRING(object_name, 0, 20) as table_name, 
+>           used_object_type,USAGE_ORIGIN,TABLE_ATTRIBUTES,IS_INNER_LJ
+>    FROM  DEFINITION_SCHEMA_VERSION_$$VERS_CURR_SCHEMA_VERSION$$.mvs_used
+>          left join DEFINITION_SCHEMA_VERSION_$$VERS_CURR_SCHEMA_VERSION$$.objects used_object
+>	  on used_object.object_uid = used_object_uid
+>    WHERE 
+>	 mv_uid = (SELECT object_uid 
+>	    	       FROM DEFINITION_SCHEMA_VERSION_$$VERS_CURR_SCHEMA_VERSION$$.objects 
+>	      	       WHERE object_name = ?mv_name)
+>    order by object_name;

--- SQL command prepared.
>>
>>
>>PREPARE select_mvs_used_umd FROM
+>	select SUBSTRING(mvobj.object_name, 0, 20)  as mv_name,SUBSTRING(btobj.object_name, 0, 20)  as bt_name, 
+>           EPOCH,DELTA_SIZE,STAT_1,STAT_2,STAT_3,STAT_4
+>	from DEFINITION_SCHEMA_VERSION_$$VERS_CURR_SCHEMA_VERSION$$.objects mvobj,
+>			DEFINITION_SCHEMA_VERSION_$$VERS_CURR_SCHEMA_VERSION$$.objects btobj,
+>			mvs_used_umd 
+>	where	btobj.object_uid = mvs_used_umd.bt_uid and 
+>			mvobj.object_uid = (SELECT object_uid 
+>	    	       FROM DEFINITION_SCHEMA_VERSION_$$VERS_CURR_SCHEMA_VERSION$$.objects 
+>	      	       WHERE object_name = ?mv_name) and
+>			mvobj.object_uid = mvs_used_umd.mv_uid
+>	order by mv_name,bt_name	;

--- SQL command prepared.
>>PREPARE select_mvs_table_info_umd FROM
+>	select SUBSTRING(mvobj.object_name, 0, 20)  as mv_name,LAST_DE_EPOCH,RECOMPUTE_EPOCH,STAT_1,STAT_2
+>	from DEFINITION_SCHEMA_VERSION_$$VERS_CURR_SCHEMA_VERSION$$.objects mvobj,
+>			DEFINITION_SCHEMA_VERSION_$$VERS_CURR_SCHEMA_VERSION$$.objects btobj,
+>			mvs_table_info_umd 
+>	where	btobj.object_uid = mvs_table_info_umd.bt_uid and 
+>			mvobj.object_uid = (SELECT object_uid 
+>	    	       FROM DEFINITION_SCHEMA_VERSION_$$VERS_CURR_SCHEMA_VERSION$$.objects 
+>	      	       WHERE object_name = ?mv_name) and
+>			mvobj.object_uid = mvs_table_info_umd.bt_uid
+>			;

--- SQL command prepared.
>>
>>
>>
>>
>>
>>
>>PREPARE select_mvs_cols FROM
+>    SELECT mv_col_num, SUBSTRING(objects.object_name, 0, 20) as orig_table, 
+>	   original_col_num orig_col, column_type, operator_type, 
+>	   is_complex, is_used_by_join, usage_type, 
+>    	   dependent_col_1 dep1, dependent_col_2 dep2, dependent_col_3 dep3, 
+>	   is_system,  SUBSTRING(text, 0, 170) col_text
+>	FROM ((DEFINITION_SCHEMA_VERSION_$$VERS_CURR_SCHEMA_VERSION$$.mvs_cols
+>		   left outer join
+>		   DEFINITION_SCHEMA_VERSION_$$VERS_CURR_SCHEMA_VERSION$$.objects objects
+>		   on objects.object_uid = original_table_uid)
+>           left outer join
+>		   DEFINITION_SCHEMA_VERSION_$$VERS_CURR_SCHEMA_VERSION$$.text text_table
+>		   on (text_table.object_uid = mv_uid        AND
+>	           text_table.object_sub_id = mv_col_num AND
+>               text_table.object_sub_id <> -1))
+>	WHERE  mv_uid = (SELECT object_uid
+>	      		 FROM DEFINITION_SCHEMA_VERSION_$$VERS_CURR_SCHEMA_VERSION$$.objects 
+>	      		 WHERE object_name = ?mv_name)
+>	ORDER BY mv_col_num,orig_table,original_col_num, usage_type;

--- SQL command prepared.
>>
>>PREPARE select_mvs_join_cols FROM
+>    SELECT 
+>    SUBSTRING(o.object_name , 0, 20) as base_table,  
+>    col_num,
+>    seq_number,
+>    veg_index,
+>    usage_type,
+>    is_complex,
+>    left_join_table_type
+>    FROM   DEFINITION_SCHEMA_VERSION_$$VERS_CURR_SCHEMA_VERSION$$.objects o,
+>	   DEFINITION_SCHEMA_VERSION_$$VERS_CURR_SCHEMA_VERSION$$.mvs_join_cols
+>    WHERE  mv_uid = (SELECT object_uid 
+>	    	     	FROM DEFINITION_SCHEMA_VERSION_$$VERS_CURR_SCHEMA_VERSION$$.objects 
+>	      		WHERE object_name = ?mv_name)
+>	  AND  o.object_uid  = DEFINITION_SCHEMA_VERSION_$$VERS_CURR_SCHEMA_VERSION$$.mvs_join_cols.table_uid
+>    ORDER BY seq_number;

--- SQL command prepared.
>>
>>
>>
>>PREPARE select_partitions FROM
+>	SELECT o.OBJECT_UID,
+>	       SubString(OBJECT_NAME,1,40) as object_name,
+>	       DATA_SOURCE,
+>	       SubString(FIRST_KEY,1,40) as first_key
+>	FROM  DEFINITION_SCHEMA_VERSION_$$VERS_CURR_SCHEMA_VERSION$$.partitions p,
+>	      DEFINITION_SCHEMA_VERSION_$$VERS_CURR_SCHEMA_VERSION$$.objects o
+>	WHERE o.object_uid=p.object_uid;

--- SQL command prepared.
>>
>>PREPARE select_group FROM
+>    SELECT ALL SUBSTRING(GroupsObj.object_name, 0, 20) as Group_name,
+>         SUBSTRING(Mvs.object_name, 0, 20) as MV_name
+>    FROM DEFINITION_SCHEMA_VERSION_$$VERS_CURR_SCHEMA_VERSION$$.objects GroupsObj,
+>       DEFINITION_SCHEMA_VERSION_$$VERS_CURR_SCHEMA_VERSION$$.objects Mvs,
+>       DEFINITION_SCHEMA_VERSION_$$VERS_CURR_SCHEMA_VERSION$$.MVGroups MVsGroup
+>    WHERE GroupsObj.object_uid = MVsGroup.MVRGroup_uid AND MVsGroup.MV_uid=Mvs.object_uid AND
+>       (( SUBSTRING ( ?gr_name FROM 1 FOR 1) = '' ) OR (MVsGroup.MVRGroup_uid = (SELECT object_uid
+>                     FROM DEFINITION_SCHEMA_VERSION_$$VERS_CURR_SCHEMA_VERSION$$.objects
+>                     WHERE object_name = ?gr_name)))
+>    ORDER BY Group_name,MV_name;

--- SQL command prepared.
>>
>>
>>PREPARE select_mvs_table_info FROM
+>    SELECT CURRENT_EPOCH, MV_ATTRIBUTES_BITMAP, IS_LOCKONREFRESH, 
+>	IS_INSERTLOG, RANGELOG_TYPE,MVSALLOWED_TYPE
+>	FROM DEFINITION_SCHEMA_VERSION_$$VERS_CURR_SCHEMA_VERSION$$.mvs_table_info
+>	WHERE base_table_uid = (SELECT object_uid 
+>	FROM DEFINITION_SCHEMA_VERSION_$$VERS_CURR_SCHEMA_VERSION$$.objects 
+>			WHERE object_name = ?table_name);

--- SQL command prepared.
>>
>>
>>
>>
>>
>>--PREPARE Compare_objects FROM
>>--    SELECT SUBSTRING(GroupsObj.object_name, 0, 20) as Group_name,
>>--	   SUBSTRING(Mvs.object_name, 0, 20) as MV_name
>>--    FROM DEFINITION_SCHEMA_VERSION_$$VERS_CURR_SCHEMA_VERSION$$.objects A, 
>>--	 DEFINITION_SCHEMA_VERSION_$$VERS_CURR_SCHEMA_VERSION$$.objects B,
>>--	 DEFINITION_SCHEMA_VERSION_$$VERS_CURR_SCHEMA_VERSION$$.MVGroups MVsGroup
>>--    WHERE GroupsObj.object_uid = MVsGroup.MVRGroup_uid AND MVsGroup.MV_uid=Mvs.object_uid 
>>--	 AND MVsGroup.MVRGroup_uid = (SELECT object_uid 
>>--	    	       FROM DEFINITION_SCHEMA_VERSION_$$VERS_CURR_SCHEMA_VERSION$$.objects 
>>--	      	       WHERE object_name = ?gr_name);
>>--
>>
>>-----------------------------------------------------
>>
>>
>>-- Run Tests
>>    set schema cat.tpcd;

--- SQL operation complete.
>>    obey TESTMV215M(TEST3);
>>-----------------------------------------------------
>>log LOGMV215M;
>>
>>alter table lineitem  Attributes automatic RANGELOG ;

--- SQL operation complete.
>>
>>create mv mv4q1
+>    refresh on request
+>  	initialize on create
+>	MVAttributes COMMIT Refresh EACH 50
+>	  as
+>    select
+>        l_returnflag,
+>        l_linestatus,
+>        sum(l_quantity) as sum_qty,
+>        sum(l_extendedprice) as sum_base_price,
+>        sum( l_extendedprice * (1-l_discount)) as sum_disc_price,
+>        sum(l_extendedprice * (1-l_discount) * (1 + l_tax) )as sum_charge,
+>        avg(l_quantity) as avg_qty,
+>        avg(l_extendedprice) as avg_price,
+>        avg(l_discount) as avg_disc,
+>        count(*) as count_order
+>    from lineitem
+>--    where 
+>--        l_shipdate <= date '1998-12-01' - interval '90' day
+>    group by l_returnflag, l_linestatus;

--- SQL operation complete.
>>
>>
>>
>>-- Basic Q1
>>-- for compare
>>PREPARE stat1 FROM
+>    select
+>        l_returnflag,
+>        l_linestatus,
+>        sum(l_quantity) as sum_qty,
+>        sum(l_extendedprice) as sum_base_price,
+>        sum( l_extendedprice * (1-l_discount)) as sum_disc_price,
+>        sum(l_extendedprice * (1-l_discount) * (1 + l_tax) )as sum_charge,
+>        avg(l_quantity) as avg_qty,
+>        avg(l_extendedprice) as avg_price,
+>        avg(l_discount) as avg_disc,
+>        count(*) as count_order
+>    from lineitem
+>--    where 
+>--        l_shipdate <= date '1998-12-01' - interval '90' day
+>    group by l_returnflag, l_linestatus
+>    order by l_returnflag, l_linestatus;

--- SQL command prepared.
>>
>>PREPARE stat2 FROM
+>    select l_returnflag, l_linestatus, sum_qty, sum_base_price, sum_disc_price,
+>           sum_charge, avg_qty, avg_price, avg_disc, count_order
+>    from mv4q1
+>    order by l_returnflag, l_linestatus;

--- SQL command prepared.
>>
>>log;
>>
>>-- insert delta
>>log;
NonStop SQL/MX Import Utility 1.0
Rows Imported = 500
Import Completed Successfully
>>
>>
>>update lineitem
+>set l_quantity = 1000
+>where l_partkey between 30.00 and 39.00;

--- 91 row(s) updated.
>>
>>refresh mv4q1 debug 54;

--- SQL operation complete.
>>log;

Task DE(CAT.TPCD.LINEITEM) has been completed in 1 transaction(s).
IUD statements performed by the task:
	Range log insert: 2 invocations.
	Range log delete: 0 invocations.
	IUD log subset delete: 0 invocations.
	IUD log subset update @IGNORE: 0 invocations.

The materialized view CAT.TPCD.MV4Q1 is being refreshed in multiple transactions...

The materialized view CAT.TPCD.MV4Q1 has been refreshed in 16 transaction(s).

Starting the log cleanup of table CAT.TPCD.LINEITEM...

Finished the log cleanup of table CAT.TPCD.LINEITEM.


>>
>>
>>log;
1,2c1,2
< >>  log TEMP1;
< >>  execute stat1;
---
> >>  log TEMP2;
> >>  execute stat2;
>>
>>
>>--------------------------------------------------------------
>>	obey TESTMV215M(TEST4);
>>--------------------------------------------------------------
>>alter table lineitem  Attributes automatic RANGELOG ;

--- SQL operation complete.
>>
>>create mv mv4q3
+>    refresh on request
+>	ignore changes on customer,orders
+>    initialize on create
+>	MVAttributes COMMIT Refresh EACH 50
+>    as
+>    select 
+>        l_orderkey, 
+>        cast(sum(l_extendedprice*(1-l_discount)) as numeric(18,2)) as revenue,
+>        o_orderdate, o_shippriority
+>    from customer,orders,lineitem
+>    where
+>        c_mktsegment = 'BUILDING'
+>        and c_custkey = o_custkey
+>        and l_orderkey = o_orderkey
+> --       and o_orderdate < date '1995-03-15'
+> --       and l_shipdate >  date '1995-03-15'
+>    group by l_orderkey, o_orderdate, o_shippriority;

--- SQL operation complete.
>>
>>
>>-- Basic Q3
>>-- for compare
>>PREPARE stat1 FROM
+>    select
+>        l_orderkey, 
+>        cast(sum(l_extendedprice*(1-l_discount)) as numeric(18,2)) as revenue,
+>        o_orderdate, o_shippriority
+>    from customer,orders,lineitem
+>    where
+>        c_mktsegment = 'BUILDING'
+>        and c_custkey = o_custkey
+>        and l_orderkey = o_orderkey
+>--        and o_orderdate < date '1995-03-15'
+>--        and l_shipdate >  date '1995-03-15'
+>    group by l_orderkey, o_orderdate, o_shippriority
+>    order by revenue desc,o_orderdate;

--- SQL command prepared.
>>
>>PREPARE stat2 FROM
+>    select l_orderkey, revenue, o_orderdate, o_shippriority
+>    from mv4q3
+>    order by revenue desc, o_orderdate;

--- SQL command prepared.
>>
>>log;
1,2c1,2
< >>  log TEMP1;
< >>  execute stat1;
---
> >>  log TEMP2;
> >>  execute stat2;
>>
>>
>>log;
NonStop SQL/MX Import Utility 1.0
Rows Imported = 2500
Import Completed Successfully
>>
>>CREATE TANDEM_CAT_REQUEST&1 16 3 @cat.tpcd.lineitem@ @1@ @0@ ;

--- SQL operation complete.
>>
>>update lineitem
+>set l_comment = 'broken'
+>where l_suppkey > 30 and l_suppkey < 40;

--- 1844 row(s) updated.
>>
>>
>>refresh mv4q3 debug 54;

--- SQL operation complete.
>>log;

Task DE(CAT.TPCD.LINEITEM) has been completed in 1 transaction(s).
IUD statements performed by the task:
	Range log insert: 2 invocations.
	Range log delete: 0 invocations.
	IUD log subset delete: 0 invocations.
	IUD log subset update @IGNORE: 0 invocations.

The materialized view CAT.TPCD.MV4Q3 is being refreshed in multiple transactions...

The materialized view CAT.TPCD.MV4Q3 has been refreshed in 3 transaction(s).

Starting the log cleanup of table CAT.TPCD.LINEITEM...

Finished the log cleanup of table CAT.TPCD.LINEITEM.


>>
>>log;
1,2c1,2
< >>  log TEMP1;
< >>  execute stat1;
---
> >>  log TEMP2;
> >>  execute stat2;
>>
>>----------------------------------------------------
>>--	obey TESTMV215M(TEST5); Bug 276
>>	obey TESTMV215M(TEST6);
>>----------------------------------------------------
>>alter table customer  Attributes automatic RANGELOG ;

--- SQL operation complete.
>>
>>create mv mv4q10
+>    refresh on request
+>	ignore changes on orders,lineitem, nation
+>	initialize on create	
+>    MVAttributes COMMIT Refresh EACH 50	
+>	as
+>    select 
+>        c_custkey, c_name,
+>        cast (sum(l_extendedprice*(1-l_discount)) 
+>        as numeric(18,2)) as revenue,
+>        c_acctbal,  n_name, c_address, c_phone, c_comment
+>    from customer,orders,lineitem, nation
+>    where 
+>        c_custkey = o_custkey
+>        and l_orderkey = o_orderkey
+>--        and o_orderdate >= date '1993-10-01'
+>--        and o_orderdate < date '1993-10-01' + interval '3' month
+>--        and l_returnflag = 'R'
+>        and c_nationkey = n_nationkey
+>    group by c_custkey, c_name, c_acctbal, c_phone, n_name,
+>        c_address, c_comment;

--- SQL operation complete.
>>
>>-- Basic Q10
>>-- for compare
>>PREPARE stat1 FROM
+>    select 
+>        c_custkey, c_name,
+>        cast (sum(l_extendedprice*(1-l_discount)) 
+>        as numeric(18,2)) as revenue,
+>        c_acctbal,  n_name, c_address, c_phone, c_comment
+>    from customer,orders,lineitem, nation
+>    where 
+>        c_custkey = o_custkey
+>        and l_orderkey = o_orderkey
+>--        and o_orderdate >= date '1993-10-01'
+>--        and o_orderdate < date '1993-10-01' + interval '3' month
+> --       and l_returnflag = 'R'
+>        and c_nationkey = n_nationkey
+>    group by c_custkey, c_name, c_acctbal, c_phone, n_name,
+>        c_address, c_comment
+>    order by revenue desc;

--- SQL command prepared.
>>
>>PREPARE stat2 FROM
+>    select c_custkey, c_name, revenue, c_acctbal, n_name, c_address, c_phone, c_comment
+>    from mv4q10
+>    order by revenue desc;

--- SQL command prepared.
>>
>>log;
1,2c1,2
< >>  log TEMP1;
< >>  execute stat1;
---
> >>  log TEMP2;
> >>  execute stat2;
>>
>>log;
NonStop SQL/MX Import Utility 1.0
Rows Imported = 75
Import Completed Successfully
>>
>>update customer
+>set c_nationkey = 12 -- 'JAPAN'
+>	where c_nationkey = 10 and c_acctbal < 4000;

--- 11 row(s) updated.
>>
>>refresh mv4q10 debug 54;

--- SQL operation complete.
>>log;

Task DE(CAT.TPCD.CUSTOMER) has been completed in 1 transaction(s).
IUD statements performed by the task:
	Range log insert: 1 invocations.
	Range log delete: 0 invocations.
	IUD log subset delete: 0 invocations.
	IUD log subset update @IGNORE: 0 invocations.

The materialized view CAT.TPCD.MV4Q10 is being refreshed in multiple transactions...

The materialized view CAT.TPCD.MV4Q10 has been refreshed in 4 transaction(s).

Starting the log cleanup of table CAT.TPCD.CUSTOMER...

Finished the log cleanup of table CAT.TPCD.CUSTOMER.


>>
>>log;
1,2c1,2
< >>  log TEMP1;
< >>  execute stat1;
---
> >>  log TEMP2;
> >>  execute stat2;
>>
>>log;
>>
>>----------------------------------------------------
>>	obey TESTMV215M(TEST7);
>>----------------------------------------------------
>>alter table partsupp  Attributes automatic RANGELOG ;

--- SQL operation complete.
>>
>>create mv mv4q11
+>    refresh on request
+>	ignore changes on supplier,nation
+>	initialize on create	
+>    MVAttributes COMMIT Refresh EACH 50	
+>    as
+>    select
+>        ps_partkey, sum(ps_supplycost*ps_availqty) as pvalue
+>    from partsupp,supplier,nation
+>    where ps_suppkey = s_suppkey
+>        and s_nationkey = n_nationkey
+> --       and n_name = 'GERMANY'
+>    group by ps_partkey;

--- SQL operation complete.
>>
>>PREPARE stat1 FROM
+>    select
+>        ps_partkey, sum(ps_supplycost*ps_availqty) as pvalue
+>    from partsupp,supplier,nation
+>    where ps_suppkey = s_suppkey
+>        and s_nationkey = n_nationkey
+> --       and n_name = 'GERMANY'
+>    group by ps_partkey
+>    order by ps_partkey desc;

--- SQL command prepared.
>>
>>PREPARE stat2 FROM
+>    select 
+>        ps_partkey, pvalue
+>    from mv4q11
+>    order by ps_partkey desc;

--- SQL command prepared.
>>
>>log;
1,2c1,2
< >>  log TEMP1;
< >>  execute stat1;
---
> >>  log TEMP2;
> >>  execute stat2;
>>
>>
>>log;
NonStop SQL/MX Import Utility 1.0
Rows Imported = 400
Import Completed Successfully
>>
>>update partsupp
+>set ps_comment = 'not available'
+>where ps_partkey between 52 and 57;

--- 24 row(s) updated.
>>
>>refresh mv4q11 debug 54;

--- SQL operation complete.
>>log;

Task DE(CAT.TPCD.PARTSUPP) has been completed in 1 transaction(s).
IUD statements performed by the task:
	Range log insert: 2 invocations.
	Range log delete: 0 invocations.
	IUD log subset delete: 0 invocations.
	IUD log subset update @IGNORE: 0 invocations.

The materialized view CAT.TPCD.MV4Q11 is being refreshed in multiple transactions...

The materialized view CAT.TPCD.MV4Q11 has been refreshed in 11 transaction(s).

Starting the log cleanup of table CAT.TPCD.PARTSUPP...

Finished the log cleanup of table CAT.TPCD.PARTSUPP.


>>
>>log;
1,2c1,2
< >>  log TEMP1;
< >>  execute stat1;
---
> >>  log TEMP2;
> >>  execute stat2;
>>log;
>>
>>
>>
>>exit;

End of MXCI Session

