>>obey TEST002(setup);
>>create schema t002sch;

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

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

--- SQL operation complete.
>>create index t002t1i1 on t002t1(b);

--- SQL operation complete.
>>create index t002t1i2 on t002t1(c);

--- SQL operation complete.
>>insert into t002t1 values (1,2, 3);

--- 1 row(s) inserted.
>>
>>sh regrhbase.ksh flush 'TRAFODION.T002SCH.T002T1';
>>sh regrhbase.ksh flush 'TRAFODION.T002SCH.T002T1I1';
>>sh regrhbase.ksh flush 'TRAFODION.T002SCH.T002T1I2';
>>
>>select * from t002t1;

A            B            C          
-----------  -----------  -----------

          1            2            3

--- 1 row(s) selected.
>>
>>create table t002t2(a int not null primary key, b int not null)
+>  salt using 4 partitions;

--- SQL operation complete.
>>create index t002t2i1 on t002t2(b);

--- SQL operation complete.
>>insert into t002t2 values (1,2);

--- 1 row(s) inserted.
>>
>>sh regrhbase.ksh flush 'TRAFODION.T002SCH.T002T2';
>>sh regrhbase.ksh flush 'TRAFODION.T002SCH.T002T2I1';
>>
>>select * from t002t2;

A            B          
-----------  -----------

          1            2

--- 1 row(s) selected.
>>
>>-- purgedata should preserve regions
>>get region stats for table t002t2;


Stats Summary
=============

  ObjectName:              TRAFODION.T002SCH.T002T2
  NumRegions:              4
  RegionsLocation:         /hbase/data/default
  TotalNumStores:          4
  TotalNumStoreFiles:      0
  TotalUncompressedSize:   0
  TotalStoreFileSize:      0
  TotalMemStoreSize:       0
  TotalReadRequestsCount:  1
  TotalWriteRequestsCount: 1

Stats Details
=============

  RegionServer:       edev06:35100
  RegionNum:          1
  RegionName:         TRAFODION.T002SCH.T002T2/1528158523869
  NumStores:          1
  NumStoreFiles:      0
  UncompressedSize:   0 (less than 1MB)
  StoreFileSize:      0 (less than 1MB)
  MemStoreSize:       0 (less than 1MB)
  ReadRequestsCount:  0
  WriteRequestsCount: 0

  RegionServer:       edev06:35100
  RegionNum:          2
  RegionName:         TRAFODION.T002SCH.T002T2/1528158523869
  NumStores:          1
  NumStoreFiles:      0
  UncompressedSize:   0 (less than 1MB)
  StoreFileSize:      0 (less than 1MB)
  MemStoreSize:       0 (less than 1MB)
  ReadRequestsCount:  1
  WriteRequestsCount: 1

  RegionServer:       edev06:35100
  RegionNum:          3
  RegionName:         TRAFODION.T002SCH.T002T2/1528158523869
  NumStores:          1
  NumStoreFiles:      0
  UncompressedSize:   0 (less than 1MB)
  StoreFileSize:      0 (less than 1MB)
  MemStoreSize:       0 (less than 1MB)
  ReadRequestsCount:  0
  WriteRequestsCount: 0

  RegionServer:       edev06:35100
  RegionNum:          4
  RegionName:         TRAFODION.T002SCH.T002T2/1528158523869
  NumStores:          1
  NumStoreFiles:      0
  UncompressedSize:   0 (less than 1MB)
  StoreFileSize:      0 (less than 1MB)
  MemStoreSize:       0 (less than 1MB)
  ReadRequestsCount:  0
  WriteRequestsCount: 0


--- SQL operation complete.
>>purgedata t002t2;

--- SQL operation complete.
>>get region stats for table t002t2;


Stats Summary
=============

  ObjectName:              TRAFODION.T002SCH.T002T2
  NumRegions:              4
  RegionsLocation:         /hbase/data/default
  TotalNumStores:          4
  TotalNumStoreFiles:      0
  TotalUncompressedSize:   0
  TotalStoreFileSize:      0
  TotalMemStoreSize:       0
  TotalReadRequestsCount:  0
  TotalWriteRequestsCount: 0

Stats Details
=============

  RegionServer:       edev06:35100
  RegionNum:          1
  RegionName:         TRAFODION.T002SCH.T002T2/1528158523869
  NumStores:          1
  NumStoreFiles:      0
  UncompressedSize:   0 (less than 1MB)
  StoreFileSize:      0 (less than 1MB)
  MemStoreSize:       0 (less than 1MB)
  ReadRequestsCount:  0
  WriteRequestsCount: 0

  RegionServer:       edev06:35100
  RegionNum:          2
  RegionName:         TRAFODION.T002SCH.T002T2/1528158523869
  NumStores:          1
  NumStoreFiles:      0
  UncompressedSize:   0 (less than 1MB)
  StoreFileSize:      0 (less than 1MB)
  MemStoreSize:       0 (less than 1MB)
  ReadRequestsCount:  0
  WriteRequestsCount: 0

  RegionServer:       edev06:35100
  RegionNum:          3
  RegionName:         TRAFODION.T002SCH.T002T2/1528158523869
  NumStores:          1
  NumStoreFiles:      0
  UncompressedSize:   0 (less than 1MB)
  StoreFileSize:      0 (less than 1MB)
  MemStoreSize:       0 (less than 1MB)
  ReadRequestsCount:  0
  WriteRequestsCount: 0

  RegionServer:       edev06:35100
  RegionNum:          4
  RegionName:         TRAFODION.T002SCH.T002T2/1528158523869
  NumStores:          1
  NumStoreFiles:      0
  UncompressedSize:   0 (less than 1MB)
  StoreFileSize:      0 (less than 1MB)
  MemStoreSize:       0 (less than 1MB)
  ReadRequestsCount:  0
  WriteRequestsCount: 0


--- SQL operation complete.
>>select * from t002t2;

--- 0 row(s) selected.
>>
>>insert into t002t2 values (1,2);

--- 1 row(s) inserted.
>>
>>sh regrhbase.ksh flush 'TRAFODION.T002SCH.T002T2';
>>sh regrhbase.ksh flush 'TRAFODION.T002SCH.T002T2I1';
>>
>>select * from t002t2;

A            B          
-----------  -----------

          1            2

--- 1 row(s) selected.
>>
>>obey TEST002(region_stats);
>>set schema t002sch;

--- SQL operation complete.
>>invoke table(region stats ());

