What is fragmentation?

Fragmentation is a database server feature that allows you to control where data is stored at the table level. Fragmentation enables you to define groups of rows or index keys within a table according to some algorithm or scheme. You can store each group or fragment (also referred to as a partition) in a separate dbspace associated with a specific physical disk. You use SQL statements to create the fragments and assign them to dbspaces.

The scheme that you use to group rows or index keys into fragments is called the distribution scheme. The distribution scheme and the set of dbspaces in which you locate the fragments together make up the fragmentation strategy. The decisions that you must make to formulate a fragmentation strategy are explained in your HCL OneDB™ Performance Guide.

After you decide whether to fragment table rows, index keys, or both, and you decide how the rows or keys should be distributed over fragments, you decide on a scheme to implement this distribution. For a description of the distribution schemes that HCL® OneDB database servers support, see Distribution schemes for table fragmentation.

When you create fragmented tables and indexes, the database server stores the location of each table and index fragment with other related information in the system catalog table named sysfragments. You can use this table to access information about your fragmented tables and indexes. If you use a user-defined routine as part of the fragmentation expression, that information is recorded in sysfragexprudrdep. For a description of the information that these system catalog tables contain, see the HCL OneDB Guide to SQL: Reference.

From the perspective of an end user or client application, a fragmented table is identical to a nonfragmented table. Client applications do not require any modifications to allow them to access the data in fragmented tables.

For some distribution schemes, the database server has information about which fragments contain which data, so it can route client requests for data to the correct fragment without accessing irrelevant fragments. (The database server cannot route client requests for data to the correct fragment for round-robin and some expression-based distribution schemes.) For more information, see Distribution schemes for table fragmentation.)