Batch job performance improvements in dual-host architecture

Abstract

In a method for improving the performance of a batch job (232) running on a database server in a dual-host architecture: In response to the batch job (232) being ready to execute on the source database server (210), the processor (504) sends a first communication (410) to the target database server (220) with a synchronization start point. While executing the batch job (232), the processor (504) utilizes pre-locking, pre-loading, and lock avoidance features to prevent lock conflicts (420). In response to the source database server (210) or the target database server (220) encountering a commit statement, the processor (504) suspends the corresponding database server and sends a second communication to inquire whether the other corresponding database server is ready to complete the commit statement (430). In response to the other corresponding database server confirming that it is ready to complete the commit statement, the processor (504) completes the commit statement on both the source database server (210) and the target database server (220).

Description

Background Technology

[0001] This invention generally relates to the field of data synchronization on database servers, and more particularly to improving the performance of batch jobs running on database servers in an active-active architecture.

[0002] Dual-host architecture is popular for distributed relational database services that use multiple database servers. It uses a pair of database servers—a source server and a target server—where the target server is a backup of the source server. Data can be read from and / or written to both servers. Dual-host architecture guarantees high availability of data access when one server goes down.

[0003] A batch job is a computer program or set of programs processed in batch mode. This means that a sequence of commands to be executed by the operating system, i.e., multiple Structured Query Language (SQL) statements, is listed in a file (often called a batch file, command file, job script, or shell script) and submitted as a single unit for execution. Summary of the Invention

[0004] Various aspects of embodiments of the present invention disclose methods, computer program products, and computer systems for improving the performance of batch jobs running on a database server in a dual-host architecture.

[0005] In response to a batch job being ready to execute on a source database server in a dual-host environment, the processor sends a first communication between the source database server and the target database server, the first communication having a synchronization start point for when to begin executing the batch job on both the source and target database servers. The processor begins executing the batch job on both the source and target database servers at the synchronization start point. In response to either the source or target database server encountering a commit statement for a unit of the batch job, the processor suspends the corresponding database server encountering the commit statement. The processor sends a second communication between the source and target database servers to inquire whether the other corresponding database server is ready to complete the commit statement. In response to the other corresponding database server confirming that it is ready to complete the commit statement, the processor completes the commit statement on both the source and target database servers.

[0006] In some aspects of embodiments of the present invention, in response to a locking conflict encountered on the source database server or the target database server, the processor sends a communication to another corresponding database server that has not encountered the locking conflict to suspend the operation.

[0007] In some aspects of embodiments of the present invention, in response to encountering an SQL error on the source database server or the target database server, the processor sends a communication to another corresponding database server that has not encountered the SQL error to suspend the operation.

[0008] In some aspects of embodiments of the present invention, the processor asynchronously executes a pre-locking function for each update statement and each delete statement in the batch job using a table scan access method across the source database server and the target database server. The pre-locking function using the table scan access method includes: scanning each page after the first page for the rows to be modified by the operation, in parallel with the main task of the operation that locks the rows to be modified on the first page; locking the rows to be modified on each page after the first page; and acquiring a page lock on the page in response to determining that the number of rows to be locked within the page exceeds a preset threshold.

[0009] In some aspects of embodiments of the invention, the processor asynchronously executes a pre-locking function for each update and each delete statement in the batch job using an index-only access method across the source database server and the target database server. The pre-locking function using the index-only access method includes: in parallel with the main task of the operation that uses an index to lock rows on a first page to be modified by the operation, using information in index entries to locate rows on each page after the first page to be modified by the operation, wherein each index entry includes a key value and a row identifier (ID), wherein the row ID includes a partition number, a data page number, and a slot number, and wherein the data page number is used to locate and determine how many rows to be modified on each page; locking the rows to be modified on each page after the first page; and acquiring a page lock on the page in response to determining that the number of rows to be locked within the page exceeds a preset threshold based on the number of data page number entries for the page.

[0010] In some aspects of embodiments of the invention, the processor asynchronously executes a pre-locking function for each update and each delete statement in the batch job using a normal index access method across the source database server and the target database server. The pre-locking function using the normal index access method includes: in parallel with the main task of the operation that uses an index to lock rows on the first page to be modified by the operation, finding rows on each page after the first page to be modified by the operation based on information in the index entries; then applying additional assertions included in the operation to determine the row or page lock to be acquired, wherein each index entry includes a key value and a row identifier (ID), wherein the row ID includes a partition number, a data page number, and a slot number, and wherein the data page number is used to locate and determine how many rows to be modified on each page; acquiring a page-level lock for each page after the first page with the rows to be modified; and downgrading the page-level lock to a row-level lock in response to determining, based on the data page number in each index entry, that the number of rows to be locked within the page does not exceed a preset threshold.

[0011] In some aspects of embodiments of the invention, the processor asynchronously executes a preloading function for each insert statement in the batch job across the source database server and the target database server. The preloading function includes, in parallel with the main task of retrieving a first set of rows, preloading a second set of rows by using an index to locate a set of leaf pages to store the second set of rows, wherein the leaf pages are defined on the table inserted by the operation.

