db2 创建实例，创建数据库，创建表

创建实例

1、首先创建和实例名一样的用户及该用户所属的组
(注意实例名不能超过8个字符)
groupadd db2icomm
groupadd db2fcomm
useradd -g db2icomm -m -d /home/db2icomm db2icomm
useradd -g db2fcomm -m -d /home/db2fcomm db2fcomm
passwd db2icomm 
密码跟帐号一样
passwd db2fcomm 
密码跟帐号一样


2、
/opt/ibm/db2/V9.7/instance/db2icrt -a server -u db2fcomm db2icomm 


可以通过db2_ps或ps -ef|grep -i INSTANCE查看DB2进程的状态
3、
当配置DB2的运行环境：成功创建实例后，检查实例目录下的.profile是否调用db2profile
.profile中是否有：
# The following three lines have been added by IBM DB2 instance utilities.
if [ -f /home/db2icomm/sqllib/db2profile ]; then
    . /home/db2icomm/sqllib/db2profile
fi


4、
db2icrt 创建的实例设置为手动启动
允许实例在每次系统重新启动后自动启动 
db2icomm@emal:~> db2iauto -on db2icomm
阻止实例在每次系统重新启动后自动启动
db2iauto -off db2icomm


5、
启动实例
db2icomm@emal:~> db2start
09/10/2014 20:14:44     0   0   SQL1063N  DB2START processing was successful.
SQL1063N  DB2START processing was successful.


停止实例
db2stop


6、
查看当前在哪个实例下
db2icomm@emal:~/Desktop> echo $DB2INSTANCE
db2icomm
或者
db2icomm@emal:~/Desktop> db2 get instance
 The current database manager instance is:  db2icomm


7、
为了对实例变量提供tcp/ip协议的支持，输入如下命令设置DB2COMM变量
db2set DB2COMM=TCPIP -i db2icomm


db2 get dbm cfg | grep SVCENAME 检查配置 SVCENAME 的值，如果 SVCENAME为空值，则需要用下面的步骤设定该值，如果是一个端口号 ( 端口号应小于 65536)，则不用读取/etc/services文件中的端口定义，如果该值是一个字符串（如：db2c_db2inst1），则在实例启动时会自动读取/etc/services 中的该字符串对应的端口号来监听。
# db2  update database manager configuration using svcename db2c_db2inst1（或者为端口号如：50001） --我们也可以通过这种方式修改DB2监听的端口号.
 db2  update database manager configuration using svcename 50002






DBI1001I  Usage:


 db2icrt [-h|-?]
         [-d]
         [-a AuthType]
         [-p PortName]
         [-s InstType]
         -u FencedID InstName


Explanation: 


An invalid argument was entered for the db2icrt command. Valid arguments
for this command are: 


-h|-?    display the usage information.


-d       turn debug mode on.


-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 (ese,wse,
         standalone, or client). 


         ese      used to create an instance for a DB2 database server
                  with local and remote clients with DPF support. This
                  type is the default instance type for DB2 Enterprise
                  Server Edition.


         wse      used to create an instance for a DB2 database server
                  with local and remote clients. This type is the
                  default instance type for DB2 Workgroup Edition, DB2
                  Express or Express-C Edition, and DB2 Connect
                  Enterprise Edition.


         standalone
                  used to create an instance for a DB2 database server
                  with local clients. This type is the default instance
                  type for DB2 Personal Edition.


         client   used to create an instance for a IBM Data Server
                  Client. This type is the default instance type for IBM
                  Data Server Client products and DB2 Connect Personal
                  Edition.


          


         DB2 products support their default instance types and the
         instance types lower than their default ones. For instance, DB2
         Enterprise Edition supports the instance types of 'ese', 'wse',
         'standalone' and '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 IBM Data Server Client is installed.


InstName is the name of the instance.


User response: 


Confirm that user IDs and group names used to create the DB2 instance
are valid. For information about naming rules, see the topic called
"User, user ID and group naming rules" in the DB2 Information Center.


Refer to the DB2 Information Center for a detailed description of the
command. Correct the syntax and reissue the command.

========================================================================================================

创建数据库

创建数据库


数据库是从 DB2 命令界面创建的。在启动命令界面之前，必须符合以下条件：


DB2 必须是活动的。
对于 AIX、Linux 和 Solaris, DB2 实例拥有者标识（例如 db2icomm）必须是 db2iadm 组的一部分。db2iadm 组是在 DB2 通用数据库安装过程中创建的。
在 DB2 命令界面，输入以下命令来创建数据库：


db2 create database db_name alias alias_name using codeset UTF-8 territory US


db_name
指定给数据库的名称。
alias_name
数据库别名。如没有提供别名，则将使用指定的数据库名称。
注:
别名不是必需要的；然而，如果使用了别名，则它必须与数据库名称相同。


