Skip to main content
Server Fault

Return to Answer

added 22 characters in body
Source Link
user9517
user9517

Your problem maybe solved by now, but for all the others with the same problem: I can offer a solution for getting the max_id +1 for all tables. The only restriction is, that the source column has to be a autoincrement column (GENERATED= 'D'). The resultset can be executed via clp or other query tools.

SELECT 'alter table ' || rtrim(TABSCHEMA) || '.' || TABNAME || ' alter column ' || COLNAME || ' restart with ' || cast((case when (trim ((replace ( HIGH2KEY, '+' , '' )))) = '' then 0 else integer(trim ((replace ( HIGH2KEY, '+' , '' ))))+2 end) as varchar(20)) || ' ;' FROM SYSCAT.COLUMNS WHERE GENERATED = 'D'

SELECT 'alter table ' || rtrim(TABSCHEMA) || '.' || TABNAME || ' alter column ' || COLNAME || ' restart with ' || cast((case when (trim ((replace ( HIGH2KEY, '+' , '' )))) = '' then 0 else integer(trim ((replace ( HIGH2KEY, '+' , '' ))))+2 end) as varchar(20)) || ' ;' FROM SYSCAT.COLUMNS WHERE GENERATED = 'D'

Your problem maybe solved by now, but for all the others with the same problem: I can offer a solution for getting the max_id +1 for all tables. The only restriction is, that the source column has to be a autoincrement column (GENERATED= 'D'). The resultset can be executed via clp or other query tools.

SELECT 'alter table ' || rtrim(TABSCHEMA) || '.' || TABNAME || ' alter column ' || COLNAME || ' restart with ' || cast((case when (trim ((replace ( HIGH2KEY, '+' , '' )))) = '' then 0 else integer(trim ((replace ( HIGH2KEY, '+' , '' ))))+2 end) as varchar(20)) || ' ;' FROM SYSCAT.COLUMNS WHERE GENERATED = 'D'

Your problem maybe solved by now, but for all the others with the same problem: I can offer a solution for getting the max_id +1 for all tables. The only restriction is, that the source column has to be a autoincrement column (GENERATED= 'D'). The resultset can be executed via clp or other query tools.

SELECT 'alter table ' || rtrim(TABSCHEMA) || '.' || TABNAME || ' alter column ' || COLNAME || ' restart with ' || cast((case when (trim ((replace ( HIGH2KEY, '+' , '' )))) = '' then 0 else integer(trim ((replace ( HIGH2KEY, '+' , '' ))))+2 end) as varchar(20)) || ' ;' FROM SYSCAT.COLUMNS WHERE GENERATED = 'D'
Source Link
antih
antih
  • 11
  • 1

Your problem maybe solved by now, but for all the others with the same problem: I can offer a solution for getting the max_id +1 for all tables. The only restriction is, that the source column has to be a autoincrement column (GENERATED= 'D'). The resultset can be executed via clp or other query tools.

SELECT 'alter table ' || rtrim(TABSCHEMA) || '.' || TABNAME || ' alter column ' || COLNAME || ' restart with ' || cast((case when (trim ((replace ( HIGH2KEY, '+' , '' )))) = '' then 0 else integer(trim ((replace ( HIGH2KEY, '+' , '' ))))+2 end) as varchar(20)) || ' ;' FROM SYSCAT.COLUMNS WHERE GENERATED = 'D'