[0012] In some aspects of embodiments of the invention, in response to encountering a select statement in the batch job, the processor asynchronously executes a lock avoidance function across the source database server and the target database server. The lock avoidance function includes: constructing an image of the active unit recovery (UR) identifier (ID) of the batch job, wherein the image includes a low boundary and a high boundary of the active UR ​​ID; in response to reading a row having a UR ID within the low boundary and the high boundary of the image, tracing the log records of the row until a version of the row having a corresponding UR ID below the low boundary is found; and executing the select statement using the version of the row having the corresponding UR ID below the low boundary without locking. Attached Figure Description

[0013] Figure 1 This is a functional flowchart illustrating how SQL statements can be executed between database servers in a dual-host architecture, according to existing technology.

[0014] Figure 2 This is a functional block diagram illustrating a distributed data processing environment according to an embodiment of the present invention;

[0015] Figure 3 This is a block diagram illustrating a unit of the main program according to an embodiment of the present invention.

[0016] Figure 4 This is a flowchart describing the operational steps of a data synchronization method according to an embodiment of the present invention, which is used to improve the performance of batch jobs running on a database server in a dual-host architecture; and

[0017] Figure 5 The present invention is described in the form of an embodiment. Figure 2 A block diagram of the server components in a distributed data processing environment. Detailed Implementation

[0018] Embodiments of this invention recognize that while dual-host architectures are widely used in the database field, they sacrifice data performance due to the data synchronization required between the source and target servers. Data performance is even worse when executing batch jobs. Data being modified or read by operations such as SELECT, INSERT, UPDATE, or DELETE but not yet committed is invisible (i.e., inaccessible for use), therefore the longer it takes to commit data, the longer the data remains invisible and inaccessible.

[0019] Figure 1 This is a functional flowchart 100 illustrating how a job is executed between database servers in a dual-host architecture according to the prior art. To complete a single commit modifying a single row of data, three communications 115 are required between the source server 105 and the target server 110, as indicated by the three arrows between the source and target. This results in slow performance and wastes system resources during the "waiting" period.

[0020] A current solution for batch jobs that require modifying multiple rows of data requires the same three communications to perform the "block" modification operation, which necessitates... Figure 1 The same four steps (1-4) are shown, but each step is performed on the "row blocks" involved in the batch job. Even though the same three communications still exist between the source and target servers, this current solution for batch jobs has three drawbacks: (1) the extended waiting time between the three communications to allow modification and submission of "row blocks" between the source and target servers; (2) an increased probability of lock conflicts; and (3) underutilization of system resources due to this serial operation. Therefore, embodiments of the present invention recognize the need to reduce the waiting time during this data synchronization process of the database server in a dual-host architecture to improve data performance.

[0021] Embodiments of the present invention provide a system and method for improving the data performance of batch jobs running on a database server in a dual-host architecture by simultaneously performing batch data modification operations on both a source server and a target server to minimize necessary communication between the source and target servers. Embodiments of the present invention also provide a system and method for improving the data performance of a database server in a dual-host architecture by pre-locking and / or pre-loading data involved in upcoming modification operations to prevent locking conflicts, which in turn reduces downtime (i.e., hang time) due to locking conflicts and reduces the likelihood of rollbacks due to locking conflicts. Embodiments of the present invention further provide a system and method for improving the data performance of a database server in a dual-host architecture by avoiding locking requirements for read (i.e., SELECT) operations.

[0022] Implementations of embodiments of the present invention may take various forms, and references are subsequently made to them. Figures 2 to 5 Discuss the details of an exemplary implementation.

[0023] Figure 2 A functional block diagram illustrating a distributed data processing environment, generally designated as 200, is described according to an embodiment of the present invention. As used herein, the term "distributed" describes a computer system comprising multiple physically separate devices operating together as a single computer system. Figure 2 This is merely an illustration of an implementation and does not imply any limitation on the environments in which different embodiments may be implemented. Those skilled in the art can make many modifications to the described environments without departing from the scope of the invention as set forth in the claims.

[0024] The distributed data processing environment 200 includes a source server 210, a target server 220, and a server 230 interconnected via a network 205. Network 205 can be, for example, a telecommunications network, a local area network (LAN), a wide area network (WAN) such as the Internet, or a combination of these, and can include wired, wireless, or fiber optic connections. Network 205 can include one or more wired and / or wireless networks capable of receiving and transmitting data, voice, and / or video signals (including multimedia signals comprising voice, data, and video information). Typically, network 205 can be any combination of connections and protocols supporting communication between the source server 210, the target server 220, the server 230, and other computing devices (not shown) within the distributed data processing environment 200.

[0025] Source server 210 and target server 220 operate as database servers in a dual-host architecture, where target server 220 is a backup of source server 210. In this embodiment, source server 210 and target server 220 can each be an independent computing device, management server, web server, or any other electronic device or computing system capable of receiving, sending, and processing data. In this embodiment, source server 210 and target server 220 represent computing systems utilizing clustered computers and components (e.g., database server computers, application server computers, etc.) that act as a single, seamless resource pool when accessed within the distributed data processing environment 200. Source server 210 and target server 220 may include internal and external hardware components, as referenced... Figure 5 Further detailed description and depiction.

