DB2 optimizes data processing from six hours to 20 minutes (1)

I'm glad that you have read this article. First of all, you need to explain that this article is suitable for beginners and developers who are new to DB2 to read. If you think it is good, you can bypass it.

This article is also a summary of my own experience. Some time ago, I made a project for the Bank. Because the bank data was developed by a third-party company, we needed to handle the interface data ourselves. After several negotiations, finally, we decided to process the data ourselves. When we see the data files sent from the data center, we have to analyze the table relationships and handle 16 tables. After a week of analyzing the table relationship, we began to prepare how to organize 16 table data into an interface table required by our system. This is also a challenge.

The following describes the data processing requirements:

1. retrieve one table data from 16 tables based on the business relationship.

2. Half of the txt files corresponding to the 16 tables provided by the Bank have more than one million data records. And every day is full

3. You must import 16 tables to the DB2 database every day. Before importing, you must clear 16 tables.

4. Data processing must be completed in the morning. Business During the day cannot be affected.

According to the above requirements, I began to process data by hand. When I thought of data processing, I used the DB2 stored procedure. However, for example, clearing tables affects efficiency.

First, optimize table clearing

In the beginning, we used

Db2 delete from tablename;

However, this speed is extremely slow. If delete is used, logs are recorded, which affects the database performance.

Later we used:

Db2 import from d: \ empty. del of del replace into tablename

Empty. del is an empty file. In this way, it takes only a few seconds to clear a table.

You can also use:

DECLARE VAR_ SQL varchar (128 );--
SET VAR_ SQL = 'alter TABLE AUTEK. UTMCA ACTIVATE NOT LOGGED INITIALLY WITH EMPTY table ';--
Execute immediate VAR_ SQL; -- such deletion is also very fast

Second, deletion Optimization

In the process of processing DB2 data, the delete operation is rarely used, and the select operation is much faster than the delete operation.

Third: reorganize the table structure

When we use load from data, sudden interruption will lead to SQL0668N. For details, refer:

Http://www.bkjia.com/database/201203/122520.html

In this case, we need db2 load from d: \ empty. del of del terminate into tablename.

Author cuiran