1.模拟带有identity表

[db2inst1@xifenfei ~]$ db2 "create table t_xff(xid smallint not null generated always as identity
> (start with 1,increment by 1),x_name varchar(200)) in ts_xifenfei"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "insert into t_xff(x_name) values('www.xifenfei.com')"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "insert into t_xff(x_name) values('XIFENFEI')"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "insert into t_xff(x_name) values('xifenfei')"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "select * from t_ff"
SQL0204N  "DB2INST1.T_FF" is an undefined name.  SQLSTATE=42704
[db2inst1@xifenfei ~]$ db2 "select * from t_xff"
XID    X_NAME
------ -------------
     1 www.xifenfei.com
     2 XIFENFEI
     3 xifenfei
  3 record(s) selected.

2.导出表结构

[db2inst1@xifenfei ~]$ mkdir move_s
[db2inst1@xifenfei ~]$ cd move_s/
[db2inst1@xifenfei move_s]$ db2look -d sample -e -l -o db2_sample.ddl
-- No userid was specified, db2look tries to use Environment variable USER
-- USER is: DB2INST1
-- Creating DDL for table(s)
-- Output is sent to file: db2_sample.ddl

3.导出数据

[db2inst1@xifenfei move_s]$ db2move sample export
Application code page not determined, using ANSI codepage 1208
*****  DB2MOVE  *****
Action:  EXPORT
Start time:  Thu Apr  5 20:21:28 2012
Connecting to database SAMPLE ... successful!  Server : DB2 Common Server V9.5.9
Binding package automatically ... /home/db2inst1/sqllib/bnd/db2common.bnd ... successful!
Binding package automatically ... /home/db2inst1/sqllib/bnd/db2move.bnd ... successful!
EXPORT:     18 rows from table "DB2INST1"."ACT"
EXPORT:      5 rows from table "DB2INST1"."CL_SCHED"
EXPORT:     14 rows from table "DB2INST1"."DEPARTMENT"
EXPORT:     42 rows from table "DB2INST1"."EMPLOYEE"
EXPORT:  10000 rows from table "DB2INST1"."EMPMDC"
EXPORT:     73 rows from table "DB2INST1"."EMPPROJACT"
EXPORT:      8 rows from table "DB2INST1"."EMP_PHOTO"
EXPORT:      8 rows from table "DB2INST1"."EMP_RESUME"
EXPORT:    145 rows from table "SYSTOOLS"."HMON_ATM_INFO"
EXPORT:      0 rows from table "SYSTOOLS"."HMON_COLLECTION"
EXPORT:      3 rows from table "DB2INST1"."IN_TRAY"
EXPORT:      8 rows from table "DB2INST1"."ORG"
EXPORT:      5 rows from table "SYSTOOLS"."POLICY"
EXPORT:     65 rows from table "DB2INST1"."PROJACT"
EXPORT:     20 rows from table "DB2INST1"."PROJECT"
EXPORT:     41 rows from table "DB2INST1"."SALES"
EXPORT:     35 rows from table "DB2INST1"."STAFF"
EXPORT:     35 rows from table "DB2INST1"."STAFFG"
EXPORT:      3 rows from table "DB2INST1"."T_XFF"
Disconnecting from database ... successful!
End time:  Thu Apr  5 20:21:32 2012

4.目标端创建数据库

C:\Windows\system32>db2 "create db db_XFF pagesize 8 k"
DB20000I  CREATE DATABASE命令成功完成。

5.目标端创建对象

C:\Windows\system32>DB2 -tvf D:\move_s\db2_sample.ddl -l d:\xifenfei.log
--检查xifenfei.log文件,发现错误,手工修复

6.导入数据文件

D:\move_s>db2move db_xff load
*****  DB2MOVE  *****
Action:  LOAD
Start time:  Sun Apr 15 23:00:17 2012
Connecting to database DB_XFF ... successful!  Server : DB2 Common Server V9.5.0
Binding package automatically ... E:\DB2\9.5\SQLLIB\BND\DB2COMMON.BND ... successful!
Binding package automatically ... E:\DB2\9.5\SQLLIB\BND\DB2MOVE.BND ... successful!
* LOAD:  table "DB2INST1"."ACT"
  -Rows read:         18
  -Loaded:            18
  -Rejected:           0
  -Deleted:            0
  -Committed:         18
* LOAD:  table "DB2INST1"."CL_SCHED"
  -Rows read:          5
  -Loaded:             5
  -Rejected:           0
  -Deleted:            0
  -Committed:          5
--中间很多记录省略
--发现identity表导入失败，需要手工处理
* LOAD:  table "DB2INST1"."T_XFF"
***  WARNING 3107.  Check message file tab19.msg!
***  SQL Warning!  SQLCODE is  3107
***  SQL3107W  消息文件中至少有一条警告消息。
  -Rows read:          3
  -Loaded:             0
  -Rejected:           3
  -Deleted:            0
  -Committed:          3
Disconnecting from database ... successful!
End time:  Sun Apr 15 23:00:26 2012

7.重新导入identity表

D:\move_s>db2 connect to db_xff
   数据库连接信息
 数据库服务器         = DB2/NT 9.5.0
 SQL 授权标识         = XIFENFEI
 本地数据库别名       = DB_XFF
