Method, system, and program for extracting data from database records using dynamic code

• US 6,567,816 B1
• Filed: 03/07/2000
• Issued: 05/20/2003
• Est. Priority Date: 03/07/2000
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Garth, John Marland, Desai, Paramesh Sampatrai
• Primary Examiner(s): Metjahic, Safet
• Assistant Examiner(s): NGUYEN, CAM LINH T
• Application Number:
  US09/520,121
• Time in Patent Office: 1,169 Days
• Field of Search: 707/101-104
• US Class Current: 1/1
• CPC Class Codes: G06F 16/258 Data format conversion from or to a database
• CPC Class Codes: Y10S 707/99942 Manipulating data structure, e.g. compression, compaction, compilation
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface

Abstract

Disclosed is a system, method, and program for extracting data from a record in a first format and inserting data into a record in a second format during an insert operation. A determination is made of location information for data in at least one record in at least one column in the first format involved in the insert operation from which data will be extracted and of location information for data in at least one record in at least one column in the second format involved in the insert operation into which the data extracted from a corresponding column in the first format will be inserted. For each column from which data will be extracted, the determined location information is used to generate at least one instruction to move the data in each column in the record in the first format to each corresponding column of one record in the second format. The location information of the columns in records in the first and second formats involved in the data movement operations is encoded in the at least one instruction. The instruction may be repeated for multiple records in the first format from which data will be extracted into multiple records in the second format.

Claims

1. A method for extracting data from a record in a first format and inserting data into a record in a second format during an insert operation, comprising:

determining location information for data in at least one record in at least one column in the first format involved in the insert operation from which data will be extracted;

determining location information for data in at least one record in at least one column in the second format involved in the insert operation into which the data extracted from a corresponding column in the first format will be inserted; and

for each column from which data will be extracted, using the determined location information to dynamically generate at least one instruction to move the data in each column in the record in the first format to each corresponding column of one record in the second format, wherein the location information of each column in the records in the first and second formats involved in the data movement operations is encoded in the at least one instruction, and wherein the at least one instruction is dynamically generated in response to a request to insert data into the record in the second format.

2. The method of claim 1, wherein the instruction may be repeated for multiple records in the first format from which data will be extracted.

3. The method of claim 1, wherein records in the first format have at least one of a different number of columns and different column data types than records in the second format.

4. The method of claim 1, wherein the location information for data in records in each column in the first format involved in the insert operation is determined from a first table indicating an offset of the column from a start of the record and a length of the column and the location for data in each column in the second format involved in the insert operation is determined from a second table indicating a location of the corresponding column in records in the second format into which the data extracted from the first column is inserted, wherein the first table and the second table are not the tables from which data is extracted or into which data is inserted.

5. The method of claim 1, wherein each column from which data is extracted is from one record in a table including records in the first format and wherein each column into which data is inserted comprises a new record in an index in the second format.

6. The method of claim 1, further comprising:

searching at least one table for records that match a search criteria, wherein each record from which data is extracted comprises records located in the search that match the search criteria, and wherein the at least one column into which the extracted data is inserted comprises a search key; and

using the search key to query records in another table.

7. The method of claim 1, wherein the at least one instruction is generated in response to a call from an insertion process, wherein the at least one instruction extracts data from at least one column of a record in the first format in memory and inserts the data into a corresponding key column in an index in the second format.

8. The method of claim 7, further comprising generating an instruction to load an address in memory of a start of the record from which data is to be extracted into a register, wherein the location information for the at least one column in the first format is an offset from the address in memory, and wherein the location information for the at least one column into which data is extracted comprises one key column of the index.

9. The method of claim 8, wherein the location information for the record in the first format comprises a first table including information on the columns of records in the first format and the location information for the index comprises a second table including information on the location of key columns of the index and information indicating for each key column a corresponding column in records in the first format, and wherein for a given key column, the second table is used to determine the column in the records in the first format corresponding to the given key column and the first table is used to determine the location in the record in the memory of the column corresponding to the key column from which data will be extracted.

10. The method of claim 9, further comprising:

performing a threshold number of move operations by determining the location information from the first and second tables during each move operation, wherein the instruction is generated and used after the move operations have been performed the threshold number of times.

11. The method of claim 7, wherein data is extracted from the columns in the record in memory into key columns in a plurality of indexes.

12. The method of claim 7, further comprising loading multiple records one-at-a-time into the same location of memory, wherein while the record is loaded in the memory, the code is executed to move the data from columns in the record loaded into memory into the index columns.

13. A system for extracting data from a record in a first format and inserting data into a record in a second format during an insert operation, comprising:

means for determining location information for data in at least one record in at least one column in the first format involved in the insert operation from which data will be extracted;

means for determining location information for data in at least one record in at least one column in the second format involved in the insert operation into which the data extracted from a corresponding column in the first format will be inserted; and

for each column from which data will be extracted, means for using the determined location information to dynamically generate at least one instruction to move the data in each column in the record in the first format to each corresponding column of one record in the second format, wherein the location information for each column in the records in the first and second formats involved in the data movement operations is encoded in the at least one instruction, and wherein the at least one instruction is dynamically generated in response to a request to insert data into the record in the second format.

14. The system of claim 13, wherein the instruction may be repeated for multiple records in the first format from which data will be extracted.

15. The system of claim 13, wherein records in the first format have at least one of a different number of columns and different column data types than records in the second format.

