DB2 operation connection, backup, recovery DB2

DB2 default need to switch to db2inst1 this default user to perform the action

[Email protected] instance]# Su-db2inst1

[Email protected] ~]$ Db2start

[Email protected] ~]$ DB2 force application All

[Email protected] ~]$ Db2stop

[Email protected] db2]$ db2stop force

Show All instances
[Email protected] ~]$ db2ilist

Displays the current instance
[[Email protected] ~]$ DB2 get instance

Delete an instance (note: You need to switch to root user rights)
[Email protected] ~]# cd/opt/ibm/db2/v9.7/instance
[Email protected] instance]# pwd
/opt/ibm/db2/v9.7/instance
[Email protected] instance]#/db2idrop db2inst1
Dbi1070i program Db2idrop completed successfully.

List which databases are in the current instance
[Email protected] instance]# Su-db2inst1
[[Email protected] ~]$ DB2 list DB directory

Create a database
[Email protected] instance]# Su-db2inst1
[Email protected] ~]$ DB2 CREATE DATABASE test

Connecting to a database
[[Email protected] ~]$ DB2 connect to test

Note: In case of password format [[email protected] ~]$ DB2 connect to test user username using password
DB2 Connect to <database> user <username> using <password>

List all active databases in the current instance
[Email protected] ~]# Su-db2inst1
[Email protected] ~]$ DB2 list active databases

View the space of a table
[Email protected] ~]$ DB2 list tablespaces [show detail]

List all user tables in the database
[[Email protected] ~]$ DB2 connect to test
[Email protected] ~]$ DB2 list tables

Create a table
[Email protected] ~]$ DB2 "CREATE TABLE student (ID int,fname varchar (), age int)"

Adding data information to the table student
[[Email protected] ~]$ DB2 "INSERT into student values (1, ' Tom ', 22)"

Show Table student All the information
[[Email protected] ~]$ DB2 "SELECT * FROM Student"

Update data
[[Email protected] ~]$ DB2 "update student set age=22 where fname= ' Sunrier '"

View Table Student Structure
[Email protected] ~]$ DB2 describe table student

[Email protected] ~]$ DB2 "describe select * from student"

Modify the field type of a table (such as the fname field in table people change varchar (30) to varchar (28))
[Email protected] ~]$ DB2 describe table people

[[Email protected] ~]$ DB2 "ALTER TABLE people ALTER COLUMN fname SET data type varchar (28)"

Add a field to a table (such as adding a Memo information field to a table people notes; Adding a number segment to table people score)
Format: DB2 "ALTER TABLE <tablename> add <columnname> <datatype>"

[[Email protected] ~]$ DB2 "ALTER TABLE people add notes varchar (100)"

Shows which applications are currently connected to the database
[Email protected] ~]# Su-db2inst1
[Email protected] db2]$ DB2 list application

Exporting data from a table

Export in del format
DB2 "Export to Teacher.txt of del select * from teacher"
DB2 "Export to Teacher_bak.txt of Del Modified by coldel| SELECT * FROM Teacher "
[[email protected] ~]$ ls
Db2inst1 sqllib sunrier Teacher.sql test.0.db2inst1.node0000.catn0000.20120817103306.001
[Email protected] ~]$ DB2 "Export to Teacher.txt of del select * from teacher"
SQL3104N The Export utility is beginning to export data to file
"Teacher.txt".

Backing up a database (such as preventing table errors)
Format: DB2 backup DB <database name> [to <dir name>]
[[Email protected] ~]$ DB2 backup DB test
sql3105n The Export utility has finished exporting "2" rows.

Recovering a database (such as deleting a table and recovering it by removing the backup file before deleting it)
Format: DB2 restore DB <database name> [from <dir name>]
Database name: Indicates the name of the restored databases

From <dir Name>: Indicates which directory path to recover from, as optional, default in current directory
[[Email protected] ~]$ DB2 connect to test
Note: If you want to change the restored database to a new database name, the format is as follows
DB2 Restore DB <database name> [from <dir name> to <new database name>]
Example: DB2 restore DB Test from/home/db2inst1/sunrier into TestDB
or DB2 restore DB test from "/home/db2inst1/sunrier" to TestDB

. View the history of test database backups
Format: DB2 list History backup all for <database name>
[[Email protected] ~]$ DB2 list history backup all for test

View error code information
[Email protected] ~]$ DB2? 22003
SQLSTATE 22003:a numeric value is an out of range.

======================================back up ========================================
DB2 List DB Directory

1. Offline Full backup
DB2 Force application All
1), first ensure that no users use DB2:
DB2 list applications for DB sample
2), shut down the database and reboot to disconnect all connections:
Db2stop Force
Db2start
3), execute Backup command: (use TSM as backup media)
DB2 backup DB Sample Use TSM
The backup succeeds and a timestamp is returned.
4), check the backup success:
DB2 List History Backup all for sample can see more records of this backup.
The DB2ADUTL query command can also see the return value.

5. Restore the database

DB2 Restore DB GLANCE From/var/wenbin/backup

When you need to connect to the database after the recovery error

Last login:wed Jan 30 02:23:23 2013

[Email protected] ~]# Su-db2inst1

[[Email protected] ~]$ DB2 connect to sample

sql1117n A connection to or activation of the database "SAMPLE" cannot be made

Because of Roll-forward PENDING. sqlstate=57019

# # #这个提示是说需要前滚期间的日志才能激活数据库

Execute the following statement:

DB2 Rollforward DB GLANCE to end of logs and stop

DB2 operation connection, backup, recovery DB2