1. db2look 导出ddl的一些用法:
db2look -d zjbi2-z 用户名 -e -o bi_bo.ddl -i bi -w pwd
db2look -d sample -e -o sample.ddl -i lenovo -w cuinan -z lenovo
db2look -d zjolap -z BI -e -i biweb -w pwd -t LIFECYCLE_INVALID_LIST_201102
2. 编目数据库:
db2 catalogtcpip node node1 remote server_ip server server_port
db2 catalog dbttest as test1 at node1
3. 删除表分区:
alter table IDX_2G_USER_MOBILE_flag_D_201104 detachpartition part6 into table IDX_2G_USER_MOBILE_D_201104_part6;
drop table IDX_2G_USER_MOBILE_D_201104_part6;
4. Runstats,reorg,reorgchk 语法
db2 "RUNSTATS ON TABLE bi.dwd_res_terminal_201105 withdistribution on key columns and index all ALLOW WRITE ACCESS TABLESAMPLESYSTEM(10)"
REORG TABLE BI.hahahaha ALLOW READ ACCESS;
db2 reorgchk update statistics on tablebi.IDX_2G_DETAIL_DIM_BARAD_ID_tem
5. 强制表走索引扫描:
alter table table_name volatile cardinality
6. 几个常用视图及表:
查看数据库锁:select * from SYSIBMADM.LOCKWAITS
查看超时语句:select * from SYSIBMADM.LONG_RUNNING_SQL
查看节点日志使用:select * from sysibmadm.log_utilization order by dbpartitionnum
查看数据库配置:SELECT * FROM SYSIBMADM.DBCFG
7. 查找指定目录下指定文件包含某些文字内容的例子:
find . -name "*.tcl" |xargs grep"DBTruncatePartitionTable" |cut -d ":" -f 1 |grep ^./ |uniq|cut -d "/" -f 2 > haha.log
8. 查看表空间数据占存储大小:
SELECT TBSP_ID,SUM(DATA_OBJECT_PAGES)*32/float(1024*1024)
FROMSYSIBMADM.SNAPTAB
WHERETBSP_ID in (6,7,8,9,10) and TABNAME like'%_201011'
GROUP BY TBSP_ID
9. 查看某分区最早运行进程:
db2terminate;
ExportDB2NODE=1
db2connect to zjbi2
db2 get snapshot for database on zjbi2|grep oldest
10. 找出需要做runstates表的语句:
SELECT'db2 "RUNSTATS ON TABLE bi.'|| c.TABNAME ||' with distribution on key columns and index all ALLOW WRITE ACCESSTABLESAMPLE SYSTEM(20)" 'FROM(select B.TBSPACE,T.TABNAME,SUM(T.DATA_OBJECT_PAGES)*32.0/1024.0/1024.0 ASSIZEGB
Fromtable(snap_get_tab_V91('zjbi2')) AS T ,SYSCAT.TABLESPACES B
whereT.tabschema='BI' ANDT.TBSP_ID=B.TBSPACEID
GROUP BYB.TBSPACE,T.TABNAME) A,SYSCAT.TABLES C
WHEREC.TABNAME=A.TABNAME(+) AND C.TABNAME LIKE '%201012' AND A.TABNAME IS NULL
and c.tabschema='BI'
11. 修改密码:db2 connect to DB_Name user UserName using OldPassword newNewPassword confirm NewPassword