DB2 入门命令练习（一）

创建一个新的数据库

[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

 

 

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29320885/viewspace-1409185/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29320885/viewspace-1409185/