[DB2]一些使用技巧 Options

[猫猫草]

1、自动调整表空间

代码

在 DB2? 数据库系统中存在两种类型的基本表空间：系统管理的空间（SMS）和数据库管理的空间（DMS）。
与 SMS 表空间相关联的容器是文件系统目录，而这些目录中的文件会随着表空间中对象的增长而增长。文件会增加至达到其中一个容器上的文件系统限制或达到数据库的表空间大小限制（请参阅db2.udb.admin.doc/doc/r0001029.htm">SQL and XQuery limits）。
DMS 表空间由文件容器或原始设备容器组成，它们的大小是在将容器指定给表空间时设置的。当容器中的所有空间都已被使用时，则认为表空间已满。但是，与 SMS 不同，您可以使用 ALTER TABLESPACE 语句来添加或扩展容器，从而允许将更多的存储器空间提供给表空间。DMS 表空间还具有称为“自动调整大小”的功能。当可以自动调整大小的 DMS 表空间中的空间被消耗时，DB2 数据库系统可能回扩展一个或多个文件容器。SMS 表空间具有类似于自动增长的功能但术语“自动调整大小”专门用于 DMS。
启用和禁用自动调整大小（AUTORESIZE）
缺省情况下，不会对 DMS 表空间启用自动调整大小功能。下列语句创建不启用自动调整大小功能的 DMS 表空间：
      CREATE TABLESPACE DMS1 MANAGED BY DATABASE
     USING (FILE '/db2files/DMS1' 10 M)要启用自动调整大小功能，将 AUTORESIZE YES 子句指定为 CREATE TABLESPACE 语句的一部分：
      CREATE TABLESPACE DMS1 MANAGED BY DATABASE
     USING (FILE '/db2files/DMS1' 10 M) AUTORESIZE YES在已创建 DMS 表空间之后，还可以通过在 ALTER TABLESPACE 语句上使用 AUTORESIZE 子句来启用或禁用自动调整大小功能：
      ALTER TABLESPACE DMS1 AUTORESIZE YES
   ALTER TABLESPACE DMS1 AUTORESIZE NO有两个其他属性（MAXSIZE 和 INCREASESIZE）与自动调整大小的表空间相关联。
最大大小（MAXSIZE）
CREATE TABLESPACE 语句上的 MAXSIZE 子句定义表空间的最大大小。例如，以下语句创建可增长至 100 兆字节（如果数据库有多个数据库分区，则是每个数据库分区的大小）的表空间：
      CREATE TABLESPACE DMS1 MANAGED BY DATABASE
     USING (FILE '/db2files/DMS1' 10 M)
     AUTORESIZE YES MAXSIZE 100 MMAXSIZE NONE 子句指定表空间没有最大限制。表空间可以一直增长，直到达到文件系统限制或 DB2 表空间限制（请参阅 SQL Reference 中的 SQL Limits 部分）。如果在启用自动调整大小功能时不指定 MAXSIZE 子句，则使用缺省值无最大大小限制。
ALTER TABLESPACE 语句更改已经启用了自动调整大小的表空间的 MAXSIZE 的值。例如：
      ALTER TABLESPACE DMS1 MAXSIZE 1 G
   ALTER TABLESPACE DMS1 MAXSIZE NONE如果指定了最大大小，则 DB2 强制使用的实际值可能会比提供的值略小，原因是 DB2 会尝试使容器增长保持一致。不可能通过使两个数量并准确达到最大值来扩展容器。
增大大小（INCREASESIZE）
当表空间中已没有空闲扩展数据块但请求了一个或多个扩展数据块时，CREATE TABLESPACE 语句上的 INCREASESIZE 子句定义用来增大表空间的空间量。可以显式大小或百分比的形式指定该值。例如：
      CREATE TABLESPACE DMS1 MANAGED BY DATABASE
     USING (FILE '/db2files/DMS1' 10 M)
     AUTORESIZE YES INCREASESIZE 5 M
   CREATE TABLESPACE DMS1 MANAGED BY DATABASE
     USING (FILE '/db2files/DMS1' 10 M)
     AUTORESIZE YES INCREASESIZE 50 PERCENT百分比值意味着每次需要增大表空间时都要计算增大大小，并且增大量基于增大时表空间大小的百分比。例如，如果表空间大小是 20 兆字节而增大大小是百分之 50，则第一次表空间增大 10 兆字节（增大到大小为 30 兆字节），下一次增大 15 兆字节。
