High water mark for DB2

table Space High water level :
The minimum size of the tablespace to create a DMS is 6 extent. For DMS table spaces, you can remove a container or shrink the size of a container from a table space. Use the ALTER TABLESPACE statement to complete this operation. Deleting or shrinking a container is only allowed if the number of extents that the operation deletes is less than or equal to the number of extents available on top of the high water mark in the tablespace, which means that only extent that have not been used can be deleted.
  The high water mark is the number of pages allocated in the table space for the highest page. For example: The tablespace has 1000 pages and extentsize is 10, then there are 100 extent. If the 42nd extent is the highest allocated extent in a tablespace, it means that the high water mark is a 42*10=420 page.

Give an example of the effect of high water levels:
(1) First create a test database-managed tablespace (DMS) as follows:

[Db2inst1@bogon tmp]$ DB2 "Create Tablespace usrtest managed by database using (FILE '/OPT/RUSRSP1 ' 64M)"
db20000i
  
   the SQL Command completed successfully.
  

Then use the list tablespace Show Detail command to view the usage of the tablespace:

[Db2inst1@bogon tmp]$ DB2 list tablespaces Show Detail

           tablespaces for current Database

 tablespace ID                        = 4
  
   name                                 = usrtest
 Type                                 = Database managed space
 Contents                             = All permanent data. Large table space.
 state                                = 0x0000
   detailed explanation:
     Normal
 totals pages   (total pages)            = 16384
 useable Pages  (pages available)          = 16352
 used pages    (pages used) = $ free Pages     (unused pages)          = 16256
 High water mark (pages)  height mark (page)     =
 page size (bytes)              = 4096
 Extent size (    pages) (Extended chunk Size (page)) =
 Prefetch size (pages) = + number of
 containers                 = 1

  

From the above output we can see that the table space has just been created 32 pages (16384-16352) that is, an extension block is not available, which corresponds to the container to retain the portion of the cost extension block, 96 pages or 3 expansion blocks have been used, which corresponds to the table space reserved cost portion.

Next, use the Db2dart command plus the DHWM option to check the table space usage. Note that the Db2dart command must be executed if the instance is stopped normally, or it may cause the database to crash.

#命令说明: Db2dart After the database name, the analysis of things under the database,/DHWM Export high water mark information, need an input:/tsi tablespace ID.
[Db2inst1@bogon tmp]$ db2dart Mytest/dhwm/tsi 4

Execution result of the command:

            Database name:mytest report
            name:mytest. RPT Old report
            Back-up:mytest. BAK
            Database subdirectory:/home/db2inst1/db2inst1/node0000/sql00004/member0000
            Operational mode:database Inspection only (INSPECT)

MYTEST. Rpt is located in the directory where the command is executed, in this case,/tmp, to view the file:

______________________________________________________________________________
---------------------------------

---------------------------------------------Action OPTION:DHWM tablespace-id:4 Creating the agent environment ...


High water mark Processing-phase start. Notes:all high water mark values and/or object sizes listed below is given in extents and not pages (unless ex Plicitly stated). * * All high water levels and object sizes are in extent units instead of page** the object ID and an object type is shown for each extent liste

        D. Each extent displays an object ID and object type. Extents marked with a asterisk (*) hold the first page of a object and these extents can only is moved by dropping and R

        The ecreating that object.* model represents the first data page of an object, and these extent can only be moved by deleting or rebuilding the object.


