问题:
DB2中有些操作要求数据库处于非激活,或者说离线(offline)状态,这时候需要停库操作,比如db2dart要求数据库是offline的,那什么样的数据库才是离线状态的?如何停库?
答:
首先需要确认一点,停库和停实例是两个概念。停库是指是使database处于非激活状态,而停实例使用的命令是db2stop
1.激活数据库的2种方法
有两种办法可以激活数据库,第一种是显式地激活,即使用db2 activate database <dbname>。第二种是隐式地激活,当有首个连接到数据库时,数据库会被隐式激活,即db2 connect to <dbname>
2.查看数据库是否处于活动状态
使用db2 list active databases,如下例,数据库SAMPLE处于活动状态
C:\windows\system32> db2 "list active databases"
Active Databases
Database name = SAMPLE
Applications connected currently = 0
Database path = C:\DB2INST1\NODE0000\SQL00005\MEMBER0000\
3.停库的办法
这个要根据数据库是显式地激活,还是隐式地激活的。如果是显式地激活的,必须使用db2 deactivate db <dbname>,如果是隐式地激活,最后一个连接断开时,库自然停掉。
测试1. 显示地激活数据库,使用list applications查看,虽然没有连接,但数据库是活动状态。
C:\windows\system32>db2 "activate db sample"
DB20000I The ACTIVATE DATABASE command completed successfully.
C:\windows\system32>db2 "list active databases"
Active Databases
Database name = SAMPLE
Applications connected currently = 0
Database path = C:\DB2INST1\NODE0000\SQL00005\MEMBER0000\
C:\windows\system32>db2 "list applications"
SQL1611W No data was returned by Database System Monitor.
C:\windows\system32>db2 "deactivate db sample"
DB20000I The DEACTIVATE DATABASE command completed successfully.
C:\windows\system32>db2 "list active databases"
SQL1611W No data was returned by Database System Monitor.
测试2. 隐式地激活数据库,使用list applications查看,有连接,并且数据库是活动状态
C:\windows\system32>db2 "connect to sample"
Database Connection Information
Database server = DB2/NT64 10.5.6
SQL authorization ID = MIAOQING...
Local database alias = SAMPLE
C:\windows\system32>db2 "list applications"
Auth Id Application Appl. Application Id DB # of
Name Handle Name Agents
-------- -------------- ---------- -------------------------------------------------------------- -------- -----
MIAOQIN> db2bp.exe 130 *LOCAL.DB2INST1.160923021540 SAMPLE 1
C:\windows\system32>db2 "list active databases"
Active Databases
Database name = SAMPLE
Applications connected currently = 1
Database path = C:\DB2INST1\NODE0000\SQL00005\MEMBER0000\
C:\windows\system32>db2 "terminate"
DB20000I The TERMINATE command completed successfully.
C:\windows\system32>db2 "list applications"
SQL1611W No data was returned by Database System Monitor.
C:\windows\system32>db2 "list active databases"
SQL1611W No data was returned by Database System Monitor.