D:\move_s>DB2 "load from tab19.ixf of ixf modified by identityoverride insert into db2inst1.t_xff"
SQL3501W  由于禁用数据库正向恢复，因此表所驻留的表空间将不被置于备份暂挂状态。
SQL3551W  表至少包含实用程序将覆盖的一个 GENERATED ALWAYS 列。
SQL3109N  实用程序正在开始从文件 "D:\move_s\tab19.ixf" 装入数据。
SQL3500W  在时间 "2012-04-15 23:06:52.393775"，实用程序在开始 "LOAD"。
SQL3150N  PC/IXF 文件中的 H 记录具有产品 "DB2    02.00"，日期 "20120405"和时间 "202132"。
SQL3153N  PC/IXF 文件中的 T 记录具有名称 "tab19.ixf"，限定符 "" 和源 ""。
SQL3519W  开始装入一致点。输入记录数 = "0"。
SQL3520W  “装入一致点”成功。
SQL3110N  实用程序已完成处理。从输入文件读了 "3" 行。
SQL3519W  开始装入一致点。输入记录数 = "3"。
SQL3520W  “装入一致点”成功。
SQL3515W  在时间 "2012-04-15 23:06:52.451619"，实用程序已经完成了 "LOAD"。
读取行数         = 3
跳过行数         = 0
装入行数         = 3
拒绝行数         = 0
删除行数         = 0
落实行数         = 3
D:\move_s>db2 "select * from db2inst1.t_xff"
XID    X_NAME
------ ------------------------------
     1 www.xifenfei.com
     2 XIFENFEI
     3 xifenfei
  3 条记录已选择。

一.load原理性知识
1.为什么要使用LOAD
load不需要写日志(或很少日志),不做检查约束和参照完整性约束,不触发Trigger,锁的时间比较短,因此特别适合大数据量的导入.

2.load过程分为4个阶段
load/build/delete/index copy.
load阶段是将源文件parser成物理数据存储的格式,直接装入到页中,而不通过db2引擎,load阶段会检查表定义,违背定义的数据不会装入到表中.
build阶段建议索引（如果装入表有索引的话）,会检查唯一性约束,违背了唯一性的数据会在delete阶段删除.
index copy阶段将index数据从指定的临时表空间拷贝到初始的表空间里.
index copy只适应于allow read access场景.load的4个阶段会记录在messages文件里.

3.load的offline和online
缺省情况下,load过程不允许其他应用访问表,即allow no access,或叫offline load（离线加载）.Allow read access,或叫online load(在线加载）,只有在load …insert into的时候才允许使用,其他应用读到的数据是加载前的数据,load … replace into会将数据先删除,再load,只能是离线加载.

4.load表的状态
load可能出现的几种状态,某一时刻可能会同时处于几种状态.只有当表是normal状态时,表才能进行正常的增删改查操作.
normal: 正常状态
set integrity pending: 如果目标表有check约束或reference约束,那么Load后此表处于set integrity pending,表明表有约束还未检查,稍后解释.
load in progress:load正在数据加载过程中.
load pending:数据提交前出现了故障,需要通过load..terminate,load..replace或load..restart解除暂挂状态
read access only:目标表数据是可以读的,当load时指定了allow read access,那表就会处于read access only状态
unavailable:表可能被删除了或从backup中恢复了.
unknown:通过load..query命令无法得知表的状态.

二.load试验测试
需要导入数据

[db2inst2@xifenfei ~]$ more /tmp/xifenfei.data
10,"MANAGE","MANAGE/ADVISE"
20,"ECOST ","ESTIMATE COST"
30,,"DEFINE SPECS"
40,"LEADPR","LEAD PROGRAM/DESIGN"
50,"SPECS ",
60,"LOGIC ","DESCRIBE LOGIC"
70,"CODE  ","CODE PROGRAMS"
80,"TEST  ","TEST PROGRAMS"
90,"ADMQS ","ADM QUERY SYSTEM"
100,"TEACH ","TEACH CLASSES"
110,"COURSE","DEVELOP COURSES"
60,"STAFF ","PERS AND STAFFING"
130,"OPERAT","OPER COMPUTER SYS"
140,"MAINT ","MAINT SOFTWARE SYS"
150,"ADMSYS","ADM OPERATING SYS"
160,"ADMDB ","ADM DATA BASES"
170,"ADMDC ","ADM DATA COMM"
80,"DOC   ","DOCUMENT"

一共18条记录

创建目标表

[db2inst2@xifenfei ~]$ db2 "CREATE TABLE XIFENFEI_LOAD(ACTNO SMALLINT NOT NULL primary key,
> ACTKWD CHAR(6) NOT NULL,ACTDESC VARCHAR(20) NOT NULL)"
DB20000I  The SQL command completed successfully.
[db2inst2@xifenfei ~]$ db2 list tables
Table/View                      Schema          Type  Creation time
------------------------------- --------------- ----- --------------------------
XIFENFEI_LOAD                   DB2INST2        T     2012-04-05-18.53.42.296503
  1 record(s) selected.
[db2inst2@xifenfei ~]$ db2 describe table xifenfei_load
                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
ACTNO                           SYSIBM    SMALLINT                     2     0 No
ACTKWD                          SYSIBM    CHARACTER                    6     0 No
ACTDESC                         SYSIBM    VARCHAR                     20     0 No
  3 record(s) selected.

创建异常表

[db2inst2@xifenfei ~]$ db2 "create table xifenfei_exp like xifenfei_load"
DB20000I  The SQL command completed successfully.
[db2inst2@xifenfei ~]$ db2 "alter table xifenfei_exp add column ts timestamp
> add column msg clob(32k)"
DB20000I  The SQL command completed successfully.
[db2inst2@xifenfei ~]$ db2 describe table xifenfei_exp
                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
ACTNO                           SYSIBM    SMALLINT                     2     0 No
ACTKWD                          SYSIBM    CHARACTER                    6     0 No
ACTDESC                         SYSIBM    VARCHAR                     20     0 No
TS                              SYSIBM    TIMESTAMP                   10     0 Yes
MSG                             SYSIBM    CLOB                     32768     0 Yes
  5 record(s) selected.

