Explore SQL via DB2 Tpc-c benchmark Implementation (1)

Brief introduction

Over the past 1.5 years, there have been many DB2 for LUW activities in the standard TPC-C benchmarks, which are often used to test performance in an online transaction processing environment. The result is a large span, and it may be a very small number on a machine with the usual configuration, and it may be a very large number in special configurations that are rare in homes and jobs.

Although the number of rows that populate the database schema may be adjusted to a certain scale, one place is still the same: SQL. In this article, you will recognize the logic of the TPC-C benchmark, understand the SQL technology, and discover how to use SQL technology in a real customer environment.

To do this, the author Serge Rielau to organize this article by first introducing the DB schema of the benchmark and its transactions. He then analyzes each transaction and interprets the SQL attributes of each transaction. At the end of this article, you should have a better understanding of the TPC-C benchmarks and the advanced SQL features of DB2, including the mechanisms behind them.

TPC-C: Benchmark Brief

The TPC-C benchmark uses a small group of transactions and tables to simulate a distribution enterprise that has a product sales cycle. The external schema and semantics of each transaction are formally specified. To briefly outline and provide a basic pattern for use, here's a summary. Where the table is marked in bold and the transaction is displayed in italics:

The database used for this benchmark consists primarily of warehouses (warehouses), regions (districts), products (items), and customers (customers). There are 100,000 ITEM in the stock (inventory) in each WAREHOUSE. Each warehouse is serviced by 10 DISTRICT. Each area is 3,000 customer service, these customers order (ordering) new products. A maximum of 15 different products, called order_line (Order items), can be made up of each order. Before the order is shipped, the order is queued as a new_order, and the inventory of each product in the warehouse is adjusted ... In the DELIVERY (delivery), to the customer charges. When PAYMENT (paid), the transaction is archived in HISTORY (history) and the revenue is billed.

In addition, customers can inquire about their order status, and the warehouse administrator can inquire about the stock levels (inventory level) of the product ordered.

To be more realistic, customers tend to forget their customer IDs, so they must be able to find the customer ID by the user's last name, but the customer's last name may be duplicated. Also, the local warehouse may not be able to meet all the orders, which require shipments from a distant warehouse.

The number of new orders that a warehouse can handle is limited to between 9 and 12.86 transactions per minute. This means that at least 256,000 warehouses are needed to achieve the 3.2 million tpmc (NEW order transactions per minute tpc-c). This means that there are 7.7 billion registered customers. So unless you have enough disks and a big fuse, don't try.