Informix Guide to SQL: Syntax

Informix Guide to SQL: Syntax
Chapter 1: SQL Statements

Home Contents Index Master Index New Book

SQL Statements

ALLOCATE COLLECTION

Use the ALLOCATE COLLECTION statement to allocate memory for an INFORMIX-ESQL/C collection variable.

Syntax

Element Purpose Restrictions Syntax
variable name
Variable name that identifies a typed or untyped collection variable for which to allocate memory
Variable must contain the name of an unallocated `ESQL/C` collection host variable.
Name must conform to language-specific rules for variable names.

Usage

The ALLOCATE COLLECTION statement creates a place in memory for the data in the collection variable that variable name identifies. To create a collection variable for an ESQL/C program, perform the following steps:

1. Declare the collection variable as a client collection variable in an ESQL/C program.

collection

2. Allocate memory for the collection variable with the ALLOCATE COLLECTION statement.

3. Populate the collection variable with elements.

collection

SELECT

The following example shows how to allocate resources with the ALLOCATE COLLECTION statement for the untyped collection variable, a_set:

EXEC SQL BEGIN DECLARE SECTION;

	client collection a_set;

EXEC SQL END DECLARE SECTION;

...

EXEC SQL allocate collection :a_set;

...

EXEC SQL deallocate collection :a_set;

The following example uses ALLOCATE COLLECTION to allocate resources for a typed collection variable, a_typed_set:

EXEC SQL BEGIN DECLARE SECTION;

	client collection set(integer not null) a_typed_set;

EXEC SQL END DECLARE SECTION;

...

EXEC SQL allocate collection :a_typed_set;

...

EXEC SQL deallocate collection :a_typed_set;

The ALLOCATE COLLECTION statement sets SQLCODE (sqlca.sqlcode) to zero if the memory allocation was successful and to a negative error code if the allocation failed.

Tip: The ALLOCATE COLLECTION statement allocates memory for an ESQL/C collection variable only. To allocate memory for ESQL/C row variables, use the ALLOCATE ROW statement.

You must explicitly release memory with the DEALLOCATE COLLECTION statement. Once you free the collection variable with the DEALLOCATE COLLECTION statement, you can reuse the collection variable.

References

See the ALLOCATE ROW and DEALLOCATE COLLECTION statements in this manual.

In the Informix Guide to SQL: Tutorial, see the discussion of collection data types in Chapter 10, "Understanding Complex Data Types." In the INFORMIX-ESQL/C Programmer's Manual, see the chapter that discusses complex data types.

ALLOCATE DESCRIPTOR

Use the ALLOCATE DESCRIPTOR statement to allocate memory for a system-descriptor area.

Syntax

Element Purpose Restrictions Syntax
descriptor
Quoted string that identifies a system-descriptor area
Use single quotes. String must represent the name of an unallocated system-descriptor area.
Quoted String, p. 1-1014

descriptor variable
Host-variable name that identifies a system-descriptor area
Variable must contain the name of an unallocated system-descriptor area.
Name must conform to language-specific rules for variable names.

occurrences
The number of item descriptors in the system-descriptor area
Value must be unsigned `INTEGER`. Default value is `100`.
Literal Number, p. 1-1001

occurrences variable
Host variable that contains the number of occurrences
Data type must be `INTEGER` or `SMALLINT`.
Name must conform to language-specific rules for variable names.

Usage

The ALLOCATE DESCRIPTOR statement creates a place in memory for a system-descriptor area. The descriptor parameter or the descriptor variable parameter identifies this area. A system-descriptor area holds information that a DESCRIBE...USING SQL DESCRIPTOR statement obtains or it holds information about the WHERE clause of a dynamically executed statement.

SPL

A DESCRIBE...USING SQL DESCRIPTOR statement also obtains information for the stored functions. For more information about stored functions, see the DESCRIBE statement on page 1-338 and Chapter 2, "SPL Statements."

