Neoview Database Administrator's Guide (R2.2)

Identity Column Support
The Neoview Loader supports the loading of data into tables that have been created with an
identity column. You can have the system automatically generate values for the identity column
as part of the load task, or you can provide the values for the identity column. In that case, the
identity column will be treated as for any other column.
To control the loading of data into a table with an identity column, specify theidentityColumn
(-ic) argument as part of the task configuration request. Valid values for this option are:
indicates that the system will generate the value for the identity column. In this case, there should be no
data provided for the identity column as part of the input data. The default value is SYSTEM.
You need to set this option only if the default was changed or the option is to be set to USER. For details
on this option, see “Considerations for SYSTEM Option” (page 107).
SYSTEM
indicates that you are providing the values for the identity column. The load task will behave the same
as for a table without an identity column. You can use any of the supported load operations (INSERT,
UPDATE, INSERTUPATE or UPDATEINSERT). The load task is performed in parallel, the same as for
loads into tables without identity columns.
USER
Considerations for SYSTEM Option
If the identity column is not part of the clustering key, the load task will be performed in parallel,
the same as for a table without an identity column. For INSERT operations, the only difference
is that SQL will generate the value for the identity column as part of the insert operation. For
UPDATE operations, the difference is that the identity column is not updated.
The remaining considerations apply when the identity column is part of the clustering key. If
the identity column is part of the clustering key, but not part of the partitioning key, the load
task will be performed in parallel, as for a table without an identity column. For INSERT
operations, the only difference is that SQL generates the value for the identity column as part of
the insert operation.
UPDATE operations require that a UNIQUE INDEX that does not include the identity column
be defined on the table. The Loader uses the columns from all qualifying unique indexes (that
is, those that do not include the identity column) in addition to the columns from the clustering
key, minus the identity column, when providing the WHERE clause for the UPDATE statement
as part of the load operation. This differs from load operations to tables without identity columns,
where only the columns of the clustering key are used.
If there is no UNIQUE INDEX that does not include the identity column, an error is returned for
any load tasks involving UPDATEs. A UNIQUE INDEX without the identity column is required
because by definition you are not providing the value for the identity column. The Loader requires
the UNIQUE INDEX in order to be able to uniquely identify the row to be updated in the target
table, because the identity column is part of the clustering key.
For example, suppose a table uses clustering key columns C1, C2, C3, ID1, where ID1 is the
identity column. It has a unique index, C4. The WHERE clause columns are C1, C2, C3, C4.
If the identity column is part of the partitioning key, the load task is performed as a serial load
using a single process on the Neoview system to execute the load operation. This is because the
value for the identity column is assigned by SQL on the Neoview system, so the Linux gelserver
process is not be able to apply the hashing function to this column, which is part of the hash
partitioning key.
The Neoview gelserver process is randomly assigned to a Neoview segment and randomly
assigned a CPU within that segment. Load operations run considerably slower than for tables
without an identity column.
For INSERT operations, SQL generates the value for the identity column as part of the insert
operation, and as the identity column is part of the partitioning key, the row being inserted could
be inserted into any of the partitions for the target table.
Input Data Considerations For Load Tasks 107