Archiving of accelerator-specific database tables

Abstract

The DBMS manages a high-performance accelerated database that synchronizes with a conventional client database. The accelerated database contains both "normal" accelerated tables, where each table replicates a table in the client database, and accelerator-only tables (AOT), which are unique to the accelerated database and can be used for analytical purposes. Rows in the AOT are archived by moving them to a dedicated accelerator-only archive stored in the accelerated database. When a user query attempts to access accelerator-only data, the DBMS rewrites the query to adapt the requested operation to the accelerated database's partitioned archived / non-archived structure. The rewritten query specifies steps for accessing archived and non-archived accelerator-only data without the DBMS front end generating a merged view of the archived and non-archived accelerator-only data. If the accelerator-only archive is stored in a read-only format, the rewrite also adds a predictive operation to prevent queries from attempting to modify the archive.

Description

[Technical Field]

[0001] This invention generally relates to database management systems (DBMS), and more particularly to the archiving of accelerated database tables. [Background technology]

[0002] Archiving is the process of moving "cold" data—data that is no longer actively used—to a dedicated archive storage repository. Archived database tables are typically stored in a data archive, separate from the primary production database tables that contain active data, and can be indexed and searched by DBMS users.

[0003] Migrating infrequently accessed archived data to low-cost storage media can reduce the consumption of expensive primary storage by the DBMS and simplify backup procedures. These benefits arise when the DBMS can efficiently inventory currently active data to identify the best candidates for archiving. Some archiving systems protect archived data from modification by storing it in a read-only format, while others allow users to modify archived tables.

[0004] Some DBMSs support a second "accelerated" database system that can provide high-performance, low-latency responses necessary for operations such as real-time online analysis. An accelerated database may have both synchronized replication of active tables contained in the production database and "accelerator-only" tables (AOT) that exist only in the accelerated database. [Overview of the Initiative] [Means for solving the problem]

[0005] Embodiments of the present invention include a system, method, and computer program product for creating and managing an AOT (Automated Access Table) of a high-performance, accelerated database containing archived data. The DBMS manages the accelerated database and synchronizes certain "regular" tables in the accelerated database with identical tables in a conventional "client" database. In addition to the regular tables, the accelerated database also has an AOT unique to the accelerated database and provides high-speed performance sufficient to meet the demands of real-time applications. Rows in the AOT are archived by moving the rows to a dedicated archive table or partition stored in the accelerated database. When a user query or other data access request attempts to access accelerator-only data, the DBMS rewrites the query to adapt the requested operation to the partitioned archive / non-archive structure of the accelerated database. Ta The query specifies a procedure for accessing archived and unarchived accelerator-only data without requiring the DBMS to perform a resource-intensive UNION ALL operation, in order to generate a unified view of archived and unarchived data. 。If the accelerator-specific archive is stored in a read-only format, the rewrite also adds a predictive operation to prevent queries from attempting to modify the archive. [Brief explanation of the drawing]

[0006] [Figure 1] Figure 1 shows a cloud computing environment according to an embodiment of the present invention. [Figure 2] Figure 2 shows an abstraction model layer according to an embodiment of the present invention. [Figure 3] Figure 3 shows the structure of a computer system and computer program code that may be used to implement a method for archiving an accelerator-specific database table according to an embodiment of the present invention. [Figure 4] Figure 4 is a flowchart showing the steps of a method for archiving an accelerator-specific database table according to an embodiment of the present invention. [Figure 5] Figure 5 is a flowchart illustrating the steps of a method according to an embodiment of the present invention for responding to a request to insert new data into a read-only accelerator-only archive. [Figure 6] Figure 6 is a flowchart illustrating the steps of a method according to an embodiment of the present invention for responding to a request to delete data from a read-only accelerator-only archive. [Figure 7] Figure 7 is a flowchart showing the steps of a method according to an embodiment of the present invention for updating an accelerated database in which archived accelerator-only data is read-only. [Figure 8] Figure 8 is a flowchart illustrating the steps of a method according to an embodiment of the present invention for responding to a request to insert new data into a read / write accelerator-only archive. [Figure 9] Figure 9 is a flowchart illustrating the steps of a method according to an embodiment of the present invention for responding to a request to delete data from a read / write accelerator-only archive. [Figure 10] Figure 10 is a flowchart showing the steps of a method according to an embodiment of the present invention for updating accelerator-specific data, where archived accelerator-specific data is stored in a read / write format. [Modes for carrying out the invention]

[0007] Embodiments of the present invention improve known DBMSs by providing a more efficient method for archiving database tables that exist only within a high-speed database and for subsequent access.

[0008] A DBMS that manages an idiomatic client database directly accessible by users and standard DBMS frontends may also manage a dedicated "accelerated" database accessed by the DBMS's accelerator component. When an SQL query or other data access request attempts to access accelerator data, the DBMS forwards the request to the accelerator, thereby determining the best way to access the requested data. For example, if an administrator requests real-time or near real-time statistics about the content or usage of a client database, these would be forwarded to the accelerator to reduce system response latency.

[0009] The accelerated database includes "normal" accelerated tables that overlap with and are synchronized with the corresponding tables in the conventional client database. This synchronization allows the accelerator to generate more accurate statistical and analytical reports on the contents of the client database, and also reduces the time required to retrieve and prepare the large amounts of modeling input data needed for predictive modeling operations.

[0010] The accelerated database may also have an "accelerator-only" table (AOT) that is dedicated to the accelerated database and has no similarity to the client database. The AOT can be used for functions such as storing metadata that describes the characteristics of the client database or usage statistics. The user can optionally limit query results to only the normal accelerated tables or only the AOT.

[0011] Generally, the AOT can be modified by data-manipulation (DML) statements (e.g., INSERT, UPDATE, and DELETE statements of Structured Query Language (SQL)) of queries executed by a dedicated accelerator component. However, the accelerator cannot directly execute DML statements on the rows of normal accelerated tables. This is because doing so would cause the manipulated rows and similar rows in the client database to become asynchronous.

[0012] Similar to other database tables, the "cold" rows of normal accelerated tables can be archived to improve performance by reducing the overall size of the database. When the table rows in the client database are archived, the similar rows in the normal accelerated tables are archived as well to maintain synchronization between the two.

[0013] Since the AOT has no corresponding table in the client database, the AOT rows can be selected for archiving based on implementation-dependent criteria, independent of the content of the client database. Users querying the accelerated database can request access to only the archived rows, only the unarchived rows, or both.

[0014] Unlike conventional databases, known accelerated databases do not archive AOT. Archiving conventional client tables generally involves moving the rows of table data to separate archive repositories typically implemented on lower-performance and less expensive storage. This reduces the amount of expensive storage required to store the more active parts of the database and improves performance because queries accessing the hot data of the client database do not need to search through the archived data.

[0015] However, these benefits are not obtained in the same way when archiving AOT. AOT operates on a dedicated platform separate from the client database platform, so archiving accelerator-specific data to a lower-performance storage medium does not reduce the storage footprint or response time of the client database. Moreover, even cold AOT data generally requires faster processing times than when the archived AOT rows are moved to a lower-performance medium, such as an optical disk or a slow hard disk. Therefore, there has been no motivation to implement a system for archiving AOT in this field.

[0016] As described below, the present invention improves the mechanism by which accelerator-specific data is managed by migrating archived rows of AOT to a separate AOT or partition (which, in some embodiments, is stored in a read-only format). This method also differs from the archiving mechanisms used by known non-accelerated client databases, because the accelerator-specific archives of the present invention remain within the accelerated database partition rather than being migrated to a partition residing on lower-cost storage. While it does not offer the same kind of benefits as offloading archives from conventional client databases, this novel structure reduces overhead and complexity within the accelerated database when a query requests archived AOT data, regardless of whether the query also requests unarchived AOT data. For example, when requesting only archived AOT data or only unarchived AOT data, processing the query only requires searching partitions containing a single desired data class. The resulting performance improvements are particularly important for accelerated systems explicitly designed to serve performance-critical functions.

[0017] The client DBMS frontend receives and parses all incoming queries, regardless of whether the query requests access to client data or only accelerated data. However, accelerator tables, whether archived or not, can only be directly accessed by the accelerator module of the accelerated database. The DBMS frontend forwards parsed requests for accelerator-specific data to the accelerator, which then executes the requested operation on the accelerated database.

