Basic concepts of DB2-instances, databases, tablespaces, and containers

DB2 basic concepts-instances, databases, tablespaces, containers DB2 supports two types of tablespaces: 1. System Management memory tablespaces (SMS-SYSTEM managed storage) 2. Database Management memory tablespace (DMS-DATABASE managed storage) default tablespace: DB2 creates three tablespaces by default when creating a database: These tablespaces are in SMS mode by default. They are: SYSCATSPACE: Include system cataloguing TEMPSPACE1: Save temporary table USERSPACE1: contains the basic concepts of user data www.2cto.com DB2 from top to bottom in DB2: Instance ), database, table space, and Container are in the same operating system, DB2 data service can run multiple instances at the same time (unlike Oracle, only one instance can be started 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) SMS (System manegement Space) mode DMS and SMS mode are specified when the table Space is created, and cannot be converted after the table Space is created. 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 tablespaces (SysCatSpace) system temporary tablespaces (SysTempSpace) User tablespaces (UserSpace) user temporary tablespaces (UserTempSpace) A database must have two basic system tablespaces, system catalog tablespace and 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, Directory, and device. They are used in DMS-based tablespaces. directories are used in SMS-based tablespaces, which do not require manual management of data storage Files, DB2 can automatically add storage files to 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. The performance of a database affects the performance of a database mainly by the following factors: Disk Memory processor (CPU) Network (Network), which is the most significant Disk, 90% of the performance bottleneck may come from disk IO competition. Secondly, the memory, on the one hand, refers to the total amount of physical memory to meet the demand, and on the other hand, refers to the correct configuration of memory-related configuration parameters; of course, the performance of the processor is also very important. Multi-Channel CPUs will play a significant effect on complex SQL queries that rely on computing power; www.2cto.com is not a major factor in the network, but an objective environmental factor, it means that a slow network speed will affect data transmission. The following lists some effective methods for improving database performance: for servers running database services, 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 disk IO competition between data storage and 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 www.2cto.com 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.
The parameter configuration of the main performance-related parameter 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.