Table Management

tableBASE complements your DB2 DBMS. Some data is best suited to standard DBMS storage and retrieval. However, data structures that are accessed over and over again perform significantly faster in memory. When there are multiple accesses per row, the table also performs best if it resides in main memory.

Types of data
Figure 63. Types of data

Certain types of data often require the housekeeping overhead provided by a DBMS like DB2; however, dynamic, or transient data (left), frequently accessed, or reference data (right) and rules data are well served by the high performance main-memory processing that tableBASE provides.

While tableBASE can improve performance in virtually any system, it is best suited to specific types of data. The most impressive benefits of tableBASE are seen with the following:

Reference Data

Reference (or semi-stable) data has a high ratio of read access compared to write access. The data may even be updated every few minutes, but there is a high volume of retrievals between update cycles. This data does not normally need to be in a database since logging of individual updates is not a requirement. Semi-stable data is ideal for in-memory processing. Some examples are pay rates, inventory codes, and rate tables.

Many different techniques have been developed for handling and using this type of data because there are many variables involved in trying to balance system performance, programming complexity, system maintenance, and reliability.

In a traditional environment without tableBASE, if one of these variables changes, then extensive programming, testing and re-testing is needed. For example, if a table increases beyond the planned maximum size, or a new variable is added, or if the table must be accessed in a different order, or by a different key.

Many of the decisions which programmers have had to make in the past, are now made automatically and dynamically by tableBASE.

Constants and Parameters

Constants, literals, and other parameter values that make up a significant part of the memory of an application are another form of reference data, which should be managed as tables rather than embedded in program logic. Typically the approach has been to place these values in memory with copy members at compile time. Without using additional I/O overhead, these values are then only available for a single task. This can increase demand for dynamic storage areas, if interactive online usage requires multiple copies of these constants for each transaction generated. Maintenance of these values is time consuming, as programs must be relinked and re-tested for every change.

With tableBASE, a single copy of these rows is instantly available to all tasks needing them, with no additional I/O overhead. Constants are maintained externally to the programs that use them, reducing maintenance requirements.

Rules Data

This is a special class of reference data that contains rules for determining which routines should be executed under a particular condition or set of conditions. Rules values of this nature have traditionally been hard-coded in application programs for optimal performance, but this approach introduces a level of program complexity which is both difficult and expensive to maintain.

With the rules for the control of the program residing in tables and accessed by tableBASE, application maintenance is greatly simplified. Examples are report distribution lists, user and password tables, and data validation tables.

Rules data is:

  • used with very high frequency and must be very efficiently integrated with program logic
  • usually small
  • changed periodically, requiring careful planning and control

Rules tables, because of their small size and frequent access, are often embedded within the programs using copy library statements. Programmers often use ad hoc techniques to maintain these programs. Changes require relinking and extensive retesting.

With tableBASE, these tables can be organized and easily controlled. The data is still available to the system at memory speeds but tables are now maintained by an integrated system and controlled by user-defined parameters, such as automatic update and backup cycles, and automatic phase-in of new table versions. This can all be done by updating tables, without changing the application software. There is no need to relink and extensively retest programs.

Transient Data

Transient data is the opposite of reference data. It is volatile and forms the bulk of memory in most online and batch applications. Once a process is complete, transient data is not retained. It will typically be regenerated each time the process is initiated. There is no perceived need to create images of the data on disk, with all the attendant overhead involved. Transient Data Tables have these characteristics:

  • data is reduced, accumulated, summarized, or reorganized from a primary data base
  • the table can be dynamically reorganized to serve different objectives
  • it is usually a temporary version of the original data, and can be discarded when the objectives are met, and regenerated when needed

Probably the most frequent use of transient data in a batch environment is the summarization of large volumes of data. Examples include sales statistics, payroll summaries, or tables of funds transferred. The traditional approach to summarization, illustrated in Figure 61, involves creating a sequential extract of the subset of records and data fields needed, followed by sorting the extract into the report order, and processing it sequentially. Figure 62 shows how tableBASE improves the performance.