Application programming interface for monitoring data warehouse activity occurring through a client/server open database connectivity interface

• US 6,363,391 B1
• Filed: 05/29/1998
• Issued: 03/26/2002
• Est. Priority Date: 05/29/1998
• Status: Expired due to Term

• Current Assignee: Bull HN Information Systems Incorporated (Atos SE)
• Original Assignee: Bull HN Information Systems Incorporated (Atos SE)
• Inventors: Rosensteel, Kenneth R. Jr.
• Primary Examiner(s): Alam, Hosain T.
• Assistant Examiner(s): FLEURANTIN, JEAN B
• Application Number:
  US09/086,818
• Time in Patent Office: 1,397 Days
• Field of Search: 707/10, 707/2, 707/100-103, 709/223
• US Class Current: 1/1
• CPC Class Codes: G06F 11/3466 Performance evaluation by tracing or monitoring
• CPC Class Codes: G06F 16/252 between a Database Management System and a front-end application
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface

Abstract

An Application Programming Interface (API) provides interoperability between different monitoring and administrative components of a data warehouse system that utilizes different standard protocols. One of the protocols is the well known data connectivity protocol, Open Database Connectivity (ODBC) that defines a standard interface between applications and data sources. A second one of the protocols is the well known network management protocol, Simple Network Management Protocol (SNMP) that defines a standard interface between an agent component and a network management system. The API provides a facility that enables the different components to access user and connection information maintained by an ODBC server component derived from servicing client system application SQL queries made by system users.

Claims

1. A method for facilitating interoperability between components of a data warehouse system containing a warehouse database for storing warehouse information, the components including a number of different monitoring and administration components for monitoring users and recording information relating to the activity of warehouse processes pertaining to accessing information stored in the warehouse database, the method comprising:

(a) including in the warehouse system, an ODBC server component operatively coupled to a number of ODBC client systems for receiving SQL requests through a first standard protocol used for data connectivity, the ODBC server component being operatively coupled to the warehouse database through an ODBC driver component for accessing information from the warehouse database using the first standard data connectivity protocol;

(b) including in the warehouse system, a storage log facility operatively coupled to the ODBC server component and to a predetermined one of the different warehouse components for enabling storage of information pertaining to user sessions and SQL queries by the OBDC server component for optimizing warehouse database storage and interfaces; and

,

(c) including in the warehouse system an API component as part of the ODBC server component that provides interoperability between the first standard protocol and other standard protocols for enabling the different warehouse monitoring and administration components to perform their functions pertaining to the warehouse database utilizing information received from the ODBC client systems and stored in the storage log facility.

2. The method of claim 1 wherein the method further comprises the step of including in the number of different monitoring and administration warehouse components:

(d) a network agent component for performing the function of monitoring users and the activity of warehouse processes utilizing one of the other standard protocols; and

,

(e) an usage monitoring component coupled to the network agent component, the usage monitoring component for performing the function of recording in a usage database, information pertaining to user sessions and SQL queries issued by the individual users of the client systems to access the warehouse database.

3. The method of claim 2 further including the step of populating the usage database with records having a predefined format by the usage monitoring component accessing information from the storage log facility.

4. The method of claim 1 wherein step (b) further includes:

storing the information as entries having a predetermined format into a number of different log files.

5. The method of claim 4 wherein the method further includes the steps of:

storing entries in a first file of the number of different log files corresponding to records identifying user sessions and their attributes; and

storing entries in a second file of the number of different log files corresponding to records identifying user SQL statements and their attributes.

6. The method of claim 5 wherein each record stored in the first log file is formatted to include the following information:

User_name;

Database_name;

Login_date;

Logout-date;

Session_ID; and

wherein each record of the second log file is formatted to include the following information;

User Requests;

Session_ID;

SQL-text;

Query-time;

Return_status;

Return_rows;

Return_bytes;

Query- type and Tuple-size.

7. The method of claim 1 wherein the method further comprises the step of including in the ODBC server component, an allocated memory area for storing routines included in the API component for maintaining interoperability between warehouse components and a number of table structures for storing entries pertaining to tracking servers operation, active end user database connections and end user SQL requests.

8. The method of claim 7 wherein the routines of the API component includes a first routine for obtaining parameters for the ODBC server component and warehouse server components, a second routine for obtaining information pertaining to active end user connections and a third routine for obtaining information pertaining to active SQL query requests made by end users.

9. The method of claim 7 wherein the number of table structures includes a servers table, a connections table and an SQL requests table.

10. The method of claim 7 wherein the servers table includes the following information sections:

a process ID section, a listen address section and a server name section.

11. The method of claim 7 wherein the connections table includes the following information sections:

session ID, connect ID, user ID, database name, login date/time;

logout date/time, number of messages sent, number of messages received, number of bytes received, last message and the direction of the last message.

12. The method of claim 7 wherein the SQL requests table includes the following information sections:

session ID, SQL text, query start time, query stop time, return status, number of rows returned, number of bytes returned, query type and tuple size.

13. The method of claim 2 wherein the usage monitoring component performs the functions of reading the storage log facility and writing data records in a defined format and running reports for analyzing the usage database.

