DB2数据迁移之export/import

联系:手机/微信(+86 17813235971) QQ(107644445)

标题:DB2数据迁移之export/import

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

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.

DB2 9.5.0.0升级至9.5.0.9(小版本升级)

联系:手机/微信(+86 17813235971) QQ(107644445)

标题:DB2 9.5.0.0升级至9.5.0.9(小版本升级)

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

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

DB2中schema管理

联系:手机/微信(+86 17813235971) QQ(107644445)

标题:DB2中schema管理

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

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内部没有用户的概念,连接用户必须是操作系统用户.

DB2中产生唯一值三种方式

联系:手机/微信(+86 17813235971) QQ(107644445)

标题: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的自增长列很相似,给出了两种方式选择,使得比它们更加灵活

DB2远程登录配置

联系:手机/微信(+86 17813235971) QQ(107644445)

标题:DB2远程登录配置

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

1.服务器端操作

[db2inst2@xifenfei ~]$ netstat -nap |grep db2sysc
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
tcp        0      0 0.0.0.0:55554               0.0.0.0:*                   LISTEN      8072/db2sysc 0
[db2inst2@xifenfei ~]$  db2 list db 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 ~]$ db2set db2comm=tcpip
--配置实例端口
[db2inst2@xifenfei ~]$ db2 update dbm cfg using svcename 5000
DB20000I  The UPDATE DATABASE MANAGER CONFIGURATION command completed
successfully.
--重启生效
[db2inst2@xifenfei ~]$ db2stop
04/02/2012 12:33:36     0   0   SQL1064N  DB2STOP processing was successful.
SQL1064N  DB2STOP processing was successful.
[db2inst2@xifenfei ~]$ db2start
04/02/2012 12:33:41     0   0   SQL1063N  DB2START processing was successful.
SQL1063N  DB2START processing was successful.
[db2inst2@xifenfei ~]$ netstat -nap |grep db2sysc
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
tcp        0      0 0.0.0.0:50000               0.0.0.0:*                   LISTEN      8462/db2sysc 0

2.客户端配置

C:\Windows\system32>db2 catalog tcpip node vm_xff remote 192.168.1.90 server 50000
DB21061E  未初始化命令行环境。
--DB21061E错误解决
C:\Windows\system32>db2cmd
--编目节点
C:\Windows\system32>db2 catalog tcpip node vm_xff remote 192.168.1.90 server 50000
DB20000I  CATALOG TCPIP NODE命令成功完成。
DB21056W  直到刷新目录高速缓存之后,目录更改才生效。
--编目数据库
C:\Windows\system32>db2 catalog db xifenfei as xifenfei at node vm_xff
DB20000I  CATALOG DATABASE命令成功完成。
DB21056W  直到刷新目录高速缓存之后,目录更改才生效。
C:\Windows\system32> db2 list node directory
 节点目录
 目录中的条目数 = 1
节点 1 条目:
 节点名                            = VM_XFF
 注释                            =
 目录条目类型                    = LOCAL
 协议                            = TCPIP
 主机名                          = 192.168.1.90
 服务名称             = 50000
--terminate使操作生效
C:\Windows\system32>db2 terminate
DB20000I  TERMINATE命令成功完成。

3.验证登陆

[db2inst2@xifenfei sqldbdir]$ db2 connect to xifenfei user db2inst2 using xifenfei
   Database Connection Information
 Database server        = DB2/LINUX 9.5.0
 SQL authorization ID   = DB2INST1
 Local database alias   = XIFENFEI
[db2inst1@xifenfei ~]$ db2 list applications
Auth Id  Application    Appl.      Application Id                                                 DB       # of
         Name           Handle                                                                    Name    Agents
-------- -------------- ---------- -------------------------------------------------------------- -------- -----
DB2INST2 db2bp.exe      45         192.168.1.1.38849.120411160214                                 XIFENFEI   1
DB2INST2 db2bp          50         *LOCAL.db2inst1.120402072313                                   XIFENFEI   1

DB2表空间管理(2)—DMS空间管理篇

