DB2常用sql命令
DB2 清除数据库序列缓存alter sequence wfr.wfr_bill_histories_snocache;
创建清空所有表数据脚本
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
页:
[1]