db2 重装

【Install Note】

1、如果许可证还没有过期 就直接按照顺序先删除DB 然后删除实例 最后卸载数据库，在此之前先导出数据。

2、如果已经过期的话 先调整系统时间 提前一个月 然后db2start 尝试启动服务。

灰色字体 系统输出

蓝色字体 输入命令

红色字体 注释部分

 

Before Uninstall

1、db2 list db directory 浏览数据库

-bash-3.1$ db2 list db directory

 

 System Database Directory

 

 Number of entries in the directory = 3

 

Database 1 entry:

 

 Database alias                       = SHIPMENT

 Database name                        = SHIPMENT

 Local database directory             = /usr/local/db2home/db2inst1

 Database release level               = d.00

 Comment                              =

 Directory entry type                 = Indirect

 Catalog database partition number    = 0

 Alternate server hostname            =

 Alternate server port number         =

 

Database 2 entry:

 

 Database alias                       = GLOBALT

 Database name                        = GLOBALT

 Local database directory             = /usr/local/db2home/db2inst1

 Database release level               = d.00

 Comment                              =

 Directory entry type                 = Indirect

 Catalog database partition number    = 0

 Alternate server hostname            =

 Alternate server port number         =

 

Database 3 entry:

 

 Database alias                       = SAMPLE

 Database name                        = SAMPLE @可有可无

 Local database directory             = /usr/local/db2home/db2inst1

 Database release level               = d.00

 Comment                              =

 Directory entry type                 = Indirect

 Catalog database partition number    = 0

 Alternate server hostname            =

 Alternate server port number         =

 

 

2、  Backup DDL.Script & DATA

@先停掉/etc/crontab中有关系统时间自动校正的任务

bash-3.1$ more /etc/crontab

SHELL=/bin/bash

PATH=/sbin:/bin:/usr/sbin:/usr/bin

MAILTO=root

HOME=/

 

# run-parts

01 * * * * root run-parts /etc/cron.hourly

##31 * * * * root /usr/local/ntpd/bin/ntpdate 192.168.199.11

02 4 * * * root run-parts /etc/cron.daily

22 4 * * 0 root run-parts /etc/cron.weekly

42 4 1 * * root run-parts /etc/cron.monthly

--准备备份用的目录

cd /usr/local/db2home

su db2inst1

mkdir 2011_11_22

mkdir 2011_11_22/SHIPMENT_DATA

mkdir 2011_11_22/GLOBALT_DATA

 

-- DDL 导出的SP会有缺失！！！使用客户端后期部署上去即可！！！

db2look -d SHIPMENT -e -l -i db2inst1 -w phxsha123 -o SHIPMENT_DDL_20111122.sql

--DATA EXTPORT

cd /usr/local/db2home/2011_11_22/SHIPMENT_DATA

db2move SHIPMENT EXPORT -tc db2inst1 -u db2inst1 -p phxsha123 -l /usr/local/db2home/2011_11_22/SHIPMENT_DATA

 

db2look -d GLOBALT -e -l -i db2inst1 -w phxsha123 -o GLOBALT_DDL_20111122.sql

db2look -d GLOBALT -i db2inst1 -w phxsha123 -e -l -o GLOBALT_DDL_2011_02_21.sql

cd /usr/local/db2home/2011_11_22/GLOBALT_DATA

db2move GLOBALT EXPORT -u db2inst1 -p phxsha123 -l /usr/local/db2home/2011_11_22/GLOBALT_DATA

 

Uninstall DB2 V9

 

--删除数据库

bash-3.1$ db2 drop db SHIPMENT

DB20000I  The DROP DATABASE command completed successfully.

bash-3.1$ db2 drop db GLOBALT

SQL1035N  The database is currently in use.  SQLSTATE=57019

bash-3.1$ db2 drop db GLOBALT

SQL1035N  The database is currently in use.  SQLSTATE=57019

@有错误 是因为还有Application在读取和操作数据库 断开应用连接后再删除

@或使用“db2 list application”先确认一下是否有Application再操作数据库

bash-3.1$ db2 force application all

DB20000I  The FORCE APPLICATION command completed successfully.

DB21024I  This command is asynchronous and may not be effective immediately.

 

bash-3.1$ db2 drop db GLOBALT

DB20000I  The DROP DATABASE command completed successfully.

bash-3.1$ db2 drop db SAMPLE

