DB2创建表、操作表等常用命令
转载：http://hi.baidu.com/ufobject/item/7fd03aeebf7be1266dabb881一、创建库表
1、创建库
1).创建数据语句
CREATE DATABASE MEDIA ON /db2data USING CODESET UTF-8 TERRITORY CN ;
2).创建BUFFERPOOL
CONNECT TO MEDIA user db2admin using password;
CREATE BUFFERPOOL "BP_MEDIA_32K"SIZE 100000 PAGESIZE 32768 NOT EXTENDED STORAGE;
COMMIT WORK;
CONNECT RESET;
TERMINATE;
;
3).创建表空间
connect to media user db2admin using password;
CREATE REGULAR TABLESPACE "TB_MEDIA" IN DATABASE PARTITION GROUP IBMDEFAULTGROUP PAGESIZE 32768 MANAGED BY DATABASE USING (FILE '/db2data/TB_MEDIA_C1'320000) EXTENTSIZE 32 PREFETCHSIZE 32 BUFFERPOOL BP_MEDIA_32K OVERHEAD 12.500000 TRANSFERRATE 0.130000 FILE SYSTEM CACHING DROPPED TABLE RECOVERY OFF;
CREATE LARGE TABLESPACE "TB_MEDIA_LOB" IN DATABASE PARTITION GROUP IBMDEFAULTGROUP PAGESIZE 32768 MANAGED BY DATABASE USING (FILE '/db2data/TB_MEDIA_LOB_C1'640000) EXTENTSIZE 32 PREFETCHSIZE 32 BUFFERPOOL BP_MEDIA_32K OVERHEAD 12.500000 TRANSFERRATE 0.130000 FILE SYSTEM CACHING DROPPED TABLE RECOVERY ON;
CREATE REGULAR TABLESPACE "TB_MEDIA_IDX" IN DATABASE PARTITION GROUP IBMDEFAULTGROUP PAGESIZE 4096 MANAGED BY DATABASE USING (FILE '/db2data/TB_MEDIA_IDX_C1'1280000) EXTENTSIZE 32 PREFETCHSIZE 32 BUFFERPOOL IBMDEFAULTBP OVERHEAD 12.500000 TRANSFERRATE 0.130000 FILE SYSTEM CACHING DROPPED TABLE RECOVERY OFF;
CREATE SYSTEM TEMPORARY TABLESPACE "TB_MEDIA_TMP" IN DATABASE PARTITION GROUP IBMTEMPGROUP PAGESIZE 32768 MANAGED BY SYSTEM USING ('/db2data/TB_MEDIA_TMP_C1' ) EXTENTSIZE 16 PREFETCHSIZE 16 BUFFERPOOL "BP_MEDIA_32K" OVERHEAD 12.5 TRANSFERRATE 0.13; DROP TABLESPACE USERSPACE1;
-- Mimic tablespace
-- ALTER TABLESPACE SYSCATSPACE PREFETCHSIZE AUTOMATIC OVERHEAD 7.500000 FILE SYSTEM CACHING TRANSFERRATE 0.060000;
-- ALTER TABLESPACE TEMPSPACE1PREFETCHSIZE AUTOMATIC OVERHEAD 7.500000 FILE SYSTEM CACHING TRANSFERRATE 0.060000;
COMMIT WORK;
CONNECT RESET;
TERMINATE;
;
二、备份数据库
1）、按表备份与恢复
备份：
a、导出创建表语句
db2look -d media -e -c -o media.ddl -i db2admin -w password
b、导出数据
db2 connect to media user db2admin using password db2 "EXPORT TO B_NEWS.IXF OF IXF MESSAGES B_NEWS.LOG SELECT * FROM DB2ADMIN.B_NEWS" 也可以，
上面内容复制到export.sh中，执行以下命令
./export.sh
恢复：
a、导入表结构
db2 -tvf media.ddl
b、导入数据
db2 connect to MEDIA user db2admin using password
db2 IMPORT FROM B_NEWS.IXF OF IXF MESSAGES B_NEWS.MSG INSERT INTO DB2ADMIN.B_NEWS
也可以，
上面内容复制到import.sh中，执行以下命令
./import.sh
2）、整库备份与恢复
备份：
a、首先要断开所有链接。命令如下：
su - db2inst
db2
force application all
db2stop force
db2start
b、备份数据库
db2 backup db media
恢复：
db2 restore db media
三、操作表
1）、创建表
create table B_NEWS ( ID BIGINT not null, DOC_LIB VARCHAR(200), DOC_NO VARCHAR(50), DOC_VERSION INT, DOC_TYPE SMALLINT, STATUS SMALLINT, REMARK VARCHAR(200), constraint PK_B_NEWS_DROP primary key (ID)
) IN "TB_MEDIA" INDEX IN "TB_MEDIA_IDX" LONG IN "TB_MEDIA_LOB" ;
2)、表中增加、删除一个字段
ALTER TABLE B_NEWS ADD COLUMN ORDER_NO INTEGER;
ALTER TABLE SYS_USER_AUTHGROUP DROP COLUMN STATUS;
3)、表中某个字段修长数据类型
ALTER TABLE B_NEWS ALTER DOC_ITEM_SOURCES SET DATA TYPE varchar(200);
4)、压缩表命令
su - db2inst
db2
connect to media user db2admin using password
reorg table B_NEWS;
5)、创建序列
CREATE SEQUENCE "SEQ_NEWS" AS INTEGERMINVALUE 1 MAXVALUE 999999999START WITH 1 INCREMENT BY 1CACHE 20 NO CYCLE NO ORDER;
ALTER SEQUENCE "SEQ_NEWS" RESTART WITH 1920;
6)、注册db2安装许可证
添加license：
db2licm -a xxx.lic
四、其它
1).查询schema的表
db2 connect to media
db2 list tables for schema db2admin
2).dbadm 权限
Running db2look as the DB2 instance user fails with SQL0031C File ".../db2lkfun.bnd" could not be opened
db2 connect to sampledb2 grant dbadm on database to user tst1
db2 grant dbadm on database to group grp1
3).增加表空间大小
DB2数据库使用时，如果表空间满了，该如何扩容呢？下文将教给您DB2数据库表空间扩容的方法，供您参考，希望对您有所帮助。
1）直接添加一个容器的例子：
db2 " ALTER TABLESPACE PAYROLL ADD (DEVICE '/dev/rhdisk9' 10000) "
加容器之后DB2会有一个自动balance的过程,可能会持续几个小时!!! 一定要注意该选项,修改前确认该选项是否能满足业务需求!
2）改变现有容器的大小(该方法不会触发balance,但如果表空间建立在裸设备上,则要扩冲裸设备空间):
db2 " ALTER TABLESPACE TS1 RESIZE (FILE '/conts/cont0' 2000, DEVICE '/dev/rcont1' 2000, FILE 'cont2' 2000) "
注意这种方式就是将原有的相应容器都改成大小是2000页
db2 "ALTER TABLESPACE TS1 RESIZE (ALL 2000)"
这种方式就是把表空间中所有的容器大小都改成2000页
db2 " ALTER TABLESPACE TS1 EXTEND (FILE '/conts/cont0' 1000, DEVICE '/dev/rcont1' 1000, FILE 'cont2' 1000) "
这种方式就是将相应的容器都扩大1000页，也就是增加1000页。
db2 " ALTER TABLESPACE DATA_TS EXTEND (ALL 1000)"
这种方式就是将所有的容器都增加1000页。
4).增加表空间大小
页:
[1]