-- Definition of Trafodion table TRAFODION.T002SCH.EXE_UTIL_REGION_STATS__
-- Definition current  Tue Jun  5 00:29:30 2018

  (
    CATALOG_NAME                     CHAR(256 BYTES) CHARACTER SET UTF8 COLLATE
      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , SCHEMA_NAME                      CHAR(256 BYTES) CHARACTER SET UTF8 COLLATE
      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , OBJECT_NAME                      CHAR(256 BYTES) CHARACTER SET UTF8 COLLATE
      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , REGION_SERVER                    CHAR(256 BYTES) CHARACTER SET UTF8 COLLATE
      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , REGION_NUM                       LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
  , REGION_NAME                      CHAR(512 BYTES) CHARACTER SET UTF8 COLLATE
      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , NUM_STORES                       INT NO DEFAULT NOT NULL NOT DROPPABLE
  , NUM_STORE_FILES                  INT NO DEFAULT NOT NULL NOT DROPPABLE
  , STORE_FILE_UNCOMP_SIZE           LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
  , STORE_FILE_SIZE                  LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
  , MEM_STORE_SIZE                   LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
  , READ_REQUESTS_COUNT              LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
  , WRITE_REQUESTS_COUNT             LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
  )

--- SQL operation complete.
>>invoke table(region stats (t002t1));

-- Definition of Trafodion table TRAFODION.T002SCH.EXE_UTIL_REGION_STATS__
-- Definition current  Tue Jun  5 00:29:31 2018

  (
    CATALOG_NAME                     CHAR(256 BYTES) CHARACTER SET UTF8 COLLATE
      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , SCHEMA_NAME                      CHAR(256 BYTES) CHARACTER SET UTF8 COLLATE
      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , OBJECT_NAME                      CHAR(256 BYTES) CHARACTER SET UTF8 COLLATE
      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , REGION_SERVER                    CHAR(256 BYTES) CHARACTER SET UTF8 COLLATE
      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , REGION_NUM                       LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
  , REGION_NAME                      CHAR(512 BYTES) CHARACTER SET UTF8 COLLATE
      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , NUM_STORES                       INT NO DEFAULT NOT NULL NOT DROPPABLE
  , NUM_STORE_FILES                  INT NO DEFAULT NOT NULL NOT DROPPABLE
  , STORE_FILE_UNCOMP_SIZE           LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
  , STORE_FILE_SIZE                  LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
  , MEM_STORE_SIZE                   LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
  , READ_REQUESTS_COUNT              LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
  , WRITE_REQUESTS_COUNT             LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
  )

--- SQL operation complete.
>>
>>select left(trim(schema_name) || '.' || trim(object_name), 14),
+>  region_num, region_name, num_stores, num_store_files,
+>  store_file_uncomp_size, store_file_size, mem_store_size,
+>  'ReadRequestsCount: ' || cast(read_requests_count as varchar(10)),
+>  'WriteRequestsCount: ' || cast(write_requests_count as varchar(10))
+> from table(region stats(t002t1));

(EXPR)                                                    REGION_NUM            REGION_NAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       NUM_STORES   NUM_STORE_FILES  STORE_FILE_UNCOMP_SIZE  STORE_FILE_SIZE       MEM_STORE_SIZE        (EXPR)                         (EXPR)
--------------------------------------------------------  --------------------  --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  -----------  ---------------  ----------------------  --------------------  --------------------  -----------------------------  ------------------------------

T002SCH.T002T1                                                               1  TRAFODION.T002SCH.T002T1/1528158469344                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      1                0                       0                     0                     0  ReadRequestsCount: 2           WriteRequestsCount: 1         

--- 1 row(s) selected.
>>
>>select left(trim(schema_name) || '.' || trim(object_name), 14),
+>  region_num, region_name, num_stores, num_store_files,
+>  store_file_uncomp_size, store_file_size, mem_store_size,
+>  'ReadRequestsCount: ' || cast(read_requests_count as varchar(10)),
+>  'WriteRequestsCount: ' || cast(write_requests_count as varchar(10))
+> from table(region stats(index t002t1i1));

(EXPR)                                                    REGION_NUM            REGION_NAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       NUM_STORES   NUM_STORE_FILES  STORE_FILE_UNCOMP_SIZE  STORE_FILE_SIZE       MEM_STORE_SIZE        (EXPR)                         (EXPR)
--------------------------------------------------------  --------------------  --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  -----------  ---------------  ----------------------  --------------------  --------------------  -----------------------------  ------------------------------

T002SCH.T002T1                                                               1  TRAFODION.T002SCH.T002T1I1/1528158479307                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    1                0                       0                     0                     0  ReadRequestsCount: 0           WriteRequestsCount: 1         

--- 1 row(s) selected.
>>
>>select left(trim(schema_name) || '.' || trim(object_name), 14),
+>  region_num, region_name, num_stores, num_store_files,
+>  store_file_uncomp_size, store_file_size, mem_store_size,
+>  'ReadRequestsCount: ' || cast(read_requests_count as varchar(10)),
+>  'WriteRequestsCount: ' || cast(write_requests_count as varchar(10))
+> from table(region stats(t002t2));

(EXPR)                                                    REGION_NUM            REGION_NAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       NUM_STORES   NUM_STORE_FILES  STORE_FILE_UNCOMP_SIZE  STORE_FILE_SIZE       MEM_STORE_SIZE        (EXPR)                         (EXPR)
--------------------------------------------------------  --------------------  --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  -----------  ---------------  ----------------------  --------------------  --------------------  -----------------------------  ------------------------------

T002SCH.T002T2                                                               1  TRAFODION.T002SCH.T002T2/1528158523869                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      1                0                       0                     0                     0  ReadRequestsCount: 0           WriteRequestsCount: 0         
T002SCH.T002T2                                                               2  TRAFODION.T002SCH.T002T2/1528158523869                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      1                0                       0                     0                     0  ReadRequestsCount: 1           WriteRequestsCount: 1         
T002SCH.T002T2                                                               3  TRAFODION.T002SCH.T002T2/1528158523869                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      1                0                       0                     0                     0  ReadRequestsCount: 0           WriteRequestsCount: 0         
T002SCH.T002T2                                                               4  TRAFODION.T002SCH.T002T2/1528158523869                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      1                0                       0                     0                     0  ReadRequestsCount: 0           WriteRequestsCount: 0         

--- 4 row(s) selected.
>>
>>select left(trim(schema_name) || '.' || trim(object_name), 14),
+>  region_num, region_name, num_stores, num_store_files,
+>  store_file_uncomp_size, store_file_size, mem_store_size,
+>  'ReadRequestsCount: ' || cast(read_requests_count as varchar(10)),
+>  'WriteRequestsCount: ' || cast(write_requests_count as varchar(10))
+> from table(region stats(index t002t2i1));