load导入数据

[db2inst2@xifenfei ~]$ db2 "load from /tmp/xifenfei.data of del modified by dumpfile=/tmp/xifenfei.dmp
messages xifenfei.msg insert into xifenfei_load for exception xifenfei_exp"
Number of rows read         = 18
Number of rows skipped      = 0
Number of rows loaded       = 16
Number of rows rejected     = 2
Number of rows deleted      = 2
Number of rows committed    = 18
SQL3107W  There is at least one warning message in the message file.

提示一共18条记录,成功了14条,拒绝2条,删除2条

查看表中记录

[db2inst2@xifenfei ~]$ db2 "select * from xifenfei_load"
ACTNO  ACTKWD ACTDESC
------ ------ --------------------
    10 MANAGE MANAGE/ADVISE
    20 ECOST  ESTIMATE COST
    40 LEADPR LEAD PROGRAM/DESIGN
    60 LOGIC  DESCRIBE LOGIC
    70 CODE   CODE PROGRAMS
    80 TEST   TEST PROGRAMS
    90 ADMQS  ADM QUERY SYSTEM
   100 TEACH  TEACH CLASSES
   110 COURSE DEVELOP COURSES
   130 OPERAT OPER COMPUTER SYS
   140 MAINT  MAINT SOFTWARE SYS
   150 ADMSYS ADM OPERATING SYS
   160 ADMDB  ADM DATA BASES
   170 ADMDC  ADM DATA COMM
  14 record(s) selected.

果真14条记录,和文件相比缺少4条记录

查看dump file

[db2inst2@xifenfei ~]$ more /tmp/xienfei.dmp.load.000
30,,"DEFINE SPECS"
50,"SPECS ",

发现两条违背表定义记录,导入过程中,直接被拒绝的两条

查看异常表

[db2inst2@xifenfei ~]$ db2 "select * from xifenfei_exp"
ACTNO  ACTKWD ACTDESC              TS                         MSG
------ ------ --------------------
    60 STAFF  PERS AND STAFFING    2012-04-05-19.02.19.984440 00001I0000500001
    80 DOC    DOCUMENT             2012-04-05-19.02.19.984440 00001I0000500001

发现两条违背唯一性约束记录,构建index的过程中删除

export导出数据

[db2inst1@xifenfei ~]$ db2 connect to sample
   Database Connection Information
 Database server        = DB2/LINUX 9.5.9
 SQL authorization ID   = DB2INST1
 Local database alias   = SAMPLE
[db2inst1@xifenfei ~]$ db2 "SELECT * FROM DB2INST1.ACT"
ACTNO  ACTKWD ACTDESC
------ ------ --------------------
    10 MANAGE MANAGE/ADVISE
    20 ECOST  ESTIMATE COST
    30 DEFINE DEFINE SPECS
    40 LEADPR LEAD PROGRAM/DESIGN
    50 SPECS  WRITE SPECS
    60 LOGIC  DESCRIBE LOGIC
    70 CODE   CODE PROGRAMS
    80 TEST   TEST PROGRAMS
    90 ADMQS  ADM QUERY SYSTEM
   100 TEACH  TEACH CLASSES
   110 COURSE DEVELOP COURSES
   120 STAFF  PERS AND STAFFING
   130 OPERAT OPER COMPUTER SYS
   140 MAINT  MAINT SOFTWARE SYS
   150 ADMSYS ADM OPERATING SYS
   160 ADMDB  ADM DATA BASES
   170 ADMDC  ADM DATA COMM
   180 DOC    DOCUMENT
  18 record(s) selected.
[db2inst1@xifenfei ~]$ db2 "export to sample.act of del messages xifenfei.log
select * from DB2INST1.ACT"
Number of rows exported: 18
[db2inst1@xifenfei ~]$ more sample.act
10,"MANAGE","MANAGE/ADVISE"
20,"ECOST ","ESTIMATE COST"
30,"DEFINE","DEFINE SPECS"
40,"LEADPR","LEAD PROGRAM/DESIGN"
50,"SPECS ","WRITE SPECS"
60,"LOGIC ","DESCRIBE LOGIC"
70,"CODE  ","CODE PROGRAMS"
80,"TEST  ","TEST PROGRAMS"
90,"ADMQS ","ADM QUERY SYSTEM"
100,"TEACH ","TEACH CLASSES"
110,"COURSE","DEVELOP COURSES"
120,"STAFF ","PERS AND STAFFING"
130,"OPERAT","OPER COMPUTER SYS"
140,"MAINT ","MAINT SOFTWARE SYS"
150,"ADMSYS","ADM OPERATING SYS"
160,"ADMDB ","ADM DATA BASES"
170,"ADMDC ","ADM DATA COMM"
180,"DOC   ","DOCUMENT"

import导入数据

[db2inst2@xifenfei ~]$ more create.act
CREATE TABLE ACT_COPY  (
ACTNO SMALLINT NOT NULL ,
ACTKWD CHAR(6) NOT NULL ,
ACTDESC VARCHAR(20) NOT NULL )
IN USERSPACE1;
[db2inst2@xifenfei ~]$ db2  -tvf create.act
CREATE TABLE ACT_COPY  ( ACTNO SMALLINT NOT NULL , ACTKWD CHAR(6) NOT NULL , ACTDESC VARCHAR(20) NOT NULL ) IN USERSPACE1
DB20000I  The SQL command completed successfully.
[db2inst2@xifenfei ~]$ db2 list tables
Table/View                      Schema          Type  Creation time
------------------------------- --------------- ----- --------------------------
ACT_COPY                        DB2INST2        T     2012-04-05-16.40.25.103571
  1 record(s) selected.
