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

DB2常用sql命令

相关推荐