Use the DB2 dart tool to analyze the DB2 Data Storage Mode

What we will introduce to you today is the correct use of the DB2 dart tool to understand the DB2 data storage method, the following is a detailed description of the main content of the article on how to use the DB2 dart tool to understand the DB2 data storage method. I hope you will have a better understanding of it after browsing it.

In this article, we will use the DB2dart tool to understand how DB2 data is stored, especially how DB2 data is stored on every page.

Introduction

Data Tables in the DB2 database are stored in tablespaces. Each tablespace can have one or more containers. page pages) are the most basic storage units in containers, each page can store a certain number of records. But what is the storage of each record on the page? Is there any tool to see which records are stored on each page?

DB2dart is a powerful tool for database analysis in DB2. It can analyze databases, tablespaces, and tables to verify that the architecture of databases and their objects is correct, you can also use it to display the contents of the database control file so that data can be extracted from tables that may not be accessed in other cases.

In this article, we will use DB2dart to display and analyze the data in the database, so as to gain a deep understanding of the data storage method of DB2, which will also help improve DBA data recovery skills.

Overview of data storage methods in DB2

There are two types of tablespaces In the DB2 database, which can be used in a single database:

System Management space SMS), the File Manager of the operating system controls the storage space.

Database Management space DMS), the database manager controls the storage space.

You can also create an automatic storage tablespace that uses SMS or DMS as the basic tablespace type. The database manager selects the actual type of SMS or DMSSMS for temporary tablespace Based on the Data Types contained in the table, and DMS for other tablespaces ).

In this article, we will only discuss DMS-type tablespaces.

A tablespace is a storage structure that contains tables, indexes, large objects, and long data. They allow you to directly specify the location of database and table data to the container. The container can be a directory name, device name, or file name. A single tablespace can span multiple containers, as shown in 1.

Figure 1. tablespace, container, and table in the database

The DB2 database manager balances the data loads distributed across all containers, and all containers are used to store data. The number of pages that the database manager writes to a container before using another container is called the extended data block extent) size. The database manager does not always store table data from the first container. In Figure 2, The tablespace HUMANERS has four containers, and the extended database size is 2 pages, with each page size 4 K bytes. Both DEPARTMENT and EMPLOYEE tables have 7 pages and span all four containers.

Figure 2. containers and extended databases in the tablespace

So how are records stored on each page? Next we will use the DB2 dart tool for a more intuitive understanding.

Introduction to DB2dart

To do this, you must first sharpen the Tool. Let's take a look at the Tool DB2dartDB2 Database Analysis and Reporting Tool. As described above, DB2dart is more of a diagnostic tool that can be used to verify whether the database and related database objects are correct and have problems. It can also be used to display the content of the database control file, this allows you to extract data from tables that are not accessible under other circumstances when you recreate the database.

If you want to view the related syntax of DB2dart, you can issue the DB2dart command in the Command window DB2CLP without any parameters) to view all its options and functions. What we will use in this article is:

/DD: extract formatted table data. Command Format: DB2dart /DD/TSI... /OI... /PS... /NP.../V Y

In the preceding command:

/TSI is used to specify the tablespace IDtablespace ID ),

/OI is used to specify the object IDobject ID ),

/PS is used to specify the start data page,

/NP is used to specify the number of data pages to be extracted,

/V is used to set whether to display details, Y: Display, N: not display.

The tablespace ID and Object ID can be obtained from the CATALOG table SYSCAT. TABLES. The following describes the specific SQL statements. You can also use/RPTN to specify the file name used to save the result.

Note that DB2dart cannot run on a database with active connections. If an active connection exists, DB2dart will output a warning message and possibly output an incorrect result. You can run the "force applications all" command to disconnect ALL databases. It is precisely because of this offline feature that DB2dart can play a huge role in the recovery after the database crash.

The above content is an introduction to how to use the DB2 dart tool to understand the DB2 data storage method. I hope you will gain some benefits.