[0018] If AOT is divided into archived and unarchived partitions, queries accessing both archived and unarchived accelerated data may require a DBMS frontend to first perform a UNION ALL operation to consolidate the archived and unarchived data into a single searchable view or domain. The resulting view then allows queries to be fully executed by the accelerator within that accelerated database. However, this UNION operation, which often occurs when large amounts of data must be merged, can be resource-intensive.

[0019] To make matters worse, queries that search through both regular accelerated tables and AOT require additional filtering predicates to distinguish between the two types of data. For example, a query that requests only archived AOT data and specifies a condition that cannot be satisfied by existing archived AOT rows must fail. Furthermore, DML query functions executed by the accelerator depending on the accelerated database can only manipulate data stored in AOT. This is because modifying regular accelerated tables without modifying the corresponding tables in the client database would asynchronously process both. Therefore, by processing queries, the accelerator needs to generate additional predicates to distinguish between rows belonging to regular accelerated tables and rows belonging to AOT, either before, during, or both after executing the query. For this reason, queries that select archived or unarchived data from AOT require additional filtering to distinguish rows from unarchived rows in archived tables.

[0020] In this way, embodiments of the present invention improve the performance of known accelerator databases by providing a more efficient mechanism for archiving accelerator-specific data and servicing queries to access AOT. These embodiments leverage the parsing and query rewriting capabilities of a dual database architecture to eliminate additional filtering predicates generated by known accelerators when querying AOT data.

[0021] First, the embodiment includes a novel method for archiving AOT records or tables. Instead of simply marking AOT or AOT records as archived, the invention moves the archived content to a second AOT stored on an accelerated database that is marked to contain only archived accelerator-specific data. This eliminates the need to generate and execute ad-hoc filtering predicates on AOT tables to distinguish between archived and unarchived records.

[0022] The embodiment also leverages the fact that the client database already has a built-in front-end capable of parsing incoming data access requests. The invention instructs the client DBMS front-end to perform a novel step of rewriting certain statements in the query requesting access to the AOT before forwarding the modified query to the accelerator. The accelerator then uses known methods to execute the modified query on the accelerated database.

[0023] Unlike current DBMS implementations, this improvement allows all functionality for distinguishing between "hot" and "cold" (i.e., unarchived and archived) rows of AOT data to be performed by the accelerator within the accelerated database. This eliminates the need for the client DBMS frontend to create filtering predicates to distinguish between archived accelerator-only content and unarchived accelerator-only content, generate a preliminary merged view by performing a UNION ALL operation depending on the content of the queried accelerated database, and instruct the accelerator to query the contents of that merged view. These performance enhancements result in improved performance, simplified query processing, and reduced lockout times during which complex queries cannot access the queried database table or partition.

[0024] These performance enhancements only modify the operation of the client DBMS system that processes queries for AOT data. There is no need to modify the application code of the DBMS that parses and processes queries that access only tables in the client database or regular tables in the accelerated database. These performance enhancements also eliminate the need for the client DBMS front-end to generate a merged view and then generate a filtering predicate that performs a UNION ALL operation to distinguish between archived accelerator-only data and unarchived accelerator-only data within that view. Finally, embodiments of the present invention store archived and unarchived accelerator data in separate accelerated tables or partitions so that compliant accelerators can process requests to access only archived AOT data or only unarchived AOT data without first determining which rows in each AOT contain archived data.

[0025] While this disclosure includes a detailed description of cloud computing, it should be understood that the implementation of the teachings enumerated herein is not limited to cloud computing environments. Rather, embodiments of the present invention can be implemented in combination with any other type of computing environment currently known or to be developed in the future.

[0026] Cloud computing is a service delivery model that enables convenient on-demand network access to a shared pool of configurable computing resources (e.g., networks, network bandwidth, servers, processing, memory, storage, applications, virtual machines, and services) that can be rapidly provisioned and released with minimal management effort or interaction with service providers. This cloud model may include at least five features, at least three service models, and at least four deployment models.

[0027] The features are as follows:

[0028] On-demand self-service: Cloud consumers can unilaterally provision computing functions, such as server time and network storage, as needed, without requiring human interaction with the service provider.

[0029] Broad network access: The functionality is available over a network and accessed through standard mechanisms that facilitate use by heterogeneous thin or thick client platforms (e.g., mobile phones, laptops, and PDAs).

[0030] Resource pooling: A provider's computing resources are pooled to serve multiple consumers using a multi-tenant model, and various physical and virtual resources are dynamically allocated and reallocated according to demand. Consumers generally do not have control or knowledge of the exact location of the resources provided, but can identify the location at a higher level of abstraction (e.g., country, state, or data center), making it location-independent.

[0031] Rapid Adaptability: Features can be provisioned quickly and flexibly, and in some cases automatically, they can scale out quickly, be released quickly, and scale in quickly. For consumers, the features available for provisioning are often unlimited and can be purchased at any amount at any time.

[0032] Measured Services: Cloud systems automatically control and optimize resource usage by employing metric functions at some level of abstraction appropriate to the type of service (e.g., storage, processing, bandwidth, and active user accounts). Resource usage can be monitored, controlled, and reported, providing transparency to both service providers and consumers.

[0033] The service model is as follows:

[0034] Software as a Service (SaaS): This refers to the functionality provided to consumers for using a provider's applications running on a cloud infrastructure. These applications are accessible from various client devices through a thin client interface, such as a web browser (e.g., web-based email). The consumer does not manage or control the underlying cloud infrastructure, such as the network, servers, operating system, storage, or even the underlying cloud infrastructure encompassing individual application functions, with the possible exception of limited, user-specific application configuration settings.

[0035] Platform as a Service (PaaS): A feature provided to a consumer for deploying applications they have created or acquired, generated using programming languages ​​and tools supported by the provider, onto a cloud infrastructure. The consumer does not manage or control the underlying cloud infrastructure, such as the network, servers, operating system, or storage, but has control over the deployed applications and, in some cases, the application hosting environment configuration.

[0036] Infrastructure as a Service (IaaS): This is a service provided to a consumer to provision processing, storage, networking, and other basic computing resources, enabling the consumer to deploy and run any software, including operating systems and applications. The consumer does not manage or control the underlying cloud infrastructure, but has limited control over the operating system, storage, deployed applications, and, in some cases, network components (e.g., the host's firewall).

[0037] The deployment models are as follows:

[0038] Private Cloud: A cloud infrastructure is operated solely for a specific organization. This cloud infrastructure may be managed by that organization or a third party, and may reside on-premises or off-premises.

[0039] Community Cloud: Cloud infrastructure is shared by several organizations and supports a specific community that shares common interests (e.g., mission, security requirements, policies, and compliance considerations). The cloud infrastructure may be managed by the organization or a third party and may reside on-premises or off-premises.

[0040] Public cloud: Cloud infrastructure is available to the general public or large industry groups and is owned by organizations that sell cloud services.

[0041] Hybrid Cloud: Cloud infrastructure is a combination of two or more clouds (private, community, or public) that remain separate entities but are brought together by standardized or proprietary technologies (e.g., cloud bursting for load balancing between clouds) that enable data and application migration.

[0042] Cloud computing environments are oriented services that focus on statelessness, low coupling, modularity, and semantic interoperability. The heart of cloud computing is the infrastructure, which includes a network of interconnected nodes.

[0043] Referring here to Figure 1, an exemplary cloud computing environment 50 is illustrated. As shown, the cloud computing environment 50 comprises one or more cloud computing nodes 10 that can communicate with local computing devices used by cloud consumers, such as personal digital assistants (PDAs) or mobile phones 54A, desktop computers 54B, laptop computers 54C, or automotive computer systems 54N, or a combination thereof. The nodes 10 can communicate with each other. The nodes 10 can be physically or virtually grouped into one or more networks, such as private clouds, community clouds, public clouds, or hybrid clouds, or a combination thereof, as described herein (not shown). This enables the cloud computing environment 50 to provide infrastructure, platforms, or software, or a combination thereof, as a service that does not require cloud consumers to maintain resources on their local computing devices. It is understood that the types of computing devices 54A to 54N shown in Figure 1 are intended to be illustrative only, and that the cloud computing node 10 and the cloud computing environment 50 can communicate with any type of computerized device via any type of network or network addressable connection or a combination thereof (for example, using a web browser).

[0044] Referring here to Figure 2, a set of functional abstraction layers provided by the cloud computing environment 50 is shown (Figure 1). It should be understood that the components, layers, and functions shown in Figure 2 are intended to be illustrative only, and that embodiments of this disclosure are not limited thereto. As illustrated, several layers and corresponding functions are provided as follows:

[0045] The hardware and software layer 60 includes hardware and software components. Examples of hardware components include a mainframe 61, RISC (Reduced Instruction Set Computer) architecture-based servers 62; servers 63; blade servers 64; storage devices 65; and network and networking components 66. In some embodiments, the software components include network application server software 67 and database software 68.

[0046] The virtualization layer 70 provides an abstraction layer from which the following examples of virtual entities may be provided: namely, a virtual server 71; virtual storage 72; a virtual network 73; such a virtual network 73 encompassing, for example, a virtual private network; a virtual application and operating system 74; and a virtual client 75.

[0047] In one example, the management layer 80 may provide several functions as described below: Resource provisioning 81 provides the dynamic procurement of computing resources and other resources used to perform tasks within the cloud computing environment. Measurement and pricing 82 provides cost tracking when resources are used within the cloud computing environment and billing or invoicing for the consumption of these resources. In one example, these resources may include application software licenses. Security provides identification and verification for cloud consumers and tasks, and protection for data and other resources. The user portal 83 provides access to the cloud computing environment for consumers and system administrators. Service level management 84 provides the allocation and management of cloud computing resources to ensure that the required service levels are met. Service level agreement (SLA) planning and execution 85 provides the pre-placement and procurement of cloud computing resources for which future requirements are anticipated in accordance with the SLA.

[0048] The workload layer 90 provides examples of several functions that the cloud computing environment may utilize. Examples of workloads and functions that may be provided from this layer include mapping and navigation 91; software development and lifecycle management 92; provision of virtual classroom education 93; data analysis processing 94; transaction processing 95; and orchestration of complex tasks for archiving accelerator-specific database tables 96.

[0049] The present invention may be a system, method, or computer program product or computer program, or a combination thereof, at any possible level of technical detail where integration is possible. The computer program product may encompass one or more computer-readable storage media having computer-readable program instructions for causing a processor to execute aspects of the present invention.

[0050] The computer-readable storage medium may be a tangible device capable of holding and storing instructions for use by an instruction execution device. The computer-readable storage medium may, but is not limited to, an electronic storage device, a magnetic storage device, an optical storage device, an electromagnetic storage device, a semiconductor storage device, or any suitable combination thereof. A non-exhaustive list of more specific examples of the computer-readable storage medium includes: portable computer diskettes®, hard disks, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), static random access memory (SRAM), compact disc read-only memory (CD-ROM), digital versatile disk (DVD), memory stick, floppy disk, mechanically encoded devices such as punch cards or grooved structures on which instructions are recorded, or any suitable combination thereof. As used herein, a computer-readable storage medium should not be interpreted as a transient signal itself, such as radio waves or other freely propagating electromagnetic waves, electromagnetic waves propagating through waveguides or other transmission media (e.g., light pulses passing through optical fiber cables), or electrical signals transmitted via wires.

[0051] The computer-readable program instructions described herein may be downloaded from a computer-readable storage medium to individual computing devices / processing devices, or to an external computer or external storage device via a network, such as the Internet, a local area network, a wide area network, or a wireless network, or a combination thereof. The network may consist of copper transmission cables, optical transmission fibers, wireless transmissions, routers, firewalls, switches, gateway computers, or edge servers, or a combination thereof. A network adapter card or network interface in each computing device / processing device receives computer-readable program instructions from the network and transfers them for storage in a computer-readable storage medium within the individual computing device / processing device.

[0052] The computer-readable program instructions for performing the operation of the present invention may be assembler instructions, instruction-set-architecture (ISA) instructions, machine instructions, machine-dependent instructions, microcode, firmware instructions, state setting data, configuration data for integrated circuits, or source code or object code written in any combination of one or more programming languages, such as object-oriented programming languages, such as Smalltalk, C++, or conventional procedural programming languages ​​(e.g., the "C" programming language or similar programming languages). The computer-readable program instructions may be executed entirely on the user's computer, partially on the user's computer, partially as a standalone software package on the user's computer, partially on the user's computer and partially on a remote computer, or entirely on a remote computer or server. In the latter scenario, the remote computer may be connected to the user's computer via any type of network, such as a local area network (LAN) or a wide area network (WAN), or such connection may be made to an external computer (e.g., via the Internet using an Internet Service Provider). In some embodiments, electronic circuits, such as programmable logic circuits, field-programmable gate arrays (FPGAs), or programmable logic arrays (PLAs), can execute computer-readable program instructions by personalizing the electronic circuit using state information of computer-readable program instructions in order to carry out the aspects of the present invention.

[0053] The aspects of the present invention are described herein with reference to methods, apparatus (systems), and computer program products or computer program flowcharts or block diagrams or combinations thereof according to embodiments of the present invention. It will be understood that each block in the flowchart or block diagram or combination thereof, and combinations of blocks in the flowchart or block diagram or combination thereof, can be implemented by computer-readable program instructions.

[0054] These computer-readable program instructions may be provided to a computer processor or other programmable data processing device to create a machine, such that instructions executed via the processor of the computer or other programmable data processing device generate means for implementing functions / operations specified in one or more blocks of the flowchart or block diagram or a combination thereof. These computer-readable program instructions may also be stored in a computer-readable storage medium that can instruct a computer-programmable data processing device or other device or a combination thereof to function in a particular manner, so that a computer-readable storage medium containing stored instructions may contain a product containing instructions that implement the functional / operational aspects specified in one or more blocks of the flowchart or block diagram or a combination thereof.

[0055] The computer-readable program instructions may also be loaded onto the computer, other programmable data processing device, or other device such that instructions executed on the computer, other programmable data processing device, or other device implement the functions / operations specified in one or more blocks of the flowchart or block diagram or combination thereof, thereby causing a series of operations on the computer, other programmable device, or other device to generate a computer-implemented process.

[0056] The flowcharts and block diagrams in the drawings illustrate the architecture, functionality, and operation of systems, methods, and computer program products or possible implementations of computer programs according to various embodiments of the present invention. In this regard, each block in the flowchart or block diagram may represent a module, segment, or part thereof of instructions, which includes one or more executable instructions for implementing one or more specified logical functions. In some alternative implementations, the functions shown in the block may occur in a different order than that shown in the drawings. For example, two consecutively shown blocks may actually be achieved as a single process executed simultaneously, substantially simultaneously, partially or entirely in a temporally overlapping manner, depending on the functions involved, or the blocks may be executed in reverse order. Note that each block in the block diagram or flowchart or a combination thereof, and any combination of multiple blocks in the block diagram or flowchart or a combination thereof, may be implemented by a special-purpose hardware-based system that performs the specified functions or operations, or by a combination of special-purpose hardware and computer instructions.

[0057] Figure 3 shows the structure of a computer system and computer program code that may be used to implement a method for archiving accelerator-specific database tables according to an embodiment of the present invention. Figure 3 refers to objects 301-315.

[0058] In Figure 3, the computer system 301 includes a processor 303 connected to one or more hardware data storage devices 311 and one or more I / O devices 313 and 315 through one or more I / O interfaces 309.

[0059] The hardware data storage device 311 may include, but is not limited to, a magnetic tape drive, a fixed or removable hard disk, an optical disk, a mobile device with storage, and a solid-state random access storage device or a read-only storage device. The I / O device may include, but is not limited to, an input device 313, such as a keyboard, scanner, portable telecommunications device, touch-sensitive display, tablet, biometric reader, joystick, trackball, or computer mouse, and an output device 315 (such as a printer, plotter, tablet, mobile phone, display, or sound-generating device). The data storage device 311, input device 313, and output device 315 may be located locally or at a remote site connected to the I / O interface 309 via a network interface.

[0060] The processor 303 may also be connected to one or more memory devices 305, which may include, but are not limited to, dynamic RAM (DRAM), static RAM (SRAM), programmable read-only memory (PROM), field-programmable gate arrays (FPGA), secure digital memory cards, SIM cards, or other types of memory devices.

