DB2相关知识点记录

Ta曰丿、她活

已于 2024-02-27 11:28:54 修改

阅读量1.1k

点赞数 1

分类专栏： DB2 文章标签： DB2

于 2019-09-11 10:59:38 首次发布

本文链接：https://blog.csdn.net/zlw_spider/article/details/97372904

版权

DB2 专栏收录该内容

9 篇文章 1 订阅

订阅专栏

【1】触发器

DROP TRIGGER DELETE_API_JHNEWS@
  CREATE TRIGGER DELETE_API_JHNEWS
  NO CASCADE BEFORE
  INSERT ON API_JHNEWS
  REFERENCING NEW AS NEWROW 
  FOR EACH ROW 
  MODE DB2SQL
  BEGIN ATOMIC
    DELETE FROM api_jhnews WHERE creat_date < CURRENT DATE - 1 DAY;
  END;

  第一行：创建触发器，指定触发器名称
  第二行：指定触发时机，如after、before、instead of
          当为before时，需加上no cascade，防止触发器级联
  第三行：触发事件，以及涉及的表
  第四行：操作前后的行记录，new 、old其中new代表新建好的行，old表示新删除的行，当使用new 和old时，
          不能用for each statemen语句，只能用for each row
  第五、六行：指定触发的次数，
          for each row,for each statement前者是按行触发，后者按语句触发如果一条SQL语句要修改100条记录，
          for each row要执行100次而for each statement执行1次
  第七行：内容开始 atomic 可用于错误发生数据回滚，非必要项
  第八行：触发操作
  第九行：结束

【2】集群数据库查看每个节点数据分布，查看数据是否均匀到每个节点

SELECT dbpartitionnum(字段1),dbpartitionnum(字段2),dbpartitionnum(字段3),count(1)
	FROM 表名
	GROUP BY dbpartitionnum(字段1),dbpartitionnum(字段2),dbpartitionnum(字段3) 
	ORDER BY dbpartitionnum(字段1),dbpartitionnum(字段2),dbpartitionnum(字段3)
	如果数据是不均匀分布的，那么每个节点的数据量是有相差倍数的。
	(1)查看表的PARTITIONING KEY 是否合理。不合理需要重组表
	具体操作查看：DB2重组表-分布主键.sql

【3】监控语句

db2 list utilities show detail

【4】临时表按行存储

存错过程中新建的临时表时以行存储: ORGANIZE BY ROW

【5】临时表

ON COMMIT DELETE ROWS     事务完成，临时表中的数据就会消失。
ON COMMIT PRESERVE ROWS   会话结束，临时表中的数据就会消失。

【6】清理冗余空间

CALL SYSPROC.ADMIN_CMD('reorg table 表名 reclaim extents only allow no access')@

【7】导入导出数据

（1）表不存在的情况
	import from tabname.ixf of ixf create into tabname
（2）表存在的情况
	import from tabname.ixf of ixf insert into tabname

--使用load方式导入
	import from 'd:\wx_customer.del' of del insert into WX_CUSTOMER
--导入中文乱码问题  1208是utf-8, 1386是gbk
	load client from 'd:\O_SALEDATA_prov.del' of del modified by codepage=1386 insert into table
	load from /home/zoomlgd/customer.del of del modified by codepage=1386 insert into table 
--导入csv文件是，从第二行开始导入，表头忽略
	load client from 'd:\wc_point_no_use.csv' of del restartcount 1 insert into table

--导出到ixf格式：
	export to tabname.ixf of ixf messages msg.out select * from tabname;
--导出到excel：
	export to d:\CC_PROV_CITY.xls of del modified by nochardel coldel0x09 select * from CC_PROV_CITY@

【8】根据表名找存储过程

SELECT * FROM syscat.PROCEDURES WHERE procschema = 'schema名称' AND text LIKE '%表名%'@

【9】查看表状态

检查表状态：
	select tabname,colcount,status from syscat.tables where tabschema = 'NBADV' AND status = 'C'@
查看哪些表load挂起:
	select * from SYSIBMADM.ADMINTABINFO where tabschema like '%DB2INST1%' and LOAD_STATUS='PENDING'

【10】数据库配置信息

get db cfg for 数据库名@
db2sysc占用cpu过高时：
	db2 reorgchk update statistics on table all

【11】数据库日志问题

提示：SQL0964C The transaction log for the database is full
增大日志文件大小：
	update db cfg for 数据库名 using LOGFILSIZ 8192
