DB2 v9 安装步骤

一、安装db2 v9服务器、创建实例、创建管理服务器
 
1.创建目录、用户
[root@seagull /]# mkdir /db2home
[root@seagull /]# mkdir /db2home/db2inst1
[root@seagull /]# mkdir /db2home/db2fenc1
[root@seagull /]# groupadd -g 999 db2iadm1
[root@seagull /]# groupadd -g 998 db2fadm1
[root@seagull /]# useradd -u 1004 -g db2iadm1 -d /db2home/db2inst1 db2inst1
[root@seagull /]# useradd -u 1003 -g db2fadm1 -d /db2home/db2fenc1 db2fenc1
passwd db2inst1
passwd db2fenc1
su - db2fenc1 --切换用户
su - db2fenc1 --根据提示，重新设置密码，使得密码生效
su - db2inst1 --切换用户
su - db2inst1 --根据提示，重新设置密码，使得密码生效
 
2.安装db2软件
[root@seagull seagull]# cd /tmp/seagull/db2v9/
[root@seagull db2v9]# ls
db2 db2_install db2setup installFixPack
 root@seagull db2v9]# ./db2_install
#注解：IBM工程师建议用文字界面安装，而不要用图形界面安装，即用db2_install,不要用db2setup
Default directory for installation of products - /opt/ibm/db2/V9.1
***********************************************************
Do you want to choose a different directory to install [yes/no] ?
no
#注解：使用默认目录就可以了
 pecify one or more of the following keywords,
separated by spaces, to install DB2 products.
  CLIENT
  RTCL
  ESE
 
Enter "help" to redisplay product names.
Enter "quit" to exit.
***********************************************************
ESE
#注解：选择安装ESE，即enterprise server edition
  
3.安装license
[root@seagull bin]# cd /opt/ibm/db2/V9.1/adm/
[root@seagull adm]# ./db2licm -a /tmp/seagull/db2v9/license/db2ese_c.lic
 IC1402I License added successfully.

 IC1426I This product is now licensed for use as specified in the License Agreement and License Information documents pertaining to the licensed copy of this product. USE OF THE PRODUCT CONSTITUTES ACCEPTANCE OF THE TERMS OF THE IBM LICENSE AGREEMENT AND LICENSE INFORMATION DOCUMENTS, LOCATED IN THE FOLLOWING DIRECTORY: "/opt/ibm/db2/V9.1/license/en_US.iso88591"
[root@seagull adm]# ./db2licm -a /tmp/seagull/db2v9/license/db2dpf.lic
 IC1402I License added successfully.

 IC1426I This product is now licensed for use as specified in the License Agreement and License Information documents pertaining to the licensed copy of this product. USE OF THE PRODUCT CONSTITUTES ACCEPTANCE OF THE TERMS OF THE IBM LICENSE AGREEMENT AND LICENSE INFORMATION DOCUMENTS, LOCATED IN THE FOLLOWING DIRECTORY: "/opt/ibm/db2/V9.1/license/en_US.iso88591"
[root@seagull adm]# ./db2licm -a /tmp/seagull/db2v9/license/db2xmlese.lic
 IC1402I License added successfully.

 IC1426I This product is now licensed for use as specified in the License Agreement and License Information documents pertaining to the licensed copy of this product. USE OF THE PRODUCT CONSTITUTES ACCEPTANCE OF THE TERMS OF THE IBM LICENSE AGREEMENT AND LICENSE INFORMATION DOCUMENTS, LOCATED IN THE FOLLOWING DIRECTORY: "/opt/ibm/db2/V9.1/license/en_US.iso88591"
 root@seagull adm]#
 
4.创建实例和样本数据库
[root@seagull V9.1]# cd /opt/ibm/db2/V9.1/instance
[root@seagull instance]# ./db2icrt -p 50000 -u db2fenc1 db2inst1
 BI1070I Program db2icrt completed successfully.
[root@seagull instance]# tail -n 6 -f /etc/services
# Local services
DB2_db2inst1 60000/tcp
DB2_db2inst1_1 60001/tcp
DB2_db2inst1_2 60002/tcp
DB2_db2inst1_END 60003/tcp

[root@seagull ~]# su - db2inst1
 db2inst1@seagull ~]$
