db2 命令(for linux)

DB2学习文档

(一)DB2的安装说明：
===========================================
[root@fedora home]# ./db2_install

Specify one or more of the following keywords,
separated by spaces, to install DB2 products.
Keyword Product Description
DB2.ESE DB2 Enterprise Server Edition for LINUX
DB2.ADMCL DB2 Administration Client for LINUX
DB2.ADCL DB2 Application Development Client for LINUX
Enter "help" to redisplay product names.
Enter "quit" to exit.
***********************************************************
DB2.ESE
The installation logfile can be found in /tmp/db2_install_log.3439.
db2_install program completed successfully.

(二)实例的相关说明
================================================
(1.)在建立实例前，首先要创建组和用户
[root@fedora home]# groupadd wangzm
[root@fedora home]# groupadd wzm
[root@fedora home]# useradd -g wangzm -d -m /home/wangzm -p 123456 wangzm
[root@fedora home]# useradd -g wzm -m -d /home/wzm -p 123456 wzm

(2.)实例的创建
[root@fedora home]# /opt/IBM/db2/V8.1/instance/db2icrt -u wangzm wzm
DBI1070I Program db2icrt completed successfully.

(3.)数据库的启动
[root@fedora home]# su - wzm
[wzm@fedora ~]$ db2start
05/20/2006 14:27:39 0 0 SQL1063N DB2START processing was successful.
SQL1063N DB2START processing was successful.
(4.)db2的进程
wzm 5981 0.0 14.1 121364 17808 pts/0 S 14:27 0:00 db2sysc
wzm 5987 0.0 13.6 121364 17108 pts/0 S 14:27 0:00 db2ipccm
wzm 5988 0.0 14.1 121364 17808 pts/0 S 14:27 0:00 db2resync
wzm 5989 0.0 12.9 118820 16228 pts/0 S 14:27 0:00 db2srvlst
wzm 5991 0.2 18.8 138584 23760 pts/0 Sl 14:27 0:01 db2hmon ,0,0,0,1,0,0,0,1e014,2,0,1,9fe0,0x11250000,0x11250000,15fc000,98006,2,138017
wzm 6204 0.0 6.0 35996 7624 pts/0 S 14:31 0:00 /home/wzm/sqllib/bin/db2bp 5846A513 5 A
wzm 6285 0.0 14.2 121364 17864 pts/0 S 14:32 0:00 db2agent (idle)
(5.)实例的显示----显示所有的
[wzm@fedora ~]$ db2ilist
db2inst1
dl
qing
wzm
(6.)显示当前的实例
[wzm@fedora ~]$ db2 get instance
The current database manager instance is: wzm
[wzm@fedora ~]$

(7.)删除一个实例
[root@fedora ~]# /opt/IBM/db2/V8.1/instance/db2idrop qing
DBI1070I Program db2idrop completed successfully.
(8.)列出当前实例中有哪些数据库
[wzm@fedora ~]$ db2 list db directory
System Database Directory
Number of entries in the directory = 1
Database 1 entry:
Database alias = WZMDB
Database name = WZMDB
Local database directory = /home/wzm
Database release level = a.00
Comment =
Directory entry type = Indirect
Catalog database partition number = 0
Alternate server hostname =
Alternate server port number =
(三.)数据库的操作说明
===========================================================
(1.) 数据库的创建
[wzm@fedora ~]$ db2 "create database wzmdb"
DB20000I The CREATE DATABASE command completed successfully.
(2.)连接数据库
[wzm@fedora ~]$ db2 connect to wzmdb
Database Connection Information
Database server = DB2/LINUX 8.2.0
SQL authorization ID = WZM
Local database alias = WZMDB
（）查看表的空间
db2 list tablespaces (show detail)
db2pd -tablespaces -db <数据库名>

(3.)表的创建
[wzm@fedora ~]$ db2 "create table wzmtb (id int,name varchar(30))"
DB20000I The SQL command completed successfully.
(4.) 显示数据库里有那些表

[wzm@fedora ~]$ db2 list tables
Table/View Schema Type Creation time
------------------------------- --------------- ----- --------------------------
WZMTB WZM T 2006-05-20-14.46.34.953560
1 record(s) selected.
(5.) 向表里添加数据
[wzm@fedora ~]$ db2 "insert into wzmtb values (1,''wzm'')"
DB20000I The SQL command completed successfully.
(6.)显示表的内容
[wzm@fedora ~]$ db2 "select * from wzmtb"
ID NAME
----------- ------------------------------
1 wzm
1 record(s) selected.
(7.)断开数据库的连接
[wzm@fedora ~]$ db2 disconnect wzmdb
DB20000I The SQL DISCONNECT command completed successfully.