[db2inst2@xifenfei ~]$ db2 "import from /home/db2inst1/sample.act of del
messages xifenfei.log insert into act_copy"
Number of rows read         = 18
Number of rows skipped      = 0
Number of rows inserted     = 18
Number of rows updated      = 0
Number of rows rejected     = 0
Number of rows committed    = 18
[db2inst2@xifenfei ~]$ db2 "select * from act_copy"
ACTNO  ACTKWD ACTDESC
------ ------ --------------------
    10 MANAGE MANAGE/ADVISE
    20 ECOST  ESTIMATE COST
    30 DEFINE DEFINE SPECS
    40 LEADPR LEAD PROGRAM/DESIGN
    50 SPECS  WRITE SPECS
    60 LOGIC  DESCRIBE LOGIC
    70 CODE   CODE PROGRAMS
    80 TEST   TEST PROGRAMS
    90 ADMQS  ADM QUERY SYSTEM
   100 TEACH  TEACH CLASSES
   110 COURSE DEVELOP COURSES
   120 STAFF  PERS AND STAFFING
   130 OPERAT OPER COMPUTER SYS
   140 MAINT  MAINT SOFTWARE SYS
   150 ADMSYS ADM OPERATING SYS
   160 ADMDB  ADM DATA BASES
   170 ADMDC  ADM DATA COMM
   180 DOC    DOCUMENT
  18 record(s) selected.

补充说明
1.chardel 指定字符串分隔符,默认是””
2.lobs to path　指定lob目录,modified by lobsinfile 指定保存一个文件;modified by lobsinsepfiles 指定每个值保存一个文件
3. into table_name指定表导入数据方式:inset(追加),insert_update(有主键,主键匹配更新,否则增加),replace(删除表数据,然后插入)
4.commitcount 和数据库提交相关,默认是automatic:db2内部自动计算什么时候提交
5.restartcount/skipcount N 表示跳过前N条记录,从N+1开始继续导入
6.rowcount N 表示插入条数

[db2inst2@xifenfei ~]$ db2 "import from /home/db2inst1/sample.act of del restartcount  10
rowcount 6  messages xifenfei.log  insert into act_copy"
Number of rows read         = 16
Number of rows skipped      = 10
Number of rows inserted     = 6
Number of rows updated      = 0
Number of rows rejected     = 0
Number of rows committed    = 16

7.插入指定列举例

[db2inst2@xifenfei ~]$ db2 "import from /home/db2inst1/sample.act of del method P(1,3) restartcount  10
rowcount 2  messages xifenfei.log  replace into act_copy(ACTNO,ACTDESC)"
SQL0668N  Operation not allowed for reason code "7" on table
"DB2INST2.ACT_COPY".  SQLSTATE=57016
[db2inst2@xifenfei ~]$ db2 reorg table act_copy
DB20000I  The REORG command completed successfully.
[db2inst2@xifenfei ~]$ db2 "import from /home/db2inst1/sample.act of del method P(1,3) restartcount  10
rowcount 2  messages xifenfei.log  replace into act_copy(ACTNO,ACTDESC)"
Number of rows read         = 12
Number of rows skipped      = 10
Number of rows inserted     = 2
Number of rows updated      = 0
Number of rows rejected     = 0
Number of rows committed    = 12
[db2inst2@xifenfei ~]$ db2 "select * from act_copy"
ACTNO  ACTKWD ACTDESC
------ ------ --------------------
   110 -      DEVELOP COURSES
   120 -      PERS AND STAFFING
  2 record(s) selected.

0.升级前DB2版本

[db2inst1@xifenfei ~]$ db2level
DB21085I  Instance "db2inst1" uses "32" bits and DB2 code release "SQL09050"
with level identifier "03010107".
Informational tokens are "DB2 v9.5.0.0", "s071001", "LINUXIA3295", and Fix Pack
"0".
Product is installed at "/opt/db2/V9.5".

1.查看哪些实例

[root@xifenfei ~]# su - db2inst1
[db2inst1@xifenfei ~]$ db2ilist
db2inst1
db2inst2

2.关闭数据库

[db2inst1@xifenfei ~]$ db2 force applications all
SQL1032N  No start database manager command was issued.  SQLSTATE=57019
[db2inst1@xifenfei ~]$ db2 terminate
DB20000I  The TERMINATE command completed successfully.
[db2inst1@xifenfei ~]$ db2stop
04/05/2012 09:17:13     0   0   SQL1032N  No start database manager command was issued.
SQL1064N  DB2STOP processing was successful.
[db2inst1@xifenfei ~]$ su - db2inst2
Password:
[db2inst2@xifenfei ~]$ db2 force applications all
DB20000I  The FORCE APPLICATION command completed successfully.
DB21024I  This command is asynchronous and may not be effective immediately.
[db2inst2@xifenfei ~]$ db2 terminate
DB20000I  The TERMINATE command completed successfully.
[db2inst2@xifenfei ~]$ db2stop
04/05/2012 09:20:00     0   0   SQL1064N  DB2STOP processing was successful.
SQL1064N  DB2STOP processing was successful.

3.关闭管理服务器（DAS）

[root@xifenfei ~]# su - dasusr1
[dasusr1@xifenfei ~]$ db2admin stop
SQL4407W  The DB2 Administration Server was stopped successfully.

4.卸装未使用的共享库

[db2inst1@xifenfei ~]$  $HOME/sqllib/bin/ipclean
/home/db2inst1/sqllib/bin/ipclean: Removing DB2 engine and client's IPC resources for db2inst1.
[db2inst2@xifenfei ~]$  $HOME/sqllib/bin/ipclean
/home/db2inst2/sqllib/bin/ipclean: Removing DB2 engine and client's IPC resources for db2inst2.

