SQL Statements
ALTER INDEX
Use the ALTER INDEX statement to put the data in a table in the order of an existing index or to release an index from the clustering attribute.
Syntax
Usage
The ALTER INDEX statement works only on indexes that are created with the CREATE INDEX statement; it does not affect constraints that are created with the CREATE TABLE statement.
You cannot alter the index of a temporary table.
TO CLUSTER Option
The TO CLUSTER option causes the rows in the physical table to reorder in the indexed order.
The following example shows how you can use the ALTER INDEX TO CLUSTER statement to order the rows in the orders table physically. The CREATE INDEX statement creates an index on the customer_num column of the table. Then the ALTER INDEX statement causes the physical ordering of the rows.
Reordering causes rewriting the entire file. This process can take a long time, and it requires sufficient disk space to maintain two copies of the table.
While a table is clustering, the table is locked IN EXCLUSIVE MODE. When another process is using the table to which index name belongs, the database server cannot execute the ALTER INDEX statement with the TO CLUSTER option; it returns an error unless lock mode is set to WAIT. (When lock mode is set to WAIT, the database server retries the ALTER INDEX statement.)
Over time, if you modify the table, you can expect the benefit of an earlier cluster to disappear because rows are added in space-available order, not sequentially. You can the table to regain performance by issuing another ALTER INDEX TO CLUSTER statement on the clustered index. You do not need to drop a clustered index before you issue another ALTER INDEX TO CLUSTER statement on a currently clustered index.
TO NOT CLUSTER Option
The NOT option drops the cluster attribute on the index name without affecting the physical table. Because only one clustered index per table can exist, you must use the NOT option to release the cluster attribute from one index before you assign it to another. The following statements illustrate how to remove clustering from one index and how a second index physically reclusters the table:
The first two statements create indexes for the orders table and cluster the physical table in ascending order on the customer_num column. The last two statements recluster the physical table in ascending order on the order_num column.
References
See the CREATE INDEX and CREATE TABLE statements in this chapter.
In the INFORMIX-Universal Server Performance Guide, see the discussion of clustered indexes.
In the Informix Guide to SQL: Tutorial, see the discussion of data-integrity constraints and the discussion of the ON DELETE CASCADE clause in Chapter 4. Also see the discussion of creating a database and tables in Chapter 9.
See the SET statement in this manual for information on object modes.
ALTER TABLE
Use the ALTER TABLE statement to modify both typed and untyped tables.
You can add, modify, or drop the constraints that are placed on a column or composite list of columns or change the extent size. You can change an untyped table to a typed table or drop the type from a typed table.
For untyped tables, you can also add, drop, or modify a column from a table, and add or drop a rowid column for a fragmented table.
You cannot alter a temporary table.
Syntax
Usage
To use the ALTER TABLE statement, you must meet one of the following conditions:
In addition to the basic privileges required for altering a table, you need the following privileges for specific operations:
When you add any kind of constraint, the name of the constraint must be unique within the database.
When you add any kind of constraint, the owner.name combination (the combination of the owner name and constraint name) must be unique within the database. 
Altering a table on which a view depends might invalidate the view.
Restrictions for Violations and Diagnostics Tables
Keep the following considerations in mind when you use the ALTER TABLE statement in connection with violations and diagnostics tables:
See the START VIOLATIONS TABLE statement on page 1-748 for further information on violations and diagnostics tables.
Alter Clause for Untyped Tables
The database server performs the actions in the Alter Clause in the order that you specify. If any of the actions fails, the entire operation is cancelled.
ADD Clause
Use the ADD clause to add a column to an existing untyped table. You cannot add a SERIAL or SERIAL8 column to a table if the table contains data.
The ADD clause appears in the Alter Clause for Untyped Table clause on page 1-57.
Algorithms for Adding Columns to Tables
INFORMIX-Universal Server uses the following two algorithms for adding columns to tables:
Scope of the In-Place Alter Algorithm
The database server uses the in-place alter algorithm if you specify the ADD clause without the BEFORE option and if you specify any clauses other than the following:
Benefits of the In-Place Alter Algorithm
The in-place alter algorithm lets you alter tables in place instead of creating a new table with the latest table definition and copying rows from the original table to the new table. The in-place alter method reduces the space that is required for altering tables and also increases the availability of the tables that are being altered.
The database server uses the slower algorithm for altering tables whenever your ALTER TABLE statement does not match the conditions for using the in-place alter algorithm. The database server uses the slower algorithm under the following conditions:
DEFAULT Clause
You can specify a default value that the database server inserts into the column when you do not specify an explicit value. When a default is not specified, and the column allows nulls, the default is NULL. When you designate NULL as the default value for a column, you cannot place a not-null constraint on the column.
You cannot place a default on SERIAL or SERIAL8 columns.
When the altered table already has rows in it, the new column contains the default value for all existing rows.
The DEFAULT clause appears in the ADD clause on page 1-58.
Literal Terms
You can designate literal terms as default values. Use a literal term to define alpha or numeric constant characters. To use a literal term as a default value, you must adhere to the rules in the following table.
|
Use a Literal
|
With Columns of Data Type
|
|---|
INTEGER
| INTEGER, SMALLINT, DECIMAL, MONEY, FLOAT, SMALLFLOAT, INT8
|
DECIMAL
| DECIMAL, MONEY, FLOAT, SMALLFLOAT
|
CHARACTER
| CHAR, VARCHAR, NCHAR, NVARCHAR,
CHARACTER VARYING, DATE
|
INTERVAL
| INTERVAL
|
DATETIME
| DATETIME
|
CHARACTER
| Opaque data types
|
Characters must be enclosed in quotation marks. Date literals must be formatted in accordance with the DBDATE environment variable. When DBDATE is not set, the format mm/dd/yyyy is assumed.
Opaque data types support only string literals for default values. The default value must be specified at the column level and not at the table level.
For information on using a literal INTERVAL, see the Literal INTERVAL segment on page 1-998. For more information on using a literal DATETIME, see the Literal DATETIME segment on page 1-995.
Data-Type Requirements
The following table indicates the data type requirements for columns that specify the CURRENT, DBSERVERNAME, SITENAME, TODAY, or USER functions as the default value.
|
Function Name
|
Data Type Requirements
|
|---|
CURRENT
| DATETIME column with matching qualifier
|
DBSERVERNAME
| CHAR, VARCHAR, NCHAR, NVARCHAR, or CHARACTER VARYING column at least 18 characters long
|
SITENAME
| CHAR, VARCHAR, NCHAR, NVARCHAR, or CHARACTER VARYING column at least 18 characters long
|
TODAY
| DATE column
|
USER
| CHAR, VARCHAR, NCHAR, NVARCHAR, or CHARACTER VARYING column at least 18 characters long
|
You cannot designate a server-defined function (that is, CURRENT, USER, TODAY, SITENAME or DBSERVERNAME) as the default value for opaque or distinct data types.
Example of a Literal Default Value
The following example adds a column to the items table. In items, the new column item_weight has a literal default value:
In this example, each existing row in the items table has a default value of 2.00 for the item_weight column.
Column-Constraint Definition
When you do not indicate a default value for a column, the default is null unless you place a not-null constraint on the column. In this case, if the not-null constraint is used, no default value exists for the column, and the column does not allow nulls. When the table contains data, however, you cannot specify a not-null constraint when you add a column (unless both the not-null constraint and a default value other than null are specified).
You cannot specify a unique or primary-key constraint on a new column if the table contains data. However, in the case of a unique constraint, the table can contain a single row of data. When you want to add a column with a primary-key constraint, the table must be empty when you issue the ALTER TABLE statement.
The following rules apply when you place unique or primary-key constraints on existing columns:
You cannot have a unique constraint on a BYTE or TEXT column, nor can you place referential or check constraints on these types of columns. A check constraint on a BYTE or TEXT column can check only for IS NULL, IS NOT NULL, or LENGTH.
The Column-Constraint Definition appears in the New Column clause on page 1-58.
Constraint-Mode Definition
You can use the Constraint-Mode Definition option for the following purposes:
The Constraint-Mode Definition appears in the Column-Constraint Definition on page 1-64.
Description of Constraint Modes
You can set constraints to the following modes: disabled, enabled, or filtering. These modes are described in the following table.
If you chose the filtering mode, you can specify the WITHOUT ERROR options. The following table describes these options.
Using Constraint Modes
You must observe the following rules when you use constraint modes:
REFERENCES Clause
The REFERENCES clause appears in the Column-Constraint Definition on page 1-64.
Restrictions on the REFERENCES Clause
Observe the following restrictions on the referenced column (the column or set of columns that you specify in the column name variable).
The following restrictions apply to the number of columns that you can specify in the column name variable:
You can specify a maximum of 16 column names. The total length of all the columns cannot exceed 390 bytes.
The data type of each referenced column must be identical to the data type of the corresponding referencing column. The only exception is that a referencing column must be INTEGER or INT8 if the referenced column is SERIAL or SERIAL8.
The referenced column or set of columns must be a unique or primary-key column. That is, the referenced column in the referenced table must already have a unique or primary-key constraint placed upon it.
Using the REFERENCES Clause in ALTER TABLE
Use the REFERENCES clause to reference a column or set of columns in another table or the same table. When you are using the ADD or MODIFY clause, you can reference a single column. When you are using the ADD CONSTRAINT clause, you can reference a single column or a set of columns.
The table that is referenced in the REFERENCES clause must reside in the same database as the altered table.
A referential constraint establishes the relationship between columns in two tables or within the same table. The relationship between the columns is commonly called a parent-child relationship. For every entry in the child (referencing) columns, a matching entry must exist in the parent (referenced) columns.
The referenced column (parent or primary-key) must be a column that is a unique or primary-key constraint. When you specify a column in the REFERENCES clause that does not meet this criterion, the database server returns an error.
The referencing column (child or foreign key) that you specify in the Add Column clause can contain null or duplicate values, but every value (that is, all foreign-key columns that contain non-null values) in the referencing columns must match a value in the referenced column.
Relationship Between Referencing and Referenced Columns
A referential constraint has a one-to-one relationship between referencing and referenced columns. If the primary key is a set of columns, the foreign key also must be a set of columns that corresponds to the primary key. The following example creates a new column in the cust_calls table, ref_order. The ref_order column is a foreign key that references the order_num column in the orders table.
When you reference a primary key in another table, you do not have to explicitly state the primary-key columns in that table. Referenced tables that do not specify the referenced column default to the primary-key column. In the previous example, because order_num is the primary key in the orders table, you do not have to reference that column explicitly.
When you place a referential constraint on a column or set of columns, and a duplicate or unique index already exists on that column or set of columns, the index is shared.
The data types of the referencing and referenced column must be identical, unless the primary-key column is SERIAL or SERIAL8 data type. When you add a column that references a SERIAL of SERIAL8 column, the column that you add must be an INTEGER or an INT8 column.
Locks Held During Creation of a Referential Constraint
When you create a referential constraint, an exclusive lock is placed on the referenced table. The lock is released after you finish with the ALTER TABLE statement or at the end of a transaction (if you are altering a table in a database with transactions, and you are using transactions).
Using ON DELETE CASCADE
Cascading deletes allow you to specify whether you want rows deleted in the child table when rows are deleted in the parent table. Normally, you cannot delete data in the parent table if child tables are associated with it. You can specify that you want the rows in the child table deleted with ON DELETE CASCADE. With ON DELETE CASCADE (or cascading deletes), when you delete a row in the parent table, any rows that are associated with that row (foreign keys) in a child table are also deleted. The principal advantage to the cascading-deletes feature is that it allows you to reduce the quantity of SQL statements you need to perform delete actions.
For example, the stock table contains the stock_num column as a primary key. The catalog table refers to the stock_num column as a foreign key. The following ALTER TABLE statements drop an existing foreign-key constraint (without cascading delete) and add a new constraint that specifies cascading deletes:
With cascading deletes specified on the child table, in addition to deleting a stock item from the stock table, the delete cascades to the catalog table that is associated with the stock_num foreign key. Of course, this cascading delete works only if the stock_num that you are deleting has not been ordered; otherwise, the constraint from the items table would disallow the cascading delete. For more information, see "What Happens to Multiple Child Tables?".
You specify cascading deletes with the REFERENCES clause on the ADD CONSTRAINT clause. You need only the References privilege to indicate cascading deletes. You do not need the Delete privilege to specify cascading deletes in tables; however, you do need the Delete privilege on tables that are referenced in the DELETE statement. After you indicate cascading deletes, when you delete a row from a parent table, Universal Server deletes any associated matching rows from the child table.
Use the ADD CONSTRAINT clause to add a REFERENCES clause with the ON DELETE CASCADE constraint.
What Happens to Multiple Child Tables?
When you have a parent table with two child tables, one with cascading deletes specified and the other without cascading deletes, and you attempt to delete a row from the parent table that applies to both child tables, the delete statement fails, and no rows are deleted from either the parent or child tables.
In the previous example, the stock table is also parent to the items table. However, you do not need to add the cascading-delete clause to the items table if you are planning to delete only unordered items. The items table is used only for ordered items.
Locking and Logging
During deletes, the database server places locks on all qualifying rows of the referenced and referencing tables. You must turn logging on when you perform the deletes. When logging is turned off in a database, even temporarily, deletes do not cascade. This restriction applies because you have no way to roll back actions if logging is turned off. For example, if a parent row is deleted, and the system crashes before the child rows are deleted, the database would have dangling child records. Such records would violate referential integrity. However, when logging is turned back on, subsequent deletes cascade.
Restriction on Cascading Deletes
Cascading deletes can be used for most deletes except correlated subqueries. In correlated subqueries, the subquery (or inner SELECT) is correlated when the value it produces depends on a value produced by the outer SELECT statement that contains it. If you have implemented cascading deletes, you cannot write deletes that use a child table in the correlated subquery. You receive an error when you attempt to delete from a query that contains such a correlated subquery.
ON DELETE CASCADE appears in the REFERENCES clause on page 1-68.
CHECK Clause
A check constraint designates a condition that must be met before data can be inserted into a column. If a row evaluates to false for any check constraint that is defined on a table during an insert or update, the database server returns an error.
Check constraints are defined using search conditions. The search condition cannot contain the following items: subqueries, aggregates, host variables, rowids, or stored procedure calls. In addition, the search condition cannot contain the following functions: the CURRENT, USER, SITENAME, DBSERVERNAME, or TODAY functions.
You cannot create check constraints for columns across tables. When you are using the ADD or MODIFY clause, the check constraint cannot depend upon values in other columns of the same table. The following example adds a new column, unit_price, to the items table and includes a check constraint that ensures that the entered value is greater than 0:
To create a constraint that checks values in more than one column, use the ADD CONSTRAINT clause. The following example builds a constraint on the column that was added in the previous example. The check constraint now spans two columns in the table.
The CHECK clause appears in the Column-Constraint Definition on page 1-64.
BEFORE Option
Use the BEFORE option of the ADD clause to specify the column before which a new column or list of columns is to be added. The column that you specify in the BEFORE option must be an existing column in the table.
If you do not include the BEFORE option in the ADD clause, the database server adds the new column or list of columns to the end of the table definition by default.
In the following example, to add the item_weight column before the total_price column, include the BEFORE option in the ADD clause:
In the following example, to add the item_weight column to the end of the table, omit the BEFORE option from the ADD clause:
The BEFORE option appears in the ADD clause on page 1-58.
DROP Clause
Use the DROP clause to drop one or more columns from a table.
The DROP clause appears in the Alter Clause for Untyped Tables on page 1-57.
How Dropping a Column Affects Constraints
When you drop a column, all constraints placed on that column are dropped, as the following list describes:
Because any constraints that are associated with a column are dropped when the column is dropped, the structure of other tables might also be altered when you use this clause. For example, if the dropped column is a unique or primary key that is referenced in other tables, those referential constraints also are dropped. Therefore the structure of those other tables is also altered.
How Dropping a Column Affects Triggers
When you drop a column that occurs in the triggering column list of an UPDATE trigger, the column is dropped from the triggering column list. If the column is the only member of the triggering column list, the trigger is dropped from the table. See the CREATE TRIGGER statement on page 1-258 for more information on triggering columns in an UPDATE trigger.
How Dropping a Column Affects Views
When you alter a table by dropping a column, views that depend on the column are not modified. However, if you attempt to use the view, you receive an error message indicating that the column was not found.
Views are not dropped because you can change the order of columns in a table by dropping a column and then adding a new column with the same name. Views based on that table continue to work. They retain their original sequence of columns.
MODIFY Clause
Use the MODIFY clause to change the data type of a column and the length of a character column, to add or change the default value for a column, and to allow or disallow nulls in a column.
|
You cannot modify a column whose data type is a collection type. You cannot modify a column type to be a collection type or a row type.
When you modify a column, all attributes previously associated with that column (that is, default value, single-column check constraint, or referential constraint) are dropped. When you want certain attributes of the column to remain, such as PRIMARY KEY, you must respecify those attributes. For example, if you are changing the data type of an existing column, quantity, to SMALLINT, and you want to keep the default value (in this case, 1) and non-null attributes for that column, you can issue the following ALTER TABLE statement:
When you modify a column that has column constraints associated with it, the following constraints are dropped:
For example, if you modify a column that has a unique constraint, the unique constraint is dropped. If this column was referenced by columns in other tables, those referential constraints are also dropped. In addition, if the column is part of a multiple-column unique or primary-key constraint, the multiple-column constraints are not dropped, but any referential constraints placed on the column by other tables are dropped. For example, a column is part of a multiple-column primary-key constraint. This primary key is referenced by foreign keys in two other tables. When this column is modified, the multiple-column primary-key constraint is not dropped, but the referential constraints placed on it by the two other tables are dropped.
If you modify a column that appears in the triggering column list of an UPDATE trigger, the trigger is unchanged.
The MODIFY clause appears in the Alter clause for Untyped Tables on page 1-57.
Altering Large-Object Characteristics
You cannot use the ALTER TABLE statement to modify the characteristics of a smart large object column. To modify a smart-large-object column, you must use one of the following:
For more information, refer to the INFORMIX-ESQL/C Programmer's Manual.
For more information, refer to the DataBlade API Programmer's Manual.
Altering the Next Serial Number
You can use the MODIFY clause to reset the next value of a SERIAL or SERIAL8 column. You cannot set the next value below the current maximum value in the column because that action can cause the database server to generate duplicate numbers. However, you can set the next value to any value higher than the current maximum, which creates gaps in the sequence.
Altering the Structure of Tables
When you use the MODIFY clause, you can also alter the structure of other tables. If the modified column is referenced by other tables, those referential constraints are dropped. You must add those constraints to the referencing tables again, using the ALTER TABLE statement.
When you change the data type of an existing column, all data is converted to the new data type, including numbers to characters and characters to numbers (if the characters represent numbers). The following statement changes the data type of the quantity column:
When a unique or primary-key constraint exists, however, conversion takes place only if it does not violate the constraint. If a data-type conversion would result in duplicate values (by changing FLOAT to SMALLFLOAT, for example, or by truncating CHAR values), the ALTER TABLE statement fails.
Modifying Tables for Null Values
You can modify an existing column that formerly permitted nulls to disallow nulls, provided that the column contains no null values. To do this, specify MODIFY with the same column name and data type and the NOT NULL keywords. The NOT NULL keywords create a not-null constraint on the column.
You can modify an existing column that did not permit nulls to permit nulls. To do this, specify MODIFY with the column name and the existing data type, and omit the NOT NULL keywords. The omission of the NOT NULL keywords drops the not-null constraint on the column. However, if a unique index exists on the column, you can remove it using the DROP INDEX statement.
An alternative method of permitting nulls in an existing column that did not permit nulls is to use the DROP CONSTRAINT clause to drop the not-null constraint on the column.
Adding a Constraint When Existing Rows Violate the Constraint
If you use the MODIFY clause to add a constraint in the enabled mode and receive an error message because existing rows would violate the constraint, you can take the following steps to add the constraint successfully:
1. Add the constraint in the disabled mode.
Issue the ALTER TABLE statement again, but this time specify the DISABLED keyword in the MODIFY clause.
2. Start a violations and diagnostics table for the target table with the START VIOLATIONS TABLE statement.
3. Issue a SET statement to switch the object mode of the constraint to the enabled mode.
When you issue this statement, existing rows in the target table that violate the constraint are duplicated in the violations table; however, you receive an integrity-violation error message, and the constraint remains disabled.
4. Issue a SELECT statement on the violations table to retrieve the nonconforming rows that are duplicated from the target table.
You might need to join the violations and diagnostics tables to get all the necessary information.
5. Take corrective action on the rows in the target table that violate the constraint.
6. After you fix all the nonconforming rows in the target table, issue the SET statement again to switch the disabled constraint to the enabled mode.
This time the constraint is enabled, and no integrity-violation error message is returned because all rows in the target table now satisfy the new constraint.
ADD CONSTRAINT Clause
Use the ALTER TABLE statement with the ADD CONSTRAINT keywords to specify a constraint on a new or existing column or on a set of columns. For example, to add a unique constraint to the fname and lname columns of the customer table, use the following statement:
To name the constraint, change the preceding statement, as the following example shows:
When you do not provide a constraint name, the database server provides one. You can find the name of the constraint in the sysconstraints system catalog table. For more information about the sysconstraints system catalog table, refer to Chapter 2 of the Informix Guide to SQL: Syntax.
The ADD CONSTRAINT clause appears in the Alter Clause for Untyped Tables on page 1-57 and the Alter Clause for Typed Tables on page 1-89.
Table-Level Constraint Definition
Use the Table-Level Constraint Definition option to add a table-level constraint. You can define a table-level constraint on one column or a set of columns. You can assign a name to the constraint and set its object mode by means of the Constraint Mode Definitions option. See page 1-65 for further information.
The Table-Level Constraint Definition clause appears in the Add Constraints clause on page 1-81.
Adding a Unique Constraint
You must follow certain rules when you add a unique constraint.
The column or columns can contain only unique values.
When you place a unique constraint on a column or set of columns, and a unique index already exists on that column or set of columns, the constraint shares the index. However, if the existing index allows duplicates, the database server returns an error. You must then drop the existing index before adding the unique constraint.
When you add a unique constraint, the name of the constraint must be unique within the database.
When you add a unique constraint, the owner.name combination (the combination of the owner name and constraint name) must be unique within the database.
A composite list can include no more than 16 column names. The total length of all the columns cannot exceed 390 bytes.
Adding a Primary-Key or Unique Constraint
You must follow certain rules when you add a unique or primary-key constraint.
When you place a unique or primary-key constraint on a column or set of columns, and a unique index already exists on that column or set of columns, the constraint shares the index. However, if the existing index allows duplicates, the database server returns an error. You must then drop the existing index before adding the constraint.
When you place a unique or primary-key constraint on a column or set of columns, and a referential constraint already exists on that column or set of columns, the duplicate index is upgraded to unique (if possible) and the index is shared.
When you place a referential constraint on a column or set of columns, and a referential constraint already exists on that column or set of columns, the duplicate index is upgraded to unique (if possible), and the index is shared.
When you add a unique or primary-key constraint, the name of the constraint must be unique within the database.
When you add a unique or primary-key constraint, the owner.name combination (the combination of the owner name and constraint name) must be unique within the database.
Privileges Required for Adding Constraints
When you own the table or have the Alter privilege on the table, you can create a unique, primary-key, or check constraint on the table and specify yourself as the owner of the constraint. To add a referential constraint, you must have the References privilege on either the referenced columns or the referenced table. When you have the DBA privilege, you can create constraints for other users.
Recovery from Constraint Violations
If you use the ADD CONSTRAINT clause to add a table-level constraint in the enabled mode and receive an error message because existing rows would violate the constraint, you can follow a procedure to add the constraint successfully. See "Adding a Constraint When Existing Rows Violate the Constraint".
DROP CONSTRAINT Clause
Use the DROP CONSTRAINT clause to drop any type of constraint, including not-null constraints.
To drop an existing constraint, specify the DROP CONSTRAINT keywords and the name of the constraint. The following statement is an example of dropping a constraint:
If a constraint name is not specified when the constraint is created, the database server generates the name. You can query the sysconstraints system catalog table for the names (including the owner) of constraints. For example, to find the name of the constraint placed on the items table, you can issue the following statement:
When you drop a unique or primary-key constraint that has a corresponding foreign key, the referential constraints is dropped. For example, if you drop the primary-key constraint on the order_num column in the orders table and order_num exists in the items table as a foreign key, that referential relationship is also dropped.
The DROP CONSTRAINT clause appears in the Alter Clause for Untyped Tables on page 1-57.
ADD TYPE Clause
Use the ALTER TABLE command to change an untyped table into a typed table. When you specify ADD TYPE, you assign a named row type to the table.
Use the ADD TYPE clause to convert an untyped table to a typed table of the named row type.
You cannot combine the ADD TYPE clause with any clause that changes the structure of the table. That is, you cannot use an ADD, DROP, or MODIFY clause in the same statement as the ADD TYPE clause.
When you add a named row type to a table, be sure that:
The ADD TYPE clause appears in the Alter Clause for Untyped Tables on page 1-57.
MODIFY NEXTSIZE Clause
Use the MODIFY NEXT SIZE clause to change the size of new extents. If you want to specify an extent size of 32 kilobytes, use a statement such as the one in the following example:
The size of existing extents is not changed.
The MODIFY NEXT SIZE clause appears in the Alter Clause for Untyped Tables on page 1-57.
LOCK MODE Clause
U se the LOCK MODE keywords to change the locking mode of a table. The default lock mode is PAGE; it is set if the table is created without using the LOCK MODE clause. You must use the LOCK MODE clause to change from page to row locking, as the following example shows:
The LOCK MODE clause appears in the Alter Clause for Untyped Tables on page 1-57.
ROWIDS Clause
Use the ROWIDS clause to add or remove rowids from a column in a fragmented table. By default, fragmented tables do not contain the hidden rowid column.
Use ADD ROWIDS to add a new column called rowid for use with fragmented tables. For each row, the database server assigns a unique number that remains stable for the life of the row. The database server creates an index that it uses when search to find the physical location of the row. After you add the rowid column, each row contains an additional 4 bytes to store the rowid value.
You can use DROP ROWIDS to drop a rowid column only if you created the rowid column with the CREATE TABLE or ALTER FRAGMENT statements on fragmented tables. You cannot drop the rowid columns of a nonfragmented table.
The ROWIDS clause appears in the Alter Clause for Untyped Tables on page 1-57.
For additional information about the rowid column, refer to the INFORMIX-Universal Server Administrator's Guide.
Alter Clause for Typed Tables
The database server performs the actions in the Alter Clause in the order that you specified. If any of the actions fails, the entire operation is cancelled.
The Alter Clause for Typed Tables appears in the ALTER TABLE syntax on page 1-55.
Altering Subtables and Supertables
The following considerations apply to tables that are part of inheritance hierarchies:
DROP TYPE Clause
Use DROP TYPE to drop the type from a table. DROP TYPE changes a typed table to an untyped table. You must drop the type from a typed table before you can modify, drop, or change the data type of a column in the table.
If a table is part of a table hierarchy, you cannot drop its type unless it is the last subtype in the hierarchy. That is, you can only drop a type from a table if that table has no subtables. When you drop the type of a subtable, it is automatically removed from the hierarchy. The table rows are deleted from all indexes defined by its supertables.
MODIFY NEXT SIZE Clause
Use the MODIFY NEXT SIZE clause to change the size of new extents. If you want to specify an extent size of 32 kilobytes, use a statement such as the one in the following example:
The size of existing extents is not changed.
References
See the CREATE TABLE, DROP TABLE, and LOCK TABLE statements in this manual.
In the Informix Guide to SQL: Tutorial, see the discussion of data-integrity constraints and the discussion of the ON DELETE CASCADE clause in Chapter 4. Also see the discussion of creating a database and tables in Chapter 9.
See the SET statement in this manual for information on object modes.
BEGIN WORK
Use the BEGIN WORK statement to start a transaction (a sequence of database operations that the COMMIT WORK or ROLLBACK WORK statement terminates).
Syntax
Usage
The following code fragment shows how you might place statements within a transaction:
Each row that an UPDATE, DELETE, or INSERT statement affects during a transaction is locked and remains locked throughout the transaction. A transaction that contains many such statements or that contains statements affecting many rows can exceed the limits that your operating system or the INFORMIX-Universal Server configuration imposes on the maximum number of simultaneous locks. If no other user is accessing the table, you can avoid locking limits and reduce locking overhead by locking the table with the LOCK TABLE statement after you begin the transaction. Like other locks, this table lock is released when the transaction terminates.
You can issue the BEGIN WORK statement only if a transaction is not in progress. If you issue a BEGIN WORK statement while you are in a transaction, the database server returns an error.
If you use the BEGIN WORK statement within a routine called by a WHENEVER statement, specify WHENEVER SQLERROR CONTINUE and WHENEVER SQLWARNING CONTINUE before the ROLLBACK WORK statement. These statements prevent the program from looping if the ROLLBACK WORK statement encounters an error or a warning.
With ANSI-Compliant Databases
The BEGIN WORK statement is not needed because transactions are implicit. A warning is generated if you use a BEGIN WORK statement immediately after one of the following statements:
An error is generated if you use a BEGIN WORK statement after any other statement.
References
See the COMMIT WORK and ROLLBACK WORK statements in this manual.
In the Informix Guide to SQL: Tutorial, see the discussion of transactions and locking in Chapter 4 and Chapter 7, respectively.
CLOSE
Use the CLOSE statement to close a cursor in the following situations:
Syntax
Usage
The CLOSE statement deallocates resources that have been allocated to a cursor when it was opened with the OPEN statement. Closing a cursor makes the cursor unusable for any statements except OPEN or FREE and releases resources that the database server had allocated to the cursor. A CLOSE statement treats a cursor that is associated with an INSERT statement (an insert cursor) differently than one that is associated with a SELECT statement (a select cursor) or an EXECUTE FUNCTION statement (a function cursor).
You can close a cursor that was never opened or that has already been closed. No action is taken in these cases.
You get an error if you close a cursor that was not open. No other action occurs.
Closing a Select or Function Cursor
When cursor id is associated with a SELECT statement (select cursor) or an EXECUTE FUNCTION statement (function cursor), the CLOSE statement terminates the SELECT or EXECUTE PROCEDURE statement. The database server releases all resources that it might have allocated to the active set of rows, for example, a temporary table that it used to hold an ordered set. The database server also releases any locks that it might have held on rows that were selected through the cursor. If a transaction contains the CLOSE statement, the database server does not release the locks until you execute COMMIT WORK or ROLLBACK WORK.
After you close a select or function cursor, you cannot execute a FETCH statement that names that cursor until you have reopened it.
Closing an Insert Cursor
When cursor id is associated with an INSERT statement (insert cursor), the CLOSE statement writes any remaining buffered rows into the database. The number of rows that were successfully inserted into the database is returned in the third element of the sqlerrd array in the sqlca structure (sqlca.sqlerrd[2]).For information on using SQLERRD to count the total number of rows that were inserted, see the PUT statement on page 1-555. T
he SQLCODE field of the sqlca structure (sqlca.sqlcode) indicates the result of the CLOSE statement for an insert cursor. If all buffered rows are successfully inserted, the database server sets SQLCODE to zero. If an error is encountered, the database server sets SQLCODE to a negative error message number.
When SQLCODE is zero, the row buffer space is released, and the cursor is closed; that is, you cannot execute a PUT or FLUSH statement that names the cursor until you reopen it.
If the insert is not successful, the number of successfully inserted rows is stored in sqlerrd. Any buffered rows that follow the last successfully inserted row are discarded. Because the CLOSE statement failed in this case, the cursor is not closed. A second CLOSE statement can be successful because no buffered rows exist. A subsequent OPEN statement should also be successful because the OPEN statement performs a successful implicit close. For example, a CLOSE statement can fail if insufficient disk space prevents some of the rows from being inserted.
Closing a Collection Cursor
You can declare both select and insert cursors on collection variables. Such cursors are called collection cursors. (For more information, see the DECLARE statement on page 1-303.) To close a collection cursor, use the CLOSE statement. The CLOSE statement deallocates resources that have been allocated for the collection cursor.
For more information on the use of OPEN with a collection cursor, see the following sections in the FETCH statement: "Fetching From a Collection Cursor" and "Inserting into a Collection Cursor".
Using End of Transaction to Close a Cursor
The COMMIT WORK and ROLLBACK WORK statements close all cursors except hold cursors (those that are declared with the WITH HOLD option of DECLARE). It is better to close all cursors explicitly, however. For select or function cursors, this action simply makes the intent of the program clear. It also helps to avoid a logic error if the WITH HOLD clause is later added to the declaration of a cursor.
For an insert cursor, it is important to use the CLOSE statement explicitly so that you can test the error code. Following the COMMIT WORK statement, SQLCODE reflects the result of the COMMIT statement, not the result of closing cursors. If you use a COMMIT WORK statement without first using a CLOSE statement, and if an error occurs while the last buffered rows are being written to the database, the transaction is still committed. For the use of insert cursors and the WITH HOLD clause, see the DECLARE statement on page 1-303.
References
See the CLOSE, DECLARE and FREE statements in this manual for general information about cursors. See the PUT and FLUSH statements in this manual for information about insert cursors. See the FETCH statement in this manual for information about select and function cursors.
In the Informix Guide to SQL: Tutorial, see the discussion of cursors in Chapter 5.
CLOSE DATABASE
Use the CLOSE DATABASE statement to close the current database.
Syntax
Usage
Following the CLOSE DATABASE statement, you can use only the DATABASE, CREATE DATABASE, and DROP DATABASE statements. A DISCONNECT statement can also follow a CLOSE DATABASE statement, but only if an explicit connection existed before you issue the CLOSE DATABASE statement. A CONNECT statement can follow a CLOSE DATABASE statement without any restrictions.
Issue the CLOSE DATABASE statement before you drop the current database.
If your database has transactions, and if you have started a transaction, you must issue a COMMIT WORK statement before you use the CLOSE DATABASE statement.
The following example shows how to use the CLOSE DATABASE statement to drop the current database:
The CLOSE DATABASE statement cannot appear in a multistatement PREPARE operation.
If you use the CLOSE DATABASE statement within a routine called by a WHENEVER statement, specify WHENEVER SQLERROR CONTINUE and WHENEVER SQLWARNING CONTINUE before the ROLLBACK WORK statement. This action prevents the program from looping if the ROLLBACK WORK statement encounters an error or a warning.
When you issue the CLOSE DATABASE statement, declared cursors are no longer valid. You must redeclare any cursors that you want to use.
References
See the CONNECT, DATABASE, CREATE DATABASE, DISCONNECT, and DROP DATABASE statements in this manual.
|
