Systems and methods for efficient DB2 outage operations
View Patent ↗In one aspect, a computer-implemented method provides more efficient outages for databases. The method may include intercepting a database transaction request prior to connection to the database subsystem and determining whether the transaction will finish before an expected drain time for the database object, the transaction being associated with the database object. The method may also include allowing the transaction to proceed when it is determined that the transaction will finish before the expected drain time and suspending the transaction otherwise. The transaction may be associated with the database object in a table so that only transactions associated with the database object are considered for suspension. In another aspect, the method may include determining which transactions running when a drain for the object is initiated and associating the running transactions with the database object in the table.
1. A computer system for reduced database outage time comprising:
a database subsystem, the database subsystem having at least one database object;
at least one processor; and
a memory storing instructions that, when executed by the at least one processor, cause the computer system to perform operations including:
intercepting a transaction prior to connection to the database subsystem,
determining whether the transaction will finish before an expected drain time for a database object, the transaction being associated with the database object,
allowing the transaction to proceed when it is determined that the transaction will finish before the expected drain time, and
suspending the transaction when it is determined that the transaction will not finish before the expected drain time.
2. The system of claim 1 , wherein the determining is performed after an outage start message for the database object is received from an outage engine.
3. The system of claim 2 , wherein the expected drain time is determined based on a time that the outage start message is received and a lead time.
4. The system of claim 1 , wherein determining whether the transaction will finish includes:
determining a historical transaction time for the transaction; and
using the historical transaction time, the current time, and a buffer time to determine whether the transaction will finish.
5. The system of claim 4 , wherein determining whether the transaction will finish includes:
determining whether the transaction is eligible to run on a protected thread, the protected thread holding the database object for a predetermined time after a particular transaction ends; and
using the predetermined time to determine whether the transaction will finish.
6. The system of claim 4 , wherein determining whether the transaction will finish before the expected drain time includes:
determining whether the transaction is eligible to run on a protected thread, the protected thread holding the database object for a predetermined time after a particular transaction ends; and
unprotecting the protected thread when it is determined that the transaction is eligible, wherein determining whether the transaction will finish occurs without consideration of the predetermined time.
7. The system of claim 1 , wherein the memory stores instructions that, when executed by the at least one processor, further cause the computer system to perform operations including:
determining whether the transaction is associated with the database object in a hash table; and
allowing the transaction to proceed when it is determined that the transaction is not associated with the database object in the hash table.
8. The system of claim 7 , wherein the memory stores instructions that, when executed by the at least one processor, further cause the computer system to perform operations including:
receiving a list of transaction codes for transactions running when a drain request issued; and
associating the transaction codes with the database object in an object/transaction cross-reference table.
9. The system of claim 7 , wherein the memory stores instructions that, when executed by the at least one processor, further cause the computer system to perform operations including:
receiving a list of transaction codes for transactions running when a drain request has waited for a predetermined amount of time; and
associating the transaction codes with the database object in an object/transaction cross-reference table.
10. The system of claim 7 , wherein the memory stores instructions that, when executed by the at least one processor, further cause the computer system to perform operations including:
receiving at least one transaction code for the database object from a user; and
associating the at least one transaction code with the database object in an object/transaction cross-reference table.
11. The system of claim 1 , wherein the memory stores instructions that, when executed by the at least one processor, further cause the computer system to perform operations including:
receiving an outage registration request; and
responsive to receiving the outage registration request, providing a lead time to a requestor of the outage registration request.
12. The system of claim 1 , wherein the memory stores instructions that, when executed by the at least one processor, further cause the computer system to perform operations including:
receiving an outage registration request; and
responsive to receiving the outage registration request, loading transaction codes associated with the database object into a hash table and calculating a suspend start time for each transaction code loaded into the hash table,
wherein the suspend start time is used to determine whether the transaction will finish before the expected drain time.
13. A method for efficient database outage operations, the method comprising:
intercepting, within a database client program, a transaction;
determining, by at least one processor, whether the transaction is associated with a database object and whether the transaction will finish before an expected drain time for the database object arrives;
allowing the transaction to proceed when it is determined that the transaction is not associated with the database object or that the transaction will finish before the expected drain time arrives; and
suspending the transaction prior to assignment of a database connection thread when it is determined that the transaction is associated with the database object and will not finish before the expected drain time arrives.
14. The method of claim 13 , wherein the expected drain time is determined based on a time that an outage begin request is received from the database and a lead time.
15. The method of claim 13 , wherein determining whether the transaction will finish includes:
retrieving a historical transaction time for the transaction; and
using the historical transaction time, the current time, and a buffer time to determine whether the transaction will finish.
16. The method of claim 15 , wherein determining whether the transaction will finish includes:
determining that the transaction is eligible to run on a protected thread, the protected thread holding the database object for a predetermined time after a particular transaction ends; and
setting the buffer time to a value at least as large as the predetermined time.
17. The method of claim 15 , wherein determining whether the transaction will finish before the expected drain time includes:
determining whether the transaction is eligible to run on a protected thread, the protected thread holding the database object for a predetermined time after a particular transaction ends; and
unprotecting the protected thread when it is determined that the transaction is eligible, wherein determining whether the transaction will finish occurs without consideration of the predetermined time.
18. The method of claim 13 , further comprising:
determining whether the transaction is associated with the database object in a hash table; and
allowing the transaction to proceed when it is determined that the transaction is not associated with the database object in the hash table.
19. The method of claim 18 , further comprising:
receiving a list of transaction codes for transactions that caused a drain request to wait; and
associating the transaction codes with the database object in an object/transaction cross-reference table.
20. A computer system comprising:
a database subsystem;
at least one database client program;
at least one processor; and
memory storing instructions that, when executed by the at least one processor, cause the computer system to perform operations including:
sending, from an outage engine, a database outage registration message to at least one database client program, the database outage registration message identifying a database object subject to the outage,
receiving, at the outage engine, a lead time from the at least one database client program,
sending an outage start message to the database client program when a drain request is expected within the lead time, the outage start message causing an intercept engine for the database client program to begin intelligently suspending transactions,
initiating a drain for the database object, and
sending a drain complete message to the database client program when the drain is complete, the drain complete message causing the intercept engine to release the suspended transactions.
21. The computer system of claim 20 , the memory storing instructions that, when executed by the at least one processor, cause the computer system to further perform operations comprising:
determining that at least one transaction is running after a drain start request; and
sending a transaction code for the transaction to the at least one intercept engine,
wherein the intercept engine associates the transaction code with the database object.
22. The computer system of claim 20 , wherein the at least one intercept engine suspends the transactions prior to assignment to a connection thread for the database subsystem.