JP5096775B2 - Data processing system - Google Patents

Description

  The present invention relates to a data processing system, in particular, in response to a request from a client terminal, an AP server requests a DB server to extract necessary data, performs necessary processing on data transmitted from the DB server, The present invention relates to a data processing system having a mechanism for returning a processing result to a client terminal.

Along with the development of client-server systems, in addition to client terminals that display data, an AP server that processes data and a DB server that is responsible for data storage are provided in order to respond to requests for larger-scale information processing. In addition, so-called three-layer client-server systems have become widespread.
In order to improve the processing speed, a load is distributed by arranging a plurality of AP servers in parallel.
JP 2005-165610 A

Since it is necessary to maintain data synchronization for the DB server, it cannot be shifted to distributed processing as easily as the AP server. Of course, database system vendors have been using various technologies to speed up the DB server itself from both the software and hardware sides, and as a result, certain results have been achieved. It cannot be denied that prices will rise.
On the other hand, since the AP server can be configured with an inexpensive PC server, it is relatively easy to increase the processing speed by increasing the number of installed servers, and data processing can be performed by using the technique of Patent Document 1. Can be achieved to some extent.

However, in the case of a conventional three-tier client-server system, in order to respond to a request from a client terminal, it is complicated to access a plurality of tables to extract a specific range of data or to command an arithmetic process between specific data A SQL statement is generated on the AP server and issued to the DB server.
In this way, in order to issue complex SQL statements, it is natural that development of application programs set up on the AP server becomes difficult, but the problem is that maintainability and reusability are reduced. That is.

In other words, in order to generate a complex SQL statement, it is necessary to develop an application program that is conscious of the structure of each table stored in the DB server in addition to the business logic. The code format is specialized for logic and table configuration.
As a result, it becomes difficult to apply a part of this application program to other business processes, and when the DB server table configuration is changed, a large correction is required.

  In addition, issuing a complicated SQL commanding the extraction of a specific range of data from a plurality of tables to the DB server may increase the burden on the DB server.

  The present invention has been devised to solve the above-mentioned problems of conventional data processing systems, and it collects data necessary for business processing without issuing complicated SQL statements to the DB server.・ The purpose is to provide technology that can be processed and that can improve the reusability and maintainability of application programs used in AP servers.

In order to achieve the above object, a data processing system according to claim 1 includes a DB server storing a plurality of tables and an AP server, wherein the AP server includes a business processing unit, a data management unit, The table includes a plurality of table-corresponding units that correspond one-to-one with each table of the DB server, a general-purpose processing unit, and a memory. The data management unit includes a table that stores data necessary for business processing, and each table. The table corresponding parts that are responsible for the above are recognized, each table corresponding part is aware of the structure of the table that each is responsible for, and the general-purpose processing part is for the sort processing function, innerJoin processing Function, leftOuterJoin processing function, sumUp processing function, average processing function, match processing function, controlBreak processing function, mergeBreak processing function Have The business processing unit, if the transaction is transmitted from the client terminal, along with determining the type of the transaction, and executes the processing for instructing acquisition of data necessary for the data management unit receives the command The data management unit specifies a table storing data necessary for processing the transaction, specifies a table corresponding unit in charge of the table, and executes processing for instructing acquisition of data necessary for the table, Each table corresponding unit that receives this command generates an SQL statement for acquiring necessary data from the table that it is in charge of, and sends it to the DB server in units of tables, and also sends the SQL unit for each table sent from the DB server. data, perform a process of storing in the memory, the business processing unit, data of the stored table unit in the memory To data, executes data processing corresponding to the transaction, the result of data processing by the business processing unit, when the data of the table unit stored in the memory is updated, the data management unit in charge table A process for instructing the corresponding unit to update the data is executed, and the table corresponding unit that has received this instruction generates an SQL statement for requesting to update the table in charge based on the data in the memory. A data processing system for executing processing to be transmitted to a DB server, and further, for the general-purpose data operation that does not depend on individual business when the business processing unit executes data processing corresponding to the transaction, A function of the processing unit is used, and the data management unit performs a test stored in the memory prior to processing by the function of the general-purpose processing unit. Execute the process of assigning the data in the table unit to a general-purpose record type that allows each data to be handled simply by the column name. The function of the above-mentioned general-purpose processing unit is necessary for this record-type data. It is characterized by executing various processes .

