US 6,931,389 B1Grant

System and method for filtering query statements from multiple plans and packages according to user-defined filters of query explain data

Issue Date:2005-08-16

•41 Claims

•29 Drawing Sheets

Abstract

An apparatus for filtering a plurality of groups of query statements according to identification data associated therewith includes a processor for executing instructions and a memory device having thereon modules of operational data and executable code for execution by the processor. The modules include at least one filtering module configured to receive one or more user-specified filtering criteria directed to a subset of the identification data and apply the filtering criteria to selectively exclude those of the plurality of groups not satisfying the filtering criteria. The modules also include a group listing module configured to display to a user those of the plurality of groups not excluded by the at least one filtering module.

Metadata

Assignee

International Business Machines Corporation

Inventors

Terry Michael Bleizeffer
Tanya Couch
Catherine Elizabeth Wuebker Drummond
Virginia Walbridge Hughes, Jr.
Theresa Hsing Lai
Joseph Davis Lea, III
David Harold Oberstadt

Application Information

Application Number:US 09/482,598

Filing Date:2000-01-13

Priority Date:1997-10-14

Art Unit:2167

Patent Drawings (29 sheets)

Description

Related Applications

[0001] This application is a continuation-in-part of U.S. patent application Ser. No. 08/949,636, filed Oct. 14, 1997 now U.S. Pat. No. 6,243,703, for “Interpreting Data Using a Graphical User Interface,” which is incorporated herein by reference.

Identification of Copyright

[0002] A portion of the disclosure of this patent document contains material which is subject to copyright protection. The copyright owner has no objection to the facsimile reproduction by anyone of the patent document or the patent disclosure, as it appears in the Patent and Trademark Office patent file or records, but otherwise reserves all copyright rights whatsoever.

Background of the Invention

[0003] 1. The Field of the Invention

[0004] The present invention relates generally to computer-implemented database systems. More specifically, the present invention relates to a system and method for filtering query statements from multiple plans and packages according to user-defined filters of query explain data.

[0005] 2. The Relevant Technology

[0006] Databases are computerized information storage and retrieval systems. A Relational Database Management System (RDBMS) is a database system which uses relational techniques for storing and retrieving data. Relational databases are organized into tables consisting of rows (tuples) and columns of data. A database typically includes many tables, and each table includes multiple rows and columns. The tables are conventionally stored in direct access storage devices (DASD), such as magnetic or optical disk drives, for semi-permanent storage.

[0007] Generally, users communicate with an RDBMS using a Structured Query Language (SQL) interface. The SQL interface allows users to create, manipulate, and query a database by formulating relational operations on the tables, either interactively, in batch files, or embedded in host languages such as C and COBOL. SQL has evolved into a standard language for RDBMS software and has been adopted as such by both the American National Standards Institute (ANSI) and the International Standards Organization (ISO).

[0008] The SQL standard provides that each RDBMS should respond to a particular query in the same way, regardless of the underlying database. However, the method that the RDBMS actually uses to find the requested information in the database is left to the RDBMS. Typically, there is more than one method that can be used by the RDBMS to access the requested data. The RDBMS, therefore, attempts to select the method that minimizes the computer time and resources (i.e. cost) for executing the query.

[0009] The RDBMS determines how to execute the SQL statements. The set of steps created by the RDBMS for executing the SQL statements is commonly referred to as the “access path.” In other words, the access path is a sequence of operations used by the RDBMS to obtain the data requested by the SQL query. Depending on the access path, an SQL statement might search an entire table space, or, alternatively, it might use an index. The access path is the key to determining how well an SQL statement performs. The description of the access path is stored in a plan table, which typically stores the access path data for a plurality of SQL statements.

[0010] In addition to determining the access path, some databases estimate the cost for executing each SQL statement. The estimated costs are typically stored in a statement table (as in the case of DB2® for OS/390®) or a similar table within the database. Like the plan table, the statement table stores the estimated statement costs for a plurality of SQL statements.

[0011] Databases also typically include statistics for such database objects as table spaces, indexes, tables, and columns. For example, in the case of a table, the statistical data may include the number of pages that contain rows of the table, the number of rows and columns in the table, as well as various other statistical data. The statistics are typically derived from the RDBMS “catalog,” which is an object that describes the entire database.

[0012] The above-described access path data, statement cost data, and object statistics data (referred to collectively as “query explain data”) assist the user in analyzing and improving the performance of SQL statements. For example, a query with a higher-than-average statement cost might alert the user to inefficiencies in the access path. By viewing the access path data, the user can selectively make changes to the query and/or the database, such as by adding an index in order to avoid a table space scan. The object statistics data similarly assists the user by describing the structure and organization of the database.

[0013] Unfortunately, analyzing SQL query performance is currently too time-consuming and complex for the average user. Typical systems include hundreds or thousands of query statements. Identifying the one or more statements that need to be improved can be a difficult task. In conventional systems, the user must manually locate the relevant query explain data in a plurality of tables, e.g. the plan table, the statement table, the function table, and the catalog tables, which can be tedious and time-consuming.

[0014] Moreover, each of the tables typically stores information corresponding to many different statements and objects. Consequently, the tables are often very large, making it difficult to locate the desired data. Likewise, the tables are often cryptic and hard to understand, even for database experts. For example, the plan table typically includes sixty or more columns and hundreds or thousands of rows. The access path data is stored in a tabular format, which, although easily understood by the RDBMS, is often too complicated to be effectively analyzed.

[0015] Furthermore, the query statements to be filtered are typically included in a number of packages and plans. A package is a collection of query statements found in a single application program. A plan is also a collection of query statements, but may include statements from one or more application programs. In large-scale database systems, the number of plans and packages is correspondingly large. Thus, a difficulty arises when trying to locate a particular package or plan for purposes of analyzing the query statements contained therein.

[0016] Accordingly, what is needed is a system, method, and article of manufacture for selectively filtering, reducing, or otherwise focusing a list of query statements based on a user's customized data requirements for improving SQL performance. Additionally, what is needed is a system, method, and article of manufacture for filtering query statements according to user-defined filters of query explain data. What is also needed is a system, method, and article of manufacture for generating user-defined filters of object reference data, statement cost data, and access path data. Moreover, what is needed is a system, method, and article of manufacture for storing and retrieving sets of user-defined filters. In addition, what is needed is a system, method, and article of manufacture for selectively filtering, reducing, or otherwise focusing a list of packages or plans according to user-defined filtering criteria.

Summary of the Invention

[0017] The present invention solves the foregoing problems by providing a system and method for filtering query statements from multiple plans and packages according to query explain data associated therewith. Additionally, the present invention provides a system and method for filtering a plurality of groups of query statements, such as one or more plans and packages, by applying user-specified filtering criteria to associated identification data.

[0018] In one aspect of the invention, an apparatus for filtering a plurality of groups of query statements according to identification data associated therewith includes a processor for executing instructions and a memory device having thereon modules of operational data and executable code for execution by the processor. In one embodiment, the modules include at least one filtering module configured to receive one or more user-specified filtering criteria directed to a subset of the identification data and apply the filtering criteria to selectively exclude those of the plurality of groups not satisfying the filtering criteria. In one embodiment, the modules also include a group listing module configured to display to a user those of the plurality of groups not excluded by the at least one filtering module.

[0019] In another aspect of the invention, a method for filtering a plurality of groups of query statements according to identification data associated therewith includes the step of receiving one or more user-specified filtering criteria directed to a subset of the identification data. In one embodiment, the method also includes the step of applying the filtering criteria to selectively exclude those of the plurality of groups not satisfying the filtering criteria. Additionally, the method includes in one embodiment the step of displaying to a user those of the plurality of groups not excluded by the filtering criteria.

