DB2基本操作

wskickahn

　　--重启数据库 FORCE APPLICATION ALL DB2STOP DB2START
　　--创建数据库 CREATE DATABASE mysdedb USING CODESET UTF-8 TERRITORY US COLLATE USING SYSTEM USER TABLESPACE MANAGED BY DATABASE USING (FILE 'd:\DB2\data\mysdedb\sdetbsp' 51200)
　　CONNECT TO mysdedb
　　--创建缓冲池(使用32k的pagesize)
　　create bufferpool sdepool size 12800 pagesize 32K create bufferpool sdepool1 size 12800 pagesize 32K
　　--创建表空间并使用32k的pagesize和自定义的缓冲池
　　CREATE REGULAR TABLESPACE regtbs PAGESIZE 32 K MANAGED BY DATABASE USING ( FILE 'C:\DB2\NODE0000\mysdedb\regtbs' 2g) bufferpool sdepool
　　CREATE REGULAR TABLESPACE idxtbs PAGESIZE 32 K MANAGED BY DATABASE USING ( FILE 'C:\DB2\NODE0000\mysdedb\idxtbs' 1g) bufferpool sdepool
　　CREATE LONG TABLESPACE lobtbs PAGESIZE 32 K MANAGED BY DATABASE USING ( FILE 'C:\DB2\NODE0000\mysdedb\lobtbs' 1g) bufferpool sdepool1
　　CREATE USER TEMPORARY TABLESPACE sdespace PAGESIZE 32 K MANAGED BY SYSTEM USING ('C:\DB2\NODE0000\mysdedb\sdespace' ) bufferpool sdepool1
　　 --授权表空间给用户 grant use of tablespace
　　--授权表空间 GRANT USE OF TABLESPACE regtbs TO PUBLIC GRANT USE OF TABLESPACE lobtbs TO PUBLIC GRANT USE OF TABLESPACE idxtbs TO PUBLIC GRANT USE OF TABLESPACE sdespace TO PUBLIC COMMENT ON TABLESPACE sdespace IS ''
　　--优化数据库配置 update db cfg for mysdedb using APPLHEAPSZ 2048 update db cfg for mysdedb using APP_CTL_HEAP_SZ 2048 update db cfg for mysdedb using LOGPRIMARY 10 update db cfg for mysdedb using LOGFILSIZ 1000
　　--重启数据库 FORCE APPLICATION ALL DB2STOP FORCE DB2START
　　--授予sde用户DBADM权限 grant DBADM on database to user sde
　　--重启数据库 FORCE APPLICATION ALL DB2STOP FORCE DB2START