A system-descriptor area contains one or more fields called item descriptors. Each item descriptor holds a data value that the database server can receive or send. The item descriptors also contain information about the data such as type, length, scale, precision, and nullability. Initially, all fields in the item-descriptor area are undefined.

The WITH MAX clause of ALLOCATE DESCRIPTOR sets the COUNT field to the number of occurrences that you specified in the occurrences parameter or the occurrences variable parameter. The DESCRIBE...USING SQL DESCRIPTOR statement sets other fields in the system-descriptor area. For more information, see "USING SQL DESCRIPTOR Clause".

If the name that you assign to a system-descriptor area matches the name of an existing system-descriptor area, the database server returns an error. If you free the descriptor with the DEALLOCATE DESCRIPTOR statement, you can reuse the descriptor.

WITH MAX Clause

You can use the optional WITH MAX clause to indicate the number of item descriptors you need. Either the occurrences parameter or the occurrences variable parameter specifies the number of item descriptors that you want in the system-descriptor area. This number must be greater than zero. When you do not specify the WITH MAX clause, the database server uses a default value of 100 for the occurrences parameter.

The following examples show the ALLOCATE DESCRIPTOR statement that includes the WITH MAX clause. The first line uses an embedded variable name to identify the system-descriptor area and the desired number of item descriptors. The second line uses a quoted string to identify the system-descriptor area and an unsigned integer to specify the desired number of item descriptors.

EXEC SQL allocate descriptor :descname with max :occ;

EXEC SQL allocate descriptor 'desc1' with max 3;

References

See the DEALLOCATE DESCRIPTOR, DECLARE, DESCRIBE, EXECUTE, FETCH, GET DESCRIPTOR, OPEN, PREPARE, PUT, and SET DESCRIPTOR statements in this manual.

In the Informix Guide to SQL: Tutorial, see the discussion of system-descriptor areas in Chapter 5.

ALLOCATE ROW

Use the ALLOCATE ROW statement to allocate memory for an INFORMIX-ESQL/C row variable.

Syntax

Element Purpose Restrictions Syntax
variable name
Variable name that identifies a typed or untyped row variable for which to allocate memory
Variable must contain the name of an unallocated `ESQL/C` row host variable.
Name must conform to language-specific rules for variable names.

Usage

The ALLOCATE ROW statement creates a place in memory for data in the row variable that variable name identifies. To create a row variable, perform the following steps in your ESQL/C program:

1. Declare the row variable.

row

2. Allocate memory for the row variable with the ALLOCATE ROW statement.

3. Populate the row variable with field values.

row

SELECT

The following example shows how to allocate resources with the ALLOCATE ROW statement for the typed row variable, a_row:

EXEC SQL BEGIN DECLARE SECTION;

	row (a int, b int) a_row;

EXEC SQL END DECLARE SECTION;

...

EXEC SQL allocate row :a_row;

...

EXEC SQL deallocate row :a_row;

The ALLOCATE ROW statement sets SQLCODE (sqlca.sqlcode) to zero if the memory allocation was successful and to a negative error code if the allocation failed.

Tip: The ALLOCATE ROW statement allocates memory for an ESQL/C row variable only. To allocate memory for ESQL/C collection variables, use the ALLOCATE COLLECTION statement.

You must explicitly release memory with the DEALLOCATE ROW statement. Once you free the row variable with the DEALLOCATE ROW statement, you can reuse the row variable.

References

See the ALLOCATE COLLECTION and DEALLOCATE ROW statements in this manual.

In the Informix Guide to SQL: Tutorial, see the discussion of rows in Chapter 10. In the INFORMIX-ESQL/C Programmer's Manual, see the chapter that discusses complex types.

ALTER FRAGMENT

Use the ALTER FRAGMENT statement to alter the fragmentation strategy of an existing table or index or to fragment an existing nonfragmented table.

Important: You cannot use ALTER FRAGMENT on a typed table.

Syntax