[0020] In yet another aspect of the invention, an article of manufacture comprises a program storage medium readable by a processor and embodying one or more instructions executable by the processor to perform the above-described method for filtering a plurality of groups of query statements according to associated identification data.

[0021] These and other objects, features, and advantages of the present invention will become more fully apparent from the following description and appended claims, or may be learned by the practice of the invention as set forth hereinafter.

Brief Description of the Drawings

[0022] These and other more detailed and specific objects and features of the present invention are more fully disclosed in the following specification, reference being had to the accompanying drawings, in which

[0023] FIG. 1 is a schematic block diagram of a computer system suitable for implementing one embodiment of the invention;

[0024] FIG. 2 is a schematic block diagram of a system for filtering query statements according to user-defined filters of query explain data according to one embodiment of the invention;

[0025] FIG. 3 is a schematic block diagram of a query visualization module according to one embodiment of the invention;

[0026] FIG. 4 is an illustration of a query statement and a portion of a plan table according to one embodiment of the invention;

[0027] FIG. 5 is an illustration of a graphical representation of an access path according to one embodiment of the invention;

[0028] FIG. 6 is a schematic block diagram of a filter module according to one embodiment of the invention;

[0029] FIG. 7 is an illustration of the data flow within a filter module according to one embodiment of the invention;

[0030] FIG. 8 is a schematic flow chart of a method for filtering query statements according to user-defined filters of query explain data;

[0031] FIG. 9 is an illustration of an interactive display provided by a package selection module according to one embodiment of the invention;

[0032] FIG. 10 is an illustration of an interactive display provided by a plan selection module according to one embodiment of the invention;

[0033] FIG. 11 is an illustration of an unfiltered list of query statements according to one embodiment of the invention;

[0034] FIG. 12 is an illustration of an interactive display provided by an object filter generation module according to one embodiment of the invention;

[0035] FIG. 13 is an illustration of a filtered list of query statements according to one embodiment of the invention;

[0036] FIG. 14 is an illustration of an interactive display provided by a cost filter generation module according to one embodiment of the invention;

[0037] FIG. 15 is an illustration of a filtered list of query statements according to one embodiment of the invention;

[0038] FIG. 16 is an illustration of an interactive display provided by an access path filter generation module according to one embodiment of the invention;

[0039] FIG. 17 is an illustration of an interactive display provided by an access path filter generation module according to one embodiment of the invention;

[0040] FIG. 18 is an illustration of a filtered list of query statements according to one embodiment of the invention;

[0041] FIG. 19 is a schematic block diagram of a statement selection module according to one embodiment of the invention;

[0042] FIG. 20 is a schematic flow chart illustrating a method for filtering groups of query statements according to one embodiment of the invention;

[0043] FIG. 21 is an illustration of an interactive display provided by a package selection module according to one embodiment of the invention;

[0044] FIG. 22 is an illustration of an interactive display provided by a package selection module according to one embodiment of the invention;

[0045] FIG. 23 is an illustration of a filtered list of packages provided by a package listing module according to one embodiment of the invention;

[0046] FIG. 24 is an illustration of an interactive display provided by a package selection module according to one embodiment of the invention;

[0047] FIG. 25 is an illustration of an interactive display provided by a plan selection module according to one embodiment of the invention;

[0048] FIG. 26 is an illustration of an interactive display provided by a plan selection module according to one embodiment of the invention;

[0049] FIG. 27 is an illustration of a filtered list of plans provided by a plan listing module according to one embodiment of the invention; and

[0050] FIG. 28 is an illustration of an interactive display provided by a plan selection module according to one embodiment of the invention.

Detailed Description of the Preferred Embodiments

[0051] The presently preferred embodiments of the present invention will be best understood by reference to the drawings, wherein like parts are designated by like numerals throughout. It will be readily understood that the components of the present invention, as generally described and illustrated in the figures herein, may be arranged and designed in a wide variety of different configurations. Thus, the following more detailed description of the embodiments of the apparatus, system, and method of the present invention, as represented in the Figures, is not intended to limit the scope of the invention, as claimed, but is merely representative of presently preferred embodiments of the invention.

[0052] The Figures include schematic block diagrams and flow chart diagrams which illustrate in more detail the preferred embodiments of the present invention. The schematic block diagrams illustrate certain embodiments of modules for performing various functions of the present invention. In general, the represented modules include therein executable and operational data for operation within a computer system of FIG. 1 in accordance with the present invention.

[0053] As used herein, the term executable data, or merely an “executable,” is intended to include any type of computer instructions and computer executable code that may be located within a memory device and/or transmitted as electronic signals over a system bus or network. An identified module of executable code may, for instance, comprise one or more physical or logical blocks of computer instructions which may, for instance, be organized as an object, procedure, or function. Nevertheless, the executables of an identified module need not be located together, but may comprise disparate instructions stored in different locations which together comprise the module and achieve the purpose stated for the module. Indeed, an executable may be a single instruction, or many instructions, and may even be distributed over several different code segments, among different programs, and across several memory devices.

[0054] Similarly, operational data may be identified and illustrated herein within modules, and may be embodied in any suitable form and organized within any suitable type of data structure. The operational data may be collected as a single data set, or may be distributed over different locations including over different storage devices, and may at least partially exist merely as electronic signals on a system bus or network.

[0055] FIG. 1 is a schematic block diagram that illustrates a computer system 10 in which executable and operational data, operating in accordance with the present invention, may be hosted on one or more computer stations 12 and 13 in a network 14. The network 14 may comprise a wide area network (WAN) or local area network (LAN) and may also comprise an interconnected system of networks, one particular example of which is the Internet and the World Wide Web supported on the Internet.

[0056] A typical computer station 12 may include a processor or CPU 16. The CPU 16 may be operably connected to one or more memory devices 18. The memory devices 18 are depicted as including a non-volatile storage device 20 such as a hard disk drive or CD-ROM drive, a read-only memory (ROM) 22, and a random access volatile memory (RAM) 24. Preferably, the computer station 12 operates under the control of an operating system (OS) 25, such as MVS®, OS/390®, AIX®, OS/2®, WINDOWS NT®, WINDOWS®, UNIX®, and the like.

[0057] The computer station 12 or system 10 in general may also include one or more input devices 26, such as a mouse or keyboard, for receiving inputs from a user or from another device. Similarly, one or more output devices 28, such as a monitor or printer, may be provided within or be accessible from the computer system 10. A network port such as a network interface card 30 may be provided for connecting to outside devices through the network 14. In the case where the network 14 is remote from the computer station, the network interface card 30 may comprise a modem, and may connect to the network 14 through a local access line such as a telephone line.

[0058] Within any given station 12, a system bus 32 may operably interconnect the CPU 16, the memory devices 18, the input devices 26, the output devices 28 the network card 30, and one or more additional ports 34. The system bus 32 and a network backbone 36 may be regarded as data carriers. As such, the system bus 32 and the network backbone 36 may be embodied in numerous configurations. For instance, wire, fiber optic line, wireless electromagnetic communications by visible light, infrared, and radio frequencies may be implemented as appropriate.

[0059] In general, the network 14 may comprise a single local area network (LAN), a wide area network (WAN), several adjoining networks, an intranet, or as in the manner depicted, a system of interconnected networks such as the Internet 40. The individual stations 12 communicate with each other over the backbone 36 and/or over the Internet 40 with varying degrees and types of communication capabilities and logic capability. The individual stations 12 may include a mainframe computer on which the modules of the present invention may be hosted.

