Claims:
1. A method for determining issues in dynamic queries, preferably in dynamic Structured Query Language (SQL) query, the method comprising:
identifying at least one database object present in at least one database, the one database object comprise at least one dynamic query;
forming at least one query set;
determining presence of at least an issue associated with the query set formed; and
reporting the issue associated with the query set formed.

2. The method as claimed in claim 1, wherein the query set is formed based on at least one input parameter associated with the database object identified.

3. The method as claimed in claim 2, wherein the database object capable of forming the query dynamically comprise at least one function and/or at least one procedure, the function and/or the procedure preferably contain at least one “execute immediate” logic or “open cursor” logic or any combination thereof

4. The method as claimed in claim 1, wherein the query set for the database object identified is stored in at least one table based on at least one input parameter associated with function/procedure of the database object, the table comprise an information associated with the input parameter and is preferable at least one of: an input name, a position, an input value, a data type, whether input parameter is mandatory or not, an database object name (function/procedure), a name of the query set formed, or any combination thereof.

5. The method as claimed in claim 2 and 4, wherein: if the query set include the database object forming the query dynamically and if the query set is not configured for any dynamic object, the method further comprises:
creating at least one query set to be stored in the table preferably having all input parameters as optional and thereby
fetching the database object for input parameters (optional parameters) for at least one possible combination.

6. The method as claimed in claim 1, wherein the issue associated with the query set formed is at least one of: a syntax issue, a functional/performance hint related issue, a query plan related issue, a dynamic sql query plan comparison issue, or any combination thereof, with other database for any query which is formed based on an input parameter combination defined for the database object.

7. The method as claimed in claim 1, wherein before determining the issue associated with the query set formed, the method further comprise:
determining if the issue from the database object from the query set selected to determine the issue requires validation for all the checks; and
disabling the database object if no validation is required for all checks; or
determining presence of the issue associated with the query set formed.

8. The method as claimed in claim 1, wherein the presence of the issue is determined by:
validating at least one function and/or at least one procedure associated with the database object for each combination (query set) and thereby storing the result of validation, preferably, with at least an exception flag 0, wherein any value other than or greater than 0 indicating presence of the ISSUE, and “0” indicating presence of NO ISSUE.

9. The method as claimed in claim 1, comprise: storing at least the issue determined associated with the query set formed in at least one report, preferably a report table.

10. A method for determining issues in dynamic query statement, preferably in dynamic Structured Query Language (SQL) statement, the method comprising:
accessing at least one database comprising a plurality of database objects;
identifying at least one database object having at least one dynamic Structured Query Language (SQL) statement;
identifying at least one input parameters combination for the database object identified;
forming at least one query statement based on the input parameters identified and the database object identified;
determining presence of at least an issue associated with the dynamic query statement formed;
reporting the issue determined for the dynamic query statement.

11. The method as claimed in claim 10, comprise: storing at least the issue determined associated with the query set formed in at least one report, preferably a report table.

12. A database management system to determine issues in dynamic query statement, preferably in dynamic Structured Query Language (SQL) statement, the database management system comprising:
a processor;
a memory coupled to the processor for executing a plurality of modules present in the memory, the plurality of modules comprising:
at least one database comprising a plurality of database objects;
an identification module configured to identify at least one database object present in at least one database, the one database object comprise at least one dynamic query;
a dynamic query formation module configured to form at least one query set based on at least one input parameter associated with the database object identified;
a determining module configured to determine presence of at least an issue associated with the query set formed; and
a reporting module configured to report the issue associated with the query set formed.

13. The database management system as claimed in claim 12, wherein the database object is capable of forming at least one query dynamically (dynamic query).

14. The database management system as claimed in claim 13, wherein the database object capable of forming the query dynamically comprise at least one function and/or at least one procedure, the function and/or the procedure preferably contain at least one “execute immediate” logic or “open cursor” logic or any combination thereof

15. The database management system as claimed in claim 12, further comprises at least one storage module configured to store the query set for the database object identified is in at least one table based on at least one input parameter associated with function/procedure of the database object, the table comprise an information associated with the input parameter and is preferable at least one of: an input name, a position, an input value, a data type, whether input parameter is mandatory or not, an database object name (function/procedure), a name of the query sent formed, or any combination thereof.

16. The database management system as claimed in claim 13 and 15, further comprises:
a creating module, if the query set include the database object forming the query dynamically and if the query set is not configured for any dynamic object, the database management system, configured to create at least one query set to be stored in the table preferably having all input parameters as optional; and
a fetching module configured to fetch the database object for input parameters (optional parameters) for at least one possible combination.

17. The database management system as claimed in claim 12, wherein the issue associated with the query set formed is at least one of: a syntax issue, a functional/performance hint related issue, a query plan related issue, a dynamic sql query plan comparison issue, or any combination thereof, with other database for any query which is formed based on an input parameter combination defined for the database object.

18. The database management system as claimed in claim 12, wherein the determining module, before determining the issue associated with the query set formed, is configured to:
determine if the issue from the database object from the query set selected to determine the issue requires validation for all the checks; and thereby
disable the database object if no validation is required for all checks; or
determine presence of the issue associated with the query set formed.

19. The database management system as claimed in claim 12, further comprises a validation module configured to determine the presence of the issue by validating at least one function and/or at least one procedure associated with the database object for each combination (query set) and thereby storing the result of validation, preferably, with at least an exception flag 0, wherein any value other than or greater than 0 indicating presence of the ISSUE, and “0” indicating presence of NO ISSUE.

20. The database management system as claimed in claim 12 further comprises: at least one storage module configured to store at least the issue determined associated with the query set formed in at least one report, preferably a report table.

21. A database management system to determine issues in dynamic query statement, preferably in dynamic Structured Query Language (SQL) statement, the database management system comprising:
a processor;
a memory coupled to the processor for executing a plurality of modules present in the memory, the plurality of modules comprising:
an accessing module configured to access at least one database comprising a plurality of database objects;
an identification module configured to identify:
at least one database object having at least one dynamic Structured Query Language (SQL) query statement; and
at least one input parameters combination for the database object identified;
a dynamic query formation module configured to form at least one query statement based on the input parameters identified and the database object identified;
a determining module configured to determine presence of at least an issue associated with the dynamic query statement formed;
a reporting module configured to report the issue determined for the dynamic query statement formed.

22. The database management system as claimed in claim 21, further comprises at least one storage module configured to store at least the issue determined associated with the query set formed in at least one report, preferably a report table.

, Description:TECHNICAL FIELD
The present subject matter described herein, in general, relates to database technologies or database management systems, and more particularly, to systems and methods for identifying issues in dynamic SQL for promoting dynamic SQL quality.

BACKGROUND
In almost all the software applications there is at least one front end and at least one backend layer. The backend layer application data is majorly stored in databases (DB) like Oracle, Sybase, SQL Server, DB2, and the like databases. In every database there are many DB programming objects which would be using a static Structured Query Language (SQL) and/or a dynamic SQL. SQL is a special-purpose programming language designed for managing data held in a relational database management system (RDBMS), or for stream processing in a relational data stream management system (RDSMS). The SQLs statements which are static in the program are called static SQL i.e. they do not change each time the program is running. These statements are complied when the program is complied. The SQLs statements which are dynamic in the program are called dynamic SQL as they will change each time when the program is running. These statements are compiled at run time. Dynamic SQL is a programming technique that enables you to build SQL statements dynamically at runtime. You can create more general purpose, flexible applications by using dynamic SQL because the full text of a SQL statement may be unknown at compilation. For example, dynamic SQL lets you create a procedure that operates on a table whose name is not known until runtime.