Element Purpose Restrictions Syntax
surviving index
The index on which you execute the `ALTER FRAGMENT` statement
The index must exist at the time you execute the statement. All indexes are detached. You cannot alter an index to become attached or detached.
Index Name, p. 1-984

surviving table
The table on which you execute the `ALTER FRAGMENT` statement
The table must exist at the time you execute the statement.
Table Name, p. 1-1048

(1 of 2)
Element	Purpose	Restrictions	Syntax
consumed table	A nonfragmented table on which you execute the `ATTACH` clause	The table must exist at the time you execute the statement. No serial columns, referential constraints, primary-key constraints, or unique constraints are allowed in the table. The table can have check constraints and not-null constraints, but these constraints are dropped after the `ATTACH` clause is executed.	Table Name, p. 1-1048
dbspace	The dbspace name that specifies where the consumed table expression occurs in the fragmentation list	The dbspace must exist at the time you execute the statement.	Identifier, p. 1-966
frag-expression	An expression that defines a fragment using a range, hash, or arbitrary rule	The frag-expression element can contain only columns from the current table and only data values from a single row. No subqueries, stored procedures, current date/time functions, or aggregates are allowed in frag-expression.	Condition, p. 1-835
surviving table	The fragmented table that survives the execution of `ALTER FRAGMENT`	The table must exist at the time you execute the statement. No referential constraints, primary-key constraints, unique constraints, check constraints, or not-null constraints are allowed in the table.	Table Name, p. 1-1048

Any tables that you attach must have been created previously in separate dbspaces. You cannot attach the same table more than once. You cannot attach a fragmented table to another fragmented table.

You must be the DBA or the owner of the tables that are involved to use the ATTACH clause.

After the tables are attached, the consumed table that is specified on the ATTACH clause no longer exists. The records that were in the consumed table must be referenced through the surviving table that is specified in the ALTER FRAGMENT ON TABLE statement.

Each table that is described in the ATTACH clause must be identical in structure; that is, all column definitions must match. The number, names, data types, and relative position of the columns must be identical. However, you cannot attach tables that contain serial columns. In addition, indexes and triggers on the surviving table survive the ATTACH, but indexes and triggers on the consumed table are dropped. Triggers are not activated with the ATTACH clause.

Tip: In Universal Server, all indexes are detached.

Combining Identically Structured Nonfragmented Tables

To make a single, fragmented table from two or more nonfragmented tables, the ATTACH clause must contain the surviving table as the first element of the attach list. The attach list is the list of tables in the ATTACH clause. For example, if you attach the tables cur_acct and new_acct, which were previously created in separate dbspaces, the surviving table cur_acct must be the first element in the attach list. The following statement illustrates this rule:

ALTER FRAGMENT ON TABLE cur_acct ATTACH cur_acct, new_acct

If you want a new rowid column on the single fragmented table, attach all tables first and then add the rowid with the ALTER TABLE statement.

Attaching a Nonfragmented Table to a Fragmented Table

To attach a nonfragmented table to an already fragmented table, the nonfragmented table must have been created in a separate dbspace and must have the same table structure as the fragmented table. The following example shows how to attach a nonfragmented table, old_acct, which was previously created in dbsp3, to a fragmented table, cur_acct:

ALTER FRAGMENT ON TABLE cur_acct ATTACH old_acct

BEFORE and AFTER Clauses

The BEFORE and AFTER clauses allow you to place a new fragment in a dbspace either before or after an existing dbspace. Use the BEFORE and AFTER clauses only when the distribution scheme is expression based (not round-robin). Attaching a new fragment without an explicit BEFORE or AFTER clause places the added fragment at the end of the fragmentation list. You cannot attach a new fragment after the remainder fragment.

Using ATTACH to Fragment Tables: Round-Robin

The following example combines nonfragmented tables pen_types and pen_makers into a single, fragmented table, pen_types. Table pen_types resides in dbspace dbsp1, and table pen_makers resides in dbspace dbsp2. Table structures are identical in each table.

