db2dual_DB2常用sql命令

DB2 清除数据库序列缓存

alter sequence wfr.wfr_bill_histories_s  nocache;

创建清空所有表数据脚本

select 'alter table '||RTRIM(TABSCHEMA)||'.'||TABNAME||' activate not logged initially with empty table;' from syscat.tables where type = 'T' AND TABSCHEMA not in ('SYSIBM  ','SYSTOOLS');

生成删除所有外键的语句

SELECT

'ALTER TABLE '||tabschema||'.'|| SUBSTR(TABNAME,1,50)||

' DROP FOREIGN KEY ' || CONSTNAME || ';'

FROM

SYSCAT.REFERENCES

;

产生生成所有外键的SQL语句

SELECT

'ALTER TABLE '||tabschema||'.'|| SUBSTR(TABNAME,1,50)||

'ADD CONSTRAINT ' || CONSTNAME||

'FOREIGN KEY (' || substr(FK_COLNAMES,1,50) || ')'||

'REFERENCES '||tabschema||'.' || SUBSTR(REFTABNAME,1,17)||

' ON DELETE ' ||

case deleterule

when 'A' then 'NO ACTION'

when 'C' then 'CASCADE'

when 'N' then 'SET NULL'

when 'R' then 'RESTRICT'

end ||

' ON UPDATE ' ||

case updaterule

when 'A' then 'NO ACTION'

when 'R' then 'RESTRICT'

end

||';'

FROM

SYSCAT.REFERENCES

;

产生删除所有表的SQL语句

select 'DROP TABLE '||rtrim(tabschema)||'.'||t.TABNAME||' ;'

from syscat.TABLES t

where tabschema like '%CMBBCD%' and t.TYPE='T'

DB2查询序列的三种方式

1.

select nextval for seqName from dual

2.

select next value for seqName from dual

3.

select seqName.nextval from dual