CA2209549C - Method and apparatus for loading data into a database in a multiprocessor environment - Google Patents
Method and apparatus for loading data into a database in a multiprocessor environment Download PDFInfo
- Publication number
- CA2209549C CA2209549C CA002209549A CA2209549A CA2209549C CA 2209549 C CA2209549 C CA 2209549C CA 002209549 A CA002209549 A CA 002209549A CA 2209549 A CA2209549 A CA 2209549A CA 2209549 C CA2209549 C CA 2209549C
- Authority
- CA
- Canada
- Prior art keywords
- data
- records
- record
- formatted
- buffer
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Expired - Fee Related
Links
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/25—Integrating or interfacing systems involving database management systems
- G06F16/254—Extract, transform and load [ETL] procedures, e.g. ETL data flows in data warehouses
-
- Y—GENERAL TAGGING OF NEW TECHNOLOGICAL DEVELOPMENTS; GENERAL TAGGING OF CROSS-SECTIONAL TECHNOLOGIES SPANNING OVER SEVERAL SECTIONS OF THE IPC; TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
- Y10—TECHNICAL SUBJECTS COVERED BY FORMER USPC
- Y10S—TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
- Y10S707/00—Data processing: database and file management or data structures
- Y10S707/99931—Database or file accessing
- Y10S707/99937—Sorting
Landscapes
- Engineering & Computer Science (AREA)
- Databases & Information Systems (AREA)
- Theoretical Computer Science (AREA)
- Data Mining & Analysis (AREA)
- Physics & Mathematics (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
This invention provides a method and apparatus for loading data having a predetermined order of data records from a source into a database using parallel processing. Using one or more reading agents, data records are read from the source in which they are stored. These data records are stored in groups of records. Each group of stored records are tagged with a sequence identifier corresponding to the predetermined order of data. Apparatus is provided for formatting the data records in the groups using a plurality of formatting agents in parallel. The formatted records are stored in formatted record groups. Sequence identifiers are written for each of the groups to the respective formatted record group. A record identification apparatus is provided for assigning page locations for the records using the sequence identifiers to maintain the predetermined order.
Description
CA 02209~49 1997-07-02 METHOD AND APPARATUS FOR LOADING DATA INTO A DATABASE
IN A MULTI PROCESSOR ENVIRONMENT
FIELD OF THE INVENTION
This invention relates to the loading of data into a database operating in a data processing system col"prisi"~ a network of multiple Central Processing Units (CPUs), typically known as 5 Symmetric Multi processors (SMPs).
BACKGROUND OF THE INVENTION
With the advent of databases it is incumbent on the user or operator of a database to INSERT
user supplied data into the database in a form that is consistent with the internal storag e form 10 of data for the database. A Database Management System (DBMS) or LOAD utility is used to convert the user supplied data into the internal database form. The actions of parsing, conversion, and formatting data into this internal form is a CPU intensive operation. LOAD
utilities reading from user supplied source media, such as disks, or tapes containing user supplied data incur the overhead of these conversions, as do INSERTs issued to the DBMS.
15 Because of these CPU intensive operations, insertion of user data may become a CPU bound activity.
It is well known in the art that database records are grouped into pages of predetermined size.
A number of pages are grouped together into a table, and each page in the table contains a 20 number that denotes its sequence in the table. Pages of a table are typically written to persistent storage (such as disk).
Symmetric Multi processors (SMPs) comprise a class of computers containing multiple CPUs.
Operating systems running on SMPs dispatch processes and threads to different CPUs in orde r 25 to distribute the workload assigned across the available processors. For a given program to exploit the power of an SMP, it is advantageous that it is designed so that it performs portion s CA 02209~49 1997-07-02 of its workload in separate dispatchable units of work which the operati ng system of the SMP
can distribute to the various CPUs in the SMP.
It would be a definite advantage in many cases that when data is loaded into the database of 5 an SMP system, the ability to perform parsing, conversion and formatting processes in concurrent dispatchable operating system units in order to exploit the advantage s of the SMP
system hardware and operating system.
The difficulty is that the design of a method and apparatus in order to load data for processing 10 in concurrent dispatchable operating system units is nontrivial because of the numerous items of state information which must be maintained as part of the database table meta-data (store d data that describes the database table concerned), su ch as free space control records, a table descriptor record, etc. as will be appreciated by those skilled in the database art. Despite the complexity of the problem, solutions to it have been attempted in the past.
However, in the normal case, adding true parallel processing (i.e.. True decoupled concurrent processing) the data is processed by each CPU in the system independently, resulting in the data being loaded into the database table in a n arbitrary sequence. This means that the data is stored in an arbitrary sequence in the table, and as well, in an arbitrary physical sequence 20 on the database storage device used by the data processing system.
As will be appreciated by those skilled in the art reviewing this application, the arbitrary sequence of data both logically in the database table and physically on the storage device being used by the data processing system to store database information can pose a problem if the 25 source data was intentionally supplied by the user in a significant order (such as cluster order) .
It can also result in poor exploitation of the buffer pool and l/O prefetchers used by the dat a processing system to accelerate or optimize data retrieval. The net result of which is ultimately that corruption of the sequence of the records in the source data may negatively impact subsequent query performance. As will be ap preciated, query performance is one of CA 02209~49 1997-07-02 the primary judgement criteria for which database products compete.
The very requirement for data order and the advantages of parallel processing appear to be opposed to each other. In order to take advantage of parallel processing it appears that the 5 prior art would require sacrifice of the required data order, while corrupting the data order appears to negatively impact processing performance including query performance.
The performance results are major indicators of product performance, and are heavily used by customers in deciding which Database products to buy.
The Transaction Processing Performance Council (TPC) regularly establishes guidelines for transaction processing and database benchmarks against which Database vendors regularly compete. Database vendors regularly publish their TPC compliant performance results. The official TPC benchmarks include both query performance, as well as the database creation time 15 (of which LOADing data is a major component). So, both the creation of the database and the subsequent query performance are major factors which customers consider, and for which Database product producers aim.
The term transaction is often applied to a wide variety of business and computer functions.
20 From the point of view of a computer function, a transaction could refer to a set of operation s including disk read/writes, operating system calls, or a type of data transfer from one system or subsystem to another.
While TPC benchmarks involve the measurement and evaluation of computer functions and 25 operations, the TPC regards a transaction as it is commonly understood in the business world:
a commercial exchange of goods, services, or money. A typical transaction, as defined by th e TPC, would include the updating to a database system for such things as inventory control (goods), airline reservations (services), or banking (money).
CA 02209~49 1997-07-02 SUMMARY OF THE INVENTION
The current invention relates to methods and apparatus for data loading into a database table which exploits true SMP parallelism. The preferred embodiment disclosed below has been laboratory tested and shown to yield excellent parallel processing, scalability permittin g good performance on SMP systems to at least I2 parallel CPUs, and which preserves the user provided order of the table data during the loading process.
In one aspect of the invention which is designed for use in a MULTI PROCESSOR data processing system capable of parallel processing, a method is provided for loading data havin g a predetermined order of data records into a database using parallel processing,using one or more reading tasks, reading each data record from the location in which it is stored; storing each said record in a buffer memory block of predetermined size;
tagging each buffer with a sequence identifier; transferring control of each said buffer to the control of one of a plurality of formatting tasks;
converting said records of data into a suitable database storage format;
storing said formatted data records in buffers;
writing said sequence identifier to said buffers of formatted data;
assigning a database page location for each record using said formatted data buffer order and said sequence identifier to maintain said predetermined order;
assembling said records into pages;
writing said pages to storage at the locations specified.
In another aspect of the invention a record is read from data records having a pre selected order from a source location of the data. Each record is stored in buffer blocks of pre selected size, and each buffer block is tagged with a sequence number corresponding to said pre selected order of said record in its source location. Control of the buffers is transferred to a plurality of formatter tasks or agents for processing in parallel. The records of data are converted to a suitable database storage format, and stored in buffers. A sequence identification number of each buffer is written to the buffers of formatted record data.
CA 02209~49 1997-07-02 Control of the buffers of formatted data is transferred to a RlDer agent. For the data records of each buffer a database page location is assigned maintaining the pre selected order by means of the formatted data record buffer order and sequence identification number. Contro I
of the formatted data is transferred to a plurality of writer agents. The formatted record s are assembled into pages which are written to system storage in the assigned storage locations.
In still another aspect of the invention apparatus is provided for loading data records having a pre selected order using a parallel data processing system to load the data into the data storage of a database while maintaining the pre selected data order. The apparatus includes at least one reader agent to read the data, preferably a number of formatter agents for converting the data records read by the reader agent to a format suitable for the database.
Apparatus is provided for storing the formatted records in a buffer, and assigning with sequence number to the buffer of the data records. A Record Identification (RlDer) agent is provided for assigning data processing system storage location for each read data record.
Apparatus is provided for assembling the formatted records into pages, and a number of writer agents are provided for writing each page to the appropriate assi gned storage location.
Yet another aspect of the invention provides a method of loading data having a predetermine d order of data records from a source into a database using parallel processing comprising:
using one or more reading agents, reading data records from the source in which they are stored;
storing said records in groups of records;
tagging each group with a sequence identifier corresponding to said predetermined order of data;
formatting the data records in said groups using a plurality of formatting agents in parallel;
storing said formatted records in formatted record groups;
writing said sequence identifier of each said group to the respective formatted record group;
CA 02209~49 1997-07-02 as~ignil,g page locations for said records using said sequence identifiers to maintain sai d predetermined order.
Still another aspect of the invention provides a computer program product comprising:
a computer usable medium having computer readable program code means embodied therein for causing a computer to load user data, the computer program product comprising:
computer readable program code means for causing a computer to effect apparatus for loading data records having a pre selected order using a parallel data processing system to load said data into the data storage of a database using said data processing system while maintaining said pre selected data order, comprising;
computer readable program code means for causing a computer to effect at least one reader agent to read said data;
computer readable program code means for causing a computer to effect buffer storage means for storing said data in buffers;
computer readable program code means for causing a computer to effect tagging means for tagging each buffer containing said data with a sequen ce number corresponding to the pre selected order of the data;
computer readable program code means for causing a computer to effect a plurality of formatter agents for converting said data records read by said at least one reader agent to a format suitable for said database;
computer readable program code means for causing a computer to effect means for storing said formatted records in formatted record buffers, with the sequence num bers of the buffers containing said data;
computer readable program code means for causing a computer to effect a (RlDer) agent for assigning page locations for each said formatted data records;
computer readable program code means for causing a computer to effect means for assembling said formatted records into pages, and;
computer readable program code means for causing a computer to effect a plurality of writer agents for writing each said pages to said assigned storage locations.
CA 02209~49 1997-07-02 Another aspect of this invention provides a program storage device readable by a machine, tangibly embodying a program of instructions executable by the machin e to perform method steps for loading data having a predetermined order of data records from a source into a database using parallel processing said method steps comprising:
using one or more reading agents, reading data records from the source in which they are stored;
storing said records in groups of records;
tagging each group with a sequence identifier corresponding to said predetermined order of data;
formatting the data records in said groups using a plurality of formatting agents in parallel;
storing said formatted records in formatted record groups;
writing said sequence identifier of each said group to the respective formatted record group;
assigning page locations for said records using said sequence identifiers to maintain sai d predetermined order.
BRIEF DESCRIPTION OF THE DRAWINGS
Figure l is a flow diagram of a preferred method of the invention.
Figure 2 is a block diagram of a process model for a preferred embodiment of theinvention.
DESCRIPTION OF THE PREFERRED EMBODIMENTS OF THE INVENTION
Referring to Figure 2 which illustrates a process model for a preferred specific embodiment of the invention it can be seen that a single Reader Agent l is provided to read user data records. This information is transferred to the control of a number of Formatter Agents 2 which convert the user data records to records in a suitable database format corresponding to that used by the database of the data processing system for which the invention can be used. The Record Identification (RlDer) Agent 3 assigns each record a location within a page CA 02209~49 1997-07-02 of the database table.
The Writer Agents 4 construct pages (i.e.. assemble formatted records into pages) based on the location assigned to each record by the RlDer agent, in conjunction with the formatted 5 record data. The Writer Agents write fully constructed pages to persistent storage (e.g.. disk).
It should be noted that page construction could be done by other agents.
The agents shown in Figure 2 combine to form a data LOADer, i.e.. a collection of software agents that together load user data into a database table.
Figure l, which represents a flow chart for the load SMP exploitation with record sequence preservation system of the present invention is described below:
A data processing system user having sequential data records to be entered into a database 15 on an SMP system would initiate loading of the data by the data processing system.
In order that the data records can be quickly processed using the capabilities of the SMP
system parallel processing the invention provides the following process which may be conveniently implemented using software agents or tasks running on the data processing 20 system At the start of record entry the user supplied records are read by a reader agent from the source supplied by the user, e.g.. diskette or tape, and stored in memory blo ck buffers in the memory area of said data processing system.
In order to assist in record order preservation during the currency of the operation each memory block buffer holding user data is tagged with a sequence number to identify its relative order in the data stream resulting from the data loading process.
CA 02209~49 1997-07-02 Once this tagging has been accomplished in a buffer, control of that memory buffer is then transferred to one of a number of formatting agents. The Formatter agent reads the user data records from the buffer received from the Reader, converts each record into internal database storage format, and stores the converted records in a second memory block or buffer in the 5 same order that these records appeared in the memory buffer containing the user data (received from the Reader agent). The buffer containing the records, now in the database storage format is tagged with the same sequence as the original memory buffer which held th e user supplied record data. The operation of the formatter agents is carried on concurrently taking advantage of the parallel processing capabilities of the SMP system on which they are 10 operating.
Once the formatted record data in a buffer is complete the formatter agent whichconstructed the formatted data records passes control of the buffer to a single RlDer agent.
15 The RlDer agent (The name RlDer was chosen as the RlDer agent assigns a Record Identifier (RID) to each record. The RID represents the location of a record on a page (including identification of the page)) receives control of buffers of formatted records from all of the concurrently executing formatter agents (i.e. parallely executing formatter agents). Upon receiving the next buffer in the sequence (which it determines based on the sequence ID
20 associated with each buffer of formatted records) it assigns a location on a page in the database table to each record.
The RlDer agent uses the sequence number on each formatted record buffer to ensure that it processes them in the originating sequence (that is, the sequence of the user data) Once its work is done the RlDer agent then passes control of the formatted data to a numbe r of writer agents implemented by the invention and operating on the data processing system for parallel or concurrent application. The writer agents then assemble the formatted records into pages, i.e.. the Writer agents construct pages based on the location assigned to each CA 02209~49 1997-07-02 record by the RlDer agent, in conjunction with the formatted record data. The fully constructed pages are then written to their location on the data processing system's database storage. This can be done by the Writer agents or some other agent.
5 As result of this, the order of records on the (e.g. disk) storage of the data proces sing system will match the order of the original user data since the lists are processed in order based on their sequence Ids.
PSEUDO CODE
The preferred embodiment above as illustrated in Figures 1 and 2 may be further understood by more particular information present in the following pseudo code which illustrates a software implementation of the invention which achieves load SMP exploitation with record sequence preservation when suitably formulated for use on a SMP data processing system.
PSEUDO CODE FOR READER AGENT:
{
Open user data source for reading;
Read nK bytes of user data into the bottom half of a memory buffer of 2nK bytes;Strip any existing partial record from the bottom of this nK bytes of data just read;
Store a copy of the partial record in a temporary storage area;
If there was a partial record in the previous read operation, then prefix that partial record to the start of the nK memory area;
Tag the memory buffer with a sequence identifier;
Transfer control of the memory buffer to one of the Formatter agents;
CA 02209~49 1997-07-02 Continue reading the next nK bytes of user data until all user data is read;
}
Comments: There can be more than one Reader agent used, as long as a relationship is S provided e.g.. in the form of a software algorithm to correctly tag the user data records in the buffers with suitable sequence Ids. In the specific embodiment illustrated only a single Reader agent is illustrated so the assignment of sequence Ids is straightforward e.g.. the first block of user data has sequence ID O, the second block has ID 1, etc.
10 While the pseudo code of the Reader agent has been generalized for a buffer storage area of 2nK bytes, the implementation tested as described below is a specific implementation for an arbitrary record length limit. Assuming that no user record could exceed 32K, a 64K buffer was allocated so that a whole number of records could be read into a block of memory. In the 64K buffer 32K of user data was read into the bottom half of the buffer. (While the 32K
15 size is up to the system designer the important consideration is that the bottom half of a buffer large enough to hold at least one record is used. The pseudo code reflects that the buffer is twice the size of the expected data block.) The data was examined by the Reader, to determine if a partial record existed at the end of the block. If a partial record existed (and it frequently did) the partial record was copied to a temporary storage space, and the end of 20 the memory block was redefined to be the byte prior to the start of the partial record. When the next 32K bytes of user data was read (again into the bottom half of the 64K b uffer ) the partial record from the end of the previous block was prefixed to this buffer. The pseudo code illustrated enables the particular embodiment described to handle complete records rather than partial records. This is a useful feature for the situation in which records have 25 varying lengths.
Sequence Ids can be in any form sufficient to identify the order of the data, such as:
alphabetic identifiers, integer identifiers, the modulo of the formatter count, etc.
CA 02209~49 1997-07-02 In another implementation of the invention user data can be alternatively read by any of the other agents in the process model.
PSEUDO CODE FOR FORMATTER AGENT:
S {
Receive control of a memory buffer containing user record data in user format;
Convert the next user record into a formatted record consistent with the form ofrecords in database storage;
The converted record, now in database form, is added to a buffer of formatted records;
If this is the last record in the buffer:
{
Tag the buffer containing formatted records with a sequence ID identical to th e sequence ID of the user data area;
Transfer control of the buffer containing database-formatted record to the RlDer agent;
Obtain control of a new buffer of user data from the Reader agent;
}
else, there are more user records to process;
{
continue formatting the next user record;
}
25 }
Comments:
There can be nay number of formatter agents, but the performance of this invention can be optimized by correlating (but not necessarily equating) the number of formatting agents with CA 02209~49 1997-07-02 the number of system CPUs online.
PSEUDO CODE FOR WRITER AGENT:
{
Receive control of a buffer of formatted records, and their associated location information from the RlDer agent;
Construct database data pages;
Write pages to storage devices;
Obtain next buffer from RlDer agent, until complete;
}
Comments:
15 There can be any number of Writer agents. Typically, the number of Writer agents should be related to the number of database storage devices used.
Database data pages could be written by agents other than the Writer agent, and could be constructed by agents other than the Write agent.
PSEUDO CODE FOR RlDer AGENT:
{
Obtain a buffer of formatted database records from one of the Formatter agents;
if this buffer is the next expected buffer in the sequence of buffers, based on the sequence ID:
{
For each record in the buffer of the formatted records, assign a location withi n a page of the database table.
CA 02209~49 1997-07-02 After processing the entire buffer, process any cached buffers of formatted records that are in sequence;
After all cached buffers of formatted records in perfect sequence have been 5 processed, return to the first step "Obtain a buffer of formatted data records...";
}
else this buffer will need to be processed later:
{
Cache this buffer, until it can be processed in sequence;
}
}
Comments:
In the implementation illustrated "preserving record sequence" is an option on the loader.
By specifying that the LOADer not exploit this capability, the RlDer is free to process data in any sequence, which can result in greater scalability on SMP systems.
Once written to disk the records can be conveniently and quickly accessed achieving a high degree of read efficiency.
Normally, to preserve the data sequence, the RlDer has to wait until it gets the next buffer of formatted data in the sequence. For example, while waiting for formatte d data buffer #6, it may receive formatted data buffers #8 and #9, then #7. It waits until #6 arrives. Once #6 arrives from the formatter that generated it the RlDer can then process buffers #6,#7,#8, and #9 even though they arrived in order 8,9,7,6.
But, if preserving order is not important, the RlDer can just process the buffers of formatted data as they arrive, regardless of their sequence ID's. (i.e.. in this example the RlDer would process buffers in the order # 8, #9, #7, and then #6 ) So the RlDer doesn't need to wait CA 02209~49 1997-07-02 for the correct buffer to show up, and wastes less time waiting.
This variation of the invention takes advantage of the parallel processing capabilities of the LOADER of the invention providing significant speed advantages but does not preserve the 5 entire original record sequence.
EXAMPLE
The following example should assist in clarifying one embodiment of the invention. In this embodiment user data is stored in a file on disk, for example, in a format compatible with the 10 LOAD utility of the database management system in use on the data processing system in use .
Shown in this example is a 3 column table stored in delimited ASCII. The 3 columns are integer, l 8 byte character, and floating point, respectively. Note that a new line character, Ox0A, is implied at the end of each line. Columns are delimited by the "," character:
5, My dog, 23.4 7, Your dog, 69.7 l 2, Bob's dog, 30.0 l 4, Sue's dog, l O. 4 20 l l, Ken's dog, l l.2 9, ~ohn's dog, 26. l The user data can be represented as follows in memory:
CA 02209~49 1997-07-02 340A3131 2C4B656E 27732064 6F672C31 4,11,Ken's dog,1 312E320A 392C4A6F 686E2773 20646F67 1.2,9,~ohn's dog 2C32362E 3 t ,26.1 5 The Reader agent stores data in a buffer, where a few bytes of storage are reserved for the sequence tag. For example, if the sequence tag was a 4 byte integer, and the memory set-aside for this tag was the first four bytes in the memory buffer, then the tagged memory (tagged with sequence OxOOOOOOOO), would have the following HEX representation:
~~~~~~~~
If the same data happened to appear in the second buffer of user data, it would be tagged with sequence ID 1 (instead of 0). In HEX form, this is OxO0000001. The memory buffer would then look like:
CA 02209~49 1997-07-02 When the formatter agent receives this buffer, it immediately can identify the sequence ID of the data, and thus it's relative position in the stream of data by reading, and storing this sequence ID.
S the formatter converts each of the user records to a form con~i~lent with the storage form of records in the DBMS. The formatted DBMS type records are stored in a buffer. The buffer is tagged with the same sequence ID as the buffer holding the corresponding user data.
For example: Consider a 64K buffer holding a list of DBMS style records, where the first 4 10 bytes of the buffer was reserved for a sequence ID.
4 byte seq. ID
N 1 bytes representing DBMS form of user record 1 N2 bytes representing DBMS form of user record 2 N3 bytes representing DBMS form of user record 3 15 N4 bytes representing DBMS form of user record 4 N5 bytes representing DBMS form of user record 5 N6 bytes representing DBMS form of user record 6 remaining unused bytes in 64K buffer.
20 Note that the number of bytes required to represent the data record in DBMS (database management system) form may vary from record to record. N 1, ....N6 may or may not be the same.
IN A MULTI PROCESSOR ENVIRONMENT
FIELD OF THE INVENTION
This invention relates to the loading of data into a database operating in a data processing system col"prisi"~ a network of multiple Central Processing Units (CPUs), typically known as 5 Symmetric Multi processors (SMPs).
BACKGROUND OF THE INVENTION
With the advent of databases it is incumbent on the user or operator of a database to INSERT
user supplied data into the database in a form that is consistent with the internal storag e form 10 of data for the database. A Database Management System (DBMS) or LOAD utility is used to convert the user supplied data into the internal database form. The actions of parsing, conversion, and formatting data into this internal form is a CPU intensive operation. LOAD
utilities reading from user supplied source media, such as disks, or tapes containing user supplied data incur the overhead of these conversions, as do INSERTs issued to the DBMS.
15 Because of these CPU intensive operations, insertion of user data may become a CPU bound activity.
It is well known in the art that database records are grouped into pages of predetermined size.
A number of pages are grouped together into a table, and each page in the table contains a 20 number that denotes its sequence in the table. Pages of a table are typically written to persistent storage (such as disk).
Symmetric Multi processors (SMPs) comprise a class of computers containing multiple CPUs.
Operating systems running on SMPs dispatch processes and threads to different CPUs in orde r 25 to distribute the workload assigned across the available processors. For a given program to exploit the power of an SMP, it is advantageous that it is designed so that it performs portion s CA 02209~49 1997-07-02 of its workload in separate dispatchable units of work which the operati ng system of the SMP
can distribute to the various CPUs in the SMP.
It would be a definite advantage in many cases that when data is loaded into the database of 5 an SMP system, the ability to perform parsing, conversion and formatting processes in concurrent dispatchable operating system units in order to exploit the advantage s of the SMP
system hardware and operating system.
The difficulty is that the design of a method and apparatus in order to load data for processing 10 in concurrent dispatchable operating system units is nontrivial because of the numerous items of state information which must be maintained as part of the database table meta-data (store d data that describes the database table concerned), su ch as free space control records, a table descriptor record, etc. as will be appreciated by those skilled in the database art. Despite the complexity of the problem, solutions to it have been attempted in the past.
However, in the normal case, adding true parallel processing (i.e.. True decoupled concurrent processing) the data is processed by each CPU in the system independently, resulting in the data being loaded into the database table in a n arbitrary sequence. This means that the data is stored in an arbitrary sequence in the table, and as well, in an arbitrary physical sequence 20 on the database storage device used by the data processing system.
As will be appreciated by those skilled in the art reviewing this application, the arbitrary sequence of data both logically in the database table and physically on the storage device being used by the data processing system to store database information can pose a problem if the 25 source data was intentionally supplied by the user in a significant order (such as cluster order) .
It can also result in poor exploitation of the buffer pool and l/O prefetchers used by the dat a processing system to accelerate or optimize data retrieval. The net result of which is ultimately that corruption of the sequence of the records in the source data may negatively impact subsequent query performance. As will be ap preciated, query performance is one of CA 02209~49 1997-07-02 the primary judgement criteria for which database products compete.
The very requirement for data order and the advantages of parallel processing appear to be opposed to each other. In order to take advantage of parallel processing it appears that the 5 prior art would require sacrifice of the required data order, while corrupting the data order appears to negatively impact processing performance including query performance.
The performance results are major indicators of product performance, and are heavily used by customers in deciding which Database products to buy.
The Transaction Processing Performance Council (TPC) regularly establishes guidelines for transaction processing and database benchmarks against which Database vendors regularly compete. Database vendors regularly publish their TPC compliant performance results. The official TPC benchmarks include both query performance, as well as the database creation time 15 (of which LOADing data is a major component). So, both the creation of the database and the subsequent query performance are major factors which customers consider, and for which Database product producers aim.
The term transaction is often applied to a wide variety of business and computer functions.
20 From the point of view of a computer function, a transaction could refer to a set of operation s including disk read/writes, operating system calls, or a type of data transfer from one system or subsystem to another.
While TPC benchmarks involve the measurement and evaluation of computer functions and 25 operations, the TPC regards a transaction as it is commonly understood in the business world:
a commercial exchange of goods, services, or money. A typical transaction, as defined by th e TPC, would include the updating to a database system for such things as inventory control (goods), airline reservations (services), or banking (money).
CA 02209~49 1997-07-02 SUMMARY OF THE INVENTION
The current invention relates to methods and apparatus for data loading into a database table which exploits true SMP parallelism. The preferred embodiment disclosed below has been laboratory tested and shown to yield excellent parallel processing, scalability permittin g good performance on SMP systems to at least I2 parallel CPUs, and which preserves the user provided order of the table data during the loading process.
In one aspect of the invention which is designed for use in a MULTI PROCESSOR data processing system capable of parallel processing, a method is provided for loading data havin g a predetermined order of data records into a database using parallel processing,using one or more reading tasks, reading each data record from the location in which it is stored; storing each said record in a buffer memory block of predetermined size;
tagging each buffer with a sequence identifier; transferring control of each said buffer to the control of one of a plurality of formatting tasks;
converting said records of data into a suitable database storage format;
storing said formatted data records in buffers;
writing said sequence identifier to said buffers of formatted data;
assigning a database page location for each record using said formatted data buffer order and said sequence identifier to maintain said predetermined order;
assembling said records into pages;
writing said pages to storage at the locations specified.
In another aspect of the invention a record is read from data records having a pre selected order from a source location of the data. Each record is stored in buffer blocks of pre selected size, and each buffer block is tagged with a sequence number corresponding to said pre selected order of said record in its source location. Control of the buffers is transferred to a plurality of formatter tasks or agents for processing in parallel. The records of data are converted to a suitable database storage format, and stored in buffers. A sequence identification number of each buffer is written to the buffers of formatted record data.
CA 02209~49 1997-07-02 Control of the buffers of formatted data is transferred to a RlDer agent. For the data records of each buffer a database page location is assigned maintaining the pre selected order by means of the formatted data record buffer order and sequence identification number. Contro I
of the formatted data is transferred to a plurality of writer agents. The formatted record s are assembled into pages which are written to system storage in the assigned storage locations.
In still another aspect of the invention apparatus is provided for loading data records having a pre selected order using a parallel data processing system to load the data into the data storage of a database while maintaining the pre selected data order. The apparatus includes at least one reader agent to read the data, preferably a number of formatter agents for converting the data records read by the reader agent to a format suitable for the database.
Apparatus is provided for storing the formatted records in a buffer, and assigning with sequence number to the buffer of the data records. A Record Identification (RlDer) agent is provided for assigning data processing system storage location for each read data record.
Apparatus is provided for assembling the formatted records into pages, and a number of writer agents are provided for writing each page to the appropriate assi gned storage location.
Yet another aspect of the invention provides a method of loading data having a predetermine d order of data records from a source into a database using parallel processing comprising:
using one or more reading agents, reading data records from the source in which they are stored;
storing said records in groups of records;
tagging each group with a sequence identifier corresponding to said predetermined order of data;
formatting the data records in said groups using a plurality of formatting agents in parallel;
storing said formatted records in formatted record groups;
writing said sequence identifier of each said group to the respective formatted record group;
CA 02209~49 1997-07-02 as~ignil,g page locations for said records using said sequence identifiers to maintain sai d predetermined order.
Still another aspect of the invention provides a computer program product comprising:
a computer usable medium having computer readable program code means embodied therein for causing a computer to load user data, the computer program product comprising:
computer readable program code means for causing a computer to effect apparatus for loading data records having a pre selected order using a parallel data processing system to load said data into the data storage of a database using said data processing system while maintaining said pre selected data order, comprising;
computer readable program code means for causing a computer to effect at least one reader agent to read said data;
computer readable program code means for causing a computer to effect buffer storage means for storing said data in buffers;
computer readable program code means for causing a computer to effect tagging means for tagging each buffer containing said data with a sequen ce number corresponding to the pre selected order of the data;
computer readable program code means for causing a computer to effect a plurality of formatter agents for converting said data records read by said at least one reader agent to a format suitable for said database;
computer readable program code means for causing a computer to effect means for storing said formatted records in formatted record buffers, with the sequence num bers of the buffers containing said data;
computer readable program code means for causing a computer to effect a (RlDer) agent for assigning page locations for each said formatted data records;
computer readable program code means for causing a computer to effect means for assembling said formatted records into pages, and;
computer readable program code means for causing a computer to effect a plurality of writer agents for writing each said pages to said assigned storage locations.
CA 02209~49 1997-07-02 Another aspect of this invention provides a program storage device readable by a machine, tangibly embodying a program of instructions executable by the machin e to perform method steps for loading data having a predetermined order of data records from a source into a database using parallel processing said method steps comprising:
using one or more reading agents, reading data records from the source in which they are stored;
storing said records in groups of records;
tagging each group with a sequence identifier corresponding to said predetermined order of data;
formatting the data records in said groups using a plurality of formatting agents in parallel;
storing said formatted records in formatted record groups;
writing said sequence identifier of each said group to the respective formatted record group;
assigning page locations for said records using said sequence identifiers to maintain sai d predetermined order.
BRIEF DESCRIPTION OF THE DRAWINGS
Figure l is a flow diagram of a preferred method of the invention.
Figure 2 is a block diagram of a process model for a preferred embodiment of theinvention.
DESCRIPTION OF THE PREFERRED EMBODIMENTS OF THE INVENTION
Referring to Figure 2 which illustrates a process model for a preferred specific embodiment of the invention it can be seen that a single Reader Agent l is provided to read user data records. This information is transferred to the control of a number of Formatter Agents 2 which convert the user data records to records in a suitable database format corresponding to that used by the database of the data processing system for which the invention can be used. The Record Identification (RlDer) Agent 3 assigns each record a location within a page CA 02209~49 1997-07-02 of the database table.
The Writer Agents 4 construct pages (i.e.. assemble formatted records into pages) based on the location assigned to each record by the RlDer agent, in conjunction with the formatted 5 record data. The Writer Agents write fully constructed pages to persistent storage (e.g.. disk).
It should be noted that page construction could be done by other agents.
The agents shown in Figure 2 combine to form a data LOADer, i.e.. a collection of software agents that together load user data into a database table.
Figure l, which represents a flow chart for the load SMP exploitation with record sequence preservation system of the present invention is described below:
A data processing system user having sequential data records to be entered into a database 15 on an SMP system would initiate loading of the data by the data processing system.
In order that the data records can be quickly processed using the capabilities of the SMP
system parallel processing the invention provides the following process which may be conveniently implemented using software agents or tasks running on the data processing 20 system At the start of record entry the user supplied records are read by a reader agent from the source supplied by the user, e.g.. diskette or tape, and stored in memory blo ck buffers in the memory area of said data processing system.
In order to assist in record order preservation during the currency of the operation each memory block buffer holding user data is tagged with a sequence number to identify its relative order in the data stream resulting from the data loading process.
CA 02209~49 1997-07-02 Once this tagging has been accomplished in a buffer, control of that memory buffer is then transferred to one of a number of formatting agents. The Formatter agent reads the user data records from the buffer received from the Reader, converts each record into internal database storage format, and stores the converted records in a second memory block or buffer in the 5 same order that these records appeared in the memory buffer containing the user data (received from the Reader agent). The buffer containing the records, now in the database storage format is tagged with the same sequence as the original memory buffer which held th e user supplied record data. The operation of the formatter agents is carried on concurrently taking advantage of the parallel processing capabilities of the SMP system on which they are 10 operating.
Once the formatted record data in a buffer is complete the formatter agent whichconstructed the formatted data records passes control of the buffer to a single RlDer agent.
15 The RlDer agent (The name RlDer was chosen as the RlDer agent assigns a Record Identifier (RID) to each record. The RID represents the location of a record on a page (including identification of the page)) receives control of buffers of formatted records from all of the concurrently executing formatter agents (i.e. parallely executing formatter agents). Upon receiving the next buffer in the sequence (which it determines based on the sequence ID
20 associated with each buffer of formatted records) it assigns a location on a page in the database table to each record.
The RlDer agent uses the sequence number on each formatted record buffer to ensure that it processes them in the originating sequence (that is, the sequence of the user data) Once its work is done the RlDer agent then passes control of the formatted data to a numbe r of writer agents implemented by the invention and operating on the data processing system for parallel or concurrent application. The writer agents then assemble the formatted records into pages, i.e.. the Writer agents construct pages based on the location assigned to each CA 02209~49 1997-07-02 record by the RlDer agent, in conjunction with the formatted record data. The fully constructed pages are then written to their location on the data processing system's database storage. This can be done by the Writer agents or some other agent.
5 As result of this, the order of records on the (e.g. disk) storage of the data proces sing system will match the order of the original user data since the lists are processed in order based on their sequence Ids.
PSEUDO CODE
The preferred embodiment above as illustrated in Figures 1 and 2 may be further understood by more particular information present in the following pseudo code which illustrates a software implementation of the invention which achieves load SMP exploitation with record sequence preservation when suitably formulated for use on a SMP data processing system.
PSEUDO CODE FOR READER AGENT:
{
Open user data source for reading;
Read nK bytes of user data into the bottom half of a memory buffer of 2nK bytes;Strip any existing partial record from the bottom of this nK bytes of data just read;
Store a copy of the partial record in a temporary storage area;
If there was a partial record in the previous read operation, then prefix that partial record to the start of the nK memory area;
Tag the memory buffer with a sequence identifier;
Transfer control of the memory buffer to one of the Formatter agents;
CA 02209~49 1997-07-02 Continue reading the next nK bytes of user data until all user data is read;
}
Comments: There can be more than one Reader agent used, as long as a relationship is S provided e.g.. in the form of a software algorithm to correctly tag the user data records in the buffers with suitable sequence Ids. In the specific embodiment illustrated only a single Reader agent is illustrated so the assignment of sequence Ids is straightforward e.g.. the first block of user data has sequence ID O, the second block has ID 1, etc.
10 While the pseudo code of the Reader agent has been generalized for a buffer storage area of 2nK bytes, the implementation tested as described below is a specific implementation for an arbitrary record length limit. Assuming that no user record could exceed 32K, a 64K buffer was allocated so that a whole number of records could be read into a block of memory. In the 64K buffer 32K of user data was read into the bottom half of the buffer. (While the 32K
15 size is up to the system designer the important consideration is that the bottom half of a buffer large enough to hold at least one record is used. The pseudo code reflects that the buffer is twice the size of the expected data block.) The data was examined by the Reader, to determine if a partial record existed at the end of the block. If a partial record existed (and it frequently did) the partial record was copied to a temporary storage space, and the end of 20 the memory block was redefined to be the byte prior to the start of the partial record. When the next 32K bytes of user data was read (again into the bottom half of the 64K b uffer ) the partial record from the end of the previous block was prefixed to this buffer. The pseudo code illustrated enables the particular embodiment described to handle complete records rather than partial records. This is a useful feature for the situation in which records have 25 varying lengths.
Sequence Ids can be in any form sufficient to identify the order of the data, such as:
alphabetic identifiers, integer identifiers, the modulo of the formatter count, etc.
CA 02209~49 1997-07-02 In another implementation of the invention user data can be alternatively read by any of the other agents in the process model.
PSEUDO CODE FOR FORMATTER AGENT:
S {
Receive control of a memory buffer containing user record data in user format;
Convert the next user record into a formatted record consistent with the form ofrecords in database storage;
The converted record, now in database form, is added to a buffer of formatted records;
If this is the last record in the buffer:
{
Tag the buffer containing formatted records with a sequence ID identical to th e sequence ID of the user data area;
Transfer control of the buffer containing database-formatted record to the RlDer agent;
Obtain control of a new buffer of user data from the Reader agent;
}
else, there are more user records to process;
{
continue formatting the next user record;
}
25 }
Comments:
There can be nay number of formatter agents, but the performance of this invention can be optimized by correlating (but not necessarily equating) the number of formatting agents with CA 02209~49 1997-07-02 the number of system CPUs online.
PSEUDO CODE FOR WRITER AGENT:
{
Receive control of a buffer of formatted records, and their associated location information from the RlDer agent;
Construct database data pages;
Write pages to storage devices;
Obtain next buffer from RlDer agent, until complete;
}
Comments:
15 There can be any number of Writer agents. Typically, the number of Writer agents should be related to the number of database storage devices used.
Database data pages could be written by agents other than the Writer agent, and could be constructed by agents other than the Write agent.
PSEUDO CODE FOR RlDer AGENT:
{
Obtain a buffer of formatted database records from one of the Formatter agents;
if this buffer is the next expected buffer in the sequence of buffers, based on the sequence ID:
{
For each record in the buffer of the formatted records, assign a location withi n a page of the database table.
CA 02209~49 1997-07-02 After processing the entire buffer, process any cached buffers of formatted records that are in sequence;
After all cached buffers of formatted records in perfect sequence have been 5 processed, return to the first step "Obtain a buffer of formatted data records...";
}
else this buffer will need to be processed later:
{
Cache this buffer, until it can be processed in sequence;
}
}
Comments:
In the implementation illustrated "preserving record sequence" is an option on the loader.
By specifying that the LOADer not exploit this capability, the RlDer is free to process data in any sequence, which can result in greater scalability on SMP systems.
Once written to disk the records can be conveniently and quickly accessed achieving a high degree of read efficiency.
Normally, to preserve the data sequence, the RlDer has to wait until it gets the next buffer of formatted data in the sequence. For example, while waiting for formatte d data buffer #6, it may receive formatted data buffers #8 and #9, then #7. It waits until #6 arrives. Once #6 arrives from the formatter that generated it the RlDer can then process buffers #6,#7,#8, and #9 even though they arrived in order 8,9,7,6.
But, if preserving order is not important, the RlDer can just process the buffers of formatted data as they arrive, regardless of their sequence ID's. (i.e.. in this example the RlDer would process buffers in the order # 8, #9, #7, and then #6 ) So the RlDer doesn't need to wait CA 02209~49 1997-07-02 for the correct buffer to show up, and wastes less time waiting.
This variation of the invention takes advantage of the parallel processing capabilities of the LOADER of the invention providing significant speed advantages but does not preserve the 5 entire original record sequence.
EXAMPLE
The following example should assist in clarifying one embodiment of the invention. In this embodiment user data is stored in a file on disk, for example, in a format compatible with the 10 LOAD utility of the database management system in use on the data processing system in use .
Shown in this example is a 3 column table stored in delimited ASCII. The 3 columns are integer, l 8 byte character, and floating point, respectively. Note that a new line character, Ox0A, is implied at the end of each line. Columns are delimited by the "," character:
5, My dog, 23.4 7, Your dog, 69.7 l 2, Bob's dog, 30.0 l 4, Sue's dog, l O. 4 20 l l, Ken's dog, l l.2 9, ~ohn's dog, 26. l The user data can be represented as follows in memory:
CA 02209~49 1997-07-02 340A3131 2C4B656E 27732064 6F672C31 4,11,Ken's dog,1 312E320A 392C4A6F 686E2773 20646F67 1.2,9,~ohn's dog 2C32362E 3 t ,26.1 5 The Reader agent stores data in a buffer, where a few bytes of storage are reserved for the sequence tag. For example, if the sequence tag was a 4 byte integer, and the memory set-aside for this tag was the first four bytes in the memory buffer, then the tagged memory (tagged with sequence OxOOOOOOOO), would have the following HEX representation:
~~~~~~~~
If the same data happened to appear in the second buffer of user data, it would be tagged with sequence ID 1 (instead of 0). In HEX form, this is OxO0000001. The memory buffer would then look like:
CA 02209~49 1997-07-02 When the formatter agent receives this buffer, it immediately can identify the sequence ID of the data, and thus it's relative position in the stream of data by reading, and storing this sequence ID.
S the formatter converts each of the user records to a form con~i~lent with the storage form of records in the DBMS. The formatted DBMS type records are stored in a buffer. The buffer is tagged with the same sequence ID as the buffer holding the corresponding user data.
For example: Consider a 64K buffer holding a list of DBMS style records, where the first 4 10 bytes of the buffer was reserved for a sequence ID.
4 byte seq. ID
N 1 bytes representing DBMS form of user record 1 N2 bytes representing DBMS form of user record 2 N3 bytes representing DBMS form of user record 3 15 N4 bytes representing DBMS form of user record 4 N5 bytes representing DBMS form of user record 5 N6 bytes representing DBMS form of user record 6 remaining unused bytes in 64K buffer.
20 Note that the number of bytes required to represent the data record in DBMS (database management system) form may vary from record to record. N 1, ....N6 may or may not be the same.
Claims (13)
1. A method of loading data having a predetermined order of data records from a sourc e into a database using parallel processing comprising:
using one or more reading agents, reading data records from the source in which they are stored;
storing said records in groups of records;
tagging each group with a sequence identifier corresponding to said predetermined order of data;
formatting the data records in said groups using a plurality of formatting agents in parallel;
storing said formatted records in formatted record groups;
writing said sequence identifier of each said group to the respective formatted record group;
assigning page locations for said records using said sequence identifiers to maintain sai d predetermined order;
using one or more reading agents, reading data records from the source in which they are stored;
storing said records in groups of records;
tagging each group with a sequence identifier corresponding to said predetermined order of data;
formatting the data records in said groups using a plurality of formatting agents in parallel;
storing said formatted records in formatted record groups;
writing said sequence identifier of each said group to the respective formatted record group;
assigning page locations for said records using said sequence identifiers to maintain sai d predetermined order;
2. The method of claim 1 including:
assembling said records into pages.
assembling said records into pages.
3. The method of claim 2 including:
writing said pages to storage at the locations specified.
writing said pages to storage at the locations specified.
4. The method of claim 1 in which said reader agents read nK bytes of user data into the bottom half of a memory buffer area of 2nK bytes;
strips any existing partial record from the bottom of said nK bytes of data just read;
stores a copy of said partial record in a temporary storage area;
if a partial record of a previous read operation exists, then prefixes that partial record to the start of said nK memory area;
tags said memory storage area with a sequence identifier;
transfers control of said memory buffer to one of said formatter agents;
continues reading the next nK bytes of user data until all user data is read.
strips any existing partial record from the bottom of said nK bytes of data just read;
stores a copy of said partial record in a temporary storage area;
if a partial record of a previous read operation exists, then prefixes that partial record to the start of said nK memory area;
tags said memory storage area with a sequence identifier;
transfers control of said memory buffer to one of said formatter agents;
continues reading the next nK bytes of user data until all user data is read.
5. For a MULTI PROCESSOR data processing system capable of parallel processing, a method of loading data having a predetermined order of data records into a database using parallel processing comprising:
using one or more reading agents, reading data records from the source in which they are stored;
storing said records in memory buffers;
tagging each buffer with a sequence identifier corresponding to said predetermined order of data;
transferring control of each said buffer to the control of one of a plurality of formattin g agents;
converting said records of data into a suitable database storage format;
storing said formatted records in formatted record buffers;
writing said sequence identifier of each said buffer to the respective said formatted record buffer;
forwarding said formatted record buffers to a record identifier agent;
assigning a page location for each record in said formatted record buffers using said sequence identifiers to maintain said predetermined order.
using one or more reading agents, reading data records from the source in which they are stored;
storing said records in memory buffers;
tagging each buffer with a sequence identifier corresponding to said predetermined order of data;
transferring control of each said buffer to the control of one of a plurality of formattin g agents;
converting said records of data into a suitable database storage format;
storing said formatted records in formatted record buffers;
writing said sequence identifier of each said buffer to the respective said formatted record buffer;
forwarding said formatted record buffers to a record identifier agent;
assigning a page location for each record in said formatted record buffers using said sequence identifiers to maintain said predetermined order.
6. The method of claim 5 in which said formatted record buffers are processed by a record identifier agent as they are received.
7. The method of claim 6 in which said formatted record buffers are processed by a record identifier in the order of their sequence identifiers.
8. The method of claim 5 including assembling said records into pages and writing said pages to storage at the location specified.
9. For a parallel processing system, a method of loading data records having a pre selected order from a source location, in parallel, into a database, while maintaining said order, comprising:
reading a record from said source location;
storing said record in memory buffers of predetermined size;
tagging each buffer with a sequence number corresponding to said p re selected order of said record in its source location;
transferring control of said buffers to a plurality of formatter agents which operate in parallel;
converting each record to a suitable database storage format;
storing said formatted records in formatted record buffers;
writing a sequence identification number of each said buffer to said corresponding formatted record buffer;
transferring control of said formatted record buffers to a RIDer agent;
for each formatted record buffer being received in sequence assigning a page location to each record;
transferring control of said formatted data buffer to a plurality of writer agents;
assembling formatted records into pages writing said pages to said assigned storage locations.
reading a record from said source location;
storing said record in memory buffers of predetermined size;
tagging each buffer with a sequence number corresponding to said p re selected order of said record in its source location;
transferring control of said buffers to a plurality of formatter agents which operate in parallel;
converting each record to a suitable database storage format;
storing said formatted records in formatted record buffers;
writing a sequence identification number of each said buffer to said corresponding formatted record buffer;
transferring control of said formatted record buffers to a RIDer agent;
for each formatted record buffer being received in sequence assigning a page location to each record;
transferring control of said formatted data buffer to a plurality of writer agents;
assembling formatted records into pages writing said pages to said assigned storage locations.
10. The method of claim 9 for a parallel processing system, a method of loading data records having a pre selected order from a source location, in parallel, into a database, while maintaining said order, comprising:
reading a record from said source location;
storing said record in memory buffers of predetermined size;
tagging each buffer with a sequence number corresponding to said p re selected order of said record in its source location;
transferring control of said buffers to a plurality of formatter agents;
converting each record to a suitable database storage format;
storing said formatted records in formatted record buffers;
writing a sequence identification number of each said buffer to said corresponding formatted record buffer;
transferring control of said formatted record buffers to a RIDer agent;
for each formatted record buffer being received processing said formatted recordbuffers in order of their respective sequence identification numbers assigning page locations to said records maintaining said pre selected order by means of said sequence number order and sequence identification number;
transferring control of said formatted data buffer to a plurality of writer agents;
assembling formatted records into pages writing said pages to said assigned storage locations.
reading a record from said source location;
storing said record in memory buffers of predetermined size;
tagging each buffer with a sequence number corresponding to said p re selected order of said record in its source location;
transferring control of said buffers to a plurality of formatter agents;
converting each record to a suitable database storage format;
storing said formatted records in formatted record buffers;
writing a sequence identification number of each said buffer to said corresponding formatted record buffer;
transferring control of said formatted record buffers to a RIDer agent;
for each formatted record buffer being received processing said formatted recordbuffers in order of their respective sequence identification numbers assigning page locations to said records maintaining said pre selected order by means of said sequence number order and sequence identification number;
transferring control of said formatted data buffer to a plurality of writer agents;
assembling formatted records into pages writing said pages to said assigned storage locations.
11. Apparatus for loading data records having a pre selected order using a parallel data processing system to load said data into the data storage of a database using said data processing system while maintaining said pre selected data order, comprising;
at least one reader agent to read said data;
buffer storage means for storing said data in buffers;
tagging means for tagging each buffer containing said data with a sequence number corresponding to the pre selected order of the data;
a plurality of formatter agents for converting said data records read by said at least one reader agent to a format suitable for said database;
means for storing said formatted records in formatted record buffers, with the sequence numbers of the buffers containing said data;
a RIDer agent for assigning page locations for each said formatted data records;means for assembling said formatted records into pages;
a plurality of writer agents for writing each said pages to said assigned storage locations.
at least one reader agent to read said data;
buffer storage means for storing said data in buffers;
tagging means for tagging each buffer containing said data with a sequence number corresponding to the pre selected order of the data;
a plurality of formatter agents for converting said data records read by said at least one reader agent to a format suitable for said database;
means for storing said formatted records in formatted record buffers, with the sequence numbers of the buffers containing said data;
a RIDer agent for assigning page locations for each said formatted data records;means for assembling said formatted records into pages;
a plurality of writer agents for writing each said pages to said assigned storage locations.
12. A computer program product comprising:
a computer usable medium having computer readable program code means embodied therein for causing a computer to load user data, the computer program product comprising:
computer readable program code means for causing a computer to effect apparatus for loading data records having a pre selected order using a parallel data processing system to load said data into the data storage of a database using said data processing system while maintaining said pre selected data order, comprising;
computer readable program code means for causing a computer to effect at least one reader agent to read said data;
computer readable program code means for causing a computer to effect buffer storage means for storing said data in buffers;
computer readable program code means for causing a computer to effect tagging means for tagging each buffer containing said data with a sequence number corresponding to the pre selected order of the data;
computer readable program code means for causing a computer to effect a plurality of formatter agents for converting said data records read by said at least one reader agent to a format suitable for said database;
computer readable program code means for causing a computer to effect means for storing said formatted records in formatted record buffers, with the sequence num bers of the buffers containing said data;
computer readable program code means for causing a computer to effect a (RIDer) agent for assigning page locations for each said formatted data records;
computer readable program code means for causing a computer to effect means for assembling said formatted records into pages, and;
computer readable program code means for causing a computer to effect a plurality of writer agents for writing each said pages to said assigned storage locations.
a computer usable medium having computer readable program code means embodied therein for causing a computer to load user data, the computer program product comprising:
computer readable program code means for causing a computer to effect apparatus for loading data records having a pre selected order using a parallel data processing system to load said data into the data storage of a database using said data processing system while maintaining said pre selected data order, comprising;
computer readable program code means for causing a computer to effect at least one reader agent to read said data;
computer readable program code means for causing a computer to effect buffer storage means for storing said data in buffers;
computer readable program code means for causing a computer to effect tagging means for tagging each buffer containing said data with a sequence number corresponding to the pre selected order of the data;
computer readable program code means for causing a computer to effect a plurality of formatter agents for converting said data records read by said at least one reader agent to a format suitable for said database;
computer readable program code means for causing a computer to effect means for storing said formatted records in formatted record buffers, with the sequence num bers of the buffers containing said data;
computer readable program code means for causing a computer to effect a (RIDer) agent for assigning page locations for each said formatted data records;
computer readable program code means for causing a computer to effect means for assembling said formatted records into pages, and;
computer readable program code means for causing a computer to effect a plurality of writer agents for writing each said pages to said assigned storage locations.
13. A program storage device readable by a machine, tangibly embodying a program of instructions executable by the machine to perform method steps for loading data having a predetermined order of data records from a source into a database using parallel processing said method steps comprising:
using one or more reading agents, reading data records from the source in which they are stored;
storing said records in groups of records;
tagging each group with a sequence identifier corresponding to said predetermined order of data;
formatting the data records in said groups using a plurality of formatting agents in parallel;
storing said formatted records in formatted record groups;
writing said sequence identifier of each said group to the respective formatted record group;
assigning page locations for said records using said sequence identifiers to maintain said predetermined order.
using one or more reading agents, reading data records from the source in which they are stored;
storing said records in groups of records;
tagging each group with a sequence identifier corresponding to said predetermined order of data;
formatting the data records in said groups using a plurality of formatting agents in parallel;
storing said formatted records in formatted record groups;
writing said sequence identifier of each said group to the respective formatted record group;
assigning page locations for said records using said sequence identifiers to maintain said predetermined order.
Priority Applications (2)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CA002209549A CA2209549C (en) | 1997-07-02 | 1997-07-02 | Method and apparatus for loading data into a database in a multiprocessor environment |
| US09/093,188 US6088705A (en) | 1997-07-02 | 1998-06-08 | Method and apparatus for loading data into a database in a multiprocessor environment |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CA002209549A CA2209549C (en) | 1997-07-02 | 1997-07-02 | Method and apparatus for loading data into a database in a multiprocessor environment |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CA2209549C true CA2209549C (en) | 2000-05-02 |
Family
ID=4161001
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CA002209549A Expired - Fee Related CA2209549C (en) | 1997-07-02 | 1997-07-02 | Method and apparatus for loading data into a database in a multiprocessor environment |
Country Status (2)
| Country | Link |
|---|---|
| US (1) | US6088705A (en) |
| CA (1) | CA2209549C (en) |
Families Citing this family (35)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US6453334B1 (en) | 1997-06-16 | 2002-09-17 | Streamtheory, Inc. | Method and apparatus to allow remotely located computer programs and/or data to be accessed on a local computer in a secure, time-limited manner, with persistent caching |
| US6205451B1 (en) * | 1998-05-22 | 2001-03-20 | Oracle Corporation | Method and apparatus for incremental refresh of summary tables in a database system |
| US6370528B1 (en) * | 1999-05-28 | 2002-04-09 | Unisys Corporation | High speed method for flushing data buffers and updating database structure control information |
| US6351744B1 (en) * | 1999-05-28 | 2002-02-26 | Unisys Corporation | Multi-processor system for database management |
| US6411969B1 (en) * | 1999-08-13 | 2002-06-25 | Unisys Corporation | Enhanced system and method for management of system database utilities |
| US6775831B1 (en) * | 2000-02-11 | 2004-08-10 | Overture Services, Inc. | System and method for rapid completion of data processing tasks distributed on a network |
| US7320004B1 (en) * | 2000-04-28 | 2008-01-15 | Microsoft Corporation | System and method for managing database files in a client management tool |
| US6581020B1 (en) * | 2000-10-10 | 2003-06-17 | Velquest Corporation | Process-linked data management system |
| US20020083183A1 (en) * | 2000-11-06 | 2002-06-27 | Sanjay Pujare | Conventionally coded application conversion system for streamed delivery and execution |
| US7062567B2 (en) | 2000-11-06 | 2006-06-13 | Endeavors Technology, Inc. | Intelligent network streaming and execution system for conventionally coded applications |
| US20020087883A1 (en) * | 2000-11-06 | 2002-07-04 | Curt Wohlgemuth | Anti-piracy system for remotely served computer applications |
| US8831995B2 (en) | 2000-11-06 | 2014-09-09 | Numecent Holdings, Inc. | Optimized server for streamed applications |
| US7451196B1 (en) | 2000-12-15 | 2008-11-11 | Stream Theory, Inc. | Method and system for executing a software application in a virtual environment |
| US7158994B1 (en) | 2001-09-28 | 2007-01-02 | Oracle International Corporation | Object-oriented materialized views |
| US7487168B2 (en) * | 2001-11-01 | 2009-02-03 | Microsoft Corporation | System and method for loading hierarchical data into relational database systems |
| US7111020B1 (en) * | 2002-03-26 | 2006-09-19 | Oracle International Corporation | Incremental refresh of materialized views containing rank function, and rewrite of queries containing rank or rownumber or min/max aggregate functions using such a materialized view |
| US8478742B2 (en) * | 2004-04-14 | 2013-07-02 | Oracle Corporation | Using estimated cost to refresh a set of materialized views (MVS) |
| US7890497B2 (en) | 2004-04-14 | 2011-02-15 | Oracle International Corporation | Using estimated cost to schedule an order for refreshing a set of materialized views (MVS) |
| US7734602B2 (en) | 2004-04-14 | 2010-06-08 | Oracle International Corporation | Choosing whether to use a delayed index maintenance depending on the portion of the materialized view (MV) changed |
| US20060048136A1 (en) * | 2004-08-25 | 2006-03-02 | Vries Jeff D | Interception-based resource detection system |
| US7240162B2 (en) | 2004-10-22 | 2007-07-03 | Stream Theory, Inc. | System and method for predictive streaming |
| US8359591B2 (en) * | 2004-11-13 | 2013-01-22 | Streamtheory, Inc. | Streaming from a media device |
| US8024523B2 (en) | 2007-11-07 | 2011-09-20 | Endeavors Technologies, Inc. | Opportunistic block transmission with time constraints |
| US20060218165A1 (en) * | 2005-03-23 | 2006-09-28 | Vries Jeffrey De | Explicit overlay integration rules |
| WO2006102621A2 (en) | 2005-03-23 | 2006-09-28 | Stream Theory, Inc. | System and method for tracking changes to files in streaming applications |
| US8261345B2 (en) | 2006-10-23 | 2012-09-04 | Endeavors Technologies, Inc. | Rule-based application access management |
| US7711678B2 (en) * | 2006-11-17 | 2010-05-04 | Microsoft Corporation | Software transaction commit order and conflict management |
| US7860847B2 (en) * | 2006-11-17 | 2010-12-28 | Microsoft Corporation | Exception ordering in contention management to support speculative sequential semantics |
| US8010550B2 (en) * | 2006-11-17 | 2011-08-30 | Microsoft Corporation | Parallelizing sequential frameworks using transactions |
| US8024714B2 (en) | 2006-11-17 | 2011-09-20 | Microsoft Corporation | Parallelizing sequential frameworks using transactions |
| US8074219B2 (en) | 2007-06-27 | 2011-12-06 | Microsoft Corporation | Order preservation in data parallel operations |
| US9323680B1 (en) * | 2007-09-28 | 2016-04-26 | Veritas Us Ip Holdings Llc | Method and apparatus for prefetching data |
| US8892738B2 (en) | 2007-11-07 | 2014-11-18 | Numecent Holdings, Inc. | Deriving component statistics for a stream enabled application |
| US11372844B1 (en) * | 2018-11-02 | 2022-06-28 | NortonLifeLock Inc. | Systems and methods for stateless asynchronous database loading while maintaining ordering |
| CN111696598B (en) * | 2020-06-12 | 2022-04-26 | 合肥沛睿微电子股份有限公司 | Storage device and low-level formatting method thereof |
Family Cites Families (7)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| JPH02236668A (en) * | 1989-03-10 | 1990-09-19 | Hitachi Ltd | Input/output processing method |
| US5117495A (en) * | 1990-03-07 | 1992-05-26 | Syncsort Incorporated | Method of sorting data records |
| JP3415914B2 (en) * | 1993-10-12 | 2003-06-09 | 富士通株式会社 | Parallel merge sort processing method |
| US5566349A (en) * | 1994-05-16 | 1996-10-15 | Trout; Ray C. | Complementary concurrent cooperative multi-processing multi-tasking processing system using shared memories with a minimum of four complementary processors |
| US5822749A (en) * | 1994-07-12 | 1998-10-13 | Sybase, Inc. | Database system with methods for improving query performance with cache optimization strategies |
| JPH0962558A (en) * | 1995-08-29 | 1997-03-07 | Fuji Xerox Co Ltd | Method and system for database management |
| US5666525A (en) * | 1995-09-21 | 1997-09-09 | The Trustees Of Columbia University In The City Of New York | System and method for performing an efficient join operation on large tables with a small main memory |
-
1997
- 1997-07-02 CA CA002209549A patent/CA2209549C/en not_active Expired - Fee Related
-
1998
- 1998-06-08 US US09/093,188 patent/US6088705A/en not_active Expired - Lifetime
Also Published As
| Publication number | Publication date |
|---|---|
| US6088705A (en) | 2000-07-11 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CA2209549C (en) | Method and apparatus for loading data into a database in a multiprocessor environment | |
| US8239343B2 (en) | Database reorganization technique | |
| JP4206586B2 (en) | Database management method and apparatus, and storage medium storing database management program | |
| US6085200A (en) | System and method for arranging database restoration data for efficient data recovery in transaction processing systems | |
| US7194589B2 (en) | Reducing disk IO by full-cache write-merging | |
| EP1808779B1 (en) | Bundling database | |
| US20020116376A1 (en) | Routine executing method in database system | |
| US6427143B1 (en) | Method for loading rows into a database table while enforcing constraints | |
| Berra et al. | The Role of Associative Array Processers in Data Base Machine Architecture | |
| US6295539B1 (en) | Dynamic determination of optimal process for enforcing constraints | |
| US6230243B1 (en) | Method, system and program products for managing changed data of castout classes | |
| US7225178B2 (en) | Data structure in database, database system for managing database and database managing method and system | |
| US5678024A (en) | Method and system for dynamic performance resource management within a computer based system | |
| US7502778B2 (en) | Apparatus, system, and method for efficient adaptive parallel data clustering for loading data into a table | |
| US7080206B2 (en) | System and method for adaptively loading input data into a multi-dimensional clustering table | |
| US5519860A (en) | Central processor index sort followed by direct record sort and write by an intelligent control unit | |
| US6219672B1 (en) | Distributed shared memory system and method of controlling distributed shared memory | |
| KR20050120660A (en) | Method of addressing data in shared memory by means of an offset | |
| US20210209087A1 (en) | Reorganization of Databases by Sectioning | |
| KR20010103620A (en) | Method for checking tablespaces involved in referential integrity | |
| Torii et al. | Accelerating non-numerical processing by an extended vector processor | |
| Curtice | Data base design using IMS/360 | |
| Stanfill | Parallel Information Retrieval Algorithms. | |
| CN115982156A (en) | Data processing method, data processing apparatus, computer device, storage medium, and program product | |
| JP3578501B2 (en) | Document search method and apparatus |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| EEER | Examination request | ||
| MKLA | Lapsed |