Copyright Notice: This article can be reproduced, please be sure to indicate the original source and author information in hyperlink when reprint. Original Source: http://www.aiview.com/notes/db2_note.htm Author: Zhang Yang Alex_doesAThotmail.com Last updated: 2004-8-5
table of Contents
Reference resource
In DB2, in an instance, a database, a TableSpace, a ContaSpace, and the like: In an operating system, DB2 data services can run multiple instances at the same time (different from Oracle in one system) Only one instance can only be used), the database is defined in an instance, and an instance can contain multiple databases. Different databases in the same example are completely independent and have their own independent system catalogs. Table space is divided into DMS mode and SMS (System Manegement Space), defined in the database, and there must be two system basic tablespaces in a database, namely system catalog table space (Syscatspace) and system temporary table space (SystemPspace) . Any object created in the database is embodied in a manner in which the record is added in the system catalog table space. For temporary tablespace, its occupied disk size is dynamically expanded according to the usage, that is, allocating disk space only when needed, and Recycling after use. In addition, if the user needs to create a table, you need to create a user table space (UserSpace) If you need to use a temporary table, you need to create a user temporary table space (UserTempSpace). The DMS is specified when the SMS type is established in the table space, and it cannot be converted after it is built. For DMS mode, a table space corresponds to one or more containers (Container), and the container specifies the physical storage location of the data. For SMS mode, you can only specify a directory and cannot be increased. The container is divided into three types. The first two are files and devices for tablespaces for DMS methods; there is a directory for tablespaces for SMS mode, this method does not require manual management data storage files, DB2 can Automatically add storage files in the directory according to the situation, as long as the disk space is allowed. In essence, the tablespace is the logical location definition of data storage, and the container is the physical location definition of data storage. The performance of a database is mainly based on the following factors: Disk (DISK), Memory, Processor (CPU), Network. Among them, the magnetic disk is most significant, 90% of the performance bottleneck may come from the disk IO competition; second is memory, one side refers to the total amount of physical memory to meet the demand, and on the other hand, the configuration parameters related to the memory should be correctly configured Of course, the performance of the processor is also very important. How many CPUs will have a significant effect on which complex SQL queries dependent computing power; the network is not mainfaster, which is an objective environmental factor, refers to the slow network speed. The transmission of data has affected. The following is listed below: Methods for increasing database performance: Configure multiple physical disks for servers running database services, and the capacity of each block does not have to be too large, which can effectively share data storage and read operation processes IO competition. That is, a multi-small-capacity disk is superior to only one large-capacity disk. If the conditions are allowed, try to make the data storage service and the operating system on the physical separated disk. Table space management method in DMS (Database Management Space). Create multiple tablespaces on different disks. You can then store data and indexes in different tablespaces, which can significantly improve performance. It is also possible to remove one of the frequent large blasters into multiple small tables, which are stored in different tablespaces, and then combined with one view. DB2 servers can manage naked devices, that is, in addition to the system and the DB2 service running the disk, to store the DB2 data separately, can be multi-block, no formatting after the partition, create a naked device to DB2 directly to DB2 for management, Storing data.
The temporary table space of the system has a lot of database performance. When the management of physical memory cannot meet the needs of database operations, the temporary data will be written to disk, and the system temporary table space is used, and this The situation will occur frequently. Try to place data from the position of the disk near the innermost track because the access speed of the disk is faster. The parameter configuration of DB2 is divided into two levels, one is an instance level, and the other is the database level. Parameters that have greater impact on data service performance are mainly configured in the database level. The following is three important memory configuration parameters: bufferpage: Share by all objects in the same database. SortHeap: Memory switching area for sorting, non-sharing, should not set too much, otherwise, it is easy to cause memory consumption, because each transaction will apply for independent memory for sorting. LockList: Shared memory and is used to record the lock created in the data service run. It is recommended to set up about 20MB and adjust it according to the actual situation. DB2 uses a row lock using a row lock. If the setting is too small, when the lock record is too much, it will cause insufficient memory. At this time, the DB2 will upgrade multiple line locks to a table lock, which will greatly reduce the concurrency performance of the application. . If the setting is too large, the multi-allocated memory will rarely be used, resulting in waste. Some other configuration parameters: NUMDB: Simultaneous instance number DB2 can be started: DB2IList lists DB2 List DASLIST List in the current system to list DAS DB2 LIST DATABASE DIRECTORY List the database defined in the current instance DB2 list TableSpaces lists tablespace DB2 List Tabses [for all] in the current database lists the table DB2 List Active DB lists of DB2 List Active DBs in the current database DB2 Get DBM Config Get DB CFG for DatabaseName DB2 Update DB CFG for DatabaseName Update DB CFG for DatabaseName Update DB CFG for DatabaseName 600M DB2 Alter BufferPool IABMDEFAULTBP SIZE = 1 DB2 List Applications Show Detail Order You can add "Show Detail" parameters later to display details. The page size of DB2 data store can only be specified in the tablespace level (different from Oracle, which can be defined in the table level) and cannot be modified after it is built. You can handle a page size of 4K DMS user temporary table space, and then delete the system's default SMS system temporary table space. In order to meet the application needs, a user temporary table space of 8k or more should be established again. DB2 UDB V8.1 is not well supported by Redhat Linux 9. The GUI installer cannot be started by default (can be set up by setting the environment LD_Assume_kernel = 2.2.5), and the SAMPLE database will not be installed, the control center will not start normally. When using a count () function, if the record number of records in the table is> 2 147 483 647, the function may return the result of the error, at which point the return type Decimal (31, 0) can be used. The DistINCT keyword can be used in a count () function, such as: select count (Distinct ID) from Table, which will not count the repetition value of the ID column. After the ORDER BY clause, if multiple column names are written, you need to specify ascending or descending order. You can temporarily turn off the log options for the Table of the LOAD.
Use: ALTER TABLE ... ACTIVATE NOT Logged Initial DB2 Several Special Registers: Current Date, Current Time, Current TimeStamp, User (User ID). About Date: Current TimeStamp 2 Days (or Hours, Seconds, Months , YEARS, ETC.) Case statement: Case WHEN Condition One Then Action ELSE Action Two End; You can use it. The ORDER BY clause cannot be used in the creative statement of the view and the fetch n rows clause. But for the order by the following methods, it can be used instead of implementation, but it will affect efficiency. Create View V_Name1 (C1, C2, C3) AS SELECT * FROM (Select Column1, Column2, Column3 from T1 Order By Column1) AS T1; Reference Resources
Learning Note -DB2 UDB V8.1 Management Learning Notes (2) Learning Notes - DB2 UDB V8.1 Management Learning Notes (3) IBM DB2 Developer Garden Http://www-900.ibm.com/developerWorks/cn/dmdd /certify/index.shtml IBM DB2 Information Center http://publib.boulder.ibm.com/infocenter/db2help/index.jsp DBFORUMS Forum http://dbforums.com/ "DB2 UDB V8.1 for Linux, UNIX, Windows Database Management "George Baklarz, Bill Wong, Machinery Industry Press" DB2 Database Management and Application Tutorial "Zhuang Jicheng, Tsinghua University Press Publishing
