DB2 issues anlysis

 1 . DB21032N , SQLSTATE=58031   ---->The database directory cannot be found on the indicated file system.

 

       Anlysis : IP ,HOSTNAME have been changed ,or db2cc can't find license issue.

 

       Method:

1.1  Change hostname|ip  in /etc/hosts:

• Check the HOSTNAME by typing :# env |grep HOSTNAME
• Check the IP : #ifconfig
• Vi '/etc/hosts'  ：127.0.0.1   $HOSTNAME

1.2 Install DB2 license

• unzip DB2 installer
• license is in :/server/db2/license/db2ese_t.lic
• /opt/ibm/db2/V9.5/adm/:# ./db2licm -a /$directory/server/db2/license/db2ese_t.lic

2.SQLCODE=-954 , SQLSTATE=57011

 

   Method:

• Increase the database configuration parameter (applheapsz) to allow a larger application heap.
• Typing: #db2 update db cfg for $databaseName using APPLHEAPSZ 3200

3.SQLCODE=-1585

 

•    Method : Add systemspace in tablespaces (this is system temp space added in configuration)

 

(new database needs tablespace and Applheapsz: System temp space,user space,buffer pool and Applheapsz)

4. SQLCODE=-206 , SQLSTATE=42703

 

    Method : Check the columns, if one column is not in table,then add it.

• # alter table db2inst1.($TableName) add column ($ColumnName)  ($Type)

5.SQLCODE=-973 , SQLSTATE=57011 ,SQLERRMC=APP_CTL_HEAP_SZ ,Driver=3.50.152 ,DSRA0010E (change the value what $SQLERRMC is)

 

   Method ：increase heap size

• Typing ： db2 update db cfg for <db-name> using <heap-name> <heap-size>
• or Typing ： ./db2 update dbm cfg using DRDA_HEAP_SZ 6144
• Reference : heapsize :APPGROUP_MEM_SZ , GROUPHEAP_RATIO , APP_CTL_HEAP_SZ
• http://publib.boulder.ibm.com/infocenter/tivihelp/v4r1/index.jsp?topic=/com.ibm.tpc_V411.doc/fqz0_r_tbs_db2_sql_973.html

6.SQLCODE=-805 , SQLSTATE=51002 , SQLERRMC: NULL2D.SYSH203 OX5359534C564C3031

 

    Anlysis : it can't find DBRM or program packagename

 

    Method :

• Typing: #db2
• db2 => bind /opt/ibm/db2/V9.5/bnd/db2clipk.bnd SQLERROR CONTINUE BLOCKING ALL GRANT PUBLIC
  
  LINE    MESSAGES FOR db2clipk.bnd
  ------  --------------------------------------------------------------------
          SQL0061W  The binder is in progress.
          SQL0091N  Binding was ended with "0" errors and "0" warnings.
• db2 => bind /opt/ibm/db2/V9.5/bnd/db2clist.bnd SQLERROR CONTINUE BLOCKING ALL GRANT PUBLIC
  
  LINE    MESSAGES FOR db2clist.bnd
  ------  --------------------------------------------------------------------
          SQL0061W  The binder is in progress.
          SQL0091N  Binding was ended with "0" errors and "0" warnings.
• db2 => bind /opt/ibm/db2/V9.5/bnd/@db2cli.lst  SQLERROR CONTINUE BLOCKING ALL GRANT PUBLIC CLIPKG 4
  
  LINE    MESSAGES FOR db2cli.lst
  ------  --------------------------------------------------------------------
          SQL0061W  The binder is in progress.
          SQL0091N  Binding was ended with "0" errors and "0" warnings.
• DB2CLI.LST contains a list that should include two bind files: 

                                                                                                          db2clipk.bnd+

                                                                                                          db2clist.bnd

7. SQL0289N  , SQLSTATE=57011,Unable to allocate new pages in table space "USERSPACE1". 

 

    Anlysis : Unable to allocate new pages in table space "<tablespace-name>".

 

   Reference : http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admi/t0005097.htm      

                    1> Either switch to a DMS TABLESPACE or recreate the SMS TABLESPACE with more directories (PATHs) such that: (number of directories) >= (max tablesize / maxfilesize).  Note that maximum file size is operating system dependent. 

                    2> Increase the maximum size for the table space.

                    3> Add new container(s) to the DMS table space and try the operation again, after the rebalancer has made the new pages available for use. 

 

  Method ：Change the db2log file path

• chmod -R 777 /data   (AIX intance,/data has more memory and capacity.chmod -R 是为该目录增加其他用户读、写、操作权限 )
• /opt/IBM/db2/V9.1/bin: # su db2inst1
• /opt/IBM/db2/V9.1/bin:$./db2 UPDATE DB CFG FOR pos USING logarchmeth1 OFF logarchmeth2 OFF logprimary 13 logsecond 4 logfilsiz 8000 newlogpath /data   （pos database 的cfg 中 logprimary=13， logsecond= 4 ，logfilsiz= 8000 )

8.SQL1005N .  The database alias "test" already exists in either the local database directory or the system database

  

    Anlysis  : 重新编目数据库：（当删除数据库后，重新创建时出错，报错信息为数据库已存在）

• catalog database oxfamadm on C: （windows)
• drop database [dbname]
• create db [dbname]

9.SQL0964C ,SQLSTATE=57011  The transaction log for the database is full.

    Anlysis :

 

If a circular log with secondary log files is being used, an attempt has been made to allocate and use     them. When the file system has no more
space, secondary logs cannot be used.

Method :Enlarge LOGSIZE,LOGPRIMARY,LOGSECOND

• Get the original CFG file of database
  • Catalog cache size (4KB) (CATALOGCACHE_SZ) = 300
  • Log buffer size (4KB) (LOGBUFSZ) = 256
  • Log file size (4KB) (LOGFILSIZ) = 1024
  • Number of primary log files (LOGPRIMARY) = 13
  • Number of secondary log files (LOGSECOND) = 4
• Update the CFG file of database
  • db2 update db cfg for OXFAMUAT using LOGFILSIZ 10240
  • db2 update db cfg for OXFAMUAT using LOGPRIMARY 20
  • db2 update db cfg for OXFAMUAT using LOGSECEND 30
• Check CFG file again
  • Catalog cache size (4KB) (CATALOGCACHE_SZ) = 300
  • Log buffer size (4KB) (LOGBUFSZ) = 256
  • Log file size (4KB) (LOGFILSIZ) = 10240
  • Number of primary log files (LOGPRIMARY) = 20
  • Number of secondary log files (LOGSECOND) = 30