The data processing system according to claim 2 is the system according to claim 1, and each of the table corresponding units extracts a necessary data range when extracting a predetermined range of data from the table in charge thereof. It is divided into a plurality of sections, issues SQL statements for each divided range unit, stores data sent from the DB server sequentially in memory, and finally aligns and connects the data .

In the data processing system according to claim 1, the SQL is issued from the AP server to the DB server in units of a single table, and once the data is stored in units of the table in the memory of the AP server, the table is stored. It has a mechanism to execute data operations such as connection, sorting, and arithmetic processing between them on the AP server side.
In this way, the processing that was previously executed on the DB server side by issuing complex SQL is replaced on the AP server side, so the load on the DB server can be reduced and the processing speed of the entire system is improved. Can be made.
Also, by subdividing SQL issuance into table units, application programs on the AP server side can also be subdivided, and its maintainability and reusability can be improved.
In addition, the general-purpose processing unit of the AP server is equipped with multiple functions for executing common data operations that do not depend on individual tasks such as matching, sorting, and control break processing. Since these functions use these functions, there is an advantage that the development of a program for realizing the business processing unit is facilitated.
Prior to the processing of the general-purpose processing unit, the data in the memory is assigned to the general-purpose record type by the data management unit, so each function of the general-purpose processing unit is simple without being aware of the data type specific to each data. Each data can be manipulated based on the column name. As a result, the reusability of the program for realizing the general-purpose processing unit can be improved.

In the data processing system according to claim 2, by dividing and issuing the SQL statement for a single table, parallelization of the search in the DB server is realized, and the DB server resources can be effectively used. .
In addition, the process of aligning the divided and acquired data and connecting them to each other is performed on the AP server side, so there is no excessive load on the DB server.

FIG. 1 is an overall configuration diagram of a data processing system 10 according to the present invention, and this system 10 includes a plurality of AP servers 12, a DB server 14, and a load balancer (load balancer) 16.
The load balancer 16 and each AP server 12, and each AP server 12 and DB server 14 are connected by a network.
A large number of client terminals 20 are connected to each AP server 12 via a network such as an intranet or the Internet and a load balancer 16.

The DB server 14 includes a DB management system (DBMS) 21 and various tables 22 for business processing.
The DB management system 21 performs input / output of data stored in each table 22, update, predetermined calculation, and the like.

Each AP server 12 includes a business processing unit 24, a data management unit 26, a general-purpose processing unit 28, a DB access processing unit 30, and a memory 32.
The business processing unit 24, the data management unit 26, the general-purpose processing unit 28, and the DB access processing unit 30 are realized by the CPU of the AP server 12 executing necessary processing according to a corresponding application program.

  The DB access processing unit 30 includes a plurality of table corresponding units 34 that correspond one-to-one to the respective tables 22 of the DB server 14. Each table corresponding unit 34 recognizes the configuration (column name, data type, etc.) of the table 22 in charge, issues an SQL statement addressed to the table 22 to the DB server 14, and from the DB server 14 A function of storing the transmitted record in the memory 32 is provided.

  The data management unit 26 recognizes the correspondence between each table 22 and the table correspondence unit 34. Further, the data management unit 26 grasps the combination of tables necessary for each business.

  The general-purpose processing unit 28 includes a group of functions for executing processes common to data operations that do not depend on individual tasks, such as matching processing, sorting processing, and control break processing. Hereinafter, an example of data processing executed by the general-purpose processing unit 28 will be shown.

(1) innerJoin
As shown in FIG. 2, this is a process of matching two files A and B with designated key items, concatenating the data of items existing in both files and outputting them to a file.

(2) leftOuterJoin
As shown in FIG. 3, two files A and B are matched with designated key items, records existing in both files are concatenated and output to a file, and for records that exist only in either file, In this process, records existing in the quasi file (file A) are output to the file.

(3) sumUp
As shown in FIG. 4, this is a process of summing up the values of specific items based on the designated key items and outputting them to a file.

(4) avarage
As shown in FIG. 5, this is a process of calculating an average value of specific item values based on the designated key item and outputting it to a file.

(5) match
This is a process in which a transaction and a master file are matched with a specified item key, and different outputs are executed depending on whether or not they match.
For example, as shown in FIG. 6, the master and the transaction are matched using the product code as a key, and if they match, the master product name is set in the transaction item and output to a file. For product codes that do not exist in the transaction, this is indicated on the screen.

(6) controlBreak
In this method, a transaction is read and a continuation process is performed for each record, and a break process designated at a timing when the break key changes is executed.
For example, as shown in FIG. 7, the total sales quantity and the average sales quantity are calculated for each product classification code and output to a file.

