DB2数据库整理

DB2基础操作创建db2数据库数据库启动关闭数据库连接 数据库配置日志模式 数据库使用一些基本方法基本操作增删改查查询优化-执行计划查询优化-查询建议 存储过程函数 进阶使用1.一条sql实现上下级数据递归查询。(super_pd_id为pd_id上级数据)2.寄存器使用3.merge into 使用4.db2top 优化以及问题查询 数据库基础知识db2隔离级别 更新日志： 2019-5-23 15:12:04 整理部分操作 未完待续 2019-7-1 16:06:07 完善基础操作 增删改 merge into使用等 2019-7-27 20:07:02 db2top 优化以及问题查询 以及执行计划等信息

DB2基础操作

创建db2数据库

//新建数据库

db2 create database <dbname> AUTOMATIC STORAGE YES ON '/dbdata' using codeset utf-8 TERRITORY cn PAGESIZE 8192 RESTRICTIVE

//链接数据库

db2 connect to <dbname>

//创建缓存池

db2 create bufferpool BP_DATA_32K size 209715 pagesize 32k

//创建表空间

db2 "CREATE LARGE TABLESPACE TBS_DATA_32K PAGESIZE 32K MANAGED BY AUTOMATIC STORAGE AUTORESIZE YES EXTENTSIZE 32 PREFETCHSIZE 64 BUFFERPOOL BP_DATA_32K" file system caching

//创建schema

db2 create schema <schemaname>

//设置执行默认schema

db2 set current schema=<schemaname>

//授权 授予dbadm权限

db2 "grant dbadm on database to user \<username>"

//设置归档日志

db2 "update db cfg for \<dbname> using NEWLOGPATH \<path>" db2 "update db cfg for \<dbname> using LOGARCHMETH1 DISK:/\<path>" db2 "update db cfg for \<dbname> using TRACKMOD YES"

//释放链接

db2 terminate

数据库启动关闭

#数据库关闭，需等待 db2stop #启动服务 db2start

数据库连接

#连接数据库 db2 connect to <databasename> user <username> using <password> #可以在当前session操作中设置默认schema db2 set currentschema='schemaname' #释放所有连接，由于异步进行，可能也需要等待 db2 force applications all

数据库配置

日志模式

（归档与循环） https://blog.csdn.net/xcl168/article/details/14135883

数据库使用

一些基本方法

1.获取数据行号。

SELECT ROW_NUMBER() OVER() AS rowID FROM <tablename>

2.字符串拼接

select 'R'||<columnname> from <tablename>

3.去除空格

select trim(<columnname>) from <tablename>

4.获取字段长度

select length(<columnname>) from <tablename>

5.定位字符位置

