DB2 CREATE TABLE TEST3 ( ID INTEGER COMPRESS SYSTEM DEFAULT ,
NAME VARCHAR ( 10 ) COMPRESS SYSTEM DEFAULT ,
NOTE VARCHAR ( 100 ) COMPRESS SYSTEM DEFAULT
) IN TABLESPACE1 VALUE COMPRESSION
可以使用 DEACTIVATE VALUE COMPRESSION 来指定表将不再对表中数据使用节省空间技术。
如果使用DEACTIVATE VALUE COMPRESSION,这将显式禁用与该表中的列相关联的
所有 COMPRESS SYSTEM DEFAULT 选项。
DB2 ALTER TABLE TEST3 ALTER COLUMN ID COMPRESS OFF
DB2 ALTER TABLE TEST3 DEACTIVATE VALUE COMPRESSION
DB2 ALTER TABLE TEST3 ACTIVATE VALUE COMPRESSION
DB2 ALTER TABLE TEST3 ALTER id COMPRESS SYSTEM DEFAULT
创建临时表:
DECLARE GLOBAL TEMPORARY TABLE gbl_temp
LIKE empltabl
ON COMMIT DELETE ROWS
NOT LOGGED
IN usr_tbsp
更改列:
ALTER TABLE SALES ADD COLUMN SOLD_QTY SMALLINT NOT NULL DEFAULT 0
ALTER TABLE SALES DROP COLUMN SOLD_QTY
修改表列的长度:
ALTER TABLE <table_name> ALTER COLUMN <column_name> <modification_type>
ALTER TABLE t1 ALTER COLUMN colnam1 SET DATA TYPE VARCHAR(4000)
ALTER TABLE t1 ALTER COLUMN colnamt1 ADD SCOPE typtab1
ALTER TABLE <table_name> ALTER COLUMN <column_name> SET DEFAULT ’new_default_value’
ALTER TABLE t1 ALTER COLUMN colnam1 SET DEFAULT ’123’
重命名表rename:
显示表: db2 list tables
修改表名:db2 rename b to a
删除表:DROP TABLE <table_name>
----------------------
约束:
NOT NULL :未知状态
ALTER TABLE EMPLOYEE ADD CONSTRAINT NEWID UNIQUE(EMPNO,HIREDATE)
ALTER TABLE <name> ADD CONSTRAINT <column_name> PRIMARY KEY <column_name>
ALTER TABLE EMPLOYEE ADD CONSTRAINT REVENUE CHECK (SALARY + COMM > 25000)
ALTER TABLE <name> ADD CONSTRAINT <column_name> FOREIGN KEY <column_name> ON DELETE <action_type> ON UPDATE <action_type>
ON UPDATE:NO ACTION RESTRICT
ON DELETE:NO ACTION RESTRICT CASCADE SET NULL
删除约束:
ALTER TABLE <table-name> DROP UNIQUE <constraint-name>
ALTER TABLE <table-name> DROP PRIMARY KEY
ALTER TABLE <table_name> DROP CHECK <check_constraint_name>
ALTER TABLE <table-name> DROP FOREIGN KEY <foreign_key_name>
索引:
CREATE UNIQUE INDEX EMP_IX ON EMPLOYEE(EMPNO)
RENAME INDEX <source index name> TO <target index name>
DROP INDEX <index_name>
db2 list node directory(查看存在的节点名)