如果在启用了自动调整大小功能时未指定 INCREASESIZE 子句，则 DB2 确定要使用的适当值，该值在表空间的存在期间可能会有变化。与 AUTORESIZE 和 MAXSIZE 一样，可以使用 ALTER TABLESPACE 语句更改 INCREASESIZE 的值。
如果指定了增长大小，则 DB2 使用的实际值可能会与提供的值稍有不同。对所用值进行这种调整是为了使表空间中各容器的增大保持一致。
如何扩展表空间
对于可以自动调整大小的表空间，当所有现有空间都已被使用并请求了更多空间时，DB2 会尝试增大该表空间的大小。DB2 确定可以扩展表空间中的哪些容器以便不需要进行重新平衡。DB2 只扩展位于表空间图（该图描述表空间的存储器布局）的最后范围内的那些容器，并且对它们扩展相同的数量。
例如，考虑下列语句：
      CREATE TABLESPACE TS1 MANAGED BY DATABASE
     USING (FILE 'C:\TS1CONT' 1000, FILE 'D:\TS1CONT' 1000,
            FILE 'E:\TS1CONT' 2000, FILE 'F:\TS1CONT' 2000)
     EXTENTSIZE 4
     AUTORESIZE YES请记住，DB2 将每个容器的一小部分（一个扩展数据块）用于元数据，以下是根据 CREATE TABLESPACE 语句为表空间创建的表空间图。（表空间图是表空间快照的输出的一部分。）
      表空间图：
    范围    分割集      分割区  最大     最大        起始        结束   调节 容器
    编号                  偏移    扩展数据块  页      分割区       分割区
    [   0] [   0]      0     995      3983      0    248   0    4 (0,1,2,3)
    [   1] [   0]      0    1495      5983    249    498   0    2 (2,3)表空间图表明标识为 2 和 3 的容器（E:\TS1CONT 和 F:\TS1CONT）是仅有的在图最后面范围内的容器。因此，当 DB2 自动扩展此表空间中的容器时，它将只扩展这两个容器。
注:
如果创建表空间时所有容器的大小都一样，则图中只有一个范围。在这种情况下，DB2 扩展每一个容器。要防止限制为只扩展一小部分容器，创建表空间时使各容器大小相等。
如在 MAXSIZE 部分中所讨论的那样，可以指定对表空间大小的最大限制，也可以提供 NONE 值以便可无限增大表空间。（当使用 NONE 或无限制时，上限实际上是由文件系统或 DB2 表空间限制定义的。）DB2 不会尝试增大表空间至超过上限。但是，在达到上限之前，尝试增大容器可能会因为文件系统已满而失败。在这种情况下，DB2 不会在增大表空间并将会向应用程序返回“空间不足”条件。
解决此情况有两种方法：
增大已满文件系统上可用的空间量。
对表空间执行一些容器操作，使得这些容器不再位于表空间图的最后。使正被讨论的容器不再处于表空间图的最后的最简易方法是将新的分割集添加至具有一组新容器的表空间。最佳示例确保所有容器大小相等。可以使用 ALTER TABLESPACE 语句的 STRIPE SET 子句来添加新的分割集。通过添加新的分割集，就会将新的范围添加至表空间图。借助于新的范围， DB2 自动尝试扩展的容器就会处于此新的分割集中，而旧的容器保持不变。
注:
当暂挂用户启动的容器操作或者正在执行后续生新平衡时，会禁用自动调整大小功能，直到落实了操作或重新平衡完成为止。
例如，表空间具有三个大小一样的容器，每个容器都位于它自己的文件系统上。当对表空间执行一些操作时，DB2 会自动扩展这三个容器。最后，其中一个文件系统变满了，对应的容器就不能再增大了。如果该文件系统上不能再提供更多的可用空间，则必须对表空间执行容器操作，使得存在问题的容器不再处于表空间图的最后范围内。在这种情况下，您可以添加新的分割集并指定两个两个容器（仍然具有空间的每个文件系统上一个），也可以指定多一些或少一些容器（再次确保要添加的每个窗口大小一样并且要使用的每个文件系统上有足够的空间）。当 DB2 尝试增大表空间的大小时，现在它将尝试扩展这些新分割集中的容器而不是旧容器。
上面描述的情况仅适用于未启用自动调整大小的自动存储器表空间。如果自动存储器表空间启用了自动调整大小，则 DB2 会通过添加容器的新分割集来自动处理文件系统变满的情况。
监视
对 DMS 表空间自动调整大小是作为表空间监视器快照输出的一部分显示的。还会显示增大大小值和最大大小值：
  
   启用自动调整大小                    = Yes 或 No
   当前表空间大小（字节）              = ###
   最大表空间大小（字节）              = ### 或 NONE
   增加大小（字节）                    = ###
   增加大小（百分比）                  = ###
   上一次成功调整大小的时间            = YYYY/MM/DD HH:MM:SS.SSSSSS
   上一次调整大小尝试失败              = Yes 或 No使用说明