从 DB2 命令行处理器输入下列命令来创建 DB2 数据库：
db2icomm@emal:~> db2 create database db_shop using codeset UTF-8 territory CN
DB20000I  The CREATE DATABASE command completed successfully.




从 DB2 命令行处理器输入下列命令来创建其它数据库结构
db2 connect to db_shop user db2icomm using db2icomm
db2 create bufferpool <BufferPoolName> size 250 pagesize 32K
db2 create bufferpool bp_8k size 250 pagesize 8K
db2 connect reset
db2 terminate
db2 force application all
db2 terminate
db2stop
db2start


从 DB2 命令行处理器输入下列命令，创建其它数据库结构：
db2 connect to <DataBaseName> user <DB2UserID> using <DB2Password>
db2 connect to db_shop user db2icomm using db2icomm
db2 create regular tablespace uddits pagesize 32K managed by system using ('<TableSpaceName>') extentsize 64 prefetchsize 32 bufferpool <BufferPoolName>
db2 "CREATE REGULAR TABLESPACE TS_SMS_8K  PAGESIZE 8 K MANAGED BY SYSTEM USING ('TS_SMS_8K')  BUFFERPOOL BP_8K"
db2 create system temporary tablespace <TempTableSpacename> pagesize 32 K managed by system using ('<TempTableSpacename>') extentsize 32 overhead 14.06 prefetchsize 32 transferrate 0.33 bufferpool <BufferPoolName>
db2 "create system temporary tablespace TTS_SMS_8K pagesize 8 K managed by system using  ('TTS_SMS_8K')   bufferpool BP_8K"


db2 "CREATE REGULAR TABLESPACE TS_DMS_8K  PAGESIZE 8 K MANAGED BY DATABASE USING (FILE 'TS_DMS_8K' 200000)  BUFFERPOOL BP_8K"
db2 "create regular tablespace IDX_DMS_8K pagesize 8 K managed by DATABASE using  (FILE 'IDX_DMS_8K' 10000)   bufferpool BP_8K"


如果要删表空间
db2 drop tablespace TS_DMS_8K,IDX_DMS_8K


db2 "DISCONNECT db_shop"
db2 "TERMINATE"


db2 connection service port 
 /etc/services

创建表

db2 "create table tb_superType(id int not null generated always as identity(start with 1,increment by 1),TypeName varchar(50),primary key(id)) in TS_DMS_8K index in IDX_DMS_8K"


db2 "create table tb_subType(id int not null generated always as identity(start with 1,increment by 1),superType int,TypeName varchar(50),primary key(id))  in TS_DMS_8K index in IDX_DMS_8K"


db2 "create table tb_rebate(grade varchar(20) not null,Amount decimal(10,2),rebate float,primary key(grade)) in TS_DMS_8K index in IDX_DMS_8K"


db2 "create table tb_order_detail(id bigint not null generated always as identity(start with 1,increment by 1),orderID bigint,goodsID bigint,price decimal(10,2),number int,primary key(id)) in TS_DMS_8K index in IDX_DMS_8K"


db2 "create table tb_order(OrderID bigint not null generated always as identity(start with 1,increment by 1),bnumber smallint,username varchar(15),Truename varchar(15),address varchar(100),postcode varchar(10),tel varchar(10),pay varchar(20),carry varchar(20),rebate float,OrderDate date,enforce int,bz varchar(200),primary key(OrderID)) in TS_DMS_8K index in IDX_DMS_8K"


db2 "create table tb_member(id int not null generated always as identity(start with 1,increment by 1),userName varchar(20),trueName varchar(20),passWord varchar(20),city varchar(20),address varchar(100),postcode varchar(6),cardNO varchar(24),cardType varchar(20),grade int,Amount decimal(10,2),tel varchar(20),email varchar(100),freeze int,primary key(id))in TS_DMS_8K index in IDX_DMS_8K"


db2 "create table tb_manager(id int not null generated always as identity(start with 1,increment by 1),manager varchar(30),PWD varchar(30),primary key(id)) in TS_DMS_8K index in IDX_DMS_8K"


db2 "create table tb_goods(id bigint not null generated always as identity(start with 1,increment by 1),typeID int,goodsName varchar(200),introduce CLOB(2097152),price decimal(10,2),nowPrice decimal(10,2),picture varchar(100),INTime timestamp,newGoods int,sale int,hit int,primary key(id)) in TS_DMS_8K index in IDX_DMS_8K"


db2 "create table tb_BBS(id int not null generated always as identity(start with 1,increment by 1),title varchar(100),content varchar(4000),TNTime timestamp ,primary key(id)) in TS_DMS_8K index in IDX_DMS_8K"