5.升级数据库软件和组件

[root@xifenfei ~]# cd server/
[root@xifenfei server]# ./installFixPack -b /opt/db2/V9.5/
DBI1017I  installFixPack is updating the DB2 product(s) installed in
      location /opt/db2/V9.5/.
DB2 installation is being initialized.
 Total number of tasks to be performed: 36
Total estimated time for all tasks to be performed: 1165
Task #1 start
Description: Stopping DB2 Fault Monitor
Estimated time 10 second(s)
Task #1 end
Task #2 start
Description: Preparing the system
Estimated time 120 second(s)
A major error occurred during the execution that caused this program to
terminate prematurely. If the problem persists, contact your technical service
representative.
For more information see the DB2 installation log at
"/tmp/installFixPack.log.9890".
--发生错误，通过日志文件寻找错误
[root@xifenfei server]# more /tmp/installFixPack.log.9890
Stopping DB2 Fault Monitor :.......Success
ERROR: The installFixPack command detected some DB2 libraries are still loaded
in memory and some applications might still be running in the current
installation copy.  All applications must be stopped. See the fix pack readme
for pre-installation instructions, and re-run the installFixPack command.
Alternatively, to override automatic checking, you can re-issue the
installFixPack command with the '-f db2lib' parameter. Note: If you re-issue
the installFixPack command with the '-f db2lib' parameter, after the DB2
instances are updated, some applications might not work properly and might need
to be restarted to function properly against the updated DB2 instance.
Preparing the system :.......Failure
--发现是some DB2 libraries are still loaded，导致升级不能进行
--使用-f db2lib参数
[root@xifenfei server]# ./installFixPack -b /opt/db2/V9.5/ -f db2lib
DBI1017I  installFixPack is updating the DB2 product(s) installed in
      location /opt/db2/V9.5/.
DB2 installation is being initialized.
 Total number of tasks to be performed: 36
Total estimated time for all tasks to be performed: 1165
Task #1 start
Description: Stopping DB2 Fault Monitor
Estimated time 10 second(s)
Task #1 end
Task #2 start
Description: Preparing the system
Estimated time 120 second(s)
Task #2 end
Task #3 start
Description: Base Client Support for installation with root privileges
Estimated time 3 second(s)
Task #3 end
Task #4 start
Description: Product Messages - English
Estimated time 11 second(s)
Task #4 end
Task #5 start
Description: Base client support
Estimated time 86 second(s)
Task #5 end
Task #6 start
Description: The DB2 required component.
Estimated time 78 second(s)
Task #6 end
Task #7 start
Description: Java Help (HTML) - English
Estimated time 7 second(s)
Task #7 end
Task #8 start
Description: Base server support for installation with root privileges
Estimated time 7 second(s)
Task #8 end
Task #9 start
Description: Global Secure ToolKit
Estimated time 14 second(s)
Task #9 end
Task #10 start
Description: Java support
Estimated time 11 second(s)
Task #10 end
Task #11 start
Description: SQL procedures
Estimated time 3 second(s)
Task #11 end
Task #12 start
Description: ICU Utilities
Estimated time 54 second(s)
Task #12 end
Task #13 start
Description: Java Common files
Estimated time 20 second(s)
Task #13 end
Task #14 start
Description: Base server support
Estimated time 197 second(s)
Task #14 end
Task #15 start
Description: IBM Software Development Kit (SDK) for Java(TM)
Estimated time 32 second(s)
Task #15 end
Task #16 start
Description: Control Center Help (HTML) - English
Estimated time 13 second(s)
Task #16 end
Task #17 start
Description: Connect support
Estimated time 3 second(s)
Task #17 end
Task #18 start
Description: Communication support - TCP/IP
Estimated time 3 second(s)
Task #18 end
Task #19 start
Description: Parallel Extension
Estimated time 3 second(s)
Task #19 end
Task #20 start
Description: Replication tools
Estimated time 16 second(s)
Task #20 end
Task #21 start
Description: Control Center
Estimated time 40 second(s)
Task #21 end
Task #22 start
Description: DB2 data source support
Estimated time 4 second(s)
Task #22 end
Task #23 start
Description: DB2 LDAP support
Estimated time 3 second(s)
Task #23 end
Task #24 start
Description: DB2 Instance Setup wizard
Estimated time 4 second(s)
Task #24 end
Task #25 start
Description: First Steps
Estimated time 3 second(s)
Task #25 end
Task #26 start
Description: Product Signature for DB2 Enterprise Server Edition
Estimated time 5 second(s)
Task #26 end
Task #27 start
Description: Sample database source
Estimated time 4 second(s)
Task #27 end
Task #28 start
Description: Installing or updating SA MP Base Component
Estimated time 40 second(s)
Task #28 end
Task #29 start
Description: Installing or updating DB2 HA scripts for SA MP Base Component
Estimated time 40 second(s)
Task #29 end
Task #30 start
Description: Setting DB2 library path
Estimated time 180 second(s)
Task #30 end
Task #31 start
Description: Executing control tasks
Estimated time 20 second(s)
Task #31 end
Task #32 start
Description: Updating global registry
Estimated time 20 second(s)
Task #32 end
Task #33 start
Description: Starting DB2 Fault Monitor
Estimated time 10 second(s)
Task #33 end
Task #34 start
Description: Updating the db2ls link
Estimated time 1 second(s)
Task #34 end
Task #35 start
Description: Updating the DB2 Administration Server
Estimated time 40 second(s)
Task #35 end
Task #36 start
Description: Updating existing DB2 instances
Estimated time 60 second(s)
Task #36 end
A minor error occurred during the execution.
For more information see the DB2 installation log at
"/tmp/installFixPack.log.15581".
--提示升级有错误发生
--检查日志文件
[root@xifenfei server]# more /tmp/installFixPack.log.15581
TSAMP_VERSION=3.2.1.2
 DBI1130E  The SA MP Base Component could not be installed or updated
      because system prerequisites were not met. See the log file
      /tmp/prereqSAM.log.17293 for details.