[db2inst1@seagull ~]$ db2sampl
  Starting the DB2 instance...
  Creating database "SAMPLE"...
  Connecting to database "SAMPLE"...
  Creating tables and data in schema "DB2INST1"...
  Stopping the DB2 instance...
  'db2sampl' processing complete.
 db2inst1@seagull sqllib]$
[db2inst1@seagull sqllib]$ db2start
11/28/2007 16:49:04 0 0 SQL1063N DB2START processing was successful.
SQL1063N DB2START processing was successful.
[db2inst1@seagull sqllib]$ db2 connect to sample
  Database Connection Information
 Database server = DB2/LINUX 9.1.0
 SQL authorization ID = DB2INST1
 Local database alias = SAMPLE
[db2inst1@seagull sqllib]$ db2 "select * from staff"
 D NAME DEPT JOB YEARS SALARY COMM
------ --------- ------ ----- ------ --------- ---------
  10 Sanders 20 Mgr 7 98357.50 -
  20 Pernal 20 Sales 8 78171.25 612.45
  30 Marenghi 38 Mgr 5 77506.75 -
  40 O'Brien 38 Sales 6 78006.00 846.55
  50 Hanes 15 Mgr 10 80659.80 -
  60 Quigley 38 Sales - 66808.30 650.25
  70 Rothman 15 Sales 7 76502.83 1152.00
  80 James 20 Clerk - 43504.60 128.20
  90 Koonitz 42 Sales 6 38001.75 1386.70
  100 Plotz 42 Mgr 7 78352.80 -
  110 Ngan 15 Clerk 5 42508.20 206.60
  120 Naughton 38 Clerk - 42954.75 180.00
  130 Yamaguchi 42 Clerk 6 40505.90 75.60
  140 Fraye 51 Mgr 6 91150.00 -
  150 Williams 51 Sales 6 79456.50 637.65
  160 Molinare 10 Mgr 7 82959.20 -
  170 Kermisch 15 Clerk 4 42258.50 110.10
  180 Abrahams 38 Clerk 3 37009.75 236.50
  190 Sneider 20 Clerk 8 34252.75 126.50
  200 Scoutten 42 Clerk - 41508.60 84.20
  210 Lu 10 Mgr 10 90010.00 -
  220 Smith 51 Sales 7 87654.50 992.80
  230 Lundquist 51 Clerk 3 83369.80 189.65
  240 Daniels 10 Mgr 5 79260.25 -
  250 Wheeler 51 Clerk 6 74460.00 513.30
  260 Jones 10 Mgr 12 81234.00 -
  270 Lea 66 Mgr 9 88555.50 -
  280 Wilson 66 Sales 9 78674.50 811.50
  290 Quill 84 Mgr 10 89818.00 -
  300 Davis 84 Sales 5 65454.50 806.10
  310 Graham 66 Sales 13 71000.00 200.30
  320 Gonzales 66 Sales 4 76858.20 844.00
  330 Burke 66 Clerk 1 49988.00 55.50
  340 Edwards 84 Sales 7 67844.00 1285.00
  350 Gafney 84 Clerk 5 43030.50 188.00
  35 record(s) selected.

 
5.创建das管理服务器
 
  为了远程客户端能够用控制中心来控制数据库服务器，需要在数据库服务器上安装das,当然，如果只是远程连接而不是远程管理，可以不用装，这里我安装了一下。
[root@seagull ~]# groupadd -g 997 db2asgrp
[root@seagull ~]# mkdir /db2home/db2as
[root@seagull ~]# useradd -u 1002 -g db2asgrp -d /db2home/db2as db2as
[root@seagull ~]# passwd db2as
[root@seagull ~]# su - db2as #为了db2as口令生效
[root@seagull ~]# cd /opt/ibm/db2/V9.1/instance
[root@seagull instance]# ./dascrt -u db2as
 db2as@seagull ~]$
[db2as@seagull ~]$ exit
[root@seagull instance]# su - db2as
[db2as@seagull ~]$ db2admin start
 
6.确认一下db2inst1实例的服务名
 
[db2inst1@seagull ~]$ db2 get dbm cfg|grep SVCENAME
 TCP/IP Service name (SVCENAME) = 50000
#注解：svcename在客户端连接时需要用到
 
7.可以看一下license情况
 