[0061] At least one memory device 305 contains stored computer program code 307, which is a computer program containing computer-executable instructions. The stored computer program code includes a program that implements a method for archiving accelerator-specific database tables according to embodiments of the present invention, and may implement other embodiments described herein, including the method illustrated in Figures 1 to 10. A data storage device 311 may store the computer program code 307. The computer program code 307 stored in the storage device 311 is configured to be executed by the processor 303 via the memory device 305. The processor 303 executes the stored computer program code 307.

[0062] In some embodiments, rather than being stored in and accessed from a hard drive, optical disc, or other writable, rewritable, or removable hardware data storage device 311, the stored computer program code 307 may be stored in a static, non-removable, read-only storage medium, such as a read-only memory (ROM) device 305, or may be accessed directly from such a static, non-removable, read-only medium 305 by the processor 303. Similarly, in some embodiments, the stored computer program code 307 may be stored as computer-readable firmware, or may be accessed directly from such firmware by the processor 303, rather than from a more dynamic or removable hardware data storage device 311, such as a hard drive or optical disc.

[0063] Accordingly, the present invention discloses a process for integrating, hosting, maintaining, and deploying computer-readable code to a computer system 301 to support a computer infrastructure, wherein the code combined with the computer system 301 can perform a method for archiving accelerator-specific database tables.

[0064] Any component of the present invention may be created, integrated, hosted, maintained, deployed, managed, serviced, supported, etc., by a service provider that facilitates methods for archiving accelerator-specific database tables. Accordingly, the present invention discloses a process for deploying or integrating a computing infrastructure capable of performing a method for archiving accelerator-specific database tables, the process comprising integrating computer-readable code into a computer system 301, wherein the code combined with the computer system 301 is capable of performing a method for archiving accelerator-specific database tables.

[0065] One or more data storage devices 311 (or one or more additional memory devices not shown in Figure 3) may be used as computer-readable hardware storage devices having computer-readable programs embedded within them and / or as computer-readable hardware storage devices having other data stored in them, wherein the computer-readable programs include stored computer program code 307. Generally, a computer program product (or, alternatively, a manufactured product) of a computer system 301 may include such computer-readable hardware storage devices.

[0066] In embodiments comprising a networked computing infrastructure, a cloud computing environment, a client-server architecture, or components of other types of distributed platforms, the functionality of the present invention may be implemented only on a client or user device, or only on a remote server or as a service of a cloud computing platform, or may be divided between local and remote components.

[0067] It is understood that program code 307 for a method of archiving accelerator-specific database tables may be deployed by manually loading program code 307 directly onto client, server, and proxy computers (not shown) by loading program code 307 onto a computer-readable storage medium (e.g., computer data storage device 311), but it is also understood that program code 307 may be deployed automatically or semi-automatically to computer system 301 by sending program code 307 to a central server (e.g., computer system 301) or a group of central servers. Program code 307 may then be downloaded to client computers (not shown) that will run program code 307.

[0068] Alternatively, program code 307 may be sent directly to the client computer via email. The program code 307 may then be detached to a directory on the client computer or loaded to a directory on the client computer, depending on the email option which selects a program to detach program code 307 to.

[0069] Alternatively, program code 307 may be sent directly to a directory on the client computer's hard drive. If a proxy server is configured, the process selects the proxy server code, determines the computer on which to place the proxy server code, transfers the proxy server code, and then installs the proxy server code on the proxy computer. Program code 307 is then sent to the proxy server and stored on the proxy server.

[0070] In one embodiment, program code 307 for a method of archiving accelerator-specific database tables is provided to coexist with software applications (not shown), operating systems (not shown), and network operating system software (not shown), and then the program code 307 is integrated into the client, server, and network environment by installing it on clients and servers in the environment in which the program code 307 will function.

[0071] The first step in the aforementioned integration of the code contained in program code 307 is to identify any software on the client and server where program code 307 will be deployed that is required by or interacts with program code 307, such as the aforementioned software including a network operating system (not shown). This identified software includes a network operating system, where the network operating system includes software that extends the base operating system by adding network functionality. Next, the software application and its version number are identified and compared with a list of software applications and their correct version numbers that have been tested to interact with program code 307. Any software applications that are missing or do not match the correct version number are upgraded to the correct version.

[0072] Program instructions that pass parameters from program code 307 to a software application are checked to ensure that the parameter list of the instruction matches the parameter list requested by program code 307. Conversely, parameters passed by the software application to program code 307 are checked to ensure that those parameters match the parameters requested by program code 307. Client and server operating systems, including network operating systems, are identified and compared to a list of operating systems, version numbers, and network software programs that have been tested to work with program code 307. Operating systems, version numbers, or network software programs that do not match the entries in the list of tested operating systems and version numbers are upgraded to the listed level on the client computer and on the server computer.

[0073] After verifying that the software to which program code 307 is to be deployed is at the correct version level that has been tested to work with program code 307, the integration is completed by installing program code 307 on both the client and server.

[0074] Embodiments of the present invention may be implemented as a method executed by a processor of a computer system, as a computer program product, as a computer system, or as a process or service executed by a processor to support computer infrastructure.

[0075] Figure 4 shows the topology used by the embodiment of the present invention when archiving AOT. Figure 4 shows items 400 to 460.

[0076] Known DBMSs that incorporate accelerated databases typically store both "normal" accelerated tables and AOTs. Each normal table overlaps with and synchronizes with its corresponding table in the DBMS's "conventional" non-accelerated client database.

[0077] In the example shown in Figure 4, an embodiment of the present invention illustrates a DBMS system comprising a client database 410 and an accelerated database 440. When an SQL query or other data access request 400 is received, the DBMS front-end 420 parses the request 400 and then processes it using the accelerator module 430 of the accelerated database 440. (Hereafter also referred to as Accelerator 430) It is sent to [location]. All access to the accelerated database 440 is managed by accelerator 430.

[0078] The DBMS module, high Tables that have not been optimized for speed highIf it is determined that rows in a table that have not been accelerated, or other partitions of data stored in the client database 410, should be archived, the archived data is moved to a separate storage repository dedicated to that archived data.

[0079] Next, the DBMS instructs the accelerator 430 to replicate this operation on the corresponding regular table in the accelerated database 440. In this way, the data stored in the regular accelerated table is maintained in synchronization with the corresponding data in the client database 410.

[0080] Since AOT does not have similar tables in the client database 410, the DBMS cannot use similar procedures to archive the contents of AOT. Therefore, when a query requests access to archived and unarchived rows of AOT, the DBMS frontend 420 must perform additional steps, such as merging tables or generating filtering predicates to select either archived or unarchived rows.

[0081] Embodiments of the present invention improve known AOT archiving processes by moving archived AOT rows to a separate archived AOT table 460. The accelerator 430, in response to determining that rows, partitions, or other components of AOT 450 should be archived, moves the archived content from the unarchived AOT 450 to a second AOT 460 reserved for archived accelerator-only data.

[0082] If the accelerated database 440 supports horizontal table partitioning, each table record, represented as tables 450 and 460, or partitions 1-4, can be a physically "real" partition stored in a different storage repository. Similarly, in other implementations, the accelerated database 440 may implement partitions 1-4 as logical partitions, and individual table rows can be mapped to partitions based on selected predicates or conditions.

[0083] In one example, rows can be selected for archiving as a function of whether the date specified by the Date column in each row contains an odd or even day. If partitions 1, 3, and 4 in Figure 4 each specify odd-numbered dates, but partition 2 specifies even-numbered dates, then the operation to archive records with even dates will move partition 2 to AOT archive 460.

[0084] Once it is determined that partition 2 of AOT 450 should be archived, accelerator 430 moves partition 2 from the unarchived AOT 450 to the archived AOT 460. As will be discussed below, if a query or application still requests access to the original content of table 450, accelerator 430 responds by accessing the content of one or both of table 450 and table 460. If the query requests only unarchived accelerator data, accelerator 430 accesses only the unarchived AOT 450. If the query requests only archived accelerator data, accelerator 430 accesses only the archived AOT 460. If the query requests accelerator data from both classes, accelerator 430 merges AOT table 450 and AOT table 460 and accesses the rows contained in the merged view.

[0085] In all cases, the DBMS frontend 420 parses query 400 and determines whether query 400 requests access to AOT. If the query requests access to archived accelerator-only data or unarchived accelerator-only data, the frontend 420 rewrites the query and forwards the modified query to the accelerator 420. The accelerator 420 uses its standard query execution capabilities to access the appropriate AOT according to the framework in Figure 4. These rewriting steps and corresponding data access procedures are described below.