(8.)显示当前数据库连接的有哪些应用程序
[wzm@fedora ~]$ db2 list application
Auth Id Application Appl. Application Id DB # of
Name Handle Name Agents
-------- -------------- ---------- ------------------------------ -------- -----
WZM db2bp 44 *LOCAL.wzm.060520074504 WZMDB 1

(9.)db2的停止
[wzm@fedora ~]$ db2stop
05/20/2006 15:50:47 0 0 SQL1064N DB2STOP processing was successful.
SQL1064N DB2STOP processing was successful.

(10.)db2的强制停止
[wzm@fedora ~]$ db2stop force
05/20/2006 16:01:32 0 0 SQL1064N DB2STOP processing was successful.
SQL1064N DB2STOP processing was successful.
(五.)db2的参数说明
===============================================
1，
(1.1)db2set -lr...........列出要设置 DB2 概要文件注册表 (针对db2数据库)
(1.2)db2set -all..........列出在服务器上已经设置的所有DB2概要文件注册表 (针对db2数据库)
(1.3)db2 get db cfg ......查看数据库的配置参数，(针对所有数据库都有效)
(1.4)db2 get db cfg for wzmdb ......查看数据库的配置参数，(针对具体的数据库有效)
(1.5)db2 get dbm cfg .............查看数据库管理器的配置参数 (针对具体的实例有效)
2.参数的更改

(2.1) db2 update db cfg using 参数名=参数值
列： db2set DB2COMM=TCPIP

(2.2)显示以更改的参数(说明：参数该完之后需重起数据库才能生效)
列：[wzm@fedora ~]$ db2set
DB2COMM=TCPIP
(2.3)给实例增加端口号
vi/etc/services
DB2_db2inst1 60000/tcp
DB2_db2inst1_1 60001/tcp
DB2_db2inst1_2 60002/tcp
DB2_db2inst1_END 60003/tcp
DB2_dl 60004/tcp
DB2_dl_1 60005/tcp
DB2_dl_2 60006/tcp
DB2_dl_END 60007/tcp
#DB2tcp 50000/tcp
#DB2tcp1 50001/tcp
DB2_qing 60008/tcp
DB2_qing_1 60009/tcp
DB2_qing_2 60010/tcp
DB2_qing_END 60011/tcp
#
db2_master 50506/tcp
db2_slave 50505/tcp
DB2_wzm 60012/tcp
DB2_wzm_1 60013/tcp
DB2_wzm_2 60014/tcp
DB2_wzm_END 60015/tcp

(2.4)把dbm的VCENAME 的值和/etc/services中的端口号对应
[wzm@fedora ~]$ db2 update dbm cfg using SVCENAME 50000
DB20000I The UPDATE DATABASE MANAGER CONFIGURATION command completed

(2.5)查看监听端口
[wzm@fedora db2backup]$ netstat -an | grep 50000
tcp 0 0 0.0.0.0:50000 0.0.0.0:* LISTEN
说明：综合上述的更改，这时就可以远程连接到实例。
(六.) 数据库的远程编目
===========================================================================
(6.1) 编目节点
[dl@fedora ~]$ db2 catalog tcpip node wzmode remote 10.4.5.212 server 50000
DB20000I The CATALOG TCPIP NODE command completed successfully.
DB21056W Directory changes may not be effective until the directory cache is
refreshed.

(6.2)显示编目的接点
[dl@fedora ~]$ db2 list node directory
Node Directory
Number of entries in the directory = 2
Node 1 entry:
Node name = WZMODE
Comment =
Directory entry type = LOCAL
Protocol = TCPIP
Hostname = 10.4.5.212
Service name = 50000

(6.3)编目远程的数据库到节点
[dl@fedora ~]$ db2 catalog db wzmdb as wzmdb_bm at node wzmode
DB20000I The CATALOG DATABASE command completed successfully.
DB21056W Directory changes may not be effective until the directory cache is
refreshed.
(6.4)显示编目的数据库
[dl@fedora ~]$ db2 list db directory
System Database Directory
Number of entries in the directory = 3