Dynamic SQL is very common in application systems that allow users to input or choose query search or sorting criteria at runtime to search the data by selecting among many possible criteria’s. This type of application requirements can be meet by using dynamic SQL because the full text of a dynamic SQL statement will be unknown during the compilation of database programming objects (i.e. functions/procedures) only at runtime of program full text of a SQL statement will be known. However, the dynamic SQL is significantly more complicated than static SQL and requires more much meticulous programming skills as compared to static SQL. The problem revolves around the fact that it changes based on the inputs received from front end. This means that in order to avoid issues and/or problems in the programming code, the code needs to be as robust as possible. So if there are any issues and/or problems in the code only during runtime it would be found. Also while in the code correction phase it is always required to make sure that while fixing some issue and/or problems any existing code is not broken or changed or altered.

In order to avoid the above discussed problems, conventionally, for the functions/procedures of the programming code having dynamic queries a manual intervention is required in order to verify whether the dynamic query formed are proper or not for each input criteria. It means, in a mathematical computation manner, if a function is having N input parameters then the function needs to be manually executed nCr times for all the possible combinations. Till date, there is no automatic mechanism to identify these combinations. All these combinations are identified manually. Further, if a problem in particular execution case is found and rectified, a manual verification is required again not only for the input scenario of the particular execution but also the other scenarios of the remaining execution, as the other scenarios may also be impacted because of this code change. Hence, the identification of these impacted cases is also done manually. If all the combinations of the queries are working fine (for example, syntax wise, hint wise and query plan wise) needs to be verified manually for each combination of queries. Also a comparison of the query plan for all these SQL in other database is required then again the same steps of generating the queries, checking the query plan in this database and then compare the dynamic sql query plan with other database is repeated with high manual intervention.

Further, it is also noted in the conventional mechanism that, in database application, objects will be changed frequently based on business needs and hence it is difficult to repeatedly verify the base functionally of modified objects to make sure dynamic SQLs are not impacted. If any SQL issue is found while function/procedure testing then these issues are to be corrected manually and after correction again the function/procedure testing needs to re-executed for all the possible test scenarios manually to make sure the fix or issues resolved is not impacting any other existing dynamic SQLs. It is very difficult to follow the above manual verification mechanism for all the database objects having dynamic SQLs in repeated manner, and in the manual verification process there would be chance of missing some test scenarios (some input (IN) parameters combination) which might give dynamic SQLs compilation error.

For example, if in a function there are 12 input parameters then to manually execute 4096 queries which gets formed considering each combination is practically a very tedious work and assuring that for all these combinations everything (Syntax issue, checking the functional and performance hint issues in SQL and Query Plan issue) has been verified without any issue is also difficult to guarantee. Also, most of the times when the query execution plan is analyzed it look fine in one particular environment (operating system (OS) + DB version) but when the same plan is checked in some other environment with different OS+ DB flavor it behaves differently. Hence, this manual activity becomes very challenging for dynamic SQL query plan to be compared in multiple environments whether it is the same or behaving differently. Further, the complete process of forming the combinations, executing the cases, analyzing the impacted cases, checking the functional and performance hint issues in SQL, query plan, dynamic sql query plan comparison with other database and running the combination sets for any dynamic function is completely manual.

The above-described deficiencies of today's query execution techniques are merely intended to provide an overview of some of the problems of conventional systems / mechanism / techniques, and are not intended to be exhaustive. Other problems with conventional systems/mechanism/techniques and corresponding benefits of the various non-limiting embodiments described herein may become further apparent upon review of the following description.

SUMMARY
This summary is provided to introduce concepts related to systems and methods for identifying issues in dynamic SQL for promoting dynamic SQL quality, and the same are further described below in the detailed description. This summary is not intended to identify essential features of the claimed subject matter nor is it intended for use in determining or limiting the scope of the claimed subject matter.

A main objective of the present invention is to solve the technical problem as recited above by providing system and method for finding issues associated with dynamic SQLs.

Another object of the present invention is to system and method for automatically checking all the combinations of a particular function/procedure irrespective of optional or mandatory input parameters for all the queries and make sure that everything is working fine.

Accordingly, in one implementation, the present invention provides a mechanism to find all the functions/procedure having dynamic SQL in a particular database and then for each of this object all the possible scenarios/combinations are identified automatically based on number of input (IN) parameters. The mechanism then validates the function/procedure for each combination and stores the results with exception flag, preferably, with the exception flag 0, any value other than or greater than 0 indicating presence of the issue, and “0” indicating presence of no issue. If hint related issues, queries plan issues, and the dynamic sql query plan comparison with other database is required to be found out, the mechanism finds the SQL having these issues which are further logged in a report table, which makes it easy to find if there are any dynamic SQLs issues in a function/ procedure as well as to keep the track of the same.

In one implementation, a framework is disclosed which automatically forms all possible sets or combinations of queries based on input parameters of function/procedure fetched/received from the database, and automatically finds if there are any syntax issues, functional/performance hint related issues, query plan related issues and dynamic sql query plan comparison issues with other database for any query which is formed based on the input parameter combination defined for that database programming object fetched/received. The framework stores all the details in a table along with object name and other environment details with which the dynamic sql query plan needs to be compared and evaluated. After fixing all the issues stored in the table there is no need to check manually whether that issues are fixed properly or not, but the framework need to run a task to find and confirm automatically if all issues have been rectified properly or not for the objects.

If the framework does not receive any inputs (input parameters) for the function/procedure having dynamic query then the framework by default based on the input parameters to the function/procedure take default values based on the data type and run the framework tasks for the function/procedure.

In one implementation, a database management system to determine issues in dynamic query statement, preferably in dynamic Structured Query Language (SQL) statement is disclosed. The database management system comprises a memory coupled to the processor for executing a plurality of modules present in the memory. The plurality of modules comprises an identification module, a dynamic query formation module, a determining module, and a reporting module. The memory further comprises at least one database comprising a plurality of database objects. The identification module is configured to identify at least one database object present in at least one database, the one database object comprise at least one dynamic query. The dynamic query formation module is configured to form at least one query set based on at least one input parameter associated with the database object identified. The determining module configured to determine presence of at least an issue associated with the query set formed. The reporting module configured to report the issue associated with the query set formed.

In one implementation, a database management system to determine issues in dynamic query statement, preferably in dynamic Structured Query Language (SQL) statement is disclosed. The database management system comprises a memory coupled to the processor for executing a plurality of modules present in the memory. The plurality of modules comprises an accessing module, an identification module, a dynamic query formation module, a determining module, a reporting module. The accessing module configured to access at least one database comprising a plurality of database objects. The identification module configured to identify at least one database object having at least one dynamic Structured Query Language (SQL) statement; and at least one input parameters combination for the database object identified. The dynamic query formation module configured to form at least one query statement based on the input parameters identified and the database object identified. The determining module configured to determine presence of at least an issue associated with the dynamic query statement formed. The reporting module configured to report the issue determined for the dynamic query statement formed.