[0086] Figure 5 is a flowchart illustrating the steps of a method according to an embodiment of the present invention for responding to a request to insert new data into a read-only accelerator-only archive. Figure 5 includes steps 500 to 570.

[0087] In some embodiments, AOT, partitions, and other types of accelerator-only archives are configured to be read-only. How an embodiment services requests to perform DML operations on an accelerator-only archive is determined in part by whether the archive is read-only.

[0088] Figures 5 to 7 illustrate the steps by which each embodiment responds to requests for insert, delete, or update DML operations on a read-only AOT archive. Figures 8 to 10 illustrate the steps by which each embodiment responds to requests for insert, delete, or update DML operations on a read / write AOT archive.

[0089] In particular, accelerator 430 returns an error in response to any query attempting to modify or delete the contents of a read-only accelerator-only archive. Records, rows, tables, or other types of permitted content can only be accessed through specialized procedures (e.g., scheduled maintenance operations) that, in some embodiments, must be performed jointly by the DBMS frontend 420 and accelerator 430. Use A It can be inserted into an archive dedicated to the accelerator. Such specialized procedures are outside the scope of this invention. This specification describes a procedure in which new content cannot be inserted into a read-only AOT archive.

[0090] The method in Figure 5 requires the parsing component of the client DBMS frontend 420 to rewrite the query statement that attempts to insert new data into an existing read-only AOT. This rewrite adds a predicate that determines whether any of the new data items satisfy the condition that requires the item to be archived. When this rewrite is forwarded to the accelerator 430, Ta The query instructs accelerator 430 to make such a determination and return an error condition if any such element is identified.

[0091] In one example, the DBMS typically archives rows in the Sales.Logistics table that are dedicated to accelerators and identify transactions older than one year. If a query requests the addition of 10 new rows to the Sales.Logistics table, the method in Figure 5 determines whether any of those 10 rows identify transactions older than one year. If any such rows are identified, the method returns an error condition because, in order to fully satisfy the query, the identified rows would need to be inserted into a read-only AOT archive (this operation is not permitted).

[0092] In step 500, the DBMS frontend 420 receives a query or other data access request.

[0093] In step 510, the DBMS frontend 420 parses the received query and determines that the query attempts to insert a database row into AOT (or, depending on the implementation details, attempts to insert a table, record, or another type of data element).

[0094] In step 520, the DBMS frontend 420 rewrites the query to add a predicate that determines the number of rows that should be inserted into AOT that satisfy the condition that those rows should be inserted into the accelerator-only archive.

[0095] In step 530, the DBMS frontend 420 performs the rewrite Ta Transfer the query to accelerator 430.

[0096] In step 540, the accelerator 430 performs the rewrite Ta Start executing the query.

[0097] In step 550, the accelerator 430 performs the rewrite Ta The system determines which of the rows to be inserted meets the archiving criteria as a result of the query. If no rows are archived, the system performs step 560 and inserts the requested row into the appropriate unarchived AOT. However, if any of the rows meet the criteria for being selected for archiving, the system instead performs step 570 and returns an error condition resulting from an improper attempt to insert the data into a read-only archive.

[0098] For example, consider an SQL query containing the following statement (row numbers are added for reference only).

[0099] (1) INSERT INTO <aot>

[0100] (2) SELECT … FROM<any_other_table> WHERE<additional_predicates>

[0101] Here, row (1) is data <aot>It requests that it be inserted into the AOT named [name of AOT].

[0102] Row (2) is an SQL predicate.<additional_predicates> Using the conditions identified by, the data to be inserted is<any_other_table> Select a sub-selection from the database table named [table name].

[0103] The DBMS frontend 420 responds to the detection of these statements by rewriting the query to add those statements.

[0104] (3) SELECT COUNT( * )

[0105] (4) FROM (SELECT … FROM<any_other_table> WHERE<additional_predicates> )

[0106] WHERE<archive_condition_predicates>

[0107] Row (3) counts the number of items returned by rows (4) and (5). Row (4) identifies that the items to be counted are extracted from the view identified by the subselect condition in row (2). Row (5) further filters the list of items returned by row (4) to determine the SQL predicate.<archive_condition_predicates> Ensure that only items that meet the archiving criteria identified by are included. In other words, rows (3) through (5) count the number of data elements that row (1) will insert into a read-only AOT archive, rather than into an unarchived AOT.

[0108] Therefore, lines (3) through (5) executed by accelerator 430 return a non-zero count when the query attempts to insert accelerator-specific data that must be archived upon insertion. Since the system is not permitted to modify the contents of such read-only archives, a non-zero count causes the query to fail.

[0109] The examples and embodiments in this document should not be interpreted as limiting the types of data access requests to queries in SQL format. The present invention is flexible enough to accommodate all types of SQL or non-SQL data access requests, insofar as the DBMS frontend 420 can parse and then rewrite the requests in a manner functionally similar to the examples described above.

[0110] Figure 6 is a flowchart showing the steps of a method according to an embodiment of the present invention for responding to a request to delete data from a read-only accelerator-only archive. Figure 6 includes steps 600 to 670.

[0111] In step 600, the DBMS frontend 420 receives a query or other data access request.

[0112] In step 610, the DBMS frontend 420 parses the received query and determines that the query attempts to delete a database row from AOT (or, depending on the implementation details, attempts to delete a table, record, or another type of data element).

[0113] In step 620, the DBMS frontend 420 rewrites the query to add a predicate that determines the number of rows that should be deleted and that satisfy the condition that those rows should now be stored in the accelerator-only archive.

[0114] In step 630, the DBMS frontend 420 performs the rewrite Ta Transfer the query to accelerator 430.

[0115] In step 640, the accelerator 430 performs the rewrite Ta Start executing the query.

[0116] In step 650, the accelerator 430 performs the rewrite Ta The system determines which of the rows to be deleted meet the criteria for archiving as a result of the query. If none of the rows are archived, the system performs step 660, deleting the requested row from the current storage location of rows in the unarchived AOT. However, if any of the rows meet the criteria for being selected for archiving, the system instead performs step 670, returning an error condition resulting from an improper attempt to delete data from a read-only archive.

[0117] For example, consider an SQL query containing the following statement (row numbers are added for reference only).

[0118] (1) DELETE FROM <aot>

[0119] (2) WHERE<additional_predicates>

[0120] Here, row (1) is data <aot>I request that it be removed from AOT, which is named as such.

[0121] Row (2) is an SQL predicate.<additional_predicates> Sub-select the data that should be deleted by listing the conditions identified by the criteria.

[0122] The DBMS frontend 420 responds to the detection of these statements by rewriting the query to add those statements.

[0123] (3) SELECT COUNT( * )

[0124] (4) FROM (SELECT … FROM <aot>WHERE<additional_predicates> )

[0125] (5) WHERE<archive_condition_predicates>

[0126] Row (3) counts the number of items returned in rows (4) and (5). Row (4) counts the number of items to be counted in the table in row (2). <aot>Identify that the predicate is being filtered. Row (5) further filters the list of items returned by row (4) to match the SQL predicate.<archive_condition_predicates> Ensure that only items that meet the archiving criteria identified by [the specified method] are included. In other words, rows (3) through (5) count the number of data elements that will be deleted by rows (1) and (2) from the read-only AOT archive rather than from the unarchived AOT.

[0127] Therefore, lines (3) to (5) executed by accelerator 430 return a non-zero count when attempting to delete some archived accelerator-only data from a read-only AOT archive. Since the system is not permitted to delete data from a read-only archive, a non-zero count causes the query to fail.

[0128] Figure 7 is a flowchart showing the steps of a method according to an embodiment of the present invention for updating an accelerated database in which archived accelerator-only data is read-only. Figure 7 includes steps 700 to 770.

[0129] Performing the requested update operation involves modifying data elements stored in an unarchived AOT or partition so that the changed data elements meet the archiving requirements. This operation may result in an unarchived AOT or partition containing data that should be stored in a read-only AOT archive. Similarly, performing an update operation that modifies previously archived data elements will result in an AOT archive or partition containing data that should not be archived if the changed data elements no longer meet the archiving requirements.