(EXPR)                                                    REGION_NUM            REGION_NAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       NUM_STORES   NUM_STORE_FILES  STORE_FILE_UNCOMP_SIZE  STORE_FILE_SIZE       MEM_STORE_SIZE        (EXPR)                         (EXPR)
--------------------------------------------------------  --------------------  --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  -----------  ---------------  ----------------------  --------------------  --------------------  -----------------------------  ------------------------------

T002SCH.T002T2                                                               1  TRAFODION.T002SCH.T002T2I1/1528158529016                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    1                0                       0                     0                     0  ReadRequestsCount: 1           WriteRequestsCount: 1         

--- 1 row(s) selected.
>>
>>select left(trim(schema_name) || '.' || trim(object_name), 14),
+> sum(store_file_size), 
+> 'ReadRequestsCount: '  || cast(sum(read_requests_count) as varchar(10)),
+> 'WriteRequestsCount: ' || cast(sum(write_requests_count) as varchar(10))
+> from table(region stats (using (select * from (
+>   get tables in schema t002sch, no header, return full names) x(a) ))) 
+> group by 1 order by 2 desc;

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

T002SCH.T002T2                                                               0  ReadRequestsCount: 1           WriteRequestsCount: 1         
T002SCH.SB_HIS                                                               0  ReadRequestsCount: 0           WriteRequestsCount: 0         
T002SCH.T002T1                                                               0  ReadRequestsCount: 2           WriteRequestsCount: 1         
T002SCH.SB_PER                                                               0  ReadRequestsCount: 0           WriteRequestsCount: 0         

--- 4 row(s) selected.
>>
>>select left(trim(schema_name) || '.' || trim(object_name), 14),
+> sum(store_file_size), 
+> 'ReadRequestsCount: '  || cast(sum(read_requests_count) as varchar(10)),
+> 'WriteRequestsCount: ' || cast(sum(write_requests_count) as varchar(10))
+> from table(region statistics (using (select * from (
+>   get tables in schema t002sch, no header, return full names) x(a) ))) 
+> group by 1 order by 2 desc;

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

T002SCH.T002T2                                                               0  ReadRequestsCount: 1           WriteRequestsCount: 1         
T002SCH.SB_HIS                                                               0  ReadRequestsCount: 0           WriteRequestsCount: 0         
T002SCH.T002T1                                                               0  ReadRequestsCount: 2           WriteRequestsCount: 1         
T002SCH.SB_PER                                                               0  ReadRequestsCount: 0           WriteRequestsCount: 0         

--- 4 row(s) selected.
>>
>>get region stats for table t002t1;


Stats Summary
=============

  ObjectName:              TRAFODION.T002SCH.T002T1
  NumRegions:              1
  RegionsLocation:         /hbase/data/default
  TotalNumStores:          1
  TotalNumStoreFiles:      0
  TotalUncompressedSize:   0
  TotalStoreFileSize:      0
  TotalMemStoreSize:       0
  TotalReadRequestsCount:  2
  TotalWriteRequestsCount: 1

Stats Details
=============

  RegionServer:       edev06:35100
  RegionNum:          1
  RegionName:         TRAFODION.T002SCH.T002T1/1528158469344
  NumStores:          1
  NumStoreFiles:      0
  UncompressedSize:   0 (less than 1MB)
  StoreFileSize:      0 (less than 1MB)
  MemStoreSize:       0 (less than 1MB)
  ReadRequestsCount:  2
  WriteRequestsCount: 1


--- SQL operation complete.
>>get region stats for index t002t1i1;


Stats Summary
=============

  ObjectName:              TRAFODION.T002SCH.T002T1I1
  NumRegions:              1
  RegionsLocation:         /hbase/data/default
  TotalNumStores:          1
  TotalNumStoreFiles:      0
  TotalUncompressedSize:   0
  TotalStoreFileSize:      0
  TotalMemStoreSize:       0
  TotalReadRequestsCount:  0
  TotalWriteRequestsCount: 1

Stats Details
=============

  RegionServer:       edev06:35100
  RegionNum:          1
  RegionName:         TRAFODION.T002SCH.T002T1I1/1528158479307
  NumStores:          1
  NumStoreFiles:      0
  UncompressedSize:   0 (less than 1MB)
  StoreFileSize:      0 (less than 1MB)
  MemStoreSize:       0 (less than 1MB)
  ReadRequestsCount:  0
  WriteRequestsCount: 1


--- SQL operation complete.
>>get region stats for table t002t2;


Stats Summary
=============

  ObjectName:              TRAFODION.T002SCH.T002T2
  NumRegions:              4
  RegionsLocation:         /hbase/data/default
  TotalNumStores:          4
  TotalNumStoreFiles:      0
  TotalUncompressedSize:   0
  TotalStoreFileSize:      0
  TotalMemStoreSize:       0
  TotalReadRequestsCount:  1
  TotalWriteRequestsCount: 1

Stats Details
=============

  RegionServer:       edev06:35100
  RegionNum:          1
  RegionName:         TRAFODION.T002SCH.T002T2/1528158523869
  NumStores:          1
  NumStoreFiles:      0
  UncompressedSize:   0 (less than 1MB)
  StoreFileSize:      0 (less than 1MB)
  MemStoreSize:       0 (less than 1MB)
  ReadRequestsCount:  0
  WriteRequestsCount: 0

  RegionServer:       edev06:35100
  RegionNum:          2
  RegionName:         TRAFODION.T002SCH.T002T2/1528158523869
  NumStores:          1
  NumStoreFiles:      0
  UncompressedSize:   0 (less than 1MB)
  StoreFileSize:      0 (less than 1MB)
  MemStoreSize:       0 (less than 1MB)
  ReadRequestsCount:  1
  WriteRequestsCount: 1

  RegionServer:       edev06:35100
  RegionNum:          3
  RegionName:         TRAFODION.T002SCH.T002T2/1528158523869
  NumStores:          1
  NumStoreFiles:      0
  UncompressedSize:   0 (less than 1MB)
  StoreFileSize:      0 (less than 1MB)
  MemStoreSize:       0 (less than 1MB)
  ReadRequestsCount:  0
  WriteRequestsCount: 0

  RegionServer:       edev06:35100
  RegionNum:          4
  RegionName:         TRAFODION.T002SCH.T002T2/1528158523869
  NumStores:          1
  NumStoreFiles:      0
  UncompressedSize:   0 (less than 1MB)
  StoreFileSize:      0 (less than 1MB)
  MemStoreSize:       0 (less than 1MB)
  ReadRequestsCount:  0
  WriteRequestsCount: 0


--- SQL operation complete.
>>get region stats for index t002t2i1;