In one implementation, a method for determining issues in dynamic queries, preferably in dynamic Structured Query Language (SQL) query is disclosed. The method comprises identifying at least one database object present in at least one database, the one database object comprise at least one dynamic query; forming at least one query set based on at least one input parameter associated with the database object identified; determining presence of at least an issue associated with the query set formed; and reporting the issue associated with the query set formed.

In one implementation, a method for determining issues in dynamic query statement, preferably in dynamic Structured Query Language (SQL) statement is disclosed. The method comprises accessing at least one database comprising a plurality of database objects; identifying at least one database object having at least one dynamic Structured Query Language (SQL) statement; identifying at least one input parameters combination for the database object identified; forming at least one query statement based on the input parameters identified and the database object identified; determining presence of at least an issue associated with the dynamic object in the query statement formed; and reporting the issue determined for the dynamic query statement formed.

In contrast to the prior-art techniques, the present invention by system and method avoids a need to manually validate any function/procedure to find dynamic SQLs issues. The technical advantage of the present invention is that there is a need to prepare the query sets only one time and in future if those functions/procedures are modified then there is no need to prepare query sets again, the new function/procedure and the existing base functionality /performance hint issue/query plan with existing query sets can be verified without putting any manual efforts.

According to the prior-art, if in a function/procedure, dynamically SQL statements are being formed then there might be a chance of SQL syntax issues, functional and performance hint issues, and query plan related issues. Also if the function/procedure consists of many input parameters then the number of queries formed might be much more, and hence it would be difficult to identify/test all the possible scenarios manually for each input parameter combination. However, in contrast to the prior-art, the present invention automatically finds and verifies issues in the dynamic SQLs.

The various options and preferred embodiments referred to above in relation to the first implementation are also applicable in relation to the other implementations.

BRIEF DESCRIPTION OF THE ACCOMPANYING DRAWINGS
The detailed description is described with reference to the accompanying figures. In the figures, the left-most digit(s) of a reference number identifies the figure in which the reference number first appears. The same numbers are used throughout the drawings to refer like features and components.

Figure 1 illustrates a flow chart showing a comparison of a conventional technique of identifying, fixing, and reporting issues in dynamic SQL with the present invention.

Figure 2 illustrates a flowchart for identifying, and reporting issues in dynamic SQL, in accordance with an embodiment of the present subject matter.

Figure 3 illustrates a sample structure of the configuration table (T_CONFIG), in accordance with an embodiment of the present subject matter.

Figure 4 (a) illustrates a flowchart for the auto generation of the queries; and Figure 4 (b) illustrates an example for the auto generation of the queries, in accordance with an embodiment of the present subject matter.

Figure 5 illustrates a flowchart for identifying, fixing, and reporting issues in dynamic SQL, in accordance with an embodiment of the present subject matter.

Figure 6(a) illustrates a query plan issue identified in accordance with an embodiment of the present subject matter, and figure 6(b) illustrates a dynamic SQL query plan comparison report, in accordance with an embodiment of the present subject matter.

Figure 7 illustrates an example of the SQL query plan issue reporting, in accordance with an embodiment of the present subject matter.

Figure 8 illustrates a database management system to determine issues in dynamic query statement, preferably in dynamic Structured Query Language (SQL) statement, in accordance with an embodiment of the present subject matter.

Figure 9 illustrates a method for determining issues in dynamic query statement, preferably in dynamic Structured Query Language (SQL) statement, in accordance with an embodiment of the present subject matter.

It is to be understood that the attached drawings are for purposes of illustrating the concepts of the invention and may not be to scale.

DETAILED DESCRIPTION OF THE PRESENT INVENTION
The following clearly describes the technical solutions in the embodiments of the present invention with reference to the accompanying drawings in the embodiments of the present invention. Apparently, the described embodiments are merely a part rather than all of the embodiments of the present invention. All other embodiments obtained by a person of ordinary skill in the art based on the embodiments of the present invention without creative efforts shall fall within the protection scope of the present invention.

The invention can be implemented in numerous ways, as a process, an apparatus, a system, a composition of matter, a computer readable medium such as a computer readable storage medium or a computer network wherein program instructions are sent over optical or electronic communication links. In this specification, these implementations, or any other form that the invention may take, may be referred to as techniques. In general, the order of the steps of disclosed processes may be altered within the scope of the invention.

A detailed description of one or more embodiments of the invention is provided below along with accompanying figures that illustrate the principles of the invention. The invention is described in connection with such embodiments, but the invention is not limited to any embodiment. The scope of the invention is limited only by the claims and the invention encompasses numerous alternatives, modifications and equivalents. Numerous specific details are set forth in the following description in order to provide a thorough understanding of the invention. These details are provided for the purpose of example and the invention may be practiced according to the claims without some or all of these specific details. For the purpose of clarity, technical material that is known in the technical fields related to the invention has not been described in detail so that the invention is not unnecessarily obscured.

In the following detailed description, numerous specific details are set forth in order to provide a thorough understanding of the invention. However, it will be understood by those skilled in the art that the present invention may be practiced without these specific details. In other instances, well-known methods, procedures, and components, modules, units and/or circuits have not been described in detail so as not to obscure the invention.

Although embodiments of the invention are not limited in this regard, discussions utilizing terms such as, for example, “processing,” “computing,” “calculating,” “determining,” “establishing”, “analyzing”, “checking”, or the like, may refer to operation(s) and/or process(es) of a computer, a computing platform, a computing system, or other electronic computing device, that manipulates and/or transforms data represented as physical (e.g., electronic) quantities within the computer's registers and/or memories into other data similarly represented as physical quantities within the computer's registers and/or memories or other information non-transitory storage medium that may store instructions to perform operations and/or processes.

Although embodiments of the invention are not limited in this regard, the terms “plurality” and “a plurality” as used herein may include, for example, “multiple” or “two or more”. The terms “plurality” or “a plurality” may be used throughout the specification to describe two or more components, devices, elements, units, parameters, or the like. Unless explicitly stated, the method embodiments described herein are not constrained to a particular order or sequence. Additionally, some of the described method embodiments or elements thereof can occur or be performed simultaneously, at the same point in time, or concurrently.

The present invention provides a framework which can form all possible sets of queries based on input parameters of function/procedure and find any syntax issue, functional/performance hint related issue, query plan related issue and dynamic sql query plan comparison issue with other database for any query which is formed based on the input parameter combination defined for that database programming object. In one implementation, the framework is an automation framework which can find all the Dynamic SQL quality issues. The present invention configures the framework with all sets in configuration table (T_CONFIG) along with object name and other environment details with which the dynamic sql query plan needs to be compared and evaluated. Form all the possible queries and report all the SQL syntax issue, functional/performance hint related issue, query plan related issue and dynamic sql query plan comparison issue with other database with the detailed information about the problem. All the issues with detailed information about the problem are reported or logged by the framework.