[0130] One solution is to update the accelerator-only partition and table by combining the delete and insert procedures described in Figures 5 and 6. However, this procedure only works correctly when updating a single row.

[0131] For example, consider a query that includes an update statement to update the "Discount" column in the row that stores the "Age" for those under 30.

[0132] (1) UPDATE customer

[0133] (2) SET discount = discount -10%

[0134] (3) WHERE age <= 30

[0135] This operation cannot be correctly implemented by performing the delete operation in Figure 6 followed by the insert operation in Figure 5. Deleting all eligible rows from the target table would require an additional step to store the deleted content in a cache or other temporary location. If such a save operation is not performed, the system will not know what values ​​to insert after the original rows have been deleted.

[0136] Similarly, performing an insert before a delete temporarily adds duplicate rows to the updated table, potentially violating uniqueness constraints, and requires additional code to select which rows to delete and which to keep during the delete operation. These issues become even more complex when the column to be updated is also used by a query predicate to determine whether updating the row changes its eligibility for archiving.

[0137] In other words, update operations cannot add data to a read-only, accelerated archive, so data elements are archived of The update operation that would make it eligible must fail. Similarly, archived data elements of Updating to a value for which the user no longer qualifies must fail because the update operation cannot remove the element from the read-only accelerated archive.

[0138] Therefore, the method in Figure 7 rewrites the query to first determine whether the update will change the archive status of the row before actually updating the row. This allows the system to read-only Use A In the dedicated archive for the accelerator The row Inserted thing or The row It will be deleted from there. that Prevent the generation of the desired results.

[0139] As described below, the system can incorporate the predicates of an update statement (from rows (2) and (3)) into a select statement that can be evaluated to identify the rows to be selected and the values ​​to which those selected rows will be updated. The resulting query statement can be executed regardless of whether any of the values ​​or literals identified in rows (2) and (3) refer to columns used by the predicates to identify archived rows.

[0140] Therefore, in the execution example, the DBMS frontend 420 rewrites the submitted query to include the statement.

[0141] (4) SELECT COUNT( * )

[0142] (5) FROM (SELECT discount -10% FROM customer WHERE age<=30>)

[0143] (6) WHERE<archive_condition_predicates>

[0144] Similar to the methods in Figures 5 and 6, line (4) counts the number of rows identified by line (5) as having been generated by the original update statement, and then, in line (6), the result set is filtered to include only the updated rows that are eligible to be archived. The resulting code indicates whether the update procedure identified archived rows and allows the system to return an error condition if an attempt is made to insert a row into or delete a row from a read-only archive.

[0145] This procedure is described in detail in the method shown in Figure 7.

[0146] In step 700, the DBMS frontend 420 receives a query or other data access request.

[0147] In step 710, the DBMS frontend 420 parses the received query and determines that the query attempts to update an AOT or partition database row (or other type of stored database content).

[0148] In step 720, the DBMS frontend 420 rewrites the query to add a statement that determines how many rows must subsequently be moved into a read-only accelerator-only archive as a result of the update.

[0149] In step 730, the DBMS frontend 420 performs the rewrite Ta Transfer the query to accelerator 430.

[0150] In step 740, the accelerator 430 performs the rewrite Ta Start executing the query.

[0151] In step 750, the accelerator 430 performs the rewrite Ta The system determines which of the rows to be updated, after executing the query, meets the archiving requirements. If rows (4) through (6) return zero, the system executes step 760 and completes the requested update operation. Otherwise, the system executes step 770 and returns an error resulting from an improper attempt to update data to a read-only archive.

[0152] Figures 8~ figure Each of the 10 methods describes how to perform insert, delete, or update DML operations that affect the content of a read / write accelerator-only archive.

[0153] Figure 8 is a flowchart showing the steps of a method according to an embodiment of the present invention for responding to a request to insert new data into a read / write accelerator-only archive. Figure 8 includes steps 800 to 860.

[0154] In step 800, the DBMS frontend 420 receives a query or other data access request.

[0155] In step 810, the DBMS frontend 420 parses the received query and determines that the query attempts to insert a database row into AOT (or, depending on implementation details, a table, record, or another type of data element). As seen in Figure 5, one example of such a statement is as follows:

[0156] (1) INSERT INTO <aot>

[0157] (2) SELECT … FROM <any_other_tables> WHERE <additional_predicates>

[0158] In Project 820, the DBMS front-end 420 rewrites the query to include two different insert statements, namely, those that refer to rows or other data elements to be inserted into the AOT archive, and those that refer to rows or other data elements to be inserted into the non-archived AOT. For example, in response to receiving exemplary SQL statements (1) and (2), the front-end 420 adds the following.

[0159] (3) INSERT INTO <non-archive_AOT>

[0160] (4) SELECT *

[0161] (5) FROM (SELECT … FROM <any_other_tables> WHERE <additional_predicates>)

[0162] (6) WHERE NOT (<archive-condition_predicates>)

[0163] (7) INSERT INTO <archive_AOT>

[0164] (8) SELECT*

[0165] (9) FROM (SELECT … FROM <any_other_tables> WHERE <additional_predicates>)

[0166] (10) WHERE<archive-condition_predicates>

[0167] In this example, lines (4) and (5) select all the rows to be inserted by lines (1) and (2) by referencing the expression and predicate of line (2) which selects the rows to be inserted. Line (6) filters the selected rows and archives them. of Delete the eligible rows. Thus, row (3) inserts a subset of inserted rows that should not be archived into a normal (i.e., unarchived) AOT or partition.

[0168] Similar to rows (4) and (5), rows (8) and (9) select all rows that should be inserted by rows (1) and (2). Row (10) filters this set of rows for archiving. of The filter is configured to include only the eligible rows. Row (7) then inserts the filtered results into the AOT archive. Since the embodiments of the present invention divided each AOT into an archived read-only table or partition and an unarchived read-only table or partition, statements (4) and (8) divide the rows to be inserted into two different subsets, one of which is inserted into the archived AOT and the other into the unarchived AOT.

[0169] In another example, the query identifies the rows to insert using the SQL VALUES clause instead of a subselect.

[0170] (1a) INSERT INTO <aot>

[0171] (2a) SELECT … FROM TABLE (VALUES ( … )) AS t( <column-names>

[0172] In such cases, the frontend 420 rewrites the query to add statements similar to those in rows (3) through (8), and then selects the records to insert again by copying the original view generation method of the query.

[0173] (3a) INSERT INTO<non-archive_AOT>

[0174] (4a) SELECT *

[0175] (5a) FROM TABLE (VALUES ( … )) AS t( <column-names>

[0176] (6a) WHERE NOT( <archive-predicates>)

[0177] (7a) INSERT INTO <archive_AOT>

[0178] (8a) SELECT *

[0179] FROM TABLE (VALUES ( … )) AS t( <column-names>

[0180] (10a) WHERE <archive-predicates>

[0181] In step 830, the DBMS frontend 420 performs the rewrite Ta Transfer the query to accelerator 430.

[0182] In step 840, the accelerator 430 performs the rewrite Ta Start executing the query.

[0183] In process 850, the accelerator 430 performs archiving as instructed in lines (3) to (6) or lines (3a) to (6a). of Insert the ineligible rows into the appropriate, unarchived AOT or partition.

[0184] In process 860, the accelerator 430 performs archiving as instructed in lines (7) to (10) or lines (7a) to (10a). of Insert the eligible rows into the appropriate AOT or partition where the archive data will be stored.

[0185] Figure 9 is a flowchart showing the steps of a method according to an embodiment of the present invention for responding to a request to delete data from a read / write accelerator-only archive. Figure 9 includes steps 900 to 950.

[0186] Unlike other operations performed by embodiments of the present invention, processing queries attempting to perform delete-type operations does not require rewriting the queries, as long as the system's accelerator-only archive is writable (i.e., not read-only). In such cases, the system utilizes embodiments that divide each AOT into archived tables or partitions and unarchived tables or partitions by simply performing a separate set of deletes for each class of data.

[0187] In step 900, the DBMS frontend 420 receives a query or other data access request.

[0188] In step 910, the DBMS frontend 420 parses the received query and determines that the query attempts to delete one or more database rows from AOT (or, depending on the implementation details, attempts to delete a table, record, or another type of data element).

[0189] In step 920, the DBMS frontend 420 forwards the received query to the accelerator 430.

[0190] In step 930, the accelerator 430 starts executing the transferred query.

[0191] In step 940, the accelerator 430 removes a first subset of rows identified by the query from the unarchived AOT of the accelerated database. The DBMS frontend 420 or accelerator 430 would have identified by known means that each row in the first subset contains data that should not be archived.

[0192] In step 950, the accelerator 430 removes a second subset of rows identified by the query from the accelerated database read / write AOT archive. The DBMS frontend 420 or accelerator 430 would have identified by known means that each row in the first subset contains accelerator-specific data that should be archived.

[0193] Figure 10 is a flowchart showing the steps of a method according to an embodiment of the present invention for updating accelerator-specific data, where archived accelerator-specific data is stored in a read / write format. Figure 10 includes steps 1000 to 1080.

[0194] When a system incorporating a read / write accelerator-only archive performs an AOT update request, the following four results are possible:

[0195] i) An archived data element is updated with data that is no longer eligible for archiving. The updated data element must then be moved to an unarchived AOT or partition;

[0196] ii) Data elements that have not been archived are being archived of The data is updated to be eligible. The updated data element must then be moved to the archived AOT or partition;

[0197] iii) Archived data elements are updated to data that is still eligible for archiving. The updated data elements are not moved to a different table or partition; and,

