>>-- create the database used for the tests
>>
>>create schema compgeneral_test023;

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

--- SQL operation complete.
>>
>>get tables;

Tables in Schema TRAFODION.COMPGENERAL_TEST023
==============================================

SB_HISTOGRAMS
SB_HISTOGRAM_INTERVALS
SB_PERSISTENT_SAMPLES

--- SQL operation complete.
>>
>>CREATE TABLE stest
+>(
+>c1 integer not null,
+>c2 integer not null,
+>c3 integer not null,
+>PRIMARY KEY (C1 ASC)
+>)
+>SALT USING 4 PARTITIONS
+>ON (C1)
+>;

--- SQL operation complete.
>>
>>
>>upsert using load into stest
+>select 
+>  x1+10*x2+100*x3+1000*x4+10000*x5,
+>  x2,
+>  x3
+>-- the from clause below creates 100,000 rows, the cross product of
+>-- 5 copies of { 0, ... 9 } 
+>  from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) T(x1)
+>transpose 0,1,2,3,4,5,6,7,8,9 as x2
+>transpose 0,1,2,3,4,5,6,7,8,9 as x3
+>transpose 0,1,2,3,4,5,6,7,8,9 as x4
+>transpose 0,1,2,3,4,5,6,7,8,9 as x5;

--- 100000 row(s) inserted.
>>
>>
>>CREATE TABLE stestc
+>(
+>c1 char(12) not null,
+>c2 char(12) not null,
+>c3 char(12) not null,
+>bigcol varchar(1024) not null,
+>PRIMARY KEY (C1 ASC)
+>)
+>SALT USING 4 PARTITIONS
+>ON (C1)
+>;