[0060] Different communication protocols, e.g., ISO/OSI, IPX, TCP/IP, may be used on the network, but in the case of the Internet, a single, layered communications protocol (TCP/IP) generally enables communications between the differing networks 14 and stations 12. Thus, a communication link may exist, in general, between any of the stations 12.

[0061] The stations 12 connected on the network 14 may comprise application servers 42, and/or other resources or peripherals 44, such as printers and scanners. Other networks may be in communication with the network 14 through a router 38 and/or over the Internet 40.

[0062] Referring now to FIG. 2, a schematic block diagram of one embodiment of the invention includes first and second stations 12A, 12B. The first station 12A is preferably a workstation-class computer, such as an PC workstation, available from IBM Corporation. The second station 12B is preferably an IBM mainframe computer operating under MVS® or OS/390®. In one embodiment, the stations 12A, 12B are coupled via a network 14 using a distributed remote data architecture (DRDA). Those skilled in the art, however, will recognize that the invention may be implemented using a variety of computing platforms and/or network architectures.

[0063] In one embodiment, the first station 12A includes a query visualization module 50, which is a tool that assists a user in visualizing or otherwise understanding query explain data for one or more queries to be executed. As described above, the query explain data includes, in one embodiment, the access path data, the statement cost data, and the object statistics data used in analyzing and improving the performance of SQL statements.

[0064] The second station 12B preferably stores a database 52, as well as an RDBMS 54 for managing the database 52, such as DB2® for OS/390®, available from IBM. As used herein, the term “database” may generically refer to a combination of the RDBMS 54 and the database 52. In one embodiment, the query visualization module 50 and the RDBMS 54 are linked via an interface module 56, such as DB2 Connect®, also available from IBM.

[0065] Referring now to FIG. 3, the query visualization module 50 preferably includes a plurality of modules containing executable and operational data suitable for operation within the memory devices 18 of FIG. 1. Of course, the memory devices 18 in which the modules of the present invention are located may also be distributed across both local and remote computer stations 12. Likewise, two or more illustrated modules may be integrated into a single module, or the function of a single module could be performed by a group of modules, without departing from the scope of the invention.

[0066] The principal components of the query visualization module 50 include a report creator 60, a graph generator 62, and a parameter browser 64. The above-described modules are, in one embodiment, intended to help the user to better understand the query explain data, subsystem parameters, and the like, in a variety of ways.

[0067] For example, the report creator 60 in one embodiment selectively prepares a report of the access path data, statement cost data, and object statistics data in an easily understood, text-based format. The user may be provided with the option of selecting one or more query statements, as well as subsets of the query explain data for the selected query statements to include in the report. The report provides the user with the requested query explain data in a centralized and readily understood format, allowing the user to efficiently analyze and improve SQL query performance. The report creator 60 is more fully described in co-pending U.S. application Ser. No. 09/482,595, filed Jan. 13, 2000, using Express Mail Label EL409135377US, for “System and Method for Selectively Preparing Customized Reports of Query Explain Data,” which is commonly assigned and is incorporated herein by reference.

[0068] A second principal component of the query visualization module 50 in one embodiment is the graph generator 62, which prepares a graphical representation of the access path of a query statement. The graph generator 62 is more fully described in co-pending application Ser. No. 08/949,636, filed Oct. 14, 1997, for “Interpreting Data Using a Graphical User Interface,” which is incorporated herein by reference. FIG. 4 illustrates an exemplary SQL query statement, processed by an RDBMS 54 to generate access path data and stored within a plan table 74 according to one embodiment of the present invention.

[0069] As shown in FIG. 5, the graph generator 62 in one embodiment uses the plan table 74 to generate a graphical representation of the access path. Preferably, access path steps of an SQL statement are graphically represented as nodes within a tree-like structure. Tables, indexes, and operations are graphically represented with unique symbols that indicate the item being represented. For example, rectangles represent tables, triangles represent indexes, and octagons represent operations such as table space scans, index scans, joins, etc. The graphical representation shows the relationship between the database objects and the operations. When the user selects a node of the graphical representation, detailed information related to the selected node is displayed on the right side of the display.

[0070] Referring again to FIG. 3, a third principal component of the query visualization module 50 in one embodiment is the parameter browser 64. Preferably, the parameter browser 64 allows a user to selectively view the subsystem parameters, for example, DSNZPARM and DSNHDECP values, used by a subsystem, as well as the install panel names and fields. Access to subsystem parameters is useful in analyzing the performance of query statements. Like the graph generator 62, the parameter browser 64 is more fully described in co-pending application Ser. No. 08/949,636, filed Oct. 14, 1997, for “Interpreting Data Using a Graphical User Interface.”

[0071] The report creator 60, graph generator 62, and parameter browser 64 are each preferably coupled to a graphical user interface (GUI) module 66. Preferably, the GUI module 66 is operably coupled to the input and output devices 26, 28 to allow the user to interact with the report creator 60, graph generator 62, and parameter browser 64.

[0072] The query visualization module 50 in the depicted embodiment also includes an explain module 68, which invokes a corresponding explain function 70 in the RDBMS 54. When invoked, the explain function 70 causes the RDBMS 54 to generate function data, access path data, and the statement cost data (referred to herein as “explain data”) for a specified query statement.

[0073] Explainable query statements include SELECT (except for SELECT INTO) and INSERT statements, and the searched form of UPDATE and DELETE statements. The explain module 68 is used to invoke an explain function 70 in which the RDBMS 54 immediately generates explain data for a specific SQL statement. This feature is useful for interactively testing specified SQL statements. Alternatively, the RDBMS 54 generates the explain data at bind time in the context of an application or package upon encountering an EXPLAIN(YES) open of the BIND command.

[0074] In one embodiment, the query visualization module 50 includes a plurality of querying modules for querying various tables in the database 52. For example, a plan table querying module 72 queries a plan table 74 to obtain access path data. Likewise, a statement table querying module 76 queries a statement table 78 to obtain statement cost data. A function table querying module 80 queries a function table 82 to obtain data concerning user-defined functions. The plan table 74, the statement table 78, and the function table 82 are collectively referred to herein as “explain tables.” Finally, a catalog querying module 86 queries the RDBMS catalog 88 to obtain object statistics for one or more database objects contained within in a plurality of user tables 90.

[0075] Although the querying function is implemented herein by four separate modules, those skilled in the art will recognize that the described functionality may be implemented by fewer modules. Additionally, in one embodiment, the above-described modules use the interface module 56 when communicating with the RDBMS 54 and database 52.

[0076] Preferably, the query visualization module 50 also includes a filter module 92, which is described in greater detail below. In one embodiment, the filter module 92 allows a user to filter a list of explainable query statements according to various user-selected criteria, including statement costs, references to particular database objects, and the inclusion of particular steps in the access paths of the statements. Moreover, in one embodiment, the user may assign a name to a set of filtering criteria and save the named set in a filter storage 94. This allows the user to easily retrieve a specified set of filtering criteria such that only the explainable query statements satisfying the criteria are listed.

[0077] In one embodiment, the filter module 92 also allows a user to filter a set of plans and packages according to various identification data, such as plan and package names, collection identifiers, version identifiers, and the like. The filter module 92 is described in greater detail below with respect to FIG. 6.

[0078] The query visualization module 50 also preferably includes a cache module 96, which caches portions of the above-described tables in a cache storage 98. For example, when the plan table querying module 72 retrieves access path data from the plan table 74, the access path data is preferably stored, and future accesses to the same data will be retrieved from the cache storage 98. Various methods may be employed for managing data in the cache storage 98, such as automatically deleting a percentage of the cached data when the amount of the data exceeds a pre-defined threshold.

