Common problems when working with SQL
Inefficient SQL statements can have a major impact on application throughput and response time.
Even minor changes in application or Db2 data structures can significantly affect performance and availability. For example, subtle statistical changes in the Db2 catalog might cause the Db2 optimizer to change access path selections. If you do not detect the changes before the application moves to production, the application might perform poorly or be unavailable.
The BMC AMI SQL Explorer for Db2 product is an SQL analysis tool. Application developers use it to identify performance problems that inefficient SQL statements cause and correct the problems before they reach production.
Similarly, DBAs can identify and manage performance impacts resulting from data structure changes before implementing those changes in production. SQL Explorer also helps resolve problems that are already in production.
The following table describes how SQL Explorer addresses common problems that application developers and DBAs face.
Problem | How SQL Explorer helps |
|---|---|
Plan table information is cryptic and requires expert knowledge of the relational database management system (RDBMS). | SQL Explorer offers expert rules that you can tailor for your environment or technical audience. DBAs and performance experts can customize the expert rules to establish installation-specific rules, set or change thresholds, and issue warnings on keywords to be avoided. Application developers can use SQL Explorer to parse and check SQL code against the expert rules base. |
DBAs need to control application performance within their change-management procedures. | Impact analysis enables you to see the effects of anticipated changes before making the changes. Also, you can use SQL Explorer to compare database request module (DBRM) libraries to Db2 catalogs. This type of comparison reduces testing time before promoting an application into production. This approach also prevents the need for rolling back the Db2 catalog to remove data that was added during testing. |
Application design reviews sometimes fail to produce quantified, objective information to justify change recommendations. | Through integration with the BMC AMI Catalog Manager for Db2 product, SQL Explorer can run automatic Explain jobs on binds or rebinds. Also, online and batch reporting offers ad hoc or regular reporting capabilities for planning and impact analysis. Recommendations provide a source of expert knowledge for DBAs, application developers, and performance experts. |
Application developers want more involvement in performing application-specific tuning. | Developers can check their SQL in the development phase of the application, rather than waiting until the application has gone to test or production and caused a problem. By offering explanations of plans, packages, or DBRMLIBs, SQL Explorer enables you to check entire applications. DBRMLIB explanations enable you to Explain SQL stored outside of the Db2 catalog. SQLX edit macro enables quick, online explanation or execution of a single SQL statement from a TSO edit session. |
Analyzing SQL can be time-consuming. Rewriting SQL statements without expert knowledge of the RDBMS can be a trial-and-error process, but maintaining expert knowledge can be difficult when new releases introduce SQL changes that affect the Db2 optimizer. | You can add or modify rules to enforce installation standards and to detect SQL statements that should be avoided in specific circumstances. |