(7) mergeBreak
In this method, a transaction and a master file are merged based on a designated item key, and a break process is executed.
For example, as shown in FIG. 8, the sales volume of a transaction is totaled for each product code, and when the product code breaks, if there is a match, the master product name is set in the transaction item and only the transaction If it does not exist, set the product name as ****** and output it to a file. If it exists only in the master, the product code is displayed on the screen as no sales.

  The general-purpose processing unit 28 can replace the data operation originally executed by the DB server 14 by using the above-described function group.

  The load balancer 16 serves to distribute the request transmitted from the client terminal 20 according to the load applied to each AP server 12.

  The client terminal 20 is composed of a computer such as a PC, and in addition to the OS, a Web browser program and a dedicated application program are set up.

  FIGS. 9 to 13 show specific examples of the table 22 stored in the DB server 14. The store table 40 (FIG. 9), the delivery group table 42 (FIG. 10), and the delivery schedule table 44 (FIG. 11). The inventory table 46 (FIG. 12) and the ordering table 48 (FIG. 13) are provided.

As shown in FIG. 9, the store table 40 has data items of store codes and store names.
The delivery group table 42 is a table for classifying stores according to delivery groups, and has a delivery group code and store code data items as shown in FIG.

  As shown in FIG. 11, the delivery schedule table 44 includes data items of delivery group code, AM, and PM. “1” is set in AM for a delivery group in which AM delivery is set, and “0” is set in AM in a delivery group in which AM delivery is not set. Similarly, “1” is set in PM for a delivery group in which afternoon delivery is set, and “0” is set in PM in a delivery group in which no afternoon delivery is set.

As shown in FIG. 12, the stock table 46 has data items of product code and stock quantity, and the stock quantity for each product is recorded.
As shown in FIG. 13, the order table 48 includes data items of a store code, a product code, and an order quantity, and information on the accepted order is recorded.

Next, a data processing procedure in the system 10 will be described with reference to the flowchart of FIG.
First, when a product ordering transaction is output from any client terminal 20, any AP server 12 receives this transaction via the load balancer 16 (S10).

An example of the transaction format is shown in FIG.
The transaction 50 includes an area 52 indicating a transaction type and a data area 54.
In the case of an order transaction, data indicating “order” is set in the type area 52. Further, the data area 54 includes a product code, a store code, and an order quantity. Here, “S0001” is set as the product code, “M001” is set as the store code, and “25” is set as the order quantity.

On the other hand, the business processing unit 24 of the AP server 12 analyzes the data set in the type area 52 and determines the transaction type (S12).
Here, since the transaction type is “ordering”, the business processing unit 24 executes processing of the ordering transaction.

  First, the business processing unit 24 provides the data management unit 26 with data corresponding to a store code and a store name, data of a delivery group to which the store belongs, and delivery schedule data of each delivery group as data necessary for executing the ordering process. Request the stock quantity data and order management data of the target product.

  In response to this, the data management unit 26 specifies the store table 40, the delivery group table 42, the delivery schedule table 44, the inventory table 46, and the ordering table 48 as the data storage source (S14), and then performs DB access processing. The corresponding table corresponding unit 34 in the unit 30 is instructed to acquire necessary data (S15).

On the other hand, each table corresponding unit 34 generates an SQL statement for acquiring a record from the table 22 that it is in charge of, and issues it to the DB server 14 (S16).
Upon receiving this, the DB management system 21 of the DB server 14 extracts a record from the corresponding table 22 and transmits it to each table corresponding unit 34 of the AP server 12.
Receiving this, each table corresponding unit 34 (S18) stores the transmitted data in the memory 32 (S20).

  The business processing unit 24 executes processing necessary for the ordering transaction based on the data on the memory 32. At this time, the business processing unit 24 uses a function group included in the general-purpose processing unit 28.

First, the business processing unit 24 identifies a delivery group related to the store for which an order has been placed (S22).
Specifically, for ordering transaction data and delivery group data, after sorting by store code as a key, matching is also performed using the store code as a key, and the delivery group code associated with the store that placed the order is specified. Assigned to purchase order transaction data.
Then, the ordering transaction data and the delivery schedule data are sorted using the assigned delivery group code as a key, and the delivery group code is matched with the delivery group code as a key, so that the delivery group (AM only, PM only, AM and PM) Is identified.