[0079] Referring now to FIG. 6, the filter module 92 preferably includes a plurality of modules containing executable and operational data suitable for operation within the memory devices 18 of FIG. 1. In one embodiment, the memory devices 18 in which the modules of the present invention are located may also be distributed across both local and remote computer stations 12. Likewise, two or more illustrated modules may be integrated into a single module without departing from the scope of the invention, and additional modules could be utilized to perform the same functions.

[0080] In one embodiment, the filter module 92 includes a statement selection module 100, which allows a user to select an initial set of query statements to be filtered. Briefly, a package comprises one or more query statements from a common application. Likewise, a plan is an assemblage of query statements, but may correspond to more than one application. The user may preferably select one or more plans or packages to create the initial set of query statements.

[0081] As described above, the initial set of query statements may be very large, particularly for a company-wide database. Consequently, it may be difficult for a user to locate a particular query statement, when, for example, the user wishes to select the statement for graphing, for SQL text display, for generation of a report containing the statement, or the like.

[0082] To alleviate this problem, the filter module 92 preferably includes a filter generation module 102. In one embodiment, the filter generation module 102 allows the user to create one or more filters for selectively reducing a list of query statements based on subsets of the query explain data previously generated for the statements by the RDBMS 54. In the depicted embodiments, each filter includes user-specified filtering criteria that are applied to the corresponding subset of the query explain data to selectively exclude from the initial set the query statements not satisfying the filtering criteria.

[0083] The filter generation module 102 preferably includes an object filter generator 104 for assisting a user in generating a user-defined object filter 106. In one embodiment, the object filter 106 includes filtering criteria for selectively excluding query statements from the initial set based on whether the query statements either reference a specified database object (such as a table, index, or the like) or reference an object created by a specified person.

[0084] Similarly, the filter generation module 102 also includes, in one embodiment, a cost filter generator 108 for assisting a user in generating a user-defined cost filter 110. The cost filter 110 preferably includes filtering criteria for selectively excluding query statements from the initial set based on how the statement cost data for each query statement compares with a specified threshold cost.

[0085] Likewise, the filter generation module 102 preferably includes an access path filter generator 112 for assisting a user in generating a user-defined access path filter 114. The access path filter 114 preferably includes filtering criteria for selectively excluding query statements from the initial set based on whether the statements include specified execution steps in their access paths. Other types of filter generators may also be added as needed.

[0086] To apply the foregoing filters, the filter module 92 preferably includes a statement filtering module 116. In one embodiment, the statement filtering module 116 applies the filtering criteria of the filters to subsets of the query explain data for the initial set of query statements.

[0087] In the depicted embodiment, the statement filtering module 116 includes an object criteria module 118 for applying the filtering criteria of an object filter 106. Preferably, the object criteria module 118 communicates with the plan table querying module 72 to obtain the access path data for each query statement from the plan table 74. From the access path data, the object criteria module 118 may determine whether each statement references a database object specified in the filtering criteria, such as a table, index, or the like.

[0088] Additionally, the statement filtering module 116 preferably includes a cost criteria module 120 for applying the filtering criteria of a cost filter 110. In one embodiment, the cost criteria module 120 communicates with the statement table querying module 76 to obtain the statement cost data for each query statement from the statement table 78. From the statement cost data, the cost criteria module 120 may determine whether each statement satisfies the filtering criteria of the cost filter 110.

[0089] In one embodiment, the statement filtering module 116 also includes an access path criteria module 122 for applying the filtering criteria of an access path filter 114. Preferably, the access path criteria module 122 communicates with the plan table querying module 72 to obtain the access path data for each query statement from the plan table 74. From the access path data, the access path criteria module 122 may determine whether the access path of each statement includes execution steps specified in the filtering criteria of the access path filter 114.

[0090] In one embodiment, the filter module 92 also includes a statement listing module 124. The statement listing module 124 preferably displays the filtered set of query statements to the user, additionally allowing the user to select one or more of the query statements for purposes of modifying the statements, including the statements in a report, explaining the query statements, or the like.

[0091] In one embodiment, the filter module 92 also includes a filter storage module 126. After the user has generated one or more filters, the filter storage module 126 preferably allows the user to store the filters as a set 130 in the filter storage 94. In one embodiment, the user may assign a name to a set 130 of filters in the filter storage 94. Later, the user may retrieve and use the stored filters without having to recreate them, as described in greater detail below.

[0092] FIG. 7 is an illustration of the data flow within the filter module 92 according to one embodiment of the invention. As described above, the RDBMS 54 generates query explain data for a plurality of query statements. Using the statement selection module 100, the user may select one or more plans and/or packages, which define an initial set of query statements to be filtered.

[0093] A user may, using the filter generation module 102, generate one or more filters, such as an object filter 106, a cost filter 110, and an access path filter 114. Optionally, the filters may be stored in the filter storage 94 by means of the filter storage module 126. Later, a user may retrieve the filters from the filter storage 94. In one embodiment, the filters may include the user's selection of the plans and/or packages.

[0094] In one embodiment, the statement filtering module 116 accepts as input the initial set of query statements, the query explain data for the statements, and one or more user-defined filters, either generated by the filter generation module 102 or retrieved from the filter storage 94. Using the techniques described above, the statement filtering module 116 applies the filtering criteria of each user-defined filter to selectively exclude from the initial set the query statements not satisfying the filtering criteria. Preferably, the statement filtering module 116 provides the filtered query statements to the statement listing module 124, which displays a list of the filtered query statements to the user.

[0095] Referring now to FIG. 8, a schematic flow chart illustrates a method of filtering query statements according to one embodiment of the invention. The method begins by determining 132 whether to create new filters or to retrieve a set 130 of stored filters. In the depicted embodiment, a user may create new filters by selecting a “new” button 146 or a similar control. Alternatively, a user may retrieve a set 130 of filters from the filter storage 94 by selecting the name of a set 130 from a pull-down menu 148 (of FIG. 10), which lists the names of the filter sets 130 stored in the filter storage 94.

[0096] If, in step 132 of FIG. 8, the user elects to create new filters, the method continues by receiving 134 a user's selection of one or more plans and/or packages. As noted above, a package corresponds to one or more query statements in a common application. Likewise, a plan is an assemblage of query statements, but may correspond to more than one application.

[0097] FIGS. 9 and 10 are illustrations of interactive displays, generated by the statement selection module 100 for receiving a user's selection of one or more plans and/or packages. FIG. 9 illustrates an interactive display for selecting packages according to one embodiment of the invention. Preferably, the statement selection module 100 provides an “add” button 134, or a similar control, through which the user may select to add one or more packages to a selected package list 136. In one embodiment, the user may add packages by name or by performing a search of the packages based on such criteria as package owner, package name, collection ID, and version. This process is described in greater detail below with respect to FIGS. 19–28.

[0098] In one embodiment, the selection module 100 employs a control such as a “change” button 138 for changing one or more of the selected packages in the selected package list 136. In addition, the selection module 100 may include a “remove” button 140 or similar control for removing one or more of the selected packages.

[0099] FIG. 10 illustrates an interactive display for selecting plans according to one embodiment of the invention. Preferably, the selection module 100 provides a control such as an “add” button 134 for adding one or more plans to a selected plan list 142. As noted above, a plan is similar to a package but may include query statements for more than one application.

