The db2 tutorial is: DB2 UDB V8.1 management learning notes (1 ). Basic concepts of DB2
Several Concepts from top to bottom in DB2:
Instance ),
Database ),
Table space ),
Container)
In an operating system, DB2 data service can run multiple instances at the same time (different from Oracle, which can only start one instance in one system ).
A database is defined in an instance. An instance can contain multiple databases. Different databases in the same instance are completely independent and have their own system cataloguing tables.
Table space can be managed in two ways:
DMS (Database management Space) Method
SMS (System manegement Space) Method
DMS and SMS are specified when the tablespace is created. After the tablespace is created, the table cannot be converted. In DMS, a tablespace corresponds to one or more containers, and the Container specifies the physical storage location of the data. For the SMS mode, only one directory can be specified and cannot be added.
Tablespaces have the following types:
System catalog tablespace (SysCatSpace)
System temporary tablespace (SysTempSpace)
User tablespace)
User temporary tablespace (UserTempSpace)
A database must have two basic system tablespaces: The system catalog tablespace and the system temporary tablespace. Any object created in the database is reflected by adding a record to the system catalog tablespace. For temporary tablespace, the disk size occupied by the temporary tablespace is dynamically scaled according to usage, that is, the disk space is allocated only when necessary and recycled after use. In addition, if you want to create a table, you need to create a user tablespace. If you want to use a temporary table, you need to create a user temporary tablespace.
There are three types of containers:
Files
Devices Device
Directory
Files and devices, used for table space in DMS mode;
Directory, which is used for table space in the SMS mode. In this mode, you do not need to manually manage data storage files. DB2 can automatically add storage files in the directory as needed, as long as the disk space permits.
In essence, the tablespace is the logical location definition of data storage, while the container is the physical location definition of data storage.
Database performance
The following factors affect the performance of a database:
Disk)
Memory (Memory)
CPU)
Network)
Among them, disks are the most significant, and 90% of performance bottlenecks may come from disk IO competition;
The second is the memory. On the one hand, the total amount of physical memory must meet the requirements, and on the other hand, the memory-related configuration parameters should be correctly configured;
Of course, the performance of the processor is also very important. Multi-Channel CPUs will play a significant effect on complex SQL queries that depend on computing power;
The network is not a major factor, but an objective environmental factor. It refers to the impact of a slow network speed on data transmission. The following lists some effective methods for improving database performance:
For servers running the database service, you can configure as many physical disks as possible. The capacity of each disk does not need to be too large. This effectively shares the IO competition between data storage and the disk during read operations. That is, the performance of disks with multiple small capacities is superior to that of disks with only one large capacity.
If conditions permit, try to make the data storage service and the operating system run on separately physically separated disks.
Table Space is managed by DMS (Database Management Space.
Create multiple tablespaces on different physical disks. Data and indexes can then be stored in different tablespaces, which can significantly improve performance. You can also split a frequently used large table into multiple small tables vertically, store them in different tablespaces, and then combine them with a view.
The DB2 server can manage bare devices. Apart from the operating disks of the system and DB2 service, it can separately prepare disks for the storage of DB2 data, which can be multiple disks. The disks do not need to be formatted After partitioning, after a bare device is created, it is directly handed over to DB2 for management and used to store data.
The temporary tablespace of the system has a great impact on the database performance. When the managed physical memory cannot meet the needs of database operations, DB2 writes the temporary data to the disk, the system temporary tablespace is used, and this situation often occurs.
Try to place data close to the inner track of the disk because the access speed of the disk is fast.
Performance-related parameters
The parameter configuration of DB2 is divided into two levels: instance level and database level. Parameters that have a major impact on data service performance are mainly configured at the database level. The following are three important memory configuration parameters:
Bufferpagelocklistsortheap
Bufferpage: shared by all objects in the same database.
Sortheap: Memory swap area used for sorting. It is not shared and should not be set to too large. Otherwise, it will easily cause memory depletion because each transaction will apply for independent memory for sorting.
There are two pages in this news. Currently, there are two pages in page 1st.