--发现是samp组件没有安装导致该错误，忽略

6.升级数据库

[root@xifenfei server]# su - db2inst1
[db2inst1@xifenfei ~]$ db2level  -a
DB21085I  Instance "db2inst1" uses "32" bits and DB2 code release "SQL09059"
with level identifier "060A0107".
Informational tokens are "DB2 v9.5.0.9", "s120228", "IP23310", and Fix Pack
"9".
Product is installed at "/opt/db2/V9.5".
[db2inst1@xifenfei ~]$ db2ilist
db2inst1
db2inst2
[db2inst1@xifenfei ~]$ db2start
04/05/2012 09:39:45     0   0   SQL1063N  DB2START processing was successful.
SQL1063N  DB2START processing was successful.
[db2inst1@xifenfei ~]$ db2 list database DIRECTORY
 System Database Directory
 Number of entries in the directory = 3
Database 1 entry:
 Database alias                       = XFF
 Database name                        = XIFENFEI
 Local database directory             = /home/db2inst1
 Database release level               = c.00
 Comment                              = XIFENFEI TEST DATABASE
 Directory entry type                 = Indirect
 Catalog database partition number    = 0
 Alternate server hostname            =
 Alternate server port number         =
Database 2 entry:
 Database alias                       = TOOLSDB
 Database name                        = TOOLSDB
 Local database directory             = /home/db2inst1
 Database release level               = c.00
 Comment                              =
 Directory entry type                 = Indirect
 Catalog database partition number    = 0
 Alternate server hostname            =
 Alternate server port number         =
Database 3 entry:
 Database alias                       = SAMPLE
 Database name                        = SAMPLE
 Local database directory             = /home/db2inst1
 Database release level               = c.00
 Comment                              =
 Directory entry type                 = Indirect
 Catalog database partition number    = 0
 Alternate server hostname            =
 Alternate server port number         =
[db2inst1@xifenfei ~]$ db2updv95 -d toolsdb
_________________________________________________________________________
                    _____   DB2 Service Tools   _____
                            I      B      M
                               db2updv95
   This tool is a service utility designed to update a DB2 Version 9.5
   database to the current fixpak level.
_________________________________________________________________________
DB2 Universal Database Version 9.5, 5622-044 (c) Copyright IBM Corp. 2007
Licensed Material - Program Property of IBM
IBM DATABASE 2 Database update to current fix pack tool
db2updv95 completed successfully for database 'toolsdb'.
[db2inst1@xifenfei ~]$ db2updv95 -d SAMPLE
_________________________________________________________________________
                    _____   DB2 Service Tools   _____
                            I      B      M
                               db2updv95
   This tool is a service utility designed to update a DB2 Version 9.5
   database to the current fixpak level.
_________________________________________________________________________
DB2 Universal Database Version 9.5, 5622-044 (c) Copyright IBM Corp. 2007
Licensed Material - Program Property of IBM
IBM DATABASE 2 Database update to current fix pack tool
db2updv95 completed successfully for database 'SAMPLE'.
--指定数据库别名
[db2inst1@xifenfei ~]$ db2updv95 -d Xff
_________________________________________________________________________
                    _____   DB2 Service Tools   _____
                            I      B      M
                               db2updv95
   This tool is a service utility designed to update a DB2 Version 9.5
   database to the current fixpak level.
_________________________________________________________________________
DB2 Universal Database Version 9.5, 5622-044 (c) Copyright IBM Corp. 2007
Licensed Material - Program Property of IBM
IBM DATABASE 2 Database update to current fix pack tool
db2updv95 completed successfully for database 'Xff'.
[db2inst1@xifenfei ~]$ su - db2inst2
Password:
[db2inst2@xifenfei ~]$ db2 list database DIRECTORY
 System Database Directory
 Number of entries in the directory = 1
Database 1 entry:
 Database alias                       = XIFENFEI
 Database name                        = XIFENFEI
 Local database directory             = /home/db2inst2/xifenfei
 Database release level               = c.00
 Comment                              =
 Directory entry type                 = Indirect
 Catalog database partition number    = 0
 Alternate server hostname            =
 Alternate server port number         =
[db2inst2@xifenfei ~]$ db2start
04/05/2012 09:46:47     0   0   SQL1063N  DB2START processing was successful.
SQL1063N  DB2START processing was successful.
[db2inst2@xifenfei ~]$ db2updv95 -d  xifenfei
_________________________________________________________________________
                    _____   DB2 Service Tools   _____
                            I      B      M
                               db2updv95
   This tool is a service utility designed to update a DB2 Version 9.5
   database to the current fixpak level.
_________________________________________________________________________
DB2 Universal Database Version 9.5, 5622-044 (c) Copyright IBM Corp. 2007
Licensed Material - Program Property of IBM
IBM DATABASE 2 Database update to current fix pack tool
db2updv95 completed successfully for database 'xifenfei'.

7.补充说明
1)在升级数据库前需要做好数据库和软件备份
2)升级过程时先关闭所有db2相关进程,如果不能正常关闭(非本文上面列举操作相关进程),直接kill
3)升级过程中,如果实例,DAS等升级失败,需要后续手工升级
4)检查相关进程,如果没有正常启动,需要手工给予启动
5)一些工具进行绑定和重新绑定packages