[0100] After the user's selection of plans and/or packages is received, the method continues by receiving 150 user-specified filtering criteria, if any, for an object filter 106. It should be recognized that a user is not required in one embodiment to create a complete set of filters or, indeed, any filters. Thus, if the user selects an “OK” button 152 (of FIG. 10) or the like without creating any filters, the statement listing module 124 (of FIG. 7) preferably lists all of the query statements in the selected plans and/or packages. FIG. 11 illustrates an unfiltered listing of the plurality of query statements for the selected plans of FIG. 10. If the user does not define an object filter 106, the method simply proceeds with step 168.

[0101] FIG. 12 is an illustration of an interactive display provided by the object filter generator 104 for receiving user-specified filtering criteria of an object filter 106. In the depicted embodiment, the interactive display for the object filter generator 104 is shared with the cost filter generator 108. However, in alternative embodiments, separate interactive displays are provided. Preferably, the interactive display for the cost filter generator 108 is selectively displayed by means of a suitable control such as a “Cost/object filters” tab 153.

[0102] In one embodiment, the interactive display includes a set of customizable relational expressions 154 for specifying filtering criteria of an object filter 106. Each relational expression preferably represents an individual filtering criterion that is applied to the object reference data for each query statement. Although the following description illustrates a preferred user interface, a variety of other user interfaces may be employed within the scope of the invention.

[0103] In the depicted embodiment, each relational expression 154 includes three fields: a name field 156, an operator field 158, and a value field 160. The name field 156 preferably represents a type of database object referenced by a query statement, such as a table or an index. In addition, the name field 156 may represent an object creator, i.e. a person who created one or more referenced objects.

[0104] The operator field 158 is preferably selectable from a number of relational operators, such as “equal to,” “not equal to,” “greater than,” “less than,” “in” or “like.” In one embodiment, the operator field 158 may be selected using a pull down menu or similar mechanism.

[0105] Preferably, the value field 160 stores the name of a specific object or a name of a specific object creator. In one embodiment, the value field 160 is specified using a text entry field or like mechanism.

[0106] For example, as shown in FIG. 12, the expression 154A includes “Table names” in the name field 156, “=” in the operator field 158, and “TBDB0101” in the value field 160. Thus, in one embodiment, the resulting relational expression, “Table names=TBDB0101,” specifies that only the query statements referencing the “TBDB0101” table will pass through the object filter 106; all other statements will be excluded. For example, as displayed by the statement listing module 124 in FIG. 13, each of the filtered statements references the “TBDB0101” table.

[0107] In one embodiment, a filter may include a plurality of relational expressions combined with boolean operators. In the illustrated embodiment of FIG. 12, the relational expressions 154 may be combined using a logical (inclusive) “OR” by selecting the “Meet any filter condition” option 162. Alternatively, the relational expressions 154 may be combined using a logical “AND” by selecting the “Meet all filter conditions” option 164. Other boolean operations may also be provided, such as NOT, exclusive-OR, and the like.

[0108] Referring again to FIG. 8, after the filtering criteria for the object filter 106 is received, the method continues by receiving 168 filtering criteria, if any, for a cost filter 110. FIG. 14 is an illustration of an interactive display provided by the cost filter generator 108 for receiving user-specified filtering criteria of a cost filter 110. Preferably, the interactive display for the cost filter generator 108 may be selectively displayed by means of the “Cost/object filters” tab 153.

[0109] In one embodiment, the interactive display includes a set of customizable relational expressions 170 for specifying the filtering criteria for a cost filter 110. Each relational expression 170 preferably represents an individual filtering criterion that is applied to the statement cost data for each query statement.

[0110] The relational expressions 170 for the cost filter 110 are similar in form to those of the object filter 106. However, the name field 156 preferably represents cost units for measuring the cost of executing the query statement. In one embodiment, the cost units are either milliseconds or service units. Service units are a measure of a computer's processing resources needed to execute the query statement. The value field 160 preferably stores a threshold cost to be compared with the statement cost of each query statement.

[0111] For example, as shown in FIG. 14, the expression 170A includes “Cost in milliseconds” in the name field 156, “>” in the operator field 158, and “1000” in the value field 160. Thus, the relational expression, “Cost in milliseconds>1000” specifies that only those query statements with an estimated cost greater than 1000 milliseconds will pass through the cost filter 110; all other statements will be excluded. For example, as displayed by the statement listing module 124 in FIG. 15, each of the filtered statements has a cost greater than 1000 milliseconds.

[0112] Referring again to FIG. 8, after the filtering criteria for the cost filter 110 is received, the method continues by receiving 172 filtering criteria, if any, for an access path filter 114. FIG. 16 is an illustration of an interactive display provided by the access path filter generator 112 for receiving user-specified filtering criteria of an access path filter 114. Preferably, the interactive display for the access path filter generator 112 is selectively displayed by means of an “Access path filters” tab 174.

[0113] In one embodiment, the access path filter generator 112 displays a plurality of customizable boolean expressions 176. Preferably, each of the boolean expressions 176 represents access path steps. For example, as depicted in FIG. 16, the access path steps may include sorts, table space scans, and the like.

[0114] In one embodiment, each of the boolean expressions 176 include a name field 156 and a value field 160. The name field 156 preferably includes a type of access path step. The value field 160 is preferably a truth value, i.e. true or false. In one embodiment, the value field 160 is selectively changeable by clicking on the associated field with a mouse or other pointing device.

[0115] Query statements are preferably allowed to pass through the access path filter 114 if the statements include any of the access path steps having a value field 160 of true. In one embodiment, the individual boolean expressions 176 are combined using a logical OR. In alternative embodiments, the expressions 176 may be combined using other boolean operators, such as AND.

[0116] In one embodiment, the access path filter generator 112 provides a “Set to all true” button 177 for conveniently setting all of the value fields 160 to true. Likewise, the access path filter generator 112 provides a “Set to all false” button 178 for setting all of the value fields 160 to false.

[0117] As illustrated in FIG. 17, a user may desire to see a list of query statements including sorts in the statements' access paths. This may be accomplished in one embodiment by setting all of the value fields 160 to false using the “Set all to false” button 178 and changing the “Sorts” value field 160 to true. Accordingly, when the access path filter 114 is applied, only the statements having sorts are displayed. For example, as displayed by the statement listing module 124 in FIG. 18, all of the listed query statements include sorts in the statements' access paths.

[0118] After the filtering criteria for the access path filter 114 is received, the method continues by creating 180 the filters corresponding to the user-defined filtering criteria. In one embodiment, the filters are data structures including representations of the relational expressions 154, 170 and boolean expressions 176. In addition, each filter may include, in one embodiment, the selection of plans and/or packages made by the user in step 144. The type of data structure is not crucial to the invention. However, it is preferred that the statement filtering module 116 is capable of interpreting the data structures to obtain the relational expressions 154, 170 and boolean expressions 176, for the filtering criteria, as well as the plan/package selection.

[0119] After the filters are created, the method continues by determining 182 whether to save the user-specified filter(s) as a set 130 in the filter storage 94. The user may elect (or may have previously elected) to give a name to a set 130 of filters and store the set 130 by means of the “Save” or “Save as” buttons 184, 186.

[0120] If the user selects one of the foregoing buttons 184, 186, the method continues with step 188 by receiving the name of the filter set 130 (if not previously provided) and storing the filter set 130 in the filter storage 94 using the specified name; otherwise, the method continues with step 192.