[db2inst1@seagull ~]$ db2licm -l
Product name: "DB2 Enterprise Server Edition"
License type: "CPU"
Expiry date: "Permanent"
Product identifier: "db2ese"
Version information: "9.1"
Annotation: "-3;(_c)"
Features:
DB2 Database Partitioning: "Licensed"
DB2 Performance Optimization ESE: "Not licensed"
DB2 Storage Optimization: "Not licensed"
DB2 Advanced Access Control: "Not licensed"
DB2 Geodetic Data Management: "Not licensed"
Homogeneous Federation for DB2: "Not licensed"
DB2 Pure XML ESE: "Licensed"
 
 
二、安装db2 v9 client on windows，并设置远程连接
 
1.图形界面安装，一步一步下去就可以了，最好选择不安装信息中心。
 
2.设置远程连接
  C:\Documents and Settings\Administrator>db2cmd
  C:\>db2 catalog tcpip node vmdb2v9 remote 10.10.10.100 server 50000
  DB20000I CATALOG TCPIP NODE命令成功完成。
  DB21056W 只有在刷新目录高速缓存以后，目录更改才会生效。
  C:\>db2 catalog db sample as sample at node vmdb2v9
  DB20000I CATALOG DATABASE命令成功完成。
  DB21056W 只有在刷新目录高速缓存以后，目录更改才会生效。

 
3.此时打开控制中心，就可以操作远程的数据库了。
 
 
三、基本操作命令练习
 
[db2inst1@seagull ~]$ db2level #查看版本
 B21085I Instance "db2inst1" uses "32" bits and DB2 code release "SQL09010"
with level identifier "02010107".
 nformational tokens are "DB2 v9.1.0.0", "s060629", "LINUXIA32", and Fix Pack
"0".
Product is installed at "/opt/ibm/db2/V9.1".
[db2inst1@seagull ~]$ db2 connect to sample #连接数据库
  Database Connection Information
 Database server = DB2/LINUX 9.1.0
 SQL authorization ID = DB2INST1
 Local database alias = SAMPLE

[db2inst1@seagull ~]$ db2 list tables for all #查看库中所有表
Table/View Schema Type Creation time
------------------------------- --------------- ----- --------------------------
ACT DB2INST1 T 2007-11-28-16.48.12.138286
........
 
[db2inst1@seagull ~]$ db2 list tables for schema db2inst1 #查看db2inst1的表
Table/View Schema Type Creation time
------------------------------- --------------- ----- --------------------------
ACT DB2INST1 T 2007-11-28-16.48.12.138286
ADEFUSR DB2INST1 S 2007-11-28-16.48.16.196999
 
[db2inst1@seagull ~]$ db2 list tablespaces #查看表空间，如果加show detail可以看详细信息
  Tablespaces for Current Database
 Tablespace ID = 0
 Name = SYSCATSPACE
 Type = Database managed space
 Contents = All permanent data. Regular table space.
 State = 0x0000
  Detailed explanation:
  Normal
 Tablespace ID = 1
 Name = TEMPSPACE1
 Type = System managed space
 Contents = System Temporary data
 State = 0x0000
  Detailed explanation:
  Normal
 Tablespace ID = 2
 Name = USERSPACE1
 Type = Database managed space
 Contents = All permanent data. Large table space.
 State = 0x0000
  Detailed explanation:
  Normal
 Tablespace ID = 3
 Name = IBMDB2SAMPLEREL
 Type = Database managed space
 Contents = All permanent data. Large table space.
 State = 0x0000
  Detailed explanation:
  Normal
 
[db2inst1@seagull ~]$ db2 list tablespace containers for 1 #查看表空间的容器
  Tablespace Containers for Tablespace 1
 Container ID = 0
 Name = /db2home/db2inst1/db2inst1/NODE0000/SAMPLE/T0000001/C0000000.TMP
 Type = Path
 
[db2inst1@seagull ~]$ db2 describe table staff #显示表结构
Column Type Type
name schema name Length Scale Nulls
------------------------------ --------- ------------------ -------- ----- ------
 D SYSIBM SMALLINT 2 0 No
 AME SYSIBM VARCHAR 9 0 Yes
 EPT SYSIBM SMALLINT 2 0 Yes
 OB SYSIBM CHARACTER 5 0 Yes
 EARS SYSIBM SMALLINT 2 0 Yes
 ALARY SYSIBM DECIMAL 7 2 Yes
 OMM SYSIBM DECIMAL 7 2 Yes
 
