【Mycat1.6之操作db2案例】

一、环境准备，配置网络，启动DB2数据库

 

 

 

二、配置Schema文件，引入DB2的JDBC链接jar

 

 

 

三、实验结果验证

 

附件：schema.xml内容

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

	<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
		<table name="tab" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />
		<table name="user1" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />
		<table name="user"  dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />
	</schema>
	 
	 <!-- 此处的 scott命名不当,严格意义上说应该叫Schema-->
	<dataNode name="dn1" dataHost="localhost1_gaojingsong1" database="scott" />
	<dataNode name="dn2" dataHost="localhost1_gaojingsong2" database="scott" />
	<dataNode name="dn3" dataHost="localhost1_gaojingsong3" database="scott" />
	
	<dataHost name="localhost1_gaojingsong1" maxCon="1000" minCon="1" balance="0" writeType="0" 
		dbType="db2" dbDriver="jdbc">
		<heartbeat>select 1 from sysibm.sysdummy1</heartbeat>
		<writeHost host="hostM1" url="jdbc:db2://192.168.8.129:50000/TEST001" 
                     user="db2inst1" password="123456" >
		</writeHost> 
	</dataHost>
	
	<dataHost name="localhost1_gaojingsong2" maxCon="1000" minCon="1" balance="0" writeType="0" 
		dbType="db2" dbDriver="jdbc">
		<heartbeat>select 1 from sysibm.sysdummy1</heartbeat>
		<writeHost host="hostM1" url="jdbc:db2://192.168.8.129:50000/TEST001" 
                     user="db2inst1" password="123456" >
		</writeHost> 
	</dataHost>
	
	<dataHost name="localhost1_gaojingsong3" maxCon="1000" minCon="1" balance="0" writeType="0" 
		dbType="db2" dbDriver="jdbc">
		<heartbeat>select 1 from sysibm.sysdummy1</heartbeat>
		<writeHost host="hostM1" url="jdbc:db2://192.168.8.129:50000/TEST001" 
                        user="db2inst1" password="123456" >
		</writeHost> 
	</dataHost> 
</mycat:schema>

数据库操作参数：
Last login: Fri May  5 21:42:37 2017
[root@localhost ~]# su - db2linst1
su: 用户 db2linst1 不存在
[root@localhost ~]# su - db2inst1
[db2inst1@localhost ~]$ db2level
DB21085I  Instance "db2inst1" uses "64" bits and DB2 code release "SQL09070" 
with level identifier "08010107".
Informational tokens are "DB2 v9.7.0.0", "s090521", "LINUXAMD6497", and Fix 
Pack "0".
Product is installed at "/opt/ibm/db2/V9.7_01".

[db2inst1@localhost ~]$ db2 list db directory

 System Database Directory

 Number of entries in the directory = 3

Database 1 entry:

 Database alias                       = TOOLSDB
 Database name                        = TOOLSDB
 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 2 entry:

 Database alias                       = XXX
 Database name                        = XXX
 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                       = TEST001
 Database name                        = TEST001
 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         =

[db2inst1@localhost ~]$ db2 connect to TEST001
SQL1032N  No start database manager command was issued.  SQLSTATE=57019
[db2inst1@localhost ~]$ db2start
05/05/2017 21:48:12     0   0   SQL1063N  DB2START processing was successful.
SQL1063N  DB2START processing was successful.
[db2inst1@localhost ~]$ db2 connect to TEST001

   Database Connection Information

 Database server        = DB2/LINUXX8664 9.7.0
 SQL authorization ID   = DB2INST1
 Local database alias   = TEST001

[db2inst1@localhost ~]$ DB2 LIST TABLES FOR USER
-bash: DB2: command not found
[db2inst1@localhost ~]$ db2  LIST TABLES FOR USER

Table/View                      Schema          Type  Creation time             
------------------------------- --------------- ----- --------------------------
USER                            DB2INST1        T     2012-08-02-05.57.32.932312
USER1                           DB2INST1        T     2012-08-02-06.47.42.505658

  2 record(s) selected.

[db2inst1@localhost ~]$ 
[db2inst1@localhost ~]$