JP3501303B2 - Compiler method for database query statements - Google Patents

Description

Description: BACKGROUND OF THE INVENTION 1. Field of the Invention The present invention relates to a database query statement compiler for compiling an embedded database query statement used for querying a database requiring high-speed execution. [0002] In the description of the present invention, the following abbreviations are used. DB: relational database DBMS: relational database processing system AP: application program UP: utility program AL: access logic in relational DB Further, since the object of the present invention is a relational database, the following databases In the description, “relational type” is omitted. [0003] The query language of the database is a data operation language for performing addition, deletion, update, search, etc. of data to the database. SQL standardized by JIS is a typical database query language. The query language of the database is simply called the query language. Generally, there are a plurality of query processing methods. For example, when an index is assigned to a certain column, there are a search method using the index and a search method not using the index. Therefore, information such as which index is used and how to process an inquiry will be referred to as access path information. [0005] A normal inquiry language (eg, SQL) does not include access path information. This is so that the database user can search without having to be aware of the access path. The query language is used when a database user interactively operates a database.
It may be embedded in a program as part of a database system program. In the latter, a query language embedded in a program is called an embedded database query language, or simply an embedded language, and a programming language from which embedding is performed is called a host language. FORTRAN, COBO as host language
General-purpose programming languages such as L, Pascal, PL / I, C, and C ++ are used. The following describes an embedded database query language directly related to the present invention. [0007] The embedded query language embedded in the host language is a function of the DBMS or DBMS before the host language is translated by the host language compiler.
The pre-processing is performed by an UP such as a preprocessor provided by. This is called preprocessing for database query language processing, or simply preprocessing. A query language that does not include an access path, such as SQL, requires a very complicated DB access control when executing the query. This is DBMS
What describes how to solve the problem using the basic function provided by the access control unit is called an access logic (hereinafter, AL). Specifically, AL indicates the following control and information. -Access path (which index to use or not to use?)-Cursor processing and repetition processing when the query result is multiple rows-Processing method of join (JOIN), sort and merge, (Nested loop join method , Merge join method, when to sort, etc.)-Processing such as subqueries,-Access order control for multiple table processing,-Usage of temporary work area, (Method of storing results of sorting and subqueries, etc.) -Arithmetic processing of arithmetic functions and set functions-Arithmetic processing of host variables and columns-Interface processing between host language and columns (host variable-column data transfer, error processing) DBMS interprets query language processing In the case of using the method, a pre-process as shown in FIG. 8 and a runtime process as shown in FIG. 9 are performed. In the drawing, solid arrows indicate the flow of processing, dotted arrows indicate the flow of data, boxes with single frames indicate processing / programs, and boxes with double frames indicate data. When the DBMS performs the processing of the query language by the compiler method, a pre-process as shown in FIG. 10 and a run-time processing as shown in FIG. 11 are performed. In this example, the pre-processing is divided into the SQL preprocessor (C200) and the DBMS (C300), but these may be a single program. In the interpreter system, a query is translated at the time of execution of an AP, so that the query is given from a terminal or is not necessary to compile (for example, an SQL statement having a simple syntax such as COMMIT WORK in the SQL language). It is effective for Conversely, since the compiler method does not require query translation processing at the time of AP execution, it is effective for processing such as queries with complicated syntax and routine queries in mission-critical business requiring high speed. In the following, the query language processing of the DBMS by the interpreter method and the compiler method will be described using SQL as a typical query language as an example. FIG. 2 shows an example of a data search program. In this example, the host language is C and the embedded database query language is SQL. When embedding the SQL language in the C language,
An identifier for distinguishing between the L language and the C language is given. In this example, the embedded inquiry sentence is identified by EXEC SQL ;. G103 is a query sentence to the database in the SQL language, and G101, G102 and G104 are C
This is a language statement. First, a conventional interpreter system will be described. In the case of the preprocess in the interpreter system, the program given in FIG. 2 is processed as shown in FIG. (1) Assume that A100 corresponds to FIG.
The embedded SQL sentence corresponding to A101 is from G104 to G
109, G110, G111, G112, G113, G
There are 6 sentences of 114. (2) The preprocessor (A200) provided by the DBMS receives A100 as an input and
For one SQL sentence, cut out the SQL sentence (A20
1), DBMS SQL interpreter call codes (H110 to H112, H114, H
116, H118, H120, H122) (A2
02), and outputs the result (A300). At this time, A300 corresponds to FIG. (3) Convert A300 to a C language compiler (A
Compile with 400). As a result, executable AP
(A500) is generated. Thereafter, the execution of this AP is performed as shown in FIG. (4) AP (B100) is replaced by AP (A50)
0). (5) When AP (B100) is executed, D
BMS SQL interpreter call instruction (dbms _- interpri
t _- SQL) is executed, and control is transferred to the DBMS (B200) with the parameter (character string of the SQL statement). (6) The DBMS (B200) translates the SQL sentence specified by the parameter (B201) and executes the DB access (B202) to access the data (B401). When the SQL sentence is translated, the schema information (B301) is referred to. In the interpreter method, since the schema is referred only when the AP is executed, there is no problem such as guarantee of schema consistency. Next, the conventional compiler system will be described. In the case of preprocessing in the compiler system, FIG. 2
Is processed as shown in FIG. (1) C100 corresponds to FIG. C10
The embedded SQL sentence corresponding to 1 is from G104 to G10
9, G110, G111, G112, G113, G11
This is 6 sentences of 4. (2) The preprocessor (C200) provided by the DBMS receives C100 as an input, and
The statement is cut out (C201), and the SQL statement is compiled (C202). In the process of compiling the SQL, syntax analysis of the extracted SQL statement (C301), semantic analysis based on schema information (C302), generation of AL (C303), registration of AL in the AL storage area (C30)
Perform 4). It is assumed that an AL ID is given when an AL is registered. Here, the ID of the AL is G104 to G109...
0. G110 ... 352 G111 ... 353 G112 ... 354 G113 ... 355 G114 ... 356. The generation of this AL (C303) is further performed according to the procedure shown in FIG. At this time,
When an ID area, an intermediate variable area, and a cursor area are required, a logic to be secured in an area managed by the DBMS is generated (C801, C802, C803).
D for individual AL generated by SQL sentence
Logic that performs processing using resources in the area managed by the BMS is generated (C804-C806). (3) The AL call execution code is expanded based on the AL ID obtained in the above process (C203). This is performed for all SQL statements in C100 (I104, I104
111, I113, I115, I117, I119),
The result (C600) is output. At this time, C600 corresponds to FIG. (4) Convert C600 to a C language compiler (C
Compile with 700). As a result, executable AP
(C800) is generated. Thereafter, the execution of this AP is performed as shown in FIG. (5) AP (D100) is replaced by AP (C80)
0). (6) When AP (D100) is executed, A
The L call execution instruction is executed and the parameter (AL ID)
Control is transferred to the DBMS (D200). (7) In the DBMS (D200), the AL ID in the AL storage area is determined by the ID of the AL specified by the parameter.
Is specified (D201), AL is read (D202),
Read the time stamp information and schema in AL (D
202), execute AL (D203), and execute data (D
(401) is accessed. Since the DBMS of the compiler system does not perform syntax analysis, reference to schema information, and determination of an access path at the time of execution, it is possible to access the DB at a higher speed than the DBMS of the interpreter system. [0033] In the processing related to the AL in the AP execution process in the conventional DBMS of the compiler system, the following are arranged:-The AL is placed under the control of the DBMS (C500);-The intermediate variables in the AL; ID variable, cursor area is DB
It is generated under MS management (C801-C803).-Inquiry control processing in AL is performed under AL managed by DBMS.
Generated in the management area (C804-C807). To ensure the consistency of the schema by checking the time stamp of the AL in the AL management area and the version check of the schema of the DBMS (D203). It is slow because it performs search processing and read processing. -AP and AL are treated as completely independent modules and cannot be integrated. -Consistent constraints on the schema are severe, making it unsuitable for a distributed development environment. There was such a problem. The present invention has been made in view of the above,
The purpose is to access AL at high speed, AP
An object of the present invention is to provide a database query statement compiler method that enables integrated optimization and distributed development. In order to achieve the above object, a database query statement compiler system according to the present invention uses an intermediate variable during access logic processing in a precompile process of a relational database query statement. -ID variables and cursor areas are secured in the host language, and if, for, and during join processing and subquery processing
The control processing including le and goto is generated in the host language, and the processing of checking whether the schema value actually used in the access logic to be precompiled matches the schema value in the database management system is performed by the access logic. The gist is to generate it immediately before the part to be executed first. According to the database query statement compiler method of the present invention, in compiling an embedded query statement,
Intermediate variables, row-ID variables, and cursor areas during AL processing are generated in the host language, and if, sub-query processing during if,
By generating control processing including for, while, and goto in the host language, it is not necessary to search and load the AL, and the AL can be executed at high speed.
Enables integrated optimization of AP and AL (logic optimization, arithmetic optimization, register allocation of variables), and schema values actually used in precompiled AL and schema in DBMS By generating the process of checking the value match immediately before the first execution of the AL, the schema can be separated from the DBMS and distributed development becomes possible. (If there is a schema, it is possible to precompile without the DBMS. A), and if there is a change in the schema that does not affect the AP, A
Can be executed without changing P. Embodiments of the present invention will be described below with reference to the drawings. FIG. 1 is a diagram for explaining a precompilation process which is a compiler method of a database query sentence according to an embodiment of the present invention. An embodiment of the present invention will be described with reference to the data search program shown in FIG. First, the pre-process is performed as shown in FIG. (1) Assume that E100 in FIG. 1 corresponds to FIG. The embedded SQL sentence corresponding to E101 is G104
From G109, G110, G111, G112, G11
3, G114. (2) The preprocessor (E200) provided by the DBMS receives E100 as an input, and
The statement is cut out (E201), and the SQL statement is compiled (E202). In the process of compiling the SQL, the syntax analysis of the extracted SQL statement (E301), the semantic analysis performed based on the schema information (E302), the generation of the AL (E303), and the generation of the schema check code (E3)
04). The generation of this AL (E303) is further described in FIG.
The procedure is as follows. At this time, in the AL,
If you need an area, an intermediate variable area, and a cursor area, use A
The logic to be secured in P is generated in the host language (E
801, E 802, and E 803), and a logic for processing the individual AL generated by the SQL sentence using the area resources secured in the AP is generated (E 804).
-E806). (3) The AL obtained in the above process is generated as a description in the host language (E203), and the schema check code is generated immediately before the first execution part of the access logic (E204), and the result (E500) ) Is output. At this time, E500 corresponds to FIGS.
501 corresponds to J104. (4) Convert E500 to a C language compiler (E
Compile at 600). As a result, executable AP
(E700) is generated. Thereafter, the execution of this program is performed as shown in FIG. (5) AP (F100) is replaced by AP (E70)
0). (6) When the AP (F100) is executed, A
L is executed (F101), and the DB is accessed by the logic described therein. As described above, according to the present invention,
In the compilation process of the embedded database query language, the process of retrieving and loading AL from the DBMS management area is not required, so that high-speed access can be performed. AP and AL logic, arithmetic operation, and variable register allocation By performing integrated optimization such as the above, high-speed access execution becomes possible. Furthermore, the schema can be separated from the DBMS, and distributed development can be facilitated (if there is schema information, precompilation can be performed without the DBMS). Further, even if there is a change in the schema, if the change does not affect the AP, the change can be executed without changing the AP, thereby improving the availability.