A function group included in the general-purpose processing unit 28 is used in the sorting process and the key value matching process.
When the general-purpose processing unit 28 operates, the data on the memory 32 is assigned to a general-purpose record type by the data management unit 26.
In this way, by assigning to a general-purpose record type prior to the operation of the general-purpose processing unit 28, the general-purpose processing unit 28 can simply handle each data with a column name without being aware of the data type. Thus, general-purpose processing is realized.
When the data operation by the general-purpose processing unit 28 is completed, the data management unit 26 converts the processing result data into a unique data type corresponding to the business processing unit 24.

Here, in order to deliver in the morning delivery and the afternoon delivery, time periods for receiving orders are determined in advance. Therefore, the business processing unit 24 determines whether or not it is a time zone in which an order request for the store can be accepted based on the delivery group of the store where the order has been placed and the current time (S24).
When it is not an orderable time zone (S24: No), the following processing is skipped.

  When it is time for ordering (S24: Yes), the business processing unit 24 checks whether the ordered product is in stock (S26). For example, inventory data is searched using the product code as a key, and the inventory quantity and the ordered quantity are compared.

When the stock quantity is smaller than the order quantity (S26: No), the following processing is skipped.
When the stock quantity is not smaller than the order quantity (S26: Yes), the business processing unit 24 updates the stock data and the order data on the memory 32 (S28).

When the data on the memory 32 is updated, the data management unit 26 instructs the table corresponding unit 34 in charge of the inventory table 46 and the table corresponding unit 34 in charge of the ordering table 48 to update the record (S29).
Receiving this, each table corresponding unit 34 acquires necessary data from the memory 32 based on this command, generates an SQL statement for a single table, and then issues it to the DB server 14 (S30).
As a result, the correspondence table 22 on the DB server 14 is updated (S32).

With the procedure described above, the processing related to the ordering transaction is completed.
Thereafter, the task processing unit 24 returns the result of the above processing to the client terminal 20 that is the transaction transmission source.
That is, when the ordering process is completed, a message to that effect is returned, and when the ordering process is not completed, the reason is returned.

According to the present embodiment, substantial processing for data on the DB server 14 is performed by the AP server 12, not the DB management system 21.
Since there are a plurality of AP servers 12, it is possible to distribute processing that has been concentrated on the DB server 14 in the past.
Therefore, the load on the DB server 14 is reduced compared to the conventional case, and the processing speed of the entire system 10 is improved.

  Moreover, the SQL statements issued from the AP server are all targeted for a single table, and the syntax is simplified, reducing the load on the DB server 14 per SQL and enormous SQL It is possible to avoid an unexpected system slowdown due to the execution of.

In addition, when issuing an SQL statement from each table corresponding unit 34 to the table 22 in charge, it can be instructed to obtain a predetermined range of data instead of obtaining the necessary range of data at once. .
For example, if it is necessary to extract 10,000 records from a specific table 22, the database server 14 side resources (usually, by issuing SQL statements in 10 batches that require 1,000 records to be acquired) (Equipped with multiple CPUs) can be used effectively to achieve high speeds for huge demands.
In this case, each table corresponding unit 24 sequentially stores the divided and acquired data on the memory 32, and aligns and combines the data when all the data is obtained.

  The process of integrating the results obtained by dividing the search by parallel processing of such a search requires a relatively large load. However, this is not done on the DB server 14 side, but on the AP server 12 side that facilitates distributed processing. The efficiency of the entire system can be achieved.

  As described above, since the SQL statement is issued from the DB access processing unit 30 of the AP server 12 in units of a single table, the program for realizing the DB access processing unit 30 is simplified, and accordingly In addition to ease of development, there is an advantage that program reusability and maintainability are improved.

  In the process on the AP server 12 side, the data operation itself that was conventionally performed by the DB server 14 is processed through the function group of the general-purpose processing unit 28, so the business processing unit 24 can specialize in business logic, and the code Can be simplified.

As described above, the data brought from each table 22 is assigned to the record type, which is an abstract data type, by the data management unit 26, and then used for data operation by the general-purpose processing unit 28. Therefore, the general-purpose processing unit 28 does not need to recognize a specific data type for each table 22, and can ensure the versatility.
Furthermore, when the data processing by the general-purpose processing unit 28 is completed, the data management unit 26 converts the processing result data into a unique data type corresponding to the business processing unit 24. It is possible to perform error checking based on.

  As described above, the data management unit 26 recognizes a specific table combination required for each business unit, and the configuration of the table 22 (column name) that each table corresponding unit 34 of the DB access processing unit 30 takes charge of. Therefore, the business processing unit 24 does not need to be aware of the table structure on the DB server 14 side. Therefore, not only the coding of the business processing unit 24 is facilitated, but also the code of the data management unit 26 and the corresponding table corresponding unit 34 need only be corrected even if the table structure is changed. The advantage of 24 codes is that they can be used as they are.