ALTER FRAGMENT ON TABLE pen_types 

	ATTACH pen_types, pen_makers

After you execute the ATTACH clause, the database server fragments the table pen_types round-robin into two dbspaces: the dbspace that contained pen_types and the dbspace that contained pen_makers. Table pen_makers is consumed, and no longer exists; all rows that were in table pen_makers are now in table pen_types.

Using ATTACH to Fragment Tables: Fragment Expression

Consider the following example that combines tables cur_acct and new_acct and uses an expression-based distribution scheme. Table cur_acct was originally created as a fragmented table and has fragments in dbspaces dbsp1 and dbsp2. The first statement of the example shows that table cur_acct was created with an expression-based distribution scheme. The second statement of the example creates table new_acct in dbsp3 without a fragmentation strategy. The third statement combines the tables cur_acct and new_acct. Table structures (columns) are identical in each table.

CREATE TABLE cur_acct (a int) FRAGMENT BY EXPRESSION

	a < 5 in dbsp1,

	a >=5 and a < 10 in dbsp2;



CREATE TABLE new_acct (a int) IN dbsp3;



ALTER FRAGMENT ON TABLE cur_acct ATTACH new_acct AS a>=10;

When you examine the sysfragments system catalog table after you have altered the fragment, you see that table cur_acct is fragmented by expression into three dbspaces. For additional information about the sysfragments system catalog table, see Chapter 2 of the Informix Guide to SQL: Syntax.

In addition to simple range rules, you can use the ATTACH clause to fragment by expression with hash or arbitrary rules. For a discussion of all types of expressions in an expression-based distribution scheme, see "FRAGMENT BY Clause for Tables".

Warning: When you specify a date value in a fragment expression, make sure to specify 4 digits instead of 2 digits for the year. When you specify a 4-digit year, the DBCENTURY environment variable has no effect on the distribution scheme. When you specify a 2-digit year, the DBCENTURY environment variable can affect the distribution scheme and can produce unpredictable results. For more information on the DBCENTURY environment variable, see the "Informix Guide to SQL: Syntax."

What Happens to Columns That Contain Large Objects?

In every table that is named in the ATTACH clause, each column that contains a large object must have the same storage type. For example, if a TEXT column is in a blobspace, the same column in all tables must be in the same blobspace. If the TEXT column is in the tblspace, the same column must be in the tblspace in all tables.

What Happens to Indexes and Triggers?

Unless you create separate index fragments, the index fragmentation is the same as the table fragmentation.

When you attach tables, any indexes or triggers that are defined on the consumed table no longer exist, and all rows in the consumed table (new_acct) are subject to the indexes and triggers that are defined in the surviving table (cur_acct). No triggers are activated with the ATTACH clause, but subsequent data manipulation operations on the "new" rows can fire triggers.

At the end of the ATTACH operation, indexes on the surviving table that were explicitly given a fragmentation strategy remain intact with that fragmentation strategy.

DETACH Clause

Use the DETACH clause to detach a table fragment from a distribution scheme and place the contents into a new nonfragmented table. For an explanation of distribution schemes, see "FRAGMENT BY Clause for Tables".


Element	Purpose	Restrictions	Syntax
dbspace-name	The name of the dbspace that contains the fragment to be detached	The dbspace must exist when you execute the statement.	Identifier, p. 1-966
new table	The table that results after you execute the `ALTER FRAGMENT` statement	The table must not exist before you execute the statement.	Table Name, p. 1-1048

The DETACH clause cannot be applied to a table if that table is the parent of a referential constraint or if a rowid column is defined on the table.

The new table that results from the execution of the DETACH clause does not inherit any indexes or constraints from the original table. Only the data remains.

The following example shows the table cur_acct fragmented into two dbspaces, dbsp1 and dbsp2:

ALTER FRAGMENT ON TABLE cur_acct DETACH dbsp2 accounts

This example detaches dbsp2 from the distribution scheme for cur_acct and places the rows in a new table, accounts. Table accounts now has the same structure (column names, number of columns, data types, and so on) as table cur_acct, but the table accounts does not contain any indexes or constraints from the table cur_acct. Both tables are now nonfragmented.

The following example shows a table that contains three fragments:

ALTER FRAGMENT ON TABLE bus_acct DETACH dbsp3 cli_acct

This statement detaches dbsp3 from the distribution scheme for bus_acct and places the rows in a new table, cli_acct. Table cli_acct now has the same structure (column names, number of columns, data types, and so on) as bus_acct, but the table cli_acct does not contain any indexes or constraints from the table bus_acct. Table cli_acct is a nonfragmented table, and table bus_acct remains a fragmented table.

INIT Clause

Use the INIT clause to perform the following functions:

Change the fragmentation strategy on a single, fragmented table including changing the order of evaluating fragment expressions
Define and initialize a new fragmentation strategy on a nonfragmented table
Convert a fragmented table to a nonfragmented table

Element Purpose Restrictions Syntax
dbspace
The dbspace that contains the fragmented information
The dbspace must exist at the time you execute the statement. When you use the FRAGMENT BY clause, you must specify at least two dbspaces. You can specify a maximum of 2,048 dbspaces.
Identifier, p. 1-966

frag-expression
An expression that defines a fragment using a range, hash, or arbitrary rule
If you specify a value for remainder dbspace, you must specify at least one fragment expression. If you do not specify a value for remainder dbspace, you must specify at least two fragment expressions. You can specify a maximum of 2,048 fragment expressions. Each fragment expression can contain only columns from the current table and only data values from a single row. No subqueries, stored procedures, current date/time functions, or aggregates are allowed in frag-expression.
Condition, p. 1-835, and Expression, p. 1-880

remainder
dbspace
The dbspace that contains data that does not meet the conditions defined in any fragment expression
If you specify two or more fragment expressions, remainder dbspace is optional. If you specify only one fragment expression, remainder dbspace is required. The dbspace that is specified in remainder dbspace must exist at the time you execute the statement.
Identifier, p. 1-966

The INIT clause allows you to fragment an existing table or index that is not fragmented without redefining the table or index. With the INIT clause, you can also convert an existing fragmentation strategy on a table or index to another fragmentation strategy. Any existing fragmentation strategy is discarded, and records are moved to fragments as defined in the new fragmentation strategy. The INIT clause also allows you to convert a fragmented table or index to a nonfragmented table or index.

When you use the INIT clause to fragment an existing nonfragmented table, all indexes on the table become fragmented in the same way as the table.

Changing an Existing Fragmentation Strategy

You can redefine a fragmentation strategy if you decide that your initial strategy does not fulfill your needs. The following example shows the statement that originally defined the fragmentation strategy on the table account and then shows the ALTER FRAGMENT statement that redefines the fragmentation strategy:

CREATE TABLE account (col1 int, col2 int) 

	FRAGMENT BY ROUND ROBIN IN dbsp1, dbsp2;



ALTER FRAGMENT ON TABLE account

	INIT FRAGMENT BY EXPRESSION 

	MOD(col1, 3) = 0 in dbsp1,

	MOD(col1, 3) = 1 in dbsp2,

	MOD(col1, 3) = 2 in dbsp3;

When you want to redefine a fragmentation strategy, and any existing dbspaces are full, you must fragment the table in different dbspaces than the full dbspaces.

Fragmenting Unique and System Indexes

You can fragment unique indexes only if the table uses an expression-based distribution scheme. The columns that are referenced in the fragment expression must be indexed columns. If your ALTER FRAGMENT INIT statement fails to meet either of these restrictions, the INIT fails, and work is rolled back.

System indexes (such as those used in referential constraints and unique constraints) utilize user indexes if the indexes exist. If no user indexes can be utilized, system indexes remain nonfragmented and are moved to the dbspace where the database was created. To fragment a system index, create the fragmented index on the constraint columns, and then use the ALTER TABLE statement to add the constraint.

Converting a Fragmented Table to a Nonfragmented Table

You might decide that you no longer want a table to be fragmented. You can use the INIT clause to convert a fragmented table to a nonfragmented table. The following example shows the original fragmentation definition as well as how to use the ALTER FRAGMENT statement to convert the table:

CREATE TABLE checks (col1 int, col2 int)

	FRAGMENT BY ROUND ROBIN IN dbsp1, dbsp2, dbsp3;



ALTER FRAGMENT ON TABLE checks INIT IN dbsp1;

You must use the IN dbspace clause to place the table in a dbspace explicitly.

When you use the INIT clause to change a fragmented table to a nonfragmented table (that is, to rid the table of any fragmentation strategy), all indexes that are fragmented in the same way as the table become nonfragmented indexes. System indexes are not affected by the use of the INIT clause on the table.

Defining a Fragmentation Strategy on a Nonfragmented Table

You can use the INIT clause to define a fragmentation strategy on a nonfragmented table. It does not matter whether the table was created with a storage option. The following example shows the original table definition as well as how to use the ALTER FRAGMENT statement to fragment the table:

CREATE TABLE balances (col1 int, col2 int) IN dbsp1;



ALTER FRAGMENT ON TABLE balances INIT 

	FRAGMENT BY EXPRESSION

	col1 <= 500 IN dbsp1,

	col1 > 500 and col1 <=1000 IN dbsp2,

	REMAINDER IN dbsp3;

WITH ROWIDS Clause

Nonfragmented tables contain a pseudocolumn called the rowid column. Fragmented tables do not contain this column unless it is explicitly created.

Use the WITH ROWIDS clause to add a new column called the rowid column. the database server assigns a unique number to each row that remains stable for the existence of the row. The database server creates an index that it uses to find the physical location of the row.After you add the WITH ROWIDS clause, each row contains an additional 4 bytes to store the rowid column.

You cannot use the WITH ROWIDS clause on typed tables.

Important: Informix recommends that you use primary keys, rather than the rowid column, as an access method.

FRAGMENT BY Clause for Tables

Use the FRAGMENT BY clause for tables to define the distribution scheme, which is either round-robin or expression based.

In a round-robin distribution scheme, specify at least two dbspaces where the fragments are placed. As records are inserted into the table, they are placed in the first available dbspace. the database server balances the load between the specified dbspaces as you insert records and distributes the rows so that the fragments always maintain approximately the same number of rows. In this distribution scheme, the database server must scan all fragments when it searches for a row.

In an expression-based distribution scheme, each fragment expression in a rule specifies a dbspace. The rule specifies how the database server determines the fragment into which a row is placed. Each fragment expression within the rule isolates data and aids the database server in searching for rows. You can specify one of the following rules:

Range rule

A range rule uses a range to specify which rows are placed in a fragment, as the following example shows:

...

	FRAGMENT BY EXPRESSION

	c1 < 100 IN dbsp1,

	c1 >= 100 and c1 < 200 IN dbsp2,

	c1 >= 200 IN dbsp3;

Hash rule

MOD

...

	FRAGMENT BY EXPRESSION

	MOD(id_num, 3) = 0 IN dbsp1,

	MOD(id_num, 3) = 1 IN dbsp2,

	MOD(id_num, 3) = 2 IN dbsp3;

Arbitrary rule

SQL

...

	FRAGMENT BY EXPRESSION

	zip_num = 95228 OR zip_num = 95443 IN dbsp2,

	zip_num = 91120 OR zip_num = 92310 IN dbsp4,

	REMAINDER IN dbsp5;

FRAGMENT BY Clause for Indexes