select locate('test',<columnname>） from <tablename>

6.截取字符串

select substr(<columnname>,<begindex>,<strnum>） from <tablename> //从左截取 select left(<columnname>,<strnum>） from <tablename> //从右截取 select right(<columnname>,<strnum>） from <tablename>

7.时间相关 http://www.cnblogs.com/wanghonghu/archive/2012/05/25/2518604.html

8.查询获取前面几条数据

select top ? select * from <tablename> fetch frist 1 rows only

9.替换字符串

select replace(<columnname>,<beforestr>,<afterstr>) from <tablename>

10.case when

select case when <columnname> is null then '' else <columnname> end from <tablename>

11.转义 https://www.cnblogs.com/OliverQin/p/5872876.html

基本操作

增

1.新增、导入表

create table <tablename>( field1 varchar(100), field2 char(2), field3 timestamp ) COMPRESS YES ADAPTIVE IN "TBS_DATA_32K" INDEX IN "TBS_INDEX_16K" ORGANIZE BY ROW; PS1: 可选用压缩对表进行空间换取 [了解压缩](https://www.ibm.com/developerworks/cn/data/library/techarticle/dm-1205db210compression/)PS2: 设置按列组织表 [了解按列组织]（https://www.ibm.com/developerworks/cn/data/library/techarticle/dm-1304whatsnewdb2105/） #create table by ddl db2 -tvf ./ddl/tablename.sql -td@ ps:新增函数等 均可以通过导入实现

2.新增注释

COMMENT ON COLUMN "DB"."TABLENAME"."FIELD" IS "字段"

3.新增主键

ALTER TABLE "DB"."TABLENAME" ADD CONSTRAINT "PK_TABLENAME" PRIMARY KEY("FIELD")

4.新增索引

CREATE INDEX "DB"."IDX_TABLENAME_01" ON "DB"."TABLENAME"("FIELD" ASC) COMPRESS YES INCLUDE NULL KEYS ALLOW REVERSE SCANS; # 加入索引后 最好执行一下runstats 来使索引生效 RUNSTATS ON TABLE TABLENAME WITH DISTRIBUTION AND DETAILED INDEXES ALL PS: 了解参数 [了解更多](https://www.cnblogs.com/liufei1983/p/9671716.html)

5.新增约束

ALTER TABLE "DB"."TABLENAME" ADD CONSTRAINT "U_TABLENAME" UNIQUE(FIELD1,FIELD2) PS: 建立约束时注意加入名称，否则可能建立到系统空间，导致的后果删除时报找不到该约束。

6.新增字段

ALTER TABLE TABLENAME add column UPDATED_DATE_TIME TIMESTAMP NOT NULL; PS：时间戳字段在更新数据时自动更新 ALTER TABLE TABLENAME add column UPDATED_DATE_TIME TIMESTAMP GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP NOT NULL；

7.新增字段默认值

ALTER TABLE TABLENAME ALTER COLUMN COLNAME SET DEFAULT ''

8.导入数据

#load data load from ./data/tablename.dat of del modified by dumpfile=$DUMPPATH/${dt}/tablename.rej coldel0x03 codepage=1386 keeblanks nochardel usedefaults replace into tableschema.tablname NONRECOVERABLE data buffer 10000 #import data import from ./data/tablename.dat of del modified by colde10x03 codepage='1386' commitcount 10000 replace into tablename

9.导入存储过程

db2 -td@ -vf ./procedures_data.sp

10.创建游标

declare cur_name cursor for select col1,col2 from table1 where table1.id=1

11.创建视图

create view v_tablename(col1,col2) as select col1,col2 from tablename ps:视图为原表加条件结果集映射，只能用来查询

删

1.删除数据库

drop database databasename

2.删除表

drop table tablename

3.删除字段

alter table tablename drop column colname PS请注意。删除字段会导致表不可用，需要reorg表。 reorg table tablename

4.删除约束

alert table <tablename> drop constraint <constraintName>

5.删除方法

drop specific function tableschema.functionname

6.删除存储过程

drop procedure tableschema.functionname

7.删除、清空表数据

#普通删除数据 delete from tablename where id=1 #快速清空表 alter table tablename not lgged initially with empty table #还可以为表导入个空文件做replace load from /dev/null of del replace into tablename

8.删除索引

drop index <indexname>

改

1.更改字段类型

#更改字段类型 alter table tablename alter column colname set data type varchar(1000)

2.更新数据

update tablename set colname='111',colname1='222' where id=1

查

1.基本查询

select * from tablename where id=1

2.分组查询

# 普通分组 select col1,count(*) from tablename group by col1 # 分组过滤 select col1,col2 from tablename group by col1,col2 having col1='01'

3.with集合查询 可用作递归

#该用法其实可以将特别复杂sql进行降维处理。使其查询逻辑更为简单。 #其中test可作为子查询，并将单个子查询简单化处理。 with test(col1,col2) as( select co1,co2 from tablename1 )，test2(c1,c2)as( select co1,co2 from tablename2 ) select col1,col2 from test,test2 where test2.c1=test.col1 [了解更多](https://blog.csdn.net/kouge94/article/details/50977801) #递归使用详见进阶使用中 一条sql实现上下级数据递归查询

4.values 创建虚拟表

# 详见进阶使用 2寄存器

查询优化-执行计划

#创建执行计划需要的表 dbvisual工具查看执行计划需要这些表 windows:C:\Program Files (x86)\IBM\SQLLIB\MISC linux:/opt/ibm/db2/v10.5/misc #找到EXPAIN.DDL 并执行 db2 connect to sample db2 -tvf EXPLAIN.DDL #或使用 db2 connect to sample db2 "CALL SYSPROC.SYSINSTALLOBJECTS('EXPLAIN','C',NULL,'DB2INST1')" #可以直接在db2环境中执行sql进行分析 sql语句用双引号括起来。 #分析程序包 db2expln -d 数据库名 -i -g -c 模式名-p程序包 -s 0 -t db2expln -d 数据库名 -i -g -c 模式名-p程序包 -s 0 -o文件名 #分析sql 语句 db2expln -d 数据库名 -i -g -q sql语句 -t db2expln -d 数据库名 -i -g -q sql语句 -o 文件名

查询优化-查询建议

#查询建议需要建立执行计划的表 #建议sql会加入索引，删除无用索引，以及提示 可以查询性能提升多少。 db2advis -d <databasename> -i 1.sql -t 5 -n <schemaname>

存储过程

函数

进阶使用

1.一条sql实现上下级数据递归查询。(super_pd_id为pd_id上级数据)

with tmpselect(pd_id) as( select pd_id from \<tablename> union all select pdid from \<tablename> table1, tmpselect table2 where table2.pd_id=table1.super_pd_id ) select pd_id from tmpselect

2.寄存器使用

SYSIBM.SYSDUMMY1、SYSIBM.DUAL 可以做虚拟数据拼接 如拼接表格数据最后一条合计记录。 select \<columnname1>, \<columnname2> from \<tablename> union all select '合计'，‘0000’ from SYSIBM.SYSDUMMY1/SYSIBM.DUAL #也可直接使用values VALUES 一条sql统计各个表数据量大小 values('tablename1',select count(1) from \<tablename1> with ur),('tablename2',select count(1) from \<tablename2> with ur)

3.merge into 使用

#使用merge into可快速进行数据整合 简单实例如下： merge into globaltable as t using(select col1,col2 from table1) as s on col1 when matched and s.col3=2 then delete when matched and (s.col3=1 and s.col4=2) then update set t.col1=s.col1,t.col2=s.col2 when not matched and s.col3=4 then insert values(s.col1，s.col2) #ps:可以在存储过程中动态生成语句进行执行

4.db2top 优化以及问题查询

#db2top 提供了很多功能，可以对当前执行的sql进行查询。 #需要使用实例用户进行执行 db2top -d <databasename> -v <schemaname> #部分操作说明， 功能快捷键功能说明DateBased展示对整个db2数据库性能的监控单元TableSpacet展示每个表空间的详细情况DynamicSqld展示提供缓存的sql语句的详细信息，对sql执行执行计划 输入大写L 输入sql的hashcode，可以查询完整sqlSessionI展示每个应用程序的会话的详细信息Bufferpoolb展示每个缓存池的信息LockU展示各个表加锁情况TableT展示各个表的信息，展示读取信息BottlenecksB展示瓶颈信息，哪个应用严重限制了系统性能Agenta如果界面中展示agentId，可以按a进行查询，会显示出是什么sql

数据库基础知识

db2隔离级别

https://www.cnblogs.com/live-and-learn077/p/5462548.html