Method and apparatus for recording and managing data object relationship data

Patent metadata

Field	Value
Publication number	US-10783122-B2
Application number	US-77725407-A
Country	US
Kind code	B2
Filing date	Jul 12, 2007
Priority date	May 10, 2002
Publication date	Sep 22, 2020
Grant date	Sep 22, 2020

Abstract

The invention relates to a method and apparatus for recording and maintaining stored information system object relationship information. Information contained within a stored information system (including system catalogs, referential constraints, triggers, table hierarchies, column references, indexes, stored program packages, system catalogs, stored procedures, stored queries, log/trace files of dynamically executed code, etc.) are searched to identify dependency relationships between objects. This object relationship information is stored and maintained in an information base. Information within the information based may be organized based upon subsets of objects that support a common application, service, or capability. Object relationship data is used to facilitate stored information system administration activities such as generating/restoring from archives, physically/logically segmenting data objects, replicating data objects; optimizing the distribution of data in distributed data system; and estimating operational capacity.

First claim

What is claimed is:

1. A method of discovering relationships among objects in a stored information system, comprising:
generating or updating an application set of a plurality of application sets, wherein the application set comprises both static and dynamic relationships between a set of objects associated with an application or capability supported by the stored information system, wherein the set of objects of the application set is handled and maintained as a consistent unit over time, and wherein the set of objects comprise one or more of files, database tables, or metadata;
identifying a first set of object relationships using static entity relationship discovery that identifies relationships between objects using one or more of stored system catalogs, data structures, or data tables;
populating the application set based on the first set of object relationships;
identifying a second set of object relationships using dynamic entity relationship discovery that identifies relationships between objects, wherein the relationships between objects are dynamically established during the stored information system execution of program code, wherein dynamic entity relationship discovery comprises at least the steps of:
identifying dependencies between database tables by searching structured query language (SQL) trace files, SQL log files, or both for the dependencies by extracting relationship metadata resulting from usage of the stored information system, wherein the SQL trace, the SQL log files, and the dependencies are dynamically established by programs of the stored information system upon executing dynamic SQL in response to a user instruction; and
storing the identified dependencies,
wherein the searching comprises searching SQL query syntax of the dynamic SQL in any of the SQL trace and the SQL log files;

updating the application set to include the second set of object relationships in addition to the first set of object relationships.

2. The method of claim 1 , wherein one or both of identifying the first set of object relationships or identifying the second set of object relationships comprises searching referential integrity constraints.

3. The method of claim 1 , wherein one or both of identifying the first set of object relationships or identifying the second set of object relationships comprises searching trigger definitions.

4. The method of claim 1 , wherein one or both of identifying the first set of object relationships or identifying the second set of object relationships comprises searching data hierarchy relationships.

5. The method of claim 1 , wherein one or both of identifying the first set of object relationships or identifying the second set of object relationships comprises searching data table column references.

6. The method of claim 1 , wherein one or both of identifying the first set of object relationships or identifying the second set of object relationships comprises tracing application execution.

7. The method of claim 1 , wherein one or both of identifying the first set of object relationships or identifying the second set of object relationships comprises tracing database execution.

8. The method of claim 1 , wherein one or both of identifying the first set of object relationships or identifying the second set of object relationships comprises tracing stored procedure execution.

9. The method of claim 1 , wherein one or both of identifying the first set of object relationships or identifying the second set of object relationships comprises tracing application package execution.

10. The method of claim 1 , further comprising:
receiving user inputs identifying a third set of object relationships;
updating the application set to include the third set of object relationships in addition to the first set of object relationships and the second set of object relationships.

11. The method of claim 10 , wherein the third set of object relationships comprises dependencies relating to relationships that are different from information within the stored information system.

12. The method of claim 1 , wherein one or both of identifying the first set of object relationships or identifying the second set of object relationships comprises receiving data object dependency information via a file interface.

13. The method of claim 1 , further comprising receiving and processing search instructions to identify the dependencies.

14. The method of claim 13 , wherein the search instructions include a starting object.

15. The method of claim 14 , wherein the starting object includes one of a table or a package.

16. The method of claim 13 , wherein the search instructions include a plurality of objects between which a relationship exists.

17. The method of claim 13 , wherein the search instructions include a plurality of tables between which a relationship exists.

18. The method of claim 1 , wherein storing identified dependencies further comprises storing information related to subsets of objects.

19. The method of claim 18 , wherein storing information related to subsets of objects comprises storing relationships between objects that are used by an application in common with the objects.

20. The method of claim 1 , further comprising managing objects based upon the stored dependencies.

21. The method of claim 20 , wherein the managing comprises performing administrative procedures based upon the stored dependencies.

22. The method of claim 21 , wherein administrative procedures are performed upon a subset of objects, based upon the stored dependencies.

23. The method of claim 22 , wherein the subset of objects is used by an application in common with the subset of objects.

24. The method of claim 22 , wherein the stored dependencies include manually defined dependencies relating to relationships that are different from information within the stored information system.

25. The method of claim 20 , wherein the managing comprises creating a backup archive.

26. The method of claim 20 , wherein the managing comprises restoring from an archive.

27. The method of claim 20 , wherein the managing comprises at least one of: allocating table space, physically segmenting data objects; logically segmenting data objects; replicating data objects; optimizing the distribution of data in a distributed data system; and estimating operational capacity.

28. The method of claim 1 , wherein the stored information system is a database.

29. The method of claim 1 , wherein extracting relationship metadata resulting from the usage of the stored information system comprises parsing the SQL query syntax of the dynamic SQL in any of the SQL trace and the SQL log files.

30. An apparatus including computer-executable instructions stored on a non-transitory computer-readable medium for discovering relationships among objects in a stored information system, the apparatus comprising:
a storage/management module configured to organize object relationship information collected into application sets and store the object relationship information within the stored information system, wherein each application set comprises both static and dynamic relationships between a set of objects associated with an application or capability supported by the stored information system, wherein the set of objects of the application set is handled and maintained

Assignees

• Farber Joel Frank
• Leamon Teresa Lynn
• Schwartz David Ray
• Smith Bryan Frederick
• Weil Donald Allan
• Servicenow Inc

Inventors

• Farber Joel Frank
• Leamon Teresa Lynn
• Schwartz David Ray
• Smith Bryan Frederick
• Weil Donald Allan

Classifications

• G06F16/21 Primary

  Design, administration or maintenance of databases · CPC title

• Y10S707/99943

  Generating database or data structure, e.g. via user interface · CPC title

• G06F16/9024

  Graphs; Linked lists (G06F16/9027 takes precedence) · CPC title

• G06F16/1734

  Details of monitoring file system events, e.g. by the use of hooks, filter drivers, logs · CPC title

• Y10S707/99944

  Object-oriented database structure · CPC title