Further, after fixing all the issues there is no need to check manually whether that issues are fixed properly or not. According to the present invention, a task of this framework is enabled to find if all issues have been rectified properly or not for the objects having the issues. It is to be noted that, this task of the framework needs to be enabled manually after fixing all the issues.

System and method for identifying issues in dynamic SQL for promoting dynamic SQL quality are disclosed.

While aspects are described for systems and methods for identifying issues in dynamic SQL for promoting dynamic SQL quality, the present invention may be implemented in any number of different computing systems, environments, and/or configurations, the embodiments are described in the context of the following exemplary systems, apparatus, and methods.

Henceforth, embodiments of the present disclosure are explained with the help of exemplary diagrams and one or more examples. However, such exemplary diagrams and examples are provided for the illustration purpose for better understanding of the present disclosure and should not be construed as limitation on scope of the present disclosure.

Referring now to figure 1, a flow chart showing a comparison of a conventional technique of identifying, fixing, and reporting issues in dynamic SQL with the present invention is illustrated. As shown in figure 1 (prior-art section), if in a function/procedure, dynamically SQL statements are getting formed then there would be chance of SQL syntax, functional & performance hint issue, query plan related issues. Also if the function/procedure is having many input parameters then the number of queries formed would be too many. It would be difficult to identify/test all the possible scenarios manually for each input parameter combination.

In contrast to the prior-art section, as shown in the figure 1, the main objective of the present invention is to find dynamic SQLs issues automatically, which is illustrated in the bottom part of the figure 1 (new solution art section). As shown in the figure 1, the present invention may run in the application database schema and find all the functions/procedure having dynamic SQL and then for each of this object, based on number of input (IN) parameters, the present invention identifies all the possible scenarios automatically. The present invention validates the function/procedure for each combination, and stores the results with exception flag, preferably, with the exception flag 0, any value other than or greater than 0 indicating presence of the issue, and “0” indicating presence of no issue. If hint related issues, queries plan related issues and dynamic sql query plan comparison related, with other database is required to be found out then that task of the present invention as shown in the figure 1 may also be run and accordingly for the SQL having the issues may be logged in a report table. This makes it easy to find if there are any dynamic SQLs issues in a function /procedure.

It is evident from the figure 1 that by the implementation of the present invention, there is no need to manually validate any function/procedure to find dynamic SQLs issues. The advantage of the present invention is that, there is need to prepare the query sets for only first time (one time) and in future if those functions/procedures are getting modified then there may not be any need to prepare Query sets again, the new function/procedure and the existing base functional /performance hint issues /query plan issues with existing Query sets can be verified without putting any manual efforts.

Referring now to figure 2, a flowchart for identifying, and reporting issues in dynamic SQL is illustrated, in accordance with an embodiment of the present subject matter. In one implementation, as shown in the figure 2, the present invention finds all database objects which are having dynamic SQLs queries. The present invention then parses all the database objects and checks which objects (functions/procedures) are having “execute immediate” or “open cursor” logic or similar logic to execute SQLs queries (i.e. queries which are dynamically getting formed). After finding all dynamic objects (functions/procedures), it is required to configure values (Query set) for dynamic objects in the configuration table (T_CONFIG) based on input parameters. If the query set is not configured for any dynamic object then by default the present invention creates one QUERYSET in the configuration table (T_CONFIG) with all input parameters as optional and then calls this particular object for input parameters (optional parameters) for all possible combination. After configuring all required Query sets for these programming objects, the present invention finds the issues. If any object is not required to be validated for all the checks then that dynamic object can be marked to disable state so that task will not pick this object.

In one implementation, for finding the issues the present invention may iterate each QUERYSET from configuration table and based on number of input parameter combination sets, task will execute each particular object for prepared combination sets. Based on input parameter passed to the object, dynamic SQLs may be formed and these dynamic SQLs will be stored in logging table (for example, say T_QUERYLOG). For each of the queries generated pre-defined hint rules would be executed to check if there any hint related issues. Further each of these queries would be picked and the plan of the query would be formed and if it falls in any of the pre-defined query plan issue category then it would be reported as an issue.

In one implementation, as shown in figure 2, if it is required to find issues for one particular database object then the particular object name (Function/Procedure name) is passed as input condition, otherwise framework would run for all the functions/procedures having dynamic queries which has been configured in the configuration table (T_CONFIG). In order to achieve this, the present invention will query all the distinct query sets for the associated object name and for each query set it will find out all the possible combination to call that function/procedure based on number of IN parameters.

Referring now to figure 3, a sample structure of the configuration table (T_CONFIG) is illustrated, in accordance with an embodiment of the present subject matter. In one implementation, a Configuration Table Usage (T_CONFIG) is shown in figure 3. The table may have 7 columns and the details of these columns are as provided below, however, it may be noted and understood by the person skilled in the art that, the columns may be altered/added/deleted based on the requirements of the applicability of the present invention and hence the structure as disclosed in the figure 3, shall not limit the protection scope of the present invention in any manner. The table as shown in the figure 3 may include, but not limited, the below details:

i. INPUTNAME: This column represents the input parameter names of a particular function/procedure.
ii. POSITION: This column represents the input parameters exact position in a particular function/procedure.
iii. INPUTVALUE: This column represents the value which needs to be passed to that object for corresponding input parameter.
iv. DATATYPE: This column represents the data type of input parameter.
v. MANDATORY: This column represents whether input parameter is mandatory or not. The value of this column can be either 1 or 0. Here 1 means input parameter is mandatory and 0 means input parameter is optional. Default value of this column will be zero. Value of this column needs to be configured based on the business needs.
vi. OBJECTNAME: This column represents the name of the function/procedure.
vii. QUERYSET: This column represents the name of the query set.

In one implementation, referring to figure 2 and 3, multiple query sets can be prepared based on the function/procedure input parameters value conditions which are used to form a dynamic SQL. If dynamic function/procedure requires multiple Queryset then it can be configured as explained in the below example. In this example two sets are prepared for F_FUNCTION_TEST function with same input parameters with different values.

In one implementation, the below formula will be applied to get the possible combination.
(nCr= n!/(r!(n-r)!) ) * Q For 0<= r <=n
Here “n” is a count of optional IN parameters of a particular function/procedure.
“r” value will be from 0 to n value.
“Q” value will be number of distinct QUERYSET for each object.

In F_FUNCTION_TEST function for each QUERYSET there are total 18 IN parameters and out of that 6 IN parameters are mandatory and remaining 12 IN parameters are optional. So the number of possible combinations for each QUERYSET will be
12C0+12C1+12C2+12C3+12C4+12C5+12C6+12C7+12C8+12C9+12C10+12C11+12C12=4096 (Total number of combinations for one QUERYSET).

It means function F_FUNCTION_TEST would be called 4096 times for one QUERYSET and since there are two QUERYSET prepared for F_FUNCTION_TEST so total number of F_FUNCTION_TEST calls will be 8192 (4096*2).

15. For example, Function F1 is having 4 IN parameters (i.e. A,B,C,D) and in these 4 IN parameters 1 IN parameter is mandatory(i.e. C is mandatory) so the possible combination to test the F1 function is: 3C0+3C1+3C2+3C3=8

