Computer Associates Think, Inc.

Platinum Technology, Inc.

Amando B Isip, Jr

Stephen J Weaver

Christopher F Parker

Liza N Ramos

Steve Donovan

A method for reorganizing data is described. The method includes the steps of reading each record of a source file and writing each record to a destination file. The method also includes the step of creating a log file containing selected log records. Each log record is associated with a change to be made to the destination file. The method further includes the steps of reading each log record of the log file and processing each record of the log file, thereby effecting the associated change to the destination file. The method still further includes the step of replacing the source file with the destination file. An apparatus and article of manufacture are also disclosed for implementing the steps of the method.

System and method for reorganizing stored data

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.

Method for loading rows into a database table while enforcing constraints

A method for maintaining exception tables for a CHECK utility includes generating a control statement including key words and associated values for predetermined exception table parameters. The control statement is parsed by the CHECK utility which automatically builds and executes SQL statements required to create and drop exception tables.

Method for maintaining exception tables for a check utility

Constraint enforcement identifies rows containing a value violating a constraint. A SQL DELETE statement is generated for each row violating the constraint utilizing an index value associated with each row. Initialization of a CHECK utility performing constraint enforcement includes identifying an index defined for the table to be checked as well as the column name for the index value to facilitate generation of the SQL DELETE statements using the index column name and value. Generating the SQL delete statement utilizing an index value associated with each row in the database table rather than by utilizing the value causing the constraint violation eliminates the need to read the entire database table to execute the DELETE statement.

Method for enforcing integrity constraints in a database table using an index

Performance of constraint checking is enhanced by dynamically determining an optimal process to perform the checking operation. If additional dataspace can be allocated, the parent index values are stored in the dataspace and a binary search is performed between the extracted foreign keys and the dataspace, thereby eliminating the need for a sort operation on extracted foreign keys to perform constraint checking. If all of the parent indexes needed for constraint enforcement cannot be stored in dataspace, then as many parent indexes as possible are stored in dataspace and multiple reader and multiple sort tasks are utilized to process the remaining parent indexes. Depending on the availability of resources and the characteristics of the database table, determined dynamically, complete or partial optimization of the constraint checking operation is accomplished.

Dynamic determination of optimal process for enforcing constraints

In response to a constraint violation in a row of a database table, an output file is generated including the characteristics of the table containing the row in error as well as an SQL UPDATE statement for the row. The SQL UPDATE statement includes the column values in the row which can be corrected by the user, the user modified SQL UPDATE statement being subsequently executed to repair the constraint violation.

Method for repairing constraint violations in a database management system

A recover or reload utility determines if a check utility is to be executed. If a check utility is to be executed, the recover or reload utility records the dependent tablespace names to virtual storage allocated to a job step instead of to virtual storage allocated to a subtask. After the recover or reload utility performs its function, if a check utility is next, the database operating system will not freemain the virtual storage (e.g, data area) into which the recover or reload utility has recorded the names of the dependent tablespaces when the subtask ends. Upon termination of the recover or reload utility, the persistent data area is available for the check utility to use.

Growing community of inventors

Richardson, TX, United States of America

Amando B Isip, Jr