Capturing database system information

A system emulation tool and method provides a user interface having various options that are selectable by a user for exporting environment information from one or more target database systems. The user interface in one arrangement includes multiple screens that are invoked in response to user action. Exported data from the one or more target database systems is stored in corresponding files. The user interface of the system emulation tool also provides options to enable a user to import the environment information to a test system. In the test system, the environment information is used to create an environment that emulates the environment of the target database system.

BACKGROUND

A substantial portion of the life cycle of software development is devoted to testing. The purpose of software testing is to detect errors in programs and, in the absence of errors, gain confidence in the proper functionality of the programs. A basic premise of software testing is that programs are adequately covered once the test cycle is complete. Thus, test cases are properly selected so that some level of confidence is derived about the reliability of software from a sample set of test cases.

In testing software, particularly software in user systems that are relatively large, the test environment (often at the site of the software developer) is usually quite different from the actual operating environment. For example, a database system such as the TERADATA® database system from NCR Corporation is a multi-node, massively parallel processing system having tens or even hundreds of nodes. In addition to the complexity of such systems, the configurations and architectures of systems used by different users or customers usually differ.

The types of tests performed depend on the software applications being tested. For example, one of the goals of a database management system is to optimize the performance of queries that access data stored in the database. Given a target environment, a plan is developed for each query to achieve better performance, sometimes referred to as selecting an access plan (query plan, join plan, or strategy) with the lowest cost (e.g., response time). The response time is the amount of time it takes to complete the execution of the query on a given system. The number of alternative access plans for a query grows at least exponentially with the number of relations (or tables) participating in the query. A cost-based model can be used to compare different methods for doing a unit of work, with the most efficient method (or one of the more efficient methods) selected.

The performance of a query plan differs depending upon environmental factors relating to the hardware and software configuration of a target system (customer system). Differences in target systems usually cause the performance of query plans to differ significantly. One technique to emulate or simulate a target (customer) environment is by using expensive, custom hardware. However, such hardware-based test facilities are usually not cost-effective.

SUMMARY

In general, a method and apparatus is provided to select environment information to extract from a target database system. For example, a method includes presenting a user interface, receiving user selection through the user interface pertaining to environment information of a target database system to extract, and receiving the environment information extracted based on the user selection from the target database system.

DETAILED DESCRIPTION

In accordance with some embodiments, a module (referred to as a “system emulation tool”) enables flexible and convenient capture of environment information of a target database system. In one arrangement, the system emulation tool is executable in a system that is separate from both the target database system and a test system, or alternatively, the system emulation tool is executable in one of the target system and test system. By capturing environment information of the target database system and providing the information to the test system, target-level emulation is enabled in the test system to allow emulation of the target system for purposes of testing, debugging, or other analysis. The captured target environment information, stored in and/or mapped to appropriate tables, files, and other storage locations in the test system, is accessible by an optimizer program in the test system. In response to a test query, the optimizer program selects the lowest cost (or a lower cost) query plan in the target environment created by target-level emulation. By using target-level emulation to generate query plans and estimated performances of the query plans, queries are tuned for better performance, the impact of environment changes on queries is better modeled, and the source of problems in a database environment is determined more efficiently. This is performed in a test or analysis system that is located at a location remote from the target system. Further, the test system can be a much smaller and less sophisticated system than the target system, making testing more convenient and less expensive.

FIG. 1shows an example arrangement having several target database systems (14A,14B), a test system10, and an emulation client system20in which a system emulation tool22is executable. In one example, the target database system14A is located at a first customer site, while the target database system14B is located at a second customer site. Each target database system14includes database management software36that manages access of data in a respective database32. In accordance with some embodiments, the database32is distributed across plural nodes in each target database system14. Such a multi-node parallel processing system is referred to as a massively parallel processing (MPP) system. Alternatively, the target system14is a single-node system having plural processors (sometimes referred to as a symmetric processing system or SMP).