F1 (A, B, C, D) - Calling F1 function with all the IN parameters value.
F1 (, B, C, D) - Calling F1 function with 2nd, 3rd and 4th IN parameters value only.
F1 (A, , C, D) - Calling F1 function with 1st ,3rd and 4th IN parameters value only.
F1 (A, B, C, ) - Calling F1 function with 1st ,2nd and 3rd IN parameters value only.
F1 ( , , C, D) - Calling F1 function with 3rd and 4th IN parameters value only
F1 ( , B, C,) - Calling F1 function with 2nd and 3rd IN parameters value only
F1 (A, , C, ) - Calling F1 function with 1st and 3rd IN parameters value only.
F1 ( , ,C, ) - Calling F1 function with 3rd IN parameters value only.

Referring now to figure 4(a) a flowchart for the auto generation of the queries is illustrated, in accordance with an embodiment of the present subject matter. In one implementation, the above example may also be practiced as an algorithm for the implementation of the present invention.

Referring now to figure 4 (b) an example for the auto generation of the queries is illustrated in accordance with an embodiment of the present subject matter. In one implementation, as shown in figure 4 (b) and above example, 3rd IN parameter(C) is mandatory so while calling F1 function C is always getting passed and optional IN parameters are getting passed for each combination whose value will be taken from configuration table(T_CONFIG). The parameters which are not getting passed those parameters value will be passed as null.

The figure 5 illustrates a flowchart for identifying, fixing, and reporting issues in dynamic SQL, in accordance with an embodiment of the present subject matter. In one implementation, after connecting to the database, the framework identifies the database objects which have dynamic SQLs, and for these objects QUERYSETs will be prepared in configuration table. The present invention provides an option to execute framework for one or all objects. The present invention may be used to process one object and then may be opted object name which will be passed to the task or optionally the present invention may execute all the identified dynamic objects.

In one implementation, the present invention iterates each QUERYSET from configuration table and based on number of input parameter combination sets, the present invention will execute each particular object for prepared combination sets. Based on input parameter passed to the object, dynamic SQLs will be formed and these dynamic SQLs will be stored in logging table (T_QUERYLOG). For each of the queries generated a pre-defined hint rules would be executed to check if there any hint related issues. Further each of these queries would be picked and the plan of the queries would be formed and if it falls in any of the pre-defined query plan issue category then it would be reported as an issue. The same process is followed if there are multiple QUERYSETs for a particular object. The present invention provides an option to compare dynamic sql query plan with another configured database to find dynamic sql query plan differences. If this option is enabled and there is any difference in query plan found then this issue would be logged in to the report table.

In one implementation, the present invention, calculates all the possible combination of IN parameters for each QUERYSET of a programming object and it will test the object automatically for all possible IN parameters combinations and finally it will store the execution result in an exception logging table (T_QUERYLOG). This logging table will have below columns:

SQLID –Assign a unique identifier to each of the dynamically generated SQL
OBJECTNAME: This column represents the name of the function/procedure
SQLQUERY – Stores dynamically formed SQL query
SETNAME – Stores which QUEYRSET is executed.
INPUTPARAMS –To store IN parameters with values so that it will be easy to identify for which combination dynamic SQL is getting formed.
EXCEPTIONFLAG – To identify dynamic SQL is having any issues or not. (i.e.0-No issue, 1- Syntax Issue, 2 – Hint Issue, 3 –Query Plan Issue, 4- Dynamic Sql Query Plan Comparison Issue)

In one example, there are “N” number of different types of issues which can come during the dynamic SQL execution like Nested Strings, Spacing and Formatting, Quote string and many more. Some of them have been mentioned for illustration purpose. There are two tables t_test and t_test1 having columns Col1, Col2 and Col2, Col3 respectively.

Table_Name Col1 Col2
t_test 20 30
t_test 1 2

Table_Name Col2 Col3
t_test1 30 40
t_test1 3 3

In one implementation, below mentioned are some of the queries which has been formed to illustrate the solution:

From_clause Where_clause SQL executed Error Failure Reason
‘select * from t_test, t_test1’ ‘ where col2=2’ select * from t_test,t_test1 where col2=2 Column ambiguously defined error As col2 is there in both the tables and in where condition it is not mentioned from which table it should select the column,
‘select * from t_test a, t_test1 b’ ‘ where b.col1 = 2’ select * from t_test a,t_test1 b where b.col1=2 Invalid identifier error As col1 is present in t_test table and not in t_test1 table
‘select * from t_test a, t_test1 b’ ‘where col1 = 2’ select * from t_test a,t_test1 bwhere col1 = 2 SQL command not properly ended As there is no space between alias and where keyword

In one implementation, the errors would be stored in the logging table once the function forming the above Queries is executed:

SQL query Input parameters EXCEPTIONFLAG SETNAME
select * from t_test,t_test1 where col2=2 i_col2 = 2 1 SET1
select * from t_test a,t_test1 b where b.col1=2 i_col1=2 1 SET1
select * from t_test a,t_test1 bwhere col1 = 2 I_col1 = 2 1 SET1

In one implementation, once all the query set queries have been executed the present invention would get the list of all the dynamically formed queries which are not having any issues (i.e. where EXCEPTIONFLAG value is 0). These queries will be used to check hint related functional and performance issues.

It is to be noted and understood by the person skilled in the art that any of the existing or new mechanisms (any hint related issues) may be used to find issues in the queries formed. Such issues to be identified or determined may be pre-defined, are configurable, and/or based on the user/application requirements.

In one implementation, functional hint guideline: Example:-Index Ascending and descending: If the present invention use the index_asc hint for a query and the index on which the present invention have used index_asc hint is not a primary key then the expected result would not be correct. If the present invention use the index_desc hint for a query and the index on which the present invention have used index_desc hint is a non primary key index then descending order of indexed value will not be proper.

In one implementation, Performance Hint Guideline: Example: Append hint: The append hint tells the optimizer to perform a direct-path insert, which improves the performance of INSERT...SELECT operations but using append hint cause a table fragmentations if the hint is used in loop conditions because as direct-path inserts append data to the end of the table, they constantly increase the table high water mark, even if there is lots of free space within table.

After taking a particular SQL query each defined hint check is applied in that SQL to find potential issue. The processing logic of each rule is different. E.g:- If any index_desc hint issue is there in any SQL statement then EXCEPTIONFLAG column value in logging table(T_QUERYLOG) will be updated to 2 (i.e. 2-Hint Issue). Similarly for all the other predefined rules in the framework for each of the stored SQL of each dynamic object it would check for all these rules to find hint related issues.

After checking hint related all functional and performance issues for all the queries the present invention will generate the query plan for each of the SQL query which are not having any hint issues (i.e for the queries in logging table T_QUERYLOG having EXCEPTIONFLAG column value should set as 0)

In one implementation, Query Plan Check Guideline: If query plan of a particular SQL statement is going for Index fast full scan, Index full scan, Table access full and Merge join cartesian. In one example, the query plan will be generated and stored in QUERY_PLAN column of result table (T_QUERYPLAN_DETAILS). The issues indentified in the query plan generated and associated parameters to be stored in the table may be pre-defined/pre-configured, and are configurable/re-configurable, and/or based on the user/application requirements.