Database 1 entry:
Database alias = WZMDB_BM
Database name = WZMDB
Node name = WZMODE
Database release level = a.00
Comment =
Directory entry type = Remote
Catalog database partition number = -1
Alternate server hostname =
Alternate server port number =

(6.4)连接编目的数据库
[dl@fedora ~]$ db2 connect to wzmdb_bm user wzm using 123456
Database Connection Information
Database server = DB2/LINUX 8.2.0
SQL authorization ID = WZM
Local database alias = WZMDB_BM
(6.5) 显示编目数据库的内容
[dl@fedora ~]$ db2 list tables
Table/View Schema Type Creation time
------------------------------- --------------- ----- --------------------------
WZMTB WZM T 2006-05-20-14.46.34.953560
1 record(s) selected.

(七.)数据库中的数据移动
================================================================================
(7.1) 将表中的数据导出
[wzm@fedora db2backup]$ db2 "export to wzmtb.ixf of ixf messages wzm.out select * from wzmtb"
Number of rows exported: 1
(7.2) 将数据导入
[wzm@fedora db2backup]$ db2 "import from wzmtb.ixf of ixf messages wzm.out create into wzmtb_tb"
Number of rows read = 1
Number of rows skipped = 0
Number of rows inserted = 1
Number of rows updated = 0
Number of rows rejected = 0
Number of rows committed = 1
(7.3)显示到入的内容
[wzm@fedora db2backup]$ db2 list tables
Table/View Schema Type Creation time
------------------------------- --------------- ----- --------------------------
WZMTB WZM T 2006-05-20-14.46.34.953560
WZMTB_TB WZM T 2006-05-20-17.43.52.010679
2 record(s) selected.
(7.4) 另一种的数据到入方法(说明，首先要建一个和原来表结构一样的表)
[wzm@fedora db2backup]$ db2 "load from wzmtb.ixf of ixf messages wzm.out insert into wzmtb_tb_tb"
Number of rows read = 1
Number of rows skipped = 0
Number of rows loaded = 1
Number of rows rejected = 0
Number of rows deleted = 0
Number of rows committed = 1

(八.)数据库备份与恢复
============================================================================
(8.1)脱机备份
[wzm@fedora db2backup]$ db2 backup db wzmdb to /home/db2backup/
Backup successful. The timestamp for this backup image is : 20060520175904
(8.2)恢复
[wzm@fedora db2backup]$ db2 restore db wzmdb taken at 20060520175904 into wamdb_db
DB20000I The RESTORE DATABASE command completed successfully.

(8.3)恢复显示

[wzm@fedora db2backup]$ db2 list db directory
System Database Directory
Number of entries in the directory = 2
Database 1 entry:
Database alias = WZMDB
Database name = WZMDB
Local database directory = /home/wzm
Database release level = a.00
Comment =
Directory entry type = Indirect
Catalog database partition number = 0
Alternate server hostname =
Alternate server port number =
Database 2 entry:
Database alias = WAMDB_DB
Database name = WAMDB_DB
Local database directory = /home/wzm
Database release level = a.00
Comment =
Directory entry type = Indirect
Catalog database partition number = 0
Alternate server hostname =
Alternate server port number =

(8.4)在线备份(online)

(8.4.1) Log retain for recovery enabled (LOGRETAIN) = OFF 状态只能脱机备份
更改参数后可以进行在线全备份
[wzm@fedora ~]$ db2 update db cfg using LOGRETAIN ON
DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.
SQL1363W One or more of the parameters submitted for immediate modification
were not changed dynamically. For these configuration parameters, all
applications must disconnect from this database before the changes become
effective.
(8.4.2) 更改后的参数显示
wzm@fedora ~]$ db2 get db cfg | grep -i logretain
Log retain for recovery enabled (LOGRETAIN) = RECOVERY
First log archive method (LOGARCHMETH1) = LOGRETAIN
(8.4.3)在更改完参数后。连不上数据库，必须进行一次脱机全备份才能连上数据库
[wzm@fedora ~]$ db2 connect to wzmdb
SQL1116N A connection to or activation of database "WZMDB" cannot be made
because of BACKUP PENDING. SQLSTATE=57019