Each target database system14is associated with a system environment34, which is made up of system-specific information as well as database-level information of each target system. Thus, as used here, “environment information” of a target database system refers to the system-specific information, database-level information, or any portion of the system-specific or database-level information.

System-specific information includes such information as the number of nodes in the target system, the number of processors or central processing units (CPUs) per node, the number of virtual processors in each node, and other system information. Database-level information includes statistics, random samples of virtual processors, data manipulation language (DML) statements, data definition language (DDL) statements, and the actual data of the database itself.

Statistics include information on how data is structured in the database, the number of rows in a table, the data demographics of a table, and approximations of the distributions of particular data values in columns (or attributes) of a table (or relation). Random samples refer to samples captured from virtual processors, which are software modules that manage access of respective portions of a database. The random samples contain the data demographics of the database portions managed by the virtual processors. DDL statements affect the structure of database objects, and may include statements such as SQL (Structured Query Language) ALTER statements (to redefine or alter databases, tables, indexes, etc.), CREATE statements (to create databases, indexes, tables, etc.), and so forth. DML statements are statements that manipulate data, such as the COMMIT statement (to make permanent all changes since the beginning of a transaction), DELETE statement (to remove rows from a table), INSERT statement (to add new rows to a table), SELECT statement (to perform a query by selecting rows and columns from one or more tables), UPDATE statement (to change data in a table), and so forth.

The target database systems14A,14B are coupled to a data network12. The data network12may be a private network, or it may be a public network such as the Internet. Communications over the data network12can be according to various techniques and protocols.

The emulation client system20, which contains the system emulation tool22, is also connected to the data network12. Various data files24stored in the emulation client system20contain target-level emulation (TLE) data exported from respective target database systems14A,14B. The system emulation tool22is able to export TLE data from the target systems14A,14B over the data network12. Once the TLE data is stored in a data file24in the emulation client tool20, the system emulation tool22sends the TLE data to the test system10. This is referred to as importing the TLE data to the test system10. In one example, the test system10is a portable computer, a desktop computer, or any other system in which database management software16is executable.

The TLE data exported by the system emulation tool22includes environment information, such as cost-related information, statistics, random samples, DDL statements, DML statements, actual database data, and so forth, from the target systems14. The environment information is then imported to the test system10, with the environment information maintained as target-level emulation data40. With the ability to export TLE data from a target database system and to import the TLE data into the test system10, an accurate test environment (to emulate a target database system14) can be created in the test system10in which various tests are performed.

The test system10further includes an optimizer module18that selects a query plan from among several possible query plans for a given query that accesses data in a database17managed by the database management software16. For more accurate performance determinations, the optimizer module18uses the target-level emulation data40that has been imported from the emulation client system20to form a test environment in the test system10to emulate a desired target system. Running in the emulated environment, the optimizer module18identifies and selects the most efficient query plan (or one of the more efficient query plans) for a given query. Based on tests performed in the emulated environment, expected performance of various queries in the target database system can be determined.

For a given query, the optimizer module18identifies a query plan that has the lowest, or one of the lowest, response times. The response time is the amount of time it takes to complete the execution of the query on the given target database system. One technique of query optimization uses a cost model to estimate the response time of a given query plan and to search the space of query plans to return a plan with a low cost. In the cost-based optimization model, different methods for doing a unit of work is compared and the most efficient method is selected (the plan with the lowest cost). Because the number of alternatives may be quite large, especially in a parallel database system with a large number of nodes running a large relational database (with many tables), the query optimizer module18uses statistics and/or sampling techniques to reduce the search space in optimizing queries.

The test system10, in accordance with one embodiment, also includes a visual explain and compare module42that presents a query plan selected by the optimizer module18in a user interface of the test system10. In one embodiment, the visual explain and compare module42provides a graphical user interface in which steps of a query plan are displayed. Each step of the query plan is depicted as an icon, with the icons connected by lines to represent the flow of the steps in the query plan. The icons are designed to represent objects such as relational algebra (e.g., select, project, join); physical algebraic operators such as nested join, merge join, hash join, and so forth; tables; sorts; redistribution; views; duplications; and other objects.

