閱讀(1.9k) 書簽贊(0) 我要糾錯(cuò)

DB2 常見問題排查

2021-08-16 09:40 更新

DB2 常見問題排查
- 備份或者還原未完成
  - 問題
  - 排查
  - 解決
- 還原完后不能連接到數(shù)據(jù)庫
  - 問題
  - 排查
  - 解決
- 空間不足不能執(zhí)行命令
  - 問題
  - 排查
  - 解決
- 事務(wù)日志已經(jīng)滿
  - 問題
  - 排查
  - 解決
- 連接數(shù)據(jù)庫，分配共享內(nèi)容失敗，內(nèi)核限制
  - 問題
- 創(chuàng)建字段 varchar 設(shè)置過大
  - 問題
- 參考引用

DB2 常見問題排查

備份或者還原未完成

問題

執(zhí)行操作，提示如下：

[db2inst@localhost ~]$ db2 deactivate db necc_db
SQL1120N  A connection to or activation of database "NECC_DB" cannot be made 
because a previous backup or restore is incomplete.  SQLSTATE=57019

排查

根據(jù) https://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.messages.sql.doc/doc/msql01120n.html描述：“因?yàn)橄惹皞浞莼驈?fù)原不完整，所以不能連接或激活數(shù)據(jù)庫名稱?！?/p>

解決

直接刪除了數(shù)據(jù)，再執(zhí)行還原該數(shù)據(jù)庫

[db2inst@localhost ~]$ db2 drop db necc_db
DB20000I  The DROP DATABASE command completed successfully.

還原完后不能連接到數(shù)據(jù)庫

問題

從備份中恢復(fù)DB2數(shù)據(jù)庫（本例是指“NECC_DB”）的拷貝到新的DB2服務(wù)器（本例是指“NECC_DBA”）。用戶已創(chuàng)建一個(gè)新的數(shù)據(jù)庫連接（指向這個(gè)新的數(shù)據(jù)庫副本），當(dāng)試圖連接這個(gè)新的數(shù)據(jù)庫時(shí)，提示如下錯(cuò)誤:

[db2inst@localhost ~]$ db2 connect to necc_db
SQL1117N  A connection to or activation of database "NECC_DB" cannot be made 
because of ROLL-FORWARD PENDING.  SQLSTATE=57019

排查

出現(xiàn)SQL1117N 由于 ROLL-FORWARD PENDING，不能連接或激活數(shù)據(jù)庫 “NECC_DBA”。

解決

將數(shù)據(jù)庫前滾。

執(zhí)行:

db2 rollforward db <database> complete

如：

[db2inst@localhost ~]$ db2 rollforward db necc_dba complete
                                 Rollforward Status
 Input database alias                   = necc_dba
 Number of members have returned status = 1
 Member ID                              = 0
 Rollforward status                     = not pending
 Next log file to be read               =
 Log files processed                    =  -
 Last committed transaction             = 2016-04-27-09.18.11.000000 UTC
DB20000I  The ROLLFORWARD command completed successfully.
[db2inst@localhost ~]$

空間不足不能執(zhí)行命令

問題

執(zhí)行 db2 語句創(chuàng)建或者刪除數(shù)據(jù)庫操作，均出現(xiàn)如下提示

SQL1004C  There is not enough storage on the file system to process the command.

排查

用戶的磁盤不夠了。增加用戶磁盤，或者刪除一些數(shù)據(jù)，來增大用戶能使用的空間。

解決

刪除了沒有用的數(shù)據(jù)庫和老舊的數(shù)據(jù)庫備份文件。

事務(wù)日志已經(jīng)滿

問題

執(zhí)行 db2 語句創(chuàng)建數(shù)據(jù)庫索引、或者執(zhí)行修改操作，均出現(xiàn)如下提示

DB2 Database Error: ERROR [57011] [IBM][DB2/LINUXX8664] SQL0964C The transaction log for the database is full.

排查

事務(wù)日志已滿。

通過 db2 => get db cfg 語句來查看日志文件的配置：

 Log file size (4KB)                         (LOGFILSIZ) = 1024
 Number of primary log files                (LOGPRIMARY) = 13
 Number of secondary log files               (LOGSECOND) = 12

解決

把日志文件的大小和日志文件的數(shù)量擴(kuò)充。

db2 => update db cfg using LOGFILSIZ 10240
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.
SQL1363W  Database must be deactivated and reactivated before the changes to 
one or more of the configuration parameters will be effective.
db2 => update db cfg using LOGPRIMARY 100
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.
SQL1363W  Database must be deactivated and reactivated before the changes to 
one or more of the configuration parameters will be effective.
db2 => update db cfg using LOGSECOND 100  
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.
db2 =>

