1. Field of the Invention
The field of the invention is data processing, or, more specifically, methods, systems, and products for mirroring database statistics.
2. Description of Related Art
The development of the EDVAC computer system of 1948 is often cited as the beginning of the computer era. Since that time, computer systems have evolved into extremely complicated devices. Today's computers are much more sophisticated than early systems such as the EDVAC. The most basic requirements levied upon computer systems, however, remain little changed. A computer system's job is to access, manipulate, and store information. Computer system designers are constantly striving to improve the way in which a computer system can deal with information.
Information stored on a computer system is often organized in a structure called a database. A database is a grouping of related structures called ‘tables,’ which in turn are organized in rows of individual data elements. The rows are often referred to a ‘records,’ and the individual data elements are referred to as ‘fields.’ In this specification generally, therefore, an aggregation of fields is referred to as a ‘data structure’ or a ‘record,’ and an aggregation of records is referred to as a ‘table.’ An aggregation of related tables is called a ‘database.’
A computer system typically operates according to computer program instructions in computer programs. A computer program that supports access to information in a database is typically called a database management system or a ‘DBMS.’ A DBMS is responsible for helping other computer programs access, manipulate, and save information in a database.
A DBMS typically supports access and management tools to aid users, developers, and other programs in accessing information in a database. One such tool is the structured query language, ‘SQL.’ SQL is query language for requesting information from a database. Although there is a standard of the American National Standards Institute (‘ANSI’) for SQL, as a practical matter, most versions of SQL tend to include many extensions. Here is an example of a database query expressed in SQL:
This SQL query accesses information in a database by selecting records from two tables of the database, one table named ‘stores’ and another table named ‘transactions.’ The records selected are those having value “Minnesota” in their store location fields and transactions for the stores in Minnesota. In retrieving the data for this SQL query, an SQL engine will first retrieve records from the stores table and then retrieve records from the transaction table. Records that satisfy the query requirements then are merged in a ‘join.’
Databases are very important to the organizations that use them. The term ‘mission critical’ is sometimes applied, meaning that without the database, the organization is critically injured. Because the database is so important, organizations take many measures to be sure it is available for use, even if the system on which it is installed is destroyed by fire, flood, earthquake, explosion, or other disaster. Databases may be backed up by periodic copying to on-site or off-site devices or locations—and databases may be backed up by mirroring.
Mirroring is a real-time incremental replication of data based on journal or log management. That is, mirroring is copying data from an original computer to a backup storage device in real time. In this specification, the backup storage device is referred to generally as a ‘backup computer.’ The term ‘real time’ means that changes in an original database are effected in a backup database at very near the actual time they occur. Because the data is copied in real time, the information stored from the original location is generally an accurate copy of the data from the production device. Data mirroring therefore is intended to provide speedy recovery of data after a failure of an original system—because the backup system is generally identical to the original at all times. Data mirroring can be implemented locally or offsite at a completely different location.
Databases are stores of data, of course, organized in tables, rows, and columns. The data in the tables, row, and columns is the ordinary operational data of direct concern to the users and organizations that rely upon it to run their businesses. Databases contain other data, however, beyond the operational data upon which users rely for business purposes. Databases contain metadata, data about data, data that describes characteristics of other data, including, for example, the operational data of the database. Metadata may describe, for example, how and when and by whom a particular set of operational data was collected, when it was accessed, and how the operational data is formatted. Metadata is essential for understanding information stored in data warehouses and has become increasingly important in XML-based Web applications.
Database statistics are metadata. In a modern DBMS, database statistics are automatically generated by a statistics engine when an attempt to optimize the execution of a query finds useful database statistics missing or stale. Database statistics may include frequency statistics, histogram statistics, and cardinality statistics describing operational data in columns of tables of a database.
Mirroring of database data has not included mirroring of database statistics. That is, traditional mirroring of database data has focused on operational data. Copying database statistics to a backup computer is traditionally a periodic, semi-manual process where a program is run on an original computer to generate a list of required backup database statistics, and then a program is run on the backup computer that takes the list as input and generates required database statistics on the backup computer.
The problem with this approach is that when a user needs to quickly switch over to the backup computer because of a failure on the original computer, many database statistics may be missing or stale. Database performance will be degraded until the backup system eventually generates the required database statistics over time as they are found missing or stale in response to attempts to optimize. Each interruption of optimization on the backup computer to wait for statistics generation represents an additional undesirable burden on system resources.
Exemplary methods, systems, and products are described that expand traditional database mirroring to include not only database production data but also database statistics. That is, exemplary methods, systems, and products are described for mirroring database statistics that operate generally by receiving by a backup application of a backup computer a journal entry representing a requirement for backup database statistics and generating by a statistics engine of the backup computer backup database statistics for a backup database in accordance with the journal entry. Mirroring database statistics according to embodiments of the present invention may also include identifying by an original DBMS of an original database a requirement for backup database statistics and inserting in a database journal by the original DBMS the journal entry representing the requirement for backup database statistics.
Identifying a requirement for database statistics may include optimizing execution of an SQL query in dependence upon database statistics for a column of a database and discovering that the database statistics for the column are missing or stale. The backup computer may be located remotely from the original computer and receiving a journal entry representing a requirement for backup database statistics may be carried out by receiving the journal entry through networked digital data communications.
A journal entry may include a journal entry type field that identifies the journal entry as an entry that represents a requirement for backup database statistics and one or more identifier fields that identify a column of a table that requires database statistics. Database statistics may be implemented as metadata of a table. Database statistics typically include histogram ranges and counts of values in histogram ranges, frequencies of occurrence of a values in columns, and cardinalities of values in columns.
The foregoing and other objects, features and advantages of the invention will be apparent from the following more particular descriptions of exemplary embodiments of the invention as illustrated in the accompanying drawings wherein like reference numbers generally represent like parts of exemplary embodiments of the invention.
‘DDL’ refers to Data Definition Language, a set of SQL keywords for DDL that define new tables and associated data elements. Although there is an ANSI standard for DDL, most commercial SQL databases have proprietary extensions in their DDL, which allow control over proprietary and nonstandard, but operationally useful, elements of a specific system.
‘XML’ refers to the eXtensible Markup Language, a specification developed by the World Wide Web Consortium (‘W3C’). XML is a pared-down version of SGML (the Standard Generalized Markup Language). XML was originally intended primarily to encode Web documents, but its use today is much broader than just the Web. It allows designers to create their own customized tags, enabling the definition, transmission, validation, and interpretation of data between applications, between computers and database, and between organizations.
‘RS-232’ is a standard interface approved by the Electronic Industries Alliance (EIA) for connecting serial devices. Almost all modems conform to the EIA-232 standard, and most personal computers have an EIA-232 port for connecting a modem or other device. In addition to modems, many display screens, mice, and serial printers are designed to connect to an EIA-232 port.
‘USB’ abbreviates Universal Serial Bus, an external bus standard that supports data transfer rates of 12 Mbps. A single USB port can be used to connect up to 127 peripheral devices, such as mice, modems, and keyboards. USB also supports Plug-and-Play installation and hot plugging.
‘IEEE 1394’ is a fast external bus standard that supports data transfer rates of up to 400 Mbps (in 1394a) and 800 Mbps (in 1394b). Products supporting the 1394 standard go under different names, depending on the company. Apple, which originally developed the technology, uses the trademarked name ‘FireWire.’ Other companies use other names, such as i.link and Lynx, to describe their 1394 products. A single 1394 port can be used to connect up 63 external devices. In addition to its high speed, 1394 also supports isochronous data—delivering data at a guaranteed rate. This makes it ideal for devices that need to transfer high levels of data in real-time, such as video devices—and mirroring database statistics, for example. ° Like USB, 1394 supports both Plug-and-Play and hot plugging, and also provides power to peripheral devices.
An Ethernet™ is local-area network (‘LAN’) architecture developed by Xerox Corporation in cooperation with DEC and Intel. Ethernet uses a bus or star topology and supports data transfer rates of 10 Mbps. The Ethernet specification served as the basis for the IEEE 802.3 standard, which specifies the physical and lower software layers of a data communications protocol stack. Ethernet is a widely used implemented LAN standard.
‘IP’ stands for Internet Protocol. IP specifies the format of data packets, a data communications protocol for transmitting the packets among computers, and a network addressing scheme. Most networks that use IP combine it with a higher-level protocol called Transmission Control Protocol (‘TCP’), which establishes a virtual connection between a destination and a source. IP and TCP are so often used together that they are often referred to simply as TCP/IP.
‘HTTP’ stands for HyperText Transfer Protocol, the principal data communications protocol of by the World Wide Web. HTTP functions in the application layer of the ISO data communications protocol stack. HTTP implementations often use TCP in the transport layer and IP in the network layer of the stack.
Exemplary methods, systems, and products for mirroring database statistics according to embodiments of the present invention are explained with reference to the accompanying drawings, beginning with
The system of
The exemplary original computer of
Database statistics are typically implemented as metadata of a table, such as, for example, metadata of tables of database (118). Database statistics may include, for example:
These three database statistics are presented for explanation only, not for limitation. The use of any database statistics as will occur to those of skill in the art is well within the scope of the present invention. Database statistics are further explained below with respect to
Optimizer (110) uses database statistics (412) from database (118) for optimizing SQL queries against database (118). Optimizer (110) notifies statistics engine (206) when the optimizer attempts to use databases statistics for a column of a table, for example, and finds the database statistics missing or stale. Statistics engine (206) generates the missing or stale statistics and notifies journal engine (210) to insert in a database journal a journal entry representing a requirement for backup database statistics. Journal entries in journal (208) are made available for retrieval by a backup computer through journal engine (210) and data communications module (214).
The system of
The system of
For further explanation,
The exemplary system of
In this example, job execution engine (104) will pass the operating system commands from job (102) to the operating system for execution and pass the SQL queries from job (102) to SQL module (116) for execution. Job execution engine (104) passes the SQL queries to SQL module (116) through application programming interface (‘API’) (108) of database management system (‘DBMS’) (106). DBMS (106) provides database management functions for database (118). DBMS (106) exposes API (108) to enable applications, including, for example, job execution engine (104) to access functions of the DBMS, including, for example, SQL module (116). The ‘SQL’ command illustrated in job (102) is a function made available through API (108).
The exemplary SQL module (116) of
The exemplary access plan generator (112) of
The exemplary access plan generator (112) also includes an optimizer (110) implemented as computer program instructions that optimize the access plan in dependence upon database management statistics. Database statistics may reveal, for example, that there are only two storeID values in the transactions table—so that it is an optimization, that is, more efficient, to scan the transactions table rather than using an index. Alternatively, database statistics may reveal that there are many transaction records with only a few transactions records for each storeID—so that it is an optimization, that is, more efficient, to access the transactions records by an index.
The exemplary SQL module (116) of
As mentioned above, mirroring database statistics in accordance with the present invention is generally implemented with computers, that is, with automated computing machinery. For further explanation,
Also stored in RAM (168) is an operating system (154). Operating systems useful in computers according to embodiments of the present invention include UNIX™, Linux™, Microsoft NT™, AIX™, IBM's i5os, and many others as will occur to those of skill in the art. Operating system (154), DBMS (106), and backup application (232) in the example of
Computer (152) of
The example computer of
The exemplary computer (152) of
For further explanation,
The method of
In the method of
For further explanation,
Each record of database journal (208) represents a database journal entry describing a change in a database or a requirement for backup database statistics. Such changes are represented by the SQL queries INSERT, UPDATE, and DELETE, as well as journal entries representing requirements for backup database statistics. The record of database journal (208) have columns representing a record number (502), a table name (504) of a table in which a change has occurred or for which backup database statistics are required, a column name (506) of a column in which a change has occurred or for which backup database statistics are required, an action type (508) indicating the type of change that occurred or whether backup database statistics are to be created or refreshed, and action parameters (510) encoding parameter data needed to carry out a change or creation of backup database statistics in a backup computer.
The action parameters (510) are encoded in name-value pairs. The encoding in name-value pairs is for explanation only, not a limitation of the present invention. Alternative encoding schemes within the scope of the invention include, for example, encodings in DDL, XML, and other encoding schemes as will occur to those of skill in the art. One alternative way to encode the SQL queries effecting changes is to include the entire original SQL query as a text string for execution in an SQL engine of a backup computer. The name-value pair encoding is useful in this example, however, because it may be applied to both the SQL queries affecting changes as well as the journal entries represented requirements for backup database statistics.
Records number 1-3 are journal entries representing changes in database data. Record number 1 in database journal (208) represents a change, insertion of a new database record in a table named ‘User_Acct’ for a user with last name Smith and first name Pete. The user's name is encoded in name-value pairs where LN represents the user's last name and FN represents the user's first name as: LN=‘smith’&FN=‘pete’. Record number 2 in database journal (208) represents a change, updating a record the ‘User_Acct’ table to insert a new logon identification (‘logonID’) for a user with last name Smith and first name Pete. The user's name is included in the journal entry to enable a backup SQL module to find the record to be updated. The name of the column to be updated is in the ‘Column Name’ (506) record number 2. The new value of the logonID is encoded in the name-value pair: NV=‘psmith’. Record number 3 in database journal (208) represents another change, deletion of the ‘User_Acct’ record for the user with last name Smith and first name Pete.
Records 4-8 are journal entries representing requirements for backup database statistics. Record number 4 is a journal entry that represents a requirement to generate all supported types of database statistics for a column named ‘zipCode’ in a table named ‘User_Acct.’ The statistics type ‘all’ is encoded in the name-value pair: STATTYPE=‘all’. Record number 5 is a journal entry that represents a requirement to generate cardinality statistics for a column named ‘zipCode’ in a table named ‘User_Acct.’ The cardinality statistics type is encoded in the name-value pair: STATTYPE=‘card’. Record number 6 is a journal entry that represents a requirement to generate frequency statistics for a column named ‘zipCode’ in a table named ‘User_Acct.’ The frequency statistics type is encoded in the name-value pair: STATTYPE=‘freq’. Record number 7 is a journal entry that represents a requirement to generate cardinality and frequency statistics for a column named ‘zipCode’ in a table named ‘User_Acct.’ The statistics types are encoded in the name-value pairs: STATTYPE=‘card’ &STATTYPE=‘freq’.
Record number 8 in the database journal (208) is a journal entry that represents a requirement to generate histogram statistics for a column named ‘zipCode’ in a table named ‘User_Acct.’ The histogram statistics type is encoded in the name-value pair: STATTYPE=‘hist’. The action parameters (510) for record number 8 also include a data value range for the histogram encoded as: RN=‘10’. The inclusion of the data value range for the histogram within the journal entry itself is optional, presented here for ease of explanation, not as a limitation of the invention. Alternatively, an optimization engine may be configured with operating parameters that include data value ranges for histogram statistics. Or an optimization engine may be programmed to determine histogram ranges algorithmically, for example, by determining the overall range of values in a column of data and dividing the overall range into a predetermined number of equal histogram ranges. Other ways of specifying or determining histogram ranges may occur to those of skill in the art, and all such ways are well within the scope of the present invention.
A backup computer, upon receiving journal entries representing requirements for backup statistics as illustrated in
Each record in the exemplary database statistics of
Statistic number 2 in the exemplary database statistics of
Statistic number 3 in the exemplary database statistics of
This value of statistic number 3 represents a count of the number of records in the User_Acct table having zip codes in a range of 10 zip codes, encoded as: begZipCode endZipCode count begZipCode endZipCode count begZipCode endZipCode count . . . . In this example, begZipCode and endZipCode represent respectfully the beginning zip code value and the ending zip code value in a histogram value range of zip codes. The value of statistic number 3, therefore, represents 120 occurrences of zip code values in the range 1001-1010,′ 57 occurrences of zip code values in the range 1011-1020,′ and so on. The inclusion of the histogram data value range within the database statistic itself is optional, presented here for ease of explanation, not as a limitation of the invention. As mentioned above in the explanation of journal entry number 8 in database journal (208) of
Exemplary embodiments of the present invention are described largely in the context of fully functional computer systems for mirroring database statistics. Readers of skill in the art will recognize, however, that the present invention also may be embodied in a computer program product disposed on signal bearing media for use with any suitable data processing system. Such signal bearing media may be transmission media or recordable media for machine-readable information, including magnetic media, optical media, or other suitable media. Examples of recordable media include magnetic disks in hard drives or diskettes, compact disks for optical drives, magnetic tape, and others as will occur to those of skill in the art. Examples of transmission media include telephone networks for voice communications and digital data communications networks such as, for example, Ethernets and networks that communicate with the Internet Protocol and the World Wide Web. Persons skilled in the art will recognize that any computer system having suitable programming means will be capable of executing the steps of the method of the invention as embodied in a program product. Persons skilled in the art will recognize that, although most of the exemplary embodiments described in this specification are oriented to software installed and executing on computer hardware, nevertheless, alternative embodiments implemented as firmware or as hardware are well within the scope of the present invention.
In view of the explanation set forth above in this specification, readers now should recognize that the benefits of mirroring database statistics according to embodiments of the present invention include: