Solved

DB2 Index maintnance

Posted on 2013-01-23
4
516 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 18

Expert Comment

by:Dave Ford
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:
Kent Olsen 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

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
Suggested Courses

759 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