联系:手机/微信(+86 17813235971) QQ(107644445)

标题:DB2表空间管理(2)—DMS空间管理篇

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

db2的DMS表空间管理主要有Exent/Reduce/Resize/Add/begin new stripe set,下面分别对这几项进行介绍
Exent扩展已有容器

[db2inst2@xifenfei ~]$ db2 list tablespace containers for 3 show detail
            Tablespace Containers for Tablespace 3
 Container ID                         = 0
 Name                                 = /home/db2inst2/dms/tbs_data01.dbf
 Type                                 = File
 Total pages                          = 3200
 Useable pages                        = 3168
 Accessible                           = Yes
SQL> select 3200*32/1024 from dual;
3200*32/1024
------------
         100
[db2inst2@xifenfei ~]$ db2 "alter tablespace tbs_data extend (file
'/home/db2inst2/dms/tbs_data01.dbf' 20M)"
DB20000I  The SQL command completed successfully.
[db2inst2@xifenfei ~]$ db2 list tablespace containers for 3 show detail
            Tablespace Containers for Tablespace 3
 Container ID                         = 0
 Name                                 = /home/db2inst2/dms/tbs_data01.dbf
 Type                                 = File
 Total pages                          = 3840
 Useable pages                        = 3808
 Accessible                           = Yes
SQL> select 3840*32/1024 from dual;
3840*32/1024
------------
         120

Reduce缩减已有容器

[db2inst2@xifenfei ~]$ db2 "alter tablespace tbs_data reduce (file
'/home/db2inst2/dms/tbs_data01.dbf' 40M)"
DB20000I  The SQL command completed successfully.
[db2inst2@xifenfei ~]$ db2 list tablespace containers for 3 show detail
            Tablespace Containers for Tablespace 3
 Container ID                         = 0
 Name                                 = /home/db2inst2/dms/tbs_data01.dbf
 Type                                 = File
 Total pages                          = 2560
 Useable pages                        = 2528
 Accessible                           = Yes
SQL> select 2560*32/1024 from dual;
2560*32/1024
------------
          80

Resize重设容器大小

[db2inst2@xifenfei ~]$ db2 "alter tablespace tbs_data resize (file
'/home/db2inst2/dms/tbs_data01.dbf' 30M)"
DB20000I  The SQL command completed successfully.
[db2inst2@xifenfei ~]$ db2 list tablespace containers for 3 show detail
            Tablespace Containers for Tablespace 3
 Container ID                         = 0
 Name                                 = /home/db2inst2/dms/tbs_data01.dbf
 Type                                 = File
 Total pages                          = 960
 Useable pages                        = 928
 Accessible                           = Yes
SQL> select 960*32/1024 from dual;
960*32/1024
-----------
         30

Add增加新容器

[db2inst2@xifenfei ~]$ db2 "alter tablespace tbs_data add (file
'/home/db2inst2/dms/tbs_data02.dbf' 30M)"
DB20000I  The SQL command completed successfully.
[db2inst2@xifenfei ~]$ db2 list tablespace containers for 3 show detail
            Tablespace Containers for Tablespace 3
 Container ID                         = 0
 Name                                 = /home/db2inst2/dms/tbs_data01.dbf
 Type                                 = File
 Total pages                          = 960
 Useable pages                        = 928
 Accessible                           = Yes
 Container ID                         = 1
 Name                                 = /home/db2inst2/dms/tbs_data02.dbf
 Type                                 = File
 Total pages                          = 960
 Useable pages                        = 928
 Accessible                           = Yes

Add增加新容器后,会在容器件进行数据Rebalance

begin new stripe set增加新容器