　　建库
　　CREATE DATABASE TS1
AUTOMATIC STORAGE NO --是否自动存储
ON 'D:\' --路径
ALIAS test
USING CODESET GBK --设置编码
TERRITORY CN COLLATE --设置地域
USING SYSTEM PAGESIZE 4096 --设置数据页大小(4kb 8kb 16kb 32kb)
WITH 'aa' --注释
　　建表-----------------------------------------------------------------------------------
　　CREATE TABLE AA
(
ID INT  GENERATED ALWAYS AS IDENTITY(START WITH 1 INCREMENT BY 1), --标示
NAME VARCHAR(50) WITH DEFAULT 'BOB',--默认值
AGE INT NOT NULL CHECK(AGE > 0 AND AGE<200),--check约束
YEARS INT NOT NULL UNIQUE, --唯一
DAYS DECIMAL(8,1) NOT NULL,
BIO CLOB(100K) LOGGED,--计入日志
PIC BLOB(2M) NOT LOGGED COMPACT,--不计入日志
PRIMARY KEY(ID)--主键
)
IN USERSPACE1--指定存储表空间
　　CREATE TABLE AA
(
ID INT  GENERATED ALWAYS AS IDENTITY(START WITH 1 INCREMENT BY 1),
NAME VARCHAR(50) WITH DEFAULT 'BOB',
AGE INT NOT NULL CHECK(AGE > 0 AND AGE<200),
YEARS INT NOT NULL UNIQUE,
DAYS DECIMAL(8,1) NOT NULL,
BIO CLOB(100K) LOGGED,
PIC BLOB(2M) NOT LOGGED COMPACT,
PRIMARY KEY(ID)
)
IN USERSPACE1;
　　
常规写法：
主键
alter table AA
add primary key(id);
删除主键
ALTER TABLE AA
DROP PRIMARY KEY
唯一
alter table AA
add  CONSTRAINT SS UNIQUE(DAYS);
　　alter table AA
DROP CONSTRAINT SS;
外键：
外键约束（foreign key constraint）有时候称作参照约束。 参照完整性（referential integrity）被定义为“数据库的所有外键值都是有效的状态”。那外键又是什么呢？ 外键（foreign key）是指表中的一列或一组列，其值必须至少匹配其父表中一行的一个主键或惟一键值。这真正意味着什么呢？它实际上并非如听起来那般复杂。简单来说，它意味着如果表（T2）中的一列（C2）存在值匹配另一个表（T1）中的一列（C1）的值， 并且 C1 是 T1 的主键，那么 C2 就是 T2 中的外键列。将包含了父键（主键或惟一键）的表称为 父表（parent table），而将包含了外键的表称为 子表（dependent table）
alter table AA
ADD  CONSTRAINT FK_ID FOREIGN KEY(ID)
references BB  on delete cascade;
　　check：
alter table AA
        add constraint name_length check (length(rtrim(name)) = 4)
　　使用 SET INTEGRITY/CONSTRAINTS 语句可以打开或者关闭表检查约束。
SET CONSTRAINTS FOR EMP OFF
ALTER TABLE EMP ADD CONSTRAINT COMP CHECK (SALARY + COMM > 15000)
SET CONSTRAINTS FOR EMP IMMEDIATE CHECKED
　　
ALTER TABLE stuMarks
CONSTRAINT FK_stuNo         
     FOREIGN KEY(stuNo) REFERENCES stuInfo(stuNo)
