Analysis of SQL n errors in DB2 Databases

When a developer modifies a table, the DB2 database reports the SQL0670N error. The specific information is as follows: how is this error caused? How can this problem be solved? First, let's take a look at the explanation of this error in the DB2 database.

When a developer modifies a table, the DB2 database reports the SQL0670N error. The specific information is as follows: how is this error caused? How can this problem be solved? First, let's take a look at the explanation of this error in the DB2 database.

When a developer modifies a table, the DB2 database reports the SQL0670N error. The details are as follows:

How is this error caused? How can this problem be solved?

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

In a tablespace with a 32 K page size, the row length cannot exceed 32677 bytes.

To solve this problem, we will naturally think of increasing the size of the tablespace restricted by the length of a large row or setting a large pagesize. Of course, we can also consider reducing the length of one or more columns to reduce the length of the row.

First, view the specific information of the tablespace:

$ Db2 list tablespaces show detail

Tablespace id = 9

Name = TPMS

Type = system management space

Content = any data

Status = 0x0000

Explanation:

Normal

Total pages = 2084

Available pages = 2084

Used pages = 2084

Unused pages = not applicable

High water mark (PAGE) = not applicable

Page size (in bytes) = 32768

Extended data block size (PAGE) = 32

Prefetch size (PAGE) = 32

Container COUNT = 1

The value of pagesize is already relatively large. You can consider increasing the tablespace.

How to expand the existing table space in DB2
1) Example of directly adding a container:

Db2 "alter tablespace tpms add (DEVICE '/dev/rhdisk9' 10000 )"
After adding a container, DB2 will have an automatic balance process, which may last for several hours !!! Online operations are at risk!

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

Db2 "alter tablespace tpms resize (FILE '/conts/cont0' 2000, devic'/dev/rcont1 '2000, FILE 'cont2' 2000 )"

Note that this method is to change the size of the original container to 2000 pages.

You can also reduce the length of one or more columns to reduce the row length.

For example, the executed SQL statement is

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

If an error is reported, you can set a smaller value of varchar (2000), so that the execution is successful. If 2000 still does not fit, it can be divided into two tables: primary key and field; primary key and other fields

The maximum length of the varchar type is 8000. If there is more data, you can also consider the long varchar or clob type.

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

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

Supplement: the maximum length of varchar is determined by the DB2 tablespace. The size of a row of data in DB2 cannot exceed the pagesize of the tablespace. The clob, dbclob, and blob sizes are 2 GB.

Generally, to improve performance, the database needs to create a tablespace for storing enlarged fields. The big field column of the data table should store the data in the corresponding tablespace, this is because it is directly read without going through the memory (buffer pool.

DB2 restrictions
1. Maximum number of columns in a table <= 1012
2. Maximum number of columns in a view <= 5000
3. Maximum length (in bytes) of a row <= 32677
4. Maximum table size in each partition (512 bytes) <=
5. maximum size of indexes in each partition (512 bytes) <=
6. Maximum number of rows in a table in each partition <= 4000000000
7. Longest index keyword (number of bytes) <= 1024
8. Maximum number of columns in an index keyword <= 16
9. Maximum number of indexes in a table <= 32767 or memory
10. the maximum number of tables referenced in an SQL statement or view <= memory collector

This article is from the "dripping water and stone" blog. Please keep this source