db2icrt [-a AuthType]
[-p PortName]
[-s InstType]
[-w WordWidth]
-u FencedID InstName
-a AuthType is the authentication type (SERVER, CLIENT, or SERVER_ENCRYPT) for the instance.
-p PortName is the port name or port number to be used by this instance.
-s InstType is the type of instance to be created (wse, ese, or client).
-u FencedID is the name of the user under which fenced UDFs and fenced stored procedures will be run. This flag is not required if only a DB2 client is installed.
-w WordWidth is the width, in bits, of the instance to be created (31, 32 or 64). You must have the requisite version of DB2 installed (31-bit, 32-bit or 64-bit) for this to work. The default width value is the lowest bit width supported by the current version of DB2, the platform, and the instance type.
示例:
# cd /usr/opt/db2_08_01/instance
# ./db2icrt -w 64 -u db2inst2 db2inst2
其他:
Db2start:启动实例
Db2stop:停止实例。
Db2icrt:创建实例。
Db2ilist:列出系统上可用的所有实例。
Db2 get instance:确定适用于当前对话的实例。
Set db2instance=<new_instance_name>:设置当前实例。
Db2iupdt InstName:更新实例配置。
DB2DIR/instance/db2iupdt -w 64 db2inst1
Db2idrop <instance_name>:删除实例。
Db2iauto –on <instance_name>:允许一个实例在每次系统重新启动后自动启动。
Db2iauto –off <instance_name>:阻止一个实例在每次系统重新启动后自动启动。
Db2set:设置环境变量。
2. 修改新加的INSTANCE的属性
设置DB2的CODEPAGE为1386:
$ db2set DB2CODEPAGE=1386
DB2COUNTRY=86
DB2COMM=tcpip
DB2CODEPAGE=1386
DB2AUTOSTART=YES
3. 启停数据库INSTANCE
Db2start:启动实例
Db2stop:停止实例。
4. 数据库的建立、修改、删除
数据库的建立:
示例1:CREATE DATABASE newdb USING CODESET GBK TERRITORY CN
示例2:
CREATE DATABASE coredb ON '/home/db2fex/db2ifex/db' ALIAS coredb USING CODESET GBK TERRITORY CN COLLATE USING SYSTEM CATALOG TABLESPACE MANAGED BY DATABASE USING ( DEVICE '/dev/rLVDBWHBPRD03' 258048 ) USER TABLESPACE MANAGED BY DATABASE USING ( DEVICE '/dev/rLVDBWHBPRD02' 7680000 ) TEMPORARY TABLESPACE MANAGED BY DATABASE USING ( DEVICE '/dev/rLVDBWHBPRD04' 258048 ) ;
数据库的修改:
db2 get db cfg for <db_name>
update db cfg for coredb using newlogpath '/home/db2fex/db2ifex/log';
update db cfg for coredb using mirrorlogpath '/home/db2fex/db2ifex/sys';
update db cfg for coredb using LOGARCHMETH1 "disk:/home/db2fexarclog";
数据库的删除
Drop database <db_name>
5. 使用CATALOG配置数据库的连接
catalog tcpip node n140 remote 182.248.24.140 server 60000;
catalog database coredb at node n140;
6. DB2 LIST的使用
查看数据库的所有连接:
Db2 list applications [show detail]
7. DB2 FORCE的使用
切断所有数据库应用连接:
Db2 force applications all
8. CREATE TABLESPACE命令的使用
建立一个名为NEWTABLESPACE的表空间:
CREATE REGULAR TABLESPACE NEWTABLESPACE MANAGED BY DATABASE USING (FILE 'inv_ts.dat' 1024M ) EXTENTSIZE 16
创建临时表空间:
CREATE USER TEMPORARY TABLESPACE STMASPACE PAGESIZE 32 K MANAGED BY DATABASE USING (FILE 'D:/DB2_TAB/STMASPACE.F1' 10000) EXTENTSIZE 256
创建表空间:STMA
CREATE REGULAR TABLESPACE STMA PAGESIZE 8 K MANAGED BY SYSTEM USING ('D:/DB2Container/Stma' ) EXTENTSIZE 8 OVERHEAD 10.5 PREFETCHSIZE 8 TRANSFERRATE 0.14 BUFFERPOOL STMABMP DROPPED TABLE RECOVERY OFF
创建系统表空间:
CREATE REGULAR TABLESPACE SYSCATSPACE
IN IBMCATGROUP
PAGESIZE 4096
MANAGED BY SYSTEM
USING( 'D: /DB2/NODE0000/SQL00001/SQLT0000.0' )
EXTENTSIZE 32
PREFETCHSIZE 16
BUFFERPOOL IBMDEFAULTBP
OVERHEAD 24.10
TRANSFERRATE 0.90
DROPPED TABLE RECOVERY OFF
;
创建用户表空间:
CREATE REGULAR TABLESPACE USERSPACE
1
I
N IBMDEFAULTGROUP
PAGESIZE 4096
MANAGED BY SYSTEM
USING( ' D: /DB2/NODE0000/SQL00001/SQLT0002.0' )
EXTENTSIZE 32
PREFETCHSIZE 16
BUFFERPOOL IBMDEFAULTBP
OVERHEAD 24.10
TRANSFERRATE 0.90
DROPPED TABLE RECOVERY ON
;
GRANT USE OF TABLESPACE USERSPACE1 TO PUBLIC
;
创建临时表空间:
CREATE TEMPORARY TABLESPACE TEMPSPACE
1
I
N IBMTEMPGROUP
PAGESIZE 4096
MANAGED BY SYSTEM
USING(‘D: /DB2/NODE0000/SQL00001/SQLT0001.0' )
EXTENTSIZE 32
PREFETCHSIZE 16
BUFFERPOOL IBMDEFAULTBP
OVERHEAD 24.10
TRANSFERRATE 0.90
;
CREATE REGULAR TABLESPACE "mytesttbs"
IN IBMDEFAULTGROUP
PAGESIZE 4096
MANAGED BY DATABASE
USING( FILE 'D:/DB2/mytesttbs' 10240,
FILE 'D:/db2/mytesttbs2' 5120 )
EXTENTSIZE 32
PREFETCHSIZE 32
BUFFERPOOL IBMDEFAULTBP
OVERHEAD 25.00
TRANSFERRATE 1.00
DROPPED TABLE RECOVERY ON
;
GRANT USE OF TABLESPACE "mytesttbs" TO USER LYLF615 WITH GRANT OPTION
;
ALTER TABLESPACE "mytesttbs" EXTEND
( FILE 'D:/DB2/mytesttbs' 5120 ) ON NODE (0)
;
修改表空间大小:
Alter tablespace syscatspace resize (device ‘/dev/rLVDBWHBPRD 03’ 258048)
9. 数据库表的建立、修改、删除
CREATE TABLE LYLF615."ot_test"
( "id" INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY ( START WITH 1, INCREMENT BY 1, NO CYCLE, CACHE 20 ),
"name" VARCHAR(10)
PRIMARY KEY ("id")
)
IN "mytesttbs"
INDEX IN "mytesttbs"
;
ALTER TABLE LYLF615."ot_test" DATA CAPTURE NONE;
ALTER TABLE LYLF615."ot_test" NOT VOLATILE;
ALTER TABLE LYLF615."ot_test" APPEND OFF;
ALTER TABLE LYLF615."ot_test" LOCKSIZE ROW;
ALTER TABLE LYLF615."ot_test" ADD PRIMARY KEY ("id");
ALTER TABLE LYLF615."ot_test" ADD "org_id" INTEGER;
10. 数据库表字段的定义
CREATE TABLE ot_test_org
(
org_id INTEGER not null,
org_name VARCHAR(10),
PRIMARY KEY (org_id)
)
IN "mytesttbs"
;
11. 索引、主外键的建立、修改、删除
ALTER TABLE LYLF615."ot_test" ADD CONSTRAINT FK_ORG_ID
FOREIGN KEY ("org_id")
REFERENCES LYLF615.OT_TEST_ORG (ORG_ID)
ON DELETE NO ACTION
ON UPDATE NO ACTION
;
ALTER TABLE LYLF615."ot_test" DROP CONSTRAINT FK_ORG_ID;
ALTER TABLE LYLF615."ot_test" ADD CONSTRAINT FK_ORG_ID
FOREIGN KEY ("org_id")
REFERENCES LYLF615.OT_TEST_ORG (ORG_ID)
ON DELETE NO ACTION
ON UPDATE NO ACTION
;
CREATE <UNIQUE> INDEX <index name> ON <table name>
(
column 1 <ASC | DESC> ,
column 2 <ASC | DESC> ...
)
UNIQUE 属性告诉 DB2,索引必须强制所有插入值的唯一性。
如果结果集是以升序和降序两种方式排序的,ALLOW REVERSE SCANS 会告诉 DB2 在索引中包括附加的指针,以允许在记录中有效地进行正向和反向链接。
DB2 能够向正在创建的索引中添加另外的列。CREATE INDEX 命令允许用户指定那些不属于实际索引但因为性能原因而保存在索引记录中的列。
CREATE UNIQUE INDEX ON EMPLOYEE (EMPNO) INCLUDE (LASTNAME,FIRSTNAME)
对于索引中包含的列而言,索引必须是 UNIQUE 的。当创建索引时,另外的列被添加到索引值中。索引不使用这些值进行排序或确定唯一性,但可以在满足 SQL 查询时使用它们。例如,下列 SELECT 语句将不需要读取实际数据行:
SELECT LASTNAME, FIRSTNAME FROM EMPLOYEE WHERE EMPNO < '000300'
要在表上创建群集索引,将 CLUSTER 关键字附加到 CREATE INDEX 命令的末尾,例如:
CREATE INDEX DEPTS-IX ON EMPLOYEE(WORKDEPT) CLUSTER
使用下列通用规则来决定为表定义的索引的经典数量。索引数量取决于数据库的主要用途:
对于在线事务处理(OLTP)环境,创建一到两个索引。
对于混合查询和 OLTP 环境,创建两到五个索引。
对于只读查询环境,创建五个以上索引。
create unique index newindx on newtable(no) include (value);
describe indexes for table newtable show detail;
drop index newindx
12. 视图的建立、修改、删除
CREATE VIEW LYLF615."ot_test_view" AS SELECT * FROM OT_TEST_ORG WHERE org_id=1
CREATE VIEW LYLF615."test_view1" AS
SELECT id,cname,a.org_id,b.org_name
FROM ot_test a, ot_test_org b
WHERE a.org_id=b.org_id
视图唯一能修改的是引用类型列,改变列的范围。其他定义好了都不能修改。当视图基于的基表drop后,视图变为无效。
13. 表关联(关联、外连接)
Union
――交集运算:INTERSECT、INTERSECT ALL
SELECT * FROM AA_WEEK X WHERE X.CODE IN (1,2,3) -- 集合A
INTERSECT -- 集合B
SELECT * FROM AA_WEEK X WHERE X.CODE IN (2,3,4);
---------------------------------
2 'Tuesday' 'Tues'
3 'Wednesday' 'Wed'
说明:INTERSECT和INTERSECT ALL是等效的。
――差集运算:EXCEPT、EXCEPT ALL
SELECT * FROM AA_WEEK X WHERE X.CODE IN (1,2,3) -- 集合A
EXCEPT -- 集合B
SELECT * FROM AA_WEEK w WHERE w.CODE IN (2,3,4);
说明:EXCEPT和EXCEPT ALL是等效的。
---------------------------------
1 'Monday' 'Mon'
――合集运算:UNION、UNION ALL
n 求合集:A+B(不消除重复行)
SELECT * FROM AA_WEEK X WHERE X.CODE IN (1,2,3) -- 集合A
UNION ALL -- 集合B
SELECT * FROM AA_WEEK X WHERE X.CODE IN (2,3,4);
n 求合集:A+B(消除重复行)
SELECT * FROM AA_WEEK X WHERE X.CODE IN (1,2,3) -- 集合A
UNION -- 集合B
SELECT * FROM AA_WEEK X WHERE X.CODE IN (2,3,4);
---------------------------------
Join
n 内连接:inner join在两表共有的行才会输出,内连接没有左右之分。
select * from t1 inner join t2 on t1.c3=t2.c1
n 左外连接:left outer join左连接保留前面表的所有记录,后表中没有的补null。
select * from t1 left outer join t2 on t1.c3=t2.c1
n 右外连接:right outer join右连接保留后表的所有记录,前表中没有的补null。在DB2的内部机制中,会把右外连接重写成左外连接.故我们在写sql语句时尽量使用左外连接。
select * from t1 right outer join t2 on t1.c3=t2.c1
n 全外连接:full outer join全外连接会输出两表的所有的数据,包括内连接和左外连接和右外连接的行。
select * from t1 full outer join t2 on t1.c3=t2.c1
n 总结:内连接,全有才有;左外连接,左有就有;右外连接,右有就有;全外连接,全都有。
14. 检查LOCKTIMEOUT 的值
The default value for LOCKTIMEOUT is -1, which means that there will be no lock timeouts - a situation that can be catastrophic for OLTP applications. Nevertheless, I all too frequently find many DB2 users with LOCKTIMEOUT = -1. Set LOCKTIMEOUT to a very short value, such as 10 or 15 seconds. Waiting on locks for extended periods of time can have an avalanche effect on locks.
First, check the value of LOCKTIMEOUT with this command:
db2 "get db cfg for DBNAME" |
and look for the line containing this text:
Lock timeout (sec) (LOCKTIMEOUT) = -1 |
If the value is -1, consider changing it to 15 seconds by using the following command (be sure to consult with the application developers or your vendor first to make sure the application is prepared to handle lock timeouts):
db2 "update db cfg for DBNAME using LOCKTIMEOUT 15" |
You should also monitor the number of lock waits, lock wait time, and amount of lock list memory in use. Issue the command:
db2 "get snapshot for database on DBNAME" |
Look for the following lines:
Locks held currently= 0 Lock waits= 0 Time database waited on locks (ms)= 0 Lock list memory in use (Bytes)= 576 Deadlocks detected= 0 Lock escalations= 0 Exclusive lock escalations= 0 Agents currently waiting on locks= 0 Lock Timeouts= 0 |
If the Lock list memory in use (Bytes) exceeds 50 percent of the defined LOCKLIST size, then increase the number of 4K pages in the LOCKLIST database configuration.
15. 计算本开发环境中BUFFER的命中率
缓冲池是内存中的一块存储区域,用于临时读入和更改数据库页(包含表行或索引项)。缓冲池的用途是为了提高数据库系统的性能。从内存访问数据要比从磁盘访问数据快得多。因此,数据库管理器需要从磁盘读取或写入磁盘的次数越少,性能就越好。对一个或多个缓冲池进行配置之所以是调优的最重要方面,是因为连接至数据库的应用程序的大多数数据(不包括大对象和长字段数据)操作都在缓冲池中进行。 缺省情况下,应用程序使用缓冲池 IBMDEFAULTBP,它是在创建数据库时创建的。当 SYSCAT.BUFFERPOOLS 目录表中该缓冲池的 NPAGES 值为 -1 时,DB2 数据库配置参数 BUFFPAGE 控制着缓冲池的大小。否则会忽略 BUFFPAGE 参数,并且用 NPAGES 参数所指定的页数创建缓冲池。 建议对于仅使用一个缓冲池的应用程序,将 NPAGES 更改成 -1,这样 BUFFPAGE 就可以控制该缓冲池的大小。这使得更新和报告缓冲池大小以及其它 DB2 数据库配置参数变得更加方便。
n 查看bufferpools:
$ db2 get db cfg for coredb |grep BUFF
Buffer pool size (pages) (BUFFPAGE) = 1000
n 计算命中率:
db2 "update monitor switches using lock ON sort ON bufferpool ON uow ON table ON statement ON"
db2 "get snapshot for all bufferpools"
在数据库快照或缓冲池快照的快照输出中,查找下列"logical reads"和"physical reads",这样就可以计算出缓冲池命中率,它可以帮助调优缓冲池:
缓冲池命中率表明数据库管理器不需要从磁盘装入页(即该页已经在缓冲池中)就能处理页请求的时间百分比。缓冲池的命中率越高,使用磁盘 I/O 的频率就越低。按如下计算缓冲池命中率:
(1 - ((buffer pool data physical reads + buffer pool index physical reads) /
(buffer pool data logical reads + pool index logical reads))
) * 100%
这个计算考虑了缓冲池高速缓存的所有页(索引和数据)。理想情况下,该比率应当超过 95%,并尽可能接近 100%。
$ db2 "get snapshot for all bufferpools"|grep logical
Buffer pool data logical reads = 200
Buffer pool temporary data logical reads = 0
Buffer pool index logical reads = 0
Buffer pool temporary index logical reads = 0
$ db2 "get snapshot for all bufferpools"|grep physical
Buffer pool data physical reads = 0
Buffer pool temporary data physical reads = 0
Buffer pool index physical reads = 0
Buffer pool temporary index physical reads = 0
这个计算考虑了缓冲池高速缓存的所有页(索引和数据)。理想情况下,该比率应当超过 95%,并尽可能接近 100%。要提高缓冲池命中率,请尝试下面这些方法: 增加缓冲池大小。
考虑分配多个缓冲池,如果可能的话,为每个经常被访问的大表所属的表空间分配一个缓冲池,为一组小表分配一个缓冲池,然后尝试一下使用不同大小的缓冲池以查看哪种组合会提供最佳性能。
如果已分配的内存不能帮助提高性能,那么请避免给缓冲池分配过多的内存。应当根据取自测试环境的快照信息来决定缓冲池的大小。
太小的缓冲池会产生过多的、不必要的物理 I/O。太大的缓冲池使系统处在操作系统页面调度的风险中并消耗不必要的 CPU 周期来管理过度分配的内存。正好合适的缓冲池大小就在"太小"和"太大"之间的某个平衡点上。适当的大小存在于回报将要开始减少的点上。
n 修改bufferpools:
方法一:
Select npages from syscat.bufferpools
Alter bufferpool ibmdefaultbp immediate size 20000
方法二:
Create bufferpool hisdbbp1 immediate size 2500 pagesize 32k
Alter tablespace syscatspace bufferpool hisdbbp1
16. 存储过程的建立及维护
项目组暂时没有使用DB2存储过程
17. Group BY、HAVING命令的使用
GROUP BY是SELECT语句的从句,用来指定查询分组条件,主要用来对查询的结果进行分组,相同组合的分组条件在结果集中只显示一行记录。使用GROUP BY从句时候,通过添加聚合函数(主要有COUNT()、SUM、MAX()、MIN()等)可以使数据聚合。GROUP BY插叙列中使用聚合函数是针对每个分组的。
GROUP BY用来指定分组条件,是一个数学集合的概念,比如针对一列进行分组,则组合分组条件的集合数为1。如果有两个分组条件,则组合分组条件的集合数为2。因此带有GROUP BY的查询一般成为分组查询,也叫组合查询。组合记录的多少取决于组合集合(不包含重复元素)中元素的个数。例如,组合条件为一列,则查询结果集记录条数应该等于这个列所有字段所组成(数学意义上的)集合的元素个数(NULL字段也算一个)。如果有两个组合列,则记录数等于实际中存在的两个列所组合的数目。
GROUP BY组合列必须出现查询的SELECT关键字后面,相同组合条件的情况下仅仅保留一个。因此,通过SELECT...GROUP BY查询出的各个列都应该是数目相同,要达到相同的目的,有两种途径:一种是将要查询的字段放到组合条件中,一种是在非组合条件的字段上使用聚合函数,当然也可以在组合列上聚合函数。处子之外,别无它法!如果查询的各个列结果数目不相等,则结果集会出现“不能对齐”的错误。 因此,将非组合条件的列在不使用聚合函数条件下放到要查询的列中,这种做法是完全错误的。
GROUP BY在做组合查询的时候,会对NULL的分组单独形成一行,进行统计。参看上面的SQL。
GROUP BY对组合条件列来说,本身就会自动分组(剔除重复的列),因此在组合条件的列上应用DISTINCT关键字是多于的。但是用在非组合条件(都有聚合函数)的列上使用DISTINCT却不是多余的。GROUP BY不但可以对列组合,还可以对列的表达式进行组合。
可以在SELECT ... GROUP BY 分组后筛选数据。筛选的关键字是HAVING。HAVING的作用和WHERE类似。都是用来过滤查询的中间记录。但是,HAVING从句指定的每个列规范必须出现在一个聚合函数内,或者出现在GROUP BY从句命名的列中。与WHERE不同的是:WHERE是在分组前(查询后)筛选数据;HAVING是在分组后筛选数据。
SELECT SUBSTR(A.HYLB_DM,1,2),COUNT(*),SUM(A.ZCZB) FROM DJ_ZT A
GROUP BY SUBSTR(A.HYLB_DM,1,2)
HAVING MAX(YEAR(A.CJRQ))<>2007;
考核要点 | 类型(基础/中级/高级) | 试题(案例) | 答案(运行环境AIX) |
新增一个数据库实例 | 中级 | 用newinst用户建立一个名为newinst的数据库实例(FencedID简单取newinst) | db2icrt –u newinst newinst |
修改新建数据库实例的属性 | 中级 | 设置newinst实例的CODEPAGE属性为1386 | db2set DB2CODEPAGE=1386;db2 terminate; |
数据库的建立、修改、删除 | 中级 | 用实例用户在名为NEWINST的实例下建立一个NEWDB的数据库,字符集为GBK,之后再删除该数据库 | su - newinst;db2 create db newdb using codeset GBK territory CN;db2 drop db newdb; |
CREATE TABLESPACE命令的使用 | 中级 | 建立一个名为NEWTBS,页面大小为4K的,SMS的表空间(建在/home/newinst/tbs/newtbs目录下) | create regular tablespace newtbs pagesize 4096 managed by system using('/home/newinst/tbs/newtbs') |
数据库表的建立、修改、删除 | 基础 | 在表空间NEWTBS上建立一个名为NEWTABLE(no,value)的表,no为主键,no为int型,value为varchar型 | create table newtable(no int not null,value varchar(5), primary key(no) ) in newtbs |
视图的建立、表关联 | 基础 | 在NEWTABLE(no,value)和NEWTABLE2(no,value)上建立视图,该视图用于显示NEWTABLE及NEWTABLE2表中no字段相等的记录 | create view newview as ( select a.no,a.value from newtable a,newtable2 b where a.no=b.no ) |
检查LOCKTIMEOUT的值 | 中级 | 查看newdb数据库的LOCKTIMEOUT参数值 | db2 connect to newdb;db2 get db cfg|grep LOCKTIMEOUT; |
修改LOCKTIMEOUT 的值 | 中级 | 修改LOCKTIMEOUT 的值为15秒 | db2 update db cfg for newdb using LOCKTIMEOUT 15 |
查看数据库的BUFFERPOOLS | 中级 | 查看数据库newdb有哪些BUFFERPOOLS | db2 connect to newdb;db2 "select * from syscat.bufferpools" |
数据记录的增、删、改、查等 | 基础 | 对表NEWTABLE、NEWTABLE2举例进行SELECT命令的使用,INSERT命令的使用,DROP命令的使用,UPDATE命令的使用,JOIN命令的使用,HAVING命令的使用,GROUP BY令的使用 | 略 |
索引的建立、查看、删除 | 基础 | 在表NEWTABLE的no列上建立唯一索引,并将value列做为该唯一索引的include列;建立后查看该索引,之后删除该索引 | create unique index newindx on newtable(no) include (value);describe indexes for table newtable show detail;drop index newindx |
启停数据库实例 | 中级 | 用newinst用户启动名为NEWINST的数据库实例,强行停止名为NEWINST的数据库实例 | su - newinst; db2start;db2stop force; |
查看数据库配置文件 | 中级 | 查看newdb数据库的数据库配置文件 | db2 connect to newdb;db2 get db cfg |
使用CATALOG命令配置数据库的连接 | 中级 | 在另一台DB2 CLIENT端通过tcpip的方式连接182.247.70.88机器上NEWINST实例下的NEWDB数据库。NEWINST实例的SVCENAME参数为50000 | db2 catalog tcpip node N88 remote 182.247.70.88 server 50000;db2 catalog db newdb as newdb at node N88; |
DB2 LIST的使用 | 中级 | 查看数据库的所有连接 | db2 list applications; |
DB2 FORCE的使用 | 中级 | 切断所有数据库应用连接 | db2 force applications all; db2 terminate; |
DB2快照 | 中级 | 打开快照监视器的bufferpool及lock开关,并拍newdb的database快照。 | db2 update monitor switches using bufferpool on lock on;db2 get snapshot for database on newdb; |
计算本开发环境中BUFFERPOOL的命中率 | 高级 | 假设数据库快照数据如下:Buffer pool data logical reads = 48 | (1-(16+47)/(48+79))*100%=50.39% |
对本系统数据库表的熟悉 | 基础 | 从本系统的基本客户信息表中按照客户号找到客户名 | 略 |