[db2inst2@xifenfei ~]$ db2 "alter tablespace tbs_data begin new stripe set (file
'/home/db2inst2/dms/tbs_data03.dbf' 10M)"
DB20000I  The SQL command completed successfully.
[db2inst2@xifenfei ~]$ db2 list tablespace containers for 3 show detail
            Tablespace Containers for Tablespace 3
 Container ID                         = 0
 Name                                 = /home/db2inst2/dms/tbs_data01.dbf
 Type                                 = File
 Total pages                          = 960
 Useable pages                        = 928
 Accessible                           = Yes
 Container ID                         = 1
 Name                                 = /home/db2inst2/dms/tbs_data02.dbf
 Type                                 = File
 Total pages                          = 960
 Useable pages                        = 928
 Accessible                           = Yes
 Container ID                         = 2
 Name                                 = /home/db2inst2/dms/tbs_data03.dbf
 Type                                 = File
 Total pages                          = 320
 Useable pages                        = 288
 Accessible                           = Yes

begin new stripe set当已有容器使用完之后,再使用新增加容器

DB2表空间管理(1)—基础篇

联系:手机/微信(+86 17813235971) QQ(107644445)

标题:DB2表空间管理(1)—基础篇

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

1.创建数据库

[db2inst2@xifenfei ~]$ db2 "create database xifenfei automatic storage yes
on /home/db2inst2/dbauto dbpath on /home/db2inst2/xifenfei
using codeset utf-8 territory cn collate using system"
DB20000I  The CREATE DATABASE command completed successfully.

DBPATH ON表示数据库目录
USING CONDESET codeset TERRITORY territory指定数据库编码集(Codeset)和区域(Territory)
automatic storage yes on /home/db2inst2/dbauto 表示启用自动存储管理,on指定路径

2.查看数据库

[db2inst2@xifenfei ~]$ db2 list db 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         =

3.连接数据库

[db2inst2@xifenfei ~]$ db2 connect to xifenfei
   Database Connection Information
 Database server        = DB2/LINUX 9.5.0
 SQL authorization ID   = DB2INST2
 Local database alias   = XIFENFEI

4.创建缓冲池

[db2inst2@xifenfei ~]$ db2 "create bufferpool bp32k size 100 pagesize 32k"
DB20000I  The SQL command completed successfully.

DB2默认创建库指创建4k的bufferpool,因为下面需要创建32k的表空间,所以需要先创建32k的bufferpool

5.创建数据库管理(DMS)的数据表空间

[db2inst2@xifenfei ~]$ db2 "create large tablespace tbs_data pagesize 32k managed by database
using (file '/home/db2inst2/dms/tbs_data01.dbf' 100M) extentsize 32 prefetchsize automatic
bufferpool bp32k no file system caching"
DB20000I  The SQL command completed successfully.

no file system caching关闭文件系统缓存,默认选项

6.创建系统管理(SMS)的临时表空间

[db2inst2@xifenfei ~]$ db2 "create temporary tablespace tbs_temp pagesize 32k
managed by system using ('/home/db2inst2/xifenfei/temp')  bufferpool bp32k"
DB20000I  The SQL command completed successfully.

7.创建系统管理(SMS)的用户临时表空间

[db2inst2@xifenfei ~]$ db2 "create user temporary tablespace tbs_user_temp pagesize 32k
managed by system using ('/home/db2inst2/xifenfei/user_temp')  bufferpool bp32k"
DB20000I  The SQL command completed successfully.

8.自动存储管理(Automatic Storage)的表空间

[db2inst2@xifenfei ~]$ db2 "create tablespace tbs_index pagesize 32k bufferpool bp32k"
DB20000I  The SQL command completed successfully.
[db2inst2@xifenfei ~]$ db2 "create tablespace tbs_data2 initialsize 10M increasesize 10M maxsize 10G"
DB20000I  The SQL command completed successfully.

数据表空间选择DMS,临时表空间选择SMS

9.查看表空间状态