[0198] iv) Unarchived data elements are updated with data that is still ineligible for archiving. The updated data elements are not moved to a different table or partition.

[0199] However, it is impossible to perform the following four different update operations:

[0200] (i) For rows that remain unarchived after the update, perform an update operation on the unarchived table or partition;

[0201] (ii) Perform an update operation on the AOT archive for all rows that remain archived after the update;

[0202] (iii) Performing insert / delete operations on unarchived rows that, after an update, must be moved from an unarchived table or partition to an accelerator-only archive; and

[0203] (iv) Performing insert / delete operations on archived rows that, after the update, must be moved from the accelerator-only archive to an unarchived table or partition.

[0204] The problem with this approach is that modifying different rows multiple times during a single query exposes the updated data to potential corruption unless all affected tables are protected from concurrent modification by any other processes throughout the duration of the four sets of queries. Furthermore, as is known in the art, even the order in which rows are updated by operations (i) to (iv) above can sometimes lead to inconsistent results.

[0205] Embodiments of the present invention address this problem by performing the following different operation sequences, which avoid updating any different rows stored in different tables or partitions multiple times:

[0206] 1) Perform an update operation on all rows of a table or partition that has not been archived;

[0207] 2) Perform an update operation on all rows of the archived table or partition;

[0208] 3) Move any rows that are eligible for archiving from an unarchived table or partition to an archived table or partition; and

[0209] 4) Move any rows that do not qualify for archiving from an archived table or partition to a non-archived table or partition.

[0210] Here, operations 1) and 2) are performed on different tables, and therefore the same row cannot be modified twice. Operations 3) and 4) simply move rows from one table to another, and therefore no rows are modified. Thus, these four operations never modify the same row twice.

[0211] Rewriting the query to add statements that perform these operations is a simple procedure, similar to the examples in Figures 5-9. For example, the content of the updated row is the same as described in the previous diagrams.<archive_condition_predicates> By applying archiving of A row may be identified as eligible (or ineligible). Once the final destination of a row is identified, moving a row from one table or partition to another can be done by performing insert and delete operations in an idiomatic manner.

[0212] This procedure can be generalized by the method shown in Figure 10.

[0213] In step 1000, the DBMS frontend 420 receives a query or other data access request.

[0214] In step 1010, the DBMS frontend 420 parses the received query and determines that the query attempts to update a database row in AOT (or, depending on the implementation details, attempts to insert a table, record, or another type of data element).

[0215] In step 1020, the DBMS frontend 420 rewrites the query in order to implement steps 1) to 4) of the four steps described above.

[0216] In step 1030, the DBMS frontend 420 performs the rewrite Ta Transfer the query to accelerator 430.

[0217] In step 1040, the accelerator 430 performs the rewrite Ta Start executing the query.

[0218] In step 1050, the accelerator 430 performs the rewrite on all rows currently stored in the unarchived table or partition. Ta Execute the update operation identified by the query.

[0219] In step 1060, the accelerator 430 rewrites all rows currently stored in the archived AOT or partition. Ta Execute the update operation identified by the query. In some embodiments, steps 1050 and 1060 may be executed in reverse order.

[0220] In process 1070, accelerator 430 performs archiving of Move any updated rows that currently have eligibility and are currently stored in an unarchived table or partition to their respective accelerator-dedicated archives.

[0221] In process 1080, accelerator 430 performs archiving of Move any updated rows that are no longer eligible and are currently stored in the accelerator-only archive to their correct, unarchived tables or partitions. In some embodiments, steps 1070 and 1080 may be performed in reverse order.

[0222] The methods in Figures 5-10 can be extended to other types of queries submitted to databases conforming to the topologies in Figures 1-4. For example, a request to access accelerator-only data that does not explicitly request archived data would be executed against unarchived AOT containing hot data. However, a query that does not limit results to unarchived data would be rewritten by the DBMS frontend 420 to perform a UNION ALL operation that first merges the contents of the archive with the corresponding unarchived table. The above methods ensure that the returned set of results does not contain duplicates.

[0223] The examples and embodiments of the present invention described herein are presented for illustrative purposes only. They should not be construed as exhaustive, nor should they limit the embodiments of the present invention to those described herein. Many other modifications and variations of the present invention that do not depart from the scope and spirit of these examples and embodiments will be apparent to those skilled in the art. The terms used herein have been selected to best illustrate the underlying principles of these examples and embodiments, to describe the practical applications and technical improvements of the present invention to known art and products, and to enable readers skilled in the art to better understand the examples and embodiments disclosed herein. < / aot> < / aot> < / aot> < / aot> < / aot> < / aot> < / aot> < / aot>

Claims

1. A database management system (DBMS) comprising and managing a client database (hereinafter referred to as the "first database") and a second database connected to the first database, wherein the first database comprises a front-end, the second database is faster than the first database, the second database stores unarchived database tables (hereinafter referred to as "unarchived database tables") and archived database tables (hereinafter referred to as "archived database tables"), the second database has a module for accessing the second database and can only be directly accessed by the module, and the DBMS comprises a processor, memory connected to the processor, and a computer-readable hardware storage device connected to the processor, wherein the storage device includes program code configured to implement a method for archiving the second database tables, which is executed by the processor via the memory. The above method involves the processor, The front-end determines whether the rows in the unarchived database table are eligible for archiving, where this determination is made by determining whether the rows in the unarchived database table meet the archiving criteria identified by the SQL predicate. To make it happen For the module in question, in accordance with the determination that it is qualified, move the determined row from the unarchived database table to the archived database table. This includes causing the execution of Here, the unarchived database table is stored in the second database, but is not replicated in the first database. The archive database table is stored in the second database, but unlike the unarchived database table, it stores rows where the rows in the unarchived database table are determined to be eligible for archiving. The aforementioned database management system.

2. The above method involves the processor instructing the front end to The system receives SQL queries from users to access the data stored in the second database, parses the queries, and then forwards each of the parsed requests to the module. This includes causing the execution of The processor includes instructing the module to execute the analyzed request for one or more rows of data stored in the second database, The system according to claim 1.

3. The aforementioned SQL query, An insert operation to insert one or more new rows into one or both of the aforementioned unarchived database table and the aforementioned archived database table. A delete operation to remove one or more existing rows from either or both of the aforementioned unarchived database table and the aforementioned archived database table. Update operation to modify data stored in one or more existing rows of either or both of the aforementioned unarchived database table and the aforementioned archived database table. The system according to claim 2, comprising a statement selected from the group consisting of the following.

4. The system according to any one of claims 1 to 3, wherein the archive database table is stored in a read-only format.

5. To parse the SQL query, if the SQL query includes an insert operation statement, With respect to the aforementioned front end, Rewrite the user request to include a statement instructing the module to count the number of rows that will be inserted by the insertion operation, the number of rows that are eligible for archiving. In response to the rewriting, the rewritten request is forwarded to the module as the analyzed request, and If the module receives notification that the number of rows is greater than 0, return an error message. A system according to any one of the following claims, which causes the execution of the above:

