Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

DB2 Index maintnance

Posted on 2013-01-23
4
Medium Priority
?
519 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 46

Accepted Solution

by:
Kent Olsen earned 2000 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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

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 (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…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

722 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