Stats Summary
=============

  ObjectName:              TRAFODION.T002SCH.T002T2I1
  NumRegions:              1
  RegionsLocation:         /hbase/data/default
  TotalNumStores:          1
  TotalNumStoreFiles:      0
  TotalUncompressedSize:   0
  TotalStoreFileSize:      0
  TotalMemStoreSize:       0
  TotalReadRequestsCount:  1
  TotalWriteRequestsCount: 1

Stats Details
=============

  RegionServer:       edev06:35100
  RegionNum:          1
  RegionName:         TRAFODION.T002SCH.T002T2I1/1528158529016
  NumStores:          1
  NumStoreFiles:      0
  UncompressedSize:   0 (less than 1MB)
  StoreFileSize:      0 (less than 1MB)
  MemStoreSize:       0 (less than 1MB)
  ReadRequestsCount:  1
  WriteRequestsCount: 1


--- SQL operation complete.
>>
>>get region statistics for table t002t1;


Stats Summary
=============

  ObjectName:              TRAFODION.T002SCH.T002T1
  NumRegions:              1
  RegionsLocation:         /hbase/data/default
  TotalNumStores:          1
  TotalNumStoreFiles:      0
  TotalUncompressedSize:   0
  TotalStoreFileSize:      0
  TotalMemStoreSize:       0
  TotalReadRequestsCount:  2
  TotalWriteRequestsCount: 1

Stats Details
=============

  RegionServer:       edev06:35100
  RegionNum:          1
  RegionName:         TRAFODION.T002SCH.T002T1/1528158469344
  NumStores:          1
  NumStoreFiles:      0
  UncompressedSize:   0 (less than 1MB)
  StoreFileSize:      0 (less than 1MB)
  MemStoreSize:       0 (less than 1MB)
  ReadRequestsCount:  2
  WriteRequestsCount: 1


--- SQL operation complete.
>>
>>get region stats for 
+>    (select * from 
+>      (get tables in schema t002sch, no header, return full names)x(a));


Stats Summary
=============

  ObjectName:              TRAFODION.T002SCH.SB_HISTOGRAMS
  NumRegions:              1
  RegionsLocation:         /hbase/data/default
  TotalNumStores:          1
  TotalNumStoreFiles:      0
  TotalUncompressedSize:   0
  TotalStoreFileSize:      0
  TotalMemStoreSize:       0
  TotalReadRequestsCount:  0
  TotalWriteRequestsCount: 0

Stats Details
=============

  RegionServer:       edev06:35100
  RegionNum:          1
  RegionName:         TRAFODION.T002SCH.SB_HISTOGRAMS/1528158446488
  NumStores:          1
  NumStoreFiles:      0
  UncompressedSize:   0 (less than 1MB)
  StoreFileSize:      0 (less than 1MB)
  MemStoreSize:       0 (less than 1MB)
  ReadRequestsCount:  0
  WriteRequestsCount: 0


Stats Summary
=============

  ObjectName:              TRAFODION.T002SCH.SB_HISTOGRAM_INTERVALS
  NumRegions:              1
  RegionsLocation:         /hbase/data/default
  TotalNumStores:          1
  TotalNumStoreFiles:      0
  TotalUncompressedSize:   0
  TotalStoreFileSize:      0
  TotalMemStoreSize:       0
  TotalReadRequestsCount:  0
  TotalWriteRequestsCount: 0

Stats Details
=============

  RegionServer:       edev06:35100
  RegionNum:          1
  RegionName:         TRAFODION.T002SCH.SB_HISTOGRAM_INTERVALS/1528158454615
  NumStores:          1
  NumStoreFiles:      0
  UncompressedSize:   0 (less than 1MB)
  StoreFileSize:      0 (less than 1MB)
  MemStoreSize:       0 (less than 1MB)
  ReadRequestsCount:  0
  WriteRequestsCount: 0


Stats Summary
=============

  ObjectName:              TRAFODION.T002SCH.SB_PERSISTENT_SAMPLES
  NumRegions:              1
  RegionsLocation:         /hbase/data/default
  TotalNumStores:          1
  TotalNumStoreFiles:      0
  TotalUncompressedSize:   0
  TotalStoreFileSize:      0
  TotalMemStoreSize:       0
  TotalReadRequestsCount:  0
  TotalWriteRequestsCount: 0

Stats Details
=============

  RegionServer:       edev06:35100
  RegionNum:          1
  RegionName:         TRAFODION.T002SCH.SB_PERSISTENT_SAMPLES/1528158460979
  NumStores:          1
  NumStoreFiles:      0
  UncompressedSize:   0 (less than 1MB)
  StoreFileSize:      0 (less than 1MB)
  MemStoreSize:       0 (less than 1MB)
  ReadRequestsCount:  0
  WriteRequestsCount: 0


Stats Summary
=============

  ObjectName:              TRAFODION.T002SCH.T002T1
  NumRegions:              1
  RegionsLocation:         /hbase/data/default
  TotalNumStores:          1
  TotalNumStoreFiles:      0
  TotalUncompressedSize:   0
  TotalStoreFileSize:      0
  TotalMemStoreSize:       0
  TotalReadRequestsCount:  2
  TotalWriteRequestsCount: 1

Stats Details
=============

  RegionServer:       edev06:35100
  RegionNum:          1
  RegionName:         TRAFODION.T002SCH.T002T1/1528158469344
  NumStores:          1
  NumStoreFiles:      0
  UncompressedSize:   0 (less than 1MB)
  StoreFileSize:      0 (less than 1MB)
  MemStoreSize:       0 (less than 1MB)
  ReadRequestsCount:  2
  WriteRequestsCount: 1


Stats Summary
=============

  ObjectName:              TRAFODION.T002SCH.T002T2
  NumRegions:              4
  RegionsLocation:         /hbase/data/default
  TotalNumStores:          4
  TotalNumStoreFiles:      0
  TotalUncompressedSize:   0
  TotalStoreFileSize:      0
  TotalMemStoreSize:       0
  TotalReadRequestsCount:  1
  TotalWriteRequestsCount: 1