或者增大主日志文件个数：
	update db cfg for 数据库名 using LOGPRIMARY 200
或者增大辅助日志文件个数：
	update db cfg for 数据库名 using LOGSECOND 20
注意：注意：logprimary+logsecond <= 256			
总事务日志容量大小=(LOGPRIMARY+LOGSECOND)*LOGFILSIZ*4k
ps: ((200 + 20) * 8192 * 4k)/1024 = 7040m

【12】数据库监控语句

切换至db2用户后，使用以下任意一条语句进行监控
(1)db2top -d 实例名
(2)nmon
(3)db2 list utilities show detail

查看某个db2进程具体情况：
db2 "get snapshot for application agentid 1610"
查看数据表空间具体情况：
db2 get snapshot for tablespaces on orderdb

【13】开启数据库监控

开启监控
su - db2inst1
db2 update monitor switches using bufferpool on lock on sort on statement on table on timestamp on uow on 
日志输出到文件
db2 get snapshot for all on dbname > snap.txt
重置监控
db2 reset monitor all 
待数据库运行一段时间后，下载sanp.txt文件。可以使用cmd的一些命令来分析该文件，找到耗时的sql语句进行优化

【14】db2导出表结构

先切换到保存数据的目录
db2look -d DB_NAME -u USER -e -o XXX.sql

【15】db2导出表数据

先切换到保存数据目录
db2move DB_NAME export -u USER -p PASSWORD

【16】由于表"xxxx"上的原因码“3”

由于load引起的暂挂锁导致xxx表不可用的。
解决办法:
[1]上数据库服务器查询表的状态，是否还在load
	db2 list utilities show detail
	db2 load query table xxxxx  
[2] 执行
	db2 load from /dev/null of del terminate into xxxxx nonrecoverable
稍等片刻即可解决
或者直接在qc里面执行：
	load client from d:\null.txt of del terminate into 表名

【17】清空表

delete from tableName@
drop table tableName
alter table TEST_CUSTOMER activate not logged initially with empty table@
TRUNCATE TABLE tab_name IMMEDIATE  --清理表数据并释放空间

【18】root capability required SQLSTATE= 08001

原因是: DB2 文件权限被更改了
[1]使用root用户，切换到/home/db2inst1/sqllib/security目录下
[2]依次执行下列语句：
    chown root db2chpw
    chown root db2ckpw
    chmod 4511 db2chpw
    chmod 4511 db2ckpw
[3] 然后再切换到db2inst1用户下重启数据库就可以了。

【19】CREATE LIKE 创建表

CREATE TABLE TEST_ZLW LIKE SESSION.rst;
INSERT INTO TEST_ZLW
SELECT * FROM SESSION.rst;

【20】TO_CHAR 格式日期用法

