Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2004
    Posts
    36

    Angry Unanswered: VERY URGENT - need to set up alter statements

    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..

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Apr 2004
    Posts
    36
    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.

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •