DB2 常用命令和表挂起的解决命令

DB2 常用命令

1. 表重组

当表挂起可用此命令

CALL SYSPROC.ADMIN_CMD('REORG TABLE     FUBB.TBBJPA3'); 

2. 表状态错误

db2 "set integrity for iplat4j.ACT_RE_model immediate checked" ;

3. 删除索引

DROP INDEX "FUBB"."TBBRE04_PK1" ;

4. 创建索引

create index TBBRE04_PK1 on TBBRE04(PROJECT_NO);

5. 添加字段

alter table XS_USER add SEX VARCHAR(1)

6. 添加唯一约束

alter table tabname add unique(colname) 

7. 修改字段类型

alter table TBBJPA3 alter column ORG_TYPE set data type DECIMAL(1);

8. 修改字段长度

alter table TBBJPP1 alter column REC_CREATOR_ID set data type VARCHAR(32)

9. 删除表字段

alter table TBBJPA3 drop column ORG_TYPE

10. 设置字段默认值

alter table TBBJPA3 alter column ORG_TYPE set default '1'

11. 添加不为空的字段并设置默认值

alter table [table_name] alter column [column_name] set default current date;

alter table TBBJPA3 add column DEALER_NAME VARCHAR(100) not null with default 1 

12. 添加表描述

comment on table [表名]  is  '描述'

13. 添加字段描述

comment  on  column  TBBJPA3.DEALER_NAME   is  'xxx';

14. 查询所有表所有列

SELECT TABSCHEMA,TABNAME,COLNAME FROM SYSCAT.COLUMNS WHERE COLNAME = 'APPROVAL_RESULT'
GROUP  BY TABNAME,COLNAME ORDER BY TABNAME ;

15. 创建表

CREATE TABLE TBBRE00 (REC_CREATOR VARCHAR(32) DEFAULT ' ' NOT NULL, REC_CREATOR_NAME VARCHAR(32) DEFAULT ' ' NOT NULL, REC_CREATE_TIME VARCHAR(14) DEFAULT ' ' NOT NULL, REC_REVISOR VARCHAR(32) DEFAULT ' ' NOT NULL, REC_REVISOR_NAME VARCHAR(32) DEFAULT ' ' NOT NULL, REC_REVISE_TIME VARCHAR(14) DEFAULT ' ' NOT NULL, ROWGUID VARCHAR(64) DEFAULT ' ' NOT NULL, DOC_ID VARCHAR(64) DEFAULT ' ' NOT NULL, PROCESS_INSTANCE_ID VARCHAR(64) DEFAULT ' ' NOT NULL, REMIND_USER_ID VARCHAR(32) NOT NULL, BUSINESS_ID VARCHAR(64), SUBJECT VARCHAR(1000), OWNER_ID VARCHAR(64), OWNER_NAME VARCHAR(100), OVERTIME_DAY VARCHAR(10) NOT NULL, REMIND_TIME VARCHAR(8), EXT1 VARCHAR(50), EXT2 VARCHAR(100), EXT3 VARCHAR(200), CONSTRAINT TBBRE00_PK PRIMARY KEY (ROWGUID));
COMMENT ON TABLE TBBRE00 IS '工作流审批时限提醒';
COMMENT ON COLUMN TBBRE00.REC_CREATOR IS '记录创建人';
COMMENT ON COLUMN TBBRE00.REC_CREATOR_NAME IS '记录创建人姓名';
COMMENT ON COLUMN TBBRE00.REC_CREATE_TIME IS '记录创建时间';
COMMENT ON COLUMN TBBRE00.REC_REVISOR IS '记录修改人';
COMMENT ON COLUMN TBBRE00.REC_REVISOR_NAME IS '记录修改人姓名';
COMMENT ON COLUMN TBBRE00.REC_REVISE_TIME IS '记录修改时间';
COMMENT ON COLUMN TBBRE00.ROWGUID IS 'ROWGUID';
COMMENT ON COLUMN TBBRE00.DOC_ID IS '文档ID';
COMMENT ON COLUMN TBBRE00.PROCESS_INSTANCE_ID IS '工作流实例ID';
COMMENT ON COLUMN TBBRE00.REMIND_USER_ID IS '提醒用户id';
COMMENT ON COLUMN TBBRE00.BUSINESS_ID IS '提醒业务id';
COMMENT ON COLUMN TBBRE00.SUBJECT IS '提醒主题';
COMMENT ON COLUMN TBBRE00.OWNER_ID IS '负责人id';
COMMENT ON COLUMN TBBRE00.OWNER_NAME IS '负责人姓名';
COMMENT ON COLUMN TBBRE00.OVERTIME_DAY IS '超时天数';
COMMENT ON COLUMN TBBRE00.REMIND_TIME IS '提醒时间';
COMMENT ON COLUMN TBBRE00.EXT1 IS '备用字段1';
COMMENT ON COLUMN TBBRE00.EXT2 IS '备用字段2';
COMMENT ON COLUMN TBBRE00.EXT3 IS '备用字段3';

16. 置空表

TRUNCATE TABLE "JTBP"."TBBRE00" immediate

17. 删除表

DROP TABLE TBBRE00;

18. 装入暂挂"状态 DB2 SQL Error: SQLCODE=-668, SQLSTATE=57016错误解决方法

 load from /dev/null of del terminate into  TBBJPA3

这个命令来解挂

19. 查看表状态

load query table <TBBJPA3>  db2 "load query table <TBBJPA3>" 
db2 load query  FUBB.TBBJPA3 
reorg table <TBBJPA3>