Solved

DB2 Index maintnance

Posted on 2013-01-23
4
517 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

622 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