自动调整表空间大小具有下列含义：
启用了自动调整大小的表空间具有 DB2 通用数据库? 版本 8.2.1 或更早版本不能识别的相关元数据。在这些版本上尝试使用启用了自动调整大小的表空间的数据库会产生故障（极有可能会返回 SQL0980C 或 SQL0902C 错误）。可能会对尝试连接至数据库或尝试复原数据库发送错误。如果表空间启用了自动调整大小，则对这些表空间禁用“自动调整大小”功能会除去元数据，从而允许在 DB2 版本 8.2.1 或更早版本上使用该数据库。
当禁用“自动调整大小”功能时，如果后来再启用此功能，则与 INCREASESIZE 和 MAXSIZE 相关联的值会丢失。
不能对使用原始设备容器的表空间启用此功能。同样，不能将原始设备容器添加至可以自动调整大小的表空间。这些操作会产生错误（SQL0109N）。如果需要添加原始设备容器，则必须首先禁用此功能。
重定向复原操作不能更改容器定义以包括原始设备容器（SQL0109N）。
由于最大大小限制了 DB2 自动增大表空间的方式，所以最大大小也限制了用户可增大表空间的方式。换言之，当执行向表空间添加空间的操作时，生成的大小必须小于或等于最大大小。可以使用 ALTER TABLESPACE 语句的 ADD、EXTEND、RESIZE 或 BEGIN NEW STRIPE SET 子句来添加空间。

[猫猫草]

2、调整事务日志尺寸

代码

db2 => update db cfg using logfilsiz 40960
DB20000I  UPDATE DATABASE CONFIGURATION 指令已順利完成。
SQL1363W  一個以上的因立即修改所提交的參數並未動態地變更
。對這些配置參數而言，
所有的應用程式都必須在變更生效前切斷與此資料庫的連接。

db2 => get snapshot for database on DBNAME
資料庫可用的日誌空間（位元組數）           = 68766259
資料庫已使用的日誌空間（位元組數）         = 2188749
已使用的輔助日誌空間上限（位元組）         = 15981617
已使用的總日誌空間上限（位元組）           = 70241329
Log space available to the database (Bytes)= 204000000
Log space used by the database (Bytes)     = 0
Maximum secondary log space used (Bytes)   = 0
Maximum total log space used (Bytes)       = 5021692
*如果Maximum secondary log space used大於0,表示transaction的空間大小太小,要放大*

解法二(增加transaction log數量):
db2 =>update db cfg for DBNAME  using LOGSECOND 40

解法三(調程式):
調整程式的transaction,不要拉太長

ref:SQLCODE: -964, SQLSTATE: 57011

[猫猫草]

3、数据导入/导出相关

注意，这些内容只是作为助记使用，详细情况应该以 db2 的帮助文档为准，比如在 db2 命令界面内，输入