After following a, step and before continuing on to the next one,disconnect and reconnect to the database. High water mark:96 pages, 3 extents (extents #0-2) [0000] 65534 0x0e [0001] 65534 0x0e [0002] 65535 0x00 Du MP High Water Mark ProCessing-phase start. Number of free extents below high water mark:0 number of used extents below high water mark:3 ...

As you can see from the above output, the database management table space only has 3 tablespace reserved overhead extension blocks When you have just created it:

High water mark:  pages, 3 extents (extents #0-2)


 [0000] 65534 0x0e   [0001] 65534 0x0e   

(2). Now, let's create a table in this tablespace:

[Db2inst1@bogon desktop]$ DB2 "CREATE TABLE T1 (id int not null,name char (+)) in Usrtest" db20000i the SQL command compl
Eted successfully. #使用list tablespaces See the usage of tablespace again [Db2inst1@bogon desktop]$ DB2 list tablespaces Show Detail tablespaces for Cur                                 Rent Database tablespace ID = 4 Name = usrtest Type = Database Managed Space Contents = ALL permanent data.
 Large table space.  State = 0x0000 Detailed explanation:normal Total pages =                           16384 useable pages = 16352 used pages (pages used) = Free pages = 16192 High Water mark (pages) (water mark) = Page size (bytes) = 4096 Extent size (pages
 ) = Prefetch size (pages) = number of containers = 1

Compared to the previous output, the expanded block used in the tablespace has been increased to 5 (160 pages). Use the Db2dart plus SHWM option to analyze the usage of the tablespace again:

High water mark:  pages, 5 extents (extents #0-4)


 [0000] 65534 0x0e   [0001] 65534 0x0e   [0002] 65535 0x XX   [0003]     4 0x40*  [0004]     4 0x00* 


Dump High water Mark Processing-phase start.

Number of free extents below high water mark:0 number of
used extents below high water mark:5

Compared to the first output, more than the fourth extent[0003] 4 0x40*
And a fifth extent: [0004] 4 0x00*
Now let's explain the meaning of [0003] 4 0x40*
[0003] representing extent 3,[0003] immediately after the 4 for the object of the 0x4 on behalf of the extension block is stored in the object's extent usage, that is, emp;0x4 after the 0 represents the stored data, 0 after the * represents this is the object's first extension block.
Now let's explain the meaning of [0004] 4 0x00*
[0004] representing extent 4,[0004] immediately after the 4 represents object 4,
4 after the 0x0 represents this is the object extension block ; 0x0 after 0 means that the data is stored, 0 after the * represents this is the first extension block of the object.
(3). Now we create an index on the table:

[Db2inst1@bogon desktop]$ DB2 "CREATE index i1 on T1 (id)"
db20000i the  SQL command completed successfully.
[Db2inst1@bogon desktop]$ DB2 list tablespaces Show Detail Total

 pages                          = 16384
 useable pages                        = 16352
 used pages    (pages used)              = 224 free
 pages                           = 16128
 Hi water mark (pages) (high water mark)    = 224
 Page size (bytes)                    = 4096
  extent size (pages)                  =
 Prefetch size (pages)                = number of
 containers                 = 1

Compared to the previous output, the expanded block used in the tablespace has been increased to 7 (224 pages). Db2dart again analyzes the usage of the tablespace:

High water mark:  224 pages, 7 extents (extents #0-6)


 [0000] 65534 0x0e   [0001] 65534 0x0e   [0002] 65535 0x XX   [0003]     4 0x40*  [0004]     4 0x00*  [0005]     4 0x41*  [0006]     4 0x01* 


Dump High Water Mark Processing-phase start.

Number of free extents below high water mark:0 number of
used extents below high water mark:7

The following two expansion blocks are added to the table space compared to the previous output:
[0005] 4 0x41* [0006] 4 0x01*

Now let's explain the meaning of [0005] 4 0x41*:
[0005] representing extent 5,[0005] immediately after the 4 represents object 4,
4 the 0x4 on behalf of the extension block is the corresponding bitmap of the object extent;
The 1 behind the 0x4 represents the first extension block of the object (the extension block counterpart) that stores the index, and 1 after the * represents it.

Now let's explain the meaning of [0006] 4 0x01* *:
[0006] representing extent 6,[0006] immediately after the 4 represents object 4,
4 after the 0x0 represents this is the object extension block ;
The 1 behind 0x0 means that the index is stored, and 1 after the * represents the first extension block for that object.

(4). Now let's insert some data into the table.

[Db2inst1@bogon desktop]$ DB2 "INSERT into T1 with C1 (col1) as (values (1) union ALL select C1.col1 +1 from C1 where C1.col 1<50000) Select C1.col1, ' testing ' from C1
db20000i the  SQL command completed successfully.
# You have to explain this SQL, I just started to see where I thought I was going to find C1.
# The result of this statement: the inserted ID from 1 to 49999,name is ' testing '
[Db2inst1@bogon desktop]$ DB2 list tablespaces Show Detail Total
 pages                          = 16384
 useable pages                        = 16352
 used pages (pages used)                = 960 free
 pages                           = 15392
 higher water mark (pages) (high water mark)    = 960
 Page size (bytes)                    = 4096
 Extent size (pages)                  =
 Prefetch size (pages)                =
 Number of cont Ainers                 = 1

Use Db2dart to look at:

High water mark:  960 pages, extents (extents #0 -29)


 [0000] 65534 0x0e   [0001] 65534 0x0e   [0002] 65535 0x00   [0003]     4 0x40* 
 [0004]     4 0x00*  [0005]     4 0x41*  [0006]     4 0x01*  [0007]     4 0x00  
 [0008]     4 0x00   [0009]     4 0x01   [0010]     4 0x00   [0011]     4 0x00  
 [0012]     4 0x00   [0013]     4 0x01   [0014]     4 0x00   [0015]     4 0x00  
 [0016]     4 0x01   [0017]     4 0x00   [0018]     4 0x00   [0019]     4 0x00  
 [0020]     4 0x01   [0021]     4 0x00   [0022]     4 0x00   [0023]     4 0x00  
 [0024]     4 0x01   [0025]     4 0x00   [0026]     4 0x00   [0027]     4 0x01  
 [0028]     4 0x00   [0029]     4 0x00  


Dump High water mark processing- Phase start.

Number of free extents below high water mark:0 number of
used extents below high water mark:30

Compared to the last output, the table space adds the following block of data:

[0007]     4 0x00  
 [0008]     4 0x00   [0009]     4 0x01   [0010]     4 0x00   [0011]     4 0x00  
 [0012]     4 0x00   [0013]     4 0x01   [0014]     4 0x00   [0015]     4 0x00  
 [0016]     4 0x01   [0017]     4 0x00   [0018]     4 0x00   [0019]     4 0x00  
 [0020]     4 0x01   [0021]     4 0x00   [0022]     4 0x00   [0023]     4 0x00  
 [0024]     4 0x01   [0025]     4 0x00   [0026]     4 0x00   [0027]     4 0x01  
 [0028]     4 0x00   [0029]     4 0x00  

Where 7,8,10,11,12,14,15,17,18,19,10,21,22,23,24,25,26,28,29 stores the data
9,13,16, 20, 24,27 stores the index.
Of course, these blocks are no longer the first extension blocks.

From the above example, you should have mastered the high water level and DMS table spatial mapping information. So how do we manage the high water level in practice?
As we have said before, the high water mark (HWM) of the DB2 database management (DMS) tablespace is the maximum number of data pages that the table space has ever used. If you use the DB2 list tablespaces Show Detail command to see that the number of pages in a DMS table space is lower than the high water mark, you can reduce the high water mark by the following method:
-Reorganization of a table space table
-Export data from a table, then delete it, recreate the table, and import the data

For the above method, first find the table that holds the high water mark, which can be obtained by Db2dart command (which can be used after stopping the instance) with the DHWM option, providing the tablespace identifier, and then obtaining it from the report file generated by the command.
And to understand what you can do to reduce HWM recommendations by doing this table, you can use the Db2dart plus LHWM option to get from the resulting report file. When using the LHWM option, the user is asked to give the identity of the tablespace and the number of pages that it wants to HWM (although it is not guaranteed that the HWM will be able to tell the user's expected value), if the value is 0, the HWM is lowered by DB2 to the lowest value that can be reached.

Question: How much space for the data page of this data is recycled when the data is deleted.

Right now

[Db2inst1@bogon desktop]$ DB2 "delete from T1 where id>1000"
db20000i the  SQL command completed successfully.< C2/>[db2inst1@bogon desktop]$ DB2 List tablespaces Show Detail Total
pages                          = 16384
 useable pages                        = 16352
 used pages  (pages used)             = 960 free
 pages                           = 15392 high
 water mark (pages)              = 960
 page Size (bytes)                    = 4096
 Extent size (pages)                  =
 Prefetch size (pages)                =
 + number of Containers                 = 1

The Used page does not change after performing the delete, analyzing the reason.

(1) Number of page T1 used to run REORGCHK analysis table

[Db2inst1@bogon tmp]$ DB2 REORGCHK on TABLE T1

Doing RUNSTATS ....


Table Statistics:

f1:100 * Overflow/card < 5
f2:100 * (Effective Space utilization of Data Pages) > 70
  f3:100 * (Required pages/total Pages) >

schema.name                     CARD     OV     NP     FP actblk    tsize  F1  F2  F3 REORG
----------------------------------------------------------------------------------------
Table:db2inst1. T1      0    563      -    45000   0   2   2-** 
-------------------------- --------------------------------------------------------------

[Db2inst1@bogon tmp]$ DB2 "SELECT TABLEID, Npages, fpages from SYSCAT. TABLES WHERE tabname = ' T1 ' "

TABLEID npages               fpages              
-----------------------------------------------
      4                  563

  1 record (s) selected.

You can see that the table T1 now has only 1000 rows of data, occupies 563 pages, and only 12 pages have data.

Here is a question, table space used page is 960, table T1 used 563, table space was created using 96 page,t1 EMP with 32 page, there are more than 100 page where to go. This problem is analyzed later.

Use Db2dart to view pages:

[Db2inst1@bogon tmp]$ DB2 disconnect all
db20000i the  SQL Disconnect command completed successfully.
[Db2inst1@bogon tmp]$ DB2 deactivate DB mytest
sql1495w  Deactivate database is successful, however, there is still A 
connection to the database.
[Db2inst1@bogon tmp]$ db2dart mytest  /dd  /tsi 4  /oi 4/ps 0/np 960/v y  /rptn T1.dart

T1.dart file:

         Page 0 of Object 4 from table Space 4.
                             BPS page header:page Data Offset = page Data Length = 4048  Page LSN = 000000000006fd0d Object page number = 0 Pool Page number = Object ID = 4 Object Type = Data Object data Pa GE Header:slot Count = the total free Space = 4 Total R
                    Eserve Space = 0 Youngest Reserve space = 0 youngest TID = 0000 0000 cf0a 
Free Space Offset = 139 Maximum Record Size = Dpg_flags = 0x0
            ......  Slot 67:offset location = x8c record Length = (x2b) record Type

      = Table Data record (fixedvar) record Flags = 0         Fixed Part Length value = $1:fixed offset:0 Type is L Ong Integer Value = 2:fixed Column offset:4 Type I         
                s Fixed Length Character String 74657374 696E6720 20202020 20202020 Testing
20202020 20202020 20202020 2020 Slots summary:total=68, in-use=68, Deleted=0. ......

Then look at the end:

         Page one of the object 4 from table Space 4.

         BPS page Header:

                     page Data Offset = page
                     data Length = 4048
                             page LSN = 000000000006fe30
                   Object Pag