Browse Prior Art Database

An Algorithm aiding in SQL Diagnosis

IP.com Disclosure Number: IPCOM000016789D
Original Publication Date: 2003-Jul-15
Included in the Prior Art Database: 2003-Jul-15
Document File: 3 page(s) / 42K

Publishing Venue

IBM

Abstract

A recurring problem in daily DBMS operation, is the failure of SQL due to bad data from the source table. Small changes in the semantic meaning of a column or NLS characters may result in an unexpected SQL statement failure. This paper describes an algorithm for diagnosing SQL failures in an autromated fashion using combinatorial methods on the predicates in the WHERE clauses of each SELECT statement in the SQL statement.

This text was extracted from a PDF file.
This is the abbreviated version, containing approximately 52% of the total text.

Page 1 of 3

An Algorithm aiding in SQL Diagnosis

A recurring problem in daily DBMS operation, is the failure of SQL due to bad data from a source table in a DBMS. Small changes in the semantic meaning of a column or NLS characters, may result in an unexpected SQL statement failure. The real problem surfaces when the DBA begins to analyze the SQL statement to determine the root cause of the failure. SQL errors are often insufficient, database logs are insufficient even with tracing turned on. What is needed is a tool to decompose the SQL into its most elementary parts and then to apply a diagnostic algorithm to find the root cause. DBMS products provide a command line interface or GUI that allows direct running of an SQL statement. DBAs would experiment with subset SQL trying to isolate the root cause. This becomes a trial and error approach that depends on the skill of the DBA. The core idea is to decompose the problem SQL statement into its constituent parts and then selectively recombining under algorithmic control to create subset SQL that may or may not fail. The line between failed and successful SQL executions aids in determining why the original SQL failed.

The algorithm is described below:
1) SELECT statements are extracted from complex SQL statements.
2) TABLE names are extracted.
3) COLUMN names are extracted.
4) WHERE clause expressions are extracted.
5) The SQL error message is provided to direct the search If no SQL error message is provided then the algorithm will begin with the embedded SELECT statements.

Pass 1: Determine if each TABLE listed has any data using the SELECT COUNT(*)

        FROM {table} statement. Tables with no data are flagged as possible causes of the SQL failure. Pass 2: Apply in a combinatorial manner, starting with each conditional clause singly and then in combinations of two then three...