20130110

One line command to restart IDENTITY in all tables

One single line command to restart all IDENTITY columns genrate by default in all tables in SCHEMA 'db2inst1':

db2 -x "select 'SELECT ''ALTER TABLE DB2WMS.'||T.TABNAME||' ALTER COLUMN ID RESTART WITH ''||TO_CHAR(CASE WHEN MAX(ID) >= 500 THEN MAX(ID) + 1 ELSE 500 END) FROM '||T.TABNAME FROM SYSCAT.TABLES T,SYSCAT.COLUMNS C WHERE T.TABNAME = C.TABNAME AND T.TABSCHEMA = C.TABSCHEMA AND T.TABSCHEMA = 'db2inst1' AND T.TYPE = 'T' AND C.GENERATED ='D'" | db2 -x +p | db2 -v +p

沒有留言: