Effect of the ATTACH Clause

After an ATTACH operation, all consumed tables no longer exist. Any CHECK constraints or NOT NULL constraints on the consumed tables also no longer exist. You must reference the records that were in the consumed tables through the surviving table.

What Happens to Indexes?

A detached index on the surviving table retains its same fragmentation strategy. That is, a detached index does not automatically adjust to accommodate the new fragmentation of the surviving table. For more information on what happens to indexes, see the discussion about altering table fragments in your IBM Informix Performance Guide.

In a logging database, an ATTACH operation extends any attached index on the surviving table according to the new fragmentation strategy of the surviving table. All rows in the consumed table are subject to these automatically adjusted indexes. For information on whether the database server completely rebuilds the index on the surviving table or reuses an index that was on the consumed table, see your IBM Informix Performance Guide.

In a nonlogging database of Dynamic Server, an ATTACH operation does not extend indexes on the surviving table according to the new fragmentation strategy of the surviving table. To extend the fragmentation strategy of an attached index according to the new fragmentation strategy of the surviving table, you must drop the index and re-create it on the surviving table.

What Happens to BYTE and TEXT Columns?

When an ATTACH occurs, BYTE and TEXT fragments of the consumed table become part of the surviving table and continue to be associated with the same rows and data fragments as they were before the ATTACH operation.

In Dynamic Server, each BYTE and TEXT column in every table that is specified in the ATTACH clause must have the same storage type, either blobspace or tblspace. If the BYTE or TEXT column is stored in a blobspace, the same column in all tables must be in the same blobspace. If the BYTE or TEXT column is stored in a tblspace, the same column must be stored in a tblspace in all tables.

In Extended Parallel Server, BYTE and TEXT columns are stored in separate, additional fragments that the database server creates for that purpose in the same dbspace as each regular table fragment where a given row resides.

What Happens to Triggers and Views?

When you attach tables, triggers on the surviving table survive the ATTACH, but triggers on the consumed table are automatically dropped. No triggers are activated by the ATTACH clause, but subsequent data-manipulation operations on the new rows can activate triggers.

Views on the surviving table survive the ATTACH operation, but views on the consumed table are automatically dropped.

What Happens with the Distribution Scheme?

You can attach a nonfragmented table to a table with any type of supported distribution scheme. In general, the resulting table has the same fragmentation strategy as the prior fragmentation strategy of the surviving_table.

When you attach two or more nonfragmented tables, however, the distribution scheme can either be based on expression or round-robin.

For Dynamic Server, only the following distribution schemes can result from combining the distribution schemes of the tables in the ATTACH clause.

For Extended Parallel Server, this table shows the distribution schemes that can result from different distribution schemes of the tables in the ATTACH clause.

In Extended Parallel Server, when you attach a nonfragmented table to a table that has hash fragmentation, the resulting table has hybrid fragmentation. You can attach a table with a hash distribution scheme to a table that currently has no fragmentation, or hash fragmentation, or hybrid fragmentation. In any of these situations, the resulting table has a hybrid distribution scheme.

The following examples illustrate the use of the ATTACH clause to create fragmented tables with different distribution schemes.

Round-Robin Distribution Scheme

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 using a round-robin scheme 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.

Expression Distribution Scheme

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 alter 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 the IBM Informix Guide to SQL: Reference.

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 that you can use in an expression-based distribution scheme, see page Fragmenting by EXPRESSION.

Hybrid Fragmentation Distribution Scheme (XPS)

Consider a case where monthly sales data is added to the sales_info table defined below. Due to the large amount of data, the table is distributed evenly across multiple coservers with a system-defined hash function. To manage monthly additions of data to the table, it is also fragmented by a date expression. The combined hybrid fragmentation is declared in the following CREATE TABLE statement:

CREATE TABLE sales_info (order_num INT, sale_date DATE, ...)
   FRAGMENT BY HYBRID (order_num) EXPRESSION
      sale_date >= '01/01/1996' AND sale_date < '02/01/1996'
         IN sales_slice_9601,
      sale_date >= '02/01/1996' AND sale_date < '03/01/1996'
         IN sales_slice_9602,
   . . .
      sale_date >= '12/01/1996' AND sale_date < '01/01/1997'
         IN sales_slice_9612;

Values for a new month are originally loaded from an external source. The data values are distributed evenly across the same coservers on which the sales_info table is defined, using a system-defined hash function on the same column:

CREATE TABLE jan_97 (order_num INT, sale_date DATE, ...)
   FRAGMENT BY HASH (order_num) IN sales_slice_9701
INSERT INTO jan_97 SELECT (...) FROM ... ;

After data values are loaded, you can attach the new table to sales_info. You can issue the following ALTER FRAGMENT statement to attach the new table:

ALTER FRAGMENT ON TABLE sales_info ATTACH jan_97 
   AS sale_date >= '01/01/1997' AND sale_date < '02/01/1997'

DETACH Clause

DETACH Clause:
 
|--DETACH--+------------------+--fragment--new_table------------|
           |  (1)             |
           '--------PARTITION-'