>>-CREATE DUPLICATE OF TABLE--table----------------------------->
 
            .-,-------.
            V         |
>--IN--+-(----dbspace-+--)-+-----------------------------------><
       '-dbslice-----------'
 

If the original table resides entirely on a single coserver, you can create duplicate copies of small tables across coservers for read-only use. For each attached index of the original table, a similarly defined index is built on each table duplicate, using the same dbspaces as the table.

Because query operators read the local copy of the table, duplicating small tables across coservers might improve the performance of some queries.

If a local copy of a duplicated table exists but is not available because the dbspace that stores it is offline (or for some similar reason), a query that requires access to the table fails. The database server does not attempt to access the original table.

The location of a duplicated table can be either a dbslice or a comma-separated list of dbspaces. You can combine dbslices and lists of dbspaces in a single CREATE DUPLICATE statement.

• If the original table is not fragmented, the dbspace list need provide only a single dbspace on each coserver.

  For example, if the table tab1 is not fragmented, enter the following statement to create a duplicate on the remaining three of the four coservers. If the original table is stored in the dbspace db1 on coserver 1 and db2 is on coserver 2, db3 is on coserver 3, and db4 is on coserver 4.

  CREATE DUPLICATE OF TABLE tab1 IN (db2, db3, db4)

• If the original table is fragmented with one fragment in the first dbspace of several dbslices that contain dbspaces on all coservers, you can create duplicate copies of the table in the remaining dbspaces of the dbslice.

  For example, you might create the tab3 table in the first dbspace of three dbslices, each of which contains a dbspace on each coserver, as follows:

  CREATE TABLE tab3 (...)   
     FRAGMENT BY HASH (....) IN dbsl1.l, dbsl2.1, dbsl3.1;

  To duplicate the tab3 table across the remaining coservers, use the following statement:

  CREATE DUPLICATE OF TABLE tab3 IN dbsl1, dbsl2, dbsl3

• You can mix dbslice names and dbspace lists in the same CREATE DUPLICATE statement. For example, instead of using dbspaces in a dbslice, for the previous example, you might enter the following statement in which dbsp2a is on coserver 2, dbsp3a is on coserver 3, and dbsp4a is on coserver 4:

  CREATE DUPLICATE OF  TABLE tab3 IN      
     dbsl1, dbsl2, (dbsp2a, dbsp3a, dbsp4a)

The first fragment of the original table is duplicated into dbsl1, which contains a dbspace on each coserver, the second fragment into dbsl2, which also contains a dbspace on each coserver, and the third fragment into the list of dbspaces.

Only one fragment of a duplicated table can reside in any single dbspace. You cannot list an existing dbspace of the duplicated table in the list of dbspaces into which it is duplicated, but it is not an error for an existing dbspace to be a member of a dbslice that specifies duplication dbspaces. Matching dbspaces in the dbslice are ignored.

The CREATE DUPLICATE statement requires the ALTER privilege.