db2 terminate
db2 connect to dbname
db2 BIND /home/db2inst2/sqllib/bnd/db2schema.bnd BLOCKING ALL GRANT PUBLIC SQLERROR CONTINUE
db2 BIND /home/db2inst2/sqllib/bnd/@db2ubind.lst BLOCKING ALL GRANT PUBLIC ACTION ADD
db2 BIND /home/db2inst2/sqllib/bnd/@db2cli.lst BLOCKING ALL GRANT PUBLIC ACTION ADD
db2 terminate
db2rbind xifenfei -l logfile all

0.DB2版本信息

[db2inst1@xifenfei ~]$ db2level
DB21085I  Instance "db2inst1" uses "32" bits and DB2 code release "SQL09050"
with level identifier "03010107".
Informational tokens are "DB2 v9.5.0.0", "s071001", "LINUXIA3295", and Fix Pack
"0".
Product is installed at "/opt/db2/V9.5".

1.显示syscat.schemata视图结构

[db2inst1@xifenfei ~]$ db2 "describe table syscat.schemata"
                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
SCHEMANAME                      SYSIBM    VARCHAR                    128     0 No
OWNER                           SYSIBM    VARCHAR                    128     0 No
OWNERTYPE                       SYSIBM    CHARACTER                    1     0 No
DEFINER                         SYSIBM    VARCHAR                    128     0 No
DEFINERTYPE                     SYSIBM    CHARACTER                    1     0 No
CREATE_TIME                     SYSIBM    TIMESTAMP                   10     0 No
REMARKS                         SYSIBM    VARCHAR                    254     0 Yes

2.查询当前存在schema

[db2inst1@xifenfei ~]$ db2 "select SCHEMANAME,owner,CREATE_TIME from syscat.schemata"
SCHEMANAME                 OWNER                      CREATE_TIME
------------            ------------                   ----------------------------
SYSIBM                     SYSIBM                     2012-03-25-15.07.07.196612
SYSCAT                     SYSIBM                     2012-03-25-15.07.07.196612
SYSFUN                     SYSIBM                     2012-03-25-15.07.07.196612
SYSSTAT                    SYSIBM                     2012-03-25-15.07.07.196612
SYSPROC                    SYSIBM                     2012-03-25-15.07.07.196612
SYSIBMADM                  SYSIBM                     2012-03-25-15.07.07.196612
SYSIBMINTERNAL             SYSIBM                     2012-03-25-15.07.07.196612
SYSIBMTS                   SYSIBM                     2012-03-25-15.07.07.196612
NULLID                     SYSIBM                     2012-03-25-15.07.23.011671
SQLJ                       SYSIBM                     2012-03-25-15.07.54.575637
SYSTOOLS                   DB2INST1                   2012-03-25-15.09.01.964744
  11 record(s) selected.

3.显示创建schema

[db2inst1@xifenfei ~]$  db2 "create schema xifenfei"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "select SCHEMANAME,owner,CREATE_TIME from syscat.schemata"
SCHEMANAME                 OWNER                      CREATE_TIME
------------            ------------                  ----------------------------
SYSIBM                     SYSIBM                     2012-03-25-15.07.07.196612
SYSCAT                     SYSIBM                     2012-03-25-15.07.07.196612
SYSFUN                     SYSIBM                     2012-03-25-15.07.07.196612
SYSSTAT                    SYSIBM                     2012-03-25-15.07.07.196612
SYSPROC                    SYSIBM                     2012-03-25-15.07.07.196612
SYSIBMADM                  SYSIBM                     2012-03-25-15.07.07.196612
SYSIBMINTERNAL             SYSIBM                     2012-03-25-15.07.07.196612
SYSIBMTS                   SYSIBM                     2012-03-25-15.07.07.196612
NULLID                     SYSIBM                     2012-03-25-15.07.23.011671
SQLJ                       SYSIBM                     2012-03-25-15.07.54.575637
SYSTOOLS                   DB2INST1                   2012-03-25-15.09.01.964744
XIFENFEI                   DB2INST1                   2012-04-03-12.01.12.724932
  12 record(s) selected.

4.隐式创建schema

[db2inst1@xifenfei ~]$ db2 "create table xff.t_xifenfei(id int,name varchar(100))"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "select SCHEMANAME,owner,CREATE_TIME from syscat.schemata"
SCHEMANAME                 OWNER                      CREATE_TIME
------------            ------------                 ----------------------------
SYSIBM                     SYSIBM                     2012-03-25-15.07.07.196612
SYSCAT                     SYSIBM                     2012-03-25-15.07.07.196612
SYSFUN                     SYSIBM                     2012-03-25-15.07.07.196612
SYSSTAT                    SYSIBM                     2012-03-25-15.07.07.196612
SYSPROC                    SYSIBM                     2012-03-25-15.07.07.196612
SYSIBMADM                  SYSIBM                     2012-03-25-15.07.07.196612
SYSIBMINTERNAL             SYSIBM                     2012-03-25-15.07.07.196612
SYSIBMTS                   SYSIBM                     2012-03-25-15.07.07.196612
NULLID                     SYSIBM                     2012-03-25-15.07.23.011671
SQLJ                       SYSIBM                     2012-03-25-15.07.54.575637
SYSTOOLS                   DB2INST1                   2012-03-25-15.09.01.964744
XIFENFEI                   DB2INST1                   2012-04-03-12.01.12.724932
XFF                        SYSIBM                     2012-04-03-12.03.12.581260
  13 record(s) selected.

隐式创建schema的所属用户会是SYSIBM(存放系统数据字典表SCHEMA)