(8.4.4)进行一次全备份
[wzm@fedora ~]$ db2 backup db wzmdb to /home/db2backup/
Backup successful. The timestamp for this backup image is : 20060521124511
(8.4.5)连接数据库成功
[wzm@fedora ~]$ db2 connect to wzmdb
Database Connection Information
Database server = DB2/LINUX 8.2.0
SQL authorization ID = WZM
Local database alias = WZMDB
(8.4.6) 进行在线全备份
[wzm@fedora db2backup]$ db2 backup db wzmdb online to /home/db2backup/ include logs without prompting
Backup successful. The timestamp for this backup image is : 20060521124905
(8.4.7)备份恢复
[wzm@fedora db2backup]$ db2 restore db wzmdb taken at 20060521130338 into wzmdb_db logtarget /home/db2backup/logs/ without prompting
SQL2540W Restore is successful, however a warning "2580" was encountered
during Database Restore while processing in No Interrupt mode.
(8.4.8)连接数据库不成功，显示暂挂前滚状态。
[wzm@fedora db2backup]$ db2 connect to wzmdb_db
SQL1117N A connection to or activation of database "WZMDB_DB" cannot be made
because of ROLL-FORWARD PENDING. SQLSTATE=57019

(8.4.9)日志的恢复
[wzm@fedora db2backup]$ db2 "rollforward db wzmdb_db to end of logs and stop overflow log path (/home/db2backup/logs)"
Rollforward Status
Input database alias = wzmdb_db
Number of nodes have returned status = 1
Node number = 0
Rollforward status = not pending
Next log file to be read =
Log files processed = S0000001.LOG - S0000001.LOG
Last committed transaction = 2006-05-21-05.03.53.000000
DB20000I The ROLLFORWARD command completed successfully.

(8.9.10)数据库的连接成功
[wzm@fedora db2backup]$ db2 connect to wzmdb_db
Database Connection Information
Database server = DB2/LINUX 8.2.0
SQL authorization ID = WZM
Local database alias = WZMDB_DB

(8.5)增量备份(两种)说明，在进行增量备份是第一次要全备份，接着才可以进行增量备份
增量备份需要该的参数(trackmod)
[wzm@fedora logs]$ db2 get db cfg for wzmdb | grep -i trackmod
Track modified pages (TRACKMOD) = OFF
[wzm@fedora logs]$ db2 update db cfg for wzmdb using TRACKMOD ON
DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.
(8.5.1)全备份
[wzm@fedora db2backup]$ db2 backup db wzmdb online to /home/db2backup/ include logs without prompting
Backup successful. The timestamp for this backup image is : 20060521140150
(8.5.2)增量备份
[wzm@fedora db2backup]$ db2 backup db wzmdb online incremental to /home/db2backup/ include logs without prompting
Backup successful. The timestamp for this backup image is : 20060521140301
(8.5.3)备份的恢复(说明先指明恢复的时间点)
[wzm@fedora db2backup]$ db2 restore db wzmdb incremental taken at 20060521140301 into wzmddd logtarget /home/db2backup/logs
DB20000I The RESTORE DATABASE command completed successfully.
(8.5.4)进行一次恢复(恢复到全备份的时间点的内容)
[wzm@fedora db2backup]$ db2 restore db wzmdb incremental taken at 20060521140150 into wzmddd logtarget /home/db2backup/logs
DB20000I The RESTORE DATABASE command completed successfully.
(8.5.5)恢复到所指定的时间点的内容
[wzm@fedora db2backup]$ db2 restore db wzmdb incremental taken at 20060521140301 into wzmddd logtarget /home/db2backup/logs
SQL2580W Warning! Restoring logs to a path which contains existing log files. Attempting to overwrite an existing log file during restore will cause the restore operation to fail.
Do you want to continue ? (y/n) y
DB20000I The RESTORE DATABASE command completed successfully.

(8.5.6)进行前滚恢复
[wzm@fedora db2backup]$ db2 "rollforward db wzmddd to end of logs and stop overflow log path (home/db2backup/logs)"
Rollforward Status
Input database alias = wzmddd
Number of nodes have returned status = 1
Node number = 0
Rollforward status = not pending
Next log file to be read =
Log files processed = S0000006.LOG - S0000006.LOG
Last committed transaction = 2006-05-21-06.03.12.000000
DB20000I The ROLLFORWARD command completed successfully.

(8.5.7)db2连接
[wzm@fedora db2backup]$ db2 connect to wzmddd
Database Connection Information
Database server = DB2/LINUX 8.2.0
SQL authorization ID = WZM
Local database alias = WZMDDD

(九,)HADR的配置
=======================================================
说明：一。主服务器10.4.5.212 重服务器10.4.5.210
二。进行hadr的配置需要数据库的名字相同，但实例名字可以不同。

