Three Suggestions for DB2 database design

Whether to install the DB2 database in Chinese or in English. If the installation program is started, you can use the/ilanguage option to specify the language used during the installation. The following describes the main content of the article.

1. Select the appropriate language and database character set.

When deploying a database in an enterprise, you must first install the database on the operating system. When installing the database, you need to select the language environment for installation. That is, the database is installed in Chinese or English. If the installation program is started, you can use the/ilanguage option to specify the language used during the installation. So far, the DB2 database has supported many languages. What language should the database be installed in? As long as the database administrator has a basic English language, it is recommended that you use an English language environment for installation. Although the Chinese language environment of the DB2 database has been well designed, I am still worried about some unknown vulnerabilities. Therefore, when installing the DB2 database, the author basically uses the English language environment for installation. Set the language to "EN" to English. Improve the efficiency of DB2 data backup and recovery.

In addition, if you want to save data other than English in the DB2 database, or you want to access the database using different character sets, you also need to select a specific database character set during database installation. All character data in the DB2 database, including data in the data dictionary, are stored in the database character set. If you use different character sets to access the database, the database administrator needs to select a superset that contains the character sets of all these users. Only in this way can the system easily use replacement characters to complete character conversion, thus improving database performance. If the selected character set is incorrect, some inexplicable problems may occur. If a user does not select the appropriate character set during database installation. Although there is no problem in storing Chinese characters during use. However, when the database is restored, it is found that some of the restored databases are originally Chinese characters, and garbled characters appear. This is mainly because the appropriate character set is not selected. Sometimes, if the character set is improperly selected, Chinese data cannot be imported smoothly when data is imported from an external data source (such as an Excel table. Therefore, when installing the database, the database administrator needs to select the appropriate character set based on the actual enterprise.

2. Evaluate the size and quantity of database objects.

The performance and stability of the DB2 database are directly related to the number and size of database objects. If there are few objects that are not complex, you can achieve high performance even if you do not plan well. If the object data is large and large, you need to plan well before designing the database. Otherwise, the database performance and stability will be greatly affected. In fact, a DB2 database is like a warehouse. objects in a database (such as indexes, data tables, and tablespaces) are like goods in a warehouse. If the number of goods is relatively small, the warehouse is open. Goods can be easily searched. However, if the number of goods is large, the storage space must be properly planned. Only in this way can the warehouse space utilization reach the optimal state. In addition, goods are stored in an orderly manner, which is particularly convenient for searching. I will take the Warehouse Management as an example to explain how to make a convenient assessment of the size and quantity of database objects and their impact on database performance and stability.

1. Plan the storage space based on the object size. The storage space should be planned based on the size of the goods in the warehouse. Or we should first prevent large goods. Only the utilization of such space is the highest. This is also true when planning DB2 objects. For example, some tables may contain many records and belong to large tables. In this case, the database administrator needs to consider whether to place it in an independent tablespace or hard disk space to improve the performance of data operations. The indexes corresponding to a large table are usually relatively large. Therefore, when the hardware conditions permit, you can store the index table and data table on different hard disks to improve the database performance. For small objects (such as data tables), you can store them in a tablespace. In fact, this tablespace is like paper boxes in the warehouse. Placing small objects in this paper box is not only free of space, but also easy to manage.

2. Plan the storage space based on the Usage Frequency of the object. When placing items in a warehouse, the goods or frequently-used items that will be used in the near future are often placed at the warehouse door or easily accessible. This makes it easier to get these goods without affecting other goods. This is also the case for objects in the DB2 database. Objects with large access volumes, such as indexes and data tables, can be stored on hard disks with good performance or on separate hard disks. In this case, accessing the data will not cause I/O conflicts with other objects, and the operation speed will be faster. Instead, it stores infrequently used objects together. Because they are rarely used, even if they are stored on hard disks with low performance, their negative impact on database performance is also very limited. How to update the execution plan in the DB2 database

3. Store database objects by category. When the goods are stored in the warehouse, they are also classified. And store the data according to the type. This is conducive to goods management and retrieval. In fact, this factor also needs to be taken into account in the design of the database object storage space. For example, when designing application software, many of them are designed based on modules. Therefore, when designing database objects, we also need to design the storage space based on this module. For example, store the database objects of the same module in the same tablespace. However, this may be contrary to the two above. In this case, it is best to write a post on the object name. For example, you can give the database objects the same prefix or suffix based on different modules. For example, if multiple tablespaces are used in the same module, the same prefix can be given to the tablespace. In this way, when you manage database objects, you can determine the modules that they belong to based on the prefix of the tablespace. It is more reasonable to add a suffix to indicate the classification of the database objects. Therefore, you need to perform classification management when managing database objects. It not only needs to be technically classified, such as indexing, data tables, and keywords. You also need to classify functions, such as by application modules.

3. Design a database backup and restoration solution.

After the database is delivered for production and use, a large number of tests are often required. However, a lot of junk data is often generated during the testing process. But it must be a clean database system for enterprise applications. Therefore, when designing a database, you need to think about how to reduce the junk data in the test process. Or how to implement the mechanism of automatically clearing junk data during interaction.

In general, we want a database backup and restoration solution to reduce the junk data produced by database testing. For example, when deploying databases for enterprises, a clean database system is usually installed first. Of course, character sets must be pre-configured. Then, use the database restoration function to restore the pre-defined database model.

In addition, two schemes are required to complement each other. For example, during database initialization, database restoration is used to create database objects. However, the database restoration method cannot be used because user data is already available during application software upgrade. Instead, you can use an application to execute some SQL code to adjust or add some database objects. No matter which method is used, the principle to be followed is to minimize testing when creating database objects for an enterprise. To achieve this, you must first create an object on the test server and test the object availability. Then, the relevant SQL code is directly executed on the database server in use.