Method for fast bulk loading data into a database while bypassing exit routines

• US 7,958,091 B2
• Filed: 02/15/2007
• Issued: 06/07/2011
• Est. Priority Date: 02/16/2006
• Status: Active Grant

• Current Assignee: Thales DIS CPL USA, Inc. (Thales SA)
• Original Assignee: Ingrian Networks Incorporated (Thales SA)
• Inventors: Charpentier, Claude, Zolotusky, Anatol, Nair, Sushil
• Primary Examiner(s): Mofiz; Apu M
• Assistant Examiner(s): DAYE, CHELCIE L
• Application Number:
  US11/707,622
• Publication Number:
  US 20070214167A1
• Time in Patent Office: 1,573 Days
• Field of Search: 707/1, 707/102, 707/104.1, 707/202, 707/643, 718/101, 718/105
• US Class Current: 707/643
• CPC Class Codes: G06F 16/2379 Updates performed during online database operations; commit processing

Abstract

System, method, computer program for fast bulk loading data into database while bypassing exit routines. Method for bulk loading of data into database tables with exit routines defined for operations on such tables, method characterized by existence of exit routines is by-passed by performing data preprocessing in batch mode before loading; and loading preprocessed data into database by performing operations on lower level of tablespace and index spaces where database tables reside and not on database tables themselves. Method for bulk loading data into database tables with exit routines defined for operations on such tables comprising: batch processing data that requires processing to generate processed data; switching tablespace and related index spaces to stop mode; marking required space map entries used; loading previously processed data into claimed data pages; updating database index pages; resetting consistency tokens in updated page; and switching the tablespace and related index spaces to start mode.

Claims

1. A method for the bulk loading of data into the tables of a database with exit routines defined for operations on such tables, the method comprising:

by-passing the existence of exit routines by performing data preprocessing transforming the data in batch mode before loading; and

loading the transformed preprocessed data into the database by performing operations on the lower level of tablespace and index spaces where the DB tables reside and not on the database tables themselves, the method further comprising;

(1) switching the tablespace and all related index spaces to a stop mode;

(2) marking required space map entries as used;

(3) loading said preprocessed data into the claimed data pages;

(4) updating database index pages;

(5) resetting the consistency tokens in each updated page; and

(6) switching the tablespace and all related index spaces to a start mode.

2. A method according to claim 1, further comprising:

switching database objects alternately between an accessible state and an inaccessible state, and while in an accessible state;

(i) identifying needed resources for data to be loaded;

(ii) carrying out said loading of the data into the database; and

(iii) updating the database to reflect the changes resulting from the loading.

3. A method as in claim 1, wherein the transforming comprises at least one of batch encryption and batch decryption.

4. A method as in claim 1, further comprising, after the step of loading previously encrypted data into the claimed data pages, loading of preformatted data pages.

5. A method as in claim 1, wherein the bulk loading is performed in an off-line mode.

6. A method as in claim 1, wherein the bulk loading is performed in an on-line mode.

7. A method as in claim 5, wherein the database includes a table and the table has an associated table space and index space related to the table space, and the off-line mode is a mode in which the database table space and the related index space for the table are switched to a stop state.

8. A method as in claim 7, wherein the stop state is a state of a database objects in which the objects are inaccessible for any on-line database transaction.

9. A method as in claim 8, wherein the database objects comprise the database table space and the related index space for the table.

10. A method as in claim 1, wherein the database comprises an IBM DB2 database.

11. A method as in claim 10, wherein the table spaces comprise DB2 tablespaces that include the rows of one or more DB2 table.

12. A method as in claim 11, wherein data being loaded into the database is written directly into data pages of the tablespace.

13. A method as in claim 1, wherein the bulk loading is performed in an on-line mode in which mode the step of switching of the tablespace and all related index spaces to stop mode and the step of switching of the tablespace and all related index spaces to start mode are handled by a semaphore and provides for alternating between an enable state and a disable state at alternating time slots until the on-line processing task execution is completed.

14. A method as in claim 13, wherein the semaphore is implemented as an executive flag (exec-flag) indicator that signals the enable or disable state.

15. A method as in claim 6, wherein the on-line bulk loading is implemented using a time splicing procedure for alternating database objects between a locked state and an unlocked state.

16. A method as in claim 1, wherein the database is an IBM DB2 database running under an IBM Z/OS operating system.

17. A method as in claim 1, wherein the processed data comprises cryptographically encrypted data.

18. A computer program stored on a non-transitory computer readable storage medium and including computer program code for executing in a processor and memory coupled to the processor to alter the operation of the computer for performing a method for the bulk loading of data into the tables of a database with exit routines defined for operations on such tables, the method comprising:

(1) batch processing of the data that requires processing to generate transformed data before loading with bypassing of the exit routines;

(2) switching of the tablespace and all related index spaces to a stop mode;

(3) marking required space map entries as used;

(4) loading previously transformed data into the claimed data pages by performing operations on the lower level of tablespace and index spaces where the DB tables reside and not on the database tables themselves;

(5) updating database index pages;

(6) resetting the consistency tokens in each updated page; and

(7) switching the tablespace and all related index spaces to a start mode.

19. A system for the bulk loading of data into the tables of a database with exit routines defined for operations on such tables, the system comprising a computer having a processor and including:

means for by-passing the existence of exit routines and for performing data preprocessing transforming the data in batch mode before loading;

means for loading the transformed preprocessed data into the database by performing operations on the lower level of tablespace and index spaces where the database tables reside and not on the database tables themselves;

means for switching of the tablespace and all related index spaces to a stop mode;

means for marking required space map entries as used;

means for updating database index pages;

means for resetting the consistency tokens in each updated page; and

means for switching the tablespace and all related index spaces to a start mode.

20. A system as in claim 19, wherein each of the means for batch processing, means for switching to a stop mode, means for marking, means for loading, means for updating, means for resetting, and means for switching to a start mode comprise a computer program module including executable code for execution in a processor.

21. A system according to claim 19, further comprising:

a coordinator responsible for reading user directives and work environment parameters, for initializing a work environment, and for validating user directives by referring to a database catalog and meta data for an encryption process, and for instantiating a plurality of resource managers;

the resource managers an input/output manager, a data buffer manager, a sort manager, an optional Data Base Management System (DBMS) Manager, a worker thread manager, and a worker queue manager; and

the input/output manager being responsible for reading-in and writing-out data through a media manager, the media manager being selected from the set of media managers consisting of the Sort Manager, the Data Buffer Manager, and the DBMS Manager.