[0121] As previously noted in step 144, a user may elect to retrieve a stored filter set 130, rather than creating new filters by means of the filter generation module 102. This is accomplished, in one embodiment, by selecting a set 130 from a pull down menu 148, which lists the names of the filter sets 130 stored in the filter storage 94. Thus, if the user elects to retrieve a stored set in step 144, the method continues by receiving 190 a name of a user-specified filter set 130 and retrieving the set 130 from the filter storage 94.

[0122] In one embodiment, the user may elect to modify the retrieved filter 190 using the techniques described in steps 150, 168, and 172. As noted above, the filters may include, in one embodiment, the selection of plans and/or packages made by the user in step 144. However, in an alternative embodiment, the user may proceed to specify a new or different set of plans and/or packages as described in step 132.

[0123] After the completion of either steps 182, 188, or step 190, one or more filters will have been generated and/or retrieved. Preferably, the user requests a list of filtered statements by selecting the “OK” button 152. Thereafter, the method continues by applying 192 the object filter 106, if any, to the query statements. After the object filter 106 is applied, the method continues by applying 194 the cost and access path filters 110, 114 if any were defined.

[0124] When the filtering process is complete, the statement listing module 124 displays a list of the filtered query statements to the user. As shown in FIG. 13, the statement listing may include such information as a statement number, a package name, a cost in milliseconds, a cost in service units, the SQL text of the query statement, a collection ID, a plan name, and an owner name.

[0125] Additionally, in one embodiment, the statement listing module 124 allows the user to select a filtered statement for graphing, for SQL text display, for generation of a report containing the statement, or the like. FIGS. 13, 15, and 18 illustrate an interactive display generated by the statement listing module 124 for listing the filtered query statements according to one embodiment of the invention.

[0126] Referring now to FIG. 19, the statement selection module 100 preferably includes a plurality of modules containing executable and operational data suitable for operation within the memory devices 18 of FIG. 1. Of course, the memory devices 18 in which the modules of the present invention are located may also be distributed across both local and remote computer stations 12. Likewise, two or more illustrated modules may be integrated into a single module, or the functionality of a single module may be implemented using a plurality of modules, without departing from the scope of the invention.

[0127] In one embodiment, the statement selection module 100 includes a package selection module 200 and a plan selection module 202 for assisting a user in selecting one or more packages and plans, respectively. As previously explained, packages and plans correspond to groups of query statements associated with application programs that query the database 52. Thus, by selectively designating one or more packages and plans, the user may define the initial set of query statements to be received as input to the statement filtering module 116 of FIG. 6.

[0128] In typical large-scale database systems, the number of packages and plans is correspondingly large, and finding a particular package or plan can be difficult. Accordingly, both the package selection module 200 and the plan selection module 202 preferably include a number of filtering modules for selectively filtering an initial set of packages and plans according to user-specified filtering criteria.

[0129] In one embodiment, the filtering criteria for each filtering module is directed to identification data stored in the catalog 88 for each package and plan. Thus, in one embodiment, the catalog querying module 86 is used to access the plan/package identification data.

[0130] For example, in one embodiment, the package selection module 200 includes a package owner filtering module 208 for filtering a set of packages according to package owner data for each package. Preferably, each package identifies an owner, i.e. a user ID of the creator or creators of the package. In one embodiment, the package owner filtering module 208 receives from a user an identification of one or more owners and filters the packages according to whether the packages were created by the specified owners.

[0131] Additionally, the package selection module 200 includes, in one embodiment, a package name filtering module 210 for filtering a set of packages according to package name data for each package. Preferably, each package has a unique name for identifying the package. In one embodiment, the package name filtering module 210 receives from a user the names of one or more packages and filters the packages according to whether the packages have the specified names.

[0132] In one embodiment, the package selection module 200 also includes a collection ID filtering module 212 for filtering a set of packages according to collection ID's. A collection ID is an identification of a group of packages. In one embodiment, the collection ID filtering module 212 receives from a user the ID's of one or more collections, and filters the packages according to whether the packages are members of the specified collections.

[0133] Additionally, in one embodiment, the package selection module 200 includes a version filtering module 214 for filtering packages according to version data. In one embodiment, each package has a version identifier for identifying the version of the package. Preferably, the version filtering module 214 receives from a user the identifiers of one or more versions and filters the packages according to whether the packages include the specified version identifiers.

[0134] Preferably, the package selection module 200 also includes a package listing module 216 for displaying to a user a selectable list of the filtered packages. In one embodiment, the user may select one or more of the packages in order to select the initial set of query statements.

[0135] In one embodiment, the plan selection module 202 includes a similar set of filtering modules. For example, the plan selection module 202 includes a plan owner filtering module 218 for filtering plans according to plan owner data for each plan. Preferably, each plan identifies an owner, i.e. the user ID of the creator or creators of the plan. In one embodiment, the filtering module 218 receives from a user an identification of one or more owners and filters the plans according to whether the plans were created by the specified owners.

[0136] Additionally, the plan selection module 202 includes, in one embodiment, a plan name filtering module 220 for filtering plans according to plan name data for each plan. Preferably, each plan has a unique name for identifying the plan. In one embodiment, the plan name filtering module 220 receives from a user one or more names of plans and filters the plans according to whether the plans have the specified names.

[0137] Preferably, the plan selection module 202 also includes a database request module (DBRM) name filtering module 222 for filtering plans according to DBRM name data associated with each plan. In one embodiment, the DBRM name filtering module 222 receives from a user one or more DBRM names and filters the plans according to the specified DBRM names.

[0138] Preferably, the plan selection module 202 also includes a plan listing module 224 for displaying to a user a selectable list of the filtered plans. In one embodiment, the user may select one or more of the plans in order to select the initial set of query statements.

[0139] Referring now to FIG. 20, a schematic flow chart illustrates a method for filtering a set of plans and packages according to one embodiment of the invention. The method begins by receiving 230 user-specified package filtering criteria for filtering an initial set of packages.

[0140] FIG. 21 illustrates an interactive display generated by the statement selection module 100, and more particularly, by the package selection module 200, according to one embodiment of the invention. As previously described, the statement selection module 100 preferably generates an “add” button 134 or other suitable control, which the user may select to add one or more packages to the selected package list 136.

[0141] As illustrated in FIG. 22, when the “add” button 134 is selected, a set of customizable package filtering criteria is preferably displayed. In one embodiment, the package filtering criteria include a number of relational expressions 250. Preferably, a relational expression 250 is provided for each filtering module, including the package owner filtering module 208, the package name filtering module 210, the collection ID filtering module 212, and the version filtering module 214.

[0142] For example, in the depicted embodiment, the package owner filtering module 208 corresponds to the relational expression 250A. Preferably, the expression 250A has the form:

- [0143] “Package owner:” OPERATOR VALUE
  where the OPERATOR field is selectable from a number of relational operators, such as “equal to,” “not equal to,” “greater than,” “less than,” “in,” or “like,” and the VALUE field stores the identifiers of one or more package owners. In one embodiment, the OPERATOR field may be selected using a pull down menu or the like, while the VALUE field may be supplied using a text entry box or other suitable mechanism.

[0144] The completed expression 250A, i.e. “Package owner: IN ADMF001, USER001, SYSADM,” is preferably interpreted by the package owner filtering module 208 as an instruction to provide to the package listing module 216 only those packages owned by “ADMF001,” “USER001,” and “SYSADM.”

[0145] The user may similarly select filters corresponding to package names (expression 250B), collection identifiers (expression 250C), and versions (expression 250D). In one embodiment, the package name filtering module 210 corresponds to the relational expression 250B, which has a similar format to expression 250A. Likewise, the collection ID filtering module 212 corresponds to the relational expression 250C, and the version filtering module 214 corresponds to the relational expression 250D.

