EP1353278A2 - Comparison of source files - Google Patents

Abstract

Method for comparison of two source files containing database queries, in which the two database queries are extracted from the source files and compared. Accordingly the syntax of each query is analyzed using syntax analysis tools, then the syntax of the database queries are compared with each other and the results of the comparison are output.

Description

The invention relates to a method for comparing two Source files containing database queries in which the Database queries determined from the two source files and in which the determined database queries a first source file with the determined Database queries of a second source file compared become. Furthermore, the invention relates to a Read comparison device with source files Readers and with database queries from source files extracting extractants.

For access to databases, especially relational ones Databases, such as DB2 from IBM, are database queries from external programs using the databases interact, generated. These database queries will via a standardized interface, the Structured Query Language (SQL) interface. in this connection are SQL queries of the programs to the database transmitted and processed in the database. These SQL queries can be from a variety of query parameters exist, for example the parameters "Cursor Name "," Statement Type "(Select, Insert, Update, Delete), "Tables", "Joins", "Predicates", "Select, Update and Insert-Columns "," Select Option Text "and" Select Option Columns "can be used.

When querying a database using SQL, the Database actions triggered which are the answering enable the database query. The response times of the Database queries can be chosen depending on the Query parameters vary widely. It is desirable that the SQL queries are optimized and if possible enable short response times.

Programs that work with the records of the databases work, will change over time. At a Change in programs can lead to change of the SQL queries that may have to be changed Response times can lead. In some cases it happens to have a negative impact on response times and are no longer tolerable. Then it is necessary that those SQL queries that are found under responsible for the extended response times are.

In such a case, the conventional Database queries of the original program and the of the new program compared. This will sequentially each database query of the original Program identified in the same place in the database query and the new program String of database queries compared. Frequently however, the database queries in the Program versions only shifted in their position. This means that a database query that is in a old program showed up at the beginning of the program in a new program, possibly at the end of the program shows up. With the conventional method one can do not determine such offset of the database query because conventionally, a first database query in one old program with the first database query in the new program and in the same way the following Database queries are checked.

It can also happen that the string is a Database queries have changed their semantics However not. With conventional comparisons, this will however not recognized and the comparison shows that have changed the database queries. Finally, can database queries are only slightly different their semantics, but changed significantly in their syntax have, which is why by conventional methods as is shown changed.

To find the one causing the long response times Database queries are traditionally necessary to all found changes manually to investigate what due to the search problems outlined above after changing database queries, a significant one Effort means.

Hence the technical result according to the invention Problem, an efficient comparison of source files, that contain database queries put.

The technical problem previously derived will solved according to the invention in that the syntax of everyone Database query determined using a syntax analysis and the syntax of the database queries of the Source files are compared with each other.

The syntax of a database query gives the individual Elements of the database query regarding their grammatical rules, their compilation, again. The meaning of the elements is irrelevant. Changes in the syntax means a changed compilation or Order of elements. However, this does not have to be inevitably lead to changed semantics.

A syntax analysis is, for example, with the help of a Parser program carried out. Here, the syntactic elements of the database query determined. According to the invention, all database queries become one first source file broken down into its syntactic elements, all database queries of a second source file in your disassembled syntactic elements and all syntaxes of the Database queries of the first source file with all Syntaxes of the database queries of the second source file compared. Differences that occur can now be better be evaluated because of changes in positioning of the syntactic elements within one Database query can be recognized. Changes in the String does not change the Syntax, which is why changed strings when comparing are not taken into account.

The sources for determining the syntax can for example the source code of the source files, the Database request module (DBRM) of the source files, the DB2 Catalog information and any other Access modules of the source files.

To ensure that every database query of the first source file with each database query the second Source file is compared, it is suggested that each Database query of the first source file with everyone Database query of the second source file is combined.

Access to the determined database queries is relieved when the syntax of the database queries get saved.

A database query is made up of syntactic ones Elements together, the different categories can be assigned. To ensure that at a comparison of syntactic elements of the corresponding Categories will be compared with each other suggested that syntactic elements of the Database queries are saved categorized.

In this context, it is also advantageous if the corresponding syntactic elements of the combined database queries compared become. Here, for example, individual parts an SQL query compared. According to the SQL specifications will determine queries and parameters Assigned to categories. Will the string of the Database query examined, the individual Query parameters, the syntactic elements, determined and their assignment to the individual categories carried out.

When performing a database query, in particular an SQL database query, different Parameters different response times. A changed one Select command has less effect than a changed where command. It is therefore proposed that assignments to the syntactic elements become. It can be advantageous here if a Difference between the syntactic elements of the combined database queries using the valency is weighted. That means changes in that syntactic elements that have little impact on the Response time, can be weighted less than those that have a major impact on response time. This makes it possible to determine if any are found Changes actually affect the Have the performance of a database query.

