DB2 clearing the database sequence cache
Alter sequence wfr.wfr_bill_histories_s NoCache;
Create a script that empties all table data
Select ' ALTER TABLE ' | | RTRIM (Tabschema) | | tabname| | ' Activate not the logged initially with empty table; ' from syscat.tables where type = ' T ' and Tabschema not in (' SYS IBM ', ' systools '); Generate statements that delete all foreign keys
SELECT
' ALTER TABLE ' | | tabschema| | '. ' | | SUBSTR (tabname,1,50) | |
' DROP FOREIGN KEY ' | | Constname | | ‘;‘
From
SYSCAT. REFERENCES
; Generate SQL statements that generate all foreign keys
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 the ' A ' then ' NO ACTION '
When ' C ' then ' CASCADE '
When ' N ' then ' SET NULL '
When ' R ' then ' RESTRICT '
End | |
' On UPDATE ' | |
Case UpdateRule
When the ' A ' then ' NO ACTION '
When ' R ' then ' RESTRICT '
End
| | '; '
From
SYSCAT. REFERENCES
; Generates an SQL statement that deletes all tables
Select ' DROP TABLE ' | | RTrim (Tabschema) | | t.tabname| | '; '
From Syscat. TABLES T
where Tabschema like '%cmbbcd% ' and t.type= ' t ' DB2 three ways to query the sequence
1.
Select Nextval for seqname from dual
2.
Select Next value for Seqname from dual
3.
Select Seqname.nextval from dual
DB2 Common SQL commands