Use the FRAGMENT BY clause for indexes to define the expression-based distribution scheme. Like the FRAGMENT BY clause for tables, the FRAGMENT BY clause for indexes supports range rules, hash rules, and arbitrary rules. See "FRAGMENT BY Clause for Tables" for an explanation of these rules.

ADD Clause

Use the ADD clause to add another fragment to an existing fragmentation list.


Element	Purpose	Restrictions	Syntax
existing dbspace	A dbspace name specified in an existing fragmentation list	The dbspace must exist at the time you execute the statement.	Identifier, p. 1-966
frag-expression	The range, hash, or arbitrary expression that defines the added fragment	The frag-expression can contain only columns from the current table and only data values from a single row. No subqueries, stored procedures, current date/time functions, or aggregates are allowed in frag-expression.	Condition, p. 1-835, and Expression, p. 1-880
new dbspace	The added dbspace in a round-robin distribution scheme	The dbspace must exist at the time you execute the statement.	Identifier, p. 1-966

Adding a New Dbspace to a Round-Robin Distribution Scheme

You can add more dbspaces to a round-robin distribution scheme. The following example shows the original round-robin definition:

CREATE TABLE book (col1 INT, col2 title)
FRAGMENT BY ROUND ROBIN in dbsp1, dbsp4;

To add another dbspace, use the ADD clause, as the following example shows:

ALTER FRAGMENT ON TABLE book ADD dbsp3;

Adding Fragment Expressions

Adding a fragment expression to the fragmentation list in an expression-based distribution scheme can shuffle records from some existing fragments into the new fragment. When you add a new fragment into the middle of the fragmentation list, all the data existing in fragments after the new one must be re-evaluated. The following example shows the original expression definition:

...

	FRAGMENT BY EXPRESSION

	c1 < 100 IN dbsp1,

	c1 >= 100 and c1 < 200 IN dbsp2,

	REMAINDER IN dbsp3;

If you want to add another fragment to the fragmentation list and have this fragment hold rows between 200 and 300, use the following ALTER FRAGMENT statement:

ALTER FRAGMENT ON TABLE news ADD

	c1 >= 200 and c1 < 300 IN dbsp4;

Any rows that were formerly in the remainder fragment and that fit the criteria c1 >=200 and c1 < 300 are moved to the new dbspace.

BEFORE and AFTER Clauses

The BEFORE and AFTER clauses allow you to place a new fragment in a dbspace either before or after an existing dbspace. Use the BEFORE and AFTER clauses only when the distribution scheme is expression based (not round-robin). You cannot add a new fragment after the remainder fragment. Adding a new fragment without an explicit BEFORE or AFTER clause places the added fragment at the end of the fragmentation list. However, if the fragmentation list contains a REMAINDER clause, the added fragment is added before the remainder fragment (that is, the remainder remains the last item on the fragment list).

REMAINDER Clause

You cannot add a remainder fragment when one already exists. When you add a new fragment to the end of the fragmentation list, and a remainder fragment exists, the records in the remainder fragment are retrieved and re-
evaluated. These records can be moved to the new fragment. The remainder fragment always remains the last item in the fragment list.

DROP Clause

Use the DROP clause to drop an existing fragment from a fragmentation

list.


Element	Purpose	Restrictions	Syntax
dbspace-name	The name of the dbspace that contains the dropped fragment	The dbspace must exist at the time you execute the statement.	Identifier, p. 1-966

You cannot drop one of the fragments when the table contains only two fragments. You cannot drop a fragment in a table that is fragmented with an expression-based distribution scheme if the fragment contains data that cannot be moved to another fragment. If the distribution scheme contains a REMAINDER clause, or if the expressions were constructed in an overlapping manner, you can drop a fragment that contains data.

When you want to make a fragmented table nonfragmented, use either the INIT or DETACH clause.

When you drop a fragment from a dbspace, the underlying dbspace is not affected. Only the fragment data within that dbspace is affected. When you drop a fragment all the records located in the fragment move to another fragment. The destination fragment might not have enough space for the
additional records. When this happens, follow one of the procedures that are listed in "Making More Space" to increase your space, and retry the procedure.

