Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2010
    Posts
    12

    Unanswered: Manual Reorg Scripts Failing

    It looks like I'm having multiple problems which doesn't help my overall opinion of DB2...

    Currently, I created a batch script that manually reorgs all tables for all databases. I do this because the systems we house are nearly a Data Warehouse, we load the data in Saturday night then do not access it until Monday morning. So in essence when the data is done being loaded there might not be quite enough change to trigger an auto reorg, just in case i run it manually Sunday morning to clean anything up in prep for Monday morning.

    Having said all that, directly below is the script i use to enumerate all tables for a database then kick off a reorg

    Code:
    set MYINSTANCE=%1
    set MYDATABASE=%2
    Set SchemaPat='%3%%'
    if %3'' == '' set SchemaPat='%%_%%'
    if %2'' == '' GOTO ERROR
    REM @echo on  
    db2 attach to %MYINSTANCE%
    db2 connect to %MYDATABASE%  
    @ECHO -- Begin Table and Index Reorg > C:\Scripts\Commands\ReorgScript_%MYDATABASE%.ddl
    @echo attach to %MYINSTANCE%  ; >> C:\Scripts\Commands\ReorgScript_%MYDATABASE%.ddl
    @echo connect to %MYDATABASE%  ; >> C:\Scripts\Commands\ReorgScript_%MYDATABASE%.ddl
    @ECHO OFF
    set TablePat='%%_++++++++++'
    
     db2 -x "select 'REORG INDEXES ALL FOR TABLE '||TRIM(TRAILING FROM T.tabschema)||'.'||TRIM(TRAILING FROM T.tabname)||';' from syscat.tables T where tabschema like %SchemaPat%  and type='T' AND EXISTS (SELECT 1 FROM SYSCAT.INDEXES I WHERE I.TABNAME=T.TABNAME AND I.TABSCHEMA=T.TABSCHEMA) union all select 'REORG TABLE '||TRIM(TRAILING FROM tabschema)||'.'||TRIM(TRAILING FROM tabname)||';' from syscat.tables where tabschema like %SchemaPat%  and type='T'  and TRIM(TRANSLATE(tabname, '++++++++++', '0123456789')) not  LIKE %TablePat% order by 1" >> C:\Scripts\Commands\ReorgScript_%MYDATABASE%.ddl
     @echo  connect reset; >> C:\Scripts\Commands\ReorgScript_%MYDATABASE%.ddl
     @echo  terminate; >> C:\Scripts\Commands\ReorgScript_%MYDATABASE%.ddl
     DB2 CONNECT RESET
    db2 -tvf C:\Scripts\Commands\ReorgScript_%MYDATABASE%.ddl  -l C:\Logs\ReorgScript_%MYDATABASE%.log
    
     db2 terminate
    
    @echo off
    GOTO END
    :ERROR
    @echo.
    @echo ERROR: A Parameter is missing
    @echo    Command Syntax is :   %0  InstanceName DatabaseName SchemaName
    @echo    SchemaName is case sensitive. If schema name is not supplied, REORG will be executed for All schemas.
    @echo.
    :END
    I pass the database name to the script using another batch script that has the following format

    Code:
    start "" /wait C:\Scripts\Auto_reorg2.bat DB2 Database1
    start "" /wait C:\Scripts\Auto_reorg2.bat DB2 Database2
    etc...
    The script will kick off a reorg on the specified database then wait until it completes to kick off the next. This all works fine.

    The problem I have been running into is:
    Recently mid way through the reorg i hit the following two errors in succession

    Code:
    08/01/2012 15:05:46 REORG TABLE DBO.Table1_2010052003080443872EC5875A
    08/01/2012 15:05:46 SQL2216N  SQL error "-1224" occurred while reorganizing a database table or its indexes.
    
    08/01/2012 15:05:46 REORG TABLE DBO.Table2_2010052003233395069BC3E166
    08/01/2012 15:05:46 SQL2216N  SQL error "-900" occurred while reorganizing a database table or its indexes.
    Then every table thereafter gives me the same -900 error.

    Ive looked through the DB2Diag.log file and cannot find anything useful except for some dump files being created during that time. I looked in the dump files but cannot find anything in the txt files and am unsure about how to open the actual dump files.

    The above problem just occurred for the first time today.

    The Second problem ive been having ever since trying to get this entire process to work is..

    When the first database is done with a reorg (normally successfully) it goes to the next database. The log shows the following errors for the second database.

    Code:
    08/01/2012 15:05:50 attach to DB2  
    08/01/2012 15:05:50 SQL1032N  No start database manager command was issued.  SQLSTATE=57019
    
    08/01/2012 15:05:50 connect to Database2  
    08/01/2012 15:05:50 SQL1032N  No start database manager command was issued.  SQLSTATE=57019
    
    08/01/2012 15:05:50 SQL1024N  A database connection does not exist.  SQLSTATE=08003 connect reset
    08/01/2012 15:05:51 DB21034E  The command was processed as an SQL statement because it was not a valid Command Line Processor command.  During SQL processing it returned:
    08/01/2012 15:05:51 SQL1024N  A database connection does not exist.  SQLSTATE=08003
    
    08/01/2012 15:05:51 terminate
    08/01/2012 15:05:51 DB20000I  The TERMINATE command completed successfully.
    It may do this same thing for a couple databases, then on say Database4 it begins to work correctly, reorging every table without problem, then say 5 more databases down the road i hit the error again.

    Its very random which database this occurs on, as if i run the script again it may fail on database 6 and 7, then not again until database 22 and 27.

    I'm not sure why it requires a "Database Manager Start Command" randomly when some work just fine without it.

    I appreciate any help on this as doing any "easy" debugging on DB2 is near impossible.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Please state DB2 version and OS.

    The SQL error -1224 is:

    SQL1224N The database manager is not able to accept new requests, has
    terminated all requests in progress, or has terminated the
    specified request because of an error or a forced interrupt.

    The -900 means that your connection was lost.

    Look in the db2diag.log file to see if anything is there.

    Andy

  3. #3
    Join Date
    Aug 2010
    Posts
    12
    DB2 V9.5 FP2 - Windows 2008 R2

    Ive looked through the DB2Diag.log file but only saw where it was creating a dump file, attached is an excerpt from the log during the time of the errors.

    Maybe I'm missing something so who knows.
    Attached Files Attached Files

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What does the script that is built to do the reorgs look like? How many tables are in it?

    Andy

  5. #5
    Join Date
    Aug 2010
    Posts
    12
    The file named ReorgScript_Database1.txt is the file generated for the first database that normally runs successfully but this time generated the errors half way through.

    The file named ReorgScript_Database2.txt is the file generated for the first database that did not try at all to do a reorg.
    Attached Files Attached Files

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by ServerGuy View Post
    doesn't help my overall opinion of DB2...
    I'm sure there are many people who care about your opinion of DB2.


    Quote Originally Posted by ServerGuy View Post
    I'm not sure why it requires a "Database Manager Start Command" randomly when some work just fine without it.
    Code:
    08/01/2012 15:05:46 REORG TABLE DBO.Table1_2010052003080443872EC5875A
    08/01/2012 15:05:46 SQL2216N  SQL error "-1224" occurred while reorganizing a database table or its indexes.
    This probably means that an attempt to reorg DBO.Table1_2010052003080443872EC5875A causes the instance to crash. After some time the instance is restarted, may be by the Windows service manager, until then all attempts to connect to any of its databases fail.

    Leaving alone the fact that such a massive reorganization of indexes and tables makes little sense, you may want to check the table in question (DBO.Table1_2010052003080443872EC5875A) with db2dart to see if it is corrupted.

    In the diagnostic log there are several references to problems with indexes in tablespace 4; you may also want to verify that the underlying storage is OK.

    I could also suggest you contact IBM software support, but they'll probably tell you first to upgrade to the latest fixpack. You're currently 7 fixpacks (3 years) behind.

    Quote Originally Posted by ServerGuy View Post
    doing any "easy" debugging on DB2 is near impossible.
    I'm sure it will change once you find your way around.
    ---
    "It does not work" is not a valid problem statement.

  7. #7
    Join Date
    Jun 2007
    Location
    germany
    Posts
    155
    n_i,
    thx for a very good example of a measured reply.
    Dick Brenholtz, Ami in Deutschland

  8. #8
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    In addition to the advice given by n_i you should contact the author of the script that generates the reorg command script and request some changes.

    The script (and the script it generates) has no error checking whatsoever.
    It is trivial to implement error checking even in legacy NT/CMD scripting language.
    You might also request that the script assert any pre-requisites before running db2 commands.
    You might also parse the output of db2reorgchk to determine which tables need a reorg and reorg only those.

    I would not let a script with those omissions near a production system.

Posting Permissions

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