Table Partitioning

Document ID : KB000055778
Last Modified Date : 14/02/2018
Show Technical Document Details

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 your counterparts in the industry, you must 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 of our customers require production-level processing around-the-clock.

The table partitioning concept addresses these needs for Advantage CA-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.

Beginning with this article, Advantage CA-Datacom eNews will introduce this capability in a series of articles that will explain table partitioning topics as follows:

  • What is Table Partitioning?
  • How will it benefit my IT organization?
  • How do I implement it?

Table partitioning was introduced with the delivery of Advantage CA-Datacom r11. This feature allows you to store rows of a single table in multiple data areas. This concept unleashes 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, you will provide a level of data segmentation that can be utilized to allow 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

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

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. Currently, SQL DDL is not supported for partitioned tables.

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 Ideal r11, we will be delivering a simple run time command that can be used to change the execution of an Ideal program "on the fly" from accessing the full parent table to a selected child table.

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

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.

In the next table partitioning article, we will present several case studies that will assist you in developing a plan for your organization.