Parameter
dbs/db4/parallel_alter_tables

Short text
Allow ALTER TABLE while INSERTS occur in parallel
See SAP Note 960392 for full details. The following is an excerpt fromthe SAP note.
ALTER TABLE may require exclusive locks on a TABLE. Continual activityon the TABLE may starve the ALTER TABLE request. For example, ALTERactivity may be frequent on Partitioned Tables in which partitions areadded and dropped.
The Parallel Alter feature actively monitors a definable set of tablesthat need coordination; then intelligently schedules read, write andalter 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 OSSmessage to the BC-DB-DB4 queue and we'll work with you to build anappropriate monitoring string.
The table(s) that need to be coordinated by must be specified viaprofile 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<(>><)> is the name of the table or view to be
monitored for coordination. <(><<)>TableName<(>><)> can containwildcards ? and
*. Where ? matches any ONE single character, and * matches many. LeTeRcAsE 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<(>><)> are coordinated together.
<(><<)>LockGroup<(>><)> can contain variables %n that correspond to therespective wildcard in <(><<)>TableName<(>><)>.
Example:

• dbs/db4/parallel_alter_tables = MYLOCK=MY*, YOURLOCK=YOUR*

Would coordinate all of "MY" tables as one group, and all of "YOUR"tables as another.
Example:

• dbs/db4/parallel_alter_tables = Lock_%0 = ?*

Would coordinate all tables on the system, using a
<(><<)>LockGroup<(>><)> equal to the first character of every table. Sotables 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 alsobe 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 thatend either on TABLE or on VIEW into the same lock group. So MYAPPTABLEand MYAPPVIEW are coordinated together because they have a lock name ofLOCK_MYAPP.
When active, the developer trace shows (for example):
C parallel_alter_tables = LOCK_%0=*TABLE C = LOCK_%0=*VIEW

Work area
Database

Default value
''

Limitation for db
DB2/400
dbs/db4/parallel_alter_instance