[0026] Server 230 may be a standalone computing device, management server, web server, mobile computing device, or any other electronic device or computing system capable of receiving, sending, and processing data. In other embodiments, server 230 may represent a server computing system utilizing multiple computers as server systems, such as in a cloud computing environment. In another embodiment, server 230 may be a laptop computer, tablet computer, netbook computer, personal computer (PC), desktop computer, personal digital assistant (PDA), smartphone, or any programmable electronic device capable of communicating via network 205 with source server 210, target server 220, and other computing devices (not shown) within distributed data processing environment 200. In another embodiment, server 230 represents a computing system utilizing cluster computers and components (e.g., database server computers, application server computers, etc.) that act as a single seamless resource pool when accessed within distributed data processing environment 200. In the described embodiments, server 230 includes batch job 232. Server 230 may include internal and external hardware components, as referenced in the documentation. Figure 5 Further detailed description and depiction.

[0027] Batch job 232 is a computer program or assembly of programs (i.e., main program 234) processed in batch mode. Batch job 232 consists of a sequence of commands (i.e., multiple Structured Query Language (SQL) statements) embedded in main program 234, submitted as a single unit for execution on the database server. Main program 234 is main language code logic containing a number of n units designated as unit recovery identifier #n (URID#n), where n represents a positive integer between 1 and any number of units present in main program 234. A unit represents a portion of code logic that includes SQL statements (e.g., SELECT, UPDATE, DELETE, INSERT, etc.) and ends with a COMMIT command indicating that data has been committed. For example, main program 234 might contain... Figure 3 The main language code logic shown has n units designated as UR ID#1, UR ID#2, ... and UR ID#n.

[0028] Figure 4 This is a flowchart 400 describing the operational steps of a data synchronization method according to an embodiment of the present invention. This method is used to improve the performance of batch jobs running on a source server and a target server in a dual-host architecture. In the embodiment, data modification operations are performed simultaneously on the source server and the target server while utilizing pre-locking, pre-loading, and lock avoidance functions to avoid lock conflicts as needed. It should be understood that... Figure 4 The process described illustrates a possible iteration of the data synchronization method, which can be repeated for each batch of jobs received from the source server.

[0029] In step 410, in response to the batch job being ready to execute on the source server, the source server sends a communication to the target server to synchronize the start point of the batch job on both the source and target servers. In an embodiment, the source server sends a first communication to the target server to inquire whether the target server is ready to run the batch job and waits for confirmation from the target server that it is ready to run the batch job. In an embodiment, the source server sends in the first communication the synchronization start point for when to begin executing the batch job on both the source and target servers.

[0030] In step 420, the source server and target server begin running the batch job at the start point of synchronization. In this embodiment, the source server and target server run the batch job by locking the rows or pages involved in the data modification operations of each unit of the batch job. If a locking conflict occurs on the source server or target server, the server with the locking conflict suspends until it can acquire the necessary lock. The server with the locking conflict sends a message to the other server to suspend until the lock can be acquired. The other server sends a message acknowledging the suspension. If the lock is not acquired in time, the server with the locking conflict performs a rollback and sends a message to the other server to perform the same rollback. The other server sends a message acknowledging the rollback. If an SQL code error occurs on the source server or target server, the server with the SQL code error performs a rollback and sends a message to the other server to perform the same rollback. The other server sends a message acknowledging the rollback.

[0031] To help avoid locking conflicts during batch job execution, this method asynchronously employs pre-locking and / or pre-loading features across the source and target servers, as needed. Pre-locking is deployed for encountered UPDATE or DELETE operations, where the data rows already exist. Pre-loading is deployed for encountered INSERT operations, where the rows to be modified do not yet exist.

[0032] For the pre-locking function, there are two possible access methods: checking rows and / or pages one by one for the rows being modified. The first access method is a table scan, and the second access method is an index scan. Using the table scan method, the server uses a hybrid locking mechanism to asynchronously perform the pre-locking function for the rows to be modified, which helps improve performance. When the main task of the operation starts at the first page and begins locking the rows to be modified, the subtasks use a table scan to scan each page after the first page for the rows to be modified and lock the rows to be modified. When the number of rows to be locked within the same page exceeds a preset threshold, for example, 50% or more of the rows on the same page, the subtasks will escalate the multi-row locks to page locks. A special pre-locked area will be established within the buffer pool to avoid excessive refreshes.

