`
dannyhz
  • 浏览: 398010 次
  • 性别: Icon_minigender_1
  • 来自: 杭州
文章分类
社区版块
存档分类
最新评论

linux下学习db2

    博客分类:
  • db2
 
阅读更多
http://blog.csdn.net/sunrier/article/details/7835578

引用



   本笔记接在上一篇 <<Linux下安装DB2数据库步骤>>  http://blog.csdn.net/sunrier/article/details/7826233 ,学习过程中使用下载的官网DB2数据库(免费版本)测试的,由于工作中DB2是用在项目中的,而且是收费的数据库,故我只在项目编程中使用它(即公司的DB2数据库收费版本),没用来作为自己测试用。嘿嘿,所以下面的大部分操作主要来自下载的官网DB2数据库(免费版本)。

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 ~]$

强制停止
[db2inst1@localhost DB2]$ db2stop force
SQL1064N  DB2STOP processing was successful.
[db2inst1@localhost DB2]$

3.显示所有的实例
[db2inst1@localhost ~]$ db2ilist
db2inst1
[db2inst1@localhost ~]$

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.列出当前实例中有哪些数据库
[root@localhost instance]# su - db2inst1
[db2inst1@localhost ~]$ db2 list db directory
SQL1031N  The database directory cannot be found on the indicated file system.
SQLSTATE=58031
[db2inst1@localhost ~]$
注:上面信息说明实例中没有数据库

7.创建数据库
[root@localhost instance]# su - db2inst1
[db2inst1@localhost ~]$ db2 create database test
SQL1032N  No start database manager command was issued.  SQLSTATE=57019
[db2inst1@localhost ~]$
[db2inst1@localhost ~]$ db2start
SQL5043N  Support for one or more communications protocols failed to start successfully. However, core database manager functionality started successfully.
[db2inst1@localhost ~]$ db2 create database test
DB20000I  The CREATE DATABASE command completed successfully.
[db2inst1@localhost ~]$ db2 list db directory
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         =
[db2inst1@localhost ~]$
注:上面通过创建一个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 ~]$
注:用密码情况下格式[db2inst1@localhost ~]$ db2 connect to test user username using password
db2 connect to <database> user <username> using  <password>

9.列出当前实例中所有激活的数据库
[root@localhost ~]# su - db2inst1
[db2inst1@localhost ~]$ db2 list active databases
SQL1032N  No start database manager command was issued.  SQLSTATE=57019
[db2inst1@localhost ~]$ db2 list db directory
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         =
[db2inst1@localhost ~]$ db2start
SQL1026N  The database manager is already active.
[db2inst1@localhost ~]$ db2 list active databases
SQL1611W  No data was returned by Database System Monitor.
[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 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
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
[db2inst1@localhost ~]$
注: 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
[db2inst1@localhost ~]$

11.列出数据库中所有用户表
[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 list tables
Table/View                      Schema          Type  Creation time           
------------------------------- --------------- ----- --------------------------
  0 record(s) selected.
[db2inst1@localhost ~]$
注:上面信息说明数据库test中还没有表

12.在数据库test中创建表student
[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 "create table student (id int,fname varchar(30),age int)"
DB20000I  The SQL command completed successfully.
[db2inst1@localhost ~]$ db2 list tables
Table/View                      Schema          Type  Creation time           
------------------------------- --------------- ----- --------------------------
STUDENT                         DB2INST1        T     2012-08-06-14.38.33.456768
  1 record(s) selected.
[db2inst1@localhost ~]$

13.向表student中添加数据信息
[db2inst1@localhost ~]$ db2 "insert into student values (1,'Tom',22)"
DB20000I  The SQL command completed successfully.
[db2inst1@localhost ~]$ db2 "insert into student values (2,'Jack',21)"
DB20000I  The SQL command completed successfully.
[db2inst1@localhost ~]$ db2 "insert into student values (3,'Sunrier',25)"
DB20000I  The SQL command completed successfully.
[db2inst1@localhost ~]$

14.显示表student所有的信息
[db2inst1@localhost ~]$ db2 "select * from student"
ID          FNAME                          AGE      
----------- ------------------------------ -----------
          1 Tom                                     22
          2 Jack                                    21
          3 Sunrier                                 25
  3 record(s) selected.
[db2inst1@localhost ~]$

15.更改表student中的数据(如将Sunrier的年龄改为22)
[db2inst1@localhost ~]$ db2 "select * from student"
ID          FNAME                          AGE      
----------- ------------------------------ -----------
          1 Tom                                     22
          2 Jack                                    21
          3 Sunrier                                 25
  3 record(s) selected.
[db2inst1@localhost ~]$ db2 "update student set age=22 where fname='Sunrier'"
DB20000I  The SQL command completed successfully.
[db2inst1@localhost ~]$ db2 "select * from student"
ID          FNAME                          AGE      
----------- ------------------------------ -----------
          1 Tom                                     22
          2 Jack                                    21
          3 Sunrier                                 22
  3 record(s) selected.
[db2inst1@localhost ~]$

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 
  3 record(s) selected.
[db2inst1@localhost ~]$

[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 ~]$

[db2inst1@localhost ~]$ db2 "describe select * from db2inst1.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 ~]$

17.创建一个新表(如people)与数据库中某个表(如student)结构相同
[db2inst1@localhost ~]$ db2 list tables
Table/View                      Schema          Type  Creation time           
------------------------------- --------------- ----- --------------------------
STUDENT                         DB2INST1        T     2012-08-06-15.26.17.189538
  1 record(s) selected.

[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 
  3 record(s) selected.
[db2inst1@localhost ~]$ db2 "select * from student"
ID          FNAME                          AGE      
----------- ------------------------------ -----------
          1 Tom                                     22
          2 Jack                                    21
          3 Sunrier                                 25
  3 record(s) selected.
[db2inst1@localhost ~]$ db2 create table people like student
DB20000I  The SQL command completed successfully.
[db2inst1@localhost ~]$ db2 list tables
Table/View                      Schema          Type  Creation time           
------------------------------- --------------- ----- --------------------------
PEOPLE                          DB2INST1        T     2012-08-16-15.13.49.396370
STUDENT                         DB2INST1        T     2012-08-06-15.26.17.189538
  2 record(s) selected.
[db2inst1@localhost ~]$ db2 describe table people
                                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 
  3 record(s) selected.
[db2inst1@localhost ~]$


18.两个结构相同的表,将原来数据库中某个表(如student)的数据导入与它相同结构的新表(如people)中
[db2inst1@localhost ~]$ db2 list tables
Table/View                      Schema          Type  Creation time           
------------------------------- --------------- ----- --------------------------
PEOPLE                          DB2INST1        T     2012-08-16-15.13.49.396370
STUDENT                         DB2INST1        T     2012-08-06-15.26.17.189538
  2 record(s) selected.
[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 
  3 record(s) selected.
[db2inst1@localhost ~]$ db2 describe table people
                                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 
  3 record(s) selected.
[db2inst1@localhost ~]$ db2 "select * from student"
ID          FNAME                          AGE      
----------- ------------------------------ -----------
          1 Tom                                     22
          2 Jack                                    21
          3 Sunrier                                 25
  3 record(s) selected.
[db2inst1@localhost ~]$ db2 "select * from people"
ID          FNAME                          AGE      
----------- ------------------------------ -----------
  0 record(s) selected.

[db2inst1@localhost ~]$ db2 "insert into people select * from student"
DB20000I  The SQL command completed successfully.
[db2inst1@localhost ~]$ db2 "select * from people"
ID          FNAME                          AGE      
----------- ------------------------------ -----------
          1 Tom                                     22
          2 Jack                                    21
          3 Sunrier                                 25
  3 record(s) selected.
[db2inst1@localhost ~]$


19.修改一个表的字段类型(如表people中的fname字段把varchar(30)改为varchar(28))
[db2inst1@localhost ~]$ db2 describe table people
                                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 
  3 record(s) selected.
[db2inst1@localhost ~]$ db2 "select * from people"
ID          FNAME                          AGE      
----------- ------------------------------ -----------
          1 Tom                                     22
          2 Jack                                    21
          3 Sunrier                                 25
  3 record(s) selected.
[db2inst1@localhost ~]$ db2 "alter table people alter column fname set data type varchar(28)"
DB20000I  The SQL command completed successfully.
[db2inst1@localhost ~]$ db2 describe table people
                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
ID                              SYSIBM    INTEGER                      4     0 Yes 
FNAME                           SYSIBM    VARCHAR                     28     0 Yes 
AGE                             SYSIBM    INTEGER                      4     0 Yes 
  3 record(s) selected.
[db2inst1@localhost ~]$ db2 "select * from people"
ID          FNAME                        AGE      
----------- ---------------------------- -----------
          1 Tom                                   22
          2 Jack                                  21
          3 Sunrier                               25
  3 record(s) selected.
[db2inst1@localhost ~]$
格式:db2 "alter table <tablename> alter column <columnname> set data type <datatype>"
tablename:表名
columnname:字段名
datatype:字段类型
注:
一般更改字段类型是有操作限制的. 将字段改为比之前类型长度大的可以;如果要改小,必须先drop掉原来的column,然后再重新添加.
虽然我上面的执行成功了,可能是因为我用的官方免费版本的,如果遇到把长度大的改为小的无法执行,先用drop,再重新添加


20.向一个表添加字段(如向表people中添加备注信息字段notes;向表people中添加分数字段score)
格式:db2 "alter table <tablename> add <columnname> <datatype>"
[db2inst1@localhost ~]$ db2 describe table people
                                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 
  3 record(s) selected.
[db2inst1@localhost ~]$ db2 "select * from people"
ID          FNAME                          AGE      
----------- ------------------------------ -----------
          1 Tom                                     22
          2 Jack                                    21
          3 Sunrier                                 25
  3 record(s) selected.
[db2inst1@localhost ~]$ db2 "alter table people add notes varchar(100)"
DB20000I  The SQL command completed successfully.
[db2inst1@localhost ~]$ db2 describe table people
                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
ID                              SYSIBM    INTEGER                      4     0 Yes 
FNAME                           SYSIBM    VARCHAR                     36     0 Yes 
AGE                             SYSIBM    INTEGER                      4     0 Yes 
NOTES                           SYSIBM    VARCHAR                    100     0 Yes 
  4 record(s) selected.
[db2inst1@localhost ~]$ db2 "select * from people"
ID          FNAME                                AGE         NOTES                                                                                             
----------- ------------------------------------ ----------- ----------------------------------------------------------------------------------------------------
          1 Tom                                           22 -                                                                                                 
          2 Jack                                          21 -                                                                                                 
          3 Sunrier                                       25 -                                                                                                 
  3 record(s) selected.
[db2inst1@localhost ~]$ db2 "alter table people add score integer"
DB20000I  The SQL command completed successfully.
[db2inst1@localhost ~]$ db2 describe table people
                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
ID                              SYSIBM    INTEGER                      4     0 Yes 
FNAME                           SYSIBM    VARCHAR                     36     0 Yes 
AGE                             SYSIBM    INTEGER                      4     0 Yes 
NOTES                           SYSIBM    VARCHAR                    100     0 Yes 
SCORE                           SYSIBM    INTEGER                      4     0 Yes 
  5 record(s) selected.
[db2inst1@localhost ~]$ db2 "select * from people"
ID          FNAME             AGE         NOTES            SCORE    
----------- ----------------- ----------- --------------
          1 Tom                        22 -                                                                                                              -
          2 Jack                       21 -                                                                                                              -
          3 Sunrier                    25 -                                                                                                              -
  3 record(s) selected.
[db2inst1@localhost ~]$


21.删除表中的某个字段(如删除表people中的字段score)
格式:db2 "alter table <tablename> drop column <columnname>"
[db2inst1@localhost ~]$ db2 describe table people
                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
ID                              SYSIBM    INTEGER                      4     0 Yes 
FNAME                           SYSIBM    VARCHAR                     36     0 Yes 
AGE                             SYSIBM    INTEGER                      4     0 Yes 
NOTES                           SYSIBM    VARCHAR                    100     0 Yes 
SCORE                           SYSIBM    INTEGER                      4     0 Yes 
  5 record(s) selected.
[db2inst1@localhost ~]$ db2 "select * from people"
ID          FNAME             AGE         NOTES            SCORE    
----------- ----------------- ----------- --------------
          1 Tom                        22 -                                                                                                              -
          2 Jack                       21 -                                                                                                              -
          3 Sunrier                    25 -                                                                                                              -
  3 record(s) selected.
[db2inst1@localhost ~]$ db2 "alter table people drop column score"
DB20000I  The SQL command completed successfully.
[db2inst1@localhost ~]$ db2 describe table people
                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
ID                              SYSIBM    INTEGER                      4     0 Yes 
FNAME                           SYSIBM    VARCHAR                     36     0 Yes 
AGE                             SYSIBM    INTEGER                      4     0 Yes 
NOTES                           SYSIBM    VARCHAR                    100     0 Yes 
  4 record(s) selected.
[db2inst1@localhost ~]$ db2 "select * from people"
ID          FNAME          AGE         NOTES                                                                                             
----------- -------------- ----------- --------------------
          1 Tom                     22 -                                                                                                 
          2 Jack                    21 -                                                                                                 
          3 Sunrier                 25 -                                                                                                 
  3 record(s) selected.
[db2inst1@localhost ~]$
注:如果drop掉字段之后,可能会导致表查询/插入操作不能执行,则需要执行一下reorg命令,优化数据结构,
   格式如db2 reorg table <tablename>
[db2inst1@localhost ~]$ db2 reorg table people
DB20000I  The REORG command completed successfully.
[db2inst1@localhost ~]$ 


22.给表中添加带默认值的字段(如向表people中添加分数字段score,默认设置为90)
格式:db2 "alter table <tablename> add column <columnname> <datatype> not null with default <value> "
[db2inst1@localhost ~]$ db2 describe table people
                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
ID                              SYSIBM    INTEGER                      4     0 Yes 
FNAME                           SYSIBM    VARCHAR                     36     0 Yes 
AGE                             SYSIBM    INTEGER                      4     0 Yes 
NOTES                           SYSIBM    VARCHAR                    100     0 Yes 
  4 record(s) selected.
[db2inst1@localhost ~]$ db2 "select * from people"
ID          FNAME          AGE         NOTES                                                                                             
----------- -------------- ----------- --------------------
          1 Tom                     22 -                                                                                                 
          2 Jack                    21 -                                                                                                 
          3 Sunrier                 25 -                                                                                                 
  3 record(s) selected.
[db2inst1@localhost ~]$ db2 "alter table people add column score interger not null with default 90"
DB20000I  The SQL command completed successfully.
[db2inst1@localhost ~]$  db2 describe table people
                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
ID                              SYSIBM    INTEGER                      4     0 Yes 
FNAME                           SYSIBM    VARCHAR                     36     0 Yes 
AGE                             SYSIBM    INTEGER                      4     0 Yes 
NOTES                           SYSIBM    VARCHAR                    100     0 Yes 
SCORE                           SYSIBM    INTEGER                      4     0 No  
  5 record(s) selected.
[db2inst1@localhost ~]$ db2 "select * from people"
ID          FNAME                                AGE         NOTES                                                                                                SCORE    
----------- ------------------------------------ ----------- ---------------------------------------------------------------------------------------------------- -----------
          1 Tom                                           22 -                                                                                                             90
          2 Jack                                          21 -                                                                                                             90
          3 Sunrier                                       25 -                                                                                                             90
  3 record(s) selected.
[db2inst1@localhost ~]$

例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 <tablename> alter column <columnname> set default <value>"


23.列出数据库中用户表
[db2inst1@localhost ~]$ db2 list tables for user
Table/View                      Schema          Type  Creation time           
------------------------------- --------------- ----- --------------------------
STUDENT                         DB2INST1        T     2012-08-06-15.26.17.189538
  1 record(s) selected.
[db2inst1@localhost ~]$


24.列出数据库中所有系统表
[db2inst1@localhost ~]$ db2 list tables for system
Table/View                      Schema          Type  Creation time           
------------------------------- --------------- ----- --------------------------
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.
[db2inst1@localhost ~]$


25.列出数据库中所有表
[root@localhost ~]# su - db2inst1
[db2inst1@localhost ~]$ db2start
SQL1026N  The database manager is already active.
[db2inst1@localhost ~]$ db2 list active databases
SQL1611W  No data was returned by Database System Monitor.
[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 list tables for all
Table/View                      Schema          Type  Creation time           
------------------------------- --------------- ----- --------------------------
STUDENT                         DB2INST1        T     2012-08-06-15.26.17.189538
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.
[db2inst1@localhost ~]$


26.列出数据库中特定用户表
[root@localhost ~]# su - db2inst1
[db2inst1@localhost ~]$ db2start
SQL1026N  The database manager is already active.
[db2inst1@localhost ~]$ db2 list active databases
SQL1611W  No data was returned by Database System Monitor.
[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 list tables for schema db2inst1
Table/View                      Schema          Type  Creation time           
------------------------------- --------------- ----- --------------------------
STUDENT                         DB2INST1        T     2012-08-06-15.26.17.189538
  1 record(s) selected.
[db2inst1@localhost ~]$ db2 list tables for schema db2inst2
Table/View                      Schema          Type  Creation time           
------------------------------- --------------- ----- --------------------------
  0 record(s) selected.
[db2inst1@localhost ~]$
格式: db2 list tables for schema <username>
注:符号<>表示必选项

27.删除表中的数据 :
[db2inst1@localhost ~]$ db2 delete from student where id=3
DB20000I  The SQL command completed successfully.
[db2inst1@localhost ~]$ db2 "select * from student"
ID          FNAME                          AGE      
----------- ------------------------------ -----------
          1 Tom                                     22
          2 Jack                                    21
  2 record(s) selected.
[db2inst1@localhost ~]$

28.删除一个数据库中的某个表
[db2inst1@localhost ~]$ db2 drop table student
DB20000I  The SQL command completed successfully.
[db2inst1@localhost ~]$ db2 list tables
Table/View                      Schema          Type  Creation time           
------------------------------- --------------- ----- --------------------------
  0 record(s) selected.
[db2inst1@localhost ~]$


29.删除一个数据库test
[db2inst1@localhost ~]$ db2 list db directory
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         =
[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 SQL command completed successfully.
[db2inst1@localhost ~]$ db2 drop db test
DB20000I  The DROP DATABASE command completed successfully.
[db2inst1@localhost ~]$ db2 list db directory
SQL1057W  The system database directory is empty.  SQLSTATE=01606
[db2inst1@localhost ~]$
注:删除数据库首先要断开数据库的连接

30.显示当前数据库连接有哪些应用程序
[root@localhost ~]# su - db2inst1
[db2inst1@localhost DB2]$ db2 list application
SQL1611W  No data was returned by Database System Monitor.
[db2inst1@localhost DB2]$ 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]$ 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
[db2inst1@localhost DB2]$

32.更改DB2 UDB注册表变量的值
db2set registry_variable_name=new_value
[db2inst1@localhost ~]$ db2set DB2COMM=TCPIP
[db2inst1@localhost ~]$

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
[db2inst1@localhost DB2]$

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 ~]$

字段之间默认分隔符号为逗号,下面使用'|'分割
[db2inst1@localhost ~]$ db2 "export to teacher_bak.txt of del modified by coldel| select * from teacher"
SQL3104N  The Export utility is beginning to export data to file
"teacher_bak.txt".
SQL3105N  The Export utility has finished exporting "2" rows.

Number of rows exported: 2
[db2inst1@localhost ~]$ ls
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
[db2inst1@localhost ~]$

以IXF格式导出 
[db2inst1@localhost ~]$ pwd
/home/db2inst1
[db2inst1@localhost ~]$ ls
db2inst1  sqllib  Sunrier  teacher_bak.txt  teacher.sql  teacher.txt  TEST.0.db2inst1.NODE0000.CATN0000.20120817103306.001
[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"
SQL3104N  The Export utility is beginning to export data to file
"/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
[db2inst1@localhost ~]$ ls /home/db2inst1/Sunrier
teacher_bak.ixf  teacher.ixf  teacher.msg  TEST.0.db2inst1.NODE0000.CATN0000.20120817150317.001
[db2inst1@localhost ~]$ cat /home/db2inst1/Sunrier/teacher.msg
SQL3104N  The Export utility is beginning to export data to file
"/home/db2inst1/Sunrier/teacher_bak.ixf".
SQL3105N  The Export utility has finished exporting "4" rows.
[db2inst1@localhost ~]$


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 ~]$ ls
db2inst1  sqllib  Sunrier  teacher.sql  teacher.txt  TEST.0.db2inst1.NODE0000.CATN0000.20120817103306.001
[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
  2 record(s) selected.
[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"
ID          FNAME                ADDRESS                                                                          BIRTH   
----------- -------------------- -------------------------------------------------------------------------------- ----------
          1 Lory                 上海徐汇中学                                                                     1978-08-06
          2 Sunrier              田林中学                                                                         1988-06-27
          3 Jerry                上海徐汇中学                                                                     1971-03-06
          4 Tim                  田林中学                                                                         1982-06-27
  4 record(s) selected.
[db2inst1@localhost ~]$

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
Table/View                      Schema          Type  Creation time           
------------------------------- --------------- ----- --------------------------
PEOPLE                          DB2INST1        T     2012-08-16-15.13.49.396370
STUDENT                         DB2INST1        T     2012-08-06-15.26.17.189538
  2 record(s) selected.
[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) )
DB20000I  The SQL command completed successfully.
insert into teacher values(1,'Lory','上海徐汇中学','1978-08-06')
DB20000I  The SQL command completed successfully.
insert into teacher values(2,'Sunrier','田林中学','1988-06-27')
DB20000I  The SQL command completed successfully.
[db2inst1@localhost ~]$ db2 describe table teacher
                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
ID                              SYSIBM    INTEGER                      4     0 No  
FNAME                           SYSIBM    VARCHAR                     20     0 No  
ADDRESS                         SYSIBM    VARCHAR                     80     0 Yes 
BIRTH                           SYSIBM    DATE                         4     0 Yes 
  4 record(s) selected.
[db2inst1@localhost ~]$ db2 "select * from teacher"
ID          FNAME                ADDRESS                                                                          BIRTH   
----------- -------------------- -------------------------------------------------------------------------------- ----------
          1 Lory                 上海徐汇中学                                                                     1978-08-06
          2 Sunrier              田林中学                                                                         1988-06-27
  2 record(s) selected.
[db2inst1@localhost ~]$

37.备份数据库(如防止表误操作)
格式:db2 backup db <database name> [ to <dir name> ]
database name:表示数据库
to <dir name>:表示为备份到的目录路径,为可选项,默认在当前目录下
[db2inst1@localhost ~]$ ls
db2inst1  sqllib  teacher.sql 
[db2inst1@localhost ~]$  db2 list db directory
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         =

[db2inst1@localhost ~]$ db2 backup db test
Backup successful. The timestamp for this backup image is : 20120817103306
[db2inst1@localhost ~]$ ls
db2inst1  sqllib  teacher.sql  TEST.0.db2inst1.NODE0000.CATN0000.20120817103306.001
[db2inst1@localhost ~]$ db2 list active databases
SQL1611W  No data was returned by Database System Monitor.
[db2inst1@localhost ~]$
注:TEST.0.db2inst1.NODE0000.CATN0000.20120817103306.001即为备份的数据库节点文件;
   执行备份命令时,如果出现无法执行,则先断开数据库的连接再执行备份命令.
   我使用的免费版本的DB2数据库测试时,从上面可以看出没有断开也可以执行,但执行完,发现数据库
   处于断开状态了
如: db2 force application all 
强制关闭实例上的所有应用程序
[db2inst1@localhost ~]$ ls
db2inst1  sqllib  Sunrier  teacher_bak.txt  teacher.sql  teacher.txt  TEST.0.db2inst1.NODE0000.CATN0000.20120817103306.001
[db2inst1@localhost ~]$ db2 backup db test to /home/db2inst1/Sunrier
Backup successful. The timestamp for this backup image is : 20120817150317
 
[db2inst1@localhost ~]$ ls
db2inst1  sqllib  Sunrier  teacher_bak.txt  teacher.sql  teacher.txt  TEST.0.db2inst1.NODE0000.CATN0000.20120817103306.001
[db2inst1@localhost ~]$ cd Sunrier/
[db2inst1@localhost Sunrier]$ ls
TEST.0.db2inst1.NODE0000.CATN0000.20120817150317.001
[db2inst1@localhost Sunrier]$
说明:上面的方法为脱机备份(也称为离线备份或者冷备份),此方法必须断开所有与数据库连接的应用后才能进行,备份时数据库不能提供给用户使用.



38.恢复数据库(如将一个表删除后,通过删除前的备份文件恢复)
格式:db2 restore db <database name> [ from <dir name> ]
database name:表示恢复的数据库名
from <dir name>:表示为从哪个目录路径下恢复,为可选项,默认在当前目录下
[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 list tables
Table/View                      Schema          Type  Creation time           
------------------------------- --------------- ----- --------------------------
PEOPLE                          DB2INST1        T     2012-08-16-15.13.49.396370
STUDENT                         DB2INST1        T     2012-08-06-15.26.17.189538
TEACHER                         DB2INST1        T     2012-08-17-10.18.18.245263
  3 record(s) selected.
[db2inst1@localhost ~]$ db2 "select * from teacher"
ID          FNAME                ADDRESS                                      BIRTH   
----------- -------------------- -------------------------------------------- ----------
          1 Lory                 上海徐汇中学                                 1978-08-06
          2 Sunrier              田林中学                                     1988-06-27
          3 Jerry                上海徐汇中学                                 1971-03-06
          4 Tim                  田林中学                                     1982-06-27
  4 record(s) selected.
[db2inst1@localhost ~]$ db2 drop table teacher
DB20000I  The SQL command completed successfully.
[db2inst1@localhost ~]$ db2 list tables
Table/View                      Schema          Type  Creation time           
------------------------------- --------------- ----- --------------------------
PEOPLE                          DB2INST1        T     2012-08-16-15.13.49.396370
STUDENT                         DB2INST1        T     2012-08-06-15.26.17.189538
  2 record(s) selected.
[db2inst1@localhost ~]$ db2 connect reset
DB20000I  The SQL command completed successfully.
[db2inst1@localhost Sunrier]$ ls /home/db2inst1/Sunrier
TEST.0.db2inst1.NODE0000.CATN0000.20120817150317.001
[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.
[db2inst1@localhost ~]$ db2 list tables
SQL1024N  A database connection does not exist.  SQLSTATE=08003
[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 list tables
Table/View                      Schema          Type  Creation time           
------------------------------- --------------- ----- --------------------------
PEOPLE                          DB2INST1        T     2012-08-16-15.13.49.396370
STUDENT                         DB2INST1        T     2012-08-06-15.26.17.189538
TEACHER                         DB2INST1        T     2012-08-17-10.18.18.245263
  3 record(s) selected.
[db2inst1@localhost ~]$ db2 "select * from teacher"
ID          FNAME                ADDRESS                                      BIRTH   
----------- -------------------- -------------------------------------------- ----------
          1 Lory                 上海徐汇中学                                 1978-08-06
          2 Sunrier              田林中学                                     1988-06-27
          3 Jerry                上海徐汇中学                                 1971-03-06
          4 Tim                  田林中学                                     1982-06-27
  4 record(s) selected.
[db2inst1@localhost ~]$
注:如果想把恢复的数据库更改为新的数据库名,则格式如下
  db2 restore db <database name>  [ from <dir name> into <new database name> ]
例: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 <database name> [ export -tc <username> ] -u <username> -p <password>
参数: -tc 创建表的用户名
      -tn 用户的表名
      -sn 模式名,即导出该模式下的所有表     
查看db2move命令帮助db2move -help
导出test数据库中的全部数据
[db2inst1@localhost ~]$ ls
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@localhost ~]$ ls
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
[db2inst1@localhost ~]$
注:
  执行上面命令后会把数据库test中全部数据提取到当前目录(/home/db2inst1),每个表的内容都存储在一个.ixf文件中,
  每个.ixf文件都有一个与之相对应的.msg文件,.msg文件是描述从表中导出数据时的信息.例外还有两个文件,db2move.lst用来
  记录.ixf文件,.msg文件与表一一对应.EXPORT.out记录的是导出数据时的屏幕输出.
 
 
导出test数据库中的teacher表中的信息
[db2inst1@localhost ~]$ ls
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
Application code page not determined, using ANSI codepage 1208
*****  DB2MOVE  *****
Action:  EXPORT
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"
Disconnecting from database ... successful!
End time:  Fri Aug 17 16:33:24 2012
[db2inst1@localhost ~]$ ls
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
[db2inst1@localhost ~]$ 

40.查看test数据库备份的历史记录
格式:db2 list history backup all for <database name>
[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

Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
  B  D  20120817150248000   F       S0000000.LOG              
----------------------------------------------------------------------------
  Contains 3 tablespace(s):
  00001 SYSCATSPACE                                                         
  00002 USERSPACE1                                                           
  00003 SYSTOOLSPACE                                                         
----------------------------------------------------------------------------
    Comment: DB2 BACKUP TEST OFFLINE                                         
Start Time: 20120817150248
   End Time: 20120817150249
     Status: A
----------------------------------------------------------------------------
  EID: 5 Location:

Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
  B  D  20120817150317001   F    D  S0000000.LOG S0000000.LOG 
----------------------------------------------------------------------------
  Contains 3 tablespace(s):
  00001 SYSCATSPACE                                                         
  00002 USERSPACE1                                                           
  00003 SYSTOOLSPACE                                                         
----------------------------------------------------------------------------
    Comment: DB2 BACKUP TEST OFFLINE                                         
Start Time: 20120817150317
   End Time: 20120817150323
     Status: A
----------------------------------------------------------------------------
  EID: 6 Location: /home/db2inst1/Sunrier

Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
  B  D  20120817150825001   F    D  S0000000.LOG S0000000.LOG 
----------------------------------------------------------------------------
  Contains 3 tablespace(s):
  00001 SYSCATSPACE                                                         
  00002 USERSPACE1                                                           
  00003 SYSTOOLSPACE                                                         
----------------------------------------------------------------------------
    Comment: DB2 BACKUP TEST OFFLINE                                         
Start Time: 20120817150825
   End Time: 20120817150832
     Status: I
----------------------------------------------------------------------------
  EID: 7 Location: /home/db2inst1/Sunrier

Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
  R  D  20120817151620001   F       S0000000.LOG S0000000.LOG 20120817150317
----------------------------------------------------------------------------
  Contains 3 tablespace(s):
  00001 SYSCATSPACE                                                         
  00002 USERSPACE1                                                           
  00003 SYSTOOLSPACE                                                         
----------------------------------------------------------------------------
    Comment: RESTORE TEST NO RF                                              
Start Time: 20120817151620
   End Time: 20120817151629
     Status: A
----------------------------------------------------------------------------
  EID: 8 Location:
[db2inst1@localhost ~]$


41.读数据库管理程序配置
db2 get dbm cfg

42.写数据库管理程序配置
db2 update dbm cfg using 参数名 参数值

43.查看数据库的配置
db2 connect to <database> user <username> using <password>
db2 get db cfg  [ for <database> ]
database:数据库名
username:用户名
password:表示密码

44.设置数据库的配置
db2 connect to <database> user <username> using <password>
db2 update db cfg for <database> 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 <database>

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\"
[db2inst1@localhost ~]$
格式:db2 ? <command>


48.查看错误码信息
[db2inst1@localhost ~]$ db2 ? 22003
SQLSTATE 22003: A numeric value is out of range.
[db2inst1@localhost ~]$
格式:db2 ? <ERRCODE>

49.待定,工作过程中遇到相关问题再补充......


实例程序
//db2dbproc.sqc
[cpp] view plain copy
/************************************************************
    FileName : db2dbproc.sqc    
    FileFunc : C语言接口访问本地DB2数据库      
    Version  : V0.1        
    Author   : Sunrier        
    Date     : 2012-08-06  
    Descp    : Linux下使用C语言访问DB2函数       
*************************************************************/ 
//(嵌入SQL语句的关键字不区分大小写)   
#include <stdio.h> 
#include <string.h> 
#include <sqlca.h> 
 
#define PARAERR                 0x04 
#define DATABASEERR             0x08 
 
EXEC SQL include sqlca; 
 
int check_error(char szMessage[]) 

    if( sqlca.sqlcode ) 
    { 
        printf("Check error report : \n"); 
        printf("Error occured : %s ,sqlcode = [%d] \n",szMessage,sqlca.sqlcode); 
        return 1; 
    } 
   
  return 0; 

 
int db2_login(int iArgcFlag,char *pUserName,char *pPassword,char *pDataBase)  

    int iRetCode = 1; 
    EXEC SQL BEGIN DECLARE SECTION; 
        char szUserName[50]; 
        char szPassword[50]; 
        char szDataBase[50]; 
    EXEC SQL END   DECLARE SECTION; 
 
    memset(szUserName,0,sizeof(szUserName)); 
    memset(szPassword,0,sizeof(szPassword)); 
    memset(szDataBase,0,sizeof(szDataBase)); 
    strcpy(szDataBase,pDataBase); 
 
    if( 1==iArgcFlag ) 
    { 
        EXEC SQL CONNECT TO:szDataBase ; 
    } 
    else 
    { 
        if( 3==iArgcFlag ) 
        { 
            strcpy(szUserName,pUserName); 
            strcpy(szPassword,pPassword); 
            EXEC SQL CONNECT TO:szDataBase USER:szUserName USING:szPassword; 
        } 
        else 
        { 
            printf("Usage : argc found error !\n"); 
            return PARAERR; 
        } 
    } 
 
    iRetCode = check_error("CONNECT TO DATABASE"); 
 
    return iRetCode; 

 
void db2_logout( void ) 

    EXEC SQL CONNECT RESET; 
 
    check_error("CONNECT RESET"); 



//demo.c
[cpp] view plain copy
/*************************************************************        
    FileName : demo.c    
    FileFunc : 测试登陆和关闭DB2数据库      
    Version  : V0.1        
    Author   : Sunrier        
    Date     : 2012-08-06  
    Descp    : Linux下使用C语言访问DB2函数         
*************************************************************/      
#include <stdio.h> 
 
int main(int argc,char *argv[])   
{   
    int iRetCode = 0;  
    int iArgcFlag;   
    char szUserName[20] = "Sunrier";     
    char szPassword[20] = "redhat";     
    char szDatabase[20] = "test";  
  
    iArgcFlag = 1;    
   
    iRetCode = db2_login(iArgcFlag,szUserName,szPassword,szDatabase);   
           
    printf("iRetCode = %d \n",iRetCode);   
     
    if( iRetCode ) 
    {   
        printf("DB2 Connection failure !\n");   
        return iRetCode; 
    } 
    else 
    {    
        printf("DB2 Connection success !\n"); 
    } 
      
    db2_logout();   
       
    return 0;   
}   


//makefile
[cpp] view plain copy
OBJS = demo 
all:$(OBJS) 
 
DB2INCPATH=/home/db2inst1/sqllib/include 
 
DB2LIBPATH=/home/db2inst1/sqllib/lib 
   
demo:demo.c db2dbproc.sqc 
        @db2 connect to test 
        @db2 prep db2dbproc.sqc 
        @gcc -I$(DB2INCPATH) -o demo demo.c db2dbproc.c -L$(DB2LIBPATH) -ldb2  
        @rm -rf db2dbproc.c 
clean:   
        @ls | grep -v ^makefile
|grep−v[.]c
| grep -v [.]h
|grep−v[.]sqc
| grep -v [.]sql$$ | xargs rm -rf 



[db2inst1@localhost DB2]$ ls
dbproc.sqc  demo.c  makefile
[db2inst1@localhost DB2]$ make
   Database Connection Information
Database server        = DB2/LINUX 9.7.1
SQL authorization ID   = DB2INST1
Local database alias   = TEST

LINE    MESSAGES FOR dbproc.sqc
------  --------------------------------------------------------------------
        SQL0060W  The "C" precompiler is in progress.
        SQL0091W  Precompilation or binding was ended with "0"
                  errors and "0" warnings.
[db2inst1@localhost DB2]$ ls
dbproc.sqc  demo  demo.c  makefile
[db2inst1@localhost DB2]$ ./demo
iRetCode = 0
DB2 Connection success !
[db2inst1@localhost DB2]$


分享到:
评论

相关推荐

    Linux 和 Windows 上的DB2安装和管理入门

    IBM DB2是一款非常著名的数据库管理系统,由IBM公司开发,适用于Linux、UNIX和Windows操作系统平台。本文将为您提供有关在...以上内容仅作为入门指导,更高级的管理技术则需要深入学习DB2的官方文档和相关的专业书籍。

    在linux系统中安装DB2数据库

    以下是对如何在Linux环境下安装DB2数据库的详细步骤和相关知识点的详解: 1. **系统准备**: 在开始安装前,确保你的Linux系统是最新状态,更新所有必要的安全补丁。检查你的Linux发行版是否支持DB2,如Red Hat ...

    IBM DB2 Linux安装指南

    通过本文档的学习,读者应能够掌握在Linux环境下安装配置DB2的基本流程,并对可能遇到的问题有所准备。随着技术的发展,DB2也在不断地完善和进步中,建议读者关注最新的版本更新和技术动态,以便更好地利用这一强大...

    DB2_Install_ for_linux

    本指南将详细介绍如何在Linux环境下安装DB2,以及一些基本的使用方法。 一、系统需求与准备工作 在开始安装DB2之前,确保你的Linux系统满足以下条件: 1. 操作系统:至少是Red Hat Enterprise Linux、SUSE Linux ...

    linux和AIX下DB2安装配置文档打包,很详细

    这个压缩包中的文档可能涵盖了以上所有方面,详细介绍了在Linux和AIX环境下安装和配置DB2的每一步骤,对于DB2管理员或系统管理员来说是非常宝贵的学习资料。通过深入学习这些文档,你可以掌握DB2在不同操作系统上的...

    DB2 10 for Linux, UNIX, and Windows Quickstart.pdf

    学习DB2 LUW还需要对数据库理论有基础的了解,如数据库原理、SQL语言、关系数据库设计等。有了这些基础后,学习DB2的各类操作和管理技巧会更加容易上手。此外,DB2的用户界面和管理工具也是需要熟悉的内容,如DB2 ...

    DB2 V10学习文档

    DB2 V10这个版本特别针对Linux、UNIX和Windows操作系统进行了优化,它提供了丰富的功能以及性能改进,以满足现代企业级应用需求。 在DB2 V10的学习文档中,我们将会学到数据库管理的各个方面,包括但不限于数据库的...

    DB2 V11.1 下载

    在Linux环境下,DB2 V11.1可以充分利用操作系统的优势,如多线程处理、内存管理和安全性。本压缩包包含的是专为Linux系统设计的DB2 V11.1版本,可能包含安装脚本、配置文件、数据库实例、数据文件以及相关的工具和...

    linux和windows上的DB2安装和管理入门

    通过本文档的学习,您不仅能够了解如何在 Linux 和 Windows 上安装 IBM DB2 9.7 数据库,还掌握了基本的管理技能。随着实践经验的积累,您可以进一步探索更多高级功能和技术,从而更好地管理和维护您的数据库系统。...

    DB2 数据库学习资料.zip

    通过这些学习资料,你可以系统地学习DB2数据库的各个方面,包括安装配置、操作管理、性能优化和开发实践。掌握DB2不仅能够提升你在数据库领域的专业技能,也为解决实际工作中的问题打下坚实基础。在学习过程中,理论...

    linux下DB2数据库安装教程.pdf

    在Linux环境下安装DB2数据库涉及多个步骤,包括系统环境的搭建、软件的安装和配置、以及数据库的初始化和使用。以下是详细的安装教程: 1. **环境搭建** - **操作系统**:首选的操作系统是 CentOS 6.5,这是一个...

    学习db2命令

    #### UNIX/Linux环境下DB2安装目录结构 - **DB2SETUP.EXE**:安装程序。 - **\SQLLIB**:安装的主要目录。 - **ADM**:管理员工具。 - **ADSM/ADSTAR**:管理员工具。 - **BIN**:可执行文件。 - **BND**:绑定...

    db2完整的学习资料

    这份"db2完整的学习资料"包含了IBM DB2各个模块的详细知识,对于想要深入了解或学习DB2的人来说,是一份不可多得的资源。下面,我将根据标题和描述,为你解析这份学习资料可能涵盖的重要知识点。 1. **DB2基础概念*...

    Linux和windows上db2安装与管理入门

    通过本文档的学习,读者可以了解到如何在Linux和Windows两种不同的操作系统上完成DB2的安装与基本管理操作。无论是对于初学者还是有一定经验的技术人员来说,掌握这些核心技能都是非常重要的。此外,熟悉跨平台管理...

    db2培训文档 linux

    根据提供的文件标题、描述、标签以及部分内容,我们可以总结出以下重要的IT知识点,这些知识点主要围绕着DB2在Linux环境下的安装、配置与管理等方面展开。 ### DB2在Linux下的安装与配置 #### 1. VMware环境搭建 -...

    Linux下DB2数据库安装教程

    近因为工作需要在学习DB2数据库,本教程讲解DB2数据库在inux下的安装步骤。  安装前请查看 DB2版本和许可证 说明来增加了解,先弄明白改安装什么版本,这里我用的是新的Express-C版本,这个版本是提供给个人学习用...

    DB2学习中文教程从入门到精通

    DB2学习中文教程旨在帮助读者从基础的数据库概念开始,逐步学习DB2数据库的安装、配置、操作和管理,最终达到精通的程度。 DB2 9.7是DB2数据库的一个版本,该版本支持的平台包括Linux、UNIX和Windows等。DB2 9.7...

    000-731 DB2 9 DBA for Linux UNIX and Windows

    【标题】"000-731 DB2 9 DBA for Linux UNIX and Windows" 涉及的是IBM的一款数据库管理...通过这些知识点的学习和实践,考生可以全面掌握DB2 9数据库在多种操作系统环境下的管理,为通过000-731认证考试做好充分准备。

    DB2 731考试学习资料

    通过深入研读这两份资料,考生可以系统地学习DB2数据库管理的知识,并通过模拟题和实践操作来提升自己的技能,以期在DB2 731考试中取得好成绩。同时,这些资料也对实际工作中需要管理和维护DB2数据库的IT专业人士...

    DB2学习笔记

    * 创建实例:db2icrt instance_name (windows) 或 db2icrt -u fenced_user_ID instance_name (linux) * 使用实例:set db2instance=实例名称 * 删除实例:db2idrop -f instance_name * 迁移实例:db2imigr instance_...

Global site tag (gtag.js) - Google Analytics