Results 1 to 7 of 7
  1. #1
    Join Date
    May 2015
    Posts
    14

    Lightbulb Answered: Reorg all tables for each active database

    Hello Guys,

    I did a shell script to generate a dynamic reorg file for all tables but I'm facing problems. The script finished with success but it's executing for all databases inside the file. When I see each logfile for example:
    1- db2wfti2_reorg_DATABASE1.log(In this file the reorg try to execute in a table who only exists in database 2...so the reorg don't execute "SQL2211N The specified table does not exist."
    2- db2wfti2_reorg_DATABASE1.log
    3- db2wfti2_reorg_DATABASE1.log


    Take a look in my script:

    DB2EXEC=/db2wfti2/home/inst/sqllib/bin/db2
    $DB2EXEC list active databases | grep "Database name" | awk '{print $4}' | while read DATABASE
    do
    REORGFILE=/db2wfti2/home/inst/scripts/REORG_$DATABASE"_"`date +"%Y%d%m%H%M%S"`.out
    $DB2EXEC -x "select 'reorg table',substr(rtrim(tabschema)||'.'||rtrim(tabname ),1,50),';'from syscat.tables where type = 'T' " > $REORGFILE
    LOGREORGFILE=/tmp/log/db2wfti2_reorg_$DATABASE.log
    echo $DATABASE"_"`date +"%Y%d%m%H%M%S"` >> $LOGREORGFILE
    $DB2EXEC connect to $DATABASE
    $DB2EXEC -tvf $REORGFILE >> $LOGREORGFILE
    done

    Could you help me how generate all reorg table for each active database without "error" as "SQL2211N The specified table does not exist."

  2. Best Answer
    Posted by tafster

    "I meant where is the connect to the db before this statement...
    $DB2EXEC -x "select 'reorg table',substr(rtrim(tabschema)||'.'||rtrim(tabname ),1,50),';'from syscat.tables where type = 'T' " > $REORGFILE"


  3. #2
    Join Date
    May 2012
    Location
    Canberra, Australia
    Posts
    176
    Provided Answers: 6
    A couple of things...
    Don't use this
    DB2EXEC=/db2wfti2/home/inst/sqllib/bin/db2
    Execute the db2profile for the instance e.g.
    . /db2wfti2/home/inst/sqllib/db2profile
    then you can use ........
    db2 list active databases
    db2 connect to $DATABASE
    db2 -tvf $REORGFILE

    You haven't connected to the database to perform this step
    select 'reorg table',substr(rtrim(tabschema)||'.'||rtrim(tabname ),1,50),';'from syscat.tables where type = 'T'
    and this only needs to be
    select 'reorg table '||rtrim(tabschema)||'.'||tabname||' ;' from syscat.tables where type = 'T'
    Andy

    "All parts should go together without forcing. You must remember that the parts you are reassembling were disassembled by you. Therefore, if you can’t get them together again, there must be a reason. By all means, do not use hammer.” — IBM maintenance manual, 1975 "

  4. #3
    Join Date
    May 2015
    Posts
    14
    I agree with you to use the profile, but my script is dynamic and I must to find all active databases. In my cases I have 3 active databasesw in an one instance. When you said that reorg table don't be need to connected to the database...I didn't get you.

    Can you explain me with more details?



    Quote Originally Posted by tafster View Post
    A couple of things...
    Don't use this
    DB2EXEC=/db2wfti2/home/inst/sqllib/bin/db2
    Execute the db2profile for the instance e.g.
    . /db2wfti2/home/inst/sqllib/db2profile
    then you can use ........
    db2 list active databases
    db2 connect to $DATABASE
    db2 -tvf $REORGFILE

    You haven't connected to the database to perform this step
    select 'reorg table',substr(rtrim(tabschema)||'.'||rtrim(tabname ),1,50),';'from syscat.tables where type = 'T'
    and this only needs to be
    select 'reorg table '||rtrim(tabschema)||'.'||tabname||' ;' from syscat.tables where type = 'T'

  5. #4
    Join Date
    May 2015
    Posts
    14

    One doubt - Reorg

    I agree with you to use the profile, but my script is dynamic and I must to find all active databases. In my cases I have 3 active databasesw in an one instance. When you said that reorg table don't be need to connected to the database...I didn't get you.

    Can you explain me with more details?



    Quote Originally Posted by tafster View Post
    A couple of things...
    Don't use this
    DB2EXEC=/db2wfti2/home/inst/sqllib/bin/db2
    Execute the db2profile for the instance e.g.
    . /db2wfti2/home/inst/sqllib/db2profile
    then you can use ........
    db2 list active databases
    db2 connect to $DATABASE
    db2 -tvf $REORGFILE

    You haven't connected to the database to perform this step
    select 'reorg table',substr(rtrim(tabschema)||'.'||rtrim(tabname ),1,50),';'from syscat.tables where type = 'T'
    and this only needs to be
    select 'reorg table '||rtrim(tabschema)||'.'||tabname||' ;' from syscat.tables where type = 'T'

  6. #5
    Join Date
    May 2012
    Location
    Canberra, Australia
    Posts
    176
    Provided Answers: 6
    I meant where is the connect to the db before this statement...
    $DB2EXEC -x "select 'reorg table',substr(rtrim(tabschema)||'.'||rtrim(tabname ),1,50),';'from syscat.tables where type = 'T' " > $REORGFILE
    Andy

    "All parts should go together without forcing. You must remember that the parts you are reassembling were disassembled by you. Therefore, if you can’t get them together again, there must be a reason. By all means, do not use hammer.” — IBM maintenance manual, 1975 "

  7. #6
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    some dba questions (for a non-dba) :
    is a reorg needed for all tables ? reorgchk can give you some hints
    offline reorg is being used.. while executing reorg : table is not available ?
    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

  8. #7
    Join Date
    May 2015
    Posts
    14

    Lightbulb Reorg OK

    I did this script and found what I wanted. The only change was about all databases...according to my rule we just need the all reorg tables for one Database who I know the name. After all is it possible to integrate this schipt with a procedure?? I was reading about the sp admin_task_add. Can you explain me more??

    Quote Originally Posted by tafster View Post
    I meant where is the connect to the db before this statement...
    $DB2EXEC -x "select 'reorg table',substr(rtrim(tabschema)||'.'||rtrim(tabname ),1,50),';'from syscat.tables where type = 'T' " > $REORGFILE
    Last edited by pimentelrj; 05-19-15 at 14:01.

Tags for this Thread

Posting Permissions

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