[0033] Using the index scan method, there are two types available: (1) index-only access and (2) normal index access. For index-only access, the server asynchronously performs pre-locking on the row to be modified using row-level locking or page-level locking based on the key range and row ID. Similarly, when the main task of the operation starts at the first page and begins using the index (i.e., the binary index tree) to find the necessary rows on the first page to lock the row to be modified, the subtask uses the index to find the rows to be modified on each page after the first page to lock the rows to be modified or, if the number of rows on the page to be modified is a preset threshold, to lock the page. Index-only access determines whether a row needs to be modified and needs to be locked without accessing the page, but rather by finding the location information in the index entry. The index entry format consists of a key value and a row ID, where the row ID contains the partition number, data page number, and slot number, where the data page number is used to locate and determine how many rows to be modified. If there are more data page number entries for a page than a preset threshold, page locking is used.

[0034] For normal index access, the server asynchronously performs pre-locking at the beginning for the rows to be modified using page-level locking based on the key range and row ID. Then, it appropriately downgrades to row-level locking when the number of rows modified within the same page falls below a preset threshold. This access method is used when additional assertions are included in the operation. Similarly, when the main task of an operation begins at the first page and starts using the index (i.e., the binary index tree) to find the necessary rows on the first page to lock the rows to be modified, subtasks use the index to find the rows to be modified on each page after the first page. To determine if a row is eligible, the subtask uses the index and then applies additional assertions to determine which row lock or page lock to acquire. Normal index access determines whether a row needs to be modified and whether locking is required without accessing pages; instead, it looks for location information in the index entry. The index entry format consists of a key value and a row ID, where the row ID contains the partition number, data page number, and slot number, with the data page number used to locate and determine how many rows to modify.

[0035] For the preload functionality deployed for encountered INSERT operations, the server asynchronously performs the preload function against the leaf pages of the index and calculates the position (i.e., slot) of the insert key, where the leaf page is defined on the table inserted by the INSERT operation. While the main task uses the index to locate the position to fetch the first set of rows (e.g., 10 rows) and insert the rows, a subtask running in parallel with the main task preloads (i.e., fetches) the second set of rows (e.g., 10 rows). The subtask locates which index leaf pages will be used to store the rows and performs the page load operation. The subtask preloads the leaf pages for the main task to use directly.

[0036] To completely avoid locking requirements for SELECT operations (i.e., read operations) during batch job execution, this method asynchronously employs lock avoidance features across the source and target servers, where necessary. When a SELECT operation is encountered, the lock avoidance feature builds a map of active UR ​​IDs, where the active UR ​​ID is one that has not yet been committed and therefore cannot be read. This map is a timeline of UR IDs, showing which UR IDs have been committed, are active, and have not yet started. The map includes a low boundary and a high boundary for the active UR ​​IDs. The lock avoidance feature utilizes a new format for row records, which, along with the row value, also includes the UR ID and a log buffer pointer. When reading a row, the map of active UR ​​IDs is used to check if the current row's UR ID is within the boundaries of the active UR ​​IDs. If the current row's UR ID is less than the low boundary, the row's value is visible to the SELECT statement.

