COMPUTER-IMPLEMENTED METHODS, SYSTEMS, AND COMPUTER PROGRAM PRODUCTS FOR ENHANCED BATCH MODE PROCESSING OF A RELATIONAL DATABASE

• US 20080201290A1
• Filed: 02/16/2007
• Published: 08/21/2008
• Est. Priority Date: 02/16/2007
• Status: Abandoned Application

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Ponmudi, Rajamanickam
• Application Number:
  US11/675,654
• Publication Number:
  US 20080201290A1
• Time in Patent Office: Days
• US Class Current: 707/1
• CPC Class Codes: G06F 16/217 Database tuning G06F16/2282 takes precedence; database performance monitoring G06F11/3409

Abstract

Computer-implemented methods, systems, and computer program products for enhanced batch mode processing of a relational database are provided. A computer-implemented method includes creating a sequential file for a relational table in a relational database, receiving an input file and a database query for the relational table, and, if needed, sorting the input file and the sequential file on a primary key or a non-key. The computer-implemented method also includes generating a temporary file for processing the input file and the sequential file and executing an operation for the database query on the sorted input file and the sorted sequential file within the temporary file. The computer-implemented method also includes storing output of the operation in the temporary file and updating the relational table with the output of the temporary file.

Claims

1. A computer-implemented method for enhanced batch mode processing of relational data files, comprising:

creating a sequential file for a relational table in a relational database;

receiving an input file and a database query for the relational table;

sorting records in the input file and the sequential file on a primary key or a non-key;

generating a temporary file for processing the input file and the sequential file;

executing an operation for the database query on the sorted records of the input file and the sorted records of the sequential file within the temporary file;

storing output of the operation in the temporary file; and

updating the relational table with the output from the temporary file.

2. The computer-implemented method of claim 1, further comprising synchronizing the relational table and the sequential file to include updated data resulting from the operation, the method further comprising:

a) retrieving all records from the sequential file;

b) removing records from the sequential file where a delete indicator is present in an update indicator field of the record;

c) sorting the records resulting from steps a) and b); and

d) removing duplicate records on the primary key by retaining the most current record of the duplicate records, the most current record determined by a timestamp present in a timestamp field of the record.

3. The computer-implemented method of claim 1, wherein creating a sequential file includes generating two or more generation data groups, each of the two or more generation data groups storing updated records from different time periods;

wherein data from each of the two or more generation data groups is presented in response to the query.

4. The computer-implemented method of claim 1, wherein the database query is an enhanced structured query language command, the enhanced structured query language command specifying that the data to be processed for the operation is retrieved from the sequential file and not from the relational table.

5. The computer-implemented method of claim 1, wherein the operation is at least one of a:

process command;

update command;

move command;

select command;

insert command; and

delete command.

6. The computer-implemented method of claim 1, further comprising creating and maintaining a sequential file for each relational table in the relational database.

7. A system for enhanced batch mode processing of relational data files, comprising:

a computer processing device; and

an application executing on the computer processing device, the application performing a method, comprising;

creating a sequential file for a relational table in a relational database;

receiving an input file and a database query for the relational table;

sorting records in the input file and the sequential file on a primary key or a non-key;

generating a temporary file for processing the input file and the sequential file;

executing an operation for the database query on the sorted records of the input file and the sorted records of the sequential file within the temporary file;

storing output of the operation in the temporary file; and

updating the relational table with the output from the temporary file.

8. The system of claim 7, wherein the application further performs:

synchronizing the relational table and the sequential file to include updated data resulting from the operation, the method further comprising;

a) retrieving all records from the sequential file;

b) removing records from the sequential file where a delete indicator is present in an update indicator field of the record;

c) sorting the records resulting from steps a) and b); and

d) removing duplicate records on the primary key by retaining the most current record of the duplicate records, the most current record determined by a timestamp present in a timestamp field of the record.

9. The system of claim 7, wherein creating a sequential file includes generating two or more generation data groups, each of the two or more generation data groups storing updated records from different time periods;

wherein data from each of the two or more generation data groups is presented in response to the query.

10. The system of claim 7, wherein the database query is an enhanced structured query language command, the enhanced structured query language command specifying that the data to be processed for the operation is retrieved from the sequential file and not from the relational table.

11. The system of claim 7, wherein the operation is at least one of a:

process command;

update command;

move command;

select command;

insert command; and

delete command.

12. The system of claim 7, wherein the application further performs:

creating and maintaining a sequential file for each relational table in the relational database.

13. A computer program product for enhanced batch mode processing of relational data files, the computer program product including instructions for causing a computer to implement a method, the method comprising:

creating a sequential file for a relational table in a relational database;

receiving an input file and a database query for the relational table;

sorting records in the input file and the sequential file on a primary key or a non-key;

executing an operation for the database query on the sorted records of the input file and the sorted records of the sequential file;

generating a temporary file that includes the output of the operation; and

updating the relational table with data in the temporary file.

14. The computer program product of claim 13, further comprising instructions for synchronizing the relational table and the sequential file to include updated data resulting from the operation, the method further comprising:

a) retrieving all records from the sequential file;

b) removing records from the sequential file where a delete indicator is present in an update indicator field of the record;

c) sorting the records resulting from steps a) and b); and

d) removing duplicate records on the primary key by retaining the most current record of the duplicate records, the most current record determined by a timestamp present in a timestamp field of the record.

15. The computer program product of claim 13, wherein creating a sequential file includes generating two or more generation data groups, each of the two or more generation data groups storing updated records from different time periods;

wherein data from each of the two or more generation data groups is presented in response to the query.

16. The computer program product of claim 13, wherein the database query is an enhanced structured query language command, the enhanced structured query language command specifying that the data to be processed for the operation is retrieved from the sequential file and not from the relational table.

17. The computer program product of claim 13, wherein the operation is at least one of a:

process command;

update command;

move command;

select command;

insert command; and

delete command.

18. The computer program product of claim 13, further comprising instructions for creating and maintaining a sequential file for each relational table in the relational database.