Parameter
dbs/db4/parallel_alter_tables
Short text
Allow ALTER TABLE while INSERTS occur in parallel
Parameter Description
See SAP Note 960392 for full details. The following is an excerpt from the SAP note.
ALTER TABLE may require exclusive locks on a table. Continual activity on the table may starve the ALTER TABLE request. For example, ALTER activity may be frequent on partitioned tables in which partitions are added and dropped.
The Parallel Alter feature actively monitors a definable set of tables that need coordination; then intelligently schedules read, write and alter operations to prevent alter starvation.
Setup
• Specify the profile parameter in the DEFAULT.PFL (see below)
• End all application server instances of the SAP system
• Delete SQL packages
Building the Profile Parameter
Note: Allow us to help you!
If you are considering using this feature, feel free to open a CSN message to the BC-DB-DB4 queue and we’ll work with you to build an appropriate monitoring string.
The table(s) that need to be coordinated by must be specified via profile parameter in
/usr/sap/[sid]/sys/profile/DEFAULT.PFL
The profile parameter has the following format:
dbs/db4/parallel_alter_tables = [lockgroup] = [tablename], etc..
Where: [/tablename][tablename] is the name of the table or view to be monitored for coordination. [/tablename][tablename] can contain wildcards ? and *. Where ? matches any one single character, and * matches many. LeTteR cAsE iS iGnOrEd.
Where: [lockgroup] is an arbitrary name that specifies the name of the lock. Multiple tables or views that resolve to the same [/lockgroup][lockgroup] are coordinated together. [/lockgroup][lockgroup] can contain variables %n that correspond to the respective wildcard in [tablename].
Example:
dbs/db4/parallel_alter_tables = MYLOCK=MY*, YOURLOCK=YOUR*
In this example, the parameter would coordinate all of “MY” tables as one group, and all of “YOUR” tables as another.
Example:
dbs/db4/parallel_alter_tables = Lock_%0 = ?*
Here, the parameter value would coordinate all tables on the system, using a [lockgroup] equal to the first character of every table. So tables SFLIGHT and SVERS would both resolve to Lock_S.
Table views complicate the specification. Because the LIB_DBSL only “sees” SQL text, views that reference “coordinated tables” must also be listed in the profile parameter.
dbs/db4/parallel_alter_tables = LOCK_%0=*TABLE, LOCK_%0=*VIEW
Would put all tables and views that start with the same prefix and that end either on TABLE or on VIEW into the same lock group. So MYAPPTABLE and MYAPPVIEW are coordinated together because they have a lock name of LOCK_MYAPP.
When active, the developer trace shows (for example):
C parallel_alter_tables = LOCK_%0=*TABLE C = LOCK_%0=*VIEW
Application Area
Database
Default Value
BLANK
Who is permitted to make changes?
DB and or BW administrator
Database System Restrictions
The parameter is only evaluated on DB2 for IBM i.
Are other parameters affected or dependent?
dbs/db4/parallel_alter_instance
Values allowed
A string according to the syntax in SAP Note 960392.