[0037] If the current row's UR ID is within the boundary of the active UR ​​IDs, the lock avoidance function traces the log entries one by one for the current row until it finds the appropriate UR ID that is no longer active. For example, if the row value is #3, the current row's UR ID is 18, and the mapping shows that UR IDs 10 through 20 are active, the lock avoidance function uses the log buffer pointer to trace back to find a previous version of the row with UR ID 14 (row value #2). Therefore, the log buffer pointer for that row version is used to trace back to the second previous version of the row with UR ID 7 (outside the active range) (row value #1), so that the second previous version of the row can be read.

[0038] If the current row's UR ID is within the boundary of the active UR ​​ID but cannot be found within the image, this means the current row was committed before the UR ID image was built and is still visible to the current read operation. This can occur when the UR is a short unit of fast commit. This lock avoidance feature improves performance by avoiding the need to acquire a shared lock when encountering a SELECT statement.

[0039] In step 430, in response to the source server encountering a "submit" statement from a unit of the batch job, the source server communicates with the target server to check if the target server is ready to complete the submit statement and suspends until the target server confirms that it is ready to submit. Once the target server communicates back with the source server to confirm that it is ready to submit, both the source server and the target server have completed the submit. In other embodiments, in response to the target server encountering a "submit" statement from a unit of the batch job, the target server communicates with the source server to check if the source server is ready to complete the submit statement and suspends until the source server confirms that it is ready to submit. Once the source server communicates back with the target server to confirm that it is ready to submit, both the source server and the target server have completed the submit.

[0040] Embodiments of the present invention utilize this data synchronization method to improve the performance of batch jobs running on source and target servers in a dual-host architecture. Performance tests were conducted to compare the current logic for running batch jobs in a dual-host vs. single-server architecture with the new logic of the data synchronization method for running batch jobs in a dual-host vs. single-server architecture. Performance tests of the current logic showed a 51.43% performance increase, while the new logic showed an 89.62% performance increase.

[0041] Figure 5 A description of an embodiment of the invention suitable for Figure 2 A block diagram of the components of a distributed data processing environment 200, including servers 230 and computing devices 500. It should be understood that... Figure 5 The description of one implementation only does not imply any limitation on the environment in which different embodiments may be implemented. Many modifications can be made to the described environment.

[0042] The computing device 500 includes a communication structure 502 that provides communication between a cache 516, a memory 506, a permanent storage device 508, a communication unit 510, and one or more input / output (I / O) interfaces 512. The communication structure 502 can be implemented using any architecture designed to transfer data and / or control information between processors (such as microprocessors, communication and network processors, etc.), system memory, peripheral devices, and any other hardware components within the system. For example, the communication structure 502 can be implemented using one or more buses or crossbar switches.

[0043] Memory 506 and persistent storage device 508 are computer-readable storage media. In this embodiment, memory 506 includes random access memory (RAM). Typically, memory 506 may include any suitable volatile or non-volatile computer-readable storage medium. Cache 516 is a fast memory that enhances the performance of computer processor 504 by storing recently accessed data from memory 506 and data near the accessed data.

[0044] The program can be stored in persistent storage device 508 and memory 506 for access and / or execution by one or more of the respective computer processors 504 via cache 516. In an embodiment, persistent storage device 508 includes a magnetic hard disk drive. As an alternative to or addition to a magnetic hard disk drive, persistent storage device 508 may include a solid-state drive, semiconductor storage device, read-only memory (ROM), erasable programmable read-only memory (EPROM), flash memory, or any other computer-readable storage medium capable of storing program instructions or digital information.

[0045] The media used in persistent storage device 508 can also be removable. For example, a removable hard disk drive can be used in persistent storage device 508. Other examples include optical discs and disks, thumb drives and smart cards, which are inserted into the drive for transfer to another computer-readable storage medium that is also part of persistent storage device 508.

[0046] In these examples, communication unit 510 provides communication with other data processing systems or devices. In these examples, communication unit 510 includes one or more network interface cards. Communication unit 510 can provide communication by using one or both of physical and wireless communication links. Programs can be downloaded to permanent storage device 508 via communication unit 510.

[0047] I / O interface 512 allows data input and output to other devices that can be connected to server 230. For example, I / O interface 512 can provide connectivity to external device 518, such as a keyboard, keypad, touchscreen, and / or some other suitable input device. External device 518 may also include portable computer-readable storage media, such as, for example, thumb drives, portable optical discs or disks, and memory cards. Software and data used to practice embodiments of the invention can be stored on such portable computer-readable storage media and can be loaded onto persistent storage device 508 via I / O interface(s) 512. I / O interface(s) 512 is also connected to display 520.

[0048] The display 520 provides a mechanism for displaying data to the user and can be, for example, a computer monitor.

[0049] The procedures described herein are identified based on the applications for which the procedures are implemented in specific embodiments of the invention. However, it should be understood that any particular procedural terminology used herein is for convenience only, and therefore the invention should not be limited to use only in any particular application identified and / or implied by such terminology.

[0050] This invention can be a system, method, and / or computer program product. A computer program product may include a computer-readable storage medium (or multiple media) having computer-readable program instructions thereon for causing a processor to execute aspects of the invention.

[0051] Computer-readable storage media can be tangible devices capable of retaining and storing instructions for use by an instruction execution device. Computer-readable storage media can be, for example, but not limited to, electronic storage devices, magnetic storage devices, optical storage devices, electromagnetic storage devices, semiconductor storage devices, or any suitable combination of the foregoing. A non-exhaustive list of more specific examples of computer-readable storage media includes the following: portable computer disks, hard disks, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), static random access memory (SRAM), portable compact disc read-only memory (CD-ROM), digital multipurpose disc (DVD), memory sticks, floppy disks, mechanical encoding devices (such as raised structures in slots having instructions recorded thereon or punched cards), and any suitable combination of the foregoing. As used herein, computer-readable storage media should not be construed as transient signals themselves, 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 fiber optic cables), or electrical signals transmitted through wires.

[0052] The computer-readable program instructions described herein can be downloaded from a computer-readable storage medium to a corresponding computing / processing device or to an external computer or external storage device via a network (e.g., the Internet, a local area network, a wide area network, and / or a wireless network). The network may include copper transmission cables, optical transmission fibers, wireless transmissions, routers, firewalls, switches, gateway computers, and / or edge servers. A network adapter card or network interface in each computing / processing device receives the computer-readable program instructions from the network and forwards them to a computer-readable storage medium within the corresponding computing / processing device.