[1]年份： TO_CHAR('2019-12-20', 'YYYY')
[2]月份：TO_CHAR('2019-12-20', 'YYYYMM')
[2]自定义格式: TO_CHAR('2019-12-20', 'YYYYMMDD')
    TO_CHAR(CURRENT DATE, 'YYYY/MM/DD') AS, 
    TO_CHAR(CURRENT TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') AS timestamp
[3]季度：TO_CHAR('2019-06-20', 'Q')
[4]周：TO_CHAR('2019-12-20', 'IYYYIW')

日期间隔
SELECT
  timestampdiff (256, char(timestamp('2013-12-30 20:30:30') - timestamp('2001-09-26 15:24:23'))) AS "间隔年",
  timestampdiff (128, char(timestamp('2013-12-30 20:30:30') - timestamp('2001-09-26 15:24:23'))) AS "间隔季度",
  timestampdiff (64, char(timestamp('2013-12-30 20:30:30') - timestamp('2001-09-26 15:24:23'))) AS "间隔月",
  timestampdiff (32, char(timestamp('2013-12-30 20:30:30') - timestamp('2001-09-26 15:24:23'))) AS "间隔周",
  timestampdiff (16, char(timestamp('2013-12-30 20:30:30') - timestamp('2001-09-26 15:24:23'))) AS "间隔日",
  timestampdiff (8, char(timestamp('2013-12-30 20:30:30') - timestamp('2001-09-26 15:24:23'))) AS "间隔时",
  timestampdiff (4, char(timestamp('2013-12-30 20:30:30') - timestamp('2001-09-26 15:24:23'))) AS "间隔分",
  timestampdiff (2, char(timestamp('2013-12-30 20:30:30') - timestamp('2001-09-26 15:24:23'))) AS "间隔秒"
  FROM SYSIBM.SYSDUMMY1@

【21】多行合并成一行

方法一：
SELECT PERSONID,replace(replace(xml2clob(xmlagg(xmlelement(NAME a, COMMODITYID||',') ORDER BY 			  	COMMODITYID)),'<A>',''),'</A>',' ') AS COMMODITYIDS 
FROM WX_SHOPPINGCAR 
GROUP BY PERSONID@

方法二：
SELECT [分组的字段 ] , LISTAGG([需要聚合的字段名], ',')
FROM [表名] 
GROUP BY [分组的字段名] ;

【22】db2将一串字符中的数字剔除出来

REPLACE(字符串，被替换字符串，替换字符)：是整体字符串的替换。
TRANSLATE(字符串，替换字符，被替换字符串)：是字符串里面的字符逐个替换。
利用这两个来获取一串字符里面的数据(仅限汉字或英文或数字字符在一起的情况)。
VALUES REPLACE('我的电脑136584625', TRIM(TRANSLATE('我的电脑136584625', '', '1234567890')), '')@

【23】位数自动补全

自动补全：LPAD和RPAD
VALUES LPAD('AAA', 8, '0')@
VALUES RPAD('AAA', 8, '0')@

【24】字符串函数

[1]COALESCE(expr1, expr2, [expr3,,,,]) 第一个expr为null, 返回第二个，第二个expr为null，然后第三个....
[2]NULLIF(expr1, expr2)  两个expr相同返回null,不同返回第一个expr的值。常用于被除数中
[3]POSSTR和LOCATE

返回字符串中最后一个空格的位置，可以使用带有负“start”参数的LOCATE_IN_STRING函数
VALUES LOCATE_IN_STRING('00111', '1', -1)

与CASE WHEN相同作用的函数：DECODE
SELECT PERSONNAME, DECODE(SEX, 1, '男', 0, '女', '其他') FROM G_PERSONINFO@

【25】db2 创建索引

创建索引：
create index  索引名 on 表名 (字段1，字段2...)	
查看索引：
describe indexes for table tabname show detail@

【26】获取运行最长的SQL

SELECT SUBSTR(APPL_NAME, 1, 15) AS APPL_NAME,
    ELAPSED_TIME_MIN,
    APPL_STATUS,
    SUBSTR(AUTHID, 1, 10) AS AUTH_ID,
    SUBSTR(INBOUND_COMM_ADDRESS, 1, 15) AS IP_ADDRESS,
    SUBSTR(STMT_TEXT, 1, 30) AS SQL_STATEMENT
FROM SYSIBMADM.LONG_RUNNING_SQL@	

获取执行次数最多的SQL:
SELECT NUM_EXECUTIONS AS "执行次数",
   AVERAGE_EXECUTION_TIME_S AS "平均时间(秒)",
   STMT_SORTS AS "排序次数",
   SORTS_PER_EXECUTION AS  "每语句排序",
   SUBSTR(STMT_TEXT, 1, 30) AS "执行语句"
FROM SYSIBMADM.TOP_DYNAMIC_SQL
WHERE NUM_EXECUTIONS > 0 
ORDER BY 2 DESC FETCH FIRST 5 ROWS ONLY@

获取排序次数最多的SQL
SELECT STMT_SORTS, SORTS_PER_EXECUTION, SUBSTR(STMT_TEXT, 1, 80) AS STMT_TEXT
FROM SYSIBMADM.TOP_DYNAMIC_SQL
ORDER BY STMT_SORTS DESC 
FETCH FIRST 5 ROWS ONLY@

【27】group的多种用法

[1]GROUP BY CUBE(A,B) = ALL + GROUP BY A + GROUP BY B + GROUP BY A,B
	==> GROUP BY CUBE 的结果是2的N次幂个组合    
[2]GROUP BY ROLLUP(A,B) = ALL + GROUP BY A + GROUP BY A,B
	==> GROUP BY ROLLUP 的结果是N+1个组合
[3]ROUP BY GROUPING SETS(A,B,()) = GROUP BY A + GROUP BY B + ALL
	==> GROUP BY GROUPING SETS 的结果是 N个组合

【28】重组表及统计表

[1]查询某个表是否需要重组：
	 db2 "reorgchk on table 表名"
[2]重组命令：
CALL SYSPROC.ADMIN_CMD('reorg table schema.tablename reclaim extents only allow no access');

[3]统计表
runstats on table [模式名].[表名] with distribution and detailed indexes all

【29】降水位

##降水位只能用实例用户
db2 "connect to orderdb ";
db2 "ALTER TABLESPACE userspace1 REDUCE MAX";

【30】在线更换表所在的空间

CALL SYSPROC.ADMIN_MOVE_TABLE( 'DB2INST1', 'API_JHNEWS_20200714', 'SYSTOOLSPACE', 'SYSTOOLSPACE' , 'SYSTOOLSPACE', '', '', '', '', '', 'MOVE') @

【31】查看数据库版本

db2licm -l

【32】查看表空间使用情况

SELECT tbsp_id, substr(tbsp_name,1,20) AS TABLESPACE_NAME, 
  SUBSTR(tbsp_content_type,1,10) AS	TABLESPACE_TYPE,
  SUM(tbsp_total_size_kb)/1024 AS TOTAL_MB,
  SUM(tbsp_used_size_kb)/1024 AS USED_MB,
  SUM(tbsp_free_size_kb)/1024 AS FREE_MB,
  tbsp_page_size AS PAGE_SIZE
FROM SYSIBMADM.TBSP_UTILIZATION 
GROUP BY tbsp_name,tbsp_content_type,tbsp_page_size,tbsp_id
ORDER BY tbsp_id

SELECT * FROM SYSIBMADM.TBSP_UTILIZATION WHERE TBSP_NAME='USERSPACECOL' ORDER BY DBPARTITIONNUM@

-- 查看表空间信息
db2 get snapshot for tablespaces on orderdb


未能在在表空间xxxx中分配新页面解决办法：数据重组，降水位

【33】查看数据库表死锁

db2 get snapshot for locks on databasename

【34】新建SCHEMA

CREATE SCHEMA "TEMP"@

【35】新建WRAPPER

CREATE WRAPPER "DRDA"
  LIBRARY 'libdb2drda.so' 
  OPTIONS (DB2_FENCED  'N')@

【36】新建SERVER

CREATE SERVER "DCDB" 
  TYPE DB2/LINUX 
  VERSION '9.7' 
  WRAPPER "DRDA"  
  AUTHORIZATION "xxx" 
  PASSWORD "xxx"
  OPTIONS(
    DB2_CONCAT_NULL_NULL  'Y',
    DB2_VARCHAR_BLANKPADDED_COMPARISON  'Y',
    DBNAME  'DCDB',
    NO_EMPTY_STRING  'N'
  )@

【37】新建MAPPING

CREATE USER MAPPING FOR DB2INST1 
  SERVER "DCDB"
  OPTIONS(
  	REMOTE_AUTHID  'nbadv',
  	REMOTE_PASSWORD  '***'
  )@

【38】数据库编目

原库（被编目的库）：10.114.20.67:60000/sdsale
目标库（要在哪个库执行编目）：10.114.20.71:60000/sale66ts

1.登陆原库 10.114.20.71 AIX
2.切换成root账户
	su - root
3.切换成实例用户
	su - db2inst1
3、设置数据库属性
	db2 update dbm cfg using federated yes
4、查看实例下的数据库，数据库编目、节点编目
	db2 list database directory ##实例下数据库
	db2 list db directory ##数据库编目
	db2 list node directory ##节点编目
	
5、创建node  db2 catalog tcpip node [节点名,随便命名，有长度限制] remote [原库ip] server [原库端口] remote_instance [原表名]
	db2 catalog tcpip node sale66db remote 10.114.20.66 server 60000 remote_instance sdsale
##DB20000I  The CATALOG TCPIP NODE command completed successfully.
##DB21056W  Directory changes may not be effective until the directory cache is 
##refreshed.
6、创建数据库 别名 db2 catalog db [原表名] as [别名,有长度限制] at node [节点名]
	db2 catalog db sdsale as sale66ts at node sale66db
##DB20000I  The CATALOG DATABASE command completed successfully.
##DB21056W  Directory changes may not be effective until the directory cache is 
##refreshed.
7、登陆数据库，使用原库账号密码 db2 connect to [数据库名] user [账号] using [密码]
$ db2 connect to sale66ts user db2inst1 using db2inst1

   Database Connection Information

 Database server        = DB2/AIX64 9.5.8
 SQL authorization ID   = NBADV
 Local database alias   = SALETEST
 
--删除结点编目
db2 uncatalog node 节点名
--删除数据库编目
db2 uncatalog db 数据库名
--刷新高速缓存
db2 terminate

--在qc里面远程nickname其他库
[1]创建 SERVER
[2]创建 MAPPING