DB2 常见问题排查

    排查

    根据 描述：“因为先前备份或复原不完整，所以不能连接或激活数据库 名称。”

    解决

    直接删除了数据，再执行还原该数据库

    2. DB20000I  The DROP DATABASE command completed successfully.

    还原完后不能连接到数据库

    问题

    从备份中恢复DB2数据库（本例是指“NECC_DB”）的拷贝到新的DB2服务器（本例是指“NECC_DBA”）。用户已创建一个新的数据库连接（指向这个新的数据库副本），当试图连接这个新的数据库时，提示如下错误:

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

    出现SQL1117N 由于 ROLL-FORWARD PENDING，不能连接或激活数据库 “NECC_DBA”。

    解决

    将数据库前滚。

    执行:

    如：

    问题

    执行 db2 语句创建或者删除数据库操作，均出现如下提示

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

    排查

    用户的磁盘不够了。增加用户磁盘，或者删除一些数据，来增大用户能使用的空间。

    删除了没有用的数据库和老旧的数据库备份文件。

    事务日志已经满

    问题

    执行 db2 语句创建数据库索引、或者执行修改操作，均出现如下提示

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

    排查

    通过 db2 => get db cfg 语句来查看日志文件的配置：

    解决

    把日志文件的大小和日志文件的数量扩充。

    1. db2 => update db cfg using LOGFILSIZ 10240
    2. DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.
    3. SQL1363W  Database must be deactivated and reactivated before the changes to 
    4. one or more of the configuration parameters will be effective.
    5. db2 => update db cfg using LOGPRIMARY 100
    6. DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.
    7. SQL1363W  Database must be deactivated and reactivated before the changes to 
    8. one or more of the configuration parameters will be effective.
    9. db2 => update db cfg using LOGSECOND 100  
    10. DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.
    11. db2 =>

    连接到数据库时，出现如下提示

    1. 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

    创建字段 varchar 设置过大

    问题

    创建字段时，varchar 设置为 500 ，报错 

    1. db2 => connect to necc_db
    3.    Database Connection Information
    5.  Database server        = DB2/LINUXX8664 10.1.0
    6.  SQL authorization ID   = DB2INST
    7.  Local database alias   = NECC_DB
    9. db2 => LIST TABLESPACES SHOW DETAIL
    11.            Tablespaces for Current Database
    13.  Tablespace ID                        = 0
    15.  Type                                 = Database managed space
    16.  Contents                             = All permanent data. Regular table space.
    17.  State                                = 0x0000
    18.    Detailed explanation:
    19.      Normal
    20.  Total pages                          = 32768
    21.  Useable pages                        = 32764
    22.  Used pages                           = 32244
    23.  Free pages                           = 520
    24.  High water mark (pages)              = 32244
    25.  Page size (bytes)                    = 4096
    26.  Extent size (pages)                  = 4
    27.  Prefetch size (pages)                = 4
    28.  Number of containers                 = 1
    30.  Tablespace ID                        = 1
    31.  Name                                 = TEMPSPACE1
    32.  Type                                 = System managed space
    33.  Contents                             = System Temporary data
    34.  State                                = 0x0000
    35.    Detailed explanation:
    36.      Normal
    37.  Total pages                          = 1
    38.  Useable pages                        = 1
    39.  Used pages                           = 1
    40.  Free pages                           = Not applicable
    41.  Page size (bytes)                    = 4096
    42.  Extent size (pages)                  = 32
    43.  Prefetch size (pages)                = 32
    44.  Number of containers                 = 1
    46.  Tablespace ID                        = 2
    48.  Type                                 = Database managed space
    49.  Contents                             = All permanent data. Large table space.
    50.  State                                = 0x0000
    51.    Detailed explanation:
    52.      Normal
    53.  Total pages                          = 16384
    54.  Useable pages                        = 16352
    55.  Used pages                           = 14048
    56.  Free pages                           = 2304
    57.  High water mark (pages)              = 14048
    58.  Page size (bytes)                    = 4096
    59.  Extent size (pages)                  = 32
    60.  Prefetch size (pages)                = 32
    61.  Number of containers                 = 1
    63.  Tablespace ID                        = 3
    64.  Name                                 = SYSTOOLSPACE
    65.  Type                                 = Database managed space
    66.  Contents                             = All permanent data. Large table space.
    67.  State                                = 0x0000
    68.    Detailed explanation:
    69.      Normal
    70.  Total pages                          = 8192
    71.  Useable pages                        = 8188
    72.  Used pages                           = 192
    73.  Free pages                           = 7996
    74.  High water mark (pages)              = 192
    75.  Page size (bytes)                    = 4096
    76.  Extent size (pages)                  = 4
    77.  Prefetch size (pages)                = 4
    78.  Number of containers                 = 1
    80. db2 =>

    ALTER TABLESPACE tablespacename CONVERT TO LARGE