如何查看IQ 中表是否分区以及分区的相关信息(2)

5. 查看表分区更详细的信息
    前面的文章介绍了查看表分区的一些基本信息，比如分区方法、分区所在的dbspace信息等。在这一小节里将向大家介绍如果通过访问IQ的系统表查看跟多表分区相关的详细信息，包括分区键、分区条件等。
    下面我们再创建一个"hash 分区"表，以及一个"哈希-范围"分区表。

--创建哈希分区表
CREATE TABLE lineitem
(
  l_orderkey int not null,
  l_partkey int,
  l_suppkey int,
  l_linenumber int not null,
  l_quantity numeric(10,2),
  l_extendedprice numeric(10,2),
  l_discount numeric(10,2),
  l_tax numeric(10,2),
  l_returnflag char(1),
  l_linestatus char(1),
  l_shipdate date,
  l_commitdate date,
  l_receiptdate date,
  l_shipinstruct char(25),
  l_shipmode char(10),
  l_comment varchar(44),
  PRIMARY KEY(l_orderkey,l_linenumber)
)
PARTITION BY HASH (l_orderkey,l_partkey,l_suppkey);

--创建"哈希-范围"分区表
CREATE TABLE table_2
(
  c1 bigint not null,
  c2 char(2),
  c3 date,   
  c4 varchar(200),
  PRIMARY KEY (c1)
)
PARTITION BY HASH (c4, c2)
SUBPARTITION BY RANGE (c3)
(
  P1 VALUES <= ('2012/03/31') ,
  P2 VALUES <= ('2012/06/30') ,
  P3 VALUES <= ('2012/09/30')
)

    下面是获得表分区详细信息的脚本

--get_par_info.sql
PARAMETERS table_name ;

select s.table_name,
    case
     when ps.partition_method = 1 then 'range partition'
     when ps.partition_method = 3 and ifnull(ps.subpartition_method,0,ps.subpartition_method) = 0 then 'hash partition'
     when ps.partition_method = 3 and ifnull(ps.subpartition_method,0,ps.subpartition_method) = 1 then 'hash-range partition'
     else 'unknow partition'
    end as partition_type,
    tc.column_name as partition_key,
    tc1.column_name as sub_partition_key,
    p.partition_object_id,
    p.partition_name,
    p.partition_values
 from systab s left outer join syspartitionscheme ps on s.object_id = ps.partitioned_object_id
    inner join syspartitionkey spk on spk.partitioned_object_id = ps.partitioned_object_id
    left outer join syspartition p on p.partitioned_object_id = ps.partitioned_object_id
    inner join systabcol tc on tc.column_id = spk.column_id and s.table_id = tc.table_id
    left outer join syssubpartitionkey sspk on sspk.partitioned_object_id = p.partitioned_object_id
    left outer join systabcol tc1 on tc1.column_id = sspk.column_id and s.table_id = tc1.table_id
 where s.table_name = '{table_name}'

   运行dbisql执行上面的sql脚本，并传递表明参数：
     dbisql -c "uid=DBA;pwd=sql" -onerror exit -nogui READ get_par_info.sql ['lineitem']
     dbisql -c "uid=DBA;pwd=sql" -onerror exit -nogui READ get_par_info.sql ['table_2']