[db2inst2@xifenfei ~]$ 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
 Total pages                          = 16384
 Useable pages                        = 16380
 Used pages                           = 10500
 Free pages                           = 5880
 High water mark (pages)              = 10500
 Page size (bytes)                    = 4096
 Extent size (pages)                  = 4
 Prefetch size (pages)                = 4
 Number of containers                 = 1
 Tablespace ID                        = 1
 Name                                 = TEMPSPACE1
 Type                                 = System managed space
 Contents                             = System Temporary data
 State                                = 0x0000
   Detailed explanation:
     Normal
 Total pages                          = 1
 Useable pages                        = 1
 Used pages                           = 1
 Free pages                           = Not applicable
 High water mark (pages)              = Not applicable
 Page size (bytes)                    = 4096
 Extent size (pages)                  = 32
 Prefetch size (pages)                = 32
 Number of containers                 = 1
 Tablespace ID                        = 2
 Name                                 = USERSPACE1
 Type                                 = Database managed space
 Contents                             = All permanent data. Large table space.
 State                                = 0x0000
   Detailed explanation:
     Normal
 Total pages                          = 8192
 Useable pages                        = 8160
 Used pages                           = 96
 Free pages                           = 8064
 High water mark (pages)              = 96
 Page size (bytes)                    = 4096
 Extent size (pages)                  = 32
 Prefetch size (pages)                = 32
 Number of containers                 = 1
 Tablespace ID                        = 3
 Name                                 = TBS_DATA
 Type                                 = Database managed space
 Contents                             = All permanent data. Large table space.
 State                                = 0x0000
   Detailed explanation:
     Normal
 Total pages                          = 3200
 Useable pages                        = 3168
 Used pages                           = 96
 Free pages                           = 3072
 High water mark (pages)              = 96
 Page size (bytes)                    = 32768
 Extent size (pages)                  = 32
 Prefetch size (pages)                = 32
 Number of containers                 = 1
 Tablespace ID                        = 4
 Name                                 = TBS_TEMP
 Type                                 = System managed space
 Contents                             = System Temporary data
 State                                = 0x0000
   Detailed explanation:
     Normal
 Total pages                          = 1
 Useable pages                        = 1
 Used pages                           = 1
 Free pages                           = Not applicable
 High water mark (pages)              = Not applicable
 Page size (bytes)                    = 32768
 Extent size (pages)                  = 32
 Prefetch size (pages)                = 32
 Number of containers                 = 1
 Tablespace ID                        = 5
 Name                                 = TBS_USER_TEMP
 Type                                 = System managed space
 Contents                             = User Temporary data
 State                                = 0x0000
   Detailed explanation:
     Normal
 Total pages                          = 1
 Useable pages                        = 1
 Used pages                           = 1
 Free pages                           = Not applicable
 High water mark (pages)              = Not applicable
 Page size (bytes)                    = 32768
 Extent size (pages)                  = 32
 Prefetch size (pages)                = 32
 Number of containers                 = 1
 Tablespace ID                        = 6
 Name                                 = TBS_INDEX
 Type                                 = Database managed space
 Contents                             = All permanent data. Large table space.
 State                                = 0x0000
   Detailed explanation:
     Normal
 Total pages                          = 1024
 Useable pages                        = 992
 Used pages                           = 96
 Free pages                           = 896
 High water mark (pages)              = 96
 Page size (bytes)                    = 32768
 Extent size (pages)                  = 32
 Prefetch size (pages)                = 32
 Number of containers                 = 1
 Tablespace ID                        = 7
 Name                                 = TBS_DATA2
 Type                                 = Database managed space
 Contents                             = All permanent data. Large table space.
 State                                = 0x0000
   Detailed explanation:
     Normal
 Total pages                          = 2560
 Useable pages                        = 2528
 Used pages                           = 96
 Free pages                           = 2432
 High water mark (pages)              = 96
 Page size (bytes)                    = 4096
 Extent size (pages)                  = 32
 Prefetch size (pages)                = 32
 Number of containers                 = 1

10.查看表空间和容器相关信息

[db2inst2@xifenfei ~]$ db2 list tablespace containers for 6 show detail
            Tablespace Containers for Tablespace 6
 Container ID                         = 0
 Name                                 = /home/db2inst2/dbauto/db2inst2/NODE0000/XIFENFEI/T0000006/C0000000.LRG
 Type                                 = File
 Total pages                          = 1024
 Useable pages                        = 992
 Accessible                           = Yes

