Method for editing the contents of a DB.sub.2 table using an editproc manager

• US 5,396,623 A
• Filed: 10/30/1992
• Issued: 03/07/1995
• Est. Priority Date: 10/30/1992
• Status: Expired due to Term

• Current Assignee: BMC Software Incorporated (KKR & Co., Inc.)
• Original Assignee: BMC Software Incorporated (KKR & Co., Inc.)
• Inventors: Veazey, Edward J. Jr., McCall, Annette B.
• Primary Examiner(s): Kulik, Paul V.
• Assistant Examiner(s): HOMERE, JEAN RAYMOND
• Application Number:
  US07/969,014
• Time in Patent Office: 858 Days
• Field of Search: 395/600
• US Class Current: 1/1
• CPC Class Codes: G06F 16/21 Design, administration or maintenance of databases
• CPC Class Codes: G06F 16/25 Integrating or interfacing systems involving database management systems
• CPC Class Codes: G06F 16/9017 using directory or table look-up use of a directory or look-up table in file systems G06F16/13
• CPC Class Codes: Y10S 707/99942 Manipulating data structure, e.g. compression, compaction, compilation

Abstract

A computer-executed method permits one or more of the following three functions to be performed on a DB2 data table: (1) dynamically altering (replacing) the EDITPROC parameter in the DB2 catalog definition of the DB2 data table, without the necessity of unloading the data from the table; (2) dynamically adding an EDITPROC to an existing DB2 data table whose DB2 catalog definitional information does not include an EDITPROC parameter; and (3) dynamically adding a column (field) to an existing DB2 data table that already includes an EDITPROC. The method does not make the assumption, made by IBM'"'"'s DSNUTILB utility program, that the DBD records for a given database are untrustworthy and that all definitional information therefore must be verified. The method makes use of code to trick DB2 into bringing certain definitional information for the data table in question, including the DBD and OBD records, into memory from DASD (disk) storage. The method also makes use of a swapping routine which provides a dynamic procedure for exchanging DBD and OBD information between the DB2 table to be modified and a shadow DB2 table which already contains the modified DBD and OBD information.

Claims

1. A method of dynamically modifying a DB2 target table, comprising the steps of:

(a) creating a shadow table having all the structures and attributes of the target table except for a modification wherein the modification comprises one or more of;

(1) replacing an existing EDITPROC of the target table with a new EDITPROC;

(2) adding an EDITPROC to the target table;

or

(3) adding a column to the target table that has an EDITPROC;

(b) retrieving a set of DB2 information so that the desired modification can be performed;

(c) updating the target table attributes using the set of information from step (b) so that the target table evidences the desired modification; and

(d) performing an SQL COMMIT of the modified target table to DB2.

2. The method of claim 1, wherein the modification comprises replacing an existing EDITPROC of the target table with a new EDITPROC.

3. The method of claim 1, wherein the modification comprises adding an EDITPROC to the target table.

4. The method of claim 1, wherein the modification comprises adding a column to the DB2 table which has an EDITPROC.

5. The method of replacing an existing EDITPROC, EP-1, of a DB2 target table, referred to as Table X, with a replacement EDITPROC, referred to as EP-2, comprising the steps of:

(a) creating a shadow table, referred to as Table Y, that has EP-2 as its EDITPROC and that otherwise duplicates the structures and attributes of Table X;

(b) modifying Table X DB2 catalog information to indicate that EP-2 is the EDITPROC for Table X instead of EP-1;

(c) retrieving DBD and OBD information for Table X and Table Y into working storage;

(d) exchanging the OBD information for Table X and Table Y to redesignate Table X as Table Y and vice versa;

(e) Committing the modified catalog and OBD information for Table X; and

(f) deleting Table Y from the user database.

6. A method of adding an EDITPROC, EP-1, to a DB2 target table, Table X, in a user database that does not have an EDITPROC comprising the steps of:

(a) creating a shadow table, Table Y, that has EP-1 as its EDITPROC and that otherwise duplicates the structures and attributes of Table X;

(b) modifying DB2 catalog information about Table X to signify that Table X has EDITPROC EP-1 by updating Table X DB2 catalog EDITPROC information and EDITPROC record length information;

(c) retrieving DBD and OBD information for Table X and Table Y into working storage;

(d) exchanging the OBD information for Table X and Table Y to redesignate Table X as Table Y and vice versa;

(e) updating INDEXES and RI constraints fansets associated with the target table to reflect the added EDITPROC;

(f) Committing the modified catalog and OBD information for Table X; and

(g) deleting Table Y from the user database.

7. A method as in claim 6, wherein step 6(b) of modifying the DB2 catalog information describing Table X to make room for information indicating that Table X has an EDITPROC includes the substeps of:

6(b)(1) translating said catalog information describing Table X from DB2 internal format into IBM 370 hardware format;

6(b)(2) changing a record-length entry in said catalog information to increase the record length shown in said entry by 10 bytes; and

6(b)(3) retranslating said catalog information back into DB2 internal format.

8. A method of adding a column, referred to as CA, to a user table, referred to as Table X, in a DB2 database that has an EDITPROC, referred to as EP-1, comprising the steps of:

(a) creating a shadow table, Table Y, that has EP-1 as its EDITPROC, that includes the column CA, and that otherwise duplicates the characteristics of Table X;

(b) modifying DB2 catalog information for Table X to indicate that Table X no longer has an EDITPROC;

(c) retrieving DBD and OBD information for Table X into working storage;

(d) modifying the DBD information and OBD information for Table X to indicate that Table X no longer has an EDITPROC, and Committing the modified DBD and OBD information;

(e) adding a column to Table X and Committing the modified Table X;

(f) retrieving DBD and OBD information for the modified Table X into working storage;

(g) exchanging OBD information for the modified Table X and Table Y to redesignate the modified Table X as Table Y and vice versa;

(h) modifying a DB2 catalog description of Table X to indicate that EP-1 is the EDITPROC for Table X;

(i) Committing Table X; and

(j) deleting Table Y.

9. A method of exchanging OBD information between two DB2 database tables Table X and Table Y, comprising the steps of:

(a) obtaining a work area in key 0 CSA;

(b) copying a set of SWAP instructions to the CSA work area from a main routine;

(c) passing program control to the copied SWAP instruction set from the main routine so that the copied instructions can be executed in secondary address space mode;

(d) searching through Table X and Y'"'"'s DBD records for a OBD information to be swapped;

(e) swapping the Table X'"'"'s OBD information and the Table Y'"'"'s OBD information so that Table X contains Table Y'"'"'s OBD information and Table Y contains Table X'"'"'s OBD information;

(f) returning program control to the main routine; and

(g) releasing the CSA work area.

10. A method of updating fanset information for a DB2 database table, Table X, comprising the steps of:

(a) obtaining a work area in key 0 CSA;

(b) copying a set of FANSET instructions to the CSA work area from a main routine;

(c) passing program control to the copied FANSET instruction set from the main routine so that the copied instructions can be executed in secondary address space mode;

(d) searching through a Table X'"'"'s DBD records for a OBD fanset information to be updated;

(e) modifying the Table X'"'"'s fanset information to evidence the inclusion of an EDITPROC in Table X;

(f) returning program control to the main routine; and

(g) releasing the CSA work area.