Solved

DB2 Index maintnance

Posted on 2013-01-23
4
501 Views
Last Modified: 2013-01-23
Is there a quick DB2 script for re-indexing, I run something like the below for the SQL servers and I just got a DB2 database i need to re index.

DECLARE @Database VARCHAR(255)  
DECLARE @Table VARCHAR(255)  
DECLARE @cmd NVARCHAR(500)  
DECLARE @fillfactor INT

SET @fillfactor = 90

DECLARE DatabaseCursor CURSOR FOR  
SELECT name FROM MASTER.dbo.sysdatabases  
WHERE name NOT IN ('master','msdb','tempdb','model','distribution','alerts','reportserver','reportservertempdb')  
ORDER BY 1  

OPEN DatabaseCursor  

FETCH NEXT FROM DatabaseCursor INTO @Database  
WHILE @@FETCH_STATUS = 0  
BEGIN  

   SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' +
  table_name + '']'' as tableName FROM ' + @Database + '.INFORMATION_SCHEMA.TABLES
  WHERE table_type = ''BASE TABLE'''  

   -- create table cursor  
   EXEC (@cmd)  
   OPEN TableCursor  

   FETCH NEXT FROM TableCursor INTO @Table  
   WHILE @@FETCH_STATUS = 0  
   BEGIN  

       IF (@@MICROSOFTVERSION / POWER(2, 24) >= 9)
       BEGIN
           -- SQL 2005 or higher command
           SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
           EXEC (@cmd)
       END
0
Comment
Question by:atorex
  • 2
4 Comments
 
LVL 18

Expert Comment

by:daveslash
ID: 38810738
Hi atorex,

On which "platform" are you running DB2? (Windows, Linux, mainframe, AS/400, etc)

That will (at least partially) determine the answer to your question.

Regards,
DaveSlash
0
 

Author Comment

by:atorex
ID: 38810755
so sorry, that would have helped I'm running it on Linux SLES 11 the DB2 version is V9.7
0
 
LVL 45

Accepted Solution

by:
Kdo earned 500 total points
ID: 38810815
Hi atorex,

You'll want to run the REORG statement from the command line processor.  It's got quite a few options, but you can deal with indexes "in bulk" without having to loop through the system tables.

REORG INDEXES ALL FOR TABLE {mytable}

The full command description is here:


REORG {TABLE table-name Table-Clause | {INDEXES ALL FOR TABLE table-name |
INDEX index-name [FOR TABLE table-name]} Index-Clause |
TABLE mdc-table-name RECLAIM EXTENTS Reclaim-Extents-Clause}
[Table-Partition-Clause] [On-DbPartitionNum-Clause]

Table-Clause:
  [INDEX index-name] [[ALLOW {READ | NO} ACCESS]
  [USE tablespace-name] [INDEXSCAN] [LONGLOBDATA [USE long-tablespace-name]]
  [KEEPDICTIONARY | RESETDICTIONARY]] |
  [INPLACE [ [ALLOW {WRITE | READ} ACCESS] [NOTRUNCATE TABLE]
  [START | RESUME] | {STOP | PAUSE} ]]

Index-Clause:
  [ALLOW {READ | NO | WRITE} ACCESS]
  [REBUILD | Space-Reclaim-Options]

Space-Reclaim-Options:
  [CLEANUP [ALL | PAGES]] [RECLAIM EXTENTS]

Reclaim-Extents-Clause:
  [ALLOW {WRITE | READ | NO} ACCESS]

Table-Partition-Clause:
  ON DATA PARTITION partition-name

On-DbPartitionNum-Clause:
  ON {{DBPARTITIONNUM | DBPARTITIONNUMS} (db-partition-number
  [TO  db-partition-number] , ... ) | ALL DBPARTITIONNUMS [EXCEPT
  {DBPARTITIONNUM | DBPARTITIONNUMS} (db-partition-number
  [TO db-partition-number] , ...)]}



Good Luck,
Kent
0
 

Author Closing Comment

by:atorex
ID: 38810839
Thanks, this should help.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now