11.显示表空间配置信息/使用信息/容器信息

[db2inst2@xifenfei ~]$ db2pd -d xifenfei -tablespaces
Database Partition 0 -- Database XIFENFEI -- Active -- Up 0 days 01:07:23
Tablespace Configuration:
Address    Id    Type Content PageSz ExtentSz Auto Prefetch BufID BufIDDisk FSC NumCntrs MaxStripe  LastConsecPg Name
0x9F43E060 0     DMS  Regular 4096   4        Yes  4        1     1         Off 1        0          3            SYSCATSPACE
0x9F43E6B0 1     SMS  SysTmp  4096   32       Yes  32       1     1         On  1        0          31           TEMPSPACE1
0x9F442EB0 2     DMS  Large   4096   32       Yes  32       1     1         Off 1        0          31           USERSPACE1
0x9F73B2E0 3     DMS  Large   32768  32       Yes  32       2     2         Off 1        0          31           TBS_DATA
0x9FAFE320 4     SMS  SysTmp  32768  32       Yes  32       2     2         On  1        0          31           TBS_TEMP
0x9FB029B0 5     SMS  UsrTmp  32768  32       Yes  32       2     2         On  1        0          31           TBS_USER_TEMP
0x9FB05420 6     DMS  Large   32768  32       Yes  32       2     2         Off 1        0          31           TBS_INDEX
0x9FB0BB20 7     DMS  Large   4096   32       Yes  32       1     1         Off 1        0          31           TBS_DATA2
Tablespace Statistics:
Address    Id    TotalPgs   UsablePgs  UsedPgs    PndFreePgs FreePgs    HWM        State      MinRecTime NQuiescers
0x9F43E060 0     16384      16380      10500      0          5880       10500      0x00000000 0          0
0x9F43E6B0 1     1          1          1          0          0          0          0x00000000 0          0
0x9F442EB0 2     8192       8160       96         0          8064       96         0x00000000 0          0
0x9F73B2E0 3     3200       3168       96         0          3072       96         0x00000000 0          0
0x9FAFE320 4     1          1          1          0          0          0          0x00000000 0          0
0x9FB029B0 5     1          1          1          0          0          0          0x00000000 0          0
0x9FB05420 6     1024       992        96         0          896        96         0x00000000 0          0
0x9FB0BB20 7     2560       2528       96         0          2432       96         0x00000000 0          0
Tablespace Autoresize Statistics:
Address    Id    AS  AR  InitSize    IncSize     IIP MaxSize     LastResize                 LRF
0x9F43E060 0     Yes Yes 33554432    -1          No  None        None                       No
0x9F43E6B0 1     Yes No  0           0           No  0           None                       No
0x9F442EB0 2     Yes Yes 33554432    -1          No  None        None                       No
0x9F73B2E0 3     No  No  0           0           No  0           None                       No
0x9FAFE320 4     No  No  0           0           No  0           None                       No
0x9FB029B0 5     No  No  0           0           No  0           None                       No
0x9FB05420 6     Yes Yes 33554432    -1          No  None        None                       No
0x9FB0BB20 7     Yes Yes 10485760    10485760    No  -2147483648 None                       No
Containers:
Address    TspId ContainNum Type    TotalPgs   UseablePgs StripeSet  Container
0x989BDE10 0     0          File    16384      16380      0          /home/db2inst2/dbauto/db2inst2/NODE0000/XIFENFEI/T0000000/C0000000.CAT
0x9F43ED00 1     0          Path    1          1          0          /home/db2inst2/dbauto/db2inst2/NODE0000/XIFENFEI/T0000001/C0000000.TMP
0x9F443500 2     0          File    8192       8160       0          /home/db2inst2/dbauto/db2inst2/NODE0000/XIFENFEI/T0000002/C0000000.LRG
0x9F73B930 3     0          File    3200       3168       0          /home/db2inst2/dms/tbs_data01.dbf
0x9F73DE90 4     0          Path    1          1          0          /home/db2inst2/xifenfei/temp
0x9FB05020 5     0          Path    1          1          0          /home/db2inst2/xifenfei/user_temp
0x9FB0B4A0 6     0          File    1024       992        0          /home/db2inst2/dbauto/db2inst2/NODE0000/XIFENFEI/T0000006/C0000000.LRG
0x9FB0C170 7     0          File    2560       2528       0          /home/db2inst2/dbauto/db2inst2/NODE0000/XIFENFEI/T0000007/C0000000.LRG

