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

Today, I will talk to you about how to analyze the case of DB2 database row length exceeding the limit SQL0670N error. Many people may not know much about it. In order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

When a developer modifies a table, the DB2 database reports a SQL0670N error as follows:

How did this mistake come about? What should we do about it?

First of all, let's take a look at the DB2 database's own explanation of this error.

This tells us that in a table space with a 32K page size, the row length cannot exceed 32677 bytes.

To solve this problem, we will naturally think of increasing the size of the table space of the row length limit or setting a larger pagesize, of course, we can also consider reducing the length of one or more columns to reduce the row length.

First check the specific information of the tablespace:

$db2 list tablespaces show detail

Tablespace identity = 9

Name = TPMS

Type = system management space

Content = any data

Status = 0x0000

Detailed explanation:

Normal

Total pages = 2084

Number of pages available = 2084

Number of pages used = 2084

Number of unused pages = not applicable

High water mark (page) = not applicable

Page size in bytes = 32768

Extended block size (page) = 32

Prefetch size (page) = 32

Number of containers = 1

The value of pagesize is already large, so you can consider increasing the tablespace.

The method of expanding existing tablespace in DB2

1) add an example of a container directly:

Db2 "ALTER TABLESPACE TPMS ADD (DEVICE'/ dev/rhdisk9' 10000)"

After adding the container, DB2 will have an automatic balance process, which may last for several hours! There is a certain risk in doing online!

2) change the size of the existing container (this method will not trigger balance, but expand the bare device space if the tablespace is built on the bare device):

Db2 "ALTER TABLESPACE TPMS RESIZE (FILE'/ conts/cont0' 2000, DEVICE'/ dev/rcont1' 2000, FILE 'cont2' 2000)"

Note that this way is to change the original corresponding containers to a size of 2000 pages.

Or consider reducing the length of one or more columns to reduce the row length.

For example, the SQL statement executed is

Alter table tpms.tpms_target alter column "target_value_by" set data type varchar (4000)

If you report an error, you can set a small value of varchar (2000) so that the execution is successful. If 2000 can not be put down, it can be divided into two tables, primary key, field; primary key, other fields

The maximum length of the varchar type is 8000, and if you have larger data, you can also consider the long varchar or clob type.

Finally, the error in this example is resolved by setting the CLOB type.

Alter table tpms.tpms_target alter column "target_value_by" set data type clob (4000)

Add: the maximum length of varchar is determined by the table space of DB2, and the size of a row of data in DB2 cannot exceed the pagesize of table space. The size of clob,dbclob and blob is 2GB.

In general, in order to improve performance, the database needs to create a table space specifically for storing large fields, and the large field columns of the data table should store the data in the corresponding table space. This is because it is not read directly through memory (buffer pool).

Limitations of DB2

1. The maximum number of columns in a table

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.