DB2之上机操作和问题解决

DB2学习笔记DB2上机操作命令1实例相关：查询实例：db2ilist查看当前实例：db2 get instance创建实例：db2icrt instance_name(windows)db2icrt -u fenced_user_IDinstance_name（linux）使用实例：set db2instance=实例名称删除实例：db2idrop -finstance_name迁移实例：db2imigr instance_name更新实例：db2iupdt instance_name连接实例：db2 attach to instance_name断开实例：db2 detach启动实例：db2start关闭实例：db2stop [force]2数据库相关：查询数据库：db2 list database directory [on /home/db2inst1]db2 list active databases创建数据库：db2 create database yzht using codeset ISO8859-1 territory cn pagesize 4096 db2 create database itp using codeset GBK territory cn pagesize 8192db2CREATE DATABASE DB2_GCB ON G: ALIAS DB2_GCB USING CODESET GBK TERRITORY CN COLLATE USING SYSTEM DFT_EXTENT_SZ 32db2 CREATE DATABASE DB2TEST1 USING CODESET GBK TERRITORY CN COLLATE USING SYSTEM PAGESIZE 4096删除数据库：db2 drop database db_name连接数据库：db2connect to db_name user user_name using password;连接复位：db2 connect reset查看连接：db2 list application for db db_name[show detail]关闭连接：db2 force applications alldb2force application(id1,id2)读数据库管理程序配置：db2 get dbm cfg写数据库管理程序配置：db2 update dbm cfg using 参数名参数值读数据库的配置：db2 get db cfg for db_name写数据库的配置：db2 update db cfg for db_name using 参数名参数值获取数据库的权限：db2GET AUTHORIZATIONS停止启动数据库：db2activate/deactivate db db_name3缓冲池相关：查询缓冲区：db2 SELECT BPNAME FROM SYSCAT.BUFFERPOOLSdb2select BPNAME , NPAGES from sysibm.sysbufferpools 创建缓冲区：db2 create bufferpool bp_name [immediate] size 5000[automatic] pagesize 8k/16k/32kPS: DEFERRED不会立刻激活缓冲池；immediate立即激活32k为缓冲池页大小，这边创建了32*100=3.2m的缓冲池空间修改缓冲池：db2 alter bufferpool bp_name SIZE bp_size删除缓冲池：db2 drop bufferpool bp_name4表空间相关：查询表空间：db2 list tablespaces [show detail]db2pd -tablespaces –db db_namedb2listtablespace containers for tbs_id show detail创建表空间：db2 CREATE REGULAR TABLESPACE tp_name PAGESIZE 4K MANAGED BY AUTOMATIC STORAGE BUFFERPOOL bp_namedb2CREATE [SYSTEM/USER] [TEMPORARY]TABLESPACE tp_name IN DATABASE PARTITION GROUP IBMDEFAULTGROUP PAGESIZE 32K MANAGED BY SYSTEM USING ('/home/exoa2/exoacontainer32k') EXTENTSIZE 32 PREFETCHSIZE 16 BUFFERPOOL bp_name OVERHEAD 24.10 TRANSFERRATE 0.90DROPPED TABLE RECOVERY OFF;GRANT USE OF TABLESPACE tp_nameTO PUBLIC;PS：create tablespace datatblsp pagesize 32Kmanaged by databaseusing (device '/dev/rdata0' 30720M ,device '/dev/rdata1' 30720M ,)extentsize 8 prefetchsize 48bufferpool databp;device：指的是设备路径。

File: 指的是文件路径。

用文件存储DB2可以做到预读取。

修改表空间：db2ALTER TABLESPACE tp_name AUTORESIZE YESdb2 alter tablespace tp_name prefetchsize 16db2 ALTER TABLESPACE tp_name BUFFERPOOL bp_name删除表空间：db2 drop TABLESPACE tp_name查询表空间容器：db2 LIST TABLESPACE CONTAINERS FOR <n>db2 list tablespace containers for tbs_id show detail授权表空间：GRANT USE OF TABLESPACE tp_name TO PUBLIC;将两个新设备容器添加到UNIX上的一个表空间中：ALTER TABLESPACE RESOURCEADD (DEVICE '/dev/rhd9' 10000, DEVICE '/dev/rhd10' 10000)把UNIX上的表空间中的两个设备容器改变大小（由1000页改为2000页）ALTER TABLESPACE HISTORYRESIZE (DEVICE '/dev/rhd7' 2000, DEVICE '/dev/rhd8' 2000)在UNIX表空间中，扩充两个设备容器（原本各含1000页）ALTER TABLESPACE HISTORYEXTEND (DEVICE '/dev/rhd11' 1000, DEVICE '/dev/rhd12' 1000)5模式相关：查询模式：db2 select * from syscat.schemata创建模式：db2 create schema s_name authorization schema_owner修改模式：SET SCHEMA = <schema-name>删除模式：db2 drop schema s_name restrict6表相关：查询表：db2 list tablesdb2 list table for system / for schema scods查询表结构：db2describe table t_name创建表：CREATE TABLE TEST (column 1 definition, column 2 definition, ...) IN <tablespace name> INDEX IN <index space name> create table zjt_views as (select * from views) definition only;PS:-- 创建数据表,并添加注释,插入数据.CREATE TABLE tbr_catalog (id bigintnot null generated by default as identity,type smallint not null,name varchar(255),parentid bigint,cataloglevel bigint,description varchar(255),PRIMARY KEY (id));comment on table tbr_catalog is 'Birt报表目录表';comment on column tbr_catalog.ID is '标识';comment on column tbr_catalog.type is '目录类型';comment on column tbr_ is '目录名称';comment on column tbr_catalog.parentid is '目录父标识';comment on column tbr_catalog.cataloglevel is '目录层次';comment on column tbr_catalog.description is '目录描述';-- 给数据表插入数据insert into tbr_catalog(id, type, name, parentid, cataloglevel, description) values (1, 0, '系统报表', 0, 0, '');insert into tbr_catalog(id, type, name, parentid, cataloglevel, description) values (2, 1, '用户报表', 0, 0, '');-- 创建外键alter table tbr_storageadd constraint fk_tbr_storageforeign key (catalogid)references tbr_catalog(id);-- 更改表,添加列alter table aaa add sex varchar(1);-- 更改表,删除列alter table aaa drop column sex;-- 去掉参数前后的空格rtrim(dm_hy.mc);修改表：alter table t_name alter column c_name set data type varchar(200) alter table t_name add column c_name varchar(200)PS: 只能添加列，不能删除列ALTER TABLE t2 ADD c3 INT CHECK (c3 > 1) DEFAULT 10 ADD c4 DECIMAL(5,2) NOT NULL ADD c5 TIMESTAMP DEFAULT CURRENT TIMESTAMP ADD COLUMN c6 CHAR(20) DEFAULT 'xyz' ADD c7 INT REFERENCES t3;删除表：db2drop table t_name锁表：db2lock table test in exclusive modedb2lock table test in share mode释放不活动的表：reorg table ***7索引相关：查询索引：db2 select * from syscat.indexesdb2 describe indexes for table t_name创建索引：CREATE <UNIQUE> INDEX <index name> ON <table name>(column 1 <ASC | DESC> ,column 2 <ASC | DESC> ...)P S:CREATE <UNIQUE> INDEX ON EMPLOYEE (EMPNO) INCLUDE (LASTNAME,FIRSTNAME)对于索引中包含的列而言，索引必须是 UNIQUE 的。