Method for loading rows into a database table while enforcing constraints

• US 6,427,143 B1
• Filed: 04/10/1998
• Issued: 07/30/2002
• Est. Priority Date: 04/10/1998
• Status: Expired due to Term

• Current Assignee: CA, Inc. (d/b/a CA Technologies) (Broadcom, Inc.)
• Original Assignee: Computer Associates Think Inc. (Broadcom, Inc.)
• Inventors: Donovan, Steve, Weaver, Stephen J., Ramos, Liza N., Isip, Amando B. Jr.
• Primary Examiner(s): SHAH, SANJIV
• Application Number:
  US09/058,754
• Time in Patent Office: 1,572 Days
• Field of Search: 707/1, 707/2, 707/4, 707/5, 707/9, 707/100, 707/101, 707/102, 707/104, 707/7, 707/10, 707/201, 707/200, 707/203, 707/202, 395/702
• US Class Current: 1/1
• CPC Class Codes: G06F 16/284 Relational databases
• CPC Class Codes: Y10S 707/99931 Database or file accessing
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface

Abstract

Data to be loaded into a table of a database is read from an input file by a LOAD utility including a CHECK subutility that performs constraint checking on each row prior to loading the row into the table. During initialization of the CHECK subutility, the DB2 catalog is read to identify any constraints that apply to the data to be loaded. If any check constraints apply, check constraint routines are built and compiled by the CHECK subutility. If any referential integrity constraints apply, the key values for the parent index of each referential integrity constraint are read and stored in an additional dataspace allocated by the CHECK subutility so that the foreign key values from each row to be loaded can be located by the CHECK subutility and compared against the parent index values prior to loading the data into the table. When the constraint checking for the loading of data is complete, the additional dataspace allocated by the CHECK subutility is released.

Claims

1. A method for loading data into a database, comprising:

prior to loading the data into the table,

receiving data to be loaded into a table;

identifying a constraint to be applied to the data;

allocating dataspace outside the address space initially allocated by a load function;

storing a value of the constraint;

comparing the value of the constraint and a value of the data; and

determining whether to load the data into the table as a function of the comparison.

2. A method for loading a record into a database, comprising:

initializing a load function;

initializing a check function;

reading a record in an input file;

allocating dataspace outside the address space initially allocated by a load function;

determining, prior to loading the record, if a constraint violation exists for the record via the check function; and

if no constraint violation exists, loading the record into the database via the load function and passing an index key for the record to a sort function.

3. The method according to claim 2, further comprising the step of, if a constraint violation exists, discarding the record.

4. The method according to claim 2, further comprising the step of determining if an end of file condition exists for the input file.

5. The method according to claim 4, further comprising the steps of, if an end of file condition exists, sorting the index key for each record of the input file and writing each index key to an indexspace.

6. The method according to claim 2, wherein the step of initializing the check function includes determining if at least one of a check constraint and a referential integrity constraint are defined for the input file.

7. The method according to claim 6, further comprising the steps of:

if the check constraint is defined for the input file, compiling a check constraint routine as a function of the check constraint; and

if the referential integrity constraint is defined for the input file, identifying a parent index for the referential integrity constraint.

8. The method according to claim 7, further comprising the steps of, when the referential integrity constraint is defined:

allocating a dataspace for the referential integrity constraint; and

attaching a reader subtask to the parent index to identify the parent key values to be stored in the allocated dataspace.

9. The method according to claim 8, wherein the step of allocating the dataspace includes allocating dataspace in addition to address space utilized by the load function.

10. The method according to claim 8, wherein the step of allocating the dataspace is performed via a service of an operating system of a computer system including a database management system.

11. The method according to claim 10, wherein the service includes an IBM MVS service, the allocated dataspace providing supplemental memory for constraint enforcement by the check function prior to loading the record into the database, a size of the allocated dataspace being determined as a function of a relative byte address of the parent index.

12. The method according to claim 6, wherein the step of determining at least one of the check constraint and the referential integrity constraint are defined for the input file is performed via reading a catalog of a database system, the database system including the database to be loaded.

13. The method according to claim 12, wherein the catalog includes a DB2 catalog of a DB2 database management system.

14. The method according to claim 2, wherein the step of reading the record includes reading the record from the input file and providing an address of the record to the check function.

15. The method according to claim 14, wherein the step of determining if the constraint violation exists includes:

determining if a check constraint is defined for the input file;

if a check constraint is defined, executing a compiled check constraint routine on the record;

determining if a referential integrity constraint is defined for the input file; and

if a referential integrity constraint is defined,

locating a foreign key for the record, and

performing, using a dataspace allocated in addition to address space utilized by the load function, a compare operation using the foreign key to determine one of a pass condition and a fail condition for the record.

16. A memory for storing data for access by an application program being executed on a data processing system for loading a record of an input file into a database, comprising:

a load function stored in the memory;

a check function stored in the memory;

a sort function stored in the memory; and

a dataspace allocated in the memory for constraint enforcement, the dataspace being outside the address space initially allocated by the load function;

wherein, utilizing the dataspace, the check function performs constraint enforcement, prior to loading, to determine if the record of the input file violates a constraint, and if no constraint violation exists, the load function loads the record into the database and passes an index key for the record to the sort function.

17. The memory according to claim 16, wherein the memory includes virtual storage in an IBM MVS operating system.

18. The memory according to claim 16, wherein the sort function includes a plurality of sort functions and an associated buffer for each of the plurality of sort functions, the plurality of sort functions allowing concurrent processing of records of the input file to write the index key for each record into an indexspace of the memory.

19. The memory according to claim 16, wherein the allocated dataspace includes a plurality of dataspaces, each of the plurality of dataspaces being associated with a predetermined referential integrity constraint.

20. The memory according to claim 16, wherein the load function includes the check function.

21. A method for loading a record into a database, comprising:

prior to loading a record into a database,

identifying a parent index for integrity constraint check;

determining a size of the parent index;

allocating a dataspace in memory to temporarily store one or more parent key values associated with the parent index, the dataspace being outside the address space initially allocated;

storing the one or more parent key values in the dataspace;

reading a record to load;

if referential integrity check is defined,

extracting a foreign key value in the record; and

comparing the foreign key value against the parent key value in the dataspace;

if the foreign key value corresponds to the parent key value,

loading the record into a tablespace;

building a key for a tablespace index; and

if the foreign key value does not correspond to the parent key value,

discarding the record.