数据存储层级关系:数据库-->表空间-->容器-->表。
表空间分类:
目录表空间
特性:每个数据库只有一个目录表空间,它是在发出 CREATE DATABASE 命令时创建的.目录表空间被 DB2 命名为 SYSCATSPACE,
用途:它保存了系统目录表;
常规表空间
特性:创建数据库时指定该表空间的缺省名为 USERSPACE1。长表空间是可选的,缺省情况下一个都不创建,
用途:保存表数据和索引、还可以保存LOB之类的长数据;
系统临时表空间
特性:随数据库创建的系统临时表空间的缺省名为 TEMPSPACE1,
用途:用于存储 SQL 操作(比如排序、重组表、创建索引和连接表)期间所需的内部临时数据;
以上是由系统管理的空间(SMS),必须有一个
以下是由数据库管理的空间(DMS),可选
长表空间
用途:用于存储长型或 LOB 表列,存储结构化类型的列或索引数据
用户临时表空间
用途:存储已声明的全局临时表
表空间的几个重要概念:
一、合适的数据页大小pagesize
创建表空间时需要考虑页大小,可以使用4k,8k,16k,32k,64k。我们在选择数据页大小时需要综合考虑空间需求和业务类型(性能需求)来确定。
页面大小选择不当会造成空间浪费,最好选择大型表空间,大型表空间的一个数据页可以存放更多的容量和行数。
二、extendsize。扩张数据块大小。
extendsize大小指定在跳到下一个容器之前可以写入一个容器中的PAGESIZE页面的数量。存储数据时数据库管理器反复均衡所有容器。该参数只有在表空间中有多个容器时才起作用,选择合理的extendsize对表空间的性能影响很大,在建表空间的时候定义设定,之后不能修改。所以我们创建表空间时必须选择合理的extendsize。
三、DB2表空间的prefetchsize的大小设置和extendsize的设置大小有关,所以首先我们应该合理设置extendsize。
创建表空间:
最有效的表空间设置属性:PAGESIZE(表空间大小)、EXTENTSIZE(将数据写入到下一个容器之前写入到当前容器中的数据的页数) 和 PREFETCHSIZE(预取)
CREATE TABLESPACE 语句的示例
下列语句将创建一个常规表空间。所讨论的所有设置都是为了进行说明。
CREATE TABLESPACE USERSPACE3
PAGESIZE 8K
MANAGED BY SYSTEM
USING ('d:\\usp3_cont1', 'e:\\usp3_cont2', 'f:\\usp3_cont3')
EXTENTSIZE 64
PREFETCHSIZE 32
BUFFERPOOL BP3 --指定缓冲池
OVERHEAD 24.1
TRANSFERRATE 0.9
查看表空间
list tablespaces
查看表空间详情
list tablespaces show detail
1、查出需要增加的表空间名
db2 connect to dbname;
SELECT substr(varchar(t.SNAPSHOT_TIMESTAMP),1, 13) AS time,
t.TABLESPACE_ID,
substr(t.TABLESPACE_NAME,1, 30) AS TABLESPACE_NAME,
t.TOTAL_PAGES*t.PAGE_SIZE/1024/1024 AS TOTAL_MB_SIZE,
t.FREE_PAGES*t.PAGE_SIZE/1024/1024 AS FREE_MB_SIZE,
decimal (t.USED_PAGES,31, 3)/t.TOTAL_PAGES* 100 AS USAGE_PERCENT
FROM table(snapshot_tbs_cfg('dbname',-2)) AS t
WHERE t.TABLESPACE_TYPE= 0
ORDER BY t.TABLESPACE_ID
2、查出表空间的容器名
select* from SYSIBMADM.SNAPCONTAINER a where a.TBSP_ID=5-----假设TBSP_ID为5
3、增加表空间
ALTER TABLESPACE USERSPACE4 EXTEND ( FILE 'D:\testdb\dataspace\userspace4_01' 500); //假设为userspace4改变表空间文件大小
ALTER TABLESPACE USERSPACE4 ADD ( FILE 'D:\testdb\dataspace\USERSPACE4_02' 500); //增加表空间文件
4、数据库表空间使用率(小于70%)
db2 list tablespaces show detail
5、缓冲池的使用情况(命中率超过90%)
select bp_name,int((1-(decimal(pool_data_p_reads+pool_index_p_reads)/nullif((pool_data_l_reads+pool_index_l_reads),0)))*100) as BP_hit_ratio
from table (snapshot_bp('dbname',-1)) as snapshot_bp
6、排序堆溢出率(小于3%)
select db_name,sort_overflows from table (snapshot_database('dbname',-1)) as snapshot_database
7、锁定升级次数(几乎总是0)
select db_name, lock_escals from table (snapshot_database('dbname',-1)) as snapshot_database
8、是否存在需要重组的表或索引
SELECT 'LOAD QUERY TABLE '||TABNAME||';' FROM SYSCAT.TABLES WHERE TABSCHEMA='ADMINISTRATOR';
LOAD QUERY TABLE emp
表状态:
REORG 暂挂
REORG TABLE emp
db2 reorgchk update statistics on table all
db2 terminate
9、如果存在性能问题则检查一下是否存在死锁
select db_name,deadlocks from table (snapshot_database('dbname',-1)) as snapshot_database
select
int(total_log_used/1024/1024) as "log used (meg)",
int(total_log_available/1024/1024) as "log space free (meg)",
int((float(total_log_used))/
float(total_log_used+total_log_available)*100 )
as "pct used",
int(tot_log_used_top/1024/1024) as "max log used (meg)",
int(sec_log_used_top/1024/1024) as "max sec. used (meg)",
int(sec_logs_allocated) as "secondaries"
from sysibmadm.snapdb
DB2表空间监控:db2pd -d sample -tablespace