14. The method of claim 7 wherein the number of different monitoring and administration warehouse components further includes an SNMP server component that operatively couples to the network agent component corresponding to an SNMP agent component and includes a MIB database for storing a number of information objects, the method further including the step of periodically polling the ODBC server component by the SNMW server component through the API component routines utilizing the one of the standard protocol corresponding to an SNMP protocol for obtaining current usage information from the table structures of the allocated memory area of the ODBC server component for transfer to a section of the MIB database allocated for monitoring data warehouse activity.

15. The method of claim 14 wherein the section is organized to contain objects being managed by the SNMP server component defining active connections, active servers and SQL requests issued by client end users.

16. The method of claim 15 wherein the method further comprises the step of including an administration interface in the SNMP server component for enabling an administrator to gather and report warehouse data activity derived from the objects stored in the MIB database.

17. The method of claim 16 wherein the method further comprises the step of including enabling the starting and stopping of the ODBC server component and for operating different ones of the warehouse components of the warehouse system.

18. A facility for providing interoperability between components of a data warehouse system containing a warehouse database for storing warehouse information, the components including a number of different monitoring and administration components for monitoring users and recording information relating to the activity of warehouse processes pertaining to accessing information stored in the warehouse database, the facility comprising:

(a) an ODBC server component operatively coupled to a number of ODBC client systems for receiving SQL requests through a first standard protocol used for data connectivity, the ODBC server component being operatively coupled to the warehouse database through an ODBC driver component for accessing information from the warehouse database using the first standard data connectivity protocol;

(b) a storage log facility operatively coupled to the ODBC server component and to a predetermined one of the warehouse components for enabling storage of information pertaining to user sessions and SQL queries by the ODBC server component for optimizing warehouse database storage and interfaces; and

,

(c) an API component included as part of the ODBC server component that provides interoperability between the first standard protocol and other standard protocols for enabling the different warehouse monitoring and administration components to perform their functions relating to the warehouse database utilizing information received from the ODBC client systems and stored in the storage log facility.

19. The facility of claim 18 wherein the number of different monitoring and administration warehouse components includes:

(a) a network agent component for performing the function of monitoring users and the activity of warehouse processes utilizing one of the other standard protocols; and

,

(b) an usage monitoring component coupled to the network agent component, the usage monitoring component recording in a usage database, information pertaining to user sessions and SQL queries issued by the individual users of the client systems to access the warehouse database.

20. The facility of claim 19 wherein the functions performed by the usage monitor component operates to access information from the storage log facility to populate the usage database with records having a predefined format.

21. The facility of claim 18 wherein the ODBC server component stores the information as entries having a predetermined format into a number of different log files.

22. The facility of claim 21 wherein the number of log files includes:

a first file for storing entries corresponding to records identifying user sessions and their attributes; and

a second file for storing entries corresponding to records identifying user SQL statements and their attributes.

23. The facility of claim 22 wherein each record stored in the first log file is formatted to include the following information:

User_name;

Database_name;

Login-date;

Logout-date;

Session_ID; and

wherein each record of the second log file is formatted to include the following information;

User_Requests;

Session-ID;

SQL text;

Query-time;

Return_status;

Return_rows;

Return-bytes;

Query- type and Tuple_size.

24. The facility of claim 18 wherein the ODBC server component further includes an allocated memory area for routines included in the API component for maintaining interoperability between warehouse components and a number of table structures for storing entries pertaining to tracking servers operation, active end user database connections and end user SQL requests.

25. The facility of claim 24 wherein the routines of the API component includes a first routine for obtaining parameters for the ODBC server component and warehouse server components, a second routine for obtaining information pertaining to active end user connections and a third routine for obtaining information pertaining to active SQL query requests made by end users.

26. The facility of claim 24 wherein the number of table structures includes a servers table, a connections table and an SQL requests table.

27. The facility of claim 26 wherein the servers table includes the following information sections:

a process ID section, a listen address section and a server name section.

28. The facility of claim 26 wherein the connections table includes the following information sections:

session ID, connect ID, user ID, database name, login date/time;

logout date/time, number of messages sent, number of messages received, number of bytes received, last message and the direction of the last message.

29. The facility of claim 26 wherein the SQL requests table includes the following information sections:

session ID, SQL text, query start time, query stop time, return status, number of rows returned, number of bytes returned, query type and tuple size.

30. The facility of claim 19 wherein the usage monitoring component performs the functions of reading the storage log facility and writing data records in a defined format and running reports for analyzing the usage database.

31. The facility of claim 24 wherein the number of different monitoring and administration warehouse components further includes an SNMP server component that operatively couples to the network agent component corresponding to an SNMP agent component and includes a MEB database for storing a number of information objects, the SNMP agent component being operative to periodically poll the ODBC server component through the API component routines utilizing the one of the standard protocols corresponding to an SNMP protocol for obtaining current usage information from the table structures of the allocated memory area of the ODBC server component for transfer to a section of the MEB database allocated for monitoring data warehouse activity.

32. The facility of claim 31 wherein the section of the NMB database is organized to contain objects being managed by the SNMP server component defining active connections, active servers and SQL requests issued by client end users.

33. The facility of claim 32 wherein the SNMP server component further includes an administration interface for enabling an administrator t warehouse data activity derived from the objects stored in the MEB database.

34. The facility of claim 33 wherein the administration interf facilities for enabling the starting and stopping of the ODBC server component and for operating different ones of the warehouse components of the warehouse system.