Shulou(Shulou.com)05/31 Report--

It is believed that many inexperienced people have no idea about how to analyze the limitation of pagesize and its corresponding addressing ability in DB2 database. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

In DB2 databases, the size of tables and tablespaces is mainly limited by pagesize and its corresponding addressing capabilities. The following will give you a detailed analysis of the size limitations of tables and tablespaces in the DB2 database.

In DB2 v8, the page address is 3 bytes, which is available to the 24th power of 2, that is, 16777216 pages can be addressable. Based on this limitation, the following table space and table size restrictions are obtained:

# of pages Page size Limit of table / tablespace

16777216 4 K 64 GB

16777216 8 K 128 GB

16777216 16 K 256 GB

16777216 32 K 512 GB

In DB2 v9, the page address is expanded to 4 bytes, which means a four-fold increase in addressing capacity. The specific restrictions are as follows:

# of pages Page size Limit of table / tablespace

536870912 4 K 2 TB

536870912 8 K 4 TB

536870912 16 K 8 TB

536870912 32 K 16 TB

Note: in DB2 v8, large type tablespaces are only used for LOB and LONG data types, but there is no similar restriction in DB2 v9. The default tablespace type is large. If you upgrade from DB2 v8 to v9, you need to manually convert the tablespace from regular to large.

ALTER TABLESPACE tablespace_name CONVERT TO LARGE

Typical error reporting in DB2 v8

Expand the capacity of more containers

Db2 "ALTER TABLESPACE tablespace-name EXTEND (ALL 1000000)"

DB21034E The command was processed as an SQL statement because it was not avalid Command Line Processor command. During SQL processing it returned:

SQL1139N The total size of the table space is too big. SQLSTATE=54047

Expand the capacity of one of the containers

Db2 "ALTER TABLESPACE tablespace-name EXTEND (FILE'/ dir/filename' 3000000)"

DB21034E The command was processed as an SQL statement because it was not avalid Command Line Processor command. During SQL processing it returned:

SQL1139N The total size of the table space is too big. SQLSTATE=54047

Add container

Db2 "ALTER TABLESPACE tablespace-name ADD (FILE'/ dir/filename' 500000)"

DB21034E The command was processed as an SQL statement because it was not avalid Command Line Processor command. During SQL processing it returned:

SQL1139N The total size of the table space is too big. SQLSTATE=54047

Through the examination, you can see

LIST TABLESPACES SHOW DETAIL

...

Tablespace ID = 8

Name = tablespace-name

Type = Database managed space

Contents = Any data

State = 0x0000

Detailed explanation:

Normal

Total pages = 16388000

Useable pages = 16387840

Used pages = 16387840

Free pages = 0

High water mark (pages) = 16387840

Page size (bytes) = 4096

Extent size (pages) = 32

Prefetch size (pages) = 128

Number of containers = 4

Minimum recovery time = 2009-06-26-04.47.15.000000

...

You can clearly see that the number of pages is close to the maximum limit

Resolution of pagesize size

Connect to the database

Db2 create bufferpool buffer pool name pagesize 16384 (bytes)

Db2 alter bufferpool buffer pool name size 5000

Db2stop force

Db2start

Db2 create large tablespace tablespace name pagesize 16k managed by automatic storage bufferpool buffer pool name

It is also possible that the query statement pagesize is too small, and you can create temporary tablespaces for the database in the same way as creating tablespaces.

After reading the above, have you mastered how to analyze the pagesize in the DB2 database and its corresponding addressing capacity limitations? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.