[0146] Referring to FIG. 20, after the package filtering criteria are received, the method continues by applying 232 the package filtering criteria to an initial set of packages. In one embodiment, the initial set may include all of the packages querying the database 52 or any user-defined subset of the packages. In one embodiment, the user may initiate the filtering process by selecting an “OK” button 152 (of FIG. 22) or the like, which will cause the filtering modules having defined relational expressions 250 to apply the filtering criteria to the initial set of packages.

[0147] Thereafter, the method continues by displaying 234 a filtered list of packages to the user. FIG. 23 illustrates the filtered list of selectable packages generated in response to the expression 250A of FIG. 22. As provided in the filtering criteria, each of the listed packages is owned by “ADMF001,” “USER001,” or “SYSADM.”

[0148] After the filtered list is displayed, the method continues by receiving 236 a user's selection of one or more of the packages. In one embodiment, the user may select the desired packages by highlighting individual packages with a mouse or other pointing device. After the one or more packages are highlighted, the user may confirm his or her selections by means of an “add” button 254 or other suitable control, which will preferably cause the selected packages (of FIG. 23) to be added to the selected package list 136, as shown in FIG. 4. These selected packages are then used to define the initial set of query statements as previously described.

[0149] The process of selecting plans is substantially similar to the above-described 8 process of selecting packages. In step 238, the method continues by receiving 238 user-specified plan filtering criteria to be used in filtering an initial set of plans. FIG. 25 illustrates an interactive display generated by the statement selection module 100 of FIG. 19, and more particularly, by the plan selection module 202, before any plans have been selected. As previously described, the statement selection module 100 provides in one embodiment an “add” button 134, which the user may select to add one or more plans to the selected plan list 142.

[0150] As illustrated in FIG. 26, when the “add” button 134 (of FIG. 25) is selected, a set of customizable plan filtering criteria is preferably displayed. In one embodiment, the plan filtering criteria includes a number of relational expressions 260 that are applied the list of plans in the database 52. Preferably, a relational expression 260 is provided for each filtering module, including the plan owner filtering module 218, the plan name filtering module 220, and the DBRM name filtering module 222.

[0151] For example, in the depicted embodiment, the plan owner filtering module 218 corresponds to the relational expression 260A. Preferably, the expression 260A has the form:

- [0152] “Plan owner:” OPERATOR VALUE
  where the OPERATOR field is selectable from a number of relational operators, such as “equal to,” “not equal to,” “greater than,” “less than,” “in,” or “like,” and the VALUE field stores the identifiers of one or more plan owners. In one embodiment, the OPERATOR field may be selected using a pull down menu or the like, while the VALUE field may be supplied using a text entry box or other suitable mechanism.

[0153] The completed expression 260A, i.e. “Plan owner: =ADMF001,” is preferably interpreted by the plan owner filtering module 218 as an instruction to provide to the plan listing module 224 only those plans owned by “ADMF001.”

[0154] Similarly, a user may further refine the selected list of plans by selecting filters corresponding to plan names (expression 260B) and DBRM names (expression 260C). In the depicted embodiment, the plan name filtering module 220 corresponds to the relational expression 260B and operates in substantially the same way as the plan owner filtering module 218. In other words, the OPERATOR field is selectable as described above, and the VALUE field stores the names of one or more plans. Likewise, the DBRM name filtering module 222 corresponds to the relational expression 260C.

[0155] For example, the completed expression 260C, i.e. “DBRM name: LIKE PROG %,” is interpreted by the DBRM name filtering module 222 as an instruction to provide to the plan listing module 224 only those plans having a DBRM name such as PROG1, PROG2, PROG3, etc. The LIKE operator permits wildcards, such as “%,” which can be used to represent any number of alphanumeric characters.

[0156] After the plan filtering criteria are received, the method of FIG. 20 continues by applying 240 the plan filtering criteria to an initial set of plans as described above. In one embodiment, the initial set may include all of the plans that query the database 52 or any user-defined subset of the plans. In one embodiment, the user may signal the filtering modules to apply the filtering criteria by selecting an “OK” button 252 (of FIG. 26) or similar control.

[0157] Once the filtering criteria of the filtering modules are applied, the method continues by displaying 242 a filtered list of plans to the user. FIG. 27 illustrates a filtered list of selectable plans generated in response to the expressions 260A and 260C of FIG. 26. As provided in the filtering criteria, each of the listed plans is owned by “ADMF001” and has a DBRM name of the form “PROG %.”

[0158] After the filtered list is displayed, the method continues by receiving 244 a user's selection of one or more of the plans. In one embodiment, the user may select the plans by highlighting individual plans with a mouse or other pointing device. After the one or more plans are highlighted, the user may confirm his or her selections by means of an “add” button 262 or other suitable control, which causes the selected plans to be added to the selected plan list 142 as shown in FIG. 28.

[0159] The present invention may be embodied in other specific forms without departing from its scope or essential characteristics. The described embodiments are to be considered in all respects only as illustrative and not restrictive. The scope of the invention is, therefore, indicated by the appended claims rather than by the foregoing description. All changes which come within the meaning and range of equivalency of the claims are to be embraced within their scope.

Claims

1. An apparatus for filtering a plurality of groups of query statements according to identification data associated therewith, each group corresponding to one or more application programs posing queries to a database, the apparatus comprising:

a memory device having thereon modules of code for execution by a processor, the modules comprising:

at least one filtering module configured to receive one or more user-specified filtering criteria directed to a subset of the identification data and apply the user-specified filtering criteria to selectively exclude those of the plurality of groups not satisfying the user-specified filtering criteria.

2. The apparatus of claim 1, further comprising:

a group listing module configured to display to a user those of the plurality of groups not excluded by the at least one filtering module.

3. The apparatus of claim 2, further comprising:

a group selection module configured to receive a user selection of one or more of the groups displayed by the group listing module for selectively filtering the query statements associated with the selected groups according to user-defined filters of query explain data.

4. The apparatus of claim 1, wherein the identification data comprises data selected from the group consisting of owner data, name data, collection ID data, and version data.

5. The apparatus of claim 1, wherein at least one group comprises a package, each package corresponding to one application program posing queries to the database.

6. The apparatus of claim 5, wherein the identification data comprises an owner of each package, the at least one filtering module comprising:

a package owner filtering module configured to filter a plurality of packages according to at least one package owner specified in the filtering criteria.

7. The apparatus of claim 5, wherein the identification data comprises a name of each package, the at least one filtering module comprising:

a package name filtering module configured to filter a plurality of packages according to at least one package name specified in the filtering criteria.

8. The apparatus of claim 5, wherein the identification data includes a collection ID to which at least one package belongs, the at least one filtering module comprising:

a collection ID filtering module configured to filter a plurality of packages according to at least one collection ID specified in the filtering criteria.

9. The apparatus of claim 5, wherein the identification data of the package includes a version of each package, the at least one filtering module comprising:

a version filtering module configured to filter a plurality of packages according to at least one package version specified in the filtering criteria.

10. The apparatus of claim 1, wherein at least one group comprises a plan, each plan corresponding to one or more application programs posing queries to the database.

11. The apparatus of claim 10, wherein the identification data includes an owner of each plan, the at least one filtering module comprising:

a plan owner filtering module configured to filter a plurality of plans according to at least one plan owner specified in the filtering criteria.

12. The apparatus of claim 10, wherein the identification data includes a name of each plan, the at least one filtering module comprising:

a plan name filtering module configured to filter a plurality of plans according to at least one plan name specified in the filtering criteria.