Another feature of the visual explain and compare module42is the ability to graphically or visually compare plural query plans for a given query, with the plural query plans generated under different conditions. This provides the user with the ability to analyze differences between different query plans. Query plans of a given query can change in response to differences in various conditions, such as differences in database management systems, differences in platforms on which database management systems are executed, differences in software (e.g., applications, operating systems), different contents of tables, and so forth.

In addition to more accurate performance of the optimizer module18, target-level emulation also allows systems less sophisticated than the target parallel systems to accurately emulate query plan generation (and associated cost estimates). In fact, many test systems10are as simple as portable or notebook computers loaded with the appropriate software and data including the optimizer module18, the database management software16, and the database17. Consequently, by using the target-level emulation feature in accordance with some embodiments, a more convenient, flexible, and cost-effective test method and apparatus is provided in test systems to more accurately test query plan generation of database management software running in target database systems.

FIG. 2shows an example arrangement of a target multi-node parallel database system14. The target database system14has plural nodes10A,100B, and so forth, coupled by an interconnect network102. Each node may include a single central processing unit (CPU) or multiple CPUs.

In addition, as illustrated inFIG. 2, each node100includes multiple virtual processors (VPROCs)104, which include parsing engines (PEs) and access module processors (AMPs). More than one PE or AMP can run in each node. PEs handle access requests, which are in the form of SQL (Standard Query Language) statements, and generates executable steps that are performed in one or more AMPs. Each AMP is responsible for a logical disk space, referred to as a virtual disk (VDISK), which includes one or more storage devices106. The concept of VPROCs and VDISKs enable a larger amount of parallelism in the parallel system14.

In further embodiments, plural VPROCs, such as PEs and AMPs, are also executable in a single-node multiprocessing system, such as an SMP system. In the single-node system, plural VPROCs are distributed across plural CPUs. In another arrangement, plural VPROCs are run on a single-node system having a single CPU. As used here, a “parallel system” refers to a multi-node parallel processing system, a single-node multiprocessing system with multiple CPUs, or a single-node system with a single CPU running multiple virtual processors.

More generally, a database system includes one or more “access modules,” which can be implemented in software, hardware, or a combination thereof, to manage access of respective portions of a database.

As described above, the system emulation tool22(FIG. 1) in the emulation client system20is able to export TLE data from a target database system, and subsequently, to import the TLE data to the test system10. In accordance with some embodiments, the system emulation tool22presents a graphical user interface (GUI)28in a display26of the emulation client system20. The GUI28is able to display the various windows and screens provided by the system emulation tool22. The windows and screens allow a user at the emulation client system20to select various options associated with the export and import of TLE data.

As shown inFIG. 3, an example window200presented by the system emulation tool22includes several menu items202as well as icons204that are selectable by a user to perform various tasks. For example, by selecting a predetermined item in the file menu, a logon screen203can be invoked to enable the user to enter information to log on to a target database system14. A field206, referred to as an RDBMS Name field, in the screen203enables a user to enter the name of the target database system. A User Name field208allows the user to enter his or her user name for access to the target database system identified in the RDBMs Name field206. A Password field210enables entry of a password. Once a user is logged on, the user is able to perform either a TLE data export or import task using the interface provided by the system emulation tool22.

FIG. 4shows a screen220that presents various options selectable by a user in exporting data from a target database system14. The user can export TLE data either by a given query (by selecting a By Query element222) or by database (by selecting a By Database element224). Selection of the By Database element224causes environment information of the entire database (including all tables of the database) to be exported. Selection of the By Query element222causes only tables referenced by the given query (or queries) to be exported.

The types of environment information to be exported are also selectable by a user. One option is to select all types of environment information. Alternatively, individual types of environment information can be selected, such as by selecting an Object Definitions option228(to capture object definitions information including DDL statements that define tables in the target database), a Statistics option230(to capture statistics data), a Random AMP Sampling option232(to capture random samples of AMPs), and a Cost Parameters option234(to capture cost-related information).

