DB2 optimizes data processing from six hours to 20 minutes (2)

Several Common commands are mentioned in the previous article, but they are rarely found on many websites.

 

This article focuses on the DB2 tablespace, because in the system I will frequently operate 16 tables and my business data table tablespace uses the same. As a result, the access speed is slow in the Process of processing business data. Therefore, the following principles should be followed for creating tablespaces and tables:

1. A table with frequent operations uses a tablespace

2. A table with a large data volume uses a tablespace

3. Use a tablespace in the business table

4. A tablespace is used for the temporary table.

You can define four different types of tablespaces in DB2, as shown below: A single tablespace can contain more than one DB2 table. This space consists of multiple pages. Each page can contain several rows, which may be data rows from any table defined in the tablespace. Segment: the segment tablespace can contain more than one DB2 table. This tablespace consists of multiple groups of pages, each group of pages is called a segment. Each segment contains several rows from a table defined in the tablespace. Partition: The partition table space can only contain one table. This space is divided into multiple partitions based on the key value range of the partition index. Each partition is treated as an independent entity, allowing concurrent processing of SQL and DB2 utility sets. LOB: the LOB tablespace only stores LOB (large object) data. LOB includes three data types: BLOB (Binary Large Object), CLOB (dense large object), and DBCLOB (double-byte large object ).

Daily tablespace management commands
In this example, most of the created tablespace pagesize is 32 KB, so
1. Create a bufferpool with a pagesize of 32 KB.
Create bufferpool bp32k size 2000 pagesize 32 k
2. View bufferpool attributes
Select * from syscat. bufferpools
3. Change the buffer pool size
Alter bufferpool bp32k size 2000
 
 
4. Create a 32 KB data block on the data page. The pre-value of KB is default, and the large DMS tablespace cached by the file system is not used.
 
 
Create large tablespace tbs_data pagesize 32 k managed by database using (file '/database/tbs_data/cont0' 100 M, file'/database/tbs_data/cont1 '100 M) extentsize 32 prefetchsize automatic bufferpool bp32k no file system caching
 
 
5. Create a temporary SMS System tablespace with a data page of 32 KB
Create temporary tablespace tbs_temp pagesize 32 k managed by system using ('/database/tbs_temp') bufferpool bp32k
 
 
 
6. Create a temporary SMS user tablespace with a data page of 32 KB
 
Create user temporary tablespace tbs_user_temp pagesize 32 k managed by system using ('/database/tbs_usertemp') bufferpool bp32k
7. Create a 32 KB Automatic Storage Management tablespace on the data page
Create tablespace tbs_index pagesize 32 k bufferpool bp32k
 
8. Create an Automatic Storage Management tablespace with an initial size of 100 MB, an incremental value of 100 MB, and a maximum size of GB
Create tablespace tbs_data2 initialsize 100 M increasesize 100 M maxsize 100G
9. View tablespace Information
List tablespaces
List tablespaces show detail
Db2pd-d testdb-tablespaces
Get snapshot for tablespaces on testdb
You can also view the sysibmadm. snaptbsp and sysibmadm. snapcontainer views.
10. View container information related to table null
List tablespace containers for 7 # tablespace id
List tablespace containers for 7 show detail
11. If there is still unallocated space in the storage of the DMS tablespace, you can use the extend, reduce, or resize option of alter tablespace to expand the size of the existing tablespace container. Extend is used to expand the container size, reduce is used to reduce the existing container size, and resize is used to reset the container size. For reduce and resize, make sure that the changed tablespace has sufficient space. Otherwise, DB2 rejects the operation.
Alter tablespace tbs_data extend (file '/database/tbs_data/cont0' 10 M)
12. If the tablespace container has no space left, you can use the add option to add the container. If necessary, you can also use the drop option to delete the container. The add and drop operations rebalance data between containers ). If the data volume is large, the rebalance may take a long time, which may have a great impact on the system.
 
Alter tablespace tbs_data add (file '/database/tbs_data/cont2' 100 m)
13. If you use the begin new strip set option, the new container is used when the existing container space is used up. This option prevents data from being rebalance between containers, but may cause data offset.
Alter tablespace tbs_data begin new stripe set (file '/database/tbs_data/cont3' 100 m)
 
14. view the tablespace path for Automatic Storage Management
Db2pd-d testdb-storagepaths
15. For automatic storage and management tablespaces, container changes cannot be made at the tablespace level, but only at the data level, because the Automatic Storage path is specified at the time of database creation. You can use the add storage on option to add a new storage path for the database. Before v9.7, the automatic storage path can only be added and cannot be deleted. The newly added storage path does not use the backup tablespace immediately. The new path is used only when the file system space in the existing storage path is full. The new path is added only to solve the capacity problem. After v9.7, the new storage path can be used immediately as long as the tablespace managed by automatic storage is rebalance.
 
Alter database testdb add storage on '/database/czm'
Alter tablespace 7 rebalance
 
 
16. Convert the DMS tablespace to the automatic storage and management tablespace.

Alter tablespace tbs_data managed by automatic storage
Alter tablespace tbs_data rebalance

Author cuiran