Dry run capability for determining whether data definition language statements can be executed with zero down time
A computer program product, system, and computer implemented method for a dry run capability for determining whether data definition language statements can be executed with zero down time. This generally comprise using a dry run method to determine what algorithm would be used with SQL data definition language statements (DDL). Specifically, The DDL statement(s) are processed but are not permitted to modify a corresponding SQL database. Such statements are processed up to the point where an algorithm to be used for the execution of such a statement can be confirmed (whether specified or based on a hierarchy of approaches). In some embodiments, the approach includes both a primary SQL database and a secondary SQL database where commands that modify the primary SQL database are reproduced at the replica SQL database but commands that are executed using a dry run method are not executed against the replica database.
1 . A computer-implemented method, comprising:
maintaining a first database and a second database, wherein the first database is associated with a structured query language (SQL) database frontend, and the second database is a replica of the first database;
receiving a data definition language (DDL) command at the SQL database frontend to be processed using a dry run mode, wherein commands executed using the dry run mode are not permitted to modify the first database, and an algorithm that would be used to execute the DDL command is identified using the dry run mode;
determining, based on the identified algorithm, whether to execute the DDL command; and
in response to a determination to execute the DDL command:
executing the DDL command at the first database, wherein the identified algorithm is used to execute the DDL command at the first database; and
executing the DDL command at the second database, wherein the DDL command is executed without specifying the algorithm to be used at the second database.
2 . The computer-implemented method of claim 1 , wherein the DDL command is executed at the first database without specifying the algorithm to be used and the DDL command is executed at the second database using the same algorithm.
3 . The computer-implemented method of claim 1 , wherein the first database has a first schema and the second database has a second schema that is different from the first schema.
4 . The computer-implemented method of claim 3 , wherein the first schema comprises at least a character type specification at a level of a column of a first table and the second schema comprises a character type specified at a level of a second table, wherein the second table is a replica of the first table.
5 . The computer-implemented method of claim 1 , further comprising:
receiving a dry run mode command at the SQL database frontend; and
setting a parameter to indicate that the SQL database frontend is in a dry run mode.
6 . The computer-implemented method of claim 5 , wherein the parameter is cleared in response to completing processing of the DDL command.
7 . The computer-implemented method of claim 5 , wherein the DDL command comprises at least a value that indicates that the command is to be processed using a dry run mode.
8 . The computer-implemented method of claim 5 , wherein the DDL command comprises an ALTER TABLE command that specifies the use of an instant algorithm or an in-place algorithm.
9 . A non-transitory computer readable medium having stored thereon a sequence of instructions which, when executed by a processor, cause a set of acts comprising:
maintaining a first database and a second database, wherein the first database is associated with a structured query language (SQL) database frontend, and the second database is a replica of the first database;
receiving a data definition language (DDL) command at the SQL database frontend to be processed using a dry run mode, wherein commands executed using the dry run mode are not permitted to modify the first database, and an algorithm that would be used to execute the DDL command is identified using the dry run mode;
determining, based on the identified algorithm, whether to execute the DDL command; and
in response to a determination to execute the DDL command:
executing the DDL command at the first database, wherein the identified algorithm is used to execute the DDL command at the first database; and
executing the DDL command at the second database, wherein the DDL command is executed without specifying the algorithm to be used at the second database.
10 . The computer readable medium of claim 9 , wherein the DDL command is executed at the first database without specifying the algorithm to be used and the DDL command is executed at the second database using the same algorithm.
11 . The computer readable medium of claim 9 , wherein the first database has a first schema and the second database has a second schema that is different from the first schema.
12 . The computer readable medium of claim 11 , wherein the first schema comprises at least a character type specification at a level of a column of a first table and the second schema comprises a character type specified at a level of a second table, wherein the second table is a replica of the first table.
13 . The computer readable medium of claim 9 , wherein the set of acts further comprise:
receiving a dry run mode command at the SQL database frontend; and
setting a parameter to indicate that the SQL database frontend is in a dry run mode.
14 . The computer readable medium of claim 13 , wherein the parameter is cleared in response to completing processing of the DDL command.
15 . The computer readable medium of claim 13 , wherein the DDL command comprises at least a value that indicates that the command is to be processed using a dry run mode.
16 . The computer readable medium of claim 13 , wherein the DDL command comprises an ALTER TABLE command that specifies the use of an instant algorithm or an in-place algorithm.
17 . A computing system comprising:
a memory to hold a set of instructions;
a computer processor to execute the set of instructions, which when executed cause a set of acts comprising:
maintaining a first database and a second database, wherein the first database is associated with a structured query language (SQL) database frontend, and the second database is a replica of the first database;
receiving a data definition language (DDL) command at the SQL database frontend to be processed using a dry run mode, wherein commands executed using the dry run mode are not permitted to modify the first database, and an algorithm that would be used to execute the DDL command is identified using the dry run mode;
determining, based on the identified algorithm, whether to execute the DDL command; and
in response to a determination to execute the DDL command:
executing the DDL command at the first database, wherein the identified algorithm is used to execute the DDL command at the first database; and
executing the DDL command at the second database, wherein the DDL command is executed without specifying the algorithm to be used at the second database.
18 . The computing system of claim 17 , wherein the DDL command is executed at the first database without specifying the algorithm to be used and the DDL command is executed at the second database using the same algorithm.
19 . The computing system of claim 17 , wherein the first database has a first schema and the second database has a second schema that is different from the first schema.
20 . The computing system of claim 19 , wherein the first schema comprises at least a character type specification at a level of a column of a first table and the second schema comprises a character type specified at a level of a second table, wherein the second table is a replica of the first table.