登录 注册

Linux 下学习 DB2 命令的笔记

本贴最后更新于 2827 天前，其中的信息可能已经天翻地覆

本笔记接在上一篇 <<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 user using

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 alter column set data type "
tablename:表名
columnname:字段名
datatype:字段类型

注:
一般更改字段类型是有操作限制的. 将字段改为比之前类型长度大的可以;如果要改小,必须先 drop 掉原来的 column,然后再重新添加.
虽然我上面的执行成功了,可能是因为我用的官方免费版本的,如果遇到把长度大的改为小的无法执行,先用 drop,再重新添加

20.向一个表添加字段(如向表 people 中添加备注信息字段 notes;向表 people 中添加分数字段 score)
格式:db2 "alter table add "
[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 drop column "
[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
[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 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 alter column set default "

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
注:符号 <> 表示必选项

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 [ to ]
database name:表示数据库
to :表示为备份到的目录路径,为可选项,默认在当前目录下
[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 [ from ]
database name:表示恢复的数据库名
from :表示为从哪个目录路径下恢复,为可选项,默认在当前目录下
[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 [ 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@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
[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 user using
db2 get db cfg [ for ]

database:数据库名
username:用户名
password:表示密码

44.设置数据库的配置
db2 connect to user using
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"
[db2inst1@localhost ~]$
格式:db2 ?

48.查看错误码信息
[db2inst1@localhost ~]$ db2 ? 22003

SQLSTATE 22003: A numeric value is out of range.

[db2inst1@localhost ~]$
格式:db2 ?

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

实例程序

//db2dbproc.sqc

[cpp] view plain copy

  1. /************************************************************
  2.  FileName : db2dbproc.sqc     

  3.  FileFunc : C语言接口访问本地DB2数据库       

  4.  Version  : V0.1         

  5.  Author   : Sunrier         

  6.  Date     : 2012-08-06   

  7.  Descp    : Linux下使用C语言访问DB2函数
  8. *************************************************************/
  9. //(嵌入 SQL 语句的关键字不区分大小写)
  10. #include
  11. #include
  12. #include
  14. #define PARAERR 0x04
  15. #define DATABASEERR 0x08
  17. EXEC SQL include sqlca;
  19. int check_error(char szMessage[])
  20. {
  21.  if( sqlca.sqlcode )  

  22.  {  

  23.      printf("Check error report : \n");  

  24.      printf("Error occured : %s ,sqlcode = [%d] \n",szMessage,sqlca.sqlcode);  

  25.      return 1;  

  26.  }
  28. return 0;
  29. }
  31. int db2_login(int iArgcFlag,char *pUserName,char *pPassword,char *pDataBase)
  32. {
  33.  int iRetCode = 1;  

  34.  EXEC SQL BEGIN DECLARE SECTION;  

  35.      char szUserName[50];  

  36.      char szPassword[50];  

  37.      char szDataBase[50];  

  38.  EXEC SQL END   DECLARE SECTION;  

  40.  memset(szUserName,0,sizeof(szUserName));  

  41.  memset(szPassword,0,sizeof(szPassword));  

  42.  memset(szDataBase,0,sizeof(szDataBase));  

  43.  strcpy(szDataBase,pDataBase);  

  45.  if( 1==iArgcFlag )  

  46.  {  

  47.      EXEC SQL CONNECT TO:szDataBase ;  

  48.  }  

  49.  else  

  50.  {  

  51.      if( 3==iArgcFlag )  

  52.      {  

  53.          strcpy(szUserName,pUserName);  

  54.          strcpy(szPassword,pPassword);  

  55.          EXEC SQL CONNECT TO:szDataBase USER:szUserName USING:szPassword;  

  56.      }  

  57.      else  

  58.      {  

  59.          printf("Usage : argc found error !\n");  

  60.          return PARAERR;  

  61.      }  

  62.  }  

  64.  iRetCode = check_error("CONNECT TO DATABASE");  

  66.  return iRetCode;
  67. }
  69. void db2_logout( void )
  70. {
  71.  EXEC SQL CONNECT RESET;  

  73.  check_error("CONNECT RESET");
  74. }

//demo.c

[cpp] view plain copy

  1. /*************************************************************
  2.  FileName : demo.c     

  3.  FileFunc : 测试登陆和关闭DB2数据库       

  4.  Version  : V0.1         

  5.  Author   : Sunrier         

  6.  Date     : 2012-08-06   

  7.  Descp    : Linux下使用C语言访问DB2函数
  8. *************************************************************/
  9. #include
  11. int main(int argc,char *argv[])
  12. {
  13.  int iRetCode = 0;   

  14.  int iArgcFlag;    

  15.  char szUserName[20] = "Sunrier";      

  16.  char szPassword[20] = "redhat";      

  17.  char szDatabase[20] = "test";   

  19.  iArgcFlag = 1;     

  21.  iRetCode = db2_login(iArgcFlag,szUserName,szPassword,szDatabase);    

  23.  printf("iRetCode = %d \n",iRetCode);    

  25.  if( iRetCode )  

  26.  {    

  27.      printf("DB2 Connection failure !\n");    

  28.      return iRetCode;  

  29.  }  

  30.  else  

  31.  {     

  32.      printf("DB2 Connection success !\n");  

  33.  }  

  35.  db2_logout();    

  37.  return 0;
  38. }

//makefile

[cpp] view plain copy

  1. OBJS = demo

  2. all:$(OBJS)

  4. DB2INCPATH=/home/db2inst1/sqllib/include

  6. DB2LIBPATH=/home/db2inst1/sqllib/lib

  8. demo:demo.c db2dbproc.sqc

  9.      @db2 connect to test  

  10.      @db2 prep db2dbproc.sqc  

  11.      @gcc -I$(DB2INCPATH) -o demo demo.c db2dbproc.c -L$(DB2LIBPATH) -ldb2   

  12.      @rm -rf db2dbproc.c

  13. clean:

  14.      @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]$

  • DB2
    3 引用 • 1 回帖
673 2 49 125 77 79 4 35 280

相关帖子

回帖
Linux 下学习 DB2 命令的笔记

欢迎来到这里！

我们正在构建一个小众社区，大家在这里相互信任，以平等 • 自由 • 奔放的价值观进行分享交流。最终，希望大家能够找到与自己志同道合的伙伴，共同成长。

注册 关于
请输入回帖内容 ...
zhaoyong
回帖
0
 帖子
6
 积分
500