Before the data from the DB server 14 is stored in the memory 32, each table corresponding unit 34 of the DB access processing unit 30 may convert each data type into a primitive type.
For example, by using the byte array type instead of the String type and the double type instead of the BigDecimal type, the memory consumption can be reduced on the order of several tens of times, and the number of data that can be handled on the AP server 12 is greatly increased. It becomes possible.
In this case, after the data operation by the general-purpose processing unit 28 is completed, the data management unit 26 reconverts it to the original data type.

1 is an overall configuration diagram of a data processing system according to the present invention. It is explanatory drawing which shows the function "innerJoin" performed by a general purpose process part. It is explanatory drawing which shows the function "leftOuterJoin" performed by a general-purpose process part. It is explanatory drawing which shows the function "sumUp" performed by a general purpose process part. It is explanatory drawing which shows the function "avarage" performed by a general purpose process part. It is explanatory drawing which shows the function "match" performed by a general purpose process part. It is explanatory drawing which shows the function "controlBreak" performed by a general purpose process part. It is explanatory drawing which shows the function "mergeBreak" performed by a general purpose process part. It is a figure which shows an example of a shop table. It is a figure which shows an example of a delivery group table. It is a figure which shows an example of a delivery schedule table. It is a figure which shows an example of an inventory table. It is a figure which shows an example of an order table. It is a flowchart which shows the procedure of the data processing by this system. It is a figure which shows an example of the format of a transaction.

Explanation of symbols

10 Data processing system
12 AP server
14 DB server
16 Load balancer
20 Client terminal
21 DB management system
22 tables
24 Business Processing Department
26 Data Management Department
28 General-purpose processing unit
30 Access processing section
32 memory
34 Table support section
40 store table
42 Delivery group table
44 Delivery schedule table
46 Stock table
48 Ordering table
50 transactions
52 Type area
54 Data area

Claims (2)

It has a DB server that stores multiple tables and an AP server.
The AP server includes a business processing unit, a data management unit, a plurality of table corresponding units corresponding to each table of the DB server, a general-purpose processing unit, and a memory.
The data management unit recognizes a table storing data necessary for business processing and a table corresponding unit responsible for each table,
Each table-corresponding unit recognizes the configuration of the table that it is in charge of,
The above general-purpose processing unit is a sort processing function, innerJoin processing function, leftOuterJoin processing function, sumUp processing function, average processing function, match processing function, controlBreak processing function, mergeBreak processing Has one or more of the functions for
When the transaction is transmitted from the client terminal , the business processing unit determines the type of the transaction and executes a process for instructing the data management unit to acquire necessary data.
The data management unit that has received this command specifies a table that stores data necessary for processing the transaction, specifies a table corresponding unit in charge of the table, and instructs the acquisition of data necessary for this Run
Each table corresponding unit that receives this command generates an SQL statement for acquiring necessary data from the table that it is in charge of, and sends it to the DB server in units of tables, and also sends the SQL unit for each table sent from the DB server. data, perform a process of storing in the memory,
The business processing unit performs data processing corresponding to the transaction on the data of the table stored in the memory,
As a result of the data processing by the business processing unit, when the data in the table unit stored in the memory is updated, the data management unit executes a process for instructing the responsible table corresponding unit to update the data. ,
The table corresponding unit that has received this instruction is a data processing system that generates a SQL statement that requests updating of the table that it is in charge of based on the data in the memory, and executes processing to send to the DB server,
Furthermore, when the business processing unit executes data processing corresponding to the transaction, the general processing unit function is used for general data operations that do not depend on the individual business,
Prior to processing by the function of the general-purpose processing unit, the data management unit substitutes the data of the table stored in the memory into a general-purpose record type that allows each data to be handled simply by a column name. Execute the process to
A function of the general-purpose processing unit performs a necessary process on the record type data . When each table corresponding unit extracts a predetermined range of data from its own table, it divides the necessary data range into a plurality, issues an SQL statement for each divided range, and is sent from the DB server. 2. The data processing system according to claim 1 , wherein the data is sequentially stored in a memory, and finally the data are aligned and combined .

Images