请选择 进入手机版 | 继续访问电脑版
MSIPO技术圈 首页 IT技术 查看内容

<IBM DB2>《DB2高阶实用命令--自留》

2023-07-13

查看数据库安装路径/版本/

[db2inst1@localhost ~]$ db2level
DB21085I  This instance or install (instance name, where applicable: 
"db2inst1") uses "64" bits and DB2 code release "SQL11014" with level 
identifier "0205010F".
Informational tokens are "DB2 v11.1.4.4", "s1811091400", "DYN1811091400AMD64", 
and Fix Pack "4".
Product is installed at "/opt/ibm/db2/V11.1".

[db2inst1@localhost ~]$ /usr/local/bin/db2ls

Install Path                       Level   Fix Pack   Special Install Number   Install Date                  Installer UID 
---------------------------------------------------------------------------------------------------------------------
/opt/ibm/db2/V11.1               11.1.4.4        4                            Tue Mar 12 21:29:55 2019 CST             0 

查看/创建/更新管理器用户

//查看
[db2inst1@localhost ~]$ /opt/ibm/db2/V11.1/instance/daslist
//创建
./dascrt -u dasusr
//删除
./dasdrop

查看/创建/更新实例用户

//查看
[db2inst1@localhost ~]$ /opt/ibm/db2/V11.1/instance/db2ilist
db2inst1
//创建
./db2icrt -u db2fenc1 db2inst1
//删除
./db2idrop db2inst1
//设置自启动
./db2iauto -on db2inst1

查看数据库大小

$db2 "call GET_DBSIZE_INFO(?,?,?,0)"

1、DATABASESIZE为数据库大小,计算为dbsize=sum(used_pages*page_size),即每个SMS和DMS的已使用的空间总和。
2、DATABASECAPACITY为数据库可用大小,计算为dbability=sum(DMS usable_pages*page size) + sum (SMS容器大小+文件系统剩余容器空间大小)。同一个文件系统包含多个SMS容器,计算容量时只计算一次空闲容量。即,所有usable_pages页大小,加上文件系统表空间的剩余目录大小。
3、都可以使用db2pd -d dbname -tab获取相关参数值。
4、DATABASECAPACITY在分区数据库上该值不可用。
5、分区库总 DATABASESIZE为分区数据库使用的总大小。

检查数据库参数是否生效

db2 get db cfg for dbname show detail 

检查表空间情况

db2pd -d dbname -tab

检查reorg情况

db2pd -d dbname -reorg

检查runstats情况

db2pd -d dbname -runstats

检查表状态

db2 load query table tablename

检查索引状态

// 替换schema
// 替换tabname
-- 空值为正常
db2 "SELECT tabname,indname,iid,index_requires_rebuild FROM TABLE(sysproc.admin_get_index_info('I','schema','tabname')) where index_requires_rebuild='Y'"

检查数据库备份进度

db2pd -d dbname -utilities

降低表空间高水位

db2 "SELECT TBSP_NAME,RECLAIMABLE_SPACE_ENABLED FROM TABLE(MON_GET_TABLESPACE('',-2)) AS T WHERE T.TBSP_PAGE_TOP > T.TBSP_USED_PAGES"
-- 如果RECLAIMABLE_SPACE_ENABLED 为1 ,表示可以回收
db2 "alter tablespace space_name REDUCE MAX";  --最大程度降低高水位
//查看碎片移动情况
db2 "SELECT * FROM TABLE( MON_GET_EXTENT_MOVEMENT_STATUS('',-2))"
db2 "select tbsp_name, num_extents_moved, num_extents_left from table(mon_get_extent_movement_status('TS_PD_DATA_001', '-2'))"

rebind表相关程序包查看

//程序包为pkgschema.pkgname
select r.routineschema, r.routinename, p.pkgschema, p.pkgname, p.valid from syscat.routines r, syscat.packages p, syscat.procedures a , syscat.routinedep b where b.specificname=r.specificname and r.specificname = a.specificname and r.routinetype= 'P' and b.bname=p.pkgname and r.routineschema <> 'SYSIBMADM' and r.routinename in (select procname from syscat.procedures where specificname in (select dname from sysibm.sysdependencies where bname in (select pkgname from syscat.packagedep where bschema='$schema' and bname='$tb_name')))

merge into语句

merge into CWD_CA A
USING TBL_CODEBRANCH B
ON A.BRANCHCODE=B.BRANCHCODE AND B.RECESTS='1' AND A.PATCHNO='0'
UPDATE SET A SAFECODE=B.SAFECODE ,A.PATCHNO='1'

等同于

UPDATE CWD_CA A
SET SAFECODE=(SELECT SAFECODE FROM TBL_CODEBRANCH B WHERE A.BRANCHCODE=B.BRANCHCODE AND B.RECSTS='1',PATCHNO='0'
WHERE PATCHNO='0'

检查当前未提交事务

db2 list indoubt transactions with prompting

删除数据库信号

[db2inst1@localhost bin]$ ipcs -a | grep db2inst1
0x3b68f262 2621440    db2inst1   711        0            0     
0x00000000 458755     db2inst1   601        34156360   2          目标   
[db2inst1@localhost bin]$ ipcrm -s 458755
ipcrm: 已移除的 id (458755)

参考:《IBM DB2进程清理 for AIX》

相关阅读

热门文章

    手机版|MSIPO技术圈 皖ICP备19022944号-2

    Copyright © 2024, msipo.com

    返回顶部