In one example, the columns of the result table may include but not limited to the columns as mentioned below and are only for understanding purpose:-
SQLID: This will be unique id for each query. This column value will be used to check difference of query plan in multiple databases.
QP_TAF_ISSUE: This column value will be 1 if query plan of SQL is going for Table access full else 0. (i.e. 1-Issue ,0-No Issue).
QP_IFS_ISSUE: This column value will be 1 if query plan of SQL is going for Index full scan else 0. (i.e. 1-Issue ,0-No Issue).
QP_IFFS_ISSUE: This column value will be 1 if query plan of SQL is going for Index fast full scan else 0. (i.e. 1-Issue ,0-No Issue).
QP_MJC_ISSUE: This column value will be 1 if query plan of SQL is going for Merge join cartesian else 0. (i.e. 1-Issue ,0-No Issue).
QUERY_PLAN: This column will be used for storing generated query plan.

After generating query plan one task would check,
If query plan is going for Table access full then QP_TAF_ISSUE column in result table will be updated with 1(Issue).
If query plan is going for Index full scan then QP_IFS_ISSUE column in result table will be updated with 1(Issue).
If query plan is going for Index fast full scan then QP_IFFS_ISSUE column in result table will be updated with 1(Issue).
If query plan is going for Merge join cartesian then QP_MJC_ISSUE column in result table will be updated with 1(Issue).

If any of the SQL query plan is going for Table access full or Index full scan or Index fast full scan or Merge join cartesian then for that SQL based on SQLID, in T_QUERYLOG table EXCEPTIONFLAG column value will be updated to 3 ( i.e. 3-Query Plan Issue).

In one implementation, if the query plan associated with the dynamic queries needs to be compared with different database then there will be an option to compare these query plans of the dynamic queries with another configured database to find the differences in the query plan. If this option, as provided in the present invention, is enabled then the logging table (T_QUERYLOG) of the primary DB would be created (Copied) in another database using database link so that query plan can be generated based on already formed dynamically SQL queries. (There is no need to form the dynamic queries again in another database). The query plan generation will be parallel activity in multiple configured databases.

In one implementation, it is to be noted that if the query plan associated with the dynamic queries needs to be compared with different database schema having similar schema structure then statistics in all the configured multiple databases should be either already gathered or the statistics set for the objects is same. The same rule also applies when the present invention need to find query plan related issues in single database (Statistics should be set or gathered for DB objects).

In one implementation, a Multiple DB Dynamic Sql Query Plan Validation Guideline may be used in the present invention. If the databases which are configured to check the query plan with the primary database in which the actual query has been formed; and there is a difference in the dynamic SQL query plan with the other DB’s then this would be reported as an issue.

For example: If there are two databases i.e. DB1and DB2 then dynamic SQLs would be formed and stored in logging table in DB1 using above mention dynamic algorithm. The entire hint related functional and performance issues check would be done in DB1. After this logging table (T_QUERYLOG) would be created in DB2 using database link; query plan generation task would be triggered in both the databases in parallel. In each databases the result table (T_QUERYPLAN_DETAILS_DB1 and T_QUERYPLAN_DETAILS_DB2) would be created for query plan as described above.

In one implementation, once query plan generation process is finished in both the databases then one task will create back the result table (T_QUERYPLAN_DETAILS_DB2) from DB2 using database link into the primary database (DB1).

In one implementation, there may be one monitoring task in primary database (DB1) which would check whether the query plan generation activity is completed or not in all the configured databases. Once completed it will compare the query plan associated with the dynamic queries is generated in all the databases and if any difference found in query plan for a particular SQLID based on T_QUERYPLAN_DETAILS table then it will update logging table(T_QUERYLOG) with EXCEPTIONFLAG column value as 4 (i.e. 4- Dynamic Sql Query Plan Comparison Issue). Referring now to figure 6(a) a query plan issue identified is illustrated in accordance with an embodiment of the present subject matter, and figure 6(b) is illustrated to explain dynamic SQL query plan comparison report, in accordance with an embodiment of the present subject matter.

For example: In one SQL query plan is going for Index range scan in DB1 but the same SQL query plan is going for Table access full in DB2 then this will be reported as dynamic sql query plan comparison issue. Referring now to figure 7, an example of the SQL query plan issue reporting is illustrated, in accordance with an embodiment of the present subject matter.

Referring now to figure 8, a database management system to determine issues in dynamic query statement, preferably in dynamic Structured Query Language (SQL) statement is illustrated, in accordance with an embodiment of the present subject matter. In one implementation, the database management system 800 is disclosed. Although the present subject matter is explained considering that the present invention is implemented in the database management system 800, it may be understood that the present invention may also be implemented in a variety of computing systems, such as a laptop computer, a desktop computer, a notebook, a workstation, a mainframe computer, a server, a network server, and the like. It will be understood that the database management system 800 may be accessed by multiple users, or applications residing on the database system. Examples of the database management system 800 may include, but are not limited to, a portable computer, a personal digital assistant, a handheld node, sensors, routers, gateways and a workstation. The database management system 800 may be communicatively coupled to other nodes or a nodes or apparatuses to form a network (not shown). Examples of the other nodes or a nodes or apparatuses may include, but are not limited to, a portable computer, a personal digital assistant, a handheld node, sensors, routers, gateways and a workstation.

In one implementation, the network (not shown) may be a wireless network, a wired network or a combination thereof. The network can be implemented as one of the different types of networks, such as GSM, CDMA, LTE, UMTS, intranet, local area network (LAN), wide area network (WAN), the internet, and the like. The network may either be a dedicated network or a shared network. The shared network represents an association of the different types of networks that use a variety of protocols, for example, Hypertext Transfer Protocol (HTTP), Transmission Control Protocol/Internet Protocol (TCP/IP), Wireless Application Protocol (WAP), and the like, to communicate with one another. Further the network may include a variety of network nodes, including routers, bridges, servers, computing nodes, storage nodes, and the like.

The database management system 800 may include a processor 802, an interface 804, and a memory 806. The processor 802 may be implemented as one or more microprocessors, microcomputers, microcontrollers, digital signal processors, central processing units, state machines, logic circuitries, and/or any nodes that manipulate signals based on operational instructions. Among other capabilities, the at least one processor is configured to fetch and execute computer-readable instructions or modules stored in the memory 806.

The interface (I/O interface) 804, may include a variety of software and hardware interfaces, for example, a web interface, a graphical user interface, and the like. The I/O interface may allow the database system, the first node, the second node, and the third node to interact with a user directly. Further, the I/O interface may enable the database management system 800 to communicate with other nodes or nodes, computing nodes, such as web servers and external data servers (not shown). The I/O interface can facilitate multiple communications within a wide variety of networks and protocol types, including wired networks, for example, GSM, CDMA, LAN, cable, etc., and wireless networks, such as WLAN, cellular, or satellite. The I/O interface may include one or more ports for connecting a number of nodes to one another or to another server. The I/O interface may provide interaction between the user and the database management system 800 via, a screen provided for the interface.