16. The system of claim 13, wherein the means for determining location information for data in records in each column in the first format involved in the insert operation determines location information from a first table indicating an offset of the column from a start of the record and a length of the column and the means for determining location information for data in each column in the second format involved in the insert operation determines location information from a second table indicating a location of the corresponding column in records in the second format into which the data extracted from the first column is inserted, wherein the first table and the second table are not the tables from which data is extracted or into which data is inserted.

17. The system of claim 13, wherein each column from which data is extracted is from one record in a table including records in the first format and wherein each column into which data is inserted comprises a new record in an index in the second format.

18. The system of claim 13, further comprising:

means for searching at least one table for records that match a search criteria, wherein each record from which data is extracted comprises records located in the search that match the search criteria, and wherein the at least one column into which the extracted data is inserted comprises a search key; and

means for using the search key to query records in another table.

19. The system of claim 13, wherein the at least one instruction is generated in response to a call from an insertion process, wherein the at least one instruction extracts data from at least one column of a record in the first format in memory and inserts the data into a corresponding key column in an index in the second format.

20. The system of claim 19, further comprising means for generating an instruction to load an address in memory of a start of the record from which data is to be extracted into a register, wherein the location information for the at least one column in the first format is an offset from the address in memory, and wherein the location information for the at least one column into which data is extracted comprises one key column of the index.

21. The system of claim 20, wherein the location information for the record in the first format comprises a first table including information on the columns of records in the first format and the location information for the index comprises a second table including information on the location of key columns of the index and information indicating for each key column a corresponding column in records in the first format, and wherein for a given key column, the second table is used to determine the column in the records in the first format corresponding to the given key column and the first table is used to determine the location in the record in the memory of the column corresponding to the key column from which data will be extracted.

22. The system of claim 21, further comprising:

means for performing a threshold number of move operations by determining the location information from the first and second tables during each move operation, wherein the instruction is generated and used after the move operations have been performed the threshold number of times.

23. The system of claim 19, wherein data is extracted from the columns in the record in memory into key columns in a plurality of indexes.

24. The system of claim 19, further comprising means for loading multiple records one-at-a-time into the same location of memory, wherein while the record is loaded in the memory, the code is executed to move the data from columns in the record loaded into memory into the index columns.

25. An article of manufacture for use in extracting data from a record in a first format and inserting data into a record in a second format during an insert operation, the article of manufacture comprising at least one computer program capable of causing a computer to perform:

determining location information for data in at least one record in at least one column in the first format involved in the insert operation from which data will be extracted;

determining location information for data in at least one record in at least one column in the second format involved in the insert operation into which the data extracted from a corresponding column in the first format will be inserted; and

for each column from which data will be extracted, using the determined location information to dynamically generate at least one instruction to move the data in each column in the record in the first format to each corresponding column of one record in the second format, wherein the location information of the columns in records in the first and second formats involved in the data movement operations is encoded in the at least one instruction, and wherein the at least one instruction is dynamically generated in response to a request to insert data into the record in the second format.

26. The article of manufacture of claim 25, wherein the instruction may be repeated for multiple records in the first format from which data will be extracted.

27. The article of manufacture of claim 25, wherein records in the first format have at least one of a different number of columns and different column data types than records in the second format.

28. The article of manufacture of claim 25, wherein the location information for data in records in each column in the first format involved in the insert operation is determined from a first table indicating an offset of the column from a start of the record and a length of the column and the location for data in each column in the second format involved in the insert operation is determined from a second table indicating a location of the corresponding column in records in the second format into which the data extracted from the first column is inserted, wherein the first table and the second table are not the tables from which data is extracted or into which data is inserted.

29. The article of manufacture of claim 25, wherein each column from which data is extracted is from one record in a table including records in the first format and wherein each column into which data is inserted comprises a new record in an index in the second format.

30. The article of manufacture of claim 25, further comprising:

searching at least one table for records that match a search criteria, wherein each record from which data is extracted comprises records located in the search that match the search criteria, and wherein the at least one column into which the extracted data is inserted comprises a search key; and

using the search key to query records in another table.

31. The article of manufacture of claim 25, wherein the at least one instruction is generated in response to a call from an insertion process, wherein the at least one instruction extracts data from at least one column of a record in the first format in memory and inserts the data into a corresponding key column in an index in the second format.

32. The article of manufacture of claim 31, further comprising generating an instruction to load an address in memory of a start of the record from which data is to be extracted into a register, wherein the location information for the at least one column in the first format is an offset from the address in memory, and wherein the location information for the at least one column into which data is extracted comprises one key column of the index.

33. The article of manufacture of claim 32, wherein the location information for the record in the first format comprises a first table including information on the columns of records in the first format and the location information for the index comprises a second table including information on the location of key columns of the index and information indicating for each key column a corresponding column in records in the first format, and wherein for a given key column, the second table is used to determine the column in the records in the first format corresponding to the given key column and the first table is used to determine the location in the record in the memory of the column corresponding to the key column from which data will be extracted.

34. The article of manufacture of claim 33, further comprising:

performing a threshold number of move operations by determining the location information from the first and second tables during each move operation, wherein the instruction is generated and used after the move operations have been performed the threshold number of times.

35. The article of manufacture of claim 31, wherein data is extracted from the columns in the record in memory into key columns in a plurality of indexes.

36. The article of manufacture of claim 31, further comprising loading multiple records one-at-a-time into the same location of memory, wherein while the record is loaded in the memory, the code is executed to move the data from columns in the record loaded into memory into the index columns.