Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2012
    Posts
    82

    Unanswered: DB2 automated scripting BASH

    I have a schema which has over 200 tables. I need to create auto increment on all of them.


    THey already have data so I need to restart all ID with MAX(ID)+1 .

    I created a bash script to do that but the problem is bash does not have a way to handle data.

    My question is how can I handle data from a query in bash..

    what I am trying to achieve pseudo code

    array[]= select tabname from syscat.tbles where required schema

    while (array[i]!=null)
    {
    max_ID = select max(id)+1 from schema.array[i]

    alter table array[i] alter column id set identity start from max_id

    i++
    }


    it is a rough code.. I have the bash code but I have to input table manually as I don't know how to handle output.



    db2 "select max(ID)+1 from schema."$source_tb"" > /tmp/tmp_result.txt


    echo -e "\n"

    rowsCountInt=`cat /tmp/tmp_result.txt | head -4 | tail -1 | sed 's/[ \t]//g'`



    echo $rowsCountInt

    echo -e "\n"

    db2 "ALTER TABLE schema."$source_tb" ALTER COLUMN ID SET GENERATED ALWAYS AS IDENTITY (START WITH "$rowsCountInt")"

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    we use something like :

    S_sql="select t.name,c.name from sysibm.systables t,sysibm.syscolumns c where c.identity='Y' and t.creator='${SCHEMA}' and t.name=c.tbname and t.creator=c.tbcreator and c.colno=0 and t.type='T' "
    db2 -x "$S_sql" | {
    while read TSREC
    do
    set - $TSREC
    Table=$1
    Cname=$2
    # -----------------------------------------------------
    # get highest value
    # ----------------------------------------------------
    High=`db2 -x "select nvl(max(${Cname}+1),1) from ${SCHEMA}.${Table}" `
    # -----------------------------------------------------------------------
    # - create alter statements = alt_ge.sql ----------------------------------
    # -----------------------------------------------------------------------
    echo "alter table ${SCHEMA}.$Table alter column ${Cname} restart with ${High} ; " >> $SQLDIR/${SCHEMA}.reset_gen
    done
    }
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

Posting Permissions

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