To iterate through the database queries quickly to be able to perform and direct access to the found database queries and their syntaxes have, it is suggested that in a table for each determined database query using this associated syntax identifying identifier is stored. Also one can Assign database query to the source file Allocation identifier can be saved. In addition, a Comparison value of a comparison result with a another database query is stored.

The syntax of a database query is combined with its syntactic element preferably in a table saved. To access this table too the syntax-specific identifier in the Saved table. Furthermore, the database query can be accessed immediately and their position in the Source file to be determined when the mapping identifier is saved. Finally, by saving a comparison result can be determined whether in the second source file a similar or the same Database query has been found. For example when comparing a database query to a first one Source file with all database queries from a second Source file first in the first step an absolute different database query found. The The comparison result is saved as a comparison value. The next step is a database query in the second source file found that has some similarity with the database query of the first source file, taking this similarity as a comparison result is saved because it is higher than the previous one found similarity. Eventually one will Database query found in the second source file, the complete with the database query of the first source file matches. Because the similarity of these two Source files are larger than those found previously Similarity, this comparison value is saved.

By saving the comparison value you can the database query in the second source file find the one that is most similar to the Has database query of the first source file.

Because different syntactic elements are different Have valences, it is suggested that each Combination of database queries using the weighted differences are assigned a similarity value becomes. The two database queries differ in syntactic elements that have little impact on have the response time, it causes them to nonetheless are similar. There will be a difference in a syntactic Found an element that has a great impact on a Response time, this causes the Database queries are less similar. That means that only minor changes in the String, however, syntactic elements involves the major impact on the Response time have little similarity to the Database queries, which at first glance may appear similar.

To make it easier to check errors, suggested that initially combined identical Database queries are identified as identical. All Database queries of the first source file, to which as identically rated database queries in the second Source file have been found - and vice versa - will be identified as identical. Then they no longer have to be checked as they have no effect on have changed database response times.

Very similar database queries also have only a small number Impact on database response time, why it is proposed that the equivalent combined Database queries are identified as identical. Equivalent means that only syntactic Items that have little impact on response time that distinguish database queries. This Database queries can also be identified as identical become.

Finally, it is suggested that similar combined Database queries are shown with their similarity value become.

Now the number of database queries to be checked to be able to limit it is proposed that a Limit determined and the similarity value at the limit is measured. Then with the help of the limit value Database queries identified as similar or dissimilar become. Exceeds the similarity value of a couple of database queries, the limit will be Database queries identified as similar. Is the Similarity value below a limit, so can can be concluded that these database queries are not are similar and shown as new or deleted can.

Finally, it is helpful if the two source files can be compared in their entirety, for which it is proposed that the total deviation of the Database queries of the first source file from the second Source file is determined. This total deviation is a Measure of how many and to what extent Database queries in a second source file from one changed the first source file.

Another object of the invention is a Comparison device, in particular with a previously described procedures with reading source files Readers and with database queries from source files extracting extractants. The reading materials are functional blocks that read the source files, so determine their content. Extractants are functional Blocks from the content of the source files Extract database queries.

An efficient and accurate determination of changed Database queries is achieved by the syntax analysis means analyzing a database query are provided and the syntactic elements of the Comparative means for comparing database queries are provided. The analysis means, for example a Parsers are functional blocks that query the database analyze and extract their syntax. You can do that Analysis means the syntax into syntactic elements disassemble and thus available for comparison put. Comparison means each receive two syntaxes and compare them with each other. With the help of Comparative means it is possible to query every database the first source file with every database query second source file regarding the syntactical elements to compare.

Storage means are preferably provided, with their Help syntactic elements of database queries get saved. Storage means are Functional units that store data. With the help of Storage medium is such a later access to the syntactic elements of every database query possible.

It is also envisaged that the results of the Comparative weighting weighting means are provided are. The weighting means are functional units that Combine and serve results with factors to the syntactic elements valences assigned. These values refer to to what extent a syntactic element relates to a Response time of a database when querying a database effect.

Finally, it is suggested that the weighted Results evaluating excrement means provided are. The excretions are functional elements the individual database queries have a similarity value assign. With the help of the elimination agent it is possible to make similar from dissimilar database queries distinguish and thus the number of examined Narrow database queries.

Fig. 1

den Ablauf eines erfindungsgemäßen Verfahrens;

Fig. 2

eine erfindungsgemäße Vorrichtung

The invention is explained in more detail below with reference to a drawing showing exemplary embodiments. The drawing shows:

Fig. 1