#创建表，插入记录
[db2inst1@seagull ~]$ db2 "create table t1(id smallint,name varchar(10))"
DB20000I The SQL command completed successfully.
[db2inst1@seagull ~]$ db2 "insert into t1 values(1,'seagull')"
DB20000I The SQL command completed successfully.
[db2inst1@seagull ~]$ db2 "insert into t1 values(2,'lgh')"
DB20000I The SQL command completed successfully.
[db2inst1@seagull ~]$ db2 "insert into t1 values(2,'海鸥')"
DB20000I The SQL command completed successfully.
[db2inst1@seagull ~]$ db2 commit
DB20000I The SQL command completed successfully.
[db2inst1@seagull ~]$ db2 "select * from t1"
 D NAME
------ ----------
  1 seagull
  2 lgh
  2 海鸥
  3 record(s) selected.
 db2inst1@seagull ~]$
 
[db2inst1@seagull ~]$ db2 get db cfg|grep code #查看数据库字符集
 Database code page = 1208
 Database code set = UTF-8
 Database country/region code = 1
 db2inst1@seagull ~]$
 
#注解，在客户端，要设置db2codepage变量与数据库一致，这样才能正确的显示汉字，客户端设置方法db2set db2codepage=1208，在windows 上，db2codepage默认为1386(GBK),linux上默认为1208(UTF-8),aix上默认为819(ISO8859-1)，要注意这个问题，我在客户端查询t1表时就发现是乱码，后来设置了客户端变量db2codepage才搞定。
  另外一种可行的办法是，在服务器上创建生产库时，指定数据库字符集为GBK，这样windows客户端就不用改db2codepage变量了，这个方法我以前用过，当时在服务器上创建生产数据库的脚本为：
 
su - db2inst1

db2set db2codepage=1386 #为了能创建gbk字符集数据库

exit #退一次为了使得设置生效

su - db2inst1

db2 create db GZDC using codeset GBK territory CN

 
connect to gzdc

db2 grant dbadm on database to db2admin

 b2 "create bufferpool BP_8K size -1 pagesize 8192 not extended storage"

db2 "alter bufferpool IBMDEFAULTBP immediate size 50000"

 

CREATE system temporary TABLESPACE TS_SYSTEMP_04 PAGESIZE 4096 MANAGED BY DATABASE USING (FILE '/db2data/db2data01/stmp4k1.dbf' 1024M, FILE '/db2data/db2data02/stmp4k2.dbf' 1024M) EXTENTSIZE 32 PREFETCHSIZE 64

 

CREATE system temporary TABLESPACE TS_SYSTEMP_08 PAGESIZE 8192 MANAGED BY DATABASE USING (FILE '/db2data/db2data01/stmp8k1.dbf' 5G, FILE '/db2data/db2data02/stmp8k2.dbf' 5G) EXTENTSIZE 192 PREFETCHSIZE 384 BUFFERPOOL BP_8K

 

 REATE LARGE TABLESPACE HOSPITALSPACE PAGESIZE 8192 MANAGED BY DATABASE USING

(FILE '/db2data/db2data01/HOSPITAL_01.dbf' 50G,

 FILE '/db2data/db2data02/HOSPITAL_02.dbf' 50G,

 FILE '/db2data/db2data01/HOSPITAL_03.dbf' 50G,

 FILE '/db2data/db2data02/HOSPITAL_04.dbf' 50G,

 FILE '/db2data/db2data01/HOSPITAL_05.dbf' 50G,

 FILE '/db2data/db2data02/HOSPITAL_06.dbf' 50G,

 FILE '/db2data/db2data01/HOSPITAL_07.dbf' 50G,

 FILE '/db2data/db2data02/HOSPITAL_08.dbf' 50G,

 FILE '/db2data/db2data01/HOSPITAL_09.dbf' 50G,

 FILE '/db2data/db2data02/HOSPITAL_10.dbf' 50G)

EXTENTSIZE 192 PREFETCHSIZE 384 BUFFERPOOL BP_8K;

 

CREATE LARGE TABLESPACE MANAGERSPACE PAGESIZE 8192 MANAGED BY DATABASE USING (FILE '/db2data/db2data01/MANAGER_01.dbf' 25G) EXTENTSIZE 192 PREFETCHSIZE 384 BUFFERPOOL BP_8K

 

alter tablespace managerspace add (file '/db2data/db2data02/MANAGER_02.dbf' 25G)