DB2 10.5 with BLU Acceleration 使用内存柱技术（in-memory columnar technologies）加快了分析和报告速度。按列组织的表将会存储磁盘上单独一组页面的每一列，在磁盘上按照列组织数据可以减少处理查询所需的 I/O 数量，因为只需要访问查询中引用的列，主要适合 OLAP 类型应用。目前只支持db210.5及以上版本
DB2_WORKLOAD注册表变量
V11.5,设置DB2_WORKLOAD为ANALYTICS
[sjck@sjck00 root]$ db2set -all
[i] DB2COMM=TCPIP
[g] DB2SYSTEM=sjck00
[sjck@sjck00 root]$ db2set DB2_WORKLOAD=ANALYTICS
[sjck@sjck00 root]$ db2set -all
[i] DB2_WORKLOAD=ANALYTICS
[i] DB2_USE_ALTERNATE_PAGE_CLEANING=ON [DB2_WORKLOAD]
[i] DB2_ANTIJOIN=EXTEND [DB2_WORKLOAD]
[i] DB2COMM=TCPIP
[g] DB2SYSTEM=sjck00
创建utf8编码，identity的数据库
[sjck@dw_dpf1 ~]$ db2 create database sjckutf8 using codeset utf-8 territory cn collate using identity pagesize 32 K
DB20000I The CREATE DATABASE command completed successfully.
[sjck@dw_dpf1 ~]$ db2 list db directory
System Database Directory
Number of entries in the directory = 2
Database 1 entry:
Database alias = SJCKDB
Database name = SJCKDB
Local database directory = /*/database
Database release level = 15.00
Comment =
Directory entry type = Indirect
Catalog database partition number = 0
Alternate server hostname =
Alternate server port number =
Database 2 entry:
Database alias = SJCKUTF8
Database name = SJCKUTF8
Local database directory = /*/db2home/sjck
Database release level = 15.00
Comment =
Directory entry type = Indirect
Catalog database partition number = 0
Alternate server hostname =
Alternate server port number =
创建列式存储的表
刚建好只能load，不能select和insert
[sjck@db2master root]$ db2 connect to sjckutf8
Database Connection Information
Database server = DB2/LINUXX8664 11.5.0.0
SQL authorization ID = SJCK
Local database alias = SJCKUTF8
[sjck@dw_dpf1 ~]$ db2 "create table sjrh.testinfo (empno char(6),lastname varchar(15),hirdate date,salary decimal(9),comm decimal(9)) organize by column"
DB20000I The SQL command completed successfully.
[sjck@dw_dpf1 ~]$ db2 "LOAD FROM /tmp/del/test.del OF DEL MODIFIED BY COLDEL,REPLACE INTO sjrh.testinfo";
Agent Type Node SQL Code Result
______________________________________________________________________________
LOAD 000 +00000000 Success.
______________________________________________________________________________
LOAD 001 +00000000 Success.
______________________________________________________________________________
LOAD 002 +00003107 Success.
______________________________________________________________________________
LOAD 003 +00000000 Success.
______________________________________________________________________________
PARTITION 001 +00000000 Success.
______________________________________________________________________________
PARTITION 002 +00000000 Success.
______________________________________________________________________________
PRE_PARTITION 000 +00000000 Success.
______________________________________________________________________________
RESULTS: 4 of 4 LOADs completed successfully.
______________________________________________________________________________
Summary of Partitioning Agents:
Rows Read = 1
Rows Rejected = 0
Rows Partitioned = 1
Summary of LOAD Agents:
Number of rows read = 1
Number of rows skipped = 0
Number of rows loaded = 1
Number of rows rejected = 0
Number of rows deleted = 0
Number of rows committed = 1
SQL3107W At least one warning message was encountered during LOAD processing.
修改配置
参数INTRA_PARALLEL,影响插入;CUR_COMMIT,影响查询;
[sjck@sjck00 ~]$ db2 get dbm cfg|grep INTRA_PARALLEL
Enable intra-partition parallelism (INTRA_PARALLEL) = NO
[sjck@sjck00 ~]$ db2 update dbm cfg using INTRA_PARALLEL YES
DB20000I The UPDATE DATABASE MANAGER CONFIGURATION command completed
successfully.
[sjck@sjck00 ~]$ db2 get dbm cfg|grep INTRA_PARALLEL
Enable intra-partition parallelism (INTRA_PARALLEL) = YES
[sjck@sjck00 ~]$ db2 get db cfg for sjckutf8|grep CUR_COMMIT
Currently Committed (CUR_COMMIT) = ON
自动空间回收
auto_reorg设置为on
[sjck@sjck00 ~]$ db2 get db cfg for sjckutf8|grep AUTO_REORG
Automatic reorganization (AUTO_REORG) = OFF
[sjck@sjck00 ~]$ db2 update db cfg for sjckutf8 using auto_reorg on
DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.
[sjck@sjck00 ~]$ db2 update db cfg for sjckutf8 using auto_maint on
DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.
[sjck@sjck00 ~]$ db2 update db cfg for sjckutf8 using auto_tbl_maint on
DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.
[sjck@sjck00 ~]$ db2 get db cfg for sjckutf8|grep AUTO_REORG
Automatic reorganization (AUTO_REORG) = ON
手动回收
后面加上RECLAIM EXTENTS
#列式
db2 reorgtable db2inst2.employee RECLAIM EXTENTS
#行式
db2 reorgtable db2inst2.employee
重启后可以查询
[sjck@sjck00 ~]$ db2 "insert into sjrh.testinfo (empno,lastname) values (333,444)"
DB20000I The SQL command completed successfully.
[sjck@sjck00 ~]$ db2 "select * from sjrh.testinfo"
EMPNO LASTNAME HIRDATE SALARY COMM
------ --------------- ---------- ----------- -----------
333 444 - - -
1 record(s) selected.
查看表所占page页大小
14*32k=448k
[sjck@sjck0 ~]$ db2 "select fpages from syscat.tables where TABNAME='TESTINFO'"
FPAGES
--------------------
14
1 record(s) selected.
将按行组织的表转换为按列组织的表
db2 "select tabname,tabschema,tableorg from syscat.tables where tableorg='C'"
[sjck@sjck00 ~]$ db2convert -d sjckutf8 -z sjrh -t test
Proceeding with the conversion...
Table RowsNum RowsComm Status Progress (%)
--------------------------------------- --------------- --------------- --------------- ---------------
"SJRH"."TEST" 1 0 UNSTARTED 0.00
Table RowsNum RowsComm Status Progress (%)
--------------------------------------- --------------- --------------- --------------- ---------------
"SJRH"."TEST" 1 0 INIT 100.00
Table RowsNum RowsComm Status Progress (%)
--------------------------------------- --------------- --------------- --------------- ---------------
"SJRH"."TEST" 1 0 COPY 0.00
Table RowsNum RowsComm Status Progress (%)
--------------------------------------- --------------- --------------- --------------- ---------------
"SJRH"."TEST" 1 0 COPY 100.00
Table RowsNum RowsComm Status Progress (%)
--------------------------------------- --------------- --------------- --------------- ---------------
"SJRH"."TEST" 0 0 REPLAY 0.00
Table RowsNum RowsComm Status Progress (%)
--------------------------------------- --------------- --------------- --------------- ---------------
"SJRH"."TEST" 0 0 REPLAY 100.00
Table RowsNum RowsComm Status Progress (%)
--------------------------------------- --------------- --------------- --------------- ---------------
"SJRH"."TEST" 0 0 SWAP 0.00
Table RowsNum RowsComm Status Progress (%)
--------------------------------------- --------------- --------------- --------------- ---------------
"SJRH"."TEST" 0 0 SWAP 100.00
Final Summary:
Table RowsNum InitSize (MB) FinalSize (MB) CompRate (%) State
--------------------------------------- --------------- --------------- --------------- --------------- ---------------
"SJRH"."TEST" 1 28.00 102.00 -264.29 Completed
Pre-Conversion Size (MB): 28.00
Post-Conversion Size (MB): 102.00
Compression Rate (Percent): -264.29
SQL2446I The db2convert command completed successfully. All row-organized tables that satisfy the specified matching criteria have been converted to column-organized tables.