Search

Ramchandra's Weblog

Just ……everything about DB2 LUW

Month

April 2009

Script to Reset Identity Columns

db2 connect to <database>
db2 QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS
db2 “select ‘select ”ALTER TABLE ‘|| rtrim(TBCREATOR) ||’.’||TBNAME ||’ ALTER COLUMN ‘||NAME ||’ RESTART WITH ”’ ||’||cast( max(‘||NAME||’)+1 as char(15)) ||”;” from ‘|| rtrim(TBCREATOR) ||’.’||TBNAME ||’;’ from   SYSIBM.SYSCOLUMNS     where IDENTITY=’Y'” > temp_identity1.sql
db2 -tvf temp_identity1.sql|grep ‘ALTER TABLE’  >  temp_identity2.sql
cat temp_identity2.sql|grep ‘;’$ > temp_identity3.sql
db2 -tvf temp_identity3.sql > temp_identity4.sql
db2 UNQUIESCE DATABASE

Advertisements

SQL Server DBA database management checklist

SQL Server DBA database management checklist

SQL Server Best Practices

SQL Server – Best Practices

Blog at WordPress.com.

Up ↑