For each table in the target database, the object definitions information includes SQL CREATE statements (issued to create tables), SQL ALTER statements (issued to redefine or alter the table), other data definition language (DDL) statements, or other object definitions. One example of a mechanism for extracting object definitions is described in U.S. patent application Ser. No. 09/892,837, entitled “Copying a Portion of a Database Structure Associated with a Query,” filed Jun. 27, 2001. However, other mechanisms can be used in other embodiments.

Random AMP samples refer to random samples of virtual processors (in this example AMPs) that capture the data demographics of data stored in storage modules managed by the respective AMPs. Example types of information included in the random AMP samples include a system name, a database name, a table name, time stamp, number of rows in the table, number of indexes on the table, minimum and maximum row lengths, and so forth.

Cost-related information includes the following types of information in one example: number of nodes, number of CPUs per node, number of AMPs per node, amount of memory per AMP, MIPS (millions of instructions per second) for each CPU, disk access speeds, network access speeds, and other system specific information.

The file name into which the exported data is to be stored is specified in a field236in the screen220inFIG. 4, and a directory in which the file is located is specified in a field238. Selection of the By Database element224causes an Export By Database screen280to be displayed (FIG. 8). Selection of the By Query element222causes an Export By Query screen240to be displayed (FIG. 5).

As shown inFIG. 5, the Export By Query screen,240contains an entry box242in which the query text is entered by the user in the screen240. Multiple queries can be entered into the box242by using an Add button244. Also, the database name is provided in a Database Name field246.

Selected queries are shown in a display box248. The screen240also provides other mechanisms for entering queries. A Browse QCD button250, when activated, allows queries from a query capture database (QCD) to be selected. The QCD contains a query list, which is basically a list of queries. Alternatively, by activating an Import SQL button252, files containing SQL text can be selected to enter queries.

The Export By Query screen240also includes various user-selectable export options254, which are similar to the export options226inFIG. 4. Note that one option that is not selectable in the screen220ofFIG. 4is an Execution Plan option256(shown as been grayed). When selected, the Execution Plan option256causes execution plans (or query plans) associated with selected queries to be exported with the TLE data. According to one embodiment, the execution plan is a series of linear/parallel steps of statements and not generated code. The exported execution plans are generated by the optimizer in the target database system for each given query.

The Export By Query screen240also includes an Estimate Time button259. When the Estimate Time button259is activated, a time value is displayed to indicate an estimated amount of time for the export operation. To export TLE data for the selected queries, the user activates an Export button258.

Selection of the Browse QCD button250in the Export By Query screen240causes a Query Capture Database screen270(FIG. 6) to be displayed. On the other hand, selection of the Import SQL button252causes an Open screen260(FIG. 7) to be displayed. As shown inFIG. 6, the Query Capture Database screen270includes a selection box272that displays the query list contained in the QCD. The query list contains the query text of plural queries along with the associated query identifiers. Selected query statements are displayed in a Query Text display box274.

As shown inFIG. 7, the Open screen260(displayed in response to selection of the Import SQL button252) displays files that contain queries. The user can select one of the files, with queries in the selected file imported into the Export By Query screen240inFIG. 5.

FIG. 8shows the Export By Database screen280that is displayed when the user selects the By Database element224inFIG. 4. InFIG. 8, the user can select one or more objects (tables or databases) for which TLE data is to be exported. A list of objects that can be selected is shown in a selection box282, and the selected objects are shown in a display box284.

The types of TLE data to be exported are selected by clicking on one or more of export options281. In addition, the Export By Database screen280contains an Estimate Time button286that when selected causes a time value to be displayed to indicate how much time is needed for the export operation. An Export button287when activated causes the export operation to proceed. Further, an Options button288provides further export options that are selectable by a user.