BRIEF DESCRIPTION OF THE DRAWINGS FIG. 1 is a view for explaining a pre-processing process which is a compiler method of a database query sentence according to an embodiment of the present invention. FIG. 2 is a diagram showing an example of a data operation program for the embodiment of FIG. 1 and a conventional example. FIG. 3 is a diagram showing a pre-processing step (compiler method) of the present invention
FIG. 4 is a diagram illustrating an AL generation process in FIG. FIG. 4 is a diagram for explaining a process (compiler method) when executing an AP according to the present invention. FIG. 5 is a diagram showing a data retrieval program (in the case of a compiler system) after preprocessing in the embodiment, together with FIGS. 6 and 7; 6 is a diagram showing a data search program (in the case of a compiler system) after preprocessing in the embodiment, together with FIGS. 5 and 7. FIG. FIG. 7 is a diagram showing a data search program (in the case of a compiler system) after preprocessing in the embodiment, along with FIGS. 5 and 6; FIG. 8 shows a conventional preprocessing process (interpreter method).
FIG. FIG. 9 is a diagram for explaining a conventional AP execution process (interpreter method). FIG. 10 shows a conventional preprocessing process (compiler method).
FIG. FIG. 11 is a diagram for explaining a conventional AP execution process (compiler method). FIG. 12 is a diagram showing a conventional data search program after preprocessing (in the case of an interpreter system). FIG. 13 is a diagram illustrating an AL generation process in a conventional AP preprocessing process (compiler method). FIG. 14 is a diagram illustrating a conventional data search program after preprocessing (in the case of a compiler method).

Claims (1)

(57) [Claims] [Claim 1] In a precompile process of a relational database query statement, an intermediate variable, a row-ID variable, and a cursor area during access logic processing are secured in a host language, and a linking process is performed. , If, for, while,
A control process including "goto" is generated in the host language, and the process of checking whether the schema value actually used in the access logic to be precompiled matches the schema value in the database management system is first performed by the access logic. A database query statement compiler method characterized by being generated immediately before a part to be executed.