1)、DB2 查看表索引使用情况
db2 "SELECT SUBSTR(SI.INDSCHEMA, 1, 30) AS INDSCHEMA, SUBSTR(SI.INDNAME, 1, 30) AS INDNAME, MGI.INDEX_SCANS, MGI.INDEX_ONLY_SCANS FROM TABLE(MON_GET_INDEX('NETPAY这里输入你的schema', 'ORDER_HIS这里输入你的表名', -2)) as MGI, SYSCAT.INDEXES AS SI WHERE MGI.TABSCHEMA = SI.TABSCHEMA AND MGI.TABNAME = SI.TABNAME AND MGI.IID = SI.IID ORDER BY MGI.INDEX_SCANS DESC WITH UR" |more
DB2 –v 查看版本
最后一次使用情况 LASTUSED v9版本支持
db2 "select INDNAME,LASTUSED from SYSCAT.INDEXES where TABNAME='ORDER_HIS_NOT_PART'";
2)、单列创建分区
DB2分区表增加分区
最近,需要在DB2的分区表中增加新的分区,用于存储数据,因此,就研究了DB2表的分区机制。现在总结,实现的方式共有三种,分别是:
1)、创建临时表,将原表数据导入临时表,之后将原表重建,扩大分区;
2)、通过ALTER TABLE TAB_NAME ADD PARTITION,为表增加分区;
3)、创建临时分区表,向临时分区表中导入数据,之后将临时分区表设置为主表的一个分区。
下面将分别说明三种方式的具体实现:
前提:原分区表PART_MAIN,建表SQL为:
[sql]
CREATE TABLE PART_MAIN(
P_ID VARCHAR(10),
P_DATE DATE
)
PARTITION BY RANGE(P_DATE) (
PARTITION OTHERS STARTING MINVALUE ENDING '2012-03-31',
PARTITION P201204 STARTING '2012-04-01' ENDING '2012-04-30',
PARTITION P201205 STARTING '2012-05-01' ENDING '2012-05-31',
PARTITION P201206 STARTING '2012-06-01' ENDING '2012-06-30'
)
需要为PART_MAIN表,增加2012-12-01--2012-12-31的数据分区。
方法一:
a、创建临时表
[sql]
CREATE TABLE TMP_PART_MAIN LIKE PART_MAIN;
b、将数据导入临时表中
[sql]
INSERT INTO TMP_PART_MAIN SELECT *
FROM PART_MAIN;
c、重建原表
[sql]
DROP TABLE PART_MAIN;
[sql]
CREATE TABLE PART_MAIN(
P_ID VARCHAR(10),
P_DATE DATE
)
PARTITION BY RANGE(P_DATE) (
PARTITION OTHERS STARTING MINVALUE ENDING '2012-03-31',
PARTITION P201204 STARTING '2012-04-01' ENDING '2012-04-30',
PARTITION P201205 STARTING '2012-05-01' ENDING '2012-05-31',
PARTITION P201206 STARTING '2012-06-01' ENDING '2012-06-30',
PARTITION P201212 STARTING '2012-12-01' ENDING '2012-12-31'
)
d、将数据从临时表导入原表中
[sql]
INSERT INTO PART_MAIN SELECT * FROM TMP_PART_MAIN;
注:该方法是最简单的一种实现方式,不需要掌握DB2分区知识,就可以实现,对于老鸟来说,这种方式是不屑使用的。下面的方法将是技术提升阶段。
方法二:
a、为表增加分区
[sql]
ALTER TABLE PART_MAIN ADD PARTITION P201212 STARTING '2012-12-01' ENDING '2012-12-31'
;
b、增加分区OK,插入数据即可
注:该方法是最直接的,只要记住ALTER TABLE TAB_NAME ADD PARTITION命令就OK。
方法三:
a、创建临时分区表
[sql]
CREATE TABLE PART_OTHER (
P_ID VARCHAR(10),
P_DATE DATE
)
PARTITION BY RANGE(P_DATE) (
PARTITION P201212 STARTING '2012-12-01' ENDING '2012-12-31'
)
;
b、往临时表中增加数据(此步可以省略,在最后一步执行)
c、将临时分区表设为原表的一个分区
[sql]
ALTER TABLE PART_MAIN ATTACH
PARTITION P201212 STARTING '2012-12-01' ENDING '2012-12-31'
FROM PART_OTHER
;
注:此命令执行后,临时分区表PART_OTHER,将不以表的方式存在,而是作为PART_MAIN表的一个分区。
d、为原表新增加的分区,设置访问权限
[sql]
SET INTEGRITY FOR PART_MAIN
ALLOW WRITE ACCESS
IMMEDIATE CHECKED
;
OK,分区增加完成,可以往新分区中增加数据了。
注:该方法实现起来过于繁琐,建议了解就好
3)、多列建分区
CREATE TABLE sales(year INT, month INT, …)
PARTITION BY RANGE(year, month)( STARTING (2000,1) ENDING (2000,3),STARTING (2000,4) ENDING (2000,6),STARTING (2000,7) ENDING (2000,9),STARTING (2000,10) ENDING (2000,12),STARTING (2001,1) ENDING (2001,3));
4)、自动建分区
db2 "CREATE TABLE ORDER_HIS_ZZF(
ORDER_SEQ_ID VARCHAR(16) NOT NULL,
MER_ID VARCHAR(15),
CP_TIME TIMESTAMP DEFAULT CURRENT TIMESTAMP NOT NULL,
CONSTRAINT P_ORDER_HIS_ZZF PRIMARY KEY (ORDER_SEQ_ID)
)
PARTITION BY RANGE(CP_TIME)
(STARTING MINVALUE,
STARTING '2010-01-01 00:00:00' ENDING '2017-12-31 23:59:59'
EVERY 1 MONTH,
ENDING MAXVALUE)
5)、创建、查看分区索引使用情况
创建分区索引:Create index idx_parti_DW_DEP_ACCT_DETAIL_HISTORY_01 on DW_DEP_ACCT_DETAIL_HISTORY (DATA_DT) partitioned;
创建非分区索引:Create index idx_parti_DW_DEP_ACCT_DETAIL_HISTORY_02 on DW_DEP_ACCT_DETAIL_HISTORY (DATA_DT) not partitioned;
查看分区索引信息:
select substr(TABNAME ,1,10) t_name,substr(DATAPARTITIONNAME ,1,10) part_name,DATAPARTITIONID ,substr(LOWVALUE,1,12) low_value,substr(HIGHVALUE,1,12) high_value from syscat.datapartitions where tabname='DW_DEP_ACCT_DETAIL_HISTORY' order by datapartitionid;
6)、创建数据表
db2 -tvf order_his_test.sql
7)、导入导出数据
导入
db2 "import from order_his.ixf of ixf COMMITCOUNT 10000 messages msgimport.txt insert into netpay.order_his_test";
导出
db2 export to myfile.ixf of ixf messages msg.txt select * from staff
8)、查看数据表的ddl
Dd2look –h 查看帮助
db2look -d dactest -t order_his -a -e -c ;
db2look -d dactest -t order_his PAYMENT_LOG -a -e -c ;
9)、查看表分区
db2 "select DATAPARTITIONNAME from syscat.datapartitions t where tabschema = 'NETPAY' and tabname ='ORDER_HIS’ with ur;
select t.DATAPARTITIONNAME from syscat.datapartitions t where tabname ='CERTIFITIOIN_ORDER' with ur
10)、查看执行计划
db2expln -d appdb -q "select * from xxx" -i -t -g -o exp2.out
11)、查看索引
db2 "select * from SYSCAT.INDEXES where TABNAME='ORDER_HIS_TEST'";
db2 "select indname from SYSCAT.INDEXES where TABNAME='ORDER_HIS_TEST'";
12)、创建、删除索引
db2 "CREATE INDEX NETPAY.IDX_MC_ORD_HIS_TEST ON NETPAY.ORDER_HIS_TEST(MER_ID,CP_TIME) ALLOW REVERSE SCANS";
db2 "DROP INDEX NETPAY.IDX_MC_ORD_HIS_TEST";
13)、表统计runstats
//跑所有的索引
db2 RUNSTATS ON TABLE netpay.ORDER_HIS WITH DISTRIBUTION AND DETAILED INDEXES ALL;
//跑某个索引
db2 RUNSTATS ON TABLE "NETPAY"."ALLOCATE_LOG" FOR INDEX "NETPAY"."IDX_ALLO_A29_NEW";
14)、db2advis
//db2advis
在linux终端直接执行
db2advis -d appdb -s "select * from (select ORD_SEQ_ID, CP_ORD_ID, CP_TIME, MER_ID, BUSI_ID, ORD_ID, ORD_AMT, CURY_ID, VERSION,BG_RET_URL, PAGE_RET_URL, GATE_ID, PARAM1, PARAM2, PARAM3, PARAM4, PARAM5, PARAM6, PARAM7,PARAM8, PARAM9, PARAM10, SHARE_TYPE, SHARE_DATA, SHARE_STAT, SHARE_SEQ_ID, SHARE_TIME, PRIV1,CHK_VALUE, ORD_DESC, PAY_STAT, PAY_NUM, PAY_AMT, PAY_TIME, PAY_SEQ_ID, REF_TYPE, REF_NUM,REF_AMT, REF_TIME, REF_SEQ_ID, REF_REQ_NUM, REF_REQ_AMT, REF_REQ_TIME, PAY_END_TIME,CUSTOM_IP, RECON_DATE, RECON_STAT, LIQ_DATE, LIQ_STAT, FEE, NOT_STAT, NOT_NUM, NOT_TIME,REG_TIME, MOD_TIME,FEE_ORD_AMT, FEE_TIME, FEE_RULE_ID,(rownumber() over(order by ord_seq_id asc)) as rownum from netpay.ORDER_HIS a where 1=1 and a.MER_ID = '808080290000001' and CP_TIME>= timestamp('2016-06-15 00:00:00') and CP_TIME<= timestamp('2016-12-15 00:00:00')) b where rownum between 0 and 1000 FETCH FIRST 60000 ROWS ONLY with ur" –g -o advis_result.txt
//对当前查询推荐索引
db2advis -d appdb -s "select count(1) from NETPAY.ORDER_HIS where 1=1 and MER_ID = '808080290000001' and to_char(CP_TIME,'yyyy-mm-dd hh24:mi:ss')>='2016-09-15 00:00:00' and to_char(CP_TIME,'yyyy-mm-dd hh24:mi:ss')<='2016-10-15 08:47:04' and PAY_STAT='1001'" -o result.out
//全库统计
db2advis -d appdb -g -o result.out
test
15)、通过xshell连接db2数据库
db2 connect to appdb;
16)、”A” cann’t Numeric format
数据库连接不上,驱动问题,lib下db2jcc.jar删除,
更换成db2jcc4.jar
17)、在原表基础上新增分区
db2 “alter table stdas.t_order_info add partition P201707 starting ‘20170701’ ending ‘20170731’”
18)、删除数据
如果表没有做分区
delete from CERTIFITIOIN_ORDER where length(card_no) <64 and mer_ord_date between '20171101' and '20171230' with ur
不超过80万条
如果表有分区
1、查询需要删除掉的分区:
select t.DATAPARTITIONNAME from syscat.datapartitions t where tabname = '?' with ur
syscat.datapartitions表存放所有的分区,根据表名时间查询出所要删除的分区名。
2、detach分区到一张临时表(该操作会创建临时表,临时表已存在会报错),detach是分离分区的意思:
alter table 表名 detach partition 分区名 into table 临时表名;
3、最后再drop掉临时表。
19)、改了字段后需要重构表结构DB2 SQL Error: SQLCODE=-20054, SQLSTATE=55019
db2 connect to stdas user db2inst1 using db2inst1
db2 reorg table stdas.T_BIND_INFO
20)、DB2 SQL Error: SQLCODE=-668, SQLSTATE=57016 解决方案
dbvisualizer客户端执行下面sql:
CALL SYSPROC.ADMIN_CMD('REORG table schema.tableName ') ;
CALL SYSPROC.ADMIN_CMD('REORG table comm.T_MER_CONFIG ')
21)、启动DB2与关闭
[db2inst1@localhost ~]$ db2start
db2stop 普通关闭 db2stop force 强制关闭
22)、连接数据库执行sql
db2 connect to CPTPDB user db2inst1 using db2inst1
db2 alter table netpay.AP_TRANS_INFO drop column SDK_VSERSION_NAME
23). SQLCODE: -551, SQLSTATE: 42501,表明无权限执行
grant insert on table NETPAY.AP_TRANS_INFO to user NETPAY;
grant update on table NETPAY.AP_TRANS_INFO to user NETPAY;
grant select on table NETPAY.AP_TRANS_INFO to user NETPAY;
grant delete on table NETPAY.AP_TRANS_INFO to user NETPAY;
24). Dbvisualizer各种中文乱码问题解决方法
1.SQL Commander里中文显示成‘口’
解:进入Tools -> Tool Properties -> General -> Appearence -> Fonts,把最上边的三个字体都改成宋体或其他可正常显示中文的字体,改完apply,重新启动Dbvisualizer就好了。
2.表数据中中文显示成问号
解:先断开连接,然后在创建连接时的Database一栏的数据库名后面加上“?generateSimpleParameterMetadata=true&useUnicode=true&characterEncoding=utf8”,然后再连接应该就可以了。
注意:这种方法不会自动把问号变成中文,要手动把问号改成中文,存量问号太多的话,就需要重新导入数据了。
如果新增数据中文还是显示成问号的话,试试把所有的编码都改成UTF8,如下:
①进入Tools -> Tool Properties -> File Encoding,把编码改成UTF-8
②在填写连接IP端口的界面点进下一个Properties标签页,左边菜单中有个Encoding,点进去之后改成UTF-8
③修改数据库字符集编码,如MYSQL数据库的话就运行如下语句:
set character_set_server=utf8;
set character_set_client=utf8;
set character_set_connection=utf8;
set character_set_results=utf8;
- 、设置最大连接数
查看最大连接数
db2 get dbm cfg
设置最大连接数1000
db2 update db cfg using MAXAPPLS 1000