Method of efficient collection of SQL performance measures

• US 5,860,069 A
• Filed: 04/11/1997
• Issued: 01/12/1999
• Est. Priority Date: 04/11/1997
• Status: Expired due to Term

• Current Assignee: BMC Software Incorporated (KKR & Co., Inc.)
• Original Assignee: BMC Software Incorporated (KKR & Co., Inc.)
• Inventors: Wright, John Harvey
• Primary Examiner(s): Black, Thomas G.
• Assistant Examiner(s): ALAM, SHAHID AL
• Application Number:
  US08/835,953
• Time in Patent Office: 641 Days
• Field of Search: 707/2, 707/4, 707/3, 707/5
• US Class Current: 1/1
• CPC Class Codes: G06F 11/3409 for performance assessment
• CPC Class Codes: G06F 11/3466 Performance evaluation by tracing or monitoring
• CPC Class Codes: G06F 16/284 Relational databases
• CPC Class Codes: G06F 2201/80 Database-specific techniques
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation
• CPC Class Codes: Y10S 707/99935 Query augmenting and refining, e.g. inexact access

Abstract

A computerized method of intercepting SQL processing within a relational database. The relational database includes a relational database management program having SQL processing logic. The method includes the steps of: (a) enabling an SQL intercept code by connecting directly into the SQL processing logic of the relational database management program; (b) using the SQL intercept code to intercept an SQL statement made to the relational database; (c) using the SQL intercept code to collect a first set of real-time SQL performance information located in selected structures of the relational database; (d) returning control to the SQL processing logic until the relational database management program has processed the SQL statement to completion; (e) using the SQL intercept code to collect a second set of real-time SQL performance information located in the structures; (f) comparing the first set with the second set in order to measure the performance of the SQL statement; and (g) returning control to the relational database management program.

Claims

1. A computer-readable medium encoded with a method of intercepting SQL processing within a relational database including a relational database management program, the relational database management program including SQL processing logic, the method comprising the steps of:

a. enabling an SQL intercept code by connecting directly into the SQL processing logic of the relational database management program;

b. using the SQL intercept code to intercept an SQL statement made to the relational database;

c. using the SQL intercept code to collect a first set of real-time SQL performance information located in selected structures;

d. returning control to the SQL processing logic until the relational database management program has processed the SQL statement to completion;

e. using the SQL intercept code to collect a second set of real-time SQL performance information located in the selected structures;

f. comparing the first set with the second set in order to measure the performance of the SQL statement; and

g. returning control to the relational database management program.

2. The medium of claim 1, wherein the relational database management program is an IBM Database 2 program further including an SQL processing module, an SQL processing routine having an SQL processing entry point and an associated address, and an SQL Data Manipulation Language processing routine having an SQL Data Manipulation Language processing entry point and an associated address, in which the step of enabling comprises the substeps of:

a. loading a substitute SQL processing routine to the medium;

b. scanning the relational database management program for the address of the entry point of the SQL processing routine and the address of the entry point of the SQL Data Manipulation Language processing routine;

c. scanning a portion of the medium, which the SQL processing module occupies, for the address of the entry point of the SQL Data Manipulation Language processing routine; and

d. replacing the address of the entry point of the SQL Data Manipulation Language processing routine with an entry point address of a substitute SQL Data Manipulation Language processing routine.

3. The medium of claim 1, wherein the IBM Database 2 program further includes an SQL processing module, an SQL processing routine having an associated entry point and address, a Master Entry Point List, and an SQL Data Manipulation Language processing routine have an associated entry point and address, in which the step of enabling comprises the substeps of:

a. loading a substitute SQL processing routine to the medium;

b. locating the Master Entry Point List;

c. scanning the Master Entry Point List for the address of the entry point of the SQL processing routine;

d. scanning the Master Entry Point List for the address of the entry point of the SQL DML processing routine;

e. scanning a portion of the medium occupied by the SQL processing routine and locating each occurrence of the entry point address of the SQL DML processing routine; and

f. for each location matched in step e, replacing the entry point of the SQL Data Manipulation Language processing routine with an entry point of the substitute SQL Data Manipulation Language processing routine.

4. The medium of claim 3, wherein the SQL processing routine is DSNXERD and the SQL DML processing routine is DSNXERT.

5. The medium of claim 1, wherein the selected structures are control blocks and data structures.

6. A computer-readable medium encoded with a method of collecting selected SQL performance information associated with the processing of a current SQL Data Manipulation Language routine statement within a IBM Database 2 program including control blocks, data structures, an SQL processing module, an SQL processing routine having an associated entry point, and an SQL Data Manipulation Language processing routine comprising processing statements and an associated entry point entry point, in which the method comprises the steps of:

a. building, acquiring, and initializing storage areas and control blocks, as required;

b. enabling an SQL intercept code by connecting directly into the SQL processing routine of the IBM Database 2 program;

c. passing control to a substitute SQL Data Manipulation Language processing routine from the IBM Database 2 program at the point at which the IBM Database 2 program begins to process an SQL Data Manipulation Language statement;

d. navigating selected structures in order to obtain a series of metrics, and, concurrently, obtaining a first set of required values from the series of metrics;

e. passing control back to the SQL Data Manipulation Language processing routine;

f. allowing the relational database management system to complete SQL Data Manipulation Language processing requirements;

g. automatically passing control back to the substitute SQL Data Manipulation Language processing routine;

h. navigating the selected structures in order to obtain the series of metrics;

i. obtaining a second set of required values from the series of metrics;

j. comparing the first set and the second set;

k. summarizing the results of the comparing; and