the sequence of a method according to the invention;

Fig. 2

a device according to the invention

Fig. 1 shows a database 2, which a variety of Data. This data can be taken from the database Structured Query Language (SQL) queries be extracted. Programs 4 work with the database 2 using the SQL queries. Depending on the in the The SQL queries containing programs 4 vary Processing times in the programs 4 from each other. Becomes an old program 4a with a new program 4b replaced, it is desirable if the differences in the SQL queries between programs 4a, 4b would be determinable and thus an analysis of the changed SQL queries would be possible.

For this purpose, the respective program is in step 6 4a, 4b analyzed and all SQL queries extracted. This is first carried out for the old program 4a and in Follow it up for the new program 4b. The sources for the extraction in step 6 can, for example, the Program 4 source codes, the database request modules (DBRM) Interfaces of programs 4, the DB2 Catalog information (Collection, Package and Version) as well as any other sources that come from different Access modules can be read out.

All extracted SQL queries are in step 8 in disassembled their syntactic elements. Here comes one Parser to use that the SQL queries in their disassembled respective syntactic elements. For everyone in the SQL query contained in program 4a and 4b is a Table 8a created the syntactic elements categorically saved. In addition, is a Reference to the position of the SQL query in the Programs 4 noted.

After all SQL queries were broken down in step 8 and into the individual tables 8a have been saved Step 10 in a storage table 10a per SQL Key of parser table 8a, the position of the SQL query in program 4a, 4b and a switch whether the SQL has already been assigned to another SQL stored.

In step 12, the individual SQL queries that are in Table 8a have been compared. All SQL queries from an old program 4a compared with the SQL queries of the new program 4b. For example, the syntactic elements Coursor Name, Statement Type, Tables, Joins, Predicates, Select, update and insert columns, select option text and Select Option Columns are compared. For any combination of SQL queries from the old program 4a with SQL queries of the new program 4b becomes one Hit accuracy determined.

In step 14, a percentage for all pairs Deviation and a weighted evaluation determined. at The percentage deviation is the difference absolutely considered between the individual SQL queries. The weighted evaluation is for individual syntactic elements have different weights determined and occurring differences based on this Weighted weights. That means the individual syntactic elements have a minor impact on the weighted deviation than others. This is because that different syntactic elements of an SQL query different effects on the Performance of the SQL query. For example a change in the order of the Select Columns query an imperceptible change in the performance of the SQL query, whereas a changed where condition in an SQL query makes a big change in the performance of the SQL query causes.

After in step 14 the differences between the individual SQL queries of program 4a with those of program 4b these differences will be carried out evaluated. There are pairs of SQL queries in programs 4a and 4b that are not different from each other deviate, their difference is shown as 0 and these pairs are matched in step 16 marked. The pairs marked as matching can remain unconsidered in further consideration. The new program 4b is different from the old one Program 4a in relation to an SQL query found in this way not changed.

All as inconsistent - their difference is greater than 0 - marked pairs are in step 18 evaluated, whereby for each SQL query of the old Program 4a the SQL query of the new program 4b that matches the closest match.

After all SQL queries are examined and the best pairs are determined in step 18, in step 20 a threshold comparison was carried out.

For this purpose, a threshold value is defined, which is the lowest deviation between pairs found determined that rated as no longer consistent become. All SQL queries that have changed in part have, but the amount of the deviation is smaller than that Threshold is considered to be partially consistent in the Step 20a and can be used in the analysis of the Programs 4a and 4b are initially disregarded.

Here the weighting of the syntactic elements plays the SQL query plays a crucial role. Although can the string of an SQL query in a program 4b differ significantly from a program 4a, however these two SQL queries differ only in one syntactic element which has little impact on the Performance of the program. Such a deviation can be disregarded.

If there is a deviation in the threshold value comparison 20 one previously found to be the best possible match Couple found that above the set threshold lies, this SQL pair is in step 20b as deleted or re-evaluated and marked. All SQL queries, that in a program 4b versus the SQL queries show considerable differences in program 4a or no longer exist, are thus considered new or erased and flagged so that these SQL queries when analyzing the new program 4b first can be taken into account.

There is a high probability that SQL queries, that have changed significantly, that Performance of a new program 4b compared to the old one Change program 4a rather than those SQL queries, which changes only insignificantly or not at all to have. The comparison according to the invention makes it possible to preselect the SQL queries ensure the analysis of a new program compared to an old program.

2 is a comparison device according to the invention shown. Programs 4a and 4b work on one Database 2, in particular a DB2 database from IBM, with the help of SQL queries. If a new program 4b introduced and an old program 4a replaced, so it comes often that the performance of the new Program 4b significantly compared to the old program 4a changed. One of the reasons for this is that the Changed SQL queries from program 4a to program 4b to have. To facilitate an analysis of the SQL queries, a comparison device 22 is proposed.

