IBM's DB2 V9.5 introduced and perfected the workload management (workload MANAGEMENT) feature, allowing us to gain insight into the system's performance and better control of resources and capabilities. In DB2 V9.5, the newly introduced Workload management feature (WLM) can help you identify, manage, and monitor the workload of your data server. This article will focus on the new features of DB2 V9.5 on workload Management (WLM) and related concepts, and help you understand and improve on the actual examples.
Building a database environment
First we install DB2 ESE V9.5 in the WINDOWS XP environment, after the installation completes, open a DB2CLP window, issue the Create DB command, create the sample database Db2test1, the command needs to specify the database code page or the database code set, because in DB2 V9. All databases created in 5 have database code pages 1208 by default, and database code sets are UTF-8, which is different from previous versions. If you want to set the database code page to 1386 and the database code set to GBK, you need to set the database code page to 1386 in the CREATE DB command or set the database code set to GBK. Specifically, as shown in Listing 1:
Listing 1. Create the sample database Db2test1
C:\> DB2 CREATE DATABASE DB2TEST1 USING CODESET GBK TERRITORY CN COLLATE
USING SYSTEM PAGESIZE 4096
DB20000I CREATE DATABASE 命令成功完成。
Command execution succeeded, so we created a sample database Db2test1. Continue to issue the get DB CFG command in this DB2CLP window to view the configuration parameters of the sample database Db2test1, we can see that the database region is CN, the database code page is set to 1386, and the database code set is set to GBK. In addition, several additional parameters are added to DB2 V9.5, namely, digital compatibility, Varchar2 compatibility, decimal floating-point rounding, enabling XML character operations, and WLM collection interval (minutes), which can be viewed in the DB2 Information Center. See the details of the sample database Db2test1 configuration parameters as shown in Listing 2:
Listing 2. To view the sample database Db2test1 configuration parameters
c:\> DB2 get DB CFG for Db2test1
Database db2test1 Database Configuration
Database configuration Release level = 0X0C00
Database Release level = 0X0C00
Database domain = CN
Database code page = 1386
Database Code set = GBK
Database country/Region code = 86
Database Collation order = UNIQUE
Standby finishing Order (alt_collate) =
Number compatibility = Off
VARCHAR2 compatibility = Off
Database Page size = 4096
Dynamic SQL Query Management (DYN_QUERY_MGMT) = DISABLE
Discovery support for this database (discover_db) = enable
Restrict access = NO
Default query optimization Class (dft_queryopt) = 5
Degree of parallelism (dft_degree) = 1
Continue (Dft_sqlmathwarn) = NO on arithmetic exceptions
Default refresh Validity period (dft_refresh_age) = 0
The table type of the default maintained option (dft_mttb_types) = SYSTEM
Number of high-frequency values reserved (num_freqvalues) = 10
Number of reserved points (num_quantiles) = 20
Decimal floating-point rounding Method (decflt_rounding) = Round_half_even
. . . . . . . . . .
Self-adjusting memory (SELF_TUNING_MEM) = On
Database Shared memory Size (4 KB) (database_memory) = AUTOMATIC
Database memory Threshold (DB_MEM_THRESH) = 10
Maximum lock list storage (4 KB) (locklist) = AUTOMATIC
List of lock percentages per application (MAXLOCKS) = AUTOMATIC
Package Cache Size (4 KB) (Pckcachesz) = AUTOMATIC
Sorted heap field value for shared sort (4 KB) (SHEAPTHRES_SHR) = AUTOMATIC
Sorted list Heap (4 KB) (SORTHEAP) = AUTOMATIC
. . . . . . . . . .
Automatic maintenance (auto_maint) = On
Automatic database backup (auto_db_backup) = Off
Automatic table maintenance (auto_tbl_maint) = On
Automatic Runstats (auto_runstats) = On
Automatic statement statistics (auto_stmt_stats) = Off
Automatic statistical information profiling (AUTO_STATS_PROF) = Off
Automatic profile update (AUTO_PROF_UPD) = Off
Automatic reorganization (auto_reorg) = Off
Enable XML character operations (Enable_xmlchar) = YES
WLM collection interval (minutes) (wlm_collect_int) = 0