l. returning control to the IBM Database 2 program.

7. The medium of claim 6 further utilizing an active reduction table, and, wherein, the current SQL Data Manipulation Language statement includes certain key values, the method comprising the additional step of summarizing the selected SQL performance information which corresponds to the current SQL Data Manipulation Language statement, for later retrieval, the step of summarizing comprising the substeps of:

a. locating an existing entry in the active reduction table that matches a key value of the current SQL Data Manipulation Language statement;

b. keeping track of certain information, such as counter values, logic flags, and status;

c. if any of the certain information does not exist, adding a new entry, setting the key values and a value for a current total to zero;

d. if the active reduction table is full, allocating a new active reduction table and going back to step a;

e. for each metric, adding each difference which the comparing step calculates to a total;

f. retaining the total as an active reduction table entry;

g. incrementing a count of updates for the active reduction table entry; and

h. automatically returning control to the IBM Database 2 program.

8. A computerized method of intercepting SQL processing within a relational database including a relational database management program, the relational database management program including SQL processing logic, the method comprising the steps of:

a. enabling an SQL intercept code by connecting directly into the SQL processing logic of the relational database management program;

b. using the SQL intercept code to intercept an SQL statement made to the relational database;

c. using the SQL intercept code to collect a first set of real-time SQL performance information located in selected structures;

d. returning control to the SQL processing logic until the relational database management program has processed the SQL statement to completion;

e. using the SQL intercept code to collect a second set of real-time SQL performance information located in the selected structures;

f. comparing the first set with the second set in order to measure the performance of the SQL statement; and

g. returning control to the relational database management program.

9. The method of claim 8, wherein the relational database management program is an IBM Database 2 program further including an SQL processing module, an SQL processing routine having an SQL processing entry point and an associated address, and an SQL Data Manipulation Language processing routine having an SQL Data Manipulation Language processing entry point and an associated address, in which the step of enabling comprises the substeps of:

a. loading a substitute SQL processing routine to the medium;

b. scanning the relational database management program for the address of the entry point of the SQL processing routine and the address of the entry point of the SQL Data Manipulation Language processing routine;

c. scanning a portion of the medium, which the SQL processing module occupies, for the address of the entry point of the SQL Data Manipulation Language processing routine; and

d. replacing the address of the entry point of the SQL Data Manipulation Language processing routine with an entry point address of a substitute SQL Data Manipulation Language processing routine.

10. The method of claim 8, wherein the IBM Database 2 program further includes an SQL processing module, an SQL processing routine having an associated entry point and address, a Master Entry Point List, and an SQL Data Manipulation Language processing routine have an associated entry point and address, in which the step of enabling comprises the substeps of:

a. loading a substitute SQL processing routine to the medium;

b. locating the Master Entry Point List;

c. scanning the Master Entry Point List for the address of the entry point of the SQL processing routine;

d. scanning the Master Entry Point List for the address of the entry point of the SQL DML processing routine;

e. scanning a portion of the medium occupied by the SQL processing routine and locating each occurrence of the entry point address of the SQL DML processing routine; and

f. for each location matched in step e, replacing the entry point of the SQL Data Manipulation Language processing routine with an entry point of the substitute SQL Data Manipulation Language processing routine.

11. The method of claim 10, wherein the SQL processing routine is DSNXERD, and the SQL DML processing routine is DSNXERT.

12. The method of claim 8, wherein the selected structures are control blocks and data structures of the relational database.

13. A computerized method of collecting selected SQL performance information associated with the processing of a current SQL Data Manipulation Language routine statement within a IBM Database 2 program including control blocks and data structures, an SQL processing module, an SQL processing routine having an associated entry point, and an SQL Data Manipulation Language processing routine comprising processing statements and an associated entry point entry point, in which the method comprises the steps of:

a. building, acquiring, and initializing storage areas and control blocks, as required;

b. enabling an SQL intercept code by connecting directly into the SQL processing routine of the IBM Database 2 program;

c. passing control to a substitute SQL Data Manipulation Language processing routine from the IBM Database 2 program at the point at which the IBM Database 2 program begins to process an SQL Data Manipulation Language statement;

d. navigating selected structures in order to obtain a series of metrics, and, concurrently, obtaining a first set of required values from the series of metrics;

e. passing control back to the SQL Data Manipulation Language processing routine;

f. allowing the relational database management system to complete SQL Data Manipulation Language processing requirements;

g. automatically passing control back to the substitute SQL Data Manipulation Language processing routine;

h. navigating the selected structures in order to obtain the series of metrics;

i. obtaining a second set of required values from the series of metrics;

j. comparing the first set and the second set;

k. summarizing the results of the comparing; and

l. returning control to the IBM Database 2 program.

14. The method of claim 13 further utilizing an active reduction table, and, wherein, the current SQL Data Manipulation Language statement includes certain key values, the method comprising the additional step of summarizing the selected SQL performance information which corresponds to the current SQL Data Manipulation Language statement, for later retrieval, the step of summarizing comprising the substeps of:

a. locating an existing entry in the active reduction table that matches a key value of the current SQL Data Manipulation Language statement;

b. keeping track of certain information, such as counter values, logic flags, and status;

c. if any of the certain information does not exist, adding a new entry, setting the key values and a value for a current total to zero;

d. if the active reduction table is full, allocating a new active reduction table and going back to step a;

e. for each metric, adding each difference which the comparing step calculates to a total;

f. retaining the total as an active reduction table entry;

g. incrementing a count of updates for the active reduction table entry; and

h. automatically returning control to the IBM Database 2 program.