--添加外键约束（主表stuInfo和从表stuMarks建立关系，关联字段为stuNo）
　　alter table AA
add sid int;
向AA表中添加一列
　　二进制大对象--BLOB，
字符大对象--CLOB，
双字节字符大对象--DBCLOB。
　　
系统目录表-----------------------------------------------------------------------------------
SYSCAT.TABLES --所有表
SYSCAT.INDEXES --索引
SYSCAT.FUNCTIONS --函数
SYSCAT.PROCEDURES --存储过程
SYSCAT.BUFFERPOOLS --缓冲池
　　序列-----------------------------------------------------------------------------------
序列对象产生整个数据库的一个唯一值。跟标识列不一样的是，序列独立于数据表。
prevva提供序列的一个当前值，而nextval提供下一个值。
　　CREATE TABLE t1 (salary int)
CREATE SEQUENCE myseq
START WITH 10
INCREMENT BY 1
NO CYCLE
　　INSERT INTO t1 VALUES (nextval for myseq)
　　自定义数据类型-----------------------------------------------------------------------------------
create distinct type 自定义类型名
as 类型名
with comparisons --为自定义类型名创建一个转换函数
　　Ex：create distinct type myint as integer with comparisons
　　
建临时表-----------------------------------------------------------------------------------
declare global temporary table  表名(模式名.表名)
like 模版表 --定义表的列和模版表的列一样
on commit preserne rows  --在处理COMMIT时，临时表的行会被保留下来
not logged  --不计入日志
in 临时表空间
　　Ex:
declare global temporary table  xabc."Man"
like staff
on commit preserne rows
not logged
in TEMPSPACE1;
　　插入-----------------------------------------------------------------------------------
insert into 表名(列名) values(值);
Ex:
insert into staff
(id,name,dept,job,years,salary,comm)
values(11,'dddddd',22,'aaa',1,200,300),
(11,'dddddd',22,'aaa',1,200,300);
　　删除-----------------------------------------------------------------------------------
delete from staff where id in(1,3);
　　更新-----------------------------------------------------------------------------------
update staff set (dept,job)=(10,'aa');
　　
查询-----------------------------------------------------------------------------------
select * from staff
　　数值类型之间可以进行运算
Ex:select salary + comm as aa from staff;
as 用来指定列名
　　模糊查询：
select name from staff where name like 'a%';
　　子查询：
select * from staff where id in(select aid from AA);
　　fetch first子句用来限制结果集中的行数
Ex:select * from staff fetch first 10 rows only;
　　distinct 用来排除结果集中重复的行
Ex:select distinct name, job from staff;
　　连接查询-----------------------------------------------------------------------------------
　　集合运算符-----------------------------------------------------------------------------------
把两个或两个以上的结果集合并成一个结果集，并去除重复结果。
UNION运算符：会把两个或两个以上的结果集合并成一个结果集
　　select * from staff where id > 200
union
select * from AA where id <100
后面跟上ALL，不去除重复结果
　　排序-----------------------------------------------------------------------------------
select * from staff where salary > 100 order by salary
默认升序，降序为DESC
　　视图-----------------------------------------------------------------------------------
create view view_AA
as select s.id,a.name
from AA a,staff s
where a.id=s.id
with check option
通过 with check option可以将约束作用在视图的列上，可以拒绝不合法的更新或插入
　　
存储过程-----------------------------------------------------------------------------------
CREATE PROCEDURE 存储过程名称 [( {可选变量} )]
[可选存储过程属性] <语句>
　　属性:
language  --指定存数过程所使用的语言。LANGUAGE SQL 是其默认值
result sets N --指定存储过程将返回几个结果集
specific name --指定存储过程的唯一名称
存储过程可以被重载，也就是说许多个不同的存储过程可以使用同一个名字，但这些存储过程所包含的参数数量不同。
用 drop specific procedure name 来删除重载过的过程
　　
参数：
in 输入
out 输出
inout 输入输出
　　用call来调用过程 ?代表输出参数
Ex: call(1,?);
　　
当BEGIN 关键字后紧随ATOMIC 关键字时，其封装的复合语句就被当作一个处理单元，也就是说，复合语句中的所有程序指令和语句都必须成功运行，以保证整个复合语句的成功运行。如果其中的一个语句发生错误，那么这整个存储过程所执行的结果都将回滚。
　　
声明变量：
declare 变量名 数据类型 default 值
Ex:declare name varchar(20) default 'bob';
　　赋值：
SET name = 'ss';
上面的语句等同于下面的：
VALUES('ss') into name;
除此之外，所有的变量都可以赋NULL 值：
SET name = NULL;
选择表中的数据给变量赋值时，可能会将一个多行的集合赋予变量，如果只想取选择结果的第一行数据时，可以使用
SET name = (select sum(c1) from T1)+"";
　　
游标---------------------------------------------------------------------------------------
游标是保持SELECT 语句执行结果的集合
DECLARE <游标名称> CURSOR [WITH RETURN <返回目标>]
<SELECT 语句>;
OPEN <游标名称>;
FETCH <游标名称> INTO <变量>;
CLOSE <游标名称>;
　　CREATE PROCEDURE set()
DYNAMIC RESULT SETS 1
LANGUAGE SQL
BEGIN
DECLARE cur CURSOR WITH RETURN TO CLIENT
FOR SELECT name, dept, job
FROM staff
WHERE salary > 20000;
OPEN cur;
END
　　
Ex:
CREATE PROCEDURE MEDIAN_RESULT_SET (OUT medianSalary DOUBLE)
　　RESULT SETS 1
　　LANGUAGE SQL
　　BEGIN
　　DECLARE v_numRecords INT DEFAULT 1;
　　DECLARE v_counter INT DEFAULT 0;
　　DECLARE c1 CURSOR FOR
　　SELECT CAST(salary AS DOUBLE)
　　FROM staff
　　ORDER BY salary;
　　DECLARE c2 CURSOR WITH RETURN FOR
　　SELECT name, job, CAST(salary AS INTEGER)
　　FROM staff
　　WHERE salary > medianSalary
　　ORDER BY salary;
　　DECLARE EXIT HANDLER FOR NOT FOUND
　　SET medianSalary = 6666;
　　SET medianSalary = 0;
　　SELECT COUNT(*) INTO v_numRecords
　　FROM STAFF;
　　OPEN c1;
　　WHILE v_counter < (v_numRecords / 2 + 1)
　　DO
　　FETCH c1 INTO medianSalary;
　　SET v_counter = v_counter + 1;
　　END WHILE;
　　CLOSE c1;
　　OPEN c2;
　　END
　　
索引-----------------------------------------------------------------------------------
索引是有序键值的集合，每一个键值指向表的一行。索引的值可以唯一，它改善了数据库的性
能。在索引上可以定义如下的一些特性:
索引顺序可以递增也可以递减速
索引键可以是独值的也可以不是独值
一些列可以一起用作索引 (这被称作混合索引)
如果索引和物理数据串在聚集在一个相似的索引序列中，它们就成为簇索引。
CREATE UNIQUE INDEX artno_ix ON artists (artno)
　　删除对象-----------------------------------------------------------------------------------
drop 对象类型 对象名
　　导出DB2 DDL-----------------------------------------------------------------------------------
db2look -d <dbname> -e -o db2look.ddl
　　运行PL脚本------------------------------------------------------------------------------------
db2 -td! –vf myScript.txt
　　
备份-----------------------------------------------------------------------------------------
$ db2 backup db 数据库名
系统会自动备份生成一个时间戳的数据备份文件,
如：xxxx.0.db2inst1.NODE0000.CATN0000.20070814031212.001