[0053] Computer-readable program instructions used to perform the operations of this invention may be assembly instructions, instruction set architecture (ISA) instructions, machine instructions, machine-dependent instructions, microcode, firmware instructions, state setting data, or source code or object code written in any combination of one or more programming languages, including object-oriented programming languages ​​(such as Smalltalk, C++, etc.) and conventional procedural programming languages ​​(such as 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, as a standalone software package, partially on the user's computer and partially on a remote computer, or entirely on a remote computer or server. In the latter case, the remote computer may be connected to the user's computer via any type of network, including a local area network (LAN) or a wide area network (WAN), or may be connected to an external computer (e.g., via the Internet using an Internet service provider). In some embodiments, electronic circuitry including, for example, programmable logic circuitry, field-programmable gate arrays (FPGAs), or programmable logic arrays (PLAs) may be personalized to execute computer-readable program instructions by utilizing state information from the computer-readable program instructions in order to perform aspects of this invention.

[0054] Various aspects of the present invention are described herein with reference to flowchart illustrations and / or block diagrams of methods, apparatus (systems), and computer program products according to embodiments of the invention. It should be understood that each block of the flowchart illustrations and / or block diagrams, as well as combinations of blocks in the flowchart illustrations and / or block diagrams, can be implemented by computer-readable program instructions.

[0055] These computer-readable program instructions may be provided to a processor of a general-purpose computer, a special-purpose computer, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, create means for implementing the functions / actions specified in one or more blocks of a flowchart and / or block diagram. These computer-readable program instructions may also be stored in a computer-readable storage medium that directs a computer, programmable data processing apparatus, and / or other device to operate in a particular manner, wherein the computer-readable storage medium storing the instructions includes an article of manufacture containing instructions that implement aspects of the functions / actions specified in one or more blocks of a flowchart and / or block diagram.

[0056] Computer-readable program instructions may also be loaded onto a computer, other programmable data processing apparatus, or other device to cause a series of operational steps to be performed on the computer, other programmable apparatus, or other device to produce a computer-implemented process, such that the instructions, which execute on the computer, other programmable apparatus, or other device, perform the functions / actions specified in one or more boxes of a flowchart and / or block diagram.

[0057] The flowcharts and block diagrams in the accompanying drawings illustrate the architecture, functionality, and operation of possible implementations of systems, methods, and computer program products according to various embodiments of the present invention. In this regard, each block in a flowchart or block diagram may represent a module, segment, or portion of instructions, including one or more executable instructions for implementing the specified logical function. In some alternative implementations, the functions marked in the blocks may occur in a non-linear order. For example, depending on the function involved, two consecutively shown blocks may execute substantially simultaneously, or these blocks may sometimes execute in reverse order. It should also be noted that each block in the block diagrams and / or flowcharts, and combinations of blocks in the block diagrams and / or flowcharts, can be implemented using a dedicated hardware-based system that performs the specified function or action or performs a combination of dedicated hardware and computer instructions.

[0058] Various embodiments of the invention have been described for illustrative purposes, but are not intended to be exhaustive or limited to the disclosed embodiments. Many modifications and variations will be apparent to those skilled in the art without departing from the scope of the invention. The terminology used herein has been chosen to best explain the principles of the embodiments, their practical application, or technical improvements to technologies found in the market, or to enable others skilled in the art to understand the embodiments disclosed herein.

Claims

1. A computer-implemented method, comprising: In response to a batch job being ready to be executed on a source database server in a dual-host environment, one or more processors send a first communication between the source database server and the target database server, the first communication having a synchronization start point for when to begin executing the batch job on both the source database server and the target database server; The batch job is executed by one or more processors at the synchronization start point on both the source database server and the target database server. In response to a commit statement from a unit of the batch job encountered by the source database server or the target database server, the one or more processors suspend the corresponding database server that encountered the commit statement. The one or more processors send a second communication between the source database server and the target database server to inquire whether another corresponding database server is ready to complete the commit statement; as well as In response to the other corresponding database server confirming that the other corresponding database server is ready to complete the commit statement, the one or more processors complete the commit statement on both the source database server and the target database server.

2. The computer-implemented method according to claim 1 further includes: In response to a locking conflict encountered on the source database server or the target database server, the one or more processors send a communication to another corresponding database server that did not encounter the locking conflict to suspend the operation.

3. The computer-implemented method according to claim 1 further includes: In response to an SQL error encountered on the source database server or the target database server, the one or more processors send a communication to another corresponding database server that did not encounter the SQL error to suspend the operation.

4. The computer-implemented method according to claim 1 further includes: The pre-locking function for each update and delete statement in the batch job, using a table scan access method, is asynchronously executed by the one or more processors across the source database server and the target database server, wherein the pre-locking function using the table scan access method includes: In parallel with the main task of locking the row on the first page to be modified by the operation, the one or more processors scan each page after the first page for the row to be modified by the operation; The one or more processors lock the lines to be modified on each page following the first page; and In response to the determination that the number of rows to be locked within a page exceeds a preset threshold, the one or more processors acquire a page lock on the page.

5. The computer-implemented method according to claim 1, further comprising: The one or more processors asynchronously execute a pre-locking function for each update and each delete statement in the batch job using an index-only access method across the source database server and the target database server, wherein the pre-locking function using the index-only access method includes: This is done in parallel with the main task of locking the row on the first page that needs to be modified by the operation using an index. The one or more processors use information in the index entries to locate the rows to be modified by the operation on each page after the first page, wherein each index entry includes a key value and a row identifier (ID), wherein the row ID includes a partition number, a data page number and a slot number, and wherein the data page number is used to locate and determine how many rows to be modified on each page; The one or more processors lock the lines to be modified on each page following the first page; and In response to the determination that the number of rows to be locked within the page exceeds a preset threshold based on the number of page number entries, the one or more processors acquire a page lock on the page.

6. The computer-implemented method according to claim 1, further comprising: The pre-locking function for each update and delete statement in the batch job, using a normal index access method, is asynchronously executed by the one or more processors across the source database server and the target database server, wherein the pre-locking function using the normal index access method includes: This is done in parallel with the main task of locking the row on the first page that needs to be modified by the operation using an index. The one or more processors locate the rows to be modified by the operation on each page after the first page based on information in the index entries, and then apply additional assertions included in the operation to determine the row or page lock to be acquired, wherein each index entry includes a key value and a row identifier (ID), wherein the row ID includes a partition number, a data page number and a slot number, and wherein the data page number is used to locate and determine how many rows to be modified on each page; The one or more processors acquire page-level locks on each page following the first page containing the row to be modified; and In response to the determination that the number of rows to be locked within a page does not exceed a preset threshold based on the data page number in each index entry, the one or more processors downgrade the page-level lock to a row-level lock.

7. The computer-implemented method according to claim 1, further comprising: The preloading function for each insert statement in the batch job is executed asynchronously by the one or more processors across the source database server and the target database server, wherein the preloading function includes: In parallel with the main task of retrieving the first set of rows, the one or more processors preload the second set of rows by using an index to locate a set of leaf pages to be used to store the second set of rows, wherein the leaf pages are defined on the table inserted by the operation.

8. The computer-implemented method according to claim 1, further comprising: In response to encountering a select statement in the batch job, one or more processors asynchronously execute a lock avoidance function across the source database server and the target database server, wherein the lock avoidance function includes: The one or more processors construct an image of the active unit recovery (UR) identifier (ID) of the batch job, wherein the image includes the low boundary and high boundary of the active UR ​​ID; In response to reading a row having a UR ID within the low and high boundaries of the image, The log records of the line are traced by the one or more processors until a version of the line with a corresponding UR ID lower than the low boundary is found; and The selection statement is executed by one or more processors using the version of the row with the corresponding UR ID below the low boundary without locking.

9. A computer-implemented method, comprising: In response to a batch job being ready to be executed on a source database server in a dual-host environment, one or more processors send a first communication between the source database server and the target database server, the first communication having a synchronization start point for when to begin executing the batch job on both the source database server and the target database server; The batch job is executed by one or more processors at the synchronization start point on both the source database server and the target database server. The pre-locking function for each update and delete statement in the batch job is executed asynchronously by the one or more processors across the source database server and the target database server using a table scan access method; In response to a commit statement from a unit of the batch job encountered by the source database server or the target database server, the one or more processors suspend the corresponding database server that encountered the commit statement. The one or more processors send a second communication between the source database server and the target database server to inquire whether another corresponding database server is ready to complete the commit statement; as well as In response to the other corresponding database server confirming that the other corresponding database server is ready to complete the commit statement, the one or more processors complete the commit statement on both the source database server and the target database server.

10. The computer-implemented method according to claim 9, further comprising: In response to a locking conflict encountered on the source database server or the target database server, the one or more processors send a communication to another corresponding database server that did not encounter the locking conflict to suspend the operation.

11. The computer-implemented method according to claim 9, further comprising: In response to an SQL error encountered on the source database server or the target database server, the one or more processors send a communication to another corresponding database server that did not encounter the SQL error to suspend the operation.

12. The computer-implemented method according to claim 9, wherein, The pre-locking function using the table scan access method includes: In parallel with the main task of locking the row on the first page to be modified by the operation, the one or more processors scan each page after the first page for the row to be modified by the operation; The one or more processors lock the lines to be modified on each page following the first page; and In response to the determination that the number of rows to be locked within a page exceeds a preset threshold, the one or more processors acquire a page lock on the page.

13. The computer-implemented method according to claim 9, further comprising: The preloading function for each insert statement in the batch job is executed asynchronously by the one or more processors across the source database server and the target database server, wherein the preloading function includes: In parallel with the main task of retrieving the first set of rows, the one or more processors preload the second set of rows by using an index to locate a set of leaf pages to be used to store the second set of rows, wherein the leaf pages are defined on the table inserted by the operation.

14. The computer-implemented method according to claim 9, further comprising: In response to encountering a select statement in the batch job, one or more processors asynchronously execute a lock avoidance function across the source database server and the target database server, wherein the lock avoidance function includes: The one or more processors construct an image of the active unit recovery (UR) identifier (ID) of the batch job, wherein the image includes the low boundary and high boundary of the active UR ​​ID; In response to reading a row having a UR ID within the low and high boundaries of the image, The log records of the line are traced by the one or more processors until a version of the line with a corresponding UR ID lower than the low boundary is found; and The selection statement is executed by one or more processors using the version of the row with the corresponding UR ID below the low boundary without locking.

15. A computer-implemented method, comprising: In response to a batch job being ready to be executed on a source database server in a dual-host environment, one or more processors send a first communication between the source database server and the target database server, the first communication having a synchronization start point for when to begin executing the batch job on both the source database server and the target database server; The batch job is executed by one or more processors at the synchronization start point on both the source database server and the target database server. The one or more processors asynchronously execute a pre-locking function for each update and delete statement in the batch job using an index-only access method across the source database server and the target database server; In response to a commit statement from a unit of the batch job encountered by the source database server or the target database server, the one or more processors suspend the corresponding database server that encountered the commit statement. The one or more processors send a second communication between the source database server and the target database server to inquire whether another corresponding database server is ready to complete the commit statement; as well as In response to the other corresponding database server confirming that the other corresponding database server is ready to complete the commit statement, the one or more processors complete the commit statement on both the source database server and the target database server.

16. The computer-implemented method according to claim 15, further comprising: In response to a locking conflict encountered on the source database server or the target database server, the one or more processors send a communication to another corresponding database server that did not encounter the locking conflict to suspend the operation.

17. The computer-implemented method according to claim 15, further comprising: In response to an SQL error encountered on the source database server or the target database server, the one or more processors send a communication to another corresponding database server that did not encounter the SQL error to suspend the operation.

18. The computer-implemented method according to claim 15, wherein, The pre-locking function using the index-only access method includes: This is done in parallel with the main task of locking the row on the first page that needs to be modified by the operation using an index. The one or more processors use the information in the index entries to look up the desired item on each page after the first page. The rows modified by the operation, wherein each index entry includes a key value and a row identifier (ID), wherein the row ID includes a partition number, a data page number, and a slot number, and wherein the data page number is used to locate and determine how many rows to modify on each page; The one or more processors lock the lines to be modified on each page following the first page; and In response to the determination that the number of rows to be locked within the page exceeds a preset threshold based on the number of page number entries, the one or more processors acquire a page lock on the page.

19. The computer-implemented method according to claim 15, further comprising: The preloading function for each insert statement in the batch job is executed asynchronously by the one or more processors across the source database server and the target database server, wherein the preloading function includes: In parallel with the main task of retrieving the first set of rows, the one or more processors preload the second set of rows by using an index to locate a set of leaf pages to be used to store the second set of rows, wherein the leaf pages are defined on the table inserted by the operation.

20. The computer-implemented method according to claim 15, further comprising: In response to encountering a select statement in the batch job, one or more processors asynchronously execute a lock avoidance function across the source database server and the target database server, wherein the lock avoidance function includes: The one or more processors construct an image of the active unit recovery (UR) identifier (ID) of the batch job, wherein the image includes the low boundary and high boundary of the active UR ​​ID; In response to reading a row having a UR ID within the low and high boundaries of the image, The log records of the line are traced by the one or more processors until a version of the line with a corresponding UR ID lower than the low boundary is found; and The selection statement is executed by one or more processors using the version of the row with the corresponding UR ID below the low boundary without locking.

21. A computer program product, comprising: One or more computer-readable storage media and program instructions commonly stored on the one or more computer-readable storage media, the stored program instructions including: In response to a batch job being ready to be executed on a source database server in a dual-host environment, a program instruction for a first communication is sent between the source database server and the target database server, the first communication having a synchronization start point for when to begin executing the batch job on both the source database server and the target database server; At the synchronization start point, the program instructions for executing the batch job are initiated on both the source database server and the target database server. In response to a submission statement from the source database server or the target database server that encounters a unit of the batch job, the program instruction of the corresponding database server that encountered the submission statement is suspended. A second communication is sent between the source database server and the target database server to inquire whether another corresponding database server is ready to complete the program instructions for the submission statement; and In response to the other corresponding database server confirming that the other corresponding database server is ready to complete the commit statement, program instructions are executed on both the source database server and the target database server to complete the commit statement.

22. The computer program product according to claim 21, further comprising: In response to a locking conflict encountered on the source database server or the target database server, a program instruction is sent to another corresponding database server that has not encountered the locking conflict to suspend the operation.

23. A computer system, comprising: One or more computer processors; One or more computer-readable storage media; Program instructions jointly stored on the one or more computer-readable storage media for execution by at least one of the one or more computer processors, the stored program instructions including: In response to a batch job being ready to be executed on a source database server in a dual-host environment, a program instruction for a first communication is sent between the source database server and the target database server, the first communication having a synchronization start point for when to begin executing the batch job on both the source database server and the target database server; At the synchronization start point, the program instructions for executing the batch job are initiated on both the source database server and the target database server. In response to a submission statement from the source database server or the target database server that encounters a unit of the batch job, the program instruction of the corresponding database server that encountered the submission statement is suspended. A second communication is sent between the source database server and the target database server to inquire whether another corresponding database server is ready to complete the program instructions for the submission statement; and In response to the other corresponding database server confirming that the other corresponding database server is ready to complete the commit statement, program instructions are executed on both the source database server and the target database server to complete the commit statement.

24. The computer system according to claim 23, further comprising: In response to a locking conflict encountered on the source database server or the target database server, a program instruction is sent to another corresponding database server that has not encountered the locking conflict to suspend the operation.

Images