If the Options button288is selected, then an Export Options screen300(shown inFIG. 9) is displayed. The Export Options screen300includes a user-selectable element302associated with exporting statistics data, user-selectable elements304associated with exporting random AMP samples (RAS) data, and user-selectable elements306associated with exporting cost parameters. A RAS Tag field308allows the entry of the name by which RAS data is stored in the target database system. A Cost Tag field310allows the entry of the name by which optimizer cost parameters are stored in the target database system.

FIG. 10shows an Export Random AMP samples (RAS) screen320that is displayed in response to activation of a Select Tables button309in the screen300. The screen320lists tables that can be selected. RAS data is exported for the selected tables.

Three RAS options are selectable. A Capture From Source option322when selected causes random AMP samples to be exported from a RAS table (containing captured samples) in the target database system. A Capture From AMPs option324when selected causes samples to be captured directly from the AMPs in the target database system, rather than from the RAS table. Another option, a Set Samples option326, causes samples to be set (by the user) in the target database system, which are then exported.

FIG. 11shows an Export Cost Parameters screen330that is displayed in response to activation of a Select Rows button311in the Export Options screen (FIG. 9). A selection box332lists rows of a cost table that contain cost parameters. Each row contains cost parameters associated with different target database systems. Thus, for example, a customer may have multiple database systems (e.g., one for testing and one for production). The selected row in the selection box332selects the cost parameters to export for a desired target database system.

The Export Cost Parameters screen330contains a Capture From Source option334, a Capture Current Costs option336, and a Set Costs option338. The Capture From Source option334when selected causes cost parameters to be exported directly from the cost table in the target database system. The Capture From AMPs option336when selected causes cost parameters to be captured directly from the AMPs in the target database system, rather than from the cost table. The Set Samples option338when selected causes cost parameters to be set (by the user) in the target database system, which are then exported.

FIG. 12shows an Export Statistics screen340that displays a list of tables names. A user can select one or more of the listed tables for which statistics data is to be exported.

TLE data exported from various target database systems are stored in respective data files. Once exported and saved, the data file can later be opened by selecting a command in the file menu to cause an Open screen350(FIG. 13) to be displayed. A list of data files is displayed in the screen350, with one of the data files selected by a user to open. Once a file is selected for opening, a screen360is displayed (as shown inFIG. 14) that shows import options362selectable by a user. The types of TLE data to import to the test system10are selected using the import options362. The options362are the same options (Object Definitions option, Statistics option, Cost Parameters option, and Random AMP Samples option) that are selectable in the export screens described above. Information pertaining to the source of the TLE data is displayed in a display box364.

The screen360also includes various buttons, including a Preview button366and an Import button368. If the Preview button366is selected, then a Preview screen370is displayed (FIG. 15) in which the TLE data to be imported to the test system10can be previewed by a user. The Preview screen360includes a Preview Selection field372that enables a user to select the type of information to preview, including object definitions data, statistics data, and random AMP samples data. In the example ofFIG. 15, the Object Definitions option in the Preview Selection field372is selected. The object definitions data is displayed in a display box380. Note that in the example of the object definitions data includes a CREATE statement. The previewed information can be edited by activating an Edit button374. Thus, in the example ofFIG. 15, the CREATE statement displayed in the box380can be modified by the user. If selected for preview, other types of TLE data can be similarly edited.

If the Random AMP Samples option in the Preview Selection field372is selected, the Preview screen370displays random AMP samples data for various tables in a database (e.g., SHRITY). Again, if the Edit button374is activated, the random AMP samples data can be modified by the user.

InFIG. 17, the Preview screen370shows the table-level statistics data for table Ti in the display box380, in response to the Statistics option in the Preview Selection field372being selected. In addition to, or as an alternative to, the table-level statistics, the Preview screen370can also show detail interval-level statistics for the table and column. Again, the information in display box380can be edited if the Edit button374is activated.