DB20000I  The DROP DATABASE command completed successfully.

 

--删除实例 @需要root权限

[root@global-tracking 2011_11_22]# cd /usr/local/db2V9/instance

[root@global-tracking instance]# ./db2ilist

db2inst1

[root@global-tracking instance]# ./db2idrop  db2inst1

DBI1324W  Support of the -f command is deprecated. For

      more information, see the DB2 Information Center.

 

 

DB20000I  The FORCE APPLICATION command completed successfully.

DB21024I  This command is asynchronous and may not be effective immediately.

 

DBI1070I  Program db2idrop completed successfully.

 

 

--卸载 @需要root权限

[root@global-tracking instance]# cd /usr/local/db2V9/install/

[root@global-tracking install]#  ./db2_deinstall -a

DBI1016I  Program db2_deinstall is performing uninstallation. Please

      wait.

 

 

The execution completed successfully.

 

For more information see the DB2 uninstallation log at

"/tmp/db2_deinstall.log.727".

[root@global-tracking install]#

 

Install DB2 V9

1、  先同步系统时间 & 同时开启卸载前停掉的时间同步的Crontab任务

/usr/local/ntpd/bin/ntpdate 192.168.199.11

2、  安装服务端在 68上的/root目录中

档名：v9.7_linuxx64_server.tar.gz

解压缩后是 /root/server  -- @可以直接使用 无需再解压缩但前提是安装的时候选择其他路径

 

[root@global-tracking ~]# cd server/

[root@global-tracking server]# ls -lah

total 104K

drwxr-xr-x  4 root root 4.0K Jun  1  2009 .

drwxr-x---  7 root root 4.0K Aug 23 10:47 ..

drwxr-xr-x  6 bin  bin  4.0K Jun  1  2009 db2

-r-xr-xr-x  1 bin  bin  5.2K Jun  1  2009 db2_deinstall

-r-xr-xr-x  1 bin  bin  5.1K Jun  1  2009 db2_install

-r-xr-xr-x  1 bin  bin  5.1K Jun  1  2009 db2ls

-r-xr-xr-x  1 bin  bin  5.1K Jun  1  2009 db2prereqcheck

-r-xr-xr-x  1 bin  bin  5.1K Jun  1  2009 db2setup

drwxr-xr-x 15 bin  bin  4.0K Jun  1  2009 doc

-r-xr-xr-x  1 bin  bin  5.1K Jun  1  2009 installFixPack

 

[root@global-tracking server]# ./db2_install

 

Default directory for installation of products - /opt/ibm/db2/V9.7

 

***********************************************************

Do you want to choose a different directory to install [yes/no] ?

yes     @问是否选择一个不同的目录安装 否则就安装到/root/server上了

Enter full path name for the install directory -

 

------------------------------------------------

/usr/local/db2V9      @填入一个安装路径，这个路径在上次卸载的时候已经删除掉了可以直接使用。

 

 

Specify one of the following keywords to install DB2 products.

 

  ESE

  CONSV

  WSE

  EXP

  PE

  CLIENT

  RTCL

 

Enter "help" to redisplay product names.

 

Enter "quit" to exit.

 

***********************************************************

ESE               @选择ese 企业版

 

 

 

创建实例

/usr/local/db2V9/instance/db2icrt -a server -u db2fenc1 db2inst1

注册profile

[root@global-tracking server]# cd /usr/local/db2home/db2inst1/sqllib/

[root@global-tracking sqllib]# ./db2profile

 

 

创建数据库

bash-3.1$ db2start

11/22/2010 14:35:16     0   0   SQL1063N  DB2START processing was successful.

SQL1063N  DB2START processing was successful.

bash-3.1$ db2

(c) Copyright IBM Corporation 1993,2007

Command Line Processor for DB2 Client 9.7.0

 

You can issue database manager commands and SQL statements from the command

prompt. For example:

    db2 => connect to sample

    db2 => bind sample.bnd

 

For general help, type: ?.

For command help, type: ? command, where command can be

the first few keywords of a database manager command. For example:

 ? CATALOG DATABASE for help on the CATALOG DATABASE command

 ? CATALOG          for help on all of the CATALOG commands.

 

To exit db2 interactive mode, type QUIT at the command prompt. Outside

interactive mode, all commands must be prefixed with 'db2'.

To list the current command option settings, type LIST COMMAND OPTIONS.

 

