VERY URGENT - need to set up alter statements

prithvi_raj · #1 (**permalink**) 07-20-04, 21:08

Hi,
I am trying to generate a sql which sets the values of identity columns with the max value +1 of the column.

The statement looks like this.

Alter table alter column col_name ( select max(col_name)+1 from table );

This kind of statements i have to generate for 4 schemas.
Can any one give me one example of how do i generate this one.
I have got problems in generating this.

Its UDB8.1 fp5 , sol os..

sathyaram_s · #2 (**permalink**) 07-20-04, 21:29

SQL does not support this type of operation ...

From the command prompt you may issue
db2 "alter table tid alter column i restart with `db2 -x select max\(i\) from tid` "

where tid is the tablename and i is the identity column name ...

HTH

sathyaram

prithvi_raj · #3 (**permalink**) 07-20-04, 21:42

Sathya , i need to generate it for all the tables for 4 schemas.
Can you give me me more detailed example , which i can directly run at command prompt.

sathyaram_s · #4 (**permalink**) 07-20-04, 21:56

Code:

db2 -x "select rtrim(tabschema)||'.'||rtrim(tabname) ,colname from syscat.columns where identity='Y'" |  while read tname tcol
do
db2 -v "alter table $tname alter column $tcol restart with `db2 -x select coalesce\(max\($tcol\),0\)+1 from $tname`"
done

Hope this helps

Cheers
Sathyaram

Internet Services

Web Development

Digital Marketing

Consumer Tech