The memory 806 may include any computer-readable medium known in the art including, for example, volatile memory, such as static random access memory (SRAM) and dynamic random access memory (DRAM), and/or non-volatile memory, such as read only memory (ROM), erasable programmable ROM, flash memories, hard disks, optical disks, and magnetic tapes. The memory 506 may include plurality of instructions or modules or applications to perform various functionalities. The memory includes routines, programs, objects, components, data structures, etc., which perform particular tasks or implement particular abstract data types.

In one implementation, a database management 800 to determine issues in dynamic query statement, preferably in dynamic Structured Query Language (SQL) statement, is disclosed. The database management system 800 comprises a processor 802, and a memory 806 coupled to the processor 802 for executing a plurality of modules present in the memory 806. The plurality of modules comprises an identification module 810, a dynamic query formation module 812, a determining module 814, and a reporting module 816. The memory 806 further comprises at least one database 826 comprising a plurality of database objects. The identification module 810 is configured to identify at least one database object present in at least one database, the one database object comprise at least one dynamic query. The dynamic query formation module 812 is configured to form at least one query set based on at least one input parameter associated with the database object identified. The determining module 814 configured to determine presence of at least an issue associated with the query set formed. The reporting module 816 configured to report the issue associated with the query set formed.

In one implementation, the database object is capable of forming at least one query dynamically. These are the database objects having dynamic SQL statements. It may be understood that the dynamic queries/statements refer to queries that are built dynamically rather than provided as an explicit query string.

In one implementation, the database object capable of forming the query dynamically comprise at least one function and/or at least one procedure, the function and/or the procedure preferably contain at least one “execute immediate” logic or “open cursor” logic or any combination thereof

In one implementation, the DBMS 800 further comprises at least one storage module 824 configured to store the query set for the database object identified is in at least one table based on at least one input parameter associated with function/procedure of the database object, the table comprise an information associated with the input parameter and is preferable at least one of: an input name, a position, an input value, a data type, whether input parameter is mandatory or not, an database object name (function/procedure), a name of the query sent formed, or any combination thereof.

In one implementation, the storage module 824 may be configured to store at least the issue determined associated with the query set formed in at least one report, preferably a report table.

In one implementation, the DBMS 800 further comprises a creating module 818 configured to create at least one query set to be stored in the table preferably having all input parameters as optional.

In one implementation, the DBMS 800 further comprises a fetching module 820 configured to fetch the database object, for input parameters (optional parameters) for at least one possible combination.

In one implementation, the issue associated with the query set formed is at least one of: a syntax issue, a functional/performance hint related issue, a query plan related issue, a dynamic sql query plan comparison issue, or any combination thereof, with other database for any query which is formed based on an input parameter combination defined for the database object.

In one implementation, the determining module 814, before determining the issue associated with the query set formed, is configured to determine if the issue from the database object from the query set selected to determine the issue requires validation for all the checks, and thereby disable the database object if no validation is required for all checks, or determine presence of the issue associated with the query set formed.

In one implementation, the DBMS 800 further comprises a validation module 822 configured to determine the presence of the issue by validating at least one function and/or at least one procedure associated with the database object for each combination (query set) and thereby storing the result of validation, preferably, with at least an exception flag 0, any value other than or greater than 0 indicating presence of the issue, and “0” indicating presence of no issue.

In one implementation, the storage module configured to store at least the issue determined associated with the query set formed in at least one report, preferably a report table.

In one implementation, a database management system 800 to determine issues in dynamic query statement, preferably in dynamic Structured Query Language (SQL) statement is disclosed. The database management system 800 comprises a memory 806 coupled to the processor 802 for executing a plurality of modules present in the memory 806. The plurality of modules comprises an accessing module 808, an identification module 810, a dynamic query formation module 812, a determining module 814, and a reporting module 816. The accessing module 808 configured to access at least one database 826 comprising a plurality of database objects. The identification module 810 configured to identify at least one database object having at least one dynamic Structured Query Language (SQL) statement; and at least one input parameters combination for the database object identified. The dynamic query formation module 812 configured to form at least one dynamic query statement based on the input parameters identified and the database object identified. The determining module 814 is configured to determine presence of at least an issue associated with the dynamic query statement formed. The reporting module 816 is configured to report the issue determined for the dynamic query statement formed.

In one implementation, the DBMS 800 further comprises at least one storage module 824 configured to store at least the issue determined associated with the query set formed in at least one report, preferably a report table.

Referring now to figure 9, a method for determining issues in dynamic queries, preferably in dynamic Structured Query Language (SQL) query is illustrated, in accordance with an embodiment of the present subject matter. The method may be described in the general context of computer executable instructions. Generally, computer executable instructions can include routines, programs, objects, components, data structures, procedures, modules, functions, etc., that perform particular functions or implement particular abstract data types. The method may also be practiced in a distributed computing environment where functions are performed by remote processing devices that are linked through a communications network. In a distributed computing environment, computer executable instructions may be located in both local and remote computer storage media, including memory storage devices.

The order in which the method is described is not intended to be construed as a limitation, and any number of the described method blocks can be combined in any order to implement the method or alternate methods. Additionally, individual blocks may be deleted from the method without departing from the protection scope of the subject matter described herein. Furthermore, the method can be implemented in any suitable hardware, software, firmware, or combination thereof. However, for ease of explanation, in the embodiments described below, the method may be considered to be implemented in the above described database management system 800.

In one implementation, a method for determining issues in dynamic queries, preferably in dynamic Structured Query Language (SQL) query is disclosed.

At block 902, at least one database 826 comprising a plurality of database objects is accessed by the DBMS 800. The database object is capable of forming at least one query dynamically.

At block 904, at least one database object having at least one dynamic Structured Query Language (SQL) statement from the DB 826 is identified by the DBMS 800. The database object capable of forming the query dynamically comprise at least one function and/or at least one procedure, the function and/or the procedure preferably contain at least one “execute immediate” logic or “open cursor” logic or any combination thereof.

In one implementation, If the query set is not configured for any dynamic object then by default this framework will create one QUERYSET in the configuration table (T_CONFIG) with all input parameters as optional and it will call this particular object for input parameters (optional parameters) for all possible combination.

At block 906, at least one input parameters combination for the database object identified is then identified by the DBMS 800.

At block 908, at least one query statement is formed by the DBMS 800 based on the input parameters identified and the database object identified. The query set for the database object identified is stored in at least one table based on at least one input parameter associated with function/procedure of the database object, the table comprise an information associated with the input parameter and is preferable at least one of: an input name, a position, an input value, a data type, whether input parameter is mandatory or not, an database object name (function/procedure), a name of the query sent formed, or any combination thereof.

At block 910, presence of at least an issue associated with the dynamic query statement formed is determined by the DBMS 800. In one implementation, the issue determined associated with the query set formed in at least one report, preferably a report table. The issue associated with the query set formed is at least one of: a syntax issue, a functional/performance hint related issue, a query plan related issue, a dynamic sql query plan comparison issue, or any combination thereof, with other database for any query which is formed based on an input parameter combination defined for the database object.

In one implementation, before determining the issue associated with the query set formed, the method determines if the issue from the database object from the query set selected to determine the issue requires validation for all the checks; and disable the database object if no validation is required for all checks, or determine presence of the issue associated with the query set formed.