DB2实例管理

联系:手机/微信(+86 17813235971) QQ(107644445)

标题:DB2实例管理

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

1.创建新实例

[root@xifenfei ~]# cd /opt/db2/V9.5/instance/
[root@xifenfei instance]# ./db2icrt -h
DBI1001I  Usage:
 db2icrt [-h|-?]
         [-d]
         [-a AuthType]
         [-p PortName]
         [-s InstType]
         -u FencedID InstName
[root@xifenfei instance]# useradd Fence2
[root@xifenfei instance]# passwd Fence2
Changing password for user Fence2.
New UNIX password:
Retype new UNIX password:
passwd: all authentication tokens updated successfully.
[root@xifenfei instance]# useradd db2inst2
[root@xifenfei instance]# passwd db2inst2
Changing password for user db2inst2.
New UNIX password:
Retype new UNIX password:
passwd: all authentication tokens updated successfully.
[root@xifenfei instance]# ./db2icrt -a server -p 55555 -s ese -u Fence2 db2inst2
DBI1070I  Program db2icrt completed successfully.
[root@xifenfei instance]# su - db2inst2
[db2inst2@xifenfei ~]$ db2 get instance
 The current database manager instance is:  db2inst2
[db2inst2@xifenfei ~]$ db2ilist
db2inst1
db2inst2
[db2inst2@xifenfei ~]$ db2greg -dump
S,DB2,9.5.0.0,/opt/db2/V9.5,,,0,0,,1332659153,0
S,DAS,9.5.0.0,/opt/db2/V9.5/das,lib/libdb2dasgcf.so,,,, ,,
I,DAS,9.5.0.0,dasusr1,/home/dasusr1/das,,1,,/opt/db2/V9.5/das,,
I,DB2,9.5.0.0,db2inst1,/home/db2inst1/sqllib,,1,0,/opt/db2/V9.5,,
I,DB2,9.5.0.0,db2inst2,/home/db2inst2/sqllib,,1,0,/opt/db2/V9.5,,

2.启动关闭实例

[db2inst2@xifenfei ~]$ db2stop
03/31/2012 11:57:48     0   0   SQL1064N  DB2STOP processing was successful.
SQL1064N  DB2STOP processing was successful.
[db2inst2@xifenfei ~]$ db2start
03/31/2012 11:57:52     0   0   SQL1063N  DB2START processing was successful.
SQL1063N  DB2START processing was successful.

3.删除实例

--先需要关闭实例
[root@xifenfei instance]# ./db2idrop db2inst2
DBI1070I  Program db2idrop completed successfully.

DB2数据库激活

联系:手机/微信(+86 17813235971) QQ(107644445)

标题:DB2数据库激活

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

db2当前激活状态

[db2inst1@xifenfei ~]$ db2licm -l
Product name:                     "DB2 Enterprise Server Edition"
License type:                     "Trial"
Expiry date:                      "06/22/2012"
Product identifier:               "db2ese"
Version information:              "9.5"
Product name:                     "DB2 Connect Server"
License type:                     "Trial"
Expiry date:                      "06/22/2012"
Product identifier:               "db2consv"
Version information:              "9.5"

查看license文件