連接數(shù)據(jù)庫，分配共享內(nèi)容失敗，內(nèi)核限制

問題

連接到數(shù)據(jù)庫時(shí)，出現(xiàn)如下提示

ERROR [57019] [IBM] SQL1084C  The database manager failed to allocate shared memory because an operating system kernel memory limit has been reached.  SQLSTATE=57019

http://www.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.messages.sql.doc/doc/msql01084c.html

創(chuàng)建字段 varchar 設(shè)置過大

問題

創(chuàng)建字段時(shí)，varchar 設(shè)置為 500 ，報(bào)錯(cuò)

SQL 錯(cuò)誤 [42727]: A table space could not be found with a page size of at least "8192" that authorization ID "DB2INST" is authorized to use.. SQLCODE=-286, SQLSTATE=42727, DRIVER=4.16.53
  com.ibm.db2.jcc.am.SqlSyntaxErrorException: A table space could not be found with a page size of at least "8192" that authorization ID "DB2INST" is authorized to use.. SQLCODE=-286, SQLSTATE=42727, DRIVER=4.16.53

http://www.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.admin.dbobj.doc/doc/c0061057.html
http://www.db2china.net/Question/39301
http://www.west.cn/www/info/28275-1.htm

db2 => connect to necc_db
   Database Connection Information
 Database server        = DB2/LINUXX8664 10.1.0
 SQL authorization ID   = DB2INST
 Local database alias   = NECC_DB
db2 => LIST TABLESPACES SHOW DETAIL
           Tablespaces for Current Database
 Tablespace ID                        = 0
 Name                                 = SYSCATSPACE
 Type                                 = Database managed space
 Contents                             = All permanent data. Regular table space.
 State                                = 0x0000
   Detailed explanation:
     Normal
 Total pages                          = 32768
 Useable pages                        = 32764
 Used pages                           = 32244
 Free pages                           = 520
 High water mark (pages)              = 32244
 Page size (bytes)                    = 4096
 Extent size (pages)                  = 4
 Prefetch size (pages)                = 4
 Number of containers                 = 1
 Tablespace ID                        = 1
 Name                                 = TEMPSPACE1
 Type                                 = System managed space
 Contents                             = System Temporary data
 State                                = 0x0000
   Detailed explanation:
     Normal
 Total pages                          = 1
 Useable pages                        = 1
 Used pages                           = 1
 Free pages                           = Not applicable
 High water mark (pages)              = Not applicable
 Page size (bytes)                    = 4096
 Extent size (pages)                  = 32
 Prefetch size (pages)                = 32
 Number of containers                 = 1
 Tablespace ID                        = 2
 Name                                 = USERSPACE1
 Type                                 = Database managed space
 Contents                             = All permanent data. Large table space.
 State                                = 0x0000
   Detailed explanation:
     Normal
 Total pages                          = 16384
 Useable pages                        = 16352
 Used pages                           = 14048
 Free pages                           = 2304
 High water mark (pages)              = 14048
 Page size (bytes)                    = 4096
 Extent size (pages)                  = 32
 Prefetch size (pages)                = 32
 Number of containers                 = 1
 Tablespace ID                        = 3
 Name                                 = SYSTOOLSPACE
 Type                                 = Database managed space
 Contents                             = All permanent data. Large table space.
 State                                = 0x0000
   Detailed explanation:
     Normal
 Total pages                          = 8192
 Useable pages                        = 8188
 Used pages                           = 192
 Free pages                           = 7996
 High water mark (pages)              = 192
 Page size (bytes)                    = 4096
 Extent size (pages)                  = 4
 Prefetch size (pages)                = 4
 Number of containers                 = 1
db2 =>

ALTER TABLESPACE tablespacename CONVERT TO LARGE

參考引用

http://www-01.ibm.com/support/docview.wss?uid=swg21962486
http://www.cnblogs.com/frankliiu-java/articles/2186343.html

以上內(nèi)容是否對您有幫助：

← DB2 備份與還原

寫筆記

我要補(bǔ)充

DB2 常見問題排查

DB2 常見問題排查

備份或者還原未完成

問題

排查

解決

還原完后不能連接到數(shù)據(jù)庫

問題

排查

解決

空間不足不能執(zhí)行命令

問題

排查

解決

事務(wù)日志已經(jīng)滿

問題

排查

解決

連接數(shù)據(jù)庫，分配共享內(nèi)容失敗，內(nèi)核限制

問題

創(chuàng)建字段 varchar 設(shè)置過大

問題

參考引用

連接數(shù)據(jù)庫，分配共享內(nèi)容失敗，內(nèi)核限制