The following examples show how to drop a fragment from a fragmentation list. The first line shows how to drop an index fragment, and the second line shows how to drop a table fragment.

ALTER FRAGMENT ON INDEX cust_indx DROP dbsp2;



ALTER FRAGMENT ON TABLE customer DROP dbsp1;

MODIFY Clause

Use the MODIFY clause to change an existing fragment expression on an existing dbspace. You can also use the MODIFY clause to move a fragment expression from one dbspace to a different dbspace.


Element	Purpose	Restrictions	Syntax
frag-expression	The modified range, hash, or arbitrary expression	The fragment expression can contain only columns from the current table and only data values from a single row. No subqueries, stored procedures, current date/time functions, or aggregates are allowed in frag-expression.	Condition, p. 1-835, and Expression, p. 1-880
mod-dbspace	The modified dbspace	The dbspace must exist when you execute the statement.	Identifier, p. 1-966
new-dbspace	The dbspace that contains the modified information	The dbspace must exist when you execute the statement.	Identifier, p. 1-966

General Usage

When you use the MODIFY clause, the underlying dbspaces are not affected. Only the fragment data within the dbspaces is affected.

You cannot change a REMAINDER fragment into a nonremainder fragment if records within the REMAINDER fragment do not pass the new expression.

Changing the Expression in an Existing Dbspace

When you use the MODIFY clause to change an expression without changing the dbspace storage for the expression, you must use the same name for the mod dbspace and the new dbspace.

The following example shows how to use the MODIFY clause to change an existing expression:

ALTER FRAGMENT ON TABLE cust_acct 

	MODIFY dbsp1 to acct_num < 65 IN dbsp1

Moving an Expression from One Dbspace to Another

When you use the MODIFY clause to move an expression from one dbspace to another, mod-dbspace is the name of the dbspace where the expression was previously located, and new-dbspace is the new location for the expression.

The following example shows how to use the MODIFY clause to move an expression from one dbspace to another:

ALTER FRAGMENT ON TABLE cust_acct

	MODIFY dbsp1 to acct_num < 35 in dbsp2

In this example, the distribution scheme for the cust_acct table is modified so that all row items in the column acct_num that are less than 35 are now contained in the dbspace dbsp2. These items were formerly contained in the dbspace dbsp1.

Changing the Expression and Moving It to a New Dbspace

When you use the MODIFY clause to change the expression and move it to a new dbspace, change both the expression name and the dbspace name.

References

See the CREATE TABLE, CREATE INDEX, ALTER TABLE statements in this manual. Also see the Condition, Data Type, Expression, and Identifier segments.

For a task-oriented discussion of each clause in the ALTER FRAGMENT statement, see Chapter 9 of the Informix Guide to SQL: Tutorial.

Informix Guide to SQL: SyntaxChapter 1: SQL Statements Home Contents Index Master Index New Book

SQL Statements

ALLOCATE COLLECTION

Usage

Usage

References

Syntax Element Purpose Restrictions Syntax variable name Variable name that identifies a typed or untyped row variable for which to allocate memory Variable must contain the name of an unallocated ESQL/C row host variable. Name must conform to language-specific rules for variable names.

Usage

Usage

ATTACH Clause

BEFORE and AFTER Clauses

WITH ROWIDS Clause

FRAGMENT BY Clause for Indexes

Adding Fragment Expressions

MODIFY Clause

Moving an Expression from One Dbspace to Another

Informix Guide to SQL: Syntax
Chapter 1: SQL Statements

Home Contents Index Master Index New Book

Syntax

Element Purpose Restrictions Syntax
variable name
Variable name that identifies a typed or untyped row variable for which to allocate memory
Variable must contain the name of an unallocated `ESQL/C` row host variable.
Name must conform to language-specific rules for variable names.