? export
? import

等等

代码

Contents
- 使用db2 backup指令备份
- 使用db2 restore指令恢复
- 使用db2look提取数据库结构DDL
- 用于数据移动的文件格式
- 使用db2move导出全部数据
- 使用db2 export指令导出数据
- 使用db2move导入（import）数据
- 使用db2 import指令导入数据
使用db2 backup指令备份
首先关闭所有到数据库的连接，将数据库置为“静默”状态：
$ db2 connect to testdb user db2inst1 using thepasswd
$ db2 quiesce database immediate force connections
$ db2 connect reset
现在可以开始备份了：
$ db2 backup database testdb to “/home/backup” user db2inst1 using thepasswd
解除数据库的“静默”状态：
$ db2 connect to testdb user db2inst1 using thepasswd
$ db2 unquiesce database
$ db2 connect reset
说明：
1， 以上指令将会把数据库testdb备份到指定目录 /home/backup下，所以请确保当前登陆用户（db2inst1）对该目录有读写的权限。
题外话，若打算使用root用户来执行备份，请先编辑文件 /etc/group，将root用户加入到与DB2相关的几个组：db2grp1, db2fgrp1, dasadm1.
2， 生成的备份文件名如下：
TESTDB.0.db2inst1.NODE0000.CATN0000.20050131205259.001
使用db2 restore指令恢复
相同库名恢复：
$ db2 restore database testdb from “/home/backup”
说明：
这将会从指定的位置恢复数据库testdb。
不同库名恢复：
$ db2 restore database testdb from “/home/backup” into testdb_new
说明：
这个指令将会建立一个新库，名为testdb_new，其结构、内容来自原先testdb的备份。
使用db2look提取数据库结构DDL
提取DDL
$ db2look –d testdb –a –e –x –o testdb.sql
参数的含义与用法请参考db2look的help.
编辑得到的DDL文件
使用db2look得到的DDL文件无法直接使用，因为其中存在一些与当前系统相关的特殊信息，所以需要对该文件进行编辑。共有几个方面：
1， 去掉文件头、尾的指令：
CONNECT TO TESTDB;
COMMIT WORK;
CONNECT RESET;
TERMINATE;
位于这几句指令之间的就是定义该数据库的DDL语句，去掉这几句话让这个文件变成一个纯粹的DDL文件，另外，有时候很容易发生DB2指令执行错误，将四句指令放到文件外手动执行更有助troubleshooting.
2， 去掉schema name.
在本案例中schema name是db2inst1。因为在进行异种平台的数据移动的时候会发生schema name的变化，最常见的就是从UNIX类系统迁移到Windows平台的时候，它缺省的schema name分别为db2inst1, db2admin.
3， 去掉全部引号
此经验主要来自Oracle，一个object，定义它的时候，使用了引号与没有使用引号是两个不同的object.
4， 去掉create table语句中指定的tablespace
这个原因与2类似，在数据移动的过程中，tablespace name发生变化是很经常的事情，所以不要指定，让（不同的）系统自己决定。
有一点非常重要，需要强调，若存在比较大的字段，就必须创建pagesize比较大的bufferpool，在创建一个tablespace使用该bufferpool。
用于数据移动的文件格式
这个部分简单介绍一下用于DB2数据移动的文件格式，共有四种：
1. ASC
非定界ASCII文件，是一个ASCII字符流。数据流中的行由行定界符分隔，而行中的每一列则通过起始和结束位置来定义。例如：
10 Head Office 160 Corporate New York
15 New England 50 Eastern Boston
20 Mid Atlantic 10 Eastern Washington
38 South Atlantic 30 Eastern Atlanta
42 Great Lakes 100 Midwest Chicago
51 Plains 140 Midwest Dallas
66 Pacific 270 Western San Francisco
84 Mountain 290 Western Denver
2. DEL
定界ASCII文件，也是一个ASCII字符流。数据流中的行由行定界符分隔，行中的列值由列定界符分隔。文件类型修饰符可用于修改这些定界符的默认值。例如：
10,"Head Office",160,"Corporate","New York"
15,"New England",50,"Eastern","Boston"
20,"Mid Atlantic",10,"Eastern","Washington"
38,"South Atlantic",30,"Eastern","Atlanta"
42,"Great Lakes",100,"Midwest","Chicago"
51,"Plains",140,"Midwest","Dallas"
66,"Pacific",270,"Western","San Francisco"
84,"Mountain",290,"Western","Denver"
3. WSF
work sheet format，工作表格式，用于与Lotus系列的软件进行数据交换。
4. PC/IXF
集成交换格式（Integration Exchange Format，IXF）数据交换体系结构的改编版本，由一些列可变长度的记录构成，包括头记录、表记录、表中每列的列描述符记录以及表中每行的一条或多条数据记录。PC/IXF 文件记录由包含了字符数据的字段组成。
使用db2move导出全部数据
db2move是一个集成式的数据移动工具，它支持导出（export）、导入（import）、装入（load）三种操作方式。其实db2move的这三种工作方式分别是通过简单使用db2 export, db2 import, db2 load指令来完成的。
此部分仅仅介绍其export功能，import和load将在稍后的部分介绍。使用db2move导出的数据文件格式是IXF。
建立并进入数据存放目录：
$ mkdir /home/backup/mydata
$ cd /home/backup/mydata
导出指定的数据库中的全部数据：
$ db2move testdb export –u db2inst1 –p thepasswd
说明：
1，这将会把数据库testdb中的全部数据提取到当前目录（/home/backup/mydata）中。每个表的内容都存储在一个.ixf文件中，每 个.ixf文件都有一个与之相对应的.msg文件，.msg文件是描述从表中导出数据时的信息的。另外还有两个文件，db2move.lst用来记 录.ixf文件、.msg文件与表的一一对应关系，EXPORT.out记录的是导出数据时的屏幕输出。
2，有关db2move指令更多的细节，请直接执行该指令，将会打印出其帮助信息。
使用db2 export指令导出数据
与上面提到的db2move的export功能不同，db2 export是一个更加细致的导出工具，它支持三种数据文件格式：DEL, WSF, IXF.
以下示范将数据库testdb中表mytbl的数据导出，存储在目录 /home/backup 下。
建立到数据库的连接：
$ db2
db2 => connect to testdb user db2inst1 using thepasswd
以DEL格式导出：
db2 => export to /home/backup/mytbl.txt of del select * from mytbl
以IXF格式导出：
db2 => export to /home/backup/mytbl.ixf of ixf select * from mytbl
注：若需要记录导出过程中的message，使用：
db2 => export to /home/backup/mytbl.ixf of ixf messages /home/backup/mytbl.msg select * from mytbl
断开连接：
db2 => connect reset
db2 => quit
$
更多有关db2 export的帮助，请：
$ db2 ? export
使用db2move导入（import）数据
以db2inst1用户身份登录到Host 2。
创建数据库mytestdb：
$ db2
db2 => create database mytestdb on ‘/home/db2inst1’ using codeset UTF-8 territory CN
db2 => connect to mytestdb user db2inst1 using thepasswd
创建一个pagesize为16K的bufferpool，名为mybigpool：
db2 => create bufferpool mybigpool immediate size 1000 pagesize 16K
创建一个tablespace使用上面创建的bufferpool，名为mybigspace：
db2 => create regular tablespace mybigspace pagesize 16K managed by system using (‘/home/db2inst1/db2inst1/NODE0000/SQL00004/SQLT0003.0’) extentsize 16 overhead 12.67 prefetchsize 16 transferrate 0.18 bufferpool mybigpool dropped table recovery off
注：
extentsize, overhead, prefetchsize, transferrate这几个参数值与所使用的服务器有关，我这里使用的值是基于普通的、使用SCSI硬盘的PC服务器的。
完成空库的创建：
db2 => commit work
db2 => connect reset
db2 => terminate
导入（import）数据：
使用import方式不需要先建表结构，即，准备好一个空库就行了。这一点与load方式不一样，load方式需要先建立表结构。
我将从Host1上导出的全部数据文件（位于Host1的 /home/backup/mydata下）复制到Host2下某个目录下，假定为 /home/movedata
$ cd /home/movedata
$ db2move mytestdb import –u db2inst1 –p thepasswd
此时屏幕上会显示有关导入数据的信息。
存在的问题：
db2move import方式只能导入“普通”的表，如果表中存在自增长的IDENTITY列，那么使用db2move import时，会出错。这是因为，如果IDENTITY列创建表的时候都是定义成always的话，那么在导入数据的时候该列数据是不能被赋值的，而是应该由系统生成，使用db2move无法导入这样的表。对于这种含有IDENTITY列的表，只能使用db2 import指令来进行导入，相关的参数是IDENTITYIGNORE，IDENTITYMISSING。我将在下一部分给出具体的指令操作。
一句题外话：
如果需要实现唯一主键，可以不必使用IDENTITY列，改而使用sequence，这样比较便于维护和管理。
使用db2 import指令导入数据
import和export是一对存在对应关系的指令，有一点不同的是，import支持四种格式：ASC, DEL, WSF, IXF, 而export只支持三种（见上面相应部分的描述）。
以下示范将数据文件/home/movedata/mytbl.ixf导入到数据库mytestdb中。
$ db2
db2 => connect to mytestdb user db2inst1 using thepasswd
db2 => import from /home/movedata/mytbl.ixf of ixf insert into mytbl
db2 => commit work
db2 => connect reset
db2 => quit
$
在上一部分，我有提到，若表中存在自增长的IDENTITY列，需要使用相应的参数才能导入，比如：
db2 => import from /home/movedata/mytbl2.ixf of ixf modified by identityignore insert into mytbl2
更多有关db2 import的帮助，请：
$ db2 ? import
Appendix
[root@tiv06 root]# uname -a
Linux tiv06.cn.ibm.com 2.4.9-e.57 #1 Thu Dec 2 20:56:19 EST 2004 i686 unknown
[root@tiv06 root]# cat /etc/redhat-release
Red Hat Linux Advanced Server release 2.1AS (Pensacola)
[db2inst1@tiv06 db2inst1]$ db2level
DB21085I Instance "db2inst1" uses "32" bits and DB2 code release "SQL08010" with level identifier "01010106".
Informational tokens are "DB2 v8.1.0.0", "s021023", "", and FixPak "0".
Product is installed at "/opt/IBM/db2/V8.1".
[root@sea root]# uname -a
Linux sea.cn.ibm.com 2.4.21-20.ELsmp #1 SMP Wed Aug 18 20:46:40 EDT 2004 i686 i686 i386 GNU/Linux
[root@sea root]# cat /etc/redhat-release
Red Hat Enterprise Linux AS release 3 (Taroon Update 3)
[db2inst1@sea db2inst1]$ db2level
DB21085I Instance "db2inst1" uses "32" bits and DB2 code release "SQL08020" with level identifier "03010106".
Informational tokens are "DB2 v8.1.0.64", "s040812", "MI00086", and FixPak "7".
Product is installed at "/opt/IBM/db2/V8.1".

[猫猫草]

4、db2 命令行下的一些常用命令

查看已编录的数据库列表
list db directory

连接数据库
connect to <数据库名> user <用户名> using <密码>

查看表结构
describe table <表名>

编录远程服务器
catalog tcpip node <自定义的节点名称，需要符合 db2 命名规则> remote <远程服务器 IP> server <远程服务器端口>
编录远程数据库
catalog database <远程数据库名称> as <本地别名> at node <前条语句定义的节点名称>

[猫猫草]

配合上文建立足够行宽的临时表空间

代码

CREATE TEMPORARY TABLESPACE mybigtmpspace
  PAGESIZE 32k
  MANAGED BY SYSTEM
  USING ('/home/dj_db2/dj_db2/NODE0000/SQL0001/SQLT0001.2')
  BUFFERPOOL mybigpool
  DROPPED TABLE RECOVERY OFF

赋予用户访问权限

代码

GRANT USE OF TABLESPACE mybigtmpspace TO PUBLIC