DB2基础知识一

 

<!--[if !supportLists]-->1.        <!--[endif]-->新加一个数据库实例

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：设置环境变量。

<!--[if !supportLists]-->2.        <!--[endif]-->修改新加的INSTANCE的属性

设置DB2的CODEPAGE为1386：

$ db2set DB2CODEPAGE=1386

DB2COUNTRY=86

DB2COMM=tcpip

DB2CODEPAGE=1386

DB2AUTOSTART=YES

<!--[if !supportLists]-->3.        <!--[endif]-->启停数据库INSTANCE

Db2start：启动实例

       Db2stop：停止实例。

<!--[if !supportLists]-->4.        <!--[endif]-->数据库的建立、修改、删除

数据库的建立:

示例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>

<!--[if !supportLists]-->5.        <!--[endif]-->使用CATALOG配置数据库的连接

catalog tcpip node n140 remote 182.248.24.140 server 60000;

catalog database coredb at node n140;

<!--[if !supportLists]-->6.        <!--[endif]-->DB2 LIST的使用

查看数据库的所有连接:

Db2 list applications [show detail]

<!--[if !supportLists]-->7.        <!--[endif]-->DB2 FORCE的使用

切断所有数据库应用连接:

Db2 force applications all

<!--[if !supportLists]-->8.        <!--[endif]-->CREATE TABLESPACE命令的使用

建立一个名为NEWTABLESPACE的表空间:

CREATE REGULAR TABLESPACE NEWTABLESPACE Pe'<*B0 O    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)