Explanation of Datacom Table Partitioning

Products

• Datacom

Issue/Introduction

IT organizations are constantly challenged to find ways to manage greater volumes of data, to move that data faster and to process the data with no downtime (24x7). Today, in order to compete on the same or higher level with counterparts in the industry, it is important to find a  way to achieve these goals. Traditionally, customers have scheduled data entry tasks during the day, batch job processing during the evening hours and maintenance operations overnight. As customers expand their businesses globally, the standard production model is no longer relevant. Many customers require production-level processing around-the-clock.

The table partitioning concept addresses these needs for Datacom sites that are challenged with "static" downtime windows that historically have been set aside for table reorganizations. For these sites, this window must be drastically diminished or entirely eliminated in order to meet new business requirements.

Environment

Datacom 15.1 

Resolution

Table Partitioning allows you to store rows of a single table in multiple data areas. This concept provides for a number of configuration scenarios that can deliver immediate benefits for 24 x 7 operations and increased data availability while maintaining the integrity and performance of your existing applications.

By separating a table's rows into multiple data areas, data can be segmented to utilize a concurrent processing mix that would otherwise not be allowed. This partitioning of data rows allows most area level DBUTLTY utilities to run a system maintenance task against one partition while still supporting other accesses to the remaining partitions. This can reduce the amount of time that a given table is inaccessible for production processing. (See Figure 1.)

Figure 1. When a non-partitioned table is converted to a partitioned table, you are able to process individual data partitions as unique entities, if necessary.

In addition to concurrent system utility processing benefits, partitioning allows organizations to run maintenance tasks on one or more data partitions while full production access is performed on other partitions. Tables are partitioned by selecting a set of columns that together comprise a partition key. Concurrent production processing is made possible for those applications that process data in predictable patterns depending on the partition key. For example, you could perform system maintenance against your data represented in the Pacific time zone while providing open application access to data applicable to the Central and Eastern time zones.

The table partitioning feature is quite flexible. A database may contain partitioned tables and non-partitioned tables. Each data area in the database may contain one or more full tables or partitions. However, a data area cannot contain more than one partition of a given parent table. (See Figure 2.)

Figure 2. The boxes represent physical data areas that can be included in periodic offline maintenance processing.

If multiple tables in a database are partitioned, they are not required to be split by the same partitioning criteria.

After converting from a non-partitioned table to a partitioned table, most existing application programs will continue to run with no change and the same performance as before. Only programs that contain SQL Data Definition Language (DDL) statements (such as CREATE TABLE) will be affected. 

Once a table has been partitioned, the physical child tables become accessible as part of the full logical parent or can be viewed as separate tables. This is accomplished by assigning a different table ID and SQL name for each child partition. New applications can be written to access one or more of the child tables. This feature could be used to provide continued access to one region when another region is unavailable or to reduce the amount of data to be processed.

In addition to child processing, an additional partitioning feature is available that allows a program to access any available partitions of the table even though other partitions are unavailable due to system maintenance or other tasks. The access utilizes a wildcard specification and does not require the user to know which partitions are available at program execution time.

An attempt to retrieve data that receives a "not found" condition would indicate that no matching rows exist in the targeted table or table partition. However, for programs that access the "wildcard" partition identifier (assigned by the DBA), a "not found" condition can also indicate that the target data could reside in a partition that is currently unavailable. (See Figure 3.)

This option may be very useful but should be accompanied with program logic that will handle the large variety of possibilities occurring when a given row is not accessible because its partition is currently unavailable. There are a few return codes that have been added to handle these new scenarios.

Figure 3. When a table partition is made inaccessible in order to process offline scheduled utility processing, the remaining partitions are available for production activity by using the specific child partition table name or by using the user-defined wildcard identifier.

Additional Information

For more information about configuring your environment for partitioned tables, please review the following documentation:
Table Partitioning

For some Case Studies showing reasons for implementing Table Partitioning, please see knowledge article " Datacom Table Partitioning - Case Studies"
As always, please contact Broadcom support for Datacom if you have further questions.