DB2数据库对象 — 表

发表于 | 分类于 ， | |

DB2数据库对象 —— 表

表设计

生成列

生成列在表中定义，在这些列中，存储的值是使用表达式计算得出的，而不是通过插入或更新操作指定

1
2
3
4
5
6
CREATE TABLE test.test1(
   field1 INT,
   field2 INT,
   create_field1 INT GENERATED ALWAYS AS (field1 + field2),
   create_field2 INT GENERATED ALWAYS AS (CASE WHEN field1 > field2 THEN 0 ELSE 1 END)
);

生成列

标识列

标识列为 DB2提供一种方法，可自动为添加至表的每一行生成唯一数值

1
2
3
4
CREATE TABLE test.test2(
   field1 INT GENERATED ALWAYS AS IDENTITY(START WITH 1, INCREMENT BY 1),
   field2 VARCHAR(10)
);

使用 GENERATED ALWAYS 定义的列始终由 DB2数据库管理器生成值，不允许用户或程序显式提供（不允许Insert值）

1
2
3
4
CREATE TABLE test.test3(
   field1 INT GENERATED BY DEFAULT AS IDENTITY,
   field2 VARCHAR(10)
);

使用 GENERATED BY DEFAULT 定义的列可以由 DB2数据库生成值，但也允许用户或程序显式的提供该值（可以Insert值）

自动编号和标识列

not null with default

1
2
3
CREATE TABLE test.test(
    field1 VARCHAR(10) NOT NULL WITH DEFAULT '1'
);

not logged initially

1. 创建不记日志表

1
2
3
4
CREATE TABLE test.test4(
   field1 INT,
   field2 VARCHAR(10)
)NOT LOGGED INITIALLY;

NOT LOGGED INITIALLY 激活后，如果此表相关语句失败（导致rollback），或者 ROLLBACK TO SAVEPOINT 被执行，那么整个事物将被回滚，并且被激活 NOT LOGGED INITIALLY 属性的表将被标记为不可访问，此表只能被删除并重建。

2. 在事物中激活不记日志特性，仅当前事务不记日志

1
ALTER TABLE test.test3 ACTIVATE NOT LOGGED INITIALLY;

3. 不记日志清空表

1
2
ALTER TABLE test.test3 ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE;
TRUNCATE TABLE test.test3 IMMEDIATE;

append on

在DB2中，当表的数据被删除时，空间并不会释放，而是在原来位置做个删除标志，表示该空间可以重用。
当执行 Insert语句时，会扫描空闲空间并将新行置入。
而启用 append on特性后，那么 执行插入时不会扫描空间空间，而是直接插入到表的最后。

1
ALTER TABLE test.test3 APPEND < ON | OFF >;

打开 APPEND ON 属性时，表不能有集群索引；
启用该属性，考虑定期 REORG 表。

pctfree

为每页预留可用空间，应付 row overflow
如果没有进行指定，默认预留 10%的空闲空

1
2
ALTER TABLE test.test3 PCTFREE 10;
ALTER TABLE test.test3 PCTFREE 0; --只读表

OVERFLOW

假如字段 name的数据类型为 VARCHAR(60),如果一开始 name长度为10个字节，这时值假设刚刚可以放到一个数据页中。
但是假如有一个 update操作将 name从10字节更新为60字节，
如果这个数据页无法放下，那么数据库就在当前位置存放一个指针，
把数据放到一个新的数据页中，这个就叫 overflow。
overflow会增加 I/O的读取，对性能不好。

volatile

如果表数据量经常变化非常大，导致统计信息不准确而不能执行索引扫描，
那么启用 volatile特性后，优化器将考虑使用索引扫描而不是表扫描

1
ALTER TABLE test.test3 VOLATILE CARDINALITY;

对于这样的表，DB2在以下情况将执行索引扫描代替表扫描，而不考虑统计信息：

  1. 引用的所有列都是索引的组成部分
  2. 索引可以在索引扫描期间应用谓词

可以在 SYSCAT.TABLES 中查询出 “PCTFREE”、”LOCKSIZE“、”VOLATILE“、”APPEND ON“ 等信息

表维护

创建表

1
2
3
4
5
6
7
8
9
CREATE TABLE test.tab (
    field1  INT             NOT NULL
    ,field2 VARCHAR(255)
    ,CONSTRAINT pk_test_tab PRIMARY KEY (field1)
) IN data_spac INDEX IN index_spac;

COMMENT ON TABLE test.tab IS '示例表';
COMMENT ON COLUMN test.tab.field1 IS '字段1';
COMMENT ON COLUMN test.tab.field2 IS '字段2';
1
CREATE TABLE <table> LIKE <tgtTable>

上述命令产生的新表不会自动创建表中部分的附属对象（唯一约束、外键约束、触发器、索引）

查询表

1
2
3
4
5
6
7
8
9
10
11
12
-- 获取表
list tables [for all | for schema <schema>]

-- 获取表结构
describe table <table>

-- 获取DDL信息
db2look -d <database> -t <table> -e

-- 相关系统表
SYSCAT.TABLES
SYSCAT.COLUMNS

修改表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 删除字段
ALTER TABLE <table> DROP COLUMN <column>;

-- 新增字段
ALTER TABLE <table> ADD COLUMN <column> <type>;

-- 修改字段类型
ALTER TABLE <table> ALTER COLUMN <column> SET DATA TYPE <type>;

-- 设置非空约束
ALTER TABLE <table> ALTER COLUMN <column> SET NOT NULL;
ALTER TABLE <table> ALTER COLUMN <column> DROP NOT NULL;

-- 修改字段名
ALTER TABLE <table> RENAME COLUMN <oldColumn> TO <newColumn>;

-- 修改表名
SET CURRENT SCHEMA <schema>;
ALTER TABLE <table> RENAME TO <newTable>;

删除表

1
DROP TABLE <table>;

参考

循序渐进DB2 （第5章）
IBM Knowledge Center