For more detailed help, refer to the Online Reference Manual.

 

db2 => CREATE DATABASE SHIPMENT USING CODESET UTF-8 TERRITORY US

DB20000I  The CREATE DATABASE command completed successfully.

db2 => CREATE DATABASE GLOBALT USING CODESET UTF-8 TERRITORY US

DB20000I  The CREATE DATABASE command completed successfully.

db2 => CREATE DATABASE SAMPLE USING CODESET UTF-8 TERRITORY US

DB20000I  The CREATE DATABASE command completed successfully.

 

@schema很重要  重构时DDL 和源数据export文件要注意修改。如果数据就是从本机导出来的就不存在这个问题。

@相对的schema有一定的权限。

bash-3.1$ db2 connect to GLOBALT

 

   Database Connection Information

 

 Database server        = DB2/LINUXX8664 9.7.0

 SQL authorization ID   = DB2INST1

 Local database alias   = GLOBALT

 

bash-3.1$ db2 grant dbadm on database to user dwdev

DB20000I  The SQL command completed successfully.

 

LOAD DATA

@ SHIPMENT部分

cd /usr/local/db2home/2011_11_22/

db2 connect to SHIPMENT

db2 -tvf SHIPMENT_DDL_20111122.sql 

cd /usr/local/db2home/2011_11_22/SHIPMENT_DATA

db2move SHIPMENT import -u db2inst1 -p phxsha123 #尽量使用load

@ GLOBALT部分

cd /usr/local/db2home/2011_11_22/

db2 connect to GLOBALT

db2 -tvf GLOBALT_DDL_20111122.sql 

cd /usr/local/db2home/2011_11_22/GLOBALT_DATA

db2move GLOBALT import -u db2inst1 -p phxsha123  #尽量使用load

/*

★If Error -à Pls look here!  

* IMPORT:  table "DWDEV   "."TB_SHIPMENT_MOVEMENT"

***ERROR -3201.  Check message file tab46.msg!

***  SQLCODE:  -3201  -  SQLSTATE:      

***  SQL3201N  The specified table cannot be replaced because another table is dependent on it.

@如果import出现以上错误 就将import命令更换为load

@db2move GLOBALT load -u db2inst1 -p phxsha123

#2011-2-23 Remark @Woody.xu & Joson.Chen

*/

配置jdbc type v4的连接

db2set DB2COMM=TCPIP

 

更新dbm & 配置连接权限

bash-3.1$ db2

(c) Copyright IBM Corporation 1993,2007

Command Line Processor for DB2 Client 9.7.0

 

You can issue database manager commands and SQL statements from the command

prompt. For example:

    db2 => connect to sample

    db2 => bind sample.bnd

 

For general help, type: ?.

For command help, type: ? command, where command can be

the first few keywords of a database manager command. For example:

 ? CATALOG DATABASE for help on the CATALOG DATABASE command

 ? CATALOG          for help on all of the CATALOG commands.

 

To exit db2 interactive mode, type QUIT at the command prompt. Outside

interactive mode, all commands must be prefixed with 'db2'.

To list the current command option settings, type LIST COMMAND OPTIONS.

 

For more detailed help, refer to the Online Reference Manual.

 

db2 => connect to SHIPMENT

 

   Database Connection Information

 

 Database server        = DB2/LINUXX8664 9.7.0

 SQL authorization ID   = DB2INST1

 Local database alias   = SHIPMENT

db2 => update dbm cfg using SVCENAME db2inst1

DB20000I  The UPDATE DATABASE MANAGER CONFIGURATION command completed

successfully.

SQL1362W  One or more of the parameters submitted for immediate modification

were not changed dynamically. Client changes will not be effective until the

next time the application is started or the TERMINATE command has been issued.

Server changes will not be effective until the next DB2START command.

db2 => update dbm cfg using SVCENAME dwetl @此配置会覆盖之前的权限信息。

db2 => quit

db2 terminate

db2stop  

db2start

 

 

@PS：以上流程是在INNER环境中的192.168.103.68上完成的 如果是新安装的Server要多一些添加用户和组的步骤

@欲知详情：You can Google it.

 

安装完记得给dwetl加权限啊！！！

db2 => grant dbadm on database to user dwetl

DB20000I  The SQL command completed successfully.

 

 

 

select * from CONTAINER