Stats Details
=============

  RegionServer:       edev06:35100
  RegionNum:          1
  RegionName:         TRAFODION.T002SCH.T002T2/1528158523869
  NumStores:          1
  NumStoreFiles:      0
  UncompressedSize:   0 (less than 1MB)
  StoreFileSize:      0 (less than 1MB)
  MemStoreSize:       0 (less than 1MB)
  ReadRequestsCount:  0
  WriteRequestsCount: 0

  RegionServer:       edev06:35100
  RegionNum:          2
  RegionName:         TRAFODION.T002SCH.T002T2/1528158523869
  NumStores:          1
  NumStoreFiles:      0
  UncompressedSize:   0 (less than 1MB)
  StoreFileSize:      0 (less than 1MB)
  MemStoreSize:       0 (less than 1MB)
  ReadRequestsCount:  1
  WriteRequestsCount: 1

  RegionServer:       edev06:35100
  RegionNum:          3
  RegionName:         TRAFODION.T002SCH.T002T2/1528158523869
  NumStores:          1
  NumStoreFiles:      0
  UncompressedSize:   0 (less than 1MB)
  StoreFileSize:      0 (less than 1MB)
  MemStoreSize:       0 (less than 1MB)
  ReadRequestsCount:  0
  WriteRequestsCount: 0

  RegionServer:       edev06:35100
  RegionNum:          4
  RegionName:         TRAFODION.T002SCH.T002T2/1528158523869
  NumStores:          1
  NumStoreFiles:      0
  UncompressedSize:   0 (less than 1MB)
  StoreFileSize:      0 (less than 1MB)
  MemStoreSize:       0 (less than 1MB)
  ReadRequestsCount:  0
  WriteRequestsCount: 0


--- SQL operation complete.
>>
>>-- stats for all indexes on a table
>>select left(trim(schema_name) || '.' || trim(object_name), 14),
+>  region_num, region_name, num_stores, num_store_files,
+>  store_file_uncomp_size, store_file_size, mem_store_size,
+>  'ReadRequestsCount: ' || cast(read_requests_count as varchar(10)),
+>  'WriteRequestsCount: ' || cast(write_requests_count as varchar(10))
+> from table(region stats (using (select * from (
+>   get indexes on table t002t1, no header, return full names) x(a) )));

(EXPR)                                                    REGION_NUM            REGION_NAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       NUM_STORES   NUM_STORE_FILES  STORE_FILE_UNCOMP_SIZE  STORE_FILE_SIZE       MEM_STORE_SIZE        (EXPR)                         (EXPR)
--------------------------------------------------------  --------------------  --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  -----------  ---------------  ----------------------  --------------------  --------------------  -----------------------------  ------------------------------

T002SCH.T002T1                                                               1  TRAFODION.T002SCH.T002T1I1/1528158479307                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    1                0                       0                     0                     0  ReadRequestsCount: 0           WriteRequestsCount: 1         
T002SCH.T002T1                                                               1  TRAFODION.T002SCH.T002T1I2/1528158488210                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    1                0                       0                     0                     0  ReadRequestsCount: 0           WriteRequestsCount: 1         

--- 2 row(s) selected.
>>get region stats for 
+>  (select * from 
+>    (get indexes on table t002t1, no header, return full names)x(a));


Stats Summary
=============

  ObjectName:              TRAFODION.T002SCH.T002T1I1
  NumRegions:              1
  RegionsLocation:         /hbase/data/default
  TotalNumStores:          1
  TotalNumStoreFiles:      0
  TotalUncompressedSize:   0
  TotalStoreFileSize:      0
  TotalMemStoreSize:       0
  TotalReadRequestsCount:  0
  TotalWriteRequestsCount: 1

Stats Details
=============

  RegionServer:       edev06:35100
  RegionNum:          1
  RegionName:         TRAFODION.T002SCH.T002T1I1/1528158479307
  NumStores:          1
  NumStoreFiles:      0
  UncompressedSize:   0 (less than 1MB)
  StoreFileSize:      0 (less than 1MB)
  MemStoreSize:       0 (less than 1MB)
  ReadRequestsCount:  0
  WriteRequestsCount: 1


Stats Summary
=============

  ObjectName:              TRAFODION.T002SCH.T002T1I2
  NumRegions:              1
  RegionsLocation:         /hbase/data/default
  TotalNumStores:          1
  TotalNumStoreFiles:      0
  TotalUncompressedSize:   0
  TotalStoreFileSize:      0
  TotalMemStoreSize:       0
  TotalReadRequestsCount:  0
  TotalWriteRequestsCount: 1

Stats Details
=============

  RegionServer:       edev06:35100
  RegionNum:          1
  RegionName:         TRAFODION.T002SCH.T002T1I2/1528158488210
  NumStores:          1
  NumStoreFiles:      0
  UncompressedSize:   0 (less than 1MB)
  StoreFileSize:      0 (less than 1MB)
  MemStoreSize:       0 (less than 1MB)
  ReadRequestsCount:  0
  WriteRequestsCount: 1


--- SQL operation complete.
>>
>>-- return summary stats only
>>get region stats for table t002t1, summary;


Stats Summary
=============

  ObjectName:              TRAFODION.T002SCH.T002T1
  NumRegions:              1
  RegionsLocation:         /hbase/data/default
  TotalNumStores:          1
  TotalNumStoreFiles:      0
  TotalUncompressedSize:   0
  TotalStoreFileSize:      0
  TotalMemStoreSize:       0
  TotalReadRequestsCount:  2
  TotalWriteRequestsCount: 1

--- SQL operation complete.
>>get region stats for index t002t1i1, summary;


Stats Summary
=============

  ObjectName:              TRAFODION.T002SCH.T002T1I1
  NumRegions:              1
  RegionsLocation:         /hbase/data/default
  TotalNumStores:          1
  TotalNumStoreFiles:      0
  TotalUncompressedSize:   0
  TotalStoreFileSize:      0
  TotalMemStoreSize:       0
  TotalReadRequestsCount:  0
  TotalWriteRequestsCount: 1

--- SQL operation complete.
>>get region stats for table t002t2, summary;


Stats Summary
=============

  ObjectName:              TRAFODION.T002SCH.T002T2
  NumRegions:              4
  RegionsLocation:         /hbase/data/default
  TotalNumStores:          4
  TotalNumStoreFiles:      0
  TotalUncompressedSize:   0
  TotalStoreFileSize:      0
  TotalMemStoreSize:       0
  TotalReadRequestsCount:  1
  TotalWriteRequestsCount: 1

--- SQL operation complete.
>>get region stats for index t002t2i1, summary;


Stats Summary
=============

  ObjectName:              TRAFODION.T002SCH.T002T2I1
  NumRegions:              1
  RegionsLocation:         /hbase/data/default
  TotalNumStores:          1
  TotalNumStoreFiles:      0
  TotalUncompressedSize:   0
  TotalStoreFileSize:      0
  TotalMemStoreSize:       0
  TotalReadRequestsCount:  1
  TotalWriteRequestsCount: 1

--- SQL operation complete.
>>
>>get region stats for 
+>  (select * from 
+>    (get tables in schema t002sch, no header, return full names)x(a)), summary;


