DB2工具类SQL及常用命令

查看表load锁状态
SELECT DISTINCT tabname from sysibmadm.admintabinfo WHERE LOAD_STATUS=‘PENDING’ WITH UR

—查看表数据在dpf分区数据库节点分布情况
select ‘select dbpartitionnum(’ || c.colname || ‘), count(1) from ’ || rtrim(c.tabschema) || ‘.’ || rtrim(c.tabname) || ’ group by dbpartitionnum(’ || c.colname || ‘) order by dbpartitionnum(’ || c.colname || ‘) with ur;’
from syscat.columns c where c.tabname in (select tabname as tabname from syscat.tables
where tbspaceid = ‘22’) and partkeyseq > 0 with ur

释放表空间
db2 “alter tablespace TABLESPACENAME reduce max”

–重启数据库
db2 force applications all --杀除所有进程
db2stop --停止数据库
db2start --启动数据库
db2 activate db hrpams --激活实例

给用户赋权限
db2 “grant dbadm on database to user czams”

–激活数据库
db2 activate db ahnxods

–查看数据库回滚信息
db2pd -utilities -alldbpartitionnums

–询盘命令
cfgmgr -v

ftp启停命令
startsrc -t ftp
stopsrc -t ftp
stopsrc -s ssh
lssrc -t ftp
lssrc -s inetd

查看目前db2实例运行情况:
db2top -d hrpams
l

HPU单表数据全量数据卸载
export LANG=EN_US
/opt/IBM/HPU/V5.1/bin/db2hpu -d ahnxods -t ODS.NSOP_MIR_AEFM21 -o ODS.NSOP_MIR_AEFM21.ixf --format ixf

HPU导入单表海量数据
ln -s -f HISUSR.APP_HIS_SFTABG_201809.ixf HISUSR.APP_HIS_SFTABG_201809.ixf.000
ln -s -f HISUSR.APP_HIS_SFTABG_201809.ixf HISUSR.APP_HIS_SFTABG_201809.ixf.001
ln -s -f HISUSR.APP_HIS_SFTABG_201809.ixf HISUSR.APP_HIS_SFTABG_201809.ixf.002
ln -s -f HISUSR.APP_HIS_SFTABG_201809.ixf HISUSR.APP_HIS_SFTABG_201809.ixf.003
ln -s -f HISUSR.APP_HIS_SFTABG_201809.ixf HISUSR.APP_HIS_SFTABG_201809.ixf.004
ln -s -f HISUSR.APP_HIS_SFTABG_201809.ixf HISUSR.APP_HIS_SFTABG_201809.ixf.005
ln -s -f HISUSR.APP_HIS_SFTABG_201809.ixf HISUSR.APP_HIS_SFTABG_201809.ixf.006
ln -s -f HISUSR.APP_HIS_SFTABG_201809.ixf HISUSR.APP_HIS_SFTABG_201809.ixf.007
ln -s -f HISUSR.APP_HIS_SFTABG_201809.ixf HISUSR.APP_HIS_SFTABG_201809.ixf.008
ln -s -f HISUSR.APP_HIS_SFTABG_201809.ixf HISUSR.APP_HIS_SFTABG_201809.ixf.009
ln -s -f HISUSR.APP_HIS_SFTABG_201809.ixf HISUSR.APP_HIS_SFTABG_201809.ixf.010
ln -s -f HISUSR.APP_HIS_SFTABG_201809.ixf HISUSR.APP_HIS_SFTABG_201809.ixf.011
ln -s -f HISUSR.APP_HIS_SFTABG_201809.ixf HISUSR.APP_HIS_SFTABG_201809.ixf.012
ln -s -f HISUSR.APP_HIS_SFTABG_201809.ixf HISUSR.APP_HIS_SFTABG_201809.ixf.013
ln -s -f HISUSR.APP_HIS_SFTABG_201809.ixf HISUSR.APP_HIS_SFTABG_201809.ixf.014
ln -s -f HISUSR.APP_HIS_SFTABG_201809.ixf HISUSR.APP_HIS_SFTABG_201809.ixf.015
ln -s -f HISUSR.APP_HIS_SFTABG_201809.ixf HISUSR.APP_HIS_SFTABG_201809.ixf.016
db2 “LOAD From NSOP_MIR_ILFM30.ixf of ixf replace Into ods.NSOP_MIR_ILFM30 partitioned db config mode load_only_verify_part part_file_location .”

–查看哪些存储过程涉及到该表
SELECT PROCNAME FROM SYSCAT.PROCEDURES WHERE SPECIFICNAME IN
(SELECT DNAME FROM SYSIBM.SYSDEPENDENCIES WHERE BNAME IN (
SELECT PKGNAME FROM SYSCAT.PACKAGEDEP WHERE BNAME=‘TABLENAME’))

查看已编目数据库:db2 list db directory
查看数据库表空间详情:db2 list tablespaces show detail
db2 connect to HR94 user username using password

查询表空间在各个节点的使用情况
SELECT A.SNAPSHOT_TIMESTAMP,A.TBSP_NAME,B.TBSP_TYPE,A.TBSP_STATE,
SUM(100A.TBSP_USABLE_PAGESB.TBSP_PAGE_SIZE/1024/1024/1024) AS TOTAL,
SUM(100A.TBSP_USED_PAGESB.TBSP_PAGE_SIZE/1024/1024/1024) AS USED,
SUM(100*(A.TBSP_USABLE_PAGES-A.TBSP_USED_PAGES)*B.TBSP_PAGE_SIZE/1024/1024/1024) AS FREE
FROM SYSIBMADM.SNAPTBSP_PART A
INNER JOIN SYSIBMADM.SNAPTBSP B ON A.TBSP_ID=B.TBSP_ID
WHERE A.TBSP_NAME=‘TBS_MAI_DATA’
GROUP BY A.SNAPSHOT_TIMESTAMP,A.TBSP_NAME,B.TBSP_TYPE,A.TBSP_STATE

使用export导出数据
db2 "export to e:\NSOP_MIR_DPFM22.ixf of ixf messages e:\msgs.txt select * from ODS.NSOP_MIR_DPFM22 "
使用import导入数据
db2 “import from ODS.NSOP_MIR_DPFM26.ixf of ixf modified by compound=100 commitcount 10000 insert into ODS.NSOP_MIR_DPFM26”

查看部分表所占用磁盘空间
SELECT TABNAME,
SUM(DATA_OBJECT_P_SIZE+INDEX_OBJECT_P_SIZE+LONG_OBJECT_P_SIZE+
LOB_OBJECT_P_SIZE+XML_OBJECT_P_SIZE)/1024 TOTALSIZE_M
FROM SYSIBMADM.ADMINTABINFO
WHERE TABNAME IN (‘TABLENAME1’
,‘TABLENAME2’
,‘TABLENAME1’)
GROUP BY TABNAME