Restrictions for a partitioned table

 

When you use partitioned tables, be aware of these restrictions.

• Referential constraints are not allowed for a partitioned table.

• If a primary key constraint for a partitioned table is added and then dropped, the primary key index is also dropped.

• If a primary key constraint is added to a partitioned table, and then removed by the user, the user is not allowed to keep the table keyed.

• If an existing nonpartitioned table does not have a primary key constraint, but the table is keyed, the keys are removed when the table is changed to a partitioned table.

• DB2^® Multisystem files (distributed tables) are already partitioned across multiple systems and cannot be partitioned across multiple members on a single system.

• An update to the partitioning key that attempts to move a row to a different partition is not allowed.

• The number of partitioning keys is restricted to 120.

• All SQL relative record processing is handled as it is for DB2 Multisystem support. The relative record number is determined in each individual partition, not the table as a whole. For example, reading to record 27 means that you read to record 27 in each partition. Each partition can contain its own record 27, none of which is the same.

• There are some restrictions on the data type of a partition key column. For range partitioning, the data type of a column used to partition a table cannot be BLOB, CLOB, DBCLOB, DATALINK, floating-point type, or a distinct type based on the these types. For hash partitioning, the data type of the column used as part of the partition key cannot be LOB, DATE, TIME, TIMESTAMP, floating-point type, or a distinct type based on one of these.

• Applications using the following CL commands must be changed to use Member *ALL to process all partitions of a partitioned table:

  • Clear Physical File Member (CLRPFM)

  • Copy from Import File (CPYFRMIMPF)

  • Copy to Import File (CPYTOIMPF)

  • Delete Network File (DLTNETF)

  • Open Query File (OPNQRYF)

  • Run Query (RUNQRY)

  • Work with Object Locks (WRKOBJLCK)

  • Apply Journaled Changes (APYJRNCHG)

  • Apply Journaled Changes Extend (APYJRNCHGX)

  • Display Journal (DSPJRN)

  • Receive Journal Entry (RCVJRNE)

  • Remove Journaled Changes (RMVJRNCHG)

  • Retrieve Journal Entry (RTVJRNE)

  • Restore Object (RSTOBJ)

  • Save Object (SAVOBJ)

  • Save Restore Object (SAVRSTOBJ)

 

Parent topic:

Partitioned tables