6. To parse the SQL query, if the SQL query includes a statement for a delete operation, With respect to the aforementioned front end, Rewrite the user request to include a statement instructing the module to count the number of rows that will be deleted by the aforementioned deletion operation, the number of rows that are eligible for archiving. In response to the rewriting, the rewritten request is forwarded to the module as the analyzed request, and If the module receives notification that the number of rows is greater than 0, return an error message. A system according to claim 2 or 3, or, as a reference to claim 2 or 3, claim 4 or 5, which causes the execution of the above.

7. To parse the SQL query, if the SQL query includes an update operation statement, With respect to the aforementioned front end, Rewrite the user request to include a statement instructing the module to count the number of rows that will be revised by the update operation, the number of rows that will be eligible for archiving after being modified by the update operation. In accordance with the above rewriting, The rewritten request, as the analyzed request, is forwarded to the module. and If the module receives notification that the number of rows is greater than 0, return an error message. A system according to claim 2 or 3, or any one of claims 4 to 6 when referring to claim 2 or 3, which causes the execution of the above.

8. The system according to any one of claims 1 to 3, wherein the archive database table is stored in a read-write format.

9. To parse the SQL query, if the SQL query includes an insert operation statement, With respect to the aforementioned front end, Identify which of the multiple rows that will be inserted by the aforementioned insertion operation are eligible for archiving. Each row identified as eligible for archiving by a rewrite request is inserted into the archive database table, and Each row identified as ineligible for archiving by the aforementioned rewrite request is inserted into the aforementioned unarchived database table. Rewrite the user request to add a statement instructing the module to do so, In response to the rewriting, the rewritten request is forwarded to the module as the analyzed request. This will cause it to execute, Here, the identification is performed by determining whether the row in the unarchived database table satisfies the archiving criteria identified by the SQL predicate. The system according to claim 8, as referenced by claim 2 or 3.

10. The system according to claim 2 or 3, or claim 8 or 9 as a reference to claim 2 or 3, wherein the method, for parsing the SQL query, if the SQL query includes a delete operation statement, causes the processor to cause the front end to forward the user request to the module.

11. To parse the SQL query, if the SQL query includes an update operation statement, With respect to the aforementioned front end, To update each row that has been requested to be updated by the aforementioned update operation, If there are rows that were not eligible for archiving before the update but will be eligible for archiving after the update, move those rows to the aforementioned archive database table, and If there are rows that were eligible for archiving before the update but will no longer be eligible after the update, move those rows to the aforementioned unarchived database table. Rewriting the user request to replace the SQL query statement with a statement instructing the module to do so, In response to the rewriting, the rewritten request is forwarded to the module as the analyzed request. A system according to claim 2 or 3, or claim 8 or 9 as a reference to claim 2 or 3, which causes the execution of the above.

12. A method of archiving, wherein the database management system (DBMS) comprises and manages a client database (hereinafter referred to as the "first database") and a second database connected to the first database, the first database comprises a front-end, the second database is faster than the first database, the second database stores unarchived database tables (hereinafter referred to as "unarchived database tables") and archived database tables (hereinafter referred to as "archived database tables"), the second database has a module for accessing the second database and can only be directly accessed by the module, and the DBMS comprises a processor, memory connected to the processor, and a computer-readable hardware storage device connected to the processor, the storage device containing program code configured to be executed by the processor via the memory and implement a method for archiving the second database tables. The above method involves the processor, The front-end determines whether the rows in the unarchived database table are eligible for archiving, where this determination is made by determining whether the rows in the unarchived database table meet the archiving criteria identified by the SQL predicate. To make it happen For the module in question, in accordance with the determination that it is qualified, move the determined row from the unarchived database table to the archived database table. This includes causing the execution of Here, the unarchived database table is stored in the second database, but is not replicated in the first database. The archive database table is stored in the second database, but unlike the unarchived database table, it stores rows where the rows in the unarchived database table are determined to be eligible for archiving. The aforementioned method.

13. The above method involves the processor instructing the front end to The system receives SQL queries from users to access the data stored in the second database, parses the queries, and then forwards each of the parsed requests to a module of the DBMS. This includes causing the execution of The processor includes instructing the module to execute the analyzed request for one or more rows of data stored in the second database, The method according to claim 12.

14. The aforementioned SQL query, An insert operation to insert one or more new rows into one or both of the aforementioned unarchived database table and the aforementioned archived database table. A delete operation to remove one or more existing rows from either or both of the aforementioned unarchived database table and the aforementioned archived database table. Update operation to modify data stored in one or more existing rows of either or both of the aforementioned unarchived database table and the aforementioned archived database table. The method according to claim 13, comprising a statement selected from the group consisting of the following.

15. The method according to any one of claims 12 to 14, wherein the archive database table is stored in a read-only format.

16. Parsing the SQL query is If the SQL query includes an insert operation statement, the front end will be instructed to: Rewrite the user request to include a statement instructing the module to count the number of rows that will be inserted by the insertion operation, the number of rows that are eligible for archiving. In response to the rewriting, the rewritten request is forwarded to the module as the analyzed request, and If the module receives notification that the number of rows is greater than 0, return an error message. The purpose is to make it happen. If the SQL query includes a delete operation statement, the front end will be instructed to: Rewrite the user request to include a statement instructing the module to count the number of rows that will be deleted by the aforementioned deletion operation, the number of rows that are eligible for archiving. In response to the rewriting, the rewritten request is forwarded to the module as the analyzed request, and If the module receives notification that the number of rows is greater than 0, return an error message. The purpose is to make it happen. If the SQL query includes an update operation statement, the front end will be instructed to: Rewrite the user request to include a statement instructing the module to count the number of rows that will be revised by the update operation, the number of rows that will be eligible for archiving after being modified by the update operation. The rewritten request is forwarded to the module as the analyzed request in response to the rewriting, and If the module receives notification that the number of rows is greater than 0, return an error message. The goal is to make it happen. The method according to any one of claims 13 or 14, or claim 15 as a reference to claim 13 or 14.

17. The method according to any one of claims 12 to 14, wherein the archive database table is stored in a read-write format.

18. Parsing the SQL query is If the SQL query includes an insert operation statement, the front end will be instructed to: Identify which of the multiple rows that will be inserted by the aforementioned insertion operation are eligible for archiving. Each row identified as eligible for archiving by a rewrite request is inserted into the archive database table, and Each row identified as ineligible for archiving by the aforementioned rewrite request is inserted into the aforementioned unarchived database table. Rewrite the user request to add a statement instructing the module to do so, In response to the rewriting, the rewritten request is forwarded to the module as the analyzed request. The purpose is to make it happen. Here, the identification is performed by determining whether the row in the unarchived database table satisfies the archiving criteria identified by the SQL predicate. If the SQL query is a delete operation, the front end further includes forwarding the user request to the module, and If the SQL query is an update operation, the front end will be told to To update each row that has been requested to be updated by the aforementioned update operation, and If there are rows that were not eligible for archiving before the update but will be eligible for archiving after the update, move those rows to the aforementioned archive database table, and If there are rows that were eligible for archiving before the update but will no longer be eligible after the update, then those rows should be placed in the aforementioned unarchived database table. Move to the Rewriting the user request to replace the SQL query statement with a statement instructing the module to do so, In response to the rewriting, the rewritten request is forwarded to the module as the analyzed request. The goal is to make it happen. The method according to claim 17, as referenced by claim 13 or 14.

19. The further includes providing at least one support service for at least one of the creation, integration, hosting, maintenance, and deployment of computer-readable program code in a computer system, wherein the computer-readable program code combined with the computer system is configured to perform decisions and movements. The method according to any one of claims 12 to 18.

20. An archiving computer program comprising a database management system (DBMS) comprising and managing a client database (hereinafter referred to as the "first database") and a second database connected to the first database, wherein the first database comprises a front-end, the second database is faster than the first database, the second database stores unarchived database tables (hereinafter referred to as "unarchived database tables") and archived database tables (hereinafter referred to as "archived database tables"), the second database has a module for accessing the second database and can only be directly accessed by the module, and the DBMS comprises a processor, memory connected to the processor, and a computer-readable hardware storage device connected to the processor, wherein the storage device includes the computer program configured to implement a method for archiving the second database tables, which is executed by the processor via the memory. A computer program that causes the processor to perform each step of the method according to any one of claims 12 to 19.

Images