Adobe Campaign Classic
In most of the Adobe Campaign builds including the Gold Standard 9032 ones, you might encounter multiple issues when adding new columns to an existing schema.
The scope of this article is to address one specific problem which is related to:
This can happen when you, for example, have an existing recipient table or any other table which already has in excess of 1 million records.
If you now need to add one extra column to that table which is of data type
Then you add that column in the schema definition and want to commit the changes in the database.
While updating the database, the database encounters a deadlock or the query blocks other operations.
This is mainly due to updating of all records to 0 based on the SQL command generated by the database wizard.
When adding an integer column, you get the SQL statement generated similar to this:
ALTER TABLE NmsRecipient ADD column_name SMALLINT; UPDATE NmsRecipient SET column_name = 0; ALTER TABLE NmsRecipient ALTER COLUMN column_name SET Default 0; ALTER TABLE NmsRecipient ALTER COLUMN icolumn_name SET NOT NULL;
The problem here is that there are 4 commands executed in the database in parallel which can cause performance issues or a deadlock.
The solution is to condense these 4 commands into one and still do the same changes as below:
ALTER TABLE NmsRecipient ADD iFichierLiveDataCliqueurs SMALLINT NOT NULL SET Default 0 ;
It does all the changes required, adding the column, setting the default, set
NOT NULL, and also setting the value of existing records to 0 because of the
NOT NULL constraint.
If there are multiple integer columns that need to be added, add them one by one and run the database wizard for each change.
This is to ensure smooth operation since the statement updates all existing records to 0 as well and depending on the number of records, it can take time.