5.删除schema

[db2inst1@xifenfei ~]$ db2 "drop schema xff"
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "END-OF-STATEMENT" was found following "drop
schema xff".  Expected tokens may include:  "RESTRICT".  SQLSTATE=42601
[db2inst1@xifenfei ~]$  db2 drop schema xff restrict
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0478N  DROP, ALTER, TRANSFER OWNERSHIP or REVOKE on object type "SCHEMA"
cannot be processed because there is an object "XFF.T_XIFENFEI", of type
"TABLE", which depends on it.  SQLSTATE=42893
[db2inst1@xifenfei ~]$ db2 "drop table xff.t_xifenfei"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$  db2 drop schema xff restrict
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "select SCHEMANAME,owner,CREATE_TIME from syscat.schemata"
SCHEMANAME                 OWNER                      CREATE_TIME
------------            ------------                 ----------------------------
SYSIBM                     SYSIBM                     2012-03-25-15.07.07.196612
SYSCAT                     SYSIBM                     2012-03-25-15.07.07.196612
SYSFUN                     SYSIBM                     2012-03-25-15.07.07.196612
SYSSTAT                    SYSIBM                     2012-03-25-15.07.07.196612
SYSPROC                    SYSIBM                     2012-03-25-15.07.07.196612
SYSIBMADM                  SYSIBM                     2012-03-25-15.07.07.196612
SYSIBMINTERNAL             SYSIBM                     2012-03-25-15.07.07.196612
SYSIBMTS                   SYSIBM                     2012-03-25-15.07.07.196612
NULLID                     SYSIBM                     2012-03-25-15.07.23.011671
SQLJ                       SYSIBM                     2012-03-25-15.07.54.575637
SYSTOOLS                   DB2INST1                   2012-03-25-15.09.01.964744
XIFENFEI                   DB2INST1                   2012-04-03-12.01.12.724932
  12 record(s) selected.

删除schema需要使用restrict关键字,而且该schema中无对象存在.

在DB2中的schema的概念和ORACLE中的概念有着本质的区别：在ORACLE中schema和用户是同一个;在DB2中schema不一定是用户,因为db2内部没有用户的概念,连接用户必须是操作系统用户.

一.Genearate_unique函数

[db2inst1@xifenfei ~]$ db2 "create table xifenfei.t_gu(custno char(13) for bit data,
> custname varchar(16))"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$  db2 "insert into xifenfei.t_gu values
> (generate_unique(),'www.xifenfei.com'),(generate_unique(),'xifenfei')"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "select * from xifenfei.t_gu"
CUSTNO                        CUSTNAME
----------------------------- ----------------
x'20120403054630527862000000' www.xifenfei.com
x'20120403054630527940000000' xifenfei
  2 record(s) selected.

generate_unique是按照国际标准时间(UTC)生成的当前时间戳加上当前数据库分区号，包含13个字节的字符串。如果调整了系统时间可能会出现重复

二.序列(Sequence)

[db2inst1@xifenfei ~]$ db2 "create sequence xifenfei.xff_seq
> as bigint
> start with 1
> increment by 1
> no maxvalue
> cycle
> cache 10"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "create table xifenfei.t_seq(xff_id bigint,
> custname varchar(16))"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "insert into xifenfei.t_seq values(nextval
> for xifenfei.xff_seq,'www.xifenfei.com'), (nextval for xifenfei.xff_seq,'xifenfei')"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "select * from xifenfei.t_seq"
XFF_ID               CUSTNAME
-------------------- ----------------
                   1 www.xifenfei.com
                   2 xifenfei
  2 record(s) selected.

和ORACLE的sequence基本相同，只是在oracle中是sequence.nextval这里改为了nextnvl for seqence

三.自增字段

--1.generated always as identity方式(不能人工干预插入数值)
[db2inst1@xifenfei ~]$ db2 "create table xifenfei.t_ind_a(xff_id bigint not null generated always
> as identity(start with 10,increment by 1,minvalue 10,maxvalue 1000000,no cycle,nocache,no order),
> custname varchar(16))"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "insert into xifenfei.t_ind_a(custname) values('www.xifenfei.com')"
DB20000I  The SQL command completed successfully.
--指定值插入失败
[db2inst1@xifenfei ~]$ db2 "insert into xifenfei.t_ind_a(xff_id,custname) values(11,'XIFENFEI')"
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0798N  A value cannot be specified for column "XFF_ID" which is defined as
GENERATED ALWAYS.  SQLSTATE=428C9
[db2inst1@xifenfei ~]$ db2 "select * from xifenfei.t_ind_a"
XFF_ID               CUSTNAME
-------------------- ----------------
                  10 www.xifenfei.com
  1 record(s) selected.
--1.generated by default as identity方式(可以人工干预插入数值)
[db2inst1@xifenfei ~]$ db2 "create table xifenfei.t_ind_d(xff_id bigint not null generated by default
> as identity (start with 10,increment by 1,minvalue 10,maxvalue 1000000,no cycle,nocache,no order),
> custname varchar(16))"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "insert into xifenfei.t_ind_d(custname) values('www.xifenfei.com')"
DB20000I  The SQL command completed successfully.
--指定值插入成功
[db2inst1@xifenfei ~]$ db2 "insert into xifenfei.t_ind_d(xff_id,custname) values(11,'XIFENFEI')"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "select * from xifenfei.t_ind_d"
XFF_ID               CUSTNAME
-------------------- ----------------
                  10 www.xifenfei.com
                  11 XIFENFEI
  2 record(s) selected.

这个功能和sql server/mysql的自增长列很相似，给出了两种方式选择，使得比它们更加灵活