三。db2 update db cfg for wzmdb using LOGINDEXBUILD on

1.首先对主数据库进行一次全备份
[wzm@fedora db2backup]$ db2 backup db wzmdb
Backup successful. The timestamp for this backup image is : 20060521165702

2.将备份考到重服务器上
[wzm@fedora db2backup]$ scp WZMDB.0.wzm.NODE0000.CATN0000.20060521165702.001 dl@10.4.5.210:/home/dl/db2backup/
dl@10.4.5.210''s password:
WZMDB.0.wzm.NODE0000.CATN0000.20060521165702.001 100% 35MB 1.3MB/s 00:26
3.对重服务器进行数据库的恢复
[dl@fedora db2backup]$ db2 restore db wzmdb taken at 20060521165702
SQL2539W Warning! Restoring to an existing database that is the same as the ba ckup image database. The database files will be deleted.
Do you want to continue ? (y/n) y
DB20000I The RESTORE DATABASE command completed successfully.

4.修改之后的配置文件
[wzm@fedora db2backup]$ db2 get db cfg for wzmdb | grep -i hadr
HADR database role = STANDARD
HADR local host name (HADR_LOCAL_HOST) = 10.4.5.212
HADR local service name (HADR_LOCAL_SVC) = wzm8
HADR remote host name (HADR_REMOTE_HOST) = 10.4.5.210
HADR remote service name (HADR_REMOTE_SVC) = wzm9
HADR instance name of remote server (HADR_REMOTE_INST) = dl
HADR timeout value (HADR_TIMEOUT) = 120
HADR log write synchronization mode (HADR_SYNCMODE) = NEARSYNC

同理
[dl@fedora db2backup]$ db2 get db cfg for wzmdb | grep -i hadr
HADR database role = STANDARD
HADR local host name (HADR_LOCAL_HOST) = 10.4.5.210
HADR local service name (HADR_LOCAL_SVC) = wzm9
HADR remote host name (HADR_REMOTE_HOST) = 10.4.5.212
HADR remote service name (HADR_REMOTE_SVC) = wzm8
HADR instance name of remote server (HADR_REMOTE_INST) = wzm
HADR timeout value (HADR_TIMEOUT) = 120
HADR log write synchronization mode (HADR_SYNCMODE) = NEARSYNC
5.启动重服务器
[dl@fedora ~]$ db2 start hadr on db wzmdb as standby
DB20000I The START HADR ON DATABASE command completed successfully.
6.启动主服务器
[wzm@fedora db2backup]$ db2 start hadr on db wzmdb as primary
DB20000I The START HADR ON DATABASE command completed successfully.

7.查看hadr的状态为peer状态。
[dl@fedora ~]$ db2pd -hadr -db wzmdb
Database Partition 0 -- Database WZMDB -- Active -- Up 0 days 00:01:15
HADR Information:
Role State SyncMode HeartBeat LogGapRunAvg (bytes)
Standby Peer Nearsync 0 0
ConnectStatus ConnectTime Timeout
Connected Sun May 21 13:40:57 2006 (1148190057) 120
LocalHost LocalService RemoteHost RemoteService RemoteInstance
10.4.5.210 wzm9
10.4.5.212 wzm8 wzm
PrimaryFile PrimaryPg PrimaryLSN StandByFile StandByPg StandByLSN
S0000007.LOG 0 0x000002AF800080AB3C43A05E0 S0000007.LOG 0 0x000002AF800080AB3C43A05E0

[wzm@fedora db2backup]$ db2pd -hadr -db wzmdb
Database Partition 0 -- Database WZMDB -- Active -- Up 0 days 00:00:33
HADR Information:
Role State SyncMode HeartBeat LogGapRunAvg (bytes)
Primary Peer Nearsync 0 0
ConnectStatus ConnectTime Timeout
Connected Sun May 21 17:18:39 2006 (1148203119) 120
LocalHost LocalService RemoteHost RemoteService RemoteInstance
10.4.5.212 wzm8
10.4.5.210 wzm9 dl
PrimaryFile PrimaryPg PrimaryLSN StandByFile StandByPg StandByLSN
S0000007.LOG 0 0x000002AF800080AB3C46C55E0 S0000007.LOG 0 0x000002AF800080AB3

转载自http://blog.sina.com.cn/s/blog_60359c5b0100hj0e.html