Operation not allowed for reason code "1" on table "DB2INST1.CONTAINER".. SQLCODE=-668, SQLSTATE=57016, DRIVER=4.7.89

症状:

Caused by: com.ibm.db2.jcc.b.SqlException: DB2 SQL Error: SQLCODE=-668, SQLSTATE=57016, SQLERRMC=1;DB2INST1.CONTAINER, DRIVER=3.50.152

能帮忙看下吗

这个错误的原因

诊断:

由于该表处于检查暂挂状态时（Check Pending）。     导致的原因是由于之前对该库做过一次数据迁移。使用的方式为db2move。

解决的办法是在表上做一次set integrity命令。且不检查其约束。

药方:

db2 => SET INTEGRITY FOR CONTAINER ALL IMMEDIATE UNCHECKED

DB20000I  The SQL command completed successfully.

 

 

补充版:

db2 => reorg table DWDEV.TB_REPORT_TEMPLATE_RESULT_FIELD

SQL2216N  SQL error "-668" occurred while reorganizing a database table or its

indexes.

db2 => SET INTEGRITY FOR DWDEV.TB_REPORT_TEMPLATE_RESULT_FIELD ALL IMMEDIATE UNCHECKED

DB20000I  The SQL command completed successfully.

db2 => reorg table DWDEV.TB_REPORT_TEMPLATE_RESULT_FIELD

DB20000I  The REORG command completed successfully.

 

 

2011年11月7日星期一 补充版 安装完成后 新建DB时报SQL1005N错误的解决方法。

db2 => CREATE DATABASE SHIPMENT USING CODESET UTF-8 TERRITORY US

SQL1005N  The database alias "SHIPMENT" already exists in either the local

database directory or system database directory.

db2 => CREATE DATABASE GLOBALT USING CODESET UTF-8 TERRITORY US

DB20000I  The CREATE DATABASE command completed successfully.

db2 => quit

DB20000I  The QUIT command completed successfully.

-bash-3.1$ db2 CATALOG DB GC503

SQL6028N  Catalog database failed because database "GC503" was not found in

the local database directory.

-bash-3.1$ db2 CATALOG DB SHIPMENT

DB20000I  The CATALOG DATABASE command completed successfully.

DB21056W  Directory changes may not be effective until the directory cache is

refreshed.

-bash-3.1$ db2 drop database SHIPMENT

DB20000I  The DROP DATABASE command completed successfully.

 

2012-7-23参上！

建议每次安装之前把如下几张表重新整理一下碎片，语法已经准备好了，诸君请用！

SET INTEGRITY FOR DB2INST1.ADVISE_INDEX ALL IMMEDIATE UNCHECKED

reorg table DB2INST1.ADVISE_INDEX

 

SET INTEGRITY FOR DB2INST1.ADVISE_MQT ALL IMMEDIATE UNCHECKED

reorg table DB2INST1.ADVISE_MQT

 

SET INTEGRITY FOR DB2INST1.ADVISE_PARTITION ALL IMMEDIATE UNCHECKED

reorg table DB2INST1.ADVISE_PARTITION

 

SET INTEGRITY FOR DB2INST1.ADVISE_TABLE ALL IMMEDIATE UNCHECKED

reorg table DB2INST1.ADVISE_TABLE

 

SET INTEGRITY FOR DB2INST1.EXPLAIN_ARGUMENT ALL IMMEDIATE UNCHECKED

reorg table DB2INST1.EXPLAIN_ARGUMENT

 

SET INTEGRITY FOR DB2INST1.EXPLAIN_DIAGNOSTIC ALL IMMEDIATE UNCHECKED

reorg table DB2INST1.EXPLAIN_DIAGNOSTIC

 

SET INTEGRITY FOR DB2INST1.EXPLAIN_DIAGNOSTIC_DATA ALL IMMEDIATE UNCHECKED

reorg table DB2INST1.EXPLAIN_DIAGNOSTIC_DATA

 

SET INTEGRITY FOR DB2INST1.EXPLAIN_OBJECT ALL IMMEDIATE UNCHECKED

reorg table DB2INST1.EXPLAIN_OBJECT

 

SET INTEGRITY FOR DB2INST1.EXPLAIN_OPERATOR ALL IMMEDIATE UNCHECKED

reorg table DB2INST1.EXPLAIN_OPERATOR

 