[db2inst1@xifenfei ~]$ db2licm -l db2ese_cV9.5CPU.lic
Product name:                     "DB2 Enterprise Server Edition"
License type:                     "CPU Option"
Expiry date:                      "Permanent"
Product identifier:               "db2ese"
Version information:              "9.5"
Features:
DB2 Database Partitioning:        "Not licensed"
DB2 Performance Optimization ESE: "Not licensed"
DB2 Storage Optimization:         "Not licensed"
DB2 Advanced Access Control:      "Not licensed"
DB2 Geodetic Data Management:     "Not licensed"
DB2 pureXML ESE:                  "Not licensed"
IBM Homogeneous Federation ESE:   "Not licensed"
IBM Homogeneous Replication ESE:  "Not licensed"
Product name:                     "DB2 Connect Server"
License type:                     "Trial"
Expiry date:                      "06/22/2012"
Product identifier:               "db2consv"
Version information:              "9.5"

导入license文件

[db2inst1@xifenfei ~]$ db2licm -a db2ese_cV9.5CPU.lic
LIC1402I  License added successfully.
LIC1426I   This product is now licensed for use as outlined in your License Agreement.
USE OF THE PRODUCT CONSTITUTES ACCEPTANCE OF THE TERMS OF THE IBM LICENSE AGREEMENT,
LOCATED IN THE FOLLOWING DIRECTORY: "/opt/db2/V9.5/license/en_US.iso88591"

再次查看db2激活状态

[db2inst1@xifenfei ~]$ db2licm -l
Product name:                     "DB2 Enterprise Server Edition"
License type:                     "CPU Option"
Expiry date:                      "Permanent"
Product identifier:               "db2ese"
Version information:              "9.5"
Features:
DB2 Database Partitioning:        "Not licensed"
DB2 Performance Optimization ESE: "Not licensed"
DB2 Storage Optimization:         "Not licensed"
DB2 Advanced Access Control:      "Not licensed"
DB2 Geodetic Data Management:     "Not licensed"
DB2 pureXML ESE:                  "Not licensed"
IBM Homogeneous Federation ESE:   "Not licensed"
IBM Homogeneous Replication ESE:  "Not licensed"
Product name:                     "DB2 Connect Server"
License type:                     "Trial"
Expiry date:                      "06/22/2012"
Product identifier:               "db2consv"
Version information:              "9.5"

各种数据库的激活方式不同(只讨论激活,不涉及授权问题):
1.oracle/mysql不需要激活,可以直接使用
2.sql server 需要序列号激活
3.db2需要license文件激活

DB2入门操作之二

联系:手机/微信(+86 17813235971) QQ(107644445)

标题:DB2入门操作之二

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

查看db2版本
db2 => select * from sysibm.sysversions
列出所有实例
[db2inst1@xifenfei ~]$ db2ilist
db2inst1
列出当前实例
[db2inst1@xifenfei ~]$ db2 get instance
 The current database manager instance is:  db2inst1
察看示例配置文件
[db2inst1@xifenfei ~]$ db2 get dbm cfg|more
察看数据库配置参数信息
[db2inst1@xifenfei ~]$ db2 get db cfg for TOOLSDB|more
列出所有表空间的详细信息
[db2inst1@xifenfei ~]$ db2 list tablespaces show detail|more
连接数据库
[db2inst1@xifenfei ~]$ db2 connect to TOOLSDB
   Database Connection Information
 Database server        = DB2/LINUX 9.7.4
 SQL authorization ID   = DB2INST1
 Local database alias   = TOOLSDB
sql操作数据库
[db2inst1@xifenfei ~]$ db2 "select * from t_xff"
查看端口号
[db2inst1@xifenfei ~]$ db2 get dbm cfg|grep SVCENAME
查看表结构
[db2inst1@xifenfei ~]$ db2 describe table t_xifenfei
查看某个表索引
[db2inst1@xifenfei ~]$ db2 describe indexes for table t_xff
显示当前活动数据库
[db2inst1@xifenfei ~]$ db2 list active databases
列出所有的系统表
[db2inst1@xifenfei ~]$ db2 list tables for system
列出表空间
[db2inst1@xifenfei ~]$ db2 list tablespaces
显示用户数据库的存取权限
[db2inst1@xifenfei ~]$ db2 GET AUTHORIZATIONS
检查 DB2 数据库管理程序配置
[db2inst1@xifenfei ~]$ db2 get dbm cfg