在DB2中從客戶端訪問服務器端的數據庫時，不能直接用connect命令，而必須先建立通信node，再在node的基礎上建立數據庫連接。在命令行的具體操作如下：

->db2 catalog tcpip node?ABC?remote?serverName?server?50000->db2 catalog db?databaseName?at node?ABC->db2 connect to?databaseName?user?Uid?using?Pwd

說明:

catalog tcpip node ABC 中的ABC是由你任意起的一個結點名，結點名不能跟已有的結點名重復

catalog db databaseName at node ABC　中的ABC指的是你在前面起的那個結點名

serverName 服務器名稱(遠程數據庫)

databaseName 數據庫名稱

Uid　用戶名

Pwd　密碼

另外，如果客戶端已經跟同一個服務器建立了node，那么如果你想連接該服務器上的另一個數據庫時，不需要再另建node，直接用同一個node即可。另外，建立node時服務器名稱后的端口不一定是50000，要看DB工程師當時的設置。

相關指令：

list db directory 列出可訪問的db

list node directory 列出可訪問的結點

下邊是DB2安裝和設置連接的具體流程：一、安裝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

#注解：使用默認目錄就可以了

Specify 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

DB2 installation is being initialized.

Total number of tasks to be performed: 46

Total estimated time for all tasks to be performed: 802

Task #1 start

Description: Checking license agreement acceptance

Estimated time 1 second(s)

Task #1 end

Task #2 start

Description: GSKit support

Estimated time 19 second(s)

Task #2 end

Task #3 start

Description: Product Messages – English

Estimated time 10 second(s)

Task #3 end

Task #4 start

Description: Base client support

Estimated time 67 second(s)

Task #4 end

Task #5 start

Description: IBM Software Development Kit (SDK) for Java(TM)

Estimated time 101 second(s)

Task #6 start

Description: Java Help (HTML) – English

Estimated time 7 second(s)

Task #6 end

Task #7 start

Description: Replication with MQ Server

Estimated time 22 second(s)

Task #7 end

Task #8 start

Description: Run-time Environment

Estimated time 78 second(s)

Task #9 start

Description: Java support

Estimated time 6 second(s)

Task #9 end

Task #10 start

Description: SQL procedures

Estimated time 3 second(s)

Task #10 end

Task #11 start

Description: ICU Utilities

Estimated time 39 second(s)

Task #11 end

Task #12 start

Description: SQL Replication Support

Estimated time 19 second(s)

Task #12 end

Task #13 start

Description: Java Common files

Estimated time 20 second(s)

Task #13 end

Task #14 start

Description: Base server support

Estimated time 47 second(s)

Task #14 end

Task #15 start

Description: Control Center Help (HTML) – English

Estimated time 13 second(s)

Task #15 end

Task #16 start

Description: Connect support

Estimated time 3 second(s)

Task #16 end

Task #17 start

Description: Configuration Assistant

Estimated time 4 second(s)

Task #17 end

Task #18 start

Description: Spatial Extender client

Estimated time 6 second(s)

Task #18 end

Task #19 start

Description: Base application development tools

Estimated time 11 second(s)

Task #19 end

Task #20 start

Description: Administration Server

Estimated time 7 second(s)

Task #20 end

Task #21 start

Description: Communication support – TCP/IP

Estimated time 3 second(s)

Task #21 end

Task #22 start

Description: Control Center

Estimated time 38 second(s)

Task #22 end

Task #23 start

Description: Parallel Extension

Estimated time 3 second(s)

Task #23 end

Task #24 start

Description: Replication tools

Estimated time 12 second(s)

Task #24 end

Task #25 start

Description: Relational wrappers common

Estimated time 3 second(s)

Task #25 end

Task #26 start

Description: DB2 data source support

Estimated time 4 second(s)

Task #26 end

Task #27 start

Description: DB2 LDAP support

Estimated time 3 second(s)

Task #27 end

Task #28 start

Description: DB2 Instance Setup wizard

Estimated time 4 second(s)

Task #28 end

Task #29 start

Description: XML Extender