Stats Summary
=============

  ObjectName:              TRAFODION.T002SCH.SB_HISTOGRAMS
  NumRegions:              1
  RegionsLocation:         /hbase/data/default
  TotalNumStores:          1
  TotalNumStoreFiles:      0
  TotalUncompressedSize:   0
  TotalStoreFileSize:      0
  TotalMemStoreSize:       0
  TotalReadRequestsCount:  0
  TotalWriteRequestsCount: 0

Stats Summary
=============

  ObjectName:              TRAFODION.T002SCH.SB_HISTOGRAM_INTERVALS
  NumRegions:              1
  RegionsLocation:         /hbase/data/default
  TotalNumStores:          1
  TotalNumStoreFiles:      0
  TotalUncompressedSize:   0
  TotalStoreFileSize:      0
  TotalMemStoreSize:       0
  TotalReadRequestsCount:  0
  TotalWriteRequestsCount: 0

Stats Summary
=============

  ObjectName:              TRAFODION.T002SCH.SB_PERSISTENT_SAMPLES
  NumRegions:              1
  RegionsLocation:         /hbase/data/default
  TotalNumStores:          1
  TotalNumStoreFiles:      0
  TotalUncompressedSize:   0
  TotalStoreFileSize:      0
  TotalMemStoreSize:       0
  TotalReadRequestsCount:  0
  TotalWriteRequestsCount: 0

Stats Summary
=============

  ObjectName:              TRAFODION.T002SCH.T002T1
  NumRegions:              1
  RegionsLocation:         /hbase/data/default
  TotalNumStores:          1
  TotalNumStoreFiles:      0
  TotalUncompressedSize:   0
  TotalStoreFileSize:      0
  TotalMemStoreSize:       0
  TotalReadRequestsCount:  2
  TotalWriteRequestsCount: 1

Stats Summary
=============

  ObjectName:              TRAFODION.T002SCH.T002T2
  NumRegions:              4
  RegionsLocation:         /hbase/data/default
  TotalNumStores:          4
  TotalNumStoreFiles:      0
  TotalUncompressedSize:   0
  TotalStoreFileSize:      0
  TotalMemStoreSize:       0
  TotalReadRequestsCount:  1
  TotalWriteRequestsCount: 1

--- SQL operation complete.
>>
>>-- where preds
>>select cast(trim(schema_name) || '.' || trim(object_name) as char(30) character set iso88591),
+> region_num, region_name, num_stores, num_store_files
+> from table(region stats(t002t1))
+> where object_name = 'T002T1';

(EXPR)                          REGION_NUM            REGION_NAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       NUM_STORES   NUM_STORE_FILES
------------------------------  --------------------  --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  -----------  ---------------

T002SCH.T002T1                                     1  TRAFODION.T002SCH.T002T1/1528158469344                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      1                0

--- 1 row(s) selected.
>>
>>select  cast(trim(object_name) as char(30) character set iso88591),
+> sum(store_file_size)
+> from table(region statistics (using (select * from (
+>   get tables in schema t002sch, no header, return full names) x(a) ))) 
+> where object_name like 'T002T1%'
+> group by 1 order by 2 desc;

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

T002T1                                             0

--- 1 row(s) selected.
>>
>>obey TEST002(cluster_stats);
>>invoke table(cluster stats());

-- Definition of Trafodion table TRAFODION.T002SCH.EXE_UTIL_CLUSTER_STATS__
-- Definition current  Tue Jun  5 00:29:37 2018

  (
    REGION_SERVER                    CHAR(256 BYTES) CHARACTER SET UTF8 COLLATE
      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , REGION_NAME                      CHAR(512 BYTES) CHARACTER SET UTF8 COLLATE
      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , CATALOG_NAME                     CHAR(256 BYTES) CHARACTER SET UTF8 COLLATE
      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , SCHEMA_NAME                      CHAR(256 BYTES) CHARACTER SET UTF8 COLLATE
      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , OBJECT_NAME                      CHAR(256 BYTES) CHARACTER SET UTF8 COLLATE
      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , NUM_STORES                       INT NO DEFAULT NOT NULL NOT DROPPABLE
  , NUM_STORE_FILES                  INT NO DEFAULT NOT NULL NOT DROPPABLE
  , STORE_FILE_UNCOMP_SIZE           LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
  , STORE_FILE_SIZE                  LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
  , MEM_STORE_SIZE                   LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
  , READ_REQUESTS_COUNT              LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
  , WRITE_REQUESTS_COUNT             LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
  )

--- SQL operation complete.
>>
>>select cast(trim(schema_name) || '.' || trim(object_name) as char(30) character set iso88591),
+> sum(store_file_size)
+> from table(cluster stats())
+> where schema_name = 'T002SCH'
+> group by 1 order by 2 desc;

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

T002SCH.SB_HISTOGRAM_INTERVALS                     0
T002SCH.T002T1I2                                   0
T002SCH.T002T1                                     0
T002SCH.SB_PERSISTENT_SAMPLES                      0
T002SCH.T002T1I1                                   0
T002SCH.T002T2                                     0
T002SCH.SB_HISTOGRAMS                              0
T002SCH.T002T2I1                                   0

--- 8 row(s) selected.
>>
>>select cast('RegionName: ' || trim(region_name) as char(30) character set iso88591),
+> sum(store_file_size)
+> from table(cluster stats())
+> where schema_name = 'T002SCH'
+> group by 1 order by 2 desc;

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

RegionName: 1528158446488                          0
RegionName: 1528158523869                          0
RegionName: 1528158488210                          0
RegionName: 1528158479307                          0
RegionName: 1528158469344                          0
RegionName: 1528158460979                          0
RegionName: 1528158454615                          0
RegionName: 1528158529016                          0

--- 8 row(s) selected.
>>
>>obey TEST002(hive_md);
>>cqd hive_max_string_length_in_bytes '20';

--- SQL operation complete.
>>invoke table(hivemd(tables));

