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.
(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 (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.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
三。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
添加新评论2 条评论
2013-08-18 20:04
2013-06-30 22:36