Estimated time 11 second(s)

Task #29 end

Task #30 start

Description: First Steps

Estimated time 3 second(s)

Task #30 end

Task #31 start

Description: DB2 Web Tools

Estimated time 12 second(s)

Task #31 end

Task #32 start

Description: Product Signature for DB2 Enterprise Server Edition

Estimated time 5 second(s)

Task #32 end

Task #33 start

Description: XML Extender samples

Estimated time 3 second(s)

Task #33 end

Task #34 start

Description: ADT sample programs

Estimated time 20 second(s)

Task #34 end

Task #35 start

Description: Sample database source

Estimated time 4 second(s)

Task #35 end

Task #36 start

Description: Spatial Extender samples

Estimated time 3 second(s)

Task #36 end

Task #37 start

Description: Informix data source support

Estimated time 3 second(s)

Task #37 end

Task #38 start

Description: Setting DB2 library path

Estimated time 120 second(s)

Task #38 end

Task #39 start

Description: Executing control tasks

Estimated time 20 second(s)

Task #39 end

Task #40 start

Description: Updating global registry

Estimated time 20 second(s)

Task #40 end

Task #41 start

Description: Starting DB2 Fault Monitor

Estimated time 10 second(s)

Task #41 end

Task #42 start

Description: Updating the db2ls link

Estimated time 1 second(s)

Task #42 end

Task #43 start

Description: Registering DB2 licenses

Estimated time 5 second(s)

Task #43 end

Task #44 start

Description: Setting default global profile registry variables

Estimated time 1 second(s)

Task #44 end

Task #45 start

Description: Initializing instance list

Estimated time 5 second(s)

Task #45 end

Task #46 start

Description: Updating global profile registry

Estimated time 3 second(s)

Task #46 end

The execution completed successfully.

For more information see the DB2 installation log at

“/tmp/db2_install.log.7134″.

[root@seagull db2v9]#

3.安裝license[root@seagull bin]# cd /opt/ibm/db2/V9.1/adm/

[root@seagull adm]# ./db2licm -a /tmp/seagull/db2v9/license/db2ese_c.lic

LIC1402I License added successfully.

LIC1426I 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

LIC1402I License added successfully.

LIC1426I 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

LIC1402I License added successfully.

LIC1426I 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

DBI1070I 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”

ID 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 #查看版本

DB21085I Instance “db2inst1″ uses “32″ bits and DB2 code release “SQL09010″

with level identifier “02010107″.

Informational 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 = 0×0000

Detailed explanation:

Normal

Tablespace ID = 1

Name = TEMPSPACE1

Type = System managed space

Contents = System Temporary data

State = 0×0000

Detailed explanation:

Normal

Tablespace ID = 2

Name = USERSPACE1

Type = Database managed space

Contents = All permanent data. Large table space.

State = 0×0000

Detailed explanation:

Normal

Tablespace ID = 3

Name = IBMDB2SAMPLEREL

Type = Database managed space

Contents = All permanent data. Large table space.

State = 0×0000

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

—————————— ——— —————— ——– —– ——

ID SYSIBM SMALLINT 2 0 No

NAME SYSIBM VARCHAR 9 0 Yes

DEPT SYSIBM SMALLINT 2 0 Yes

JOB SYSIBM CHARACTER 5 0 Yes

YEARS SYSIBM SMALLINT 2 0 Yes

SALARY SYSIBM DECIMAL 7 2 Yes

COMM 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″

ID 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

db2 “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

CREATE 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)

個人注解：

DB2感覺很容易裝啊，比oracle要簡單多了，而且管理好像也簡單，只不過db2不支持rac模式，db2自己的dpf模式，和rac有本質差別，在其中一個數據庫節點所在的主機出現問題時，其管理的數據庫分區必須切換到正常節點才能保證整個數據庫的完整使用，而且正常情況下，數據庫分區之間可能會有比較大的網絡通信負載

看來oracle的rac機制還是比較難實現的。

總結

以上是生活随笔為你收集整理的oracle42997,oracle与db2的比较的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。