SET INTEGRITY FOR DB2INST1.EXPLAIN_PREDICATE ALL IMMEDIATE UNCHECKED

reorg table DB2INST1.EXPLAIN_PREDICATE

 

SET INTEGRITY FOR DB2INST1.EXPLAIN_STATEMENT ALL IMMEDIATE UNCHECKED

reorg table DB2INST1.EXPLAIN_STATEMENT

 

SET INTEGRITY FOR DB2INST1.EXPLAIN_STREAM ALL IMMEDIATE UNCHECKED

reorg table DB2INST1.EXPLAIN_STREAM

 

SET INTEGRITY FOR DWDEV.ADVISE_INDEX ALL IMMEDIATE UNCHECKED

reorg table DWDEV.ADVISE_INDEX

 

SET INTEGRITY FOR DWDEV.ADVISE_MQT ALL IMMEDIATE UNCHECKED

reorg table DWDEV.ADVISE_MQT

 

SET INTEGRITY FOR DWDEV.ADVISE_PARTITION ALL IMMEDIATE UNCHECKED

reorg table DWDEV.ADVISE_PARTITION

 

SET INTEGRITY FOR DWDEV.ADVISE_TABLE ALL IMMEDIATE UNCHECKED

reorg table DWDEV.ADVISE_TABLE

 

SET INTEGRITY FOR DWDEV.EXPLAIN_ARGUMENT ALL IMMEDIATE UNCHECKED

reorg table DWDEV.EXPLAIN_ARGUMENT

 

SET INTEGRITY FOR DWDEV.EXPLAIN_DIAGNOSTIC ALL IMMEDIATE UNCHECKED

reorg table DWDEV.EXPLAIN_DIAGNOSTIC

 

SET INTEGRITY FOR DWDEV.EXPLAIN_DIAGNOSTIC_DATA ALL IMMEDIATE UNCHECKED

reorg table DWDEV.EXPLAIN_DIAGNOSTIC_DATA

 

SET INTEGRITY FOR DWDEV.EXPLAIN_OBJECT ALL IMMEDIATE UNCHECKED

reorg table DWDEV.EXPLAIN_OBJECT

 

SET INTEGRITY FOR DWDEV.EXPLAIN_OPERATOR ALL IMMEDIATE UNCHECKED

reorg table DWDEV.EXPLAIN_OPERATOR

 

SET INTEGRITY FOR DWDEV.EXPLAIN_PREDICATE ALL IMMEDIATE UNCHECKED

reorg table DWDEV.EXPLAIN_PREDICATE

 

 

SET INTEGRITY FOR DWDEV.EXPLAIN_STATEMENT ALL IMMEDIATE UNCHECKED

reorg table DWDEV.EXPLAIN_STATEMENT

 

SET INTEGRITY FOR DWDEV.EXPLAIN_STREAM ALL IMMEDIATE UNCHECKED

reorg table DWDEV.EXPLAIN_STREAM

 

SET INTEGRITY FOR DWDEV.TB_NOTIFICATION_EVENT ALL IMMEDIATE UNCHECKED

reorg table DWDEV.TB_NOTIFICATION_EVENT

 

SET INTEGRITY FOR DWDEV.TB_SHIPMENT_MOVEMENT_DET ALL IMMEDIATE UNCHECKED

reorg table DWDEV.TB_SHIPMENT_MOVEMENT_DET

 

121227补充 : 修改配置参数大小

* LOAD:  table "DB2INST1"."CARGODETAILHBL"

***  ERROR -964.  Check message file tab14.msg!

***  SQLCODE:  -964  -  SQLSTATE:  57011

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

 

db2 update db cfg for ceshi using LOGFILSIZ 2048

DB20000I  UPDATE DATABASE CONFIGURATION 命令成功完成。

 

db2 update db cfg for ceshi using LOGPRIMARY 20

DB20000I  UPDATE DATABASE CONFIGURATION 命令成功完成。

 

db2 update db cfg for ceshi using LOGSECOND 5

DB20000I  UPDATE DATABASE CONFIGURATION 命令成功完成。

查看：db2 get db cfg for ceshi

…..

 Log file size (4KB)                         (LOGFILSIZ) = 1024

 Number of primary log files                (LOGPRIMARY) = 13

 Number of secondary log files               (LOGSECOND) = 4

…..

...

..

 

重启 db2stop force

db2start 再次db2move load 即可