-- Definition of Trafodion table TRAFODION.T002SCH.HIVEMD_TABLES__
-- Definition current  Tue Jun  5 00:29:38 2018

  (
    CATALOG_NAME                     CHAR(256 BYTES) CHARACTER SET UTF8 COLLATE
      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , SCHEMA_NAME                      CHAR(256 BYTES) CHARACTER SET UTF8 COLLATE
      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , TABLE_NAME                       CHAR(256 BYTES) CHARACTER SET UTF8 COLLATE
      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , FILE_FORMAT                      CHAR(24) CHARACTER SET ISO88591 COLLATE
      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , CREATE_TIME                      LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
  , NUM_COLS                         INT NO DEFAULT NOT NULL NOT DROPPABLE
  , NUM_PART_COLS                    INT NO DEFAULT NOT NULL NOT DROPPABLE
  , NUM_BUCKET_COLS                  INT NO DEFAULT NOT NULL NOT DROPPABLE
  , NUM_SORT_COLS                    INT NO DEFAULT NOT NULL NOT DROPPABLE
  , HIVE_OWNER                       CHAR(256 BYTES) CHARACTER SET UTF8 COLLATE
      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , FIELD_DELIMITER                  SMALLINT NO DEFAULT NOT NULL NOT DROPPABLE
  , RECORD_TERMINATOR                SMALLINT NO DEFAULT NOT NULL NOT DROPPABLE
  , NULL_FORMAT                      CHAR(8) CHARACTER SET ISO88591 COLLATE
      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , LOCATION                         CHAR(1024 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , HIVE_TABLE_TYPE                  CHAR(128 BYTES) CHARACTER SET UTF8 COLLATE
      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  )

--- SQL operation complete.
>>invoke table(hivemd(columns));

-- Definition of Trafodion table TRAFODION.T002SCH.HIVEMD_COLUMNS__
-- Definition current  Tue Jun  5 00:29:38 2018

  (
    CATALOG_NAME                     CHAR(256 BYTES) CHARACTER SET UTF8 COLLATE
      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , SCHEMA_NAME                      CHAR(256 BYTES) CHARACTER SET UTF8 COLLATE
      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , TABLE_NAME                       CHAR(256 BYTES) CHARACTER SET UTF8 COLLATE
      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , COLUMN_NAME                      CHAR(256 BYTES) CHARACTER SET UTF8 COLLATE
      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , SQL_DATA_TYPE                    CHAR(32) CHARACTER SET ISO88591 COLLATE
      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , FS_DATA_TYPE                     INT NO DEFAULT NOT NULL NOT DROPPABLE
  , DISPLAY_DATA_TYPE                CHAR(96) CHARACTER SET ISO88591 COLLATE
      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , HIVE_DATA_TYPE                   CHAR(32) CHARACTER SET ISO88591 COLLATE
      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , COLUMN_SIZE                      INT NO DEFAULT NOT NULL NOT DROPPABLE
  , CHARACTER_SET                    CHAR(40) CHARACTER SET ISO88591 COLLATE
      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , COLUMN_PRECISION                 INT NO DEFAULT NOT NULL NOT DROPPABLE
  , COLUMN_SCALE                     INT NO DEFAULT NOT NULL NOT DROPPABLE
  , DT_CODE                          INT NO DEFAULT NOT NULL NOT DROPPABLE
  , NULLABLE                         INT NO DEFAULT NOT NULL NOT DROPPABLE
  , COLUMN_NUMBER                    INT NO DEFAULT NOT NULL NOT DROPPABLE
  , PART_COL_NUMBER                  INT NO DEFAULT NOT NULL NOT DROPPABLE
  , BUCKET_COL_NUMBER                INT NO DEFAULT NOT NULL NOT DROPPABLE
  , SORT_COL_NUMBER                  INT NO DEFAULT NOT NULL NOT DROPPABLE
  , DATETIME_QUALIFIER               CHAR(28) CHARACTER SET ISO88591 COLLATE
      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , DATETIME_START_FIELD             INT NO DEFAULT NOT NULL NOT DROPPABLE
  , DATETIME_END_FIELD               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , DEFAULT_VALUE                    CHAR(240 BYTES) CHARACTER SET UTF8 COLLATE
      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  )
  PRIMARY KEY (CATALOG_NAME ASC, SCHEMA_NAME ASC, TABLE_NAME ASC)

--- SQL operation complete.
>>
>>process hive statement 'drop table thive002';

--- SQL operation complete.
>>process hive statement 'create table thive002 (a int, b string)';

--- SQL operation complete.
>>select cast(table_name as char(30) character set iso88591), 
+>       file_format, num_cols, num_part_cols, num_bucket_cols, num_sort_cols,
+>       field_delimiter, record_terminator, hive_table_type
+>  from table(hivemd(tables, "hive", "thive002"))
+>   where file_format = 'TEXTFILE';

(EXPR)                          FILE_FORMAT               NUM_COLS     NUM_PART_COLS  NUM_BUCKET_COLS  NUM_SORT_COLS  FIELD_DELIMITER  RECORD_TERMINATOR  HIVE_TABLE_TYPE
------------------------------  ------------------------  -----------  -------------  ---------------  -------------  ---------------  -----------------  --------------------------------------------------------------------------------------------------------------------------------

thive002                        TEXTFILE                            2              0                0              0                1                 10  MANAGED_TABLE                                                                                                                   

--- 1 row(s) selected.
>>select cast(table_name as char(30) character set iso88591), 
+>       file_format, num_cols, num_part_cols, num_bucket_cols, num_sort_cols,
+>       field_delimiter, record_terminator, hive_table_type
+>  from table(hivemd(tables)) where table_name = 'thive002'
+>  and file_format = 'TEXTFILE';

(EXPR)                          FILE_FORMAT               NUM_COLS     NUM_PART_COLS  NUM_BUCKET_COLS  NUM_SORT_COLS  FIELD_DELIMITER  RECORD_TERMINATOR  HIVE_TABLE_TYPE
------------------------------  ------------------------  -----------  -------------  ---------------  -------------  ---------------  -----------------  --------------------------------------------------------------------------------------------------------------------------------

thive002                        TEXTFILE                            2              0                0              0                1                 10  MANAGED_TABLE                                                                                                                   

--- 1 row(s) selected.
>>
>>cqd nested_joins 'OFF';

--- SQL operation complete.
>>select cast(column_name as char(30) character set iso88591), 
+>       sql_data_type, fs_data_type, hive_data_type,
+>       column_size, column_scale, 
+>       column_number, part_col_number, bucket_col_number, sort_col_number
+>  from table(hivemd(columns, "hive", "thive002")) C,
+>       table(hivemd(tables, "hive", "thive002")) T
+>   where C.table_name = T.table_name
+>    and T.file_format = 'TEXTFILE'
+>  order by column_number;

(EXPR)                          SQL_DATA_TYPE                     FS_DATA_TYPE  HIVE_DATA_TYPE                    COLUMN_SIZE  COLUMN_SCALE  COLUMN_NUMBER  PART_COL_NUMBER  BUCKET_COL_NUMBER  SORT_COL_NUMBER
------------------------------  --------------------------------  ------------  --------------------------------  -----------  ------------  -------------  ---------------  -----------------  ---------------

a                               SIGNED INTEGER                             132  int                                         4             0              0               -1                 -1               -1
b                               VARCHAR                                     64  string                                     20            -1              1               -1                 -1               -1

--- 2 row(s) selected.
>>
>>select cast(column_name as char(30) character set iso88591), 
+>       sql_data_type, fs_data_type, hive_data_type,
+>       column_size, column_scale, 
+>       column_number, part_col_number, bucket_col_number, sort_col_number
+>  from table(hivemd(tables, "hive", "thive002")) T,
+>       table(hivemd(columns, "hive", "thive002")) C
+>  where C.table_name = T.table_name
+>    and T.file_format = 'TEXTFILE'
+>    and T.table_name = 'thive002'
+>    and C.table_name = 'thive002'
+>  order by column_number;

(EXPR)                          SQL_DATA_TYPE                     FS_DATA_TYPE  HIVE_DATA_TYPE                    COLUMN_SIZE  COLUMN_SCALE  COLUMN_NUMBER  PART_COL_NUMBER  BUCKET_COL_NUMBER  SORT_COL_NUMBER
------------------------------  --------------------------------  ------------  --------------------------------  -----------  ------------  -------------  ---------------  -----------------  ---------------

a                               SIGNED INTEGER                             132  int                                         4             0              0               -1                 -1               -1
b                               VARCHAR                                     64  string                                     20            -1              1               -1                 -1               -1

--- 2 row(s) selected.
>>cqd nested_joins reset;

--- SQL operation complete.
>>
>>prepare s from select schema_name from table(hivemd(tables)) where table_name = 'thive002' and file_format = 'TEXTFILE';

--- SQL command prepared.
>>-- should return one row
>>execute s;

SCHEMA_NAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

hive                                                                                                                                                                                                                                                            

--- 1 row(s) selected.
>>-- should return one row
>>execute s;

SCHEMA_NAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

hive                                                                                                                                                                                                                                                            

--- 1 row(s) selected.
>>
>>process hive statement 'drop table thivearr';

--- SQL operation complete.
>>process hive statement 'create table thivearr (a array<string>)';

--- SQL operation complete.
>>-- should return warning 8742
>>select table_name from table(hivemd(columns)) where table_name = 'thivearr';

*** WARNING[8742] GetMetaDataInfo operation could not be completed. Reason: Datatype array<string> for column 'a' in table hive.hive.thivearr is not supported. This table will be ignored.

--- 0 row(s) selected.
>>
>>process hive ddl 'drop table thivealldt';

--- SQL operation complete.
>>process hive ddl 'create table thivealldt(a smallint, b int, c bigint, d decimal(3,1), e string, f char(10), g varchar(7), h date, i timestamp)';

--- SQL operation complete.
>>select fs_data_type, left(sql_data_type, 20) sql_data_type, left(display_data_type, 60) display_data_type, hive_data_type, column_precision, column_scale, column_size  from table(hivemd(columns, "hive","thivealldt"  ));

FS_DATA_TYPE  SQL_DATA_TYPE         DISPLAY_DATA_TYPE                                             HIVE_DATA_TYPE                    COLUMN_PRECISION  COLUMN_SCALE  COLUMN_SIZE
------------  --------------------  ------------------------------------------------------------  --------------------------------  ----------------  ------------  -----------

         130  SIGNED SMALLINT       SMALLINT                                                      smallint                                        15             0            2
         132  SIGNED INTEGER        INT                                                           int                                             31             0            4
         134  SIGNED LARGEINT       LARGEINT                                                      bigint                                          63             0            8
         130  SIGNED SMALLINT       NUMERIC(3, 1)                                                 decimal(3,1)                                     3             1            2
          64  VARCHAR               VARCHAR(20 BYTES) CHARACTER SET UTF8                          string                                          -1            -1           20
           0  CHARACTER             CHAR(10 CHARS) CHARACTER SET UTF8                             char(10)                                        -1            -1           40
          64  VARCHAR               VARCHAR(7 CHARS) CHARACTER SET UTF8                           varchar(7)                                      -1            -1           28
         192  DATETIME              DATE                                                          date                                             1             0            4
         192  DATETIME              TIMESTAMP(9)                                                  timestamp                                        3             6           11

--- 9 row(s) selected.
>>
>>create table createOptions (a int) HBASE_OPTIONS 
+>(max_versions = '3',
+> min_versions = '2',
+> time_to_live = '100',
+> blockcache = 'false',
+> in_memory = 'true',
+> compression = 'GZ',
+> bloomfilter = 'ROWCOL',
+> blocksize = '10000',
+> data_block_encoding = 'DIFF',
+> cache_blooms_on_write = 'false',
+>cache_data_on_write = 'false',
+>cache_indexes_on_write = 'false',
+>compact_compression = 'GZ',
+>prefix_length_key = '10',
+>evict_blocks_on_close = 'true',
+>keep_deleted_cells = 'false',
+>replication_scope = '0',
+>max_filesize = '4000000' ,
+>compact = 'true',
+>durability = 'async_wal',
+>memstore_flush_size = '2000000',
+>split_policy = 'org.apache.hadoop.hbase.regionserver.KeyPrefixRegionSplitPolicy',
+>CACHE_DATA_IN_L1 = 'false',
+>HDFS_STORAGE_POLICY = 'hot' ,
+>prefetch_blocks_on_open = 'false'
+>);

--- SQL operation complete.
>>
>>showddl createOptions ;

CREATE TABLE TRAFODION.T002SCH.CREATEOPTIONS
  (
    A                                INT DEFAULT NULL
  )
 ATTRIBUTES ALIGNED FORMAT
  HBASE_OPTIONS
  (
    MAX_VERSIONS = '3',
    MIN_VERSIONS = '2',
    TIME_TO_LIVE = '100',
    BLOCKCACHE = 'false',
    IN_MEMORY = 'true',
    COMPRESSION = 'GZ',
    BLOOMFILTER = 'ROWCOL',
    BLOCKSIZE = '10000',
    DATA_BLOCK_ENCODING = 'DIFF',
    CACHE_BLOOMS_ON_WRITE = 'false',
    CACHE_DATA_ON_WRITE = 'false',
    CACHE_INDEXES_ON_WRITE = 'false',
    COMPACT_COMPRESSION = 'GZ',
    PREFIX_LENGTH_KEY = '10',
    EVICT_BLOCKS_ON_CLOSE = 'true',
    KEEP_DELETED_CELLS = 'false',
    REPLICATION_SCOPE = '0',
    MAX_FILESIZE = '4000000',
    COMPACT = 'true',
    DURABILITY = 'async_wal',
    MEMSTORE_FLUSH_SIZE = '2000000',
    SPLIT_POLICY = 'org.apache.hadoop.hbase.regionserver.KeyPrefixRegionSplitPolicy',
    CACHE_DATA_IN_L1 = 'false',
    HDFS_STORAGE_POLICY = 'hot',
    PREFETCH_BLOCKS_ON_OPEN = 'false'
  )
;

--- SQL operation complete.
>>
>>log;
