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 causes 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) is given a new name with the character “~” (tilde) appended, followed optionally by a sequence number, to create a unique name that is unlikely to look like a name users would choose.
For example, suppose that you create a table employees on the write server, and a query server persistent table with the same name already exists (created on a local store in the query server), then Sybase IQ renames the query server table employees~1.
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, schedule DDL at the write server that affects normal query operation on the multiplex servers to avoid active users.