恢复数据库
$ db2 restore db 数据库名 taken at 20070814031212（直接取数据备份生成的时间戳即可）
　　
CONNECT TO SAMPLE
　　CREATE TABLE AA
(
ID INT  GENERATED ALWAYS AS IDENTITY(START WITH 1 INCREMENT BY 1),
NAME VARCHAR(50) WITH DEFAULT 'BOB',
AGE INT NOT NULL CHECK(AGE > 0 AND AGE<200),
YEARS INT NOT NULL UNIQUE,
DAYS DECIMAL(8,1) NOT NULL,
BIO CLOB(100K)  LOGGED,
PIC BLOB(2M) NOT  LOGGED COMPACT,
PRIMARY KEY(ID)
)
IN USERSPACE1;
　　CREATE TABLE BB
(
ID INT  GENERATED ALWAYS AS IDENTITY(START WITH 1 INCREMENT BY 1),
NAME VARCHAR(50) WITH DEFAULT 'BOB',
AGE INT NOT NULL CHECK(AGE > 0 AND AGE<200),
PRIMARY KEY(ID)
)
IN USERSPACE1;
　　ALTER TABLE AA
DROP PRIMARY KEY;
alter table BB
add primary key(id);
　　SELECT * FROM AA;
　　alter table AA
add  UNIQUE(DAYS);
　　alter table AA
DROP CONSTRAINT SS;
　　alter table BB
        add constraint name_length check (length(rtrim(name)) = 4);
　　ALTER TABLE  BB
ADD BID INT;
　　alter table AA
ADD  CONSTRAINT FK_ID FOREIGN KEY(ID)
references BB on delete cascade;
　　alter table AA
DROP  CONSTRAINT FK_ID ;
　　
INSERT INTO AA (NAME,AGE,YEARS,DAYS) VALUES('DDD',2,11,13);
　　INSERT INTO BB  (NAME,AGE) VALUES('DDDS',2);
　　CREATE  INDEX AA ON BB (BID)
　　SELECT * FROM SYSCAT.REFERENCES
　　DROP TABLE AA;