In one implementation, the presence of the issue is determined by validating at least one function and/or at least one procedure associated with the database object for each combination (query set) and thereby storing the result of validation, preferably, with the exception flag 0, any value other than or greater than 0 indicating presence of the issue, and “0” indicating presence of no issue.

In one implementation, the issues determined associated with the query set formed are stored in at least one report, preferably a report table.

At block 914, the issue determined for the dynamic query statement formed is reported by / using the DBMS 800.

In one implementation, a method for determining issues in dynamic queries, preferably in dynamic Structured Query Language (SQL) query is disclosed. The method comprises identifying at least one database object present in at least one database, the one database object comprise at least one dynamic query 904; forming at least one query set based on at least one input parameter associated with the database object identified 908; determining presence of at least an issue associated with the query set formed 910; and reporting the issue associated with the query set formed 914.

The technical benefit and advantage of the present invention as compared to the prior-art technique (manual evaluation of dynamic SQL) is provided below using an example of a function which is having 12 Input parameters. So the number of different queries which can be formed is 4096 (Based on nCr formula).

The prior-art requires to generate all 4096 queries manually for the dynamic function and keep it in excel sheet or database table to manage it. All the queries are required to be executed manually 4000+ times to check the syntax issue. Hint issues are required to be checked manually for all the queries which will depend on the competency of the person checking the issues. The query plan related issues required to be checked manually for all the queries and store the report in excel sheet or database table. After fixing all the issues found, again have to manually check if have introduced new issue related to syntax, hint or query plan for all impacted queries due to the new code modification. To check the dynamic sql query plan differences in multiple databases, it is required to check the query plan for all 4096 queries manually in multiple databases. After fixing the dynamic sql query plan difference issue for the reported queries, it may impact the execution plan of other queries. So, in order to check the impact again query plan difference verification for these queries needs to be done.

In contrast to the above prior-art, the present invention (framework) automatically generates 4096 queries and stores them in database table. The present invention reports for all queries in this function which can cause syntax issues by a single click. The present invention automatically finds all hint related issues based on the predefined rules which has been configured. The present invention generates the report for all query plan issues and stores the report in database table. After all the issues reported by the framework have been fixed, the present invention will report all issue related to syntax, hint or query plan due to the new code modification. The present invention generates the report for the dynamic sql query plan differences in multiple databases for all 4096 queries and would display a consolidated report. After fixing the dynamic sql query plan difference issue for the reported queries, the present invention generates dynamic sql query plan differences report if any, because of the new code modification done/performed.

Apart from what is discussed above, the present invention has some additional advantages and technical benefits, as provided below:
• The present invention finds all the programming objects using dynamic queries and finds all the issues related to it (SQL syntax issue, functional/performance hint related issue, query plan related issue and dynamic sql query plan comparison issue with other database with the detailed information about the problem) for any database application system.
• Manual effort for validating each function/procedure for dynamic runtime issues can be eliminated by using the present invention. Even after fixing one issue there is no guarantee that the existing things would be working fine according to the prior-art. However, the present invention framework guarantees that.
• The present invention provides a dynamic sql “N” automation framework for forming and finding all the syntax issues for the complete database application system.
• The present invention provides a dynamic sql Automation Framework to find functional, performance issues caused because of wrong usage of database hint in the application systems
• The present invention provides automation for finding query plan related issues for dynamic sql.
• The present invention provides automation for dynamic query plan comparison/validation with other databases.

A person skilled in the art may understand that any known or new algorithms may be used for the implementation of the present invention. However, it is to be noted that, the present invention provides a method to be used during back up operation to achieve the above mentioned benefits and technical advancement irrespective of using any known or new algorithms.

A person of ordinary skill in the art may be aware that in combination with the examples described in the embodiments disclosed in this specification, units and algorithm steps may be implemented by electronic hardware, or a combination of computer software and electronic hardware. Whether the functions are performed by hardware or software depends on the particular applications and design constraint conditions of the technical solution. A person skilled in the art may use different methods to implement the described functions for each particular application, but it should not be considered that the implementation goes beyond the scope of the present invention.

It may be clearly understood by a person skilled in the art that for the purpose of convenient and brief description, for a detailed working process of the foregoing system, apparatus, and unit, reference may be made to a corresponding process in the foregoing method embodiments, and details are not described herein again.

In the several embodiments provided in the present application, it should be understood that the disclosed system, apparatus, and method may be implemented in other manners. For example, the described apparatus embodiment is merely exemplary. For example, the unit division is merely logical function division and may be other division in actual implementation. For example, a plurality of units or components may be combined or integrated into another system, or some features may be ignored or not performed. In addition, the displayed or discussed mutual couplings or direct couplings or communication connections may be implemented through some interfaces. The indirect couplings or communication connections between the apparatuses or units may be implemented in electronic, mechanical, or other forms.

When the functions are implemented in a form of a software functional unit and sold or used as an independent product, the functions may be stored in a computer-readable storage medium. Based on such an understanding, the technical solutions of the present invention essentially, or the part contributing to the prior art, or a part of the technical solutions may be implemented in a form of a software product. The computer software product is stored in a storage medium, and includes several instructions for instructing a computer node (which may be a personal computer, a server, or a network node) to perform all or a part of the steps of the methods described in the embodiment of the present invention. The foregoing storage medium includes: any medium that can store program code, such as a USB flash drive, a removable hard disk, a read-only memory (Read-Only Memory, ROM), a random access memory (Random Access Memory, RAM), a magnetic disk, or an optical disc.

Devices that are in communication with each other need not be in continuous communication with each other, unless expressly specified otherwise. In addition, devices that are in communication with each other may communicate directly or indirectly through one or more intermediaries.

When a single device or article is described herein, it will be readily apparent that more than one device/article (whether or not they cooperate) may be used in place of a single device/article. Similarly, where more than one device or article is described herein (whether or not they cooperate), it will be readily apparent that a single device/article may be used in place of the more than one device or article or a different number of devices/articles may be used instead of the shown number of devices or programs. The functionality and/or the features of a device may be alternatively embodied by one or more other devices which are not explicitly described as having such functionality/features. Thus, other embodiments of the invention need not include the device itself.

Finally, the language used in the specification has been principally selected for readability and instructional purposes, and it may not have been selected to delineate or circumscribe the inventive subject matter. It is therefore intended that the scope of the invention be limited not by this detailed description, but rather by any claims that issue on an application based here on. Accordingly, the disclosure of the embodiments of the invention is intended to be illustrative, but not limiting, of the scope of the invention, which is set forth in the following claims.

With respect to the use of substantially any plural and/or singular terms herein, those having skill in the art can translate from the plural to the singular and/or from the singular to the plural as is appropriate to the context and/or application. The various singular/plural permutations may be expressly set forth herein for sake of clarity.

Although implementations for systems and methods for identifying issues in dynamic SQL for promoting dynamic SQL quality have been described in language specific to structural features and/or methods, it is to be understood that the appended claims are not necessarily limited to the specific features or methods described. Rather, the specific features and methods are disclosed as examples of implementations of the systems and methods for identifying issues in dynamic SQL for promoting dynamic SQL quality.