Data Definition Language (DDL) operations include CREATE, DROP, and ALTER. DDL operations on a given table or index lock out all other readers and writers from any table being modified. This approach is crucial to the accuracy of query results. It ensures, for example, that a table column does not disappear from the database while you are selecting data from that column.
CREATE, DROP, and ALTER commands have the following special properties:
They cannot start while any other transaction is using the table or index they are modifying.
For example, if a user issues a SELECT on a table, the table is locked and cannot be altered until the user logs out, issues a SELECT on another table, or issues a ROLLBACK.
They include an automatic COMMIT on completion.
Existing transactions that try to use the table being modified receive an error. In other words, if you are accessing a table, and a DDL command changes that table, your command fails.
At any given time, only one of the commands CREATE DBSPACE, DROP DBSPACE, and CHECKPOINT can be executing in a database.
In an IQ multiplex you can execute CREATE, DROP, and ALTER commands for tables in the Main IQ Store, but only on the write server. To complete DDL operations for the IQ Store, query servers must disconnect users that hold locks on the target database.CREATE, DROP, and ALTER commands are not permitted on query servers, except for tables in the Catalog Store, on temporary tables, and on local IQ Main Stores. (Local IQ Main Stores are only visible to users of that query server.)
If more than one DDL command is attempted at the same time, users may get this error message:
Cannot perform DDL command now on table <tablename> as a DDL command is already in progress on that table.
If a CREATE DBSPACE or DROP DBSPACE command is in progress, and a user explicitly issues a CHECKPOINT command, the checkpoint fails with the message:
Run time SQL Error
If a CHECKPOINT command is in progress, a user who issues a CREATE DBSPACE or DROP DBSPACE command gets the following message:
Cannot perform requested command as there is a CHECKPOINT command in progress.
A user who issues CREATE DBSPACE during a drop gets the message:
Cannot perform requested command as there is a DROP DBSPACE command in progress.
A user who issues DROP DBSPACE during a create gets the message:
Cannot perform requested command as there is a CREATE DBSPACE command in progress.
See “Versioning of temporary tables” for special rules regarding temporary tables.
When one transaction issues a DDL command on a given table or index, any other transaction that began before the DDL transaction commits, and that tries to access that table, receives an error.
When this error occurs, any additional attempts to read or write to the table in the current transaction will fail.
Users on query servers are not permitted to modify tables in the shared IQ Main Store. If they attempt to do so, they get the error message:
Not permitted on multiplex
Users on query servers may create and modify tables on local IQ Main Stores, but such tables will only be visible to other users of that query server.
If a transaction modifies the definition of a table that is part of a join index, it locks every table with any columns that are joined in that index. This result occurs whether or not the particular columns in the original write transaction are being joined.
There is an exception to these rules for index creation commands. CREATE INDEX and CREATE JOIN INDEX can occur concurrently with a SELECT on the table(s) affected by the index creation. Sybase IQ prevents use of the new index or join index until the transaction creating the index commits.
While the commands GRANT, REVOKE, and SET OPTION are also considered DDL operations, they cause no concurrency conflicts, and so are not restricted. GRANT and REVOKE always cause an automatic commit; SET OPTION causes an automatic commit except when it is specified as TEMPORARY. GRANT and REVOKE are not allowed for any user currently connected to the database. SET OPTION affects all subsequent SQL statements sent to the database server, except for certain options that do not take effect until after you restart the database server. See the Sybase IQ Reference Manual for details of setting options.
