Q:

subquery within alter table in db2

I want to run subquery within ALTER TABLE command in DB2.
ALTER TABLE user ALTER column userId SET GENERATED ALWAYS AS IDENTITY 
                                                     (start with 2646)

The above query works fine. But I want to give the start with value from query itself like below.
ALTER TABLE user ALTER column userId SET GENERATED ALWAYS AS IDENTITY 
                           (start with (SELECT MAX(userId) FROM user))

I tried to achieve this using functions and stored procedures. But the problem is table name should be specified in both. I want to alter table query for nearly 40 tables. 
CREATE FUNCTION findMax (tablename VARCHAR(64), columnname VARCHAR(255))
                          RETURNS INTEGER 
                          RETURN SELECT MAX(columnname)
                                         FROM tablename

A:

I have done this before by using sql scripts and doing multiple passes against the database.
You can do the same if you are using your findMax function if you have SQL like this:
select 'findMax( ' || tabname || ' , ' || colname || ')' 
from syscat.columns where identity = 'Y'

Simply redirect the output of that sql to a text file and then run the text file as sql.
You can do this directly with the ALTER TABLE too but I think you'd need three passes.