The reading means 24 access the SQL queries of the Programs 4 via interfaces 24a, 24b. The through the Reading means 24 determined SQL queries of the respective Programs 4 are in their by the extraction means 26 disassembled syntactic elements. For each SQL query in storage means 28 a table with the ordered ones syntactical elements of the respective SQL query stored. Thus there are 28 in the storage means the two programs 4a and 4b tables with all SQL queries before, the tables the SQL queries for Store syntactical elements in an orderly manner.

Comparison means 30 access the storage means 28 and read the tables of the SQL queries. With help the comparison means 30 become SQL queries of the program 4a compared with SQL queries of the program 4b. At the All SQL queries of the two programs are compared compared with each other. Are by the means of comparison Found 30 SQL queries related to their syntactic elements correspond to each other, they become SQL queries using the elimination means 32 as identified identically. Identical identified SQL queries need to analyze for differences between programs 4a and 4b no longer be taken into account.

The determined by the comparison means 30 Differences in the SQL queries are determined using the Weighting means 34 weighted. It means that Differences in the individual syntactic elements be evaluated differently. After a weighting all differences have been carried out, the Searched for pairs of SQL queries that reside in the two Programs 4a and 4b are most similar. In the Elimination means 32 becomes the measure of similarity evaluated between the pairs found and with a Threshold compared. Are pairs of SQL queries similar to each other, d. H. lie their weighted Differences below a certain threshold, so will they are shown as partially identical. SQL queries, that are hardly similar, d. H. their weighted value the distinction is above the threshold, are considered reported new or deleted.

When analyzing for differences between the Programs 4a and 4b must first be defined as new or deleted SQL queries are examined. This makes it possible to do it quickly and efficiently Distinguish between two program versions and any performance problems that arise quickly to be able to fix.

LIST OF REFERENCE NUMBERS

2

Database

4a, b

query programs

6

Extract

8th

dismantle

8a

Parser tables

10

main memory

10a

Main memory map

12

to compare

14

Determining differences

16

Mark as match

18

Finding the best pairs

20

thresholding

22

comparison means

22a

interfaces

24

reading means

26

extracting agent

28

storage means

30

comparison means

32

clearing agents

34

Weighting means

Claims (17)

Method for comparing two source files containing database queries, in which the database queries are determined from the two source files, and in which the ascertained database queries of a first source file are compared with the ascertained database queries of a second source file, characterized in that that the syntax of each database query is determined using a syntax analysis, that the syntaxes of the database queries of the source files are compared, and that a comparison result is output. A method according to claim 1, characterized in that each database query of the first source file is combined with each database query of the second source file. Method according to claim 1 or 2, characterized in that syntaxes of the database queries are stored. Method according to one of claims 1 to 3, characterized in that syntactic elements of the database query are categorized. Method according to one of claims 1 to 4, characterized in that the corresponding syntactic elements of the combined database queries are compared with one another. Method according to one of claims 1 to 5, characterized in that weights are assigned to the syntactic elements and that a difference between the syntactic elements of the combined database queries is weighted using the weight. Method according to one of Claims 1 to 6, characterized in that an identifier identifying the associated syntax, an association identifier assigning the database query to the source file and a comparison value representing a comparison result with another database query are stored in a table for each database query determined. Method according to one of Claims 1 to 7, characterized in that each combination of database queries is assigned a similarity value with the aid of the weighted differences. Method according to one of claims 1 to 8, characterized in that identical combined database queries are identified as identical. Method according to one of claims 1 to 9, characterized in that equivalent combined database queries are identified as identical. Method according to one of claims 1 to 10, characterized in that similar combined database queries are shown with their similarity value. Method according to one of claims 1 to 11, characterized in that a limit value is determined and the similarity value is measured at the limit value and with the aid of the limit value the database queries are identified as similar or dissimilar. Method according to one of claims 1 to 12, characterized in that the total deviation of the database queries of the first source file from the second source file is determined. Comparison device, in particular with a method according to one of claims 1 to 13, with reading means reading source files, and with database queries extracting extractors from source files, characterized, that the syntax of a database query analyzing analysis means is provided, and that comparative comparison means are provided for the syntactic elements of the database queries. Comparison device according to claim 14, characterized in that storage means storing syntactic elements of the database queries are provided. Comparison device according to claim 14 or 15, characterized in that the results of the comparison means weighting weighting means are provided. Comparison device according to one of claims 15 to 16, characterized in that the weighted results evaluating excretion means are provided.

Images