System for accessing database tables mapped into memory for high performance data retrieval

• US 6,463,439 B1
• Filed: 07/15/1999
• Issued: 10/08/2002
• Est. Priority Date: 07/15/1999
• Status: Expired due to Term

• Current Assignee: CGI Technologies and Solutions Incorporated (CGI Group)
• Original Assignee: American Management Systems, Incorporated (CGI, Inc.)
• Inventors: Dahlberg, Mikael
• Primary Examiner(s): Shah, Sanjiv
• Application Number:
  US09/353,395
• Time in Patent Office: 1,181 Days
• Field of Search: 707/200, 707/100, 707/101, 707/203, 707/102, 707/10, 707/2, 707/1
• US Class Current: 707/624
• CPC Class Codes: G06F 16/217 Database tuning G06F16/2282 takes precedence; database performance monitoring G06F11/3409
• CPC Class Codes: G06F 16/24539 using cached or materialised query results
• CPC Class Codes: G06F 16/24547 Optimisations to support specific applications; Extensibility of optimisers
• 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

A system and method that enables quick access to large volumes of data on a realtime basis and is totally transparent to application programs that use the data. This is accomplished by placing information extracted from the database into a master file stored in a data storage device and then loaded into memory for access by application programs. When information in the database changes the corresponding information is updated using an incremental file and an index file that are then loaded into memory for access by application programs. The master file, index file and incremental file are linked in such as fashion to enable quick access to data desired.

Claims

1. A method of accessing a database, comprising:

placing information extracted from the database into a first data structure in a storage device, wherein the first data structure is in a master file and the second data structure is in an incremental file; and

updating the information in the storage device periodically or when requested when a change in the information occurs in the database by placing the updated information into a second data structure in the storage device which is linked to the first data structure; and

wherein the first data structure and the second data structure are linked by a third data structure, which is in an index file; and

wherein the placing information extracted from the database into a first data structure in the storage device comprises;

creating a first system query language statement;

extracting information from the database using the system first query language statement;

sorting the extracted information based on primary key;

placing the sorted information into the master file in the storage device; and

sending a message to said plurality of application programs that the master file has been created.

2. A method of accessing a database as recited in claim 1, further comprising:

placing the data contained in the master file, the index file and the incremental file into memory for access by a plurality of application programs.

3. A method of accessing a database as recited in claim 2, further comprising:

updating the information in memory when an incremental extraction is done.

4. A method of accessing a database as recited in claim 3, wherein the updating the information in the storage device periodically or when requested when a change in the information occurs in the database, further comprises:

creating a second system query language statement;

extracting information from the database using the second system query language statement;

extracting update information from a table indicating by key which information of the extracted database information has been updated, added or deleted;

converting the extracted information and the extracted update information into update data structures;

sorting the update data structures by primary key;

comparing the update data structures to the master file;

creating another index file based on the comparison of the update data structures to the master file;

creating another incremental file based on the master file and the index file; and

sending a message to said plurality of application programs that another index file and another incremental file have been created.

5. A method of accessing a database as recited in claim 4, wherein the information can be effective dated data and accessed by effective date.

6. A method of accessing a database, comprising:

creating a first system query language statement;

extracting information from the database using the system first query language statement;

sorting the extracted information based on primary key;

placing the sorted information into a master file in a data storage device;

sending a message to a plurality of application programs that the master file has been created; and

updating the information in the data storage device periodically or when requested when a change in the information occurs in the database, comprising;

creating a second system query language statement;

extracting information from the database using the second system query language statement;

extracting update information from a table indicating by key which information of the extracted database information has been updated, added or deleted;

converting the extracted information and the extracted update information into update data structures;

sorting the update data structures by primary key;

comparing the update data structures to the master file;

creating another index file based on the comparison of the update date structures to the master file;

creating another incremental file based on the master file and the index file; and

sending a message to said plurality of application programs that another index file and another incremental file have been created.

7. A system to access a database, comprising:

an Xref storage manager to extract on-the-fly information from the database and place the information into a first data structure in a storage device and to update on-the-fly the information in the storage device periodically or when requested when a change in the information occurs in the database by placing the updated information into a second data structure in the data storage device which is linked to the first data structure;

wherein the information extracted from the database is convertible to different data formats and different data values; and

wherein the first data structure is in a master file and the second data structure is in an incremental file;

a plurality of application programs;

a Xref Interface to interface with the plurality of application programs and retrieve information stored in the master file and incremental file requested by the plurality of application programs;

wherein the first data structure and the second data structure are linked by a third data structure which is in an index file; and

wherein the Xref Storage Manager creates a system query language statement and uses this system query language statement to retrieve the information from the database, wherein the Xref Storage Manager sorts the extracted information based on primary key and places the sorted information into the master file in the storage device, and wherein the Xref Storage Manager sends a message to said plurality of application programs that the master file has been created.