13. The apparatus of claim 10, wherein the identification data includes a database request module (DBRM) name for each plan, the at least one filtering module comprising:

a DBRM name filtering module configured to filter a plurality of plans according to at least one DBRM name specified in the filtering criteria.

14. A method for filtering a plurality of groups of query statements according to identification data associated therewith, each group corresponding to one or more application programs posing queries to a database, the method comprising:

receiving one or more user-specified filtering criteria directed to a subset of the identification data; and

applying the user-specified filtering criteria to selectively exclude those of the plurality of groups not satisfying the user-specified filtering criteria.

15. The method of claim 14, further comprising:

receiving a user selection of one or more of the groups not excluded by the filtering criteria for selectively filtering the query statements associated with the selected groups according to user-defined filters of query explain data.

16. The method of claim 14, wherein the identification data comprises data selected from the group consisting of owner data, name data, collection ID data, and version data.

17. The method of claim 14, further comprising:

obtaining the identification data for at least one group from a database catalog.

18. The method of claim 14, wherein at least one group comprises a package, each package corresponding to one application program posing queries to the database.

19. The method of claim 18, wherein the identification data comprises an owner of each package, the filtering step comprising:

filtering a plurality of packages according to at least one package owner specified in the filtering criteria.

20. The method of claim 18, wherein the identification data comprises a name of each package, the filtering step comprising:

filtering a plurality of packages according to at least one package name specified in the filtering criteria.

21. The method of claim 18, wherein the identification data includes a collection ID to which at least one package belongs, the filtering step comprising:

filtering a plurality of packages according to at least one collection ID specified in the filtering criteria.

22. The method of claim 18, wherein the package identification data includes a version of each package, the filtering step comprising:

filtering a plurality of packages according to at least one package version specified in the filtering criteria.

23. The method of claim 14, wherein at least one group comprises a plan, each plan corresponding to one or more application programs.

24. The method of claim 23, wherein the identification data includes an owner of each plan, the filtering step comprising:

filtering a plurality of plans according to at least one plan owner specified in the filtering criteria.

25. The method of claim 23, wherein the identification data includes a name of each plan, the filtering step comprising:

filtering a plurality of plans according to at least one plan name specified in the filtering criteria.

26. The method of claim 23, wherein the identification data includes a database request module (DBRM) name for each plan, the filtering step comprising:

filtering a plurality of plans according to at least one DBRM name specified in the filtering criteria.

27. The method of claim 14, further comprising:

displaying to a user those of the plurality of groups not excluded by the filtering criteria.

28. An article of manufacture comprising a program storage medium readable by a processor and embodying one or more instructions executable by the processor to perform a method for filtering a plurality of groups of query statements according to identification data associated therewith, each group corresponding to one or more application programs posing queries to a database, the method comprising:

receiving one or more user-specified filtering criteria directed to a subset of the identification data; and

applying the user-specified filtering criteria to selectively exclude those of the plurality of groups not satisfying the user-specified filtering criteria.

29. The article of manufacture of claim 28, the method further comprising:

30. The article of manufacture of claim 28, wherein the identification data comprises data selected from the group consisting of owner data, name data, collection ID data, and version data.

31. The article of manufacture of claim 28, the method further comprising:

obtaining the identification data for at least one group from a database catalog.

32. The article of manufacture of claim 28, wherein at least one group comprises a package, each package corresponding to one application program posing queries to the database.

33. The article of manufacture of claim 32, wherein the identification data comprises an owner of each package, the filtering step comprising:

filtering a plurality of packages according to at least one package owner specified in the filtering criteria.

34. The article of manufacture of claim 32, wherein the identification data comprises a name of each package, the filtering step comprising:

filtering a plurality of packages according to at least one package name specified in the filtering criteria.

35. The article of manufacture of claim 32, wherein the identification data includes a collection ID to which at least one package belongs, the filtering step comprising:

filtering a plurality of packages according to at least one collection ID specified in the filtering criteria.

36. The article of manufacture of claim 32, wherein the identification data of the package includes a version of each package, the filtering step comprising:

filtering a plurality of packages according to at least one package version specified in the filtering criteria.

37. The article of manufacture of claim 28, wherein at least one group comprises a plan, each plan corresponding to one or more application programs posing queries to the database.

38. The article of manufacture of claim 37, wherein the identification data includes an owner of each plan, the filtering step comprising:

filtering a plurality of plans according to at least one plan owner specified in the filtering criteria.

39. The article of manufacture of claim 37, wherein the identification data includes a name of each plan, the filtering step comprising:

filtering a plurality of plans according to at least one plan name specified in the filtering criteria.

40. The article of manufacture of claim 37, wherein the identification data includes a database request module (DBRM) name for each plan, the filtering step comprising:

filtering a plurality of plans according to at least one DBRM name specified in the filtering criteria.

41. The article of manufacture of claim 37, the method further comprising:

displaying to a user those of the plurality of groups not excluded by the filtering criteria.

Patent Citations (14)

Patent	Date	Inventor	Cited By
US5315504(A)	1994-05-01	Lemble	Applicant
US5367675(A)	1994-11-01	Cheng et al.	Applicant
US5455945(A)	1995-10-01	VanderDrift	Examiner
US5666528(A)	1997-09-01	Thai	Examiner
US5787411(A)	1998-07-01	Groff et al.	Applicant
US5809266(A)	1998-09-01	Touma et al.	Examiner
US5819251(A)	1998-10-01	Kremer et al.	Applicant
US5826076(A)	1998-10-01	Bradley et al.	Applicant
US5844554(A)	1998-12-01	Geller et al.	Applicant
US5884299(A)	1999-03-01	Ramesh et al.	Applicant
US5899997(A)	1999-05-01	Ellacott	Applicant
US5960428(A)	1999-09-01	Lindsay et al.	Examiner
US6115705(A)	2000-09-01	Larson	Examiner
US6539371(B1)	2003-03-01	Bleizeffer et al.	Examiner

Non-Patent Literature (9)

Lee et al., Outer Joins and Filters for Instantiating Objects from Relational Databases through Views, Knoweldge and Data Engineering, IEEE Transactions, vol. 6, isuue 1, pp. 108-119.Applicant
Method for Optimizing for N Rows in a Database Management System; IBM Technical Disclosure Bulletin; vol. 38 No. 12, Dec. 1995; p. 317.Applicant
Dynamic Ordering of Joined Rows Using Fields from Multiple Tables; IBM Technical Disclosure Bulletin; vol. 36 No. 11, Nov. 1993; pp. 363-366.Applicant
DB2 Cost Formula; IBM Technical Disclosure Bulletin; vol. 34 No. 12, May 1992; pp. 389-394.Applicant
Multiple Indexed Access Path in a Relational Database System; IBM Technical Disclosure Bulletin; vol. 32 No. 10B, Mar. 1990; pp. 388-392.Applicant
Access Path Selection in a Relational Database Management System; IBM Technical Disclosure Bulletin; vol. 22 No. 4, Sep. 1979; pp. 1657-1660.Applicant
James, RH, et al.; Portable Information Filtering System: Network Information Servers: Proceedings 7th International Conference on Computer Communications and Networks 1998; pp. 874-880.Applicant
Harder, T. et al.; Access Path Support for Referential Integrity in SQL2; VLDB Journal, vol. 5; No. 3, 1996; pp. 196-214.Applicant
Ubell, M. et al.; Embedding image Query Operations in an Object relational Database Management System; Proceedings of the SPIE—The International Society for Optical Engineering, vol. 2420, 1995; pp. 197-203.Applicant

Abstract