Automated SQL Quality Assurance

To leverage available skill sets and to save on costs, many large IT organizations have been moving their DB2 development efforts to off-platform programmers. These programmers are not mainframe developers, and may not even have mainframe logon accounts. They write the applications in JAVA or C, and create SQL that will be run against existing production mainframe DB2 systems.

It is very easy for these development teams to generate dynamic SQL that satisfies the application data requirement, but it may execute in an inefficient manner on the mainframe. This can cost the company a lot of money in terms of wasted CPU cycles, which can impact the monthly license charges that apply to their DB2 systems.

To mitigate this wasted CPU cost, many companies have adopted a method for evaluating the SQL before it goes to production to ensure shop standards and best practices are followed. These methods may be manual, which requires a lot of time and is error prone, or they may exploit a third-party software solution that can evaluate the dynamic SQL against a rule base to ensure efficiency.

Many third-party solutions can monitor the executing SQL and display the amount of CPU cycles being consumed. These types of solutions require DB2 traces to capture information and, in doing so, consume additional CPU cycles, further driving up operating costs.

Some solutions provide diagnostic processes to show how the SQL is running and what might be done to correct it in the future. These solutions provide tools to identify problematic SQL and suggest corrective actions. The SQL is retrieved from the DB2 Dynamic Statement Cache and can be grouped together to reduce the overhead of SQL code examination. A rule-based approach can be used against both static and dynamic SQL. The rules can establish shop standards and best practices and ensure only efficient SQL is running in production. Such Quality Assurance tools can be set up to scan existing SQL, identifying poor performance for a quick Return on Investment. Then the solution can be embedded into the development and production promotion processes to identify any new SQL that violates the rule set.

An efficient and proactive DB2 organization should evaluate SQL performance and Quality Assurance tools to reduce the cost of poor performing SQL in production. The cost is real, and is reflected each month on the IT organization’s Monthly License Charge bill. DB2 is typically a large portion of that bill, so improving the performance of SQL not only makes the users and customers happy, but it will also make senior management happy by reducing a real dollar cost for mainframe operations.