建立一個新的資料庫

[db2inst1@xttdb ~]$ db2 "create database test02 using codeset utf-8 territor

y CN"

DB20000I The CREATE DATABASE command completed successfully.

檢視資料庫資訊

[root@xttdb expc]# db2 list database directory

System Database Directory

Number of entries in the directory = 2

Database 1 entry:

Database alias = TEST01

Database name = TEST01

Local database directory = /home/db2inst1

Database release level = f.00

Comment =

Directory entry type = Indirect

Catalog database partition number = 0

Alternate server hostname =

Alternate server port number =

Database 2 entry:

Database alias = TEST02

Database name = TEST02

Local database directory = /home/db2inst1

Database release level = f.00

Comment =

Directory entry type = Indirect

Catalog database partition number = 0

Alternate server hostname =

Alternate server port number =

連線資料庫

[db2inst1@xttdb ~]$ db2 connect to test02

Database Connection Information

Database server = DB2/LINUX 10.1.0

SQL authorization ID = DB2INST1

Local database alias = TEST02

檢視所有連線資訊

[db2inst1@xttdb ~]$ db2 list application

Auth Id Application Appl. Application Id DB # of

Name Handle Name Agents

-------- -------------- ---------- -------------------------------------------------------------- -------- -----

ROOT db2bp 98 *LOCAL.db2inst1.150119163417 TEST02 1

DB2INST1 db2bp 108 *LOCAL.db2inst1.150119163649 TEST02 1

關閉db2資料庫

在有連線的時候直接關閉db2會提示有其他連線存在

[db2inst1@xttdb ~]$ db2stop

SQL1025N The database manager was not stopped because databases are still active.

斷開所有的連線

[db2inst1@xttdb ~]$ db2 force application all

DB20000I The FORCE APPLICATION command completed successfully.

DB21024I This command is asynchronous and may not be effective immediately.

再次關閉資料庫即可

[db2inst1@xttdb ~]$ db2stop

SQL1064N DB2STOP processing was successful.

開啟資料庫

[db2inst1@xttdb ~]$ db2start

SQL1063N DB2START processing was successful.

刪除資料庫

[db2inst1@xttdb ~]$ db2 drop db test01

DB20000I The DROP DATABASE command completed successfully.

[db2inst1@xttdb ~]$ db2stop

SQL1064N DB2STOP processing was successful.

[db2inst1@xttdb ~]$ db2 list database directory

System Database Directory

Number of entries in the directory = 1

Database 1 entry:

Database alias = TEST02

Database name = TEST02

Local database directory = /home/db2inst1

Database release level = f.00

Comment =

Directory entry type = Indirect

Catalog database partition number = 0

Alternate server hostname =

Alternate server port number =

檢視幫助資訊

[root@xttdb expc]# db2 ? SQL1092N

SQL1092N The requested command or operation failed because the user ID

does not have the authority to perform the requested command or

operation. User ID: "".

Explanation:

Authentication of a user is completed using a security facility outside

of the DB2 database system (part of the operating system or a separate

product.) Authorization is performed using DB2 facilities (using DB2

tables and configuration files.) This message can be returned when there

is a problem with either authentication or authorization.

[root@xttdb expc]# db2 ? restore

RESTORE DATABASE source-db-alias { restore-options | CONTINUE | ABORT }

restore-options:

[USER username [USING password]]

[Restore-Inventory-Clause] [INCREMENTAL [AUTOMATIC | ABORT]]

[Media-Target-Clause] [TAKEN AT date-time]

[[TO target-directory] | [ON path [{,path}...] [DBPATH ON path]]]

[TRANSPORT [STAGE IN staging-db-alias] [USING STOGROUP stogroup-name]]

[INTO target-db-alias] [LOGTARGET {directory | {{INCLUDE | EXCLUDE} [FORCE]}}]

[NEWLOGPATH directory] [WITH num-buff BUFFERS] [BUFFER buffer-size]

[REPLACE HISTORY FILE] [REPLACE EXISTING]

[REDIRECT [GENERATE SCRIPT file-name]] [PARALLELISM n] [COMPRLIB lib-name]

[COMPROPTS options-string] [WITHOUT ROLLING FORWARD]

[WITHOUT PROMPTING]

建立表

[db2inst1@xttdb ~]$ db2

db2 => connect to test02

Database Connection Information

Database server = DB2/LINUX 10.1.0

SQL authorization ID = ROOT

Local database alias = TEST02

db2 => list tables

Table/View Schema Type Creation time

------------------------------- --------------- ----- --------------------------

0 record(s) selected.

db2 => create table test_tab(id varchar(100),name varchar(100))

DB20000I The SQL command completed successfully.

db2 => commit

DB20000I The SQL command completed successfully.

db2 => list tables

Table/View Schema Type Creation time

------------------------------- --------------- ----- --------------------------

TEST_TAB ROOT T 2015-01-20-01.08.03.470497

1 record(s) selected.

列出表空間資訊

db2 => list tablespaces

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

State = 0x0000

Detailed explanation:

Normal

Tablespace ID = 2

Name = USERSPACE1

Type = Database managed space

Contents = All permanent data. Large table space.

State = 0x0000

Detailed explanation:

Normal

Tablespace ID = 3

Name = SYSTOOLSPACE

Type = Database managed space

Contents = All permanent data. Large table space.

State = 0x0000

Detailed explanation:

Normal

DB2 入門命令練習（一）

相關文章