![CREATE SCHEMA prohibited on active multiplex [CR 366937]](arrow-right.gif){kind=icon}
A static collision is a database state that prevents a DDL statement from executing, even if the statement is retried. For example, attempting to drop a user data type fails if that data type is in use in a table definition. This situation is static, because retrying the statement never succeeds, even after all users are disconnected or after the server is restarted. In an interactive setting, a static collision results in a SQL error.
A dynamic collision is a database server state that prevents a DDL statement from executing at the moment, but eventually clears on its own and allows the statement to succeed, if the statement is retried. For example, altering a table fails if the table is in use in a query by another user. This situation is dynamic, because retrying the statement succeeds once other operations complete, after all users are disconnected, or after the server is restarted. In an interactive setting, a dynamic collision results in a SQL error.
In a multiplex environment, the query server resolves a static collision that occurs while executing a DDL statement propagated from the write server, by renaming objects that conflict. The object (a table or domain) gets a new name made from the original name with the character “~” (tilde) appended, followed optionally by a sequence number. This creates a unique name that is unlikely to look like a name users would choose.
For information on objects that have been renamed due to static collisions, run the stored procedure sp_iqmpxrenameinfo. This procedure returns both the old name and the new name of the object.
To resolve a dynamic collision during DDL execution, the query server finds the connections responsible and disconnects them.
The disconnect is logged in the .iqmsg file. Here is an example of a message that appears in the .iqmsg when a dynamic collision occurs:
Shared IQ Store update DDL statement: drop table DBA.gtt44 Disposition: SQLSTATE:42W21 -- dropped 1 connection(s) for table: DBA.gtt44 Retry successful
Users can avoid collisions by managing domain and table names carefully across the members of the multiplex. To avoid the inconvenience of disconnects, DDL at the write server that affects normal query operation on the multiplex servers should be scheduled to avoid active users.