8. A system to access a database as recited in claim 7, wherein the Xref interface places the data contained in the master file, the index file and the incremental file into memory for access by a plurality of application programs.

9. A system to access a database as recited in claim 8, wherein the Xref Interface updates the information in memory when an incremental extraction is done.

10. A system to access a database as recited in claim 9, wherein the Xref Storage Manager updates the information in the storage device periodically or when requested when a change in the information occurs in the database by extracting update information from a table indicating by key which information of the extracted database information has been updated, added or deleted and creating another incremental file, and another index file based on the master file and the update information, and wherein the Xref Storage Manager sends a message to said plurality of application programs that another index file and another incremental file have been created.

11. A system to access a database as recited in claim 10, wherein the information can be effective dated data and accessed by effective date.

12. A computer program embodied on a computer-readable medium to access a database, comprising:

an Xref storage manager code segment to extract information from the database and place the information into a first data structure in a data storage device and to update the information in the data storage device periodically or when requested when a change in the information occurs in the database by placing the updated information into a second data structure in data storage device which is linked to the first data structure, wherein the first data structure is in a master file and the second data structure is in an incremental file; and

wherein the information extracted from the database is convertible to different data formats and different data values;

a plurality of application program code segments;

a Xref Interface code segment to interface with the plurality of application programs and retrieve information stored in the master file and incremental file requested by the plurality of application programs;

wherein the first data structure and the second data structure are linked by a third data structure which is in an index file; and

wherein the Xref Storage Manager code segment creates a system query language statement and uses this system query language statement to retrieve the information from the data, wherein the Xref Storage Manager code segment sorts the extracted information based on primary key and places the sorted information into the master file, and wherein the Xref Storage Manager code segment sends a message to said plurality of application programs that the master file has been created.

13. A computer program embodied on a computer-readable medium to access a database as recited in claim 12, wherein the Xref interface code segment places the data contained in the master file, the index file and the incremental file into memory for access by a plurality of application programs, and the Xref Interface code segment updates the information in memory when an incremental extraction is done.

14. A computer program embodied on a computer-readable medium to access a database as recited in claim 13, wherein the Xref Storage Manager code segment updates the information in the storage device periodically or on request when a change in the information occurs in the database by extracting update information from a table indicating by key which information of the extracted database information has been updated, added or deleted and creating another incremental file, and another index file based on the master file and the update information, and wherein the Xref Storage Manager code segments sends a message to said plurality of application program code segments that another index file and another incremental file have been created.

15. A computer program embodied on a computer-readable medium to access a database as recited in claim 14, wherein the information can be effective dated data and accessed by effective date.

16. A method of accessing a database, comprising:

placing information extracted from the database into a first data structure in a storage device, wherein said placing comprises

creating a first system query language statement,

extracting information from the database using the system first query language statement,

sorting the extracted information based on primary key,

placing the sorted information into the master file in the storage device, and

sending a message to said plurality of application programs that the master file has been created; and

updating the information in the storage device periodically or when requested when a change in the information occurs in the database by placing the updated information into a second data structure in the storage device which is linked to the first data structure, wherein the first data structure and second data structures are tables, wherein the first data structure is a master file and the second data structure is an incremental file, wherein the first data structure and second data structure are accessible by a plurality of application programs, wherein the first data structure and the second data structure are linked by a third data structure that is an index file.

17. A method of accessing a database as recited in claim 16, further comprising:

placing the data contained in the master file, the index file and the incremental file into memory for access by a plurality of application programs.

18. A method of accessing a database as recited in claim 17, further comprising:

updating the information in memory when an incremental extraction is done.

19. A method of accessing a database as recited in claim 18, wherein the updating the information in the storage device periodically or when requested when a change in the information occurs in the database, further comprises:

creating a second system query language statement;

extracting information from the database using the second system query language statement;

extracting update information from a table indicating by key which information of the extracted database information has been updated, added or deleted;

converting the extracted information and the extracted update information into update data structures;

sorting the update data structures by primary key;

comparing the update data structures to the master file;

creating another index file based on the comparison of the update data structures to the master file;

creating another incremental file based on the master file and the index file; and

sending a message to said plurality of application programs that another index file and another incremental file have been created.

20. A method of accessing a database as recited in claim 19, wherein the information can be effective dated data and accessed by effective date.

21. A method of accessing a database, comprising:

placing information extracted from the database into a first data structure in a storage device, wherein said placing comprises

creating a first system query language statement,

extracting information from the database using the system first query language statement,

sorting the extracted information based on primary key,

placing the sorted information into the master file in the storage device, and

sending a message to said plurality of application programs that the master file has been created; and

updating the information in the storage device periodically or when requested when a change in the information occurs in the database by placing the updated information into a second data structure in the storage device which is linked to the first data structure.