Browse Prior Art Database

Efficient Handling of Changes to a Common Workload Management System in a Shared-Nothing Distributed RDBMS

IP.com Disclosure Number: IPCOM000131962D
Original Publication Date: 2005-Nov-24
Included in the Prior Art Database: 2005-Nov-24
Document File: 6 page(s) / 236K

Publishing Venue

IBM

Abstract

This invention minimizes the performance impact of shared workload management changes in a distributed RDBMS while still allowing for traditional unit of work scope of control over such changes (e.g. COMMIT or ROLLBACK). It achieves this by postponing the activation of definition changes until the actual time that the unit of work containing the change is formally committed, allowing for existing definitions to continue to be used in the interim.

This text was extracted from a PDF file.
At least one non-text object (such as an image or picture) has been suppressed.
This is the abbreviated version, containing approximately 52% of the total text.

Page 1 of 6

Efficient Handling of Changes to a Common Workload Management System in a Shared-Nothing Distributed RDBMS

Disclosed is a mechanism for efficient handling of changes to a workload management system used in common by all database partitions in a shared-nothing distributed RDBMS. When a database partition of the RDBMS starts up, it loads the common workload management definitions from a central repository into memory on each database partition. This list is designated as the "committed list". Fig. 1a depicts a partitioned database system where the "committed list" is loaded from the permanent tables on the disk into memory on the catalog partition. Each remote partition that starts up gets the "committed list" from the catalog partition and loads it into its memory (Fig. 1b).

Shared Memory

Shared Memory

WLM Def n's (committed)

 Central Repository

WLM Defn's (committed)

WLM Defn's (committed)

Catalog Partition

Partition 1

Shared Memory

Partition 2

Figure 1a

1

[This page contains 1 picture or other non-text object]

Page 2 of 6

Shared Memory

WLM Defn's (committed)

Shared Memory

WLM Def n's (committed)

 Central Repository

Catalog Partition

Partition 1

Shared Memory

WLM Defn's (committed)

Partition 2

Figure 1b

The RDBMS performs all workload evaluation and processing using the "committed list" at each database partition.

A change in the common workload management definitions is done through an SQL statement. When this SQL statement occurs, the RDBMS acquires an exclusive lock on the central repository of the definitions before making changes to the definitions. The RDBMS then creates a new list of definitions, designated as the "uncommitted list", that includes the uncommited changes. The RDBMS then sends the "uncommitted list" to be stored at each active database partition as such.

Fig. 2 depicts a partitioned database system where a workload management definition change has occurred, an "uncommitted list" is created at the catalog partition, and then sent to each remote partition.

2

Page 3 of 6

Shared Memory

WLM Defn's (committed)

Shared Memory

WLM Defn's (committed)

WLM Defn's (uncommitted)

 Central Repository

WLM Defn's (uncommitted

WLM Defn's (uncommitted

Catalog Partition

Partition 1

Shared Memory

WLM Defn's (committed)

Partition 2

Figure 2

When the unit of work containing the SQL statement that changed the workload management definitions ends, the "uncommitted list" is either freed if a rollback has occurred or the existing "committed list" is replaced if a commit has occurred. This processing is done as part of the normal end of unit of work processing at each database partition, i.e. as part of the rollback or commit logic executed on each database partition. Fig. 3 depicts a partitioned database system where a workload management definition change has occurred and the unit of work that contains the definition change has been committed. It shows that the previously "uncommitted list" now becoming the "committed list" and the previo...