Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 522
  • Last Modified:

DB2 Index maintnance

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
atorex
Asked:
atorex
  • 2
1 Solution
 
Dave FordSoftware Developer / Database AdministratorCommented:
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
 
atorexAuthor Commented:
so sorry, that would have helped I'm running it on Linux SLES 11 the DB2 version is V9.7
0
 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
atorexAuthor Commented:
Thanks, this should help.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now