FIG. 18shows the various commands available under the Tools menu item410of the window200presented by the systems emulation tool22. The Tools menu410includes the following items: Export (to export TLE data from a source system), Import (to import data into a test system), and Undo Import (to undo a previous import action). Another item in the Tools menu410is the Pack item. Selection of the Pack item enables multiple data files (containing exported TLE data) to be packed into one file. Thus, as shown inFIG. 19in a Pack screen420, multiple files can be selected and packed into one file (referred to as a .pac file).

To undo a pack operation, the Unpack item in the Tool menu410can be selected (FIG. 20) in an Unpack screen422to unpack a pack file (.pac file). This causes the .pac file to be broken up into its original data files.

If the Undo Import item in the Tools menu410(FIG. 18) is selected, then a Clean Up screen430is displayed (FIG. 21). By selecting one or more of the import options432in the Clean Up screen, edits made by the user can be undone. Thus, depending on which options are selected, edits made to the object definitions data, statistics data, cost parameters data, and random AMP samples data can be undone.

Referring toFIG. 22, components of the target database system14and the test system10are illustrated in greater detail. As discussed above, the target database system14contains various database-level information, including statistics156, DDL statements158, random AMP samples statements160, actual data162associated with the database32, and other information. In addition, cost-related information may be contained in configuration files154. In one embodiment, the configuration files154are referred to as cost global distributed objects (GDO). The various types of information are stored in one or more storage units196. Also, the database system14includes one or more control units194on which various software modules are executable.

To export or extract target information, an export module190is capable of responding to requests from the system emulation tool22(FIG. 1) by retrieving the requested information. Desired statistics156, DDL statements158, random AMP samples160, user data162, and cost-related information is extracted and communicated across the network12to the emulation client system20.

The exported TLE data is communicated from the emulation client system20to the test system10in an import process. Once imported, the TLE data is applied to appropriate locations in the test system10(e.g., relational tables, files, and other locations). For example, the extracted statistics, DDL statements, random AMP samples, and user data may be stored in locations170,172,174, and176, respectively, by an import module191running on one or more control units184in the test system10. The imported TLE data is stored in one or more storage units186.

In addition, the import module191maps the extracted cost information into a cost table180, which is a relational table. In one embodiment, the diagnostic query statement, which is a SQL statement, includes a diagnostic Dump Costs statement. The diagnostic Dump Costs statement dumps the extracted cost information into rows of the cost table180.

The various software modules or routines discussed herein, such as the optimizer module18, the system emulation tool22, and other software modules or routines are executable on corresponding control units in respective systems. Instructions and data associated with the software modules or routines are stored in respective storage units. Each control unit includes a microprocessor, a microcontroller, a processor card (including one or more microprocessors or microcontrollers), or other control or computing devices. As used here, a “controller” can refer to either hardware or software or a combination of both. In addition, although referred to in the singular, “controller” is also intended to cover multiple hardware components, multiple software components, or a communication thereof.

Each storage unit includes one or more machine-readable storage media for storing data and instructions. The storage media includes different forms of memory including semiconductor memory devices, such as dynamic or static random access memories (DRAMs or SRAMs), erasable and programmable read-only memories (EPROMs), electrically erasable and programmable read-only memories (EEPROMs) and flash memories; magnetic disks such as fixed, floppy and removable disks; other magnetic media including tapes; an optical media such as compact disks (CDs) or digital video disks (DVDs). Instructions that make up the various software routines or modules in the various systems or nodes are stored in the respective storage units. The instructions, when executed by a respective control unit, causes the corresponding system to perform programmed acts.

The instructions of the software routines or modules are transported to a system in one of many different ways. For example, code segments including instructions stored on floppy disks, CD or DVD media, a hard disk, or transported through a network interface card, modem, or other interface device are loaded into the system and executed as corresponding software routines or modules. In the loading or transport process, data signals that are embodied in carrier waves (transmitted over telephone lines, network lines, wireline links, cables, and the like), communicate the code segments, including instructions, to the network element. Such carrier waves are in the form of electrical, optical, acoustical, electromagnetic, or other types of signals.