DB2相關資料庫指令
1.資料庫執行個體的啟動
首先要啟動資料庫的執行個體,即切換到db2inst1使用者(注:db2inst1使用者為目前資料庫的執行個體),然後執行db2start啟動資料庫的執行個體
[root@localhost instance]# su - db2inst1
[db2inst1@localhost ~]$ db2start
SQL1063N DB2START processing was successful.
[db2inst1@localhost ~]$
2.資料庫執行個體的關閉
首先在db2inst1使用者下強制關閉執行個體上的所有應用程式,然後再關閉資料庫執行個體
[db2inst1@localhost ~]$ db2 force application all
DB20000I The FORCE APPLICATION command completed successfully.
DB21024I This command is asynchronous and may not be effective immediately.
[db2inst1@localhost ~]$ db2stop
SQL1064N DB2STOP processing was successful.
強制停止
[db2inst1@localhost DB2]$ db2stop force
[db2inst1@localhost DB2]$
3.顯示所有的執行個體
[db2inst1@localhost ~]$ db2ilist
db2inst1
4.顯示目前的執行個體
[db2inst1@localhost ~]$ db2 get instance
The current database manager instance is: db2inst1
[db2inst1@localhost ~]$
5.删除一個執行個體(注:需切換到root使用者權限下)
[root@localhost ~]# cd /opt/ibm/db2/V9.7/instance
[root@localhost instance]# pwd
/opt/ibm/db2/V9.7/instance
[root@localhost instance]# ./db2idrop db2inst1
DBI1070I Program db2idrop completed successfully.
[root@localhost instance]#
6.列出目前執行個體中有哪些資料庫
[db2inst1@localhost ~]$ db2 list db directory
SQL1031N The database directory cannot be found on the indicated file system.
SQLSTATE=58031
注:上面資訊說明執行個體中沒有資料庫
7.建立資料庫
[db2inst1@localhost ~]$ db2 create database test
SQL1032N No start database manager command was issued. SQLSTATE=57019
SQL5043N Support for one or more communications protocols failed to
start successfully. However, core database manager functionality started
successfully.
DB20000I The CREATE DATABASE command completed successfully.
System Database Directory
Number of entries in the directory = 1
Database 1 entry:
Database alias = TEST
Database name = TEST
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 =
注:上面通過建立一個test的資料庫,并說明了建立資料庫時,要先啟動資料庫,然後建立資料庫test成功後并列出了目前執行個體中的所有資料庫,隻存在資料庫test
使用UTF-8編碼
db2 create database test on '/home/db2inst1' using codeset UTF-8 territory CN
on '/home/db2inst1' 表示資料庫路徑
一般情況下'/home/db2inst1'為預設資料庫路徑
8.連接配接資料庫
[db2inst1@localhost ~]$ db2 connect to test
Database Connection Information
Database server = DB2/LINUX 9.7.1
SQL authorization ID = DB2INST1
Local database alias = TEST
注:用密碼情況下格式[db2inst1@localhost ~]$ db2 connect to test user username using password
db2 connect to user using
9.列出目前執行個體中所有激活的資料庫
[root@localhost ~]# su - db2inst1
[db2inst1@localhost ~]$ db2 list active databases
[db2inst1@localhost ~]$ db2start
SQL1026N The database manager is already active.
SQL1611W No data was returned by Database System Monitor.
[db2inst1@localhost ~]$ db2 list active databases
Active Databases
Database name = TEST
Applications connected currently = 1
Database path = /home/db2inst1/db2inst1/NODE0000/SQL00001/
[db2inst1@localhost ~]$
注:可以看出檢視目前激活的資料庫為已經啟動後連接配接的資料庫
10.檢視表的空間
[db2inst1@localhost ~]$ db2 list tablespaces [ show detail ]
Tablespaces for Current Database
Tablespace ID = 0
Name = SYSCATSPACE
Type = Database managed space
Contents = All permanent data. Regular table space.
State = 0x0000
Detailed explanation:
Normal
Tablespace ID = 1
Name = TEMPSPACE1
Type = System managed space
Contents = System Temporary data
Tablespace ID = 2
Name = USERSPACE1
Contents = All permanent data. Large table space.
注: show detail為可選項,顯示更詳細資訊
或者 db2pd -tablespaces -db 資料庫名
[db2inst1@localhost ~]$ db2pd -tablespaces -db test
Database Partition 0 -- Database TEST -- Active -- Up 0 days 00:03:11
Tablespace Configuration:
Address Id Type Content PageSz ExtentSz Auto Prefetch BufID BufIDDisk FSC NumCntrs MaxStripe LastConsecPg Name
0x9DC2A060 0 DMS Regular 4096 4 Yes 4 1 1 Off 1 0 3 SYSCATSPACE
0x9DC2B4B0 1 SMS SysTmp 4096 32 Yes 32 1 1 On 1 0 31 TEMPSPACE1
0x9DC30940 2 DMS Large 4096 32 Yes 32 1 1 Off 1 0 31 USERSPACE1
Tablespace Statistics:
Address Id TotalPgs UsablePgs UsedPgs PndFreePgs FreePgs
HWM Max HWM State MinRecTime NQuiescers PathsDropped
0x9DC2A060 0 24576 24572 18712 0 5860
18712 18712 0x00000000 0 0 No
0x9DC2B4B0 1 1 1 1 0 0
0 0 0x00000000 0 0 No
0x9DC30940 2 8192 8160 96 0 8064
96 96 0x00000000 0 0 No
Tablespace Autoresize Statistics:
Address Id AS AR InitSize IncSize IIP MaxSize LastResize LRF
0x9DC2A060 0 Yes Yes 33554432 -1 No None None No
0x9DC2B4B0 1 Yes No 0 0 No 0 None No
0x9DC30940 2 Yes Yes 33554432 -1 No None None No
Containers:
Address TspId ContainNum Type TotalPgs UseablePgs PathID StripeSet Container
0x9B218F00 0 0 File 24576 24572 0
0 /home/db2inst1/db2inst1/NODE0000/TEST/T0000000/C0000000.CAT
0x9B219120 1 0 Path 1 1 0
0 /home/db2inst1/db2inst1/NODE0000/TEST/T0000001/C0000000.TMP
0x9B219390 2 0 File 8192 8160 0
0 /home/db2inst1/db2inst1/NODE0000/TEST/T0000002/C0000000.LRG
11.列出資料庫中所有使用者表
[db2inst1@localhost ~]$ db2 list tables
Table/View Schema Type Creation time
------------------------------- --------------- ----- --------------------------
0 record(s) selected.
注:上面資訊說明資料庫test中還沒有表
12.在資料庫test中建立表student
[db2inst1@localhost ~]$ db2 "create table student (id int,fname varchar(30),age int)"
DB20000I The SQL command completed successfully.
[db2inst1@localhost ~]$ db2 list tables
STUDENT DB2INST1 T 2012-08-06-14.38.33.456768
1 record(s) selected.
13.向表student中添加資料資訊
[db2inst1@localhost ~]$ db2 "insert into student values (1,'Tom',22)"
[db2inst1@localhost ~]$ db2 "insert into student values (2,'Jack',21)"
[db2inst1@localhost ~]$ db2 "insert into student values (3,'Sunrier',25)"
14.顯示表student所有的資訊
[db2inst1@localhost ~]$ db2 "select * from student"
ID FNAME AGE
----------- ------------------------------ -----------
1 Tom 22
2 Jack 21
3 Sunrier 25
3 record(s) selected.
15.更改表student中的資料(如将Sunrier的年齡改為22)
[db2inst1@localhost ~]$ db2 "update student set age=22 where fname='Sunrier'"
3 Sunrier 22
16.檢視表student結構
[db2inst1@localhost ~]$ db2 describe table student
Data type Column
Column name schema Data type name Length Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
ID SYSIBM INTEGER 4 0 Yes
FNAME SYSIBM VARCHAR 30 0 Yes
AGE SYSIBM INTEGER 4 0 Yes
或
[db2inst1@localhost ~]$ db2 "describe select * from student"
Column Information
Number of columns: 3
SQL type Type length Column name Name length
-------------------- ----------- ------------------------------ -----------
497 INTEGER 4 ID 2
449 VARCHAR 30 FNAME 5
497 INTEGER 4 AGE 3
[db2inst1@localhost ~]$ db2 "describe select * from db2inst1.student"
17.建立一個新表(如people)與資料庫中某個表(如student)結構相同
STUDENT DB2INST1 T 2012-08-06-15.26.17.189538
[db2inst1@localhost ~]$ db2 describe table student
[db2inst1@localhost ~]$ db2 "select * from student"
[db2inst1@localhost ~]$ db2 create table people like student
PEOPLE DB2INST1 T 2012-08-16-15.13.49.396370
2 record(s) selected.
[db2inst1@localhost ~]$ db2 describe table people
18.兩個結構相同的表,将原來資料庫中某個表(如student)的資料導入與它相同結構的新表(如people)中
[db2inst1@localhost ~]$ db2 "select * from people"
[db2inst1@localhost ~]$ db2 "insert into people select * from student"
[db2inst1@localhost ~]$ db2 "select * from people"
19.修改一個表的字段類型(如表people中的fname字段把varchar(30)改為varchar(28))
[db2inst1@localhost ~]$ db2 describe table people
[db2inst1@localhost ~]$ db2 "alter table people alter column fname set data type varchar(28)"
FNAME SYSIBM VARCHAR 28 0 Yes
ID FNAME AGE
----------- ---------------------------- -----------
1 Tom 22
2 Jack 21
3 Sunrier 25
格式:db2 "alter table alter column set data type "
tablename:表名
columnname:字段名
datatype:字段類型
注:
一般更改字段類型是有操作限制的. 将字段改為比之前類型長度大的可以;如果要改小,必須先drop掉原來的column,然後再重新添加.
雖然我上面的執行成功了,可能是因為我用的官方免費版本的,如果遇到把長度大的改為小的無法執行,先用drop,再重新添加
20.向一個表添加字段(如向表people中添加備注資訊字段notes;向表people中添加分數字段score)
格式:db2 "alter table add "
[db2inst1@localhost ~]$ db2 "alter table people add notes varchar(100)"
FNAME SYSIBM VARCHAR 36 0 Yes
NOTES SYSIBM VARCHAR 100 0 Yes
4 record(s) selected.
ID
FNAME AGE
NOTES
----------- ------------------------------------ -----------
----------------------------------------------------------------------------------------------------
1 Tom 22
-
2 Jack 21
3 Sunrier 25
[db2inst1@localhost ~]$ db2 "alter table people add score integer"
SCORE SYSIBM INTEGER 4 0 Yes
5 record(s) selected.
ID FNAME AGE NOTES SCORE
----------- ----------------- ----------- --------------
1 Tom 22
-
-
2 Jack 21
3 Sunrier 25
21.删除表中的某個字段(如删除表people中的字段score)
格式:db2 "alter table drop column "
[db2inst1@localhost ~]$ db2 "alter table people drop column score"
ID FNAME AGE
----------- -------------- ----------- --------------------
1 Tom 22
2 Jack 21
3 Sunrier 25
注:如果drop掉字段之後,可能會導緻表查詢/插入操作不能執行,則需要執行一下reorg指令,優化資料結構,
格式如db2 reorg table
[db2inst1@localhost ~]$ db2 reorg table people
DB20000I The REORG command completed successfully.
[db2inst1@localhost ~]$
22.給表中添加帶預設值的字段(如向表people中添加分數字段score,預設設定為90)
格式:db2 "alter table add column not null with default "
[db2inst1@localhost ~]$ db2 "alter table people add column score interger not null with default 90"
[db2inst1@localhost ~]$ db2 describe table people
SCORE SYSIBM INTEGER 4 0 No
NOTES
SCORE
-----------
-
90
例1.向表people中添加位址字段address預設設定為shanghai
db2 "alter table people add column address varchar(30) not null with default 'ShangHai'"
例2.将表people中位址字段address預設設定改為目前時間
db2 "alter table people alter column address set default current date"
格式:db2 "alter table alter column set default "
23.列出資料庫中使用者表
[db2inst1@localhost ~]$ db2 list tables for user
24.列出資料庫中所有系統表
[db2inst1@localhost ~]$ db2 list tables for system
ATTRIBUTES SYSCAT V 2012-08-06-14.20.49.182036
AUDITPOLICIES SYSCAT V 2012-08-06-14.20.49.237474
AUDITUSE SYSCAT V 2012-08-06-14.20.49.243535
BUFFERPOOLDBPARTITIONS SYSCAT V 2012-08-06-14.20.49.264336
BUFFERPOOLNODES SYSCAT V 2012-08-06-14.20.49.277662
BUFFERPOOLS SYSCAT V 2012-08-06-14.20.49.281563
CASTFUNCTIONS SYSCAT V 2012-08-06-14.20.49.309007
CHECKS SYSCAT V 2012-08-06-14.20.49.314078
...............................................................................
ROUTINES SYSSTAT V 2012-08-06-14.20.52.502569
TABLES SYSSTAT V 2012-08-06-14.20.52.510027
HMON_ATM_INFO SYSTOOLS T 2012-08-06-14.35.41.029633
HMON_COLLECTION SYSTOOLS T 2012-08-06-14.35.41.208925
POLICY SYSTOOLS T 2012-08-06-14.35.40.156347
396 record(s) selected.
25.列出資料庫中所有表
[db2inst1@localhost ~]$ db2 list tables for all
ATTRIBUTES SYSCAT V 2012-08-06-15.21.20.819408
AUDITPOLICIES SYSCAT V 2012-08-06-15.21.20.886143
AUDITUSE SYSCAT V 2012-08-06-15.21.20.911042
BUFFERPOOLDBPARTITIONS SYSCAT V 2012-08-06-15.21.20.936300
BUFFERPOOLNODES SYSCAT V 2012-08-06-15.21.20.957929
BUFFERPOOLS SYSCAT V 2012-08-06-15.21.20.978954
CASTFUNCTIONS SYSCAT V 2012-08-06-15.21.21.011517
CHECKS SYSCAT V 2012-08-06-15.21.21.036428
COLAUTH SYSCAT V 2012-08-06-15.21.21.061589
COLCHECKS SYSCAT V 2012-08-06-15.21.21.094771
COLDIST SYSCAT V 2012-08-06-15.21.21.114126
................................................................................
FUNCTIONS SYSSTAT V 2012-08-06-15.21.25.272699
INDEXES SYSSTAT V 2012-08-06-15.21.25.289851
ROUTINES SYSSTAT V 2012-08-06-15.21.25.315173
TABLES SYSSTAT V 2012-08-06-15.21.25.320565
HMON_ATM_INFO SYSTOOLS T 2012-08-06-15.30.39.352789
HMON_COLLECTION SYSTOOLS T 2012-08-06-15.30.39.498061
POLICY SYSTOOLS T 2012-08-06-15.30.38.749121
397 record(s) selected.
26.列出資料庫中特定使用者表
[db2inst1@localhost ~]$ db2 list tables for schema db2inst1
[db2inst1@localhost ~]$ db2 list tables for schema db2inst2
格式: db2 list tables for schema
注:符号表示必選項
27.删除表中的資料 :
[db2inst1@localhost ~]$ db2 delete from student where id=3
28.删除一個資料庫中的某個表
[db2inst1@localhost ~]$ db2 drop table student
29.删除一個資料庫test
[db2inst1@localhost ~]$ db2 drop db test
SQL1035N The database is currently in use. SQLSTATE=57019
SQL1025N The database manager was not stopped because databases are still active.
[db2inst1@localhost ~]$ db2 connect reset
DB20000I The DROP DATABASE command completed successfully.
SQL1057W The system database directory is empty. SQLSTATE=01606
[db2inst1@localhost ~]$
注:删除資料庫首先要斷開資料庫的連接配接
30.顯示目前資料庫連接配接有哪些應用程式
[db2inst1@localhost DB2]$ db2 list application
[db2inst1@localhost DB2]$ db2 connect to test
[db2inst1@localhost DB2]$ db2 list application
Auth Id Application Appl. Application Id DB # of
Name Handle Name Agents
-------- -------------- ---------- -------------------------------------------------------------- -------- -----
DB2INST1 db2bp 35 *LOCAL.db2inst1.120807014245 TEST 1
[db2inst1@localhost DB2]$
31.檢視DB2全部受支援的系統資料庫變量清單
[db2inst1@localhost DB2]$ db2set -lr
DB2_OVERRIDE_BPF
DB2_PARALLEL_IO
DB2ACCOUNT
DB2ADMINSERVER
DB2BQTIME
DB2BQTRY
.........................................
DB2TCP_CLIENT_KEEPALIVE_TIMEOUT
DB2_PMODEL_SETTINGS
DB2_PMAP_COMPATIBILITY
DB2_HADR_ROS
DB2_STANDBY_ISO
32.更改DB2 UDB系統資料庫變量的值
db2set registry_variable_name=new_value
[db2inst1@localhost ~]$ db2set DB2COMM=TCPIP
33.檢視在伺服器上已經設定的所有DB2概要檔案系統資料庫
[db2inst1@localhost DB2]$ db2set -all
[i] DB2PROCESSORS=0
[i] DB2COMM=TCPIP
[i] DB2AUTOSTART=YES
[g] DB2SYSTEM=localhost.localdomain
[g] DB2INSTDEF=db2inst1
[g] DB2ADMINSERVER=db2dasusr1
34.導出表中的資料
以DEL格式導出
db2 "export to teacher.txt of del select * from teacher"
db2 "export to teacher_bak.txt of del modified by coldel| select * from teacher"
[db2inst1@localhost ~]$ ls
db2inst1 sqllib Sunrier teacher.sql TEST.0.db2inst1.NODE0000.CATN0000.20120817103306.001
[db2inst1@localhost ~]$ db2 "export to teacher.txt of del select * from teacher"
SQL3104N The Export utility is beginning to export data to file
"teacher.txt".
SQL3105N The Export utility has finished exporting "2" rows.
Number of rows exported: 2
[db2inst1@localhost ~]$ ls
db2inst1 sqllib Sunrier teacher.sql teacher.txt TEST.0.db2inst1.NODE0000.CATN0000.20120817103306.001
[db2inst1@localhost ~]$ cat teacher.txt
1,"Lory","上海徐彙中學",19780806
2,"Sunrier","田林中學",19880627
字段之間預設分隔符号為逗号,下面使用'|'分割
[db2inst1@localhost ~]$ db2 "export to teacher_bak.txt of del modified by coldel| select * from teacher"
"teacher_bak.txt".
db2inst1 sqllib Sunrier teacher_bak.txt teacher.sql teacher.txt TEST.0.db2inst1.NODE0000.CATN0000.20120817103306.001
[db2inst1@localhost ~]$ cat teacher_bak.txt
1|"Lory"|"上海徐彙中學"|19780806
2|"Sunrier"|"田林中學"|19880627
以IXF格式導出
[db2inst1@localhost ~]$ pwd
/home/db2inst1
[db2inst1@localhost ~]$ ls /home/db2inst1/Sunrier
TEST.0.db2inst1.NODE0000.CATN0000.20120817150317.001
[db2inst1@localhost ~]$ db2 "export to /home/db2inst1/Sunrier/teacher.ixf of ixf select * from teacher"
"/home/db2inst1/Sunrier/teacher.ixf".
SQL3105N The Export utility has finished exporting "4" rows.
Number of rows exported: 4
[db2inst1@localhost ~]$ ls /home/db2inst1/Sunrier
teacher.ixf TEST.0.db2inst1.NODE0000.CATN0000.20120817150317.001
[db2inst1@localhost ~]$
如需要導出記錄過程的message
[db2inst1@localhost ~]$ db2 "export to
/home/db2inst1/Sunrier/teacher_bak.ixf of ixf messages
/home/db2inst1/Sunrier/teacher.msg select * from teacher"
Number of rows exported: 4
teacher_bak.ixf teacher.ixf teacher.msg TEST.0.db2inst1.NODE0000.CATN0000.20120817150317.001
[db2inst1@localhost ~]$ cat /home/db2inst1/Sunrier/teacher.msg
"/home/db2inst1/Sunrier/teacher_bak.ixf".
35.導入資料到一個表中
db2 "import from teacher.txt of del insert into teacher"
db2 "import from teacher_bak.txt of del modified by coldel| insert into teacher"
[db2inst1@localhost ~]$ cat teacher.txt
3,"Jerry","上海徐彙中學",19710306
4,"Tim","田林中學",19820627
[db2inst1@localhost ~]$ db2 "select * from teacher"
ID FNAME ADDRESS BIRTH
----------- -------------------- -------------------------------------------------------------------------------- ----------
1 Lory 上海徐彙中學 1978-08-06
2 Sunrier 田林中學 1988-06-27
[db2inst1@localhost ~]$ db2 "import from teacher.txt of del insert into teacher"
SQL3109N The utility is beginning to load data from file "teacher.txt".
SQL3110N The utility has completed processing. "2" rows were read from the
input file.
SQL3221W ...Begin COMMIT WORK. Input Record Count = "2".
SQL3222W ...COMMIT of any database changes was successful.
SQL3149N "2" rows were processed from the input file. "2" rows were
successfully inserted into the table. "0" rows were rejected.
Number of rows read = 2
Number of rows skipped = 0
Number of rows inserted = 2
Number of rows updated = 0
Number of rows rejected = 0
Number of rows committed = 2
[db2inst1@localhost ~]$ db2 "select * from teacher"
3 Jerry 上海徐彙中學 1971-03-06
4 Tim 田林中學 1982-06-27
36.利用腳本建立表
格式:db2 -tvf scriptName.sql
teacher.sql為以下内容
----建立表teacher
create table teacher
(
id integer not null ,
fname varchar(20) not null,
address varchar(80) default '上海',
birth date,
primary key(id)
);
--建表結束
--以下為插入資料字段
insert into teacher values(1,'Lory','上海徐彙中學','1978-08-06');
insert into teacher values(2,'Sunrier','田林中學','1988-06-27');
[db2inst1@localhost ~]$ db2 list tables
[db2inst1@localhost ~]$ db2 -tvf teacher.sql
create table teacher ( id integer not null , fname varchar(20) not null,
address varchar(80) default '上海', birth date, primary key(id) )
insert into teacher values(1,'Lory','上海徐彙中學','1978-08-06')
insert into teacher values(2,'Sunrier','田林中學','1988-06-27')
[db2inst1@localhost ~]$ db2 describe table teacher
ID SYSIBM INTEGER 4 0 No
FNAME SYSIBM VARCHAR 20 0 No
ADDRESS SYSIBM VARCHAR 80 0 Yes
BIRTH SYSIBM DATE 4 0 Yes
37.備份資料庫(如防止表誤操作)
格式:db2 backup db [ to ]
database name:表示資料庫
to :表示為備份到的目錄路徑,為可選項,預設在目前目錄下
db2inst1 sqllib teacher.sql
[db2inst1@localhost ~]$ db2 list db directory
[db2inst1@localhost ~]$ db2 backup db test
Backup successful. The timestamp for this backup image is : 20120817103306
db2inst1 sqllib teacher.sql TEST.0.db2inst1.NODE0000.CATN0000.20120817103306.001
注:TEST.0.db2inst1.NODE0000.CATN0000.20120817103306.001即為備份的資料庫節點檔案;
執行備份指令時,如果出現無法執行,則先斷開資料庫的連接配接再執行備份指令.
我使用的免費版本的DB2資料庫測試時,從上面可以看出沒有斷開也可以執行,但執行完,發現資料庫
處于斷開狀态了
如: db2 force application all
強制關閉執行個體上的所有應用程式
[db2inst1@localhost ~]$ db2 backup db test to /home/db2inst1/Sunrier
Backup successful. The timestamp for this backup image is : 20120817150317
[db2inst1@localhost ~]$ cd Sunrier/
[db2inst1@localhost Sunrier]$ ls
[db2inst1@localhost Sunrier]$
說明:上面的方法為脫機備份(也稱為離線備份或者冷備份),此方法必須斷開所有與資料庫連接配接的應用後才能進行,備份時資料庫不能提供給使用者使用.
38.恢複資料庫(如将一個表删除後,通過删除前的備份檔案恢複)
格式:db2 restore db [ from ]
database name:表示恢複的資料庫名
from :表示為從哪個目錄路徑下恢複,為可選項,預設在目前目錄下
TEACHER DB2INST1 T 2012-08-17-10.18.18.245263
ID FNAME ADDRESS BIRTH
----------- -------------------- -------------------------------------------- ----------
1 Lory 上海徐彙中學 1978-08-06
2 Sunrier 田林中學 1988-06-27
3 Jerry 上海徐彙中學 1971-03-06
4 Tim 田林中學 1982-06-27
[db2inst1@localhost ~]$ db2 drop table teacher
[db2inst1@localhost ~]$ db2 connect reset
DB20000I The SQL command completed successfully.
[db2inst1@localhost Sunrier]$ ls /home/db2inst1/Sunrier
[db2inst1@localhost ~]$ db2 restore db test from /home/db2inst1/Sunrier
SQL2539W Warning! Restoring to an existing database that is the same as the
backup image database. The database files will be deleted.
Do you want to continue ? (y/n) y
DB20000I The RESTORE DATABASE command completed successfully.
SQL1024N A database connection does not exist. SQLSTATE=08003
注:如果想把恢複的資料庫更改為新的資料庫名,則格式如下
db2 restore db [ from into ]
例:db2 restore db test from /home/db2inst1/Sunrier into testdb
或者db2 restore db test from "/home/db2inst1/Sunrier" into testdb
39.db2move指令的使用
db2move是一個內建式的資料移動工具,它具有導入(import),導出(export),裝入(load)三種操作方法.
db2move導出的資料檔案格式是IXF(Integration Exchange Format)內建交換格式.
格式:db2move [ export -tc ] -u -p
參數: -tc 建立表的使用者名
-tn 使用者的表名
-sn 模式名,即導出該模式下的所有表
檢視db2move指令幫助db2move -help
導出test資料庫中的全部資料
db2inst1 Sunrier teacher.sql TEST.0.db2inst1.NODE0000.CATN0000.20120817103306.001
sqllib teacher_bak.txt teacher.txt
[db2inst1@localhost ~]$ db2move test export -u db2inst1 -p Sunrier
Application code page not determined, using ANSI codepage 1208
***** DB2MOVE *****
Action: EXPORT
Start time: Fri Aug 17 15:58:34 2012
Connecting to database TEST ... successful! Server : DB2 Common Server V9.7.1
Binding package automatically ... /home/db2inst1/sqllib/bnd/db2common.bnd ... successful!
Binding package automatically ... /home/db2inst1/sqllib/bnd/db2move.bnd ... successful!
EXPORT: 136 rows from table "SYSTOOLS"."HMON_ATM_INFO"
EXPORT: 0 rows from table "SYSTOOLS"."HMON_COLLECTION"
EXPORT: 3 rows from table "DB2INST1"."PEOPLE"
EXPORT: 5 rows from table "SYSTOOLS"."POLICY"
EXPORT: 3 rows from table "DB2INST1"."STUDENT"
EXPORT: 4 rows from table "DB2INST1"."TEACHER"
Disconnecting from database ... successful!
End time: Fri Aug 17 15:58:35 2012
db2inst1 sqllib tab1.msg tab3.ixf tab4.ixf tab5.msg
teacher_bak.txt TEST.0.db2inst1.NODE0000.CATN0000.20120817103306.001
db2move.lst Sunrier tab2.ixf tab3.msg tab4.msg tab6.ixf teacher.sql
EXPORT.out tab1.ixf tab2.msg tab4a.001.lob tab5.ixf tab6.msg teacher.txt
注:
執行上面指令後會把資料庫test中全部資料提取到目前目錄(/home/db2inst1),每個表的内容都存儲在一個.ixf檔案中,
每個.ixf檔案都有一個與之相對應的.msg檔案,.msg檔案是描述從表中導出資料時的資訊.例外還有兩個檔案,db2move.lst用來
記錄.ixf檔案,.msg檔案與表一一對應.EXPORT.out記錄的是導出資料時的螢幕輸出.
導出test資料庫中的teacher表中的資訊
db2inst1 Sunrier teacher.sql TEST.0.db2inst1.NODE0000.CATN0000.20120817103306.001
sqllib teacher_bak.txt teacher.txt
[db2inst1@localhost ~]$ db2move test export -tn teacher -u db2inst1 -p Sunrier
Start time: Fri Aug 17 16:33:24 2012
All table names matching: TEACHER;
Connecting to database TEST ... successful! Server : DB2 Common Server V9.7.1
EXPORT: 4 rows from table "DB2INST1"."TEACHER"
End time: Fri Aug 17 16:33:24 2012
db2inst1 EXPORT.out Sunrier tab1.msg teacher.sql TEST.0.db2inst1.NODE0000.CATN0000.20120817103306.001
db2move.lst sqllib tab1.ixf teacher_bak.txt teacher.txt
40.檢視test資料庫備份的曆史記錄
格式:db2 list history backup all for
[db2inst1@localhost ~]$ db2 list history backup all for test
List History File for test
Number of matching file entries = 4
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
B D 20120817103306001 F D S0000000.LOG S0000000.LOG
----------------------------------------------------------------------------
Contains 3 tablespace(s):
00001 SYSCATSPACE
00002 USERSPACE1
00003 SYSTOOLSPACE
Comment: DB2 BACKUP TEST OFFLINE
Start Time: 20120817103306
End Time: 20120817103315
Status: A
EID: 4 Location: /home/db2inst1
B D 20120817150248000 F S0000000.LOG
Start Time: 20120817150248
End Time: 20120817150249
EID: 5 Location:
B D 20120817150317001 F D S0000000.LOG S0000000.LOG
Start Time: 20120817150317
End Time: 20120817150323
EID: 6 Location: /home/db2inst1/Sunrier
B D 20120817150825001 F D S0000000.LOG S0000000.LOG
Start Time: 20120817150825
End Time: 20120817150832
Status: I
EID: 7 Location: /home/db2inst1/Sunrier
R D 20120817151620001 F S0000000.LOG S0000000.LOG 20120817150317
Comment: RESTORE TEST NO RF
Start Time: 20120817151620
End Time: 20120817151629
EID: 8 Location:
41.讀資料庫管理程式配置
db2 get dbm cfg
42.寫資料庫管理程式配置
db2 update dbm cfg using 參數名 參數值
43.檢視資料庫的配置
db2 connect to user using
db2 get db cfg [ for ]
database:資料庫名
username:使用者名
password:表示密碼
44.設定資料庫的配置
db2 update db cfg for using 參數名 參數值
45.添加DB2服務端口50000
切換到root使用者下su - root
[root@localhost etc]# vi /etc/services
在/etc/services檔案中加入db2inst1 50000/tcp
/etc/services内容格式:
# service-name port/protocol [aliases ...] [# comment]
46.斷開與資料庫的連接配接
db2 connect reset 或 db2 terminate
db2 disconnect
47.檢視指令幫助
[db2inst1@localhost ~]$ db2 ? db2start
{START DATABASE MANAGER | DB2START} [REMOTE [INSTANCE] instance-name
{ADMINNODE node-name | HOSTNAME hostname} USER username USING password]
[ADMIN MODE {USER username | GROUP groupname }] [PROFILE profile]
[DBPARTITIONNUM db-partition-number] [ADD DBPARTITIONNUM HOSTNAME hostname
PORT logical-port [COMPUTER computer-name] [USER username] [PASSWORD password]
[NETNAME netname] [LIKE DBPARTITIONNUM db-partition-number |
WITHOUT TABLESPACES]] | STANDALONE | RESTART [HOSTNAME hostname]
[PORT logical-port] [COMPUTER computer-name] [USER username] [PASSWORD password]
[NETNAME netname] ] ]
NOTE: From the operating system prompt, prefix commands with 'db2'.
Special characters MAY require an escape sequence (\), for example:
db2 \? change database
db2 ? change database xxx comment with \"text\"
格式:db2 ?
48.檢視錯誤碼資訊
[db2inst1@localhost ~]$ db2 ? 22003
SQLSTATE 22003: A numeric value is out of range.
49.待定,工作過程中遇到相關問題再補充......
![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)