Deadlock concept Table 1 T,: update row 1 of table 1 T, update row 2 of table 2 update row 2 of table 2 √ 1 T3: deadlock T. deadly Row 2 Table 2 Row 1 Row 2 Because applications do not voluntarily release locks on data that they need,a deadlock detector process is required to break deadlocks and allow application processing to continue As its name suggests, the deadlock detector monitors the information about agents waiting on locks. The deadlock detector arbitrarily selects one of the applications in the deadlock and releases the locks currently held by that"volunteered"application By releasing the locks of that application, the data required by other waiting applications is made available for use. The waiting applications can then access the data required to complete transactions. Related concepts: page DB2 architecture and process overview"on page 11 sk storag ge overview Understanding how data is stored on disk helps you tune 1/O Disk-storage performance factors The hardware that makes up your system can influence the performance of your system. n example of the influence of hardware on performance, consider some of the implications associated with disk storage Four aspects of disk-storage management affect performance Division of storage Chapter 2 Architecture and processes 15
Because applications do not voluntarily release locks on data that they need, a deadlock detector process is required to break deadlocks and allow application processing to continue. As its name suggests, the deadlock detector monitors the information about agents waiting on locks. The deadlock detector arbitrarily selects one of the applications in the deadlock and releases the locks currently held by that “volunteered” application. By releasing the locks of that application, the data required by other waiting applications is made available for use. The waiting applications can then access the data required to complete transactions. Related concepts: v “Locks and performance” on page 63 v “DB2 architecture and process overview” on page 11 Disk storage overview Understanding how data is stored on disk helps you tune I/O. Disk-storage performance factors The hardware that makes up your system can influence the performance of your system. As an example of the influence of hardware on performance, consider some of the implications associated with disk storage. Four aspects of disk-storage management affect performance: v Division of storage x x Deadlock concept Table 1 Table 2 Row 1 Row 1 Row 2 Row 2 T : update row 1 of table 1 1 T : update row 2 of table 2 T : deadlock 2 3 Application A T : update row 2 of table 2 1 T : update row 1 of table 1 T : deadlock 2 3 Application B . . . . . . . . . . . . . . . . . . Figure 2. Deadlock detector Chapter 2. Architecture and processes 15
How you divide a limited amount of storage between indexes and data and among table spaces determines to a large degree how each will perform in different situations Wasted storage Wasted storage in itself may not affect the performance of the system that is using it, but wasted storage is a resource that could be used to improve Distribution of disk i/o How well you balance the demand for disk 1/0 across several disk storage devices, and controllers can affect how fast the database manager can retrieve information from disks Lack of available storage Reaching the limit of available storage can degrade overall performance Related concepts: DMS device considerations" on page 307 Database directories and files"on page 16 SMS table spaces"on page 19 DMS table spaces"on page 20 Table and index management for standard tables"on page 23 Table and index management for MDC tables"on page 28 Database directories and files When you create a database, information about the database including default information is stored in a directory hierarchy. The hierarchical directory structure is created for you at a location that is determined by the information you provide in the CREAtE DATABASE command. If you do not specify the location of the directory path or drive when you create the database, the default location is used It is recommended that you explicitly state where you would like the database created In the directory you specify in the CREATE DATABASE command, a subdirectory that uses the name of the instance is created. This subdirectory ensures that databases created in different instances under the same directory do not use the same path. Below the instance-name subdirectory, a subdirectory named NoDE0000 is created. This subdirectory differentiates partitions in a logically partitioned database environment. Below the node-name directory, a subdirectory named sQLo000l is created. This name of his subdirectory uses the database token and represents the database being created. SQL00001 contains objects associated with the first database created, 16 Administration Guide: Performance
How you divide a limited amount of storage between indexes and data and among table spaces determines to a large degree how each will perform in different situations. v Wasted storage Wasted storage in itself may not affect the performance of the system that is using it, but wasted storage is a resource that could be used to improve performance elsewhere. v Distribution of disk I/O How well you balance the demand for disk I/O across several disk storage devices, and controllers can affect how fast the database manager can retrieve information from disks. v Lack of available storage Reaching the limit of available storage can degrade overall performance. Related concepts: v “DMS device considerations” on page 307 v “Database directories and files” on page 16 v “SMS table spaces” on page 19 v “DMS table spaces” on page 20 v “Table and index management for standard tables” on page 23 v “Table and index management for MDC tables” on page 28 Database directories and files When you create a database, information about the database including default information is stored in a directory hierarchy. The hierarchical directory structure is created for you at a location that is determined by the information you provide in the CREATE DATABASE command. If you do not specify the location of the directory path or drive when you create the database, the default location is used. It is recommended that you explicitly state where you would like the database created. In the directory you specify in the CREATE DATABASE command, a subdirectory that uses the name of the instance is created. This subdirectory ensures that databases created in different instances under the same directory do not use the same path. Below the instance-name subdirectory, a subdirectory named NODE0000 is created. This subdirectory differentiates partitions in a logically partitioned database environment. Below the node-name directory, a subdirectory named SQL00001 is created. This name of this subdirectory uses the database token and represents the database being created. SQL00001 contains objects associated with the first database created, 16 Administration Guide: Performance
and subsequent databases are given higher numbers: SQL00002, and so on. These subdirectories differentiate databases created in this instance on the directory that you specified in the CREAtE DATABASE command The directory structure appears as follows your directory/<your instance>/NODE0000/SQL00001/ The database directory contains the following files that are created as part of the CREATE databaSe command The files SQLBP l and SQLBP2 contain buffer pool information. Each file has a duplicate copy to provide a backup. The files SQLSPCS I and SQLSPCS 2 contain table space information. Each file has a duplicate copy to provide a backup The SQLDBCON file contains database configuration information. Do not edit this file. To change configuration parameters, use either the Control Center or the command -line statements UPDatE DATABASE mANaGeR conFiguration and RESEt DATABASE MANAGER CONFIGURATION The DB2RHIST ASC history file and its backup DB2RHIST BAK contain history information about backups, restores, loading of tables, reorganization of tables, altering of a table space and other changes to a The DB2TSCHNG HIS file contains a history of tablespace changes at a log-file level. For each log file, DB2TSCHG HIS contains information that helps to identify which tablespace are affected by the log file. Tablespace recovery uses information from this file to determine which log files to process during tablespace recovery. You can examine the contents of both history files in a text editor. The log control files, SQLOGCTL LFH and SQLOGMIR. LFH, contain information about the active logs Recovery processing uses information from this file to determine how far back in the logs to begin recovery. The SQLOGDIR subdirectory contains the actual log files. Note: You should ensure the log subdirectory is mapped to different disks than those used for your data. a disk problem could then be restricted to your data or the logs but not both. This can provide a substantial performance benefit because the log files and database containers do not compete for movement of the same disk heads. To change the location of the log subdirectory, change the newlogpath database configuration parameter The SQLINSLK file helps to ensure that a database is used by only one instance of the database manager Chapter 2 Architecture and processes 17
and subsequent databases are given higher numbers: SQL00002, and so on. These subdirectories differentiate databases created in this instance on the directory that you specified in the CREATE DATABASE command. The directory structure appears as follows: <your_directory>/<your_instance>/NODE0000/SQL00001/ The database directory contains the following files that are created as part of the CREATE DATABASE command. v The files SQLBP.1 and SQLBP.2 contain buffer pool information. Each file has a duplicate copy to provide a backup. v The files SQLSPCS.1 and SQLSPCS.2 contain table space information. Each file has a duplicate copy to provide a backup. v The SQLDBCON file contains database configuration information. Do not edit this file. To change configuration parameters, use either the Control Center or the command-line statements UPDATE DATABASE MANAGER CONFIGURATION and RESET DATABASE MANAGER CONFIGURATION. v The DB2RHIST.ASC history file and its backup DB2RHIST.BAK contain history information about backups, restores, loading of tables, reorganization of tables, altering of a table space, and other changes to a database. The DB2TSCHNG.HIS file contains a history of tablespace changes at a log-file level. For each log file, DB2TSCHG.HIS contains information that helps to identify which tablespace are affected by the log file. Tablespace recovery uses information from this file to determine which log files to process during tablespace recovery. You can examine the contents of both history files in a text editor. v The log control files, SQLOGCTL.LFH and SQLOGMIR.LFH, contain information about the active logs. Recovery processing uses information from this file to determine how far back in the logs to begin recovery. The SQLOGDIR subdirectory contains the actual log files. Note: You should ensure the log subdirectory is mapped to different disks than those used for your data. A disk problem could then be restricted to your data or the logs but not both. This can provide a substantial performance benefit because the log files and database containers do not compete for movement of the same disk heads. To change the location of the log subdirectory, change the newlogpath database configuration parameter. v The SQLINSLK file helps to ensure that a database is used by only one instance of the database manager. Chapter 2. Architecture and processes 17
Additional information for sms database directories The SQLT' subdirectories contain the default System Managed Space(SMS) table spaces required for an operational database. Three default table spaces SQLTo000.0 subdirectory contains the catalog table space with the system catalog tables SQLTO001.0 subdirectory contains the default temporary table space. SQLTO002.0 subdirectory contains the default user data table space Each subdirectory or container has a file created in it called SQLTAG. NAM. This file marks the subdirectory as being in use so that subsequent table space creation does not attempt to use these subdirectories In addition, a file called SQL* Dat stores information about each table that the subdirectory or container contains The asterisk ()is replaced by a unique set of digits that identifies each table. For each SQL*. DAT file there might b one or more of the following files, depending on the table type, the reorganization status of the table, or whether indexes, LOB, or LONG fields exist for the table SQL*. BMP(contains block allocation information if it is an mdc table) SQL.LF (contains LONG VARCHAR or LONG VARGRAPHIC data) SQL.LB(contains BLOB, CLOB, or dbClOb data) SQL LBA (contains allocation and free space information about SQL*. LB files) SQLINX(contains index table data) SQL. DTR (contains temporary data for a reorganization of an SQL*. DAT SQL LFR (contains temporary data for a reorganization of an SQL*. LF file SQL. RLB (contains temporary data for a reorganization of an SQL*. LB file) SQL. RBA(contains temporary data for a reorganization of an SQL* LBA file) Related concept Comparison of SMS and dMs table spaces"in the Administration Guide Pla DMS device considerations"on page 307 SMS table spaces"on page 19 DMS table spaces"on page 20 Illustration of the DMS table-space address map"on page 22 18 Administration Guide: Performance
Additional information for SMS database directories The SQLT* subdirectories contain the default System Managed Space (SMS) table spaces required for an operational database. Three default table spaces are created: v SQLT0000.0 subdirectory contains the catalog table space with the system catalog tables. v SQLT0001.0 subdirectory contains the default temporary table space. v SQLT0002.0 subdirectory contains the default user data table space. Each subdirectory or container has a file created in it called SQLTAG.NAM. This file marks the subdirectory as being in use so that subsequent table space creation does not attempt to use these subdirectories. In addition, a file called SQL*.DAT stores information about each table that the subdirectory or container contains. The asterisk (*) is replaced by a unique set of digits that identifies each table. For each SQL*.DAT file there might be one or more of the following files, depending on the table type, the reorganization status of the table, or whether indexes, LOB, or LONG fields exist for the table: v SQL*.BMP (contains block allocation information if it is an MDC table) v SQL*.LF (contains LONG VARCHAR or LONG VARGRAPHIC data) v SQL*.LB (contains BLOB, CLOB, or DBCLOB data) v SQL*.LBA (contains allocation and free space information about SQL*.LB files) v SQL*.INX (contains index table data) v SQL*.DTR (contains temporary data for a reorganization of an SQL*.DAT file) v SQL*.LFR (contains temporary data for a reorganization of an SQL*.LF file) v SQL*.RLB (contains temporary data for a reorganization of an SQL*.LB file) v SQL*.RBA (contains temporary data for a reorganization of an SQL*.LBA file) Related concepts: v “Comparison of SMS and DMS table spaces” in the Administration Guide: Planning v “DMS device considerations” on page 307 v “SMS table spaces” on page 19 v “DMS table spaces” on page 20 v “Illustration of the DMS table-space address map” on page 22 18 Administration Guide: Performance
anding the Recovery History File"in the Data Recovery and Hig Availability Guide and Refer Related reference: CREATE DATABASE Command" in the command reference Table space overview The following sections describe table spaces and discuss the two types of table spaces available in DB2, comparing their advantages and disadvantages Table spaces Two types of table spaces are supported: System Managed Space(SMS) and Database Managed Space(DMS). Each has characteristics that make it appropriate for different environments SMS table spaces are an excellent choice for general purposes. They provide good performance with little administration cost. DMS table spaces are the choice for better performance with somewhat more administration cost. DMS table spaces in device containers provide the best performance because double uffering does not occur. Double buffering, which occurs when data is buffered first at the database manager level and then at the file system level, might be an additional cost for file containers or SMs table spaces Related concepts SMS table spaces"on page 19 DMS table spaces"on page 20 SMS table spaces System Managed Space(SMS)table spaces store data in operating system files. The data in the table spaces is striped by extent across all the containers in the system. An extent is a group of consecutive pages defined to the database. Each table in a table space is given its own file name which is used by all containers. The file extension denotes the type of the data stored in the file. To spread the space use evenly across all containers in the table space, the tarting extents for tables are placed in round-robin fashion across all containers. Such distribution of extents is particularly important if the database contains many small tables Note: SMS table spaces can take advantage of file-system prefetching and caching In a SMS table space, space for tables is allocated on demand. This expansion is done a single page at a time by default. However, in certain work loads Chapter 2 Architecture and processes 19
v “Understanding the Recovery History File” in the Data Recovery and High Availability Guide and Reference Related reference: v “CREATE DATABASE Command” in the Command Reference Table space overview The following sections describe table spaces and discuss the two types of table spaces available in DB2, comparing their advantages and disadvantages. Table spaces Two types of table spaces are supported: System Managed Space (SMS) and Database Managed Space (DMS). Each has characteristics that make it appropriate for different environments. SMS table spaces are an excellent choice for general purposes. They provide good performance with little administration cost. DMS table spaces are the choice for better performance with somewhat more administration cost. DMS table spaces in device containers provide the best performance because double buffering does not occur. Double buffering, which occurs when data is buffered first at the database manager level and then at the file system level, might be an additional cost for file containers or SMS table spaces. Related concepts: v “SMS table spaces” on page 19 v “DMS table spaces” on page 20 SMS table spaces System Managed Space (SMS) table spaces store data in operating system files. The data in the table spaces is striped by extent across all the containers in the system. An extent is a group of consecutive pages defined to the database. Each table in a table space is given its own file name which is used by all containers. The file extension denotes the type of the data stored in the file. To spread the space use evenly across all containers in the table space, the starting extents for tables are placed in round-robin fashion across all containers. Such distribution of extents is particularly important if the database contains many small tables. Note: SMS table spaces can take advantage of file-system prefetching and caching In a SMS table space, space for tables is allocated on demand. This expansion is done a single page at a time by default. However, in certain work loads Chapter 2. Architecture and processes 19