前面通过control center将客户机连接到服务器时,本质上是通过命令行完成的,,在弄清具体过程之前,必须先弄明白 节点目录,系统数据库目录,本地数据库目录几个概念。
节点目录,系统数据库目录,远程数据库目录
创建数据库之后,DB2才会有数据库目录;只有在一个需要连接到服务器的客户机上才需要编目。类似于oracle的tnsname机制。
测试环境:
| OS | DB2 version | Instance | Database |
HOST1 | Windows 2008 R2 x64 | v9.5 ese x86 | DB2 | sample,toolsdb |
HOST2 | Linux 5.8 x64 | v9.7 aese x64 | db2inst1 | sample,test1 |
并且在HOST1中使用control center将HOST2下的实例与数据库进行了注册
一.节点目录
用于存储远程数据库的所有连通性信息。
在control conter所在机器(即客户机角色),查看节点目录
C:\Program Files (x86)\IBM\SQLLIB\BIN>db2 list node directory
Node Directory
Number of entries in the directory = 1
Node 1 entry:
Node name = DB2INST1
Comment =
Directory entry type = LOCAL
Protocol = TCPIP
Hostname = ibm1
Service name = 50000
由windows control center得到的语句:
UNCATALOG NODE IBM1;
CATALOG ADMIN TCPIP NODE IBM1 REMOTE ibm1 SYSTEM IBM1 OSTYPE LINUXX8664;
UNCATALOG NODE DB2INST1;
CATALOG TCPIP NODE DB2INST1 REMOTE ibm1 SERVER 50000 REMOTE_INSTANCE db2inst1 SYSTEM IBM1 OSTYPE LINUXX8664;
节点目录文件的位置:
windows2003下:
C:\Documents and Settings\All Users\Application Data\IBM\DB2\DB2COPY1\DB2\SQLNODIR\SQLNODIR
windows2008:
C:\ProgramData\IBM\DB2\DB2COPY1\DB2\SQLNODIR
Linux下:
/home/db2inst1/sqllib/sqlnodir/sqlnodir
同一文件夹下的SQLNOBAK是SQLNODIR的备份,都是二进制文件。
在linux下建立联通到windows下的节点目录:
[db2inst1@ibm1 ~]$ db2 catalog tcpip node db2win remote 10.0.0.52 server 50000
DB20000I The CATALOG TCPIP NODE command completed successfully.
DB21056W Directory changes may not be effective until the directory cache is
refreshed.
[db2inst1@ibm1 ~]$ db2 list node directory
Node Directory
Number of entries in the directory = 1
Node 1 entry:
Node name = DB2WIN
Comment =
Directory entry type = LOCAL
Protocol = TCPIP
Hostname = 10.0.0.52
Service name = 50000
解除节点目录:
[db2inst1@ibm1 ~]$ db2 uncatalog node db2win
DB20000I The UNCATALOG NODE command completed successfully.
DB21056W Directory changes may not be effective until the directory cache is
refreshed.
二.系统数据库目录:
包含本地数据库目录与远程映射到本地的数据库目录。
系统数据库目录是在实例级存储的,对于每个实例,都存在一个数据库目录文件,该文件针对此实例编目的每个数据库都包含一个条目。
windows下查看系统数据库目录
C:\Program Files (x86)\IBM\SQLLIB\BIN>db2 list db directory
System Database Directory
Number of entries in the directory = 4
Database 1 entry:
Database alias = TOOLSDB
Database name = TOOLSDB
Local database directory = C:
Database release level = c.00
Comment =
Directory entry type = Indirect
Catalog database partition number = 0
Alternate server hostname =
Alternate server port number =
Database 2 entry:
Database alias = SAMPLE1
Database name = SAMPLE
Node name = DB2INST1
Database release level = c.00
Comment =
Directory entry type = Remote
Catalog database partition number = -1
Alternate server hostname =
Alternate server port number =
Database 3 entry:
Database alias = TEST1
Database name = TEST1
Node name = DB2INST1
Database release level = c.00
Comment =
Directory entry type = Remote
Catalog database partition number = -1
Alternate server hostname =
Alternate server port number =
Database 4 entry:
Database alias = SAMPLE
Database name = SAMPLE
Local database directory = C:
Database release level = c.00
Comment =
Directory entry type = Indirect
Catalog database partition number = 0
Alternate server hostname =
Alternate server port number =
Linux下将windows下DB注册到数据库目录:
(特别注意,注册数据库目录的前提是要先有节点目录)
[db2inst1@ibm1 ~]$
db2 catalog db sample as sample2 at node db2win
DB20000I The CATALOG DATABASE command completed successfully.
DB21056W Directory changes may not be effective until the directory cache is
refreshed.
Linux下查看系统数据库目录:
[db2inst1@ibm1 ~]$ db2 list db directory
System Database Directory
Number of entries in the directory = 3
Database 1 entry:
Database alias = SAMPLE2
Database name = SAMPLE
Node name = DB2WIN
Database release level = d.00
Comment =
Directory entry type = Remote
Catalog database partition number = -1
Alternate server hostname =
Alternate server port number =
Database 2 entry:
Database alias = TEST1
Database name = TEST1
Local database directory = /home/db2inst1
Database release level = d.00
Comment =
Directory entry type = Indirect
Catalog database partition number = 0
Alternate server hostname =
Alternate server port number =
Database 3 entry:
Database alias = SAMPLE
Database name = SAMPLE
Local database directory = /home/db2inst1
Database release level = d.00
Comment =
Directory entry type = Indirect
Catalog database partition number = 0
Alternate server hostname =
Alternate server port number =
解除数据库目录
db2 uncatalog db sample2
DB20000I The UNCATALOG DATABASE command completed successfully.
DB21056W Directory changes may not be effective until the directory cache is
refreshed.
三. 本地数据库目录
包含了驻留在当前机器上的数据库信息。本地数据库目录目录驻留在数据库结构内部。当create database
时会隐式地对数据库进行编目。
windows下查看本地数据库目录:
db2 list db directory on c:
Local Database Directory on c:
Number of entries in the directory = 2
Database 1 entry:
Database alias = TOOLSDB
Database name = TOOLSDB
Database directory = SQL00001
Database release level = c.00
Comment =
Directory entry type = Home
Catalog database partition number = 0
Database partition number = 0
Database 2 entry:
Database alias = SAMPLE
Database name = SAMPLE
Database directory = SQL00002
Database release level = c.00
Comment =
Directory entry type = Home
Catalog database partition number = 0
Database partition number = 0
linux下查看本地数据库目录:
db2 list db directory on /home/db2inst1/
Local Database Directory on /home/db2inst1/
Number of entries in the directory = 2
Database 1 entry:
Database alias = TEST1
Database name = TEST1
Database directory = SQL00001
Database release level = d.00
Comment =
Directory entry type = Home
Catalog database partition number = 0
Database partition number = 0
Database 2 entry:
Database alias = SAMPLE
Database name = SAMPLE
Database directory = SQL00002
Database release level = d.00
Comment =
Directory entry type = Home
Catalog database partition number = 0
Database partition number = 0
四.从linux下,通过系统数据库目录连接到windows下的sample数据库
在准备connect 之前,先查看有哪些db directory可用:
[db2inst1@ibm1 ~]$ db2 list db directory
System Database Directory
Number of entries in the directory = 3
Database 1 entry:
Database alias = SAMPLE2
Database name = SAMPLE
Node name = DB2WIN
Database release level = d.00
Comment =
Directory entry type = Remote
Catalog database partition number = -1
Alternate server hostname =
Alternate server port number =
Database 2 entry:
Database alias = TEST1
Database name = TEST1
Local database directory = /home/db2inst1
Database release level = d.00
Comment =
Directory entry type = Indirect
Catalog database partition number = 0
Alternate server hostname =
Alternate server port number =
Database 3 entry:
Database alias = SAMPLE
Database name = SAMPLE
Local database directory = /home/db2inst1
Database release level = d.00
Comment =
Directory entry type = Indirect
Catalog database partition number = 0
Alternate server hostname =
Alternate server port number =
db2 connect to sample2 user db2admin using db2admin
Database Connection Information
Database server = DB2/NT 9.5.0
SQL authorization ID = DB2ADMIN
Local database alias = SAMPLE2
[db2inst1@ibm1 ~]$ db2 "select * from administrator.employee"
EMPNO FIRSTNME MIDINIT LASTNAME WORKDEPT PHONENO HIREDATE JOB EDLEVEL SEX BIRTHDATE SALARY BONUS COMM
------ ------------ ------- --------------- -------- ------- ---------- -------- ------- --- ---------- ----------- ----------- -----------
000010 CHRISTINE I HAAS A00 3978 01/01/1995 PRES 18 F 08/24/1963 152750.00 1000.00 4220.00
000020 MICHAEL L THOMPSON B01 3476 10/10/2003 MANAGER 18 M 02/02/1978 94250.00 800.00 3300.00
000030 SALLY A KWAN C01 4738 04/05/2005 MANAGER 20 F 05/11/1971 98250.00 800.00 3060.00
.......................
42 record(s) selected.