--- SQL operation complete.
>>
>>upsert using load into stestc
+>select 
+>  x1 || x2 || x3 || x4 || x5,
+>  x2 || x4 || x1,
+>  x5 || x3,
+>  'Wow this is really big ' || x1 || x2 || x3 || x4 || x5 ||
+>  ' and now we concatenate lots more junk, more junk than you' ||
+>  ' can shake a stick at because we like to use up storage' ||
+>  ' and exercise code paths that result in string truncation.' ||
+>  ' And you thought you had better things to do.'
+>-- the from clause below creates 100,000 rows, the cross product of
+>-- 5 copies of { 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j' }
+>  from (values('a'),('b'),('c'),('d'),('e'),('f'),('g'),('h'),('i'),('j')) T(x1)
+>transpose 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j' as x2
+>transpose 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j' as x3
+>transpose 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j' as x4
+>transpose 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j' as x5;

--- 100000 row(s) inserted.
>>
>>
>>create table stest_empty like stest with partitions;

--- SQL operation complete.
>>
>>get tables;

Tables in Schema TRAFODION.COMPGENERAL_TEST023
==============================================

SB_HISTOGRAMS
SB_HISTOGRAM_INTERVALS
SB_PERSISTENT_SAMPLES
STEST
STESTC
STEST_EMPTY

--- SQL operation complete.
>>  -- should be just stest, stest_empty, stestc, and the sb_* tables
>>
>>?section ustat1p
>>
>>-- positive tests
>>
>>-- create a persistent sample
>>update statistics for table stest create sample random 20 percent;

--- SQL operation complete.
>>
>>prepare s1 from
+>select object_name,sample_name,reason,last_where_predicate
+>  from "_MD_".objects o join sb_persistent_samples s
+>   on o.object_uid = s.table_uid
+>  where object_name = ? and object_type = 'BT';

--- SQL command prepared.
>>
>>execute s1 using 'STEST';

OBJECT_NAME                                                                                                                                                                                                                                                       SAMPLE_NAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           REASON  LAST_WHERE_PREDICATE
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  ------  --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

STEST                                                                                                                                                                                                                                                             TRAFODION.COMPGENERAL_TEST023.TRAF_SAMPLE_08256920717490504707_1499123202_644772                                                                                                                                                                                                                                                                                                                                                                                                                                      M                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           

--- 1 row(s) selected.
>>-- should see one row
>>
>>get tables;

Tables in Schema TRAFODION.COMPGENERAL_TEST023
==============================================

SB_HISTOGRAMS
SB_HISTOGRAM_INTERVALS
SB_PERSISTENT_SAMPLES
STEST
STESTC
STEST_EMPTY
TRAF_SAMPLE_08256920717490504707_1499123202_644772

--- SQL operation complete.
>> -- should be stest, stest_empty, stestc, sb_* tables + a sample table
>>
>>-- create another one, showing its replacement
>>update statistics for table stest remove sample;

--- SQL operation complete.
>>update statistics for table stest create sample random 10 percent;

--- SQL operation complete.
>>
>>get tables;

Tables in Schema TRAFODION.COMPGENERAL_TEST023
==============================================

SB_HISTOGRAMS
SB_HISTOGRAM_INTERVALS
SB_PERSISTENT_SAMPLES
STEST
STESTC
STEST_EMPTY
TRAF_SAMPLE_08256920717490504707_1499123287_739643

--- SQL operation complete.
>> -- should be stest, stest_empty, stestc, sb_* tables + a different sample table
>>
>>execute s1 using 'STEST';

OBJECT_NAME                                                                                                                                                                                                                                                       SAMPLE_NAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           REASON  LAST_WHERE_PREDICATE
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  ------  --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

STEST                                                                                                                                                                                                                                                             TRAFODION.COMPGENERAL_TEST023.TRAF_SAMPLE_08256920717490504707_1499123287_739643                                                                                                                                                                                                                                                                                                                                                                                                                                      M                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           

--- 1 row(s) selected.
>>-- should see one row
>>
>>-- remove a persistent sample table
>>update statistics for table stest remove sample;

--- SQL operation complete.
>>
>>get tables;

Tables in Schema TRAFODION.COMPGENERAL_TEST023
==============================================

SB_HISTOGRAMS
SB_HISTOGRAM_INTERVALS
SB_PERSISTENT_SAMPLES
STEST
STESTC
STEST_EMPTY

--- SQL operation complete.
>> -- should be stest, stest_empty, stestc, sb_* tables only
>>
>>execute s1 using 'STEST';

--- 0 row(s) selected.
>>-- should see zero rows
>>
>>-- create a persistent sample table using PERSISTENT
>>
>>update statistics for table stest on every column sample random 10 percent persistent;

--- SQL operation complete.
>>
>>get tables;

Tables in Schema TRAFODION.COMPGENERAL_TEST023
==============================================

SB_HISTOGRAMS
SB_HISTOGRAM_INTERVALS
SB_PERSISTENT_SAMPLES
STEST
STESTC
STEST_EMPTY
TRAF_SAMPLE_08256920717490504707_1499123343_842843

--- SQL operation complete.
>> -- should be stest, stest_empty, stestc, sb_* tables + another sample table
>>
>>execute s1 using 'STEST';

OBJECT_NAME                                                                                                                                                                                                                                                       SAMPLE_NAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           REASON  LAST_WHERE_PREDICATE
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  ------  --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

STEST                                                                                                                                                                                                                                                             TRAFODION.COMPGENERAL_TEST023.TRAF_SAMPLE_08256920717490504707_1499123343_842843                                                                                                                                                                                                                                                                                                                                                                                                                                      I                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           

--- 1 row(s) selected.
>>-- should see one row
>>
>>-- do it again showing its replacement
>>update statistics for table stest remove sample;

--- SQL operation complete.
>>update statistics for table stest on every column sample random 10 percent persistent;

--- SQL operation complete.
>>
>>get tables;

Tables in Schema TRAFODION.COMPGENERAL_TEST023
==============================================

SB_HISTOGRAMS
SB_HISTOGRAM_INTERVALS
SB_PERSISTENT_SAMPLES
STEST
STESTC
STEST_EMPTY
TRAF_SAMPLE_08256920717490504707_1499123383_331045

--- SQL operation complete.
>> -- should be stest, stest_empty, stestc, sb_* tables + another sample table
>>
>>execute s1 using 'STEST';

OBJECT_NAME                                                                                                                                                                                                                                                       SAMPLE_NAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           REASON  LAST_WHERE_PREDICATE
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  ------  --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

STEST                                                                                                                                                                                                                                                             TRAFODION.COMPGENERAL_TEST023.TRAF_SAMPLE_08256920717490504707_1499123383_331045                                                                                                                                                                                                                                                                                                                                                                                                                                      I                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           

--- 1 row(s) selected.
>>-- should see one row
>>
>>-- change the data in the table and do an INCREMENTAL update stats
>>insert into stest values (100000,1,1),
+>  (100001,1,1),
+>  (100002,1,1),
+>  (100003,1,1),
+>  (100004,1,1),
+>  (100005,1,1),
+>  (100006,1,1),
+>  (100007,1,1),
+>  (100008,1,1),
+>  (100009,1,1),
+>  (100010,2,1),
+>  (100011,2,1),
+>  (100012,2,1),
+>  (100013,2,1),
+>  (100014,2,1),
+>  (100015,2,1),
+>  (100016,2,1),
+>  (100017,2,1),
+>  (100018,2,1),
+>  (100019,2,1),
+>  (100020,3,1),
+>  (100021,3,1),
+>  (100022,3,1),
+>  (100023,3,1),
+>  (100024,3,1),
+>  (100025,3,1),
+>  (100026,3,1),
+>  (100027,3,1),
+>  (100028,3,1),
+>  (100029,3,1);

--- 30 row(s) inserted.
>>
>>-- These two CQDs are to get around annoying 9222 and 9224 warnings
>>-- that happen non-deterministically due to "gaps" in the sample histograms.
>>-- Note: The warnings now only appear if "update statistics log on" is in 
>>-- force, which it isn't here. Nevertheless the CQDs encourage the exercise
>>-- of a particular code path within the incremental update stats code so
>>-- we leave them here.
>>cqd USTAT_IUS_INTERVAL_ROWCOUNT_CHANGE_THRESHOLD '0.15';

--- SQL operation complete.
>>cqd USTAT_IUS_INTERVAL_UEC_CHANGE_THRESHOLD '0.15';

--- SQL operation complete.
>>
>>update statistics for table stest on existing columns incremental where c1 >= 100000;

--- SQL operation complete.
>>
>>execute s1 using 'STEST';

OBJECT_NAME                                                                                                                                                                                                                                                       SAMPLE_NAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           REASON  LAST_WHERE_PREDICATE
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  ------  --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

STEST                                                                                                                                                                                                                                                             TRAFODION.COMPGENERAL_TEST023.TRAF_SAMPLE_08256920717490504707_1499123383_331045                                                                                                                                                                                                                                                                                                                                                                                                                                      I        c1 >= 100000                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       

--- 1 row(s) selected.
>>
>>-- similar tests, but using character data types
>>update statistics for table stestc on every column sample random 10 percent persistent;

--- SQL operation complete.
>>
>>insert into stestc values ('naaaa','abc','ba','Hava Nagila'),
+>  ('naaab','abc','ba','Le Marseillais'),
+>  ('naaac','abc','ba','some other song'),
+>  ('naaad','abc','ba','more data'),
+>  ('naaae','abc','ba','more data'),
+>  ('naaaf','abc','ba','more data'),
+>  ('naaag','abc','ba','more data'),
+>  ('naaah','abc','ba','more data'),
+>  ('naaai','abc','ba','more data'),
+>  ('naaaj','abc','ba','more data'),
+>  ('naaba','bhe','ba','more data'),
+>  ('naabb','bhe','ba','more data'),
+>  ('naabc','bhe','ba','more data'),
+>  ('naabd','bhe','ba','more data'),
+>  ('naabe','bhe','ba','more data'),
+>  ('naabf','bhe','ba','more data'),
+>  ('naabg','bhe','ba','Ha! caught you sleeping!'),
+>  ('naabh','bhe','ba','more data'),
+>  ('naabi','bhe','ba','more data'),
+>  ('naabj','bhe','ba','more data'),
+>  ('naaca','fib','ba','more data'),
+>  ('naacb','fib','ba','more data'),
+>  ('naacc','fib','ba','more data'),
+>  ('naacd','fib','ba','more data'),
+>  ('naace','fib','ba','more data'),
+>  ('naacf','fib','ba','more data'),
+>  ('naacg','fib','ba','more data'),
+>  ('naach','fib','ba','more data'),
+>  ('naaci','fib','ba','more data'),
+>  ('naacj','fib','ba','more data');

--- 30 row(s) inserted.
>>
>>update statistics for table stestc on existing columns incremental where c1 >= 'naaaa';

--- SQL operation complete.
>>
>>get tables;

Tables in Schema TRAFODION.COMPGENERAL_TEST023
==============================================

SB_HISTOGRAMS
SB_HISTOGRAM_INTERVALS
SB_PERSISTENT_SAMPLES
STEST
STESTC
STEST_EMPTY
TRAF_SAMPLE_08256920717490504707_1499123383_331045
TRAF_SAMPLE_08256920717490506747_1499123452_066783

--- SQL operation complete.
>>
>>execute s1 using 'STESTC';

OBJECT_NAME                                                                                                                                                                                                                                                       SAMPLE_NAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           REASON  LAST_WHERE_PREDICATE
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  ------  --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

STESTC                                                                                                                                                                                                                                                            TRAFODION.COMPGENERAL_TEST023.TRAF_SAMPLE_08256920717490506747_1499123452_066783                                                                                                                                                                                                                                                                                                                                                                                                                                      I        c1 >= 'naaaa'                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      

--- 1 row(s) selected.
>>
>>?section ustat1n
>>-- negative tests
>>
>>-- attempt to create a sample on an empty table
>>update statistics for table stest_empty create sample random 10 percent;

*** ERROR[9207] The specified SAMPLE option generated an empty sample set. Modify the SAMPLE option and resubmit.

--- SQL operation failed with errors.
>>
>>get tables;

Tables in Schema TRAFODION.COMPGENERAL_TEST023
==============================================

SB_HISTOGRAMS
SB_HISTOGRAM_INTERVALS
SB_PERSISTENT_SAMPLES
STEST
STESTC
STEST_EMPTY
TRAF_SAMPLE_08256920717490504707_1499123383_331045
TRAF_SAMPLE_08256920717490506747_1499123452_066783

--- SQL operation complete.
>> -- should be the same as previous "get tables"
>>
>>execute s1 using 'STEST_EMPTY';

--- 0 row(s) selected.
>>-- should be zero rows
>>
>>update statistics for table stest_empty on every column sample random 10 percent persistent;

*** WARNING[9220] The table is empty, so no persistent sample table was created.

--- SQL operation completed with warnings.
>>
>>get tables;

Tables in Schema TRAFODION.COMPGENERAL_TEST023
==============================================

SB_HISTOGRAMS
SB_HISTOGRAM_INTERVALS
SB_PERSISTENT_SAMPLES
STEST
STESTC
STEST_EMPTY
TRAF_SAMPLE_08256920717490504707_1499123383_331045
TRAF_SAMPLE_08256920717490506747_1499123452_066783

--- SQL operation complete.
>> -- should be the same as previous "get tables"
>>
>>execute s1 using 'STEST_EMPTY';

--- 0 row(s) selected.
>>-- should be zero rows
>>
>>-- attempt to remove a non-existant persistent sample
>>update statistics for table stest_empty remove sample;

*** WARNING[9228] There were no sample tables to drop.

--- SQL operation completed with warnings.
>>
>>get tables;

Tables in Schema TRAFODION.COMPGENERAL_TEST023
==============================================

SB_HISTOGRAMS
SB_HISTOGRAM_INTERVALS
SB_PERSISTENT_SAMPLES
STEST
STESTC
STEST_EMPTY
TRAF_SAMPLE_08256920717490504707_1499123383_331045
TRAF_SAMPLE_08256920717490506747_1499123452_066783

--- SQL operation complete.
>> -- should be the same as previous "get tables"
>>
>>-- attempt to do incremental with an invalid where clause
>>update statistics for table stest on existing columns incremental where 1;

*** ERROR[9219] Incremental UPDATE STATISTICS: An operation failed, possibly due to an invalid WHERE clause.

*** ERROR[15001] A syntax error occurred at or before: 
UPSERT USING LOAD INTO TRAFODION.COMPGENERAL_TEST023.TRAF_SAMPLE_08256920717490
504707_1499123383_331045_I (SELECT * FROM TRAFODION.COMPGENERAL_TEST023.STEST W
HERE  1  SAMPLE RANDOM 9.963000 PERCENT );
                     ^ (180 characters from start of SQL statement)

*** ERROR[8822] The statement was not prepared.

--- SQL operation failed with errors.
>>
>>get tables;

Tables in Schema TRAFODION.COMPGENERAL_TEST023
==============================================

SB_HISTOGRAMS
SB_HISTOGRAM_INTERVALS
SB_PERSISTENT_SAMPLES
STEST
STESTC
STEST_EMPTY
TRAF_SAMPLE_08256920717490504707_1499123383_331045
TRAF_SAMPLE_08256920717490506747_1499123452_066783

--- SQL operation complete.
>> -- should be the same as previous "get tables"
>>
>>-- attempt to do incremental with a syntactically valid but semantically invalid where clause
>>update statistics for table stest on existing columns incremental where badcol > 5;

*** ERROR[9219] Incremental UPDATE STATISTICS: An operation failed, possibly due to an invalid WHERE clause.

*** ERROR[4001] Column BADCOL is not found.  Tables in scope: TRAFODION.COMPGENERAL_TEST023.STEST.  Default schema: TRAFODION.SCH.

*** ERROR[8822] The statement was not prepared.

--- SQL operation failed with errors.
>>
>>-- attempt to do incremental when no persistent sample exists
>>update statistics for table stestc remove sample;

--- SQL operation complete.
>>update statistics for table stestc on existing columns incremental where c1 >= 'naaaa';

*** ERROR[9221] Incremental UPDATE STATISTICS cannot be performed due to the absence of the IUS persistent sample table for TRAFODION.COMPGENERAL_TEST023.STESTC.  Use a regular UPDATE STATISTICS command with the sample clause and PERSISTENT first to create such a persistent sample table.

--- SQL operation failed with errors.
>>
>>-- attempt to create a persistent sample when one already exists, syntax 1
>>update statistics for table stest on every column sample random 10 percent persistent;

*** ERROR[9251] A persistent sample table already exists. Use UPDATE STATISTICS ... REMOVE SAMPLE to drop it first if desired.

--- SQL operation failed with errors.
>>
>>-- attempt to create a persistent sample when one already exists, syntax 2
>>update statistics for table stest create sample random 20 percent;

*** ERROR[9251] A persistent sample table already exists. Use UPDATE STATISTICS ... REMOVE SAMPLE to drop it first if desired.

--- SQL operation failed with errors.
>>
>>get tables;

Tables in Schema TRAFODION.COMPGENERAL_TEST023
==============================================

SB_HISTOGRAMS
SB_HISTOGRAM_INTERVALS
SB_PERSISTENT_SAMPLES
STEST
STESTC
STEST_EMPTY
TRAF_SAMPLE_08256920717490504707_1499123383_331045

--- SQL operation complete.
>> -- should be the same as previous "get tables" except only one sample table
>>
>>
>>?section clnup
>>
>>drop table stest;

--- SQL operation complete.
>>drop table stestc;

--- SQL operation complete.
>>drop table stest_empty;

--- SQL operation complete.
>>
>>get tables;

Tables in Schema TRAFODION.COMPGENERAL_TEST023
==============================================

SB_HISTOGRAMS
SB_HISTOGRAM_INTERVALS
SB_PERSISTENT_SAMPLES

--- SQL operation complete.
>>  -- sample tables should be gone too
>>
>>drop schema compgeneral_test023 cascade;

--- SQL operation complete.
>> -- cascade needed because of sb_* tables
>>
>>log;
