Browse Prior Art Database

Technique to Allow DB2 Utilities and Commands to Run While SQL Applications have a Table Space Locked

IP.com Disclosure Number: IPCOM000105850D
Original Publication Date: 1993-Sep-01
Included in the Prior Art Database: 2005-Mar-20
Document File: 4 page(s) / 102K

Publishing Venue

IBM

Related People

Bonner, CR: AUTHOR [+5]

Abstract

The following figure illustrates the claim and drain concepts of this disclosure. It applies to a LOAD utility and a SQL application serializing on the same partition of a partitioned table space. It is assumed that the application is bound with BIND parameters:

This text was extracted from an ASCII text file.
This is the abbreviated version, containing approximately 49% of the total text.

Technique to Allow DB2 Utilities and Commands to Run While SQL Applications have a Table Space Locked

      The following figure illustrates the claim and drain concepts
of this disclosure.  It applies to a LOAD utility and a SQL
application serializing on the same partition of a partitioned table
space.  It is assumed that the application is bound with BIND
parameters:

ACQUIRE(ALLOCATE) and RELEASE(DEALLOCATE).

_____________________________________________________________________

      Time -->

      1     2    3    4    5     6    7     8    9     10   11   12
      |----|----|----|----|----|----|----|----|----|----|----|

SQL application
  Allocate                  o
  Write claim P1                 <------------------->
  Commit                                         o
  Deallocate                                                    o

Utility                             wait
  Load P1                           <--------------><-------->
_____________________________________________________________________
         SQL vs utility serialization, same partition

The Figure is an example showing a drain by a utility of a partition
being accessed by a SQL application making updates.

Events at time tn.

Time t1   An allocation duration partition lock is obtained by the
SQL
          application on every partition in the table space.  This is
          normal DB2 locking.

Time t2   The SQL application makes a write claim at first reference
to:

          O    Data partition 1
          O    Index partition 1.

Time t3   LOAD initiates a drain of:

          O    CS readers, RR readers and writers on data partition 1
          O    CS readers, RR readers and writers on index partition
1.

           LOAD waits until time t6 (commit) when the write claim is
          released by the SQL application.
          LOAD could have timed out if the wait time had exceeded the
          utility time-out value.

Time t6   The application commits which results in the release of the
          write claims on data partition 1 and index partition 1.
LOAD
          successfully takes over partition 1.

Time t8   The LOAD completes and releases all drain locks on data
          partition 1 and index partition 1.

          Should the application wished to have resumed following its
          commit at time t6, it would have had to wait until the LOAD
          completes at time t8.

Time t12  The application de-allocates which releases the partition
          locks.

      Thus the utility has been able to break in on the application
at its commit poin...