SAP Basis Tech Supports

Symptom
Long running CREATE INDEX statements when generating or changing an index on large tables (via transport or within DDIC).
Other terms
z/OS, DB2, DB2-z/OS, OS390, zos
index, create, ddic, se14, rebuild index, defer yes, create index
performance, activation, spam, saint, import
Reason and Prerequisites
Performance of CREATE INDEX statement
Solution

Implementation
To enable the automatic usage of REBUILD INDEX within the Data Dictionary proceed as follows:
A) Apply the following DBSL Patch Levels and Support Packages
Basis ReleasePatch LevelSupport Package620229SAPKB62065640229SAPKB64023700156SAPKB700167020SAPKB70202710100SAPKB71007
The DBSL patch info text is:
DB2-z/OS: Automatic REBUILD INDEX within DDIC
Note that the new functionality is part of all other Netweaver Enhancement Packages (7.01, 7.03, …, 7.11, …).
B) Ensure that the DSNUTILS stored procedures are set up as described in the Database Administration Guide.
General information
Once the REBUILD INDEX functionality is switched on (see section ‘Usage’ below) index creations triggered by DDIC activations, imports and SAINT/SPAM activities are handled as follows:
1. The index is created with attribute DEFER YES2. The utility REBUILD INDEX is called using the specified options.3. The output of the REBUILD INDEX utility is written to the developer trace (transaction ST11). Only in case of a problem it is partially visible within the activation log of the index. Therefore, always analyse the developer trace of work proces (dev_w*) if errors occur.
Usage
Once the relevant DBSL patch and the support package (see above) are applied to the system an additional section called ‘Automatic Rebuild Index’ appears within transaction SE14–>’Edit’–>’Storage parameters’.
It offers the following displays and buttons:
Row ‘System-wide default’
The first row serves to administrate the system-wide setting of the REBUILD INDEX handling.Display ‘USED/NOT USED’: Shows whether the system-wide usage of REBUILD INDEX is switched on or switched off.Button ‘OK’: Serves to switch on the system-wide usage of REBUILD INDEX for all index creations. If chosen a popup appears which lets you specify the REBUILD INDEX options.Button ‘CANCEL’: Serves to switch off the system-wide usage of the REBUILD INDEX feature.Button ‘Enforce system-wide’: Enforces the system-wide setting of REBUILD INDEX (switched on/off, options) for all indexes. All individual settings (see below) are switched off.Button ‘Options’: Serves to modify the system-wide REBUILD INDEX options.Button ‘INFO’: Offers a direct link to this SAP note.Row ‘This table’s indexes’
Within the second row individual REBUILD INDEX settings for the table displayed can be specified.Display ‘USED/NOT USED’: REBUILD INDEX is used/not used for all indexes of the table.Buttons ‘OK’ & ‘CANCEL’: Switches on/off the usage of REBUILD INDEX for this table’s indexesButton ‘Use default’: If chosen the individual table setting are over-written by the system-wide default (switched on/off, options).Button ‘Options’: Serves to modify the REBUILD INDEX options for this table’s indexes.