Shulou(Shulou.com)06/01 Report--

Problem description:

The most recent tablespace expansion, the environment is DB2 10.1FP4jisuse 11.3, the file system uses Symantec StorageFoundation, there is a tablespace with a total of 32 containers, each container size is 20g, intend to expand this tablespace, so I used the command of resize (all 24g), db2 "alter tablespace tbs resize (all 24g)". After the execution, I found that tbs started rebalance. This is a bit strange. I added space to the existing container. The HWM of the tablespace is not affected, so why does it trigger rebalance?

Problem analysis:

Look at the snapshot of the tablespace and find that the mapping of this tablespace is a little strange (this is after the completion of rebalance). The container size is all the same. why is the mapping like this?

Range Stripe Stripe Max Max Start End Adj. Containers

Number Set Offset Extent Page Stripe Stripe

[0] [0] 0 163835 5242751 0 40958 0 4

[1] [0] 0 237563 7602047 40959 49150 09

[2] [0] 0 401398 12844767 49151 81917 0 5 (4, 5, 6, 7, 8)

[3] [0] 0 499702 15990495 81918 90109 0 12 (4, 5, 7, 8, 9, 10, 11, 12, 13, 14, 15)

[4] [0] 0 600054 19201759 90110 104445 0 7 (9, 10, 11, 12, 13, 14, 15)

[5] [0] 0 1212383 38796287 104446 131068 023 (9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 21, 22, 24, 25, 26, 27, 29, 30, 30)

[6] [0] 0 1572831 50330623 131069 153596 0 16 (16, 17, 18, 19, 21, 22, 24, 25, 26, 27, 28, 29, 30, 31)

Checked the information center, there are some instructions about tablespace map, but not exactly what will cause rebalance, I firmly believe that this problem is related to the distribution of tablespaces, so I did some tests on tablespace expansion, and finally recreated the phenomenon.

Experimental test:

= = Test 1: add database files, the new data files are larger than the original data files =

Initial tablespace distribution

Db2 get snapshot for tablespaces on sampledb > > ts1.out

Table space map:

Range Stripe Stripe Max Max Start End Adj. Containers

Number Set Offset Extent Page Stripe Stripe

[0] [0] 0 77 2495 0 38 0 2 (0prime1)

Execution

Db2 "alter tablespace USERSP4 add (file'/ db2data/db2inst1/sampledb/data/usersp4_c03' 20m, file'/ db2data/db2inst1/sampledb/data/usersp4_c04' 20m)"

View tablespace distribution

Db2 get snapshot for tablespaces on sampledb > > ts2.out

Table space map:

Range Stripe Stripe Max Max Start End Adj. Containers

Number Set Offset Extent Page Stripe Stripe

[0] [0] 0 155 4991 0 38 0 4

[1] [0] 0 235 7551 39 78 0 2 (2p3)

Execution

Db2 "alter tablespace USERSP4 resize (all 20m)"

View tablespace distribution

Db2 get snapshot for tablespaces on sampledb > > ts3.out

Table space map:

Range Stripe Stripe Max Max Start End Adj. Containers

Number Set Offset Extent Page Stripe Stripe

[0] [0] 0 315 10111 0 78 0 4

The automatic table space is distributed neatly.

= = Test 2: add database files, the new data files are smaller than the original data files =

Initial tablespace distribution

Db2 get snapshot for tablespaces on sampledb > > ts1.out

Table space map:

Range Stripe Stripe Max Max Start End Adj. Containers

Number Set Offset Extent Page Stripe Stripe

[0] [0] 0 77 2495 0 38 0 2 (0prime1)

Execution

Db2 "alter tablespace USERSP4 add (file'/ db2data/db2inst1/sampledb/data/usersp4_c03' 5m, file'/ db2data/db2inst1/sampledb/data/usersp4_c04' 5m)"

View tablespace distribution

Db2 get snapshot for tablespaces on sampledb > > ts4.out

Table space map:

Range Stripe Stripe Max Max Start End Adj. Containers

Number Set Offset Extent Page Stripe Stripe

[0] [0] 0 39 1279 0 19 0 2 (0prime1)

[1] [0] 0 115 3711 20 38 0 4

Execution

Db2 "alter tablespace USERSP4 resize (all 10m)"

View tablespace distribution

Db2 get snapshot for tablespaces on sampledb > > ts5.out

Range Stripe Stripe Max Max Start End Adj. Containers

Number Set Offset Extent Page Stripe Stripe

[0] [0] 0 39 1279 0 19 0 2 (0prime1)

[1] [0] 0 115 3711 20 38 0 4

[2] [0] 0 155 4991 39 58 0 2 (2prime3)

After the resize all command is executed, the tablespace distribution is not automatically normalized!

Db2 "alter tablespace USERSP4 resize (all 20m)"

Db2 get snapshot for tablespaces on sampledb > > ts6.out

Table space map:

Range Stripe Stripe Max Max Start End Adj. Containers

Number Set Offset Extent Page Stripe Stripe

[0] [0] 0 39 1279 0 19 0 2 (0prime1)

[1] [0] 0 275 8831 20 78 0 4

[2] [0] 0 315 10111 79 98 0 2 (2)

Once again execute resize (all 20m) to expand the tablespace, the tablespace distribution is still not automatically normalized!

Db2 "alter tablespace USERSP4 resize (all 30m)"

Db2 get snapshot for tablespaces on sampledb > > ts7.out

Table space map:

Range Stripe Stripe Max Max Start End Adj. Containers

Number Set Offset Extent Page Stripe Stripe

[0] [0] 0 39 1279 0 19 0 2 (0prime1)

[1] [0] 0 435 13951 20 118 0 4

[2] [0] 0 475 15231 119 138 0 2 (2)

Once again execute resize (all 30m) to expand the tablespace, the tablespace distribution is still not automatically normalized!

If the table space utilization rate is very high after the execution of resize (all 20m), for example, after using the third Stripe, that is, the space after extent 276, and then executing resize (all 30m), the extent starting on 276 will trigger rebalance.

Conclusion: through the above tests, we can see that when DB2 adds new data files, if the new data file is larger than the original data file, use the resize command to automatically regulate the tablespace distribution; if the new data file is smaller than the original data file, using the resize command can not automatically regulate the tablespace distribution, resulting in irregular data distribution, which may cause tablespace rebalance during the expansion period. It is recommended that DB2 add data files each time, the same size as the original data file, or at least larger than the original data file.

There is also a question: if there is such a phenomenon of data file misalignment, how can we make the data file regular?

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.