Datacom Table Partitioning

Products

Datacom

Issue/Introduction

This article is the second part of a series on Table Partitioning. In many cases, it is easier to articulate how a particular feature or function is implemented by giving specific case study examples. This article describes several case studies using fictional companies so that you can get an idea how this feature can be administered in your Datacom/DB Database 24x7 strategy.

Environment

Datacom 15.1

Resolution

Case Study 1

Global Apparel Market (GAM) has operations in New York, London, and Singapore. Their business requirements are not compatible with a scheduled outage at the same point in time because each location must have full production capabilities during business hours. To ensure they receive optimum performance from applications that read data sequentially, offline data re-organization is essential. Characteristics of their business for all locations include:

Heavy online processing during business hours
Offline batch processing after business hours
Same production business applications
Many applications require sequential record processing
Offline weekly data re-organization utility is executed

There are a few performance issues that are raised with this scenario. First, when London is running their offline batch processing, it would contend with resources used during peak online processing in New York. The same contention will occur among all three cities during a 24-hour cycle. Secondly, since production processing must cease during the weekly data re-organization, two of the locations will have to interrupt their production online or batch processing until the re-organization utility has completed.

In order to ensure full production access during peak local hours, GAM can partition their tables by city. This will segregate the data so that offline scheduled data re-organizations will not adversely affect local production workloads as described above (See Figure 1.)

Figure 1: Tables can be partitioned to satisfy regional scheduled maintenance requirements.

Case Study 2

Federated Phone Company has 25 million customers across the U.S. Their customer billing system manages a very large database with heavy sequential processing requirements that include numerous add/delete requests. Since this type of processing requires regular database re-organizations to optimize performance levels, they are having difficulty maintaining production service level agreements. Business requirements include:

Billing system has a large number of database records
Primary process method: sequential
Heavy add/delete database record activity

Since production database activity must be halted during the offline database re-organization "window", it is essential to limit the size of this "window" as much as possible. With the table partitioning feature, the re-organization utility can be run against multiple partitions at the same time. This method, along with the parallel re-org feature, can bring immediate relief for those customers that have very limited scheduled downtimes (See Figure 2.)

Figure 2: Tables can be partitioned to speed up the data re-organization process. When combined with the parallel re-org feature, scheduled downtimes can be significantly reduced.

Case Study 3

Jiffy Auto Repair has affiliates in the U.S. and Europe. Since this corporation has been in existence for quite some time, their data volume requirements are quite extensive because much of the data is historical. Their biggest challenge is that as their business expands, their offline database re-organization "window" expands. Business application signature:

Majority of table data is historical
Current year data has minimal updates
Current month data has regular updates

In this case, table partitions can be defined for each of three data access requirements. Since there is no update activity against the historical data, there is no need to re-organize the data in this partition. Separating the historical data from the highly volatile data will drastically reduce the downtime requirements for this particular scenario. Current year data and current month data can be scheduled for monthly and weekly data re-organizations respectively (See Figure 3.)

Figure 3. By separating aged data from current data, you can limit the time required for scheduled maintenance.

Case Study 4

Affiliated Art Brokers provides leads and detailed information to prospective art dealers around the world. They have expanded their web presence so they now provide access to their database around the clock on a subscription basis. This is a competitive business, it is very important to provide access to as much data as possible. Their requirements include:

24x7 data access
Optimized search method
Fast recovery from unscheduled outages

Since access to as much data as possible is critical to the success of the company, a plan must be put in place to extend data access capabilities even when scheduled outages occur. By splitting up their data, they are still able to provide customers continuous access to the target data. However, during scheduled re-organizations (monthly) against one data partition at-a-time, they continue to provide access to the other data partitions. To facilitate this, the application program would need to add logic that uses the "wildcard" table ID (assigned by the DBA) during the scheduled outage. This would allow access to all available data partitions (See Figure 4.)

Figure 4. For organizations that require continuous data availability, partitioning allows scheduled maintenance for a portion of the data while the other partitions have full database access.

Additional Information

For an introduction to Table Partitioning, please see knowledge article " Explanation of Datacom Table Partitioning" .
As always, please contact Broadcom support for Datacom if you have further questions.